Oracle & Linux Blog: May 2015

This demo is to show issues during Oracle 11g Database cloning.

Oracle Version: 11.2.0.4
Operating System: Oracle Enterprise Linux 6.5

I have two nodes : rac1 ( Catalog Database ) & rac2 ( Target Database )
Target Database Name: DB002
Catalog DB Name: DB001
Catalog TNS service name: SN001
Auxiliary DB Name: clone
Auxiliary TNS Service Name: clone

Catalog TNS entry:

SN001 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1)(PORT = 1621))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = SN001)
)
)

Auxiliary TNS Entry:

CLONE =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = rac1.localdomain)(PORT = 1721))

)

(CONNECT_DATA =

(SERVICE_NAME = clone)

)

We are going to create the Auxiliary instance on the node rac1 where we already have Catalog Database.

Here we are going to create clone by connecting from Target DB Server(rac2).

Now, Initially we must copy the parameter file & password file from Target(rac2) to Auxiliary(rac1). It is possible to just start the auxiliary instance using db_name & db_unique_name parameters and pass the other parameters from active database . In this case I have passed all the parameters on Auxiliary parameter file reciting on its server itself.

The parameter file looks as below:

clone.__db_cache_size=444596224

clone.__java_pool_size=4194304

clone.__large_pool_size=4194304

clone.__oracle_base='/oracle'#ORACLE_BASE set from environment

clone.__pga_aggregate_target=432013312

clone.__sga_target=641728512

clone.__shared_io_pool_size=0

clone.__shared_pool_size=176160768

clone.__streams_pool_size=0

*.audit_file_dest='/oracle/admin/CLONE/adump'

*.compatible='11.2.0.4'

*.control_files='+DATA1/clone/controlfile/current.278.880327563'#Set by RMAN

*.db_block_size=8192

*.db_file_name_convert='+DATA1/db002/datafile','+DATA1/clone/datafile'

*.db_name='CLONE'#Reset to original value by RMAN

*.db_recovery_file_dest='+DATA1'

*.db_recovery_file_dest_size=10737418240

*.diagnostic_dest='/oracle'

*.local_listener='CLNLIST'

*.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST'

*.log_file_name_convert='+DATA1/db002/onlinelog','+DATA1/clone/onlinelog'

*.memory_target=1024m

*.service_names='CLONE'

*.undo_tablespace='UNDOTBS1'

Firstly, I have copied the pfile from target and then created the spfile with appropriate changes. The parameters which were changed are db_name,db_unique_name,db_file_name_convert,log_file_name_convert,control_files and very important "compatible" parameter.

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

[oracle@rac2 dbs]$ pwd

/oracle/app/db/dbs

[oracle@rac2 dbs]$ scp -r initDB002.ora rac1:/oracle/app/db/dbs/initclone.ora

Now, copy the password file of Target DB from Target DB Server (rac2) to Auxiliary DB Server(rac1):

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

[oracle@rac2 dbs]$ pwd

/oracle/app/db/dbs

[oracle@rac2 dbs]$ scp -r orapwDB002 rac1:/oracle/app/db/dbs/orapwclone

Once copied make the changes in the Auxiliary instance "clone" parameter file as mentioned above.

Now, we are ready with our setup. Execute the following command from Target DB Server(rac2):

[oracle@rac2 dbs]$ rman target / catalog rman11g/<password>@sn001 auxiliary sys/<password>@clone

Recovery Manager: Release 11.2.0.4.0 - Production on Thu May 21 23:36:05 2015

connected to target database: DB002 (DBID=1192118653)

connected to recovery catalog database

connected to auxiliary database: CLONE (not mounted)

RMAN>

Now, Please execute the below "Duplicate" command. Ensure that you have copied the target database password file(orapwDB002) and rename it (orapwclone) otherwise you will face the Segmentation Fault error. Below is the Oracle Support Note to resolve the issue:

Segmentation Fault During Duplicate From Active Database (Doc ID 1370601.1)

Also, ensure that you have change the parameter "COMPATIBLE" to the correct version of the database. Otherwise, you will get the following error:

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of Duplicate Db command at 05/21/2015 23:32:53

RMAN-05501: aborting duplication of target database

RMAN-03015: error occurred in stored script Memory Script

