Tag: backup based

Rman Duplicate from noarchivelog mode target database

Target (source) details:

Database Name	dhstg
Hostname	Nsmdev01
Ip Address	172.16.110.18
OS	Linux
Version	x86_64
Datafile Location	/data1/dhstg
Backup Location	/backup/rman_backup

Tns details:

DHSTG =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = Nsmdev01.corp.netsoftmate.com)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = dhstg.corp.netsoftmate.com)

)

Destination details:

Database Name	dhstg
Hostname	Nsmstg01
Ip Address	172.16.110.16
OS	Linux
Version	x86_64
Datafile Location	/data1/diff/dhstg
Backup Location	/backup/rman_backup

Tns details:

dhstg_dup =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = dhstg_dup)

)

Pre-requisites:

Oracle net configuration:
Both target and destination server should have tns entries.
Static listener registration on auxiliary site.
Service should be register on aux listener.
Password files from target database.
Sqlnet.ora should have correct parameters.
Target database should be running through spfile.
Check connectivity between target and destination server.
Target database instance should be in mount mode.

Steps on target (source) server:

1. Put tns entry of auxiliary database into target $ORACLE_HOME/network/admin directory

[oracle@Nsmdev01 admin]$ cd /data2/app/oracle/product/11.2.0/network/admin

[oracle@Nsmdev01 admin]$ vi tnsnames.ora

dhstg_dup =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = dhstg_dup)

)

[[oracle@Nsmdev01 admin]$ tnsping dhstg_dup

TNS Ping Utility for Linux: Version 11.2.0.1.0 – Production on 06-APR-2015 12:34:39

Used parameter files:

/data2/app/oracle/product/11.2.0/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.110.16)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = dhstg_dup)))

OK (0 msec)

2. Check database whether it is running in archive log mode.

SQL> select name,open_mode,log_mode from v$database;

NAME OPEN_MODE LOG_MODE

——— ——————– ————

DHSTG READ WRITE NOARCHIVELOG

3. Check database is running from spfile. While duplicating database with different directory structure target database must be running with spfile.

SQL> select value from v$parameter where name=’spfile’;

VALUE

——————————————————————————–

/data2/app/oracle/product/11.2.0/dbs/spfiledhstg.ora

SQL> show parameter pfile

NAME TYPE VALUE

———————————— ———– ——————————

spfile string /data2/app/oracle/product/11.2 .0/dbs/spfiledhstg.ora

4. Create pfile from spfile

SQL> create pfile=’/backup/rman_backup/initdhstg.ora’ from spfile;

File created.

5. Create password file.

[oracle@Nsmdev01 dbs]$ orapwd file=orapwdhstg password=oracle

6. Copy the password file and init file to destination $ORACLE_HOME/dbs directory using scp or ftp

[oracle@Nsmdev01 dbs]$ scp orapwdhstg oracle@172.16.110.16:/data2/app/oracle/product/11.2.0/dbs/

oracle@172.16.110.16’s password:

orapwdhstg 100% 1536 1.5KB/s 00:00

[oracle@Nsmdev01 rman_backup]$ pwd

/backup/rman_backup

[oracle@Nsmdev01 rman_backup]$ scp initdhstg.ora oracle@172.16.110.16:/data2/app/oracle/product/11.2.0/dbs/

oracle@172.16.110.16’s password:

initdhstg.ora 100% 861 0.8KB/s 00:00

7. Shutdown and start the instance in mount stage.

SQL> select name,open_mode from v$database;

NAME OPEN_MODE

——— ——————–

DHSTG READ WRITE

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 1068937216 bytes

Fixed Size 2220200 bytes

Variable Size 838864728 bytes

Database Buffers 222298112 bytes

Redo Buffers 5554176 bytes

Database mounted.

SQL> select name,open_mode from v$database;

NAME OPEN_MODE

——— ——————–

DHSTG MOUNTED

Steps on destination server:

8. Put static listener entry into listener.ora file in $ORACLE_HOME/netwrok/admin directory.

oracle@Nsmstg01 admin]$ cd /data2/app/oracle/product/11.2.0/network/admin

[oracle@Nsmstg01 admin]$

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = dhstg_dup)

(ORACLE_HOME = /data2/app/oracle/product/11.2.0)

(SID_NAME = dhstg)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

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

)

(DESCRIPTION =

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

)

Here global_name is nothing but service name and sid_name is instance name

now reload the listener and check the services

[oracle@Nsmstg01 admin]$ lsnrctl

LSNRCTL for Linux: Version 11.2.0.1.0 – Production on 06-APR-2015 12:13:54

Welcome to LSNRCTL, type “help” for information.

[oracle@Nsmstg01 admin]$ lsnrctl

LSNRCTL for Linux: Version 11.2.0.1.0 – Production on 06-APR-2015 12:43:43

Welcome to LSNRCTL, type “help” for information.

LSNRCTL> reload

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.110.16)(PORT=1521)))

The command completed successfully

LSNRCTL> services

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.110.16)(PORT=1521)))

Services Summary…

Service “aux.corp.netsoftmate.com” has 1 instance(s).

Instance “aux”, status READY, has 1 handler(s) for this service…

Handler(s):

“DEDICATED” established:0 refused:0 state:ready

LOCAL SERVER

Service “auxXDB.corp.netsoftmate.com” has 1 instance(s).

Instance “aux”, status READY, has 1 handler(s) for this service…

Handler(s):

“D000” established:0 refused:0 current:0 max:1022 state:ready

DISPATCHER <machine: Nsmstg01, pid: 4392>

(ADDRESS=(PROTOCOL=tcp)(HOST=Nsmstg01)(PORT=38403))

Service “dhstg.corp.netsoftmate.com” has 1 instance(s).

Instance “dhstg”, status BLOCKED, has 1 handler(s) for this service…

Handler(s):

“DEDICATED” established:0 refused:0 state:ready

LOCAL SERVER

Service “dhstg_dup” has 1 instance(s).

Instance “dhstg”, status UNKNOWN, has 1 handler(s) for this service…

Handler(s):

“DEDICATED” established:0 refused:0

LOCAL SERVER

Service “dhstg_dup.corp.netsoftmate.com” has 1 instance(s).

Instance “dhstg”, status BLOCKED, has 1 handler(s) for this service…

Handler(s):

“DEDICATED” established:0 refused:0 state:ready

LOCAL SERVER

The command completed successfully

9. put Tns entry for target(source) database in $ORACLE_HOME/network/admin directory

[oracle@Nsmstg01 admin]$ vi tnsnames.ora

DHSTG =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = dhstg.corp.netsoftmate.com)

)

[oracle@Nsmstg01 admin]$ tnsping DHSTG

TNS Ping Utility for Linux: Version 11.2.0.1.0 – Production on 06-APR-2015 12:15:52

Used parameter files:

/data2/app/oracle/product/11.2.0/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.110.18)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = dhstg.corp.netsoftmate.com)))

OK (0 msec)

10. Create the directory structure different from target (source) server.

[oracle@Nsmstg01 dhstg]$ mkdir -p /data1/diff/dhstg

11. Set the oracle sid and start the instance in nomount stage with pfile, here start the instance with pfile is must or else you will get error.

RMAN-05537: DUPLICATE without TARGET connection when auxiliary instance

is started with spfile cannot use SPFILE clause

[oracle@Nsmstg01 dbs]$ export ORACLE_SID=DHSTG

[oracle@Nsmstg01 dbs]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Mon Apr 6 12:22:14 2015

Enter user-name: sys as sysdba

Enter password:

Connected to an idle instance.

SQL> startup nomount pfile=’/data2/app/oracle/product/11.2.0/dbs/initdhstg.ora’;

ORACLE instance started.

Total System Global Area 1068937216 bytes

Fixed Size 2220200 bytes

Variable Size 838864728 bytes

Database Buffers 222298112 bytes

Redo Buffers 5554176 bytes

SQL>

12. Now connect to Rman by issuing following command

[oracle@Nsmstg01 admin]$ rman target sys/oracle@dhstg auxiliary sys/oracle@dhstg_dup

Recovery Manager: Release 11.2.0.1.0 – Production on Mon Apr 6 12:58:08 2015

connected to target database: DHSTG (DBID=112149522, not open)

connected to auxiliary database: DHSTG (not mounted)

RMAN>

13. Issue the command to duplicate the database with same name but different directory structure from noarchivelog mode target DB.

RMAN> DUPLICATE TARGET DATABASE TO dhstg FROM ACTIVE DATABASE

2> spfile

3> parameter_value_convert

4> ‘/data1/dhstg’, ‘/data1/diff/dhstg’

5> set db_file_name_convert

6> ‘/data1/dhstg’, ‘/data1/diff/dhstg’

7> set log_file_name_convert

8> ‘/data1/dhstg’, ‘/data1/diff/dhstg’

9> nofilenamecheck;

Starting Duplicate Db at 06-APR-15

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=63 device type=DISK

contents of Memory Script:

{

backup as copy reuse

targetfile ‘/data2/app/oracle/product/11.2.0/dbs/spfiledhstg.ora’ auxiliary format

‘/data2/app/oracle/product/11.2.0/dbs/spfiledhstg.ora’ ;

sql clone “alter system set spfile= ”/data2/app/oracle/product/11.2.0/dbs/spfiledhstg.ora””;

}

executing Memory Script

Starting backup at 06-APR-15

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=129 device type=DISK

Finished backup at 06-APR-15

sql statement: alter system set spfile= ”/data2/app/oracle/product/11.2.0/dbs/spfiledhstg.ora”

contents of Memory Script:

{

sql clone “alter system set db_name =

”DHSTG” comment=

”duplicate” scope=spfile”;

sql clone “alter system set control_files =

”/data1/diff/dhstg/control01.ctl”, ”/data1/diff/dhstg/control02.ctl” comment=

”” scope=spfile”;

sql clone “alter system set db_file_name_convert =

”/data1/dhstg”, ”/data1/diff/dhstg” comment=

”” scope=spfile”;

sql clone “alter system set log_file_name_convert =

”/data1/dhstg”, ”/data1/diff/dhstg” comment=

”” scope=spfile”;

shutdown clone immediate;

startup clone nomount;

}

executing Memory Script

sql statement: alter system set db_name = ”DHSTG” comment= ”duplicate” scope=spfile

sql statement: alter system set control_files = ”/data1/diff/dhstg/control01.ctl”, ”/data1/diff/dhstg/control02.ctl” comment= ”” scope=spfile

sql statement: alter system set db_file_name_convert = ”/data1/dhstg”, ”/data1/diff/dhstg” comment= ”” scope=spfile

sql statement: alter system set log_file_name_convert = ”/data1/dhstg”, ”/data1/diff/dhstg” comment= ”” scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area 1068937216 bytes

Fixed Size 2220200 bytes

Variable Size 838864728 bytes

Database Buffers 222298112 bytes

Redo Buffers 5554176 bytes

contents of Memory Script:

{

sql clone “alter system set db_name =

”DHSTG” comment=

”Modified by RMAN duplicate” scope=spfile”;

sql clone “alter system set db_unique_name =

”DHSTG” comment=

”Modified by RMAN duplicate” scope=spfile”;

shutdown clone immediate;

startup clone force nomount

backup as copy current controlfile auxiliary format ‘/data1/diff/dhstg/control01.ctl’;

restore clone controlfile to ‘/data1/diff/dhstg/control02.ctl’ from

‘/data1/diff/dhstg/control01.ctl’;

alter clone database mount;

}

executing Memory Script

sql statement: alter system set db_name = ”DHSTG” comment= ”Modified by RMAN duplicate” scope=spfile

sql statement: alter system set db_unique_name = ”DHSTG” comment= ”Modified by RMAN duplicate” scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area 1068937216 bytes

Fixed Size 2220200 bytes

Variable Size 838864728 bytes

Database Buffers 222298112 bytes

Redo Buffers 5554176 bytes

Starting backup at 06-APR-15

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

copying current control file

output file name=/data2/app/oracle/product/11.2.0/dbs/snapcf_dhstg.f tag=TAG20150406T125217 RECID=2 STAMP=876315139

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

Finished backup at 06-APR-15

Starting restore at 06-APR-15

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=63 device type=DISK

channel ORA_AUX_DISK_1: copied control file copy

Finished restore at 06-APR-15

database mounted

contents of Memory Script:

{

set newname for datafile 1 to

“/data1/diff/dhstg/system01.dbf”;

set newname for datafile 2 to

“/data1/diff/dhstg/sysaux01.dbf”;

set newname for datafile 3 to

“/data1/diff/dhstg/undotbs01.dbf”;

set newname for datafile 4 to

“/data1/diff/dhstg/users01.dbf”;

set newname for datafile 5 to

“/data1/diff/dhstg/example01.dbf”;

set newname for datafile 6 to

“/data1/diff/dhstg/data01.dbf”;

backup as copy reuse

datafile 1 auxiliary format

“/data1/diff/dhstg/system01.dbf” datafile

2 auxiliary format

“/data1/diff/dhstg/sysaux01.dbf” datafile

3 auxiliary format

“/data1/diff/dhstg/undotbs01.dbf” datafile

4 auxiliary format

“/data1/diff/dhstg/users01.dbf” datafile

5 auxiliary format

“/data1/diff/dhstg/example01.dbf” datafile

6 auxiliary format

“/data1/diff/dhstg/data01.dbf” ;

}

executing Memory Script

executing command: SET NEWNAME

Starting backup at 06-APR-15

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

input datafile file number=00001 name=/data1/dhstg/system01.dbf

output file name=/data1/diff/dhstg/system01.dbf tag=TAG20150406T125229

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

channel ORA_DISK_1: starting datafile copy

input datafile file number=00002 name=/data1/dhstg/sysaux01.dbf

output file name=/data1/diff/dhstg/sysaux01.dbf tag=TAG20150406T125229

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

channel ORA_DISK_1: starting datafile copy

input datafile file number=00003 name=/data1/dhstg/undotbs01.dbf

output file name=/data1/diff/dhstg/undotbs01.dbf tag=TAG20150406T125229

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

channel ORA_DISK_1: starting datafile copy

input datafile file number=00006 name=/data1/dhstg/data01.dbf

output file name=/data1/diff/dhstg/data01.dbf tag=TAG20150406T125229

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

channel ORA_DISK_1: starting datafile copy

input datafile file number=00005 name=/data1/dhstg/example01.dbf

output file name=/data1/diff/dhstg/example01.dbf tag=TAG20150406T125229

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/dhstg/users01.dbf

output file name=/data1/diff/dhstg/users01.dbf tag=TAG20150406T125229

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

Finished backup at 06-APR-15

contents of Memory Script:

{

switch clone datafile all;

}

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy RECID=2 STAMP=876315893 file name=/data1/diff/dhstg/system01.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=3 STAMP=876315894 file name=/data1/diff/dhstg/sysaux01.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=4 STAMP=876315894 file name=/data1/diff/dhstg/undotbs01.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=5 STAMP=876315894 file name=/data1/diff/dhstg/users01.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=6 STAMP=876315895 file name=/data1/diff/dhstg/example01.dbf

datafile 6 switched to datafile copy

input datafile copy RECID=7 STAMP=876315895 file name=/data1/diff/dhstg/data01.dbf

contents of Memory Script:

{

recover

clone database

noredo

delete archivelog

;

}

executing Memory Script

Starting recover at 06-APR-15

using channel ORA_AUX_DISK_1

Finished recover at 06-APR-15

contents of Memory Script:

