1. Environment

On Primary (Step 2 to Step 12)

2. Enable Forced Logging on Primary

3. Check Password File on Primary

4. Configure a Standby Redo Log on Primary

5. Verify Archive Mode Enabled on Primary

6. Set Primary Database Initialization Parameters

7. Backup Primary Database for configure Standby

8. Transfer PASSWORD FILE TO STANDBY SIDE

9. Transfer Backup from Primary to Standby

10. Transfer pfile from primary to standby

11. Configure TNS for Primary

12. Verify connection ‘AS SYSDBA’ from Primary

On STANDBY (Step 13 to Step 21)

13. Configure TNS for STANDBY

14. Configure init parameter for STANDBY

15. Add oratab entry

16. Create required directories

17. Startup Nomount

18. Verify connection ‘AS SYSDBA’ from Standby

19. Run RMAN Duplicate for standby

20. Verify Standby redo logs

21. Enable MRP on STANDBY

22. Verify Sync

1. Environment

Primary:

	Platform	: Linuxx86_64
	Server Name	: HOST1.DOMAIN.COM, IP: 192.168.1.11
	DB Version	: Oracle 19C
	File system     : Normal
	Database Name	: ERPDB
        DB_UNIQUE_NAME  : ERPDB
	Oracle Home Path: /u01/app/oracle/product/19.3.0.0.0
		
Standby:
		
	Platform	: Linuxx86_64
	Server Name	: HOST2.DOMAIN.COM, IP: 192.168.1.12
	DB Version	: Oracle 19C
	File system     : Normal
	Database Name	: ERPDB
        DB_UNIQUE_NAME  : ERPDB_DG
	Oracle Home Path: /u01/app/oracle/product/19.3.0.0.0

2. Enable Forced Logging on Primary

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

NAME      OPEN_MODE            CDB
--------- -------------------- ---
ERPDB READ WRITE           NO

SQL> select force_logging from v$database;

FORCE_LOGGING
---------------------------------------
NO

SQL> ALTER DATABASE FORCE LOGGING;

Database altered.

SQL> select force_logging from v$database;

FORCE_LOGGING
---------------------------------------
YES 

SQL>

3. Check Password File on Primary

$ pwd
/u01/app/oracle/product/19.3.0.0.0/dbs
$ ls -ltr orapwERPDB
-rw-r-----. 1 oracle dba 3584 Nov 22 10:30 orapwERPDB

4. Configure a Standby Redo Log on Primary

SQL> select b.thread#, a.group#, a.member, b.bytes FROM v$logfile a, v$log b WHERE a.group# = b.group#;

   THREAD#     GROUP# MEMBER                                                            BYTES
---------- ---------- ------------------------------------------------------------ ----------
         1          3 /u02/oracle/oradata/ERPDB/redo03.log                       209715200
         1          2 /u02/oracle/oradata/ERPDB/redo02.log                       209715200
         1          1 /u02/oracle/oradata/ERPDB/redo01.log                       209715200

SQL>

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/u02/oracle/oradata/ERPDB/redo04.log') SIZE 200M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/u02/oracle/oradata/ERPDB/redo05.log') SIZE 200M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/u02/oracle/oradata/ERPDB/redo06.log') SIZE 200M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/u02/oracle/oradata/ERPDB/redo07.log') SIZE 200M;

Database altered.

SQL>

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                                       IS_     CON_ID
---------- ------- ------- ------------------------------------------------------------ --- ----------
         3         ONLINE  /u02/oracle/oradata/ERPDB/redo03.log                      NO           0
         2         ONLINE  /u02/oracle/oradata/ERPDB/redo02.log                      NO           0
         1         ONLINE  /u02/oracle/oradata/ERPDB/redo01.log                      NO           0
         4         STANDBY /u02/oracle/oradata/ERPDB/redo04.log                      NO           0
         5         STANDBY /u02/oracle/oradata/ERPDB/redo05.log                      NO           0
         6         STANDBY /u02/oracle/oradata/ERPDB/redo06.log                      NO           0
         7         STANDBY /u02/oracle/oradata/ERPDB/redo07.log                      NO           0

7 rows selected.

SQL>

SQL> select a.group#, a.member, b.bytes FROM v$logfile a, v$standby_log b WHERE a.group# = b.group#;

    GROUP# MEMBER                                                            BYTES
---------- ------------------------------------------------------------ ----------
         4 /u02/oracle/oradata/ERPDB/redo04.log                       209715200
         5 /u02/oracle/oradata/ERPDB/redo05.log                       209715200
         6 /u02/oracle/oradata/ERPDB/redo06.log                       209715200
         7 /u02/oracle/oradata/ERPDB/redo07.log                       209715200

SQL>

5. Verify Archive Mode Enabled on Primary

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u02/oracle/archive/ERPDB
Oldest online log sequence     1208112
Next log sequence to archive   1208113
Current log sequence           1208113
SQL>

6. Set Primary Database Initialization Parameters

SQL> alter system set db_unique_name='ERPDB' scope=spfile;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(ERPDB,ERPDB_DG)' scope=both;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/u02/oracle/archive/ERPDB VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ERPDB' scope=both;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=ERPDB_DG LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ERPDB_DG' scope=both;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.dbf' SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30 scope=both;

System altered.

SQL> ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET fal_client=ERPDB scope=both;

System altered.

SQL>

Please note: The FAL_CLIENT database initialization parameter is no longer required from 11gR2

SQL> ALTER SYSTEM SET fal_server=ERPDB_DG scope=both;

System altered.

SQL> ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/u02/oracle/oradata/ERPDB_DG','/u02/oracle/oradata/ERPDB' SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/u02/oracle/oradata/ERPDB_DG','/u02/oracle/oradata/ERPDB' SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

System altered.

SQL>
SQL> create pfile='/home/oracle/initERPDB_after.ora' from spfile;

File created.

SQL> 

7. Backup Primary Database for configure Standby

$ mkdir -p /u02/oracle/backup/DB_NAME
$ cd /u02/oracle/backup/DB_NAME
rman target / 
run { 
allocate channel t1 type disk; 
allocate channel t2 type disk; 
allocate channel t3 type disk; 
backup database format '/u02/oracle/backup/ERPDB/database_%d_%u_%s'; release channel t1; 
release channel t2; 
release channel t3; 
} 
sql 'alter system archive log current'; 
run { 
allocate channel a1 type disk; 
allocate channel a2 type disk; 
allocate channel a3 type disk; 
backup archivelog all format '/u02/oracle/backup/ERPDB/arch_%d_%u_%s'; release channel a1; 
release channel a2; 
release channel a3; 
} 

8. Transfer PASSWORD FILE TO STANDBY SIDE

Copy the password file from the primary to standby $ORACLE_HOME/dbs and rename it to the standby database name.

$ cd /u01/app/oracle/product/19.3.0.0.0/dbs/
$ scp orapwERPDB oracle@host2:/u01/app/oracle/product/19.3.0.0.0/dbs/orapwERPDB_DG
oracle@host2's password:
orapwERPDB                        100% 3584     3.5KB/s   00:00

9. Transfer Backup from Primary to Standby

$ cd/u02/oracle/backup/ERPDB
$ scp * oracle@host2:/u02/oracle/backup/ERPDB_DG/

10. Transfer pfile from primary to standby

$ scp initERPDB_after.ora oracle@host2:/u02/oracle/backup/ERPDB_DG/
oracle@host2's password:
initERPDB_after.ora                                100% 1780     1.7KB/s   00:00

11. Configure TNS for Primary

[oracle@host1 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/19.3.0.0.0/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER_19C =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ERPDB)
      (ORACLE_HOME = /u01/app/oracle/product/19.3.0.0.0)
      (SID_NAME = ERPDB)
    )
  )

LISTENER_19C =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = host1.domain.com)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER_19C = /u01/app/oracle

[oracle@rac1 admin]$

[oracle@rac1 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.3.0.0.0/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ERPDB_DG =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.12)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ERPDB_DG)
    )
  )

ERPDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.11)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ERPDB)
    )
  )

LISTENER_19C =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.11)(PORT = 1521))

[oracle@host1 admin]$

[oracle@host1 admin]$ lsnrctl status LISTENER_19C

12. Verify connection ‘AS SYSDBA’ from Primary

[oracle@host1 ~]$ sqlplus SYS/SYS@ERPDB AS SYSDBA

SQL*Plus: Release 19.3.0.0.0 Production on Sat Jan 5 17:58:18 2022

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.3.0.0.0 - 64bit Production

