A-A+

参数log_archive_format引起的ORA-19563一则

2013年09月11日 Backup&Recovery, TroubleShooting 暂无评论 阅读 1,942 次

该篇描述的是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

给我留言

Copyright © YallonKing 保留所有权利.   Theme  Ality

用户登录

分享到: