Home » RDBMS Server » Server Administration » REDO LOG concepts
REDO LOG concepts [message #238154] Wed, 16 May 2007 21:17 Go to next message
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 #238156 is a reply to message #238154] Wed, 16 May 2007 21:38 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
With most *nix (including Solaris) OS if/when 1 process has a file open & a 2nd process issues a "delete", the file (& disk space it consumes) does not really go away (returned to OS) until the 1st process closes the file.

For example, if you have a VERY, VERY, VERY large listener.log file & issue the "rm listener.log" command; the space does not get reclaimed until the listener process (which has the file locked open) get shutdown/restarted.

The same is true for Oracle & the redo log files.
Re: REDO LOG concepts [message #238158 is a reply to message #238154] Wed, 16 May 2007 21:49 Go to previous messageGo to next message
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 #238160 is a reply to message #238154] Wed, 16 May 2007 21:58 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
> As Oracle claims that you can not loose a single commited transaction

Oracle also claims to be UNBREAKABLE.

The rule is, there are exceptions to every RULE; but this rule.

If you purposely delete Oracle files, you deserve what ever happens & you could have a MAJOR challenge to get the DB to ever open again.
Re: REDO LOG concepts [message #238162 is a reply to message #238154] Wed, 16 May 2007 22:04 Go to previous messageGo to next message
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 #238163 is a reply to message #238154] Wed, 16 May 2007 22:13 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>would love to try that...
Nobody is stopping you from performing your own tests.
Re: REDO LOG concepts [message #238191 is a reply to message #238154] Thu, 17 May 2007 01:21 Go to previous messageGo to next message
zeshanali76
Messages: 180
Registered: January 2006
Location: Pakistan
Senior Member

The transaction U have committed was written in Archive log and DBWr wrote in Data file. And also keep in mind the deletion process of files from HD is that file is deleted just header deleted of file. file exists there. When u reboot ur database it tried to read the redo file but there was no header of that file and finally database could not started successfully. In this case there is a point of OS. *nix deletes file while running, for this u can search a script that if file is in use then cannot be deleted. U cannot do this practice on Windows. Windows would never delete/rename a file while it is in use. ok


Zeeshan Ali Awan
Re: REDO LOG concepts [message #238193 is a reply to message #238154] Thu, 17 May 2007 01:24 Go to previous messageGo to next message
zeshanali76
Messages: 180
Registered: January 2006
Location: Pakistan
Senior Member

One thing more i wanna share. u have checked the V$logfile b4 deleting them u should check them also after deleting them. What would be the status of v$logfile. It would show u ur log members and logfiles there.


Zeeshan Ali Awan
Re: REDO LOG concepts [message #238205 is a reply to message #238193] Thu, 17 May 2007 01:51 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #240925 is a reply to message #238154] Sun, 27 May 2007 17:58 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>One question....whether the archiver will be able to archive that current redo log file during whose time the redo logs were physically removed???

What will you do if 1 person says YES & another person says NO?

Conduct your own test & tell us what you learn.
Re: REDO LOG concepts [message #240927 is a reply to message #240924] Sun, 27 May 2007 20:04 Go to previous message
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

Previous Topic: diff B/W DBA and APPS DBA
Next Topic: renaming database name
Goto Forum:
  


Current Time: Mon Dec 05 02:46:36 CST 2016

Total time taken to generate the page: 0.10540 seconds