Guenadi N Jilevski's Oracle BLOG

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

Substitution Variables in Oracle 11g RMAN scripts

 

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

  1. 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

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

RMAN> CONNECT TARGET *

2> BACKUP TAG

Enter value for 1: ‘FULL’

‘FULL’ COPIES

Enter value for 2: 1

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>

  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

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

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.

  1. 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

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

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>

  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

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

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

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 | oracle

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: