Recovering from loss of all control files

~ Antoine

“Sometimes shit happens”. That’s what I thought when I realized that I had lost all the control files of one of my test databases. Of course, I had no backup because “it’s just a test database”…

Fortunately, I only lost my control files but no datafiles or redo log files. This is a very important point because if you are in this situation, all is not lost!

So today, I will show you how you can recover your Oracle database after the loss of all control files.

Context

For the purpose of this article, I reproduced the situation on my “ORCL” database (Oracle 12.2) on my Linux lab “linuxlabo01”.

According to Oracle documentation, the commands should work on Oracle 11g and onward.

Let’s break my lab database!

Nothing complicated here, I’m just going to delete the control files and try to shutdown and restart my database.

[oracle@linuxlab01 ~]$ . oraenv
ORACLE_SID = [ORCL] ? ORCL
The Oracle base remains unchanged with value /u01/app/oracle

[oracle@linuxlab01 ~]$ sqlplus / as sysdba
[...]
SQL> show parameter control


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /u02/app/oracle/oradata/ORCL/c
                                                 ontrolfile/o1_mf_g14zp9ml_.ctl
                                                 , /u02/app/oracle/fast_recover
                                                 y_area/ORCL/controlfile/o1_mf_
                                                 g14zp9ov_.ctl

[oracle@linuxlab01 ~]$ rm -f /u02/app/oracle/oradata/ORCL/controlfile/o1_mf_g14zp9ml_.ctl /u02/app/oracle/fast_recovery_area/ORCL/controlfile/o1_mf_g14zp9ov_.ctl
[oracle@linuxlab01 ~]$ ls -lrt /u02/app/oracle/oradata/ORCL/controlfile/o1_mf_g14zp9ml_.ctl /u02/app/oracle/fast_recovery_area/ORCL/controlfile/o1_mf_g14zp9ov_.ctl
ls: cannot access /u02/app/oracle/oradata/ORCL/controlfile/o1_mf_g14zp9ml_.ctl: No such file or directory
ls: cannot access /u02/app/oracle/fast_recovery_area/ORCL/controlfile/o1_mf_g14zp9ov_.ctl: No such file or directory

Now, I get the following error when I try to shutdown the database properly:

SQL> shut immediate;
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u02/app/oracle/oradata/ORCL/controlfile/o1_mf_g14zp9ml_.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

Of course, I can shutdown the database with “abort”:

SQL> shut abort;
ORACLE instance shut down.

But then, the following error message appears when I try to restart it:

SQL> startup;
ORACLE instance started.

Total System Global Area  578813952 bytes
Fixed Size                  8623256 bytes
Variable Size             293604200 bytes
Database Buffers          272629760 bytes
Redo Buffers                3956736 bytes
ORA-00205: error in identifying control file, check alert log for more info

And, in the file alert_ORCL.log:

[oracle@linuxlab01 ~]$ cd /u01/app/oracle/diag/rdbms/orcl/ORCL/trace
[oracle@linuxlab01 trace]$ view alert_ORCL.log
[...]
2018-12-13T17:59:47.532493+01:00
TMON started with pid=32, OS id=31678
2018-12-13T17:59:47.610666+01:00
ALTER DATABASE   MOUNT
2018-12-13T17:59:47.630143+01:00
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u02/app/oracle/fast_recovery_area/ORCL/controlfile/o1_mf_g14zp9ov_.ctl'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u02/app/oracle/oradata/ORCL/controlfile/o1_mf_g14zp9ml_.ctl'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7

Given that I don’t have any remaining control file or backup, I will have to recreate it manually!

Gather needed information

In order to create a new control file from scratch, you need several information.

Database name

If you don’t know the database name, you can find it in your spfile or in your alert.log file for example.

“Log mode” (ARCHIVELOG / NOARCHIVELOG)

This information is stored in the control file, so there is no reliable way to obtain it in our situation.

However, there are other ways to derive this information:

  • List the files contain in the directory specified in the “log_archive_dest*” or “db_recovery_file_dest” parameters. If you don’t find any file, it is likely your database was not in ARCHIVELOG mode.
  • List the “*arc*.trc” files in your trace directory. If you don’t find any recent file, it is likely your database was not in ARCHIVELOG mode:
[oracle@linuxlab01 ~]$ cd /u01/app/oracle/diag/rdbms/orcl/ORCL/trace
[oracle@linuxlab01 trace]$ ls -lrt *arc*
ls: cannot access *arc*: No such file or directory

Location of all datafiles

You need to get the list of all datafiles of your database (not tempfiles). It is important not to miss any datafile, as omitting a file may result in loss of data from this file or loss of access to the entire database!

On my lab server, it is really simple because I use OMF and all datafiles are created in the same directory:

[oracle@linuxlab01 ~]$ ls -lrt /u02/app/oracle/oradata/ORCL/datafile/
total 1572944
-rw-r-----. 1 oracle oinstall  33562624 Dec 13 16:50 o1_mf_temp_g14zrkjl_.tmp
-rw-r-----. 1 oracle oinstall 838868992 Dec 13 17:58 o1_mf_system_g14zhgy8_.dbf
-rw-r-----. 1 oracle oinstall 104865792 Dec 13 17:58 o1_mf_idx1_g1507zx2_.dbf
-rw-r-----. 1 oracle oinstall 482353152 Dec 13 17:58 o1_mf_sysaux_g14zl4hf_.dbf
-rw-r-----. 1 oracle oinstall  73408512 Dec 13 17:58 o1_mf_undotbs1_g14znswh_.dbf
-rw-r-----. 1 oracle oinstall 104865792 Dec 13 17:58 o1_mf_data1_g1507m18_.dbf
-rw-r-----. 1 oracle oinstall   5251072 Dec 13 17:58 o1_mf_users_g14zo16o_.dbf

Location and size of all online redo log files

As for datafiles, you need the complete list of all your online log files and know which group they belong to.

Again, it’s really easy on my lab server because I’m using OMF and the group number is present in the file name:

[oracle@linuxlab01 ~]$ ls -lrt /u02/app/oracle/oradata/ORCL/onlinelog/
total 614412
-rw-r-----. 1 oracle oinstall 209715712 Dec 13 17:04 o1_mf_3_g14zqb3c_.log
-rw-r-----. 1 oracle oinstall 209715712 Dec 13 17:58 o1_mf_1_g14zpdhs_.log
-rw-r-----. 1 oracle oinstall 209715712 Dec 13 17:59 o1_mf_2_g14zpdkn_.log

[oracle@linuxlab01 ~]$ ls -lrt /u02/app/oracle/fast_recovery_area/ORCL/onlinelog/
total 614412
-rw-r-----. 1 oracle oinstall 209715712 Dec 13 17:04 o1_mf_3_g14zqswm_.log
-rw-r-----. 1 oracle oinstall 209715712 Dec 13 17:58 o1_mf_1_g14zpsvf_.log
-rw-r-----. 1 oracle oinstall 209715712 Dec 13 17:59 o1_mf_2_g14zppp9_.log

The size can be obtained with the following command (I use “awk” to round the size because Oracle adds an extra overhead to the redo log file. For example, if you create a 200M log file, its size on the filesystem is of 200M + 4k).

[oracle@linuxlab01 ~]$ du -sk /u02/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_3_g14zqswm_.log |awk '{print int($0/1024)}'
200

Database character set

If you don’t know your database character set, you can find it in your alert.log file (it is written at startup):

[oracle@linuxlab01 ~]$ cd /u01/app/oracle/diag/rdbms/orcl/ORCL/trace
[oracle@linuxlab01 trace]$ grep "Database Characterset is" alert_ORCL.log
Database Characterset is AL32UTF8

Other information

Other information can be derived from what you found in the previous steps:

  • MAXLOGFILES: the maximum number of online redo log file groups that can be created for the database.
  • MAXLOGMEMBERS: the maximum number of members, or identical copies, for a redo log file group.
  • MAXDATAFILES:  the initial sizing of the datafiles section of the control file (use the number of data files you have found before. This is not a hard limit! If you want to add more data files in the future, the value will automatically be expanded)
  • MAXINSTANCES: the maximum number of instances that can simultaneously have the database mounted and open (for RAC environment).

If you do not know what value to assign to these settings, take a larger one! The only drawback is that the control file will be larger than necessary.

Now, we have everything we need to create a new control file!

“Create control file” script

Now we need to generate the SQL script that will create our new control file. You need to adapt the script with what you found in the previous paragraphs (database name, list of datafiles, log mode, charset, …).

For my database, it looks like this:

STARTUP FORCE NOMOUNT;
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 2
LOGFILE
  GROUP 1 (
    '/u02/app/oracle/oradata/ORCL/onlinelog/o1_mf_1_g14zpdhs_.log',
    '/u02/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_1_g14zpsvf_.log'
  ) SIZE 200M,
  GROUP 2 (
    '/u02/app/oracle/oradata/ORCL/onlinelog/o1_mf_2_g14zpdkn_.log',
    '/u02/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_2_g14zppp9_.log'
  ) SIZE 200M,
  GROUP 3 (
    '/u02/app/oracle/oradata/ORCL/onlinelog/o1_mf_3_g14zqb3c_.log',
    '/u02/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_3_g14zqswm_.log'
  ) SIZE 200M
DATAFILE
  '/u02/app/oracle/oradata/ORCL/datafile/o1_mf_system_g14zhgy8_.dbf',
  '/u02/app/oracle/oradata/ORCL/datafile/o1_mf_idx1_g1507zx2_.dbf',
  '/u02/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_g14zl4hf_.dbf',
  '/u02/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_g14znswh_.dbf',
  '/u02/app/oracle/oradata/ORCL/datafile/o1_mf_data1_g1507m18_.dbf',
  '/u02/app/oracle/oradata/ORCL/datafile/o1_mf_users_g14zo16o_.dbf'
CHARACTER SET AL32UTF8;
RECOVER DATABASE;
ALTER DATABASE OPEN;

Put the script in a file, we will execute it from SQLPlus in the next step.

Review the script several times, you will not have a second chance if you miss a datafile…

Script execution

It’s time to cross your fingers and hope that it works!

oracle@linuxlab01 ~]$ sqlplus / as sysdba
[...]
SQL> @controlfile.sql
ORACLE instance started.

Total System Global Area  578813952 bytes
Fixed Size                  8623256 bytes
Variable Size             293604200 bytes
Database Buffers          272629760 bytes
Redo Buffers                3956736 bytes

Control file created.

Media recovery complete.

Database altered.

It looks good! Let’s do some checks:

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> select * from v$recover_file;

no rows selected

I will not make a complete list, but make all possible checks! Check your datafiles, your tablespaces, your data…

Post creation

Here is a non-exhaustive list of things to do after recreating your control file and successfully opening your database:

  • Add tempfiles to your temporary tablespace(s). If you query the “dba_temp_files” view, you will find that all tempfiles are missing.. Don’t forget your PDBs if you are using Oracle multitenant!
  • Recreate the standby redolog files if needed
  • and of course… backup your database and your control file!

I hope you find this articled useful. Stay tuned for more DBA stuff!

Published by Antoine

Database administrator in Montpellier (France), creator of "mydbaworld.com".

Leave a Reply

Your email address will not be published. Required fields are marked *