Tuesday, March 15, 2011

CREATE CONTROL FILE MANUALLY ......AS YOU HAVE LOST ALL BACKUP OF IT




SQL> startup
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.

Total System Global Area  209715200 bytes
Fixed Size                  1248164 bytes
Variable Size              79692892 bytes
Database Buffers          125829120 bytes
Redo Buffers                2945024 bytes
ORA-00205: error in identifying control file, check alert log for more info

..Above error shows that controlfile is missing.


No worry you can create the same:-)


Shut your instance..

SQL> shut immediate;
ORA-01507: database not mounted


ORACLE instance shut down.


Now, start with nomount

SQL> startup nomount
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.

Total System Global Area  209715200 bytes
Fixed Size                  1248164 bytes
Variable Size              79692892 bytes
Database Buffers          125829120 bytes
Redo Buffers                2945024 bytes


Create controlfile manually...

SQL> CREATE CONTROLFILE REUSE DATABASE "DBTEST" NORESETLOGS
     MAXLOGFILES 16
      MAXLOGMEMBERS 3
      MAXDATAFILES 100
      MAXINSTANCES 8
       MAXLOGHISTORY 292
   LOGFILE
  GROUP 1 'D:\oracle\product\10.2.0\oradata\dbtest\REDO01.LOG'  SIZE 50M,
  GROUP 2 'D:\oracle\product\10.2.0\oradata\dbtest\REDO02.LOG'  SIZE 50M,
  GROUP 3 'D:\oracle\product\10.2.0\oradata\dbtest\REDO03.LOG'  SIZE 50M
   DATAFILE
    'D:\ORACLE\PRODUCT\10.2.0\ORADATA\dbtest\SYSTEM01.DBF',
    'D:\ORACLE\PRODUCT\10.2.0\ORADATA\dbtest\UNDOTBS01.DBF',
   'D:\ORACLE\PRODUCT\10.2.0\ORADATA\dbtest\SYSAUX01.DBF',
    'D:\ORACLE\PRODUCT\10.2.0\ORADATA\dbtest\RMAN01.DBF',
   'D:\ORACLE\PRODUCT\10.2.0\ORADATA\dbtest\USE01.DBF',
   'D:\ORACLE\PRODUCT\10.2.0\ORADATA\dbtest\USE02.DBF';



Control file created.

...you see now your controlfile is successfully created.


Open the database :-)


SQL> alter database open;

Database altered.


Check your data...


SQL> select count(*) from testcf;

  COUNT(*)
----------
        19