Use Oracle Data Guard with the Database CLI

This article explains how to use the database CLI to set up Data Guard with Fast-Start Failover (FSFO) in Oracle Cloud Infrastructure. The topics in this article explain how to prepare the primary and standby databases, and then configure Data Guard to transmit redo data from the primary database and apply it to the standby database.

Oracle recommends that you use the Console instead of the database CLI to set up and work with Data Guard in Oracle Cloud Infrastructure.

Note

This article assumes that you are familiar with Data Guard and FSFO. To learn more about them, see Use Oracle Data Guard on a DB System.

Prerequisites

To perform the procedures in this topic, you'll need the following information for the primary and standby databases.

  • db_name (or oracle_sid)
  • db_unique_name
  • oracle home directory (or database home)

Finding the Database Information

After you've launched the primary and standby DB systems and created databases as described later in this topic, you can use the CLI on those systems to find the needed database information.

  1. SSH to the DB System.
    ssh -i <private_key_path> opc@<db_system_ip_address>
  2. Log in as opc and then sudo to the root user. Use sudo su - with a hyphen to invoke the root user's profile, which will set the PATH to the dbcli directory (/opt/oracle/dcs/bin).
    sudo su -
  3. To find the db_name (or oracle_sid) and db_uniqueName, run the dbcli list-databases -j command.
    dbcli list-databases -j
    Output:
    [ {
      "id" : "80ad855a-5145-4f8f-a08f-406c5e4684ff",
      "name" : "dbtst",
      "dbName" : "dbtst",
      "databaseUniqueName" : "dbtst_phx1cs",
      "dbVersion" : "12.1.0.2",
      "dbHomeId" : "2efe7af7-0b70-4e9b-ba8b-71f11c6fe287",
      "instanceOnly" : false,
       .
       .
       .  
  4. To find the oracle home directory (or database home), run the dbcli list-dbhomes command. If there are multiple database homes on the DB system, use the one that matches the "dbHomeId" in the dbcli list-databases -j command output shown above.
    dbcli list-dbhomes
    Output:
    ID                                       Name                 DB Version                               Home Location                                 Status
    ---------------------------------------- -------------------- ---------------------------------------- --------------------------------------------- ----------
    2efe7af7-0b70-4e9b-ba8b-71f11c6fe287     OraDB12102_home1     12.1.0.2.160719 (23739960, 23144544)     /u01/app/oracle/product/12.1.0.2/dbhome_1     Configured
    33ae99fe-5413-4392-88da-997f3cd24c0f     OraDB11204_home1     11.2.0.4.160719 (23054319, 23054359)     /u01/app/oracle/product/11.2.0.4/dbhome_1     Configured

Create a Primary DB System

If you don't already have a primary DB system, create one as described in Overview of Creating a DB System. The DB system will include an initial database. You can create additional databases by using the Database Commands available on the DB system.

Create a Standby DB System

Note

The standby database must have the same db_name as the primary database, but it must have a different db_unique_name. If you use the same database name for the standby and primary, you will have to delete the database from the standby DB system by using the dbcli delete-database command before you can run the dbcli create-database command described below. Deleting and creating the database will take several minutes to complete. The dbcli commands must be run as the root user.

  1. Create a standby DB system as described in Overview of Creating a DB System and wait for the DB system to finish provisioning and become available.

    You can create the standby DB system in a different availability domain from the primary DB system for availability and disaster recovery purposes (this is strongly recommended). You can create the standby DB system in the primary DB system's cloud network so that both systems are in a single, routable network.

  2. SSH to the DB System.
    ssh -i <private_key_path> opc@<db_system_ip_address>
  3. Log in as opc and then sudo to the root user. Use sudo su - with a hyphen to invoke the root user's profile, which will set the PATH to the dbcli directory (/opt/oracle/dcs/bin).
    sudo su -
  4. The DB system will include an initial database, but you'll need to create a standby database by using the dbcli create-database command with the --instanceonly parameter. This parameter creates only the database storage structure and starts the database in nomount mode (no other database files are created).

    When using --instanceonly, both the --dbname and --adminpassword parameters are required and they should match the dbname and admin password of the primary database to avoid confusion.

    The following sample command prompts for the admin password and then creates a storage structure for a database named dbname.

    dbcli create-database --dbname <same as primary dbname>;--databaseUniqueName <different from primary uniquename>;--instanceonly --adminpassword

    If you are using pluggable databases, also specify the --cdb parameter.

    For complete command syntax, see Database Commands.

  5. Wait a few minutes for the dbcli create-database command to create the standby database.

    You can use the dbcli list-jobs command to verify that the creation job ran successfully, and then the dbcli list-databases command verify that the database is configured.

Prepare the Primary DB System

To prepare the primary DB system, you'll need to configure static listeners, update tnsnames.ora, and configure some database settings and parameters.

Configuring the Static Listeners

Create static listeners to be used by RMAN and Data Guard Broker.

  1. SSH to the primary DB system, log in as the opc or root user, and sudo to the grid OS user.
    sudo su - grid
  2. Edit /u01/app/<version>/grid/network/admin/listener.ora and add the following content to it. The first static listener shown here is optional. The second DGMGRL static listener is optional for version 12.1 or later databases, but required for version 11.2 databases.
    SID_LIST_LISTENER=
      (SID_LIST=
        (SID_DESC=
        (SDU=65535)
        (GLOBAL_DBNAME = <primary_db_unique_name>.<primary_db_domain>)
        (SID_NAME = <primary_oracle_sid>)
        (ORACLE_HOME=<oracle_home_directory>)
        (ENVS="TNS_ADMIN=<oracle_home_directory>/network/admin")
        )
        (SID_DESC=
        (SDU=65535)
    	(GLOBAL_DBNAME = <primary_db_unique_name>_DGMGRL.<primary_db_domain>)
        (SID_NAME = <primary_oracle_sid>)
        (ORACLE_HOME=<oracle_home_directory>)
        (ENVS="TNS_ADMIN=<oracle_home_directory>/network/admin")
        )
      )
  3. Save your changes and then restart the listener.
    srvctl stop listener
    srvctl start listener

Adding Net Service Names to tnsnames.ora

As the oracle user, edit $ORACLE_HOME/network/admin/tnsnames.ora and add the standby database net service name to it.

<standby db_unique_name> =
  (DESCRIPTION =
    (SDU=65535)		
    (ADDRESS = (PROTOCOL = TCP)(HOST = <standby_server>.<domain>) (PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = <standby db_unique_name>.<standby db_domain>)
    )
  )

The sample above assumes that name resolution is working and that the <standby_server>.<domain> is resolvable at the primary database. You can also use the private IP address of the standby server if the IP addresses are routable within a single cloud network (VCN).

Configuring Primary Database Parameters

Tip:

If the primary and standby hosts have different directory structures, you might need to set additional parameters that are not discussed here, such as the log_file_name_convert parameter. See the RMAN documentation for more information about how to create standbys for hosts with different directory structures.

  1. As the oracle user, enable automatic standby file management.
    alter system set standby_file_management=AUTO;
  2. Identify the Broker configuration file names and locations. The commands used for this depend on the type of database storage. If you're not sure of the database storage type, use the Database Commands on the DB system.

    For ACFS database storage, use the following commands to set the Broker configuration files.

    alter system set dg_broker_config_file1='/u02/app/oracle/oradata/<Primary db_unique_name>/dbs/dr1<Primary db_unique_name>.dat';
    alter system set dg_broker_config_file2='/u02/app/oracle/oradata/<Primary db_unique_name>/dbs/dr2<Primary db_unique_name>.dat'; 

    For ASM database storage, use the following commands to set the Broker configuration files.

    alter system set dg_broker_config_file1='+DATA/<Primary db_unique_name>/dr1<db_unique_name>.dat';
    alter system set dg_broker_config_file2='+DATA/<Primary db_unique_name>/dr2<db_unique_name>.dat';
  3. Enable Broker DMON process for the database.
    alter system set dg_broker_start=true;
  4. Force database logging for all database transactions.
    alter database force logging ;
  5. Add Standby Redo Logs (SRLs), based on the Online Redo Logs (ORLs). On a newly launched DB system, there will be three ORLs of size 1073741824, so create four SRLs of the same size.

    You can use the query below to determine the number and size (in bytes) of the ORLs.

    select group#, bytes from v$log;
    Output:
        GROUP# BYTES
    ---------- ----------
             1 1073741824
             2 1073741824
             3 1073741824

    All of the ORLs must be the same size.

    The SRLs must be the same size as the ORLs, but there must be at least one more SRL than the ORLs. In the example above, there are three ORLs, so four SRLs are required. So specify the current redo logs plus one, and use the same size as the redo logs.

    alter database add standby logfile thread 1 size <size>;

    There should be only one member in the SRL group (by default, a DB system is created with only one member per SRL group). To ensure this, you can name the file with the following syntax.

    alter database add standby logfile thread 1 group 4 (<logfile name with full path>) size 1073741824, group 5(<logfile name with full path>) size 1073741824 ...

    For ASM/OMF configurations, the above command uses the diskgroup instead of <logfile name with full path>.

    alter database add standby logfile thread 1 group 4 (+RECO) size 1073741824, group 5(+RECO) size 1073741824 ...

    Tip:

    ORLs and SRLs should be sized so that log switches do not occur more frequently than every 10 minutes. This requires knowledge of the application and may need to be adjusted after deployment. For more information, see Use Standby Redo Logs and Configure Size Appropriately.

  6. Verify that you created the correct number of SRLs.
    select group#, bytes from v$standby_log;
  7. Make sure the database is in ARCHIVELOG mode.
    archive log list
  8. Enable database FLASHBACK. The minimum recommended value for db_flashback_retention_target is 120 minutes.
    alter database flashback on ;
    alter system set db_flashback_retention_target=120;  
  9. Perform a single switch redo log to activate archiving if database is newly created. (At least one log must be archived prior to running the RMAN duplicate.)
    alter system switch logfile;

Prepare the Standby Database

Before you prepare the standby database, make sure the database home on the standby is the same version as on the primary. (If the primary and standby databases are both newly created with the same database version, the database homes will be the same.) If it is not, create a database home that is the same version. You can use the Dbhome Commands to verify the versions and create a new database home as needed.

To prepare the standby DB system, you'll need to configure static listeners, update tnsnames.ora, configure TDE Wallet, create a temporary password file, verify connectivity, run RMAN DUPLICATE, enable FLASHBACK, and then create the database service.

Configuring the Static Listeners

Create static listeners to be used by RMAN and Data Guard Broker.

  1. SSH to the standby DB system, log in as the opc or root user, and sudo to the grid OS user.
    sudo su - grid
  2. Append the following content to /u01/app/<db_version>/grid/network/admin/listener.ora.

    The first static listener shown below is required for RMAN DUPLICATE. The second DGMGRL static listener is optional for database versions 12.2.0.1 and 12.1.0.2, but required for database version 11.2.0.4.

    SID_LIST_LISTENER=
      (SID_LIST=
        (SID_DESC=
        (SDU=65535)
        (GLOBAL_DBNAME = <standby db_unique_name>.<standby db_domain>)
        (SID_NAME = <standby oracle_sid>)
        (ORACLE_HOME=<oracle home directory>)
        (ENVS="TNS_ADMIN=<oracle home directory>/network/admin")
        )
        (SID_DESC=
        (SDU=65535)
        (GLOBAL_DBNAME = <standby db_unique_name>_DGMGRL.<standby db_domain>)
        (SID_NAME = <standby oracle_sid>)
        (ORACLE_HOME=<oracle home directory>) 
        (ENVS="TNS_ADMIN=<oracle home directory>/network/admin") 
        )
      )
  3. Restart the listener.
    srvctl stop listener
    srvctl start listener
  4. Verify that the static listeners are available. The sample output below is for database version 12.1.0.2. Note that the ...status UNKNOWN messages are expected at this point.
    lsnrctl status
    Output:
    LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 29-SEP-2016 21:09:25
    
    Copyright (c) 1991, 2014, Oracle.  All rights reserved.
    
    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
    STATUS of the LISTENER
    ------------------------
    Alias                     LISTENER
    Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
    Start Date                29-SEP-2016 21:09:19
    Uptime                    0 days 0 hr. 0 min. 5 sec
    Trace Level               off
    Security                  ON: Local OS Authentication
    SNMP                      OFF
    Listener Parameter File   /u01/app/12.1.0.2/grid/network/admin/listener.ora
    Listener Log File         /u01/app/grid/diag/tnslsnr/dg2/listener/alert/log.xml
    Listening Endpoints Summary...
      (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.1.24)(PORT=1521)))
    Services Summary...
    Service "dg2_phx2hx.oratst.org" has 1 instance(s).
      Instance "dg2", status UNKNOWN, has 1 handler(s) for this service...
    Service "dg2_phx2hx_DGMGRL.oratst.org" has 1 instance(s).
      Instance "dg2", status UNKNOWN, has 1 handler(s) for this service...
    The command completed successfully

