Data Guard

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

Password File

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

[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

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.

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 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.

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

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

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

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 databases in idle, nomount or restricted externally.

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

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.

On Primary Database.

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

RMAN Duplication

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

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 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.

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.

/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.

Leave a Reply

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