Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Bule.

Asked: November 23, 2002 - 10:56 pm UTC

Last updated: August 19, 2010 - 1:19 am UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

Dear Tomacle,

Hope you are well.
I have 2 questions:
1. If I want to backup Database in Archive log mode, do I need to backup only archive logs and control files? Do I need redo log files?
But I must backup full database ( Offline backup) but I need not backup Datafiles daily.
I only need to backup archive logs and control files daily. Will I backup archive logs and control files after shutting down Database?
If I don't shutdown Database, will I able to get last information from control file?

2. I have 2 Database servers s1 and s2. One time , an error message ocurrs that no data is retrived from a table. An error message was shown ' I/O error'. I thought that error was occured due to block level corruption. I shut down the Database. I copy the all Datafiles, archive logs, control files, redo log files to another server s2 and start up that new server s2 and the production Database is running smoothly. But when I want to startup the original server S1, the database can not startup due to one datafile FMS_DATA01.DBF :

ORA-01157: cannot identify/lock data file 14 - see DBWR trace file
ORA-01110: data file 14: 'D:\ORA81DATA\FMS_DATA01.DBF'
ORA-27048: skgfifi: file header information is invalid
OSD-04004: invalid file header

But I copied this datafile into another server S2, and that s2 server is running well without any error. But what is the problem? I delete Oracle software including registry from the Problemetic server S1 and install Oracle freshly again on this server. I Copied all required Oracle files from backup into appropriate locations and tried to startup the database. But the same error ocurred.

Please tell what is the problem.

Thanks and regards,


and Tom said...

1) you NEVER want to backup online redo logs -- NEVER (archive or noarchivelog).

The only thing that could happen by backing up online redo logs is you accidently restore them over the current online redo logs -- wiping out any chance for a complete recovery.

Just don't back them up.

You need to read:
</code> http://docs.oracle.com/cd/A87860_01/doc/server.817/a76993/toc.htm
from cover to cover.  There are NO SHORTCUTS here.  The single most important job of a DBA is backup and recovery.  If you don't know this stuff inside-out, backwards and forwards -- you are not doing your job.


NEVER do a cold backup in archive log mode.  That is just an utter and total waste.  OFFLINE backups are for losers.  Hot is the only way to backup an archive log mode database.  Nonarchivelog mode databases -- don't know where they fit in in a production environment personally so they don't really exist.

read
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:3151992035532 <code>

2) yup, thats what I would do -- erase the software and reinstall because a datafile has gone bad. (not). You had a datafile corruption, it frankly sounds like your disk was going bad (had a bad sector). You got really lucky when you copied it the first time and the OS was able to read it. Then the disk went kaput. No more reading of that sector for you anymore. That is why the database could not start.

You should have gone to a backup of the file BEFORE the disk error showed itself. That is the file you should have restored (after determining WHY the disk could not be read) and recovered. That would have been the correct approach -- take your backups (thats what they are there for) and use the archived logs to catch that good file up to the rest of the system. That is what MEDIA recovery is all about.

Please -- read the backup and recovery guide -- read it a couple of times. Understand how all of the pieces work together. Do this if you value your data.


