[Oracle] Duplicate from active

Hello everybody,

today we are going to talk about a very useful procedure, duplicate from active database. In this way you can duplicate your database to another database, that can be on the same host or on remote host.

Let’s start!

First of all, you need to create a copy of the pfile/spfile of your active database:

SQL> CREATE PFILE='/home/oracle/Vito/pfile${DB_SOURCE}.ora' FROM SPFILE;

And you need to modify it, changing database name and setting the parameter for changing the name of datafiles:

$ vi /home/oracle/Vito/pfile${DB_SOURCE}.ora
*.control_files='${DG_TARGET}/${DB_TARGET}/control01.ctl','${DG_TARGET}/${DB_TARGET}/control02.ctl'
*.db_file_name_convert='${DG_SOURCE}/${DB_SOURCE}','${DG_TARGET}/${DB_TARGET}'
${DB_SOURCE}2.instance_number=2
${DB_SOURCE}1.instance_number=1
${DB_TARGET}2.thread=2
${DB_TARGET}1.thread=1
${DB_TARGET}2.undo_tablespace='UNDOTBS2'
${DB_TARGET}1.undo_tablespace='UNDOTBS1'

Now you can get the size of the active database:

SQL> SELECT 
ROUND(( SELECT SUM(BYTES)/1024/1024/1024 DATA_SIZE FROM DBA_DATA_FILES ) +
( SELECT NVL(SUM(BYTES),0)/1024/1024/1024 TEMP_SIZE FROM DBA_TEMP_FILES ) +
( SELECT SUM(BYTES)/1024/1024/1024 REDO_SIZE FROM SYS.V_$LOG ) +
( SELECT SUM(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 CONTROLFILE_SIZE FROM V$CONTROLFILE),2) "SIZE IN GB"
FROM DUAL;
SIZE IN GB
----------
      85.2

Now you need to define the entry in the /etc/oratab:

$ vi /etc/oratab
${DB_TARGET}1:/sw/oracle/product/12c/db:N               # line added by Vito
${DB_TARGET}:/sw/oracle/product/12c/db:N               # line added by Vito

After this, you need to verify the password file and set the use of large pages:

SQL> SHOW PARAMETER REMOTE_LOGIN_PASSWORDFILE
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile            string      EXCLUSIVE
ALTER SYSTEM SET USE_LARGE_PAGES=TRUE SCOPE=SPFILE SID='*';

If it is not exclusive/online, set to it. After this, you need to copy the password fil efrom the source hostname to all the machines that are in the cluster of the target db:

scp ${SOURCE_OH}/dbs/orapw${DB_SOURCE}1 ${TARGET_HOSTNAME}:${TARGET_OH}/dbs/orapw${DB_TARGET}1
 scp $ORACLE_HOME/dbs/orapw${DB_SOURCE}1 ${TARGET_HOSTNAME2}:${TARGET_OH}/dbs/orapw${DB_TARGET}2

Now you need to define a static entry on listener of the machine where you’re going to place your secondary db and reload the listener.

$ vi listener.ora
#you've to add this entry on your static listener, for example
SID_LIST_LISTENER=
 (SID_LIST =
   (SID_DESC =
      (GLOBAL_DBNAME = ${DB_TARGET})
        (ORACLE_HOME = /sw/oracle/product/12c/db)
      (SID_NAME = ${DB_TARGET}1)
    )
 )

$ lsnrctl reload LISTENER

After this, you’ve to check that the new entry is added and the status is UNKNOWN:

$ lsnrctl status LISTENER

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 25-OCT-2019 19:54:22

Copyright  1991, 2016, Oracle.  All rights reserved.

Connecting to 
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                06-JUL-2019 16:35:35
Uptime                    111 days 3 hr. 18 min. 46 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
.
.
.
Listening Endpoints Summary...
  
Services Summary...
Service "${DB_TARGET}" has 1 instance.
  Instance "${DB_TARGET}1", status UNKNOWN, has 1 handler for this service...
The command completed successfully

Now we can start the secondary instance, creating all the directory:

$ cd /sw/oracle/admin
$ mkdir ${DB_TARGET}
$ chmod 750 ${DB_TARGET}
$ cd ${DB_TARGET}
$ mkdir adump
$ . oraenv <<< ${DB_TARGET}1
$ sqlplus / as sysdba
SQL> STARTUP PFILE='/home/oracle/Vito/pfile${DB_TARGET}.ora' NOMOUNT;
SQL> create SPFILE='${DG_TARGET}' from pfile='/home/oracle/Vito/pfile${DB_TARGET}.ora';

Then you need to check on your ASM where the spfile has been created and modify the init.ora:

$ vi ${TARGET_OH}/dbs/init${DB_TARGET}1.ora
SPFILE='${DG_TARGET}/${DB_TARGET}/PARAMETERFILE/spfile.409.1035618463'

Set the primary db in archivelog:

SQL> alter database mount force;
 SQL> alter database archivelog;
 SQL> alter database startup force;

Now you can start the rman client and start the duplicate:

$ rman
RMAN> connect TARGET ${usr}/${psw}@${source_hostname}:${port}/${DB_SOURCE}
RMAN> connect AUXILIARY ${usr}/${psw}@${target_hostname}:${port}/${DB_TARGET}

run {
allocate auxiliary channel a1 type disk;
allocate auxiliary channel a2 type disk;
allocate auxiliary channel a3 type disk;
allocate auxiliary channel a4 type disk;
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;

duplicate target database to ${DB_TARGET} from active database;

release channel a1;
release channel a2;
release channel a3;
release channel a4;
}

After this, you need to copy the previous init.ora to all the machines of the target cluster:

$ scp ${TARGET_OH}/dbs/init${DB_TARGET}1.ora ${TARGET_MACHINE2}:${TARGET_OH}/dbs/init${DB_TARGET}2.ora

Now you can add your db to the cluster:

$ srvctl add database -d ${DB_TARGET} -oh ${TARGET_OH} -spfile ${DG_TARGET}/${DB_TARGET}/PARAMETERFILE/spfile.409.1035618463
$ srvctl add instance -d ${DB_TARGET} -i ${DB_TARGET}1 -n ${TARGET_MACHINE}
$ srvctl add instance -d ${DB_TARGET} -i ${DB_TARGET}2 -n ${TARGET_MACHINE2}
$ srvctl start database -d ${DB_TARGET}
$ srvctl stop database -d ${DB_TARGET}
$ srvctl add service -d ${DB_TARGET} -s S${DB_TARGET} -preferred ${DB_TARGET}1,${DB_TARGET}2

Finish, now you have your duplicated database in cluster.

You can use this procedure, for example, to keep up to date non-productional environment and let developers to work always on updated data.

Hope that will help you.

Regards!