缺少log_archive_config导致归档路径被禁用

10g的DATA GUARD的一个主要特点就是引入了log_archive_config参数，如果缺少这个参数，可能会导致归档路径被禁用。

 

 

看别人建立DATA GUARD时碰到了这个问题，当时觉得比较有意思，于是特意重现一下。

当前是一个已经配置好的DATA GUARD，为了模拟错误，先将这个参数设置为空：

SQL> alter system set log_archive_config = '';

System altered.

SQL> alter system switch logfile;

System altered.

SQL> show parameter archive_dest_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      SERVICE=standby LGWR SYNC VALI
                                                 D_FOR=(ONLINE_LOGFILES,PRIMARY
                                                 _ROLE) DB_UNIQUE_NAME=standby
SQL> alter system set log_archive_dest_2 = 'SERVICE=standby ARCH ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby';

System altered.

SQL> alter system switch logfile;

System altered.

日志切换后，alert文件中并没有任何的错误，包含重新设置log_archive_dest_2参数后再次执行切换日志，alert文件中仍然看不到预期的错误。

导致问题无法重现的原因可能是由于系统中已经设置过log_archive_config参数了，虽然现在置为空，但是这个参数的生效可能会一直保留，于是尝试重启数据库：

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size                  2074112 bytes
Variable Size             486541824 bytes
Database Buffers         1644167168 bytes
Redo Buffers               14700544 bytes
Database mounted.
Database opened.
SQL> alter system switch logfile;

System altered.

SQL> alter system set log_archive_dest_2 = 'SERVICE=standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby';

System altered.

SQL> alter system switch logfile;

System altered.

这是从alert文件中已经可以看到预期的错误了：

Thu Dec 23 21:42:48 2010
Completed: ALTER DATABASE OPEN
Thu Dec 23 21:42:51 2010
Thread 1 advanced to log sequence 11
  Current log# 2 seq# 11 mem# 0: /data/oradata/primary/redo02.log
Thu Dec 23 21:42:51 2010
Errors in file /opt/ora10g/admin/primary/bdump/primary_arc1_18406.trc:
ORA-16057: DGID from server not in Data Guard configuration
Thu Dec 23 21:42:51 2010
FAL[server, ARC1]: Error 16057 creating remote archivelog file 'standby'
FAL[server, ARC1]: FAL archive failed, see trace file.
Thu Dec 23 21:42:51 2010
Errors in file /opt/ora10g/admin/primary/bdump/primary_arc1_18406.trc:
ORA-16055: FAL request rejected
ARCH: FAL archive failed. Archiver continuing
Thu Dec 23 21:42:51 2010
ORACLE Instance primary - Archival Error. Archiver continuing.
Thu Dec 23 21:43:46 2010
Shutting down archive processes
Thu Dec 23 21:43:51 2010
ARCH shutting down
ARC2: Archival stopped
Thu Dec 23 21:45:10 2010
ALTER SYSTEM SET log_archive_dest_2='SERVICE=standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby' SCOPE=BOTH;
LNS1 started with pid=18, OS id=18425
Thu Dec 23 21:45:29 2010
Thread 1 advanced to log sequence 12
  Current log# 3 seq# 12 mem# 0: /data/oradata/primary/redo03.log
Thu Dec 23 21:45:29 2010
Errors in file /opt/ora10g/admin/primary/bdump/primary_arc1_18406.trc:
ORA-16057: DGID from server not in Data Guard configuration
Thu Dec 23 21:45:29 2010
FAL[server, ARC1]: Error 16057 creating remote archivelog file 'standby'
FAL[server, ARC1]: FAL archive failed, see trace file.
Thu Dec 23 21:45:29 2010
Errors in file /opt/ora10g/admin/primary/bdump/primary_arc1_18406.trc:
ORA-16055: FAL request rejected
ARCH: FAL archive failed. Archiver continuing
Thu Dec 23 21:45:29 2010
ORACLE Instance primary - Archival Error. Archiver continuing.
Thu Dec 23 21:50:51 2010
Errors in file /opt/ora10g/admin/primary/bdump/primary_arc1_18406.trc:
ORA-16057: DGID from server not in Data Guard configuration
Thu Dec 23 21:50:51 2010
PING[ARC1]: Heartbeat failed to connect to standby 'standby'. Error is 16057.
Thu Dec 23 21:55:51 2010
Errors in file /opt/ora10g/admin/primary/bdump/primary_arc1_18406.trc:
ORA-16057: DGID from server not in Data Guard configuration
Thu Dec 23 21:55:51 2010
PING[ARC1]: Heartbeat failed to connect to standby 'standby'. Error is 16057.
Thu Dec 23 22:00:51 2010
Errors in file /opt/ora10g/admin/primary/bdump/primary_arc1_18406.trc:
ORA-16057: DGID from server not in Data Guard configuration
Thu Dec 23 22:00:51 2010
PING[ARC1]: Heartbeat failed to connect to standby 'standby'. Error is 16057.

