Navigation Bar

Saturday, September 21, 2024

Dataguard Checks Queries

General database information 
select name	db_unique_name 	switchover_status 	database_role	open_mode	protection_mode	protection_level from v$database;
On Standby
NAMEDB_UNIQUE_NAMESWITCHOVER_STATUSDATABASE_ROLEOPEN_MODEPROTECTION_MODEPROTECTION_LEVEL
ORCLALPHANOT ALLOWEDPHYSICAL STANDBYREAD ONLY WITH APPLYMAXIMUM PERFORMANCEMAXIMUM PERFORMANCE
On Primary
NAMEDB_UNIQUE_NAMESWITCHOVER_STATUSDATABASE_ROLEOPEN_MODEPROTECTION_MODEPROTECTION_LEVEL
ORCLorclTO STANDBYPRIMARYREAD WRITEMAXIMUM PERFORMANCEMAXIMUM PERFORMANCE

To display redo information like redo data generated by the primary database that is not available on the standby database and how much redo has not been applied to the standby database.
select * from v$dataguard_stats;
On Standby
SOURCE_DBIDSOURCE_DB_UNIQUE_NAMENAMEVALUEUNITTIME_COMPUTEDDATUM_TIMECON_ID
1700090799orcltransport lag+00 00:00:00day(2) to second(0) interval05/27/2024 13:40:5805/27/2024 13:40:560
1700090799orclapply lag+00 00:00:00day(2) to second(0) interval05/27/2024 13:40:5805/27/2024 13:40:560
1700090799orclapply finish time+00 00:00:00.000day(2) to second(3) interval05/27/2024 13:40:58 0
0 estimated startup time22second05/27/2024 13:40:58 0

To Check for messages/errors

select * from v$dataguard_status order by facility,	timestamp desc;
On the Standby Database
FACILITYSEVERITYDEST_IDMESSAGE_NUMERROR_CODECALLOUTTIMESTAMPMESSAGECON_ID
Log Apply ServicesInformational0120NO27-MAY-24 13:00:25Media Recovery Log /u01/app/oracle/oradata/ORCL/arch/ora_1_86_1172310070.arc0
Log Apply ServicesWarning0130NO27-MAY-24 13:00:25Media Recovery Waiting for T-1.S-87 (in transit)0
Log Apply ServicesInformational0110NO27-MAY-24 13:00:24Managed Recovery starting Real Time Apply0
Log Apply ServicesControl0100YES27-MAY-24 13:00:19Background Managed Recovery process started0
Log Apply ServicesControl090YES27-MAY-24 13:00:19Starting Managed Recovery process for Physical Standby0
Log Transport ServicesControl080YES27-MAY-24 12:50:30Completed archiving T-1.S-860
Log Transport ServicesControl070YES27-MAY-24 12:50:29Beginning to archive T-1.S-86 (SCN:0x0000000001a98eff-SCN:0x0000000001a9ac89)0
Remote File ServerInformational060NO27-MAY-24 12:50:28Opened LNO:5 for DBID:1700090799 B-1172310070.T-1.S-860
Remote File ServerInformational050NO27-MAY-24 12:50:27krsr_rfs_atc: Identified database type as 'PHYSICAL STANDBY': Client is FAL (PID:10731)0
Remote File ServerInformational040NO27-MAY-24 12:50:27Opened LNO:4 for DBID:1700090799 B-1172310070.T-1.S-870
Remote File ServerInformational030NO27-MAY-24 12:50:26Primary database is in MAXIMUM PERFORMANCE mode0
Remote File ServerInformational020NO27-MAY-24 12:50:26krsr_rfs_atc: Identified database type as 'PHYSICAL STANDBY': Client is Foreground (PID:10739)0
Remote File ServerInformational010NO27-MAY-24 12:50:26krsr_rfs_atc: Identified database type as 'PHYSICAL STANDBY': Client is ASYNC (PID:10755)0

On the primary


FACILITYSEVERITYDEST_IDMESSAGE_NUMERROR_CODECALLOUTTIMESTAMPMESSAGECON_ID
Log Transport ServicesControl060YES21-SEP-24 17:27:21Beginning to archive LNO:3 T-1.S-870
Log Transport ServicesWarning0312541NO21-SEP-24 17:24:13Check whether the listener is up and running.0
Log Transport ServicesWarning0512541NO21-SEP-24 17:24:13Check whether the listener is up and running.0
Log Transport ServicesControl040YES21-SEP-24 17:24:13Completed archiving T-1.S-860
Log Transport ServicesControl020YES21-SEP-24 17:24:11Beginning to archive T-1.S-86 (SCN:0x0000000001a98eff-SCN:0x0000000001a9ac89)0
Log Transport ServicesWarning0112541NO21-SEP-24 17:24:06Check whether the listener is up and running.0

