10G ARCHIVELOG寫滿問題
?
??? 昨天在數(shù)據(jù)導(dǎo)入的時候遇到了一個問題，在用imp導(dǎo)入的時候，因為數(shù)據(jù)量過大，導(dǎo)致了所有的歸檔日志被寫滿，所有日志均無法歸檔，整個數(shù)據(jù)庫hang住。
?
??? 關(guān)閉數(shù)據(jù)庫之后打開時報錯：
??? SQL> startup mount
??? ORACLE instance started.
??? Total System Global Area? 135338868 bytes
??? Fixed Size?????????????????? 453492 bytes
??? Variable Size???????????? 109051904 bytes
??? Database Buffers?????????? 25165824 bytes
??? Redo Buffers???????????????? 667648 bytes
??? Database mounted.
???
??? SQL> alter database open
??? alter database open
??? *
???
第
1
行出現(xiàn)錯誤
:
??? ORA-16014:
日志
1
的序列號
50
未歸檔
,
沒有可用的目的地
??? ORA-00312:
聯(lián)機日志
1
線程
1: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\dodo\REDO01.LOG'
?
?
??? 查看具體的alert日志可以發(fā)現(xiàn)，是由于歸檔日志的大小超出了限定范圍
??? 具體日志如下(本機的沒拷過來，網(wǎng)上摘一段充個數(shù))：
??? [Copy to clipboard] [ - ]CODE:
??? *** SERVICE NAME:() 2006-03-25 17:30:42.109
??? *** SESSION ID:(157.1) 2006-03-25 17:30:42.109
??? kcrrwkx: work to do 0x1 (start)
??? ORA-19815:
警告
: db_recovery_file_dest_size
字節(jié)
(
共
2147483648
字節(jié)
)
已使用
100.00%,
尚有
0
字節(jié)可用。
??? *** 2006-03-25 17:30:44.843
??? ************************************************************************
??? You have following choices to free up space from flash recovery area:
??? 1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
?????? then consider changing RMAN ARCHIVELOG DELETION POLICY.
??? 2. Back up files to tertiary device such as tape using RMAN
?????? BACKUP RECOVERY AREA command.
??? 3. Add disk space and increase db_recovery_file_dest_size parameter to
?????? reflect the new space.
??? 4. Delete unnecessary files using RMAN DELETE command. If an operating
?????? system command was used to delete files, then use RMAN CROSSCHECK and
?????? DELETE EXPIRED commands.
??? ************************************************************************
??? ORA-19809:
超出了恢復(fù)文件數(shù)的限制
??? ORA-19804:
無法回收
51228672
字節(jié)磁盤空間
(
從
2147483648
限制中
)
??? *** 2006-03-25 17:30:44.984 60680 kcrr.c
??? ARC0: Error 19809 Creating archive log file to 'D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\compiere\ARCHIVELOG\2006_03_25\O1_MF_1_94_%U_.ARC'
??? *** 2006-03-25 17:30:44.984 58942 kcrr.c
??? kcrrfail: dest:10 err:19809 force:0 blast:1
??? *** 2006-03-25 17:30:45.578 20146 kcrr.c
??? ORA-16038:
日志
1
序列號
94
無法歸檔
??? ORA-19809:
超出了恢復(fù)文件數(shù)的限制
??? ORA-00312:
聯(lián)機日志
1
線程
1: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\compiere\REDO01.LOG'
??? *** 2006-03-25 17:31:41.796
??? *** 2006-03-25 17:31:41.796 20146 kcrr.c
??? ORA-16014:
日志
3
的序列號
93
未歸檔
,
沒有可用的目的地
??? ORA-00312:
聯(lián)機日志
3
線程
1: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\compiere\REDO03.LOG'
?
?
??? 查看flash recovery area的使用情況：
??? SQL> select * from v$flash_recovery_area_usage;
???
??? FILE_TYPE??? PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
??? ------------ ------------------ ------------------------- ---------------
??? CONTROLFILE???????????????? .34???????????????????????? 0?????????????? 1
??? ONLINELOG????????????????? 7.32???????????????????????? 0?????????????? 3
??? ARCHIVELOG??????????????? 65.01???????????????????????? 0????????????? 49
??? BACKUPPIECE?????????????????? 0???????????????????????? 0?????????????? 0
??? IMAGECOPY???????????????????? 0???????????????????????? 0?????????????? 0
??? FLASHBACKLOG????????????????? 0???????????????????????? 0?????????????? 0
???
??? 6 rows selected.
?
??? 計算flash recovery area已經(jīng)占用的空間：
??? SQL> select sum(percent_space_used)*3/100 from v$flash_recovery_area_usage;
???
??? SUM(PERCENT_SPACE_USED)*3/100
??? -----------------------------
?????????????????????????? 2.1033
?
??? 查看大小參數(shù)
??? SQL> show parameter db_recovery
???
??? NAME???????????????????????????????? TYPE??????? VALUE
??? -------------------------------- ----------- --------------------------------------------
??? db_recovery_file_dest??????????? string????? D:\oracle\product\10.2.0\flash_recovery_area
??? db_recovery_file_dest_size?????? big integer 2147483648
?
?
??? 可見是因為初始化設(shè)置的db_recovery_file_dest_size=2G不足，導(dǎo)致online redo log無法歸檔，因此可以有三種辦法解決此問題：一是修改初始化參數(shù)db_recovery_file_dest_size，增加至大于歸檔文件總?cè)萘浚欢侵付ㄆ渌臍w檔日志路徑，使歸檔日志保存至別處；三是備份數(shù)據(jù)庫，然后用RMAN刪除歸檔文件(必須使用RMAN，直接手工刪除是沒有用的，具體見：
http://www.eygle.com/archives/2005/03/oracle10gecieif.html
)。
?
??? 注：此問題僅針對10g及以上版本，9i沒有db_recovery_file_dest_size參數(shù)，可以直接指定路徑，也可以直接手動刪除來釋放空間。
?
?