Oracle DBA: Active database duplication in oracle 11gR2

Active database duplication in oracle 11gR2

Here I am duplicating an oracle database to a new server with out using any backups. The duplication takes place by connecting directly to the source database. This feature is newly introduced in oracle 11gR2.

SOURCE SERVER

The source database should be in archive log mode for the active duplication to work. Check the archive log mode of the source database.

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 12
Next log sequence to archive 14
Current log sequence 14
SQL>

Add the following entry to $ORACLE_HOME/network/admin/tnsnames.ora file in the source server.

TESTDB_DESTINATION =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(ORACLE_SID = testdb)

)

TARGET SERVER

Create the necessary directories.

Here I have created the directories same as that of source server.

[oracle@test1 ~]$ mkdir -p /u01/app/oracle/oradata/testdb

[oracle@test1 ~]$ mkdir -p /u01/app/oracle/fast_recovery_area/testdb

[oracle@test1 ~]$ mkdir -p /u01/app/oracle/admin/testdb/adump

Create a pfile with the name inittestdb.ora

Create a pfile with only one parameter DB_NAME, rest of the parameters will be available from the spfile backup during duplication.

[oracle@test1 ~]$ cd $ORACLE_HOME/dbs

[oracle@test1 ~]$ cat inittestdb.ora

DB_NAME=testdb

[oracle@test1 ~]$

Start the duplicate database in nomount

[oracle@test1 ~]$ export ORACLE_SID=testdb

[oracle@test1 ~]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Tue May 7 22:15:20 2013

Enter user-name: /as sysdba

Connected to an idle instance.

SQL> startup nomount

ORACLE instance started.

Total System Global Area 1000189952 bytes

Fixed Size 1340832 bytes

Variable Size 570428000 bytes

Database Buffers 423624704 bytes

Redo Buffers 4796416 bytes

SQL>

Create a password file in $ORACLE_HOME/dbs

The password given should be same as that of the password file in the source server.

[oracle@test dbs]$ pwd
/u01/app/oracle/product/11.2.0/db_1/dbs
[oracle@test dbs]$ orapwd file=orapwtestdb password=oracle entries=10
[oracle@test dbs]$

Add the below entries to the $ORACLE_HOME/network/admin/tnsnames.ora file.

[oracle@test1 admin]$ cat tnsnames.ora

TESTDB_SOURCE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.192)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(ORACLE_SID = testdb)
)
)

TESTDB_DESTINATION =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.193)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(ORACLE_SID = testdb)
)
)

Add the below entry to the $ORACLE_HOME/network/admin/listener.ora file.

[oracle@test1 admin]$ cat listener.ora

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

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

)

ADR_BASE_LISTENER = /u01/app/oracle

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = testdb)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

(SID_NAME = testdb)

)

[oracle@test1 admin]$

Restart or reload the listener after it's configuration

Connect to target and auxiliary database using rman

Open another terminal and connect to the target and auxiliary database using rman. Use the respective connect descriptors for both source and target databases.

[oracle@test1 backup]$ rman target sys/oracle@testdb_source auxiliary sys/oracle@testdb_destination

Recovery Manager: Release 11.2.0.1.0 - Production on Fri May 10 20:07:35 2013

connected to target database: TESTDB (DBID=2596658962)

connected to auxiliary database (not started)

RMAN>

Duplicate the database

RMAN> duplicate database to testdb from active database spfile nofilenamecheck;

Starting Duplicate Db at 10-MAY-13

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=19 device type=DISK

contents of Memory Script:

{

backup as copy reuse

targetfile '/u01/app/oracle/product/11.2.0/db_1/dbs/spfiletestdb.ora' auxiliary format

'/u01/app/oracle/product/11.2.0/db_1/dbs/spfiletestdb.ora' ;

sql clone "alter system set spfile= ''/u01/app/oracle/product/11.2.0/db_1/dbs/spfiletestdb.ora''";

}

executing Memory Script

Starting backup at 10-MAY-13

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=39 device type=DISK

Finished backup at 10-MAY-13