Adding Net Service Names to tnsnames.ora

As the oracle user, add the standby database net service name to $ORACLE_HOME/network/admin/tnsnames.ora. $ORACLE_HOME is the database home where the standby database is running.

<Primary db_unique_name> =
  (DESCRIPTION =
    (SDU=65535)
    (ADDRESS = (PROTOCOL = TCP)(HOST = <primary_server>.<domain>) (PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = <primary db_unique_name).<primary db_domain>)
    )
  )
 
<Standby db_unique_name> =
  (DESCRIPTION =
    (SDU=65535)
    (ADDRESS = (PROTOCOL = TCP)(HOST = <standby_server>.<domain>) (PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = <standby db_unique_name>.<db_domain>)
    )
  )

Copying the TDE Wallets to the Standby System

Copy the TDE wallet files from the primary DB system to standby DB system using SCP. The following sample command assumes the SCP command is being run by the oracle OS user and that the private key for oracle has been created and exists on the host where SCP is being run.
scp -i <private key> primary_server:/opt/oracle/dcs/commonstore/wallets/tde/<primary db_unique_name>/* standby_server:/opt/oracle/dcs/commonstore/wallets/tde/<standby db_unique_name>

Setting Up the Standby System Configuration

As the oracle user, create the following directory for database version 11.2.0.4. This step is optional for version 12.2.0.1 and version 12.1.0.2.

mkdir  -pv /u03/app/oracle/redo/<standby db_unique_name uppercase>/controlfile

Creating the Audit File Destination

As the oracle user, create the following directory to use as the audit file destination.

mkdir -p /u01/app/oracle/admin/<db_name>/adump

Otherwise, the RMAN duplicate command used later will fail.

Creating a Temporary Password File

As the oracle user, create a temporary password file.

orapwd file=$ORACLE_HOME/dbs/orapw<standby oracle_sid>    password=<admin password for primary> entries=5

The password must be the same as the admin password of the primary database. Otherwise, the RMAN duplicate step below will fail with: RMAN-05614: Passwords for target and auxiliary connections must be the same when using active duplicate.

Verifying the Standby Database is Available

  1. As the oracle user, set the environment variables.
    . oraenv
  2. Replace $ORACLE_HOME/dbs/init<standby sid_name>.ora with the following content:
    db_name=<Primary db_name>
    db_unique_name=<standby db_unique_name>
    db_domain=<standby db_domain>
  3. Remove the spfile from the standby.
    /u02/app/oracle/oradata/<standby db_unique_name>/dbs/spfile$ORACLE_SID.ora 

    The database needs to be started in nomount mode with no spfile specified, but the original init file contains an spfile parameter which will prevent the RMAN duplicate step from working.

  4. Set the ORACLE_UNQNAME environment variable to point to your DB_UNIQUE_NAME.
    export ORACLE_UNQNAME =db_unique_name
    Note

    If you do not perform this step, the wallet will not be opened, and running the RMAN DUPLICATE command in the subsequent step will fail.
  5. The dbcli create-database --instanceonly command used earlier opens the standby database as a primary in read/write mode, so the database needs to be brought down before proceeding to the nomount step below.
    sqlplus / as sysdba
    
    shutdown immediate
  6. Start the database in nomount mode.
    startup nomount

Verifying the Database Connections

Verify the connection between the primary and standby databases.

  1. Make sure that the listener port 1521 is open in the security list(s) used for the primary and standby DB systems. For more information, see Update the Security List for the DB System.
  2. From the primary database, connect to standby database.
    sqlplus sys/<password>@<standby net service name> as sysdba
  3. From standby database, connect to primary database.
    sqlplus sys/<password>@<primary net service name> as sysdba

Running the RMAN DUPLICATE Command

Run the RMAN DUPLICATE command on the standby DB system, as the oracle user.

If the primary database is large, you can allocate additional channels to improve performance. For a newly installed database, one channel typically runs the database duplication in a couple of minutes.

Make sure that there are no errors generated by the RMAN DUPLICATE command. If errors occur, restart the database using the init.ora file (not spfile) in case it is generated under $ORACLE_HOME/dbs as part of RMAN DUPLICATE.

In the following examples, use lowercase for the <Standby db_unique_name> unless otherwise specified.

For ACFS storage layout, run the following commands.

rman target sys/<password>@<primary alias>  auxiliary sys/<password>@<standby alias> log=rman.out
                  
run { allocate channel prim1 type disk;
    allocate auxiliary channel sby type disk;
    duplicate target database for standby from active database
    dorecover
    spfile
    parameter_value_convert '/<Primary db_unique_name>/','/<Standby db_unique_name>/','/<Primary db_unique_name uppercase>/','/<Standby db_unique_name uppercase >/'
    set db_unique_name='<Standby db_unique_name>'
    set db_create_file_dest='/u02/app/oracle/oradata/<Standby db_unique_name>'
    set dg_broker_config_file1='/u02/app/oracle/oradata/<Standby db_unique_name>/dbs/dr1<Standby db_unique_name>.dat'
    set dg_broker_config_file2='/u02/app/oracle/oradata/<Standby db_unique_name>/dbs/dr2<Standby db_unique_name>.dat'
    set dispatchers ='(PROTOCOL=TCP) (SERVICE=<Standby db_unique_name>XDB)'
    set instance_name='<Standby db_unique_name>'
    ;
}

For ASM storage layout, run the following commands.

rman target sys/<password>@<primary alias>  auxiliary sys/<password>@<standby alias> log=rman.out
  
run { 
    allocate channel prim1 type disk;
    allocate auxiliary channel sby type disk;
    duplicate target database for standby from active database
    dorecover
    spfile
    parameter_value_convert '/<Primary db_unique_name>/','/<Standby db_unique_name>/','/<Primary db_unique_name uppercase>/','/<Standby db_unique_name uppercase>/'
    set db_unique_name='<Standby db_unique_name>'           
    set dg_broker_config_file1='+DATA/<Standby db_unique_name>/dr1<Standby db_unique_name>.dat'
    set dg_broker_config_file2='+DATA/<Standby db_unique_name>/dr2<Standby db_unique_name>.dat'
    set dispatchers ='(PROTOCOL=TCP) (SERVICE=<Standby db_unique_name>XDB)'
    set instance_name='<Standby db_unique_name>'  
    ;
}

Enabling Database FLASHBACK

  1. As a Data Guard best practice, enable flashback and set db_flashback_retention_target to at least 120 minutes on both the primary and standby databases.
    alter database flashback on;
    alter system set db_flashback_retention_target=120;
  2. Verify that the standby database is created properly.
    select FORCE_LOGGING, FLASHBACK_ON, OPEN_MODE, DATABASE_ROLE,SWITCHOVER_STATUS, DATAGUARD_BROKER, PROTECTION_MODE from v$database ;

Creating a Database Service

Oracle recommends creating a database service for the standby database by using srvctl.

For ACFS storage layout.

  1. Create a shared directory and copy the spfile file to it.
    mkdir -pv /u02/app/oracle/oradata/<Standby db_unique_name>/dbs
    cp $ORACLE_HOME/dbs/spfile<standby oracle_sid>.ora /u02/app/oracle/oradata/<Standby db_unique_name>/dbs
  2. Stop and remove the existing database service.
    srvctl stop database 
        -d <standby db_unique_name>
    
    srvctl remove database 
        -d <standby db_unique_name>
  3. Create the database service.
    srvctl add database 
        -d <standby db_unique_name> 
        -n <standby db_name> 
        -o $ORACLE_HOME 
        -c SINGLE 
        -p '/u02/app/oracle/oradata/<standby db_unique_name>/dbs/spfile<standby db_name>.ora'
        -x <standby hostname> 
        -s "READ ONLY" 
        -r PHYSICAL_STANDBY 
        -i <db_name>
    
    srvctl setenv database 
        -d <standby db_unique_name> 
        -t "ORACLE_UNQNAME=<standby db_unique_name>"
    
    srvctl config database 
        -d <standby db_unique_name>
  4. Start the database service.
    srvctl start database 
        -d <standby db_unique_name>
  5. Clean up the files from $ORACLE_HOME/dbs.
    rm $ORACLE_HOME/dbs/spfile<standby oracle_sid>.ora
    rm $ORACLE_HOME/dbs/init<standby oracle_sid>.ora
  6. Create the $ORACLE_HOME/dbs/init<standby oracle_sid>.ora file to reference the new location of the spfile file.
    SPFILE='/u02/app/oracle/oradata/<standby db_unique_name>/dbs/spfile<standby db_name>.ora'
  7. Stop the standby database and then start it by using srvctl.
    srvctl stop database 
        -d <standby db_unique_name>
    
    srvctl start database 
        -d <standby db_unique_name>

For ASM storage layout.

  1. Consider generating the spfile file under +DATA.
    create pfile='init<standby oracle_sid>.ora' from spfile ;
    create spfile='+DATA' from pfile='init<standby oracle_sid>.ora' ;
  2. Stop and remove the existing database service.
    srvctl stop database 
        -d <standby db_unique_name>
    
    srvctl remove database 
        -d <standby db_unique_name>
  3. Create the database service.
    srvctl add database 
        -d <standby db_unique_name> 
        -n <standby db_name> 
        -o $ORACLE_HOME 
        -c SINGLE  
        -p '+DATA/<standby db_unique_name>/PARAMETERFILE/spfile.xxx.xxxxxx'
        -x <standby hostname> 
        -s "READ ONLY" 
        -r PHYSICAL_STANDBY 
        -i <db_name>
    
    srvctl setenv database 
        -d <standby db_unique_name> 
        -t "ORACLE_UNQNAME=<standby db_unique_name>"
    
    srvctl config database 
        -d <standby db_unique_name>
  4. Start the database service.
    srvctl start database 
        -d <standby db_unique_name>
  5. Clean up the files from $ORACLE_HOME/dbs.
    rm $ORACLE_HOME/dbs/init<standby oracle_sid>.ora
    rm $ORACLE_HOME/dbs/spfile<standby oracle_sid>.ora
  6. Create $ORACLE_HOME/dbs/init<standby oracle_sid>.ora file to reference the new location of the spfile file.
    SPFILE='+DATA/<standby db_unique_name>/PARAMETERFILE/spfile.xxx.xxxxxx'
  7. Stop the database and start the standby database by using srvctl.
    srvctl start database -d <standby db_unique_name>

Configure Data Guard

Perform the following steps to complete the configuration of Data Guard and enable redo transport from the primary database and redo apply in the standby database.

  1. Run the dgmgrl command line utility from either the primary or standby DB system and connect to the primary database using sys credentials.
    connect sys/<sys password>@<primary tns alias>
  2. Create the Data Guard configuration and identify for the primary and standby databases in the dgmgrl command line utility.
    create configuration mystby as primary database is <primary db_unique_name> connect identifier is <primary tns alias>;
    add database  <standby db_unique_name> as connect identifier is <standby tns alias>  maintained as physical;
  3. Enable Data Guard configuration the dgmgrl command line utility.
    enable configuration;
  4. Verify that Data Guard setup was done properly. Run the following SQL in both the primary and standby databases in the SQL prompt.
    select FORCE_LOGGING, FLASHBACK_ON, OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, DATAGUARD_BROKER, PROTECTION_MODE from v$database;
  5. Verify that Data Guard processes are initiated in the standby database.
    select PROCESS,PID,DELAY_MINS from V$MANAGED_STANDBY;
  6. Verify parameter configuration on primary and standby.
    show parameter log_archive_dest_
    show parameter log_archive_config
    show parameter fal_server
    show parameter log_archive_format
  7. Verify that the Data Guard configuration is working in the dgmgrl command line utility. Specifically, make sure redo shipping and redo apply are working and that the standby is not unreasonably lagging behind the primary.
    show configuration verbose
    show database verbose <standby db_unique_name>
    show database verbose <primary db_unique_name>

    Any discrepancies, errors, or warnings should be resolved. You can also run a transaction on the primary and verify that it's visible in the standby.

  8. Verify that the Data Guard configuration is functioning as expected by performing switchover and failover in both directions. Run show configuration after each operation and make sure there are no errors or warnings in the dgmgrl command line utility.

    Caution:

    This step is optional, based on your discretion. If for any reason the configuration is not valid, the switchover and/or failover will fail and it might be difficult or impossible to start the primary database. A recovery of the primary might be required, which will affect availability.

    switchover to <standby db_unique_name>
    switchover to <primary db_unique_name>
    #connect to standby before failover:
    
    connect sys/<sys password>@<standby db_unique_name>
    failover to <standby db_unique_name>
    reinstate database <primary db_unique_name>
    #connect to primary before failover:
    
    connect sys/<sys password>@<primary db_unique_name>
    failover to <primary db_unique_name>
    reinstate database <standby db_unique_name>

Configure Observer (Optional)

The best practice for high availability and durability is to run the primary, standby, and observer in separate availaility domains. The observer determines whether or not to failover to a specific target standby database. The server used for observer requires the Oracle Client Administrator software, which includes the Oracle SQL NET and Broker. Execute following commands in dgmgrl command line utility.

  1. Configure TNS alias names for both the primary and standby databases as described previously, and verify the connection to both databases.
  2. Change protection mode to either maxavailability or maxperformance (maxprotection is not supported for FSFO).

    To enable maxavailability:

    edit database <standby db_unique_name> set property 'logXptMode'='SYNC';
    edit database <primary db_unique_name> set property 'logXptMode'='SYNC';
    edit configuration set protection mode as maxavailability;

    To enable maxperformance:

    edit configuration set protection mode as maxperformance;
    edit database <standby db_unique_name> set property 'logXptMode'='ASYNC';
    edit database <primary db_unique_name> set property 'logXptMode'='ASYNC';

    For maxperformance, the FastStartFailoverLaglimit property limits the maximum amount of permitted data loss to 30 seconds by default.

  3. The following properties should also be considered. Run show configuration verbose to see their current values.
    • FastStartFailoverPmyShutdown
    • FastStartFailoverThreshold
    • FastStartFailoverTarget
    • FastStartFailoverAutoReinstate

    (Running show configuration will result in the following error until the observer is started: Warning : ORA-16819: fast-start failover observer not started.)

  4. Enable fast-start failover from Broker:
    Enable fast_start failover
  5. Verify the fast-start failover and associated settings.
    show fast_start failover
  6. Start the observer from Broker (it will run in the foreground, but can also be run in the background).
    start observer
  7. Verify fast-start failover is enabled and without errors or warnings.
    show configuration verbose
  8. Always test failover in both directions to ensure that everything is working as expected. Verify that FSFO is running properly by performing a shutdown abort of the primary database.

    The observer should start the failover to the standby database. If protection mode is set to maxprotection, some loss of data can occur, based on the FastStartFailoverLaglimit value.