Oracle error after abrupt shutdown of server

[Former Member]

Hi..

Today i was taking online backup using sapdba. During backup server abruptly shutdown due to power failure.

Now i am trying to restart the server but it terminates with error code "2".

Here is my startdb.log

First trying to shutdown the database - May be,

the database is in the nomount or mount state

-

---

Wed Apr 29 22:23:50 IST 2009

starting database

SQL*Plus: Release 9.2.0.3.0 - Production on Wed Apr 29 22:23:50 2009

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to an idle instance.

ORACLE instance started.

Total System Global Area 463963864 bytes

Fixed Size 730840 bytes

Variable Size 268435456 bytes

Database Buffers 193462272 bytes

Redo Buffers 1335296 bytes

Database mounted.

ORA-01113: file 2 needs media recovery

ORA-01110: data file 2: '/oracle/PRD/sapdata3/prd_1/prd.data1'

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production

With the Partitioning option

JServer Release 9.2.0.3.0 - Production

-

---

Wed Apr 29 22:23:56 IST 2009

Connect to the database to verify, that the database is now open

R3trans check finished with return code: 12

• ERROR: Startup of database failed

Notify Database Administrator.

/usr/sap/PRD/SYS/exe/run/startdb: Terminating with error code 2

It is asking for media recovery due to tablespace in BACKUP mode.

I tried with command ALTER TABLESPACE '------' END BACKUP.

but is give error like----

Alter tablespace PSAPPRD end backup;

Alter tablespace PSAPPRD end backup

*

ERROR at line 1:

ORA-01199: file 70 is not in online backup mode

ORA-01110: data file 70: '/oracle/PRD/sapdata5/prd_54/prd.data54'

ORA-01199: file 71 is not in online backup mode

ORA-01110: data file 71: '/oracle/PRD/sapdata5/prd_55/prd.data55'

ORA-01199: file 72 is not in online backup mode

ORA-01110: data file 72: '/oracle/PRD/sapdata5/prd_56/prd.data56'

ORA-01199: file 73 is not in online backup mode

ORA-01110: data file 73: '/oracle/PRD/sapdata5/prd_57/prd.data57'

ORA-01199: file 74 is not in online backup mode

ORA-01110: data file 74: '/oracle/PRD/sapdata5/prd_58/prd.data58'

ORA-01199: file 75 is not in online backup mode

ORA-01110: data file 75: '/oracle/PRD/sapdata5/prd_59/prd.data59'

ORA-01199: file 76 is not in online backup mode

ORA-01110: data file 76: '/oracle/PRD/sapdata5/prd_60/prd.data60'

ORA-01199: file 77 is not in online backup mode

ORA-01110: data file 77: '/oracle/PRD/sapdata5/prd_61/prd.data61'

ORA-01199: file 78 is not in online backup mode

ORA-01110: data file 78: '/oracle/PRD/sapdata5/prd_62/prd.data6

Now what i can do further. file '2' is saying for media recovery but in this case i got a information form oracle site that media recovery should be avoided.

Please help!

Thanks in advance

Regards.

Raju

[stefan_koehler · Accepted solution]

Hello Raju,

> Today i was taking online backup using sapdba. During backup server abruptly shutdown due to power failure.

in this case the behavior of oracle is normal. Some (or all) data files are still in backup mode and need to be set to end backup .. after that you can start the database normally (recovery is done automatically).

To identify which data files are still in backup mode .. please use the following SELECT

shell> sqlplus / as sysdba
SQL> startup mount;
SQL> select substr(a.name,1,30) "DbFile", a.file# "File#", b.status "Status" 
from v$datafile a, v$backup b
where a.file# = b.file# order by a.file#; 

After you have identified the data files (status = ACTIVE) you have to set them into "END BACKUP" mode with the following SQL:

shell> sqlplus / as sysdba
SQL> alter database datafile '<DATA_FILE_NAME>' end backup;

Regards

Stefan

[Former Member · Accepted solution]

hi. stefan,

i got the following info from given query.

DbFile File# Status

-

---

-

---

-

---

/oracle/PRD/sapdata1/system_1/ 1 NOT ACTIVE

/oracle/PRD/sapdata3/prd_1/prd 2 ACTIVE

/oracle/PRD/sapdata3/prd_2/prd 3 NOT ACTIVE

/oracle/PRD/sapdata3/prd_3/prd 4 NOT ACTIVE

/oracle/PRD/sapdata3/prd_4/prd 5 NOT ACTIVE

/oracle/PRD/sapdata4/prd_5/prd 6 NOT ACTIVE

/oracle/PRD/sapdata4/prd_6/prd 7 NOT ACTIVE

/oracle/PRD/sapdata4/prd_7/prd 8 NOT ACTIVE

/oracle/PRD/sapdata4/prd_8/prd 9 NOT ACTIVE

/oracle/PRD/sapdata4/prd_9/prd 10 NOT ACTIVE

/oracle/PRD/sapdata1/prd620_1/ 11 NOT ACTIVE

after that i ran the command as per your suggestion -but got the following--

alter database datafile '/oracle/PRD/sapdata3/prd_1/prd.data1' end backup;

alter database datafile '/oracle/PRD/sapdata3/prd_1/prd.data1' end backup

*

ERROR at line 1:

ORA-01235: END BACKUP failed for 1 file(s) and succeeded for 0

ORA-01122: database file 2 failed verification check

ORA-01110: data file 2: '/oracle/PRD/sapdata3/prd_1/prd.data1'

ORA-01208: data file is an old version - not accessing current version

Now what should i do.

Please help!.

Regards.

Raju

[Former Member · Accepted solution]

@Stefan: What is wrong on a recovery?

[stefan_koehler · Accepted solution]

Hi Thomas,

you wrote that this is the only solution (edited your post). In this case it is not necessary.

Regards

Stefan

[stefan_koehler · Accepted solution]

Hello Raju,

am i right that the data file 2 is corresponding to the tablespace PSAPPRD?

Please try this (not sure what you did before that it comes to this situation):

shell> sqlplus / as sysdba
SQL> startup mount;
SQL> recover tablespace PSAPPRD;
SQL> shutdown immediate;
SQL> startup
SQL> ALTER TABLESPACE PSAPPRD ONLINE; (if not already done)

Regards

Stefan

[Former Member · Accepted solution]

Hi.. Stefan,

Thanks for this great help. Now the problem is resolved.

Thanks again!!!

Regards,

Raju

[Former Member]

While online backup oracle backups each datafile separtly:

begin backup datafile 1
backup operation
end backup datafile 1

begin backup datafile 2
backup operation
end backup datafile 2

begin backup datafile X
backup operation
end backup datafile X

If a datafile will backuped all changes only are written to redologs. The datafile is readonly! After completing the backup of the datafile all changes from redologs will written to datafile... The datafile will updated.

On starting of the db oracle checks that all datafiles have the correct version. If system was crahed before oracle has written data from redologs to datafile oracle doesn't start because the datafile has the wrong version number.

Manually ending the backup mode can result in data lost! Recover the last successfully completed full backup with all following archive logs. This is the savest way.

Greetings

Thomas

[stefan_koehler]

Hello Thomas,

> The datafile is readonly! After completing the backup of the datafile all changes from redologs will written to datafile

That is totally wrong. While an online backup is performed the data file is still written .. just the SCN is "frozen". If you finish your backup and set the data file to "END BACKUP" the SCN is updated to the current .. nothing else.

> Recover the last successfully completed full backup with all following archive logs.

Absolutely wrong!

Just read this one:

http://www.speakeasy.org/~jwilton/oracle/hot-backup.html

Regards

Stefan