RMAN Hands On

Summary :

This hands on practice is organized into 4 chapters

• CATALOG
• ADMINISTRATION
• BACKUP
• RESTORE AND RECOVERY

RMAN Check List

This check list provides a quick overview of items that are important to consider when working with RMAN

• Define a backup retention policy
• Setup CONTROLFILE AUTOBACKUP ON
• Setup ARCHIVELOG DELETION POLICY to a number of ‘backed up’ times that satisfy your recovery needs
• Generate logs of backup jobs and monitor them for errors
• Execute periodically administrative scripts to clean up the backup repository from obsolete and expired backups
• Use a Flash Recovery Area to maintain an online staging area of your last backups. That will enable fast restore and recovery in case of need
• Use a change tracking file and incremental backups that satisfy your recovery needs.
• Prepare and test a complete set of recovery scenarios and train the DBA team on them, they should include at least the following scenarios:

- System Tablespace loss

- Online Redolog loss

- Controlfile loss

- Data Tablespace loss

- Single/multiple datafile loss

- Archived log sequences loss- Block corruptions recovery

- Total loss (database)

- Total loss (server = database/software/parameter files)

The Hands-On Environment

· 11g RDBMS software

· 11g ASM storage configured with 2 disk groups:

§ DATADG, for the databases, and

§ FRADG, for the flash recovery area, for storing some of the backups and

   for archived log destination.

· 1 catalog database

· 1 test database

· 1 file system for storing other backups outside ASM

Two general setup scripts are used to set the environment, they are run from within most other scripts.

#!/usr/bin/tcsh

# set-environment

setenv ORACLE_HOME /oradisk/oracle/app/product/11.1.0/db_1

setenv PATH $ORACLE_HOME/bin:/usr/local/bin:/usr/bin:/bin

setenv rmanuser rman # the rman catalog owner

setenv rmanpwd cat # the rman catalog owner password

setenv dbauser sys # the dba user

setenv dbapwd oracle # the dba user password

setenv rmandb rmancat # the rman catalog database

setenv datadb redpanda # the test database

clear

echo

# eof script

-- sqlenv.sql

set echo off head off feed off

alter session set nls_date_format='dd/mm/yy hh24:mi:ss';

set pages 50000 lines 120 echo on head on veri on time on ti on

-- eof script

CATALOG

This chapter show how to create the Rman catalog, how to register a database with it and how to review some of the information contained in the catalog.

The Catalog chapter covers the following topics:

1-set-catalog

2-create-catalog

3-register-database

4-verify-registration

5-manual-backup-registration

6-create-scripts

7-export-catalog

8-query-catalog

9-get-catalog-version

10-upgrade-catalog

11-unregister-database

12-drop-catalog

ADMINISTRATION

This chapter show how execute the most common administrative tasks within rman. They are important in order to maintain a catalog that reflects the real backups we have.

Maintaining a clean updated catalog will help to simplify restore operations.

The Administration chapter cover the following topics:

1-crosscheck

2-list

3-report

4-delete

BACKUP

Rman provides numerous and flexible ways to backup a database or parts of a database, in this chapter a few of the several options are shown.

The following scripts are part of this chapter:

0-make-io

1-change_tracking

2-backup-backupset-compressed

3-backup-as-copy

4-incremental-backup-and-tags

5-backup-plus-archived-logs

6-backup-controlfile

7-incremental-for-backup-recover

8-incremental-for-quick-recover

RECOVERY

Restore and recovery are the critical ends of the Backup and Recovery policies, in this chapter we simulate critical failures and we execute the restore and recovery using the backups implemented on the previous chapter.

The following scenarios are implemented:

1-system-tablespace-loss //////1b-recover-system-tablespace

2-user-datafile-loss ///// ///////2b-recover-users-tablespace

3-online-redo-loss //////////////3b-recover-from-redo-loss

4-controlfile-loss ////////////////4b-recover-from-controlfile-loss

5-database-loss /////////////////5b-recover-from-total-loss

CATALOG

The Catalog chapter covers the following topics:

1-set-catalog

2-create-catalog

3-register-database

4-verify-registration

5-manual-backup-registration

6-create-scripts

7-export-catalog

8-query-catalog

9-get-catalog-version

10-upgrade-catalog

11-unregister-database

12-drop-catalog

1-set-catalog                                                          

This script create the rman user that will be the owner of the rman catalog, on the catalog database.

The catalog database is usually a small database it contains and maintains the metadata of all rman backups performed using the catalog.

#!/usr/bin/tcsh

source ./set-environment

sqlplus $dbauser/$dbapwd@$rmandb as sysdba<<eof

@sqlenv

set echo on

CREATE USER rman IDENTIFIED BY cat

TEMPORARY TABLESPACE temp

DEFAULT TABLESPACE users

QUOTA UNLIMITED ON users;

GRANT RECOVERY_CATALOG_OWNER TO rman;

exit;

eof

exit

2-create-catalog

Using the rman user created on step 1, this script connects to the catalog database using the rman command line interface, and creates the rman catalog.

The rman catalog is a set of database objects that will be used to store the rman backups metadata.

#!/usr/bin/tcsh

source ./set-environment

rman CATALOG $rmanuser/$rmanpwd@$rmandb <<eof

CREATE CATALOG;

eof

exit

3-register-database

Once the rman catalog is set, the databases to be backed up need to be registered with it.

In this case we execute rman connecting to the database to be registered, the TARGET and to the CATALOG database.

#!/usr/bin/tcsh

source ./set-environment

echo executing command : REGISTER DATABASE

echo

rman TARGET $dbauser/$dbapwd@$datadb CATALOG $rmanuser/$rmanpwd@$rmandb <<eof

REGISTER DATABASE;

eof

exit

4-verify-registration

Once a databases is registered with rman, we can get information about it from the catalog.

In this case we execute the report schema command to get information.

#!/usr/bin/tcsh

source ./set-environment

echo Executing Command : REPORT SCHEMA

echo

rman TARGET $dbauser/$dbapwd@$datadb CATALOG $rmanuser/$rmanpwd@$rmandb <<eof

REPORT SCHEMA;

eof

exit

5-manual-backup-registration

Manual backups, made using Rman without a recovery catalog or user scripts, can be cataloged and made available to the rman catalog.

In this example a controlfile backup is made using rman in NOCATALOG mode, afterwards the backup is catalogued.

#!/usr/bin/tcsh

source ./set-environment

echo Executing command : backup current controlfile

echo

rman TARGET $dbauser/$dbapwd@$datadb NOCATALOG<<eof

backup current controlfile;

list backup;

exit;

eof

echo

echo Type file name to catalog

set fname = $<

echo

echo Executing Command : CATALOG backuppiece '<filename>'

echo

rman TARGET $dbauser/$dbapwd@$datadb CATALOG $rmanuser/$rmanpwd@$rmandb <<eof

CATALOG backuppiece '$fname';

eof

exit

6-create-scripts

Rman tasks can be run using scripts, that can be stored on the rman catalog.

Global scripts will be available to all databases, non-global scripts will be available only to the target database.

This example shows how to create a global script.

#!/usr/bin/tcsh

source ./set-environment

echo Executing Command : CREATE GLOBAL SCRIPT

echo

rman TARGET $dbauser/$dbapwd@$datadb CATALOG $rmanuser/$rmanpwd@$rmandb <<eof

CREATE GLOBAL SCRIPT global_full_backup COMMENT 'use only with ARCHIVELOG mode databases'

{ BACKUP DATABASE PLUS ARCHIVELOG;

DELETE NOPROMPT OBSOLETE;

}

LIST GLOBAL SCRIPT NAMES;

eof

exit

7-export-catalog

The database holding the rman catalog should be protected against information loss, the minimum level of protection can be implemented by executing a full export of the catalog schema.

In case of catalog loss an import of the schema on a new or existing database will make the catalog available again.

#!/usr/bin/tcsh

source ./set-environment

echo Executing Command : exp $rmanuser/$rmanpwd@$rmandb file=rman-catalog-export.dmp

echo

exp $rmanuser/$rmanpwd@$rmandb file=rman-catalog-export.dmp owner=rman feedback=100

exit

8-query-catalog

In some specific situations it may be useful to query directly the recovery catalog. to do so we need to know the Database ID (dbid) and the DB Key of the database we want to query data for on the recovery catalog.

#!/usr/bin/tcsh

source ./set-environment

clear

echo

echo Querying Catalog Views for the Target DB_KEY or DBID Values

echo DB_KEY is used only in the recovery catalog.

echo You can determine the DBID by looking at the output displayed when RMAN          

        connects to the database

echo or by querying a V\$DATABASE view as in the following:

echo

echo select dbid from v\$database

echo

set v_db=v\$database

sqlplus -s $dbauser/$dbapwd@$datadb as sysdba<<eof

@sqlenv

SELECT DBID FROM $v_db;

exit

eof

echo

echo You can then obtain the DB_KEY on the Catalog Database based on the DBID

echo Enter the DBID

set v_dbid = $<

echo

echo select db_key from rc_database where dbid=$v_dbid;

sqlplus -s $rmanuser/$rmanpwd@$rmandb <<eof

@sqlenv

SELECT DB_KEY FROM RC_DATABASE WHERE DBID = $v_dbid;

eof

echo

echo Having the DB_KEY we can execute other queries on the catalog database

echo

echo Enter the DB_KEY

set v_dbkey = $<

echo

echo To obtain the backups per incarnation of a database you can execute the

        following script:

echo

echo SELECT DBINC_KEY,BS_KEY, BACKUP_TYPE, COMPLETION_TIME

echo FROM RC_DATABASE_INCARNATION i, RC_BACKUP_SET b

echo WHERE i.DB_KEY = $v_dbkey

echo AND i.DB_KEY = b.DB_KEY ;

sqlplus -s $rmanuser/$rmanpwd@$rmandb <<eof

@sqlenv

SELECT DBINC_KEY,BS_KEY, BACKUP_TYPE, COMPLETION_TIME

FROM RC_DATABASE_INCARNATION i, RC_BACKUP_SET b

WHERE i.DB_KEY = $v_dbkey

