SkillBuilders | Oracle 10g: RMAN | Lesson 9: RMAN Scripting and Reporting

Instructor's Notes

(If applicable)

Lesson 9 – Scripting and Reporting with Oracle RMAN
Author: Dave Anderson, SkillBuilders
www.skillbuilders.com
Last Update: October 17, 2007

This lesson will cover the following topics:

Scripting Commands

CREATE SCRIPT, REPLACE SCRIPT, RUN, etc
Storing scripts in the catalog

Reports

REPORT Command
LIST Command
Querying the catalog

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

This lesson will teach you how to write RMAN scripts and create RMAN reports. It covers RMAN commands such as REPLACE SCRIPT, RUN, LIST and REPORT.

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

Scripts are a sequence of RMAN commands. Any command that can be enclosed in a RUN block is a valid script command – even calling another script. Scripts are stored in the recovery catalog. This provides a centrally-located repository of tested (hopefully) backup and recovery routines.
Script names are case sensitive.
Another example would be to build a script to perform a full database recovery

replace script full_recover {
allocate channel d1 type disk;
restore database;
recover database;
}

Oracle provides sample scripts in $ORACLE_HOME/rdbms/demo. Look for files with a “.rcv” extension.

Other Text:
(Examples or comments displayed on slide, if any).

REPLACE SCRIPT level_0_backup { allocate channel d1 type disk format 'c:\oracle\backups\weekly_level_0.%d.$T'; backup incremental level 0 TAG 'Weekly Level 0' database; } run { execute script level_0_backup;}

Instructor's Notes

(If applicable)

The following commands are related to RMAN scripting:

CREATE – Create a script. Command fails with RMAN-20401 if script already exists. You must be connected to the Recovery Catalog and Target databases. The Oracle manual says connection to a target database is not required, but this is not true.
REPLACE – Replace an existing script. Will create the script if the script does not exist.
RUN – Execute an existing script.
DELETE SCRIPT script_name – Delete the script from the catalog. Be careful, no warning is given.
PRINT SCRIPT script_name – Display the script on the screen or print to a log file. To print to the log file, start RMAN with the LOG=filename option and run the PRINT SCRIPT command.

Script names cannot contain spaces, hyphens or reserved words unless you enclose the name in double-quotes:

CREATE SCRIPT “Weekly Backup” …

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

The query shown above produces a list of all scripts stored in the Recovery catalog. To see the content of your scripts:

SELECT TEXT
FROM RC_DATABASE_INCARNATION i, RC_STORED_SCRIPT_LINE s
WHERE i.DB_KEY = 1 -- variable, adjust to correct key
AND SCRIPT_NAME = 'level_0_backup'
-- adjust script_name to correct script name
AND i.DB_KEY = s.DB_KEY
AND i.CURRENT_INCARNATION = 'YES';

See the supplied script RMAN_SCRIPTS.SQL for a working copy of this query.

Other Text:
(Examples or comments displayed on slide, if any).

SQL> connect rman/rman@recovery Connected. SQL> SELECT * FROM RC_STORED_SCRIPT; DB_KEY DB_NAME SCRIPT_NAME ---------- -------- ---------------------- 1 PROD level_0_backup

Instructor's Notes

(If applicable)

There are several different methods of accessing backup and recovery metadata.

The REPORT command provides reports on backups; what have been taken and what need to be taken.
The LIST command reports on the status of backup sets and image copies.
The SHOW command displays the current persistent settings controlled by the CONFIGURE command.
If you have employed the use of a Recovery Catalog, you can also logon to the database that contains the recovery catalog and issue SELECTs against the views in the recovery catalog.

Examples can be found on the following pages.

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

The LIST command queries the recovery catalog (or target control file if the catalog is not available) to produce information about backup sets (also image copies and proxy copies).
Listing all backup sets can be a lengthy report. It may be better to write this report to a log file as shown:

rman target sys/change_on_install@prod catalog rman/rman@workshop log=c:\rman\rman.log
list backup;
Exit

New incarnations of a database are created when the ALTER DATABASE OPEN RESETLOGS command is used – typically when a point-in-time recovery has been done. The LIST INCARNATION command shows the incarnations available. RMAN provides a procedure for recovering to an older incarnation of the database – if a recovery catalog is available. Refer to the Oracle Backup and Recovery Users Guide for the procedure.
LIST INCARNATION also shows the database id (DBID) of the database. This is needed if you are recovering a control file without the aid of the RMAN catalog. It is a good idea to record all the DBID’s of your databases in a safe place.

Other Text:
(Examples or comments displayed on slide, if any).

list backup [of database] [completed {after | before | between date} ]; list backup [of database] summary; list incarnation; See next page for example

Instructor's Notes

