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

OS: Oracle Linux
DB: 11.2.0.1.0

1. Create pfile from target (source) database (here ora11g)

oracle11g@orcl11g oracle]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Aug 25 12:18:27 2016

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

SQL> create pfile='$ORACLE_HOME/dbs/initsource.ora' from spfile;

File created.

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.

......

DUPDB2 =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVER=DEDICATED)

(SERVICE_NAME = DUPDB2)

)

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

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

4. Set environment for duplicated 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

5. Create folders in destination host for duplicated database

[oracle11g@orcl11g oracle]$ mkdir -p /u01/app/oracle/oradata/dupdb2

[oracle11g@orcl11g oracle]$ mkdir -p /u01/app/oracle/flash_recovery_area/dupdb2

[oracle11g@orcl11g oracle]$ mkdir -p /u01/app/oracle/admin/dupdb2/adump

[oracle11g@orcl11g oracle]$ mkdir -p /u01/app/oracle/admin/dupdb2/dpdump

6. Copy the initialization parameter file from the target (source) database

oracle11g@orcl11g oracle]$ cp $ORACLE_HOME/dbs/initsource.ora

/u01/app/oracle/oradata/dupdb2/inittarget.ora

7. Edit the pfile inittarget.ora as:

ora11g.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
ora11g.__pga_aggregate_target=973078528

ora11g.__sga_target=1426063360

ora11g.__shared_io_pool_size=0

ora11g.__shared_pool_size=536870912

ora11g.__streams_pool_size=33554432

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.db_block_size=8192

*.db_domain=''

*.db_name='DUPDB2'

*.db_recovery_file_dest_size=21474836480

*.diagnostic_dest='/u01/app/oracle'

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

*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST'

*.memory_target=2394947584

*.nls_date_format='mm/dd/yyyy hh24:mi:ss'

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

######new added lines##############

db_file_name_convert=("+DATA/ora11g/datafile","/u01/app/oracle/oradata/dupdb2",

"+DATA/ora11g/tempfile","/u01/app/oracle/oradata/dupdb2")

log_file_name_convert=("+DATA/ora11g/onlinelog","/u01/app/oracle/oradata/dupdb2",
"+FRA/ora11g/onlinelog","/u01/app/oracle/flash_recovery_area/dupdb2")

######modified lines###############

*.audit_file_dest='/u01/app/oracle/admin/dupdb2/adump'

*.db_create_file_dest='/u01/app/oracle/oradata'

*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area/dupdb2'

*.control_files='/u01/app/oracle/oradata/dupdb2/control01.ctl',

'/u01/app/oracle/flash_recovery_area/dupdb2/control02.ctl'#Restore Controlfile

###################################

8. Copy the password file as well

oracle11g@orcl11g oracle]$ cp $ORACLE_HOME/dbs/orapwora11g

$ORACLE_HOME/dbs/orapwDUPDB2

9. Startup the target database in nomount mode using modified parameter file and create spfile from it.

SQL> startup nomount pfile='/u01/app/oracle/oradata/dupdb2/inittarget.ora';

ORACLE instance started.

Total System Global Area 2388422656 bytes

Fixed Size 2215664 bytes

Variable Size 1342177552 bytes

Database Buffers 1023410176 bytes

Redo Buffers 20619264 bytes

SQL> sho parameter db_name

NAME TYPE VALUE

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

db_name string DUPDB2

SQL> create spfile from pfile='/u01/app/oracle/oradata/dupdb2/inittarget.ora';

File created.

10.Edit listener.ora file

(/u01/app/grid/product/11.2.0/grid/network/admin/listener.ora)

Before editing we have to stop listener([grid@orcl11ggrid]$lsnrctl stop)

......

(SID_DESC =

(GLOBAL_DBNAME = DUPDB2)

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

(SID_NAME = DUPDB2)

)

.....

Start listener ([grid@orcl11g grid]$ lsnrctl start)

11.Connect to target and auxiliary instance then duplicate database

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

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

connected to target database: ORA11G (DBID=4280113117)

connected to auxiliary database: DUPDB2 (not mounted)

RMAN> duplicate target database to DUPDB2 from active database;

Starting Duplicate Db at 25-AUG-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:

{

sql clone "create spfile from memory";

}

executing Memory Script

sql statement: create spfile from memory

contents of Memory Script:

{

shutdown clone immediate;

startup clone nomount;

}

executing Memory Script

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 db_name = ''ORA11G''

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

sql clone "alter system set db_unique_name = ''DUPDB2''

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/dupdb2/control01.ctl';

restore clone controlfile to

'/u01/app/oracle/flash_recovery_area/dupdb2/control02.ctl' from

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

alter clone database mount;

}

