A-A+
参数log_archive_format引起的ORA-19563一则
该篇描述的是9i的库由于参数log_archive_format设置不正确导致备份归档日志时报错ORA-19563，解决倒是很容易，但是问题是：在正常业务期间，数据库发生检查点，切换日志，产生归档的日志只是对同一个归档日志的覆盖叠加的写，而且在alert中没有任何相关的报错。之前记得有一次对该参数进行修改重启数据库的时候，会报相关的错误，但是这次并没有相关错误抛出，只是对同一个归档日志的叠加写，然后是覆盖。
下边便是该问题的一个处理过程：
操作系统信息：
bash-2.03$ cat /etc/release
Solaris 8 2/04 s28s_hw4wos_05a SPARC
Copyright 2004 Sun Microsystems, Inc. All Rights Reserved.
Assembled 08 January 2004
数据库版本：
SQL> select * from gv$version;
INST_ID BANNER
---------- ----------------------------------------------------------------
1 Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
1 PL/SQL Release 9.2.0.4.0 - Production
1 CORE 9.2.0.3.0 Production
1 TNS for Solaris: Version 9.2.0.4.0 - Production
1 NLSRTL Version 9.2.0.4.0 - Production
数据库当前归档信息：
SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /opt/oracle/oradata/arch Oldest online log sequence 26 Next log sequence to archive 28 Current log sequence 28 SQL> !ls -latr /opt/oracle/oradata/arch total 6 drwxrwxr-x 5 oracle dba 512 May 20 22:23 .. -rw-r----- 1 oracle dba 1024 Sep 9 21:55 1_0.dbf drwxr-xr-x 2 oracle dba 512 Sep 9 21:55 .
人为产生检查点、切换日志、归档日志：
SQL> alter system checkpoint; System altered. SQL> alter system archive log current; System altered. SQL> alter system switch logfile; System altered. SQL> / System altered. SQL> / System altered. SQL> alter system switch logfile; System altered. SQL> / System altered. SQL> / System altered. SQL> !ls -latr /opt/oracle/oradata/arch total 8 drwxrwxr-x 5 oracle dba 512 May 20 22:23 .. -rw-r----- 1 oracle dba 1536 Sep 9 22:01 1_0.dbf drwxr-xr-x 2 oracle dba 512 Sep 9 22:01 .
此处发现，该归档日志1_0.dbf一致在增长，并没有在该目录下产生新的归档日志，而且在观察中还会发现，有覆盖写的情况出现（该处没有重现该现象）。
下边查看问题参数：
SQL> show parameter log
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_online_log_dest_1 string
db_create_online_log_dest_2 string
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
dblink_encrypt_login boolean FALSE
log_archive_dest string
log_archive_dest_1 string LOCATION=/opt/oracle/oradata/a
rch
log_archive_dest_10 string
log_archive_dest_2 string
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_3 string
log_archive_dest_4 string
log_archive_dest_5 string
log_archive_dest_6 string
log_archive_dest_7 string
log_archive_dest_8 string
log_archive_dest_9 string
log_archive_dest_state_1 string enable
log_archive_dest_state_10 string enable
log_archive_dest_state_2 string enable
log_archive_dest_state_3 string enable
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_4 string enable
log_archive_dest_state_5 string enable
log_archive_dest_state_6 string enable
log_archive_dest_state_7 string enable
log_archive_dest_state_8 string enable
log_archive_dest_state_9 string enable
log_archive_duplex_dest string
log_archive_format string %t_%U.dbf
log_archive_max_processes integer 2
log_archive_min_succeed_dest integer 1
log_archive_start boolean TRUE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_trace integer 0
log_buffer integer 524288
log_checkpoint_interval integer 0
log_checkpoint_timeout integer 1800
log_checkpoints_to_alert boolean FALSE
log_file_name_convert string
log_parallelism integer 1
logmnr_max_persistent_sessions integer 1
remote_login_passwordfile string EXCLUSIVE
此处，明显可以看到参数log_archive_format的值中的 %U 明显错误，该参数应该是rman中format的参数，估计是某位同事把二者混淆了。
下边是备份报的错误：
Starting backup at 09-SEP-13 current log archived released channel: c1 released channel: c2 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of backup command at 09/09/2013 21:39:10 ORA-19563: archived log header validation failed for file /opt/oracle/oradata/arch/1_0.dbf RMAN> Recovery Manager complete.
查看alert日志文件：
Mon Sep 9 21:50:01 2013
ARCH: Evaluating archive log 3 thread 1 sequence 21
ARCH: Beginning to archive log 3 thread 1 sequence 21
Creating archive destination LOG_ARCHIVE_DEST_1: '/opt/oracle/oradata/arch/1_0.dbf'
Mon Sep 9 21:50:01 2013
ARC1: Evaluating archive log 3 thread 1 sequence 21
ARC1: Unable to archive log 3 thread 1 sequence 21
Log actively being archived by another process
Mon Sep 9 21:50:01 2013
ARCH: Completed archiving log 3 thread 1 sequence 21
Mon Sep 9 21:50:16 2013
Thread 1 advanced to log sequence 23
Current log# 2 seq# 23 mem# 0: /opt/oracle/oradata/cakmcdb/redo02.log
Mon Sep 9 21:50:16 2013
ARC1: Evaluating archive log 1 thread 1 sequence 22
ARC1: Beginning to archive log 1 thread 1 sequence 22
Creating archive destination LOG_ARCHIVE_DEST_1: '/opt/oracle/oradata/arch/1_0.dbf'
ARC1: Completed archiving log 1 thread 1 sequence 22
Mon Sep 9 21:55:36 2013
Thread 1 advanced to log sequence 24
Current log# 3 seq# 24 mem# 0: /opt/oracle/oradata/cakmcdb/redo03.log
Mon Sep 9 21:55:36 2013
ARC0: Evaluating archive log 2 thread 1 sequence 23
ARC0: Beginning to archive log 2 thread 1 sequence 23
Creating archive destination LOG_ARCHIVE_DEST_1: '/opt/oracle/oradata/arch/1_0.dbf'
ARC0: Completed archiving log 2 thread 1 sequence 23
Mon Sep 9 21:55:37 2013
Thread 1 advanced to log sequence 25
Current log# 1 seq# 25 mem# 0: /opt/oracle/oradata/cakmcdb/redo01.log
Mon Sep 9 21:55:37 2013
ARC1: Evaluating archive log 3 thread 1 sequence 24
ARC1: Beginning to archive log 3 thread 1 sequence 24
Creating archive destination LOG_ARCHIVE_DEST_1: '/opt/oracle/oradata/arch/1_0.dbf'
ARC1: Completed archiving log 3 thread 1 sequence 24
Mon Sep 9 21:55:38 2013
Thread 1 advanced to log sequence 26
Current log# 2 seq# 26 mem# 0: /opt/oracle/oradata/cakmcdb/redo02.log
Mon Sep 9 21:55:38 2013
ARC0: Evaluating archive log 1 thread 1 sequence 25
ARC0: Beginning to archive log 1 thread 1 sequence 25
Creating archive destination LOG_ARCHIVE_DEST_1: '/opt/oracle/oradata/arch/1_0.dbf'
ARC0: Completed archiving log 1 thread 1 sequence 25
Mon Sep 9 21:55:39 2013
Thread 1 advanced to log sequence 27
Current log# 3 seq# 27 mem# 0: /opt/oracle/oradata/cakmcdb/redo03.log
Mon Sep 9 21:55:39 2013
ARC1: Evaluating archive log 2 thread 1 sequence 26
ARC1: Beginning to archive log 2 thread 1 sequence 26
Creating archive destination LOG_ARCHIVE_DEST_1: '/opt/oracle/oradata/arch/1_0.dbf'
ARC1: Completed archiving log 2 thread 1 sequence 26
Mon Sep 9 21:55:39 2013
Thread 1 advanced to log sequence 28
Current log# 1 seq# 28 mem# 0: /opt/oracle/oradata/cakmcdb/redo01.log
Mon Sep 9 21:55:39 2013
ARC0: Evaluating archive log 3 thread 1 sequence 27
ARC0: Beginning to archive log 3 thread 1 sequence 27
Creating archive destination LOG_ARCHIVE_DEST_1: '/opt/oracle/oradata/arch/1_0.dbf'
ARC0: Completed archiving log 3 thread 1 sequence 27
Mon Sep 9 22:00:59 2013
Thread 1 advanced to log sequence 29
Current log# 2 seq# 29 mem# 0: /opt/oracle/oradata/cakmcdb/redo02.log
此处，发现没有相关错误，只有之前做的切换日志等操作的记录，并且还可以看到，切换后的日志一直在给同一个归档文件/opt/oracle/oradata/arch/1_0.dbf写，这里应该抛出相关错误，估计和oracle的bug相关吧。
参数修改后，备份归档便恢复正常，如下：
SQL> show parameter log
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_online_log_dest_1 string
db_create_online_log_dest_2 string
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
dblink_encrypt_login boolean FALSE
log_archive_dest string
log_archive_dest_1 string LOCATION=/opt/oracle/oradata/a
rch
log_archive_dest_10 string
log_archive_dest_2 string
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_3 string
log_archive_dest_4 string
log_archive_dest_5 string
log_archive_dest_6 string
log_archive_dest_7 string
log_archive_dest_8 string
log_archive_dest_9 string
log_archive_dest_state_1 string enable
log_archive_dest_state_10 string enable
log_archive_dest_state_2 string enable
log_archive_dest_state_3 string enable
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_4 string enable
log_archive_dest_state_5 string enable
log_archive_dest_state_6 string enable
log_archive_dest_state_7 string enable
log_archive_dest_state_8 string enable
log_archive_dest_state_9 string enable
log_archive_duplex_dest string
log_archive_format string %t_%s.arc
log_archive_max_processes integer 2
log_archive_min_succeed_dest integer 1
log_archive_start boolean TRUE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_trace integer 0
log_buffer integer 524288
log_checkpoint_interval integer 0
log_checkpoint_timeout integer 1800
log_checkpoints_to_alert boolean FALSE
log_file_name_convert string
log_parallelism integer 1
logmnr_max_persistent_sessions integer 1
remote_login_passwordfile string EXCLUSIVE
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /opt/oracle/oradata/arch
Oldest online log sequence 34
Next log sequence to archive 36
Current log sequence 36
SQL> !
bash-2.03$ cd /opt/oracle/oradata/arch
bash-2.03$ ls -ltr
total 204928
-rw-r----- 1 oracle dba 104857088 Sep 10 22:19 1_0.dbf
bash-2.03$ exit
exit
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> !
bash-2.03$ pwd
/opt/oracle
bash-2.03$ cd /opt/oracle/oradata/arch
bash-2.03$ ls -ltr
total 293810
-rw-r----- 1 oracle dba 104857088 Sep 10 22:19 1_0.dbf
-rw-r----- 1 oracle dba 1024 Sep 11 09:01 1_37.arc
-rw-r----- 1 oracle dba 45468160 Sep 11 09:01 1_36.arc
