How to Duplicate a 19c Physical Standby Database

You don't have to create an empty database on the standby server for building a standby database. In this post, I will show you how to duplicate a 19c physical standby database from scratch by the following sections.

  1. Password File
  2. File Management Mode
  3. Parameter File
  4. Necessary Directories
  5. Name Resolution
  6. Connect Identifiers
  7. Listeners
  8. Force Logging
  9. RMAN Duplication
  10. Rename Online Redo Logs
  11. /etc/oratab

1. Password File

Just copy the password file from the primary and rename it.

[oracle@standby-19c ~]$ scp -p primary-19c:$ORACLE_HOME/dbs/orapwPRIMDB $ORACLE_HOME/dbs/orapwSTANDB
oracle@primary-19c's password:
orapwPRIMDB                                   100% 2048   950.7KB/s   00:00

2. File Management Mode

For convenience, we can change standby file management mode from MANUAL into AUTO for creating data files on the standby database automatically in the future.

[oracle@primary-19c ~]$ sqlplus / as sysdba
...
SQL> alter system set standby_file_management=auto scope=both;

System altered.

3. Parameter File

We created a plain-texted parameter file on the primary database for shipping to the standby one.

SQL> create pfile from spfile;

File created.

Just copy the parameter file from the primary and rename as we wanted.

[oracle@standby-19c ~]$ scp -p primary-19c:$ORACLE_HOME/dbs/initPRIMDB.ora $ORACLE_HOME/dbs/initSTANDB.ora
oracle@primary-19c's password:
initPRIMDB.ora                                100% 1189     1.2MB/s   00:00

Then we modified the parameter file. That is, change all DB_UNIQUE_NAME, PRIMDB into STANDB.

[oracle@standby-19c ~]$ vi $ORACLE_HOME/dbs/initSTANDB.ora
STANDB.__data_transfer_cache_size=0
STANDB.__db_cache_size=520093696
STANDB.__inmemory_ext_roarea=0
STANDB.__inmemory_ext_rwarea=0
STANDB.__java_pool_size=16777216
STANDB.__large_pool_size=16777216
STANDB.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
STANDB.__pga_aggregate_target=620756992
STANDB.__sga_target=922746880
STANDB.__shared_io_pool_size=50331648
STANDB.__shared_pool_size=301989888
STANDB.__streams_pool_size=0
STANDB.__unified_pga_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/COMPDB/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/u01/app/oracle/oradata/COMPDB/control01.ctl','/u01/app/oracle/fast_recovery_area/COMPDB/control02.ctl'
*.db_block_size=8192
*.db_name='COMPDB'
*.db_unique_name='STANDB'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=10g
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=STANDBXDB)'
*.local_listener='LISTENER_STANDB'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=1470m
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.processes=500
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'

Then we create SPFILE from the parameter file and startup nomount.

[oracle@standby-19c ~]$ sqlplus / as sysdba
...
SQL> create spfile from pfile;

File created.

Yes, SPFILE can be created in idle state if you have set ORACLE_SID environment variable correctly. Automatically, SPFILE will go to the default location if no location specified in the command.

NOMOUNT Standby

We should startup the standby database to NOMOUNT state for later duplication through RMAN.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1543500144 bytes
Fixed Size                  8896880 bytes
Variable Size             956301312 bytes
Database Buffers          570425344 bytes
Redo Buffers                7876608 bytes

Can we startup the standby database to MOUNT? No, we can't, because we don't have any control file so far. For further clarifying some startup concepts, I think you might be interested in knowing more about how to start Oracle database correctly.

4. Necessary Directories

According to the parameter file, there're several directories that we should created in advance.

[oracle@standby-19c ~]$ mkdir -p /u01/app/oracle/admin/COMPDB/adump
[oracle@standby-19c ~]$ mkdir -p /u01/app/oracle/oradata/COMPDB
[oracle@standby-19c ~]$ mkdir -p /u01/app/oracle/fast_recovery_area/COMPDB

5. Name Resolution

Just make sure we have correct hostname resolution in /etc/hosts on both sides.

[oracle@primary-19c ~]$ cat /etc/hosts
...
10.10.1.1 primary-19c primary-19c.example.com
10.20.1.1 standby-19c standby-19c.example.com
[oracle@standby-19c ~]$ cat /etc/hosts
...
10.10.1.1 primary-19c primary-19c.example.com
10.20.1.1 standby-19c standby-19c.example.com

6. Connect Identifiers

Just make sure we have correct connect identifiers in tnsnames.ora on both sides.

[oracle@primary-19c ~]$ cat $ORACLE_HOME/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

PRIMDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = primary-19c)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PRIMDB)(UR=A)
    )
  )

STANDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = standby-19c)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = STANDB)(UR=A)
    )
  )

LISTENER_PRIMDB =
  (ADDRESS = (PROTOCOL = TCP)(HOST = primary-19c)(PORT = 1521))
[oracle@standby-19c ~]$ cat $ORACLE_HOME/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

PRIMDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = primary-19c)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PRIMDB)(UR=A)
    )
  )

STANDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = standby-19c)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = STANDB)(UR=A)
    )
  )

LISTENER_STANDB =
  (ADDRESS = (PROTOCOL = TCP)(HOST = standby-19c)(PORT = 1521))

Did you see that? I used UR=A in CONNECT_DATA for connecting to a database which is in idle, nomount or restricted externally.

7. Listeners

Make sure listeners are running on both sides.

[oracle@primary-19c ~]$ lsnrctl status
...
Services Summary...
Service "PRIMDB" has 1 instance(s).
  Instance "PRIMDB", status READY, has 1 handler(s) for this service...
Service "PRIMDBXDB" has 1 instance(s).
  Instance "PRIMDB", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@standby-19c ~]$ lsnrctl status
...
Services Summary...
Service "STANDB" has 1 instance(s).
  Instance "STANDB", status BLOCKED, has 1 handler(s) for this service...
The command completed successfully

BLOCKED is a listener state which means no one can connect to a NOMOUNT database unless some special conditions.

8. Force Logging

We have to enable FORCE_LOGGING to make the primary database log every change, no matter what. That is, this feature will suppress all NOLOGGING in every level in the database. Now let's check the status of FORCE_LOGGING by querying V$DATABASE.

On Primary Database.

SQL> select force_logging from v$database;

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

Then we enable it.

SQL> alter database force logging;

Database altered.

Check the status again.

SQL> select force_logging from v$database;

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

Not only data guard, using GoldenGate to synchronize both sides also requires FORCE_LOGGING to be enabled.

9. RMAN Duplication

Do you notice that? We have not copy the control file to the standby server yet. This is because it will be done in this step.

If everything is ready, we can duplicate the database to the standby server.

[oracle@primary-19c ~]$ rman target sys@primdb auxiliary sys@standb
...
target database Password:
connected to target database: COMPDB (DBID=1095872693)
auxiliary database Password:
connected to auxiliary database: COMPDB (not mounted)

RMAN> run {
  allocate channel d1 type disk;
  allocate channel d2 type disk;
  allocate auxiliary channel a1 type DISK;
  allocate auxiliary channel a2 type DISK;
  duplicate target database for standby from active database nofilenamecheck dorecover;
}
2> 3> 4> 5> 6> 7>
using target database control file instead of recovery catalog
allocated channel: d1
channel d1: SID=29 device type=DISK

allocated channel: d2
channel d2: SID=220 device type=DISK

allocated channel: a1
channel a1: SID=591 device type=DISK

allocated channel: a2
channel a2: SID=11 device type=DISK

Starting Duplicate Db at 07-AUG-19
current log archived

contents of Memory Script:
{
   backup as copy reuse
   passwordfile auxiliary format  '/u01/app/oracle/product/19.0.0/dbhome_1/dbs/orapwSTANDB'   ;
}
executing Memory Script

Starting backup at 07-AUG-19
Finished backup at 07-AUG-19
duplicating Online logs to Oracle Managed File (OMF) location

contents of Memory Script:
{
   restore clone from service  'primdb' standby controlfile;
}
executing Memory Script

Starting restore at 07-AUG-19

channel a1: starting datafile backup set restore
channel a1: using network backup set from service primdb
channel a1: restoring control file
channel a1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/COMPDB/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/COMPDB/control02.ctl
Finished restore at 07-AUG-19

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

sql statement: alter database mount standby database
RMAN-05158: WARNING: auxiliary (datafile) file name /u01/app/oracle/oradata/COMPDB/system01.dbf conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (datafile) file name /u01/app/oracle/oradata/COMPDB/sysaux01.dbf conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (datafile) file name /u01/app/oracle/oradata/COMPDB/undotbs01.dbf conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (datafile) file name /u01/app/oracle/oradata/COMPDB/users01.dbf conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (tempfile) file name /u01/app/oracle/oradata/COMPDB/temp01.dbf conflicts with a file used by the target database