To display current status information for specific physical standby database background processes.

select process,	status,	thread#, sequence#, block#, blocks from v$managed_standby;
On the standby Database

PROCESSSTATUSTHREAD#SEQUENCE#BLOCK#BLOCKS
ARCHCONNECTED0000
DGRDALLOCATED0000
DGRDALLOCATED0000
ARCHCONNECTED0000
ARCHCLOSING18614336339
ARCHCONNECTED0000
ARCHCONNECTED0000
ARCHCONNECTED0000
ARCHCONNECTED0000
ARCHCONNECTED0000
ARCHCONNECTED0000
ARCHCONNECTED0000
ARCHCONNECTED0000
ARCHCONNECTED0000
ARCHCONNECTED0000
ARCHCONNECTED0000
ARCHCONNECTED0000
ARCHCONNECTED0000
ARCHCONNECTED0000
ARCHCONNECTED0000
ARCHCONNECTED0000
ARCHCONNECTED0000
ARCHCONNECTED0000
ARCHCONNECTED0000
ARCHCONNECTED0000
ARCHCONNECTED0000
ARCHCONNECTED0000
ARCHCONNECTED0000
ARCHCONNECTED0000
ARCHCONNECTED0000
ARCHCONNECTED0000
ARCHCONNECTED0000
RFSIDLE187494561
RFSIDLE1000
RFSIDLE0000
MRP0APPLYING_LOG18749456409600

To find logs received and applied on the physical standby database

select 'last_logs_applied : ' as lastlog_applied, to_char(next_time,	'DD-MON-YY HH24:MI:SS') FROM V$ARCHIVED_LOG
WHERE SEQUENCE# = (SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG WHERE APPLIED = 'YES')
union
select 'last_logs_received : ' as lastlog_received, to_char(next_time,	'DD-MON-YY HH24:MI:SS') FROM V$ARCHIVED_LOG
WHERE SEQUENCE# = (SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG );
LASTLOG_APPLIEDLOG_TIME
last_logs_applied : 21-SEP-24 17:24:10
last_logs_received : 21-SEP-24 17:24:10

To monitor efficient recovery operations as well as to estimate the time required to complete the current operation in progress:

SELECT TO_CHAR(START_TIME	'DD-MON-RR HH24:MI:SS') start_time, item, round(sofar/1024,2) "MB/Sec"
FROM v$recovery_progress;
START_TIMEitemMB/Sec 
27-MAY-24 13:00:24Active Apply Rate0.9
27-MAY-24 13:00:24Average Apply Rate0.01
27-MAY-24 13:00:24Maximum Apply Rate1.33
27-MAY-24 13:00:24Redo Applied0.03
27-MAY-24 13:00:24Recovery ID0
27-MAY-24 13:00:24Last Applied Redo0
27-MAY-24 13:00:24Active Time0.17
27-MAY-24 13:00:24Apply Time per Log0.15
27-MAY-24 13:00:24Checkpoint Time per Log0.02
27-MAY-24 13:00:24Elapsed Time3.5
27-MAY-24 13:00:24Standby Apply Lag0

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.
SELECT THREAD#, GROUP#, SEQUENCE#, BYTES, ARCHIVED, STATUS FROM V$STANDBY_LOG ORDER BY THREAD#,	GROUP#;
On Standby
THREAD#GROUP#SEQUENCE#BYTESARCHIVEDSTATUS
1487209715200YESACTIVE
150209715200NOUNASSIGNED
160209715200NOUNASSIGNED
180209715200NOUNASSIGNED
190209715200NOUNASSIGNED
110052428800YESUNASSIGNED
111052428800YESUNASSIGNED
112052428800YESUNASSIGNED
113052428800YESUNASSIGNED
1140209715200YESUNASSIGNED
1230209715200YESUNASSIGNED

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.
SELECT THREAD#,	DEST_ID,	DESTINATION,	GVAD.STATUS	,TARGET,	SCHEDULE,	PROCESS,	MOUNTID
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;

On Standby
THREAD#DEST_IDDESTINATIONSTATUSTARGETSCHEDULEPROCESSMOUNTID
11/u01/app/oracle/oradata/ORCL/arch/VALIDLOCALACTIVEARCH0
12orclVALIDREMOTEPENDINGLGWR0
132/u01/app/oracle/oradata/ORCL/arch/VALIDLOCALACTIVERFS0


