Active Database Duplication (non-ASM to ASM on same host)

OS: Oracle Linux

DB: 11.2.0.1.0

1. First of all, define storage to duplicate database (Ex:+DATA_DUP,+FRA_DUP)

2. Create a service for new database in tnsnames.ora file

# tnsnames.ora Network Configuration File:

/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

......

DUPDB3 =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVER=DEDICATED)

(SERVICE_NAME = DUPDB3)

)

3. Register the database with the GRID listener statically.

#Need to login as GRID user

#[Loc:/u01/app/oracle/product/11.2.0/grid/network/admin/listener.ora]

Before adding new duplicated database to listener, we have to stop listener.

Then add

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = ora11g)

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

(SID_NAME = ora11g)

)

.......

(SID_DESC =

(GLOBAL_DBNAME = DUPDB3)

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

(SID_NAME = DUPDB3)

)

Then start listener again.

4. Add a line in the file /etc/oratab to reflect the new database instance

DUPDB3:/u01/app/oracle/product/11.2.0/dbhome_1:N

5. Create initialization parameter file initDUPDB3.ora with just one parameter, database name. Other
required parameters will be set in duplicate command using spfile parameter.

#set database name for duplicated (auxiliary instance) database

db_name=DUPDB3

6. Create password file for new database

[oracle11g@orcl11g Desktop]$ cd $ORACLE_HOME/dbs

[oracle11g@orcl11g dbs]$ orapwd file=orapwDUPDB3 entries=5

OR just copy from source

[oracle11g@orcl11g~]cp $ORACLE_HOME/dbs/orapwDUPDB2 $ORACLE_HOME/dbs/orapwDUPDB3

7. Set environment for target database as SID

[oracle11g@orcl11g oracle]$ . oraenv

ORACLE_SID = [ora11g] ? DUPDB2

The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

is /u01/app/oracle

8. Start target(source) database in mount state (for noarchivelog mode this time).

(If the source database is open, then archiving must be enabled)

[oracle11g@orcl11g~]$ sqlplus / as sysdba

SQL> startup mount;

Oracle instance started

Total System Global Area 217157632 bytes

Fixed Size 2211928 bytes

Variable Size 159387560 bytes

Database Buffers 50331648 bytes

Redo Buffers 5226496 bytes

database mounted

9. Set environment for duplicated database as SID

[oracle11g@orcl11g oracle]$ . oraenv

ORACLE_SID = [DUPDB2] ? DUPDB3

The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 is /u01/app/oracle

10. Start auxiliary database in nomount state.

[oracle11g@orcl11g~]$ sqlplus / as sysdba

SQL> startup nomount;

Oracle instance started

Total System Global Area 217157632 bytes

Fixed Size 2211928 bytes

Variable Size 159387560 bytes

Database Buffers 50331648 bytes

Redo Buffers 5226496 bytes

11. Connect to target and auxiliary instance.

[oracle11g@orcl11g~]$ rman target sys/222333@DUPDB2 auxiliary=sys/222333@DUPDB3

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Aug 25 18:07:33 2016

connected to target database: DUPDB2 (DBID=740794324, not open)

connected to auxiliary database: DUPDB3 (not mounted)

RMAN>

12. Now we will duplicate database using SPFILE option in command. There are many alternatives to define
parameters in SPFILE. Most of them will be shown. For full description refer to documentation Backup
and Recovery User's Guide.

Option 1:

RMAN> duplicate target database to DUPDB3 from active database

db_file_name_convert='/u01/app/oracle/oradata/dupdb2','+DATA_DUP'

SPFILE parameter_value_convert '/u01/app/oracle/oradata/dupdb2','+DATA_DUP'

SET control_files='+DATA_DUP','+FRA_DUP'

SET log_file_name_convert='/u01/app/oracle/oradata/dupdb2','+DATA_DUP',

'/u01/app/oracle/flash_recovery_area/dupdb2','+FRA_DUP'

SET db_create_file_dest='+DATA_DUP'

