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