Oracle | Redo Logs

Oracle Redo Log Files Version 21c

General Information

Data Dictionary Objects

ALL_LOG_GROUPS	USER_LOG_GROUPS	V$INSTANCE_LOG_GROUP
CDB_LOG_GROUPS	V$LOG	V$LOG_HISTORY
DBA_LOG_GROUPS	V$LOGFILE	V$THREAD
LOG$	V$LOGHIST

init File Parameters log_checkpoint_timeout ... set to 0

Log Files With Redundancy

(Group with multiple members) LOGFILE GROUP 1 ('/u01/app/oracle/oradata/orabase/redo1a.log', '/u05/app/oracle/fast_recovery_area/orabase/onelinelog/redo1b.log') SIZE 2G, GROUP 2 ('/u01/app/oracle/oradata/orabase/redo2a.log', '/u05/app/oracle/fast_recovery_area/orabase/onelinelog/redo2b.log') SIZE 2G, GROUP 3 ('/u01/app/oracle/oradata/orabase/redo3a.log', '/u05/app/oracle/fast_recovery_area/orabase/onelinelog/redo3b.log') SIZE 2G, GROUP 4 ('/u01/app/oracle/oradata/orabase/redo4a.log', '/u05/app/oracle/fast_recovery_area/orabase/onelinelog/redo4b.log') SIZE 2G,

Log File Sizing

As a rule-of-thumb one should try to balance archive logging between the following mutually exclusive criteria

The larger the log files the less frequent the log switches and the better performance
The smaller the log files the less risk of data loss in the event of log file corruption or a need to recover

The generally accepted balance point, in most organizations is archive log file switches once every 5 to 15 minutes: In other words 4 to 12 switches per hour.

Other advantages to consider in using the log file switch frequency recommended here, as opposed to fewer switches, are:

Fewer archivelog records and files to track
Fewer backup records for RMAN and backup software to track and maintain
Many backup appliances requires 1 file per set, so the more data that can backup at once the smaller the channel allocation overhead incurred and the faster backups and restores will take place

Status Privileges

Status	Description
active	The online redo log is active and required for instance recovery, but is not the log to which the database is currently writing. It may be in use for block recovery, and may or may not be archived.
clearing	The log is being re-created as an empty log after an ALTER DATABASE CLEAR LOGFILE statement. After the log is cleared, then the status changes to UNUSED.
clearing_current	The 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.
current	The online redo log is active, that is, needed for instance recovery, and it is the log to which the database is currently writing. The redo log can be open or closed.
inactive	The log is no longer needed for instance recovery. It may be in use for media recovery, and may or may not be archived.
unused	The online redo log has never been written to.

Related System Privileges

ALTER DATABASE

ALTER SYSTEM

Managing Log File Groups

Add a redo log file group ALTER DATABASE ADD LOGFILE ('<log_member_path_and_name>', '<log_member_path_and_name>') SIZE <integer> <K | M | G>;

ALTER DATABASE ADD LOGFILE

      ('/u01/app/oracle/oradata/orabase/redo4a.log', '/u05/app/oracle/fast_recovery_area/orabase/onelinelog/redo4b.log') SIZE 2G;

Add a redo log file group and specifying the group number ALTER DATABASE ADD LOGFILE GROUP <group_number> ('<log_member_path_and_name>') SIZE <integer> <K | M | G>;

ALTER DATABASE ADD LOGFILE GROUP 5 ('c:\temp\newlog1.log') SIZE 500M;

Relocate redo log files ALTER DATABASE RENAME FILE '<existing_path_and_file_name>' TO '<new_path_and_file_name>';

