ORA-16038: log 3 sequence# 103 cannot be archived

[size=large]今天在自己機器做了個實驗，插入10萬條，由于空間少，重啟數據庫時出現：?

[size=x-large]SQL> startup?
ORACLE instance started.?

Total System Global Area? 188743680 bytes?
Fixed Size????????????????? 1218460 bytes?
Variable Size???????????? 167774308 bytes?
Database Buffers?????????? 16777216 bytes?
Redo Buffers??????????????? 2973696 bytes?
Database mounted.?
ORA-16038: log 3 sequence# 103 cannot be archived?
ORA-19502: write error on file "", blockno? (blocksize=)?
ORA-00312: online log 3 thread 1: '/home/lc_orauser/oradata/niutest/redo03.log'?


后來發現是 閃回區的空間被全部占用?

select group#,sequence#,archived,status from v$log;?

??? GROUP#? SEQUENCE# ARC STATUS?
---------- ---------- --- ----------------?
???????? 1??????? 104 NO? INACTIVE?
???????? 3??????? 103 NO? INACTIVE?
???????? 2??????? 105 NO? CURRENT?


--1、清空閃回區空間，根據查詢視圖v$log可知，當前活動日志為2號日志組，則此時需要清空3號日志組的，?

alter database clear unarchived logfile group 3;?

然后再?

alter database open;?

解決了。?

--2、增大db_recovery_file_dest_size的值?

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 2G?
SQL> alter system set db_recovery_file_dest_size=3G scope=both;?
系統已更改。?
SQL> alter database open;?
數據庫已更改。?

為什么會出現這種情況呢？?

(1).檢查flash recovery area的使用情況：?
SQL> select * from v$flash_recovery_area_usage;?
FILE_TYPE??? PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES?
------------ ------------------ ------------------------- ---------------?
CONTROLFILE?????????????????? 0???????????????????????? 0?????????????? 0?
ONLINELOG???????????????????? 0???????????????????????? 0?????????????? 0?
ARCHIVELOG???????????????? 6.36???????????????????????? 0?????????????? 4?
BACKUPPIECE???????????????? .22???????????????????????? 0?????????????? 1?
IMAGECOPY???????????????? 63.68???????????????????????? 0?????????????? 5?
FLASHBACKLOG??????????????? .51?????????????????????? .25?????????????? 2?
已選擇6行。?
SQL>?
(2).計算flash recovery area已經占用的空間：?
SQL> select sum(percent_space_used)*3/100 from v$flash_recovery_area_usage;?
SUM(PERCENT_SPACE_USED)*3/100?
-----------------------------?
?????????????????????? 2.1231?
可以看到，這里已經有2.1231G使用了，這說明我們剛開始設置的db_recovery_file_dest_size=2G不足，導致online redo log無法歸檔，在這里，我們通過設置db_recovery_file_dest_size參數，增大了flash recovery area來解決這個問題。?
(3).也可以通過刪除flash recovery area中不必要的備份來釋放flash recovery area空間來解決這個問題：?
????? (1). delete obsolete;?
????? (2). crosscheck backupset;?
???????????? delete expired backupset;[/size][/size]