10G ARCHIVELOG寫滿問題
?
??? 昨天在數據導入的時候遇到了一個問題，在用imp導入的時候，因為數據量過大，導致了所有的歸檔日志被寫滿，所有日志均無法歸檔，整個數據庫hang住。
?
??? 關閉數據庫之后打開時報錯：
??? 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
行出現錯誤
:
??? ORA-16014:
日志
1
的序列號
50
未歸檔
,
沒有可用的目的地
??? ORA-00312:
聯機日志
1
線程
1: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\dodo\REDO01.LOG'
?
?
??? 查看具體的alert日志可以發現，是由于歸檔日志的大小超出了限定范圍
??? 具體日志如下(本機的沒拷過來，網上摘一段充個數)：
??? [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
字節
(
共
2147483648
字節
)
已使用
100.00%,
尚有
0
字節可用。
??? *** 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:
超出了恢復文件數的限制
??? ORA-19804:
無法回收
51228672
字節磁盤空間
(
從
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:
超出了恢復文件數的限制
??? ORA-00312:
聯機日志
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:
聯機日志
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已經占用的空間：
??? SQL> select sum(percent_space_used)*3/100 from v$flash_recovery_area_usage;
???
??? SUM(PERCENT_SPACE_USED)*3/100
??? -----------------------------
?????????????????????????? 2.1033
?
??? 查看大小參數
??? 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
?
?
?
??? 注：此問題僅針對10g及以上版本，9i沒有db_recovery_file_dest_size參數，可以直接指定路徑，也可以直接手動刪除來釋放空間。
?
?