1) Basic information of database (Primary or Standby)
Primary:-
set lines 200
set pages 200
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 orcl READ WRITE MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE NOT ALLOWED
Standby:-
set lines 200
set pages 200
SELECT DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS FROM V$DATABASE;SQL> SQL>
DATABASE_ROLE INSTANCE OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL SWITCHOVER_STATUS
---------------- ------------ ------------ -------------------- -------------------- --------------------
PHYSICAL STANDBY orcldg MOUNTED MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE NOT ALLOWED2) Sync check on Standby DB query:-
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#;
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 103294 103294 03) To check redo apply and Media recovery service status:
SELECT PROCESS,STATUS, THREAD#,SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY ;
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
------- ------------ ---------- ---------- ---------- ----------
ARCH CONNECTED 0 0 0 0
DGRD ALLOCATED 0 0 0 0
DGRD ALLOCATED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
MRP0 APPLYING_LOG 1 136 141429 409600
7 rows selected.
4) To calculate the Redo bytes per second
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.12.2024', 'DD.MM.YYYY')
AND TO_DATE ('01.12.2024', 'DD.MM.YYYY');5) To check status of Data Guard synchronization(standby):
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 326) To verify MRP - Managed Recovery is running on the standby :
SELECT PROCESS FROM V$MANAGED_STANDBY WHERE PROCESS LIKE 'MRP%';
PROCESS
-------
MRP0
7) To show information about the protection mode, the protection level, the role of the database, and switchover status:
Primary:-
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 orcl READ WRITE MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE NOT ALLOWED
Standby:-
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 orcldg MOUNTED MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE NOT ALLOWED8) On the standby database, query the V$ARCHIVED_LOG view to identify existing files in the archived redo log.
SELECT THREAD#, MAX(SEQUENCE#) AS "LAST_APPLIED_LOG" FROM V$LOG_HISTORY GROUP BY THREAD#; THREAD# LAST_APPLIED_LOG ---------- ---------------- 1 135 SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
9) To determine which log files were not received by the standby site.
Primary:-
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /data1/ORCL/ARCH
Oldest online log sequence 167
Next log sequence to archive 169
Current log sequence 169
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#);
THREAD# SEQUENCE#
---------- ----------
1 136
1 137
1 138
1 139
1 140
1 141
1 142
1 143
1 144
1 145
1 146
1 147
1 148
1 149
1 150
1 151
1 152
1 153
1 154
1 155
1 156
1 157
1 158
1 159
1 160
1 161
1 162
1 163
1 164
1 165
1 166
1 167
1 168
Standby:-
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#);
THREAD# SEQUENCE#
---------- ----------
1 119
1 120
1 121
1 122
1 123
1 124
1 125
1 126
1 127
1 128
1 129
1 130
1 131
1 134
1 132
1 133
1 135Note:- In above case archive log from 136 to 168 are not applied to Standby database.
10) Archivelog difference: Run this on the primary database. (not for real-time apply):
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 168 135 20-NOV-2024 22:30:29 3311) To check archive log apply on primary database:
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
---------- ----------------------------------------------------- ---------- ---------- ----------
136 /data1/ORCL/ARCH/1_136_1178543939.arc 1 Local YES NO
137 /data1/ORCL/ARCH/1_137_1178543939.arc 1 Local YES NO
138 /data1/ORCL/ARCH/1_138_1178543939.arc 1 Local YES NO
139 /data1/ORCL/ARCH/1_139_1178543939.arc 1 Local YES NO
140 /data1/ORCL/ARCH/1_140_1178543939.arc 1 Local YES NO
141 /data1/ORCL/ARCH/1_141_1178543939.arc 1 Local YES NO
142 /data1/ORCL/ARCH/1_142_1178543939.arc 1 Local YES NO
143 /data1/ORCL/ARCH/1_143_1178543939.arc 1 Local YES NO
144 /data1/ORCL/ARCH/1_144_1178543939.arc 1 Local YES NO
145 /data1/ORCL/ARCH/1_145_1178543939.arc 1 Local YES NO
146 /data1/ORCL/ARCH/1_146_1178543939.arc 1 Local YES NO
147 /data1/ORCL/ARCH/1_147_1178543939.arc 1 Local YES NO
148 /data1/ORCL/ARCH/1_148_1178543939.arc 1 Local YES NO
149 /data1/ORCL/ARCH/1_149_1178543939.arc 1 Local YES NO
150 /data1/ORCL/ARCH/1_150_1178543939.arc 1 Local YES NO
151 /data1/ORCL/ARCH/1_151_1178543939.arc 1 Local YES NO
152 /data1/ORCL/ARCH/1_152_1178543939.arc 1 Local YES NO
153 /data1/ORCL/ARCH/1_153_1178543939.arc 1 Local YES NO
154 /data1/ORCL/ARCH/1_154_1178543939.arc 1 Local YES NO
155 /data1/ORCL/ARCH/1_155_1178543939.arc 1 Local YES NO
156 /data1/ORCL/ARCH/1_156_1178543939.arc 1 Local YES NO
157 /data1/ORCL/ARCH/1_157_1178543939.arc 1 Local YES NO
158 /data1/ORCL/ARCH/1_158_1178543939.arc 1 Local YES NO
159 /data1/ORCL/ARCH/1_159_1178543939.arc 1 Local YES NO
160 /data1/ORCL/ARCH/1_160_1178543939.arc 1 Local YES NO
161 /data1/ORCL/ARCH/1_161_1178543939.arc 1 Local YES NO
162 /data1/ORCL/ARCH/1_162_1178543939.arc 1 Local YES NO
163 /data1/ORCL/ARCH/1_163_1178543939.arc 1 Local YES NO
164 /data1/ORCL/ARCH/1_164_1178543939.arc 1 Local YES NO
165 /data1/ORCL/ARCH/1_165_1178543939.arc 1 Local YES NO
166 /data1/ORCL/ARCH/1_166_1178543939.arc 1 Local YES NO
167 /data1/ORCL/ARCH/1_167_1178543939.arc 1 Local YES NO
168 /data1/ORCL/ARCH/1_168_1178543939.arc 1 Local YES NO
33 rows selected.
12) To calculate how far behind the standby database is in hours compared to the Primary database in Oracle Data Guard.
SELECT
(a.amct - b.bmct) * 24 AS "Hours Standby is Behind:"
FROM
(SELECT MAX(completion_time) AS amct
FROM v$archived_log) a,
(SELECT MAX(completion_time) AS bmct
FROM v$archived_log
WHERE applied = 'YES') b;
Hours Standby is Behind:
------------------------
565.85611113) how to check/verify that standby redo log files were used for real time apply
set lines 200
set pages 200
col DEST_NAME for a25
select DEST_ID,dest_name,status,type,srl,recovery_mode from v$archive_dest_status where dest_id<3;
DEST_ID DEST_NAME STATUS TYPE SRL RECOVERY_MODE
---------- ------------------------- --------------- ---------------- --- ----------------------------------
1 LOG_ARCHIVE_DEST_1 VALID LOCAL NO MANAGED REAL TIME APPLY
2 LOG_ARCHIVE_DEST_2 INACTIVE LOCAL NO IDLE14) how to check/verify that standby redo log files were used for real time apply
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 15) 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
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;
Primary Output - since I deferred the location
THREAD# DEST_ID DESTINATION STATUS TARGET SCHEDULE PROCESS MID
---------- ---------- ----------------------------------- --------- ---------------- -------- ------- ---
1 1 /data1/ORCL/ARCH VALID PRIMARY ACTIVE ARCH 0
1 2 orcldg DEFERRED STANDBY PENDING LGWR 0
Standby Output:-
THREAD# DEST_ID DESTINATION STATUS TARGET SCHEDULE PROCESS MID
---------- ---------- ----------------------------------- --------------- ---------------- -------- ------- ---
1 1 /data1/ORCLDG/ARCH VALID LOCAL ACTIVE ARCH 0
1 32 /data1/ORCLDG/ARCH VALID LOCAL ACTIVE RFS 016) Determining Dataguard Standby Apply Rate
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
You can see from the applied_time how far behind you are in applying. So is my apply rate 47MB per second