executing Memory Script

sql statement: alter system set db_name = ''ORA11G''

comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set db_unique_name = ''DUPDB2''

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 25-AUG-16

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=10 device type=DISK

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_ora11g.f

tag=TAG20160825T180918 RECID=35 STAMP=920830161

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

Finished backup at 25-AUG-16

Starting restore at 25-AUG-16

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=9 device type=DISK

channel ORA_AUX_DISK_1: copied control file copy

Finished restore at 25-AUG-16

database mounted

contents of Memory Script:

{

set newname for datafile 1 to "/u01/app/oracle/oradata/dupdb2/system.dbf";

set newname for datafile 2 to "/u01/app/oracle/oradata/dupdb2/sysaux.dbf";

set newname for datafile 3 to "/u01/app/oracle/oradata/dupdb2/undotbs1.dbf";

set newname for datafile 4 to "/u01/app/oracle/oradata/dupdb2/users.dbf";

set newname for datafile 5 to "/u01/app/oracle/oradata/dupdb2/example.dbf";

set newname for datafile6to "/u01/app/oracle/oradata/dupdb2/rcat_data_01.dbf";

backup as copy reuse

datafile 1 auxiliary format "/u01/app/oracle/oradata/dupdb2/system.dbf"

datafile 2 auxiliary format "/u01/app/oracle/oradata/dupdb2/sysaux.dbf"

datafile 3 auxiliary format "/u01/app/oracle/oradata/dupdb2/undotbs1.dbf"

datafile 4 auxiliary format "/u01/app/oracle/oradata/dupdb2/users.dbf"

datafile 5 auxiliary format "/u01/app/oracle/oradata/dupdb2/example.dbf"

datafile 6 auxiliary format "/u01/app/oracle/oradata/dupdb2/rcat_data_01.dbf";

sql 'alter system archive log current';

}

executing Memory Script

executing command: SET NEWNAME

Starting backup at 25-AUG-16

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

input datafile file number=00004 name=+DATA/ora11g/datafile/users.dbf

output file name=/u01/app/oracle/oradata/dupdb2/users.dbf

tag=TAG20160825T180929

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

channel ORA_DISK_1: starting datafile copy

input datafile file number=00001 name=+DATA/ora11g/datafile/system.dbf

output file name=/u01/app/oracle/oradata/dupdb2/system.dbf

tag=TAG20160825T180929

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

channel ORA_DISK_1: starting datafile copy

input datafile file number=00002 name=+DATA/ora11g/datafile/sysaux.dbf

output file name=/u01/app/oracle/oradata/dupdb2/sysaux.dbf

tag=TAG20160825T180929

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

channel ORA_DISK_1: starting datafile copy

input datafile file number=00003 name=+DATA/ora11g/datafile/undotbs1.dbf

output file name=/u01/app/oracle/oradata/dupdb2/undotbs1.dbf

tag=TAG20160825T180929

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

channel ORA_DISK_1: starting datafile copy

input datafile file number=00005 name=+DATA/ora11g/datafile/example.dbf

output file name=/u01/app/oracle/oradata/dupdb2/example.dbf

tag=TAG20160825T180929

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

channel ORA_DISK_1: starting datafile copy

input datafile file number=00006 name=+DATA/ora11g/datafile/rcat_data_01.dbf

output file name=/u01/app/oracle/oradata/dupdb2/rcat_data_01.dbf

tag=TAG20160825T180929

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

Finished backup at 25-AUG-16

sql statement: alter system archive log current

contents of Memory Script:

{

backup as copy reuse

archivelog like

"+FRA/ora11g/archivelog/2016_08_25/thread_1_seq_537.748.920830477"

auxiliary format

"/u01/app/oracle/flash_recovery_area/dupdb2/DUPDB2/archivelog/2016_08_25/o1_mf_1_537_%u_.arc" ;

catalog clone recovery area;

switch clone datafile all;

}

executing Memory Script

Starting backup at 25-AUG-16

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log copy

input archived log thread=1 sequence=537 RECID=662 STAMP=920830479

output file

name=/u01/app/oracle/flash_recovery_area/dupdb2/DUPDB2/archivelog/2016_08_25/

o1_mf_1_537_4nre5ggi_.arc RECID=0 STAMP=0

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

Finished backup at 25-AUG-16

searching for all files in the recovery area

List of Files Unknown to the Database

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

File Name: /u01/app/oracle/flash_recovery_area/dupdb2/DUPDB2/archivelog/2016_08_25/o1_mf_1_537_4nre5ggi_.arc

cataloging files...

cataloging done

List of Cataloged Files

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

File Name: /u01/app/oracle/flash_recovery_area/dupdb2/DUPDB2/archivelog/2016_08_25/o1_mf_1_537_4nre5ggi_.arc

