Recovery Catalog

Configure the Recovery Catalog:

Lets assume that 'MARS' is the Catalog Database and the recovery Database is 'EARTH'


1. First create a user to hold the recovery catalog:

CONNECT sys/sys123 @ mars AS SYSDBA

2. Create tablepsace to hold repository :


CREATE TABLESPACE "RMAN"
DATAFILE 'C:\ORCL\RMAN\RMAN01.DBF' SIZE 6208K REUSE
AUTOEXTEND ON NEXT 64K MAXSIZE 32767M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

3. Create rman schema owner


CREATE USER rman IDENTIFIED BY rman123
TEMPORARY TABLESPACE temp
DEFAULT TABLESPACE rman
QUOTA UNLIMITED ON rman;

GRANT connect, resource, recovery_catalog_owner TO rman;


4. Then create the recovery catalog:

C:>rman catalog = rman/rman123 @ mars

Recovery Manager: Release 9.2.0.1.0 - Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

connected to recovery catalog database
recovery catalog is not installed

RMAN> create catalog tablespace "RMAN";

recovery catalog created

RMAN> exit

Recovery Manager complete.

C:>
5.Register Database


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

C:>rman catalog=rman/rman@ mars target=sys/password@ earth

Recovery Manager: Release 9.2.0.1.0 - Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

connected to target database:earth (DBID=1371963417)
connected to recovery catalog database

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN>

RMAN

Recovery Manager is Oracle’s utility to manage the backup, and more importantly the recovery, of the database. It eliminates operational complexity while providing superior performance and availability of the database. Recovery Manager debuted with Oracle8 to provide DBAs an integrated backup and recovery solution. Recovery Manager determines the most efficient method of executing the requested backup, restore, or recovery operation and then executes these operations in concert with the Oracle database server. Recovery Manager and the server automatically identify modifications to the structure of the database and dynamically adjust the required operation to adapt to the changes.

RMAN Configuration commands :::

RMAN> connect target sys/justchill

connected to target database: MARS (DBID=617151761)
using target database controlfile instead of recovery catalog

RMAN> CONFIGURE DEFAULT DEVICE TYPE TO DISK;

new RMAN configuration parameters:
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
new RMAN configuration parameters are successfully stored

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

RMAN> CONFIGURE BACKUP OPTIMIZATION ON;

new RMAN configuration parameters:
CONFIGURE BACKUP OPTIMIZATION ON;
new RMAN configuration parameters are successfully stored

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'c:\orcl\rman\ora_cf%F'
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'c:\orcl\rman\ora_df%t_s%s_s%p';
RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'c:\orcl\rman\snp123.ora' ;

RMAN> show all;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'c:\orcl\rman\ora_cf%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'c:\orcl\rman\ora_df%t_s%s_s%p';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'C:\ORCL\RMAN\SNP123.ORA';


RMAN BACKUP Commands :------------

RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE; ////Incremental Backups
RMAN> BACKUP DATABASE PLUS ARCHIVELOG; ////Backup the Database
RMAN> BACKUP VALIDATE DATABASE ARCHIVELOG ALL; /// Testing Backups
RMAN> report schema; ///show all schemas
RMAN> BACKUP TABLESPACE EXAMPLE; /// backup particular tablespace
RMAN> BACKUP DATAFILE 5; /// backup particular datafile
RMAN> BACKUP DATABASE NOT BACKED UP SINCE TIME 'SYSDATE-1'; /// upto time

