OraFAQ Forum: Server Administration » how to recreate controlfile

Home » RDBMS Server » Server Administration » how to recreate controlfile (10.2.0.1)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

how to recreate controlfile [message #557344]

Tue, 12 June 2012 01:28

andy huang
Messages: 498
Registered: July 2011

Senior Member

Dear all,
I can not run the control file treace file,why?

[oracle@hxl oradata_bak]$ more run_sql.sql
CREATE CONTROLFILE REUSE DATABASE "ORACL" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/oracl/redo01.log'  SIZE 50M,
  GROUP 2 '/u01/app/oracle/oradata/oracl/redo02.log'  SIZE 50M,
  GROUP 3 '/u01/app/oracle/oradata/oracl/redo03.log'  SIZE 50M
-- STANDBY LOGFILE

DATAFILE
  '/u01/app/oracle/oradata/oracl/system01.dbf',
  '/u01/app/oracle/oradata/oracl/undotbs01.dbf',
  '/u01/app/oracle/oradata/oracl/sysaux01.dbf',
  '/u01/app/oracle/oradata/oracl/users01.dbf',
  '/u01/app/oracle/oradata/oracl/hxl01.dbf',
  '/u01/app/oracle/oradata/oracl/streams_tbs01.dbf',
  '/u01/app/oracle/oradata/oracl/hxl02.dbf'
CHARACTER SET AL32UTF8
;

SQL> @/u01/oradata_bak/run_sql.sql;
SP2-0042: unknown command "DATAFILE" - rest of line ignored.
SP2-0734: unknown command beginning "'/u01/app/..." - rest of line ignored.
SP2-0734: unknown command beginning "'/u01/app/..." - rest of line ignored.
SP2-0734: unknown command beginning "'/u01/app/..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SP2-0734: unknown command beginning "'/u01/app/..." - rest of line ignored.
SP2-0734: unknown command beginning "'/u01/app/..." - rest of line ignored.
SP2-0734: unknown command beginning "'/u01/app/..." - rest of line ignored.
SP2-0734: unknown command beginning "'/u01/app/..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SP2-0734: unknown command beginning "CHARACTER ..." - rest of line ignored.
1 CREATE CONTROLFILE REUSE DATABASE "ORACL" RESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/u01/app/oracle/oradata/oracl/redo01.log' SIZE 50M,
9 GROUP 2 '/u01/app/oracle/oradata/oracl/redo02.log' SIZE 50M,
10 GROUP 3 '/u01/app/oracle/oradata/oracl/redo03.log' SIZE 50M
11* -- STANDBY LOGFILE
SQL>

Report message to a moderator

Re: how to recreate controlfile [message #557346 is a reply to message #557344]

Tue, 12 June 2012 01:31

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

set sqlblanklines ON

Or remove the blank lines.

Regards
Michel

[Updated on: Tue, 12 June 2012 01:32]

Report message to a moderator

Re: how to recreate controlfile [message #557350 is a reply to message #557346]

Tue, 12 June 2012 01:53

andy huang
Messages: 498
Registered: July 2011

Senior Member

Thanks,
Michel!

Report message to a moderator

Re: how to recreate controlfile [message #557352 is a reply to message #557350]

Tue, 12 June 2012 01:55

andy huang
Messages: 498
Registered: July 2011

Senior Member

Michel,
New issue is raise,there is no any backup of my database,how can i do?

SQL> set sqlblanklines ON;
SQL> @/u01/oradata_bak/run_sql.sql;
CREATE CONTROLFILE REUSE DATABASE "ORACL" RESETLOGS  ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01115: IO error reading block from file  (block # 1)
ORA-01110: data file : '/u01/app/oracle/oradata/oracl/system01.dbf'
ORA-27091: unable to queue I/O
ORA-27067: size of I/O buffer is invalid
Additional information: 2

Report message to a moderator

Re: how to recreate controlfile [message #557362 is a reply to message #557352]

Tue, 12 June 2012 02:37

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Recreate a new one.

Regards
Michel

Report message to a moderator

Re: how to recreate controlfile [message #557364 is a reply to message #557362]

Tue, 12 June 2012 02:41

andy huang
Messages: 498
Registered: July 2011

Senior Member

Thanks,
what is your meaning? recreate a new database? if this, my data will be lost.

Report message to a moderator

Re: how to recreate controlfile [message #557366 is a reply to message #557364]

Tue, 12 June 2012 02:48

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

If you cannot start your database and have no backup, anyway you data are lost.

Regards
Michel

Report message to a moderator

Re: how to recreate controlfile [message #557367 is a reply to message #557366]

Tue, 12 June 2012 02:55

andy huang
Messages: 498
Registered: July 2011

Senior Member

Thanks,
Michel,This problem can not fixed?

Report message to a moderator

Re: how to recreate controlfile [message #557370 is a reply to message #557367]

Tue, 12 June 2012 03:50

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Which problem EXACTLY?

Regards
Michel

Report message to a moderator

Re: how to recreate controlfile [message #557464 is a reply to message #557370]

Tue, 12 June 2012 20:27

andy huang
Messages: 498
Registered: July 2011

Senior Member

Thanks,
Michel,the problem as the flowing:

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/u01/app/oracle/oradata/oracl/system01.dbf'
ORA-01205: not a data file - type number in header is 0

Report message to a moderator

Re: how to recreate controlfile [message #557465 is a reply to message #557464]

Tue, 12 June 2012 22:00

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

01205, 00000, "not a data file - type number in header is %s"
// *Cause:  The file type in the header is not correct for a data file. This
//         is probably a log file or control file. If the type is not a small 
//         non-zero positive number then the header is corrupted.
// *Action: Restore a copy of the correct data file and do recovery as needed.

Report message to a moderator