========================
Fixing "log file switch" wait event
========================
Fix "log file switch" wait event by adding Redo Log Group by Example.
"log file switch" wait event is related to frequent log file switches, and waits till the log file was archived, and can be free for writing.
Current status
See average redo log switches
SELECT thread#, COUNT(*)/24 avg_switches_per_hour
SELECT thread#, COUNT(*)/24 avg_switches_per_hour
FROM V$LOG_HISTORY
WHERE first_time > SYSDATE - 1
GROUP BY thread#;
See redo log groups
COL STATUS FOR A20
SELECT group#, sequence#, bytes, members, status
FROM V$LOG;
GROUP# SEQUENCE# BYTES MEMBERS STATUS
---------- ---------- ---------- ---------- --------------------
1 14794 524288000 1 CURRENT
2 14792 524288000 1 INACTIVE
3 14793 524288000 1 ACTIVE
See redo log files
set linesize 120
col MEMBER for A40
col STATUS for A10
col TYPE for A10
SELECT * FROM V$LOGFILE;
GROUP# STATUS TYPE MEMBER
---------- ---------- ---------- ----------------------------------------
1 ONLINE /oracle_db/db1/db_igt/ora_redo_01_a.rdo
2 ONLINE /oracle_db/db1/db_igt/ora_redo_02_a.rdo
3 ONLINE /oracle_db/db1/db_igt/ora_redo_03_a.rdo
Add 3 redo files
ALTER DATABASE ADD LOGFILE GROUP 4 ('/oracle_db/db1/db_igt/ora_redo_04_a.rdo') SIZE 1024M REUSE;
ALTER DATABASE ADD LOGFILE GROUP 5 ('/oracle_db/db1/db_igt/ora_redo_05_a.rdo') SIZE 1024M REUSE;
ALTER DATABASE ADD LOGFILE GROUP 6 ('/oracle_db/db1/db_igt/ora_redo_06_a.rdo') SIZE 1024M REUSE;
SELECT group#, sequence#, bytes, members, status
FROM V$LOG;
GROUP# SEQUENCE# BYTES MEMBERS STATUS
---------- ---------- ---------- ---------- ----------
1 14794 524288000 1 ACTIVE
2 14795 524288000 1 CURRENT
3 14793 524288000 1 INACTIVE
4 0 524288000 1 UNUSED
5 0 524288000 1 UNUSED
6 0 524288000 1 UNUSED
ALTER DATABASE DROP LOGFILE GROUP 1;
ALTER DATABASE ADD LOGFILE GROUP 1 ('/oracle_db/db1/db_igt/ora_redo_01_a.rdo') SIZE 1024M REUSE;
ALTER DATABASE DROP LOGFILE GROUP 2;
ALTER DATABASE ADD LOGFILE GROUP 2 ('/oracle_db/db1/db_igt/ora_redo_02_a.rdo') SIZE 1024M REUSE;
ALTER DATABASE ADD LOGFILE GROUP 2 ('/oracle_db/db1/db_igt/ora_redo_02_a.rdo') SIZE 1024M REUSE;
ALTER DATABASE DROP LOGFILE GROUP 3;
ALTER DATABASE ADD LOGFILE GROUP 3 ('/oracle_db/db1/db_igt/ora_redo_03_a.rdo') SIZE 1024M REUSE;
SET LINESIZE 120
COL MEMBER FOR A50
COL TYPE FOR A12
COL STATUS FOR A20
SELECT * FROM V$LOGFILE;
---------- ------ --------------------------------------- -----------
1 ONLINE /oracle_db/db1/db_igt/ora_redo_01_a.rdo NO
2 ONLINE /oracle_db/db1/db_igt/ora_redo_02_a.rdo NO
3 ONLINE /oracle_db/db1/db_igt/ora_redo_03_a.rdo NO
4 ONLINE /oracle_db/db1/db_igt/ora_redo_04_a.rdo NO
5 ONLINE /oracle_db/db1/db_igt/ora_redo_05_a.rdo NO
6 ONLINE /oracle_db/db1/db_igt/ora_redo_06_a.rdo NO
SQL> SELECT GROUP#, SEQUENCE#, BYTES, MEMBERS, STATUS
FROM V$LOG;
GROUP# SEQUENCE# BYTES MEMBERS STATUS
---------- ---------- ---------- ---------- ------------
1 467716 524288000 1 INACTIVE
2 467717 524288000 1 INACTIVE
3 467713 524288000 1 INACTIVE
4 467718 524288000 1 CURRENT
5 467714 524288000 1 INACTIVE
6 467715 524288000 1 INACTIVE
GROUP# SEQUENCE# BYTES MEMBERS STATUS
---------- ---------- ---------- ---------- ------------
1 467716 524288000 1 INACTIVE
2 467717 524288000 1 INACTIVE
3 467713 524288000 1 INACTIVE
4 467718 524288000 1 CURRENT
5 467714 524288000 1 INACTIVE
6 467715 524288000 1 INACTIVE
To switch to another logfile
ALTER SYSTEM SWITCH LOGFILE;
System altered.
SELECT group#, sequence#, bytes, members, status FROM V$LOG;
GROUP# SEQUENCE# BYTES MEMBERS STATUS
---------- ---------- ---------- ---------- ----------
1 14794 524288000 1 ACTIVE
2 14795 524288000 1 ACTIVE
3 14793 524288000 1 INACTIVE
4 14796 524288000 1 CURRENT
5 0 524288000 1 UNUSED
6 0 524288000 1 UNUSED
Log status:
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.
To drop the member or group, the Status should be UNUSED or INACTIVE.
ALTER DATABASE DROP LOGFILE MEMBER '/oracle_db/db1/db_igt/ora_redo_01_a.rdo';
ALTER DATABASE DROP LOGFILE GROUP 1;
Log status:
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.
To drop the member or group, the Status should be UNUSED or INACTIVE.
ALTER DATABASE DROP LOGFILE MEMBER '/oracle_db/db1/db_igt/ora_redo_01_a.rdo';
ALTER DATABASE DROP LOGFILE GROUP 1;
SELECT GROUP#, MEMBER FROM V$LOGFILE;
ALTER DATABASE ADD LOGFILE GROUP 1 ('/oracle_db/db1/db_igt/ora_redo_01_a.rdo') SIZE 1000M REUSE;
ALTER DATABASE ADD LOGFILE GROUP 2 ('/oracle_db/db1/db_igt/ora_redo_02_a.rdo') SIZE 1000M REUSE;
ALTER DATABASE ADD LOGFILE GROUP 3 ('/oracle_db/db1/db_igt/ora_redo_03_a.rdo') SIZE 1000M REUSE;
SELECT * FROM V$LOGFILE;
GROUP# STATUS TYPE MEMBER IS_RECOVERY_
------ ------ ------- ---------------------------------------- -----
1 ONLINE /oracle_db/db1/db_igt/ora_redo_01_a.rdo NO
2 ONLINE /oracle_db/db1/db_igt/ora_redo_02_a.rdo NO
3 ONLINE /oracle_db/db1/db_igt/ora_redo_03_a.rdo NO
SELECT GROUP#, SEQUENCE#, BYTES, MEMBERS, STATUS FROM V$LOG;
GROUP# SEQUENCE# BYTES MEMBERS STATUS
---------- ---------- ---------- ---------- ----------------------------------------------------------------
1 15463 524288000 1 CURRENT
2 15461 524288000 1 INACTIVE
3 15462 524288000 1 INACTIVE
ALTER SYSTEM SWITCH LOGFILE;
SQL> SELECT GROUP#, SEQUENCE#, BYTES, MEMBERS, STATUS FROM V$LOG;
GROUP# SEQUENCE# BYTES MEMBERS STATUS
---------- ---------- ------------------ ---------- ----------------------------------------------------------------
1 15463 524288000 1 ACTIVE
2 15464 524288000 1 CURRENT
3 15462 524288000 1 INACTIVE
SQL> SELECT GROUP#, SEQUENCE#, BYTES, MEMBERS, STATUS FROM V$LOG;
GROUP# SEQUENCE# BYTES MEMBERS STATUS
---------- ---------- ------------------ ---------- ----------------------------------------------------------------
1 15463 524288000 1 ACTIVE
2 15464 524288000 1 CURRENT
3 15462 524288000 1 INACTIVE
ALTER DATABASE DROP LOGFILE MEMBER '/oracle_db/db1/db_igt/ora_redo_01_a.rdo';
ALTER DATABASE DROP LOGFILE GROUP 3;
SELECT * FROM V$LOGFILE;
GROUP# STATUS TYPE MEMBER IS_RECOVERY_
---------- -------------------- ---------------------------- ---------------------------------------- ------------
1 ONLINE /oracle_db/db1/db_igt/ora_redo_01_a.rdo NO
2 ONLINE /oracle_db/db1/db_igt/ora_redo_02_a.rdo NO
3 ONLINE /oracle_db/db1/db_igt/ora_redo_03_a.rdo NO
SQL> ALTER DATABASE DROP LOGFILE GROUP 3;
Database altered.
SQL> SELECT GROUP#, SEQUENCE#, BYTES, MEMBERS, STATUS FROM V$LOG;
GROUP# SEQUENCE# BYTES MEMBERS STATUS
---------- ---------- ------------------ ---------- -----------------
1 15463 524288000 1 ACTIVE
2 15464 524288000 1 CURRENT
SQL> SELECT * FROM V$LOGFILE;
GROUP# STATUS TYPE MEMBER IS_RECOVERY_
---------- -------------------- -------------------- ---------------------------------------- ------------
1 ONLINE /oracle_db/db1/db_igt/ora_redo_01_a.rdo NO
2 ONLINE /oracle_db/db1/db_igt/ora_redo_02_a.rdo NO
SQL> ALTER DATABASE ADD LOGFILE GROUP 3 ('/oracle_db/db1/db_igt/ora_redo_03_a.rdo') SIZE 1000M REUSE;
Database altered.
SQL> SELECT GROUP#, SEQUENCE#, BYTES, MEMBERS, STATUS FROM V$LOG;
GROUP# SEQUENCE# BYTES MEMBERS STATUS
---------- ---------- ------------------ ---------- -----------------
1 15463 524288000 1 ACTIVE
2 15464 524288000 1 CURRENT
3 0 1048576000 1 UNUSED
ALTER DATABASE DROP LOGFILE GROUP 1;
ALTER DATABASE ADD LOGFILE GROUP 1 ('/oracle_db/db1/db_igt/ora_redo_01_a.rdo') SIZE 1000M REUSE;
ALTER DATABASE DROP LOGFILE GROUP 2;
ALTER DATABASE ADD LOGFILE GROUP 2 ('/oracle_db/db1/db_igt/ora_redo_02_a.rdo') SIZE 1000M REUSE;
SQL> SELECT GROUP#, SEQUENCE#, BYTES, MEMBERS, STATUS FROM V$LOG;
GROUP# SEQUENCE# BYTES MEMBERS STATUS
---------- ---------- ------------------ ---------- -----------------
1 0 1048576000 1 UNUSED
2 0 1048576000 1 UNUSED
3 15465 1048576000 1 CURRENT
ALTER DATABASE DROP LOGFILE GROUP 3;
SELECT * FROM V$LOGFILE;
GROUP# STATUS TYPE MEMBER IS_RECOVERY_
---------- -------------------- ---------------------------- ---------------------------------------- ------------
1 ONLINE /oracle_db/db1/db_igt/ora_redo_01_a.rdo NO
2 ONLINE /oracle_db/db1/db_igt/ora_redo_02_a.rdo NO
3 ONLINE /oracle_db/db1/db_igt/ora_redo_03_a.rdo NO
SQL> ALTER DATABASE DROP LOGFILE GROUP 3;
Database altered.
SQL> SELECT GROUP#, SEQUENCE#, BYTES, MEMBERS, STATUS FROM V$LOG;
GROUP# SEQUENCE# BYTES MEMBERS STATUS
---------- ---------- ------------------ ---------- -----------------
1 15463 524288000 1 ACTIVE
2 15464 524288000 1 CURRENT
SQL> SELECT * FROM V$LOGFILE;
GROUP# STATUS TYPE MEMBER IS_RECOVERY_
---------- -------------------- -------------------- ---------------------------------------- ------------
1 ONLINE /oracle_db/db1/db_igt/ora_redo_01_a.rdo NO
2 ONLINE /oracle_db/db1/db_igt/ora_redo_02_a.rdo NO
SQL> ALTER DATABASE ADD LOGFILE GROUP 3 ('/oracle_db/db1/db_igt/ora_redo_03_a.rdo') SIZE 1000M REUSE;
Database altered.
SQL> SELECT GROUP#, SEQUENCE#, BYTES, MEMBERS, STATUS FROM V$LOG;
GROUP# SEQUENCE# BYTES MEMBERS STATUS
---------- ---------- ------------------ ---------- -----------------
1 15463 524288000 1 ACTIVE
2 15464 524288000 1 CURRENT
3 0 1048576000 1 UNUSED
ALTER DATABASE DROP LOGFILE GROUP 1;
ALTER DATABASE ADD LOGFILE GROUP 1 ('/oracle_db/db1/db_igt/ora_redo_01_a.rdo') SIZE 1000M REUSE;
ALTER DATABASE DROP LOGFILE GROUP 2;
ALTER DATABASE ADD LOGFILE GROUP 2 ('/oracle_db/db1/db_igt/ora_redo_02_a.rdo') SIZE 1000M REUSE;
SQL> SELECT GROUP#, SEQUENCE#, BYTES, MEMBERS, STATUS FROM V$LOG;
GROUP# SEQUENCE# BYTES MEMBERS STATUS
---------- ---------- ------------------ ---------- -----------------
1 0 1048576000 1 UNUSED
2 0 1048576000 1 UNUSED
3 15465 1048576000 1 CURRENT
Example, add a group with 2 members
ALTER DATABASE ADD LOGFILE GROUP 14
('/oracle_db/db1/db_igt/ora_redo_04_a.rdo',
'/oracle_db/db2/db_igt/ora_redo_04_a.rdo')
SIZE 1024M;
SELECT GROUP#, TYPE, MEMBER FROM V$LOGFILE ORDER BY GROUP#;
GROUP# TYPE MEMBER
---------- ------------- ---------------------------------------
1 ONLINE /oracle_db/db1/db_igt/ora_redo_01_a.rdo
2 ONLINE /oracle_db/db1/db_igt/ora_redo_02_a.rdo
3 ONLINE /oracle_db/db1/db_igt/ora_redo_03_a.rdo
14 ONLINE /oracle_db/db1/db_igt/ora_redo_04_a.rdo
14 ONLINE /oracle_db/db2/db_igt/ora_redo_04_a.rdo
COL STATUS FOR A20
SELECT group#, sequence#, bytes, members, status FROM V$LOG;SQL>
GROUP# SEQUENCE# BYTES MEMBERS STATUS
---------- ---------- ---------- ---------- ---------
1 19411 1073741824 1 INACTIVE
2 19412 1073741824 1 CURRENT
3 19410 1073741824 1 INACTIVE
14 0 1073741824 2 UNUSED
No comments:
Post a Comment