Oracle DB won't come up - URGENT

[desbo]

I managed to to a stupid thing and shutdown the oracle db whilst it was doing a hot backup.

The first error that occurred when I tried to start the database said that one of the table spaces was corrupt. Having googled this it seemed that Oracle may have been lying to me and that i just needed to "alter tablespace xxxx backup no".

I did this and tried to start the db again. This time it came up with "ORA-01092: ORACLE instance terminated. Disconnection forced". The suggestion was that one of the control files was corrupt so I tried fixing that by copying them around.

Now I get this error
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/mtmsdb002/oradata/BATH/sysBATH01.dbf'
ORA-01207: file is more recent than controlfile - old controlfile

Now I am really stumped.
Every where I look seems to suggest "backup controlfile recovery" is the option but it is all getting beyond me.

As you may guess I don't know what I am doing.

Help!!!!! Please

 

[SantaMufasa]

"alter tablespace xxxx backup no"

I have never seen that syntax. If you want to take a tablespace out of hot backup mode, the syntax should be:

ALTER TABLESPACE <ts name> END BACKUP;

If I were in your position, I would try the following (from a shutdown database):

SQL> STARTUP MOUNT (which may not work if the control files are truly corrupted)
SQL> RECOVER DATABASE

...and see what happens. If your lucky stars are with you, then the recovery will complete successfully.


If Oracle, at any point, says that a tablespace is in backup mode, issue the "...END BACKUP" command I listed above.

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[desbo]

Mounted OK but when I tried recovery still got

ORA-00283: recovery session canceled due to errors
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/mtmsdb002/oradata/BATH/sysBATH01.dbf'
ORA-01207: file is more recent than controlfile - old controlfile


Any oter ideas other than a restore and roll forward?

Thanks

 

[SantaMufasa]

Then here's the next thing I would try: First, backup all of your database files. Next, run this code:

SQL> startup mount
SQL> alter database backup controlfile to trace;
SQL> shutdown immediate (or 'shutdown abort', if necessary)

Then go out to the path that is your USER_DUMP_DEST and rename (to something meaningful) and examine the file that was just created. (The file name will be something like, '<SID>_ora_nnnn.trc'). By your running that resulting script from a "SQL>" against your shutdown database, it should reconstruct your control files and recover the database (as you will see in the script).

Let us know those results.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[desbo]

Then go out to the path that is your USER_DUMP_DEST

How do I find out this??

Sorry I told you I was out of my depth.

 

[karluk]

Your immediate problem is that when you were "copying around" the control files you seem to have replaced the current control file with an older version. That's a serious no-no, which you should fix if you still have a copy of the current control file somewhere.

If you don't have the latest control file anywhere, then your only option is to use the syntax

recover database using backup controlfile until...

But unfortunately it sounds as if your database recovery may fail, even if you restore a current control file or use the "using backup controlfile" clause. It all depends on how serious the earlier error you got about the corrupt tablespace. That may require a restore from backup. But you need to fix your control file problem before you'll be able to proceed with rest of the recovery.

 

[desbo]

I have found my USER_DUMP_DEST.

I do have a copy of my ctl files before I did anything but I though that I had copied those back. I have tried to restart the db many times since these were copied away.

Should I copy the files back again andf see if that sorts it?

Thanks

 

[SantaMufasa]

Yes.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[karluk]

Yes, definitely replace your controlfiles with the copy. The control file contains a record of the last system change number (SCN), which it compares to the SCN recorded in the header of your data files. If the SCN in the data file is greater than the one in the control file, you get the error you are seeing. Try to fix that if possible before proceeding.

SantaMufasa's suggestion to backup the control file to trace and recreate it that way won't produce a control file with up-to-date SCN, so that's not the way to proceed if you really do have a current control file somewhere.

Make sure you copy the current control file to all the places it's supposed to be. Most Oracle databases have three separate control file locations.

 

[desbo]

I did the "controlfile trace" fix and that returned me to a previous error - "ORA-01092: ORACLE instance terminated. Disconnection forced".

I then copied the backed up control files back to their original locations and now I get this error (a new one) when I try to strat the db

ORA-01991: invalid password file '/oracle/server/product/9.2.0/dbs/orapwBATH'

Am I gettin somewhere?

Thanks

 

[karluk]

Is the name of your database really "BATH"? Can you look in /oracle/server/product/9.2.0/dbs and see what, if any, files are located there with names beginning with "orapw"? Oracle is expecting to find a password file in this directory named "orapw + dbname". Is there any chance you may have overwritten this file while attempting your recovery?

 

[desbo]

ORACLE_SID is definately BATH

I did an "orapwd" and recreated the pasword file and re copied the backed up control files back to their original locations and I am back with error

ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/mtmsdb002/oradata/BATH/sysBATH01.dbf'
ORA-01207: file is more recent than controlfile - old controlfile

 

[desbo]

It ia almost decision time. If I can't get the db started very soon then it is back to last nights backup and a roll forward.

Any ideas to save me having to do this would be appreciated.

Thanks

 

[karluk]

So, the backed up control file can't have been as recent as the one that was in place when your database originally crashed. If you don't have a copy of that, your only option is to try the "recover using backup controlfile..." syntax.

 

[SantaMufasa]

Desbo,

If you have last night's backup, and all intervening archivelog files, then how about your restoring last night backup files (database and on-line redo log files) and the most recent control file(s), then do a "RECOVER DATABASE" command. That will, at least, get you up to the time of the most recent control file (saving you and your co-worker from applying all of the transactions since last night).

Let us know how that goes for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[desbo]

Karluk, can you please give a full description of

"recover using backup controlfile..." syntax

and I will give it a try.

I really need it exactly, command by command.

Thanls

 

[karluk]

Yikes, SantaMufasa, don't suggest restoring the online redo logs. That would overwrite the current logs with last night's logs and destroy all activity recorded in the current logs. The online redo logs aren't typically backed up in any case, but restoring them would be a serious mistake.

 

[desbo]

the online redo logs will not be restored as per our recovery proceedures.

 

[karluk]

There are multiple options. Basically you need to specify the point in time to end the backup. The simplest is

recover database using backup controlfile until cancel

That will start applying archive logs one by one, giving you the option to cancel the recovery after each log has been applied. The disadvantage is that you can't recover past the last available archivelog.

You can also try

recover database using backup controlfile until scn 123456789

You have to set an appropriate value of scn just before your database crashed, which you should be able to get from the database alert log.

You can also specify a time. For example,

recover database using backup controlfile until time trunc(sysdate)+13/24

would recover the database up to 3:00 p.m. this afternoon.

 

[karluk]

Correction: The last recovery is until 1:00 p.m. this afternoon.