Blog / People and Culture Blog

CREATE CATALOG AND RECOVER CONTROLFILE WITH CATALOG

Viyanixavier D 4 min read

CREATE CATALOG AND RECOVER CONTROLFILE WITH CATALOG

  PRIMARY CATALOG
HOSTNAME 192.168.1.33 192.168.1.21
DB PORT 1521 1525
DB NAME VIYANI CATDB

 

  • In Oracle, A catalog is a collection of data dictionaries that store metadata, such as the structure of tables, views, and indexes.
  • You can create a catalog in Oracle using the following steps:

 

TARGET (CATDB): 192.168.1.21

 

[oracle@test ~]$ export ORACLE_SID=catdb

[oracle@test ~]$

[oracle@test ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 – Production on Sun Feb 5 18:13:37 2023

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:

 

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production

Version 19.3.0.0.0

SQL> select name, open_mode from v$database;

NAME   OPEN_MODE

——— ——————–

CATDB   READ WRITE

 

SQL> show user;

USER is “SYS”

 

SQL> select name from v$datafile;

NAME

——————————————————————————–

/u01/app/oracle/oradata/CATDB/datafile/o1_mf_system_kxz2lgq9_.dbf

/u01/app/oracle/oradata/CATDB/datafile/o1_mf_sysaux_kxz2mwdn_.dbf

/u01/app/oracle/oradata/CATDB/datafile/o1_mf_undotbs1_kxz2nokf_.dbf

/u01/app/oracle/oradata/CATDB/datafile/o1_mf_users_kxz2npop_.dbf

 

SQL> create tablespace rmantbs datafile ‘/u01/app/oracle/oradata/CATDB/datafile/rman_01.dbf’ size 1G;

Tablespace created.

 

SQL> select tablespace_name, sum(bytes)/1024/1024/1024 as tbs_size from dba_data_files where tablespace_name=‘RMANTBS’ group by tablespace_name;

TABLESPACE_NAME  TBS_SIZE

—————————— ———-

RMANTBS 1

 

SQL> create user rman identified by rman123

  2  default tablespace rmantbs

  3  temporary tablespace temp

  4  quota unlimited on rmantbs;

User created.

 

SQL> grant connect, resource to rman;

Grant succeeded.

 

SQL> grant recovery_catalog_owner to rman;

Grant succeeded.

 

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production

Version 19.3.0.0.0

 

  • COPY AND PAST THE TNSNAMES.ORA FILE IN BOTH SERVERS.

 

[oracle@xavier ~]$ cd $ORACLE_HOME/network/admin

[oracle@xavier admin]$ ls

listener.ora  samples  shrept.lst  sqlnet.ora  tnsnames.ora

 

[oracle@xavier admin]$ vi tnsnames.ora   >>>>> To save press esc:wq

[oracle@xavier admin]$

 

Or we may copy the file using cat command also…

 

[oracle@xavier admin]$ cat >> tnsnames.ora    >>>>>>    with ending CTL+D

 

[oracle@xavier admin]$ cat tnsnames.ora

VIYANI =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.33)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = viyani)

)

)

 

LISTENER_CATDB =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.21)(PORT = 1525))

CATDB =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.21)(PORT = 1525))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = catdb)

)

)

SOURCE (VIYANI): 192.168.1.33

 

[oracle@xavier ~]$ export ORACLE_SID=viyani

 

[oracle@xavier ~]$ rman target / catalog rman/rman123@catdb

 

Recovery Manager: Release 19.0.0.0.0 – Production on Sun Feb 5 18:34:24 2023

Version 19.3.0.0.0

 

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

 

connected to target database: VIYANI (DBID=1110182900)

connected to recovery catalog database

 

RMAN> create catalog;

recovery catalog created

 

RMAN> register database;

database registered in recovery catalog

starting full resync of recovery catalog

full resync complete

 

RMAN> resync catalog;

starting full resync of recovery catalog

full resync complete

 

RECOVER CORRUPTED CONTROLFILE WITH CATALOG DATABASE:

 

SQL> shutdown immediate;

ORA-00210: cannot open the specified control file

ORA-00202: control file: ‘/u01/app/oracle/oradata/VIYANI/controlfile/o1_mf_kvyjpwvp_.ctl’

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

 

SQL> shutdown abort;

ORACLE instance shut down.

 

SQL> startup nomount

ORACLE instance started.

 

Total System Global Area 1795159104 bytes

Fixed Size     8897600 bytes

Variable Size   469762048 bytes

Database Buffers  1308622848 bytes

Redo Buffers     7876608 bytes

 

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production

Version 19.3.0.0.0

 

[oracle@xavier ~]$ rman target / catalog rman/rman123@catdb

 

Recovery Manager: Release 19.0.0.0.0 – Production on Mon Feb 6 15:00:09 2023

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

 

connected to target database: VIYANI (not mounted)

connected to recovery catalog database

 

RMAN> restore controlfile;

Starting restore at 06-FEB-23

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=38 device type=DISK

 

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/VIYANI/autobackup/2023_01_31/cf_c-1110182900-20230206-01

channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/VIYANI/autobackup/2023_01_31/cf_c-1110182900-20230206-01 tag=TAG20230206T145443

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

output file name=/u01/app/oracle/oradata/VIYANI/controlfile/o1_mf_kvyjpwvp_.ctl

Finished restore at 06-FEB-23

 

RMAN> alter database mount;

released channel: ORA_DISK_1

Statement processed

 

RMAN> recover database;

Starting recover at 06-FEB-23

Starting implicit crosscheck backup at 06-FEB-23

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=44 device type=DISK

Crosschecked 15 objects

Finished implicit crosscheck backup at 06-FEB-23

 

Starting implicit crosscheck copy at 06-FEB-23

using channel ORA_DISK_1

Finished implicit crosscheck copy at 06-FEB-23

 

searching for all files in the recovery area

cataloging files…

cataloging done

 

List of Cataloged Files

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

File Name: /u01/app/oracle/fast_recovery_area/VIYANI/autobackup/2023_01_31/cf_c-1110182900-20230206-01

using channel ORA_DISK_1

 

starting media recovery

archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/oradata/VIYANI/onlinelog/o1_mf_2_kvyjpzg3_.log

archived log file name=/u01/app/oracle/oradata/VIYANI/onlinelog/o1_mf_2_kvyjpzg3_.log thread=1 sequence=2

media recovery complete, elapsed time: 00:00:00

Finished recover at 06-FEB-23

 

RMAN> alter database open resetlogs;

Statement processed

new incarnation of database registered in recovery catalog

starting full resync of recovery catalog

full resync complete

 

RMAN> exit

[oracle@xavier ~]$

[oracle@xavier ~]$ export ORACLE_SID=viyani

 

[oracle@xavier ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 19.0.0.0.0 – Production on Mon Feb 6 15:02:51 2023

Version 19.3.0.0.0

 

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

 

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production

Version 19.3.0.0.0

 

SQL> select name, open_mode from v$database;

NAME   OPEN_MODE

——— ——————–

VIYANI   READ WRITE

 

 

 

 

Related blogs

Dataguard manual switchover
Database Blog

Dataguard manual switchover

Introduction Oracle Data Guard plays a pivotal role in ensuring business continuity by maintaining synchronized standby databases that can seamlessly take over…

Naveen Kandasamy 1 min read
People and Culture Blog

Troubleshooting Query Plan Optimization Using Query Store

Introduction SQL Server Query Store is enabled to assist in monitoring query performance and identifying query plan regressions. Query Store captures a history…

Thetchina Moorthy Natarajan 6 min read
People and Culture Blog

When tempdb Won’t Shrink

Introduction In SQL Server, the tempdb system database can grow significantly due to heavy workloads, temporary objects, or large queries. Sometimes, even after…

Thetchina Moorthy Natarajan 1 min read