{

shutdown clone immediate;

startup clone nomount;

sql clone “alter system set db_name =

”DHSTG” 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 1068937216 bytes

Fixed Size 2220200 bytes

Variable Size 838864728 bytes

Database Buffers 222298112 bytes

Redo Buffers 5554176 bytes

sql statement: alter system set db_name = ”DHSTG” 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 1068937216 bytes

Fixed Size 2220200 bytes

Variable Size 838864728 bytes

Database Buffers 222298112 bytes

Redo Buffers 5554176 bytes

sql statement: CREATE CONTROLFILE REUSE SET DATABASE “DHSTG” RESETLOGS NOARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 ( ‘/data1/diff/dhstg/redo01.log’ ) SIZE 50 M REUSE,

GROUP 2 ( ‘/data1/diff/dhstg/redo02.log’ ) SIZE 50 M REUSE,

GROUP 3 ( ‘/data1/diff/dhstg/redo03.log’ ) SIZE 50 M REUSE

DATAFILE

‘/data1/diff/dhstg/system01.dbf’

CHARACTER SET AL32UTF8

contents of Memory Script:

{

set newname for tempfile 1 to

“/data1/diff/dhstg/temp01.dbf”;

switch clone tempfile all;

catalog clone datafilecopy “/data1/diff/dhstg/sysaux01.dbf”,

“/data1/diff/dhstg/undotbs01.dbf”,

“/data1/diff/dhstg/users01.dbf”,

“/data1/diff/dhstg/example01.dbf”,

“/data1/diff/dhstg/data01.dbf”;

switch clone datafile all;

}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /data1/diff/dhstg/temp01.dbf in control file

cataloged datafile copy

datafile copy file name=/data1/diff/dhstg/sysaux01.dbf RECID=1 STAMP=876315921

cataloged datafile copy

datafile copy file name=/data1/diff/dhstg/undotbs01.dbf RECID=2 STAMP=876315921

cataloged datafile copy

datafile copy file name=/data1/diff/dhstg/users01.dbf RECID=3 STAMP=876315922

cataloged datafile copy

datafile copy file name=/data1/diff/dhstg/example01.dbf RECID=4 STAMP=876315922

cataloged datafile copy

datafile copy file name=/data1/diff/dhstg/data01.dbf RECID=5 STAMP=876315922

datafile 2 switched to datafile copy

input datafile copy RECID=1 STAMP=876315921 file name=/data1/diff/dhstg/sysaux01.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=2 STAMP=876315921 file name=/data1/diff/dhstg/undotbs01.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=3 STAMP=876315922 file name=/data1/diff/dhstg/users01.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=4 STAMP=876315922 file name=/data1/diff/dhstg/example01.dbf

datafile 6 switched to datafile copy

input datafile copy RECID=5 STAMP=876315922 file name=/data1/diff/dhstg/data01.dbf

contents of Memory Script:

{

Alter clone database open resetlogs;

}

executing Memory Script

database opened

Finished Duplicate Db at 06-APR-15

14. Once database replicated open the target database.

SQL> alter database open;

Database altered.

SQL> select name,open_mode from v$database;

NAME OPEN_MODE

——— ——————–

DHSTG READ WRITE

Issues:

When you replicate database where target database is in noarchivelog mode. You will receive following error.

RMAN> DUPLICATE TARGET DATABASE TO dhstg FROM ACTIVE DATABASE

2> SPFILE

3> PARAMETER_VALUE_CONVERT

4> ‘/data1/dhstg’, ‘/data1/diff/dhstg’

5> SET DB_FILE_NAME_CONVERT

6> ‘/data1/dhstg’, ‘/data1/diff/dhstg’

7> SET LOG_FILE_NAME_CONVERT

8> ‘/data1/dhstg’, ‘/data1/diff/dhstg’

9> NOFILENAMECHECK;

Starting Duplicate Db at 06-APR-15

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=63 device type=DISK

contents of Memory Script:

{

backup as copy reuse

targetfile ‘/data2/app/oracle/product/11.2.0/dbs/spfiledhstg.ora’ auxiliary format

‘/data2/app/oracle/product/11.2.0/dbs/spfiledhstg.ora’ ;

sql clone “alter system set spfile= ”/data2/app/oracle/product/11.2.0/dbs/spfiledhstg.ora””;

}

executing Memory Script

Starting backup at 06-APR-15

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=129 device type=DISK

Finished backup at 06-APR-15

sql statement: alter system set spfile= ”/data2/app/oracle/product/11.2.0/dbs/spfiledhstg.ora”

contents of Memory Script:

{

sql clone “alter system set db_name =

”DHSTG” comment=

”duplicate” scope=spfile”;

sql clone “alter system set control_files =

”/data1/diff/dhstg/control01.ctl”, ”/data1/diff/dhstg/control02.ctl” comment=

”” scope=spfile”;

sql clone “alter system set db_file_name_convert =

”/data1/dhstg”, ”/data1/diff/dhstg” comment=

”” scope=spfile”;

sql clone “alter system set LOG_FILE_NAME_CONVERT =

”/data1/dhstg”, ”/data1/diff/dhstg” comment=

”” scope=spfile”;

shutdown clone immediate;

startup clone nomount;

}

executing Memory Script

sql statement: alter system set db_name = ”DHSTG” comment= ”duplicate” scope=spfile

sql statement: alter system set control_files = ”/data1/diff/dhstg/control01.ctl”, ”/data1/diff/dhstg/control02.ctl” comment= ”” scope=spfile

sql statement: alter system set db_file_name_convert = ”/data1/dhstg”, ”/data1/diff/dhstg” comment= ”” scope=spfile

sql statement: alter system set LOG_FILE_NAME_CONVERT = ”/data1/dhstg”, ”/data1/diff/dhstg” comment= ”” scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area 1068937216 bytes

Fixed Size 2220200 bytes

Variable Size 838864728 bytes

Database Buffers 222298112 bytes

Redo Buffers 5554176 bytes

contents of Memory Script:

{

sql clone “alter system set db_name =

”DHSTG” comment=

”Modified by RMAN duplicate” scope=spfile”;

sql clone “alter system set db_unique_name =

”DHSTG” comment=

”Modified by RMAN duplicate” scope=spfile”;

shutdown clone immediate;

startup clone force nomount

backup as copy current controlfile auxiliary format ‘/data1/diff/dhstg/control01.ctl’;

restore clone controlfile to ‘/data1/diff/dhstg/control02.ctl’ from

‘/data1/diff/dhstg/control01.ctl’;

alter clone database mount;

}

executing Memory Script

sql statement: alter system set db_name = ”DHSTG” comment= ”Modified by RMAN duplicate” scope=spfile

sql statement: alter system set db_unique_name = ”DHSTG” comment= ”Modified by RMAN duplicate” scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area 1068937216 bytes

Fixed Size 2220200 bytes

Variable Size 838864728 bytes

Database Buffers 222298112 bytes

Redo Buffers 5554176 bytes

Starting backup at 06-APR-15

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

copying current control file

output file name=/data2/app/oracle/product/11.2.0/dbs/snapcf_dhstg.f tag=TAG20150406T141312 RECID=3 STAMP=876319993

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

Finished backup at 06-APR-15

Starting restore at 06-APR-15

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=63 device type=DISK

channel ORA_AUX_DISK_1: copied control file copy

Finished restore at 06-APR-15

database mounted

contents of Memory Script:

{

set newname for datafile 1 to

“/data1/diff/dhstg/system01.dbf”;

set newname for datafile 2 to

“/data1/diff/dhstg/sysaux01.dbf”;

set newname for datafile 3 to

“/data1/diff/dhstg/undotbs01.dbf”;

set newname for datafile 4 to

“/data1/diff/dhstg/users01.dbf”;

set newname for datafile 5 to

“/data1/diff/dhstg/example01.dbf”;

set newname for datafile 6 to

“/data1/diff/dhstg/data01.dbf”;

backup as copy reuse

datafile 1 auxiliary format

“/data1/diff/dhstg/system01.dbf” datafile

2 auxiliary format

“/data1/diff/dhstg/sysaux01.dbf” datafile

3 auxiliary format

“/data1/diff/dhstg/undotbs01.dbf” datafile

4 auxiliary format

“/data1/diff/dhstg/users01.dbf” datafile

5 auxiliary format

“/data1/diff/dhstg/example01.dbf” datafile

6 auxiliary format

“/data1/diff/dhstg/data01.dbf” ;

sql ‘alter system archive log current’;

}

executing Memory Script

executing command: SET NEWNAME

Starting backup at 06-APR-15

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 04/06/2015 14:21:33

ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode

continuing other job steps, job failed will not be re-run

channel ORA_DISK_1: starting datafile copy

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 04/06/2015 14:21:33

ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode

continuing other job steps, job failed will not be re-run

channel ORA_DISK_1: starting datafile copy

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 04/06/2015 14:21:33

ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode

continuing other job steps, job failed will not be re-run

channel ORA_DISK_1: starting datafile copy

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 04/06/2015 14:21:33

ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode

continuing other job steps, job failed will not be re-run

channel ORA_DISK_1: starting datafile copy

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 04/06/2015 14:21:33

ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode

continuing other job steps, job failed will not be re-run

channel ORA_DISK_1: starting datafile copy

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

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

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

RMAN-03002: failure of Duplicate Db command at 04/06/2015 14:21:34

RMAN-03015: error occurred in stored script Memory Script

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 04/06/2015 14:21:34

ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode

Solution: Work around for this is shutdown the target database and start in mount stage.

When you start the auxiliary instance with spfile.

[oracle@Nsmstg01 dbs]$ export ORACLE_SID=dhstg

[oracle@Nsmstg01 dbs]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Mon Apr 6 15:15:22 2015

Enter user-name: sys as sysdba

Enter password:

Connected to an idle instance.

SQL> startup nomount

ORACLE instance started.

Total System Global Area 1068937216 bytes

Fixed Size 2220200 bytes

Variable Size 838864728 bytes

Database Buffers 222298112 bytes

Redo Buffers 5554176 bytes

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@Nsmstg01 dbs]$ rman target sys/oracle@dhstg auxiliary sys/oracle@dhstg_dup

Recovery Manager: Release 11.2.0.1.0 – Production on Mon Apr 6 15:16:07 2015

connected to target database: DHSTG (DBID=112149522, not open)

connected to auxiliary database: DHSTG (not mounted)

RMAN>

RMAN> DUPLICATE TARGET DATABASE TO dhstg FROM ACTIVE DATABASE

2> SPFILE

3> PARAMETER_VALUE_CONVERT

4> ‘/data1/dhstg’, ‘/data1/diff/dhstg’

5> SET DB_FILE_NAME_CONVERT

6> ‘/data1/dhstg’, ‘/data1/diff/dhstg’

7> SET LOG_FILE_NAME_CONVERT

8> ‘/data1/dhstg’, ‘/data1/diff/dhstg’

9> NOFILENAMECHECK;

Starting Duplicate Db at 06-APR-15

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

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

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

RMAN-03002: failure of Duplicate Db command at 04/06/2015 15:47:03

RMAN-05537: DUPLICATE without TARGET connection when auxiliary instance is started with spfile cannot use SPFILE clause

solution: Start the auxiliary instance with pfile

Conclusion
In the above article, we have learned that Active Duplication using Rman utility with same database name and different directory structure, and our source Database is in noarchive log mode, and there are some vital things we need remember while performing active duplication of database running in no archive log mode, we have came a cross two errors and their work arounds.

BY
Name: Mirza Sarmadullah Baig
Designation: Senior Database Engineer
Organization: Netsoftmate IT Solutions.
Email: info@netsoftmate.com

February 12, 2017

Rman backup based duplication with different DB name and directory structure without using SPFILE

Overview:

Oracle Recovery Manager (RMAN) provides a comprehensive foundation for efficiently backing up and recovering the Oracle databases, it provides a common interface, via command line and Enterprise Manager, for backup tasks across different host operating systems, automates administration of your backup strategies.
In this scenario we have different database name and different directory structure. And we are not using SPFILE.

Target
(source) details:

Database Name	prod
Hostname	NSM-SRC
Ip Address	172.16.110.18
OS	Linux
Version	x86_64
Datafile Location	/data2/prod
Backup Location	/backup/rman_backup

Destination details

Database Name	nospfile
Hostname	NSM-TRG
Ip Address	172.16.110.16
OS	Linux
Version	x86_64
Datafile Location	/data2/nospfile/
Backup Location	/backup/rman_backup

Pre-requisites:

Password file from target database.
Sqlnet.ora should have correct parameters.
Target database should be running through pfile.
Auxiliary instance should be start with pfile in nomount stage.

Steps on source server

Set Oracle sid on which backup will perform.

[oracle@NSM-SRC
dbs]$ hostname

NSM-SRC

[oracle@NSM-SRC
dbs]$ export ORACLE_SID=prod

Check database is running from pfile or spfile.

NAME OPEN_MODE

———-
————–

PROD READ WRITE

SQL>
select value from v$parameter where name=’spfile’;

VALUE

——————–

SQL>
show parameter pfile

NAME TYPE VALUE

————————————
——————————— ——————————

spfile string

Connect to target instance via RMAN.

[oracle@NSM-SRC
dbs]$ rman target sys/oracle

Recovery
Manager: Release 11.2.0.1.0 – Production on Mon Apr 13 10:13:01 2015

connected
to target database: PROD (DBID=284539893)

RMAN>

Issue the following command to initiate backup. Before issue the backup command check the backup location is sat correctly.

RMAN>
show all;

using
target database control file instead of recovery catalog

RMAN
configuration parameters for database with db_unique_name PROD are:

CONFIGURE
RETENTION POLICY TO REDUNDANCY 1; # default

CONFIGURE
BACKUP OPTIMIZATION OFF; # default

CONFIGURE
DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE
CONTROLFILE AUTOBACKUP ON;

CONFIGURE
CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘/backup/rman_backup/controlfile_%F’;

