How to create a Recovery Catalog in Oracle

-

How to create a Recovery Catalog in Oracle

What is a Recovery Catalog?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

A recovery catalog is a database schema used by RMAN to store metadata about one or more Oracle databases. 

Typically, you store the catalog in a dedicated database. A recovery catalog provides the following benefits:

1. A recovery catalog creates redundancy for the RMAN repository stored in the control file of each target database. The recovery catalog serves as a secondary metadata repository. If the target control file and all backups are lost, then the RMAN metadata still exists in the recovery catalog.

2. A recovery catalog centralizes metadata for all your target databases. Storing the metadata in a single place makes reporting and administration tasks easier to perform.

3. A recovery catalog can store metadata history much longer than the control file. This capability is useful if you must do a recovery that goes further back in time than the historyin the control file. The added complexity of managing a recovery catalog database can be offset by the convenience of having the extended backup history available.

Some RMAN features function only when you use a recovery catalog. For example, you can store RMAN scripts in a recovery catalog. The chief advantage of a stored script is that it is available to any RMAN client that can connect to the target database and recovery catalog. Command files are only available if the RMAN client has access to the file system on which they are stored.

A recovery catalog is required when you use RMAN in a Data Guard environment. By storing backup metadata for all primary and standby databases, the catalog enables you to offload backup tasks to one standby database while enabling you to restore backups on other databases in the environment.


Steps that are needed to create a Recovery Catalog.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

To create a Recovery Catalog, Perform the below mentioned steps:

STEP I
~~~~~~~~

You have to decide where you are going to store the Recovery Catalog data, that means you have to decide and create (if required) the database that will be used for Recovery Catalog purpose.

Database Name: reccatdb

Please ensure that Recovery Catalog is separate from the target database(s) for which it will store RMAN repository like it should (I will say must) have separate disk space etc. from the target database(s).
It is good to have the Recovery Catalog database in ARCHIVELOG mode.


STEP II
~~~~~~~~

Now create a user/schema in Recovery Catalog database that will hold the tables of Recovery Catalog. Also create a separate tablespace for the recovery catalog schema. 

1. Start SQL*Plus and connect with administrator privileges to the database containing the recovery catalog. In this example, the database is reccatdb.

2.  Create a user and schema for the recovery catalog. For example, you could enter the following SQL statement:

(Note: The below command is an example only, Please supply your own values at required places.)

SQL>CREATE USER rman_user IDENTIFIED BY rman_pass#345
TEMPORARY TABLESPACE temp
DEFAULT TABLESPACE rman_tab
QUOTA UNLIMITED ON rman_tab;

3.  Grant the RECOVERY_CATALOG_OWNER role to the schema owner. This role provides the user with all privileges required to maintain and query the recovery catalog.

SQL>GRANT RECOVERY_CATALOG_OWNER TO rman_user;


STEP III
~~~~~~~~

After creating the catalog owner, create the catalog tables with the RMAN CREATE CATALOG command. The command creates the catalog in the default tablespace of the catalog owner.

To create the recovery catalog:

1.  Start RMAN and connect to the database that will contain the catalog. Connect to the database as the recovery catalog owner.

$ rman
RMAN> connect CATALOG rman_user@reccatdb

It will ask for rman_user password. Provide the password that you give to the user while creating it.

2.  Run the CREATE CATALOG command to create the catalog. The creation of the catalog can take several minutes. If the catalog tablespace is the user's default tablespace, then you can run the following command:

RMAN> CREATE CATALOG;

You can specify the tablespace name for the catalog in the CREATE CATALOG command. For example:
RMAN> CREATE CATALOG TABLESPACE cat_tbs;

Note:
If the tablespace name that you want to use for the recovery catalog is an RMAN reserved word, then it must be uppercase and enclosed in quotes. For example: 

RMAN>CREATE CATALOG TABLESPACE 'CATALOG';

3.  You can check the results by using SQL*Plus to query the recovery catalog to see which tables were created:

SQL> SELECT TABLE_NAME FROM USER_TABLES;

You have to connect to database as rman_user [Recovery Catalog Owner].

Comments

Post a Comment

Back To Top

Popular posts from this blog

error 18 at 0 depth lookup: self signed certificate

-
I was trying to test SSL connection between MySQL client and server. For that I created SSL certificate and keys by following the MySQL documentation at: Creating SSL Certificates and Keys Using openssl After finishing up all the commands when I verify the certificates by using: openssl verify -CAfile ca.pem server-cert.pem client-cert.pem I got the following output: error 18 at 0 depth lookup : self signed certificate error server-cert.pem: verification failed client-cert.pem: OK  Reason : The Common Name value used for the server and client certificates/keys must each differ from the Common Name value used for the CA certificate. Otherwise, the certificate and key files will not work for servers compiled using OpenSSL. Solution : When OpenSSL prompts you for the Common Name for each certificate, use different names. Common Name (e.g. server FQDN or YOUR name) []: I used values as follows: CA Cert:  ssl-ca-cert Server Cert: ssl-ca-server-cert Client Cert:  ssl-c
Read more

How to check fragmentation in MySQL tables

-
MySQL tables, including MyISAM and InnoDB, two of the most common types, experience fragmentation as data is inserted, updated and deleted randomly. Fragmentation can leave large holes in your table, blocks which must be read when scanning the table. Optimizing your table can therefore make full table scans and range scans more efficient. We will mention how to check fragmentation in MySQL tables using SQL queries. USE < put_your_dbname_here > SELECT TABLE_NAME, CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 ), 2), 'M') TOTAL_SIZE, CONCAT(ROUND(( DATA_FREE ) / ( 1024 * 1024 ), 2), 'M') DATA_FREE FROM information_schema.TABLES where table_schema = database() and ROUND(( DATA_FREE ) / ( 1024 * 1024 ), 2) > 0.00 ORDER BY DATA_FREE DESC; SELECT TABLE_NAME, ROUND(((Data_length - (TABLE_ROWS * Avg_row_length))/Data_length) * 100, 2) FRAG_Percent FROM information_schema.TABLES where table_schema = database() and TABLE_ROWS > 0
Read more

How to Drop or Remove or Decommission a Database in Oracle

-
How to Drop or Remove or Decommission a Database in Oracle Database Decommission steps for Oracle 10G and 11g databases Note: ~~~~~~~~ Every organization has its own documented way of removing or decommissioning the active/running database that is supporting an Enterprise’s Application. Here I am presenting some general steps which may coincide with your organization’s standard operation procedure (SOP). 1)   Please get the required approval from Business/Customer to proceed for database decommissioning. 2)   Send the Notification to Business/Customer that we are going to decommission the database. (if required) 3)   Raise a Request for Storage and Backup team to take the required backup (provide all the details in the raised request) and to keep the Backup for a specified period of time, say for next 2 years, (That is called the backup retention period). Retention Period may be varied based on your business needs and SLA. The backup can be on disk or
Read more