Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

Sunday, October 6, 2019

Redo logs Details:

Oracle writes to only one online redo log group at a time.Once the online redo log(s) in that group are filled then Oracle will switch to writing the next online redo log group, and so on in a circular fashion.

Each online redo log is assigned a unique sequence number.

We can multiplex each online redo log group. This means that each redo log group can consist of more than one online redo log file. Each file is known as a member. Each member should be located on a different disk, to protect the group from losing all of its members in the event a disk failure should occur. Oracle writes to those members in parallel, to ensure that the database is always recoverable while maintaining performance.

The online redo logs are first created when the database is created, and the database cannot live without them.If all members of the active redo log group are lost, the database crashes, and worse yet, there will be data loss.

Redo logs can have following STATUS’s (select status from v$log;):-

CURRENT – Current redo log. This implies that the redo log is active. The redo log could be open or closed.
ACTIVE – 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.
INACTIVE – Log is no longer needed for instance recovery. It may be in use for media recovery. It might or might not be archived.
CLEARING – Log is being re-created as an empty log after an ALTER DATABASE CLEAR LOGFILE statement. After the log is cleared, the status changes to UNUSED.
CLEARING_CURRENT – Current log is being cleared of a closed thread. The log can stay in this status if there is some failure in the switch such as an I/O error writing the new log header.
UNUSED – Online redo log has never been written to. This is the state of a redo log that was just added, or just after a RESETLOGS, when it is not the current redo log.

Alter system switch logfile;

To switch the logfile to change the STATUS of the logfile from CURRENT to ACTIVE or INACTIVE.

"alter system checkpoint" causes the current active redo log file members inactive
by flushing dirty buffers to data files and recording SCN to data file header and control file,
so that we could drop this group.

ALTER SYSTEM SWITCH LOGFILE vs ALTER SYSTEM ARCHIVE LOG CURRENT:

Both will force a log switch but  will be different way.
ALTER SYSTEM ARCHIVELOG CURRENT is the preferred one that should be used for backup scripts.

The reason being,when you do an 'ALTER SYSTEM SWITCH LOGFILE', you get the prompt back immediately. This command performs a database checkpoint, switches to the next log, In background signals the  archiver to copy the logfile to the archive destination, and returns the prompt.This does not wait for the archive to complete.

Whereas, an 'ALTER SYSTEM ARCHIVE LOG CURRENT' does all of the above, but does not return back to the prompt until the archive is complete.This command is safer because it waits for the OS to acknowledge(ACK) that the redo log has been successfully written.

If you are running RAC, the ALTER SYSTEM ARCHIVE LOG CURRENTwill switch the logs on all RAC nodes (instances), whereas ALTER SYSTEM SWITCH LOGFILE will only switch he logfile on the instance where you issue the switch command.  Hence, ALTER SYSTEM ARCHIVE LOG CURRENT is a best practice for RAC systems.

Alter system switch logfile, tells the background processes to perform the archiving of the current redo log and returns control immediately to the session issuing the statement.

Alter system archive log current, means your server/shadow process performs the arhciving and control is only returned to the session once the archiving is complete.

It issues database checkpoint
It immediately starts writing to the next redo log
In the background, the "switch logfile" command tells the ARCH background process to copy the "old" redo log file to the redo log filesystem. 

ARCHIVE LOG CURRENT waits for the ARCHiver to complete its writing.

SWITCH LOGFILE will not wait for the ARCHiver to complete writing online redo logs to archivelog log filesystem. This is fast.

Few related queries:

set lines 200 pages 200
col GROUP for a10
col TYPE for a20
col MEMBER for a100
col IS_ for a70
select * from v$log; 1:00 PM 
select GROUP#,THREAD#,MEMBERS,STATUS from v$log;

col status for a10;
col GROUP# for 9999;
col type for a10;
col member for a85;
col IS_RECOVERY_DEST_FILE for a5;
set lines 300

select * from v$logfile; 

col status for a10;
col GROUP# for 9999;
col type for a10;
col member for a85;
col IS_RECOVERY_DEST_FILE for a5;
set lines 300
select group#,thread#,members,status,bytes from v$log; 
select * from v$logfile; 

set lines 200 pages 200
col GROUP for a10
col TYPE for a20
col MEMBER for a100
col IS_ for a70
select * from v$log;  
select GROUP#,THREAD#,MEMBERS,STATUS,MEMBERS from v$log;
select * from v$logfile; 


select member, a.group#, a.status, b.status from v$log a, v$logfile b where  a.group# = b.group#  order by a.group#, member;

https://databaseinternalmechanism.com/oracle-backup-recovery/recovering-from-the-loss-of-redo-log-files/


Recovering from the LOSS of REDO log files >>https://databaseinternalmechanism.com/oracle-backup-recovery/recovering-from-the-loss-of-redo-log-files/


No comments:

Post a Comment