Forum OpenACS Development: Screwed Oracle

1: Screwed Oracle

Posted by roland dunn on 10/29/02 07:09 PM

Hi,

Weve had a bad moment with one of our ORacle installations - looks
like one of our .DBF files has been corrupted - and not backed up.
Consquently we're stuck in a loop somewhat like the following:

SVRMGR> recover database using backup controlfile;
ORA-00279: change 316938 generated at 06/18/2001 14:44:28 needed for
thread 1
ORA-00289: suggestion :
/ora8/m01/app/oracle/product/8.1.6/dbs/arch1_186.dbf
ORA-00280: change 316938 for thread 1 is in sequence #186
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00308: cannot open archived log
'/ora8/m01/app/oracle/product/8.1.6/dbs/arch1_186.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
SVRMGR>

Anyone any ideas where to go from here?
Thanks, Roland.

2: Response to Screwed Oracle (response to 1)

Posted by roland dunn on 10/29/02 11:48 PM

Just to expand a little on this. This is more information on where we got to:

Somehow we've managed to delete (and lose i.e. not backup) one of our .dbf files. Now, when Oracle starts up on reboot if I try to login using sqlplus I get the error: "ORA-01033: ORACLE initialization or shutdown in progress".

So: if I go into "svrmgrl", and "connect internal", "shutdown immediate" (on shutdown immediate I get "ORA-01109: database not open"), "startup" - I then get the message "ORA-01589: must use RESETLOGS or NORESETLOGS option for database open" - what should we do now? Anyone any ideas?

3: Response to Screwed Oracle (response to 1)

Posted by Jeff Davis on 10/29/02 11:58 PM

This is what I have written down for dropping a database when you delete the .dbf file first:

connect internal;
startup mount;
alter database datafile "/fullpath/filename.dbf" offline drop;
alter database open;
drop tablespace tablespacename including contents;

but if you don't want to drop the associated tablespace I suspect you will have to seek guidance from an actual DBA (not a cargo cult DBA which is what I am).

4: Response to Screwed Oracle (response to 1)

Posted by Jeff Davis on 10/30/02 12:11 AM

Also, this page <a href="http://www.dbaoncall.net/references/ht_recover_with_lost_df.html">http://www.dbaoncall.net/references/ht_recover_with_lost_df.html</a>
seems to cover exactly the kind of problem you are having now...

5: Response to Screwed Oracle (response to 1)

Posted by roland dunn on 10/30/02 12:45 AM

Thanks for your answers, moved me forward a little .....

I do the following fine:
connect internal;
startup mount;
alter database datafile "/fullpath/filename.dbf" offline drop;

But, when I do this:
alter database open;

I get this:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

So, I run:
alter database open resetlogs;

I get this:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/ora8/m01/app/oracle/oradata/ora8/system01.dbf'

So .... I then jump to the web page you pointed me at (which bizarrely I had looked at briefly before), and do the following:

recover database until cancel using backup controlfile;

and get:
ORA-00279: change 10965631 generated at 10/29/2002 18:13:13 needed for thread 1
ORA-00289: suggestion : /ora8/m01/app/oracle/product/8.1.6/dbs/arch1_2644.dbf
ORA-00280: change 10965631 for thread 1 is in sequence #2644
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

SO: I hit <RET> and get:
ORA-00308: cannot open archived log '/ora8/m01/app/oracle/product/8.1.6/dbs/arc
h1_2644.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/ora8/m01/app/oracle/oradata/ora8/system01.dbf'

And it is these last three errors I _always_ seem to end up with.

Agh!
Thanks for your help so far ......

6: Response to Screwed Oracle (response to 1)

Posted by Jeff Davis on 10/30/02 12:51 AM

My reading of that page was that when it said

Specify log: {=suggested | filename | AUTO | CANCEL}

you should enter your redo log (they had /db02/oradata/ptools2/redo05.log there).

7: Response to Screwed Oracle (response to 1)

Posted by roland dunn on 10/30/02 01:09 AM

Jeff,

If you could see my face ..... the biggest of biggest smiles. If you were in the vicinity you'd be in danger of kisses.

I _cannot_ thank you enough.
Fantastic.

Needless to say - it works again!
Cheers Roland.

8: Response to Screwed Oracle (response to 1)

Posted by Jeff Davis on 10/30/02 09:39 AM

I am actually in the vicinity (more or less), although I guess it could be argued that 12 stops on the circle line is not really the vicinity after all :)

9: Re: Screwed Oracle (response to 1)

Posted by Dharmendra Singh on 04/12/06 04:50 PM

Hi,

If you are refreshing a database from another one, try to change the log_archive_format parameter of target database to be same as that of source database and then apply the archive.
It can resolve your problem.

Regards,
D