sql statement: alter system set spfile= ''/u01/app/oracle/product/11.2.0/db_1/dbs/spfiletestdb.ora''

contents of Memory Script:

{

sql clone "alter system set db_name =

''TESTDB'' comment=

''duplicate'' scope=spfile";

shutdown clone immediate;

startup clone nomount;

}

executing Memory Script

sql statement: alter system set db_name = ''TESTDB'' comment= ''duplicate'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area 1000189952 bytes

Fixed Size 1340832 bytes

Variable Size 591399520 bytes

Database Buffers 402653184 bytes

Redo Buffers 4796416 bytes

contents of Memory Script:

{

sql clone "alter system set db_name =

''TESTDB'' comment=

''Modified by RMAN duplicate'' scope=spfile";

sql clone "alter system set db_unique_name =

''TESTDB'' comment=

''Modified by RMAN duplicate'' scope=spfile";

shutdown clone immediate;

startup clone force nomount

backup as copy current controlfile auxiliary format '/u01/app/oracle/oradata/testdb/control01.ctl';

restore clone controlfile to '/u01/app/oracle/flash_recovery_area/testdb/control02.ctl' from

'/u01/app/oracle/oradata/testdb/control01.ctl';

alter clone database mount;

}

executing Memory Script

sql statement: alter system set db_name = ''TESTDB'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set db_unique_name = ''TESTDB'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area 1000189952 bytes

Fixed Size 1340832 bytes

Variable Size 591399520 bytes

Database Buffers 402653184 bytes

Redo Buffers 4796416 bytes

Starting backup at 10-MAY-13

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

copying current control file

output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_testdb.f tag=TAG20130510T201646 RECID=3 STAMP=815084233

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:51

Finished backup at 10-MAY-13

Starting restore at 10-MAY-13

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=18 device type=DISK

channel ORA_AUX_DISK_1: copied control file copy

Finished restore at 10-MAY-13

database mounted

RMAN-05538: WARNING: implicitly using DB_FILE_NAME_CONVERT

contents of Memory Script:

{

set newname for datafile 1 to

"/u01/app/oracle/oradata/testdb/system01.dbf";

set newname for datafile 2 to

"/u01/app/oracle/oradata/testdb/sysaux01.dbf";

set newname for datafile 3 to

"/u01/app/oracle/oradata/testdb/undotbs01.dbf";

set newname for datafile 4 to

"/u01/app/oracle/oradata/testdb/users01.dbf";

set newname for datafile 5 to

"/u01/app/oracle/oradata/testdb/example01.dbf";

backup as copy reuse

datafile 1 auxiliary format

"/u01/app/oracle/oradata/testdb/system01.dbf" datafile

2 auxiliary format

"/u01/app/oracle/oradata/testdb/sysaux01.dbf" datafile

3 auxiliary format

"/u01/app/oracle/oradata/testdb/undotbs01.dbf" datafile

4 auxiliary format

"/u01/app/oracle/oradata/testdb/users01.dbf" datafile

5 auxiliary format

"/u01/app/oracle/oradata/testdb/example01.dbf" ;

sql 'alter system archive log current';

}

executing Memory Script

executing command: SET NEWNAME

Starting backup at 10-MAY-13

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

input datafile file number=00001 name=/u01/app/oracle/oradata/testdb/system01.dbf

output file name=/u01/app/oracle/oradata/testdb/system01.dbf tag=TAG20130510T201807

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:05:27

channel ORA_DISK_1: starting datafile copy

input datafile file number=00002 name=/u01/app/oracle/oradata/testdb/sysaux01.dbf

output file name=/u01/app/oracle/oradata/testdb/sysaux01.dbf tag=TAG20130510T201807

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:08:21

channel ORA_DISK_1: starting datafile copy

input datafile file number=00005 name=/u01/app/oracle/oradata/testdb/example01.dbf

output file name=/u01/app/oracle/oradata/testdb/example01.dbf tag=TAG20130510T201807

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:36

channel ORA_DISK_1: starting datafile copy

