Note: This document is prepared to demonstrate how to create the catalog database using Oracle 11g,Oracle 10g ,Oracle 9i on AIX, Linux OS (and every different flavors of Linux OS) .Also demonstrating how to register the target databases to catalog databases and along with the testing of incremental level 0,level 1 and archive backups.
Server prms01 --- 173.55.199.240 -primary
Server srms01--- 172.58.1.48 -secondary
Brief about Catalog Database:
The RMAN Recovery Catalog is a database schema that holds the metadata detailing the RMAN backup operations performed on the target database. The metadata include the following information from about the target database: database structure, RMAN configuration, data file and archive log backups (backup sets, pieces and copies), archived redo logs and their copies. In addition to the metadata the recovery catalog can also store scripts that can be used by all registered target databases.
Initial Requirement:
The database that houses the recovery catalog to be in ARCHIVELOG mode.
For our setup to demostrate I have chosen DEBASHIS db server to house the catalog database.
Catalog Database on: DEBASHIS
Step by Step RMAN Recovery Catalog Creation:
1. First create a table space to house the recovery catalog schema.
2. Create the recovery catalog owner/schema and provide the necessary privileges to connect to catalog db and hold backup information.
3. Update the tnsnames.ora files both in the target database and catalog database so that both database can able to do tnsping or reachable each other without any failure.
4. Connect to the instance and create the RMAN catalog.
5. Register a database inside the RMAN catalog.
6. Important: resync the catalog database with target database time to time.
Additional Steps:
7. To unregister the database from catalog database.
Below are specifications we will use during CATALOG creation
Step1. First create a table space to house the recovery catalog schema.
login into server prms01(DEBASHIS) where we have to host the catalog DB.
We have to create a separate tablespace named as “RCATP_DATA” of size 40GB,let's say.
prms01> sqlplus as sysdba
SQL> set pages 9999 lines 300
SQL> col TABLESPACE_NAME for a20
SQL> col FILE_NAME for a40
SQL> create tablespace rcatp_data datafile ‘/debashis/data001/rcatp_data_001.dbf’ size 20000 M;
SQL> Alter tablespace rcatp_data add datafile ‘/debashis/data001/rcatp_data_002.dbf’ size 20000 M;
Also Create Temporary Tablespace TEMP of size 8GB
SQL> CREATE TABLESPACE RCATP_TEMP tempfile ‘/debashis/tmp001/temp_001.dbf’ size 8000M;
P.N--> If you are supporting multiple DB environments,then it is good to create separate catalog owner for each db version like below.
Step 2. Create the recovery catalog owner/schema and provide the necessary privileges to connect to catalog db and hold backup information.
We have to create 4 users on DEBASHIS DB server, separate for Oracle Database12C,Oracle Database11g,Oracle Database10g, Oracle Database9i, and Oracle Database8I, so that it is easy for us to manage the ids.
DEBASHIS server and create the respective versions catalog owner.
PRMS01> sqlplus "/ as sysdba"
SQL> create user RCAT8i identified by ***** default tablespace RCATP_DATA temporary tablespace RCATP_TEMP;
SQL> create user rcat9i identified by ***** default tablespace RCATP_DATA temporary tablespace RCATP_TEMP;
SQL>create user rcat10g identified by ***** default tablespace RCATP_DATA temporary tablespace RCATP_TEMP;
SQL>create user rcat11g identified by ***** default tablespace RCATP_DATA temporary tablespace RCATP_TEMP;
SQL> create user rcat12c identified by eDISON_01 default tablespace RCATP_DATA temporary tablespace RCATP_TEMP;
Whereas
RCAT8I : Catalog Schema owner for Oracle 8i databases
RCAT9I : Catalog Schema owner for Oracle 9i databases
RCAT10G : Catalog Schema owner for Oracle 10g database.
RCAT11G : Catalog Schema owner for Oracle 11g databases.
RCAT12C: Catalog Schema owner for Oracle 12c databases.
Steps2.1 Grant the RECOVERY_CATALOG_OWNER role to the above users.
grant recovery_catalog_owner to RCAT8i;
grant recovery_catalog_owner to rcat9i;
grant recovery_catalog_owner to rcat10g;
grant recovery_catalog_owner to rcat11g;
grant recovery_catalog_owner to rcat12c;
grant connect,resource to RCAT8I;
grant connect,resource to rcat9i;
grant connect,resource to rcat10g;
grant connect,resource to rcat11g;
grant connect,resource to rcat12c;
Here the role "RECOVERY_CATALOG_OWNER" provides the user with all privileges required to maintain and query the recovery catalog.
And RECOVERY_CATALOG_OWNER role contains below privileges which are mandatory to manage catalog database.
Use below command to check what privileges RECOVERY_CATALOG_OWNER role does have.
Select * from dba_sys_privs where grantee=’RECOVERY_CATALOG_OWNER';
Step3. Update the tnsnames.ora files both in the target database and catalog database so that both database can able to do tnsping or reachable each other without any failure.
Both server should be reachable to each other and for that we have to make the entry of the tnsnames of target database in catalog database housing db server and vice-versa.
Step 4. Connect to the instance and create the RMAN catalog.
Connect to the database that contains the catalog owner i.e. (DEBASHIS Database-hostname prms01).
Run the 'CREATE CATALOG' command to create the catalog
P.N ->
Command is rman target /catalog catalogowner@sid
Steps are
For 8i oracle@prms01:~> rman target / catalog RCAT8I/eDISON_01@debashis.jnv.com
RMAN> CREATE CATALOG;
For 9i oracle@prms01:~> rman target / catalog RCAT9I/eDISON_01@debashis.jnv.com
RMAN> CREATE CATALOG;
For 10g oracle@prms01:~> rman target / catalog RCAT10G/eDISON_01@debashis.jnv.com
RMAN> CREATE CATALOG;
For 11g oracle@prms01:~>rman target / catalog RCAT11G/eDISON_01@debashis.jnv.com
RMAN> CREATE CATALOG;
For OEM-12c database oracle@prms01:~>rman target / catalog rcat12c/eDISON_01@debashis.jnv.com
RMAN> CREATE CATALOG;
There might be chances that you will face below issues while creating catalog database.
ORA1950: no privileges on table name 'RCATP_DATA'
Solution:
SQL> alter user RCAT12C quota unlimited on RCATP_DATA;
SQL>select * from dba_ts_quotas where username=’RCAT12C';
Step 5. Register a database inside the RMAN catalog.
Connect to target database using respective catalog owner and register the database.
Registering the database.
EXAMPLE only
For 8i DEBASHIS8i:/apps/oracle> rman target / catalog RCAT8I/password@debashis.jnv.com
RMAN> REGISTER DATABASE;
For 9i Log in to All 9i DBs> rman target / catalog RCAT9I/password@debashis.jnv.com
RMAN> REGISTER DATABASE;
For 10g Login to all 10g dbs> rman target / catalog RCAT10G/password@debashis.jnv.com
RMAN> REGISTER DATABASE;
SAME to 11g and 12c as well.
Make sure that the registration was successful by running REPORT SCHEMA.
Step is valid for each database after registration to catalog database.
Example Connect to 11g DBS> rman target / catalog RCAT11G/password@debashis.jnv.com
RMAN> REPORT SCHEMA;
<<< It should get all the TS and Datafile asscoited with it of the target DB>>
To check what database has been registered in catalog database.
Logon as respective catalog owner to catalog database either via RMAN or SQLPLUS
Example for Oracle 10g.
Via RMAN -> go to catalog databaseà oracle@prms01:~> rman target / catalog RCAT10G/password@debashis.jnv.com
And execute below query:
RMAN> list db_unique_name all;
-> It should show all the 10g database registered to catalog database. à Step is same for 8i, 9i, 11g just have to connect through their respective catalog owner.
Via SQLPLUS->
go to catalog database
oracle@prms01:~>sqlplus RCAT8I/password@debashis.jnv.com
oracle@prms01:~>sqlplus RCAT9I/password@debashis.jnv.com
oracle@prms01:~>sqlplus RCAT10g/password@debashis.jnv.com
oracle@prms01:~>sqlplus RCAT11g/password@debashis.jnv.com
oracle@prms01:~>sqlplus RCAT12c/password@debashis.jnv.com
Command to execute is:
SQL> select * from rc_database;
Steps6.We have incorporated the resync catalog command daily, weekly and archive scripts in
debashis10g:/apps/oracle> grep -i -e 'rman target /' -e 'resync' /debashis10g/admin/scripts/backup_archive.ksh
rman target / catalog rcat10g/password@debashis.jnv.com <<EOF
resync catalog;
debashis10g:/apps/oracle> grep -i -e 'rman target /' -e 'resync'
/debashis10g/admin/scripts/backup_archive.ksh
rman target / catalog rcat10g/password@debashis.jnv.com <<EOF
resync catalog;
To check backup SYNC up in target database Vs Catalog database user below query
In Target Database use below query:
SET pages 9999 lines 300
col INSTANCE for a10
col STATUS for a25
col START_TIME for a20
col END_TIME for a20
select distinct a.SESSION_KEY,(SELECT instance_name FROM v$instance) instance,
--b.BACKUP_TYPE,
b.incremental_level,
a.STATUS,
to_char(a.START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(a.END_TIME,'mm/dd/yy hh24:mi') end_time,
a.elapsed_seconds/3600 hrs
from
V$RMAN_BACKUP_JOB_DETAILS a, v$backup_set_details b
where
a.session_stamp = b.session_stamp
--and trunc(end_time)>=trunc(sysdate-1)
and b.incremental_level is not null
order by a.session_key;
In Catalog Database use below query:
SET pages 9999 lines 300
col INSTANCE for a10
col STATUS for a25
col START_TIME for a20
col END_TIME for a20
select distinct a.SESSION_KEY,b.db_name,
--b.BACKUP_TYPE,
b.incremental_level,
a.STATUS,
to_char(a.START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(a.END_TIME,'mm/dd/yy hh24:mi') end_time,
a.elapsed_seconds/3600 hrs
from
RC_RMAN_BACKUP_JOB_DETAILS a, RC_backup_set_details b
where
a.session_stamp = b.session_stamp
--and trunc(end_time)>=trunc(sysdate-1)
and b.incremental_level is not null
order by a.session_key;
<< in both end the output should be same>>
For archive Log checking
All are just an example.
Check in target database using below query
SQL> select (select name from v$database)dbaname, MIN_FIRST_CHANGE#,MAX_NEXT_CHANGE# from v$backup_archivelog_summary;
DBANAME MIN_FIRST_CHANGE# MAX_NEXT_CHANGE#
--------- ----------------- ----------------
U092 326110113 328303646
From Catalog database
oracle@debashis10g:~> sqlplus debashis10g/password@debashis.jnv.com
SQL> select db_name,MIN_FIRST_CHANGE#,MAX_NEXT_CHANGE# from rc_backup_archivelog_summary;
DB_NAME MIN_FIRST_CHANGE# MAX_NEXT_CHANGE#
-------- ----------------- ----------------
U092 326110113 328303646
Remark:
To unregister a database from the recovery catalog, use UNREGISTER DATABASE;
Connect to target database with respective catalog owner
And use command
RMAN> UNREGISTER DATABASE;
UNREGISTER commands which help us Unregister a Oracle database from the recovery catalog.
RMAN> UNREGISTER DATABASE;
RMAN> UNREGISTER DATABASE NOPROMPT;
RMAN> UNREGISTER DATABASE prod1;
RMAN> UNREGISTER DATABASE prod2 NOPROMPT;
RMAN> UNREGISTER DB_UNIQUE_NAME prod2;
RMAN> UNREGISTER DB_UNIQUE_NAME prod1 NOPROMPT;
RMAN> UNREGISTER DB_UNIQUE_NAME prod2 INCLUDING BACKUPS;
RMAN> UNREGISTER DB_UNIQUE_NAME prod3 INCLUDING BACKUPS NOPROMPT;
Server prms01 --- 173.55.199.240 -primary
Server srms01--- 172.58.1.48 -secondary
Brief about Catalog Database:
The RMAN Recovery Catalog is a database schema that holds the metadata detailing the RMAN backup operations performed on the target database. The metadata include the following information from about the target database: database structure, RMAN configuration, data file and archive log backups (backup sets, pieces and copies), archived redo logs and their copies. In addition to the metadata the recovery catalog can also store scripts that can be used by all registered target databases.
Initial Requirement:
The database that houses the recovery catalog to be in ARCHIVELOG mode.
For our setup to demostrate I have chosen DEBASHIS db server to house the catalog database.
Catalog Database on: DEBASHIS
Step by Step RMAN Recovery Catalog Creation:
1. First create a table space to house the recovery catalog schema.
2. Create the recovery catalog owner/schema and provide the necessary privileges to connect to catalog db and hold backup information.
3. Update the tnsnames.ora files both in the target database and catalog database so that both database can able to do tnsping or reachable each other without any failure.
4. Connect to the instance and create the RMAN catalog.
5. Register a database inside the RMAN catalog.
6. Important: resync the catalog database with target database time to time.
Additional Steps:
7. To unregister the database from catalog database.
Below are specifications we will use during CATALOG creation
| Catalog Database Primary server Info | Catalog Database Secondary Server Info | Catalog Host DB Name | Tablespace Allocated | TEMP TS allocated | Catalog Owner | |
| prms01 | srms01 | DEBASHIS | RCATP_DATA | RCATP_TEMO | For 8i DB | RCAT8i |
| Ip- 173.55.199.240 | Ip-172.58.1.48 | For 9i DB | RCAT9i | |||
| For 10g DB | RCAT10g | |||||
| For 11g DB | RCAT11g | |||||
Step1. First create a table space to house the recovery catalog schema.
login into server prms01(DEBASHIS) where we have to host the catalog DB.
We have to create a separate tablespace named as “RCATP_DATA” of size 40GB,let's say.
prms01> sqlplus as sysdba
SQL> set pages 9999 lines 300
SQL> col TABLESPACE_NAME for a20
SQL> col FILE_NAME for a40
SQL> create tablespace rcatp_data datafile ‘/debashis/data001/rcatp_data_001.dbf’ size 20000 M;
SQL> Alter tablespace rcatp_data add datafile ‘/debashis/data001/rcatp_data_002.dbf’ size 20000 M;
Also Create Temporary Tablespace TEMP of size 8GB
SQL> CREATE TABLESPACE RCATP_TEMP tempfile ‘/debashis/tmp001/temp_001.dbf’ size 8000M;
P.N--> If you are supporting multiple DB environments,then it is good to create separate catalog owner for each db version like below.
Step 2. Create the recovery catalog owner/schema and provide the necessary privileges to connect to catalog db and hold backup information.
We have to create 4 users on DEBASHIS DB server, separate for Oracle Database12C,Oracle Database11g,Oracle Database10g, Oracle Database9i, and Oracle Database8I, so that it is easy for us to manage the ids.
DEBASHIS server and create the respective versions catalog owner.
PRMS01> sqlplus "/ as sysdba"
SQL> create user RCAT8i identified by ***** default tablespace RCATP_DATA temporary tablespace RCATP_TEMP;
SQL> create user rcat9i identified by ***** default tablespace RCATP_DATA temporary tablespace RCATP_TEMP;
SQL>create user rcat10g identified by ***** default tablespace RCATP_DATA temporary tablespace RCATP_TEMP;
SQL>create user rcat11g identified by ***** default tablespace RCATP_DATA temporary tablespace RCATP_TEMP;
SQL> create user rcat12c identified by eDISON_01 default tablespace RCATP_DATA temporary tablespace RCATP_TEMP;
Whereas
RCAT8I : Catalog Schema owner for Oracle 8i databases
RCAT9I : Catalog Schema owner for Oracle 9i databases
RCAT10G : Catalog Schema owner for Oracle 10g database.
RCAT11G : Catalog Schema owner for Oracle 11g databases.
RCAT12C: Catalog Schema owner for Oracle 12c databases.
Steps2.1 Grant the RECOVERY_CATALOG_OWNER role to the above users.
grant recovery_catalog_owner to RCAT8i;
grant recovery_catalog_owner to rcat9i;
grant recovery_catalog_owner to rcat10g;
grant recovery_catalog_owner to rcat11g;
grant recovery_catalog_owner to rcat12c;
grant connect,resource to RCAT8I;
grant connect,resource to rcat9i;
grant connect,resource to rcat10g;
grant connect,resource to rcat11g;
grant connect,resource to rcat12c;
Here the role "RECOVERY_CATALOG_OWNER" provides the user with all privileges required to maintain and query the recovery catalog.
And RECOVERY_CATALOG_OWNER role contains below privileges which are mandatory to manage catalog database.
Use below command to check what privileges RECOVERY_CATALOG_OWNER role does have.
Select * from dba_sys_privs where grantee=’RECOVERY_CATALOG_OWNER';
Step3. Update the tnsnames.ora files both in the target database and catalog database so that both database can able to do tnsping or reachable each other without any failure.
Both server should be reachable to each other and for that we have to make the entry of the tnsnames of target database in catalog database housing db server and vice-versa.
Step 4. Connect to the instance and create the RMAN catalog.
Connect to the database that contains the catalog owner i.e. (DEBASHIS Database-hostname prms01).
Run the 'CREATE CATALOG' command to create the catalog
P.N ->
Command is rman target /catalog catalogowner@sid
Steps are
For 8i oracle@prms01:~> rman target / catalog RCAT8I/eDISON_01@debashis.jnv.com
RMAN> CREATE CATALOG;
For 9i oracle@prms01:~> rman target / catalog RCAT9I/eDISON_01@debashis.jnv.com
RMAN> CREATE CATALOG;
For 10g oracle@prms01:~> rman target / catalog RCAT10G/eDISON_01@debashis.jnv.com
RMAN> CREATE CATALOG;
For 11g oracle@prms01:~>rman target / catalog RCAT11G/eDISON_01@debashis.jnv.com
RMAN> CREATE CATALOG;
For OEM-12c database oracle@prms01:~>rman target / catalog rcat12c/eDISON_01@debashis.jnv.com
RMAN> CREATE CATALOG;
There might be chances that you will face below issues while creating catalog database.
ORA1950: no privileges on table name 'RCATP_DATA'
Solution:
SQL> alter user RCAT12C quota unlimited on RCATP_DATA;
SQL>select * from dba_ts_quotas where username=’RCAT12C';
Step 5. Register a database inside the RMAN catalog.
Connect to target database using respective catalog owner and register the database.
Registering the database.
EXAMPLE only
For 8i DEBASHIS8i:/apps/oracle> rman target / catalog RCAT8I/password@debashis.jnv.com
RMAN> REGISTER DATABASE;
For 9i Log in to All 9i DBs> rman target / catalog RCAT9I/password@debashis.jnv.com
RMAN> REGISTER DATABASE;
For 10g Login to all 10g dbs> rman target / catalog RCAT10G/password@debashis.jnv.com
RMAN> REGISTER DATABASE;
SAME to 11g and 12c as well.
Make sure that the registration was successful by running REPORT SCHEMA.
Step is valid for each database after registration to catalog database.
Example Connect to 11g DBS> rman target / catalog RCAT11G/password@debashis.jnv.com
RMAN> REPORT SCHEMA;
<<< It should get all the TS and Datafile asscoited with it of the target DB>>
To check what database has been registered in catalog database.
Logon as respective catalog owner to catalog database either via RMAN or SQLPLUS
Example for Oracle 10g.
Via RMAN -> go to catalog databaseà oracle@prms01:~> rman target / catalog RCAT10G/password@debashis.jnv.com
And execute below query:
RMAN> list db_unique_name all;
-> It should show all the 10g database registered to catalog database. à Step is same for 8i, 9i, 11g just have to connect through their respective catalog owner.
Via SQLPLUS->
go to catalog database
oracle@prms01:~>sqlplus RCAT8I/password@debashis.jnv.com
oracle@prms01:~>sqlplus RCAT9I/password@debashis.jnv.com
oracle@prms01:~>sqlplus RCAT10g/password@debashis.jnv.com
oracle@prms01:~>sqlplus RCAT11g/password@debashis.jnv.com
oracle@prms01:~>sqlplus RCAT12c/password@debashis.jnv.com
Command to execute is:
SQL> select * from rc_database;
Steps6.We have incorporated the resync catalog command daily, weekly and archive scripts in
debashis10g:/apps/oracle> grep -i -e 'rman target /' -e 'resync' /debashis10g/admin/scripts/backup_archive.ksh
rman target / catalog rcat10g/password@debashis.jnv.com <<EOF
resync catalog;
debashis10g:/apps/oracle> grep -i -e 'rman target /' -e 'resync'
/debashis10g/admin/scripts/backup_archive.ksh
rman target / catalog rcat10g/password@debashis.jnv.com <<EOF
resync catalog;
To check backup SYNC up in target database Vs Catalog database user below query
In Target Database use below query:
SET pages 9999 lines 300
col INSTANCE for a10
col STATUS for a25
col START_TIME for a20
col END_TIME for a20
select distinct a.SESSION_KEY,(SELECT instance_name FROM v$instance) instance,
--b.BACKUP_TYPE,
b.incremental_level,
a.STATUS,
to_char(a.START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(a.END_TIME,'mm/dd/yy hh24:mi') end_time,
a.elapsed_seconds/3600 hrs
from
V$RMAN_BACKUP_JOB_DETAILS a, v$backup_set_details b
where
a.session_stamp = b.session_stamp
--and trunc(end_time)>=trunc(sysdate-1)
and b.incremental_level is not null
order by a.session_key;
In Catalog Database use below query:
SET pages 9999 lines 300
col INSTANCE for a10
col STATUS for a25
col START_TIME for a20
col END_TIME for a20
select distinct a.SESSION_KEY,b.db_name,
--b.BACKUP_TYPE,
b.incremental_level,
a.STATUS,
to_char(a.START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(a.END_TIME,'mm/dd/yy hh24:mi') end_time,
a.elapsed_seconds/3600 hrs
from
RC_RMAN_BACKUP_JOB_DETAILS a, RC_backup_set_details b
where
a.session_stamp = b.session_stamp
--and trunc(end_time)>=trunc(sysdate-1)
and b.incremental_level is not null
order by a.session_key;
<< in both end the output should be same>>
For archive Log checking
All are just an example.
Check in target database using below query
SQL> select (select name from v$database)dbaname, MIN_FIRST_CHANGE#,MAX_NEXT_CHANGE# from v$backup_archivelog_summary;
DBANAME MIN_FIRST_CHANGE# MAX_NEXT_CHANGE#
--------- ----------------- ----------------
U092 326110113 328303646
From Catalog database
oracle@debashis10g:~> sqlplus debashis10g/password@debashis.jnv.com
SQL> select db_name,MIN_FIRST_CHANGE#,MAX_NEXT_CHANGE# from rc_backup_archivelog_summary;
DB_NAME MIN_FIRST_CHANGE# MAX_NEXT_CHANGE#
-------- ----------------- ----------------
U092 326110113 328303646
Remark:
To unregister a database from the recovery catalog, use UNREGISTER DATABASE;
Connect to target database with respective catalog owner
And use command
RMAN> UNREGISTER DATABASE;
UNREGISTER commands which help us Unregister a Oracle database from the recovery catalog.
RMAN> UNREGISTER DATABASE;
RMAN> UNREGISTER DATABASE NOPROMPT;
RMAN> UNREGISTER DATABASE prod1;
RMAN> UNREGISTER DATABASE prod2 NOPROMPT;
RMAN> UNREGISTER DB_UNIQUE_NAME prod2;
RMAN> UNREGISTER DB_UNIQUE_NAME prod1 NOPROMPT;
RMAN> UNREGISTER DB_UNIQUE_NAME prod2 INCLUDING BACKUPS;
RMAN> UNREGISTER DB_UNIQUE_NAME prod3 INCLUDING BACKUPS NOPROMPT;
No comments:
Post a Comment