CONFIGURE
DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default

CONFIGURE
DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE
ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE
CHANNEL DEVICE TYPE DISK FORMAT
‘/backup/rman_backup/db_arch_%U.bkp’;

CONFIGURE
MAXSETSIZE TO UNLIMITED; # default

CONFIGURE
ENCRYPTION FOR DATABASE OFF; # default

CONFIGURE
ENCRYPTION ALGORITHM ‘AES128’; # default

CONFIGURE
COMPRESSION ALGORITHM ‘BASIC’ AS OF RELEASE ‘DEFAULT’ OPTIMIZE FOR LOAD TRUE ;
# default

CONFIGURE
ARCHIVELOG DELETION POLICY TO NONE; # default

CONFIGURE
SNAPSHOT CONTROLFILE NAME TO
‘/data2/app/oracle/product/11.2.0/dbs/snapcf_prod.f’; # default

RMAN>
list backup;

specification
does not match any backup in the repository

RMAN>
backup database plus archivelog;

Starting
backup at 13-APR-15

current
log archived

allocated
channel: ORA_DISK_1

channel
ORA_DISK_1: SID=15 device type=DISK

channel
ORA_DISK_1: starting archived log backup set

channel
ORA_DISK_1: specifying archived log(s) in backup set

input
archived log thread=1 sequence=153 RECID=149 STAMP=876910536

channel
ORA_DISK_1: starting piece 1 at 13-APR-15

channel
ORA_DISK_1: finished piece 1 at 13-APR-15

piece
handle=/backup/rman_backup/db_arch_2uq495u9_1_1.bkp tag=TAG20150413T101536
comment=NONE

channel
ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished
backup at 13-APR-15

Starting
backup at 13-APR-15

using
channel ORA_DISK_1

channel
ORA_DISK_1: starting full datafile backup set

channel
ORA_DISK_1: specifying datafile(s) in backup set

input
datafile file number=00001 name=/data2/prod/system01.dbf

input
datafile file number=00002 name=/data2/prod/sysaux01.dbf

input
datafile file number=00005 name=/data2/prod/example01.dbf

input
datafile file number=00003 name=/data2/prod/undotbs01.dbf

input
datafile file number=00006 name=/data2/prod/data01.dbf

input
datafile file number=00004 name=/data2/prod/users01.dbf

input
datafile file number=00007 name=/data2/prod/test01.dbf

input
datafile file number=00008 name=/data2/prod/tools01.dbf

channel
ORA_DISK_1: starting piece 1 at 13-APR-15

channel
ORA_DISK_1: finished piece 1 at 13-APR-15

piece
handle=/backup/rman_backup/db_arch_2vq495ua_1_1.bkp tag=TAG20150413T101538 comment=NONE

channel
ORA_DISK_1: backup set complete, elapsed time: 00:00:55

Finished
backup at 13-APR-15

Starting
backup at 13-APR-15

current
log archived

using
channel ORA_DISK_1

channel
ORA_DISK_1: starting archived log backup set

channel
ORA_DISK_1: specifying archived log(s) in backup set

input
archived log thread=1 sequence=154 RECID=150 STAMP=876910594

channel
ORA_DISK_1: starting piece 1 at 13-APR-15

channel
ORA_DISK_1: finished piece 1 at 13-APR-15

piece
handle=/backup/rman_backup/db_arch_30q49603_1_1.bkp tag=TAG20150413T101635
comment=NONE

channel
ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished
backup at 13-APR-15

Starting
Control File Autobackup at 13-APR-15

piece
handle=/backup/rman_backup/controlfile_c-284539893-20150413-00 comment=NONE

Finished
Control File Autobackup at 13-APR-15

RMAN>
list backup;

List of
Backup Sets

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

BS
Key Size Device Type Elapsed Time Completion Time

——-
———- ———– ———— —————

32 3.36M
DISK 00:00:00 13-APR-15

BP Key: 32 Status: AVAILABLE Compressed: NO Tag: TAG20150413T101536

Piece Name:
/backup/rman_backup/db_arch_2uq495u9_1_1.bkp

List of Archived Logs in backup set 32

Thrd Seq
Low SCN Low Time Next SCN
Next Time

—- ——- ———- ——— ———-
———

1
153 5172938 13-APR-15 5176490 13-APR-15

BS
Key Type LV Size Device Type Elapsed Time Completion Time

——-
—- — ———- ———– ———— —————

33 Full
1.11G DISK 00:00:54 13-APR-15

BP Key: 33 Status: AVAILABLE Compressed: NO Tag: TAG20150413T101538

Piece Name:
/backup/rman_backup/db_arch_2vq495ua_1_1.bkp

List of Datafiles in backup set 33

File LV Type Ckp SCN Ckp Time
Name

—- — —- ———- ——— —-

1
Full 5176498 13-APR-15
/data2/prod/system01.dbf

2
Full 5176498 13-APR-15
/data2/prod/sysaux01.dbf

3
Full 5176498 13-APR-15 /data2/prod/undotbs01.dbf

4
Full 5176498 13-APR-15
/data2/prod/users01.dbf

5
Full 5176498 13-APR-15
/data2/prod/example01.dbf

6
Full 5176498 13-APR-15
/data2/prod/data01.dbf

7
Full 5176498 13-APR-15
/data2/prod/test01.dbf

8
Full 5176498 13-APR-15
/data2/prod/tools01.dbf

BS
Key Size Device Type Elapsed Time Completion Time

——-
———- ———– ———— —————

34 9.00K
DISK 00:00:00 13-APR-15

BP Key: 34 Status: AVAILABLE Compressed: NO Tag: TAG20150413T101635

Piece Name:
/backup/rman_backup/db_arch_30q49603_1_1.bkp

List of Archived Logs in backup set 34

Thrd Seq
Low SCN Low Time Next SCN
Next Time

—- ——- ———- ——— ———-
———

1
154 5176490 13-APR-15 5176531 13-APR-15

BS
Key Type LV Size Device Type Elapsed Time Completion Time

——-
—- — ———- ———– ———— —————

35 Full
9.52M DISK 00:00:02
13-APR-15

BP Key: 35 Status: AVAILABLE Compressed: NO Tag: TAG20150413T101636

Piece Name:
/backup/rman_backup/controlfile_c-284539893-20150413-00

Control File Included: Ckp SCN: 5176543 Ckp time: 13-APR-15

RMAN>

Create pfile and copy to destination server

SQL> create
pfile=’/backup/rman_backup/initprod.ora’ from spfile;

File created.

[oracle@NSM-SRC
dbs]$ scp initprod.ora
oracle@172.16.110.16:/data1/oracle11g/product/dbs/initnospfile.ora

oracle@172.16.110.16’s
password:

initprod.ora

100% 1169 1.1KB/s
00:00

Before start scp/ftp to destination server create directory structure to accommodate rman backup on destination server.

[oracle@NSM-TRG
prod]$ mkdir -p /backup/rman_backup/

Copy rman backup from target to destination server.

RMAN>
exit

Recovery
Manager complete.

[oracle@NSM-SRC
dbs]$ cd /backup/rman_backup/

[oracle@NSM-SRC
rman_backup]$ ls -lrt

total
1176020

-rw-r—–.
1 oracle dba 3526656 Apr 13 10:15
db_arch_2uq495u9_1_1.bkp

-rw-r—–.
1 oracle dba 1190690816 Apr 13 10:16 db_arch_2vq495ua_1_1.bkp

-rw-r—–.
1 oracle dba 9728 Apr 13 10:16
db_arch_30q49603_1_1.bkp

-rw-r—–.
1 oracle dba 9994240 Apr 13 10:16
controlfile_c-284539893-20150413-00

[oracle@NSM-SRC
rman_backup]$ scp * oracle@172.16.110.16:/backup/rman_backup/

oracle@172.16.110.16’s
password:

controlfile_c-284539893-20150413-00

100% 9760KB 9.5MB/s
00:01

db_arch_2uq495u9_1_1.bkp

100% 3444KB 3.4MB/s
00:00

db_arch_2vq495ua_1_1.bkp

100% 1136MB 11.1MB/s
01:42

db_arch_30q49603_1_1.bkp

100% 9728 9.5KB/s
00:00

Steps on destination server:

Create new directory structure.

[oracle@NSM-TRG
dbs]$ mkdir -p /data2/nospfile/

Edit the pfile which we have copied from source server.

/data1/oracle11g/product/dbs

[oracle@NSM-TRG
dbs]$ vi initnospfile.ora

Here we have modified new controlfile location and db_name parameters

*.control_files=’/data2/nospfile/control01.ctl’,’/data2/nospfile/control02.ctl’

*.db_name=’nospfile’

Create password file

[oracle@NSM-TRG
dbs]$ orapwd file=orapwnospfile password=oracle

Start the instance in no mount stage.

[oracle@NSM-TRG
dbs]$ export ORACLE_SID=nospfile

[oracle@NSM-TRG
dbs]$ sqlplus

SQL*Plus:
Release 11.2.0.1.0 Production on Mon Apr 13 10:42:46 2015

Enter
user-name: sys as sysdba

Enter password:

Connected
to an idle instance.

SQL>
startup nomount

ORACLE
instance started.

Total
System Global Area 1152450560 bytes

Fixed
Size 2212696 bytes

Variable
Size 352324776 bytes

Database
Buffers 788529152 bytes

Redo
Buffers 9383936 bytes

SQL>

SQL>
exit

Disconnected
from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit
Production

With the
Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@NSM-TRG
dbs]$

Connect to auxiliary instance via RMAN and issue the duplicate command.

[oracle@NSM-TRG
dbs]$ rman auxiliary sys/oracle

Recovery
Manager: Release 11.2.0.1.0 – Production on Mon Apr 13 10:47:05 2015

connected to auxiliary database:
NOSPFILE (not mounted)

RMAN>

