Prepare the Primary Database

Enable Logging

Please carry out the following steps on the primary server if needed.

Check if the primary database is in archivelog mode and that force logging is enabled.

SQL> select name,log_mode,force_logging from v$database;

NAME      LOG_MODE     FORCE_LOGGING
--------- ------------ ---------------------------------------
TPCH1T    NOARCHIVELOG   NO

Enable Archive Log mode

If the database is in NOARCHIVELOG mode, then we need to enable it.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1.6267E+11 bytes
Fixed Size                 30140696 bytes
Variable Size            1.8790E+10 bytes
Database Buffers         1.4334E+11 bytes
Redo Buffers              506728448 bytes
Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

Enable Force Logging

If force logging is not enabled, then enable it using the following commands.

SQL> alter database force logging;
Database altered.

SQL> alter system switch logfile;
System altered.

Initialization Parameters

In the primary database, we need to define initialization parameters that control redo transport services while the database is in the primary role.

There are additional parameters that need to be added that control the receipt of the redo data and apply services when the primary database is transitioned to the standby role.

DB_NAME=TPCH1T
DB_UNIQUE_NAME=TPCH1T
LOG_ARCHIVE_CONFIG='DG_CONFIG=(TPCH1T,TPCH1T_S)'
LOG_ARCHIVE_DEST_1= 'LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=TPCH1T'
LOG_ARCHIVE_DEST_2= 'SERVICE=TPCH1T _SBY ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=TPCH1T'
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

The following shows the additional standby role initialization parameters on the primary database. These parameters take effect when the primary database is transitioned to the standby role.

FAL_SERVER=TPCH1T_S
DB_FILE_NAME_CONVERT='/TPCH1T_S/','/TPCH1T/'
LOG_FILE_NAME_CONVERT='/TPCH1T_S/','/TPCH1T/'
STANDBY_FILE_MANAGEMENT=AUTO

These parameters can be set by running the commands below.

SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(TPCH1T,TPCH1T_S)'  scope=BOTH;
SQL> alter system set LOG_ARCHIVE_DEST_1= 'LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=TPCH1T'  scope=BOTH;
SQL> alter system set LOG_ARCHIVE_DEST_2= 'SERVICE=TPCH1T_S ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=TPCH1T_S'  scope=BOTH;
SQL> alter system set REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE  scope=SPFILE;
SQL> alter system set LOG_ARCHIVE_FORMAT='%t_%s_%r.arc'  scope=SPFILE;
SQL> alter system set FAL_SERVER=TPCH1T_S  scope=BOTH;
SQL> alter system set DB_FILE_NAME_CONVERT='/TPCH1T_S/','/TPCH1T/'  scope=SPFILE;
SQL> alter system set LOG_FILE_NAME_CONVERT='/TPCH1T_S/','/TPCH1T/'   scope=SPFILE;
SQL> alter system set STANDBY_FILE_MANAGEMENT=AUTO  scope=BOTH;

Configure Services

Make sure the TNS entries for both the Primary the Standby database are included in the $ORACLE_HOME/network/admin/tnsnames.ora file on both servers (Primary and Standby).

TPCH1T =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = sn1-r720-e03-07.puretec.purestorage.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TPCH1T)
    )
  )
  
TPCH1T_S =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = sn1-r720-e03-09.puretec.purestorage.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TPCH1T_S)
    )
  )

Password File Setup

Copy the password file from Primary to the Standby server with the db_unique_name format of the standby.

scp $ORACLE_HOME/dbs/orapwTPCH1T oracle@10.21.184.15:/u01/app/oracle/product/19.0.0/dbhome_1/dbs/orapwTPCH1T_S