Cloning Oracle 12c Database Using Active database Duplication
Steps for creating database using Active database Duplication:
1. Change the archive log mode :
If it is noarchivelog mode, switch to archivelog mode.
SQL> SHUTDOWN IMMEDIATE SQL> STARTUP MOUNT SQL> alter database archivelog Database altered. SQL> alter database open Database altered. SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination +DG01 Oldest online log sequence 299300 Next log sequence to archive 299305 Current log sequence 299305
2. Enable force logging mode:
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
3. Initialization Parameters:
Check the DB_NAME and DB_UNIQUE_NAME parameters. In this case they are both set to “prod” on the primary database.
The DB_NAME of the standby database will be the same as that of the primary, but it must have a different DB_UNIQUE_NAME value.
SQL> show parameter db_name; NAME TYPE VALUE -------- ----- ------ db_name string prod SQL> show parameter db_unique; NAME TYPE VALUE -------------- ----- ------ db_unique_name string prod
SQL> alter system set log_archive_config='DG_CONFIG=(prod,clone)' SCOPE=both sid='*'; System altered. SQL> alter system set log_archive_dest_1='LOCATION=/u01/app/oracle/oradata/prod/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prod' SCOPE=both sid='*'; System altered. SQL> alter system set log_archive_dest_2='SERVICE=clone LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=clone' SCOPE=both sid='*'; SQL> alter system set fal_server=prod SCOPE=both sid='*'; System altered. SQL> alter system set fal_client=clone SCOPE=both sid='*'; System altered. SQL> alter system set standby_file_management=auto SCOPE=both sid='*'; System altered. SQL> alter system set REMOTE_LOGIN_PASSWORDFILE=exclusive scope=spfile; System altered.
4. Listener Configuration in Target database:
[oracle@devserver ]$ export ORACLE_SID=clone [oracle@devserver ]$ export ORACLE_HOME=/oracle/app/oracle/product/12.1.0/dbhome_1 [oracle@devserver admin]$ cd $ORACLE_HOME/network/admin [oracle@devserver admin]$ cat listener.ora # listener.ora Network Configuration File: /oracle/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora # Generated by Oracle configuration tools. SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1) (SID_NAME = clone ) ) ) LISTENER_CLONE = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = devserver.localdomain.com)(PORT = 1521)) ) ) ADR_BASE_LISTENER = /u01/app/oracle
5. TNS Service Name Registration in Target database:
CLONE = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = devserver.localdomain.com)(PORT = 1521)) ) (CONNECT_DATA = (SID = CLONE) ) )
6. Create respective directories in Target Server:
[oracle@devserver admin]$ mkdir /oracle/app/oracle/oradata/clone/ctrl [oracle@devserver admin]$ mkdir /oracle/app/oracle/oradata/clone/data [oracle@devserver admin]$ mkdir /oracle/app/oracle/oradata/clone/logs [oracle@devserver admin]$ mkdir /oracle/app/oracle/oradata/clone/arch [oracle@devserver admin]$ mkdir /oracle/app/oracle/admin/clone/adump
7. Start listener in Target side:
[oracle@proddr01 admin] $lsnrctl start LISTENER_CLONE LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 28-JAN-2019 14:05:49 Copyright (c) 1991, 2014, Oracle. All rights reserved. Starting listener to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=devserver.localdomain.com)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER_CLONE Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production Start Date 03-DEC-2018 14:09:08 Uptime 55 days 23 hr. 56 min. 40 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /oracle/app/oracle/product/12.1.0/db_1/network/admin/listener.ora Listener Log File /oracle/app/oracle/diag/tnslsnr/devserver/listener_clone/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=devserver.localdomain.com)(PORT=1521))) Services Summary... Service "clone" has 1 instance(s). Instance "clone", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully
8. Copying password & parameter file to standby server:
- After copying pfile,only keep the parameter entry in PFILE:
db_name
[oracle@devserver ]$ cd $ORACLE_HOME/dbs [oracle@devserver dbs]$ scp initprod.ora orapwprod oracle@devserver:/oracle/app/oracle/product/12.1.0/dbhome_1/dbs oracle@devserver's password: initprod.ora 100% 1536 1.5KB/s 00:00 orapwprod 100% 1536 1.5KB/s 00:00 [oracle@proddr01 dbs]$ mv orapwprod orapwclone [oracle@proddr01 dbs]$ cat initprod.ora db_name='clone'
9. Check connectivity between primary and standby side:
[oracle@devserver ]$ tnsping prod [In boths the nodes] [oracle@devserver ]$ tnsping clone [In boths the nodes]
10. Clone Database Creation:
Startup in nomount stage :
[oracle@proddr01 ]$ export ORACLE_SID=clone [oracle@proddr01 ]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.1.0 Production on Thu Jan 29 01:12:25 2019 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to an idle instance. SQL> startup nomount ORACLE instance started. Total System Global Area 217157632 bytes Fixed Size 2211928 bytes Variable Size 159387560 bytes Database Buffers 50331648 bytes Redo Buffers 5226496 bytes
11. Connect RMAN to create target database:
[oracle@proddr01 ]$ rman target sys/****@prod auxiliary sys/****@clone
Recovery Manager: Release 12.1.0.2.0 - Production on Sun Jan 30 20:15:10 2019 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD (DBID=1459429229)
connected to auxiliary database: PROD (not mounted)
RMAN> run
{
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
allocate auxiliary channel stby type disk;
duplicate target database for standby from active database
spfile
parameter_value_convert 'prod','proddr'
set db_file_name_convert='+DG01/prod/datafile','/oradata1/clone/data'
set db_unique_name='clone'
set cluster_database='false'
set log_file_name_convert='+DG01/prod/onlinelog','/oradata1/clone/logs'
set control_files='/oracle/app/oracle/oradata/clone/ctrl/control.ctl'
set fal_client='clone'
set fal_server='prod'
set audit_file_dest='/oracle/app/oracle/admin/proddr/adump'
set log_archive_config='dg_config=(clone,prod)'
set log_archive_dest_1='location=location=/oradata1/prod/arch'
set log_archive_dest_2='service=prod async valid_for=(online_logfiles,primary_role) db_unique_name=prod'
set sga_target='50GB'
set sga_max_size='50GB'
set undo_tablespace='UNDOTBS1'
nofilenamecheck;
}
using target database control file instead of recovery catalog
allocated channel: prmy1
channel prmy1: SID=42 device type=DISK
allocated channel: prmy2
channel prmy2: SID=36 device type=DISK
allocated channel: prmy3
channel prmy3 : SID=45 device type=DISK
allocated channel: prmy4
channel prmy4 : SID=45 device type=DISK
allocated channel: stby
channel stby: SID=20 device type=DISK
Starting Duplicate Db at 30-JAN-19
.
.
.
.
.
Finished Duplicate Db at 30-JAN-19
released channel: prmy1
released channel: prmy2
released channel: prmy3
released channel: prmy4
released channel: stby
RMAN>
12. Disable Archivelog mode in Target database:
SQL> shut immediate SQL> startup mount SQL> alter database noarchivelog Database altered. SQL> alter database open Database altered.