Substitution Variables in Oracle 11g RMAN scripts

Starting with Oracle 11g substitution variables can be used in RMAN scripts similarly to what sqlplus used to offer in earlier releases.

Let’s create a scripts e:\backup.cmd with the following content.

CONNECT TARGET /

BACKUP TAG &1 COPIES &2 &3;

EXIT;

Let’s create e:\backup.bat file with the following content.

set ORACLE_HOME=E:\oracle11gr2\app\User\product\11.2.0\dbhome_1

echo %1

echo %2

echo %3

rman @’e:\backup.cmd’ using %1 %2 %3

We can invoke the script without substitution variables and will be prompted for values.

E:\oracle11gR2\app\User\product\11.2.0\dbhome_1>rman @e:\backup.cmd

Recovery Manager: Release 11.2.0.1.0 – Production on Mon Sep 6 17:05:28 2010

RMAN> CONNECT TARGET *

2> BACKUP TAG

Enter value for 1: ‘FULL’

‘FULL’ COPIES

Enter value for 2: 1

Enter value for 3: database

database;

3> EXIT;

connected to target database: ORCL11GR (DBID=702742010)

Starting backup at 06-SEP-10

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=6 device type=DISK

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=E:\ORACLE11GR2\APP\USER\ORADATA\ORCL11GR2\

SYSTEM01.DBF

input datafile file number=00002 name=E:\ORACLE11GR2\APP\USER\ORADATA\ORCL11GR2\

SYSAUX01.DBF

input datafile file number=00005 name=E:\ORACLE11GR2\APP\USER\ORADATA\ORCL11GR2\

EXAMPLE01.DBF

input datafile file number=00003 name=E:\ORACLE11GR2\APP\USER\ORADATA\ORCL11GR2\

UNDOTBS01.DBF

input datafile file number=00004 name=E:\ORACLE11GR2\APP\USER\ORADATA\ORCL11GR2\

USERS01.DBF

channel ORA_DISK_1: starting piece 1 at 06-SEP-10

channel ORA_DISK_1: finished piece 1 at 06-SEP-10

piece handle=E:\ORACLE11GR2\APP\USER\FLASH_RECOVERY_AREA\ORCL11GR2\BACKUPSET\201

0_09_06\O1_MF_NNNDF_FULL_689X9PC2_.BKP tag=FULL comment=NONE

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

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

including current SPFILE in backup set

channel ORA_DISK_1: starting piece 1 at 06-SEP-10

channel ORA_DISK_1: finished piece 1 at 06-SEP-10

piece handle=E:\ORACLE11GR2\APP\USER\FLASH_RECOVERY_AREA\ORCL11GR2\BACKUPSET\201

0_09_06\O1_MF_NCSNF_FULL_689XF9M9_.BKP tag=FULL comment=NONE

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

Finished backup at 06-SEP-10

Recovery Manager complete.

E:\oracle11gR2\app\User\product\11.2.0\dbhome_1>

We can pass the parameters directly to the rman

E:\oracle11gR2\app\User\product\11.2.0\dbhome_1>rman @e:\backup.cmd ‘TEST1’ 1 ‘

database’

Recovery Manager: Release 11.2.0.1.0 – Production on Mon Sep 6 16:55:58 2010

RMAN> CONNECT TARGET *

2> BACKUP TAG TEST1 COPIES 1 database;

3> EXIT;

connected to target database: ORCL11GR (DBID=702742010)

Starting backup at 06-SEP-10

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=65 device type=DISK

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=E:\ORACLE11GR2\APP\USER\ORADATA\ORCL11GR2\

SYSTEM01.DBF

input datafile file number=00002 name=E:\ORACLE11GR2\APP\USER\ORADATA\ORCL11GR2\

SYSAUX01.DBF

input datafile file number=00005 name=E:\ORACLE11GR2\APP\USER\ORADATA\ORCL11GR2\

EXAMPLE01.DBF

input datafile file number=00003 name=E:\ORACLE11GR2\APP\USER\ORADATA\ORCL11GR2\

UNDOTBS01.DBF

input datafile file number=00004 name=E:\ORACLE11GR2\APP\USER\ORADATA\ORCL11GR2\

USERS01.DBF

channel ORA_DISK_1: starting piece 1 at 06-SEP-10