conn / as sysdba

      

      SELECT member

      FROM v_$logfile;

      

      SHUTDOWN;

      

      host

      

      $ cp /u03/logs/log1a.log /u04/logs/log1a.log

      $ cp /u03/logs/log1b.log /u05/logs/log1b.log

      

      $ exit

      

      startup mount

      

      ALTER DATABASE RENAME FILE '/u03/logs/log1a.log' TO '/u04/oradata/log1a.log';

      

      ALTER DATABASE RENAME FILE '/u04/logs/log1b.log' TO '/u05/oradata/log1b.log';

      

      ALTER DATABASE OPEN;

      

      host

      

      $ rm /u03/logs/log1a.log

      $ rm /u03/logs/log1b.log

      

      $ exit

      

      SELECT member

      FROM v_$logfile;

Clear corrupt log files that have not been archived so that it is not archived ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP <group_number>;

ALTER DATABASE CLEAR 
      UNARCHIVED LOGFILE GROUP 2;

Drop a redo log file group ALTER DATABASE DROP LOGFILE GROUP <group_number>;

ALTER DATABASE DROP LOGFILE GROUP 5;

Manage Log File Members

Adding a single log file group member ALTER DATABASE ADD LOGFILE MEMBER '<log_member_path_and_name>' TO GROUP <group_number>;

ALTER DATABASE ADD LOGFILE MEMBER '/oracle/dbs/log2b.log' TO GROUP 2;

Add a log group containing two members ALTER DATABASE ADD LOGFILE GROUP <integer> (<logfile_path_and_name>, <logfile_path_and_name>) SIZE <integer><K | M | G>;

ALTER DATABASE ADD LOGFILE GROUP 4 ('/u01/logs/redo4a.log', '/u02/logs/redo4b.log') SIZE 500M;

Drop Log File Group ALTER DATABASE DROP [STANDBY] LOGFILE GROUP <integer>;

SELECT group#, status

      FROM gv$log;

      

          GROUP# STATUS

      ---------- ----------------

         1 INACTIVE

         2 ACTIVE

         3 CURRENT

         4 INACTIVE

         5 INACTIVE

      

      ALTER DATABASE DROP LOGFILE GROUP 5;

Dropping log file group member ALTER DATABASE DROP [STANDBY] LOGFILE MEMBER <logfile_member_path_and_name>;

SELECT *

      FROM gv$logfile

      WHERE group# IN (

        SELECT group#

        FROM gv$log

        WHERE status = 'INACTIVE');

      

      ALTER DATABASE
      DROP LOGFILE MEMBER '/oracle/dbs/log3c.log';

Drop Log File by Descriptor ALTER DATABASE DROP [STANDBY] LOGFILE <file_name>;

ALTER DATABASE DROP LOGFILE '/oracle/dbs/log3c.log';

Manage Log File Threads

Add a redo log containing two members to a thread ALTER DATABASE ADD LOGFILE THREAD <integer> GROUP <integer> (<logfile_path_and_name>, <logfile_path_and_name>);

ALTER DATABASE ADD LOGFILE THREAD 5 GROUP 3

      ('/u03/oradabase/redo315.log', '/home/oracle/orabase/redo325.log');

Dumping Log Files

Dumping a log file to trace ALTER SYSTEM DUMP LOGFILE '<logfile_path_and_name>' DBA MIN <file_number> <block_number> DBA MAX <file_number> <block_number>;

or

ALTER SYSTEM DUMP LOGFILE '<logfile_path_and_name>' TIME MIN <value> TIME MIN <value>;

conn uwclass/uwclass@pdbdev

      

      ALTER SESSION SET nls_date_format='MM/DD/YYYY HH24:MI:SS';

      

      Session altered.

      

      SELECT SYSDATE

      FROM dual;

      

      SYSDATE

      -------------------

      12/11/2020 19:54:25

      

      CREATE TABLE test AS

      SELECT owner, object_name, object_type

      FROM all_objects;

      

      INSERT INTO test

      (owner, object_name, object_type)

      VALUES

      ('UWCLASS', 'log_dump', 'TEST');

      

      COMMIT;

      

      conn sys@pdbdev as sysdba

      

      SELECT ((SYSDATE-1/1440)-TO_DATE('01/01/2013','MM/DD/YYYY'))*86400 ssec

      FROM dual;

      

            SSEC

      ----------

 250718034

      

      ALTER SYSTEM DUMP LOGFILE 'u01/app/oracle/product/oradata/orabase/redo01a.log' TIME MIN 
      250718034;