Rating

  (29 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks

Blue, November 24, 2002 - 10:04 pm UTC

Good suggestion and tips , Dear Tom.
Do you suggest for Hot back up?
In archive log mode, I must do hot backup?

What is the main reason not to do cold backup in Archive log mode?

Tom Kyte
November 25, 2002 - 7:20 am UTC

read the second link above for my thoughts on why cold backup in archive log mode is a bad idea.

What should be done?

Blue, November 25, 2002 - 11:21 pm UTC

Thanks Tom for your good idea.
But my query is: If I use Database with archive log mode, I should and must use hot backup and I will never do cold or OS backup?

Tom Kyte
November 26, 2002 - 7:57 am UTC

asked and answered, from above:

....
NEVER do a cold backup in archive log mode. That is just an utter and total
waste. OFFLINE backups are for losers. Hot is the only way to backup an
archive log mode database. Nonarchivelog mode databases -- don't know where
they fit in in a production environment personally so they don't really exist.

......




Good Tips

Blue, November 26, 2002 - 10:14 pm UTC

Dear Tom,
Thank you so much for your good and useful tips.
But I have some curisity, don't mind.
When I need to do cold backup?
Soppose, I backup full all my Datafiles, control files to Tape and I regularly backup my all archivelogs and controlfiles daily. One day my database is lost but I have all datafiles, upto-date controlfiles, archive logs in my Tape. I restore all datafiles, control files and archivelogs from Tape into my Server and startup the Database.
Can I not get last backup information?
What do think, what is this technique and do you think that this technique is not good and this technique should not follow?


Tom Kyte
November 26, 2002 - 10:33 pm UTC

quote: When I need to do cold backup?
answer: only when you are in NO archive log mode. never any other time

quote: Can I not get last backup information?
answer: if you have the last hot/cold backup, all archives and all online redo logs -- you can recover fully. if you lose an(or more) online redo log, you may lose some committed transactions - unless you are also using data guard in 9i, then you WON'T lose the online redo logs.




Backup and Recovery

Blue, November 27, 2002 - 12:42 am UTC

Dear Tom,
Thanks a lot. Sorry to bother you again and again.
I describe a Backup and restore method, I need to know that whether this technique is applicable or not:
-----------------------------------------------
I backup full all my Datafiles once a time to Tape and I regularly backup my all archivelogs and controlfiles daily.
I don't want to backup Datafiles daily, because it takes a huge time thats why I backup (OS backup) all Datafiles one time to Tape. But I backup archivelogs and control files regularly.
One day my database is crashed.
I install the same version of Oracle and the same Database name again on that server and copy Datafiles, controlfiles and archive logs from Tape to the exact locations of the Server.
If I need, I create password file.
Finally I startup the Database and restore the Data from archivelogs which it needs.
Now my questions is: Is it possible to restore Database by this technique?
-----------------------
I believe that I will get your valuable comments on it.


Tom Kyte
November 27, 2002 - 7:27 am UTC

Yes -- but you do want to backup your database more then just once. It would take a really long time to apply all of those archives (and lots of tapes to hold) .

This sounds like a weekly schedule - once a week you backup (hot) all datafiles and during the week you backup archives (and a controlfile to TRACE)

Thanks and Review

Blue, November 27, 2002 - 10:11 pm UTC

Dear Tom,
I am astonished that how you give answer so fast!!!!
Thanks again.
I can tell simply:
1. If I want to do daily backup I should and must use Hot(Online) backup of Datafiles, control files and redo logs.
When I backup controlfiles in this situation , do we get last information in controlfiles? Do I need online redo logs?
2. If We want as weekly backup, We can do cold/OS backup of datafiles. But this takes huge time to recovery data from archive logs.
Am I correct or wrong ?


Tom Kyte
November 28, 2002 - 7:52 am UTC

1) you need to read:

</code> http://docs.oracle.com/docs/cd/A87860_01/doc/server.817/a76993/toc.htm <code>

and absorb 100% of that material. Until you can answer all of these questions in your sleep -- you are not ready to be the primary "keeper of the backups".

you do not backup ONLINE redo logs. you do backup ARCHIVES.

you need to read about the controlfiles in order to understand that you really want a backup of it to TRACE (a textual "create control file" statment). A binary backup is only useful really to create a textual "create controlfile" statment after the fact.

2) weekly would be NO DIFF then daily -- just more archives to apply is all.

You would *not* backup cold.

Reader

A reader, November 28, 2002 - 11:03 am UTC

"
A
binary backup is only useful really to create a textual "create controlfile"
statment after the fact.
"

Does this imply that you can create a textual control file
from th backup of binary control file ?

Tom Kyte
November 29, 2002 - 9:18 am UTC

yup.

</code> http://docs.oracle.com/docs/cd/A87860_01/doc/server.817/a76993/recoscen.htm#11872 <code>

