Skip to content

Physical Standby Data Guard useful SQL scripts

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

 

Brijesh Gogia

6 Comments

  1. Golekha Sahu Golekha Sahu

    Thanks for best effort !

  2. Sham Sham

    Ton thanks for ur marvelous effort !!!

  3. Deborah Deborah

    Much appreciate your effort- Useful scripts especially when the grid is not available. 🙂

  4. abi abi

    Excellent Stuff

  5. Dan Dan

    Great post with accurate information! Thanks!

  6. Tim Gerringer at tgerringer@foresightgroup.com Tim Gerringer at tgerringer@foresightgroup.com

    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

Leave a Reply