Oracle Control File Recreation

Have you lost or deleted a control file from an Oracle database? We’re here to help! Follow the steps below to recreate the control file.

1. If you try to take the backup of control file using the below command, when the control files are deleted you will get the following error. Instead, get the control file copy from any other database and edit data, redo file and character set information according to your environment.

alter database backup control file to trace as ‘/tmp/Alpha_19012016.ctl’;

SQL> alter database backup control file to trace as ‘/tmp/Alpha_19012016.ctl’;
alter database backup control file to trace as ‘/tmp/Alpha_19012016.ctl’
*
ERROR at line 1:
ORA-00210: cannot open the specified control file
ORA-00202: control file: ‘/u02/Alpha/control01.ctl’
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

2. Alter system switch logfile; (You will get errors in alert log as there is no control file)

ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Sun Jan 17 13:00:01 2016
Errors in file /u01/diag/rdbms/Alpha/Alpha/trace/Alpha_m001_4599.trc:
ORA-00210: cannot open the specified control file
ORA-00202: control file: ‘/u02/Alpha/control01.ctl’
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

3. Check the sequence number for archives before database shutdown.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u03/arc
Oldest online log sequence     5794
Next log sequence to archive   5796
Current log sequence           5796

4. Shut abort.

5. Update control file trace file created in step 1 from any other database and keep only the CREATE_CONTROL_FILE block. Remove the rest of the lines and also remove any blank lines.

Sample listed below.

Note 1: We need to make sure datafiles, redo and character set are listed as per current database where control file was deleted.

Note 2: Make sure to use NORESETLOGS from control file. RESETLOGS option will cause data loss.

[oracle@DB tmp]$ cat Alpha_19012016.ctl
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE “Alpha” NORESETLOGS  ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 63
MAXLOGHISTORY 1168
LOGFILE
GROUP 2 ‘/u04/redo/Alpha_21.rdo
‘  SIZE 500M BLOCKSIZE 512,
GROUP 4 ‘/u04/redo/Alpha_41.rdo’  SIZE 500M BLOCKSIZE 512,
GROUP 5 ‘/u04/redo/Alpha_51.rdo’  SIZE 500M BLOCKSIZE 512
DATAFILE
‘/u02/Alpha/system01.dbf’,
‘/u02/Alpha/sysaux01.dbf’,
‘/u02/Alpha/undotbs01.dbf’,
‘/u02/Alpha/users01.dbf’,
‘/u02/Alpha/Alpha/datafile/o1_mf_venus_mars_9s1djzjx_.dbf’,
‘/u02/Alpha/Alpha/datafile/o1_mf_venus_mars_9s1dk3s3_.dbf’,
‘/u02/Alpha/Alpha/datafile/o1_mf_venus_mars_9s1dk7bn_.dbf’,
‘/u02/Alpha/Alpha/datafile/o1_mf_venus_mars_9s1dkb9z_.dbf’,
‘/u02/Alpha/Alpha/datafile/o1_mf_venus_mars_9s1dkg7x_.dbf’,
‘/u02/Alpha/Alpha/datafile/o1_mf_venus_mars_9s1dklo5_.dbf’,
‘/u02/Alpha/Alpha/datafile/o1_mf_venus_mars_9s1dkpcm_.dbf’,
CHARACTER SET WE8MSWIN1252;

6. Keep command on temp file creation from control file trace. These will be created after the database is opened normally.

ALTER TABLESPACE TEMP ADD TEMPFILE ‘/u02/Alpha/temp01.dbf’;

ALTER TABLESPACE USERS_TEMP ADD TEMPFILE ‘/u02/Alpha/user_temp_01.dbf’;

7. Run CREATE_CONTROLFILE command from SQL prompt. It will mount the database.

sqlplus / as sysdba(database starts in mount mode)

[oracle@database tmp]$ vi Alpha_19012016.ctl

[oracle@database tmp]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 19 20:51:59 2016

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

Connected to an idle instance.

SQL> @Alpha_19012016.ctl
ORACLE instance started.

Total System Global Area 8551575552 bytes
Fixed Size                                   2270360 bytes
Variable Size                       4211084136 bytes
Database Buffers               4328521728 bytes
Redo Buffers                             9699328 bytes

Control file created.

8. From SQL prompt only run : recover database;

SQL> recover database;
Media recovery complete.

9. Alert database open

SQL> alter database open;

Database altered.

10. Check the sequence number for archives again and compare with step #3 – It should match.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u03/arc
Oldest online log sequence     5794
Next log sequence to archive   5796
Current log sequence           5796

11. Switch logfiles three (3) times to make sure the database is running properly. Be sure to check the alert log.

alter system switch logfile;

12. Recreate temp files at this point from step #6.

ALTER TABLESPACE TEMP ADD TEMPFILE ‘/u02/Alpha/temp01.dbf’
SIZE 32767M REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
ALTER TABLESPACE USERS_TEMP ADD TEMPFILE ‘/u02/Alpha/user_temp_01.dbf’
SIZE 1500M REUSE AUTOEXTEND OFF;

Want to learn more tips from our Managed Service experts? Click here.

Related Posts

• Proactive Maintenance of Your Oracle Database – Key Considerations
• Oracle RMAN Myth Busting
• Oracle RMAN Best Practices
• Boost Oracle Performance with a Comprehensive Health Check
• Top 3 Communication Skills for IT Support Providers