On Primary
THREAD#DEST_IDDESTINATIONSTATUSTARGETSCHEDULEPROCESSMOUNTID
11/u01/app/oracle/oradata/ORCL/arch/VALIDPRIMARYACTIVEARCH0
12alphaVALIDSTANDBYPENDINGLGWR0

Verify the last sequence# received and the last sequence# applied to standby database.

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;
ThreadLast seq receivedLast seq applied
18686

Additional check queries for dataguard 

Check V$ARCHIVE_GAP for gaps between physical and standby database
To determine gaps in archive logs on the standby database. Since fal_server and fal_client parameters are configured, dataguard should automatically resolve the archive log gaps by requesting the primary database to send the missing archive logs. But if you dont have the archive logs that the physical standby database needs, there will be gaps and the standby database will be out of sync. You will nned to take remedial actions to resolve these gaps.
SQL> SELECT * FROM V$ARCHIVE_GAP;
Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization parameter is defined to a value that is sufficiently large enough to maintain adequate log switch information to resolve archivelog gaps.

Queries of SCN and timestamp that can be run on primary and stanby to determine the lag
SQL> select current_scn, SCN_TO_TIMESTAMP(current_scn) from v$database; --scn 38558373
SQL> select scn_to_timestamp(38558373) from dual;

Check V$ARCHIVE_DEST for errors 
SQL> SELECT DEST_NAME, STATUS, ERROR FROM V$ARCHIVE_DEST WHERE STATUS='ERROR';

Show received archived logs on physical standby 

select registrar, creator, thread#, sequence#, first_change#, next_change# from v$archived_log;
On the standby database
 
REGISTRARCREATORTHREAD#SEQUENCE#FIRST_CHANGE#NEXT_CHANGE#
SRMNSRMN1552760684327607362
SRMNSRMN1562760736227607378
RFSARCH1572760737827628657
RFSARCH1602766050527662145
RFSARCH1582762865727634661
RFSARCH1802784556427847264
RFSARCH1832786824227870109
RFSARCH1592763466127660505
RFSARCH1632769288427694317
RFSARCH1622769013427692884
RFSARCH1642769431727696037
RFSARCH1652769603727697757
RFSARCH1612766214527690134
RFSARCH1672770040527702113
RFSARCH1662769775727700405
RFSARCH1682770211327704788
RFSARCH1702770626827707669
RFSARCH1692770478827706268
RFSARCH1712770766927788297
RFSARCH1732780858727811370
RFSARCH1722778829727808587
RFSARCH1742781137027813762
RFSARCH1752781376227816515
RFSARCH1772783385127836836
RFSARCH1762781651527833851
RFSARCH1782783683627843320
RFSARCH1792784332027845564
RFSARCH1822786400427868242
RFSARCH1812784726427864004
RFSARCH1842787010927873939
RFSARCH1852787393927889407
RFSARCH1862788940727896969

Show archived logs sent on primary database 

select registrar, creator, thread#, sequence#, first_change#, next_change# from v$archived_log;
REGISTRARCREATORTHREAD#SEQUENCE#FIRST_CHANGE#NEXT_CHANGE#
FGRDFGRD1362531039925382027
FGRDFGRD1372538202725385638
FGRDFGRD1392641857726440791
FGRDFGRD1402644079126449986
FGRDFGRD1422645214526461635
FGRDFGRD1562760736227607378
FGRDFGRD1492751590227515929
FGRDFGRD1532755895927575544
FGRDFGRD1542757554427606843
FGRDFGRD1552760684327607362
FGRDFGRD1442746491427471593
ARCHARCH1211803361118091783
ARCHARCH1221809178318099255
ARCHARCH1231809925519115685
ARCHARCH1241911568520164793
ARCHARCH1252016479320260810
ARCHARCH1262026081021275680
ARCHARCH1272127568022276846
ARCHARCH1282227684623278152
ARCHARCH1292327815224279303
ARCHARCH1302427930324280983
ARCHARCH1312428098324282447
ARCHARCH1322428244724284156
ARCHARCH1332428415624285937
ARCHARCH1342428593724287291
ARCHARCH1352428729125310399
ARCHARCH1382538563826418577

To check the mrp status in db level SELECT PROCESS, CLIENT_PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM GV$MANAGED_STANDBY; One of the rows will be for process MRP as below MRP0 N/A APPLYING_LOG 1 520 20261 20971520 References 
Physical Standby Data Guard useful SQL scripts
Verify Data Guard Implementation

Thought for the day
Humility is the fear of the Lord; 
  its wages are riches and honor and life. 
Proverbs 4:4 

A good name is more desirable than great riches; 
  to be esteemed is better than silver or gold. 
Proverbs 4:1
at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)