when log switch occurs....

Quote:

---

Originally Posted by reydp

doing checkpoint manually through "alter system" and issuing a shutdown abort after is basically the same of issuing a command of shutdown immediate.
So there is no need for crash/instance recovery to perform because all datafiles have been in sync, not unless there were pending/ongoing process executed during those commands that will generate new redo blocks.

---

I disagree. Oracle will have to perform crash recovery as the database was not closed in a consistent state. The checkpoint means that only a fairly small amount of redo will have to be applied, but it still has to be recovered. Oracle background activities can generate redo, so redo is generated even if there is no users in the database.

So, Tamil,
You are telling me, that if I issue VEEEERY long update, and suppose I have just 2 VEEERY SMALL redo log groups, I can hypotetically fail in situation where the update fills up the first log, then fills up the second one (the first is checkpointed at that time) and then the first gets reused.
And the reused redo is still needed for the instance recovery?
So if now my instance fail I will loose data?

Sorry, but I do not belive that. The check point have nothing to deal with the size of the transactions and with the trasactions at all. Nothing is wrong if we checkpoint the file and writre the dirty buffers of an uncommited transaction to the disk, so that the redo is no longer needed.
About the experiment, yes, the effect will be as you said, but the reason is quite different.
When you do 'alter system checkpoint' this is a SYNCHRONIOUS comand. That means that system starts agrresively to checkpoint and you do not get the control back until the checkpoint does not finishes. So when then you issue shutdown abort, you really do not need an instance recovery
'Alter system switch logfile' on the other hand switches the log file, hence STARTS the checkpoint, and returns the control to you, so now, when you "shutdown abort" the checkpoint is not yet finished and hence the redo log is still needed for instance recovery.
Here is the proove of what I say
------------------------
1* select group#,status from v$log

GROUP# STATUS
---------- ----------------
1 CURRENT
2 ACTIVE
3 ACTIVE

SQL> alter system switch logfile
2 /

System altered.

SQL> select group#,status from v$log
2 /

GROUP# STATUS
---------- ----------------
1 ACTIVE
2 CURRENT
3 ACTIVE

SQL> alter system checkpoint;

System altered.

SQL> select group#,status from v$log
2 /

GROUP# STATUS
---------- ----------------
1 INACTIVE
2 CURRENT
3 INACTIVE

SQL>
----------------------------------------------------

As you can see, alter system switch logfile leaves the previously "current" logfile in ACTIVE status, what means it is currently checkpointed
Alter system checkpoint
on the other hand leaves all the non-current log files INACTIVE what means the checkpoint have completed and the files are no longer needed for instance recovery


Regards
Boris

Quote:

---

Originally Posted by tamilselvan

For example, You update millions of rows in table and then before commit/rollback, the system crashed even though several log switches occurred during the update process.

---

Tamil, I think you are confusing transaction recover with instance recovery.

Redo logs are not required for transaction recovery. At instance recovery, the undo tablespace is recovered, like any other and any active transactions are rolled back. This has nothing to do with checkpointing.

On line redo logs are the only thing that is needed to perform instance recovery or what some are referring to as crash recovery.

Checkpoints may be either incremental or full. In either case oracle records the low cache RBA (redo block address) on disk in the control file. This is the location that is needed inside the redo log to perform instance recovery. See x$kcccp

An active log is needed for instance recovery, You can not switch into an active log.

Quote:

---

Originally Posted by jhmartin

I disagree. Oracle will have to perform crash recovery as the database was not closed in a consistent state. The checkpoint means that only a fairly small amount of redo will have to be applied, but it still has to be recovered. Oracle background activities can generate redo, so redo is generated even if there is no users in the database.

---

When you do a FAST CHECKPOINT all dirty buffers with current or below SCN value will be written to data files regardless if it is commited or uncommited. At this state, the only thing that needs for instance recovery is the uncommited data which is actually a transaction recovery(rolling back) in order for the database to be in a consistent state. And the SCN information needed for this transaction recovery is recorded also in the redo log.

That's why I mentioned in my previous post:
unless there were pending/ongoing process executed during those commands that will generate new redo blocks
This means pending(NOT commited/NOT rolled back) or ongoing process.
Or let me clear it PENDING transaction.

I hope this clears out.

Quote:

---

Originally Posted by tamilselvan

Yes, you are partially right. In general, after a checkpoint, the redo in the redo log files is no longer needed for crash/instance recovery. However, the redo logs may be needed for instance recovery if ta single transaction's redo size is greater than the redo log file size.
For example, You update millions of rows in table and then before commit/rollback, the system crashed even though several log switches occurred during the update process.
Another example is when you clone the database, the recovery process may ask you to enter redo log files which are older than the current redo log.

---

If the redo size of a single transaction is greater than the size of one or few redo logs but not the total redo logs group size then I believe Active not just Current redo logs will be needed for transaction recovery during instance recovery.

If in case the redo size of a single transaction is greater than the total size of all the redo logs groups then most probable you will bump the "Checkpoint not complete" error. So, I don't think there's going to be a several log switches that could occur.

Gyus,
Again, the checkpoint has nothing to deal with the size of the transaction, nor the log switch has. "active" redo log means that the redo log is currently being checkpointed and the checkpoint have still not completed, what ,yes, means that it's still needed for instance recovery, however no connection to the transaction size.
"If in case the redo size of a single transaction is greater than the total size of all the redo logs groups then most probable you will bump the "Checkpoint not complete" error."
That's simply not true. Checkpoint not completed is signalled when you are trying to reuse a log file, which is still not finished with the checkpoint and hence you cannot reuse it, so the server process have to wait until the checkpoint do not complete and the file is not ready for reusing. This appears generally when teh redo generation is faster than the checkpoint.
I am really amaized that such fundamental mechanism can cause such heated discussions.

Regards
Boris

Good discussion....

Quote:

---

Originally Posted by ixion

On line redo logs are the only thing that is needed to perform instance recovery or what some are referring to as crash recovery.

---

Isn't undo used for recovery as well? Redo is played back, and then undo is applied after that...

correct Tomcat,
After the redo application, the instance is opened and SMON takes care to rollback all the transactions that have been opened at the time of failure.
That's called rollback phase of the instance recovery.

Bore,

From oracle manual:
V$LOG STATUS column = ACTIVE means:
Log is active but is not the current log. It is needed for crash recovery. It may be in use for block recovery. It may or may not be archived.

The whole point of discussion is: Does oracle need the redo log file for crash recovery "after log switch occurs" because checkpoint is done?

My answer is yes.

Well,
I would say the discussion is Does the instance needs for instance recovery the active log because the checkpoint is still not finished and my answer to that is yes as well.
But as I understood, the discussion was is the checkpoint dependent somehow on the size of the transaction, how exactly the checkpoint is performed etc. and hence my comments on that

So... basically we agree upon the mechanism

Regards
Boris