Saturday, June 14, 2008

How to Set Up a Recovery Catalog

How to Set Up a Recovery Catalog

Create the database to be used as the recovery catalog. Alternatively, identify an already
existing database and use that. Because a single recovery catalog can serve more than
one database, you may choose to create a single recovery catalog that services all your
databases that are being backed up.


1. Create table space RMAN catalog if it’s not there

SQL>
1 create tablespace rman_cat
2 datafile 'D:\oracle\product\10.2.0\oradata\pom\rman.DBF'
3 size 10m autoextend on next 10m maxsize 2000m
4* extent management local autoallocate segment space management auto
SQL> /

Tablespace created.

2. In the recovery catalog database, create the user that will own the recovery catalog data.
For example, assume that catdb is the name of the catalog database, and rman_cat is
the name of a tablespace you have created where the catalog data is to be stored.

SQL>
1 create user rman identified by cat
2 temporary tablespace temp
3 default tablespace rman_cat
4* quota unlimited on rman_cat
SQL> /

User created.

2. Grant the RECOVERY_CATALOG_OWNER role to the catalog owner. This role provides
the user with all privileges required to maintain and query the recovery catalog.
SQL> GRANT RECOVERY_CATALOG_OWNER TO RMAN;

Grant succeeded.

4. Start RMAN and log in as the catalog owner.


d:\opis2005\ADMIN\Scripts\COM ( POM )RMAN CATALOG RMAN/CAT

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Jun 10 14:56:43 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to recovery catalog database


RMAN> CREATE CATALOG TABLESPACE RMAN_CAT;

recovery catalog created

RMAN> EXIT


Recovery Manager complete.

5. Connect to the target database by using RMAN, and register it in the newly created
recovery catalog.

d:\opis2005\ADMIN\Scripts\COM ( POM )RMAN TARGET / CATALOG RMAN/CAT

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Jun 10 14:58:52 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: POM (DBID=1104548899)
connected to recovery catalog database


RMAN> REGISTER DATABASE;

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

How to see what is committed in table (in past)

Here is the query to see the result of 2 days before. Interval might be day, hour, minute, second
etc.

SELECT * FROM Table1
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '2' day)
WHERE site_code = 'ITACS';