Hello Readers,
In this article, we will see Creation of RMAN Recovery Catalog.
Target Database : Catalog Database:
Name : AVPROD Name: AVDEV
IP Address : 192.168.0.130 IP Address : 192.168.0.60
What is RMAN Recovery Catalog :
The RMAN recovery catalog is a schema created inside of a new or existing database that is used to store metadata about Oracle databases.
1.The recovery catalog stores database metadata history for much longer than the controlfile. Also, if the target database’s controlfile and all backups are lost, the metadata is still stored inside of the recovery catalog.
2.If the preferred method is using backup scripts instead of command files, they can be stored in the database for safekeeping. This is useful when the server is lost and the backup scripts along with it.
3.The recovery catalog can be used as a centralized location to store information about all of your Oracle databases. This makes it much easier to run various reports about the backups.
Follow below steps to implement Recovery Catalog Setup
Target Database:
Check the Database
|
1 2 3 4 |
SQL> select name from v$database; NAME --------- AVPROD |
Step 1:
Create a listener
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
[oracle@oracle ~]$ cd /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/ [oracle@oracle admin]$ vi listener.ora tdb = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.0.130)(PORT = 7777)) ) ) ) SID_LIST_tdb = (SID_LIST = (SID_DESC = (SID_NAME = AVPROD) (ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1) ) ) |
Start the listener
|
1 |
oracle@oracle admin]$ lsnrctl start tdb |
Step 2 :
Create a directory to store RMAN backup files
|
1 |
[oracle@oracle ~]$ mkdir -p /u01/oradata/AVPROD/rmanbkp |
Step 3:
Goto dbs locations and create password file
|
1 2 |
[oracle@oracle ~]$ cd $ORACLE_HOME/dbs [oracle@oracle dbs]$ orapwd file=orapw$ORACLE_SID password=sys force=y |
Catalog database Side :
Catalog Database can be created newly or we can utilized existing database.
Step 4:
Create an entry in tnsnames.ora
|
1 2 3 4 5 6 7 8 9 10 |
[oracle@oracle admin]$ vi tnsnames.ora to_tdb = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.130 )(PORT = 7777)) ) (CONNECT_DATA = (SID = AVPROD) ) ) |
Check the connectivity of Oracle Net Services using tnsping
|
1 2 3 4 5 6 7 8 |
oracle@oracle admin]$ tnsping to_tdb TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 24-FEB-2021 22:25:32 Copyright (c) 1997, 2014, Oracle. All rights reserved. Used parameter files: /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.130)(PORT = 7777))) (CONNECT_DATA = (SID = AVPROD))) OK (0 msec) |
Step 5:
Create a tablespaces and Schema to store metadata about Oracle databases.
|
1 2 |
SQL> create tablespace cdb_ts datafile '/u01/oradata/avprod/cdb_ts.dbf' size 100m; Tablespace created |
Create Schema with catalog privilege called RECOVERY_CATALOG_OWNER
|
1 2 |
SQL> grant connect,resource,unlimited tablespace,recovery_catalog_owner to bco identified by bco; Grant succeeded. |
Make default tablespace to schema
|
1 2 3 |
SQL> alter user bco default tablespace cdb_ts; User altered. |
Step 6:
Connect to RMAN catalog
|
1 2 3 4 5 |
[oracle@oracle ~]$ rman catalog bco/bco Recovery Manager: Release 12.1.0.2.0 - Production on Wed Feb 24 22:30:01 2021 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to recovery catalog database RMNAN > |
Issue a command CREATE CATALOG
|
1 2 3 |
RMAN> create catalog recovery catalog created RMAN> |
Step 7:
Connect to database and check tables created under BCO user
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
[oracle@oracle ~]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Wed Feb 24 22:31:25 2021 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> conn bco/bco Connected. SQL> select count(*) from tab; COUNT(*) ---------- 157 |
Step 8:
Connect to TARGET DATABASE using RMAN RECOVERY CATALOG
|
1 2 3 4 5 6 |
[oracle@oracle ~]$ rman catalog bco/bco target sys/sys@to_tdb Recovery Manager: Release 12.1.0.2.0 - Production on Wed Feb 24 22:35:26 2021 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to target database: AVPROD (DBID=4020222652) connected to recovery catalog database |
Step 9:
Register the target database
check the existing registered dabases
|
1 |
RMAN> list db_unique_name all; |
Register the Database :
|
1 2 3 4 5 |
RMAN> register database; database registered in recovery catalog starting full resync of recovery catalog full resync complete |
Check the registered databases
|
1 2 3 4 5 |
RMAN> list db_unique_name all; List of Databases DB Key DB Name DB ID Database Role Db_unique_name ------- ------- ----------------- --------------- ------------------ 1 AVPROD 4020222652 PRIMARY AVPROD |
Execute the following RMAN Script for full database and archivelog backups.
Below script also deletes archivelogs before 1 day
Below script will take current controlfile backup.
|
1 2 3 4 5 6 7 8 9 10 |
RMAN> run { allocate channel c1 type disk; allocate channel c2 type disk; configure backup optimization on; backup as compressed backupset database plus archivelog format '/u01/oradata/AVPROD/rmanbkp/%U.bkp' skip inaccessible tag 'disk'; configure backup optimization off; delete noprompt archivelog all backed up 1 times to disk completed before 'SYSDATE-1'; backup current controlfile format '/u01/oradata/AVPROD/rmanbkp/%U.ctl' tag 'disk'; } |
Output for above script :
|
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 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 |
allocated channel: c1 channel c1: SID=59 device type=DISK allocated channel: c2 channel c2: SID=24 device type=DISK old RMAN configuration parameters: CONFIGURE BACKUP OPTIMIZATION OFF; new RMAN configuration parameters: CONFIGURE BACKUP OPTIMIZATION ON; new RMAN configuration parameters are successfully stored Starting backup at 04-MAR-21 current log archived skipping archived logs of thread 1 from sequence 14 to 19; already backed up channel c1: starting compressed archived log backup set channel c1: specifying archived log(s) in backup set input archived log thread=1 sequence=20 RECID=70 STAMP=1066292118 channel c1: starting piece 1 at 04-MAR-21 channel c1: finished piece 1 at 04-MAR-21 piece handle=/u01/oradata/AVPROD/rmanbkp/25vosksm_1_1.bkp tag=DISK comment=NONE channel c1: backup set complete, elapsed time: 00:00:01 Finished backup at 04-MAR-21 Starting backup at 04-MAR-21 channel c1: starting compressed full datafile backup set channel c1: specifying datafile(s) in backup set input datafile file number=00002 name=/u01/oradata/AVPROD/sysaux.dbf input datafile file number=00003 name=/u01/oradata/AVPROD/undotbs1.dbf input datafile file number=00006 name=/u01/oradata/AVPROD/ts100.dbf input datafile file number=00014 name=/u01/oradata/AVPROD/ts1.dbf channel c1: starting piece 1 at 04-MAR-21 channel c2: starting compressed full datafile backup set channel c2: specifying datafile(s) in backup set input datafile file number=00001 name=/u01/oradata/AVPROD/system.dbf input datafile file number=00004 name=/u01/oradata/AVPROD/userdata.dbf input datafile file number=00005 name=/u01/oradata/AVPROD/ts10.dbf input datafile file number=00013 name=/u01/oradata/AVPROD/undo2.dbf channel c2: starting piece 1 at 04-MAR-21 channel c1: finished piece 1 at 04-MAR-21 piece handle=/u01/app/oracle/product/12.1.0/dbhome_1/dbs/26vosksn_1_1 tag=TAG20210304T081519 comment=NONE channel c1: backup set complete, elapsed time: 00:00:15 channel c2: finished piece 1 at 04-MAR-21 piece handle=/u01/app/oracle/product/12.1.0/dbhome_1/dbs/27vosksn_1_1 tag=TAG20210304T081519 comment=NONE channel c2: backup set complete, elapsed time: 00:00:15 Finished backup at 04-MAR-21 Starting backup at 04-MAR-21 current log archived channel c1: starting compressed archived log backup set channel c1: specifying archived log(s) in backup set input archived log thread=1 sequence=21 RECID=71 STAMP=1066292134 channel c1: starting piece 1 at 04-MAR-21 channel c1: finished piece 1 at 04-MAR-21 piece handle=/u01/oradata/AVPROD/rmanbkp/28voskt6_1_1.bkp tag=DISK comment=NONE channel c1: backup set complete, elapsed time: 00:00:01 Finished backup at 04-MAR-21 Starting Control File Autobackup at 04-MAR-21 piece handle=/u01/oradata/AVPROD/rmanbkp/c-4020222652-20210304-03.ctl comment=NONE Finished Control File Autobackup at 04-MAR-21 old RMAN configuration parameters: CONFIGURE BACKUP OPTIMIZATION ON; new RMAN configuration parameters: CONFIGURE BACKUP OPTIMIZATION OFF; new RMAN configuration parameters are successfully stored Starting backup at 04-MAR-21 channel c1: starting full datafile backup set channel c1: specifying datafile(s) in backup set including current control file in backup set channel c1: starting piece 1 at 04-MAR-21 channel c1: finished piece 1 at 04-MAR-21 piece handle=/u01/oradata/AVPROD/rmanbkp/2avoskt8_1_1.ctl tag=DISK comment=NONE channel c1: backup set complete, elapsed time: 00:00:01 Finished backup at 04-MAR-21 Starting Control File Autobackup at 04-MAR-21 piece handle=/u01/oradata/AVPROD/rmanbkp/c-4020222652-20210304-04.ctl comment=NONE Finished Control File Autobackup at 04-MAR-21 released channel: c1 released channel: c2 RMAN> |
Check backup files
Note :
Backup files will be created in Target database location : /u01/oradata/AVPROD/rmanbkp
|
1 2 3 4 5 6 7 8 9 |
[oracle@oracle rmanbkp]$ ls -ltr -rw-r-----. 1 oracle dba 15729664 Mar 4 08:09 1nvoskgg_1_1.bkp -rw-r-----. 1 oracle dba 9379840 Mar 4 08:09 1ovoskgv_1_1.bkp -rw-r-----. 1 oracle dba 29859840 Mar 4 08:09 1mvoskgg_1_1.bkp -rw-r-----. 1 oracle dba 6698496 Mar 4 08:09 1pvoskh7_1_1.bkp -rw-r-----. 1 oracle dba 57876480 Mar 4 08:09 1rvoskh8_1_1.bkp -rw-r-----. 1 oracle dba 56492032 Mar 4 08:09 1qvoskh8_1_1.bkp -rw-r-----. 1 oracle dba 8704 Mar 4 08:09 1svoskib_1_1.bkp -rw-r-----. 1 oracle dba 8454144 Mar 4 08:09 c-4020222652-20210304-01.ctl |
Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates
KTEXPERTS is always active on below social media platforms.
Facebook : https://www.facebook.com/ktexperts/
LinkedIn : https://www.linkedin.com/company/ktexperts/
Twitter : https://twitter.com/ktexpertsadmin
YouTube : https://www.youtube.com/c/ktexperts
Instagram : https://www.instagram.com/knowledgesharingplatform
Loading...