input datafile file number=00003 name=/u01/app/oracle/oradata/testdb/undotbs01.dbf

output file name=/u01/app/oracle/oradata/testdb/undotbs01.dbf tag=TAG20130510T201807

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:03:44

channel ORA_DISK_1: starting datafile copy

input datafile file number=00004 name=/u01/app/oracle/oradata/testdb/users01.dbf

output file name=/u01/app/oracle/oradata/testdb/users01.dbf tag=TAG20130510T201807

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:04

Finished backup at 10-MAY-13

sql statement: alter system archive log current

contents of Memory Script:

{

backup as copy reuse

archivelog like "/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2013_05_10/o1_mf_1_13_8rt3c9o7_.arc" auxiliary format

"/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2013_05_10/o1_mf_1_13_%u_.arc" ;

catalog clone recovery area;

switch clone datafile all;

}

executing Memory Script

Starting backup at 10-MAY-13

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log copy

input archived log thread=1 sequence=13 RECID=7 STAMP=815085510

output file name=/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2013_05_10/o1_mf_1_13_0uo9adu9_.arc RECID=0 STAMP=0

channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:15

Finished backup at 10-MAY-13

searching for all files in the recovery area

List of Files Unknown to the Database

=====================================

File Name: /u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2013_05_10/o1_mf_1_13_0uo9adu9_.arc

File Name: /u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2013_05_07/o1_mf_1_1_8rldm7gx_.arc

File Name: /u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2013_05_09/o1_mf_1_12_0no9822u_.arc

File Name: /u01/app/oracle/flash_recovery_area/TESTDB/onlinelog/o1_mf_1_8rqqy1qx_.log

File Name: /u01/app/oracle/flash_recovery_area/TESTDB/onlinelog/o1_mf_2_8rqr6nck_.log

File Name: /u01/app/oracle/flash_recovery_area/TESTDB/onlinelog/o1_mf_3_8rgb8ovq_.log

File Name: /u01/app/oracle/flash_recovery_area/TESTDB/onlinelog/o1_mf_3_8rhrjzbh_.log

File Name: /u01/app/oracle/flash_recovery_area/TESTDB/onlinelog/o1_mf_1_8rhrfoj0_.log

File Name: /u01/app/oracle/flash_recovery_area/TESTDB/onlinelog/o1_mf_2_8rgb8fsy_.log

File Name: /u01/app/oracle/flash_recovery_area/TESTDB/onlinelog/o1_mf_3_8rqrlvf8_.log

File Name: /u01/app/oracle/flash_recovery_area/TESTDB/onlinelog/o1_mf_1_8rgb8bk5_.log

File Name: /u01/app/oracle/flash_recovery_area/TESTDB/onlinelog/o1_mf_2_8rhrhtl4_.log

cataloging files...

cataloging done

List of Cataloged Files

=======================

File Name: /u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2013_05_10/o1_mf_1_13_0uo9adu9_.arc

File Name: /u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2013_05_07/o1_mf_1_1_8rldm7gx_.arc

File Name: /u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2013_05_09/o1_mf_1_12_0no9822u_.arc

List of Files Which Where Not Cataloged

=======================================

File Name: /u01/app/oracle/flash_recovery_area/TESTDB/onlinelog/o1_mf_1_8rqqy1qx_.log

RMAN-07518: Reason: Foreign database file DBID: 2598336272 Database Name: TESTDB

File Name: /u01/app/oracle/flash_recovery_area/TESTDB/onlinelog/o1_mf_2_8rqr6nck_.log

RMAN-07518: Reason: Foreign database file DBID: 2598336272 Database Name: TESTDB

File Name: /u01/app/oracle/flash_recovery_area/TESTDB/onlinelog/o1_mf_3_8rgb8ovq_.log

RMAN-07518: Reason: Foreign database file DBID: 2598027485 Database Name: TESTDB

File Name: /u01/app/oracle/flash_recovery_area/TESTDB/onlinelog/o1_mf_3_8rhrjzbh_.log

RMAN-07518: Reason: Foreign database file DBID: 2598074574 Database Name: TESTDB