Below are some of the frequently used Physical Standby Data Guard related SQL queries
1) Basic information of database (primary or standby)
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 –––––––– ––––––––––––––– –––––––––– PHYSICAL STANDBY stdby READ ONLY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE NOT ALLOWED
2) Check for messages/errors
SQL> SELECT MESSAGE FROM V$DATAGUARD_STATUS; MESSAGE –––––––––––––––––––––––––––––––––––––––– ARC0: Archival started ARC1: Archival started ARC2: Archival started ARC2: Becoming the ‘no FAL’ ARCH ARC1: Becoming the heartbeat ARCH ARC1: Becoming the active heartbeat ARCH ARC3: Archival started
3) To display current status information for specific physical standby database background processes.
SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY ; PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS ––––- –––––– ––––– ––––– ––––– ––––– ARCH CONNECTED 0 0 0 0 ARCH CONNECTED 0 0 0 0 ARCH CLOSING 1 54 45056 755 ARCH CLOSING 1 57 1 373 RFS IDLE 0 0 0 0 RFS IDLE 0 0 0 0 RFS IDLE 0 0 0 0 RFS IDLE 1 58 30239 1 8 rows selected.
4) Show received archived logs on physical standby
Run this query on physical standby
SQL> select registrar, creator, thread#, sequence#, first_change#, next_change# from v$archived_log; REGISTR CREATOR THREAD# SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# –––- –––- ––––– ––––– ––––––- –––––– RFS ARCH 1 29 1630326 1631783 RFS ARCH 1 30 1631783 1632626 RFS LGWR 1 31 1632626 1669359 RFS ARCH 1 33 1676050 1676124 RFS ARCH 1 32 1669359 1676050 RFS ARCH 1 35 1681145 1681617 RFS ARCH 1 34 1676124 1681145 RFS ARCH 1 37 1688494 1688503 RFS ARCH 1 36 1681617 1688494 RFS ARCH 1 38 1688503 1689533 RFS LGWR 1 39 1689533 1697243
5) To check the log status
SQL> select ‘Last Log applied : ‘ Logs, to_char(next_time,‘DD-MON-YY:HH24:MI:SS’) Time from v$archived_log where sequence# = (select max(sequence#) from v$archived_log where applied=’YES’) union select ‘Last Log received : ‘ Logs, to_char(next_time,‘DD-MON-YY:HH24:MI:SS’) Time from v$archived_log where sequence# = (select max(sequence#) from v$archived_log); LOGS TIME –––––––––– –––––––––––––- Last Log applied : 24-MAR-14:10:11:10 Last Log received : 27-MAR-14:12:40:17
6) To display various information about the redo data. This includes redo data generated by the primary database that is not yet available on the standby database and how much redo has not yet been applied to the standby database.
set lines 132 col value format a20 SQL> select name, value from V$DATAGUARD_STATS; NAME VALUE ––––––––––––––– –––––––––– transport lag +00 00:00:00 apply lag apply finish time estimated startup time 23
7) to monitor efficient recovery operations as well as to estimate the time required to complete the current operation in progress:
SQL> select to_char(start_time, ‘DD-MON-RR HH24:MI:SS’) start_time, item, round(sofar/1024,2) “MB/Sec” from v$recovery_progress where (item=‘Active Apply Rate’ or item=‘Average Apply Rate’); START_TIME ITEM MB/SEC –––––- –––––––––––––––- –––– 27-MAR-14 15:49:44 Active Apply Rate 8.5 27-MAR-14 15:49:44 Average Apply Rate 6.30
8) To find last applied log
SQL> select to_char(max(FIRST_TIME),‘hh24:mi:ss dd/mm/yyyy’) FROM V$ARCHIVED_LOG where applied=‘YES’; TO_CHAR(MAX(FIRST_T –––––––––- 10:11:08 24/03/2014
9) To see if standby redo logs have been created. The standby redo logs should be the same size as the online redo logs. There should be (( # of online logs per thread + 1) * # of threads) standby redo logs. A value of 0 for the thread# means the log has never been allocated.
SQL> SELECT thread#, group#, sequence#, bytes, archived, status FROM v$standby_log order by thread#, group#; THREAD# GROUP# SEQUENCE# BYTES ARC STATUS ––––– ––––– ––––– ––––– –- –––––––––- 1 8 0 104857600 NO UNASSIGNED 1 9 58 104857600 YES ACTIVE 1 10 0 104857600 NO UNASSIGNED 1 11 0 104857600 YES UNASSIGNED
10) To produce a list of defined archive destinations. It shows if they are enabled, what process is servicing that destination, if the destination is local or remote, and if remote what the current mount ID is. For a physical standby we should have at least one remote destination that points the primary set.
column destination format a35 wrap column process format a7 column ID format 99 column mid format 99 SQL> SELECT thread#, dest_id, destination, gvad.status, target, schedule, process, mountid mid FROM gv$archive_dest gvad, gv$instance gvi WHERE gvad.inst_id = gvi.inst_id AND destination is NOT NULL ORDER BY thread#, dest_id; THREAD# DEST_ID DESTINATION STATUS TARGET SCHEDULE PROCESS MID –––– –––- ––––––––––––––––––––––––––––––––––––––––– 1 1 USE_DB_RECOVERY_FILE_DEST VALID LOCAL ACTIVE ARCH 0 1 2 brij VALID REMOTE PENDING LGWR 0 1 32 USE_DB_RECOVERY_FILE_DEST VALID LOCAL ACTIVE RFS 0
11) Verify the last sequence# received and the last sequence# applied to standby database.
SQL> SELECT al.thrd “Thread”, almax “Last Seq Received”, lhmax “Last Seq Applied” FROM (select thread# thrd, MAX(sequence#) almax FROM v$archived_log WHERE resetlogs_change#=(SELECT resetlogs_change# FROM v$database) GROUP BY thread#) al, (SELECT thread# thrd, MAX(sequence#) lhmax FROM v$log_history WHERE resetlogs_change#=(SELECT resetlogs_change# FROM v$database) GROUP BY thread#) lh WHERE al.thrd = lh.thrd; Thread Last Seq Received Last Seq Applied ––––– ––––––––- –––––––– 1 57 53
- Oracle Multitenant DB 4 : Parameters/SGA/PGA management in CDB-PDB - July 18, 2021
- Oracle Multitenant DB 3 : Data Dictionary Architecture in CDB-PDB - March 20, 2021
- Oracle Multitenant DB 2 : Benefits of the Multitenant Architecture - March 19, 2021
Thanks for best effort !
Ton thanks for ur marvelous effort !!!
Much appreciate your effort- Useful scripts especially when the grid is not available. 🙂
Excellent Stuff
Great post with accurate information! Thanks!
my problem is we have 50 plus primary databases with standby databases and most have 2 physical standbys. the only way I feel completely comfortable all is good is to query the primary db with:
select max(sequence#) from v$log_history;
and query the physical standby with:
SELECT al.thrd “Thread”, almax “Last Seq Received”, lhmax “Last Seq Applied”
FROM (select thread# thrd, MAX(sequence#) almax
FROM v$archived_log
WHERE resetlogs_change#=(SELECT resetlogs_change# FROM v$database)
GROUP BY thread#) al,
(SELECT thread# thrd, MAX(sequence#) lhmax
FROM v$log_history
WHERE resetlogs_change#=(SELECT resetlogs_change# FROM v$database)
GROUP BY thread#) lh
WHERE al.thrd = lh.thrd;
I then verify the last seq received and applied on standby matches latest log from primary.
the problem I have is I can’t figure out a way to automate this check process I am doing. I have static queries for above for the 50 plus databases that I dump to txt file and I can scan it to verify ok. I want a way to automate this.
I am wondering if our oem repository database has this information somewhere in it that I can find so its all in one db. the way I currently do it is to have to log into each primary and each standby one by one to figure out if all is good across Data Guard env.
I have tried to create db links to standbys from primary and that does not work. so open to suggestions?
thank you