RE: REDO LOG Concepts

From: Peter McLarty <p.mclarty_at_cqu.edu.au>
Date: Thu, 17 May 2007 14:18:04 +1000
Message-ID: <27AA2E9CA7A0C44283BC1E9B00086AA90673903B@UNIMAIL.staff.ad.cqu.edu.au>

Hi Deepak
You will likey find that your database when you succeed in opening it does not show the transaction as successful. This is because the commit caused the lgwr to write your activity to the redo logs however the dbw has not at this time written your transaction to the datafile. This means that when you issued the shutfown immediate, the redo log items are not necessarily written to the datafiles. There is no requirement for Oracle to do this, I suspect that they do.

The concepts guide is not clear on this with an immediate shutdown, however if it was a shutdown abort your tranaction would in effect be rolled back(not a true roll back) as in the abort Oracle waits until the restart to apply or not apply information in the redo files.

From: DBA Deepak [mailto:oracle.tutorials_at_gmail.com] Sent: Thursday, 17 May 2007 01:08 PM
To: daniel.fink_at_optimaldba.com
Cc: oracle-l_at_freelists.org
Subject: Re: REDO LOG Concepts

Hi Daniel,

I did not try for other processes. But will the rm command in OS command queue? Is there any posibility to rollback the delete when the instance is open? And if at all it is possible will the commited transactions be reclaimed?

Deepak

On 5/17/07, Daniel W. Fink <daniel.fink_at_optimaldba.com> wrote:

This occurs as at least one of the Oracle processes (LGWR, others?) still has the redo log open. You can rm it and it will 'disappear' from the file list, but as it is still open by LGWR. so when the log buffer is flushed and written by LGWR, it is written to the file that LGWR still has open. Once you shutdown the instance, LGWR terminates and lets go of the file, which is then 'officially' rmed. When the instance is restarted, the process(es) attempt to open the file, but cannot as it has been deleted.

	-- 
	Daniel Fink
	
	Oracle Performance, Diagnosis and Training
	
	OptimalDBA    http://www.optimaldba.com
<http://www.optimaldba.com/> 
	Oracle Blog   http://optimaldba.blogspot.com

<http://optimaldba.blogspot.com/>

	Join me at Miracle Scotland DB Forum!
	http://www.miracleltd.com/index.asp?page=167&page2=343
	
	
	DBA Deepak wrote: 

		Hi All,
		 
		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 <http://10.2.0.2/> on Solaris 9 (64-bit)

Regards,

Deepak

-- 
Regards,

Deepak
Oracle DBA 

--
http://www.freelists.org/webpage/oracle-l

Received on Wed May 16 2007 - 23:18:04 CDT