Validate DR Readiness

Review best practices for disaster recovery readiness and verify your disaster recovery setup for planned and unplanned events.

About DR Readiness

Review best practices for disaster recovery (DR) readiness.

Use Active Data Guard to offload read-only workload to the standby database to provide continuous, application-level validation that the standby is ready for production. This provides a level of assurance in addition to continuous Oracle block-level validation performed by Oracle Data Guard apply processes.

Use a snapshot standby database to create an exact replica of a production database for development and testing purposes. Periodically place the standby in read/write mode (using Data Guard Snapshot Standby) to validate its readiness to support read-write production workloads. A snapshot standby may also be used for a final level of functional and performance testing of patches and upgrades since the DR system size is similar to the production system. A Snapshot Standby continues to receive redo from the primary database where it is archived for later use, which provides data protection at all times. However, Recovery time (RTO) will be extended by the amount of time required to convert the Snapshot Standby back to the standby database if a failover is required while testing is in progress. Additional storage is required for the fast recovery area when a standby is in snapshot mode (to hold archived redo received from the primary production database for later use and current redo and flashback logs generated by the snapshot standby).

The following are some of the benefits of using a snapshot standby database:

It provides an exact replica of a production database for development and testing while maintaining data protection at all times. You can use the Oracle Real Application Testing option to capture the primary database workload and then replay it for test purposes on the snapshot standby.
It is easily refreshed to contain current production data by converting to a physical standby and resynchronizing.

Convert the Standby Database to a Snapshot Standby

When you want an exact replica of a production database, you can use Oracle Data Guard Broker to convert the physical standby database to a snapshot standby database and validate the failover.

A snapshot standby is a fully updatable standby database that is created from a physical standby database. On snapshot standby databases, the redo data is received, but not applied until the snapshot standby database is converted back to a physical standby database.

The Oracle Data Guard command-line interface (DGMGRL) enables you to manage a Oracle Data Guard broker configuration and its various members directly from the command-line interface.

Log into a DGMGRL session on the standby database instance with the sys user name and password.
```
DGMRGL> connect
Username: sys
Password:
```
The output will look similar to the following:
```
Connected to "ORCLCDB_iad1s3"
Connected as SYSDBA.
```

Convert the physical standby database to a snapshot standby database.

DGMGRL> convert database standby_database_unique_name (standbyDB) to snapshot standby;

When the conversion is complete, the output will have a line similar to the following:

Converting database "standby_database_unique_name" to a Snapshot Standby database, please wait...
Database "standby_database_unique_name" converted successfully

View the configuration to verify the conversion.

DGMGRL> show configuration;

The output will look similar to the following:

Configuration - onpremdr
  Protection Mode: MaxPerformance
  Members:
  orclcdb_iad1s3 - Primary database
    orclcdb        - Physical standby database 
Fast-Start Failover:  Disabled
Configuration Status:
SUCCESS   (status updated 26 seconds ago)

Convert the snapshot standby back into a physical standby database.

DGMGRL> convert database standby_database_unique_name to physical standby;

The output will look similar to the following:

Converting database "orclcdb" to a Physical Standby database, please wait...
Operation requires a connection to database "orclcdb_iad1s3"
Connecting ...
Connected to "ORCLCDB_IAD1S3"
Connected as SYSDBA.
Operation requires shut down of instance "ORCLCDB" on database "orclcdb"
Shutting down instance "ORCLCDB"...
Connected to "ORCLCDB"
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires start up of instance "ORCLCDB" on database "orclcdb"
Starting instance "ORCLCDB"...
Connected to an idle instance.
ORACLE instance started.
Connected to "ORCLCDB"
Database mounted.
Connected to "ORCLCDB"
Continuing to convert database "orclcdb" ...
Database "orclcdb" converted successfully

View the configuration to verify the conversion.

DGMGRL> show configuration;

The output will look similar to the following:

Configuration - onpremdr

  Protection Mode: MaxPerformance
  Members:
  orclcdb_iad1s3 - Primary database
    orclcdb        - Physical standby database 
      Warning: ORA-16854: apply lag could not be determined

Fast-Start Failover:  Disabled

Configuration Status:
WARNING   (status updated 11 seconds ago)

Set the Oracle Data Guard Parameters