channel ORA_DISK_1: finished piece 1 at 06-SEP-10

piece handle=E:\ORACLE11GR2\APP\USER\FLASH_RECOVERY_AREA\ORCL11GR2\BACKUPSET\201

0_09_06\O1_MF_NNNDF_TEST1_689WQJ6Q_.BKP tag=TEST1 comment=NONE

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

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

including current SPFILE in backup set

channel ORA_DISK_1: starting piece 1 at 06-SEP-10

channel ORA_DISK_1: finished piece 1 at 06-SEP-10

piece handle=E:\ORACLE11GR2\APP\USER\FLASH_RECOVERY_AREA\ORCL11GR2\BACKUPSET\201

0_09_06\O1_MF_NCSNF_TEST1_689WV4CP_.BKP tag=TEST1 comment=NONE

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

Finished backup at 06-SEP-10

Recovery Manager complete.

We can pass the parameters directly to rman specifying using option

E:\oracle11gR2\app\User\product\11.2.0\dbhome_1>rman @e:\backup.cmd using ‘TEST

1′ 1 ‘database’

Recovery Manager: Release 11.2.0.1.0 – Production on Mon Sep 6 16:58:14 2010

RMAN> CONNECT TARGET *

2> BACKUP TAG TEST1 COPIES 1 database;

3> EXIT;

connected to target database: ORCL11GR (DBID=702742010)

Starting backup at 06-SEP-10

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=6 device type=DISK

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=E:\ORACLE11GR2\APP\USER\ORADATA\ORCL11GR2\

SYSTEM01.DBF

input datafile file number=00002 name=E:\ORACLE11GR2\APP\USER\ORADATA\ORCL11GR2\

SYSAUX01.DBF

input datafile file number=00005 name=E:\ORACLE11GR2\APP\USER\ORADATA\ORCL11GR2\

EXAMPLE01.DBF

input datafile file number=00003 name=E:\ORACLE11GR2\APP\USER\ORADATA\ORCL11GR2\

UNDOTBS01.DBF

input datafile file number=00004 name=E:\ORACLE11GR2\APP\USER\ORADATA\ORCL11GR2\

USERS01.DBF

channel ORA_DISK_1: starting piece 1 at 06-SEP-10

channel ORA_DISK_1: finished piece 1 at 06-SEP-10

piece handle=E:\ORACLE11GR2\APP\USER\FLASH_RECOVERY_AREA\ORCL11GR2\BACKUPSET\201

0_09_06\O1_MF_NNNDF_TEST1_689WVQRH_.BKP tag=TEST1 comment=NONE

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

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

including current SPFILE in backup set

channel ORA_DISK_1: starting piece 1 at 06-SEP-10

channel ORA_DISK_1: finished piece 1 at 06-SEP-10

piece handle=E:\ORACLE11GR2\APP\USER\FLASH_RECOVERY_AREA\ORCL11GR2\BACKUPSET\201

0_09_06\O1_MF_NCSNF_TEST1_689WZ226_.BKP tag=TEST1 comment=NONE

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

Finished backup at 06-SEP-10

Recovery Manager complete.

E:\oracle11gR2\app\User\product\11.2.0\dbhome_1>

We can invoke the e:\backup.bat passing the values at command line.

E:\oracle11gR2\app\User\product\11.2.0\dbhome_1>E:\backup.bat ‘TEST1’ 1 ‘databas

e’

E:\oracle11gR2\app\User\product\11.2.0\dbhome_1>set ORACLE_HOME=E:\oracle11gr2\a

pp\User\product\11.2.0\dbhome_1

E:\oracle11gR2\app\User\product\11.2.0\dbhome_1>echo ‘TEST1’

‘TEST1’

E:\oracle11gR2\app\User\product\11.2.0\dbhome_1>echo 1

E:\oracle11gR2\app\User\product\11.2.0\dbhome_1>echo ‘database’

‘database’

E:\oracle11gR2\app\User\product\11.2.0\dbhome_1>rman @’e:\backup.cmd’ using ‘TES

T1’ 1 ‘database’

Recovery Manager: Release 11.2.0.1.0 – Production on Mon Sep 6 17:22:55 2010

RMAN> CONNECT TARGET *

2> BACKUP TAG TEST1 COPIES 1 database;

3> EXIT;

connected to target database: ORCL11GR (DBID=702742010)

