RMAN Practices - Controlfile restore Scenario
In this article we will be covering Controlfile recovery Scenario in RAC database.

Loss Only One of ControlFile when there are multiplexed copy of Controlfile.

In this scenario we assume that we are not having any AUTOBACKUP and there were 2 controlfile as below.
we deleted 1 controlfile intentionally and then restored it using RMAN as below.

Current status of Controlfile

SQL> show parameter contro

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------

control_files			 string	 +DATA/TNT/CONTROLFILE/current.286.1119111973, 
                                 +FRA/TNT/CONTRLFILE/current.734.1115903105
SQL> 
Remove 1 controlfile as below.

grid@racsetn1 ~]$ srvctl stop database -d TNT

[oracle@racsetn1 ~]$ su - grid

[grid@racsetn1 ~]$ asmcmd
ASMCMD> cd FRA/TNT/CONTROLFILE/
ASMCMD> rm Current.734.1115903105
ASMCMD> exit

[grid@racsetn1 ~]$ logout

start database in NOMOUNT on node1 as below.

[oracle@racsetn1 ~]$ sqlplus "/as sysdba"

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 2751463160 bytes
Fixed Size		    8900344 bytes
Variable Size		  637534208 bytes
Database Buffers	 2097152000 bytes
Redo Buffers		    7876608 bytes

SQL> show parameter control

NAME				     TYPE	      VALUE
------------------------------------ ----------- ------------------------------
control_files			   string	 +DATA/TNT/CONTROLFILE/current.286.1119111973, 
                                     +FRA/TNT/CONTROLFILE/current.734.1115903105

Let we restore controlfile from existing controlfile as below.

[oracle@racsetn1 ~]$ rman target /

connected to target database: TNT (not mounted)

RMAN> restore controlfile from '+DATA/TNT/CONTROLFILE/current.286.1119111973';

Starting restore at 26-OCT-22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=17 instance=TNT1 device type=DISK
channel ORA_DISK_1: copied control file copy
output file name=+DATA/TNT/CONTROLFILE/current.286.1119111973
output file name=+FRA/TNT/CONTROLFILE/current.734.1119113919
Finished restore at 26-OCT-22
RMAN> exit

Recovery Manager complete.
[oracle@racsetn1 ~]$ 

Verify restored File from grid user as below.

[oracle@racsetn1 ~]$ su - grid
[grid@racsetn1 ~]$ asmcmd
ASMCMD> cd +FRA/TNT/CONTROLFILE/
ASMCMD> ls -lrt 
WARNING:option 'r' is deprecated for 'ls'
please use 'reverse'

Type         Redund  Striped  Time             Sys  Name
CONTROLFILE  UNPROT  FINE     OCT 26 16:00:00  Y    current.734.1119113919
ASMCMD> exit

Let we start Database now as below.

[oracle@racsetn1 ~]$ sqlplus "/as sysdba"

SQL> alter database mount;
Database altered.

[grid@racsetn1 ~]$ srvctl start database -d TNT

Verify if database properly UP.

SQL> show parameter control

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
control_files			     string	 +DATA/TNT/CONTROLFILE/current.286.1119111973, 
                                      +FRA/TNT/CONTROLFILE/current.734.1119113919
SQL> @name

NAME	  OPEN_MODE	       DATABASE_ROLE	PROTECTION_MODE
--------- -------------------- ---------------- --------------------
TNT	  READ WRITE	       PRIMARY		MAXIMUM PERFORMANCE
TNT	  READ WRITE	       PRIMARY		MAXIMUM PERFORMANCE

Hence this practice completed now. !!

Loss of ALL CONTROLFILE when AUTOBACKUP is ON .

Check current status of Controlfile as below.

SQL> show parameter control

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------

control_files			     string	 +DATA/TNT/CONTROLFILE/current. 286.1119111973,
                                      +FRA/TNT/CONTROLFILE/current.734.1115903105

Let we remove all Controlfile as below.

[grid@racsetn1 ~]$ srvctl stop database -d TNT
 
[grid@racsetn1 ~]$ asmcmd
ASMCMD>  cd +DATA/TNT/CONTROLFILE/
ASMCMD> ls 
current.286.1119115365
ASMCMD> rm current.286.1119115365
ASMCMD> cd +FRA/TNT/CONTROLFILE/
ASMCMD> ls 
current.734.1119115365
ASMCMD> rm current.734.1119115365
ASMCMD> exit
[grid@racsetn1 ~]$ logout

