Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
There’s a need to creating a Recovery Catalog so that the backups taken from Primay or Physical Standby database can be used among each other.
Oracle best recommended also recommends that the server where the Recovery Catalog will be different from the servers in the Data Guard Environment.
Let’s Test
1. We connect to SQLPLUS on the server to be used for Recovery Catalog.
1 2 3 4 5 6 7 8 9 10 | [oracle@primary admin]$ sqlplus sys/Passw0rd4@broker as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Mon Feb 13 15:27:34 2017 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 |
2. We check if the connection is correct.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | [Primary] SQL> show parameter name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ cell_offloadgroup_name string db_file_name_convert string db_name string broker db_unique_name string broker global_names boolean FALSE instance_name string broker lock_name_space string log_file_name_convert string pdb_file_name_convert string processor_group_name string service_names string broker |
3. The current datafiles in the catalog server are queried.
1 2 3 4 5 6 7 8 9 | [Primary] SQL> select file_name from dba_data_files; FILE_NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/broker/system01.dbf /u01/app/oracle/oradata/broker/sysaux01.dbf /u01/app/oracle/oradata/broker/example01.dbf /u01/app/oracle/oradata/broker/users01.dbf /u01/app/oracle/oradata/broker/undotbs01.dbf |
4. We create a new tablespace to hold the Recovery Catalog information.
1 2 3 | [Primary] SQL> create tablespace rcts datafile '/u01/app/oracle/oradata/broker/rcts01.dbf' size 30M autoextend on; Tablespace created. |
5. We create a Recovery Catalog user.
1 2 3 | [Primary] SQL> create user rcowner identified by "rcowner" default tablespace rcts quota unlimited on rcts; User created. |
6. The RECOVERY_CATALOG_OWNER Role is assigned to the user.
1 2 3 | [Primary] SQL> grant recovery_catalog_owner to rcowner; Grant succeeded. |
7. We connect to the Catalog database with RMAN.
1 2 3 4 5 6 7 8 9 | [oracle@primary admin]$ rman Recovery Manager: Release 12.1.0.2.0 - Production on Mon Feb 13 15:33:58 2017 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. RMAN> connect catalog rcowner/rcowner@broker connected to recovery catalog database |
8. The catalog is created.
1 2 3 | RMAN> create catalog; recovery catalog created |
9. We connect the primary database to be TARGET.
1 2 3 | RMAN> connect target 'sys/Passw0rd4@primary as sysdba' connected to target database: PRIMARY (DBID=1772883609) |
10. The primary database is REGISTERed to the catalog.
1 2 3 4 5 | RMAN> register database; database registered in recovery catalog starting full resync of recovery catalog full resync complete |
With this operation, it copies the information in the Control File of the RMAN Primary database to the Recovery Catalog tables. Backup information is now in both Control File and Recovery Catalog.
Thus, in case of a problem in Control File, I will be able to access Control File information from Recovery Catalog.
At this stage I do not REGISTER the Physical Standby database. In the future, when using CONFIGURE DB_UNIQUE_NAME parameter, this database will be REGISTER automatically.
11. All databases registered to Recovery Catalog are listed.
1 2 3 4 5 6 7 | RMAN> list db_unique_name of database; List of Databases DB Key DB Name DB ID Database Role Db_unique_name ------- ------- ----------------- --------------- ------------------ 1 PRIMARY 1772883609 PRIMARY PRIMARY |
12. Tablespaces of primary database are listed.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | RMAN> report schema for db_unique_name 'primary'; Report of database schema for database with db_unique_name PRIMARY List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1 820 SYSTEM YES /u01/app/oracle/oradata/primary/system01.dbf 3 860 SYSAUX NO /u01/app/oracle/oradata/primary/sysaux01.dbf 4 150 UNDOTBS1 YES /u01/app/oracle/oradata/primary/undotbs01.dbf 5 1243 EXAMPLE NO /u01/app/oracle/oradata/primary/example01.dbf 6 5 USERS NO /u01/app/oracle/oradata/primary/users01.dbf List of Temporary Files ======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 1 197 TEMP 32767 /u01/app/oracle/oradata/primary/temp01.dbf |
13. All archive logs in the primary database are listed.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | RMAN> list archivelog all for db_unique_name 'primary'; List of Archived Log Copies for database with db_unique_name BOSTON ================================================================ ===== Key Thrd Seq S Low Time ------- ---- ------- - --------- ... 358 1 6 A 13-FEB-17 Name: /u01/app/oracle/recovery_area/PRIMARY/archivelog/2017_02_13/o1_mf_1_6_db2v2vw6_.arc 359 1 7 A 13-FEB-17 Name: /u01/app/oracle/recovery_area/PRIMARY/archivelog/2017_02_13/o1_mf_1_7_db2v3byy_.arc 360 1 8 A 13-FEB-17 Name: /u01/app/oracle/recovery_area/PRIMARY/archivelog/2017_02_13/o1_mf_1_8_db2v3dmn_.arc 361 1 9 A 13-FEB-17 Name: /u01/app/oracle/recovery_area/PRIMARY/archivelog/2017_02_13/o1_mf_1_9_db2v3wkl_.arc 362 1 10 A 13-FEB-17 Name: /u01/app/oracle/recovery_area/PRIMARY/archivelog/2017_02_13/o1_mf_1_10_db2v41w4_.arc 363 1 11 A 13-FEB-17 Name: /u01/app/oracle/recovery_area/PRIMARY/archivelog/2017_02_13/o1_mf_1_11_db2v4q0q_.arc 364 1 12 A 13-FEB-17 Name: /u01/app/oracle/recovery_area/PRIMARY/archivelog/2017_02_13/o1_mf_1_12_db2v7xpg_.arc 365 1 13 A 13-FEB-17 Name: /u01/app/oracle/recovery_area/PRIMARY/archivelog/2017_02_13/o1_mf_1_13_db34sy4t_.arc |
14. All configurable RMAN parameters for the primary database are listed.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | RMAN> show all for db_unique_name 'primary'; RMAN configuration parameters for database with db_unique_name PRIMARY are: CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default CONFIGURE BACKUP OPTIMIZATION OFF; # default CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE MAXSETSIZE TO UNLIMITED; # default CONFIGURE ENCRYPTION FOR DATABASE OFF; # default CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/12.1.0/db_1/dbs/snapcf_primary.f'; # default |
15. We are editing the Backup Retention Policy.
1 2 3 4 5 6 7 | RMAN> configure retention policy to recovery window of 7 days; new RMAN configuration parameters: CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS; new RMAN configuration parameters are successfully stored starting full resync of recovery catalog full resync complete |
16. We determine when archive logs will be deleted.
1 2 3 4 5 6 7 | RMAN> configure archivelog deletion policy to applied on all standby; new RMAN configuration parameters: CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY; new RMAN configuration parameters are successfully stored starting full resync of recovery catalog full resync complete |
17. Which service to use is specified in TNSNAMES to connect to the primary database.
1 2 3 4 5 6 7 | RMAN> configure db_unique_name 'primary' connect identifier 'primary'; new RMAN configuration parameters: CONFIGURE DB_UNIQUE_NAME 'primary' CONNECT IDENTIFIER 'primary'; new RMAN configuration parameters are successfully stored starting full resync of recovery catalog full resync complete |
When RMAN will RESYNC the Databases in the Catalog, it must be connected to the databases remotely. For this reason, it is necessary to specify which service to connect to these databases.
18. In order to connect to the Physical Standby database, which service will be used is specified in TNSNAMES.
1 2 3 4 5 6 7 | RMAN> configure db_unique_name 'physical' connect identifier 'physical'; new RMAN configuration parameters: CONFIGURE DB_UNIQUE_NAME 'physical' CONNECT IDENTIFIER 'physical'; new RMAN configuration parameters are successfully stored starting full resync of recovery catalog full resync complete |
19. We list the databases connected to the catalog.
1 2 3 4 5 6 7 8 | RMAN> list db_unique_name of database; List of Databases DB Key DB Name DB ID Database Role Db_unique_name ------- ------- ----------------- --------------- ------------------ 1 PRIMARY 1772883609 PRIMARY PRIMARY 1 PRIMARY 1772883609 STANDBY PHYSICAL |
Although there is no REGISTER operation for the Physical Standby Database, 2 databases appear in the catalog. The reason for this is that as we said before, only the REGISTER operation of the Primary database is mandatory.
Standby Databases are automatically REGISTERed with Configure parameters.
20. We list the Tablespaces of the Physical Standby Database.
1 2 3 4 5 6 7 8 9 10 11 12 13 | RMAN> report schema for db_unique_name 'physical'; Report of database schema for database with db_unique_name PHYSICAL List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1 820 SYSTEM YES 3 860 SYSAUX NO 4 150 UNDOTBS1 YES 5 1243 EXAMPLE NO 6 5 USERS NO |
21. Connects to the Physical Standby database as TARGET.
1 2 3 4 5 6 7 8 | [oracle@primary admin]$ rman target sys/Passw0rd4@physical catalog rcowner/rcowner@broker Recovery Manager: Release 12.1.0.2.0 - Production on Mon Feb 13 16:03:36 2017 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to target database: PRIMARY (DBID=1772883609) connected to recovery catalog database |
22. We list the current RMAN parameters for the Physical database.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | RMAN> show all for db_unique_name 'physical'; RMAN configuration parameters for database with db_unique_name PHYSICAL are: CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS; CONFIGURE BACKUP OPTIMIZATION OFF; # default CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE MAXSETSIZE TO UNLIMITED; # default CONFIGURE ENCRYPTION FOR DATABASE OFF; # default CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default CONFIGURE DB_UNIQUE_NAME 'primary' CONNECT IDENTIFIER 'primary'; CONFIGURE DB_UNIQUE_NAME 'physical' CONNECT IDENTIFIER 'physical'; CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY; CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/12.1.0/db_1/dbs/snapcf_physical.f'; # default |
23. We edit the Backup optimization parameter.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | RMAN> configure backup optimization on; new RMAN configuration parameters: CONFIGURE BACKUP OPTIMIZATION ON; new RMAN configuration parameters are successfully stored Sadece Catalog' a bağlanılıp bu parametre çalıştırılmak istendiğinde aşağıdaki hata alınır. RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of configure command at 02/15/2017 09:15:46 RMAN-06171: not connected to target database |
The way to resolve this error is to either connect to the database where the parameter will be edited as TARGET as specified, or to set the DBID. We set the DBID as follows.
The DBID is learned.
1 2 3 4 5 | [Primary] SQL> select DBID from v$database; DBID ---------- 1772883609 |
It connects to the catalog.
1 2 3 4 5 6 7 | [oracle@primary ~]$ rman Recovery Manager: Release 12.1.0.2.0 - Production on Wed Feb 15 09:18:48 2017 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. RMAN> connect catalog rcowner/rcowner@broker |
The DBID is set.
1 2 3 4 | RMAN> set dbid 1772883609; executing command: SET DBID database name is "PRIMARY" and DBID is 1772883609 |
A parameter is set for testing purposes.
1 2 3 4 5 | RMAN> configure controlfile autobackup on for db_unique_name 'primary'; new RMAN configuration parameters: CONFIGURE CONTROLFILE AUTOBACKUP ON; new RMAN configuration parameters are successfully stored |
The value of the parameter is displayed.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | RMAN> show all for db_unique_name 'primary'; RMAN configuration parameters for database with db_unique_name PRIMARY are: CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS; CONFIGURE BACKUP OPTIMIZATION OFF; # default CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default CONFIGURE CONTROLFILE AUTOBACKUP ON; CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE MAXSETSIZE TO UNLIMITED; # default CONFIGURE ENCRYPTION FOR DATABASE OFF; # default CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default CONFIGURE DB_UNIQUE_NAME 'primary' CONNECT IDENTIFIER 'primary'; CONFIGURE DB_UNIQUE_NAME 'physical' CONNECT IDENTIFIER 'physical'; CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY; |
We query whether the parameter has changed in the other database in the Catalog.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | RMAN> show all for db_unique_name 'physical'; RMAN configuration parameters for database with db_unique_name PHYSICAL are: CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS; CONFIGURE BACKUP OPTIMIZATION ON; CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE MAXSETSIZE TO UNLIMITED; # default CONFIGURE ENCRYPTION FOR DATABASE OFF; # default CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default CONFIGURE DB_UNIQUE_NAME 'primary' CONNECT IDENTIFIER 'primary'; CONFIGURE DB_UNIQUE_NAME 'physical' CONNECT IDENTIFIER 'physical'; CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY; |
Although the DBIDs of the Primary and Physical Standby database are the same, I could not understand why only the Primary was changed.
1 2 3 4 5 | [Primary] SQL> select dbid from v$database; DBID ---------- 1772883609 |
1 2 3 4 5 | [Physical] SQL> select dbid from v$database; DBID ---------- 1772883609 |