错误信息很明确ORA-16057，说明当前的SERVICE归档设置的主机没有包括在DATA GUARD配置中。

下面将log_archive_config参数添加回来：

SQL> alter system set log_archive_config = 'DG_CONFIG=(primary,standby)';

System altered.

SQL> alter system switch logfile;

System altered.

检查alert文件：

Thu Dec 23 22:05:29 2010
ALTER SYSTEM SET log_archive_config='DG_CONFIG=(primary,standby)' SCOPE=BOTH;
Thu Dec 23 22:05:40 2010
Thread 1 advanced to log sequence 13
  Current log# 1 seq# 13 mem# 0: /data/oradata/primary/redo01.log
Thu Dec 23 22:05:40 2010
ARC0: Archivelog destination LOG_ARCHIVE_DEST_2 disabled: destination Data Guard configuration error

这时，第二个预期的错误也出现了对于LOG_ARCHIVE_DEST_2参数设置的路径被禁止掉。

SQL> show parameter log_archive_dest_%2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      SERVICE=standby LGWR ASYNC VAL
                                                 ID_FOR=(ONLINE_LOGFILES,PRIMAR
                                                 Y_ROLE) DB_UNIQUE_NAME=standby
log_archive_dest_state_2             string      enable

仅从参数上看，log_archive_dest_state_2的值仍然是enable，但是这个归档路径已经不会完成归档操作了：

SQL> select name, sequence#
  2  from v$archived_log
  3  order by 2, 1;

NAME                                                          SEQUENCE#
------------------------------------------------------------ ----------
                                                                      3
/data/oradata/primary/archivelog/1_4_737020478.dbf                    4
standby                                                               4
                                                                      4
/data/oradata/primary/archivelog/1_5_737020478.dbf                    5
standby                                                               5
/data/oradata/primary/archivelog/1_6_737020478.dbf                    6
standby                                                               6
/data/oradata/primary/archivelog/1_7_737020478.dbf                    7
standby                                                               7
/data/oradata/primary/archivelog/1_8_737020478.dbf                    8
standby                                                               8
/data/oradata/primary/archivelog/1_9_737020478.dbf                    9
standby                                                               9
/data/oradata/primary/archivelog/1_10_737020478.dbf                  10
/data/oradata/primary/archivelog/1_11_737020478.dbf                  11
/data/oradata/primary/archivelog/1_12_737020478.dbf                  12
/data/oradata/primary/archivelog/1_13_737020478.dbf                  13

18 rows selected.

最近4个归档都没有想到远端，检查V$ARCHIVE_DEST视图：

SQL> select dest_name, status, error
  2  from v$archive_dest
  3  where dest_id = 2;

DEST_NAME            STATUS    ERROR
-------------------- --------- ------------------------------------------------------------
LOG_ARCHIVE_DEST_2   DISABLED  ORA-16057: DGID from server not in Data Guard configuration

可以看到，路径2对应的状态是DISABLED，通过设置log_archive_dest_state_2为ENABLE，可以解决这个问题：

SQL> alter system set log_archive_dest_state_2 = enable;

System altered.

SQL> alter system switch logfile;

System altered.

检查视图状态：

SQL> select name, sequence#
  2  from v$archived_log
  3  order by 2, 1;

NAME                                                          SEQUENCE#
------------------------------------------------------------ ----------
                                                                      3
/data/oradata/primary/archivelog/1_4_737020478.dbf                    4
standby                                                               4
                                                                      4
/data/oradata/primary/archivelog/1_5_737020478.dbf                    5
standby                                                               5
/data/oradata/primary/archivelog/1_6_737020478.dbf                    6
standby                                                               6
/data/oradata/primary/archivelog/1_7_737020478.dbf                    7
standby                                                               7
/data/oradata/primary/archivelog/1_8_737020478.dbf                    8
standby                                                               8
/data/oradata/primary/archivelog/1_9_737020478.dbf                    9
standby                                                               9
/data/oradata/primary/archivelog/1_10_737020478.dbf                  10
STANDBY                                                              10
/data/oradata/primary/archivelog/1_11_737020478.dbf                  11
STANDBY                                                              11
/data/oradata/primary/archivelog/1_12_737020478.dbf                  12
STANDBY                                                              12
/data/oradata/primary/archivelog/1_13_737020478.dbf                  13
STANDBY                                                              13
/data/oradata/primary/archivelog/1_14_737020478.dbf                  14
standby                                                              14

24 rows selected.

SQL> select dest_name, status, failure_count, error
  2  from v$archive_dest    
  3  where dest_id = 2;

DEST_NAME            STATUS    ERROR
-------------------- --------- ------------------------------------------------------------
LOG_ARCHIVE_DEST_2   VALID

有的时候并不要过分相信Oracle返回的状态，比如这个例子中路径已经被禁止，但是查询参数log_archive_dest_state_2的值却是ENABLE。而解决这个问题的办法，又恰好就是将log_archive_dest_state_2参数的值改为ENABLE。

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/4227/viewspace-683461/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/4227/viewspace-683461/