Example:
RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE; ////Incremental Backups Starting backup at 08-FEB-08 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=15 devtype=DISK channel ORA_DISK_1: starting incremental level 0 datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset input datafile fno=00001 name=C:\ORCL\P123\VITALDATA\SYSTEM01.DBF input datafile fno=00002 name=C:\ORCL\P123\VITALDATA\UNDOTBS01.DBF input datafile fno=00004 name=C:\ORCL\P123\VITALDATA\EXAMPLE01.DBF input datafile fno=00009 name=C:\ORCL\P123\VITALDATA\XDB01.DBF input datafile fno=00005 name=C:\ORCL\P123\VITALDATA\INDX01.DBF input datafile fno=00008 name=C:\ORCL\P123\VITALDATA\USERS01.DBF input datafile fno=00003 name=C:\ORCL\P123\VITALDATA\DRSYS01.DBF input datafile fno=00006 name=C:\ORCL\P123\VITALDATA\ODM01.DBF input datafile fno=00007 name=C:\ORCL\P123\VITALDATA\TOOLS01.DBF input datafile fno=00010 name=C:\ORCL\P123\VITALDATA\DATA.DBF input datafile fno=00011 name=C:\ORCL\P123\VITALDATA\TS_STH.DBF channel ORA_DISK_1: starting piece 1 at 08-FEB-08 channel ORA_DISK_1: finished piece 1 at 08-FEB-08 piece handle=C:\ORCL\RMAN\ORA_DF646157625_S2_S1 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55 Finished backup at 08-FEB-08 Starting Control File and SPFILE Autobackup at 08-FEB-08 piece handle=C:\ORCL\RMAN\ORA_CFC-617151761-20080208-00 comment=NONE Finished Control File and SPFILE Autobackup at 08-FEB-08

RMAN Administrative Commands:-------

RMAN> REPORT NEED BACKUP; ///files need to be backed up based on the configured retention policy

RMAN> REPORT OBSOLETE; /// to view what backups are obsolete and delete them.

RMAN> DELETE OBSOLETE; //// to view what backups are obsolete and delete them.

RMAN> BACKUP VALIDATE DATABASE ARCHIVELOG ALL; //to check for corruption or verify that database files are in the correct location

RMAN> REPORT UNRECOVERABLE; //report any files that have not been backed up since the last nolog operation

RMAN> DELETE ARCHIVELOG UNTIL TIME 'SYSDATE-1'; //run this command to delete the archive logs that are older then yesterday

RMAN> CROSSCHECK BACKUP OF DATABASE;

RMAN> LIST BACKUP OF DATAFILE 4; /// view the backups for datafile 4

RMAN> LIST BACKUP OF CONTROLFILE; /// view the backups for control file

RMAN Complete Recovery :------
When you perform complete recovery, you recover the backups to the current SCN. You can either recover the whole database at once or recover individual tablespaces or datafiles. Because you do not have to open the database with the RESETLOGS option after complete recovery as you do after incomplete recovery, you have the option of recovering some datafiles at one time and the remaining datafiles later

1.Recovering Datafiles :::
If u know the file corrupted then use the command below..
for example - DATAFILE 5 C:\orcl\p123\vitaldata\EXAMPLE01.BDF corrupted

RMAN> CONNECT TARGET SYS/justchill
RMAN> RESTORE DATAFILE 5;
RMAN> RECOVER DATAFILE 5;
RMAN> SQL 'alter tablespace example online';

2. Recovering Database :::
The database must be in MOUNT mode to restore and recover with RMAN.

RMAN> CONNECT TARGET SYS/justchill
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE;
RMAN> ALTER DATABASE OPEN;

3. Recovering Tablespaces :::

RMAN> CONNECT TARGET SYS/justchill
RMAN> RESTORE TABLESPACE EXAMPLE;
RMAN> RECOVER TABLESPACE EXAMPLE;
RMAN> SQL 'ALTER TABLESPACE EXAMPLE ONLINE';


RMAN InComplete Recovery :------
Incomplete recovery uses a backup to produce a noncurrent version of the database. In other words, you do not apply all of the redo records generated after the most recent backup. You usually perform incomplete recovery of the whole database in the following situations:

1.Media failure destroys some or all of the online redo logs.

2. A user error causes data loss, for example, a user inadvertently drops a table.

3.You cannot perform complete recovery because an archived redo log is missing.

4.You lose your current control file and must use a backup control file to open the database.