RMAN> DUPLICATE TARGET DATABASE TO
nospfile BACKUP LOCATION ‘/backup/rman_backup’

DB_FILE_NAME_CONVERT
‘/data2/prod’,’/data2/nospfile’

LOGFILE GROUP 1(‘/data2/nospfile/redo01.log’)
SIZE 50m,

GROUP 2(‘/data2/nospfile/redo02.log’)
SIZE 50m;

Starting
Duplicate Db at 13-APR-15

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

Fixed
Size 2212696 bytes

Variable
Size 335547560 bytes

Database
Buffers 805306368 bytes

Redo
Buffers 9383936 bytes

contents
of Memory Script:

{

sql clone “alter system set db_name =

”PROD” comment=

”Modified by RMAN duplicate”
scope=spfile”;

sql clone “alter system set db_unique_name =

”NOSPFILE” comment=

”Modified by RMAN duplicate”
scope=spfile”;

shutdown clone immediate;

startup clone force nomount

restore clone primary controlfile from
‘/backup/rman_backup/controlfile_c-284539893-20150413-00’;

alter clone database mount;

}

executing
Memory Script

sql
statement: alter system set db_name
= ”PROD” comment= ”Modified by RMAN
duplicate” scope=spfile

sql
statement: alter system set
db_unique_name = ”NOSPFILE”
comment= ”Modified by RMAN duplicate” scope=spfile

Oracle
instance shut down

Oracle
instance started

Total
System Global Area 1152450560 bytes

Fixed
Size 2212696 bytes

Variable
Size 335547560 bytes

Database
Buffers 805306368 bytes

Redo
Buffers 9383936 bytes

Starting
restore at 13-APR-15

allocated
channel: ORA_AUX_DISK_1

channel
ORA_AUX_DISK_1: SID=63 device type=DISK

channel
ORA_AUX_DISK_1: restoring control file

channel
ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03

output
file name=/data2/nospfile/control01.ctl

output
file name=/data2/nospfile/control02.ctl

Finished
restore at 13-APR-15

database
mounted

released
channel: ORA_AUX_DISK_1

allocated
channel: ORA_AUX_DISK_1

channel
ORA_AUX_DISK_1: SID=63 device type=DISK

contents
of Memory Script:

{

set until scn 5176531;

set newname for datafile 1 to

“/data2/nospfile/system01.dbf”;

set newname for datafile 2 to

“/data2/nospfile/sysaux01.dbf”;

set newname for datafile 3 to

“/data2/nospfile/undotbs01.dbf”;

set newname for datafile 4 to

“/data2/nospfile/users01.dbf”;

set newname for datafile 5 to

“/data2/nospfile/example01.dbf”;

set newname for datafile 6 to

“/data2/nospfile/data01.dbf”;

set newname for datafile 7 to

“/data2/nospfile/test01.dbf”;

set newname for datafile 8 to

“/data2/nospfile/tools01.dbf”;

restore

clone database

;

}

executing
Memory Script

executing
command: SET until clause

executing
command: SET NEWNAME

Starting
restore at 13-APR-15

using
channel ORA_AUX_DISK_1

channel
ORA_AUX_DISK_1: starting datafile backup set restore

channel
ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel
ORA_AUX_DISK_1: restoring datafile 00001 to /data2/nospfile/system01.dbf

channel
ORA_AUX_DISK_1: restoring datafile 00002 to /data2/nospfile/sysaux01.dbf

channel
ORA_AUX_DISK_1: restoring datafile 00003 to /data2/nospfile/undotbs01.dbf

channel
ORA_AUX_DISK_1: restoring datafile 00004 to /data2/nospfile/users01.dbf

channel
ORA_AUX_DISK_1: restoring datafile 00005 to /data2/nospfile/example01.dbf

channel
ORA_AUX_DISK_1: restoring datafile 00006 to /data2/nospfile/data01.dbf

channel
ORA_AUX_DISK_1: restoring datafile 00007 to /data2/nospfile/test01.dbf

channel
ORA_AUX_DISK_1: restoring datafile 00008 to /data2/nospfile/tools01.dbf

channel
ORA_AUX_DISK_1: reading from backup piece
/backup/rman_backup/db_arch_2vq495ua_1_1.bkp

channel
ORA_AUX_DISK_1: piece handle=/backup/rman_backup/db_arch_2vq495ua_1_1.bkp
tag=TAG20150413T101538

channel
ORA_AUX_DISK_1: restored backup piece 1

channel
ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:45

Finished
restore at 13-APR-15

contents
of Memory Script:

{

switch clone datafile all;

}

executing
Memory Script

datafile 1
switched to datafile copy

input
datafile copy RECID=9 STAMP=876912854 file name=/data2/nospfile/system01.dbf

datafile 2
switched to datafile copy

input
datafile copy RECID=10 STAMP=876912855 file name=/data2/nospfile/sysaux01.dbf

datafile 3
switched to datafile copy

input
datafile copy RECID=11 STAMP=876912856 file name=/data2/nospfile/undotbs01.dbf

datafile 4
switched to datafile copy

input
datafile copy RECID=12 STAMP=876912857 file name=/data2/nospfile/users01.dbf

datafile 5
switched to datafile copy

input
datafile copy RECID=13 STAMP=876912857 file name=/data2/nospfile/example01.dbf

datafile 6
switched to datafile copy

input
datafile copy RECID=14 STAMP=876912858 file name=/data2/nospfile/data01.dbf

datafile 7
switched to datafile copy

input
datafile copy RECID=15 STAMP=876912859 file name=/data2/nospfile/test01.dbf

datafile 8
switched to datafile copy

input
datafile copy RECID=16 STAMP=876912860 file name=/data2/nospfile/tools01.dbf

contents
of Memory Script:

{

set until scn 5176531;

recover

clone database

delete archivelog

;

}

executing
Memory Script

executing
command: SET until clause

Starting
recover at 13-APR-15

using
channel ORA_AUX_DISK_1

starting
media recovery

channel
ORA_AUX_DISK_1: starting archived log restore to default destination

channel
ORA_AUX_DISK_1: restoring archived log

archived
log thread=1 sequence=154

channel
ORA_AUX_DISK_1: reading from backup piece
/backup/rman_backup/db_arch_30q49603_1_1.bkp

channel
ORA_AUX_DISK_1: piece handle=/backup/rman_backup/db_arch_30q49603_1_1.bkp
tag=TAG20150413T101635

channel
ORA_AUX_DISK_1: restored backup piece 1

channel
ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

archived
log file name=/backup/archive/1_154_872701561.dbf thread=1 sequence=154

channel
clone_default: deleting archived log(s)

archived
log file name=/backup/archive/1_154_872701561.dbf RECID=1 STAMP=876912867

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

Finished
recover at 13-APR-15

contents
of Memory Script:

{

shutdown clone immediate;

startup clone nomount;

sql clone “alter system set db_name =

”NOSPFILE” 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 1152450560 bytes

Fixed
Size 2212696 bytes

Variable
Size 335547560 bytes

Database
Buffers 805306368 bytes

Redo
Buffers 9383936 bytes

sql
statement: alter system set db_name
= ”NOSPFILE” 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 1152450560 bytes

Fixed
Size 2212696 bytes

Variable
Size 335547560 bytes

Database
Buffers 805306368 bytes

Redo
Buffers 9383936 bytes

sql
statement: CREATE CONTROLFILE REUSE SET DATABASE “NOSPFILE” RESETLOGS
ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP
1 ( ‘/data2/nospfile/redo01.log’ ) SIZE 50 M ,

GROUP
2 ( ‘/data2/nospfile/redo02.log’ ) SIZE 50 M

DATAFILE

‘/data2/nospfile/system01.dbf’

CHARACTER SET WE8MSWIN1252

contents
of Memory Script:

{

set newname for tempfile 1 to

“/data2/nospfile/temp01.dbf”;

switch clone tempfile all;

catalog clone datafilecopy “/data2/nospfile/sysaux01.dbf”,

“/data2/nospfile/undotbs01.dbf”,

“/data2/nospfile/users01.dbf”,

“/data2/nospfile/example01.dbf”,

“/data2/nospfile/data01.dbf”,

“/data2/nospfile/test01.dbf”,

“/data2/nospfile/tools01.dbf”;

switch clone datafile all;

}

executing
Memory Script

executing
command: SET NEWNAME

renamed
tempfile 1 to /data2/nospfile/temp01.dbf in control file

cataloged
datafile copy

datafile
copy file name=/data2/nospfile/sysaux01.dbf RECID=1 STAMP=876912891

cataloged
datafile copy

datafile
copy file name=/data2/nospfile/undotbs01.dbf RECID=2 STAMP=876912892

cataloged
datafile copy

datafile
copy file name=/data2/nospfile/users01.dbf RECID=3 STAMP=876912892

cataloged
datafile copy

datafile
copy file name=/data2/nospfile/example01.dbf RECID=4 STAMP=876912892

cataloged
datafile copy

datafile
copy file name=/data2/nospfile/data01.dbf RECID=5 STAMP=876912893

cataloged
datafile copy

datafile
copy file name=/data2/nospfile/test01.dbf RECID=6 STAMP=876912893

cataloged
datafile copy

datafile
copy file name=/data2/nospfile/tools01.dbf RECID=7 STAMP=876912894

datafile 2
switched to datafile copy

input datafile
copy RECID=1 STAMP=876912891 file name=/data2/nospfile/sysaux01.dbf

datafile 3
switched to datafile copy

input
datafile copy RECID=2 STAMP=876912892 file name=/data2/nospfile/undotbs01.dbf

datafile 4
switched to datafile copy

input
datafile copy RECID=3 STAMP=876912892 file name=/data2/nospfile/users01.dbf

datafile 5
switched to datafile copy

input
datafile copy RECID=4 STAMP=876912892 file name=/data2/nospfile/example01.dbf

datafile 6
switched to datafile copy

input
datafile copy RECID=5 STAMP=876912893 file name=/data2/nospfile/data01.dbf

datafile 7
switched to datafile copy

input
datafile copy RECID=6 STAMP=876912893 file name=/data2/nospfile/test01.dbf

datafile 8
switched to datafile copy

input
datafile copy RECID=7 STAMP=876912894 file name=/data2/nospfile/tools01.dbf

contents
of Memory Script:

{

Alter clone database open resetlogs;

}

executing
Memory Script

database
opened

Finished
Duplicate Db at 13-APR-15

RMAN>

Conclusion
In the preceding scenario based article, we have learned that backup based duplication using Rman utility with different database name and different directory structure, without using spfile

BY
Name: Mirza Sarmadullah Baig
Designation: Senior Database Engineer
Organization: Netsoftmate IT Solutions.
Email: info@netsoftmate.com

February 12, 2017

Rman Targetless duplication with same DB name and same directory structure

Overview
Oracle Recovery Manager (RMAN) provides a comprehensive foundation for efficiently backing up and recovering the Oracle databases, it provides a common interface, via command line and Enterprise Manager, for backup tasks across different host operating systems, automates administration of your backup strategies.
Backup-based duplication uses an RMAN backup of the target (source) database as its source to create the data files in the auxiliary (destination) environment.
No connection to the target database is required. This is referred to as targetless duplication. This technique only requires a connection to the auxiliary database. Targetless duplication is available in Oracle 11g release 2 and higher.
The big advantage to backup based duplication is that if you work in an environment where it’s not possible to have a simultaneous connection to both the target and the auxiliary database you can still duplicate a database provided you can copy an RMAN backup to the auxiliary database
Server (or provided the backup is on network-mounted storage readable from the auxiliary server). In many environments, due to security rules, there is no network connectivity allowed from test environments to the production server.
In this scenario we have same database name and same directory structure.

Environment Details

Target (source) details:

Database Name	prod
Hostname	Nsmprod
Ip Address	172.16.110.18
OS	Linux
Version	x86_64
Datafile Location	/data2/prod
Backup Location	/backup/rman_backup

Destination details:

Database Name	prod
Hostname	Nsmstg
Ip Address	172.16.110.16
OS	Linux
Version	x86_64
Datafile Location	/data2/prod
Backup Location	/backup/rman_backup

Pre-requisites:

Password file from target database.
Sqlnet.ora should have correct parameters.
Target database should be running through spfile.

Steps on target (source) server:
1. export Oracle sid.

[oracle@Nsmprod dbs]$
hostname

Nsmprod

[oracle@Nsmprod dbs]$ export
ORACLE_SID=prod

2. Connect to target database via RMAN.

[oracle@Nsmprod dbs]$ rman
target sys/oracle

Recovery Manager: Release
11.2.0.1.0 – Production on Thu Apr 9 10:47:41 2015

connected to target
database: PROD (DBID=284539893)

RMAN>

3. Issue the following command to initiate backup. Before issue the backup command check the backup location is sat correctly.

RMAN> show all;

RMAN configuration
parameters for database with db_unique_name PROD are:

CONFIGURE RETENTION POLICY
TO REDUNDANCY 1; # default

CONFIGURE BACKUP
OPTIMIZATION OFF; # default

CONFIGURE DEFAULT DEVICE
TYPE TO DISK; # default

CONFIGURE CONTROLFILE
AUTOBACKUP ON;

CONFIGURE
CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO
‘/backup/rman_backup/controlfile_%F’;

CONFIGURE DEVICE TYPE DISK
PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default

CONFIGURE DATAFILE BACKUP
COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP
COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE
CHANNEL DEVICE TYPE DISK FORMAT
‘/backup/rman_backup/db_arch_%U.bkp’;

CONFIGURE MAXSETSIZE TO
UNLIMITED; # default

CONFIGURE ENCRYPTION FOR
DATABASE OFF; # default

CONFIGURE ENCRYPTION
ALGORITHM ‘AES128’; # default

CONFIGURE COMPRESSION
ALGORITHM ‘BASIC’ AS OF RELEASE ‘DEFAULT’ OPTIMIZE FOR LOAD TRUE ; # default

CONFIGURE ARCHIVELOG
DELETION POLICY TO NONE; # default

CONFIGURE SNAPSHOT
CONTROLFILE NAME TO ‘/data2/app/oracle/product/11.2.0/dbs/snapcf_prod.f’; #
default

RMAN>

RMAN> list backup;

specification does not match
any backup in the repository

RMAN>
backup database plus archivelog;

Starting backup at 10-APR-15

current log archived

using channel ORA_DISK_1

channel ORA_DISK_1: starting
archived log backup set

channel ORA_DISK_1:
specifying archived log(s) in backup set

input archived log thread=1
sequence=90 RECID=86 STAMP=875202890

input archived log thread=1
sequence=91 RECID=87 STAMP=875225400

input archived log thread=1
sequence=92 RECID=88 STAMP=875253619

input archived log thread=1
sequence=93 RECID=89 STAMP=875287426

input archived log thread=1
sequence=94 RECID=90 STAMP=875311887

input archived log thread=1
sequence=95 RECID=91 STAMP=875338210

input archived log thread=1
sequence=96 RECID=92 STAMP=875371700

input archived log thread=1
sequence=97 RECID=93 STAMP=875398375

input archived log thread=1
sequence=98 RECID=94 STAMP=875422534

input archived log thread=1
sequence=99 RECID=95 STAMP=875455916

input archived log thread=1
sequence=100 RECID=96 STAMP=875489442

input archived log thread=1
sequence=101 RECID=97 STAMP=875511892

input archived log thread=1
sequence=102 RECID=98 STAMP=875532973

input archived log thread=1
sequence=103 RECID=99 STAMP=875566589

input archived log thread=1
sequence=104 RECID=100 STAMP=875598381

input archived log thread=1
sequence=105 RECID=101 STAMP=875617253

input archived log thread=1
sequence=106 RECID=102 STAMP=875650222

input archived log thread=1
sequence=107 RECID=103 STAMP=875683804

input archived log thread=1
sequence=108 RECID=104 STAMP=875692844

input archived log thread=1
sequence=109 RECID=105 STAMP=875726521

input archived log thread=1
sequence=110 RECID=106 STAMP=875759423

input archived log thread=1
sequence=111 RECID=107 STAMP=875771364

input archived log thread=1
sequence=112 RECID=108 STAMP=875804400

input archived log thread=1
sequence=113 RECID=109 STAMP=875809288

input archived log thread=1
sequence=114 RECID=110 STAMP=875842252

input archived log thread=1
sequence=115 RECID=111 STAMP=875857844

input archived log thread=1
sequence=116 RECID=112 STAMP=875883160

input archived log thread=1
sequence=117 RECID=113 STAMP=875939543

input archived log thread=1
sequence=118 RECID=114 STAMP=875955632

input archived log thread=1
sequence=119 RECID=115 STAMP=875960595

input archived log thread=1
sequence=120 RECID=116 STAMP=876018651

input archived log thread=1
sequence=121 RECID=117 STAMP=876036137

input archived log thread=1
sequence=122 RECID=118 STAMP=876094207

input archived log thread=1
sequence=123 RECID=119 STAMP=876117455

input archived log thread=1
sequence=124 RECID=120 STAMP=876173429

input archived log thread=1
sequence=125 RECID=121 STAMP=876203937

input archived log thread=1
sequence=126 RECID=122 STAMP=876259857

input archived log thread=1
sequence=127 RECID=123 STAMP=876288622

input archived log thread=1
sequence=128 RECID=124 STAMP=876308432

input archived log thread=1
sequence=129 RECID=125 STAMP=876364250

input archived log thread=1
sequence=130 RECID=126 STAMP=876378605

input archived log thread=1
sequence=131 RECID=127 STAMP=876398550

input archived log thread=1
sequence=132 RECID=128 STAMP=876399532

input archived log thread=1
sequence=133 RECID=129 STAMP=876419413

input archived log thread=1
sequence=134 RECID=130 STAMP=876421079

input archived log thread=1
sequence=135 RECID=131 STAMP=876461598

input archived log thread=1
sequence=136 RECID=132 STAMP=876504610

input archived log thread=1
sequence=137 RECID=133 STAMP=876548085

input archived log thread=1
sequence=138 RECID=134 STAMP=876567056

input archived log thread=1
sequence=139 RECID=135 STAMP=876567230

input archived log thread=1
sequence=140 RECID=136 STAMP=876583581

input archived log thread=1
sequence=141 RECID=137 STAMP=876583754

input archived log thread=1
sequence=142 RECID=138 STAMP=876586722

input archived log thread=1
sequence=143 RECID=139 STAMP=876586874

input archived log thread=1
sequence=144 RECID=140 STAMP=876641790

input archived log thread=1
sequence=145 RECID=141 STAMP=876670700

channel ORA_DISK_1: starting
piece 1 at 10-APR-15

channel ORA_DISK_1: finished
piece 1 at 10-APR-15

piece
handle=/backup/rman_backup/db_arch_2qq41rnc_1_1.bkp tag=TAG20150410T153820
comment=NONE

channel ORA_DISK_1: backup
set complete, elapsed time: 00:01:35

Finished backup at 10-APR-15

Starting backup at 10-APR-15

using channel ORA_DISK_1

channel ORA_DISK_1: starting
full datafile backup set

channel ORA_DISK_1:
specifying datafile(s) in backup set

input datafile file
number=00001 name=/data2/prod/system01.dbf

input datafile file
number=00002 name=/data2/prod/sysaux01.dbf

input datafile file
number=00005 name=/data2/prod/example01.dbf

input datafile file
number=00003 name=/data2/prod/undotbs01.dbf

input datafile file
number=00006 name=/data2/prod/data01.dbf

input datafile file
number=00004 name=/data2/prod/users01.dbf

input datafile file
number=00007 name=/data2/prod/test01.dbf

input datafile file
number=00008 name=/data2/prod/tools01.dbf

channel ORA_DISK_1: starting
piece 1 at 10-APR-15

channel ORA_DISK_1: finished
piece 1 at 10-APR-15

piece handle=/backup/rman_backup/db_arch_2rq41rqd_1_1.bkp
tag=TAG20150410T153956 comment=NONE

channel ORA_DISK_1: backup
set complete, elapsed time: 00:01:05

Finished backup at 10-APR-15

Starting backup at 10-APR-15

current log archived

using channel ORA_DISK_1

channel ORA_DISK_1: starting
archived log backup set

channel ORA_DISK_1:
specifying archived log(s) in backup set

input archived log thread=1
sequence=146 RECID=142 STAMP=876670863

channel ORA_DISK_1: starting
piece 1 at 10-APR-15

channel ORA_DISK_1: finished
piece 1 at 10-APR-15

piece
handle=/backup/rman_backup/db_arch_2sq41rsf_1_1.bkp tag=TAG20150410T154103
comment=NONE

channel ORA_DISK_1: backup
set complete, elapsed time: 00:00:01

Finished backup at 10-APR-15

Starting Control File and
SPFILE Autobackup at 10-APR-15

piece
handle=/backup/rman_backup/controlfile_c-284539893-20150410-00 comment=NONE

Finished Control File and
SPFILE Autobackup at 10-APR-15

RMAN>

4. Create pfile and copy to destination server.

SQL> create
pfile=’/backup/rman_backup/initprod.ora’ from spfile;

File created.

[oracle@Nsmprod
rman_backup]$ scp initprod.ora
oracle@172.16.110.16:/data1/oracle11g/product/dbs/

oracle@172.16.110.16’s
password:

initprod.ora

100% 1169 1.1KB/s
00:00

create directory structure to accommodate rman backup pieces on destination server and start copy.

[oracle@Nsmstg prod]$ mkdir
-p /backup/rman_backup/

5. Copy rman backup from target to destination server.

RMAN> exit

Recovery Manager complete.

[oracle@Nsmprod
rman_backup]$ ls -lrth

total 2.8G

-rw-r—–. 1 oracle dba
1.7G Apr 10 15:39 db_arch_2qq41rnc_1_1.bkp

-rw-r—–. 1 oracle dba 1.2G
Apr 10 15:40 db_arch_2rq41rqd_1_1.bkp

-rw-r—–. 1 oracle
dba 24K Apr 10 15:41
db_arch_2sq41rsf_1_1.bkp

-rw-r—–. 1 oracle dba
9.6M Apr 10 15:41 controlfile_c-284539893-20150410-00

[oracle@Nsmprod
rman_backup]$ scp * oracle@172.16.110.16:/backup/rman_backup/

oracle@172.16.110.16’s
password:

controlfile_c-284539893-20150410-00

100% 9792KB 9.6MB/s
00:01

db_arch_2qq41rnc_1_1.bkp

100% 1668MB 11.2MB/s
02:29

db_arch_2rq41rqd_1_1.bkp

100% 1135MB 11.2MB/s
01:41

db_arch_2sq41rsf_1_1.bkp

100% 24KB
23.5KB/s 00:00

Steps on destination server
6. Create directory structure same as target (source) database.

[oracle@Nsmstg prod]$ mkdir
-p /data2/prod

[oracle@Nsmstg prod]$ mkdir
-p /backup/archive/

7. Start the instance in nomount stage.

[oracle@Nsmstg dbs]$ export
ORACLE_SID=prod

[oracle@Nsmstg dbs]$ sqlplus

SQL*Plus: Release 11.2.0.1.0
Production on Fri Apr 10 15:58:10 2015

Enter user-name: sys as
sysdba

Enter password:

Connected to an idle
instance.

SQL>

SQL> startup nomount

ORACLE instance started.

Total System Global Area
1152450560 bytes

Fixed Size 2212696 bytes

Variable Size 352324776 bytes

Database Buffers 788529152 bytes

Redo Buffers 9383936 bytes

SQL> exit

Disconnected from Oracle
Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production

With the Partitioning, OLAP,
Data Mining and Real Application Testing options

[oracle@Nsmstg dbs]$

8. Connect to auxiliary instance via RMAN

[oracle@Nsmstg dbs]$ rman
auxiliary sys/oracle

Recovery Manager: Release
11.2.0.1.0 – Production on Fri Apr 10 15:59:22 2015

connected to auxiliary
database: PROD (not mounted)

RMAN>

9. Issue the following command to duplicate database with same name and same directory structure.

RMAN>
duplicate database to prod backup location ‘/backup/rman_backup’ nofilenamecheck;

Starting Duplicate Db at
10-APR-15

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

Fixed Size 2212696 bytes

Variable Size 335547560 bytes

Database Buffers 805306368 bytes

Redo Buffers 9383936 bytes

contents of Memory Script:

{

sql clone “alter system set db_name =

”PROD” comment=

”Modified by RMAN duplicate”
scope=spfile”;

sql clone “alter system set db_unique_name =

”PROD” comment=

”Modified by RMAN duplicate”
scope=spfile”;

shutdown clone immediate;

startup clone force nomount

restore clone primary controlfile from
‘/backup/rman_backup/controlfile_c-284539893-20150410-00’;

alter clone database mount;

}

executing Memory Script

sql statement: alter system
set db_name = ”PROD” comment= ”Modified by RMAN
duplicate” scope=spfile

sql statement: alter system
set db_unique_name = ”PROD” comment= ”Modified by RMAN
duplicate” scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global
Area 1152450560 bytes

Fixed Size 2212696 bytes

Variable Size 335547560 bytes

Database Buffers 805306368 bytes

Redo Buffers 9383936 bytes

Starting restore at
10-APR-15

allocated channel:
ORA_AUX_DISK_1

channel ORA_AUX_DISK_1:
SID=63 device type=DISK

channel ORA_AUX_DISK_1:
restoring control file

channel ORA_AUX_DISK_1:
restore complete, elapsed time: 00:00:03

output file
name=/data2/prod/control01.ctl

output file
name=/data2/prod/control02.ctl

Finished restore at
10-APR-15

database mounted

released channel:
ORA_AUX_DISK_1

allocated channel:
ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=63
device type=DISK

contents of Memory Script:

{

set until scn 5022379;

set newname for datafile 1 to

“/data2/prod/system01.dbf”;

set newname for datafile 2 to

“/data2/prod/sysaux01.dbf”;

set newname for datafile 3 to

“/data2/prod/undotbs01.dbf”;

set newname for datafile 4 to

“/data2/prod/users01.dbf”;

set newname for datafile 5 to

“/data2/prod/example01.dbf”;

set newname for datafile 6 to

“/data2/prod/data01.dbf”;

set newname for datafile 7 to

“/data2/prod/test01.dbf”;

set newname for datafile 8 to

“/data2/prod/tools01.dbf”;

restore

clone database

;

}

executing Memory Script

executing command: SET until
clause

executing command: SET
NEWNAME

executing command: SET NEWNAME

executing command: SET
NEWNAME

Starting restore at
10-APR-15

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1:
starting datafile backup set restore

channel ORA_AUX_DISK_1:
specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1:
restoring datafile 00001 to /data2/prod/system01.dbf

channel ORA_AUX_DISK_1:
restoring datafile 00002 to /data2/prod/sysaux01.dbf

channel ORA_AUX_DISK_1:
restoring datafile 00003 to /data2/prod/undotbs01.dbf

channel ORA_AUX_DISK_1:
restoring datafile 00004 to /data2/prod/users01.dbf

channel ORA_AUX_DISK_1:
restoring datafile 00005 to /data2/prod/example01.dbf

channel ORA_AUX_DISK_1:
restoring datafile 00006 to /data2/prod/data01.dbf

channel ORA_AUX_DISK_1:
restoring datafile 00007 to /data2/prod/test01.dbf

channel ORA_AUX_DISK_1:
restoring datafile 00008 to /data2/prod/tools01.dbf

channel ORA_AUX_DISK_1:
reading from backup piece /backup/rman_backup/db_arch_2rq41rqd_1_1.bkp

channel ORA_AUX_DISK_1:
piece handle=/backup/rman_backup/db_arch_2rq41rqd_1_1.bkp
tag=TAG20150410T153956

channel ORA_AUX_DISK_1:
restored backup piece 1

channel ORA_AUX_DISK_1:
restore complete, elapsed time: 00:00:35

Finished restore at
10-APR-15

contents of Memory Script:

{

switch clone datafile all;

}

executing Memory Script

datafile 1 switched to
datafile copy

input datafile copy RECID=1
STAMP=876672089 file name=/data2/prod/system01.dbf

datafile 2 switched to
datafile copy

input datafile copy RECID=2
STAMP=876672089 file name=/data2/prod/sysaux01.dbf

datafile 3 switched to
datafile copy

input datafile copy RECID=3
STAMP=876672090 file name=/data2/prod/undotbs01.dbf

datafile 4 switched to
datafile copy

input datafile copy RECID=4
STAMP=876672090 file name=/data2/prod/users01.dbf

datafile 5 switched to
datafile copy

input datafile copy RECID=5
STAMP=876672090 file name=/data2/prod/example01.dbf

datafile 6 switched to
datafile copy

input datafile copy RECID=6
STAMP=876672091 file name=/data2/prod/data01.dbf

datafile 7 switched to
datafile copy

input datafile copy RECID=7
STAMP=876672091 file name=/data2/prod/test01.dbf

datafile 8 switched to
datafile copy

input datafile copy RECID=8
STAMP=876672091 file name=/data2/prod/tools01.dbf

contents of Memory Script:

{

set until scn 5022379;

recover

clone database

delete archivelog

;

}

executing Memory Script

executing command: SET until
clause

Starting recover at
10-APR-15

using channel ORA_AUX_DISK_1

starting media recovery

channel ORA_AUX_DISK_1:
starting archived log restore to default destination

channel ORA_AUX_DISK_1:
restoring archived log

archived log thread=1
sequence=146

channel ORA_AUX_DISK_1:
reading from backup piece /backup/rman_backup/db_arch_2sq41rsf_1_1.bkp

channel ORA_AUX_DISK_1:
piece handle=/backup/rman_backup/db_arch_2sq41rsf_1_1.bkp
tag=TAG20150410T154103

channel ORA_AUX_DISK_1:
restored backup piece 1

channel ORA_AUX_DISK_1:
restore complete, elapsed time: 00:00:01

archived log file
name=/backup/archive/1_146_872701561.dbf thread=1 sequence=146

channel clone_default:
deleting archived log(s)

archived log file
name=/backup/archive/1_146_872701561.dbf RECID=1 STAMP=876672098

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

Finished recover at
10-APR-15

contents of Memory Script:

{

shutdown clone immediate;

startup clone nomount;

sql clone “alter system set db_name =

”PROD” 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 1152450560 bytes

Fixed Size 2212696 bytes

Variable Size 335547560 bytes

Database Buffers 805306368 bytes

Redo Buffers 9383936 bytes

sql statement: alter system
set db_name = ”PROD” 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 1152450560 bytes

Fixed Size 2212696 bytes

Variable Size 335547560 bytes

Database Buffers 805306368 bytes

Redo Buffers 9383936 bytes

sql statement: CREATE
CONTROLFILE REUSE SET DATABASE “PROD” RESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP
1 ( ‘/data2/prod/redo01.log’ ) SIZE 50 M
REUSE,

GROUP
2 ( ‘/data2/prod/redo02.log’ ) SIZE 50 M
REUSE,

GROUP
3 ( ‘/data2/prod/redo03.log’ ) SIZE 50 M
REUSE

DATAFILE

‘/data2/prod/system01.dbf’

CHARACTER SET WE8MSWIN1252

contents of Memory Script:

{

set newname for tempfile 1 to

“/data2/prod/temp01.dbf”;

switch clone tempfile all;

catalog clone datafilecopy “/data2/prod/sysaux01.dbf”,

“/data2/prod/undotbs01.dbf”,

“/data2/prod/users01.dbf”,

“/data2/prod/example01.dbf”,

“/data2/prod/data01.dbf”,

“/data2/prod/test01.dbf”,

“/data2/prod/tools01.dbf”;

switch clone datafile all;

}

executing Memory Script

executing command: SET
NEWNAME

renamed tempfile 1 to
/data2/prod/temp01.dbf in control file

cataloged datafile copy

datafile copy file
name=/data2/prod/sysaux01.dbf RECID=1 STAMP=876672123

cataloged datafile copy

datafile copy file name=/data2/prod/undotbs01.dbf
RECID=2 STAMP=876672123

cataloged datafile copy

datafile copy file
name=/data2/prod/users01.dbf RECID=3 STAMP=876672124

cataloged datafile copy

datafile copy file
name=/data2/prod/example01.dbf RECID=4 STAMP=876672124

cataloged datafile copy

datafile copy file
name=/data2/prod/data01.dbf RECID=5 STAMP=876672125

cataloged datafile copy

datafile copy file
name=/data2/prod/test01.dbf RECID=6 STAMP=876672125

cataloged datafile copy

datafile copy file
name=/data2/prod/tools01.dbf RECID=7 STAMP=876672125

datafile 2 switched to
datafile copy

input datafile copy RECID=1
STAMP=876672123 file name=/data2/prod/sysaux01.dbf

datafile 3 switched to
datafile copy

input datafile copy RECID=2
STAMP=876672123 file name=/data2/prod/undotbs01.dbf

datafile 4 switched to
datafile copy

input datafile copy RECID=3
STAMP=876672124 file name=/data2/prod/users01.dbf

datafile 5 switched to
datafile copy

input datafile copy RECID=4
STAMP=876672124 file name=/data2/prod/example01.dbf

datafile 6 switched to
datafile copy

input datafile copy RECID=5
STAMP=876672125 file name=/data2/prod/data01.dbf

datafile 7 switched to
datafile copy

input datafile copy RECID=6
STAMP=876672125 file name=/data2/prod/test01.dbf

datafile 8 switched to
datafile copy

input datafile copy RECID=7
STAMP=876672125 file name=/data2/prod/tools01.dbf

contents of Memory Script:

{

Alter clone database open resetlogs;

}

executing Memory Script

database opened

Finished Duplicate Db at
10-APR-15

RMAN>

RMAN> exit

Recovery Manager complete.

[oracle@Nsmstg dbs]$ sqlplus

SQL*Plus: Release 11.2.0.1.0
Production on Fri Apr 10 16:04:05 2015

Enter user-name: sys as
sysdba

Enter password:

Connected to:

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

With the Partitioning, OLAP,
Data Mining and Real Application Testing options

SQL> select
name,open_mode from v$database;

NAME OPEN_MODE

———
——————–

PROD READ WRITE

SQL> select host_name from
v$instance;

HOST_NAME

—————————————————————-

Nsmstg

SQL> select
tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME STATUS

——————————
———

SYSTEM ONLINE

SYSAUX ONLINE

UNDOTBS1 ONLINE

TEMP ONLINE

USERS ONLINE

EXAMPLE ONLINE

DATA ONLINE

TEST ONLINE

TOOLS ONLINE

9 rows selected.

Conclusion
In the above article, we have learned that backup based duplication using Rman utility with same database name and same directory structure, we can refer this as targetless duplication, where no connectivity between target and auxiliary instance is required.

BY
Name: Omer
Designation: Senior Database Engineer
Organization: Netsoftmate IT Solutions.
Contact us: info@netsoftmate.com

February 12, 2017

Rman backup based duplication with same DB name and directory structure and target db have Read only tablespaces

You can replicate a target (source) database using an RMAN backup when duplicating to an auxiliary (destination) database. The basic idea here is to copy an RMAN backup to an auxiliary server and create the auxiliary database directly from the backup. This is a simple and powerful technique for replicating a database. It is especially applicable where there’s no direct network connection between the target database and the auxiliary.

Here we are having same database name and same directory structure. And target database have read only tablespaces.

Environment details
Target
(source) details:

Database Name	prod
Hostname	Nsmdev
Ip Address	172.16.110.18
OS	Linux
Version	x86_64
Datafile Location	/data2/prod
Backup Location	/backup/rman_backup

Destination details:

Database Name	prod
Hostname	Nsmqty
Ip Address	172.16.110.16
OS	Linux
Version	x86_64
Datafile Location	/data2/prod
Backup Location	/backup/rman_backup

Pre-requisites

Password file from target database.
Sqlnet.ora should have correct parameters.
Target database should be running through spfile.

Steps on target (source) server
1. export Oracle sid.

[oracle@Nsmdev dbs]$
hostname

Nsmdev

[oracle@Nsmdev dbs]$ export
ORACLE_SID=prod

2. Login to database and check tablespace details.

SQL> select
tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME STATUS

——————–
———-

SYSTEM ONLINE

SYSAUX ONLINE

UNDOTBS1 ONLINE

TEMP ONLINE

USERS ONLINE

EXAMPLE ONLINE

DATA READ ONLY

TEST ONLINE

TOOLS READ ONLY

9 rows selected.

3. Connect to RMAN.

[oracle@Nsmdev ~]$ rman
target sys/oracle

Recovery Manager: Release
11.2.0.1.0 – Production on Wed Apr 15 12:44:17 2015

connected to target
database: PROD (DBID=284539893)

RMAN>

4. Issue the following command to initiate backup. Before issue the backup command check the backup location is sat correctly.

RMAN> show all;

RMAN configuration
parameters for database with db_unique_name PROD are:

CONFIGURE RETENTION POLICY
TO REDUNDANCY 1; # default

CONFIGURE BACKUP
OPTIMIZATION OFF; # default

CONFIGURE DEFAULT DEVICE
TYPE TO DISK; # default

CONFIGURE CONTROLFILE
AUTOBACKUP ON;

CONFIGURE CONTROLFILE
AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘/backup/rman_backup/controlfile_%F’;

CONFIGURE DEVICE TYPE DISK
PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default

CONFIGURE DATAFILE BACKUP
COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP
COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE CHANNEL DEVICE
TYPE DISK FORMAT
‘/backup/rman_backup/db_arch_%U.bkp’;

CONFIGURE MAXSETSIZE TO
UNLIMITED; # default

CONFIGURE ENCRYPTION FOR
DATABASE OFF; # default

CONFIGURE ENCRYPTION
ALGORITHM ‘AES128’; # default

CONFIGURE COMPRESSION
ALGORITHM ‘BASIC’ AS OF RELEASE ‘DEFAULT’ OPTIMIZE FOR LOAD TRUE ; # default

CONFIGURE ARCHIVELOG
DELETION POLICY TO NONE; # default

CONFIGURE SNAPSHOT
CONTROLFILE NAME TO ‘/data2/app/oracle/product/11.2.0/dbs/snapcf_prod.f’; #
default

RMAN> backup database
plus archivelog;

Starting backup at 15-APR-15

current log archived

using channel ORA_DISK_1

channel ORA_DISK_1: starting
archived log backup set

channel ORA_DISK_1:
specifying archived log(s) in backup set

input archived log thread=1
sequence=161 RECID=157 STAMP=877092331

channel ORA_DISK_1: starting
piece 1 at 15-APR-15

channel ORA_DISK_1: finished
piece 1 at 15-APR-15

piece handle=/backup/rman_backup/db_arch_3lq4enfb_1_1.bkp
tag=TAG20150415T124531 comment=NONE

channel ORA_DISK_1: backup
set complete, elapsed time: 00:00:01

Finished backup at 15-APR-15