(If applicable)

This example illustrates the use of the COMPLETED option to limit the search for backupsets created within a specified time period.

Other Text:
(Examples or comments displayed on slide, if any).

RMAN> list backup completed after 'sysdate-365'; List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 1 Full 120K DISK 00:00:05 05-MAY-04 BP Key: 1 Status: AVAILABLE Tag: TAG20040505T171926 Piece Name: C:\ORACLE\ORA92\DATABASE\01FL14SU_1_1 List of Datafiles in backup set 1 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 4 Full 1453101 05-MAY-04 C:\ORACLE\ORADATA\CLASS2\EXAMPL

Instructor's Notes

(If applicable)

“Expired” backups (i.e. backupsets) are backupsets where the actual OS files are missing, perhaps because they were deleted with an OS utility. The CROSSCHECK command scans disk and tape devices to determine which backupset pieces are missing, & marks them as “expired” in recovery catalog.
CROSSCHECK BACKUP checks the existence of backupsets, pieces, controlfile autobackups.
CROSSCHECK COPY checks the existence of image copies, control file copies (not autobackups) and copies of archive logs.

RMAN> crosscheck backupset;
using channel ORA_DISK_1
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=C:\ORACLE\BACKUPS\PROD.FULL.7.1 recid=3 stamp=460818584
RMAN> list expired backupset summary;

Other Text:
(Examples or comments displayed on slide, if any).

RMAN> crosscheck backupset completed after 'sysdate-30'; RMAN> list expired backup summary; RMAN> crosscheck copy completed after 'sysdate-30'; RMAN> list expired copy; RMAN> list backup of controlfile; Learn more about “expired” in the Maintenance lesson

Instructor's Notes

(If applicable)

The REPORT command provides reports on backups; what backups have been taken and what need to be taken.
The report command senses when the schema has changed and will issue a RESYNC CATALOG command if necessary. For example, if you add a tablespace and issue a REPORT SCHEMA command, the first action is “starting full resync of recovery catalog”. The report schema command looks like this:
RMAN> report schema;
starting full resync of recovery catalog
full resync complete
Report of database schema

Other Text:
(Examples or comments displayed on slide, if any).

report need backup; report need backup redundancy 2; report need backup days 1; report need backup incremental 1; report obsolete; report schema;

Instructor's Notes

(If applicable)

If you setup your environment as this course suggests, you have a user called RMAN on the database that holds the recovery catalog. This user owns a series of views which make up the recovery catalog. Issue “SELECT view_name FROM user_views;” to see all view names. These views contain information on all the target databases that you have registered in the catalog. The REPORT and LIST commands are easy ways to query these views from within RMAN. However, you can also query these view with the SELECT statement.
The example above queries the view RC_DATABASE. RC_DATABASE contains one row for every target database registered in this catalog. The RC_DATABASE_INCARNATION view contains one row for every incarnation of every database:

SELECT db_key, dbid, dbinc_key, name, resetlogs_time
FROM rc_database_incarnation

Notes Continue…

Other Text:
(Examples or comments displayed on slide, if any).

SQL> select db_key, dbinc_key, dbid, name 2 from rc_database; DB_KEY DBINC_KEY DBID NAME ---------- ---------- ---------- -------- 1 264 4167529595 PROD

Instructor's Notes

(If applicable)

And remember from the “Scripting” section of this chapter, we can list all stored scripts:

To find all views owned by user RMAN, login to RMAN on the recovery catalog database and execute this query:

select view_name from user_views;

To find the exact start-time of an RMAN backup, use the TO_CHAR function on the START_TIME Column of the RC_BACKUP_SET view.

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

The SHOW command displays the configured persistent settings. i.e. The settings set with the CONFIGURE command.
SHOW ALL will show all settings. Individual settings can be displayed by name as shown above.

Other Text:
(Examples or comments displayed on slide, if any).

RMAN> show default device type; RMAN configuration parameters are: CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default RMAN> show retention policy; RMAN configuration parameters are: CONFIGURE RETENTION POLICY TO REDUNDANCY 2;

Instructor's Notes

(If applicable)

Workshop: Scripting and Reporting
1. Create a script to perform a Level 0 Incremental backup of the TOOLS tablespace. Store this script in the recovery catalog.
2. Execute the script created in step 1.
3. Use SQL*Plus to create and execute a query to list all scripts stored in the recovery catalog.
4. From SQL*Plus, use the following query to create a useful RMAN command file: select 'print script '|| script_name || ';' from rc_stored_script;Spool the output to a file called PRINT.RMAN.
  
  Then start RMAN with the “CMDFILE=print.rman” option. Review RMAN Commands lesson if necessary.

Other Text:
(Examples or comments displayed on slide, if any).