At this stage if we try to start DB we will get ERROR as below.

[oracle@racsetn1 ~]$ sqlplus "/as sysdba"

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 2751463160 bytes
Fixed Size		    8900344 bytes
Variable Size		  637534208 bytes
Database Buffers	 2097152000 bytes
Redo Buffers		    7876608 bytes
ORA-00205: error in identifying control file, check alert log for more info


Let we RESTORE Controlfile from AUTOBACKUP as below.

[oracle@racsetn1 ~]$ rman target /

connected to target database: TNT (not mounted)

RMAN> set DBID=1427195521;

executing command: SET DBID

RMAN> restore controlfile from AUTOBACKUP;

Starting restore at 26-OCT-22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=269 instance=TNT1 device type=DISK

recovery area destination: +FRA
database name (or database unique name) used for search: TNT
channel ORA_DISK_1: AUTOBACKUP +FRA/TNT/AUTOBACKUP/2022_10_26/s_1119112291.771.1119112293 found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20221026
channel ORA_DISK_1: restoring control file from AUTOBACKUP +FRA/TNT/AUTOBACKUP/2022_10_26/s_1119112291.771.1119112293
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=+DATA/TNT/CONTROLFILE/current.286.1119115455
output file name=+FRA/TNT/CONTROLFILE/current.734.1119115455
Finished restore at 26-OCT-22

RMAN> exit
Recovery Manager complete.

Let we Mount database and start Recovery as below.

[oracle@racsetn1 ~]$ sqlplus "/as sysdba"

SQL> alter database mount;

Database altered.

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

Let we RECOVER database as below

[oracle@racsetn1 ~]$ rman target /

connected to target database: TNT (DBID=1427195521, not open)

RMAN> recover database;

Starting recover at 26-OCT-22
Starting implicit crosscheck backup at 26-OCT-22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
allocated channel: ORA_DISK_2
allocated channel: ORA_DISK_3
Crosschecked 5 objects
Crosschecked 5 objects
Crosschecked 5 objects
Finished implicit crosscheck backup at 26-OCT-22

Starting implicit crosscheck copy at 26-OCT-22
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
Crosschecked 1 objects
Finished implicit crosscheck copy at 26-OCT-22

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: +FRA/TNT/ARCHIVELOG/2022_10_26/thread_1_seq_1.767.1119113777
File Name: +FRA/TNT/ARCHIVELOG/2022_10_26/thread_1_seq_2.743.1119114991
File Name: +FRA/TNT/AUTOBACKUP/2022_10_26/s_1119112291.771.1119112293

using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3

starting media recovery

archived log for thread 1 with sequence 1 is already on disk as file +FRA/TNT/ARCHIVELOG/2022_10_26/thread_1_seq_1.767.1119113777
archived log for thread 1 with sequence 2 is already on disk as file +FRA/TNT/ARCHIVELOG/2022_10_26/thread_1_seq_2.743.1119114991
archived log for thread 2 with sequence 1 is already on disk as file +DATA/TNT/ONLINELOG/group_3.291.1115904155
archived log file name=+FRA/TNT/ARCHIVELOG/2022_10_26/thread_1_seq_1.767.1119113777 thread=1 sequence=1
archived log file name=+DATA/TNT/ONLINELOG/group_3.291.1115904155 thread=2 sequence=1
archived log file name=+FRA/TNT/ARCHIVELOG/2022_10_26/thread_1_seq_2.743.1119114991 thread=1 sequence=2
Finished recover at 26-OCT-22

RMAN> exit               

Now we need to use RESETLOG option to open database on node-1 only,

[oracle@racsetn1 ~]$ sqlplus "/as sysdba"

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 2751463160 bytes
Fixed Size		    8900344 bytes
Variable Size		  637534208 bytes
Database Buffers	 2097152000 bytes
Redo Buffers		    7876608 bytes
Database mounted.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;

Database altered.

SQL> @name

NAME	  OPEN_MODE	       DATABASE_ROLE	PROTECTION_MODE
--------- -------------------- ---------------- --------------------
TNT	  READ WRITE	       PRIMARY		MAXIMUM PERFORMANCE

SQL> exit

Now move to Second node and start database as below.

[oracle@racsetn2 ~]$ export ORACLE_SID=TNT2
[oracle@racsetn2 ~]$ sqlplus "/as sysdba"
Connected to an idle instance.

SQL> STARTUP
ORACLE instance started.