Starting backup at 06-SEP-10

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=65 device type=DISK

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=E:\ORACLE11GR2\APP\USER\ORADATA\ORCL11GR2\

SYSTEM01.DBF

input datafile file number=00002 name=E:\ORACLE11GR2\APP\USER\ORADATA\ORCL11GR2\

SYSAUX01.DBF

input datafile file number=00005 name=E:\ORACLE11GR2\APP\USER\ORADATA\ORCL11GR2\

EXAMPLE01.DBF

input datafile file number=00003 name=E:\ORACLE11GR2\APP\USER\ORADATA\ORCL11GR2\

UNDOTBS01.DBF

input datafile file number=00004 name=E:\ORACLE11GR2\APP\USER\ORADATA\ORCL11GR2\

USERS01.DBF

channel ORA_DISK_1: starting piece 1 at 06-SEP-10

channel ORA_DISK_1: finished piece 1 at 06-SEP-10

piece handle=E:\ORACLE11GR2\APP\USER\FLASH_RECOVERY_AREA\ORCL11GR2\BACKUPSET\201

0_09_06\O1_MF_NNNDF_TEST1_689YB1N3_.BKP tag=TEST1 comment=NONE

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

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

including current SPFILE in backup set

channel ORA_DISK_1: starting piece 1 at 06-SEP-10

channel ORA_DISK_1: finished piece 1 at 06-SEP-10

piece handle=E:\ORACLE11GR2\APP\USER\FLASH_RECOVERY_AREA\ORCL11GR2\BACKUPSET\201

0_09_06\O1_MF_NCSNF_TEST1_689YFOYO_.BKP tag=TEST1 comment=NONE

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

Finished backup at 06-SEP-10

Recovery Manager complete.

E:\oracle11gR2\app\User\product\11.2.0\dbhome_1>

September 6, 2010 - Posted by gjilevski | oracle

No comments yet.

About

Guenadi Jilevski is a Senior Oracle Professional (OCP 7, 8, 8i, 9i, 10g, 11g, 12c OCE 10gR2 RAC, OCE Oracle RAC 11gR2 and Grid Infrastructure Administrator, Oracle Exadata 11g Certified Implementation Specialist,Oracle GoldenGate 10g/11g Certified Implementation Specialist and OCM 9i, OCM 11g) with more than 20 years of industrial experience in high end Oracle technology starting with Oracle RDBMS version 6 up to 11g. He has been involved with multi platform High Availability Solutions starting with OPS and Veritas and Sun cluster as well as complex multiple node RAC 9i, 10g and 11g implementations on AIX, Linux, Solaris and HP. He has experience in deploying disaster recovery solutions with standby databases compliant to Oracle MAA and third party solutions such as Shareplex and Goldengate. As a former employee of Oracle Corporation Advanced Technology Solutions Consulting Practice in Americas, Guenadi has been involved in multiple visible high end projects for RAC enabling EBS, Peoplesoft, Siebel ERP applications, end to end performance tuning, architectured High Availability and Disaster Recovery solutions to name a few. Presently he is working as A Senior Oracle Consultant in EMEA.

Guenadi Jilevski is Oracle OCP 7, 8, 8i, 9i, 10g, 11g, 12c OCE 10gR2 RAC, OCE Oracle RAC 11gR2 and Grid Infrastructure Administrator, Oracle Exadata 11g Certified Implementation Specialist,Oracle GoldenGate 10g/11g Certified Implementation Specialist and Oracle OCM9i , OCM 11g, Teradata Certified DBA, Informix Certified DBA.

The views expressed on this blog are my own and do not necessarily reflect the views of Current/Past/Future Employers. The views and opinions expressed by visitors on this blog are theirs solely and may not reflect mine.

Archives
Archives
Blog Stats
- 1,168,193 hits
Top Rated
Blogroll
- WordPress.com 0
- WordPress.org 0
Calendar

September 2010

M T W T F S S

1 2 3 4 5

6 7 8 9 10 11 12

13 14 15 16 17 18 19

20 21 22 23 24 25 26

27 28 29 30

« Aug Oct »
Search

Search for:
Email Subscription

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 1,112 other followers

Email Address:
OraNA

Guenadi N Jilevski's Oracle BLOG

Oracle RAC, DG, EBS, DR and HA DBA BLOG