SQL>

On STANDBY (Step 12 to Step 21)

13. Configure TNS for STANDBY

[oracle@host2 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/19.3.0.0.0/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER_19C =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ERPDB_DG)
      (ORACLE_HOME = /u01/app/oracle/product/19.3.0.0.0)
      (SID_NAME = ERPDB_DG)
    )
  )

LISTENER_19C =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = host2.domain.com)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER_12C = /u01/app/oracle

[oracle@rac2 admin]$

[oracle@rac2 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.3.0.0.0/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ERPDB_DG =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.12)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ERPDB_DG)
    )
  )

ERPDB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.11)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ERPDB)
    )
  )

LISTENER_19C =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.12)(PORT = 1521))

[oracle@host2 admin]$

[oracle@host2 admin]$ lsnrctl status LISTENER_19C

LSNRCTL for Linux: Version 19.3.0.0.0 - Production on 03-JAN-2022 18:14:00

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host2.domain.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_12C
Version                   TNSLSNR for Linux: Version 19.3.0.0.0 - Production
Start Date                03-JAN-2022 18:01:00
Uptime                    0 days 0 hr. 09 min. 30 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/19.3.0.0.0/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/host2/listener_19c/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=host2.domain.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "ERPDB_DG" has 1 instance(s).
  Instance "ERPDB_DG", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@rac2 admin]$

14. Configure init parameter for STANDBY

Modify iniERPDB_after.ora and renamed to iniERPDB_DG.ora

[oracle@host2 ERPDB_DG]$ cat initERPDB_DG.ora

STANDBY_DB.__data_transfer_cache_size=0
STANDBY_DB.__db_cache_size=369098752
STANDBY_DB.__inmemory_ext_roarea=0
STANDBY_DB.__inmemory_ext_rwarea=0
STANDBY_DB.__java_pool_size=16777216
STANDBY_DB.__large_pool_size=33554432
STANDBY_DB.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
STANDBY_DB.__pga_aggregate_target=587202560
STANDBY_DB.__sga_target=687865856
STANDBY_DB.__shared_io_pool_size=33554432
STANDBY_DB.__shared_pool_size=218103808
STANDBY_DB.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/ERPDB_DG/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/u02/oracle/oradata/ERPDB_DG/control01.ctl','/u02/oracle/oradata/ERPDB_DG/control02.ctl'
*.db_block_size=8192
*.db_file_name_convert='/u02/oracle/oradata/ERPDB','/u02/oracle/oradata/ERPDB_DG'
*.db_name='ERPDB'
*.db_unique_name='ERPDB_DG'
*.diagnostic_dest='/u01/app/oracle'
*.fal_client='ERPDB_DG'
*.fal_server='ERPDB'
*.log_archive_config='DG_CONFIG=(ERPDB,ERPDB_DG)'
*.log_archive_dest_1='LOCATION=/u02/oracle/archive/ERPD_DG VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ERPDB_DG'
*.log_archive_dest_2='SERVICE=ERPDB LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ERPDB'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=30
*.log_file_name_convert='/u02/oracle/oradata/ERPDB','/u02/oracle/oradata/ERPDB_DG'
*.memory_target=1201m
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'

[oracle@host2 ERPDB_DG]$

15. Add oratab entry

[oracle@host2 ~]$ cat /etc/oratab | grep -i ERPDB_DG
ERPDB_DG:/u01/app/oracle/product/19.3.0.0.0:N
[oracle@host2 ~]$

16. Create required directories

[oracle@host2 ~]$ mkdir -p /u01/app/oracle/admin/ERPDB_DG/adump
[oracle@host2 ~]$ mkdir -p /u02/oracle/oradata/ERPDB_DG
[oracle@host2 ~]$ mkdir -p /u02/oracle/archive/ERPDB_DG
[oracle@host2 ~]$

17. Startup Nomount

[oracle@host2 ~]$ . oraenv
ORACLE_SID = [ERPDB_DG] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@rac2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.3.0.0.0 Production on Sat Jan 5 18:01:38 2022

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount pfile='/u02/oracle/backup/ERPDB_DG/initERPDB_DG.ora';
ORACLE instance started.

