Active database cloning from target to source – release 19c.

Creation of Duplicate database using the RMAN

• Starting from 11G you can proceed with the duplicate database in 2 ways – Active cloning and backup-based cloning.
• Now we are going to drive with active cloning with of Rman.

Advantage of Active cloning: Active cloning will helps duplication of live data from source to target side over the network to the auxiliary destination and then creates the duplicate database. The only requirement is you should have R-man backup or copies of live data. Rest will be taken care by auxiliary channel. High Level steps: A) Both active database and auxiliary database should be ping to preform the activity over the network. B) Copy the password file and Pfile through SCP form active(Source) to auxiliary(target) C) Startup the auxiliary with nomount stage and create the Spfile and the duplicate database with Spfile in nomount stage. D ) Creates a control file for the duplicate database with RMAN over the network. E) Restores from backup or copies from the active database the latest control file that satisfies the UNTIL clause requirements. F) Restarts the auxiliary(Target) instance and mounts the duplicate control file. G) Creates the duplicate datafiles and recovers them with incremental backups and archived redo logs H) Opens the duplicate database with the RESETLOGS option. The below figure, will helps you for better understanding how the source and duplicate database connecting via RMAN.

Step : 1

Check both side source and duplicate side IP address and hostname update that into the /etc/hosts

To change the hostname from target side you can use below command.

   

[root@prod21 ~]#  hostnamectl set-hostname _________

Step 2:

Here the Active database name – new19c and duplication database name – newclone 19c

Go the listener path

>>> cd $ORACLE_HOME/network/admin

>>> Vi listener.ora

change the hostname to ip address

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = new19c)
(ORACLE_HOME = /u02/app/oracle/product/19.0.0/dbhome_1)
(SID_NAME = new19c)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.25.128)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL=IPC) KEY = EXTPROD1521))
)

Step 3 :

From source open the tnsnames.ora and do the necessary changes

>>> cd $ORACLE_HOME/network/admin

>>> Vi tnsname.ora

[oracle@prod21 admin]$ cat tnsnames.ora

TESTCLONE =

(DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = prod21.com)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME =testclone)

    )

LISTENER_TESTCLONE =

(ADDRESS = (PROTOCOL = TCP)(HOST = prod21.com)(PORT = 1521)) 

NEW19C =
(DESCREIPTION =

(ADDRESS = (PROTOCOL =TCP) (HOST = 192.168.25.128) (port =1521))

(CONNECT_DATA

(SERVER =DEDICATED)

(SERVICE_NAME=new19c)

)

)

NEWCLONE19C =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.25.129)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = NEW19C)

    )

)

# tnsnames.ora Network Configuration File: /u02/app/oracle/product/19.0.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

Step 4 : You can create listner and tnsname via two ways in target side.

A) Netmgr or Netca

B) SCP from source to target.
we are going with second option scp from source to target side

From the target side move the tnsname.ora as tnsname.ora_bkp and do the SCP from source to target

[oracle@prod21 admint] scp tnsnames.ora oracle@targetip /targetadmin path

Step 5 :

Ping the tnsping from both side with database name

Tnsping target databasename

Tnsping source databasename

Do it  both the side

if there any Problem in tnsping you need to stop the firewall with below command from root user

service iptables stop

Step :6

Copy pfile and password file from source to target.

[oracle@prod21 dbs] scp orapwdatabasename initdatabasename oracle@sourceipaddress:target dbs path

 

Step 7:

Check the target path, whether copy files are applied

ls

Step 8:

Create necessary directories by looking into the pfile in target location.

Adump

Fastrecovery area

Oradata

Step 9:

Connect the target database in nomount stage with copied source pfile.

SQL > startup pfile=’path/initdatabasename.ora’ nomount:

Step 10:

[oracle@prod2 admin]$  Create spfile from pfile;

Then go with shut immediate

Step 11:

Then startup the database with spfile in nomount stage

[oracle@prod2 admin]$  Show parameter spfile;

Step 12 : For checking

Connect the source database with target  rman

[oracle@prod2 admin]$ rman target sys/oracle@new19c

Step 13: For checking

Give exit and connect with the below command

Rman Target /You can see connect to target database : database name (not mounted)

Step 14:

Again give exit and connect the source database from target server with below command.

Make sure target database should be in nomount stage and  source database should be in open stage. Listener should be up for both side

Connect target system name /password@sourcedatabasename auxiliary targetsystem/password

[oracle@prod2 admin]$ rman target sys/oracle@new19c auxiliary sys/oracle

Step 15:

Now you can user below command to clone from source to target with rman.RMAN>  duplicate database to ‘new19c’ from active database NOFILENAMECHECK;

