logs are not shipped to the physical standby database in Oracle Data Guard

 

If logs are not shipped to the physical standby database in Oracle Data Guard, the following steps can help resolve the issue based on the root cause. 

Below is a structured guide to address the problem.

---

Common Symptoms

• Logs are not shipped after a log switch.
• FAL (Fetch Archive Log) fails.
• Standby database shows a mismatch in the applied column.
• RMAN reports errors (e.g., RMAN-08137).
• Errors in alert logs (e.g., ORA-12154, ORA-3113, ORA-3135).
• ARCH process warnings (e.g., WARN: ARCH: Terminating pid <PID>).

Causes

• OS or network errors causing ARCH processes to hang.
• Reaching the maximum number of ARCx processes (log_archive_max_processes).
• Firewall-related issues (SQL*Net packet inspection or similar).
• Abrupt outages or crashes on the primary/standby server.

---

Step-by-Step Solution

Step 1: Disable Data Guard Broker (if enabled)

Disabling the broker avoids conflicts during manual recovery.

ALTER SYSTEM SET dg_broker_start=FALSE;

---

Step 2: Configure Primary Database

1. Defer Log Transport State Temporarily stop log shipping to standby.

   ALTER SYSTEM SET log_archive_dest_state_2='defer';
   ALTER SYSTEM SWITCH LOGFILE;
   

2. Reset log_archive_dest_2 Reset the configuration of log_archive_dest_2 to clear any stuck processes.

   SHOW PARAMETER log_archive_dest_2; -- Note the current value
   ALTER SYSTEM SET log_archive_dest_2 = '';
   ALTER SYSTEM SWITCH LOGFILE;
   

---

Step 3: Prepare the Standby Database

1. Cancel Managed Recovery Stop managed recovery on the standby.

   ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

2. Shutdown the Standby Database Shut down the standby instance to reset its processes.

   SHUTDOWN IMMEDIATE;

---

Step 4: Restart ARC Processes on Primary

1. Identify and Kill Stuck ARC Processes On the primary server, find and kill the stuck ARCx processes.

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

2. Verify Restart Oracle will automatically respawn the ARCx processes after killing them.

---

Step 5: Restart the Standby Database

1. Start Standby in Mount Mode Bring the standby database back online in mount mode.

   
   STARTUP MOUNT;
   

2. Resume Managed Recovery Resume the recovery process on the standby database.

   
   ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
   

---

Step 6: Reconfigure Log Transport on Primary

1. Restore log_archive_dest_2 Set log_archive_dest_2 to its original value.

   ALTER SYSTEM SET log_archive_dest_2='service=....'; -- Use the noted value from Step 2
   ALTER SYSTEM SET log_archive_dest_state_2='enable';
   

---

Step 7: Re-enable Data Guard Broker

If the Data Guard Broker was previously enabled, reactivate it on both primary and standby.

ALTER SYSTEM SET dg_broker_start=TRUE;

---

Step 8: Network and Firewall Check

Work with the network administrator to ensure:

• SQL*Net Fixup Protocol is disabled.
• Deep Packet Inspection (DPI) is turned off.
• SQL packets are not modified by firewalls.

---

Additional Notes

• If these steps do not resolve the issue, a restart of the primary database may be required as a last resort.
• To diagnose deeper issues, enable SQL*Net tracing as described in Doc ID 2010476.1 or consult Oracle Support.

---

Preventive Measures

1. Monitor Processes: Regularly check the status of ARCx processes.

   ps -ef | grep -i arc

2. Firewall Rules: Ensure firewalls are configured to allow uninterrupted communication between primary and standby databases.

3. Set Adequate Log Archive Processes: Increase log_archive_max_processes if necessary (default is 

   ALTER SYSTEM SET log_archive_max_processes=<value>;

By following these steps, you can resolve and prevent log shipping issues in Oracle Data Guard environments.