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]

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

相关推荐