========================
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
COL STATUS FOR A20
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
set linesize 120
col MEMBER for A40
col STATUS for A10
col TYPE for A10
SELECT * FROM V$LOGFILE;
GROUP# STATUS TYPE MEMBER IS_RECOVERY_ CON_ID
---------- ---------- ---------- ---------------------------------------- ------------ ----------
1 ONLINE /oracle_db/db1/db_igt/ora_redo_01_a.rdo NO 0
2 ONLINE /oracle_db/db1/db_igt/ora_redo_02_a.rdo NO 0
3 ONLINE /oracle_db/db1/db_igt/ora_redo_03_a.rdo NO 0
Add 3 redo files
ALTER DATABASE ADD LOGFILE GROUP 4 ('/oracle_db/db1/db_igt/ora_redo_04_a.rdo') SIZE 500M REUSE;
ALTER DATABASE ADD LOGFILE GROUP 5 ('/oracle_db/db1/db_igt/ora_redo_05_a.rdo') SIZE 500M REUSE;
ALTER DATABASE ADD LOGFILE GROUP 6 ('/oracle_db/db1/db_igt/ora_redo_06_a.rdo') SIZE 500M 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 DROP LOGFILE GROUP 2;
ALTER DATABASE DROP LOGFILE GROUP 3;
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;
SET LINESIZE 120
COL MEMBER FOR A50
COL TYPE FOR A12
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
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
No comments:
Post a Comment