Archive log & ORA-16014
今天在导数据的时候，发现session突然hang住了，一直没反应。从hang住的地方的日志看了半天，也没发现可疑问题。因为按道理来说，导这点数据不至于这么慢啊，以前又不是没导过。
突然想到是不是flash recovery area爆满了，查了下alert文件，果不其然，发现了如下信息...
================================================================
Thu Mar 10 15:03:42 2011
Errors in file c:\oracle\product\10.2.0\admin\lo\bdump\lo_arc1_3560.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 4294967296 bytes is 100.00% used, and has 0 remaining bytes available.
....
ARCH: Archival stopped, error occurred. Will continue retrying
Thu Mar 10 14:58:42 2011
Errors in file c:\oracle\product\10.2.0\admin\lo\bdump\lo_arc0_3548.trc:
ORA-16014: log 2 sequence# 1893 not archived, no available destinations
ORA-00312: online log 2 thread 1: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\LO\REDO02.LOG'
==================================================================
我晕....我以为这个测试数据库不是归档模式的呢，居然是归档的。现在因为flash recovery area的空间爆满，导致当前online日志没有办法归档了，所以整个系统hang住了。
我想既然归档日志都没啥用，就干脆全删除得了，也省得再增加flash reacovery area的大小了。于是一步做，二不休就把归档日志全删除了。满以为删了之后，刚才的那个session会继续执行下去。但是等了好一会，也没见什么反应！
通过sys用户登录，手动归档下当前日志文件...
====================================================
SQL> alter system archive log current;
alter system archive log current
*
ERROR at line 1:
ORA-16014: log 2 sequence# 1893 not archived, no available destinations
ORA-00312: online log 2 thread 1:
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\LO\REDO02.LOG'
====================================================
居然还是报16014的错！看来oracle还没有“意识”到flash recovery area已经被清空了。怎么办呢？我不想重启数据库，因为我可不想再浪费时间重新导数据。
想到可以重新设置下归档路径参数，再试试...
============================================================
SQL> alter system set log_archive_dest_1='location=E:\oracle\flash_recovery_area\LO';
System altered.
SQL> alter system archive log current;
System altered.
=============================================================
将归档路径仍然指向flash recovery area, 再重新归档下当前online 日志，发现现在可以work了。而那个导数据的session也恢复正常了！等导完数据之后，再把数据库改为非归档模式（反正是测试用数据库），免得再出这个问题。 （貌似如果一开始就增加下db_recovery_file_dest_size的大小，也是可以的）
==============！！！！==============
仔细想了想，刚才的做法有点回避问题的嫌疑，为啥oracle没有“意识”到flash recovery area已经被清空了呢？ 因为我是在OS层面直接手动删除的，oracle是不知晓的，这种操作也是不妥的。想到RMAN的crosscheck命令来更新oracle的"catalog"的信息，应该在手动删除之后，用corsscheck来“通知”oracle我已经做的改动才对。
现在通过查询V$FLASH_RECOVERY_AREA_USAGE来看一下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 98.97 0 87
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG 0 0 0
6 rows selected.
从查询结果来看，空间几乎被archive log占满了，而且一共有87个归档日志文件。这个显然是“错误”的信息，是我手动删除归档日志前的情况，可以通过RMAN (list archivelog all)来证实下...
==============================================================================================
RMAN> list archivelog all;
List of Archived Log Copies
Key Thrd Seq S Low Time Name
------- ---- ------- - --------- ----
1 1 1806 X 01-MAR-11 E:\ORACLE\FLASH_RECOVERY_AREA\LO\ARCHIVELOG\2011_03_01\O1_MF_1_1806_6PSQ7X6N_.ARC
2 1 1807 X 01-MAR-11 E:\ORACLE\FLASH_RECOVERY_AREA\LO\ARCHIVELOG\2011_03_01\O1_MF_1_1807_6PSQ7YD0_.ARC
3 1 1808 X 01-MAR-11 E:\ORACLE\FLASH_RECOVERY_AREA\LO\ARCHIVELOG\2011_03_01\O1_MF_1_1808_6PSQ8KV8_.ARC
4 1 1809 X 01-MAR-11 E:\ORACLE\FLASH_RECOVERY_AREA\LO\ARCHIVELOG\2011_03_01\O1_MF_1_1809_6PSQW8J7_.ARC
5 1 1810 X 01-MAR-11 E:\ORACLE\FLASH_RECOVERY_AREA\LO\ARCHIVELOG\2011_03_01\O1_MF_1_1810_6PSQWNNT_.ARC
6 1 1811 X 01-MAR-11 E:\ORACLE\FLASH_RECOVERY_AREA\LO\ARCHIVELOG\2011_03_01\O1_MF_1_1811_6PSQWZQ6_.
....<omit some records>...
82 1 1887 X 10-MAR-11 E:\ORACLE\FLASH_RECOVERY_AREA\LO\ARCHIVELOG\2011_03_10\O1_MF_1_1887_6QJVYDP7_.ARC
83 1 1888 X 10-MAR-11 E:\ORACLE\FLASH_RECOVERY_AREA\LO\ARCHIVELOG\2011_03_10\O1_MF_1_1888_6QJVZF8C_.ARC
84 1 1889 X 10-MAR-11 E:\ORACLE\FLASH_RECOVERY_AREA\LO\ARCHIVELOG\2011_03_10\O1_MF_1_1889_6QJW1LL1_.ARC
85 1 1890 X 10-MAR-11 E:\ORACLE\FLASH_RECOVERY_AREA\LO\ARCHIVELOG\2011_03_10\O1_MF_1_1890_6QJW4L1D_.ARC
86 1 1891 X 10-MAR-11 E:\ORACLE\FLASH_RECOVERY_AREA\LO\ARCHIVELOG\2011_03_10\O1_MF_1_1891_6QJW6JKB_.ARC
87 1 1892 X 10-MAR-11 E:\ORACLE\FLASH_RECOVERY_AREA\LO\ARCHIVELOG\2011_03_10\O1_MF_1_1892_6QJWL06O_.ARC
88 1 1893 A 10-MAR-11 E:\ORACLE\FLASH_RECOVERY_AREA\LO\ARC01893_0718887929.001
89 1 1894 A 10-MAR-11 E:\ORACLE\FLASH_RECOVERY_AREA\LO\ARC01894_0718887929.001
90 1 1895 A 10-MAR-11 E:\ORACLE\FLASH_RECOVERY_AREA\LO\ARC01895_0718887929.001
91 1 1896 A 10-MAR-11 E:\ORACLE\FLASH_RECOVERY_AREA\LO\ARC01896_0718887929.001
92 1 1897 A 10-MAR-11 E:\ORACLE\FLASH_RECOVERY_AREA\LO\ARC01897_0718887929.001
93 1 1898 A 10-MAR-11 E:\ORACLE\FLASH_RECOVERY_AREA\LO\ARC01898_0718887929.001
94 1 1899 A 10-MAR-11 E:\ORACLE\FLASH_RECOVERY_AREA\LO\ARC01899_0718887929.001
95 1 1900 A 10-MAR-11 E:\ORACLE\FLASH_RECOVERY_AREA\LO\ARC01900_0718887929.001
96 1 1901 A 10-MAR-11 E:\ORACLE\FLASH_RECOVERY_AREA\LO\ARC01901_0718887929.001
97 1 1902 A 10-MAR-11 E:\ORACLE\FLASH_RECOVERY_AREA\LO\ARC01902_0718887929.001
98 1 1903 A 10-MAR-11 E:\ORACLE\FLASH_RECOVERY_AREA\LO\ARC01903_0718887929.001
RMAN>
==============================================================================================
可以看到前87条记录正式之前删除的归档日志。后面的几条是我更改了归档日志路径之后的归档日志。现在通过crosscheck, 然后删除expired的文件...
==============================================================================================
RMAN> crosscheck archivelog all;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=106 devtype=DISK
validation failed for archived log
archive log filename=E:\ORACLE\FLASH_RECOVERY_AREA\LO\ARCHIVELOG\2011_03_01\O1_MF_1_1806_6PSQ7X6N_.ARC recid=1 stamp=744666493
validation failed for archived log
.......<omit some records>...
archive log filename=E:\ORACLE\FLASH_RECOVERY_AREA\LO\ARCHIVELOG\2011_03_10\O1_MF_1_1890_6QJW4L1D_.ARC recid=85 stamp=745425174
validation failed for archived log
archive log filename=E:\ORACLE\FLASH_RECOVERY_AREA\LO\ARCHIVELOG\2011_03_10\O1_MF_1_1891_6QJW6JKB_.ARC recid=86 stamp=745425235
validation failed for archived log
archive log filename=E:\ORACLE\FLASH_RECOVERY_AREA\LO\ARCHIVELOG\2011_03_10\O1_MF_1_1892_6QJWL06O_.ARC recid=87 stamp=745425602
validation succeeded for archived log
archive log filename=E:\ORACLE\FLASH_RECOVERY_AREA\LO\ARC01893_0718887929.001 recid=88 stamp=745427539
validation succeeded for archived log
archive log filename=E:\ORACLE\FLASH_RECOVERY_AREA\LO\ARC01894_0718887929.001 recid=89 stamp=745427547
validation succeeded for archived log
archive log filename=E:\ORACLE\FLASH_RECOVERY_AREA\LO\ARC01895_0718887929.001 recid=90 stamp=745427548
validation succeeded for archived log
archive log filename=E:\ORACLE\FLASH_RECOVERY_AREA\LO\ARC01896_0718887929.001 recid=91 stamp=745427565
validation succeeded for archived log
archive log filename=E:\ORACLE\FLASH_RECOVERY_AREA\LO\ARC01897_0718887929.001 recid=92 stamp=745427585
validation succeeded for archived log
archive log filename=E:\ORACLE\FLASH_RECOVERY_AREA\LO\ARC01898_0718887929.001 recid=93 stamp=745427604
validation succeeded for archived log
archive log filename=E:\ORACLE\FLASH_RECOVERY_AREA\LO\ARC01899_0718887929.001 recid=94 stamp=745427985
validation succeeded for archived log
archive log filename=E:\ORACLE\FLASH_RECOVERY_AREA\LO\ARC01900_0718887929.001 recid=95 stamp=745428316
validation succeeded for archived log
archive log filename=E:\ORACLE\FLASH_RECOVERY_AREA\LO\ARC01901_0718887929.001 recid=96 stamp=745428413
validation succeeded for archived log
archive log filename=E:\ORACLE\FLASH_RECOVERY_AREA\LO\ARC01902_0718887929.001 recid=97 stamp=745428546
validation succeeded for archived log
archive log filename=E:\ORACLE\FLASH_RECOVERY_AREA\LO\ARC01903_0718887929.001 recid=98 stamp=745428773
Crosschecked 98 objects
RMAN> delete expired archivelog all;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=106 devtype=DISK
List of Archived Log Copies
Key Thrd Seq S Low Time Name
------- ---- ------- - --------- ----
1 1 1806 X 01-MAR-11 E:\ORACLE\FLASH_RECOVERY_AREA\LO\ARCHIVELOG\2011_03_01\O1_MF_1_1806_6PSQ7X6N_.ARC
2 1 1807 X 01-MAR-11 E:\ORACLE\FLASH_RECOVERY_AREA\LO\ARCHIVELOG\2011_03_01\O1_MF_1_1807_6PSQ7YD0_.ARC
3 1 1808 X 01-MAR-11 E:\ORACLE\FLASH_RECOVERY_AREA\LO\ARCHIVELOG\2011_03_01\O1_MF_1_1808_6PSQ8KV8_.ARC
4 1 1809 X 01-MAR-11 E:\ORACLE\FLASH_RECOVERY_AREA\LO\ARCHIVELOG\2011_03_01\O1_MF_1_1809_6PSQW8J7_.ARC
5 1 1810 X 01-MAR-11 E:\ORACLE\FLASH_RECOVERY_AREA\LO\ARCHIVELOG\2011_03_01\O1_MF_1_1810_6PSQWNNT_.ARC
6 1 1811 X 01-MAR-11 E:\ORACLE\FLASH_RECOVERY_AREA\LO\ARCHIVELOG\2011_03_01\O1_MF_1_1811_6PSQWZQ6_.ARC
7 1 1812 X 01-MAR-11 E:\ORACLE\FLASH_RECOVERY_AREA\LO\ARCHIVELOG\2011_03_01\O1_MF_1_1812_6PSQXJW1_.ARC
8 1 1813 X 01-MAR-11 E:\ORACLE\FLASH_RECOVERY_AREA\LO\ARCHIVELOG\2011_03_01\O1_MF_1_1813_6PSQXTNO_.ARC
9 1 1814 X 01-MAR-11 E:\ORACLE\FLASH_RECOVERY_AREA\LO\ARCHIVELOG\2011_03_01\O1_MF_1_1814_6PSZ7BQM_.ARC
....<omit some records>...
==========================================================================================
现在在查看下归档日志...
==========================================================================================
RMAN> list archivelog all;
List of Archived Log Copies
Key Thrd Seq S Low Time Name
------- ---- ------- - --------- ----
88 1 1893 A 10-MAR-11 E:\ORACLE\FLASH_RECOVERY_AREA\LO\ARC01893_0718887929.001
89 1 1894 A 10-MAR-11 E:\ORACLE\FLASH_RECOVERY_AREA\LO\ARC01894_0718887929.001
90 1 1895 A 10-MAR-11 E:\ORACLE\FLASH_RECOVERY_AREA\LO\ARC01895_0718887929.001
91 1 1896 A 10-MAR-11 E:\ORACLE\FLASH_RECOVERY_AREA\LO\ARC01896_0718887929.001
92 1 1897 A 10-MAR-11 E:\ORACLE\FLASH_RECOVERY_AREA\LO\ARC01897_0718887929.001
93 1 1898 A 10-MAR-11 E:\ORACLE\FLASH_RECOVERY_AREA\LO\ARC01898_0718887929.001
94 1 1899 A 10-MAR-11 E:\ORACLE\FLASH_RECOVERY_AREA\LO\ARC01899_0718887929.001
95 1 1900 A 10-MAR-11 E:\ORACLE\FLASH_RECOVERY_AREA\LO\ARC01900_0718887929.001
96 1 1901 A 10-MAR-11 E:\ORACLE\FLASH_RECOVERY_AREA\LO\ARC01901_0718887929.001
97 1 1902 A 10-MAR-11 E:\ORACLE\FLASH_RECOVERY_AREA\LO\ARC01902_0718887929.001
98 1 1903 A 10-MAR-11 E:\ORACLE\FLASH_RECOVERY_AREA\LO\ARC01903_0718887929.001
RMAN>
==========================================================================================
可以看到只剩下有效的归档日志文件了。那么V$FLASH_RECOVERY_AREA_USAGE有变化吗？
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 0 0 0
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG 0 0 0
6 rows selected.
SQL>
可以看到这个视图的信息被更新了。所以以后要记得手动删除归档日志文件后，用RMAN 的crosscheck来更新下catalog (control file)的信息，让Oracle知道做的更改操作。
================
P.S. 看到网上有人讲了怎么解决这个问题，很全面-- http://www.beyondoracle.com/2008/10/11/archivelog-ora-16014-log-sequence-not-archived/
--------------------------------------
Regards,
FangwenYu