Before executing a switchover, set the Oracle Data Guard parameters on the primary and standby database instances.

On the on-premises primary database, check the system change number (SCN).
```
select standby_became_primary_scn from v$database;
```
The output will look similar to the following:
```
STANDBY_BECAME_PRIMARY_SCN
--------------------------
                              0
```
Verify that the flashback is not enabled on the primary database.
```
select flashback_on from v$database;
```
The output will look similar to the following:
```
FLASHBACK_ON
------------------
NO
```
On the on-premises instance, create a recovery directory with the same primary database name as the Oracle User.
In this example, ORCLCDB is the name of the on-premises database.
```
[oracle@bastion dbhome_1]$ cd /opt/oracle/
[oracle@bastion oracle]$ mkdir recovery
[oracle@bastion oracle]$ cd recovery/
[oracle@bastion recovery]$ mkdir ORCLCDB
[oracle@bastion oracle]$ cd ORCLCDB /
```
Confirm the full path.
```
$ pwd
```
```
/opt/oracle/recovery/ORCLCDB
```
Set the size of the DB_RECOVERY_FILE_DEST_SIZE parameter to meet your database size.
The following example uses 20G as the sample data size. Set the data size based on your database.
```
SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=20G;
```
```
System altered
```
Define the database recovery file destination to the directory you created earlier.
```
SQL> alter system set db_recovery_file_dest = '/opt/oracle/recovery/on_premises_SID';
```
```
System altered
```

Enable flashback on the primary database.

SQL> alter database flashback on;

Database altered

Confirm that database flashback is enabled (on).
```
SQL> select flashback_on from v$database;
```
The output will look similar to the following:
```
FLASHBACK_ON
------------------
YES
```
Determine whether flashback is enabled on the OCI standby database in the cloud.
```
SQL> select flashback_on from v$database;
```
The output will look similar to the following:
```
FLASHBACK_ON
------------------
NO
```
Query the open mode status for the standby database in the cloud.
```
SQL> select open_mode from v$database;
```
The output will look similar to the following:
```
OPEN_MODE
-------------------- 
MOUNTED
```

Set the flashback on the database instance in the cloud.

SQL> alter database recover managed standby database cancel;

Database altered

Query the open mode status for the standby database in the cloud.
```
SQL> select open_mode from v$database;
```
The output will look similar to the following:
```
OPEN_MODE
-------------------- 
MOUNTED
```

Enable flashback for the standby database.

SQL> alter database flashback on;

Database altered

Disconnect the standby from the session.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered

Confirm that database flashback is enabled (on).

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

Display the retention parameters.

SQL> show parameter_retention;

The output will look similar to the following:

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target	     integer	 1440
undo_retention			      integer	 900

Switch the Primary Database From On-Premises to OCI

When you have a planned activity, such as maintenance, you can make the database in Oracle Cloud Infrastructure (OCI) the primary database with zero data loss. A switchover is a planned event that's initiated on the on-premises database and completed on the database instance in the cloud.

Log into a DGMGRL session on the on-premises database with a sys user name and password.
```
DGMRGL> connect
```

On the on-premises database, validate that the standby database is ready for switchover and failover.

In this example, the primary database is orclcdb and the standby database is orclcdb_iadls3.

DGMGRL> validate database orclcdb_iad1s3 (StandbyDatabaseOCIInstance)

The output will look similar to the following:

Database Role:     Physical standby database
  Primary Database:  orclcdb

  Ready for Switchover:  Yes
  Ready for Failover:    Yes (Primary Running)

  Managed by Clusterware:
    orclcdb       :  NO             
    orclcdb_iad1s3:  NO             
    Validating static connect identifier for the primary database orclcdb...
    The static connect identifier allows for a connection to database "orclcdb".

  Log Files Cleared:
    orclcdb Standby Redo Log Files:         Cleared
    orclcdb_iad1s3 Online Redo Log Files:   Not Cleared
    orclcdb_iad1s3 Standby Redo Log Files:  Available

  Current Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status       
              (orclcdb)               (orclcdb_iad1s3)                     
    1         3                       2                       Insufficient SRLs

  Future Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status       
              (orclcdb_iad1s3)        (orclcdb)                            
    1         3                       0                       Insufficient SRLs
    Warning: standby redo logs not configured for thread 1 on orclcdb

