Friday, 17 August 2012

Creating and Removing the Catalog

 

Creating the catalog:


RMAN can be used either with or without a recovery catalog. A recovery catalog is a schema stored in a database that tracks backups and stores scripts for use in RMAN backup and recovery situations. The RMAN schema generally only requires 15 megabyte per year per database backed up.


The recovery catalog can be define in two ways. They are
1.       Base Recovery Catalog:- is a database schema that contains RMAN metadata for a set of target databases.
2.       Virtual Private Catalog:- is a set of synonyms and views that enable user access to a subset of a base recovery catalog.

Syntax:

CREATE CATALOG;
CREATE VIRTUAL CALTALOG;


Prerequisites:
1.      Execute this command only at the RMAN prompt.
2.      RMAN must be connected to the recovery catalog database and the catalog database must be open.
example:
$rman catalog rmancat/rmancat
RMAN> CONNECT CALTALOG rmancat/rmnacat@ravi11
3.      A connection to a target database is not required.
4.      The recovery catalog owner, whether the catalog is a base recovery catalog or a virtual private catalog, must be granted the RECOVERY_CATALOG_OWNER role. This user must also be granted space privileges in the tablespace where the recovery catalog tables will reside. The recovery catalog is created in the default tablespace of the recovery catalog owner.
5.      If you are creating a virtual private catalog, then the base recovery catalog owner must have used the RMAN GRANT command to grant either the CATALOG or REGISTER privilege.
example:
RMAN> CONNECT CATALOG catowner@catdb
recovery catalog database Password: password
connected to recovery catalog database
RMAN> GRANT CATALOG FOR DATABASE prod1 TO vpc1;
RMAN> EXIT;
    For virtual
RMAN> CONNECT CATALOG vpc1@catdb
recovery catalog database Password: password
connected to recovery catalog database
RMAN> CREATE VIRTUAL CATALOG;
RMAN> EXIT;
Note: if you grant the permissions to the virtual Recovery catalog must be executed the following PL/SQL procedure.

base_catalog_owner.DBMS_RCVCAT.CREATE_VIRTUAL_CATALOG


SQL> CONNECT vpc1@catdb
Enter password: password
Connected.
SQL> BEGIN
  2  catowner.DBMS_RCVCAT.CREATE_VIRTUAL_CATALOG;
  3  END;
  4  /




Steps to creating Catalog:-



Step1: To create a tablespace to hold RMAN data. connect with SYSDBA privileges to the database containing the recovery catalog

 $ sqlplus "sys/change_on_install as sysdba"
 SQL> select name,ts# from v$tempfile;

             NAME                                        TS#
/prod/pra10/oradata/temp01.dbf          3

SQL> select name from v$datafile;

          NAME
/prod/pra10/oradata/system01.dbf
/prod/pra10/oradata/undtbs01.dbf
/prod/pra10/oradata/sysaux01.dbf
/prod/pra10/oradata/userdata01.dbf

SQL> CREATE TABLESPACE rman_data DATAFILE '/prod/pra10/admin/rman_data01.dbf'
  2  SIZE 100M
  3  AUTOEXTEND ON NEXT 100M
  4  MAXSIZE 2G
  5  SEGMENT SPACE MANAGEMENT AUTO;

Tablespace created.

Step2: create user for RMAN catalog.

 SQL> CREATE USER rmancat IDENTIFIED BY rmancat
  2  DEFAULT TABLESPACE rman_data
  3  QUOTA UNLIMITED ON rman_data;

User created.

SQL> ALTER USER rmancat TEMPORARY TABLESPACE temp;

User altered.

(or)

SQL> CREATE USER rmancat IDENTIFIED BY rmancat
  2  DEFAULT TABLESPACE rman_data
  3  QUOTA UNLIMITED ON rman_data
  4  TEMPORARY TABLESPACE TEMP;

User created.

Step3: Grant the recovery_catalog_owner role to the user. This role provides all of the privileges required to maintain and query the recovery catalog.

SQL> GRANT connect,resource TO rmancat;

Grant succeeded.

SQL> GRANT recovery_catalog_owner TO rmancat;

Grant succeeded.

Step4: Once the owner user is created, the RMAN recovery catalog schema can be added. Connect to the RMAN using created catalog user as follows.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production with the Partitioning, OLAP and Data Mining options

[oracle@srvr77 pra10]$ rman catalog rmancat/rmancat

