Whether the online redo log file is in active or inactive mode ?

[pissallio]

SQL>select group,sequence,archived,status from v$log
GROUP# SEQUENCE# ARCHIVED STATUS
1 193938 YES ACTIVE
2 193939 NO CURRENT
3 193937 YES INACTIVE
9 193936 YES INACTIVE



My Oracle database was in archived mode. And if an online redo log file was in "active" status, It meant that it was being archived. If it was in "inactive" status, it meant that it has already been archived and it can be reused again . Am i right ?

In addition , if my oracle database was in noarchived mode, What's the difference between "active" and "inactive" status of an online redo log file.

 

[SantaMufasa]

Pissallio,

The meanings of the statuses in v$log specifically mean:

UNUSED: Oracle8 has never written to this group,
CURRENT: This is the group to which Oracle is presently writing contents of the log buffer.
ACTIVE: Oracle has written to this log before, Oracle needs this group for instance recovery.
INACTIVE: Oracle has written to this log before; Oracle does not need this group for instance recovery.

These are the meanings regardless of whether you are in ARCHIVELOG mode or in NOARCHIVELOG mode.

Let us know if this answers your inquiry.

Mufasa
(aka Dave of Sandy, Utah, USA)
@ 05:10 (11Nov04) UTC (aka "GMT" and "Zulu"),
@ 22:10 (10Nov04) Mountain Time

 

[pissallio]

Thanks for your reply

 

[pissallio]

Hi,santa
I have observe the v$log of an nonarchived-log database in order to find something. Two status were found about the v$log;
the 1st status:
GROUP# ARCHIVED STATUS
1 NO INACTIVE
2 NO ACTIVE
3 NO CURRENT

Because the database was in non-archived mode , I think all of the 3 redo logs should be needed for instance recovery, So all of the redo logs should be in active status except one in current mode.
But in the above example, why the other redo log was in inactive mode ?

The 2nd status:
GROUP# ARCHIVED STATUS
1 NO INACTIVE
2 NO INACTIVE
3 NO CURRENT

In the 2nd circumstance, none of the redo was in active mode. Does it means that the instance can be recovered without any online redo logs ?

Thanks a lot.

 

[SantaMufasa]

Pissallio,

These are very good questions.

First, if you encounter a situation in which you need to go back to your last full backup, restore the data files, and issue a "RECOVER DATABASE" command, then obviously, you will need all archived and on-line redo log files since the checkpoint of the files you restore.

But notice in my explanations, above, I used the term "Instance Recovery", not "Database Recovery". An "Instance Recovery" is necessary under circumstances where the power fails, for example, and the instance abnormally terminates. Another example of the need for "Instance Recovery" is if you issue a "SHUTDOWN ABORT" command, which is virtually equivalent to a power failure.

In both cases, the next time you startup the database, Oracle needs to perform an Instance Recovery. Oracle does not need to start from the last-backup copies of your database, but rather it just need to apply changes that appear in one or more on-line redo logs.

Your first scenario implies that should the power fail, Oracle needs to apply redo entries first from Group #2, then from Group #3. This could result from a transaction that began whilst Group #2 was the CURRENT on-line redo log, but did not COMMIT or ROLLBACK until Group #3 was CURRENT.

Your second scenario implies that should the power fail, Oracle needs redo information only from Group #3.

Does this provide a satisfactory explanation?

Mufasa
(aka Dave of Sandy, Utah, USA)
e-mail: dave@dasages.com
@ 23:10 (15Nov04) UTC (aka "GMT" and "Zulu"),
@ 16:10 (15Nov04) Mountain Time

 

[pissallio]

Hi,Santa
Excellent solutions to my problems. Thanks!