ORA-00210 - ORACLEAGENT BLOG

Description

In this article we are going to see how to restore a loss of controlfile using autobackup.

Step 1:- Check the RMAN configuration and controlfile autobackup feature is ON.

[oracle@localhost scripts]$ . ora19c.env
[oracle@localhost scripts]$ rman target /
Recovery Manager: Release 19.0.0.0.0 – Production on Sun Jan 31 12:56:58 2021
Version 19.10.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: INBAA (DBID=936024966)

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name INBAA are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON; # 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/19.0.0/dbs/snapcf_inbaa.f’; # default

RMAN>

Step 2:- Simulate a failure to remove the controlfile when the database is running.

[oracle@localhost scripts]$ . ora19c.env
[oracle@localhost scripts]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 – Production on Sun Jan 31 12:55:02 2021
Version 19.10.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area 3019895280 bytes
Fixed Size 8901104 bytes
Variable Size 738197504 bytes
Database Buffers 2264924160 bytes
Redo Buffers 7872512 bytes
Database mounted.
Database opened.
SQL> select open_mode,name from v$database;

OPEN_MODE NAME

READ WRITE INBAA

SQL> select name from v$controlfile;

SQL> !
[oracle@localhost scripts]$ cd
[oracle@localhost ~]$ cd /u01/app/oracle/oradata/INBAA/
[oracle@localhost INBAA]$ ls
control01.ctl redo01.log redo02.log redo03.log sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf
[oracle@localhost INBAA]$ rm -rf control01.ctl
[oracle@localhost INBAA]$ cd
[oracle@localhost ~]$ cd /u01/app/oracle/recovery_area/INBAA/
[oracle@localhost INBAA]$ ls
archivelog autobackup backupset control02.ctl onlinelog
[oracle@localhost INBAA]$ rm -rf control02.ctl

[oracle@localhost INBAA]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 – Production on Sun Jan 31 13:09:30 2021
Version 19.10.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.10.0.0.0

SQL>
SQL> select tablespace_name from dba_data_files;
select tablespace_name from dba_data_files
*
ERROR at line 1:
ORA-00210: cannot open the specified control file
ORA-00202: control file: ‘/u01/app/oracle/oradata/INBAA/control01.ctl’
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL>
SQL> select status from v$instance;

STATUS

OPEN
SQL>
SQL> shut immediate
ORA-00210: cannot open the specified control file
ORA-00202: control file: ‘/u01/app/oracle/oradata/INBAA/control01.ctl’
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> shut abort
ORACLE instance shut down.
SQL>

Step 3:- Keep the database in NOMOUNT stage and restore the controlfile

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 3019895280 bytes
Fixed Size 8901104 bytes
Variable Size 738197504 bytes
Database Buffers 2264924160 bytes
Redo Buffers 7872512 bytes
SQL>
Step 4:- Since we are not using a RMAN catalog we need to set the DBID

[oracle@localhost scripts]$ . ora19c.env
[oracle@localhost scripts]$ rman target /

Recovery Manager: Release 19.0.0.0.0 – Production on Sun Jan 31 13:16:07 2021
Version 19.10.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: INBAA (not mounted)

RMAN> set dbid=936024966;

executing command: SET DBID

RMAN> restore controlfile from autobackup;

Starting restore at 31-JAN-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=38 device type=DISK

recovery area destination: /u01/app/oracle/recovery_area
database name (or database unique name) used for search: INBAA
channel ORA_DISK_1: AUTOBACKUP /u01/app/oracle/recovery_area/INBAA/autobackup/2021_01_28/o1_mf_s_1063052742_j15n3h5b_.bkp found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20210131
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20210130
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20210129
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20210128
channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/app/oracle/recovery_area/INBAA/autobackup/2021_01_28/o1_mf_s_1063052742_j15n3h5b_.bkp
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/u01/app/oracle/oradata/INBAA/control01.ctl
output file name=/u01/app/oracle/recovery_area/INBAA/control02.ctl
Finished restore at 31-JAN-21
RMAN>

Step 5:- Mount and recover the database

RMAN> alter database mount;

released channel: ORA_DISK_1
Statement processed
RMAN> 
RMAN> recover database;

Starting recover at 31-JAN-21
Starting implicit crosscheck backup at 31-JAN-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=44 device type=DISK
Crosschecked 10 objects
Finished implicit crosscheck backup at 31-JAN-21
Starting implicit crosscheck copy at 31-JAN-21
using channel ORA_DISK_1
Finished implicit crosscheck copy at 31-JAN-21
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/app/oracle/recovery_area/INBAA/autobackup/2021_01_28/o1_mf_s_1063052742_j15n3h5b_.bkp
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/oradata/INBAA/redo01.log
archived log file name=/u01/app/oracle/oradata/INBAA/redo01.log thread=1 sequence=1
media recovery complete, elapsed time: 00:00:01
Finished recover at 31-JAN-21
RMAN>

Step 7:- Open the database using resetlogs option

RMAN> alter database open resetlogs;

Statement processed

RMAN>

SQL> startup nomount;
ORACLE instance started.
Total System Global Area            3019895280 bytes
Fixed Size                                       8901104 bytes
Variable Size                                 738197504 bytes
Database Buffers                         2264924160 bytes
Redo Buffers                                 7872512 bytes
SQL> select open_mode,name from v$database;

Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

Tag Archives: ORA-00210

STATUS