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; |