Creating a RAC Physical Standby Database Using DBCA with the -createDuplicateDB -createAsStandby Option

In this blog, we will guide you through the steps to create a Physical Standby database using DBCA with the -createDuplicateDB -createAsStandby option in a RAC environment.

The environment consists of:

Primary:

  • Hosts: dbsrv01.localdomain, dbsrv02.localdomain.
  • Oracle RAC 19c with Release Update (RU) 19.23, database named "master".
  • Operating System: Oracle Linux 8.

Physical Standby:

  • Hosts: dbsrv03.localdomain, dbsrv04.localdomain.
  • Oracle RAC 19c with Release Update (RU) 19.23, database named "masters".
  • Operating System: Oracle Linux 8.

master (Primary)

masters (Physical Standby)

1. Cluster Configuration:cluster has two active nodes dbsrv01 and dbsrv02 with SCAN master-scan, ASM diskgroup +DATA, +OCRVOTE and +RECO and configured with a database named master.

 

[root@dbsrv01 ~]# su - grid

[grid@dbsrv01 ~]$ olsnodes -n -s

dbsrv01 1       Active

dbsrv02 2       Active

 

[grid@dbsrv01 ~]$ srvctl config scan

SCAN name: master-scan, Network: 1

 [grid@dbsrv01 ~]$ asmcmd ls

DATA/

OCRVOTE/

RECO/

 

[grid@dbsrv01 ~]$ su - oracle

Password:

 [oracle@dbsrv01 ~]$ srvctl config database

master

 [oracle@dbsrv01 ~]$ srvctl status database -db master

Instance master1 is running on node dbsrv01

Instance master2 is running on node dbsrv02

 

1. Cluster Configuration:cluster has two active nodes dbsrv03 and dbsrv04 with SCAN masters-scan, ASM diskgroup +DATA, +OCRVOTE and +RECO.

 

[root@dbsrv03 ~]# su – grid

[grid@dbsrv03 ~]$ olsnodes -n -s

dbsrv03 1       Active

dbsrv04 2       Active

 

[grid@dbsrv03 ~]$ srvctl config scan

SCAN name: masters-scan, Network: 1

[grid@dbsrv03 ~]$ asmcmd ls

DATA/

OCRVOTE/

RECO/

2. Configure net services for standby on both db nodes.

 [oracle@dbsrv01 ~]$ cd $ORACLE_HOME/network/admin

[oracle@dbsrv01 admin]$ cat tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

 

MASTER =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = master-scan)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = master)

    )

  )

 

MASTERS =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = masters-scan)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = masters)

    )

  )

 

2. While running dbca -createAsStandby, it configures a temporary static listener on the current node, creates the tnsnames.ora file under $ORACLE_HOME/network/admin on both nodes and configures the net services for the standby database.

3. Configure the archivelog destination and enable archivelog mode.

 

[oracle@dbsrv01 ~]$ srvctl stop database -db master

[oracle@dbsrv01 ~]$ srvctl start database -db master -startoption mount

[oracle@dbsrv01 ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 5 22:16:06 2025

Version 19.23.0.0.0

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

 

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.23.0.0.0

 

SQL> alter system set log_archive_dest_1='location=+RECO valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=master' scope=both sid='*';

 

System altered.

 

SQL> alter database archivelog;

 

Database altered.

 

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.23.0.0.0

 

[oracle@dbsrv01 ~]$ srvctl stop database -db master

[oracle@dbsrv01 ~]$ srvctl start database -db master

 

3. Here is the output showing that, As part of the DBCA process, a static listener and tnsnames.ora file were created, along with the standby configuration of net service names.

 

[oracle@dbsrv03 ~]$ ps -ef |grep tns

root          27       2  0 10:15 ?        00:00:00 [netns]

grid        4968       1  0 10:20 ?        00:00:00 /u01/app/19.3.0/grid/bin/tnslsnr LISTENER -no_crs_notify -inherit

grid       14815       1  0 10:26 ?        00:00:03 /u01/app/19.3.0/grid/bin/tnslsnr ASMNET1LSNR_ASM -no_crs_notify -inherit

grid       15771       1  0 10:26 ?        00:00:00 /u01/app/19.3.0/grid/bin/tnslsnr LISTENER_SCAN1 -no_crs_notify -inherit

oracle     86656       1  0 11:47 ?        00:00:00 /u01/app/oracle/product/19.3.0/dbhome_1/bin/tnslsnr LISTENER20250107114612 -inherit

oracle     97272   35606  0 12:06 pts/0    00:00:00 grep --color=auto tns

 

[oracle@dbsrv03 ~]$ cat $ORACLE_HOME/network/admin/listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

 

SID_LIST_LISTENER20250107114612 =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = masters1)

    )

  )

 

LISTENER20250107114612 =

  (ADDRESS_LIST =

    (ADDRESS = (PROTOCOL = TCP)(HOST = dbsrv03.localdomain)(PORT = 1522))

  )

 

[oracle@dbsrv03 ~]$ cat $ORACLE_HOME/network/admin/tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

 

