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.

Environment Details

Target (Source) details

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

Tns details:

PROD =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = prod)

)

Destination (Auxiliary) details:

Database Name	Prod
Hostname	Nsm-linux01
Ip Address	172.16.110.16
OS	Linux
Version	x86_64
Datafile Location	/data2/prod
Backup Location	/backup/rman_backup

Tns details:

DUP =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = 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

Steps on target (source) server

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

[oracle@Snsm-linux02 admin]$ cd /data2/app/oracle/product/11.2.0/network/admin

[oracle@Snsm-linux02 admin]$ vi tnsnames.ora

DUP =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = dup)

)

[oracle@Snsm-linux02 admin]$ tnsping dup

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

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

OK (0 msec)

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

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

PROD READ WRITE ARCHIVELOG

Check database is running from spfile.

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

VALUE

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

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

SQL> show parameter pfile

NAME TYPE VALUE

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

spfile string /data2/app/oracle/product/11.2

.0/dbs/spfileprod.ora

Create pfile from spfile.

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

File created.

Create password file.

[oracle@Snsm-linux02 dbs]$ pwd

/data2/app/oracle/product/11.2.0/dbs

[oracle@Snsm-linux02 dbs]$orapwd file=orapwprod password=oracle

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

scp orapwprod oracle@172.16.110.18:/data2/app/oracle/product/11.2.0/dbs/

scp /backup/rman_backup/initprod.ora oracle@172.16.110.18:/data2/app/oracle/product/11.2.0/dbs/

Steps on destination server

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

[oracle@Nsm-linux01 admin]$ cd /data2/app/oracle/product/11.2.0/network/admin

[oracle@Nsm-linux01 admin]$

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = dup)

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

(SID_NAME = prod)

)

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@Nsm-linux01 admin]$ lsnrctl

LSNRCTL for Linux: Version 11.2.0.1.0 – Production on 01-APR-2015 12:46:41

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 “dup” has 1 instance(s).

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

Handler(s):

“DEDICATED” established:0 refused:0

LOCAL SERVER

Service “prod” has 1 instance(s).

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

LOCAL SERVER

The command completed successfully

Put Tns entry for target (source) database in $ORACLE_HOME/network/admin directory

[oracle@Nsm-linux01 admin]$ vi tnsnames.ora

PROD =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = prod)

)

[oracle@Nsm-linux01 admin]$ tnsping prod

TNS Ping Utility for Linux: Version 11.2.0.1.0 – Production on 01-APR-2015 12:49:28

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

OK (10 msec)

Create the directory structure same as target(source) server.

[oracle@Nsm-linux01 admin]$ mkdir -p /data2/prod

[oracle@Nsm-linux01 admin]$ mkdir -p /backup/archive

Set the oracle Sid and start the instance in no mount stage.

[oracle@Nsm-linux01 admin]$ export ORACLE_SID=prod

[oracle@Nsm-linux01 admin]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Wed Apr 1 12:54:37 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 335547560 bytes

Database Buffers 805306368 bytes

Redo Buffers 9383936 bytes

SQL>

Now connect to Rman

[oracle@Nsm-linux01 admin]$ rman target sys/oracle@prod auxiliary sys/oracle@dup

Recovery Manager: Release 11.2.0.1.0 – Production on Wed Apr 1 12:56:39 2015

connected to target database: PROD (DBID=284539893)

connected to auxiliary database: PROD (not mounted)

RMAN>

Issue the command to duplicate the database

RMAN> DUPLICATE TARGET DATABASE TO prod FROM ACTIVE DATABASE NOFILENAMECHECK;

Starting Duplicate Db at 01-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

Following is the output.

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

backup as copy current controlfile auxiliary format ‘/data2/prod/control01.ctl’;

restore clone controlfile to ‘/data2/prod/control02.ctl’ from

‘/data2/prod/control01.ctl’;

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 backup at 01-APR-15

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=76 device type=DISK