On the on-premises (primary) database, execute the command to switchover to the standby database.

DGMGRL> switchover to orclcdb_iad1s3 (StandbyDatabaseOCIInstance)

The output will look similar to the following:

Performing switchover NOW, please wait...
New primary database "orclcdb_iad1s3" is opening...
Operation requires start up of instance "ORCLCDB" on database "orclcdb"
Starting instance "ORCLCDB"...
Connected to an idle instance.
ORACLE instance started.
Connected to "ORCLCDB"
Database mounted.
Database opened.
Connected to "ORCLCDB"
Switchover succeeded, new primary is "orclcdb_iad1s3"

On the on-premises database, display the configuration status.

DGMGRL> show configuration

The output will look similar to the following:

Configuration - onpremdr

  Protection Mode: MaxPerformance
  Members:
  orclcdb_iad1s3 - Primary database
  orclcdb        - Physical standby database 

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 70 seconds ago)

On the database in the cloud, determine the open mode status.
```
SQL> select open_mode from v$database;
```
The output will look similar to the following:
```
OPEN_MODE
-------------------- 
READ WRITE
```
On the on-premises database, determine the open mode status.
```
SQL> select open_mode from v$database;
```
The output will look similar to the following:
```
OPEN_MODE
-------------------- 
READ ONLY WITH APPLY
```

The standby database in the cloud is now the primary database.

Switch the Primary Database Back From OCI to On-Premises

After making the database instance in Oracle Cloud Infrastructure (OCI) the primary database, use the Oracle Data Guard command-line interface (DGMGRL) on the OCI database instance to switch back and make your on-premises database your primary database again.

Validate the database roles for the primary database in the cloud.

In this example, ORCLCDB is the name of the on-premises database and orclcdb_iad1s3 is the name of the database instance in the cloud.

DGMGRL> validate database orclcdb;

The output might look similar to the following:

Database Role: Physical standby database
  Primary Database: orclcdb_iad1s3

  Ready for Switchover:  Yes
  Ready for Failover:    Yes (Primary Running)

  Managed by Clusterware:
    orclcdb_iad1s3:  NO             
    orclcdb       :  NO             
    Validating static connect identifier for the primary database orclcdb_iad1s3...
Unable to connect to database using (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.0.2.1)(PORT=1539)))(CONNECT_DATA=(SERVICE_NAME=ORCLCDB_iad1s3_DGMGRL.sub08120021431.onpremocidrvcn.example.com)(INSTANCE_NAME=ORCLCDB)(SERVER=DEDICATED)(STATIC_SERVICE=TRUE)))
ORA-12541: TNS:no listener

Failed.
    Warning: Ensure primary database's StaticConnectIdentifier property
    is configured properly so that the primary database can be restarted
    by DGMGRL after switchover

  Log Files Cleared:
    orclcdb_iad1s3 Standby Redo Log Files:  Cleared
    orclcdb Online Redo Log Files:          Not Cleared
    orclcdb Standby Redo Log Files:         Available

  Current Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status       
              (orclcdb_iad1s3)        (orclcdb)                            
    1         3                       2                       Insufficient SRLs

  Future Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status       
              (orclcdb)               (orclcdb_iad1s3)                     
    1         3                       2                       Insufficient SRLs

If you get the static connect identifier warning message, review the configuration parameters, SID service name, host IP address, or port address.

Log into a DGMGRL session with a sys user name and password.
```
DGMRGL> connect
```

Review the static connect identifier for the on-premises database.

DGMGRL> show database orclcdb StaticConnectIdentifier

Review the output to verify that the StaticConnectIdentifier parameters are accurate. The following is an example:

DGMGRL> show database orclcdb StaticConnectIdentifier
  StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.0.2.1)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=ORCLCDB)(INSTANCE_NAME=ORCLCDB)(SERVER=DEDICATED)(STATIC_SERVICE=TRUE)))'

Review the static connect identifier for the database instance in the cloud.

DGMGRL> show database orclcdb_iad1s3 StaticConnectIdentifier

  StaticConnectIdentifier = 
'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.30.3)(PORT=1539)
))(CONNECT_DATA=(SERVICE_NAME=ORCLCDB_iad1s3_DGMGRL.sub08120021431.onpremocidrvcn.examplevcn.com) 
INSTANCE_NAME=ORCLCDB)(SERVER=DEDICATED)))'

