Error in create controlfile Options

[Munchun Singh]

Hi,

By mistakenly Oradata file has been deleted and there is I have not any backup
when we Startup the database then we get an Error as

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

Now I want to create controlfile as

Microsoft Windows [Version 6.1.7600]
Copyright © 2009 Microsoft Corporation. All rights reserved.

C:\Users\SARASWATIMAA>SQLPLUS

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Apr 8 23:15:09 2012

Copyright © 1982, 2005, Oracle. All rights reserved.

Enter user-name: SYS AS SYSDBA
Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL>
SQL> SHUTDOWN
ORA-01507: database not mounted


ORACLE instance shut down.
SQL>
SQL> STARTUP
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file 'C:\ORACLE\PRODUCT\10.2.0\DB_2\DATABASE\INITORCL.ORA'
SQL>
SQL> STARTUP PFILE='C:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\PFILE\INITORCL.TXT'
ORACLE instance started.

Total System Global Area 117440512 bytes
Fixed Size 1247588 bytes
Variable Size 58721948 bytes
Database Buffers 50331648 bytes
Redo Buffers 7139328 bytes
ORA-00205: error in identifying control file, check alert log for more info

SQL> SHUTDOWN
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> STARTUP PFILE='C:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\PFILE\INITORCL.TXT' NOMOUNT
ORACLE instance started.

