[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!
ENVIRONMENT
- OS: Oracle Linux 7
- DB version: Oracle 12.2
VARIABLES
- DB_SOURCE: The name of the active database
- DB_TARGET: The name of the target db
- DG_SOURCE: The name of source diskgroup
- DG_TARGET: The name of target diskgroup
- SOURCE_HOSTNAME: The name of the machine where the active database is located
- TARGET_HOSTNAME: The name of the machine where the target database is located
- USR: usually system
- PWD: password of system
- SOURCE_OH: Oracle home of the active database
- TARGET_OH: Oracle home of target database
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!