Article on: RMAN Catalog configure / de-configure
Applied to: Above Oracle version 8.1.x.x ( Tested in Oracle version 10g / 11g)
A)About Document:
1) Preserve RMAN repository information if control file is lost.
2) Can store much more history of backup then control file.
3) Can also hold RMAN stored scripts and sequence of RMAN commands for common backup tasks.
4) Except of stored scripts all features works well in both options.
5) Can hold data for one or more databases in a separate DB schema.
6) The tablespace that holds the repository database requires at least 125MB to hold recovery catalog entries; Starting out with available free space of 125MB will in most cases be sufficient for the first year, and enabling additional extents of 50MB each will be sufficient in the long term depending on how many databases you manage in the recovery catalog.
B)Contents of the Recovery Catalog :
The recovery catalog contains information about RMAN operations, including:
1) Datafile and archived redo log backup sets and backup pieces
2) Datafile copies
3) Archived redo logs and their copies
4) Tablespaces and datafiles on the target database
5) Stored scripts, which are named user-created sequences of RMAN commands
6) Persistent RMAN configuration settings
C) Operations of Recovery Catalog
1) Create recovery catalog [ CREATE CATALOG ]:
> SYS can not be the owner of catalog.
> Don’t use target DB for recovery catalog. Must be protected.
> ARCHIVE log mode is recommended for catalog.
> Ensure catalog and target DB do not reside on same disk.
> Assign separate table-space for recovery catalog schema.
Example:
$ CONNECT SYS/DEV@DEVDB AS SYSDBA
-- [1] Create user for recovery catalog ]-----------------
sql> CREATE TABLESPACE TOOLS
DATAFILE '/ORACLE/ORADATA/DEVDB/TOOLS/tools_01.dbf' SIZE 100M AUTOEXTEND ON;
sql> CREATE USER "RMAN" IDENTIFIED BY CATDEFAULT TABLESPACE "TOOLS" QUOTA UNLIMITED ON "TOOLS";
sql> GRANT CONNECT TO RMAN;
-- [2] Create recovery catalog owner ]-----------------
GRANT RECOVERY_CATALOG_OWNER TO RMAN;
-- [3] Create recovery catalog ]-----------------
RMAN> (enter RMAN at OS command prompt OR at Run)
RMAN> CONNECT CATALOG RMAN/CAT@DEVDB
RMAN> CREATE CATALOG;
2) Register DB in Recovery catalog [ REGISTER DATABASE / CATALOG ]:
> Registering database copies all repository data about target DB control file to recovery catalog.
> Multiple targets can be catalog.
> RMAN uses DBID to distinguish one DB from another.
Example:
RMAN> CONNECT TARGET SYS/SRDC@TEST1
-- connect with target database
RMAN> CONNECT CATALOG RMAN/CAT@DEVDB
-- connect with recovery catalog
RMAN> STARTUP MOUNT;
-- if target DB not mounted mount it
RMAN> REGISTER DATABASE;
-- Register database
RMAN> REPORT SCHEMA;
-- to verify that registration was successful
3) Older file (data-file copies back-up piece, archive-log) can also be cataloged.
Only oracle 8 and higher files can be cataloged.
Oracle 7 files can also be cataloged but must be consistent.
RMAN> CATALOG DATAFILE '/OLDBACKUP/USER01.DBF';
-Single DATA file
RMAN> CATALOG ARCHIVELOG '/OLDBACKUP/USER02.DBF';
-Single archivelog file
RMAN> CATALOG BACKUPPIECE '/OLDBACKUP/USER01.BKP';
-Single backup peice file
RMAN> CATALOG START WITH '/OLDBACKUP/';
-Multiple backup files specify only Directory name
4) UN-Register target database [ UNREGISTER DATABASE / DELETE ]:
> When database is un-registered from the catalog all records in recovery catalog are lost.
> Can be registering again based on the contents of the control files at the time registration.
> It is not necessary to connect the target database, but if you do not use SET DBID.
Example:
RMAN> CONNECT TARGET SYS/SRDC@TEST1
-- connect with target database
RMAN> CONNECT CATALOG RMAN/CAT@DEVDB
-- connect with recovery catalog
RMAN> UNREGISTER DATABASE;
If you want to delete all backups of the database. These following commands will delete file physically and removes entries from catalog.
RMAN>DELETE BACKUP DEVICE TYPE sbt;
--Delete all backups from tap drive
RMAN>DELETE BACKUP DEVICE TYPE DISK;
--Delete all backups from disk drive
RMAN>DELETE COPY;
D) Resetting DB Incarnation in the recovery catalog
> The SQL statement ALTER DATABASE OPEN RESTLOGS create new incarnation of the DB.
> Records of new incarnation can be access using V$DATABASE_INCARNATION view
> When you issue command ALTE DATABASE OPEN RESTLOGS incarnation record automatically created in catalog.
And DB issue RESET DATABASE command automatically.
> All subsequent backups and log archiving done by the target DB associated with the new DB incarnation.
> You may need to change the current incarnation for some recovery tasks.
Example:
RMAN> LIST INCARNATION OF DATABASE TEST1;
-- Obtain incarnation key (Inc Key)
RMAN> RESET DATABASETO INCARNATION 2;
-- Reset database to old incarnation
a) Removing DELETED records form catalog after Upgrade; [ PRGRMANC.SQL ]:
> In oracle 9i and later RMAN always removes catalog records on deleting files.
> In releases prior to oracle 9i RMAN show status DELETED after deleting the physical files.
> If you upgrade repository prior to oracle 9i then you need to set DELETED status. By using a SQL scripts
> This script removes all records with the status DELETED.
Example:
SQL> CONNECT RMAN/CAT@DEVDB
-- Connect to recovery catalog
SQL> @/ORACLE/PRODUCT/10.2.0/CLIENT_1/RDBMS/ADMIN/PRGRMANC.SQL
--Run script
b) Resynchronize the Recovery Catalog; [ RESYNC CATALOG ]:
> When RMAN performs a resynchronization it compares recovery catalog to control file with information that is missing or changed.
> RMAN perform resynchronization automatically as needed. But resynchronization can also do manually.
> In partial resynchronization RMAN reads current control file to update change information (new backups, new archive log) and does not resynchronize metadata (physical schema, data-file, table-spaces, redo-threads, rollback-segments).
> In full resynchronization RMAN updates all changed records.
> Resynchronize after physical database change.
> If you maintain recovery catalog the use RESYNC CATALOG often enough before they are reused.
> If set CONTROL_FILE_RECORD_KEEP_TIME = 7 days you muse resynchronize before 7 days.
Example:
RMAN> CONNECT TARGET SYS/SRDC@TEST1
-- connect with target database
RMAN> CONNECT CATALOG RMAN/CAT@DEVDB
-- connect with recovery catalog
RMAN> RESYNC CATALOG;
E) Backing up and recovering the recovering catalog:
If you do not backup the recovery catalog and a disk failure occurs and destroy recovery catalog then it will be very difficult to recovery the database. There are three possible ways to recover the recovery catalog.
1.Enable Archive log mode and take backups like any other DB and recover the recovery catalog in case of failure.
2.Use export and import utility to recreate or move catalog.
Don’t run CREATE CATALOG command either before or after import.
Export recovery catalog.
$ EXP mailto:rman/cat@devdb%20 FILE=cat.dmp OWNER=rman
Create recovery catalog user.
Import recovery catalog dump.
IMP mailto:USERID=rman2/cat2@devdb%20FILE=cat.dmp FROMUSER=rman TOUSER=rman21.
3.Re-Create Recovery Catalog from existing backups
Create new catalog in the new database.
Register target database.
Use RESYNC CATALOG command.
Issue CATLOG START WITH …. Commands to re-catalog any backups
Backup the recovery catalog as the same frequency as you backup.
F) Cataloging and Uncataloging Backups and Copies
The CATALOG command adds information about useable backups to the RMAN repository. Use this command to record backups created with tools other than RMAN, such as datafile copies created with operating system-level utilities. You can also use this command if you have backups which are created using RMAN but which are no longer listed in the RMAN repository. RMAN can use these backups in restore and recovery operations.
For example:
## copy made with operating system copy cmd
RMAN> CATALOG DATAFILECOPY '/backup/users01.bak';
RMAN> CATALOG LIKE'/backup'
Note: that the second example adds all usable backups where the filepath begins with /backup to the RMAN repository, including files in the directory /backup/users01.bak, files in subdirectories such as /backup/tuesday/users01.bak.old, and files in directories whose name starts with /backup, such as /backup-2001/users01.bak.old. Take care when choosing your argument for CATALOG LIKE.
The CHANGE ... UNCATALOG syntax lets you remove information about backups and copies from the RMAN repository. If you manually delete a backup using operating system commands, CHANGE...UNCATALOG updates the repository to reflect that change.
For example:
RMAN>CHANGE CONTROLFILECOPY '/tmp/cf.cpy' UNCATALOG;
RMAN>CHANGE BACKUPSET 121,122,127,203,300 UNCATALOG;
Hope this document will help.
Thanks
Applied to: Above Oracle version 8.1.x.x ( Tested in Oracle version 10g / 11g)
A)About Document:
1) Preserve RMAN repository information if control file is lost.
2) Can store much more history of backup then control file.
3) Can also hold RMAN stored scripts and sequence of RMAN commands for common backup tasks.
4) Except of stored scripts all features works well in both options.
5) Can hold data for one or more databases in a separate DB schema.
6) The tablespace that holds the repository database requires at least 125MB to hold recovery catalog entries; Starting out with available free space of 125MB will in most cases be sufficient for the first year, and enabling additional extents of 50MB each will be sufficient in the long term depending on how many databases you manage in the recovery catalog.
B)Contents of the Recovery Catalog :
The recovery catalog contains information about RMAN operations, including:
1) Datafile and archived redo log backup sets and backup pieces
2) Datafile copies
3) Archived redo logs and their copies
4) Tablespaces and datafiles on the target database
5) Stored scripts, which are named user-created sequences of RMAN commands
6) Persistent RMAN configuration settings
C) Operations of Recovery Catalog
1) Create recovery catalog [ CREATE CATALOG ]:
> SYS can not be the owner of catalog.
> Don’t use target DB for recovery catalog. Must be protected.
> ARCHIVE log mode is recommended for catalog.
> Ensure catalog and target DB do not reside on same disk.
> Assign separate table-space for recovery catalog schema.
Example:
$ CONNECT SYS/DEV@DEVDB AS SYSDBA
-- [1] Create user for recovery catalog ]-----------------
sql> CREATE TABLESPACE TOOLS
DATAFILE '/ORACLE/ORADATA/DEVDB/TOOLS/tools_01.dbf' SIZE 100M AUTOEXTEND ON;
sql> CREATE USER "RMAN" IDENTIFIED BY CATDEFAULT TABLESPACE "TOOLS" QUOTA UNLIMITED ON "TOOLS";
sql> GRANT CONNECT TO RMAN;
-- [2] Create recovery catalog owner ]-----------------
GRANT RECOVERY_CATALOG_OWNER TO RMAN;
-- [3] Create recovery catalog ]-----------------
RMAN> (enter RMAN at OS command prompt OR at Run)
RMAN> CONNECT CATALOG RMAN/CAT@DEVDB
RMAN> CREATE CATALOG;
2) Register DB in Recovery catalog [ REGISTER DATABASE / CATALOG ]:
> Registering database copies all repository data about target DB control file to recovery catalog.
> Multiple targets can be catalog.
> RMAN uses DBID to distinguish one DB from another.
Example:
RMAN> CONNECT TARGET SYS/SRDC@TEST1
-- connect with target database
RMAN> CONNECT CATALOG RMAN/CAT@DEVDB
-- connect with recovery catalog
RMAN> STARTUP MOUNT;
-- if target DB not mounted mount it
RMAN> REGISTER DATABASE;
-- Register database
RMAN> REPORT SCHEMA;
-- to verify that registration was successful
3) Older file (data-file copies back-up piece, archive-log) can also be cataloged.
Only oracle 8 and higher files can be cataloged.
Oracle 7 files can also be cataloged but must be consistent.
RMAN> CATALOG DATAFILE '/OLDBACKUP/USER01.DBF';
-Single DATA file
RMAN> CATALOG ARCHIVELOG '/OLDBACKUP/USER02.DBF';
-Single archivelog file
RMAN> CATALOG BACKUPPIECE '/OLDBACKUP/USER01.BKP';
-Single backup peice file
RMAN> CATALOG START WITH '/OLDBACKUP/';
-Multiple backup files specify only Directory name
4) UN-Register target database [ UNREGISTER DATABASE / DELETE ]:
> When database is un-registered from the catalog all records in recovery catalog are lost.
> Can be registering again based on the contents of the control files at the time registration.
> It is not necessary to connect the target database, but if you do not use SET DBID.
Example:
RMAN> CONNECT TARGET SYS/SRDC@TEST1
-- connect with target database
RMAN> CONNECT CATALOG RMAN/CAT@DEVDB
-- connect with recovery catalog
RMAN> UNREGISTER DATABASE;
If you want to delete all backups of the database. These following commands will delete file physically and removes entries from catalog.
RMAN>DELETE BACKUP DEVICE TYPE sbt;
--Delete all backups from tap drive
RMAN>DELETE BACKUP DEVICE TYPE DISK;
--Delete all backups from disk drive
RMAN>DELETE COPY;
D) Resetting DB Incarnation in the recovery catalog
> The SQL statement ALTER DATABASE OPEN RESTLOGS create new incarnation of the DB.
> Records of new incarnation can be access using V$DATABASE_INCARNATION view
> When you issue command ALTE DATABASE OPEN RESTLOGS incarnation record automatically created in catalog.
And DB issue RESET DATABASE command automatically.
> All subsequent backups and log archiving done by the target DB associated with the new DB incarnation.
> You may need to change the current incarnation for some recovery tasks.
Example:
RMAN> LIST INCARNATION OF DATABASE TEST1;
-- Obtain incarnation key (Inc Key)
RMAN> RESET DATABASETO INCARNATION 2;
-- Reset database to old incarnation
a) Removing DELETED records form catalog after Upgrade; [ PRGRMANC.SQL ]:
> In oracle 9i and later RMAN always removes catalog records on deleting files.
> In releases prior to oracle 9i RMAN show status DELETED after deleting the physical files.
> If you upgrade repository prior to oracle 9i then you need to set DELETED status. By using a SQL scripts
> This script removes all records with the status DELETED.
Example:
SQL> CONNECT RMAN/CAT@DEVDB
-- Connect to recovery catalog
SQL> @/ORACLE/PRODUCT/10.2.0/CLIENT_1/RDBMS/ADMIN/PRGRMANC.SQL
--Run script
b) Resynchronize the Recovery Catalog; [ RESYNC CATALOG ]:
> When RMAN performs a resynchronization it compares recovery catalog to control file with information that is missing or changed.
> RMAN perform resynchronization automatically as needed. But resynchronization can also do manually.
> In partial resynchronization RMAN reads current control file to update change information (new backups, new archive log) and does not resynchronize metadata (physical schema, data-file, table-spaces, redo-threads, rollback-segments).
> In full resynchronization RMAN updates all changed records.
> Resynchronize after physical database change.
> If you maintain recovery catalog the use RESYNC CATALOG often enough before they are reused.
> If set CONTROL_FILE_RECORD_KEEP_TIME = 7 days you muse resynchronize before 7 days.
Example:
RMAN> CONNECT TARGET SYS/SRDC@TEST1
-- connect with target database
RMAN> CONNECT CATALOG RMAN/CAT@DEVDB
-- connect with recovery catalog
RMAN> RESYNC CATALOG;
E) Backing up and recovering the recovering catalog:
If you do not backup the recovery catalog and a disk failure occurs and destroy recovery catalog then it will be very difficult to recovery the database. There are three possible ways to recover the recovery catalog.
1.Enable Archive log mode and take backups like any other DB and recover the recovery catalog in case of failure.
2.Use export and import utility to recreate or move catalog.
Don’t run CREATE CATALOG command either before or after import.
Export recovery catalog.
$ EXP mailto:rman/cat@devdb%20 FILE=cat.dmp OWNER=rman
Create recovery catalog user.
Import recovery catalog dump.
IMP mailto:USERID=rman2/cat2@devdb%20FILE=cat.dmp FROMUSER=rman TOUSER=rman21.
3.Re-Create Recovery Catalog from existing backups
Create new catalog in the new database.
Register target database.
Use RESYNC CATALOG command.
Issue CATLOG START WITH …. Commands to re-catalog any backups
Backup the recovery catalog as the same frequency as you backup.
F) Cataloging and Uncataloging Backups and Copies
The CATALOG command adds information about useable backups to the RMAN repository. Use this command to record backups created with tools other than RMAN, such as datafile copies created with operating system-level utilities. You can also use this command if you have backups which are created using RMAN but which are no longer listed in the RMAN repository. RMAN can use these backups in restore and recovery operations.
For example:
## copy made with operating system copy cmd
RMAN> CATALOG DATAFILECOPY '/backup/users01.bak';
RMAN> CATALOG LIKE'/backup'
Note: that the second example adds all usable backups where the filepath begins with /backup to the RMAN repository, including files in the directory /backup/users01.bak, files in subdirectories such as /backup/tuesday/users01.bak.old, and files in directories whose name starts with /backup, such as /backup-2001/users01.bak.old. Take care when choosing your argument for CATALOG LIKE.
The CHANGE ... UNCATALOG syntax lets you remove information about backups and copies from the RMAN repository. If you manually delete a backup using operating system commands, CHANGE...UNCATALOG updates the repository to reflect that change.
For example:
RMAN>CHANGE CONTROLFILECOPY '/tmp/cf.cpy' UNCATALOG;
RMAN>CHANGE BACKUPSET 121,122,127,203,300 UNCATALOG;
Hope this document will help.
Thanks
No comments:
Post a Comment