OraFAQ Forum: Server Administration » SPFile Corruption

Home » RDBMS Server » Server Administration » SPFile Corruption

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

SPFile Corruption [message #221093]

Fri, 23 February 2007 16:03

jinesh_s4u
Messages: 8
Registered: February 2007

Junior Member

It is said that you never edit the spfile directly as it corrupts it, but i have done the mistake of editing it directly by opening it in pico editor.
Can anybody help me out about how i fix this error.

As a step towards rectification, i am trying to start the database with these commands

$ SQLPLUS /NOLOG
SQL> CONNECT sys/password as SYSDBA
SQL> STARTUP PFILE = ' /u01/app/oracle/product/10.1.0/db_1/dbs/initedobe.ora'

and it is giving following messages ,

ORACLE instance started.

Total System Global Area 159383552 bytes
Fixed Size 777856 bytes
Variable Size 158343552 bytes
Database Buffers 0 bytes
Redo Buffers 262144 bytes
Database mounted.
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/u01/app/oracle/oradata/edobe/system01.dbf'
ORA-01207: file is more recent than controlfile - old controlfile

On going through the description of the error at
http://ora-01207.ora-code.com/ , it says that repeating the startup and shutdown sequence will eliminate the error. But this has not helped me.

I have also tried to create a new spfile with CREATE SPFILE command, but it has not helped me as it gives the same error when i start the database.

Any help will be appreciated

Thanks
-Jinesh

Report message to a moderator

Re: SPFile Corruption [message #221095 is a reply to message #221093]

Fri, 23 February 2007 16:23

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Based upon what was posted, I conclude that the ORA-01110 error has nothing directly related to spfile changes.
AFAIK, the solution is to do a point in time recovery.

Report message to a moderator

Re: SPFile Corruption [message #221106 is a reply to message #221095]

Fri, 23 February 2007 22:23

jinesh_s4u
Messages: 8
Registered: February 2007

Junior Member

I have not done the backup earlier. All i had done was copied the control files, and data files to a different location.

Since i have not done the backup with rman or other utility, i don't know how do i do point in time recovery. It will be great if you can tell me how i do that.

And the other thing its difficult to digest for me is that, since i had unintentionally corrupted the spfile, and had not edited the control files or datafiles why after recreating the spfile with the CREATE SPFILE, it is throwing the errors when i start the databse with the new spfile.

-Jinesh.

Report message to a moderator

Re: SPFile Corruption [message #221110 is a reply to message #221106]

Fri, 23 February 2007 22:51

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

Hi,
SQL> STARTUP PFILE = ' /u01/app/oracle/product/10.1.0/db_1/dbs/initedobe.ora'
Now your database is started with PFILE.

If you already edit SPFILE directly then just create new SPILE from PFILE.

"create spfile from pfile"

or
ORA-01207: file is more recent than controlfile - old controlfile

It is error because of your control file is not recant with your datafile.. really say what you did with your control file ?

regards
Taj

[Updated on: Fri, 23 February 2007 22:51]

Report message to a moderator

Re: SPFile Corruption [message #221111 is a reply to message #221093]

Fri, 23 February 2007 22:56

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

In the bigger scheme of things, it does not matter what happened in the past.
It is what it is.
Without backups, issue $ newfs & start over.
I have no magic bullet.

Report message to a moderator

Re: SPFile Corruption [message #221241 is a reply to message #221110]

Sun, 25 February 2007 23:10

jinesh_s4u
Messages: 8
Registered: February 2007

Junior Member

Hi Taj,
I have already tried to start the databse with
STARTUP PFILE = '$ORACLE_HOME/dbs/initedobe.ora' but still gave me that error.

Also i tried to create a SPFILE from PFILE named initedobe.ora.
and tried to start the databse with these sequence of commands.
1. CREATE SPFLE FROM PFILE = $ORACLE_HOME/dbs/initedobe.ora and this created the binary file spfileedobe.ora

2. STARTUP

and this also gave me the same ORA-01207 error.

The only thing i did in the past was editing the spfileedobe.ora. I have never done anything with controlfiles.

Also according the ORA error code explanation, it says that you keep on trying to start the databse and eventually the sequence number in control file will become higher that that in datafile, but i have tried almost 60-70 times but that has not worked.
Plz help me and take me out of this situation.

Thanks for your help

Jinesh

Report message to a moderator

Re: SPFile Corruption [message #221242 is a reply to message #221111]

Sun, 25 February 2007 23:11

jinesh_s4u
Messages: 8
Registered: February 2007

Junior Member

I cannot start over again as i will loose my schema and data. i need to try everything before that last resort.

Thanks
Jinesh

Report message to a moderator

Re: SPFile Corruption [message #221243 is a reply to message #221241]

Sun, 25 February 2007 23:17

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

Hi,
and this also gave me the same ORA-01207 error.
If your not do anything with your control file then possible you have more than one database on some machine.
or you have more control file copy and your pfile point old control file copy becuase when you create spfile from pfile it will give your ORA-01207 error.

Suggestion:
1.check your more recent or right control file.
2.shutdown and startup your database with PFILE(INIT.ORA) NOT INIT<SID>.ORA

we have three file >> spfile<sid>.ora , init<sid>.ora or init.ora
3. make sure your init.ora file point correct copy of control file.

if your problem still exists then just create new control file.
and startup your database.

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "taj"
NORESETLOGS [archivelog/noarchivelog]
MAXLOGFILES 5 
MAXLOGMEMBERS 3 
MAXDATAFILES 10 
MAXINSTANCES 1 
MAXLOGHISTORY 113
LOGFILE 
GROUP 1 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\TAJ\REDO01.LOG' SIZE 10M,
GROUP 2 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\TAJ\REDO02.LOG' SIZE 10M,
GROUP 3 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\TAJ\REDO03.LOG' SIZE 10M
DATAFILE 
'D:\ORACLE\PRODUCT\10.1.0\ORADATA\TAJ\SYSTEM01.DBF' SIZE xxx, 
'D:\ORACLE\PRODUCT\10.1.0\ORADATA\TAJ\USERS01.DBF' SIZE 
xxx, 
. 
. 
. 
.
CHARACTER SET [chracater_set]

hope this helps
if any doubts please let us know.

regards
Taj

Report message to a moderator

Re: SPFile Corruption [message #221389 is a reply to message #221243]

Mon, 26 February 2007 09:33

jinesh_s4u
Messages: 8
Registered: February 2007

Junior Member

Hi Taj,

i will like to tell couple of things as per your previous reply.
1. I have only one databse on my machine.
2. I have 3 copies of control file and my pfile points to all of them. While create a new spfile from this pfile, it generates a new binary spfile without any errors.
3. when i start the databse with the default STARTUP command, it reads the new spfile created and then throws the ORA-01207 error.

As per your suggestion,
1. I did start the databse with
STARTUP PFILE = $ORACLE_HOME/dbs/init.ora and it points to the latest control files that i have, then it stills throws the same error.

2. With new control file creation, i want to ask couple of things :
a. if i specify all the current datafiles aswell as SYSTEM01.dbf, then is it going to overwrite these datafiles. I don't want these datafiles be over-written as i will loose my data and schema.
b. while specifying the datafiles eg, SYSTEM01.dbf , USER01.dbf and my own datafiles, what SIZE should i specify ?

Thanks Taj,

Jinesh

Report message to a moderator

Re: SPFile Corruption [message #221393 is a reply to message #221389]

Mon, 26 February 2007 09:48

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

Hi,
if i specify all the current datafiles aswell as SYSTEM01.dbf, then is it going to overwrite these datafiles. I don't want these datafiles be over-written as i will loose my data and schema.
"create controlfile" statement create control file with current database structure. it will not overwrite your datafiles.

"create controlfile" statement use when we have no backup of control file and all controlfiles are lost.

In your condition first recover from most recent backup of controlfiles.
if no backup >>> then go with "create controlfile" statments.

b. while specifying the datafiles eg, SYSTEM01.dbf , USER01.dbf and my own datafiles, what SIZE should i specify ?

select bytes/1024/1024 from dba_data_files;

regards
Taj

Report message to a moderator

Re: SPFile Corruption [message #221411 is a reply to message #221393]

Mon, 26 February 2007 10:49

jinesh_s4u
Messages: 8
Registered: February 2007

Junior Member

Hi Taj,
Thanks for your instant reply.

I am getting a error while executing the following query
select bytes/1024/1024 from dba_data_files;

The Error is
select bytes/1024/1024 from dba_data_files
*
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only

I ran the following sequence of Commands
$ sqlplus /NOLOG
SQL> connect sys/oracle as SYSDBA
SQL> STARTUP NOMOUNT
SQL> select bytes/1024/1024 from dba_data_files

and it gave me the error,
so i don't know how to figure out the size of the datafiles.
Let me know so i can create a new control file.

Thank Taj,
-Jinesh

Report message to a moderator

Re: SPFile Corruption [message #221415 is a reply to message #221411]

Mon, 26 February 2007 10:59

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

>> SQL> STARTUP NOMOUNT
Your database is not open!!!
First open the database and then run the sql.

Report message to a moderator

Re: SPFile Corruption [message #221570 is a reply to message #221093]

Tue, 27 February 2007 08:42

clioteux
Messages: 5
Registered: February 2007
Location: algiers

Junior Member

Hi,
I think that Taj has answered you with the solution to your problem :
1- Restore the database using a backup, or
2- If you don't have a backup, re-create the controlfile using the script given by Taj.

In the futur, don't forget to backup your Controlfile using this command :
Alter database backup controlfile to trace;

This will create an SQL script (in the udump directory) that permit to recreate the controlfile;

Report message to a moderator

Re: SPFile Corruption [message #221577 is a reply to message #221570]

Tue, 27 February 2007 09:14

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

Hi,
i want to add one more command with your suggestion.

alter database backup controlfile to 'drive:\backup\control.ctl';

it will create BINARY FORMAT control file backup.

regards
Taj

Report message to a moderator

Re: SPFile Corruption [message #221813 is a reply to message #221577]

Wed, 28 February 2007 09:36

jinesh_s4u
Messages: 8
Registered: February 2007

Junior Member

Hi Taj,
I tried the create control file statement that you had mentioned earlier, but i was unable to specify the size of each Database file. I cannot run the query
select bytes/1024/1024 from dba_data_files;
as my database is not open. It is throwing some error stating that queries can be executed on fixed tables only.

So can you plz mention how do i compute the size of my each datafile in the create control file statement. I tried specifying the actual datafile size but that didn't work. It gave me the following error
ORA-01163: SIZE clause indicates [string] (blocks), but should match header [string] .

Is it fine if i don't specify the datafile size and by doing that the create control file statement computes the datafile size itself ?

Thanks Taj,
- Jinesh

Report message to a moderator

Re: SPFile Corruption [message #222648 is a reply to message #221813]

Mon, 05 March 2007 15:06

lelica
Messages: 1
Registered: March 2007

Junior Member

Hi Jinesh,
I am assuming:
- your database is not in archive log mode
- you don't have any RMAN backup (oracle export) or archive log files on the disk. You have only online redo log files.

I am hoping this is you hobby database and not production one. Things look pretty bad and you have to accept lost, just question is how much is not too much.

Diagnostic:
- Spfile is not a problem at all. Problem is a control file spfile points to (old and not usable). Try to identify all copies of control files you have on the disk and the last modification date. Do all of them match in size and timestamp?
If not there is a hope. Check system datafile timestamp and compare with control file timestamp. Probably system file is newer than controlfile.
-Before you do anything with control file copy current version(s) to a new location just to have it (in case)
- If all control files have the same timestamp than you are in a trouble and you have to accept a lost. All you will lose are all information in online redo log files not jet saved in database files, since re-creating a new control file would need to reset online redo log files and their content is permanently lost. You would not lose any information saved in the datafiles but all changes afterwords yes.
So solution:
1) Check all controlfile timestamps and hope they are not the same.
2) Presereve all copies before you do ANYTHING on another location.
3) if by any chance you managed to corrupt only first control file from which database reads first then the other one or two will contain the right data/timestamp and you can copy good one to the first one. Then try to start database in stages (nomont,mount,open) to see where it will fail.
4) If there is no good copy of control file anywhere on the disk you have to recreate it and possibly (depends how lucky you are)loose all information in online redo log files.
5) How to do it:
- database must be shut down and all processes not running ( I assume that is the case)
% set ORACLE_SID=database_name
%set ORACLE_HOME=oracle_home
% sqlplus /nolog
SQL> connect / as sysdba
SQL> startup nomount;

(if you like choose your pfile, just know where it points to - which control file(s))
SQL> alter database mount;
This will open your current control file (old one).
SQL> alter database backup controlfile to trace;
This will "read" your current binary control file and create "visible" text (sql) file under "udump" folder. Find it and look into content. It should contain all file names as controlfile "remembers" and much more. This script can be used to re-create control file and only control file. To be clear re-creating a control file "destroys" already existing one. So before you do it make sure you know what is going to be lost.
6) Now if you decide to continue first backup all data files and online redo log files and control file and spfile to be able to go to the previous stage if needed. (first shutdown database before you copy files)
7) SQl> shutdown;
SQL> startup nomount;
SQL> create controlfile ....

Follow instruction from generated trace/sql file. Eventually you will. Clear and stright forward. read each line and all comment and do not "run" the whole script. Copy /pasete line by line.
Do not reset log files if you do not have to.
If you have to make sure you understand that all resent changes preserved in log files but not written to the disk will be lost.

Good luck,
Regards,
Vitostan

Report message to a moderator

Re: SPFile Corruption [message #222822 is a reply to message #222648]

Tue, 06 March 2007 09:27

jinesh_s4u
Messages: 8
Registered: February 2007

Junior Member

Thank you Vitoshan,
I will try the solution you suggested. As far as I am able to retrieve my database with all the updates saved in the datafile, then i am fine. Since this is my test database its fine if i loose the data in online redo logs.
I will let you know once i am done.

-Jinesh

Report message to a moderator

Previous Topic:	error ORA-12560 install Service ->OracleService<-
Next Topic:	Excel,text Data through ODBC in Oracle

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jan 25 15:54:19 CST 2025