Review the output to verify that the StaticConnectIdentifier parameters, such as the port number and service name, are correct.

Correct any StaticConnectIdentifier parameters, such as the port number and service name.
When editing the properties, you must use the Oracle Data Guard command-line interface (DGMGRL) without any line breaks. This example corrects the port number and service name for the database instance in the cloud (orclcdb_iad1s3).
```
DGMGRL> edit database 'orclcdb_iad1s3' set property StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.0.2.1)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=ORCLCDB)(INSTANCE_NAME=ORCLCDB)(SERVER=DEDICATED)))';
```
```
Property "staticconnectidentifier" updated
```

Validate the database instance in the cloud.

DGMGRL> validate database orclcdb_iad1s3;

The output will look similar to the following:

Database Role:    Primary database
  Ready for Switchover:  Yes

  Managed by Clusterware:
    orclcdb_iad1s3:  NO             
    Validating static connect identifier for the primary database orclcdb_iad1s3...
    The static connect identifier allows for a connection to database "orclcdb_iad1s3".

Validate the on-premises database.

DGMGRL> validate database orclcdb;

The output will look similar to the following:

Database Role:     Physical standby database
  Primary Database:  orclcdb_iad1s3
  Ready for Switchover:  Yes
  Ready for Failover:    Yes (Primary Running)

  Managed by Clusterware:
    orclcdb_iad1s3:  NO             
    orclcdb       :  NO             
    Validating static connect identifier for the primary database orclcdb_iad1s3...
    The static connect identifier allows for a connection to database "orclcdb_iad1s3".

  Standby Apply-Related Information:
    Apply State:      Running
    Apply Lag:        2 seconds (computed 7 seconds ago)
    Apply Delay:      0 minutes

  Log Files Cleared:
    orclcdb_iad1s3 Standby Redo Log Files:  Cleared
    orclcdb Online Redo Log Files:          Not Cleared
    orclcdb Standby Redo Log Files:         Available

  Current Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status       
              (orclcdb_iad1s3)        (orclcdb)                            
    1         3                       2                       Insufficient SRLs

  Future Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status       
              (orclcdb)               (orclcdb_iad1s3)                     
    1         3                       2                       Insufficient SRLs

Display and review the configuration.

DGMGRL> show configuration;

The output will look similar to the following:

Configuration - onpremdr

  Protection Mode: MaxPerformance
  Members:
  orclcdb_iad1s3 - Primary database
    orclcdb        - Physical standby database 

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 27 seconds ago)

Switchover from the database instance in OCI to the on-premises database.

DGMGRL> switchover to orclcdb;

The output will look similar to the following:

Performing switchover NOW, please wait...
Operation requires a connection to database "orclcdb"
Connecting ...
Connected to "ORCLCDB"
Connected as SYSDBA.
New primary database "orclcdb" is opening...
Operation requires start up of instance "ORCLCDB" on database "orclcdb_iad1s3"
Starting instance "ORCLCDB"...
Connected to an idle instance.
ORACLE instance started.
Connected to "ORCLCDB_iad1s3"
Database mounted.
Database opened.
Connected to "ORCLCDB_iad1s3"
Switchover succeeded, new primary is "orclcdb"

After the database is mounted, you might receive the following warning:

ORA-16000: database or pluggable database open for read-only access  

Please complete the following steps to finish switchover:
       start up instance "ORCLCDB" of database "orclcdb"

You can ignore the warning and proceed to the next step.

On the standby, which is now the primary, connect and review the configuration.

DGMGRL> show configuration;

The output will look similar to the following:

Configuration - onpremdr

  Protection Mode: MaxPerformance
  Members:
  orclcdb        - Primary database
    orclcdb_iad1s3 - Physical standby database 

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 86 seconds ago)

On the primary database instance, ORCLCDB, verify that the mode is read write.

SQL> select database_role, open_mode from v$database;

DATABASE_ROLE	 OPEN_MODE
---------------- --------------------
PRIMARY 	   READ WRITE

(Optional) You can use the verbose command to display a complete stack dump, including the role, state, instance, properties, and the location of the Alert log and the Data Guard Broker log.
```
DGMGRL> show database verbose orclcdb
```

You can switch the primary database back and forth between the on-premises database and the OCI database instance.