To perform incomplete media recovery, you must restore all datafiles from backups created prior to the time to which you want to recover and then open the database with the RESETLOGS option when recovery completes. The RESETLOGS operation creates a new incarnation of the database. in other words, a database with a new stream of log sequence numbers starting with log sequence 1.

The following commands are issued within RMAN, using the database id(DBID) returned from
the query. You will receive an error message at the end of recover due to the loss of the
online redo logs

RMAN> SET DBID 1003121815;
RMAN> CONNECT TARGET SYS/ORACLE@ORCL.WORLD;
RMAN> STARTUP NOMOUNT;
RMAN> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO
'C:\ORCl\RMAN\ora_cf%F';
RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;
RMAN> STARTUP MOUNT;
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE;


Since the online logs were lost, complete recovery is not possible. Open the database
with resetlogs to continue.

RMAN> ALTER DATABASE OPEN RESETLOGS;


To monitor the RMAN @monitorjob type this in SQL promt.

How to check a physical corruption?

There are various methods to check / understand the physical corruption:
(a) To check the corruption we shall run DBV on the datafiles, which will explain the corruption.
DBV utility will scan through the files at OS level. While reading the blocks it checks for any fractured blocks , checksum failure & other checking failures. It will report corrupted blocks and also classifies them in what type of object were they (table/index/etc).
LIMITATIONS WITH DBV:
o DBV cannot understand any table / index rowcount mismatch.
o DBV cannot understand any complex corruptions, especially corruptions below cache layer of a datablock.
o Some blocks that may not be part of Oracle, they would have been dropped. But DBV will still report that block as corrupted. When you check with the query against dba_extents (given below) there wont be any rows returned. And this corrupt block will not affect normal database operations as Oracle is not going to read this block. But while performing RMAN backups we still report this block as corrupted.
o Logical corruptions with undo / redo records cannot be identified.
o Some types of corruptions that cannot be explored while we do an OS level read.

(b) RMAN to validate the database files.
The following command will validate all the datafiles in the database.

RMAN> BACKUP VALIDATE DATABASE;

- If you want to validate a particular datafile you shall use the following command, this will vaildate the file# 1
RMAN> BACKUP VALIDATE DATAFILE 1;

LIMITATIONS WITH RMAN:
o All the limitations that apply to DBV are applicable to RMAN VALIDATE also.

(c) Export Utility / SELECT:
To check the corruption, we shall export the affected segment / schema / tablespace / full.
If we doubt a particular table, we shall either perform a full table scan (or) CTAS.
If there is any corruptions with the exporting object, export will fail with corruption error.
In case there no space in filesystem to perform an export, we shall do it to /dev/null.
LIMITATIONS WITH EXPORT:
o It fails immediately after encountering the first corruption., so complete picture will be missed-out.
o export performs full table scan, so we will not be checking the indexes. Any corruption in index (or) table/index rowcount/rowid mismatch will not be checked by export.
o A full database export reads data dictionary, but it doesn't mean that there is no corruption with data dictionary when this export completes successfully.

(d) ANALYZE command:
ANALYZE has been referred as the best utility that performs maximum checks.
This is the easy utility to check the corruptions associated with index on any means.

To check the corruption with a table do the following:
ANALYZE TABLE VALIDATE STRUCTURE;

To check the corruption with an index do the following:
ANALYZE INDEX VALIDATE STRUCTURE;

To check the corruption with the table and its index(s) to perform the cross reference checkings do the following:
ANALYZE TABLE VALIDATE STRUCTURE CASCADE;

For partition tables, we need to use ANALYZE command with INTO INVALID_ROWS option, similar to the following:
ANALYZE TABLE VALIDATE STRUCTURE CASCADE INTO INVALID_ROWS;
ANALYZE PARTITON TABLE VALIDATE STRUCTURE CASCADE

LIMITATIONS WITH ANALYZE:
o It is not a limitation, rather the common problem. It would be difficult to perform an analyze with huge segments as it needs to scan all the blocks with that segment. We shall use ONLINE option there. But still the performance problem may not be acceptable for busy segments.
o Again this fails when it encounters the first corruption, it will not proceed after that.