Data guard start/stop restart redo apply

Standby stop start apply log

I want to start or stop redo apply without dgbroker. I also want to know other queries to check Dataguard and standby status.
These commands are compatible with Oracle 18c, 12c, 11g, 10g, 9i.

Starting Redo Apply on standby database

The managed recovery can be started as a background or foreground process. FYI :) today, no one uses Dataguard in foreground mode.
The « disconnect from session » option allows the background process to do the managed recovery. It will start the MRP (managed recovery process) on the standby.

To start Redo Apply in the foreground, issue the following SQL statement.

Without Real Time Apply (RTA) on standby database

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

 

With Real Time Apply (RTA)

If you configured your standby redo logs, you can start real-time apply using the following command:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

 

Stopping Redo Apply on standby database

To stop Redo Apply in the foreground, issue the following SQL statement.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

 

Monitoring Redo Apply on Physical Standby Databases

 

Last sequence received and applied

You can use this (important) SQL to check whether your physical standby is in Sync with the Primary:

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#;

 

Standby database process status

select distinct process, status, thread#, sequence#, block#, blocks from v$managed_standby ;

 

If using real time apply

select TYPE, ITEM, to_char(TIMESTAMP, 'DD-MON-YYYY HH24:MI:SS') from v$recovery_progress where ITEM='Last Applied Redo';

or

select recovery_mode from v$archive_dest_status where dest_id=1;

 

Author: Vincent Fenoll (Oracle DBA in Montreal)

Summary
Article Name
Data guard start/stop restart redo apply - Oracle scripts
Description
I want to start or stop redo apply without dgbroker. I also want to know other queries to check Dataguard and standby status.
Author
Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.