Without shutting down primary, we need to create physical standby database using RMAN DUPLICATE FROM ACTIVE DATABASE command (No need to take backup of primary database).

Active Data Guard is a new option for Oracle Database 11g Enterprise Edition.

Oracle Active Data Guard enables read-only access to a physical standby database for queries, sorting, reporting, web-based access, etc., while continuously applying changes received from the production/primary database.

1. Enable Forced Logging on Primary

2. Create Password File on Primary

3. Configure a Standby Redo Log on Primary

4. Verify Archive Mode Enabled on Primary

5. Set Primary Database Initialization Parameters

6. Transfer Spfile and Password file to Standby node

7. Set Standby Database Initialization Parameters

8. Crete required directories on Standby

9. Add below entry in ORATAB on Standby

10. Create spfile from pfile on Standby

11. Configure TNS Entries on Primary

12. Configure TNS Entries on Standby

13. Verify connection ‘AS SYSDBA’ from Primary

14. Create Standby Database

15. Enable MRP on Standby

16. Verify the Physical Standby Database Is Performing Properly

Enviroment

Source:

Platform          : Linuxx86_64
Server Name       : HOST1.DOMAIN.COM, IP: 192.168.1.11
DB Version        : Oracle 19.3.0.0.0 , File system: Normal
DB Name           : ERPDB, DB_UNIQUE_NAME: ERPDB
Flashback         : Disabled
Oracle Home Path  : /u01/app/oracle/product/19.3.0.0.0/db_1
		
Target:
		
Platform          : Linuxx86_64
Server Name       : RAC2.RAJASEKHAR.COM, IP: 192.168.1.12
DB Version        : Oracle 19.3.0.0.0 , File system: Normal
DB Name           : ERPDB, DB_UNIQUE_NAME: ERPDB_DG
Flashback         : Disabled
Oracle Home Path  : /u01/app/oracle/product/19.3.0.0.0/db_1

1. Enable Forced Logging on Primary

SQL> select name, open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
ERPDB      READ WRITE

SQL> select force_logging from v$database;

FOR
---
NO

SQL> ALTER DATABASE FORCE LOGGING;

Database altered.

SQL> select force_logging from v$database;

FOR
---
YES 

SQL>

2. Create Password File on Primary

[oracle@host1 ~]$ cd $ORACLE_HOME/dbs
[oracle@host1 dbs]$ pwd
/u01/app/oracle/product/19.3.0.0.0/db_1/dbs
[oracle@host1 dbs]$ orapwd file=orapwaERPDB password=sys force=y
[oracle@host1 dbs]$ ls -ltr orapwapac
-rw-r----- 1 oracle oinstall 1536 May 34 16:25 orapwaERPDB
[oracle@host1 dbs]$

3. Configure a Standby Redo Log on Primary

SQL> set lines 180
SQL> col MEMBER for a60
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 /u01/app/oracle/oradata/ERPDB/redo03.log                        52428800
         1          2 /u01/app/oracle/oradata/ERPDB/redo02.log                        52428800
         1          1 /u01/app/oracle/oradata/ERPDB/redo01.log                        52428800

SQL>

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

Database altered.

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

Database altered.

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

Database altered.

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

Database altered.

SQL>

SQL> select * from v$logfile;

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

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 /u01/app/oracle/oradata/ERPDB/redo04.log                        52428800
         5 /u01/app/oracle/oradata/ERPDB/redo05.log                        52428800
         6 /u01/app/oracle/oradata/ERPDB/redo06.log                        52428800
         7 /u01/app/oracle/oradata/ERPDB/redo07.log                        52428800

SQL>

4. Verify Archive Mode Enabled on Primary

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

5. Set Primary Database Initialization Parameters

SQL> show parameter pfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/19.3.0.0.0
                                                 /db_1/dbs/spfileERPDB.ora
SQL> create pfile='/home/oracle/initERPDB.ora' from spfile;

File created.

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=/u01/app/oracle/arch/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> ALTER SYSTEM SET fal_server=ERPDB_DG scope=both;

System altered.

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

System altered.

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

System altered.

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

System altered.

SQL>

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

File created.

SQL>