Recovery Manager: Release 10.2.0.1.0 – Production on Fri Aug 17 00:16:13 2012 Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to recovery catalog database

RMAN> create catalog;

recovery catalog created

RMAN> exit

Recovery Manager complete.
Step5: Each database that the catalog will track must be registered.
Registering a Database with RMAN
The following process can be used to register a database with RMAN:
·        Make sure the recovery catalog database is open.
·        Connect RMAN to both the target database and recovery catalog database. 


[oracle@srvr77 pra10]$ rman catalog rmancat/rmancat target /

Recovery Manager: Release 10.2.0.1.0 – Production on Fri Aug 17 00:16:13 2012 Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target Database: PRA10(2822383112)
connected to recovery catalog database

RMAN> REGISTER DATABASE;

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



*****************************************************
Note: In this temporary file is must assign to the catalog tablespace
All of the mechanisms for virtual private catalogs are in the recovery catalog schema itself. The security is provided by the catalog database, not by the RMAN client.
***************************************************************************

[oracle@srvr77 pra10]$ rman catalog rmancat/rmancat target /@ravi11

Recovery Manager: Release 10.2.0.1.0 - Production on Fri Aug 17 00:22:06 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04005: error from target database:
ORA-12154: TNS: could not resolve the connect identifier specified



Removing Catalog:-

Syntax:-
SQL> execute dbms_rcvcat.unregisterdatabase(db_key, db_id)
To unregister a database, do the following:
NOTE: If the target database does not exists anymore, the only steps to execute are (1) and (3). Because the backupsets cannot be deleted from the catalog (requires to connect to the target database) they are not be deleted from disk or tape either. So you have to remove these backupsets manually. A list of the related backupsets are:
SQL> select handle from rc_backup_piece where db_id = <see step (1)>;


  1. Identify the database that you want to unregister. Run the following query from the recovery catalog using Server Manager or SQL*Plus (connected as the RMAN user):
    SQL> select * from rc_database;

    DB_KEY  DBINC_KEY       DBID NAME     RESETLOGS_CHANGE# RESETLOGS
---------- ---------- ---------- -------- ----------------- ---------
         1          2 2498101982 TARGDB                   1 15-JAN-04
       105        106 2457750772 OIDDB                    1 14-DEC-03
       128        129 2351019032 OMSDB                    1 15-JAN-04
       301        302 2498937635 TARGDB              140831 25-JAN-04
    For this example, I want to unregister all databases with this catalog.
  2. Remove the backupsets that belong to the database that you want to unregister.
    • Find the backupsets of the database that you want to unregister.
      RMAN> list backupset of database;
    • Remove the backupsets that belongs only to the database you want to unregister.
      RMAN> allocate channel for delete type disk;
RMAN> change backupset XXX delete;
      NOTE: You need to allocate a channel for the delete. In this example a disk drive is being used and not a tape. The procedure for a backup done to tape is the same except you have to allocate a different channel for tape. Example:
      RMAN> allocate channel for delete type 'sbt_tape';
      The XXX value is the 'list of key' value from the 'list backupset of database' command
  3. Unregister the database by executing the following procedure from the recovery catalog:
    SQL> execute dbms_rcvcat.unregisterdatabase(db_key, db_id)
    The "db_key" and "db_id" values you will get by running the following query from the recovery catalog:
    SQL> select * from rc_database;
    DB_KEY  DBINC_KEY       DBID NAME     RESETLOGS_CHANGE# RESETLOGS
---------- ---------- ---------- -------- ----------------- ---------
         1          2 2498101982 TARGDB                   1 15-JAN-04
       105        106 2457750772 OIDDB                    1 14-DEC-03
       128        129 2351019032 OMSDB                    1 15-JAN-04
       301        302 2498937635 TARGDB              140831 25-JAN-04
    Make sure you are using the correct values by looking at the 'NAME' column of the "rc_database" table. Here is an example of how to unregister all databases within this catalog:
    SQL> execute dbms_rcvcat.unregisterdatabase(1, 2498101982)

PL/SQL procedure successfully completed.

SQL> execute dbms_rcvcat.unregisterdatabase(105, 2457750772)

PL/SQL procedure successfully completed.

SQL> execute dbms_rcvcat.unregisterdatabase(128, 2351019032)

PL/SQL procedure successfully completed.

SQL> execute dbms_rcvcat.unregisterdatabase(301, 2498937635)

PL/SQL procedure successfully completed.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)