Dataguard commands and SQL scripts

Dataguard commands and SQL scripts

PHYSICAL STANDBY COMMANDS:


To start redo apply in foreground:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;

To stop redo apply process on the Standby database (to stop MRP):

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

To start real-time redo apply:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

To start redo apply in background:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
or
SQL> RECOVER MANAGED STANDBY DATABASE DISCONNECT;

To check redo apply and Media recovery service status:

SQL> SELECT PROCESS,STATUS, THREAD#,SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY ;

If managed standby recovery is not running or not started with real-time apply, restart managed recovery with real-time apply enabled:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

To gather Data Guard configuration information(standby)

SQL> SELECT DATABASE_ROLE,OPEN_MODE, PROTECTION_MODE FROM V$DATABASE;

DATABASE_ROLE OPEN_MODE PROTECTION_MODE
---------------- -------------------- --------------------
PHYSICAL STANDBY MOUNTED MAXIMUM PERFORMANCE

SQL> SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS WHERE RECOVERY_MODE!='IDLE';

RECOVERY_MODE
-----------------------
MANAGED REAL TIME APPLY

To calculate the Redo bytes per second

SQL> SELECT SUM (BLOCKS * BLOCK_SIZE) / 1024 / 1024 / 60 / 60 / 30 REDO_MB_PER_SEC
 FROM GV$ARCHIVED_LOG
 WHERE FIRST_TIME BETWEEN TO_DATE ('01.05.2016', 'DD.MM.YYYY')
 AND TO_DATE ('01.06.2016', 'DD.MM.YYYY');

To check status of Data Guard synchronization(standby):

SQL> SELECT NAME, VALUE FROM V$DATAGUARD_STATS;

NAME VALUE
--------------------- -------------------------------
transport lag          +00 00:00:00
apply lag              +00 00:00:00
apply finish time      +00 00:00:00.000
estimated startup time 32

To verify there is no log file gap between the primary and the standby database:

SQL> SELECT STATUS, GAP_STATUS FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID = 3;

STATUS GAP_STATUS
--------- ------------------------
VALID NO GAP

To verify that the primary database can be switched to the standby role:

A value of TO STANDBY or SESSIONS ACTIVE indicates that the primary database can be switched to the standby role. If neither of these values is returned, a switchover is not possible because redo transport is either misconfigured or is not functioning properly.

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
--------------------
TO STANDBY

You can use verify command to verfy switchover

This comman will generate warnings in alert log file and you can check it before switchover

SQL> ALTER DATABASE SWITCHOVER to STBY_DB_SID VERIFY;

To convert the primary database into a physical standby :

Before switchover the current control file is backed up to the current SQL session trace file and it possible to reconstruct a current control file, if necessary.

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;

To verify Managed Recovery is running on the standby :

SQL> SELECT PROCESS FROM V$MANAGED_STANDBY WHERE PROCESS LIKE 'MRP%';

PROCESS
---------
MRP0

To show information about the protection mode, the protection level, the role of the database, and switchover status:

SQL> SELECT DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS FROM V$DATABASE;

DATABASE_ROLE     INSTANCE    OPEN_MODE    PROTECTION_MODE     PROTECTION_LEVEL     SWITCHOVER_STATUS
---------------- ---------- ------------ -------------------- -------------------- -------------------- --------------------
PRIMARY           TESTCDB    READ WRITE    MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE   TO STANDBY

On the standby database, query the V$ARCHIVED_LOG view to identify existing files in the archived redo log.

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
Or
SQL> SELECT THREAD#, MAX(SEQUENCE#) AS "LAST_APPLIED_LOG" FROM V$LOG_HISTORY GROUP BY THREAD#;

On the standby database, query the V$ARCHIVED_LOG view to verify the archived redo log files were applied.

SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

To determine which log files were not received by the standby site.

SQL> SELECT LOCAL.THREAD#, LOCAL.SEQUENCE#
FROM (SELECT THREAD#, SEQUENCE#
FROM V$ARCHIVED_LOG
WHERE DEST_ID = 1) LOCAL
WHERE LOCAL.SEQUENCE# NOT IN (SELECT SEQUENCE#
FROM V$ARCHIVED_LOG
WHERE DEST_ID = 2 AND THREAD# = LOCAL.THREAD#);


Archivelog difference: Run this on the primary database. (not for real-time apply):

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

