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

[size=large][size=large][/size][size=medium]今天在自己机器做了个实验，插入10万条，由于空间少，重启数据库时出现：

[size=x-large][/size]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]