Run the following commnad, we can see ‘-pwfile ‘+DATA1/TESTDB/PASSWORD/pwdTESTDB’ has been added into configuration automatically when we create password file.
SQL> SELECT thread#, group#, sequence#, bytes, archived, status FROM v$standby_log order by thread#, group#;
THREAD# GROUP# SEQUENCE# BYTES ARC STATUS ---------- ---------- ---------- ---------- --- ---------- 1 11 0 1073741824 NO UNASSIGNED 1 12 328 1073741824 YES ACTIVE 1 13 0 1073741824 YES UNASSIGNED 2 21 0 1073741824 NO UNASSIGNED 2 22 234 1073741824 YES ACTIVE 2 23 0 1073741824 YES UNASSIGNED
6 rows selected.
Check primary & standby database protection mode and switchover status.
— On Primary
SQL>SELECTNAME,OPEN_MODE,DATABASE_ROLE,PROTECTION_MODE,DB_UNIQUE_NAME,
SWITCHOVER_STATUS FROM V$DATABASE;
NAME OPEN_MODE DATABASE_R PROTECTION_MODE DB_UNIQUE_ SWITCHOVER_STATUS
--------- ------------ ---------- -------------------- ---------- --------------------
TESTDB READ WRITE PRIMARY MAXIMUM PERFORMANCE TESTDB TO STANDBY
— ON standby
SQL>SELECTNAME,OPEN_MODE,DATABASE_ROLE,PROTECTION_MODE,DB_UNIQUE_NAME,
SWITCHOVER_STATUS FROM V$DATABASE;
NAME OPEN_MODE DATABASE_R PROTECTION_MODE DB_UNIQUE_ SWITCHOVER_STATUS
--------- ---------- ---------- -------------------- ---------- --------------------
TESTDB MOUNTED PHYSICAL STANDBY MAXIMUM PERFORMANCE STBYDB NOT ALLOWED
Check maximum archive log sequences
— ON primary
SQL> select THREAD#,STATUS,ENABLED,GROUPS,INSTANCE, MAX(SEQUENCE#) FROM gv$THREAD group by THREAD#,STATUS,ENABLED,GROUPS,INSTANCE ;
THREAD# STATUS ENABLED GROUPS INSTANCE MAX(SEQUENCE#) ---------- ------ -------- ---------- ---------- -------------- 2 OPEN PUBLIC 2 TESTDB2 234 1 OPEN PUBLIC 2 TESTDB1 328
— On standby
SQL> select THREAD#,STATUS,ENABLED,GROUPS,INSTANCE, MAX(SEQUENCE#) FROM gv$THREAD group by THREAD#,STATUS,ENABLED,GROUPS,INSTANCE ;
THREAD# STATUS ENABLED GROUPS INSTANCE MAX(SEQUENCE#) ---------- ------ -------- ---------- ---------- -------------- 2 OPEN PUBLIC 2 TESTDB2 234 1 OPEN PUBLIC 2 TESTDB1 328
Check archived logs are applied or not until the maximum sequences
— On primary
SQL> SELECT THREAD#,DEST_ID,APPLIED,max(SEQUENCE#) FROM V$ARCHIVED_LOG where DEST_ID>1 group by THREAD#,APPLIED,DEST_ID;
THREAD# DEST_ID APPLIED MAX(SEQUENCE#)
---------- ---------- --------- --------------
1 2 YES 327
2 2 YES 233
On standby
SQL> SELECT THREAD#,DEST_ID,APPLIED,max(SEQUENCE#) FROM V$ARCHIVED_LOG where APPLIED='YES' group by THREAD#,APPLIED,DEST_ID;
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 41
Check recovery process
— on standby only
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 order by start_time;
START_TIME ITEM MB/Sec ------------------ -------------------------------- ---------- 28-FEB-25 22:07:39 Active Apply Rate 1.33 28-FEB-25 22:07:39 Active Time 34.95 28-FEB-25 22:07:39 Apply Time per Log .68 28-FEB-25 22:07:39 Average Apply Rate .12 28-FEB-25 22:07:39 Checkpoint Time per Log 0 28-FEB-25 22:07:39 Redo Applied 18 28-FEB-25 22:07:39 Last Applied Redo 0 28-FEB-25 22:07:39 Log Files .05 28-FEB-25 22:07:39 Maximum Apply Rate 69.75 28-FEB-25 22:07:39 Recovery ID 0 28-FEB-25 22:07:39 Elapsed Time 144.17
11 rows selected.
Check gap status between primary and standby database
SQL>select status, gap_status from v$archive_dest_status where dest_id = 2;
STATUS GAP_STATUS --------- ------------------------ VALID NO GAP
Appendix – Monitoring Data Guard Configuration Health Using SQL
— on primary database
Goal
Query
Expected Results
Check if any remote standby archive destination is getting errors. Check if all remote standby archive destinations is enabled or “VALID”.
select sysdate,status,error from gv$archive_dest_status where type=’PHYSICAL’ and status!=’VALID’ or error is not null;
Good behavior – no rows returned. If query returns rows, then raise an alert with the returned data.
Check if any nologging activity has occurred on the primary for the last day.
select file#, name, unrecoverable_change#, unrecoverable_time from v$datafile where unrecoverable_time>(sysdate – 1);
Good behavior – no rows returned. If query returns rows, then the standby is vulnerable and the subsequent file needs to be refreshed on the standby.
Detect gaps on the standby
select sysdate,database_mode,recovery_mode, gap_status from v$archive_dest_status where type=’PHYSICAL’ and gap_status!=’NO GAP’;
Good behavior – no rows returned. If query returns rows, then there’s an existing gap between the primary and the standby. Subsequent action is to run the same query on the standby, if the output is identical, then no action required. If the output on the standby does not match the primary then the datafile on the standby should be refreshed.
Assess if any severe Data Guard event occurred in the last day
SQL> select * from v$dataguard_status where severity in (‘Error’,’Fatal’) and TIMESTAMP > sysdate -1;
Good behavior – no rows returned. If query returns rows then the result should raise an alert with the returned output.
FOR SYNC ENVIRONMENTS ONLY:
Assess if running in Maximum Availability mode and configuration is in sync.
select sysdate,protection_mode, synchronized, synchronization_status from v$archive_dest_status where type=’PHYSICAL’ and synchronization_status !=’OK’;
Good behavior – no rows returned. If query returns rows then the result should raise an alert with the returned output.
— on standby
Goal
Query
Expected Results
Determine if there is a transport lag
select name,value,time_computed,datum_time from v$dataguard_stats where name=’transport lag’ and value > ‘+00 00:01:00’;
Good behavior – no rows are returned. If no rows are returned then this implies that there is no transport lag
Determine if there is an apply lag
select name,value,time_computed,datum_time from v$dataguard_stats where name=’apply lag’ and value > ‘+00 00:01:00’;
Good behavior – no rows are returned. If no rows are returned then this implies that there is no apply lag
Standby data file check (offline files or files that are not accessible)
select * from v$datafile_header where status =’OFFLINE’ or ERROR is not null;
Good behavior – no rows returned. Any rows returned will show which file(s) are having IO or recovery issues.
Verify that the Media Recovery Process is currently running.
select * from v$managed_standby where process like ‘MRP%’;
Good behavior – rows returned. If no rows are returned then the MRP process is not running.
Assess if any severe Data Guard event occurred in the last day
select * from v$dataguard_status where severity in (‘Error’,’Fatal’) and timestamp > (sysdate -1);
Good behavior – no rows returned. If query returns rows, then the result should raise an alert with the returned output.
Note: For snapshot standby, you do not need Flashback enabled at database level
Convert physical standby to snapshot standby
On Standby Database
SQL> select name, open_mode from v$database;
NAME OPEN_MODE --------- ----------- TESTDB MOUNTED
SQL> alter database convert to snapshot standby; alter database convert to snapshot standby * ERROR at line 1: ORA-38784: Cannot create restore point 'SNAPSHOT_STANDBY_REQUIRED_02/28/2025 21:40:27'. ORA-01153: an incompatible media recovery is active
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database convert to snapshot standby;
Database altered.
SQL> alter database open;
Database altered.
SQL> select name, guarantee_flashback_database from v$restore_point;
NAME GURA ---------------------------------------------- -------------
SNAPSHOT_STANDBY_REQUIRED_02/28/2025 21:41:04 YES
SQL> create table student(id number(2), name varchar2(10));
Table created.
SQL> insert into student values(1,'John');
1 row created.
SQL> insert into student values (2,'James');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from student;
ID NAME ---------- ---------- 1 John 2 James
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE NO SQL> select name, open_mode from gv$pdbs;
NAME OPEN_MODE -------------------- ---------- PDB$SEED READ ONLY PDB1 READ WRITE PDB$SEED MOUNTED PDB1 MOUNTED
SQL> alter pluggable database pdb1 open instances=all;
Pluggable database altered.
SQL> select name, open_mode from gv$pdbs;
NAME OPEN_MODE -------------------- ---------- PDB$SEED READ ONLY PDB1 READ WRITE PDB$SEED MOUNTED PDB1 MOUNTED
Have to restart database for all PDB open.
$ srvctl stop database -d TESTDB -o immediate
$ srvctl start database -d TESTDB -o open
SQL> col NAME format a20 SQL> select name, open_mode from gv$pdbs;
NAME OPEN_MODE -------------------- ---------- PDB$SEED READ ONLY PDB1 READ WRITE PDB$SEED READ ONLY PDB1 READ WRITE
SQL> select name, open_mode, database_role from v$database;
SQL> select name, open_mode, database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE --------- -------------------- ---------------- ETSTDB MOUNTED SNAPSHOT STANDBY
SQL> alter database convert to physical standby; alter database convert to physical standby * ERROR at line 1: ORA-38777: database must not be started in any other instance
Verify the ASM Filer Driver (AFD) Kernel module has been loaded.
$ lsmod | grep afd oracleafd 226702 1
To confirm ASM Filter Driver has already been installed, loaded, is supported and versions, use afddriverstate installed | loaded | supported | version.
$ afddriverstate version AFD-9325: Driver OS kernel version = 3.10.0-862.el7.x86_64. AFD-9326: Driver build number = RELEASE. AFD-9231: Driver build version = 19.0.0.0.0 (19.24.0.0.0). AFD-9547: Driver available build number = RELEASE. AFD-9232: Driver available build version = 19.0.0.0.0 (19.24.0.0.0).
To check the ASMFD filtering status:
$ asmcmd afd_state ASMCMD-9526: The AFD state is 'LOADED' and filtering is 'ENABLED' on host 'RACNODE1'
Check and list ASMFD disks.
$ asmcmd afd_lsdsk
Oracle ASM Thin Provisioned Attribute
The ASM Thin Provisioned attribute enables or disables the functionality to discard unused storage space after a diskgroup rebalance is completed.
Note: the ASM Thin Provisioned attribute only works with the ASM Filter Driver, setting it on non-ASMFD diskgroups has no effect.
The attribute value can be true to enable or false to disable the functionality. The default value is false.
Oracle ASM informs the storage array which space is no longer used and can be repurposed. If a rebalance is not running, a rebalance can be triggered manually with the ALTER DISKGROUP … REBALANCE SQL statement.
How to check ASM ‘thin_provisioned’ attribute ?
set linesize 250
column diskgroup heading 'Diskgroup' format a15 column name heading 'Attribute Name' format a25 column value heading 'Value' format a15 column read_only heading 'Read Only' format a15
SELECT SUBSTR(dg.name,1,12) AS diskgroup, SUBSTR(a.name,1,24) AS name, SUBSTR(a.value,1,24) AS value, read_only FROM V$ASM_DISKGROUP dg, V$ASM_ATTRIBUTE a WHERE dg.group_number = a.group_number AND a.name NOT LIKE '%template%' AND a.name LIKE '%thin%' /
Diskgroup Attribute Name Value Read Only --------------- ------------------------- --------------- --------------- DATA2 thin_provisioned true N FRA thin_provisioned FALSE N OCR thin_provisioned FALSE N REDO thin_provisioned true N
OR
$ asmcmd lsattr -G FRA | grep thin thin_provisioned
We can easily change the ‘thin_provisioned’ attribute using ASMCA or using ‘alter disk group <DISK_GROUP> set attribute ‘thin_provisioned’=’TRUE’;’
SQL> alter diskgroup FRA set attribute 'thin_provisioned'='TRUE'; Diskgroup altered.
Since Oracle 18c the phase option includes the keywords WITH or WITHOUT, removing the need to use the underscore parameters, check your version documentation for limitations.
SQL> ALTER DISKGROUP DATA REBALANCE WITHOUT COMPACT; Diskgroup altered.
SQL > alter diskgroup data2 rebalance with balance compact; Diskgroup altered.
SQL> ALTER DISKGROUP DATA REBALANCE WITH BALANCE COMPACT POWER 1024 WAIT; Diskgroup altered.
Make DBA Life Easy 