Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question.

Asked: July 15, 2009 - 3:58 pm UTC

Last updated: June 01, 2021 - 1:45 am UTC

Version: 9208

Viewed 50K+ times! This question is

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 Tom 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.

Rating

  (9 ratings)

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

Comments

Please elaborate

Arvind, February 08, 2011 - 11:02 am UTC

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


Tom Kyte
February 10, 2011 - 3:54 pm 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.

Bhuban, July 19, 2012 - 1:29 am UTC

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

Huru, September 13, 2012 - 2:03 pm UTC

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

Tom Kyte
September 14, 2012 - 6:46 pm UTC

you do not give sufficient information to help you. tell us about your datafiles, how you shutdown the database, what you actually *did*

Loss of Redo Log file in No Archive mode

karthikeyan, April 11, 2013 - 2:37 pm UTC

Hi Tom ,
I have seen the reviews for 'Recovering the Redo Log file when it was deleted with out having backup and in No archive log mode ....
But i don't know how it create the redo log file using cancel based recovery and why we use reset set logs there ...
but i know giving this commands i can get back my database but i dont know what was happening at the background ( i mean , how it creates the log file
Tom Kyte
April 22, 2013 - 2:57 pm UTC

it doesn't create the log file?????


if you have a log file for every hour (assume, for this example)...

and you lost the redo log file from 9am (it is 12noon right now). and your disks crash

the BEST you can do is restore your last full backup and roll forward until JUST BEFORE 9am (you lost 9am-10am!!!!) and open your database. You cannot do a complete recovery.

about redologs

srinadh, August 15, 2016 - 9:07 am UTC

hai tom,
actually i removed redologs from os (without opening the database) so i couldnt open my database so i followed the above procedure i was getting like this
sql>startup mount;
ORA-03113: end-of-file on communication channel

Connor McDonald
August 15, 2016 - 11:58 am UTC

Well...its sound like your scenario was different to the one in this post....

When you lose a database...priority #1 is STOP. Assess. Then (and only then) act.

Recovered my database with the steps outlined

A reader, December 05, 2017 - 3:55 am UTC

The steps outlined worked like magic. thanks a lot.
Connor McDonald
December 06, 2017 - 1:04 pm UTC

Glad we could help

Development TEST DB in NON-ARCHIVE Mode. NEVER backed up

Mohamed Abuhaya, May 27, 2021 - 9:22 pm UTC

Hello Tom,
I faced the same problem, but I stuck with this error:
ORA-16433: The database must be opened in read/write mode.
Why? and I hope to help?
----------------------------
Look at my steps to solve:

SQL> startup
ORACLE instance started.

Total System Global Area 5144301568 bytes
Fixed Size 2290176 bytes
Variable Size 1006636544 bytes
Database Buffers 4127195136 bytes
Redo Buffers 8179712 bytes
Database mounted.
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1:
'C:\APP\ADMINISTRATOR\ORADATA\OAPEX\REDO02.LOG'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.


SQL> shutdown
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 5144301568 bytes
Fixed Size 2290176 bytes
Variable Size 1006636544 bytes
Database Buffers 4127195136 bytes
Redo Buffers 8179712 bytes
Database mounted.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery


SQL> recover database until cancel;
ORA-00283: recovery session canceled due to errors
ORA-16433: The database must be opened in read/write mode.


SQL> alter database open read write;
alter database open read write
*
ERROR at line 1:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1:
'C:\APP\ADMINISTRATOR\ORADATA\OAPEX\REDO02.LOG'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
Connor McDonald
May 28, 2021 - 7:13 am UTC

Without redologs, the chances are you have lost your data.

You could try this:

alter system set "_allow_resetlogs_corruption"=true scope=spfile;
alter system set undo_management='MANUAL' scope=spfile;
shutdown abort;
startup mount;
alter database open resetlogs;

but be aware that you are basically telling the database "Hey, its ok if stuff is corrupted".

You may have to shutdown abort / startup several times before the database will finally open, and even then, the data is now questionable. If you *do* get it open, immediately export the data (do not take a backup, because those files are no longer reliable) and re-import into a new database.


Development TEST DB in NON-ARCHIVE Mode. NEVER backed up

Mohamed Abuhaya, May 29, 2021 - 10:26 am UTC

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery


SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'C:\APP\ADMINISTRATOR\ORADATA\OAPEX\SYSTEM01.DBF'
Connor McDonald
June 01, 2021 - 1:45 am UTC

You can try the steps in this post

https://smarttechways.com/2013/03/23/allow-resetlogs-corruption-steps/

but I stress, you're heading down a slippery slope here

Development TEST DB in NON-ARCHIVE Mode. NEVER backed up

Mohamed Abuhaya, May 29, 2021 - 10:29 am UTC

After Recreate Control File, I face the following errors:
Control file created.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [2662], [0], [8816932], [0],
[8825870], [12583104], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [2662], [0], [8816931], [0],
[8825870], [12583104], [], [], [], [], [], []
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2662], [0], [8816927], [0],
[8825870], [12583104], [], [], [], [], [], []
Process ID: 2988
Session ID: 191 Serial number: 1