Total System Global Area 1275068416 bytes
Fixed Size                  8620272 bytes
Variable Size             939525904 bytes
Database Buffers          318767104 bytes
Redo Buffers                8155136 bytes
SQL>
SQL> create spfile from pfile='/u02/oracle/backup/ERPDB_DG/initERPDB_DG.ora';

File created.

SQL> shut immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL>
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1275068416 bytes
Fixed Size                  8620272 bytes
Variable Size             939525904 bytes
Database Buffers          318767104 bytes
Redo Buffers                8155136 bytes
SQL>

18. Verify connection ‘AS SYSDBA’ from Standby

[oracle@rac2 ~]$ sqlplus SYS/SYS@ERPDB_DG AS SYSDBA

SQL*Plus: Release 19.3.0.0.0 Production on Sat Jan 5 18:18:57 2022

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 19.3.0.0.0 - 64bit Production

SQL>

19. Run RMAN Duplicate for standby

[oracle@host2 ~]$ cd /u02/oracle/backup/ERPDB_DG/
[oracle@host2 ERPDB_DGB]$
[oracle@host2 ERPDB_DG]$rman auxiliary /
run {
allocate auxiliary channel t1 type disk;
allocate auxiliary channel t2 type disk;
allocate auxiliary channel t3 type disk;
SET NEWNAME FOR DATABASE   TO  '/u02/oracle/oradata/ERPDB_DG/%b';
duplicate target database for standby backup location '/u02/oracle/backup/ERPDB_DG' nofilenamecheck;
}

20. Verify Standby Database

SQL> set lines 190
SQL> SELECT NAME,OPEN_MODE,DB_UNIQUE_NAME,DATABASE_ROLE,PROTECTION_MODE FROM V$DATABASE;

NAME      OPEN_MODE            DB_UNIQUE_NAME                 DATABASE_ROLE    PROTECTION_MODE
--------- -------------------- ------------------------------ ---------------- --------------------
ERPDB     MOUNTED              ERPDB_DG                    PHYSICAL STANDBY MAXIMUM PERFORMANCE

SQL>
SQL> col member for a50
SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                             IS_     CON_ID
---------- ------- ------- -------------------------------------------------- --- ----------
         3         ONLINE  /u02/oracle/oradata/ERPDB_DG/redo03.log         NO           0
         2         ONLINE  /u02/oracle/oradata/ERPDB_DG/redo02.log         NO           0
         1         ONLINE  /u02/oracle/oradata/ERPDB_DG/redo01.log         NO           0
         4         STANDBY /u02/oracle/oradata/ERPDB_DG/redo04.log         NO           0
         5         STANDBY /u02/oracle/oradata/ERPDB_DG/redo05.log         NO           0
         6         STANDBY /u02/oracle/oradata/ERPDB_DG/redo06.log         NO           0
         7         STANDBY /u02/oracle/oradata/ERPDB_DG/redo07.log         NO           0

7 rows selected.

SQL> select a.group#, a.member, b.bytes FROM v$logfile a, v$standby_log b WHERE a.group# = b.group#;

    GROUP# MEMBER                                                  BYTES
---------- -------------------------------------------------- ----------
         4 /u02/oracle/oradata/ERPDB_DG/redo04.log          209715200
         5 /u02/oracle/oradata/ERPDB_DG/redo05.log          209715200
         6 /u02/oracle/oradata/ERPDB_DG/redo06.log          209715200
         7 /u02/oracle/oradata/ERPDB_DG/redo07.log          209715200

SQL>

21. Enable MRP on STANDBY

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

Database altered.

SQL> /
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active <---- This means Standby database configured properly.


SQL>

22. Verify Sync

On Primary

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

NAME      OPEN_MODE            DATABASE_ROLE    INSTANCE_NAME
--------- -------------------- ---------------- ----------------
ERPDB     READ WRITE           PRIMARY          ERPDB

SQL> select max(sequence#) from v$archived_log where archived='YES';

MAX(SEQUENCE#)
--------------
            47 <-----

SQL>

On STANDBY

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

NAME      OPEN_MODE            DATABASE_ROLE    INSTANCE_NAME
--------- -------------------- ---------------- ----------------
ERPDB      MOUNTED              PHYSICAL STANDBY ERPDB_DG

SQL> select max(sequence#) from v$archived_log where applied='YES';

MAX(SEQUENCE#)
--------------
            47 <-----

SQL>