RMAN-06136: ORACLE error from auxiliary database: ORA-00201: control file version 11.2.0.4.0 incompatible with ORACLE version 11.2.0.0.0

ORA-00202: control file: '+DATA1/clone/controlfile/current.278.880327563'

In order to resolve the above error make the changes in the spfile of auxiliary instance(clone) as follows:

SQL> show parameter compatible

NAME TYPE VALUE

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

compatible string 11.2.0

SQL> select name,issys_modifiable from v$parameter where name = 'compatible';

NAME

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

ISSYS_MOD

---------

compatible

FALSE

SQL> alter system set compatible='11.2.0.4' scope=spfile;

System altered.

SQL> shutdown immediate

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup nomount

ORACLE instance started.

Total System Global Area 1068937216 bytes

Fixed Size 2260088 bytes

Variable Size 616563592 bytes

Database Buffers 444596224 bytes

Redo Buffers 5517312 bytes

SQL> exit

Once making the above changes execute the following command:

[oracle@rac2 dbs]$ rman target / catalog rman11g/rmaniig@sn001 auxiliary sys/sys123@clone

Recovery Manager: Release 11.2.0.4.0 - Production on Thu May 21 23:36:05 2015

connected to target database: DB002 (DBID=1192118653)

connected to recovery catalog database

connected to auxiliary database: CLONE (not mounted)

RMAN> duplicate target database to 'clone' from active database nofilenamecheck;

Starting Duplicate Db at 21-MAY-15

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=24 device type=DISK

contents of Memory Script:

{

sql clone "alter system set control_files =

''+DATA1/clone/controlfile/current.278.880327563'' comment=

''Set by RMAN'' scope=spfile";

sql clone "alter system set db_name =

''DB002'' comment=

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

sql clone "alter system set db_unique_name =

''CLONE'' comment=

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

shutdown clone immediate;

startup clone force nomount

backup as copy current controlfile auxiliary format '+DATA1/clone/controlfile/current.278.880327563';

sql clone "alter system set control_files =

''+DATA1/clone/controlfile/current.278.880327563'' comment=

''Set by RMAN'' scope=spfile";

shutdown clone immediate;

startup clone nomount;

alter clone database mount;

}

executing Memory Script

sql statement: alter system set control_files = ''+DATA1/clone/controlfile/current.278.880327563'' comment= ''Set by RMAN'' scope=spfile

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

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

Oracle instance shut down

Oracle instance started

Total System Global Area 1068937216 bytes

Fixed Size 2260088 bytes

Variable Size 616563592 bytes

Database Buffers 444596224 bytes

Redo Buffers 5517312 bytes

Starting backup at 21-MAY-15

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=21 device type=DISK

channel ORA_DISK_1: starting datafile copy

copying current control file

output file name=/oracle/app/db/dbs/snapcf_DB002.f tag=TAG20150521T233642 RECID=24 STAMP=880328203

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

Finished backup at 21-MAY-15

sql statement: alter system set control_files = ''+DATA1/clone/controlfile/current.278.880327563'' comment= ''Set by RMAN'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area 1068937216 bytes

Fixed Size 2260088 bytes

Variable Size 616563592 bytes

Database Buffers 444596224 bytes

Redo Buffers 5517312 bytes

database mounted

RMAN-05529: WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.

contents of Memory Script:

{

set newname for datafile 1 to

"+data1";

set newname for datafile 2 to

"+data1";

set newname for datafile 3 to

"+data1";

set newname for datafile 4 to

"+data1";

set newname for datafile 5 to

"/oracle/dbfs/qrapp_ts_01.dbf";

backup as copy reuse

datafile 1 auxiliary format

"+data1" datafile

2 auxiliary format

"+data1" datafile

3 auxiliary format

"+data1" datafile

4 auxiliary format

"+data1" datafile

5 auxiliary format

"/oracle/dbfs/qrapp_ts_01.dbf" ;

sql 'alter system archive log current';

}

executing Memory Script

executing command: SET NEWNAME

Starting backup at 21-MAY-15

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

input datafile file number=00005 name=/oracle/dbfs/qrapp_ts_01.dbf

output file name=/oracle/dbfs/qrapp_ts_01.dbf tag=TAG20150521T233701

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

channel ORA_DISK_1: starting datafile copy

input datafile file number=00001 name=+DATA1/db002/datafile/system.268.877566173