channel ORA_DISK_1: starting datafile copy

copying current control file

output file name=/data2/app/oracle/product/11.2.0/dbs/snapcf_prod.f tag=TAG20150401T124940 RECID=4 STAMP=875882981

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

Finished backup at 01-APR-15

Starting restore at 01-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 01-APR-15

database mounted

contents of Memory Script:

{

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”;

backup as copy reuse

datafile 1 auxiliary format

“/data2/prod/system01.dbf” datafile

2 auxiliary format

“/data2/prod/sysaux01.dbf” datafile

3 auxiliary format

“/data2/prod/undotbs01.dbf” datafile

4 auxiliary format

“/data2/prod/users01.dbf” datafile

5 auxiliary format

“/data2/prod/example01.dbf” ;

sql ‘alter system archive log current’;

}

executing Memory Script

executing command: SET NEWNAME

Starting backup at 01-APR-15

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

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

output file name=/data2/prod/system01.dbf tag=TAG20150401T124955

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

channel ORA_DISK_1: starting datafile copy

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

output file name=/data2/prod/sysaux01.dbf tag=TAG20150401T124955

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

channel ORA_DISK_1: starting datafile copy

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

output file name=/data2/prod/example01.dbf tag=TAG20150401T124955

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

channel ORA_DISK_1: starting datafile copy

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

output file name=/data2/prod/undotbs01.dbf tag=TAG20150401T124955

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

channel ORA_DISK_1: starting datafile copy

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

output file name=/data2/prod/users01.dbf tag=TAG20150401T124955

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

Finished backup at 01-APR-15

sql statement: alter system archive log current

contents of Memory Script:

{

backup as copy reuse

archivelog like “/backup/archive/1_116_872701561.dbf” auxiliary format

“/backup/archive/1_116_872701561.dbf” ;

catalog clone archivelog “/backup/archive/1_116_872701561.dbf”;

switch clone datafile all;

}

executing Memory Script

Starting backup at 01-APR-15

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log copy

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

output file name=/backup/archive/1_116_872701561.dbf RECID=0 STAMP=0

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

Finished backup at 01-APR-15

cataloged archived log

archived log file name=/backup/archive/1_116_872701561.dbf RECID=112 STAMP=875883644

datafile 1 switched to datafile copy

input datafile copy RECID=4 STAMP=875883644 file name=/data2/prod/system01.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=5 STAMP=875883644 file name=/data2/prod/sysaux01.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=6 STAMP=875883645 file name=/data2/prod/undotbs01.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=7 STAMP=875883645 file name=/data2/prod/users01.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=8 STAMP=875883645 file name=/data2/prod/example01.dbf

contents of Memory Script:

{

set until scn 4529390;

recover

clone database

delete archivelog

;

}

executing Memory Script

executing command: SET until clause

Starting recover at 01-APR-15

using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 116 is already on disk as file /backup/archive/1_116_872701561.dbf

archived log file name=/backup/archive/1_116_872701561.dbf thread=1 sequence=116

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

Finished recover at 01-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”;

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=875883671

cataloged datafile copy

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

cataloged datafile copy

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

cataloged datafile copy

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

datafile 2 switched to datafile copy

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

datafile 3 switched to datafile copy

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

datafile 4 switched to datafile copy

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

datafile 5 switched to datafile copy

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

contents of Memory Script:

{

Alter clone database open resetlogs;

}

executing Memory Script

database opened

Finished Duplicate Db at 01-APR-15

Issues which we have faced are as follows

Everything has went well listener services were also worked fine, RMAN connectivity from auxiliary server also fine using net service name but When issue the duplicate database command from RMAN> prompt it has thrown following error.

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

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

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

RMAN-03002: failure of Duplicate Db command at 03/31/2015 16:01:01

RMAN-03015: error occurred in stored script Memory Script

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 03/31/2015 16:01:01

ORA-17629: Cannot connect to the remote database server

ORA-17627: ORA-12154: TNS:could not resolve the connect identifier specified

ORA-17629: Cannot connect to the remote database server

Then we have searched for solutions, we get one MOS note 1144273.1

This note gives some idea like causes and solutions from that we have checked in our databases on destination server there are wrong parameters in sqlnet.ora then we have corrected the sqlnet.ora file as follows

[oracle@Nsm-linux01 admin]$ cat sqlnet.ora

# sqlnet.ora Network Configuration File: /data2/app/oracle/product/11.2.0/network/admin/sqlnet.ora

# Generated by Oracle configuration tools.

SQLNET.AUTHENTICATION_SERVICES = (NTS,NONE)

#NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

ADR_BASE = /data2/app/oracle

Now we have executed again, this time we get different error, check below.

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

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

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

RMAN-03002: failure of Duplicate Db command at 03/31/2015 16:23:02

RMAN-03015: error occurred in stored script Memory Script

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 03/31/2015 16:23:02

ORA-17628: Oracle error 19505 returned by remote Oracle server

After dig out the error we get to know that there is no directory structure is available on destination server to accommodate data files and control Files, we have created the directory structure and execute the duplicate command again, this time has successful.

Additionally there is one more MOS note for known issues while duplicating RMAN. 1366290.1

Conclusion

In the above article, we have learned that Active Duplication using Rman utility with same database name and same directory structure, and we have faced some errors and their work around.

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

This email has been checked for viruses by Avast antivirus software.
www.avast.com

SCN Based RMAN backup in nohup

By Netsoftmate on

November 2, 2017

- Uncategorized

In this article, I would like to demonstrate how to create a shell script to perform an RMAN SCN based backup and execute in background.

Tip: Scripting is a powerful tool to automate and execute jobs in background.

Below example shows how to perform an SCN based RMAN Incremental backup using 4 Channels when we perform Roll-forward of Physical Standby.

Step 1. Make a RMAN command file with RMAN Commands.

Create a cmd script and include RMAN backup command.

Get current SCN of Physical Standby and select a location to store RMAN backup files.

SQL> column BYTES current_scn 99999999999999

SQL> select current_scn from v$database;

CURRENT_SCN

———–

7707430599033

$ cd /orac01/orclprd_standby
$ vi SCN_based_backup.cmd
run
{
allocate channel t1 type disk ;
allocate channel t2 type disk ;
allocate channel t3 type disk ;
allocate channel t4 type disk ;
backup incremental from scn 7707430599033 database format ‘/ora01/orclprd_standby/stnd_backp_%U.bak’;
release channel t1;
release channel t2;
release channel t3;
release channel t4;
}

Step 2. Prepare shell script and include above .cmd RMAN

$ vi rman_scn_based_incremental_bkup_for_standby.ksh
#!/bin/ksh
export ORACLE_SID=orclprd
export ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/db
export PATH=$PATH1:$ORACLE_HOME/bin
rman target / msglog /ora01/orclprd_standby/rman_SCN_backup.log cmdfile=/ora01/orclprd/RMAN_SCN_backup_from_primary/SCN_ based_backup.cmd

Step 3. Change permissions of above shell scripts

$ chmod 755 rman_scn_based_incremental_bkup_for_standby

Step 4. Start RMAN using nohup to run in background

$ nohup ./rman_scn_based_incremental_bkup_for_standby &

Monitor RMAN log

$ tail -300f /ora01/orclprd_standby/rman_SCN_backup.log

Conclusion: In this article we have learned how to perform RMAN SCN based backup using Unix shell script. Incremental backup are very useful such as rolling forward an standby database.

Author : Hameed

Rman Duplicate from noarchivelog mode target database

By Netsoftmate on

February 12, 2017

- Uncategorized

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

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

By Netsoftmate on

February 12, 2017

- Uncategorized

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

Rman Targetless duplication with same DB name and same directory structure

By Netsoftmate on

February 12, 2017

- Uncategorized

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