Pages

Tuesday, August 6, 2019

Dataguard start/stop or restart redo apply

Dataguard start/stop or restart redo apply


To start or stop redo apply without dgbroker.
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;

No comments:

Post a Comment