Total System Global Area 2751463160 bytes
Fixed Size		    8900344 bytes
Variable Size		  654311424 bytes
Database Buffers	 2080374784 bytes
Redo Buffers		    7876608 bytes
Database mounted.
Database opened.
SQL> exit

Check DB status if everything is fine,

[oracle@racsetn1 ~]$ sqlplus "/as sysdba"

SQL> @name

NAME	  OPEN_MODE	       DATABASE_ROLE	PROTECTION_MODE
--------- -------------------- ---------------- --------------------
TNT	  READ WRITE	       PRIMARY		MAXIMUM PERFORMANCE
TNT	  READ WRITE	       PRIMARY		MAXIMUM PERFORMANCE

SQL> 

Restore CONTROLFILE from Manual Backed Up location (NO AUTOBACKUP).

Let we REST AUTOBACKUP of controlfile as below.

RMAN> show all;
RMAN configuration parameters for database with db_unique_name TNT are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 3 BACKUP TYPE TO BACKUPSET;
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;
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 '/ora_home/app/19c/db/dbs/snapcf_TNT1.f'; # default

RMAN> configure CONTROLFILE AUTOBACKUP OFF;

old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
new RMAN configuration parameters are successfully stored

Let we take backup of controlfile as user defined location as below.

RMAN> backup current controlfile format '/ora_home/bkp/TNT/CTRL_%d_%u_%s';

Starting backup at 26-OCT-22
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
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 26-OCT-22
channel ORA_DISK_1: finished piece 1 at 26-OCT-22
piece handle=/ora_home/bkp/TNT/CTRL_TNT_c31b8no5_387 tag=TAG20221026T175101 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 26-OCT-22

Recovery Manager complete.
 
Start Database in Mount Mode and start recovery as below.

[oracle@racsetn1 TNT]$ sqlplus "/as sysdba"

SQL> alter database mount;

Database altered.

SQL> exit

[oracle@racsetn1 TNT]$ rman target /

connected to target database: TNT (DBID=1427195521, not open)

RMAN> recover database;

Starting recover at 26-OCT-22
Starting implicit crosscheck backup at 26-OCT-22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
allocated channel: ORA_DISK_2
allocated channel: ORA_DISK_3
Crosschecked 6 objects
Crosschecked 6 objects
Crosschecked 7 objects
Finished implicit crosscheck backup at 26-OCT-22

Starting implicit crosscheck copy at 26-OCT-22
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
Crosschecked 1 objects
Finished implicit crosscheck copy at 26-OCT-22

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: +FRA/TNT/ARCHIVELOG/2022_10_26/thread_1_seq_2.786.1119117163

using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3

starting media recovery

archived log for thread 1 with sequence 2 is already on disk as file +FRA/TNT/ARCHIVELOG/2022_10_26/thread_1_seq_2.786.1119117163
archived log for thread 2 with sequence 4 is already on disk as file +DATA/TNT/ONLINELOG/group_4.292.1115904165
archived log file name=+FRA/TNT/ARCHIVELOG/2022_10_26/thread_1_seq_2.786.1119117163 thread=1 sequence=2
archived log file name=+DATA/TNT/ONLINELOG/group_4.292.1115904165 thread=2 sequence=0
creating datafile file number=9 name=+DATA/TNT/DATAFILE/ts_nolog.296.1119117123
archived log file name=+FRA/TNT/ARCHIVELOG/2022_10_26/thread_1_seq_2.786.1119117163 thread=1 sequence=2
archived log file name=+DATA/TNT/ONLINELOG/group_4.292.1115904165 thread=2 sequence=0
Finished recover at 26-OCT-22

RMAN> exit
Recovery Manager complete.
[oracle@racsetn1 TNT]$ 

Open database in RESETLOGS as below

[oracle@racsetn1 TNT]$ sqlplus "/as sysdba"

SQL> alter database open resetlogs;

Database altered.

SQL> @name

NAME	  OPEN_MODE	       DATABASE_ROLE	PROTECTION_MODE
--------- -------------------- ---------------- --------------------
TNT	  READ WRITE	       PRIMARY		MAXIMUM PERFORMANCE

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

Open database on Second Node as below

[oracle@racsetn2 ~]$ sqlplus "/as sysdba"

SQL> startup;
ORACLE instance started.

Total System Global Area 2751463160 bytes
Fixed Size		    8900344 bytes
Variable Size		  654311424 bytes
Database Buffers	 2080374784 bytes
Redo Buffers		    7876608 bytes
Database mounted.
Database opened.
SQL>