log_archive_config配置错误导致的ORA-16053

前言：

log_archive_config 决定remote redo log的传输，正常配置data guard的环境中，参数应为：dg_config=(procdb,procdb_std)     

[ DG_CONFIG=(remote_db_unique_name1 [, ... remote_db_unique_name30) | NODG_CONFIG ]

 

在搭建ADG的时候，如果不小心将unique name写错，日志不会传输到备库，就会报如下错误：

 

 

这是你会发现，defer and enable log_archive_dest_2 是不管用的，杀掉主库的归档进程重启，也是不管用的。日志就是不传。

这是因为主服务器上的ARCx进程永远卡在网络上，或者负责更新应用列的ARCx进程会卡在网络上，无法恢复自己。

执行如下操作：

 

ARCx processes on the primary need to be restarted.
Assuming that log transport from the primary is configured by log_archive_dest_2.

Please perform the following:

1) If the Data Guard Broker is running, disable Data Guard Broker on both primary and standby:

SQL> alter system set dg_broker_start=FALSE;

2) On the Primary Database:

- Set log transport state to DEFER status:
SQL> alter system set log_archive_dest_state_2='defer';
SQL> alter system switch logfile;
- Reset log_archive_dest_2
SQL> show parameter log_archive_dest_2 -------------------> Note this value

NAME TYPE VALUE
------------------------------------ ---------- ------------------------------
log_archive_dest_2 string service="(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 1.1.1.111)(PORT = 1521))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = procdb)(UR=A)))" lgwr async compression=enable valid_for=(online_logfiles,primary_role) db_unique_name=procdb

SQL> alter system set log_archive_dest_2 = '';
- Switch logfiles on the Primary
SQL> alter system switch logfile;

3) On the Standby Database:

- Cancel Managed Recovery
SQL> alter database recover managed standby database cancel;
- Shutdown the Standby Database
SQL> shutdown immediate

4) On the Primary: kill the ARCx Processes and the Database will respawn them automatically immediately without harming it.

ps -ef | grep -i arc
kill -9 <ospid of ARC process> <another ospid of ARC process> ...

5) On standby server, startup Standby Database and resume Managed Recovery

SQL> startup mount;
SQL> alter database recover managed standby database [using current logfile] disconnect;

6) Re-enable Log Transport Services on the Primary:

 

SQL>Alter system set log_archive_dest_2='Service=..............' ; Set log_archive_dest_2 (Which was reset in step 2) to its original value

Alter system set log_archive_dest_2='service="(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 1.1.1.111)(PORT = 1521))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = procdb)(UR=A)))" lgwr async compression=enable valid_for=(online_logfiles,primary_role) db_unique_name=procdb';

SQL> alter system set log_archive_dest_state_2='enable' ;

At this point all the ARCx processes should be up and running on the Primary.

7) Re-enable the Data Guard Broker for both, Primary and Standby if applicable:

SQL> alter system set dg_broker_start=true;

8) Please work with your Network Administrator to make sure the following Firewall Features are disabled.

SQLNet fixup protocol
Deep Packet Inspection (DPI)
SQLNet packet inspection
SQL Fixup
SQL ALG (Juniper firewall)

 

执行后恢复正常

 

参考：Logs are not shipped to the physical standby database (Doc ID 1130523.1)