Defer Log Shipping Oracle Data Guard

Sometimes it becomes necessary to stop the log shipping procedure without doing a shutdown of the standby database or server. For eg: IF there is a network related activity in you organization and there will be a network outage. You can defer the log shipping. Or if you are relocating your standby database server. Please note : Log shipping has to beĀ  enabled explicitly later for the logs to start applying on Standby server again.
On Primary database server
SQL> alter system set log_archive_dest_state_2=DEFER scope=both;
this defers(delays the log shipping)
Now do a switch of logfile to confirm the logfile didnt come on the standby database. Alternately you can check from SQL command using.
On the primary server:
SQL> SELECT max(sequence#) AS “PRIMARY” FROM v$log_history;
On the standby server:
SQL> SELECT max(sequence#) AS “STANDBY”, applied
FROM v$archived_log GROUP BY applied;

Category: Database

Tags:

7 comments

  1. You may also want to set scope parameter to memory or both.

    alter system set log_archive_dest_state_2=defer scope=both;

  2. I have a question if I apply PSU patch on standby first and ran catbundle.sql and then apply PSU patch on primary with out making log_archive_dest_state_2=defer what will happen on standby?

  3. lovely post…can you please correct

    On Primary database server

    SQL> alter system set log_archive_destination_state_2=DEFER ;

    this defers(delays the log shipping)

    the log_archive_destination_state_2 should be log_archive_dest_state_2

    Good work

Leave a Reply

Article by: Shadab Mohammad