Instance recovery behaviour

[DaPi]

Originally posted by alison
Where is that information stored if not in the Redo Log's

Good question - I don't really know.
Logic says that it is implied from the existance of a transaction in the RBS that is not marked as ended (commited or rolled-back) - 'coz that's the only place I think it could come from! Can anyone confirm that?

[adewri]

Originally posted by DaPi
So you no longer need LS#100 to rollback T1 - what you need is in the RBS on disk, except for (perhaps) changes in LS#101 (and then LS#102).

Well if that happens, im going to stop archiving on my databases.

Dapi do you think its possible... I agree that the uncomitted data goes to the disk... but saying that you no longer need redo log would be in correct, dont you think so?

[DaPi]

Hi Amar (Happy Birthday!),

I have heard rumours that redologs & archivelogs sometimes come in useful for media recovery . . . .

(Can you speed up ARCH by sending the file to a null device? (JOKE!))

[adewri]

Originally posted by DaPi
Hi Amar (Happy Birthday!),

I have heard rumours that redologs & archivelogs sometimes come in useful for media recovery . . . .

(Can you speed up ARCH by sending the file to a null device? (JOKE!))

I'll try that

And thanks for the wishes... you reminded me, i almost forgot
Bye guys see you tomorrow

[jmodic]

Originally posted by DaPi
Logic says that it is implied from the existance of a transaction in the RBS that is not marked as ended (commited or rolled-back) - 'coz that's the only place I think it could come from! Can anyone confirm that?

YES.

[celebguy_dv]

I am stupid

[quester]

Originally posted by alison
If the instance crashes whilst there are uncommitted changes in the
datafiles then this is repaired during instance recovery during the rollback
phase after the roll forward of all the changes in the online redologs. This
is done by reading the redo logs to look for commits on all the rolled
forward changes. But in NOARCHIVELOG mode commits for any changes that were
in the datafile before the instance crash could have been recorded in the
redologs that have been overwritten. How does SMON know what has been
committed or not if the database is in NOARCHIVELOG mode?

I had discussed this topic sometime back (though a little more than this). u can refer this thread, that has some wonderful responses..

http://www.dbasupport.com/forums/sho...0&pagenumber=1

Quester

[pando]

Originally posted by DaPi
Good question - I don't really know.
Logic says that it is implied from the existance of a transaction in the RBS that is not marked as ended (commited or rolled-back) - 'coz that's the only place I think it could come from! Can anyone confirm that?

RBS and that's what I have been trying to say in the beginning

Oracle reads redo log and RBS, then use redo to rebuild RBS and read RBS again. RBS are just data, they are flushed to disk by checkpoints as well and checkpoints will ensure you that the redo log which protects the undo can be overwritten (the dirty RBS data is flushed to disk)

[adewri]

Originally posted by pando
RBS are just data, they are flushed to disk by checkpoints as well and checkpoints will ensure you that the redo log which protects the undo can be overwritten (the dirty RBS data is flushed to disk)

Every thing fits the block, but i have a doubt. if this thing happens then after a shutdown abort in such case... the database should not ask for media recovery or will it ask ??

[adewri]

ok here is what i found.

I have a database in no archivelogmode. I did the following.

SQL> create table test (enumber number);

SQL> declare
  1  x number := 1;
  2  begin
  3  for x in 1 .. 200000 loop
  4  insert into test values (10000000);
  5  end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> select count(1) from test;

  COUNT(1)
----------
    200000

extract from alert logfile...
my redolog files a 5 MB in size.

  Current log# 2 seq# 36 mem# 0: E:\ORACLE\ORADATA\ORCL\REDO02.LOG
Tue Mar 04 13:24:04 2003
Thread 1 cannot allocate new log, sequence 37
Checkpoint not complete
  Current log# 2 seq# 36 mem# 0: E:\ORACLE\ORADATA\ORCL\REDO02.LOG
Thread 1 advanced to log sequence 37
  Current log# 1 seq# 37 mem# 0: E:\ORACLE\ORADATA\ORCL\REDO01.LOG
Thread 1 cannot allocate new log, sequence 38
Checkpoint not complete
  Current log# 1 seq# 37 mem# 0: E:\ORACLE\ORADATA\ORCL\REDO01.LOG
Thread 1 advanced to log sequence 38
  Current log# 2 seq# 38 mem# 0: E:\ORACLE\ORADATA\ORCL\REDO02.LOG
Tue Mar 04 13:24:29 2003
Thread 1 cannot allocate new log, sequence 39
Checkpoint not complete
  Current log# 2 seq# 38 mem# 0: E:\ORACLE\ORADATA\ORCL\REDO02.LOG
Thread 1 advanced to log sequence 39
  Current log# 1 seq# 39 mem# 0: E:\ORACLE\ORADATA\ORCL\REDO01.LOG

Then i did a shutdown abort, remember i haven't committed the transaction yet.

SQL*Plus: Release 9.2.0.2.0 - Production on Tue Mar 4 13:19:15 2003

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Enter user-name: sys as sysdba
Enter password:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.2.1 - Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
JServer Release 9.2.0.2.0 - Production

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  101784268 bytes
Fixed Size                   453324 bytes
Variable Size              75497472 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.
SQL>

Didn't ask for any recovery.

SQL> select * from test;

no rows selected

SQL>

So alison i hope this clear your doubts... Atleast it cleared mine

HTH