Create lost control file in Oracle

Create lost control file in Oracle

It’s seems having error in starting the database. Some problem seems to be found in Control file during startup of database.
Database is started upto no mount stage but during mount it will generate errors: (Both errors are of different Server:)

ORA-00600: internal error code, arguments: [2131], [9], [8], [], [], [], [], [], [], [], [], []

ORA-00205: error in identifying control file, check alert log for more info

Solution
We decided to create an new control file for fixing the errors getting in startup the database.
Note: Before creating new control file take backup of oradata folder. It may caused problem in datafiles.

Following are the steps to Create New Control File

Sample of Trace of control file :
Control File

1. If you have trace file generated then its good otherwise edit the following create control file command:
Note: Edit the control file command with option database Name, redo files , datafiles location etc.

--If Database is shutdown normal and you have redo logs files then use noresetlogs option
CREATE CONTROLFILE REUSE DATABASE "XE" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 'D:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ONLINELOG\O1_MF_1_DX8Z3980_.LOG' SIZE 50M BLOCKSIZE 512,
GROUP 2 'D:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ONLINELOG\O1_MF_2_DX8Z3C51_.LOG' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'D:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF',
'D:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSAUX.DBF',
'D:\ORACLEXE\APP\ORACLE\ORADATA\XE\UNDOTBS1.DBF',
'D:\ORACLEXE\APP\ORACLE\ORADATA\XE\USERS.DBF'
;

OR
--If you donot have redologs then use following command:
CREATE CONTROLFILE REUSE DATABASE "XE" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 'D:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ONLINELOG\O1_MF_1_DX8Z3980_.LOG' SIZE 50M BLOCKSIZE 512,
GROUP 2 'D:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ONLINELOG\O1_MF_2_DX8Z3C51_.LOG' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'D:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF',
'D:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSAUX.DBF',
'D:\ORACLEXE\APP\ORACLE\ORADATA\XE\UNDOTBS1.DBF',
'D:\ORACLEXE\APP\ORACLE\ORADATA\XE\USERS.DBF'
;

2. Rename the already existing control file and Start the database in No mount state.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size 2260048 bytes
Variable Size 759169968 bytes
Database Buffers 301989888 bytes
Redo Buffers 5517312 bytes

3. Use the above edit control file command for create new control file.

SQL> CREATE CONTROLFILE REUSE DATABASE "XE" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 'D:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ONLINELOG\O1_MF_1_DX8Z3980_.LOG' SIZE 50M BLOCKSIZE 512,
GROUP 2 'D:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ONLINELOG\O1_MF_2_DX8Z3C51_.LOG' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'D:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF',
'D:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSAUX.DBF',
'D:\ORACLEXE\APP\ORACLE\ORADATA\XE\UNDOTBS1.DBF',
'D:\ORACLEXE\APP\ORACLE\ORADATA\XE\USERS.DBF'
;

Control file created.

4. If you have trace file of control then you found following statement configure RMAN setting.
Note: Skip this step if donot have control file trace.

SQL> VARIABLE RECNO NUMBER;
SQL> EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('RETENTION POLICY','TO REDUNDANCY 2');

PL/SQL procedure successfully completed.

SQL> VARIABLE RECNO NUMBER;
SQL> EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','OFF');

PL/SQL procedure successfully completed.

5. Recover database is required if database is not shut-down normal.

-- if the last shut-down was not normal or immediate.
RECOVER DATABASE
OR
-- if the last shut-down was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE

6. Open the database normally or resetlog depend upon the Redo log file you lost or have it.

-- Database can now be opened normally. (If redo log file is good)
ALTER DATABASE OPEN;

-- Database can now be opened zeroing the online logs.( if redo log file is not good.)
ALTER DATABASE OPEN RESETLOGS;

7. Create temp file as needed.

ALTER TABLESPACE TEMP ADD TEMPFILE 'D:\ORACLEXE\APP\ORACLE\ORADATA\XE\TEMP.DBF'
SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.