ORA-16014错误解决办法?
1.问题以及解决过程?
SQL> select status from v$instance;?
STATUS?
------------?
MOUNTED?
SQL> alter database open;?
alter database open?
*?
第 1 行出现错误:?
ORA-16014: 日志 2 的序列号 27 未归档,没有可用的目的地?
ORA-00312: 联机日志 2 线程 1:?
'D:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/REDO02.LOG'?
SQL> show parameter db_recovery_file?
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 archive log current;?
alter system archive log current?
ORA-01109: 数据库未打开?
SQL> alter system switch logfile;?
alter system switch logfile?
SQL> shutdown immediate;?
已经卸载数据库。?
ORACLE 例程已经关闭。?
SQL> startup?
ORACLE 例程已经启动。?
Total System Global Area??201326592 bytes?
Fixed Size??????????????????1248092 bytes?
Variable Size??????????????88081572 bytes?
Database Buffers??????????109051904 bytes?
Redo Buffers????????????????2945024 bytes?
数据库装载完毕。?
ORA-16038: 日志 2 序列号 27 无法归档?
ORA-19809: 超出了恢复文件数的限制?
SQL> show parameter db_recovery?
db_recovery_file_dest_size?????????? big integer 2G?
SQL> alter system set db_recovery_file_dest_size=3G scope=both;?
系统已更改。?
数据库已更改。?
2.反思:?
(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;