Wednesday, May 7, 2008

Working with RMAN stored scripts in Recovery Catalog

Within RMAN you can save commands and execute it whenever you wish. Stored scripts bring this facility where we should not bother about OS scripts whether RMAN client has proper permission on it or not.

Stored Scripts can be two types.

1)Global Stored Scripts:A global stored script can be run against any database registered in the recovery catalog, if the RMAN client is connected to the recovery catalog and a target database.

2)Local Stored Scripts:A local stored script is associated with the target database to which RMAN is connected when the script is created, and can only be executed when you are connected to that target database.

How to Created Stored Script:
---------------------------------

To create local stored script.
CREATE SCRIPT query_backup
{
SHOW ALL;
REPORT NEED BACKUP;
REPORT OBSOLETE;
}

To create global stored with a comment added to it,

CREATE GLOBAL SCRIPT global_query_backup
COMMENT 'This is a sample global script which returns some query'
{
SHOW ALL;
REPORT NEED BACKUP;
REPORT OBSOLETE;
}


Alternatively you can create script from a text file. To create local script from text file in '/oradata2' just use,

CREATE SCRIPT test_script FROM FILE '/oradata2/my_script_file.txt';


How to run Stored Scripts:
------------------------------

To run stored script use,
RUN{
EXECUTE SCRIPT query_backup;
}

It will first search for local stored script with name query_backup. If not found then will check global stored script with the name. If you have same name in both local and global script then to run global script explicitly use,

RUN{
EXECUTE GLOBAL SCRIPT global_query_backup;
}


Displaying a Stored Script:
----------------------------------
PRINT SCRIPT query_backup;
PRINT GLOBAL SCRIPT global_query_backup;


RMAN> PRINT GLOBAL SCRIPT global_query_backup;

printing stored global script: global_query_backup
{SHOW ALL;
REPORT NEED BACKUP;
REPORT OBSOLETE;
}

Listing Stored Scripts:
-----------------------------

To view both global and local, for the currently connected target database use,

LIST SCRIPT NAMES;

To view only global script names use,

LIST GLOBAL SCRIPT NAMES;

To view the names of all scripts stored in the current recovery catalog, including global scripts and local scripts for all target databases registered in the recovery catalog, use,

LIST ALL SCRIPT NAMES;


Remember that to run LIST SCRIPT NAMES RMAN must be connected to target database.

To run LIST GLOBAL SCRIPT NAMES and LIST ALL SCRIPT NAMES RMAN need not to be connected to target database.

Example:
---------------

-bash-3.00$ rman CATALOG catalog_user/catalog_pwd@saturn:1521/ARJU.SATURN.ARJUBD.COM

Recovery Manager: Release 10.2.0.1.0 - Production on Thu May 8 01:14:36 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to recovery catalog database

RMAN> LIST GLOBAL SCRIPT NAMES;
List of Stored Scripts in Recovery Catalog
Global Scripts
Script Name
Description
-----------------------------------------------------------------------
global_query_backup
This is a sample global script which returns some query

Updating Stored Scripts:
-------------------------

To update a script use,
REPLACE [GLOBAL]SCRIPT
{
BACKUP DATABASE PLUS ARCHIVELOG;
}

Here like,
REPLACE SCRIPT query_backup
{
BACKUP DATABASE PLUS ARCHIVELOG;
}

If the script does not already exist, then RMAN creates it.

Deleting Stored Scripts
--------------------------------
DELETE SCRIPT query_bakcup;
DELETE GLOBAL SCRIPT global_query_backup;


If you use DELETE SCRIPT without GLOBAL, and there is no stored script for the target database with the specified name, RMAN will look for a global stored script by the specified name and delete the global script if it exists.

Related Documents:
-----------------------

How to Create Recovery Catalog and Use it

6 comments:

Anonymous said...

Hi, thanks for this useful post. I am trying to store some scripts in RMAN catalog in 8i and keep getting syntax error messages. Is there difference between the way one scripts on the RMAN command line and the one in a script? For example below script seems fine to me and if I execute it one by one from RMAN no problems, but when I try to put it into the replace script { } or create script { } syntax I get errors. Any idea?

Sinan

Anonymous said...

Sorry, forgot post the script:

create script MNTPART2
{
allocate channel ch1 type disk;
crosscheck backup;
delete expired backupset;
release channel;
allocate channel ch1 type disk;
change archivelog all crosscheck;
release channel;
resync catalog;
}

Arju said...

Your script has syntax error. The correct one will be,

RMAN> create script MNTPART
{
allocate channel ch1 device type disk;
crosscheck backup;
delete expired backupset;
release channel ch1;
allocate channel ch1 type disk;
change archivelog all crosscheck;
release channel ch1;
resync catalog;
}2> 3> 4> 5> 6> 7> 8> 9> 10> 11>

created script MNTPART

Anonymous said...

Hi Arju,
I am using 9i DB.
I created a script by ur steps but
RMAN> CREATE SCRIPT query_backup
2> {
3> SHOW ALL;
4> REPORT NEED BACKUP;
5> REPORT OBSOLETE;
6> }

created script query_backup

but when i try do LIST it it giving me error

RMAN> LIST SCRIPT;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01005: syntax error: found "script": expecting one of: "archivelog, backup,
backuppiece, backupset, backed, completed, copy, controlfilecopy, datafilecopy,
device, expired, incarnation, like, proxy, recoverable, tag"
RMAN-01007: at line 1 column 6 file: standard input

RMAN> LIST ALL SCRIPT NAMES;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01005: syntax error: found "script": expecting one of: "archivelog, backup,
backuppiece, backupset, backed, completed, copy, controlfilecopy, datafilecopy,
device, expired, incarnation, like, proxy, recoverable, tag"
RMAN-01007: at line 1 column 6 file: standard input

Anonymous said...

My RMAN version is 8.1.7 on Windows 2000 server. The script you provided does not work for me. Any ideas what the difference would be? I am using a catalog database.

Thanks,
Sinan

Arju said...

You must have recovery catalog to create script. store it , rename it , delete it.