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/11417069/viewspace-684094/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/11417069/viewspace-684094/