Related Queries

This query can be used to create a report giving a graphical view of dates and times of log file usage set linesize 121 col 00 format 99 col 01 format 99 col 02 format 99 col 03 format 99 col 04 format 99 col 05 format 99 col 06 format 99 col 07 format 99 col 08 format 99 col 09 format 99 col 10 format 99 col 11 format 99 col 12 format 99 col 13 format 99 col 14 format 99 col 15 format 99 col 16 format 99 col 17 format 99 col 18 format 99 col 19 format 99 col 20 format 99 col 21 format 99 col 22 format 99 col 23 format 99 SELECT TO_CHAR(first_time,'MMDD') MMDD, TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'00',1,0)),'99') "00", TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'01',1,0)),'99') "01", TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'02',1,0)),'99') "02", TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'03',1,0)),'99') "03", TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'04',1,0)),'99') "04", TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'05',1,0)),'99') "05", TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'06',1,0)),'99') "06", TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'07',1,0)),'99') "07", TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'08',1,0)),'99') "08", TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'09',1,0)),'99') "09", TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'10',1,0)),'99') "10", TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'11',1,0)),'99') "11", TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'12',1,0)),'99') "12", TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'13',1,0)),'99') "13", TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'14',1,0)),'99') "14", TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'15',1,0)),'99') "15", TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'16',1,0)),'99') "16", TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'17',1,0)),'99') "17", TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'18',1,0)),'99') "18", TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'19',1,0)),'99') "19", TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'20',1,0)),'99') "20", TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'21',1,0)),'99') "21", TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'22',1,0)),'99') "22", TO_CHAR(SUM(DECODE(TO_CHAR(first_time,'HH24'),'23',1,0)),'99') "23" FROM v$log_history GROUP BY TO_CHAR(first_time,'MMDD') ORDER BY 1;

Redo Log generation rate query from Jonathan Lewis [Click Here] SELECT TO_CHAR(first_time,'dd hh24:mi:ss') first_time, ROUND(24 * 60 * (LEAD(first_time,1) OVER (ORDER BY first_time) - first_time),2) minutes FROM v$log_history v ORDER BY recid; FIRST_TIME MINUTES ----------- ---------------- 17 21:22:03 .68 17 21:22:44 1.42 17 21:24:09 4836.33 21 06:00:29 4737.63 24 12:58:07 5342.37 28 06:00:29 4317.85 01 05:58:20 5460.42 05 00:58:45

View information on log files SELECT * FROM gv$log;

View information on log file history SELECT thread#, first_change#, TO_CHAR(first_time,'MM-DD-YY HH12:MIPM'), next_change# FROM gv$log_history; THREAD# FIRST_CHANGE# TO_CHAR(FIRST_TI NEXT_CHANGE# ---------- ------------- ---------------- ------------ 1 4572020 11-17-20 09:22PM 4577438 1 4577438 11-17-20 09:22PM 5589993 1 5589993 11-17-20 09:24PM 6059245 1 6059245 11-21-20 06:00AM 6447771 1 6447771 11-24-20 12:58PM 6847706 1 6847706 11-28-20 06:00AM 7214268 1 7214268 12-01-20 05:58AM 7618574 1 7618574 12-05-20 12:58AM 7973814

Forcing log file switches ALTER SYSTEM SWITCH LOGFILE; or ALTER SYSTEM CHECKPOINT;

Clear a log file if it has become corrupt ALTER DATABASE CLEAR LOGFILE GROUP <group_number>;

-- this statement overcomes two situations where dropping redo logs is not possible: If there are only two log groups The corrupt redo log file belongs to the current group.

ALTER DATABASE CLEAR LOGFILE GROUP 4;

Clear A Log File If It Has Become Corrupt And Avoid Archiving ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP <group_number>;

-- use this version of clearing a log file if the corrupt log file has not been archived.

ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;