Redo logs can have following STATUS’s (select status from v$log;):-
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.
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.
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.
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.
INACTIVE –> Log is no longer needed for instance recovery. It may be in use for media recovery. It might or might not be archived.
alter system switch logfile vs. alter system checkpoint:
SQL> alter database drop logfile group 6;
alter database drop logfile group 6
*
ERROR at line 1:
ORA-01623: log 6 is current log for instance devdb2 (thread 2) - cannot drop
ORA-00312: online log 6 thread 2: '+DG1/devdb/onlinelog/group_6.271.649475633'
ORA-00312: online log 6 thread 2:
'+DISCOVERYDEST/devdb/onlinelog/group_6.262.649475637'
SQL> alter system switch logfile;
System altered.
SQL> alter database drop logfile group 6;
alter database drop logfile group 6
*
ERROR at line 1:
ORA-01624: log 6 needed for crash recovery of instance devdb2 (thread 2)
ORA-00312: online log 6 thread 2: '+DG1/devdb/onlinelog/group_6.271.649475633'
ORA-00312: online log 6 thread 2:
'+DISCOVERYDEST/devdb/onlinelog/group_6.262.649475637'
SQL> alter system checkpoint;
System altered.
SQL> alter database drop logfile group 6;
Database altered.
SQL>
"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 done in 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.
Query to view:
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;
=================
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
b.group#, a.status, b.status, b.member
from
v$log a, v$logfile b
where
a.group#=b.group#
order by
1,2;
================
Rename and add redo:
shutdown immediate
mv o1_mf_1_bz092nk8_.log redo_1_01.log
mv o1_mf_2_bz092p4b_.log redo_1_02.log
mv o1_mf_3_bz092r26_.log redo_1_03.log
mv o1_mf_4_bz090nqm_.log redo_1_04.log
mv o1_mf_5_bz090p6y_.log redo_1_05.log
mv o1_mf_6_bz090r8j_.log redo_1_06.log
startup mount
alter database rename file '';
alter database rename file '/oracle/oradata/SID/datafile/SID/onlinelog/o1_mf_1_bz092nk8_.log' to '/oracle/oradata/SID/datafile/SID/onlinelog/redo_1_01.log';
alter database rename file '/oracle/oradata/SID/datafile/SID/onlinelog/o1_mf_2_bz092p4b_.log' to '/oracle/oradata/SID/datafile/SID/onlinelog/redo_1_02.log';
alter database rename file '/oracle/oradata/SID/datafile/SID/onlinelog/o1_mf_3_bz092r26_.log' to '/oracle/oradata/SID/datafile/SID/onlinelog/redo_1_03.log';
alter database rename file '/oracle/oradata/SID/datafile/SID/onlinelog/o1_mf_4_bz090nqm_.log' to '/oracle/oradata/SID/datafile/SID/onlinelog/redo_1_04.log';
alter database rename file '/oracle/oradata/SID/datafile/SID/onlinelog/o1_mf_5_bz090p6y_.log' to '/oracle/oradata/SID/datafile/SID/onlinelog/redo_1_05.log';
alter database rename file '/oracle/oradata/SID/datafile/SID/onlinelog/o1_mf_6_bz090r8j_.log' to '/oracle/oradata/SID/datafile/SID/onlinelog/redo_1_06.log';
alter database open;
ALTER DATABASE ADD LOGFILE group 4 ('/oracle/oradata/SID/datafile/SID/onlinelog/redo_1_04.log') SIZE 1000M;
ALTER DATABASE ADD LOGFILE THREAD 1 group 4 ('/oracle/oradata/SID/datafile/SID/onlinelog/redo_1_04.log') SIZE 500M;
Add one member per group:
SQL>
alter
database add logfile member '/u02/oradata/testdb/redo01.log'
to
group 1;
Recovering from the LOSS of REDO log files >>https://databaseinternalmechanism.com/oracle-backup-recovery/recovering-from-the-loss-of-redo-log-files/