MASTERS =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = masters-scan)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = SHARED)

      (SERVICE_NAME = masters)

    )

  )

 

4. Enable force logging and configure dataguard parameters.

 

[oracle@dbsrv01 ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 5 20:57:58 2025

Version 19.23.0.0.0

 

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

 

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.23.0.0.0

 

SQL> alter database force logging;

 

Database altered.

 

SQL> alter system set log_archive_config='dg_config=(master,masters)' scope=both sid='*' ;

 

System altered.

 

SQL> alter system set log_archive_dest_2='service=masters lgwr async valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=masters' scope=both sid='*';

 

System altered.

 

SQL> alter system set fal_server='masters' scope=both sid='*';

 

System altered.

 

SQL> alter system set standby_file_management='auto' scope=both sid='*';

 

System altered.

 

SQL> SELECT name,value from v$parameter WHERE name IN ('log_archive_config','log_archive_dest_1','log_archive_dest_2','fal_server', 'standby_file_management');

 

NAME                      VALUE

------------------------- ------------------------------------------------------------------------------------------

log_archive_dest_1        location=+RECO valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=master

log_archive_dest_2        service=masters lgwr async valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=masters

fal_server                masters

log_archive_config        dg_config=(master,masters)

standby_file_management   auto

 

 

SQL> select name,open_mode,database_role,force_logging,log_mode from gv$database;

 

NAME      OPEN_MODE       DATABASE_ROLE    FORCE_LOGGING        LOG_MODE

--------- --------------- ---------------- -------------------- ------------

MASTER    READ WRITE      PRIMARY          YES                  ARCHIVELOG

MASTER    READ WRITE      PRIMARY          YES                  ARCHIVELOG

 

 

 

 

 

5. Create Standby Redo logs.

 SQL> select thread#,group#,bytes/1024/1024 mb,status from v$log;

   THREAD#     GROUP#         MB STATUS

---------- ---------- ---------- ----------------

         1         11        200 CURRENT

         1         12        200 INACTIVE

         2         13        200 CURRENT

         2         14        200 INACTIVE

 

SQL> alter database add standby logfile thread 1 group 5 ('+RECO') size 200m,group 6 ('+RECO') size 200m,group 7 ('+RECO') size 200m;

 Database altered.

 

SQL> alter database add standby logfile thread 2 group 8 ('+RECO') size 200m,group 9 ('+RECO') size 200m,group 10 ('+RECO') size 200m;

 Database altered.

 

 

SQL> select group#,type,member from v$logfile;

     GROUP# TYPE    MEMBER

---------- ------- ------------------------------------------------

         5 STANDBY +RECO/MASTER/ONLINELOG/group_5.312.1189631315

         6 STANDBY +RECO/MASTER/ONLINELOG/group_6.313.1189631321

         7 STANDBY +RECO/MASTER/ONLINELOG/group_7.314.1189631323

         8 STANDBY +RECO/MASTER/ONLINELOG/group_8.315.1189631333

         9 STANDBY +RECO/MASTER/ONLINELOG/group_9.316.1189631337

        10 STANDBY +RECO/MASTER/ONLINELOG/group_10.317.1189631341

        11 ONLINE  +RECO/MASTER/ONLINELOG/group_11.355.1189774295

        12 ONLINE  +RECO/MASTER/ONLINELOG/group_12.356.1189774297

        13 ONLINE  +RECO/MASTER/ONLINELOG/group_13.357.1189774307

        14 ONLINE  +RECO/MASTER/ONLINELOG/group_14.358.1189774309

 

10 rows selected.

 

 

 

 

4. Run DBCA to create a RAC physical standby database named "masters" by passing the required parameter values as follows.

 [oracle@dbsrv03 ~]$ dbca -silent -createDuplicateDB -gdbname master -sid masters -primaryDBConnectionString master-scan:1521/master -sysPassword "sys" -createAsStandby -dbUniqueName masters -initParams fal_server=master -databaseConfigType RAC -adminManaged -nodelist dbsrv03,dbsrv04

Prepare for db operation

22% complete

Listener config step

44% complete

Auxiliary instance creation

67% complete

RMAN duplicate

89% complete

Post duplicate database operations

100% complete

 

Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/masters/masters.log" for further details.

 

 

5. Configure dataguard parameters and start the MRP apply process.

 [oracle@dbsrv03 ~]$ sqlplus / as sysdba

 SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jan 6 19:19:23 2025

Version 19.23.0.0.0

 

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

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.23.0.0.0

 

SQL> select name,db_unique_name,open_mode,database_role from gv$database;

 

NAME      DB_UNIQUE_NAME  OPEN_MODE       DATABASE_ROLE

--------- --------------- --------------- ----------------

MASTER    masters         READ ONLY       PHYSICAL STANDBY

MASTER    masters         READ ONLY       PHYSICAL STANDBY

 

SQL> SELECT name,value from v$parameter WHERE name IN ('log_archive_config','log_archive_dest_1','log_archive_dest_2','fal_server', 'standby_file_management');

 

NAME                      VALUE

------------------------- ------------------------------------------------------------------------------------------

log_archive_dest_1       

log_archive_dest_2    

fal_server                master

log_archive_config        dg_config=(master,masters)

standby_file_management   AUTO

 

SQL> alter system set log_archive_dest_1='location=+RECO valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=masters' scope=both sid='*';

System altered.

 

SQL> alter system set log_archive_dest_2='service=master lgwr async valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=master' scope=both sid='*';

System altered.

 

SQL> SELECT name,value from v$parameter WHERE name IN ('log_archive_config','log_archive_dest_1','log_archive_dest_2','fal_server', 'standby_file_management');

 

NAME                      VALUE

------------------------- ------------------------------------------------------------------------------------------

log_archive_dest_1        location=+RECO valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=masters

log_archive_dest_2        service=master lgwr async valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=master

fal_server                master

log_archive_config        dg_config=(master,masters)

standby_file_management   AUTO

 

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

Database altered.

 

SQL> select thread#,sequence#,block#,name,role,action from v$dataguard_process;

 

   THREAD#  SEQUENCE#     BLOCK# NAME  ROLE                     ACTION

---------- ---------- ---------- ----- ------------------------ ------------

         0          0          0 LGWR  log writer               IDLE

         0          0          0 TMON  redo transport monitor   IDLE

         0          0          0 TT00  gap manager              IDLE

         0          0          0 TT01  redo transport timer     IDLE

         0          0          0 ARC0  archive local            IDLE

         0          0          0 ARC1  archive redo             IDLE

         0          0          0 ARC2  archive redo             IDLE

         0          0          0 ARC3  archive redo             IDLE

         0          0          0 MRP0  managed recovery         IDLE

         1        112        340 PR00  recovery logmerger       APPLYING_LOG

         0          0          0 PR01  recovery apply slave     IDLE

         0          0          0 PR02  recovery apply slave     IDLE

         2         35       9955 rfs   RFS async                RECEIVING

         0          0          0 rfs   RFS archive              IDLE

         2         35          0 rfs   RFS ping                 IDLE

         0          0          0 rfs   RFS archive              IDLE

         0          0          0 rfs   RFS archive              IDLE

         1        112      49170 rfs   RFS async                RECEIVING

         0          0          0 rfs   RFS archive              IDLE

         1        112          0 rfs   RFS ping                 IDLE

 

20 rows selected.

 

SQL> select name,db_unique_name,open_mode,database_role from gv$database;

 

NAME       DB_UNIQUE_NAME  OPEN_MODE            DATABASE_ROLE

---------- --------------- -------------------- ----------------

MASTER     masters         READ ONLY WITH APPLY PHYSICAL STANDBY

MASTER     masters         READ ONLY WITH APPLY PHYSICAL STANDBY

 SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.23.0.0.0

[oracle@dbsrv03 ~]$ srvctl status database -db masters

Instance masters1 is running on node dbsrv03

Instance masters2 is running on node dbsrv04

 

Comments

Post a Comment

Popular posts from this blog

How to Reposition Extract, Data Pump and Replicat in GoldenGate During Trail File Corruption

In this blog, we will walk you through the steps to reposition the Extract, Data Pump and Replicat processes in the event of Extract trail file corruption.   The environment consists of: Host: dbsrv01.localdomain: Source Database (19c) named "master" running on OEL 8.8, Extracting HR.REGIONS table using GoldenGate 21.3. Host: dbsrv02.localdomain: Target Database (19c) named "orcl" running on OEL 8.8, Replicating HR.REGIONS table using GoldenGate 21.3. Source (master) Target (orcl) 1. We have an integrated Extract running on the source database.   [oracle@dbsrv01 ~]$ ggsci   Oracle GoldenGate Command Interpreter for Oracle Version 21.3.0.0.0 OGGCORE_21.3.0.0.0_PLATFORMS_210728.1047_FBO Oracle Linux 7, x64, 64bit (optimized), Oracle Database 21c and lower supported versions on Jul 29 2021 03:59:23 Operating system character set identified as UTF-8.   Copyright (C) 1995, 2021, Oracle and/or its...
Read more

Real-Time data replication from Oracle to Cassandra using Oracle GoldenGate 21c

In this blog, we will guide you through the steps to set up data replication from an Oracle database to Apache Cassandra using Oracle GoldenGate 21c. This ensures real-time data synchronization between relational and NoSQL environments, enabling seamless data integration. The environment consists of: Source (Oracle Database): Hosts: dbsrv01.localdomain (192.168.56.81). Oracle database with (RU 19.23), database named "master". Operating System: Oracle Linux 8. Goldengate:21.3.0.0.0.   Target (Apache Cassandra): Hosts: dbsrv02.localdomain (192.168.56.82). Apache Cassandra 4.1, Keyspace named "XSPACE". Operating System: Oracle Linux 8. Goldengate:21.4.0.0.0. Oracle 19.23.0.0.0 (Source) Apache Cassandra 4.1 (Target) 1. Prepare the source database for GoldenGate capture by enabling archivelog mode, force logging, supplemental logging, configure streams pool memory and enable goldengate_replication parameter to tru...
Read more