| REDO LOG concepts [message #238154] |
Wed, 16 May 2007 21:17  |
dba_bng
Messages: 13
Registered: April 2007
|
Junior Member |
|
|
Hi Gurus,
As we know when we issue a COMMIT the log buffer content is flushed to the online redo log file before the "Commit complete" status is returned to the user. Please correct me if I am wrong.
I have the following scenario...
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u04/oradata/BIDWDB01/redo03.log
/u04/oradata/BIDWDB01/redo02.log
/u04/oradata/BIDWDB01/redo01.log
SQL> !rm /u04/oradata/BIDWDB01/redo01.log
SQL> !rm /u04/oradata/BIDWDB01/redo02.log
SQL> !rm /u04/oradata/BIDWDB01/redo03.log
SQL> connect scott/oracle
Connected.
SQL> update emp set ename=ename;
14 rows updated.
SQL> commit;
Commit complete.
SQL> commit;
Commit complete.
SQL> conn / as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 4479516672 bytes
Fixed Size 1984504 bytes
Variable Size 905975816 bytes
Database Buffers 3556769792 bytes
Redo Buffers 14786560 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u04/oradata/BIDWDB01/redo01.log'
-----------------------
My question is even after deleting all the online redo log files how did Oracle returned the Commit complete status to the user? What happened to the log buffer data flush?
I am using 10.2.0.2 on Solaris 9 (64-bit)
Regards,
Deepak
|
|
|
|
|
|
| Re: REDO LOG concepts [message #238158 is a reply to message #238154] |
Wed, 16 May 2007 21:49  |
dba_bng
Messages: 13
Registered: April 2007
|
Junior Member |
|
|
Hi,
Thanks a lot for your reply. But what will happen to the transactions commited after the redo log files got deleted. As Oracle claims that you can not loose a single commited transaction. Here it is allowing me to commit transactions even if the redo logs are not available.
It looks like Oracle will use virtual log files. Then again till how long this instance will continue running? for ever, untill shutdown by the DBA or any other problem happens?
|
|
|
|
|
|
| Re: REDO LOG concepts [message #238162 is a reply to message #238154] |
Wed, 16 May 2007 22:04 |
dba_bng
Messages: 13
Registered: April 2007
|
Junior Member |
|
|
Thanks for your reply. But till how long the instance can keep running like this?
Will it also be applicable if we delete data files as well? Though have not tried and would love to try that...
|
|
|
|
|
|
|
|
|
|
| Re: REDO LOG concepts [message #238205 is a reply to message #238193] |
Thu, 17 May 2007 01:51 |
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
|
Senior Member |
|
|
hi zeeshan,
after deleting the files using rm command too, the v$log file will list the log files.
This is because, the file are deleted physically from the disk, not logically from the database control file.
|
|
|
|
| Re: REDO LOG concepts [message #238212 is a reply to message #238162] |
Thu, 17 May 2007 02:10 |
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
|
Senior Member |
|
|
hi dba bng,
I think you are doing the testing case study for redo logs..
Dear, as said in above replys, though you deleted the files physically using rm, in *nix system, they will not be deleted actually until all the applications or process that refereces or uses it be closed..
In your case, you are deleting files from SQL prompt using !rm command but the oracle client is still using these redo log files. That's why after deleting them, when you try to commit (using other user scott too) , it does it for you without any complaint!!
Then after when you shutdown your database, those redo log files are no more referenced and then will be deleted actually..
So afterward, when you try to start database, the control file is loaded and read which still contains path for redo logfiles which you have deleted (As you haven't remove it's entry from database i.e. you havn't deleted them logically).
Those files are not found physically, So oracle will return an error, and will not start (As it needs atleast 2 redo log files to start).
This is what i understarnd. Hope this helps you clear your concepts..
Regards..
|
|
|
|
| Re: REDO LOG concepts [message #238221 is a reply to message #238154] |
Thu, 17 May 2007 02:31 |
saibal
Messages: 111
Registered: March 2007
Location: India
|
Senior Member |
|
|
|
The 'Commit complete' message that you received in spite of deleting the redo logs could be because the actual commit in oracle is the generation of SCN. Therefore if the SCN could be generated--the commit SCN-- then it is understandable that the commit complete message was generated
|
|
|
|
| Re: REDO LOG concepts [message #240924 is a reply to message #238221] |
Sun, 27 May 2007 16:27 |
yogendra@80
Messages: 3
Registered: May 2007
|
Junior Member |
|
|
saibal>The 'Commit complete' message that you received in spite of deleting the redo logs could be because the actual commit in oracle is the generation of SCN. Therefore if the SCN could be generated--the commit SCN-- then it is understandable that the commit complete message was generated
As far as i am aware the commit is not completed for any transaction till the redo is written for that transaction in the redolog.Simply generating some SCN (the commit SCN ) and keeping it in memory (read LOG BUFFER) will not end the transaction. So although the redo log files are removed physically but still the LGWR is able to access the current redo log file and will be writing in it (as explained in the posts above) the transaction entries and so sucessful transactions will occur till the current log fills. As soon as log switch happns the LGWR will not be finding next redo log file(coz it's been removed physically already) so the db will hang.......Please correct me if i am wrong ......and then furher steps like starting db in mount mode then recreating log groups and removing the obsolete log groups logically is to be carried out........
One question....whether the archiver will be able to archive that current redo log file during whose time the redo logs were physically removed???
|
|
|
|
|
|
| Re: REDO LOG concepts [message #240927 is a reply to message #240924] |
Sun, 27 May 2007 20:04 |
saibal
Messages: 111
Registered: March 2007
Location: India
|
Senior Member |
|
|
@yogendra@80
| Quote: | As far as i am aware the commit is not completed for any transaction till the redo is written for that transaction in the redolog.
|
Let me pecisely try to clarify what happens when we commit:
An SCN is generated--the commit SCN.
Any remaining redo is flushed from the log buffer to the log files
and the SCN, which has already been generated is written to the header of the logfiles. THIS STEP IS INDEED THE COMMIT. If this step has occurred, then you have committed your transaction, else you haven't.Period. Check the view v$TRANSACTION, before and after this step--you will find that an entry for the transaction that existed before, disappeared after this step was completed.
The locks held by the transaction are released.
There is a 'fast block cleanout.'
Now, regarding the question of the OP, since deleting the redo logs physically, without first dropping the log files, means on a logical level, Oracle can still see the header of the log files and can actually record the SCN there--if this step was not happening, the OP would not have received the COMMIT COMPLETE message.
As for your question of whether you can archive the physically deleted log files, my answer would be 'NO'.
Regards
[Updated on: Sun, 27 May 2007 20:16] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
