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;
7 comments
The right command is alter system set log_archive_dest_state_3=defer;
Thanks for the correction. Updated.
You may also want to set scope parameter to memory or both.
alter system set log_archive_dest_state_2=defer scope=both;
Thanks for the feedback
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?
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
Thanks for the correction. Noted and corrected in the post.