Oracle9i Recovery
Manager (RMAN)

Recovery manager is a platform
independent utility for coordinating your backup and restoration procedures
across multiple servers. In my opinion it's value is limited if you only have
on or two instances, but it comes into it's own where large numbers of
instances on multiple platforms are used. The reporting features alone mean
that you should never find yourself in a position where your data is in danger
due to failed backups.

The functionality of RMAN is too diverse to be covered in this article so I
shall focus on the basic backup and recovery functionality.

Create Recovery Catalog

First create a user to hold the
recovery catalog:

CONNECT sys/password@w2k1 AS SYSDBA -- Create tablepsace to hold repositoryCREATE TABLESPACE "RMAN" DATAFILE 'C:\ORACLE\ORADATA\W2K1\RMAN01.DBF' SIZE 6208K REUSEAUTOEXTEND ON NEXT 64K MAXSIZE 32767MEXTENT MANAGEMENT LOCALSEGMENT SPACE MANAGEMENT AUTO; -- Create rman schema ownerCREATE USER rman IDENTIFIED BY rmanTEMPORARY TABLESPACE temp DEFAULT TABLESPACE rman QUOTA UNLIMITED ON rman; GRANT connect, resource, recovery_catalog_owner TO rman;

Then create the recovery catalog:

C:>rman catalog=rman/rman@w2k1 Recovery Manager: Release 9.2.0.1.0 - Production Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved. connected to recovery catalog databaserecovery catalog is not installed RMAN> create catalog tablespace "RMAN"; recovery catalog created RMAN> exit Recovery Manager complete. C:>

Register Database

Each database to be backed up by
RMAN must be registered:

C:>rman catalog=rman/rman@w2k1 target=sys/password@w2k2 Recovery Manager: Release 9.2.0.1.0 - Production Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved. connected to target database: W2K2 (DBID=1371963417)connected to recovery catalog database RMAN> register database; database registered in recovery catalogstarting full resync of recovery catalogfull resync complete RMAN>

Existing user-created backups can
be added to the catalog using:

RMAN> catalog datafilecopy 'C:\Oracle\Oradata\TSH1.dbf';RMAN> catalog archivelog 'log1', 'log2', 'log3', ... 'logN';

Full Backup

First we configure several
persistant parameters for this instance:

RMAN> configure retention policy to recovery window of 7 days;RMAN> configure default device type to disk;RMAN> configure controlfile autobackup on;RMAN> configure channel device type disk format 'C:\Oracle\Admin\W2K2\Backup%d_DB_%u_%s_%p';

Next we perform a complete
database backup using a single command:

RMAN> run {2> backup database plus archivelog;3> delete noprompt obsolete;4> }

The recovery catalog should be
resyncronized on a regular basis so that changes to the database structure and
presence of new archive logs is recorded. Some commands perform partial and
full resyncs implicitly, but if you are in doubt you can perform a full resync
using the follwoing command:

RMAN> resync catalog;

Restore & Recover
The Whole Database

If the controlfiles and online
redo logs are still present a whole database recovery can be achieved by
running the following script:

run { shutdown immediate; # use abort if this fails startup mount; restore database; recover database; alter database open;}

This will result in all datafiles
being restored then recovered. RMAN will apply archive logs as necessary until
the recovery is complete. At that point the database is opened. If the
tempfiles are still present you can issue a command like like the following for
each of them:

sql "ALTER TABLESPACE temp ADD TEMPFILE ''C:\Oracle\oradata\W2K2\temp01.dbf'' REUSE";

If the tempfiles are missing they
must be recreated as follows:

sql "ALTER TABLESPACE temp ADD TEMPFILE ''C:\Oracle\oradata\W2K2\temp01.dbf'' SIZE 100M AUTOEXTEND ON NEXT 64K";

Restore & Recover A
Subset Of The Database

A subset of the database can be
restored in a similar fashion:

run { sql 'ALTER TABLESPACE users OFFLINE IMMEDIATE'; restore tablespace users; recover tablespace users; sql 'ALTER TABLESPACE users ONLINE'; }

Incomplete Recovery

As you would expect, RMAN allows
incomplete recovery to a specified time, SCN or sequence number:

run { shutdown immediate; startup mount; set until time 'Nov 15 2000 09:00:00'; # set until scn 1000; # alternatively, you can specify SCN # set until sequence 9923; # alternatively, you can specify log sequence number restore database; recover database; alter database open resetlogs;}

The
incomplete recovery requires the database to be opened using the RESETLOGS option.

Disaster Recovery

In a disaster situation where all
files are lost you can only recover to the last SCN in the archived redo logs.
Beyond this point the recovery would have to make reference to the online redo
logs which are not present. Disaster recovery is therefore a type of incomplete
recovery. To perform disaster recovery connect to RMAN:

C:>rman catalog=rman/rman@w2k1 target=sys/password@w2k2

Once in RMAN do the following:

startup nomount;restore controlfile;alter database mount;

From SQL*Plus as SYS get the last
archived SCN using:

SQL> SELECT archivelog_change#-1 FROM v$database; ARCHIVELOG_CHANGE#-1-------------------- 1048438 1 row selected. SQL>

Back in RMAN do the following:

run { set until scn 1048438; restore database; recover database; alter database open resetlogs;}

If the "until scn" were
not set the following type of error would be produced once a redo log was
referenced:

RMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of recover command at 03/18/2003 09:33:19RMAN-06045: media recovery requesting unknown log: thread 1 scn 1048439

With the database open all
missing tempfiles must be replaced:

sql "ALTER TABLESPACE temp ADD TEMPFILE ''C:\Oracle\oradata\W2K2\temp01.dbf'' SIZE 100M AUTOEXTEND ON NEXT 64K";

Once the database is fully
recovered a new backup should be perfomed.

The recovered database will be registered in the catalog as a new incarnation.
The current incarnation can be listed and altered using the following commands:

list incarnation;reset database to incarnation x;

Lists And Reports

RMAN has extensive listing and
reporting functionality allowing you to monitor you backups and maintain the
recovery catalog. Here are a few useful commands:

# Show all backup detailslist backup; # Show items that beed 7 days worth of# archivelogs to recover completelyreport need backup days = 7 database; # Show/Delete items not needed for recoveryreport obsolete;delete obsolete; # Show/Delete items not needed for point-in-time# recovery within the last weekreport obsolete recovery window of 7 days; delete obsolete recovery window of 7 days; # Show/Delete items with more than 2 newer copies availablereport obsolete redundancy = 2 device type disk;delete obsolete redundancy = 2 device type disk; # Show datafiles that connot currently be recoveredreport unrecoverable database;report unrecoverable tablespace 'USERS';

It's worth spending some time looking
at all the reporting capabilities whilst deciding whether you should switch
from shell scripting to RMAN. It might just influence your decision.

For more information see:

Ajit's space

Monday, August 31, 2009

Root file system read write mount

Sunday, August 16, 2009

10.1.3 - going back to manual editing of files

Friday, August 14, 2009