[oracle@rac1 ~]$ cat initapac.ora 
ERPDB.__db_cache_size=339738624
ERPDB.__java_pool_size=4194304
ERPDB.__large_pool_size=4194304
ERPDB.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
ERPDB.__pga_aggregate_target=335544320
ERPDB.__sga_target=503316480
ERPDB.__shared_io_pool_size=0
ERPDB.__shared_pool_size=146800640
ERPDB.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/ERPDB/adump'
*.audit_trail='db'
*.compatible='19.3.0.0.0'
*.control_files='/u01/app/oracle/oradata/ERPDB/control01.ctl','/u01/app/oracle/oradata/ERPDB/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='/u01/app/oracle/oradata/ERPDB_DG','/u01/app/oracle/oradata/ERPDB','/u02/ERPDB_DG','/u02/ERPDB'
*.db_name='ERPDB'
*.db_unique_name='ERPDB'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=apacXDB)'
*.fal_client='ERPDB'
*.fal_server='ERPDB_DG'
*.log_archive_config='DG_CONFIG=(ERPDB,ERPDB_DG)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/arch/ERPDB VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ERPDB'
*.log_archive_dest_2='SERVICE=ERPDB_DG LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ERPDB_DG'
*.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='/u01/app/oracle/oradata/ERPDB_DG','/u01/app/oracle/oradata/ERPDB'
*.memory_target=838860800
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
[oracle@host1 ~]$ 

6. Transfer SPFILE AND PASSWORD FILE TO STANDBY SIDE

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

The username is required to be SYS and the password needs to be the same on the Primary and Standby.

The best practice for this is to copy the passwordfile as suggested.

The password file name must match the instance name/SID used at the standby site, not the DB_NAME.

[oracle@host1 ~]$ scp initERPDB.ora oracle@host2:/home/oracle/initERPDB_DG.ora
initERPDB.ora                                                 100% 1487     1.5KB/s   00:00
[oracle@host1 ~]$ cd /u01/app/oracle/product/11.2.0/db_1/dbs/orapERPDB_DG
[oracle@host1 dbs]$ scp orapwapac oracle@rac2:/u01/app/oracle/product/19.3.0.0.0/db_1/dbs/
orapERPDB_DG                                                   100% 1536     1.5KB/s   00:00
[oracle@host1 dbs]$

7. Set Standby Database Initialization Parameters

Edit shown highlighted to set it up for the standby role

[oracle@host2 ~]$ cat initERPDB_DG.ora
emea.__db_cache_size=339738624
emea.__java_pool_size=4194304
emea.__large_pool_size=4194304
emea.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
emea.__pga_aggregate_target=335544320
emea.__sga_target=503316480
emea.__shared_io_pool_size=0
emea.__shared_pool_size=146800640
emea.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/ERPDB_DG/adump'
*.audit_trail='db'
*.compatible='19.3.0.0.0'
*.control_files='/u01/app/oracle/oradata/ERPDB_DG/control01.ctl','/u01/app/oracle/oradata/ERPDB_DG/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='/u01/app/oracle/oradata/ERPDB','/u01/app/oracle/oradata/ERPDB_DG','/u02/ERPDB','/u02/ERPDB_DG'
*.db_name='ERPDB'
*.db_unique_name='ERPDB_DG'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ERPDB_DGXDB)'
*.fal_client='ERPDB_DG'
*.fal_server='ERPDB'
*.log_archive_config='DG_CONFIG=(ERPDB,ERPDB_DG)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/arch/ERPDB_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='/u01/app/oracle/oradata/ERPDB','/u01/app/oracle/oradata/ERPDB_DG'
*.memory_target=838860800
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
[oracle@host2 ~]$

8. Crete required directories on Standby

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

9. Add below entry in ORATAB on Standby

ERPDB_DG:/u01/app/oracle/product/19.3.0.0.0/db_1:N

10. Create spfile from pfile on Standby

Once the new parameter file is ready we create from it the spfile:

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

SQL*Plus: Release 19.3.0.0.0 Production on Sat Jul 25 19:34:58 2022

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

Connected to an idle instance.

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

Total System Global Area  835104768 bytes
Fixed Size                  2232960 bytes
Variable Size             490737024 bytes
Database Buffers          339738624 bytes
Redo Buffers                2396160 bytes
SQL> create spfile from pfile='/home/oracle/initERPDB_DG.ora';

File created.

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


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

Total System Global Area  835104768 bytes
Fixed Size                  2232960 bytes
Variable Size             490737024 bytes
Database Buffers          339738624 bytes
Redo Buffers                2396160 bytes
SQL> show parameter pfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/19.3.0.0.0
                                                 /db_1/dbs/spfileERPDB_DG.ora
SQL>

11. Configure TNS Entries on Primary

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

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.11)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

[oracle@host1 admin]$ 

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

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

ERPDB_DG =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.12)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ERPDB_DG)(UR = A)
    )
  )
[oracle@rac1 admin]$ 

[oracle@rac1 ~]$ lsnrctl status