SELECT A.THREAD#,
 B.LAST_SEQ,
 A.APPLIED_SEQ,
 A.LAST_APP_TIMESTAMP,
 B.LAST_SEQ - A.APPLIED_SEQ ARC_DIFF
 FROM ( SELECT THREAD#,
 MAX (SEQUENCE#) APPLIED_SEQ,
 MAX (NEXT_TIME) LAST_APP_TIMESTAMP
 FROM GV$ARCHIVED_LOG
 WHERE APPLIED = 'YES'
 GROUP BY THREAD#) A,
 ( SELECT THREAD#, MAX (SEQUENCE#) LAST_SEQ
 FROM GV$ARCHIVED_LOG
 GROUP BY THREAD#) B
 WHERE A.THREAD# = B.THREAD#;

 THREAD#   LAST_SEQ    APPLIED_SEQ  LAST_APP_TIMESTAMP   ARC_DIFF
---------- ---------- -----------   --------------------- ----------
 1         21282      21281         09-IYUL-2016 12:06:5     1
 2         23747      23746         09-IYUL-2016 12:16:13    1

To check archive log apply on primary database:

SQL> SET LINESIZE 150
SET PAGESIZE 999
COL NAME FORMAT A60
COL DEST_TYPE FORMAT A10
COL ARCHIVED FORMAT A10
COL APPLIED FORMAT A10

SELECT SEQUENCE#,
NAME,
DEST_ID ,
CASE WHEN STANDBY_DEST = 'YES' THEN 'Standby' ELSE 'Local' END
AS DEST_TYPE ,
ARCHIVED ,
APPLIED
FROM V$ARCHIVED_LOG
WHERE SEQUENCE# > (SELECT MAX (SEQUENCE#)
FROM V$ARCHIVED_LOG
WHERE STANDBY_DEST = 'YES' AND APPLIED = 'YES')
ORDER BY SEQUENCE# , DEST_ID ;


 SEQUENCE#  NAME                                                          DEST_ID  DEST_TYPE  ARCHIVED APPLIED
---------- -------------------------------------------------------------- -------  ---------- -------- --------
 23748      +FRA/TESTCDB/ARCHIVELOG/2016_07_09/thread_2_seq_23748.10041.9   1      Local        YES       NO
 23748      +DATA/TESTCDB/ARCHIVELOG/2016_07_09/thread_2_seq_23748.10062.   2      Local        YES       NO
 23748      TESTSTB                                                         3      Standby      YES       NO



LOGICAL STANDBY COMMANDS:


To Restart SQL apply on logical standby

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

To Stop SQL apply on logical standby

SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;

Run the following SQL against the logical standby to start real-time SQL apply if the SQL apply failed with an error, and you are 100% certain that the transaction is safe to skip

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE SKIP FAILED TRANSACTION;

To see unsupported tables for logical standby:

SQL> SELECT * FROM DBA_LOGSTDBY_UNSUPPORTED_TABLE ORDER BY OWNER, TABLE_NAME;

To know which archive log sequences are at what stage for logical standby?

SQL> SELECT 'RESTART' "TYPE",
 P.RESTART_SCN "SCN",
 TO_CHAR (P.RESTART_TIME, 'yyyy/mm/dd hh24:mi:ss') "TIME",
 L.SEQUENCE# "SEQ#"
 FROM V$LOGSTDBY_PROGRESS P, DBA_LOGSTDBY_LOG L
 WHERE P.RESTART_SCN >= L.FIRST_CHANGE# AND P.RESTART_SCN < L.NEXT_CHANGE#
UNION
SELECT 'RESTART',
 P.RESTART_SCN,
 TO_CHAR (P.RESTART_TIME, 'yyyy/mm/dd hh24:mi:ss'),
 L.SEQUENCE#
 FROM V$LOGSTDBY_PROGRESS P, V$STANDBY_LOG L
 WHERE P.RESTART_SCN >= L.FIRST_CHANGE# AND P.LATEST_SCN <= L.LAST_CHANGE#
UNION
SELECT 'APPLIED',
 P.APPLIED_SCN,
 TO_CHAR (P.APPLIED_TIME, 'yyyy/mm/dd hh24:mi:ss'),
 L.SEQUENCE#
 FROM V$LOGSTDBY_PROGRESS P, DBA_LOGSTDBY_LOG L
 WHERE P.APPLIED_SCN >= L.FIRST_CHANGE# AND P.APPLIED_SCN < L.NEXT_CHANGE#
UNION
SELECT 'APPLIED',
 P.APPLIED_SCN,
 TO_CHAR (P.APPLIED_TIME, 'yyyy/mm/dd hh24:mi:ss'),
 L.SEQUENCE#
 FROM V$LOGSTDBY_PROGRESS P, V$STANDBY_LOG L
 WHERE P.APPLIED_SCN >= L.FIRST_CHANGE# AND P.LATEST_SCN <= L.LAST_CHANGE#
UNION
SELECT 'MINING',
 P.MINING_SCN,
 TO_CHAR (P.MINING_TIME, 'yyyy/mm/dd hh24:mi:ss'),
 L.SEQUENCE#
 FROM V$LOGSTDBY_PROGRESS P, DBA_LOGSTDBY_LOG L
 WHERE P.MINING_SCN >= L.FIRST_CHANGE# AND P.MINING_SCN < L.NEXT_CHANGE#
UNION
SELECT 'MINING',
 P.MINING_SCN,
 TO_CHAR (P.MINING_TIME, 'yyyy/mm/dd hh24:mi:ss'),
 L.SEQUENCE#
 FROM V$LOGSTDBY_PROGRESS P, V$STANDBY_LOG L
 WHERE P.MINING_SCN >= L.FIRST_CHANGE# AND P.LATEST_SCN <= L.LAST_CHANGE#
UNION
SELECT 'SHIPPED',
 P.LATEST_SCN,
 TO_CHAR (P.LATEST_TIME, 'yyyy/mm/dd hh24:mi:ss'),
 L.SEQUENCE#
 FROM V$LOGSTDBY_PROGRESS P, DBA_LOGSTDBY_LOG L
 WHERE P.LATEST_SCN >= L.FIRST_CHANGE# AND P.LATEST_SCN < L.NEXT_CHANGE#
UNION
SELECT 'SHIPPED',
 P.LATEST_SCN,
 TO_CHAR (P.LATEST_TIME, 'yyyy/mm/dd hh24:mi:ss'),
 L.SEQUENCE#
 FROM V$LOGSTDBY_PROGRESS P, V$STANDBY_LOG L
 WHERE P.LATEST_SCN >= L.FIRST_CHANGE# AND P.LATEST_SCN <= L.LAST_CHANGE#;

To know is the SQL Apply up to date

SQL> SELECT TO_CHAR(LATEST_TIME,'yyyy/mm/dd hh24:mi:ss') "LATEST_TIME",
TO_CHAR(APPLIED_TIME,'yyyy/mm/dd hh24:mi:ss') "APPLIED_TIME",
APPLIED_SCN, LATEST_SCN
FROM V$LOGSTDBY_PROGRESS;

To know is the Logical standby applying changes? Run the following SQL against the Logical standby database:

SQL> SELECT REALTIME_APPLY, STATE FROM V$LOGSTDBY_STATE;

If the value of STATE is “NULL” or “SQL APPLY NOT ON” then the Sql Apply is not running.The value of REALTIME_APPLY should be Y to allow for real time apply from the standby redo logs. To know what major Sql Apply events have occurred, run the following SQL against the Logical standby database:

SQL> SELECT TO_CHAR (EVENT_TIME, 'YYYY/MM/DD HH24:MI:SS') "EVENT_TIME",
STATUS, EVENT
FROM DBA_LOGSTDBY_EVENTS
ORDER BY EVENT_TIME;

To know what major Dataguard events have occurred, run the following SQL against the Logical standby database:

SQL> SELECT TO_CHAR (TIMESTAMP, 'yyyy/mm/dd hh24:mi:ss') "TIME",
ERROR_CODE "ERROR", DEST_ID "DEST", MESSAGE
FROM V$DATAGUARD_STATUS
WHERE timestamp > TRUNC (SYSDATE + 6 / 24)
ORDER BY timestamp DESC;

To know where are the archive logs going and are there any achieving issues, run the following SQL against either the logical standby or primary database:

SQL> SELECT DEST_ID "DID",
STATUS, DESTINATION, ARCHIVER, VALID_NOW, VALID_TYPE, VALID_ROLE, ERROR
FROM V$ARCHIVE_DEST
WHERE STATUS <> 'INACTIVE';