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?
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?
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?
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.
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 ?
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 ?
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
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.
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
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
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?
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
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.
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.
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?
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 !
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 !
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
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?
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).
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?
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
September 07, 2009 - 8:33 pm UTC
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?
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}
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}