Cloning Oracle 12c Database Using Active database Duplication :-

To create a Physical Standby database using RMAN DUPLICATE FROM ACTIVE DATABASE feature which is now available in 11g Release 1 onwards. This enables us to create a physical standby database without having to take a backup of the primary database as a prerequisite step.

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.