Lost all Redo log file -- why does rman backup DB without redolog ?

 -- Thanks for the question regarding "Lost all Redo log file", version 9208

Submitted on 15-Jul-2009 15:58 UTC

Tom's latest followup | Bookmark | Bottom

Last updated 14-Sep-2012 18:46

You Asked

Hi Tom,
I am practising Recovery .
CASE 1)I have Development TEST DB in NON-ARCHIVE Mode. NEVER backed up.

All Redo log files including the active one ,are dropped from the OS.

How to recover such a DB. I dont mind loosing Transactions.

Is it possible ?

CASE 2 )I have Development TEST DB in ARCHIVE Mode. NEVER backed up.

All Redo log files including the active one ,are dropped from the OS.
Have all the archived logs.

How to recover such a DB. I dont mind loosing Transactions.

Is it possible ?

and we said...

1) if the database was open and you removed the redo and the database is not shutdown normal (eg: it crashed), you have just lost all of your data.

If your redo was lost and the database was either

a) not running, had been shutdown NORMAL (not abort, not crashed), then you have no problem.

b) running - but you were able to shutdown normal (since in unix, erasing a file doesn't really erase it if someone has it open), then you have no problem.

But if the database was shutdown abort or otherwise crashed - you have lost it all.

sys%ORA9IR2> select log_mode from v$database;

LOG_MODE
------------
NOARCHIVELOG

sys%ORA9IR2> select member from v$logfile;

MEMBER
-------------------------------------------------------------------------------
/home/ora9ir2/oradata/ora9ir2/redo01.log
/home/ora9ir2/oradata/ora9ir2/redo02.log
/home/ora9ir2/oradata/ora9ir2/redo03.log

sys%ORA9IR2> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
sys%ORA9IR2> !rm /home/ora9ir2/oradata/ora9ir2/redo01.log

sys%ORA9IR2> !rm /home/ora9ir2/oradata/ora9ir2/redo02.log

sys%ORA9IR2> !rm /home/ora9ir2/oradata/ora9ir2/redo03.log

sys%ORA9IR2> startup
ORACLE instance started.

Total System Global Area  235999648 bytes
Fixed Size                   450976 bytes
Variable Size             201326592 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/home/ora9ir2/oradata/ora9ir2/redo01.log'


sys%ORA9IR2> shutdown
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
sys%ORA9IR2> startup mount
ORACLE instance started.

Total System Global Area  235999648 bytes
Fixed Size                   450976 bytes
Variable Size             201326592 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.
sys%ORA9IR2> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery


sys%ORA9IR2> recover database until cancel;
Media recovery complete.
sys%ORA9IR2> alter database open resetlogs;

Database altered.

sys%ORA9IR2> select member from v$logfile;

MEMBER
-------------------------------------------------------------------------------
/home/ora9ir2/oradata/ora9ir2/redo01.log
/home/ora9ir2/oradata/ora9ir2/redo02.log
/home/ora9ir2/oradata/ora9ir2/redo03.log

sys%ORA9IR2> !ls -l /home/ora9ir2/oradata/ora9ir2/redo01.log
-rw-rw----  1 ora9ir2 ora9ir2 104858112 Jul 16 12:13 
/home/ora9ir2/oradata/ora9ir2/redo01.log

2) same thing as above. If your database was shutdown clean AND THEN the logs were lost - no problem.

If your database was shutdown crashed - we needed those redo logs to perform instance crash recovery and you have just lost all of your data - your archives are useless.

Reviews

Please elaborate   February 8, 2011 - 11am UTC Bookmark | Bottom | Top Reviewer: Arvind from St. Louis, USA Hi Tom, From the above explanation "since in unix, erasing a file doesn't really erase it if someone has it open" Can you please elaborate it a little, what would be the case when we would erase the online redo log file but they will not be deleted? How would someone have it opened? What did you mean by this in context of the redo logs? Another non-related question -- Can we get a notification email when you post a reply to our queries here on AskTom? Thanks a lot! Arvind Followup   February 10, 2011 - 3pm UTC: say the oracle database is writing to redo01.log. It has it open, the file is opened by that process. say you rm redo01.log. The file is still open by the oracle process, but the rm will SUCCEED. The rm simply unlinks the filename in the directory - it doesn't remove the file data. The oracle process will continue to write to the file - the file just won't exist in the directory (the directory entry is gone) When the last process that has that redo01.log file opens it - because the directory entry is wiped it - the file data will become unlinked to - then the file is truly erased. Notifications can be had if you are the one that asked the original question only.   July 19, 2012 - 1am UTC Bookmark | Bottom | Top Reviewer: Bhuban from Delhi, India It is a good example. After recovery database in mount status the redo files are created by oracle process itself as the naming conventions are already in controlfile. --Thanks!!! Lost redo log   September 13, 2012 - 2pm UTC Bookmark | Bottom | Top Reviewer: Huru from Azerbaijan Baku HI TOM! I am practising Recovery and lost my redo log files [oracle@localhost orcl]$ ls control01.ctl redo01.log system01.dbf undotbs01.dbf example01.dbf sysaux01.dbf temp01.dbf users01.dbf 1.SQL> startup ORACLE instance started. Total System Global Area 975081472 bytes Fixed Size 1340664 bytes Variable Size 620759816 bytes Database Buffers 348127232 bytes Redo Buffers 4853760 bytes Database mounted. ORA-03113: end-of-file on communication channel Process ID: 2289 Session ID: 125 Serial number: 5 2.SQL> startup mount ORA-24324: service handle not initialized ORA-01041: internal error. hostdef extension doesn't exist 3. SQL> alter database open resetlogs; ERROR: ORA-03114: not connected to ORACLE 4.SQL> recover database until cancel; ERROR: ORA-03114: not connected to ORACLE Followup   September 14, 2012 - 6pm UTC: you do not give sufficient information to help you. tell us about your datafiles, how you shutdown the database, what you actually *did*