ora-01113 ora-01110

[bnagaraj]

Hi,

We have a job scheduled to shutdown database and take the cold backup of our database every sunday. Yesterday due to some reason, the backup kicked off every hour.(Database is in NOARCHIVE LOG MODE)

At 8pm the backup completed and I started the database, and then restarted the windows box.

After the restart, the database did not come up and when I tried to start the db, I got the following error message:

ORA-01113: file 13 needs media recovery
ORA-01110: data file 13: 'F:\dbfiles3\prod61\scpodata3.dbf'

As taking the backup again and trying to issue a recover database would take more time (was pressed for time), I just restored from my earlier cold back and brought back the db up.

Now- Theoritically what went wrong:
1) Why did I start getting Ora-01113/01110
2) Given that I am on Noarchivelog mode, could I have just issued 'recover database' to recover from this error.

Please advise;
Nagarajan

[gbrabham]

You could have tried the "alter database recover". It would have
at least tried to read thru the redos and possibly had enough there
to do the recovery that you needed to get the database up.

Gregg

[patel_dil]

Originally posted by bnagaraj
1) Why did I start getting Ora-01113/01110

One possibility is You may have restarted your database without waiting for the O/S backup process of DBF files to get complete.

[bnagaraj]

"One possibility is You may have restarted your database without waiting for the O/S backup process of DBF files to get complete."

What do you actually mean by 'restarted your database without waiting for the o/s backup process of dbf files to complete.

I had restarted the database and then -restarted the box-.

This started after restarting the box.

Nagaraj

[patel_dil]

When you say you restarted the 'BOX', was the database shutdown clean? Sometimes back, I tested trying to do copy the DBF files while the database was open, and tried doing a normal shutdown. It was taking a long time so I did shutdown abort. And when I tried to startup, I got the errors you pointed out.

So my guess is, When you restarted your 'BOX', some of your scripts might have been copying your DBF files and the database was still open. The shutdown may not be proper. Did you check your alert log file? Does it say more?

[badrinathn]

How about this?
http://www.dbasupport.com/forums/sho...light=ora01113

[thanigai]

Did you check the alert log.
Are you sure database did clean shutdown before backup started?.
might have waited for the job to complete...and this kind of case backup is no good.

to overcome this kind of issuses...(Ofcourse Big brains won't agree)
make shutdown script like below..

setenv ORACLE_SID $ORACLE_SID
$ORACLE_HOME/bin/svrmgrl < connect internal
shutdown abort
startup restrict
shutdown
#startup mount
EOF

you are fine.
Thanks.
Thanigaivasan.

[yls177]

i faced that issue too... below is what i did .. i read that from metalink


alter database datafile name offline;
recover tablespace name;
alter tablespace name online;
alter database datafile name online;

[akhadar]

Originally posted by bnagaraj
Hi,

Yesterday due to some reason, the backup kicked off every hour.(Database is in NOARCHIVE LOG MODE)

Nagarajan

Did u find out why the cold backup was kicked off what is the reason? I suspect that OS couldn't copy some of your datafiles.May be this is a I/O error.Recovery is little bit tricky since the database is in Noarchive mode.It is glad that u had a cold backup.Otherwise in this scenario u could have lost some of information while recovering from that datafile.