SET db_recovery_file_dest='+FRA_DUP'

password file;

Option 2:

RMAN>duplicate target database to DUPDB3 from active database

SPFILE parameter_value_convert '/u01/app/oracle/oradata/dupdb2','+DATA_DUP'

SET control_files='+DATA_DUP','+FRA_DUP'

SET log_file_name_convert='/u01/app/oracle/oradata/dupdb2','+DATA_DUP',

'/u01/app/oracle/flash_recovery_area/dupdb2','+FRA_DUP'

SET db_file_name_convert='/u01/app/oracle/oradata/dupdb2','+DATA_DUP/dupdb3'

SET db_recovery_file_dest='+FRA_DUP'

password file;

Option 3:

RMAN>duplicate target database to DUPDB3 from active database

SPFILE parameter_value_convert '/u01/app/oracle/oradata/dupdb2','+DATA_DUP'

SET control_files='+DATA_DUP','+FRA_DUP'

SET log_file_name_convert='/u01/app/oracle/oradata/dupdb2','+DATA_DUP',

'/u01/app/oracle/flash_recovery_area/dupdb2','+FRA_DUP'

SET db_file_name_convert='/u01/app/oracle/oradata/dupdb2','+DATA_DUP'

password file;

Note: If FRA not defined in spfile then when DUPDB2's FRA is defined for DUPDB3 as
db_recovery_file_dest='/u01/app/oracle/flash_recovery_area/dupdb2'. And same think is also defined for new
created files as db_create_file_dest='/u01/app/oracle/oradata'.

Option 4:

RMAN>duplicate target database to DUPDB3 from active database

SPFILE

SET control_files='+DATA_DUP','+FRA_DUP'

SET log_file_name_convert='/u01/app/oracle/oradata/dupdb2','+DATA_DUP',

'/u01/app/oracle/flash_recovery_area/dupdb2','+FRA_DUP'

SET db_file_name_convert='/u01/app/oracle/oradata/dupdb2','+DATA_DUP'

SET db_recovery_file_dest='+FRA_DUP'

SET db_create_file_dest='+DATA_DUP'

password file;

Option 5: It doesn't work because db_file_name_convert parameter is not defined.

RMAN>duplicate target database to DUPDB3 from active database

SPFILE parameter_value_convert '/u01/app/oracle/oradata/dupdb2','+DATA_DUP'

SET control_files='+DATA_DUP','+FRA_DUP'

SET log_file_name_convert='/u01/app/oracle/oradata/dupdb2','+DATA_DUP',

'/u01/app/oracle/flash_recovery_area/dupdb2','+FRA_DUP'

SET db_create_file_dest='+DATA_DUP'

SET db_recovery_file_dest='+FRA_DUP'

password file;

Note: In all options the following warning message occurs, which not affect the duplication process:

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

Cause: It was not possible to convert ASM Oracle Managed Files names using DB_FILE_NAME_CONVERT parameter. RMAN changed these invalid names to
the converted disk group name instead.

       Action: No action is required. This is an informational message only. If the automatic change is incorrect, use one of the following options instead of using
                          DB_FILE_NAME_CONVERT for ASM Oracle Managed Files: 1) use RMAN command SET NEWNAME for each Oracle Managed File.
                          2) set DB_CREATE_FILE_DEST initialization parameter in auxiliary instance and not specify DB_FILE_NAME_CONVERT.

Duplication process continue...

Starting Duplicate Db at 23-SEP-16

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=10 device type=DISK

contents of Memory Script:

{

backup as copy reuse

targetfile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwDUPDB2' auxiliary format

'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwDUPDB3' targetfile

'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileDUPDB2.ora' auxiliary format

'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileDUPDB3.ora' ;

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

}

executing Memory Script

Starting backup at 23-SEP-16

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=9 device type=DISK

Finished backup at 23-SEP-16

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

contents of Memory Script:

{

sql clone "alter system set db_name = ''DUPDB3'' comment=

''duplicate'' scope=spfile";

sql clone "alter system set control_files =

''+DATA_DUP'', ''+FRA_DUP'' comment=

'''' scope=spfile";

sql clone "alter system set log_file_name_convert =

''/u01/app/oracle/oradata/dupdb2'', ''+DATA_DUP'', ''/u01/app/oracle/flash_recovery_area/dupdb2'', ''+FRA_DUP'' comment=

'''' scope=spfile";

sql clone "alter system set db_file_name_convert =

''/u01/app/oracle/oradata/dupdb2'', ''+DATA_DUP'' comment=

'''' scope=spfile";

sql clone "alter system set db_recovery_file_dest =

''+FRA_DUP'' comment=

'''' scope=spfile";

sql clone "alter system set db_create_file_dest =

''+DATA_DUP'' comment=

'''' scope=spfile";

shutdown clone immediate;

startup clone nomount;

}

executing Memory Script

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

sql statement: alter system set control_files = ''+DATA_DUP'', ''+FRA_DUP'' comment= '''' scope=spfile

sql statement: alter system set log_file_name_convert = ''/u01/app/oracle/oradata/dupdb2'', ''+DATA_DUP'', ''/u01/app/oracle/flash_recovery_area/dupdb2'', ''+FRA_DUP'' comment= '''' scope=spfile

sql statement: alter system set db_file_name_convert = ''/u01/app/oracle/oradata/dupdb2'', ''+DATA_DUP'' comment= '''' scope=spfile

sql statement: alter system set db_recovery_file_dest = ''+FRA_DUP'' comment= '''' scope=spfile

sql statement: alter system set db_create_file_dest = ''+DATA_DUP'' comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area 2388422656 bytes

Fixed Size 2215664 bytes

Variable Size 1358954768 bytes

Database Buffers 1006632960 bytes

Redo Buffers 20619264 bytes

contents of Memory Script:

{

sql clone "alter system set control_files =

''+DATA_DUP/dupdb3/controlfile/current.280.923341755'', ''+FRA_DUP/dupdb3/controlfile/current.278.923341755'' comment=

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

sql clone "alter system set db_name =

''DUPDB2'' comment=

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

sql clone "alter system set db_unique_name =

''DUPDB3'' comment=

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

shutdown clone immediate;

startup clone force nomount

backup as copy current controlfile auxiliary format '+DATA_DUP/dupdb3/controlfile/current.276.923341755';

restore clone controlfile to '+FRA_DUP/dupdb3/controlfile/current.276.923341755' from

'+DATA_DUP/dupdb3/controlfile/current.276.923341755';

sql clone "alter system set control_files =

''+DATA_DUP/dupdb3/controlfile/current.276.923341755'', ''+FRA_DUP/dupdb3/controlfile/current.276.923341755'' 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 = ''+DATA_DUP/dupdb3/controlfile/current.280.923341755'', ''+FRA_DUP/dupdb3/controlfile/current.278.923341755'' comment= ''Set by RMAN'' scope=spfile

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

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

Oracle instance shut down

Oracle instance started

Total System Global Area 2388422656 bytes

Fixed Size 2215664 bytes

Variable Size 1358954768 bytes

Database Buffers 1006632960 bytes

Redo Buffers 20619264 bytes

Starting backup at 23-SEP-16

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/dbhome_1/dbs/snapcf_DUPDB2.f tag=TAG20160923T194920 RECID=15 STAMP=923341762

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

Finished backup at 23-SEP-16

Starting restore at 23-SEP-16

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=137 device type=DISK

channel ORA_AUX_DISK_1: copied control file copy

Finished restore at 23-SEP-16

sql statement: alter system set control_files = ''+DATA_DUP/dupdb3/controlfile/current.276.923341755'', ''+FRA_DUP/dupdb3/controlfile/current.276.923341755'' comment= ''Set by RMAN'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area 2388422656 bytes

Fixed Size 2215664 bytes

Variable Size 1358954768 bytes

Database Buffers 1006632960 bytes

Redo Buffers 20619264 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 "+DATA_DUP/system.dbf";

set newname for datafile 2 to "+DATA_DUP/sysaux.dbf";

set newname for datafile 3 to "+DATA_DUP/undotbs1.dbf";

set newname for datafile 4 to "+DATA_DUP/users.dbf";

set newname for datafile 5 to "+DATA_DUP/example.dbf";

set newname for datafile 6 to "+DATA_DUP/rcat_data_01.dbf";

backup as copy reuse

   datafile 1 auxiliary format "+DATA_DUP/system.dbf"
   datafile 2 auxiliary format "+DATA_DUP/sysaux.dbf"
   datafile 3 auxiliary format "+DATA_DUP/undotbs1.dbf"

datafile 4 auxiliary format "+DATA_DUP/users.dbf"

datafile 5 auxiliary format "+DATA_DUP/example.dbf"

datafile 6 auxiliary format "+DATA_DUP/rcat_data_01.dbf" ;

}

executing Memory Script

executing command: SET NEWNAME

Starting backup at 23-SEP-16

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

input datafile file number=00004 name=/u01/app/oracle/oradata/dupdb2/users.dbf

output file name=+DATA_DUP/users.dbf tag=TAG20160923T194949

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:11:26

channel ORA_DISK_1: starting datafile copy

input datafile file number=00001 name=/u01/app/oracle/oradata/dupdb2/system.dbf

output file name=+DATA_DUP/system.dbf tag=TAG20160923T194949

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

channel ORA_DISK_1: starting datafile copy

input datafile file number=00002 name=/u01/app/oracle/oradata/dupdb2/sysaux.dbf

output file name=+DATA_DUP/sysaux.dbf tag=TAG20160923T194949

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

channel ORA_DISK_1: starting datafile copy

input datafile file number=00003 name=/u01/app/oracle/oradata/dupdb2/undotbs1.dbf

output file name=+DATA_DUP/undotbs1.dbf tag=TAG20160923T194949

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

channel ORA_DISK_1: starting datafile copy

input datafile file number=00005 name=/u01/app/oracle/oradata/dupdb2/example.dbf

output file name=+DATA_DUP/example.dbf tag=TAG20160923T194949

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

channel ORA_DISK_1: starting datafile copy

input datafile file number=00006 name=/u01/app/oracle/oradata/dupdb2/rcat_data_01.dbf

output file name=+DATA_DUP/rcat_data_01.dbf tag=TAG20160923T194949

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

Finished backup at 23-SEP-16

contents of Memory Script:

{

switch clone datafile all;

}

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy RECID=15 STAMP=923342862 file name=+DATA_DUP/system.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=16 STAMP=923342862 file name=+DATA_DUP/sysaux.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=17 STAMP=923342862 file name=+DATA_DUP/undotbs1.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=18 STAMP=923342862 file name=+DATA_DUP/users.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=19 STAMP=923342862 file name=+DATA_DUP/example.dbf

datafile 6 switched to datafile copy

input datafile copy RECID=20 STAMP=923342862 file name=+DATA_DUP/rcat_data_01.dbf

contents of Memory Script:

{

recover

clone database

noredo

delete archivelog

;

}

executing Memory Script

Starting recover at 23-SEP-16

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=137 device type=DISK

Finished recover at 23-SEP-16

contents of Memory Script:

{

shutdown clone immediate;

startup clone nomount;

sql clone "alter system set db_name =

''DUPDB3'' comment=

''Reset to original value by RMAN'' scope=spfile";

sql clone "alter system reset db_unique_name scope=spfile";

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 2388422656 bytes

Fixed Size 2215664 bytes

Variable Size 1358954768 bytes

Database Buffers 1006632960 bytes

Redo Buffers 20619264 bytes

sql statement: alter system set db_name = ''DUPDB3'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area 2388422656 bytes

Fixed Size 2215664 bytes

Variable Size 1358954768 bytes

Database Buffers 1006632960 bytes

Redo Buffers 20619264 bytes

sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DUPDB3" RESETLOGS NOARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 584

LOGFILE

GROUP 1 ( '+data_dup', '+fra_dup' ) SIZE 50 M REUSE,

GROUP 2 ( '+data_dup', '+fra_dup' ) SIZE 50 M REUSE,

GROUP 3 ( '+data_dup', '+fra_dup' ) SIZE 50 M REUSE

DATAFILE

'+DATA_DUP/system.dbf'

CHARACTER SET AL32UTF8

contents of Memory Script:

{

set newname for tempfile 1 to

"+data_dup";

switch clone tempfile all;

catalog clone datafilecopy "+DATA_DUP/sysaux.dbf",

"+DATA_DUP/undotbs1.dbf",

"+DATA_DUP/users.dbf",

"+DATA_DUP/example.dbf",

"+DATA_DUP/rcat_data_01.dbf";

switch clone datafile all;

}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +data_dup in control file

cataloged datafile copy

datafile copy file name=+DATA_DUP/sysaux.dbf RECID=1 STAMP=923342897

cataloged datafile copy

datafile copy file name=+DATA_DUP/undotbs1.dbf RECID=2 STAMP=923342897

cataloged datafile copy

datafile copy file name=+DATA_DUP/users.dbf RECID=3 STAMP=923342897

cataloged datafile copy

datafile copy file name=+DATA_DUP/example.dbf RECID=4 STAMP=923342897

cataloged datafile copy

datafile copy file name=+DATA_DUP/rcat_data_01.dbf RECID=5 STAMP=923342897

datafile 2 switched to datafile copy

input datafile copy RECID=1 STAMP=923342897 file name=+DATA_DUP/sysaux.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=2 STAMP=923342897 file name=+DATA_DUP/undotbs1.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=3 STAMP=923342897 file name=+DATA_DUP/users.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=4 STAMP=923342897 file name=+DATA_DUP/example.dbf

datafile 6 switched to datafile copy

input datafile copy RECID=5 STAMP=923342897 file name=+DATA_DUP/rcat_data_01.dbf

contents of Memory Script:

{

Alter clone database open resetlogs;

}

executing Memory Script

database opened

Finished Duplicate Db at 23-SEP-16

12. Now define SPFILE to start from ASM and edit the pfile to point to the SPFILE in ASM:

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

ORACLE instance started.

Total System Global Area 2388422656 bytes

Fixed Size 2215664 bytes

Variable Size 1358954768 bytes

Database Buffers 1006632960 bytes

Redo Buffers 20619264 bytes

SQL> create spfile='+DATA_DUP' from pfile='$ORACLE_HOME/dbs/initDUPDB3.ora';

File created.

SQL> shutdown immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup;

ORACLE instance started.

Total System Global Area 2388422656 bytes

Fixed Size 2215664 bytes

Variable Size 1358954768 bytes

Database Buffers 1006632960 bytes

Redo Buffers 20619264 bytes

Database mounted.

Database opened.

SQL> show parameter spfile;

NAME TYPE VALUE

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

spfile string /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileDUPDB3.ora

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

(first we search initial location and name of spfile in ASM then use mkalias if needed and define new name and location for spfile as in ASM to non-ASM post)

cat > initDUPDB3.ora

SPFILE='+DATA_DUP/dupdb3/spfileDUPDB3.ora'

PRESS CTRL+D

SQL> startup;

ORACLE instance started.

Total System Global Area 2388422656 bytes

Fixed Size 2215664 bytes

Variable Size 1358954768 bytes

Database Buffers 1006632960 bytes

Redo Buffers 20619264 bytes

Database mounted.

Database opened.

SQL> sho parameter spfile;

NAME TYPE VALUE

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

spfile string +DATA_DUP/dupdb3/spfiledupdb3.ora

That is it!!!

All about Databases - Oracle, PostgeSQL, Mysql, Big Data .....

Monday, November 7, 2016

Active Database Duplication (non-ASM to ASM on same host)

OS: Oracle Linux

No comments:

Post a Comment