LSNRCTL for Linux: Version 19.3.0.0.0 - Production on 25-JUL-2022 19:22:10

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host1.domain.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.3.0.0.0 - Production
Start Date                25-JUL-2022 19:20:00
Uptime                    0 days 0 hr. 2 min. 10 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/19.3.0.0.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/rac1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=host1.domain.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "ERPDB" has 1 instance(s).
  Instance "ERPDB", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@rac1 ~]$ 

[oracle@rac1 ~]$ tnsping ERPDB

TNS Ping Utility for Linux: Version 19.3.0.0.0 - Production on 25-JUL-2022 19:38:14

Copyright (c) 1997, 2022 , Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/19.3.0.0.0/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.11)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ERPDB)))
OK (0 msec)
[oracle@host1 ~]$
[oracle@host1 ~]$ tnsping ERPDB_DG

TNS Ping Utility for Linux: Version 19.3.0.0.0 - Production on 25-JUL-2022 19:38:17

Copyright (c) 1997, 2022 , Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/19.3.0.0.0/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.12)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ERPDB_DG)(UR = A)))
OK (0 msec)
[oracle@host1 ~]$

12. Configure TNS Entries on Standby

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

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.12)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

[oracle@host2 admin]$ 

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

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

ERPDB_DG =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.12)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ERPDB_DG)(UR = A)
    )
  )
[oracle@host2 admin]$ 

[oracle@host2 ~]$ lsnrctl status

LSNRCTL for Linux: Version 19.3.0.0.0 - Production on 25-JUL-2022 19:24:10

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.12)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                25-JUL-2022 19:22:24
Uptime                    0 days 0 hr. 2 min. 10 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/19.3.0.0.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/host2/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.12)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "ERPDB_DG" has 1 instance(s).
  Instance "ERPDB_DG", status BLOCKED, has 1 handler(s) for this service...
The command completed successfully
[oracle@host2 ~]$ 


[oracle@host2 ~]$ tnsping ERPDB

TNS Ping Utility for Linux: Version 19.3.0.0.0 - Production on 25-JUL-2022 19:37:17

Copyright (c) 1997, 2022, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.11)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ERPDB)))
OK (0 msec)
[oracle@host2 ~]$
[oracle@host2 ~]$ tnsping ERPDB_DG

TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 25-JUL-2022 19:37:25

Copyright (c) 1997, 2022, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.12)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ERPDB_DG)(UR = A)))
OK (10 msec)
[oracle@rac2 ~]$

13. Verify connection ‘AS SYSDBA’ from Primary

[oracle@host1 ~]$ sqlplus sys/sys@apac as sysdba

SQL*Plus: Release 19.3.0.0.0 Production on Sat Jul 25 19:45:37 2022

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


Connected to:
Oracle Database 11g Enterprise Edition Release 19.3.0.0.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 19.3.0.0.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@host1 ~]$ 
[oracle@host1 ~]$ sqlplus sys/sys@emea as sysdba

SQL*Plus: Release 19.3.0.0.0 Production on Sat Jul 25 19:45:57 2022

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


Connected to:
Oracle Database 11g Enterprise Edition Release 19.3.0.0.0.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 19.3.0.0.0.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@host1 ~]$

14. Create Standby Database

[oracle@host1 ~]$ rman target sys/sys@ERPDB auxiliary sys/sys@ERPDB_DG

Recovery Manager: Release 19.3.0.0.0 - Production on Sat Jul 25 19:46:37 2022

Copyright (c) 1982, 2022, Oracle and/or its affiliates.  All rights reserved.

connected to target database: APAC (DBID=2914488844)
connected to auxiliary database: APAC (not mounted)

RMAN> duplicate target database for standby from active database nofilenamecheck;
 spfile
 set db_unique_name='ERPDB_DG'
 SET CLUSTER_DATABASE='FALSE'

Starting Duplicate Db at 25-JUL-15
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/u01/app/oracle/product/19.3.0.0.0.0/db_1/dbs/orapwERPDB' auxiliary format
 '/u01/app/oracle/product/19.3.0.0.0/db_1/dbs/orapwERPDB_DG'   ;
}
executing Memory Script

Starting backup at 25-JUL-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=67 device type=DISK
Finished backup at 25-JUL-15

contents of Memory Script:
{
   backup as copy current controlfile for standby auxiliary format  '/u01/app/oracle/oradata/ERPDB_DG/control01.ctl';
   restore clone controlfile to  '/u01/app/oracle/oradata/ERPDB_DG/control02.ctl' from
 '/u01/app/oracle/oradata/ERPDB_DG/control01.ctl';
}
executing Memory Script

Starting backup at 25-JUL-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/19.3.0.0.0/db_1/dbs/snapcf_apac.f tag=TAG20150725T195200 RECID=1 STAMP=886017123
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 25-JUL-15

Starting restore at 25-JUL-15
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 25-JUL-15

contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:
{
   set newname for tempfile  1 to
 "/u01/app/oracle/oradata/ERPDB_DG/temp01.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to
 "/u01/app/oracle/oradata/ERPDB_DG/system01.dbf";
   set newname for datafile  2 to
 "/u01/app/oracle/oradata/ERPDB_DG/sysaux01.dbf";
   set newname for datafile  3 to
 "/u01/app/oracle/oradata/ERPDB_DG/undotbs01.dbf";
   set newname for datafile  4 to
 "/u01/app/oracle/oradata/ERPDB_DG/users01.dbf";
   backup as copy reuse
   datafile  1 auxiliary format
 "/u01/app/oracle/oradata/ERPDB_DG/system01.dbf"   datafile
 2 auxiliary format
 "/u01/app/oracle/oradata/ERPDB_DG/sysaux01.dbf"   datafile
 3 auxiliary format
 "/u01/app/oracle/oradata/ERPDB_DG/undotbs01.dbf"   datafile
 4 auxiliary format
 "/u01/app/oracle/oradata/ERPDB_DG/users01.dbf"   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/ERPDB_DG/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 25-JUL-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/ERPDB/system01.dbf
output file name=/u01/app/oracle/oradata/ERPDB_DG/system01.dbf tag=TAG20150725T195217
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:45
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/ERPDB/sysaux01.dbf
output file name=/u01/app/oracle/oradata/ERPDB_DG/sysaux01.dbf tag=TAG20150725T195217
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/ERPDB/undotbs01.dbf
output file name=/u01/app/oracle/oradata/ERPDB_DG/undotbs01.dbf tag=TAG20150725T195217
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/ERPDB/users01.dbf
output file name=/u01/app/oracle/oradata/ERPDB_DG/users01.dbf tag=TAG20150725T195217
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 25-JUL-15

sql statement: alter system archive log current

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=886017341 file name=/u01/app/oracle/oradata/ERPDB_DG/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=886017341 file name=/u01/app/oracle/oradata/ERPDB_DG/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=886017341 file name=/u01/app/oracle/oradata/ERPDB_DG/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=886017341 file name=/u01/app/oracle/oradata/ERPDB_DG/users01.dbf
Finished Duplicate Db at 25-JUL-15

RMAN>

15. Enable MRP on Standby

[oracle@host2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.3.0.0.0 Production on Sat Jul 25 19:58:37 2022

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


Connected to:
Oracle Database 11g Enterprise Edition Release 19.3.0.0.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

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

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

SQL>

SQL> alter database open;

Database altered.

[oracle@host2 ~]$ ps -ef | grep mrp
oracle    1523   701  0 20:00 pts/1    00:00:00 grep mrp 
[oracle@host2 ~]$


SQL> alter database recover managed standby database disconnect from session;

Database altered.

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

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
ERPDB      READ ONLY WITH APPLY PHYSICAL STANDBY

SQL>

SQL> !ps -ef | grep mrp
oracle    1533     1  0 20:01 ?        00:00:00 ora_mrp0_emea 
oracle    1539  1524  0 20:01 pts/1    00:00:00 /bin/bash -c ps -ef | grep mrp
oracle    1541  1539  0 20:01 pts/1    00:00:00 grep mrp

SQL>

16. Verify the Physical Standby Database Is Performing Properly

On Primary

SQL> show parameters db_unique_name;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      ERPDB
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

 SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
         5 25-JUL-22 25-JUL-22
         6 25-JUL-22 25-JUL-22
         7 25-JUL-22 25-JUL-22
         8 25-JUL-22 25-JUL-22

4 rows selected.
SQL>
SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

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

MAX(SEQUENCE#)
--------------
            10 

SQL>

On Standby

SQL> set lines 180
SQL> col MEMBER for a60
SQL> select * from v$logfile;

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

7 rows selected.

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

 SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
         5 25-JUL-22 25-JUL-22
         6 25-JUL-22 25-JUL-22
         7 25-JUL-22 25-JUL-22
         8 25-JUL-22 25-JUL-22
         9 25-JUL-22 25-JUL-22
        10 25-JUL-22 25-JUL-22

6 rows selected.

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

MAX(SEQUENCE#)
--------------
            10 <----

SQL>