Oracle

Oracle database Cloning-24

Advertisements

Cloning

Cloning is creating a same database as in different name and the DBID will be different.

Use

It mostly used to test the application functionality and new implementation.

Methods of cloning

  1. Cold backup
  2. Hot Backup
  3. RMAN Backup (duplicated database / active database)

Pre- Requirement

Create a folder according to the versions.

11g:

/opt/oracle (ORACLE_BASE ) –> (Admin , Oradata & diag)

Admin –>DBname (adump, create, ddump & pfile)

Oradata –> DBnames

Diag –> RDMBS–>DBname–>DBname–>Trace (bdump,cdump & udump)

All in under trace, but without folders

  1. Clod backup Cloning

It’s an offline cloning.

Go to source database.

Pre- requesting: Note down the following.

Sql> select name from v$datafile;

Sql> select member from v$logfile;

Sql> Alter database backup controlfile to trace as ‘\opt\con.trc’;

Sql> create pfile =’/opt/initmuthu.ora’ from spfile;

Sql> shutdown immediate;

 

Step 1 Prepare a pfile

 

$ cd /opt

$ mv initmuthu.ora  /destination/inittestdb.ora

$ vi inittestdb.ora

Replace the Muthu as testDB and save it.

 

Step 2 Prepare Control file

 

$ vi /opt/con.trc

Come to startup nomount & copy the following.

Create controlfile reuse’Muthu’ noresetlogs archivelog

Logfile  ‘/opt/oracle/oradata/Muthu/redo01.log’ .. etc

datafile  ‘/opt/oracle/oradata/Muthu/system.dbf’ … etc

Copy and save it to the new notepad and change the file as following.

Create controlfile SET ’TESTDB’ resetlogs noarchivelog

Logfile  ‘/opt/oracle/oradata/TestDB/redo01.log’ .. etc

datafile  ‘/opt/oracle/oradata/TestDB /system.dbf’ … etc;

 

Step 3 Copy a file to new database – target DB

$  cd /opt/oracle/Muthu

$ cp –r *.dbf /opt/oracle/oradata/testDB

$ cp –r *.log /opt/oracle/oradata/testDB

 

Step 4 Run the scripts

$ export ORACLE_SID= testDB

$ orapwd file = $ORACLE_HOME/dbs/orapwtestDB password=sys

$ sqlplus /nolog

Sql> conn sys/sys as sysdba

Sql> startup pfile=’/opt/inittestdb.ora’ nomount

Sql>

Create controlfile SET ’TESTDB’ resetlogs noarchivelog

Logfile  ‘/opt/oracle/oradata/TestDB/redo01.log’ .. etc

datafile  ‘/opt/oracle/oradata/TestDB /system.dbf’ … etc;

 

sql> recover database;

sql> alter database open resetlogs;

sql> select name from v$database;

TestDB 

 

  1. Hot backup Cloning

It’s an online cloning.

Go to source database.

Pre- requesting: Note down the following.

Sql> select * from v$tablespace;

Sql> select file_name from dba_data_files where tablespace_name =’tools’;

Step 1

Sql> alter tablespace begin backup;

$ cd /opt/oracle/Muthu/

$ cp toolso1.dbf /opt/oracle/testDB/

Sql> alter tablespace end backup;

 

Like the above backup & copy all the data files from source to destination.

 

Step 2 Copy Archive logs

Sql> Archive log list

Go to destination and create one folder

$ mkdir –p /opt/archive/testdb

$ cd /opt/archive/Muthu

$ cp –r  * .arc /opt/archive/testdb

Step 3 Prepare Control file

Sql> Alter database backup controlfile to trace as ‘\opt\con.trc’;

 

$ vi /opt/con.trc

Come to startup nomount & copy the following.

Create controlfile reuse’Muthu’ noresetlogs archivelog

Logfile  ‘/opt/oracle/oradata/Muthu/redo01.log’ .. etc

datafile  ‘/opt/oracle/oradata/Muthu/system.dbf’ … etc

Copy and save it to the new notepad and change the file as following.

Create controlfile SET ’TESTDB’ resetlogs noarchivelog

Logfile  ‘/opt/oracle/oradata/TestDB/redo01.log’ .. etc

datafile  ‘/opt/oracle/oradata/TestDB /system.dbf’ … etc;

Step 4 Prepare SPfile

$ cd /opt

$ mv initmuthu.ora  /destination/inittestdb.ora

$ vi inittestdb.ora

