Vignesh D – Sharing Real-World Oracle DBA & Cloud Insights

Saturday, September 11, 2021

CREATE RECOVERY CATALOG DATABASE

CREATE RECOVERY 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

Ø archive log

Ø backups (backup sets, pieces and copies),

Ø archived redo logs

Target DB = Target Database (livedb)

Recovery Catalog = A database of backup information of all the databases registered with RMAN (rcdb)

Step 1: create a new database for the recovery catalog using dbca

Step 2:create a table space in recovery catalog database

[oracle@vignesh ~]$ . rcdb.env

[oracle@vignesh ~]$ sqlplus / as sysdba

SQL> create tablespace rec_cat datafile'/u01/app/oracle/oradata/RCDB/datafile/rec_cat.dbf' size 1g;

Tablespace created.

STEP 3: Next create the recovery catalog owner.

The user SYS cannot be the owner of the recovery catalog.so we need to create new user to the recovery database.

SQL>create user reccat identified by reccat default tablespace rec_cat quota unlimited on rec_cat;

User created.

STEP 4: Grant RECOVERY_CATALOG_OWNER role to recovery catlog user (e.g. reccat user)

RECOVERY_CATALOG_OWNER – this privileges is used to perform catalog recovery in database

SQL>grant recovery_catalog_owner to reccat;

Grant succeeded.

STEP 5:check listener status and add the tns entry for rcdb database

Listener .ora

[oracle@vignesh admin]$ cat listener.ora

livedb =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.*.*)(PORT = 1521))

)

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))

)

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)

(PROGRAM = extproc)

)

(SID_DESC =

(GLOBAL_DBNAME = livedb)

(ORACLE_HOME =/u01/app/oracle/product/19.0.0/dbhome_1)

(SID_NAME = livedb)

)

Tnsnames.ora

livedb=

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.*.*)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = livedb)

)

rcdb=

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.*.*)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = rcdb)

)

STEP 6: - Connect to target DB (livedb) and recovery catlog DB (rcdb)

[oracle@vignesh ~]$ . livedb.env

[oracle@vignesh ~]$ cd

[oracle@vignesh ~]$ rman target / catalog reccat@rcdb

Recovery Manager: Release 19.0.0.0.0 - Production on Sun Sep 12 01:12:44 2021

Version 19.3.0.0.0

connected to target database: LIVEDB (DBID=3038400691)

recovery catalog database Password:***

connected to recovery catalog database

STEP 7: create a catalog for rcdb tablespace rec_cat;

RMAN> create catalog tablespace rec_cat;

recovery catalog created

STEP 8: Register the databases which will use this database as recovery catalog

[oracle@vignesh ~]$ rman target / catalog reccat@rcdb

connected to target database: LIVEDB (DBID=3038400691)

recovery catalog database Password:

connected to recovery catalog database

RMAN> register database;

database registered in recovery catalog

starting full resync of recovery catalog

full resync complete

STEP 9- check RMAN configure and update

RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;

new RMAN configuration parameters:

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;

new RMAN configuration parameters are successfully stored

starting full resync of recovery catalog

full resync complete

RMAN> configure default device type to disk;

old RMAN configuration parameters:

CONFIGURE DEFAULT DEVICE TYPE TO DISK;

new RMAN configuration parameters:

CONFIGURE DEFAULT DEVICE TYPE TO DISK;

new RMAN configuration parameters are successfully stored

starting full resync of recovery catalog

full resync complete

STEP 10 - Take database full backup connecting to recovery catalog DB.

RMAN> run{

backup database plus archivelog;

delete noprompt obsolete;

}

Starting backup at 12-SEP-21

current log archived

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=3 RECID=37 STAMP=1083029104

input archived log thread=1 sequence=4 RECID=38 STAMP=1083029378

input archived log thread=1 sequence=5 RECID=39 STAMP=1083029616

channel ORA_DISK_1: starting piece 1 at 12-SEP-21

channel ORA_DISK_1: finished piece 1 at 12-SEP-21

piece handle=/u01/app/oracle/fast_recovery_area/LIVEDB/backupset/2021_09_12/o1_mf_annnn_TAG20210912T013336_jmt2ws1t_.bkp tag=TAG20210912T013336 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 12-SEP-21

Starting backup at 12-SEP-21

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00005 name=/u01/app/oracle/oradata/LIVEDB/datafile/rman.dbf

input datafile file number=00001 name=/u01/app/oracle/oradata/LIVEDB/datafile/o1_mf_system_jmhwt5ld_.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/LIVEDB/datafile/o1_mf_sysaux_jmhwt5n5_.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/LIVEDB/datafile/o1_mf_undotbs1_jmhwt5pt_.dbf

input datafile file number=00007 name=/u01/app/oracle/oradata/LIVEDB/datafile/o1_mf_users_jmhwt5pw_.dbf

channel ORA_DISK_1: starting piece 1 at 12-SEP-21

channel ORA_DISK_1: finished piece 1 at 12-SEP-21

piece handle=/u01/app/oracle/fast_recovery_area/LIVEDB/backupset/2021_09_12/o1_mf_nnndf_TAG20210912T013338_jmt2wvp1_.bkp tag=TAG20210912T013338 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45

Finished backup at 12-SEP-21

Starting backup at 12-SEP-21

current log archived

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=6 RECID=40 STAMP=1083029664

channel ORA_DISK_1: starting piece 1 at 12-SEP-21

channel ORA_DISK_1: finished piece 1 at 12-SEP-21

piece handle=/u01/app/oracle/fast_recovery_area/LIVEDB/backupset/2021_09_12/o1_mf_annnn_TAG20210912T013424_jmt2y91q_.bkp tag=TAG20210912T013424 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 12-SEP-21

