RMAN Cloning of Database

RMAN provides the DUPLICATE command, which uses the backups of the database to create the clone database. Files are restored to the target database, after which an incomplete recovery is performed and the clone database is opened using RESETLOGS option. All the preceding steps are performed automatically by RMAN without any intervention from the DBA.

Considerations-

Source Database Name: orcl

Clone Database Name: testdb

Steps to be followed-

Make sure good backup present for the orcl database

create pfile with changed value

create the required directory sturcture

Make entry in the listener and tns file

start the db in nomount using the newly created pfile

Connect to RMAN and using duplicate command create the database

Create the pfile in the default location.

SQL> show parameter pfile

NAME    TYPE        VALUE

------------------------------------ ----------- --------------------------

spfile  string      /home/app/oracle/product/10.2.0/db_1/dbs/spfileorcl.ora

SQL> create pfile='/home/app/oracle/product/10.2.0/db_1/dbs/inittestdb.ora' from spfile;

File created.

Make the parameter changes

[oracle@localhost dbs]$ vi inittestdb.ora

orcl.__db_cache_size=201326592

orcl.__java_pool_size=4194304

orcl.__large_pool_size=4194304

orcl.__shared_pool_size=71303168

orcl.__streams_pool_size=0

*.audit_file_dest='/home/app/oracle/admin/orcl/adump'

*.background_dump_dest='/home/app/oracle/admin/orcl/bdump'

*.compatible='10.2.0.1.0'

*.control_files='/home/app/oracle/oradata/orcl/control01.ctl','/home/app/oracle/oradata/orcl/control02.ctl','/home/app/oracle/oradata/orcl/control03.ctl'

*.core_dump_dest='/home/app/oracle/admin/orcl/cdump'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_name='orcl'

*.db_recovery_file_dest='/home/app/oracle/flash_recovery_area'

*.db_recovery_file_dest_size=2147483648

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'

*.job_queue_processes=10

*.open_cursors=300

*.pga_aggregate_target=94371840

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=285212672

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='/home/app/oracle/admin/orcl/udump'

Create the dump folder

[oracle@localhost testdb]$ pwd

/home/app/oracle/admin/testdb

[oracle@localhost testdb]$ mkdir adump bdump cdump udump

[oracle@localhost testdb]$ pwd

/home/app/oracle/oradata/testdb

[oracle@localhost testdb]$ cd /home/app/oracle/flash_recovery_area

[oracle@localhost flash_recovery_area]$ mkdir testdb

[oracle@localhost flash_recovery_area]$ cd testdb

[oracle@localhost testdb]$ pwd

/home/app/oracle/flash_recovery_area/testdb

[oracle@localhost testdb]$

Changes made-

[oracle@localhost dbs]$ vi inittestdb.ora

[oracle@localhost dbs]$ cat inittestdb.ora

testdb.__db_cache_size=201326592

testdb.__java_pool_size=4194304

testdb.__large_pool_size=4194304

testdb.__shared_pool_size=71303168

testdb.__streams_pool_size=0

*.audit_file_dest='/home/app/oracle/admin/testdb/adump'

*.background_dump_dest='/home/app/oracle/admin/testdb/bdump'

*.compatible='10.2.0.1.0'

*.control_files='/home/app/oracle/oradata/testdb/control01.ctl','/home/app/oracle/oradata/testdb/control02.ctl','/home/app/oracle/oradata/testdb/control03.ctl'

*.core_dump_dest='/home/app/oracle/admin/testdb/cdump'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_name='testdb'

*.db_recovery_file_dest='/home/app/oracle/flash_recovery_area/testdb/'

*.db_recovery_file_dest_size=2147483648

*.dispatchers='(PROTOCOL=TCP) (SERVICE=testdbXDB)'

*.job_queue_processes=10

*.open_cursors=300

*.pga_aggregate_target=94371840

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=285212672

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='/home/app/oracle/admin/testdb/udump'

db_file_name_convert=('/home/app/oracle/oradata/orcl','/home/app/oracle/oradata/testdb')

log_file_name_convert=('/home/app/oracle/oradata/orcl','/home/app/oracle/oradata/testdb')

Make entry in the listner.ora

[oracle@localhost admin]$ cat listener.ora

# listener.ora Network Configuration File: /home/app/oracle/product/10.2.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = orcl)

      (ORACLE_HOME = /home/app/oracle/product/10.2.0/db_1)

      (PROGRAM = extproc)

    )

  (SID_DESC =

      (SID_NAME = testdb)

      (ORACLE_HOME = /home/app/oracle/product/10.2.0/db_1)

      (PROGRAM = extproc)

    )

  )

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

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

    )

  )

Make entry in the tnsname.ora file

[oracle@localhost admin]$ cat tnsnames.ora

# tnsnames.ora Network Configuration File: /home/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

orcl =

  (DESCRIPTION =

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

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

    )

  )