Replace the Muthu as testDB and save it.

 

Step 5 Run the scripts

Go to destination DB : TESTDB

$ export ORACLE_SID= testDB

$ orapwd file = $ORACLE_HOME/dbs/orapwtestDB password=sys

$ sqlplus /nolog

Sql> conn sys/sys as sysdba

Sql> startup pfile=’/opt/inittestdb.ora’ nomount

Sql>

Create controlfile SET ’TESTDB’ resetlogs noarchivelog

Logfile  ‘/opt/oracle/oradata/TestDB/redo01.log’ .. etc

datafile  ‘/opt/oracle/oradata/TestDB /system.dbf’ … etc;

 

sql> recover database using backup controlfile until cancel;

sql> alter database open resetlogs;

sql> select name from v$database;

TestDB

Using backup control file, It will apply all the archive log files with until cancel option.

Until cancel, It will stop upto copied archive logs, otherwise it will wait for next archive, may be the source does not have one generated.

 

  1. RMAN backup Cloning

 

Catalog DB is an optional.

Ex: Source DB: Muthu Catalog: testDB Clone DB: Rajesh

We need to create a net service for Muthu (Muthu) and testDB (test)

$ rman target  sys/sys @muthu catalog rmancat/rman@test

Rman> backup database;

Rman> backup archivelog all;

 

 

Login to Muthu

Step 1 Prepare SPfile

Sql> create pfile =’/opt/initmuthu.ora’ from spfile;

$ mv initmuthu.ora  /destination/initRajesh.ora

$ vi initRajesh.ora

Add the below lines and change the parameter accordingly.

Db_file_name_convert =’/opt/oracle/oradata/muthu’, ‘/opt/oracle/oradata/rajesh’

log_file_name_convert =’/opt/oracle/oradata/muthu’, ‘/opt/oracle/oradata/rajesh’

*.compatible=’10.2.0.1.0′
*.control_files=’/u03/oracle/clone_db/control01.ctl’,’/u03/oracle/
clone_db/control02.ctl’,’/u03/oracle/clone_db/control03.ctl’
*.db_block_size=8192
*.db_name=’clone_db’
*.sga_target=285212672

RMAN, will automatically try to restore it source database location. So we need to convert to destination location.

 

$ export ORACLE_SID= Rajesh

$ orapwd file = $ORACLE_HOME/dbs/orapwRajesh password=sys

$ sqlplus /nolog

Sql> conn sys/sys as sysdba

Sql> startup pfile=’/opt/initRajesh.ora’ nomount

 

Open one more terminal.

$ export ORACLE_SID= Rajesh

$ rman target sys/sys@muthu catalog rmancat/rman@test  auxiliary /   – Muthu is source – test catalog and auxiliary is destination

Rman> duplicate target database to  rajesh – It will restore & recover

DBAs will use RMAN clone. For 60GB it will take ~ 40 minutes.

 

11g New Feature

Active Database cloning using Rman

This will not require RMAN backup. No backup needed.

Step1 : Change pfile

Step2 : Duplicate/ Restore DB

 

Sql> create pfile =’/opt/initmuthu.ora’ from spfile;

$ mv initmuthu.ora  /destination/initRajesh.ora

$ vi initRajesh.ora

Add the below lines

Db_file_name_convert =’/opt/oracle/oradata/muthu’, ‘/opt/oracle/oradata/rajesh’

log_file_name_convert =’/opt/oracle/oradata/muthu’, ‘/opt/oracle/oradata/rajesh’

 

RMAN, will automatically try  to  restore it source database location. So we need to  convert to  destination location.

 

$ export ORACLE_SID= Rajesh

$ orapwd file = $ORACLE_HOME/dbs/orapwRajesh password=sys

$ sqlplus /nolog

Sql> conn sys/sys as sysdba

Sql> startup pfile=’/opt/initRajesh.ora’ nomount

 

Open one more terminal.

$ export ORACLE_SID= Rajesh

$ rman target sys/sys @muthu catalog rmancat/rman@test  auxiliary /   – Muthu is source – test catalog and auxiliary is destination

Rman> duplicate target database to  rajesh from active database – It wil restore & recover

 

Post Cloning:

Sql> select * from v$tablesapce;

Sql> select * from dba_temp_file;

No row selected.

Sql> alter tablespace temp add tempfile ‘/opt/orac/e/rajesh/temp01.dbf’ size 100m;

For 11g RMAN method — No post cloning needed.

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

Leave a Reply

%d bloggers like this: