Active Database Duplication (ASM to ASM on same host)

OS: Oracle Linux
DB: 11.2.0.1.0

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

2. Check all datafiles from RMAN
RMAN> report schema;

using target database control file instead of recovery catalog

Report of database schema for database with db_unique_name ORA11G

List of Permanent Datafiles

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

File Size(MB) Tablespace RB segs Datafile Name

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

1 880 SYSTEM *** +DATA/ora11g/datafile/system.dbf

2 630 SYSAUX *** +DATA/ora11g/datafile/sysaux.dbf

3 200 UNDOTBS1 *** +DATA/ora11g/datafile/undotbs1.dbf

4 3833 USERS *** +DATA/ora11g/datafile/users.dbf

5 100 EXAMPLE *** +DATA/ora11g/datafile/example.dbf

6 60 RCAT_DATA *** +DATA/ora11g/datafile/rcat_01.dbf

List of Temporary Files

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

File Size(MB) Tablespace Maxsize(MB) Tempfile Name

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

1 29 TEMP 32767 +DATA/ora11g/tempfile/temp.265.883913047

3. Waits until the online redo log has completed the writing of the redo log file to the file system
(optional)

RMAN> sql 'alter system archive log current';

(more about switch logfile and archivelog current refer to
http://www.dba-oracle.com/t_alter_system_switch_logfile_vs_alter_system_archivelog_current.htm)

4. Create pfile from spfile.

SQL> create pfile='initDUPDB.ora' from spfile;

File created.

5. Edit initDUPDB.ora (created at $ORACLE_HOME/dbs)as

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

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.db_block_size=8192

*.db_domain=''

*.db_create_file_dest='+DATA_DUP'

*.control_files= ('+DATA_DUP','+FRA_DUP')

*.db_name='DUPDB'

*.db_file_name_convert=('+DATA','+DATA_DUP')

*.log_file_name_convert=('+DATA','+DATA_DUP','+FRA','+FRA_DUP')

*.db_recovery_file_dest='+FRA_DUP'

*.db_recovery_file_dest_size=8474836480

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

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

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

6. Add the below entry in the /etc/oratab file

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

7. Define environment for new database

[oracle11g@orcl11g Desktop]$ . oraenv

ORACLE_SID = [ora11g] ? DUPDB

The Oracle base for

ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 is /u01/app/oracle

(more about . oraenv

reference: http://www.orafaq.com/wiki/oraenv

http://www.dba-oracle.com/t_linux_oracle_oraenv.htm)

8. Start new duplicated database in nomount mode

oracle11g@orcl11g Desktop]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1 Production on Tue Aug 23 10:24:14 2016

Connected to an idle instance.

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

SQL> show parameter db_name

NAME TYPE VALUE

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

db_name string DUPDB

SQL> show parameter convert

NAME TYPE VALUE

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

db_file_name_convert string +DATA, +DATA_DUP

log_file_name_convert string +DATA, +DATA_DUP, +FRA, +FRA_DUP

9. Create the TNS ENTRY in the tnsnames.ora file

[loc:/u01/app/oracle/product/11.2.0/dbhome_1/network/admin]

DUPDB =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVER=DEDICATED)

(SERVICE_NAME = DUPDB)

)

10.Register the database with the GRID LISTENER statically.

#Need to login as GRID

#[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

[grid@orcl11g ~]$ lsnrctl stop

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 23-AUG-2016 10:41:16

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)

(KEY=EXTPROC1521)))

The command completed successfully

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 = DUPDB)

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

(SID_NAME = DUPDB)

)

11.Then start listener with

[grid@orcl11g ~]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.1.0 -

Production on 23-AUG-2016 10:41:25

Starting /u01/app/grid/product/11.2.0/grid/bin/tnslsnr:

please wait...

TNSLSNR for Linux: Version 11.2.0.1.0 - Production System parameter file is

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

Log messages written to

/u01/app/grid/diag/tnslsnr/orcl11g/listener/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)

(KEY=EXTPROC1521)))

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=orcl11g)

(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)

(KEY=EXTPROC1521)))

STATUS of the LISTENER

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

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.1.0 Production

Start Date 23-AUG-2016 10:41:25

Uptime 0 days 0 hr. 0 min. 0 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File

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

Listener Log File

/u01/app/grid/diag/tnslsnr/orcl11g/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=orcl11g)(PORT=1521)))

Services Summary...

Service "DUPDB" has 1 instance(s).

Instance "DUPDB", status UNKNOWN, has 1 handler(s) for this

service...

Service "ora11g" has 1 instance(s).

Instance "ora11g", status UNKNOWN, has 1 handler(s) for this

service...

The command completed successfully

12.Check TNS

[oracle11g@orcl11g Desktop]$ tnsping DUPDB

TNS Ping Utility for Linux: Version 11.2.0.1.0 -

Production on 23-AUG-2016 10:45:14

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact

(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)

(HOST = orcl11g)(PORT = 1521)))

(CONNECT_DATA = (SERVER=DEDICATED) (SERVICE_NAME = DUPDB)))

OK (10 msec)

13.Create password file for new database

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

(must change path as this before creating password file)

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

Enter password for SYS:

[oracle11g@orcl11g dbs]$

NOTE:It is important to make database name and service name in same

case (uppercase or lowercase in all files: listener.ora, initDUPDB.ora, tnsnames.ora)

References:
http://selvathiruppathi.blogspot.com/2012/01/connection-to-rman-auxiliary-fails with.html

http://manuoracle.blogspot.com/2013/05/rman-04006-error-from-auxiliary.html

NOTE: Active duplication requires that the source and auxiliary instances use the same password as the source database, whereas backup-based duplication without connections to the target database and recovery catalog requires only that all backups and database copies reside in a single location.

14.Initiate duplicate process

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

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Aug 23

10:51:29 2016

connected to target database: ORA11G (DBID=4280113117)

connected to auxiliary database: DUPDB (not mounted)

RMAN>

15.Check number of datafiles and tempfiles (or we can see from step 2 RMAN>report schema)

continue from SYS terminal

[oracle11g@orcl11g dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Aug 23

11:03:04 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> conn sys as sysdba

Enter password:

Connected.

SQL> sho parameter db_name

NAME TYPE VALUE

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

db_name string ora11g

SQL> select file_name from dba_data_files;

FILE_NAME

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

+DATA/ora11g/datafile/users.dbf

+DATA/ora11g/datafile/undotbs1.dbf

+DATA/ora11g/datafile/sysaux.dbf

+DATA/ora11g/datafile/system.dbf

+DATA/ora11g/datafile/example.dbf

+DATA/ora11g/datafile/rcat_data_01.dbf

6 rows selected.

SQL> select name from v$tempfile;

NAME

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

+DATA/ora11g/tempfile/temp.265.883913047

16. Continue from RMAN terminal (from step 14)

RMAN> run {

2> SET NEWNAME FOR DATAFILE 1 TO "+DATA_DUP";

3> SET NEWNAME FOR DATAFILE 2 TO "+DATA_DUP";

4> SET NEWNAME FOR DATAFILE 3 TO "+DATA_DUP";

5> SET NEWNAME FOR DATAFILE 4 TO "+DATA_DUP";

6> SET NEWNAME FOR DATAFILE 5 TO "+DATA_DUP";

7> SET NEWNAME FOR DATAFILE 6 TO "+DATA_DUP";

8> SET NEWNAME FOR TEMPFILE 1 TO "+DATA_DUP";

9> DUPLICATE TARGET DATABASE TO DUPDB FROM ACTIVE DATABASE;

10> }

executing command: SET NEWNAME

Starting Duplicate Db at 23-AUG-16

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=99 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 217157632 bytes

Fixed Size 2211928 bytes

Variable Size 159387560 bytes

Database Buffers 50331648 bytes

Redo Buffers 5226496 bytes

contents of Memory Script:

{

sql clone "alter system set control_files =

''+DATA_DUP/dupdb/controlfile/current.258.920634865'',

''+FRA_DUP/dupdb/controlfile/current.258.920634865''

comment=

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

sql clone "alter system set db_name =

''ORA11G'' comment=

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

sql clone "alter system set db_unique_name =

''DUPDB'' comment=

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

shutdown clone immediate;

startup clone force nomount

backup as copy current controlfile auxiliary format

'+DATA_DUP/dupdb/controlfile/current.259.920634865';

restore clone controlfile to

'+FRA_DUP/dupdb/controlfile/current.259.920634865' from

'+DATA_DUP/dupdb/controlfile/current.259.920634865';

sql clone "alter system set control_files =

''+DATA_DUP/dupdb/controlfile/current.259.920634865'',

''+FRA_DUP/dupdb/controlfile/current.259.920634865'' 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/dupdb/controlfile/current.258.920634865'',

''+FRA_DUP/dupdb/ controlfile/current.258.920634865''

comment= ''Set by RMAN'' scope=spfile

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

''Modified by RMAN duplicate'' scope=spfile

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

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

Oracle instance shut down

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

Starting backup at 23-AUG-16

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=147 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=TAG20160823T115431 RECID=30 STAMP=920634875

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

Finished backup at 23-AUG-16

Starting restore at 23-AUG-16

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=10 device type=DISK

channel ORA_AUX_DISK_1: copied control file copy

Finished restore at 23-AUG-16

sql statement: alter system set control_files =

''+DATA_DUP/dupdb/controlfile/current.259.920634865'',

''+FRA_DUP/dupdb/ controlfile/current.259.920634865''

comment= ''Set by RMAN'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)

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

contents of Memory Script:

{

set newname for datafile 1 to "+DATA_DUP";

set newname for datafile 2 to "+DATA_DUP";

set newname for datafile 3 to "+DATA_DUP";

set newname for datafile 4 to "+DATA_DUP";

set newname for datafile 5 to "+DATA_DUP";

set newname for datafile 6 to "+DATA_DUP";

backup as copy reuse

datafile 1 auxiliary format "+DATA_DUP"

datafile 2 auxiliary format "+DATA_DUP"

datafile 3 auxiliary format "+DATA_DUP"

datafile 4 auxiliary format "+DATA_DUP"

datafile 5 auxiliary format "+DATA_DUP"

datafile 6 auxiliary format "+DATA_DUP";

sql 'alter system archive log current';

}

executing Memory Script

executing command: SET NEWNAME

Starting backup at 23-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

loutput file name=+DATA_DUP/dupdb/datafile/users.260.920634909

tag=TAG20160823T115508

channel ORA_DISK_1: datafile copy complete, elapsed time:

00:19:25

channel ORA_DISK_1: starting datafile copy

input datafile file number=00001

name=+DATA/ora11g/datafile/system.dbf

output file name=+DATA_DUP/dupdb/datafile/system.261.920636075

tag=TAG20160823T115508

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

channel ORA_DISK_1: starting datafile copy

input datafile file number=00002

name=+DATA/ora11g/datafile/sysaux.dbf

output file name=+DATA_DUP/dupdb/datafile/sysaux.262.920636381

tag=TAG20160823T115508

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

channel ORA_DISK_1: starting datafile copy

input datafile file number=00003

name=+DATA/ora11g/datafile/undotbs1.dbf

output file name=+DATA_DUP/dupdb/datafile/undotbs1.263.920636587

tag=TAG20160823T115508

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

channel ORA_DISK_1: starting datafile copy

input datafile file number=00005

name=+DATA/ora11g/datafile/example.dbf

output file name=+DATA_DUP/dupdb/datafile/example.264.920636661

tag=TAG20160823T115508

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

channel ORA_DISK_1: starting datafile copy

input datafile file number=00006

name=+DATA/ora11g/datafile/rcat_data_01.dbf

output file

name=+DATA_DUP/dupdb/datafile/rcat_data.265.920636697

tag=TAG20160823T115508

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

Finished backup at 23-AUG-16

sql statement: alter system archive log current

contents of Memory Script:

{

backup as copy reuse

archivelog like

"+FRA/ora11g/archivelog/2016_08_23/

thread_1_seq_531.306.920636723" auxiliary format "+FRA_DUP";

catalog clone recovery area;

switch clone datafile all;

}

executing Memory Script

Starting backup at 23-AUG-16

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log copy

input archived log thread=1 sequence=531 RECID=656 STAMP=9636724

output file

name=+FRA_DUP/dupdb/archivelog/2016_08_23/

thread_1_seq_531.260.920636727 RECID=0 STAMP=0

channel ORA_DISK_1:archived log copy complete,

elapsed time:00:00:07

Finished backup at 23-AUG-16

searching for all files in the recovery area

List of Files Unknown to the Database

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

File Name:

+fra_dup/DUPDB/ARCHIVELOG/2016_08_23/thread_1_seq_531.260.92027

File Name: +fra_dup/DUPDB/CONTROLFILE/Current.256.920632491

File Name: +fra_dup/DUPDB/CONTROLFILE/Current.257.920632491

File Name: +fra_dup/DUPDB/CONTROLFILE/Current.258.920634865

cataloging files...

cataloging done

List of Cataloged Files

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

File Name:
+fra_dup/DUPDB/ARCHIVELOG/2016_08_23/thread_1_seq_531.260.92027

List of Files Which Where Not Cataloged

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

File Name: +fra_dup/DUPDB/CONTROLFILE/Current.256.920632491

RMAN-07517: Reason: The file header is corrupted

File Name: +fra_dup/DUPDB/CONTROLFILE/Current.257.920632491

RMAN-07517: Reason: The file header is corrupted

File Name: +fra_dup/DUPDB/CONTROLFILE/Current.258.920634865

RMAN-07517: Reason: The file header is corrupted

datafile 1 switched to datafile copy

input datafile copy RECID=30 STAMP=920636735 file

name=+DATA_DUP/dupdb/datafile/system.261.920636075

datafile 2 switched to datafile copy

input datafile copy RECID=31 STAMP=920636735 file

name=+DATA_DUP/dupdb/datafile/sysaux.262.920636381

datafile 3 switched to datafile copy

input datafile copy RECID=32 STAMP=920636735 file

name=+DATA_DUP/dupdb/datafile/undotbs1.263.920636587

datafile 4 switched to datafile copy

input datafile copy RECID=33 STAMP=920636735 file

name=+DATA_DUP/dupdb/datafile/users.260.920634909

datafile 5 switched to datafile copy

input datafile copy RECID=34 STAMP=920636736 file

name=+DATA_DUP/dupdb/datafile/example.264.920636661

datafile 6 switched to datafile copy

input datafile copy RECID=35 STAMP=920636736 file

name=+DATA_DUP/dupdb/datafile/rcat_data.265.920636697

contents of Memory Script:

{

set until scn 3852444;

recover

clone database

delete archivelog;

}

executing Memory Script

executing command: SET until clause

Starting recover at 23-AUG-16

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=12 device type=DISK

starting media recovery

archived log for thread 1 with sequence 531 is already on disk

as file

+FRA_DUP/dupdb/archivelog/2016_08_23/thread_1_seq_531.260.92027

archived log file name=

+FRA_DUP/dupdb/archivelog/2016_08_23/thread_1_seq_531.260.92027 thread=1 sequence=531

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

Finished recover at 23-AUG-16

contents of Memory Script:

{

shutdown clone immediate;

startup clone nomount;

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

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

Fixed Size 2211928 bytes

Variable Size 159387560 bytes

Database Buffers 50331648 bytes

Redo Buffers 5226496 bytes

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

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

Fixed Size 2211928 bytes

Variable Size 159387560 bytes

Database Buffers 50331648 bytes

Redo Buffers 5226496 bytes

sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DUPDB"

RESETLOGS ARCHIVELOG

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/dupdb/datafile/system.261.920636075'

CHARACTER SET AL32UTF8

contents of Memory Script:

{

set newname for tempfile 1 to "+DATA_DUP";

switch clone tempfile all;

catalog clone datafilecopy

"+DATA_DUP/dupdb/datafile/sysaux.262.920636381",

"+DATA_DUP/dupdb/datafile/undotbs1.263.920636587",

"+DATA_DUP/dupdb/datafile/users.260.920634909",

"+DATA_DUP/dupdb/datafile/example.264.920636661",

"+DATA_DUP/dupdb/datafile/rcat_data.265.920636697";

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/dupdb/datafile/sysaux.262.920636381 RECID=1

STAMP=920636767

cataloged datafile copy

datafile copy file

name=+DATA_DUP/dupdb/datafile/undotbs1.263.920636587 RECID=2

STAMP=920636767

cataloged datafile copy

datafile copy file

name=+DATA_DUP/dupdb/datafile/users.260.920634909 RECID=3

STAMP=920636767

cataloged datafile copy

datafile copy file

name=+DATA_DUP/dupdb/datafile/example.264.920636661 RECID=4

STAMP=920636767

cataloged datafile copy

datafile copy file

name=+DATA_DUP/dupdb/datafile/rcat_data.265.920636697 RECID=5

STAMP=920636767

datafile 2 switched to datafile copy

input datafile copy RECID=1 STAMP=920636767 file

name=+DATA_DUP/dupdb/datafile/sysaux.262.920636381

datafile 3 switched to datafile copy

input datafile copy RECID=2 STAMP=920636767 file

name=+DATA_DUP/dupdb/datafile/undotbs1.263.920636587

datafile 4 switched to datafile copy

input datafile copy RECID=3 STAMP=920636767 file

name=+DATA_DUP/dupdb/datafile/users.260.920634909

datafile 5 switched to datafile copy

input datafile copy RECID=4 STAMP=920636767 file

name=+DATA_DUP/dupdb/datafile/example.264.920636661

datafile 6 switched to datafile copy

input datafile copy RECID=5 STAMP=920636767 file

name=+DATA_DUP/dupdb/datafile/rcat_data.265.920636697

contents of Memory Script:

{

Alter clone database open resetlogs;

}

executing Memory Script

database opened

Finished Duplicate Db at 23-AUG-16

RMAN>

17. Test duplicated database

[oracle11g@orcl11g Desktop]$ . oraenv

ORACLE_SID = [ora11g] ? DUPDB

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

[oracle11g@orcl11g Desktop]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Aug 23 12:58:02 2016

connected to target database: DUPDB (DBID=645850839)

RMAN> report schema;

using target database control file instead of recovery catalog

Report of database schema for database with db_unique_name DUPDB

List of Permanent Datafiles

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

File Size(MB)Tablespace RB segs Datafile Name

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

1 880 SYSTEM ***

+DATA_DUP/dupdb/datafile/system.261.920636075

2 630 SYSAUX ***

+DATA_DUP/dupdb/datafile/sysaux.262.920636381

3 200 UNDOTBS1 ***

+DATA_DUP/dupdb/datafile/undotbs1.263.920636587

4 3833 USERS ***

+DATA_DUP/dupdb/datafile/users.260.920634909

5 100 EXAMPLE ***

+DATA_DUP/dupdb/datafile/example.264.920636661

6 60 RCAT_DATA ***

+DATA_DUP/dupdb/datafile/rcat_data.265.920636697

List of Temporary Files

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

File Size(MB) Tablespace Maxsize(MB) Tempfile Name

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

1 29 TEMP 32767

+DATA_DUP/dupdb/tempfile/temp.269.920636841

18. Now we have to transfer spfileDUPDB.ora to ASM storage

[oracle11g@orcl11g Desktop]$ sqlplus / as sysdba

SQL> create pfile='initDUPDB2.ora' from memory;

File created.

SQL> shutdown immediate;

$ cd $ORACLE_HOME/dbs

edit initDUPDB2.ora

Remove all the deprecated parameters.

(I don't exactly understand which deprecated parameters will be

deleted exactly)

Then delete old initDUPDB.ora file and rename new initDUPDB2.ora

file to initDUPDB.ora

b)just use old initDUPDB.ora file with all parameters defined at

the beginning.

content of initDUPDB.ora file is like that

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

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

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files= ('+DATA_DUP','+FRA_DUP')

*.db_block_size=8192

*.db_create_file_dest='+DATA_DUP'

*.db_name='DUPDB'

*.db_domain=''

*.db_file_name_convert=('+DATA','+DATA_DUP')

*.log_file_name_convert=('+DATA','+DATA_DUP','+FRA','+FRA_DUP')

*.db_recovery_file_dest='+FRA_DUP'

*.db_recovery_file_dest_size=8474836480

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

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

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS

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

19.Start duplicated database in nomount mode.

sqlplus '/as sysdba'

SQL> startup nomount

pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initDUPDB.ora';

ORACLE instance started.

Total System Global Area 271437824 bytes

Fixed Size 2227456 bytes

Variable Size 213910272 bytes

Database Buffers 50331648 bytes

Redo Buffers 4968448 bytes

SQL> create spfile='+DATA_DUP' from

pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initDUPDB.ora';

File created.

SQL> shut abort;

20.Get the LOCATION OF SPFILE from ASM [LOGIN FROM ANOTHER SESSION AS GRID]

ASMCMD> ls

DATA/

DATA_DUP/

FRA/

FRA_DUP/

ASMCMD> ls -lt DATA_DUP/DUPDB

Type Redund Striped Time Sys Name

Y TEMPFILE/

Y PARAMETERFILE/

Y ONLINELOG/

Y DATAFILE/

Y CONTROLFILE/

ASMCMD>cd parameterfile

ASMCMD> ls

spfile.270.920641713

ASMCMD> cd ..

ASMCMD> ls

CONTROLFILE/

DATAFILE/

ONLINELOG/

PARAMETERFILE/

TEMPFILE/

ASMCMD> mkalias data_dup/dupdb/parameterfile/spfile.270.920641713 spfileDUPDB.ora

ASMCMD> ls

CONTROLFILE/

DATAFILE/

ONLINELOG/

PARAMETERFILE/

TEMPFILE/

spfileDUPDB.ora

ASMCMD>

21.Edit the pfile to point to the SPFILE in ASM:

cat > initDUPDB.ora

SPFILE='+DATA_DUP/DUPDB/spfileDUPDB.ora'

PRESS CTRL+D

22.Start database to check if it is started from spfileDUPDB.ora from ASM.

[oracle11g@orcl11g Desktop]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Aug 23 13:52:37 2016

Connected to an idle instance.

SQL> startup

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.

Database opened.

SQL> show parameter spfile

NAME TYPE VALUE

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

spfile String +DATA_DUP/dupdb/spfiledupdb.ora

SQL>

23. End!!!

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

Sunday, November 6, 2016

Active Database Duplication (ASM to ASM on same host)

No comments:

Post a Comment