Total System Global Area 117440512 bytes
Fixed Size 1247588 bytes
Variable Size 58721948 bytes
Database Buffers 50331648 bytes
Redo Buffers 7139328 bytes
SQL>
SQL>
SQL> CREATE CONTROLFILE SET DATABASE "ORCL" RESETLOGS NOARCHIVELOG
2 LOGFILE
3 GROUP 1('C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\LOG_A1.LOG' , 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\LOG_A2.LOG') SIZE 240M,
4 GROUP 2('C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\LOG_B1.LOG' , 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\LOG_B2.LOG') SIZE 240M,
5 GROUP 3('C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\LOG_C1.LOG' , 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\LOG_C2.LOG') SIZE 240M
6 DATAFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM_A1.DBF' SIZE 720M
7 SYSAUX DATAFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX_A1.DBF' SIZE 720M
8 ;
SYSAUX DATAFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX_A1.DBF' SIZE 720M
*
ERROR at line 7:
ORA-01967: invalid option for CREATE CONTROLFILE

SQL> CREATE CONTROLFILE SET DATABASE "ORCL" RESETLOGS NOARCHIVELOG
2 LOGFILE
3 GROUP 1('C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\LOG_A1.LOG' , 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\LOG_A2.LOG') SIZE 240M,
4 GROUP 2('C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\LOG_B1.LOG' , 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\LOG_B2.LOG') SIZE 240M,
5 GROUP 3('C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\LOG_C1.LOG' , 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\LOG_C2.LOG') SIZE 240M
6 DATAFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM_A1.DBF' SIZE 720M
7 ;
CREATE CONTROLFILE SET DATABASE "ORCL" RESETLOGS NOARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01565: error in identifying file
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM_A1.DBF'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.

SQL> CREATE CONTROLFILE SET DATABASE "ORCL" RESETLOGS NOARCHIVELOG
2 LOGFILE
3 GROUP 1('C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\LOG_A1.LOG' , 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\LOG_A2.LOG') SIZE 240M,
4 GROUP 2('C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\LOG_B1.LOG' , 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\LOG_B2.LOG') SIZE 240M,
5 GROUP 3('C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\LOG_C1.LOG' , 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\LOG_C2.LOG') SIZE 240M
6 DATAFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM_A1.DBF' SIZE 720M
7 DEFAULT TEMPORARY TABLESPACE TEMP_A1 TEMPFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP_A1.DBF' SIZE 640M
8 ;
DEFAULT TEMPORARY TABLESPACE TEMP_A1 TEMPFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP_A1.DBF' SIZE 640M
*
ERROR at line 7:
ORA-01967: invalid option for CREATE CONTROLFILE

SQL>

Right now till, We will not Create Controlfile.....


----- Alert_Orcl as-------------


Sun Apr 08 23:15:44 2012
Shutting down instance: further logons disabled
Sun Apr 08 23:15:44 2012
Stopping background process MMNL
Sun Apr 08 23:15:45 2012
Stopping background process MMON
Sun Apr 08 23:15:46 2012
Shutting down instance (normal)
License high water mark = 1
Sun Apr 08 23:15:46 2012
ALTER DATABASE CLOSE NORMAL
ORA-1507 signalled during: ALTER DATABASE CLOSE NORMAL...
ARCH: Archival disabled due to shutdown: 1090
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
ARCH: Archival disabled due to shutdown: 1090
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Sun Apr 08 23:16:44 2012
Adjusting the default value of parameter parallel_max_servers
from 80 to 25 due to the value of parameter processes (40)
Sun Apr 08 23:16:44 2012
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned off.
LICENSE_MAX_USERS = 0
SYS auditing is disabled
ksdpec: called for event 13740 prior to event group initialization
Starting up ORACLE RDBMS Version: 10.2.0.1.0.
System parameters with non-default values:
control_files = C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL01.CTL, C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL02.CTL, C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL03.CTL
db_recovery_file_dest = C:\oracle\product\10.2.0\db_2/flash_recovery_area
db_recovery_file_dest_size= 2147483648
instance_name = ORCL
audit_file_dest = C:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\ADUMP
background_dump_dest = C:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\BDUMP
user_dump_dest = C:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP
core_dump_dest = C:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\CDUMP
db_name = orcl
pga_aggregate_target = 96468992
PMON started with pid=2, OS id=3588
PSP0 started with pid=3, OS id=3392
MMAN started with pid=4, OS id=2600
DBW0 started with pid=5, OS id=908
LGWR started with pid=6, OS id=2488
CKPT started with pid=7, OS id=2460
SMON started with pid=8, OS id=3452
RECO started with pid=9, OS id=3932
MMON started with pid=10, OS id=2560
MMNL started with pid=11, OS id=3812
Sun Apr 08 23:16:45 2012
ALTER DATABASE MOUNT
Sun Apr 08 23:16:45 2012
ORA-00202: control file: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL01.CTL'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.

Sun Apr 08 23:16:45 2012
ORA-205 signalled during: ALTER DATABASE MOUNT...
Sun Apr 08 23:16:56 2012
Shutting down instance: further logons disabled
Sun Apr 08 23:16:56 2012
Stopping background process MMNL
Sun Apr 08 23:16:57 2012
Stopping background process MMON
Sun Apr 08 23:16:58 2012
Shutting down instance (normal)
License high water mark = 1
Sun Apr 08 23:16:58 2012
ALTER DATABASE CLOSE NORMAL
ORA-1507 signalled during: ALTER DATABASE CLOSE NORMAL...
ARCH: Archival disabled due to shutdown: 1090
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
ARCH: Archival disabled due to shutdown: 1090
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Sun Apr 08 23:17:09 2012
Adjusting the default value of parameter parallel_max_servers
from 80 to 25 due to the value of parameter processes (40)
Sun Apr 08 23:17:09 2012
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned off.
LICENSE_MAX_USERS = 0
SYS auditing is disabled
ksdpec: called for event 13740 prior to event group initialization
Starting up ORACLE RDBMS Version: 10.2.0.1.0.
System parameters with non-default values:
control_files = C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL01.CTL, C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL02.CTL, C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL03.CTL
db_recovery_file_dest = C:\oracle\product\10.2.0\db_2/flash_recovery_area
db_recovery_file_dest_size= 2147483648
instance_name = ORCL
audit_file_dest = C:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\ADUMP
background_dump_dest = C:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\BDUMP
user_dump_dest = C:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP
core_dump_dest = C:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\CDUMP
db_name = orcl
pga_aggregate_target = 96468992
PMON started with pid=2, OS id=2880
PSP0 started with pid=3, OS id=2520
MMAN started with pid=4, OS id=1048
DBW0 started with pid=5, OS id=3908
LGWR started with pid=6, OS id=1236
CKPT started with pid=7, OS id=1264
SMON started with pid=8, OS id=3960
RECO started with pid=9, OS id=3480
MMON started with pid=10, OS id=404
MMNL started with pid=11, OS id=2780
Sun Apr 08 23:24:02 2012
CREATE CONTROLFILE SET DATABASE "ORCL" RESETLOGS NOARCHIVELOG
LOGFILE
GROUP 1('C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\LOG_A1.LOG' , 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\LOG_A2.LOG') SIZE 240M,
GROUP 2('C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\LOG_B1.LOG' , 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\LOG_B2.LOG') SIZE 240M,
GROUP 3('C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\LOG_C1.LOG' , 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\LOG_C2.LOG') SIZE 240M
DATAFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM_A1.DBF' SIZE 720M

Sun Apr 08 23:24:02 2012
WARNING: Default Temporary Tablespace not specified in CREATE DATABASE command
Default Temporary Tablespace will be necessary for a locally managed database in future release
Sun Apr 08 23:24:02 2012
Errors in file c:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_4080.trc:
ORA-01565: error in identifying file 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM_A1.DBF'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.

ORA-1503 signalled during: CREATE CONTROLFILE SET DATABASE "ORCL" RESETLOGS NOARCHIVELOG
LOGFILE
GROUP 1('C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\LOG_A1.LOG' , 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\LOG_A2.LOG') SIZE 240M,
GROUP 2('C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\LOG_B1.LOG' , 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\LOG_B2.LOG') SIZE 240M,
GROUP 3('C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\LOG_C1.LOG' , 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\LOG_C2.LOG') SIZE 240M
DATAFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM_A1.DBF' SIZE 720M
...


Please Help where is My Mistake in Creating Cotrolfile
I want to know that in which condition we use "SET" or "REUSE" in Create Controlfile


-- Neeraj Singh

[burleson]

Hi Neeraj,

When an Oracle datafile is deleted, you only have a few options:

• Oracle technical support - If you have purchased silver or gold support from Oracle, they will work with you to use the BBED utility.
• Do it yourself - You can use BBED to fix corruption by yourself, but you must be able to read hex and understand the data block internals.
• Hire an expert - You can hire a 3rd party Oracle expert to recover you data.
  
  *********************************************
  >> By mistakenly Oradata file has been deleted and there is I have not any backup
  
  Ouch! You have NO baclup at all? Ever?
  
  If so, that data is lost forever.
  
  >> where is My Mistake in Creating Cotrolfile
  
  That is not your mistake. The mistake was deleting a data file.
  
  
  *********************************************
  
  >> ORA-00205
  
  See here:
  
  http://dba-oracle.com/t_ora-00205:_error_i...controlfile.htm
  
  
  It is a standard practice to have at least two control file (normally three), so you need to identify the surviving control files and replace the missing one with one of the others.

[HAL9000]

Please read this carefully:

http://www.dba-oracle.com/t_recover_oracle...t_data_file.htm

[SteveC]

Your initial problem had nothing whatsoever to do with having lost a data file. The error is about a missing control file, which is a world of difference, and is perhaps the easiest recovery scenario in the world, with respect to Oracle.

I'm sure some of the higher-ranked search results from Google, out of the more than 107,000 out there ("oracle loss of controlfile"), specifically address your problem. You could even read the documentation Oracle provides for free, even from back in the dark ages of Oracle 8.

http://docs.oracle.com/cd/A87860_01/doc/se...oscen.htm#11848