testdb =

  (DESCRIPTION =

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

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = testdb)

    )

  )

EXTPROC_CONNECTION_DATA =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

    )

    (CONNECT_DATA =

      (SID = PLSExtProc)

      (PRESENTATION = RO)

    )

  )

[oracle@localhost admin]$ export ORACLE_SID=testdb

[oracle@localhost dbs]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jun 28 03:13:10 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount pfile='$ORACLE_HOME/dbs/inittestdb.ora';

ORACLE instance started.

Total System Global Area  285212672 bytes

Fixed Size                  1218992 bytes

Variable Size              79693392 bytes

Database Buffers          201326592 bytes

Redo Buffers                2973696 bytes

SQL> exit

Take the database and archivelog backup

[oracle@localhost dbs]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Thu Jun 28 11:04:12 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: orcl (DBID=4144628158)

RMAN> backup database plus archivelog;

[oracle@localhost dbs]$ export ORACLE_SID=testdb

[oracle@localhost dbs]$ rman target sys/sys@orcl auxiliary /

Recovery Manager: Release 10.2.0.1.0 - Production on Thu Jun 28 11:04:12 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: orcl (DBID=4144628158)

connected to auxiliary database: testdb (not mounted)

RMAN>duplicate target database to testdb;

RMAN>exit

[oracle@localhost dbs]$ export ORACLE_SID=testdb

[oracle@localhost dbs]$ sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jun 28 11:04:12 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE

--------- ----------

TESTDB    READ WRITE

Cold Cloning of Database

Cold Cloning is one the reliable methods that is done using the Cold Backup. The drawback of this method is that the database has to be shutdown while taking the cold backup.

Cosiderations-

Source Database Name: orcl

Clone Database Name: testdb

Steps to be followed-

#Create pfile

#Create controlfile

#Shutdown the dataabase

#Copy the datafile to the new location

#Start the db in nomount using the newly created pfile

#Execute the controlfile which is created

#Recover the database

#Open the database in resetlogs

Create the pfile in the default location.

SQL> show parameter pfile

NAME    TYPE        VALUE

------------------------------------ ----------- ------------------------------

spfile    string      /home/app/oracle/product/10.2.0/db_1/dbs/spfileorcl.ora

SQL> create pfile='/home/app/oracle/product/10.2.0/db_1/dbs/inittestdb.ora' from spfile;

File created.

Make the parameter changes

[oracle@localhost dbs]$ vi inittestdb.ora

orcl.__db_cache_size=201326592

orcl.__java_pool_size=4194304

orcl.__large_pool_size=4194304

orcl.__shared_pool_size=71303168

orcl.__streams_pool_size=0

*.audit_file_dest='/home/app/oracle/admin/orcl/adump'

*.background_dump_dest='/home/app/oracle/admin/orcl/bdump'

*.compatible='10.2.0.1.0'

*.control_files='/home/app/oracle/oradata/orcl/control01.ctl','/home/app/oracle/oradata/orcl/control02.ctl','/home/app/oracle/oradata/orcl/control03.ctl'

*.core_dump_dest='/home/app/oracle/admin/orcl/cdump'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_name='orcl'

*.db_recovery_file_dest='/home/app/oracle/flash_recovery_area'

*.db_recovery_file_dest_size=2147483648

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'

*.job_queue_processes=10

*.open_cursors=300

*.pga_aggregate_target=94371840

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=285212672

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='/home/app/oracle/admin/orcl/udump'

Create the dump folder

[oracle@localhost ~]$ mkdir /home/app/oracle/admin/testdb

[oracle@localhost ~]$ cd /home/app/oracle/admin/testdb

[oracle@localhost testdb]$ pwd

/home/app/oracle/admin/testdb

[oracle@localhost testdb]$ mkdir adump bdump cdump udump

[oracle@localhost testdb]$ pwd

/home/app/oracle/oradata/testdb

[oracle@localhost testdb]$ cd /home/app/oracle/flash_recovery_area

[oracle@localhost flash_recovery_area]$ mkdir testdb

[oracle@localhost flash_recovery_area]$ cd testdb

[oracle@localhost testdb]$ pwd

/home/app/oracle/flash_recovery_area/testdb

[oracle@localhost testdb]$

[oracle@localhost dbs]$ vi inittestdb.ora

[oracle@localhost dbs]$ cat inittestdb.ora

testdb.__db_cache_size=201326592

testdb.__java_pool_size=4194304

testdb.__large_pool_size=4194304

testdb.__shared_pool_size=71303168

testdb.__streams_pool_size=0

*.audit_file_dest='/home/app/oracle/admin/testdb/adump'

*.background_dump_dest='/home/app/oracle/admin/testdb/bdump'

*.compatible='10.2.0.1.0'