AND i.DB_KEY = b.DB_KEY ;

eof

echo

echo For querying RC_BACKUP_FILES you must call DBMS_RCVMAN.SETDATABASE ,

        with the DBID of a database

echo registered in the catalog, the fourth parameter must be DBID.

echo The other parameters must all be NULL.

echo

echo "CALL DBMS_RCVMAN.SETDATABASE(null,null,null,$v_dbid,null);"

echo

sqlplus -s $rmanuser/$rmanpwd@$rmandb <<eof

@sqlenv

CALL DBMS_RCVMAN.SETDATABASE(null,null,null,$v_dbid,null);

select BACKUP_TYPE,STATUS,TAG,BYTES,COMPLETION_TIME

from RC_BACKUP_FILES;

eof

9-get-catalog-version

This example show how to get the catalog version on the rman catalog database

#!/usr/bin/tcsh

source ./set-environment

clear

echo

echo "SELECT * FROM rcver;"

echo

sqlplus -s $rmanuser/$rmanpwd@$rmandb <<eof

@sqlenv

SELECT * FROM rcver;

eof

echo

exit

10-upgrade-catalog

This example show how to upgrade the catalog to the last version, the upgrade command needs to be entered twice (to confirm catalog upgrade) to get it executed.

#!/usr/bin/tcsh

source ./set-environment

rman TARGET $dbauser/$dbapwd@$datadb CATALOG $rmanuser/$rmanpwd@$rmandb <<eof

UPGRADE CATALOG;

UPGRADE CATALOG;

Eof

11-unregister-database

If a database is not longer required on the rman catalog it can be unregistered.

This command needs to be executed with the 'noprompt' option if run from within a script, otherwise it assumes NO as response to the confirmation request.

Note that rman metadata is always stored also on the controlfile, if a database is unregistered from the catalog, it can be afterwards registered again and all backups contained in the controlfile will be cataloged

#!/usr/bin/tcsh

source ./set-environment

rman TARGET $dbauser/$dbapwd@$datadb CATALOG $rmanuser/$rmanpwd@$rmandb <<eof

UNREGISTER DATABASE;

Eof

12-drop-catalog

An rman catalog can be removed with the drop catalog command. If executed from a script the command needs to be entered twice to get it run.

#!/usr/bin/tcsh

source ./set-environment

echo Executing Command : DROP CATALOG

echo

rman CATALOG $rmanuser/$rmanpwd@$rmandb <<eof

DROP CATALOG;

Eof

ADMINISTRATION

The Administration chapter cover the following topics:

1-crosscheck

2-list

3-report

4-delete

1-crosscheck

Crosscheck check that cataloged backups exist on disk or tape;

 if they don't exist they are marked as expired;

 if they exist but are no longer required to satisfy the redundancy policy, it mark the backups as obsolete.

Obsolete and expired backups can be removed from disk and the catalog later with the delete obsolete/expired commands.

#!/usr/bin/tcsh

source ./set-environment

echo Executing Command : CROSSCHECK

echo

rman TARGET $dbauser/$dbapwd@$datadb CATALOG $rmanuser/$rmanpwd@$rmandb <<eof

CROSSCHECK BACKUP; # checks backup sets, proxy copies, and image copies

CROSSCHECK COPY OF DATABASE;

CROSSCHECK BACKUPSET;

CROSSCHECK ARCHIVELOG ALL;

eof

exit

2-list

List produces a report of existing backups, different kind of oracle files can be listed separately with the list command.

#!/usr/bin/tcsh

source ./set-environment

echo Executing Command : LIST

rman TARGET $dbauser/$dbapwd@$datadb CATALOG $rmanuser/$rmanpwd@$rmandb log=scrlog

<<eof

LIST ARCHIVELOG ALL;

LIST BACKUPSET;

LIST EXPIRED BACKUPSET;

LIST FAILURE;

LIST RECOVERABLE BACKUPSET;

eof

more scrlog

rm scrlog

exit

3-report

Report produces a concise list of existing backups, including the full path to the backup files.

#!/usr/bin/tcsh

source ./set-environment

echo Executing Command : REPORT

echo

rman TARGET $dbauser/$dbapwd@$datadb CATALOG $rmanuser/$rmanpwd@$rmandb log=scrlog <<eof

REPORT SCHEMA;

REPORT OBSOLETE;

REPORT NEED BACKUP;

REPORT UNRECOVERABLE;

REPORT SCHEMA AT TIME 'SYSDATE-1';

eof

more scrlog

rm scrlog

exit

4-delete

Delete remove obsolete backups from disk; obsolete backups are not required to satisfy the retention policy.

It does remove expired backups from the catalog also; expired backups exist on the catalog but were removed from disk.

#!/usr/bin/tcsh

source ./set-environment

echo Executing Command : DELETE

rman TARGET $dbauser/$dbapwd@$datadb CATALOG $rmanuser/$rmanpwd@$rmandb log=scrlog

<<eof

DELETE NOPROMPT OBSOLETE;

DELETE NOPROMPT EXPIRED BACKUPSET;

eof

more scrlog

rm scrlog

exit