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
Determining Dataguard Standby Apply Rate
SQL> select snapshot_time, thread#, sequence#, applied_scn, applied_time, apply_rate from V$standby_apply_snapshot; SNAPSHOT_TIME THREAD# SEQUENCE# APPLIED_SCN APPLIED_TIME APPLY_RATE ----------------- ------- --------- ----------- ------------- ---------- 27-02-23 15:45:08 1 8684 2549146933 27-02-08 15:44:48 47234 27-02-23 15:45:08 2 7145 2549146847 27-02-08 15:44:49 47234