contents of Memory Script:
{
   set newname for tempfile  1 to
 "/u01/app/oracle/oradata/COMPDB/temp01.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to
 "/u01/app/oracle/oradata/COMPDB/system01.dbf";
   set newname for datafile  3 to
 "/u01/app/oracle/oradata/COMPDB/sysaux01.dbf";
   set newname for datafile  4 to
 "/u01/app/oracle/oradata/COMPDB/undotbs01.dbf";
   set newname for datafile  7 to
 "/u01/app/oracle/oradata/COMPDB/users01.dbf";
   restore
   from  nonsparse   from service
 'primdb'   clone database
   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

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

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 07-AUG-19

channel a1: starting datafile backup set restore
channel a1: using network backup set from service primdb
channel a1: specifying datafile(s) to restore from backup set
channel a1: restoring datafile 00001 to /u01/app/oracle/oradata/COMPDB/system01.dbf
channel a2: starting datafile backup set restore
channel a2: using network backup set from service primdb
channel a2: specifying datafile(s) to restore from backup set
channel a2: restoring datafile 00003 to /u01/app/oracle/oradata/COMPDB/sysaux01.dbf
channel a2: restore complete, elapsed time: 00:00:16
channel a2: starting datafile backup set restore
channel a2: using network backup set from service primdb
channel a2: specifying datafile(s) to restore from backup set
channel a2: restoring datafile 00004 to /u01/app/oracle/oradata/COMPDB/undotbs01.dbf
channel a1: restore complete, elapsed time: 00:00:32
channel a1: starting datafile backup set restore
channel a1: using network backup set from service primdb
channel a1: specifying datafile(s) to restore from backup set
channel a1: restoring datafile 00007 to /u01/app/oracle/oradata/COMPDB/users01.dbf
channel a2: restore complete, elapsed time: 00:00:16
channel a1: restore complete, elapsed time: 00:00:01
Finished restore at 07-AUG-19

sql statement: alter system archive log current
current log archived

contents of Memory Script:
{
   restore clone force from service  'primdb'
           archivelog from scn  2076215;
   switch clone datafile all;
}
executing Memory Script

Starting restore at 07-AUG-19

channel a1: starting archived log restore to default destination
channel a1: using network backup set from service primdb
channel a1: restoring archived log
archived log thread=1 sequence=7
channel a2: starting archived log restore to default destination
channel a2: using network backup set from service primdb
channel a2: restoring archived log
archived log thread=1 sequence=8
channel a1: restore complete, elapsed time: 00:00:01
channel a2: restore complete, elapsed time: 00:00:01
Finished restore at 07-AUG-19

datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=1015714014 file name=/u01/app/oracle/oradata/COMPDB/system01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=1015714014 file name=/u01/app/oracle/oradata/COMPDB/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=1015714014 file name=/u01/app/oracle/oradata/COMPDB/undotbs01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=4 STAMP=1015714014 file name=/u01/app/oracle/oradata/COMPDB/users01.dbf

contents of Memory Script:
{
   set until scn  2088975;
   recover
   standby
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 07-AUG-19

starting media recovery

archived log for thread 1 with sequence 7 is already on disk as file /u01/app/oracle/fast_recovery_area/STANDB/archivelog/2019_08_07/o1_mf_1_7_gnos2wwp_.arc
archived log for thread 1 with sequence 8 is already on disk as file /u01/app/oracle/fast_recovery_area/STANDB/archivelog/2019_08_07/o1_mf_1_8_gnos2wyw_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/STANDB/archivelog/2019_08_07/o1_mf_1_7_gnos2wwp_.arc thread=1 sequence=7
archived log file name=/u01/app/oracle/fast_recovery_area/STANDB/archivelog/2019_08_07/o1_mf_1_8_gnos2wyw_.arc thread=1 sequence=8
media recovery complete, elapsed time: 00:00:02
Finished recover at 07-AUG-19

contents of Memory Script:
{
   delete clone force archivelog all;
}
executing Memory Script

deleted archived log
archived log file name=/u01/app/oracle/fast_recovery_area/STANDB/archivelog/2019_08_07/o1_mf_1_8_gnos2wyw_.arc RECID=1 STAMP=1015714013
Deleted 1 objects

deleted archived log
archived log file name=/u01/app/oracle/fast_recovery_area/STANDB/archivelog/2019_08_07/o1_mf_1_7_gnos2wwp_.arc RECID=2 STAMP=1015714013
Deleted 1 objects

Finished Duplicate Db at 07-AUG-19
released channel: d1
released channel: d2
released channel: a1
released channel: a2

Let's check the standby database.

[oracle@standby-19c ~]$ sqlplus / as sysdba
...
SQL> select name, open_mode, database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
COMPDB    MOUNTED              PHYSICAL STANDBY

Do you see that? RMAN mounted our standby database! This is because RMAN first duplicate the standby control file to the standby server and then mounted the database for further duplication.

For more strategies on standby database duplication, you may check: Duplicate a Standby Database - An Overview.

10. Rename Online Redo Logs

We succeeded to duplicate a 19c standby database. However, some configuration were not right.

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/fast_recovery_area/STANDB/onlinelog/o1_mf_3_gnos363m_.log
/u01/app/oracle/fast_recovery_area/STANDB/onlinelog/o1_mf_2_gnos343t_.log
/u01/app/oracle/fast_recovery_area/STANDB/onlinelog/o1_mf_1_gnos31k2_.log

As you can see, it looks like that RMAN duplicate online redo logs as Oracle-Managed Files (OMF) on the standby database.

Although they won't affect later data guard operations, you can keep it or fix it by moving and renaming them in order to build a symmetric architecture of data guard.

SQL> !mv /u01/app/oracle/fast_recovery_area/STANDB/onlinelog/o1_mf_1_gnos31k2_.log /u01/app/oracle/oradata/COMPDB/redo01.log

SQL> !mv /u01/app/oracle/fast_recovery_area/STANDB/onlinelog/o1_mf_2_gnos343t_.log /u01/app/oracle/oradata/COMPDB/redo02.log

SQL> !mv /u01/app/oracle/fast_recovery_area/STANDB/onlinelog/o1_mf_3_gnos363m_.log /u01/app/oracle/oradata/COMPDB/redo03.log

SQL> alter database rename file '/u01/app/oracle/fast_recovery_area/STANDB/onlinelog/o1_mf_1_gnos31k2_.log' to '/u01/app/oracle/oradata/COMPDB/redo01.log';

Database altered.

SQL> alter database rename file '/u01/app/oracle/fast_recovery_area/STANDB/onlinelog/o1_mf_2_gnos343t_.log' to '/u01/app/oracle/oradata/COMPDB/redo02.log';

Database altered.

SQL> alter database rename file '/u01/app/oracle/fast_recovery_area/STANDB/onlinelog/o1_mf_3_gnos363m_.log' to '/u01/app/oracle/oradata/COMPDB/redo03.log';

Database altered.

Some more advanced topics about moving parameter files, control files, redo log files and data files can be found at How to Move Oracle Database.

Let's check online redo logs again.

SQL> column group# format 9;
SQL> column member format a50;
SQL> select group#, type, member from v$logfile order by 1;

GROUP# TYPE    MEMBER
------ ------- --------------------------------------------------
     1 ONLINE  /u01/app/oracle/oradata/COMPDB/redo01.log
     2 ONLINE  /u01/app/oracle/oradata/COMPDB/redo02.log
     3 ONLINE  /u01/app/oracle/oradata/COMPDB/redo03.log

Good, we keep them symmetric between both sides.

11. /etc/oratab

One last thing, since we did not create the database through DBCA, we should add an entry to /etc/oratab manually for declaring the standby database.

[oracle@standby-19c ~]$ vi /etc/oratab
...
STANDB:/u01/app/oracle/product/19.0.0/dbhome_1:Y

Next, it's time to configure 19c data guard broker for data synchronization.

2 thoughts on “How to Duplicate a 19c Physical Standby Database”

  1. Hi,
    I try to duplicate oracle 19 db, but failed to drop/recreate redolog on the standby.
    You made the choise to move and rename the redo. What about drop online redolog and recrete them ? I try this as it was working on 12.2, but it failed as one online redolog on th estandby is stil actif (CURRENT) !
    > select thread#, group#, sequence#, status, bytes from v$log;
    THREAD# GROUP# SEQUENCE# STATUS BYTES
    ———- ———- ———- —————- ———-
    1 2 0 CURRENT 104857600

    SQL> alter database clear logfile group 2;

    Database altered.

    SQL> alter database drop logfile group 2;
    alter database drop logfile group 2
    *
    ERROR at line 1:
    ORA-01623: log 2 is current log for instance DB196 (thread 1) – cannot drop
    ORA-00312: online log 2 thread 1: ‘/oradata/fra/DB196_C2/onlinelog/o1_mf_2_h1xyo8fs_.log’

    Any idea to avoid this ?
    Regards
    Olivier

    1. My best guess is that the standby control file keeps the status of online redo log files, and you tried to create a new one to replace it. Maybe you should use alter system archive log current to make it switch to INACTIVE. Please check the status before dropping it.

      Dropping / Creating redo logs should be taken care of cautiously, you may check the post: How to Resize Redo Logs in Oracle.

Leave a Reply

Your email address will not be published. Required fields are marked *