Rman automatically perform :

1. Rman Restores from active database backup and copies the latest control file that stratifies.
2. Above steps mounts the auxiliary instance by using the restored control file or the backup control file that is copied from the active database.
3. Use metadata in the RMAN repository to select the backups that are used to restore the data files to the auxiliary instance. This step applies to backup-based duplication
4. Copy the duplicate database files to the destination host and restores them to a noncurrent point in time by using incremental backups and archived redo log files.
5. Shut down and restarts the auxiliary database instance on the destination host in NOMOUNT mode.
6. Create a new control file, which then creates and stores the new DBID in the data files.

Starting Duplicate Db at 02-AUG-07
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=97 device type=DISK

contents of Memory Script:
{
set newname for datafile 1 to “u02\APP\ORACLE\ORADATA\NEW19C\SYSTEM01.DBF”;
set newname for datafile 2 to “u02\APP\ORACLE\ORADATA\NEW19C\SYSAUX01.DBF”;
set newname for datafile 3 to “u02\APP\ORACLE\ORADATA\NEW19C\UNDOTBS01.DBF”;
set newname for datafile 4 to “u02\APP\ORACLE\ORADATA\NEW19C\USERS01.DBF”;
backup as copy reuse
datafile 1 auxiliary format “u02\APP\ORACLE\ORADATA\NEW19C\SYSTEM01.DBF”
datafile 2 auxiliary format “u02\APP\ORACLE\ORADATA\NEW19C\SYSAUX01.DBF”
datafile 3 auxiliary format “u02\APP\ORACLE\ORADATA\NNEW19C\UNDOTBS01.DBF”
datafile 4 auxiliary format “u02\APP\ORACLE\ORADATA\NEW19C\USERS01.DBF” ;
sql ‘alter system archive log current’;
}
executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME

Starting backup at 02-AUG-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=123 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=U02\APP\ORACLE\ORADATA\NEW19C\SYSTEM01.DBF
output file name=U02\APP\ORACLE\ORADATA\NEW19C\SYSTEM01.DBF tag=TAG20070802T114254 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:08:22
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=U02\APP\ORACLE\ORADATA\NEW19C\SYSAUX01.DBF
output file name=U02\ORACLE\ORADATA\NEW19C\SYSAUX01.DBF tag=TAG20070802T114254 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:05:59
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=U02\APP\ORACLE\ORADATA\NEW19C\UNDOTBS01.DBF
output file name=U02\APP\ORACLE\ORADATA\NEW19C\UNDOTBS01.DBF tag=TAG20070802T114254 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:57
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=U02\APP\ORACLE\ORADATA\NEW19C\USERS01.DBF
output file name=U02\ORACLE\ORADATA\NEW19C\USERS01.DBF tag=TAG20070802T114254 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 02-AUG-07

sql statement: alter system archive log current
sql statement: CREATE CONTROLFILE REUSE SET DATABASE “NEW19C” RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( U02\APP\ORACLE\ORADATA\NEW19C\REDO01.LOG’ ) SIZE 50 M REUSE,
GROUP 2 ( U02\APP\ORACLE\ORADATA\NEW19C\REDO02.LOG’ ) SIZE 50 M REUSE,
GROUP 3 ( U02\APP\ORACLE\ORADATA\NEW19C\REDO03.LOG’ ) SIZE 50 M REUSE
DATAFILE
‘U02\ORACLE\ORADATA\NEW19C\SYSTEM01.DBF’
CHARACTER SET AL32UTF8

contents of Memory Script:
{
backup as copy reuse
archivelog like “U02\APP\ORACLE\PRODUCT\19.0.0\DB_1\RDBMS\ARC00042_0629061547.001” auxiliary format
“U02\APP\ORACLE\PRODUCT\19.0.0\DB_1\RDBMS\ARC00042_0629061547.001” archivelog like
“U02\APP\ORACLE\FLASH_RECOVERY_AREA\NEWCLONE19C\ARCHIVELOG\2007_08_02\O1_MF_1_42_3C2YJNP7_.ARC” auxiliary format
“U02\APP\ORACLE\PRODUCT\19.0.0\DB_1\RDBMS\ARC00042_0629061547.001” ;
catalog clone archivelog “U02\APP\ORACLE\PRODUCT\19.0.0\DB_1\RDBMS\ARC00042_0629061547.001”;
catalog clone archivelog “U02\APP\ORACLE\PRODUCT\19.0.0\DB_1\RDBMS\ARC00042_0629061547.001”;
switch clone datafile all;
}
executing Memory Script