describes what you would do if you lose all current controlfiles. Specifically:

...
Have not backed up the control file using the TO TRACE option, but used the TO filename option of ALTER DATABASE BACKUP CONTROLFILE

Use the control file copy to obtain SQL output. Copy the backup control file and execute STARTUP MOUNT before ALTER DATABASE BACKUP CONTROLFILE TO TRACE NORESETLOGS. If the control file copy predated a recent structural change, edit the TO TRACE output to reflect the structural change.
........

that is a really wonderful manual that if you call yourself a DBA but haven't read it -- then you are not a DBA.



Reader

A reader, November 29, 2002 - 2:47 pm UTC

That relies on the init.ora file. In a total disaster
recovery scenario, if you forget the database name
( database != SID ) or the DB_BLOCK_SIZE, the database
will not mount and trace file can not be generated

Tom Kyte
November 30, 2002 - 8:16 am UTC

well, there is a super finite number of block sizes -- so that is not an issue.

and getting the dbname is pretty easy -- strings SYSTEM.DBF | more, it'll pop to the top...

but yes -- your init.ora files should be part of your backup set and if you use 9ir2 rman and above, it'll make it so for you so you cannot forget it.

(but, use a backup controlfile to trace, that binary one just complicates things)

Thanks

A reader, November 30, 2002 - 11:35 am UTC


Clean Archive Logs

A reader, April 09, 2004 - 5:01 pm UTC

The article does explain how clean Archive logs.

I have cleaned some of my old Archive logs from the destination folder. But I can still
see them from Enterprise manager. What is the problem.

Tom Kyte
April 09, 2004 - 5:19 pm UTC

OEM queries v$ tables to show you where the archives are by default to be found if needed.

OEM is not showing you where they actualy are -- just where an automatic "recover this datafile from a backup" would look for any needed ones.

there is "no problem"

recover archivelog files

sunny, April 22, 2004 - 2:48 am UTC

hai tom,

can i ask u one question.this is sunny.i get one problem during recovering the archive log files.actual the problem is i took backup from my production database(archive log files).And restore it on the standby database(standby database shutdown means OFFLINE stage).when i am appling recovery(sql>recover stand database;)it asked me the auto/cancel.i gave cancel.after that i get WARNING:recovery is succeeded but not open resetlogs.file1 is inconsistent.datafile 1'/disk1..../systemo1.dbf'.this is wrror message i get.please give me the anewer to recover

Tom Kyte
April 22, 2004 - 7:34 am UTC

Your copy of SYSTEM is newer than the last archive you applied. You need to apply some more from production -- system post dates the archives.

are you following the steps as documented in the data guard docs?

recover archivelog files

sunny, April 22, 2004 - 2:49 am UTC

hai tom,

can i ask u one question.this is sunny.i get one problem during recovering the archive log files.actual the problem is i took backup from my production database(archive log files).And restore it on the standby database(standby database shutdown means OFFLINE stage).when i am appling recovery(sql>recover stand database;)it asked me the auto/cancel.i gave cancel.after that i get WARNING:recovery is succeeded but not open resetlogs.file1 is inconsistent.datafile 1'/disk1..../systemo1.dbf'.this is wrror message i get.please give me the anewer to recover

why no cold backup

John, July 23, 2004 - 5:28 am UTC

Tom,

I know you told so many times that to avoid cold backup.
is the reason for this because the database has to be down while it is backed up? or there any other reasons?

thanks

Tom Kyte
July 23, 2004 - 8:42 am UTC

because the database must be down...

which means you told the end users "sorry, you cannot have your data, we are backing up"

which means end users assume "Oracle is not a very available solution is it"

which means I spend lots of time correcting that mistaken assumption.


Same reason I'm against things like scheduled rebuilds of indexes and other needless "offline operations" that make the database unavailable -- it rarely, if ever, needs be unavailable.


Also, you sort of flush all of the caches you spent a long time filling up. the database is much slower after a startup getting them all warm again.

thanks Tom

John, July 25, 2004 - 10:32 pm UTC


some archivelogs

A reader, August 09, 2004 - 4:11 pm UTC

Hi Tom,

i have a recovery qn. i had a full backup of a db taken on jul 15th, 2004. the db generated archive logs after this. for some space issues, i deleted few days worth logs and i have the saved logs from jul 19th, 2004. now if i want to restore only one datafile and i do not have about 4 days logs what will happen? can i still restore the db?

Tom Kyte
August 09, 2004 - 8:14 pm UTC



sorry -- you have "garbage".

sort of like saying "i'd like to know everything you learned since july 15th 2004 -- even though you had amnesia from july 15th-july 19th"

those backups are not useful.

Recovering Tablespaces

Kumar, December 22, 2005 - 1:34 am UTC

Hi Tom,
Please explain me this scenario.
I have cold backup taken at 12.00 AM and after that I add datafiles, extend segments.... on a tablespace, say, users. I accidentaly delete the tablespace including contents. How can I recover the tablespace with the changes?

Regards,
Kumar

Tom Kyte
December 22, 2005 - 10:41 am UTC

It'll be a point in time recovery of the database.

</code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a96566/rcmrecov.htm#1008332 <code>

You have to do a partial recovery upto the point in time right before you did this thing.

automatic online recovery.

abz, August 07, 2006 - 2:34 am UTC

I am running Oracle 9i Release 2 9.2.0.1 on windows 2000
server with NOARCHIVELOG.

Oracle was up and running, I just changed the access rights
of the only datafile I have, and, mistakenly, I disallowed
access to that datafile for the Administrator user through which oracle accesses the datafile. So oracle was not able
to write datafile. After some time we restarted the whole
system, and came to know that oracle database is not opening, we realized our mistake and corrected it, that is
we allowd full access to that datafile for Administrator user through which oracle accesses that datafile, and
then we restared that whole system again, but the oracle
was not opening the database, it was giving error "file needs media recovery". We didnt had any kind of backup of our database, So on SQL prompt (while the database was in mount state) I run RECOVER DATABASE, it completed successfuly, and then the database opened, and now its running fine.

RECOVER DATABASE applied the online redolog files and made the datafile consistent, right?

So my question is, oracle says, it does online recovery automatically, so why it needed for me to explicitly issue RECOVER DATABASE.




Tom Kyte
August 07, 2006 - 8:03 am UTC

you got lucky here - really really lucky.

in NOarchivelog mode, all I can promise is someday you will lose everything since your last full backup. Not MIGHT lose - WILL LOSE. You almost did lose it here.


The file was offlined, it was not a normal "crash recovery", you brought the file back into Oracle and has to recover it. FORTUNATELY, you were so very very very lucky to have all of the redo we needed in the online logs - had they switched and overwritten themselves - you would have lost that file.

You are very lucky today.
You will not be so lucky in the future.

redo switch

abz, August 07, 2006 - 8:22 am UTC

Ok, but you said

"had they switched the redo and overwritten..."

How can it switch the online redolog files until it
didnt get confirmation that the data is written to
DATAFILE.

By the way its not our production database, so nothing
to worry about much.


Tom Kyte
August 07, 2006 - 8:32 am UTC

the file was taken offline - it was not "part of the database" anymore. You took it out of our control.

There is no such thing as a non-production database :) it was a production instance for someone. Else, why did you even bother with fixing it?

offline?

abz, August 07, 2006 - 9:46 am UTC

The file was not take offline with ALTER TABLESPACE DROP DATAFILE .... OFFLINE.

Its access rights were changed accidently through Operating System.

Do you mean in this situation, even though the DATAFILE is not written the DBWR will give go ahead signal for switching the log file?

Tom Kyte
August 07, 2006 - 9:32 pm UTC

it is like you just took the datafile away from it, you ripped it away, it is like the disk disappeared (well, not only like - but exactly like)

you are no noarchivelog mode, you have stated very loudly that "losing data is dandy, ok, not a problem, go for it"

non-production database

abz, August 10, 2006 - 9:40 am UTC

:)

ok then why at metalink when we raise TAR,
oracle ask us if it is a production database or not :)



file header information is invalid

barmadu, December 10, 2008 - 5:22 am UTC

Hi,

I try to recover dbf files I backed up with a cold backup. While I try to recreate those controlfiles I have the same error as the thread creator:

ORA-01157: cannot identify/lock data file 14 - see DBWR trace file
ORA-01110: data file 14: 'D:\ORA81DATA\FMS_DATA01.DBF'
ORA-27048: skgfifi: file header information is invalid
OSD-04004: invalid file header

It seems that those files are corrupted. I tried to recover them on different Oracle versions, but no way.

Is there any solutions to recover them ?

Sorry for my english.

Thanks !
Tom Kyte
December 10, 2008 - 9:53 am UTC

please utilize support.


one wonders what process 'corrupted' them - is it possible for example you did the restore via FTP and you did not do the FTP in binary mode? Have you tried - re-restoring the files.


no support

barmadu, December 11, 2008 - 9:41 am UTC

Thanks for the answer !

I have no support :(
I didn't do the restore but I know it was done by ftp (I don't know in which mode)

Yes I tried to restore the files and I have the same errors.

thanks for help !



Tom Kyte
December 11, 2008 - 7:08 pm UTC

you probably corrupted the files with ftp.

make sure they are the same size as they are in the backups - make sure you issued "type bin" before pulling or pushing the files over ftp.

otherwise windows will change \n into \r\n (carriage return into carriage return/line feed)

Alexander, September 01, 2009 - 1:33 pm UTC

Hi Tom,

I came across something new today, I hope you can provide your two cents.

A developer reported in the morning their application wouldn't start up, they were getting this error:

ORA-00376: file 9 cannot be read at this time ORA-01110: data file 9: '/ocp25d/data02/SVC_INDX_02.dbf'


So checked dba_data_files, and it was left in RECOVERY status. I have no idea why. The only things I'm running against this database are exports and hot backups. Would either of those switched a datafile's status?

I'm also getting these messages while attempting to restore it

channel ORA_SBT_TAPE_1: starting datafile backupset restore
channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
restoring datafile 00009 to /ocp25d/data02/SVC_INDX_02.dbf
channel ORA_SBT_TAPE_1: reading from backup piece backup_OCP25D_jpknkeur_1_1
ORA-19870: error reading backup piece backup_OCP25D_jpknkeur_1_1
ORA-19504: failed to create file "/ocp25d/data02/SVC_INDX_02.dbf"
ORA-27044: unable to write the header block of file
Linux Error: 6: No such device or address
Additional information: 7
failover to previous backup


Tom Kyte
September 01, 2009 - 5:49 pm UTC

is the mount point ocp25d *available*, I'd say "no"

Alexander, September 02, 2009 - 4:47 pm UTC

Yeah, we had some disk errors.

So I can restore that file into a new fs using the SET NEWNAME clause no problem right?
Tom Kyte
September 03, 2009 - 7:24 am UTC