Starting Control File and SPFILE Autobackup at 12-SEP-21

piece handle=/u01/app/oracle/fast_recovery_area/LIVEDB/autobackup/2021_09_12/o1_mf_s_1083029666_jmt2ybx4_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 12-SEP-21

RMAN retention policy will be applied to the command

RMAN retention policy is set to recovery window of 2 days

using channel ORA_DISK_1

no obsolete backups found

the recovery database configuration completed also full database backup taken by catalog user

Tuesday, September 7, 2021

LEVEL 0 and LEVEl 1 backup recovery using RMAN

LEVEL 0 and LEVEl 1 backup recovery using RMAN

Connect rman and take level 0 backup

[oracle@vignesh ~]$ rman target/

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Sep 8 04:38:18 2021

Version 19.3.0.0.0

connected to target database: LIVEDB (DBID=3038400691)

RMAN> backup incremental level 0 database plus archivelog;

Starting backup at 08-SEP-21

current log archived

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=34 device type=DISK

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=5 RECID=1 STAMP=1082689816

input archived log thread=1 sequence=6 RECID=2 STAMP=1082689893

input archived log thread=1 sequence=7 RECID=3 STAMP=1082690477

input archived log thread=1 sequence=8 RECID=4 STAMP=1082690478

input archived log thread=1 sequence=9 RECID=5 STAMP=1082690480

input archived log thread=1 sequence=10 RECID=6 STAMP=1082690866

input archived log thread=1 sequence=11 RECID=7 STAMP=1082690927

input archived log thread=1 sequence=12 RECID=8 STAMP=1082691628

input archived log thread=1 sequence=13 RECID=9 STAMP=1082692404

input archived log thread=1 sequence=14 RECID=10 STAMP=1082692477

input archived log thread=1 sequence=15 RECID=11 STAMP=1082692656

input archived log thread=1 sequence=16 RECID=12 STAMP=1082695103

channel ORA_DISK_1: starting piece 1 at 08-SEP-21

channel ORA_DISK_1: finished piece 1 at 08-SEP-21

piece handle=/u01/app/oracle/fast_recovery_area/LIVEDB/backupset/2021_09_08/o1_mf_annnn_TAG20210908T043825_jmhw79d8_.bkp tag=TAG20210908T043825 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15

Finished backup at 08-SEP-21

Starting backup at 08-SEP-21

using channel ORA_DISK_1

channel ORA_DISK_1: starting incremental level 0 datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=/u01/app/oracle/oradata/LIVEDB/datafile/o1_mf_system_jmho76m8_.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/LIVEDB/datafile/o1_mf_sysaux_jmho8n57_.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/LIVEDB/datafile/o1_mf_undotbs1_jmho9f8x_.dbf

input datafile file number=00007 name=/u01/app/oracle/oradata/LIVEDB/datafile/o1_mf_users_jmho9gcd_.dbf

channel ORA_DISK_1: starting piece 1 at 08-SEP-21

channel ORA_DISK_1: finished piece 1 at 08-SEP-21

piece handle=/u01/app/oracle/fast_recovery_area/LIVEDB/backupset/2021_09_08/o1_mf_nnnd0_TAG20210908T043841_jmhw7s4x_.bkp tag=TAG20210908T043841 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55

Finished backup at 08-SEP-21

Starting backup at 08-SEP-21

current log archived

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=17 RECID=13 STAMP=1082695177

channel ORA_DISK_1: starting piece 1 at 08-SEP-21

channel ORA_DISK_1: finished piece 1 at 08-SEP-21

piece handle=/u01/app/oracle/fast_recovery_area/LIVEDB/backupset/2021_09_08/o1_mf_annnn_TAG20210908T043937_jmhw9kdv_.bkp tag=TAG20210908T043937 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 08-SEP-21

Starting Control File and SPFILE Autobackup at 08-SEP-21

piece handle=/u01/app/oracle/fast_recovery_area/LIVEDB/autobackup/2021_09_08/o1_mf_s_1082695178_jmhw9mmw_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 08-SEP-21

Check backup summary and note the level 0 backup TAG

RMAN> list backup of database summary;

List of Backups

===============

Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag

------- -- -- - ----------- --------------- ------- ------- ---------- ---

7 B F A DISK 08-SEP-21 1 1 NO TAG20210908T032750

11 B F A DISK 08-SEP-21 1 1 NO TAG20210908T035340

15 B 0 A DISK 08-SEP-21 1 1 NO TAG20210908T043841

RMAN> exit

Once level 0 completed create a user and table insert the record for the purpose of level 1 backup

Recovery Manager complete.

[oracle@vignesh ~]$ export ORACLE_SID=livedb

[oracle@vignesh ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Sep 8 04:42:05 2021

Version 19.3.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> create user ogr identified by ogr;

User created.

SQL> grant connect, resource, create session to ogr;

Grant succeeded.

SQL> alter user ogr default tablespace users quota unlimited on users;

User altered.

SQL> conn ogr/ogr

Connected.

SQL> insert into test values(1,'one');

1 row created.

SQL> insert into test values(2,'Two');

1 row created.

SQL> insert into test values(3,'Three');

1 row created.

SQL> insert into test values(4,'Four');

1 row created.

SQL> commit;

Commit complete.

Connect rman and take level 1 backup

[oracle@vignesh ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Sep 8 04:44:07 2021

Version 19.3.0.0.0

connected to target database: LIVEDB (DBID=3038400691)

RMAN> backup incremental level 1 database plus archivelog;

Starting backup at 08-SEP-21

current log archived

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=70 device type=DISK