Starting backup at 02-AUG-07
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=42 RECID=35 STAMP=629553646
output file name=U02\APP\ORACLE\PRODUCT\19.0.0\DBHOME_1\RDBMS\ARC00042_0629061547.001 RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=42 RECID=36 STAMP=629553646
output file name=U02\APP\ORACLE\PRODUCT\19.0.0\DB_1\RDBMS\ARC00042_0629061547.001 RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 02-AUG-07

cataloged archived log
archived log file name=U02\APP\ORACLE\PRODUCT\19.0.0\DBHOME_1\RDBMS\ARC00042_0629061547.001 RECID=1 STAMP=629553800

cataloged archived log
archived log file name=U02\APP\ORACLE\PRODUCT\19.0.0\DBHOME_1\RDBMS\ARC00042_0629061547.001 RECID=2 STAMP=629553800

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=629553800 file name=U02\APP\ORACLE\ORADATA\NEW19C\SYSAUX01.DBF
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=629553801 file name=U02\APP\ORACLE\ORADATA\NEW19C\UNDOTBS01.DBF

contents of Memory Script:
{
set until scn 833606;
recover
clone database
delete archivelog
;
}
executing Memory Script

executing command: SET until clause

Starting recover at 02-AUG-07
using channel ORA_AUX_DISK_1
datafile 4 not processed because file is read-only

starting media recovery

archived log for thread 1 with sequence 42 is already on disk as file U02\APP\ORACLE\PRODUCT\19.0.0\DBHOME_1\RDBMS\ARC00042_0629061547.001
archived log file name=U02\APP\ORACLE\PRODUCT\19.0.0\DBHOME_1\RDBMS\ARC00042_0629061547.001 thread=1 sequence=42
media recovery complete, elapsed time: 00:00:01
Finished recover at 02-AUG-07

contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount ;
}
executing Memory Script

database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 263639040 bytes

Fixed Size 1332544 bytes
Variable Size 83888832 bytes
Database Buffers 171966464 bytes
Redo Buffers 6451200 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE “TEST” RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( ‘U02\APP\ORACLE\ORADATA\NEW19C\REDO01.LOG’ ) SIZE 50 M REUSE,
GROUP 2 ( ‘U02\APP\ORACLE\ORADATA\NEW19C\REDO02.LOG’ ) SIZE 50 M REUSE,
GROUP 3 ( ‘U02\APP\ORACLE\ORADATA\NEW19C\REDO03.LOG’ ) SIZE 50 M REUSE
DATAFILE
‘U02\APP\ORACLE\ORADATA\NEW19C\SYSTEM01.DBF’
CHARACTER SET AL32UTF8

contents of Memory Script:
{
set newname for tempfile 1 to
“U02\APP\ORACLE\ORADATA\NEW19C\TEMP01.DBF”;
switch clone tempfile all;
catalog clone datafilecopy “U02\APP\ORACLE\ORADATA\NEW19C\SYSAUX01.DBF”;
catalog clone datafilecopy “U02\APP\ORACLE\ORADATA\NEW19C\UNDOTBS01.DBF”;
switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to U02\APP\ORACLE\ORADATA\NEW19C\TEMP01.DBF in control file

cataloged datafile copy
datafile copy file name=U02\APP\ORACLE\ORADATA\NEW19C\SYSAUX01.DBF RECID=1 STAMP=629553825

cataloged datafile copy
datafile copy file name=E:\ORACLE\ORADATA\TEST\UNDOTBS01.DBF RECID=2 STAMP=629553826

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=629553825 file name=U02\APP\ORACLE\ORADATA\NEW19C\SYSAUX01.DBF
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=629553826 file name=U02\APP\ORACLE\ORADATA\NEW19C\UNDOTBS01.DBF

contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script

database opened

contents of Memory Script:
{
catalog clone datafilecopy “U02\APP\ORACLE\ORADATA\NEW19C\USERS01.DBF”;
switch clone datafile 4 to datafilecopy “U02\APP\ORACLE\ORADATA\NEW19C\USERS01.DBF”;
#online the readonly tablespace
sql clone “alter tablespace USERS online”;
}
executing Memory Script

cataloged datafile copy
datafile copy file name=U02\APP\ORACLE\ORADATA\NEW19C\USERS01.DBF RECID=3 STAMP=629553870

datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=629553870 file name=U02\APP\ORACLE\ORADATA\NEW19C\USERS01.DBF

sql statement: alter tablespace USERS online
Finished Duplicate Db at 02-AUG-07

Step 16 :

Now we can see database in read-write mode.

Step 17:

Also you can user in target side which is available source side.

SQL> conn scott/scott

Connected.

SQL> show user

USER is “scott”

SQL> select count(*) from student1;

 COUNT(*)

———-

      3000