datafile 1 switched to datafile copy

input datafile copy RECID=35 STAMP=920830485 file

name=/u01/app/oracle/oradata/dupdb2/system.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=36 STAMP=920830485 file

name=/u01/app/oracle/oradata/dupdb2/sysaux.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=37 STAMP=920830485 file

name=/u01/app/oracle/oradata/dupdb2/undotbs1.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=38 STAMP=920830485 file

name=/u01/app/oracle/oradata/dupdb2/users.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=39 STAMP=920830485 file

name=/u01/app/oracle/oradata/dupdb2/example.dbf

datafile 6 switched to datafile copy

input datafile copy RECID=40 STAMP=920830485 file

name=/u01/app/oracle/oradata/dupdb2/rcat_data_01.dbf

contents of Memory Script:

{

set until scn 3955558;

recover

clone database

delete archivelog;

}

executing Memory Script

executing command: SET until clause

Starting recover at 25-AUG-16

using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 537 is already on disk as file /u01/app/oracle/flash_recovery_area/dupdb2/DUPDB2/archivelog/2016_08_25/o1_mf_1_537_4nre5ggi_.arc

archived log file name=/u01/app/oracle/flash_recovery_area/dupdb2/DUPDB2/archivelog/2016_08_25/o1_mf_1_537_4nre5ggi_.arc thread=1 sequence=537

media recovery complete, elapsed time: 00:00:04

Finished recover at 25-AUG-16

contents of Memory Script:

{

shutdown clone immediate;

startup clone nomount;

sql clone "alter system set db_name = ''DUPDB2''

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 = ''DUPDB2''

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 "DUPDB2" RESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 584

LOGFILE

GROUP 1 ('/u01/app/oracle/oradata/dupdb2/group_1.259.887811641',

'/u01/app/oracle/flash_recovery_area/dupdb2/group_1.269.887811647')

SIZE 50 M REUSE,

GROUP 2 ('/u01/app/oracle/oradata/dupdb2/group_2.269.887811653',

'/u01/app/oracle/flash_recovery_area/dupdb2/group_2.273.887811659')

SIZE 50 M REUSE,

GROUP 3 ('/u01/app/oracle/oradata/dupdb2/group_3.270.887811663',

'/u01/app/oracle/flash_recovery_area/dupdb2/group_3.272.887811669')

SIZE 50 M REUSE

DATAFILE '/u01/app/oracle/oradata/dupdb2/system.dbf'

CHARACTER SET AL32UTF8

contents of Memory Script:

{

set newname for tempfile 1 to

"/u01/app/oracle/oradata/dupdb2/temp.265.883913047";

switch clone tempfile all;

catalog clone datafilecopy "/u01/app/oracle/oradata/dupdb2/sysaux.dbf",

"/u01/app/oracle/oradata/dupdb2/undotbs1.dbf",

"/u01/app/oracle/oradata/dupdb2/users.dbf",

"/u01/app/oracle/oradata/dupdb2/example.dbf",

"/u01/app/oracle/oradata/dupdb2/rcat_data_01.dbf";

switch clone datafile all;

}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/dupdb2/temp.265.883913047

in control file

cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/dupdb2/sysaux.dbf RECID=1

STAMP=920830509

cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/dupdb2/undotbs1.dbf RECID=2

STAMP=920830509

cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/dupdb2/users.dbf RECID=3

STAMP=920830509

cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/dupdb2/example.dbf RECID=4

STAMP=920830509

cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/dupdb2/rcat_data_01.dbf RECID=5

STAMP=920830509

datafile 2 switched to datafile copy input datafile copy RECID=1 STAMP=920830509

file name=/u01/app/oracle/oradata/dupdb2/sysaux.dbf

datafile 3 switched to datafile copy input datafile copy RECID=2 STAMP=920830509

file name=/u01/app/oracle/oradata/dupdb2/undotbs1.dbf

datafile 4 switched to datafile copy input datafile copy RECID=3 STAMP=920830509

file name=/u01/app/oracle/oradata/dupdb2/users.dbf

datafile 5 switched to datafile copy input datafile copy RECID=4 STAMP=920830509

file name=/u01/app/oracle/oradata/dupdb2/example.dbf

datafile 6 switched to datafile copy input datafile copy RECID=5 STAMP=920830509

file name=/u01/app/oracle/oradata/dupdb2/rcat_data_01.dbf

contents of Memory Script:

{

Alter clone database open resetlogs;

}

executing Memory Script

database opened

Finished Duplicate Db at 25-AUG-16

RMAN>

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

Sunday, November 6, 2016

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

No comments:

Post a Comment