Physical Standby Data Guard Health Check

Check N+1 standby redo logs are created on both primary and standby

— on primary

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 YES UNASSIGNED
         1         12          0 1073741824 YES UNASSIGNED
         1         13          0 1073741824 YES UNASSIGNED
         2         21          0 1073741824 YES UNASSIGNED
         2         22          0 1073741824 YES UNASSIGNED
         2         23          0 1073741824 YES UNASSIGNED

6 rows selected.

— on standby

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;

   THREAD#    DEST_ID APPLIED   MAX(SEQUENCE#)
---------- ---------- --------- --------------
         1          1 YES                  327
         2          1 YES                  233

Check recovery progress with gv$managed_standby view.

— On primary

SQL>  select INST_ID,process,pid,status,sequence# from gv$managed_standby order by 1,2;

   INST_ID PROCESS   PID                      STATUS        SEQUENCE#
---------- --------- ------------------------ ------------ ----------
         1 ARCH      70960                    CLOSING             327
         1 ARCH      70964                    CLOSING             320
         1 ARCH      70914                    CLOSING             317
         1 ARCH      70956                    CLOSING             318
         1 DGRD      70921                    ALLOCATED             0
         1 DGRD      70917                    ALLOCATED             0
         1 DGRD      9311                     ALLOCATED             0
         1 DGRD      87854                    ALLOCATED             0
         1 LNS       87745                    WRITING             328
         2 ARCH      45746                    CLOSING             232
         2 ARCH      45884                    CLOSING             230
         2 ARCH      45829                    CLOSING             233
         2 ARCH      45559                    CLOSING             231
         2 DGRD      123096                   ALLOCATED             0
         2 DGRD      45652                    ALLOCATED             0
         2 DGRD      45897                    ALLOCATED             0
         2 DGRD      45491                    ALLOCATED             0
         2 LNS       45891                    WRITING             234

18 rows selected.

On standby

SQL>  select INST_ID,process,pid,status,sequence# from gv$managed_standby order by 1,2;

   INST_ID PROCESS   PID                      STATUS        SEQUENCE#
---------- --------- ------------------------ ------------ ----------
         1 ARCH      19160                    CLOSING             229
         1 ARCH      19189                    CLOSING             327
         1 ARCH      19208                    CLOSING             319
         1 ARCH      19230                    CLOSING             326
         1 DGRD      19173                    ALLOCATED             0
         1 DGRD      19178                    ALLOCATED             0
         1 MRP0      28123                    APPLYING_LOG        234
         1 RFS       59847                    RECEIVING           328
         2 ARCH      74638                    CLOSING             233
         2 ARCH      74715                    CLOSING             227
         2 ARCH      74711                    CLOSING             232
         2 ARCH      74705                    CLOSING             230
         2 DGRD      74693                    ALLOCATED             0
         2 DGRD      74661                    ALLOCATED             0
         2 RFS       76404                    IDLE                  0
         2 RFS       115608                   RECEIVING           234
         2 RFS       76410                    IDLE                  0

17 rows selected.

Check Dataguard status

— on standby only

SQL> 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           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.

Convert RAC Physical Standby To Snapshot Standby

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;

NAME                 OPEN_MODE            DATABASE_ROLE
-------------------- -------------------- ----------------
TESTDB               READ WRITE           SNAPSHOT STANDBY

Revert back snapshot standby to physical standby

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

$ srvctl stop  instance  -d TESTDB -i TESTDB2 -o immediate

SQL>  alter database convert to physical standby;

Database altered.

SQL>  select name, open_mode, database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
TESTDB    MOUNTED              PHYSICAL STANDBY

SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.

$ srvctl start  database -d TESTDB -o mount

SQL> alter database recover managed standby database disconnect;

Database altered.

No Stats, Stale Stats and Locked Stats in Oracle Objects

N0 Stats

SQL> SELECT OWNER, TABLE_NAME from dba_tables where last_analyzed is null;

SQL> SELECT OWNER, TABLE_NAME from dba_indexes  where last_analyzed is null;

SQL>  select count(*) from dba_ind_statistics where LAST_ANALYZED is null;

  COUNT(*)
----------
       795

SQL>  select count(*) from dba_tab_statistics where LAST_ANALYZED is null;

  COUNT(*)
----------
       406

Stale Stats

SQL> SELECT OWNER, TABLE_NAME, STALE_STATS FROM DBA_TAB_STATISTICS WHERE STALE_STATS='YES' ;

SQL>  SELECT OWNER, TABLE_NAME, STALE_STATS FROM DBA_IND_STATISTICS WHERE STALE_STATS='YES';

Locked Stats

 SQL> SELECT OWNER, TABLE_NAME, STATTYPE_LOCKED FROM DBA_TAB_STATISTICS
WHERE STATTYPE_LOCKED IS NOT NULL ;

SQL> SELECT OWNER, TABLE_NAME, STATTYPE_LOCKED FROM DBA_IND_STATISTICS WHERE STATTYPE_LOCKED IS NOT NULL;

Collect stats

SQL> exec dbms_stats.gather_fixed_objects_stats;

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_schema_stats ('SYSTEM');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_schema_stats ('SYS');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_dictionary_stats;

PL/SQL procedure successfully completed.

Oracle ASM Thin Provisioned Attribute and Reclaim Storage Space

ASMFD Installation & Configuration

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 installed
AFD-9203: AFD device driver installed status: 'true'

$ afddriverstate loaded
AFD-9205: AFD device driver loaded status: 'true'

$ afddriverstate supported
AFD-9200: Supported

$ 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

$ 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.

SQL>select INST_ID, OPERATION, STATE, POWER, SOFAR, EST_WORK, EST_RATE, EST_MINUTES from gv$asm_operation;

INST_ID OPERA STAT POWER  SOFAR   EST_WORK   EST_RATE EST_MINUTES
------- ----- ---- ----- ------ ---------- ---------- -----------
      2 REBAL WAIT     1
      2 REBAL WAIT     1
      2 REBAL WAIT     1
      1 REBAL RUN      1   1951    8634894       2475        3488
      1 REBAL DONE     1   1017       1017          0           0
      1 REBAL DONE     1      0          0          0           0

6 rows selected.

RMAN-06004: Oracle error from recovery catalog database: RMAN-20005: target database name is ambiguous

When trying to duplicate a database, the following RMAN errors occur:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 01/29/2025 16:01:38
RMAN-05501: aborting duplication of target database
RMAN-06004: Oracle error from recovery catalog database: RMAN-20005: target database name is ambiguous

RESOLUTION

RMAN> SET DBID=123456789;
RMAN> DUPLICATE DATABASE....