Go back to OCM 12c Preparation Project
Hands On Lab – Index
[1] Creating a Recovery Catalog
1.1 Planning the Size of the Recovery Catalog Schema
1.2 Allocating Disk Space for the Recovery Catalog Database
1.3 Creating the Recovery Catalog Schema Owner
1.4 Executing the CREATE CATALOG Command
[2] Registering a Database in the Recovery Catalog
[3] Catalog any older backups whose records are no longer stored in the target control file.
3.1 Cataloging Backups in the Recovery Catalog
3.2 About Resynchronization of the Recovery Catalog
3.3 Deciding When to Resynchronize the Recovery Catalog
3.4 Manually Resynchronizing the Recovery Catalog
3.5 HOL-Register second targer database in recovery catalog.
[4] Creating and Managing Virtual Private Catalogs
[5] Managing Stored SCRIPTS
[6] Upgrading the Recovery Catalog
[7] Import and Move the recovery catalog
HOL-We have two databases on separate machines,oemdb in racnode1 and cdb1 in racnode2,We will create recovery catalog in their respective schema’s rcatalog1 and rcatalog2. We will import both the schema’s into new recovery catalog databse orcl.
The catalog is a database schema that contains the RMAN repository data for one or more target databases. One of the priorities of a DBA is to avoid losing data. In this objective we will see how to create and maintain the Recovery Catalog. This metadata repository is used to centralize the information contained in the CONTROLFILE all our databases. Thus we have redundancy information of a historical Backups greater than would be saved in the CONTROLFILE (CONTROL_FILE_RECORD_KEEP_TIME parameter). In addition, the RMAN catalog gives us a historical structure of the database in time and save stored scripts STORED SCRIPTS). 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. Default Configuration SQL> show parameter CONTROL_FILE_RECORD_KEEP_TIME NAME TYPE VALUE ------------------------------------ ----------- ----------------------- control_file_record_keep_time integer 7 SQL> archive log list Database log mode No Archive Mode Automatic archival Disabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 3 Current log sequence 5 SQL> Caution: Regardless of whether you use a recovery catalog, never use RMAN when CONTROL_FILE_RECORD_KEEP_TIME is set to 0. If you do, then you may lose backup records. The catalog includes the following types of metadata: Data file and archived redo log backup sets and backup pieces Data file copies Archived redo logs and their copies Database structure (tablespaces and data files) Stored scripts, which are named user-created sequences of RMAN commands Persistent RMAN configuration settings Note: Do not use the target database to be backed up as the database for the recovery catalog. The recovery catalog must be protected if the target database is lost. Caution: Ensure that the recovery catalog and target databases do not reside on the same disk. If both your recovery catalog and your target database suffer hard disk failure, then your recovery process is much more difficult. If possible, take other measures as well to eliminate common points of failure between your recovery catalog database and the databases that you are backing up. 13 Managing a Recovery Catalog [1] Creating a Recovery Catalog This section explains the phases of recovery catalog creation. This section contains the following topics: #Configuring the Recovery Catalog Database -> When you use a recovery catalog, RMAN requires that you maintain a recovery catalog schema. -> The recovery catalog is stored in the default tablespace of the schema. -> Privileged users such as SYS cannot be the owner of the recovery catalog. -> Decide which database you will use to install the recovery catalog schema, and also how you will back up this database. Also, -> decide whether to operate the catalog database in ARCHIVELOG mode, which is recommended. #->Planning the Size of the Recovery Catalog Schema For example, Assume that the trgt database has 100 files, and that you back up the database once a day, producing 50 backup sets containing 1 backup piece each. If you assume that each row in the backup piece table uses the maximum amount of space, then one daily backup consumes less than 170 kilobytes in the recovery catalog. So, if you back up once a day for a year, then the total storage in this period is about 62 megabytes. Assume approximately the same amount for archived logs. Thus, the worst case is about 120 megabytes for a year for metadata storage. For a more typical case in which only a portion of the backup piece row space is used, 15 MB for each year is realistic. If you plan to register multiple databases in your recovery catalog, then remember to add up the space required for each one based on the previous calculation to arrive at a total size for the default tablespace of the recovery catalog schema. #->Allocating Disk Space for the Recovery Catalog Database Table 13-1 Typical Recovery Catalog Space Requirements for 1 Year Type of Space Space Requirement SYSTEM tablespace 90 MB Temp tablespace 5 MB Rollback or undo tablespace 5 MB Recovery catalog tablespace 15 MB for each database registered in the recovery catalog Online redo logs 1 MB each (three groups, each with two members) #Creating the Recovery Catalog Schema Owner SQL> create tablespace tools 2 datafile '/u01/app/oracle/oradata/orcl/tools.dbf' size 50M; Tablespace created. SQL> create user sbu identified by sbu 2 default tablespace tools 3 quota unlimited on tools 4 temporary tablespace temp; User created. SQL> grant recovery_Catalog_owner,connect,sysbackup to sbu; Grant succeeded. Note: Starting with Oracle Database 12c Release 1 (12.1.0.2), the recovery catalog database must use the Enterprise Edition of Oracle Database. #Executing the CREATE CATALOG Command $rman catalog sbu/sbu@orcl RMAN> CREATE CATALOG; Note: You can specify the tablespace name for the catalog in the CREATE CATALOG command. For example: RMAN> CREATE CATALOG TABLESPACE cat_tbs; $sqlplus sbu/sbu@orcl SQL> select TABLE_NAME,TABLESPACE_NAME from USER_TABLES; SQL> col table_name format a20 SQL> col tablespace_name format a21 SQL> / TABLE_NAME TABLESPACE_NAME -------------------- --------------------- DB TOOLS NODE TOOLS CONF TOOLS DBINC TOOLS PDB TOOLS PDBINC TOOLS PDB_DBINC TOOLS CKP TOOLS TS TOOLS TSATT TOOLS DF TOOLS SITE_DFATT TOOLS TF TOOLS SITE_TFATT TOOLS OFFR TOOLS RR TOOLS RT TOOLS ORL TOOLS RLH TOOLS AL TOOLS BS TOOLS BP TOOLS BCF TOOLS CCF TOOLS XCF TOOLS BSF TOOLS BDF TOOLS CDF TOOLS XDF TOOLS BRL TOOLS BCB TOOLS CCB TOOLS SCR TOOLS SCRL TOOLS XAL TOOLS RSR TOOLS FB TOOLS GRSP TOOLS NRSP TOOLS VPC_USERS TOOLS VPC_DATABASES TOOLS CFS TOOLS XMLSTORE TOOLS SERVER TOOLS RCFILE TOOLS ORSEVENT TOOLS RRCACHE TOOLS BCR TOOLS ROUT TOOLS RCVER TOOLS TEMPRES TOOLS DELETED_OBJECT TOOLS WATERMARKS TOOLS BASCHEMAVER DO_SEQ CONFIG 56 rows selected. [2] Registering a Database in the Recovery Catalog #From Machine1 -bash-4.1$ rman target / catalog sbu/sbu@orcl Recovery Manager: Release 12.1.0.2.0 - Production on Thu Aug 8 10:49:52 2019 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to target database: OEMDB (DBID=683605331) connected to recovery catalog database RMAN> list incarnation; RMAN> REGISTER DATABASE; database registered in recovery catalog starting full resync of recovery catalog full resync complete RMAN> list incarnation; List of Database Incarnations DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time ------- ------- -------- ---------------- --- ---------- ---------- 1 9 OEMDB 683605331 PARENT 1 07-JUL-14 1 2 OEMDB 683605331 CURRENT 1594143 03-AUG-19 RMAN> report schema; Report of database schema for database with db_unique_name OEMDB List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1 790 SYSTEM YES /u01/app/oracle/oradata/oemdb/system01.dbf 3 710 SYSAUX NO /u01/app/oracle/oradata/oemdb/sysaux01.dbf 4 780 UNDOTBS1 YES /u01/app/oracle/oradata/oemdb/undotbs01.dbf 5 250 PDB$SEED:SYSTEM NO /u01/app/oracle/oradata/oemdb/pdbseed/system01.dbf 6 5 USERS NO /u01/app/oracle/oradata/oemdb/users01.dbf 7 550 PDB$SEED:SYSAUX NO /u01/app/oracle/oradata/oemdb/pdbseed/sysaux01.dbf 8 510 PDB:SYSTEM NO /u01/app/oracle/oradata/oemdb/pdb/system01.dbf 9 610 PDB:SYSAUX NO /u01/app/oracle/oradata/oemdb/pdb/sysaux01.dbf 10 5 PDB:USERS NO /u01/app/oracle/oradata/oemdb/pdb/pdb_users01.dbf 11 60 PDB:MGMT_ECM_DEPOT_TS NO /u01/app/oracle/oradata/oemdb/pdb/mgmt_depot.dbf 12 2600 PDB:MGMT_TABLESPACE NO /u01/app/oracle/oradata/oemdb/pdb/mgmt.dbf 13 200 PDB:MGMT_AD4J_TS NO /u01/app/oracle/oradata/oemdb/pdb/mgmt_ad4j.dbf List of Temporary Files ======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 1 197 TEMP 32767 /u01/app/oracle/oradata/oemdb/temp01.dbf 2 100 PDB$SEED:TEMP 32767 /u01/app/oracle/oradata/oemdb/pdbseed/pdbseed_temp012019-08-03_04-24-06-PM.dbf 3 26 PDB:TEMP 32767 /u01/app/oracle/oradata/oemdb/pdb/temp012019-08-03_04-24-06-PM.dbf [3] Catalog any older backups whose records are no longer stored in the target control file. #->Cataloging Backups in the Recovery Catalog CATALOG DATAFILECOPY '/disk1/old_datafiles/01_01_2003/users01.dbf'; CATALOG ARCHIVELOG '/disk1/arch_logs/archive1_731.dbf', '/disk1/arch_logs/archive1_732.dbf'; CATALOG BACKUPPIECE '/disk1/backups/backup_820.bkp'; You can also catalog multiple backup files in a directory by using the CATALOG START WITH command, as shown in the following example: CATALOG START WITH '/disk1/backups/'; For example, assume that a group of directories /disk1/backups, /disk1/backups-year2003, /disk1/backupsets, and /disk1/backupsets/test and so on, all contain backup files. The following command catalogs all files in all of these directories, because /disk1/backups is a prefix for the paths for all of these directories: CATALOG START WITH '/disk1/backups'; Note: When RMAN performs a resynchronization, it compares the recovery catalog to either the current or backup control file of the target database and updates the catalog with metadata that is missing or changed. Most RMAN commands perform a resynchronization automatically when the target control file is mounted and the catalog is available. In a Data Guard environment, RMAN can perform a reverse resynchronization to update a database control file with metadata from the catalog. About Resynchronization of the Recovery Catalog Resynchronization of the recovery catalog ensures that the metadata that RMAN obtains from the control file stays current. Resynchronizations can be full or partial. In a partial resynchronization, RMAN reads the current control file of the target database to update changed metadata about new backups, new archived redo logs, and so on. RMAN does not resynchronize metadata about the database physical schema. In a full resynchronization, RMAN updates all changed records, including those for the database schema. RMAN performs a full resynchronization after structural changes to database (adding or dropping database files, creating new incarnation, and so on) or after changes to the RMAN persistent configuration. RMAN creates a snapshot control file, which is a temporary backup control file, when it performs a full resynchronization. The database ensures that only one RMAN session accesses a snapshot control file at any point in time. RMAN creates the snapshot control file in an operating system-specific location on the target database host. This snapshot control file ensures that RMAN has a consistent view of the control file. Because the control file is intended for short-term use, it is not registered in the catalog. RMAN records the control file checkpoint in the recovery catalog to indicate the currency of the catalog. Deciding When to Resynchronize the Recovery Catalog Resynchronizing After the Recovery Catalog is Unavailable Resynchronizing in ARCHIVELOG Mode When You Back Up Infrequently Resynchronizing After Configuring a Standby Database Resynchronizing the Recovery Catalog Before Control File Records Age Out Manually Resynchronizing the Recovery Catalog RMAN> RESYNC CATALOG ; starting full resync of recovery catalog full resync complete The following example resynchronizes the control file of standby1: RMAN> RESYNC CATALOG FROM DB_UNIQUE_NAME standby1; To resynchronizes the control files for all databases in the Data Guard environment: RMAN> RESYNC CATALOG FROM DB_UNIQUE_NAME ALL; #Updating the Recovery Catalog After Changing a DB_UNIQUE_NAME RMAN> LIST DB_UNIQUE_NAME OF DATABASE; RMAN> CHANGE DB_UNIQUE_NAME FROM prodsf1 TO prodsf2; #Unregistering a Target Database from the Recovery Catalog RMAN> UNREGISTER DATABASE; ->To unregister database standby: RMAN> UNREGISTER DB_UNIQUE_NAME standby1; database name is "OEMDB" and DBID is 683605331 Do you really want to unregister the database (enter YES or NO)? yes database unregistered from the recovery catalog RMAN> list incarnation; === HOL : Register second targer database in recovery catalog. === 1. Register fist target database - cdb1 -bash-4.1$ rman target / catalog sbu/sbu@orcl Recovery Manager: Release 12.1.0.2.0 - Production on Thu Aug 8 11:57:51 2019 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to target database: CDB1 (DBID=989477122) connected to recovery catalog database RMAN> register database; database registered in recovery catalog starting full resync of recovery catalog full resync complete RMAN> list incarnation; List of Database Incarnations DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time ------- ------- -------- ---------------- --- ---------- ---------- 195 203 CDB1 989477122 PARENT 1 07-JUL-14 195 196 CDB1 989477122 CURRENT 1594143 08-AUG-19 2. Register second target database oemdb from machine2 -bash-4.1$ export ORACLE_SID=oemdb -bash-4.1$ -bash-4.1$ rman target / catalog sbu/sbu@orcl Recovery Manager: Release 12.1.0.2.0 - Production on Thu Aug 8 11:58:31 2019 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to target database: OEMDB (DBID=683605331) connected to recovery catalog database RMAN> register database; database registered in recovery catalog starting full resync of recovery catalog full resync complete -bash-4.1$ rman catalog sbu/sbu@orcl Recovery Manager: Release 12.1.0.2.0 - Production on Thu Aug 8 12:00:24 2019 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to recovery catalog database RMAN> list incarnation; List of Database Incarnations DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time ------- ------- -------- ---------------- --- ---------- ---------- 195 203 CDB1 989477122 PARENT 1 07-JUL-14 195 196 CDB1 989477122 CURRENT 1594143 08-AUG-19 395 403 OEMDB 683605331 PARENT 1 07-JUL-14 395 396 OEMDB 683605331 CURRENT 1594143 03-AUG-19 In this case,even if different user is used (not sbu) to connect to the recovery catalog. list incarnation command would list both databases as an output. [4] If needed, create virtual private catalogs for specific users and determine the metadata to which they are permitted access. Creating and Managing Virtual Private Catalogs -bash-4.1$ export ORACLE_SID=orcl -bash-4.1$ sql SQL*Plus: Release 12.1.0.2.0 Production on Thu Aug 8 12:04:42 2019 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> create tablespace vc datafile '/u01/app/oracle/oradata/orcl/vc.dbf' size 15M; Tablespace created. SQL> create user vc identified by vc 2 default tablespace vc 3 quota unlimited on vc 4 temporary tablespace temp; User created. SQL> grant recovery_catalog_owner,connect,resource to vc; Grant succeeded. SQL> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options -bash-4.1$ -bash-4.1$ rman catalog sbu/sbu@orcl Recovery Manager: Release 12.1.0.2.0 - Production on Thu Aug 8 12:06:44 2019 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to recovery catalog database RMAN> GRANT CATALOG FOR DATABASE oemdb to vc; Grant succeeded. RMAN> exit Recovery Manager complete. -bash-4.1$ -bash-4.1$ rman catalog vc/vc@orcl Recovery Manager: Release 12.1.0.2.0 - Production on Thu Aug 8 12:07:48 2019 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to recovery catalog database RMAN-07540: Cleanup is required for the VPC user schema << exit Recovery Manager complete. -bash-4.1$ oerr rman 07540 7540, 3, "Cleanup is required for the VPC user schema" // *Cause: The VPC user specified in the connect string does have extra // privileges granted. // *Action: Execute '?/rdbms/admin/dbmsrmanvpc.sql' after connecting to // a catalog database as SYS to cleanup VPC users schemas. -bash-4.1$ -bash-4.1$ sqlplus sys/oracle_4U@orcl as sysdba SQL> @?/rdbms/admin/dbmsrmanvpc.sql -all Checking the operating user... Passed ======================================== Upgrading the VPC user schemas registerd in the base catalog of SBU ---------------------------------------- Upgrading: VC Synonyms... Views... Cleanup of privileges... Grant of privileges... ---------------------------------------- Removing old VPC views in the base catalog of SBU... ======================================== UPGRADE STATUS The VPC user schemas of these catalogs: SBU have been successfully upgraded to the new VPD model! -bash-4.1$ rman catalog vc/vc@orcl Recovery Manager: Release 12.1.0.2.0 - Production on Thu Aug 8 12:12:21 2019 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to recovery catalog database RMAN> create virtual catalog; found eligible base catalog owned by SBU created virtual catalog against base catalog owned by SBU RMAN> list incarnation; List of Database Incarnations DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time ------- ------- -------- ---------------- --- ---------- ---------- 395 403 OEMDB 683605331 PARENT 1 07-JUL-14 395 396 OEMDB 683605331 CURRENT 1594143 03-AUG-19 -bash-4.1$ rman catalog sbu/sbu@orcl Recovery Manager: Release 12.1.0.2.0 - Production on Thu Aug 8 12:13:24 2019 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to recovery catalog database RMAN> list incarnation; List of Database Incarnations DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time ------- ------- -------- ---------------- --- ---------- ---------- 195 203 CDB1 989477122 PARENT 1 07-JUL-14 195 196 CDB1 989477122 CURRENT 1594143 08-AUG-19 395 403 OEMDB 683605331 PARENT 1 07-JUL-14 395 396 OEMDB 683605331 CURRENT 1594143 03-AUG-19 RMAN> exit Recovery Manager complete. -bash-4.1$ -bash-4.1$ rman catalog vc/vc@orcl Recovery Manager: Release 12.1.0.2.0 - Production on Thu Aug 8 12:13:51 2019 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to recovery catalog database RMAN> list incarnation; List of Database Incarnations DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time ------- ------- -------- ---------------- --- ---------- ---------- 395 403 OEMDB 683605331 PARENT 1 07-JUL-14 395 396 OEMDB 683605331 CURRENT 1594143 03-AUG-19 RMAN> exit [5] Managing Stored SCRIPTS export ORACLE_SID=oemdb -bash-4.1$ rman target / catalog sbu/sbu@orcl Recovery Manager: Release 12.1.0.2.0 - Production on Thu Aug 8 12:58:55 2019 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to target database: OEMDB (DBID=683605331) connected to recovery catalog database RMAN> create global script my_script { backup spfile; } created global script my_script RMAN> create script my_script2 { backup current controlfile; } created script my_script2 RMAN> LIST SCRIPT NAMES; List of Stored Scripts in Recovery Catalog Scripts of Target Database OEMDB Script Name Description ----------------------------------------------------------------------- my_script2 Global Scripts Script Name Description ----------------------------------------------------------------------- my_script export ORACLE_SID=cdb1 -bash-4.1$ rman target / catalog sbu/sbu@orcl Recovery Manager: Release 12.1.0.2.0 - Production on Thu Aug 8 13:05:02 2019 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to target database: CDB1 (DBID=989477122) connected to recovery catalog database RMAN> list script names; List of Stored Scripts in Recovery Catalog Global Scripts Script Name Description ----------------------------------------------------------------------- my_script RMAN> print script my_script; printing stored global script: my_script { backup spfile; } RMAN> run { 2> execute script my_script; 3> } executing global script: my_script Starting backup at 08-AUG-19 starting full resync of recovery catalog full resync complete allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=273 device type=DISK channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including current SPFILE in backup set channel ORA_DISK_1: starting piece 1 at 08-AUG-19 channel ORA_DISK_1: finished piece 1 at 08-AUG-19 piece handle=/u01/app/oracle/fast_recovery_area/CDB1/backupset/2019_08_08/o1_mf_nnsnf_TAG20190808T130535_gnqn68nj_.bkp tag=TAG20190808T130535 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 08-AUG-19 Starting Control File and SPFILE Autobackup at 08-AUG-19 piece handle=/u01/app/oracle/fast_recovery_area/CDB1/autobackup/2019_08_08/o1_mf_s_1015765538_gnqn6c88_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 08-AUG-19 RMAN> RMAN> replace script my_script { backup current controlfile;} replaced script my_script -bash-4.1$ rman target / catalog sbu/sbu@orcl Recovery Manager: Release 12.1.0.2.0 - Production on Thu Aug 8 13:09:57 2019 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to target database: CDB1 (DBID=989477122) connected to recovery catalog database RMAN> print script my_script; printing stored script: my_script { backup current controlfile;} RMAN> delete script my_script; deleted script: my_script RMAN> list script names; List of Stored Scripts in Recovery Catalog Global Scripts Script Name Description ----------------------------------------------------------------------- my_script RMAN> delete script my_script; deleted global script: my_script RMAN> list script names; List of Stored Scripts in Recovery Catalog No scripts in recovery catalog RMAN> quit Recovery Manager complete. -bash-4.1$ rman target / catalog sbu/sbu@orcl Recovery Manager: Release 12.1.0.2.0 - Production on Thu Aug 8 13:12:17 2019 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to target database: OEMDB (DBID=683605331) connected to recovery catalog database RMAN> list script names; List of Stored Scripts in Recovery Catalog Scripts of Target Database OEMDB Script Name Description ----------------------------------------------------------------------- my_script2 -bash-4.1$ rman target / catalog sbu/sbu@orcl script my_script2 log=/tmp/myscript2.log -bash-4.1$ cat /tmp/myscript2.log Recovery Manager: Release 12.1.0.2.0 - Production on Thu Aug 8 13:15:31 2019 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to target database: OEMDB (DBID=683605331) connected to recovery catalog database executing script: my_script2 Starting backup at 08-AUG-19 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=237 device type=DISK channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including current control file in backup set channel ORA_DISK_1: starting piece 1 at 08-AUG-19 channel ORA_DISK_1: finished piece 1 at 08-AUG-19 piece handle=/u01/app/oracle/fast_recovery_area/OEMDB/backupset/2019_08_08/o1_mf_ncnnf_TAG20190808T131535_gnqns0xd_.bkp tag=TAG20190808T131535 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02 Finished backup at 08-AUG-19 Starting Control File and SPFILE Autobackup at 08-AUG-19 piece handle=/u01/app/oracle/fast_recovery_area/OEMDB/autobackup/2019_08_08/o1_mf_s_1015766139_gnqns3oh_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 08-AUG-19 Recovery Manager complete. [6] Upgrading the Recovery Catalog Note: Starting with Oracle Database 12c Release 1 (12.1.0.2), the recovery catalog database must use the Enterprise Edition of Oracle Database. If you use a version of the recovery catalog schema that is older than that required by the RMAN client, then you must upgrade it. For example, you must upgrade the catalog if you use an Oracle Database 11g RMAN client with a release 10.2 version of the recovery catalog schema. The Oracle Database 10g Release 1 version of the recovery catalog schema requires the CREATE TYPE privilege. If you created the recovery catalog owner in a release before 10gR1, and if you granted the RECOVERY_CATALOG_OWNER role when it did not include the CREATE TYPE privilege, then you must grant CREATE TYPE to this user explicitly before upgrading the catalog. Query the RCVER table to obtain the schema version SELECT * FROM rcver; If the table displays multiple rows, then the highest version in the RCVER table is the current catalog schema version. To upgrade the recovery catalog to Oracle Database 12c Release 1 (12.1.0.1) SQL> GRANT CREATE TYPE TO rman; SQL> EXIT; RMAN> UPGRADE CATALOG; RMAN> UPGRADE CATALOG; To upgrade the recovery catalog to Oracle Database 12c Release 1 (12.1.0.2) Use SQL*Plus to connect to the recovery catalog database as the SYS user with the SYSDBA privilege. Run the dbmsrmansys.sql script to grant additional privileges that are required for the RECOVERY_CATALOG_OWNER role. SQL> @$ORACLE_HOME/rdbms/admin/dbmsrmansys.sql RMAN> UPGRADE CATALOG; RMAN> UPGRADE CATALOG; [7] Import and Move the recovery catalog You can use the IMPORT CATALOG command in RMAN to merge one recovery catalog schema into another. This command is useful in the following situations: [1] You have multiple recovery catalog schemas for different versions of the database. You want to merge all existing schemas into one without losing backup metadata. [2] You want to move a recovery catalog from one database to another database. By default, RMAN unregisters the imported databases from the source catalog schema after a successful import. To indicate whether the unregister was successful, RMAN prints messages before and after unregistering the merged databases. You can also specify the NO UNREGISTER option to specify that the databases is not unregistered from the source catalog. When using IMPORT CATALOG, the version of the source recovery catalog schema must be equal to the current version of the RMAN executable with which you run the command. When importing one recovery catalog into another, no connection to a target database is necessary. RMAN only needs connectivity to the source and destination catalogs. In this example, database srcdb contains a 10.2 recovery catalog schema owned by user 102cat, while database destdb contains an 11.1 recovery catalog schema owned by user 111cat. To import a recovery catalog: RMAN> CONNECT CATALOG 111cat@destdb; RMAN> IMPORT CATALOG 102cat@srcdb; A variation is to import metadata for a subset of the target databases registered in the source catalog. You can specify the databases by DBID or database name, as shown in the following examples: IMPORT CATALOG 102cat@srcdb DBID=1423241, 1423242; IMPORT CATALOG 102cat@srcdb DB_NAME=prod3, prod4; CONNECT TARGET "sbu@prod1 AS SYSBACKUP"; LIST BACKUP; ==== HOL : We have two databases on separate machines,oemdb in racnode1 and cdb1 in racnode2,We will create recovery catalog in their respective schema's rcatalog1 and rcatalog2. ==== We will import both the schema's into new recovery catalog databse orcl. From Machine2 -bash-4.1$ export ORACLE_SID=cdb1 SQL> alter session set container=pdb1; Session altered. SQL> show user USER is "SYS" SQL> show con_name CON_NAME ------------------------------ PDB1 SQL> create tablespace rcatalog1 datafile '/u01/app/oracle/oradata/cdb1/pdb1/rcatalog1.dbf' size 10m Tablespace created. SQL> create user rcatalog1 identified by rcatalog1 2 default tablespace rcatalog1 3 quota unlimited on rcatalog1 4 temporary tablespace temp; User created. SQL> grant connect,resource,recovery_catalog_owner to rcatalog1; Grant succeeded. bash-4.1$ cat sqlnet.ora # sqlnet.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/sqlnet.ora # Generated by Oracle configuration tools. NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME) -bash-4.1$ sqlplus rcatalog1/rcatalog1@racnode2/pdb1 SQL*Plus: Release 12.1.0.2.0 Production on Fri Aug 9 11:03:34 2019 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> show con_name CON_NAME ------------------------------ PDB1 SQL> show user USER is "RCATALOG1" SQL> exit -bash-4.1$ rman catalog rcatalog1/rcatalog1@racnode2/pdb1 Recovery Manager: Release 12.1.0.2.0 - Production on Fri Aug 9 11:05:20 2019 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to recovery catalog database RMAN> create catalog; -bash-4.1$ rman target / catalog rcatalog1/rcatalog1@racnode2/pdb1 Recovery Manager: Release 12.1.0.2.0 - Production on Fri Aug 9 11:07:57 2019 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to target database: CDB1 (DBID=989477122) connected to recovery catalog database RMAN> register database; database registered in recovery catalog starting full resync of recovery catalog full resync complete RMAN> list incarnation; List of Database Incarnations DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time ------- ------- -------- ---------------- --- ---------- ---------- 1 104 CDB1 989477122 PARENT 1 07-JUL-14 1 2 CDB1 989477122 CURRENT 1594143 08-AUG-19 RMAN> exit #From Machine1 -bash-4.1$ export ORACLE_SID=oemdb -bash-4.1$ sql SQL> show parameter db_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string oemdb SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB READ WRITE NO SQL> SQL> alter session set container=pdb; Session altered. SQL> create tablespace rcatalog2 2 datafile '/u01/app/oracle/oradata/oemdb/pdb/rcatalog2.dbf' size 10m; Tablespace created. SQL> create user rcatalog2 identified by rcatalog2 2 temporary tablespace temp 3 default tablespace rcatalog2 4 quota unlimited on rcatalog2; User created. SQL> grant connect,resource,recovery_catalog_owner to rcatalog2; Grant succeeded. SQL> connect rcatalog2/rcatalog2@racnode1/pdb Connected. SQL> show con_name CON_NAME ------------------------------ PDB SQL> select * from tab; no rows selected SQL> exit -bash-4.1$ rman catalog rcatalog2/rcatalog2@racnode1/pdb Recovery Manager: Release 12.1.0.2.0 - Production on Fri Aug 9 11:12:47 2019 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to recovery catalog database RMAN> create catalog; -bash-4.1$ rman target / catalog rcatalog2/rcatalog2@racnode1/pdb Recovery Manager: Release 12.1.0.2.0 - Production on Fri Aug 9 11:14:08 2019 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to target database: OEMDB (DBID=683605331) connected to recovery catalog database RMAN> register database; database registered in recovery catalog starting full resync of recovery catalog full resync complete RMAN> list incarnation; List of Database Incarnations DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time ------- ------- -------- ---------------- --- ---------- ---------- 1 9 OEMDB 683605331 PARENT 1 07-JUL-14 1 2 OEMDB 683605331 CURRENT 1594143 03-AUG-19 RMAN> exit Recovery Manager complete. #Merge the catalog schemas in central repositroy recovery catalog. -bash-4.1$ export ORACLE_SID=orcl -bash-4.1$ sql -bash-4.1$ rman catalog sbu/sbu@orcl Recovery Manager: Release 12.1.0.2.0 - Production on Fri Aug 9 11:19:21 2019 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to recovery catalog database RMAN> list incarnation; -bash-4.1$ bash-4.1$ rman catalog sbu/sbu@orcl Recovery Manager: Release 12.1.0.2.0 - Production on Fri Aug 9 11:19:58 2019 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to recovery catalog database RMAN> import catalog rcatalog1/rcatalog1@racnode2/pdb1 2> ; Starting import catalog at 09-AUG-19 connected to source recovery catalog database import validation complete database unregistered from the source recovery catalog Finished import catalog at 09-AUG-19 RMAN> list incarnation; List of Database Incarnations DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time ------- ------- -------- ---------------- --- ---------- ---------- 796 899 CDB1 989477122 PARENT 1 07-JUL-14 796 797 CDB1 989477122 CURRENT 1594143 08-AUG-19 RMAN> import catalog rcatalog2/rcatalog2@racnode1/pdb 2> ; Starting import catalog at 09-AUG-19 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of import catalog command at 08/09/2019 11:21:04 RMAN-04035: error from source recovery catalog database: ORA-12154: TNS:could not resolve the connect identifier specified RMAN> import catalog rcatalog2/rcatalog2@racnode1.localdomain:1521/pdb 2> ; Starting import catalog at 09-AUG-19 connected to source recovery catalog database import validation complete database unregistered from the source recovery catalog Finished import catalog at 09-AUG-19 RMAN> RMAN> list incarnation; List of Database Incarnations DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time ------- ------- -------- ---------------- --- ---------- ---------- 796 899 CDB1 989477122 PARENT 1 07-JUL-14 796 797 CDB1 989477122 CURRENT 1594143 08-AUG-19 998 1006 OEMDB 683605331 PARENT 1 07-JUL-14 998 999 OEMDB 683605331 CURRENT 1594143 03-AUG-19 -bash-4.1$ rman catalog rcatalog1/rcatalog1@racnode2/pdb1 Recovery Manager: Release 12.1.0.2.0 - Production on Fri Aug 9 11:24:02 2019 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to recovery catalog database RMAN> list incarnation; RMAN> quit -bash-4.1$ rman catalog rcatalog2/rcatalog2@racnode1/pdb Recovery Manager: Release 12.1.0.2.0 - Production on Fri Aug 9 11:25:58 2019 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to recovery catalog database RMAN> list incarnation; RMAN> exit ====================== Patch to Documentation ====================== Mantain Recovery Catalogs 1. Documentation -> Database Administrator -> Backup and Recovery User's Guide -> 13 Managing a Recovery Catalog
Thank you for visiting this blog…