*.control_files='/home/app/oracle/oradata/testdb/control01.ctl','/home/app/oracle/oradata/testdb/control02.ctl','/home/app/oracle/oradata/testdb/control03.ctl'

*.core_dump_dest='/home/app/oracle/admin/testdb/cdump'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_name='testdb'

*.db_recovery_file_dest='/home/app/oracle/flash_recovery_area/testdb/'

*.db_recovery_file_dest_size=2147483648

*.dispatchers='(PROTOCOL=TCP) (SERVICE=testdbXDB)'

*.job_queue_processes=10

*.open_cursors=300

*.pga_aggregate_target=94371840

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=285212672

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='/home/app/oracle/admin/testdb/udump'

Create the directory to store the datafile and redolog file.

Check the datafile and redolog file names

SQL> select name from v$datafile;

NAME

--------------------------------------------------------------------------------

/home/app/oracle/oradata/orcl/system01.dbf

/home/app/oracle/oradata/orcl/undotbs01.dbf

/home/app/oracle/oradata/orcl/sysaux01.dbf

/home/app/oracle/oradata/orcl/users01.dbf

/home/app/oracle/oradata/orcl/example01.dbf

SQL> select member from v$logfile;

MEMBER

--------------------------------------------------------------------------------

/home/app/oracle/oradata/orcl/redo03.log

/home/app/oracle/oradata/orcl/redo02.log

/home/app/oracle/oradata/orcl/redo01.log

[oracle@localhost ~]$ cd /home/app/oracle/oradata/

[oracle@localhost oradata]$ ls

orcl

[oracle@localhost oradata]$ mkdir testdb

[oracle@localhost oradata]$ cd testdb

[oracle@localhost testdb]$ pwd

/home/app/oracle/oradata/testdb

Create the controlfile script.

SQL> alter database backup controlfile to trace as '/home/oracle/testdbctl.sql';

Database altered.

Make necessary changes in the controlfile script.

[oracle@localhost dbs]$ cat /home/oracle/testdbctl.sql

CREATE CONTROLFILE SET DATABASE "TESTDB" RESETLOGS  ARCHIVELOG

    MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 100

    MAXINSTANCES 8

    MAXLOGHISTORY 292

LOGFILE

  GROUP 1 '/home/app/oracle/oradata/testdb/redo01.log'  SIZE 50M,

  GROUP 2 '/home/app/oracle/oradata/testdb/redo02.log'  SIZE 50M,

  GROUP 3 '/home/app/oracle/oradata/testdb/redo03.log'  SIZE 50M

DATAFILE

  '/home/app/oracle/oradata/testdb/system01.dbf',

  '/home/app/oracle/oradata/testdb/undotbs01.dbf',

  '/home/app/oracle/oradata/testdb/sysaux01.dbf',

  '/home/app/oracle/oradata/testdb/users01.dbf',

  '/home/app/oracle/oradata/testdb/example01.dbf'

CHARACTER SET WE8ISO8859P1

;

Shutdown the database

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

Copy the datafiles and redologfile(/home/app/oracle/oradata/orcl/) to the new directory(/home/app/oracle/oradata/testdb).

[oracle@localhost orcl]$ pwd

/home/app/oracle/oradata/orcl

[oracle@localhost orcl]$ cp *.dbf /home/app/oracle/oradata/testdb/

Export the sid parameter and start the database in nomount state

[oracle@localhost ~]$ export ORACLE_SID=testdb

[oracle@localhost dbs]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jun 26 10:20:06 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount pfile='$ORACLE_HOME/dbs/inittestdb.ora';

ORACLE instance started.

Total System Global Area  285212672 bytes

Fixed Size                  1218992 bytes

Variable Size              79693392 bytes

Database Buffers          201326592 bytes

Redo Buffers                2973696 bytes

Execute the controlfile script which is created

SQL> @/home/oracle/testdbctl.sql

Control file created.

Apply the log file which is at current state in the old database(orcl)

SQL> select * from v$log where status='CURRENT';

GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS  ARC    STATUS

---------- ---------- ---------- ---------- ---------- --- ----------------

FIRST_CHANGE#    FIRST_TIM

------------- ---------

  3              1                  7                52428800      1          NO    CURRENT

 502135               05-MAR-13

Recover the database now and apply the current logfile.

SQL> recover database using backup controlfile until cancel;

ORA-00279: change 501827 generated at 20/06/2013 08:37:10 needed for thread 1

ORA-00289: suggestion :

/home/app/oracle/flash_recovery_area/testdb/SCHOOL/archivelog/2013_20_06/o1_mf_1

_6_%u_.arc

ORA-00280: change 501827 for thread 1 is in sequence #6

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

/home/app/oracle/oradata/orcl/redo03.log

Log applied.

Media recovery complete.

Open the database in reset logs.

SQL> alter database open resetlogs;

Database altered.

Check the database state.

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE

--------- ----------

TESTDB    READ WRITE