what did you do when you practiced your recovery once a week in the past? (You are a long time reader of this site, you know I say "the only thing a DBA cannot get wrong is recovery - we can fix everything else, but if they cannot recover - ouch. Practice practice practice. Do things like 'do not exp/imp for test, just restore'".... etc...


but yes

Alexander, September 03, 2009 - 4:01 pm UTC

It surprises me you know who people are. In my case I'm not sure if that's a good or bad thing.

I practice the best I can but our crappy tape system makes it very difficult, we have few tape drives so things are very slow.

That bring me to my next question, I'm trying to figure out what's going on with my restore. It's taking an eternity, I want to find the bottle neck. What does this mean to you:

SQL> @rman_restore_progress.sql
SQL> ---------------------------------------------------------
SQL> -- RMAN RESTORE PROGRESS...
SQL> ---------------------------------------------------------
SQL> SET FEEDBACK OFF
SQL> ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS' ;
SQL> SET ECHO ON
SQL> COL SOFAR FORMAT 999,999,999,999
SQL> COL TOTALWORK FORMAT 999,999,999,999
SQL> COL %_COMPLETE FORMAT 999.9
SQL> COL MESSAGE format a40
SQL> SELECT SID, SERIAL#, SOFAR, TOTALWORK
  2       , ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
  3       , SYSDATE + TIME_REMAINING/3600/24 END_AT
  4       , MESSAGE
  5    FROM V$SESSION_LONGOPS
  6   WHERE OPNAME LIKE 'RMAN%'
  7   ORDER BY 6 DESC
  8  /

SID    SERIAL#            SOFAR        TOTALWORK %_COMPLETE END_AT              MESSAGE
---------- ---------- ---------------- ---------------- ---------- ------------------- ----------------------------------------
       152          1          232,264        1,536,000       15.1 2009-09-05 00:39:31 RMAN: full datafile restore: Set Count 5
                                                                                       743: 232264 out of 1536000 Blocks done

       159          7        8,165,063        9,341,440       87.4 2009-09-03 19:09:01 RMAN: aggregate input: restore 347: 8165
                                                                                       063 out of 9341440 Blocks done

       155          9        2,135,040        2,135,040      100.0 2009-09-03 15:30:07 RMAN: full datafile restore: Set Count 5
                                                                                       777: 2135040 out of 2135040 Blocks done

       157         17          166,400          166,400      100.0 2009-09-03 15:30:07 RMAN: full datafile restore: Set Count 5
                                                                                       779: 166400 out of 166400 Blocks done

SQL> COLUMN EVENT FORMAT a10
SQL> COLUMN SECONDS_IN_WAIT FORMAT 999
SQL> COLUMN STATE FORMAT a20
SQL> COLUMN CLIENT_INFO FORMAT a30
SQL>
SQL> SELECT p.SPID, sw.EVENT, sw.SECONDS_IN_WAIT AS SEC_WAIT,
  2         sw.STATE, CLIENT_INFO
  3  FROM V$SESSION_WAIT sw, V$SESSION s, V$PROCESS p
  4  WHERE sw.EVENT LIKE '%sbt%'
  5         AND s.SID=sw.SID
  6         AND s.PADDR=p.ADDR
;  7

SPID         EVENT        SEC_WAIT STATE                CLIENT_INFO
------------ ---------- ---------- -------------------- ------------------------------
17001        Backup: sb          6 WAITING              rman channel=ORA_SBT_TAPE_1
             tread2


That looks to me like it's not waiting on tape long, and it's restoring the datafile (which should be really fast).
Tom Kyte
September 04, 2009 - 3:50 pm UTC

that is how long the last wait/current wait is, not the cumulative time

... V$SESSION_WAIT displays the resources or events for which active sessions are waiting.
.....

so that means it has been waiting for the last six seconds at least (maybe closer to nine, we tend to update in 3 second increments)

Alexander, September 04, 2009 - 4:12 pm UTC

Do you know how it accesses information from tape? I don't understand what is happening. If I keep re-running the 2nd query, it varies showing 6 secs, 9, 3....as if it's constantly going back and forth. Does it go and grab x number of datafiles? Does it get all them at once? Does it restore them one at a time as it gets them, etc?
Tom Kyte
September 06, 2009 - 8:59 am UTC

it reads, then it writes, reads, writes, reads, writes.


Alexander, September 07, 2009 - 8:09 pm UTC

I had to jinx myself and say it was easy. Why is this trying to touch the fs I specifically told it not to use and go somewhere else?

RMAN> run { set until time "TO_DATE('2009-08-31 18:00:00','YYYY-MM-DD HH24:MI:SS')" ;
2>         set newname for datafile 8 to '/ocp25d/data01/SVC_DATA_02.dbf';
3>         set newname for datafile 9 to '/ocp25d/data01/SVC_INDX_02.dbf';
4>         restore database ;
5>         recover database ;
6>       }

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 07-SEP-09
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_SBT_TAPE_1

skipping datafile 1; already restored to file /ocp25d/data01/system01.dbf
skipping datafile 3; already restored to file /ocp25d/data01/sysaux01.dbf
skipping datafile 4; already restored to file /ocp25d/data01/users01.dbf
skipping datafile 2; already restored to file /ocp25d/undo01/undotbs01.dbf
skipping datafile 7; already restored to file /ocp25d/data01/SVC_CLOB_01.dbf
skipping datafile 5; already restored to file /ocp25d/data01/SVC_DATA_01.dbf
skipping datafile 6; already restored to file /ocp25d/data01/SVC_INDX_01.dbf
datafile 8 is already restored to file /ocp25d/data01/SVC_DATA_02.dbf
datafile 9 is already restored to file /ocp25d/data01/SVC_INDX_02.dbf
Finished restore at 07-SEP-09

Starting recover at 07-SEP-09
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_SBT_TAPE_1

starting media recovery
media recovery failed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 09/07/2009 19:19:46
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed
 start until time 'AUG 31 2009 18:00:00'
ORA-00283: recovery session canceled due to errors
ORA-01110: data file 9: '/ocp25d/data02/SVC_INDX_02.dbf'
ORA-01157: cannot identify/lock data file 9 - see DBWR trace file
ORA-01110: data file 9: '/ocp25d/data02/SVC_INDX_02.dbf'


data01 = good
data02 = bad

Alexander, September 08, 2009 - 7:46 pm UTC

Tom,

So the restore completed, but I choose a time to restore to when the datafile was still offline. I dropped it because it's just indexes, but for whatever reason it's still in v$datafile. The application still has problems trying to read from it.

(x212kdc:oracle)> sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.2.0 - Production on Tue Sep 8 10:29:58 2009

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> alter database datafile '/ocp25d/data01/SVC_INDX_02.dbf' offline drop;

Database altered.

SQL> select name from v$datafile;

NAME
------------------------------------------------------------------------------------------------------------------------------------
/ocp25d/data01/system01.dbf
/ocp25d/undo01/undotbs01.dbf
/ocp25d/data01/sysaux01.dbf
/ocp25d/data01/users01.dbf
/ocp25d/data01/SVC_DATA_01.dbf
/ocp25d/data01/SVC_INDX_01.dbf
/ocp25d/data01/SVC_CLOB_01.dbf
/ocp25d/data01/SVC_DATA_02.dbf
/ocp25d/data01/SVC_INDX_02.dbf

9 rows selected.

??? Why is it there?
Tom Kyte
September 09, 2009 - 6:10 am UTC

drop the tablespace that goes with it, the index segments are still there as far as we are concerned.

Cold Backup No archive log mode

A reader, August 17, 2010 - 1:59 am UTC

My database is running in no archive log mode.I have the cold back up of my datafiles.I lost a datafile.I restored it from the latest cold backup,but cant open the database as it is asking for media recovery.I tried to recover it but getting error:ORA-00279,ORA-00289,ORA-00280,Specify log:{(RET)=suggested|filename|AUTO|CANCEL}
Tom Kyte
August 19, 2010 - 1:19 am UTC

if you are in noarchivelog mode - you have made the decision to LOSE DATA AT SOME POINT.

that must be very much understood - noarchivelog mode = YOU WILL LOSE DATA.


In noarchivelog mode, there is NO MEDIA recovery - none, if you restore one file from backup - you have to restore ALL FILES from backup and you lose EVERY CHANGE made since that backup.

Period.

That is the (fully documented, well understand, been that way since the beginning of time) way it works.

Cold Backup No archive log mode

sajreena, August 17, 2010 - 2:01 am UTC

My database is running in no archive log mode.I have the cold back up of my datafiles.I lost a datafile.I restored it from the latest cold backup,but cant open the database as it is asking for media recovery.I tried to recover it but getting error:ORA-00279,ORA-00289,ORA-00280,Specify log:{(RET)=suggested|filename|AUTO|CANCEL}

More to Explore

Backup/Recovery

Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.