output file name=+DATA1/clone/datafile/system.273.880328381 tag=TAG20150521T233701

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

channel ORA_DISK_1: starting datafile copy

input datafile file number=00002 name=+DATA1/db002/datafile/sysaux.269.877566407

output file name=+DATA1/clone/datafile/sysaux.287.880328637 tag=TAG20150521T233701

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

channel ORA_DISK_1: starting datafile copy

input datafile file number=00003 name=+DATA1/db002/datafile/undotbs1.270.877566553

output file name=+DATA1/clone/datafile/undotbs1.286.880328823 tag=TAG20150521T233701

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

channel ORA_DISK_1: starting datafile copy

input datafile file number=00004 name=+DATA1/db002/datafile/users.272.877566575

output file name=+DATA1/clone/datafile/users.285.880328837 tag=TAG20150521T233701

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

Finished backup at 21-MAY-15

sql statement: alter system archive log current

starting full resync of recovery catalog

full resync complete

contents of Memory Script:

{

backup as copy reuse

archivelog like "+DATA1/db002/archivelog/2015_05_21/thread_1_seq_5.259.880328843" auxiliary format

"+DATA1" ;

catalog clone start with "+DATA1";

switch clone datafile all;

}

executing Memory Script

Starting backup at 21-MAY-15

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log copy

input archived log thread=1 sequence=5 RECID=23 STAMP=880328862

output file name=+DATA1/clone/archivelog/2015_05_21/thread_1_seq_5.284.880328873 RECID=0 STAMP=0

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

Finished backup at 21-MAY-15

searching for all files that match the pattern +DATA1

List of Files Unknown to the Database

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

File Name: +data1/CLONE/ARCHIVELOG/2015_05_21/thread_1_seq_5.284.880328873

File Name: +data1/CLONE/DATAFILE/SYSTEM.273.880328381

File Name: +data1/CLONE/DATAFILE/SYSAUX.287.880328637

File Name: +data1/CLONE/DATAFILE/UNDOTBS1.286.880328823

File Name: +data1/CLONE/DATAFILE/USERS.285.880328837

File Name: +data1/CLONE/CONTROLFILE/Current.269.880153829

File Name: +data1/CLONE/CONTROLFILE/Current.272.880153829

File Name: +data1/CLONE/CONTROLFILE/Current.271.880154783

File Name: +data1/CLONE/CONTROLFILE/Current.270.880154783

File Name: +data1/CLONE/CONTROLFILE/Current.288.880155389

File Name: +data1/CLONE/CONTROLFILE/Current.280.880155389

File Name: +data1/CLONE/CONTROLFILE/Current.279.880327563

File Name: +data1/DB001/spfileDB001.ora

File Name: +data1/DB001/TEMPFILE/TEMP.262.877261011

File Name: +data1/DB001/ONLINELOG/group_1.261.877260993

File Name: +data1/DB001/ONLINELOG/group_2.257.877260997

File Name: +data1/DB001/ONLINELOG/group_3.258.877260999

File Name: +data1/DB001/CONTROLFILE/Current.263.877260989

File Name: +data1/DB001/DATAFILE/TBS1.266.877304819

File Name: +data1/DB001/DATAFILE/TBS2.267.877305447

File Name: +data1/DB001/DATAFILE/RMAN_TS.268.877478703

File Name: +data1/ASM/ASMPARAMETERFILE/REGISTRY.253.876617431

cataloging files...

cataloging done

List of Cataloged Files

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

File Name: +data1/CLONE/ARCHIVELOG/2015_05_21/thread_1_seq_5.284.880328873

File Name: +data1/CLONE/DATAFILE/SYSTEM.273.880328381

File Name: +data1/CLONE/DATAFILE/SYSAUX.287.880328637

File Name: +data1/CLONE/DATAFILE/UNDOTBS1.286.880328823

File Name: +data1/CLONE/DATAFILE/USERS.285.880328837

File Name: +data1/DB001/TEMPFILE/TEMP.262.877261011

File Name: +data1/DB001/DATAFILE/TBS1.266.877304819

File Name: +data1/DB001/DATAFILE/TBS2.267.877305447

File Name: +data1/DB001/DATAFILE/RMAN_TS.268.877478703

List of Files Which Where Not Cataloged

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

File Name: +data1/CLONE/CONTROLFILE/Current.269.880153829