Oracle Event:log file switch (checkpoint incomplete)

Event:

Top 10 Foreground Events by Total Wait Time

Event	Waits	Total Wait Time (sec)	Wait Avg(ms)	% DB time	Wait Class
DB CPU		7192.3		42.0
log file switch (checkpoint incomplete)	4,848	5884.2	1213.75	34.4	Configuration
log file sync	5,291,158	4000.8	0.76	23.4	Commit
enq: TX - allocate ITL entry	260	222.2	854.71	1.3	Configuration
buffer busy waits	176,618	199.8	1.13	1.2	Concurrency
library cache: mutex X	113,018	160.4	1.42	.9	Concurrency
virtual circuit wait	47,586	131.7	2.77	.8	Network
log file switch completion	1,877	126.1	67.16	.7	Configuration
cursor: pin S	7,070	71.7	10.15	.4	Concurrency
enq: TX - index contention	49,764	65.2	1.31	.4	Concurrency

See this line

log file switch (checkpoint incomplete)

4,848

5884.2

1213.75

34.4

Configuration

This is high shouldn't be above 20ms. (May vary for systems)

Action: This is what I did:

1. Asked for control file copy

2. and ls -ltr from archive destination for latest picture, frequency of file creation.

I my case, redo log file was 10M and was switching every minute, see has fast archives are getting created.

Beefing redologfile to 500M and expecting switch at about 10min each. Increasing log file group to 3 or 6.  Three means 30min for overwrite of redolog file post archival and 6 means 60mins(may see less because of very busy system).

Commands sequence:

spool /tmp/redomod.lst

set line 500

col MEMBER for a100

select * from v$logfile;

select * from v$log;

alter database add logfile group 4 '/oracle/oradata/mis/redo04.log' size 500M;

alter database add logfile group 5 '/oracle/oradata/mis/redo05.log' size 500M;

alter database add logfile group 6 '/oracle/oradata/mis/redo06.log' size 500M;

--run :

execute alter system switch logfile;  /* <==== 3times*/

-- Look for status in output of following query = 'INACTIVE' for 1,2,3. Only inactive can be dropped. Any other status wait and execute following as group 1/2/3 become inactive.

select * from v$log; 

---Now drop 1,2,3 ONLY WHEN GROUP STATUS US INACTIVE !!!! ONE BY ONE.

alter database drop logfile group 1;

alter database drop logfile group 2;

alter database drop logfile group 3;

 -----USE OS command to rename redo file. eg: mv /oracle/oradata/mis/redo01.log /oracle/oradata/mis/redo01.log.bak

alter database add logfile group 1 '/oracle/oradata/mis/redo01.log' size 500M;

alter database add logfile group 2 '/oracle/oradata/mis/redo02.log' size 500M;

alter database add logfile group 3 '/oracle/oradata/mis/redo03.log' size 500M;

alter database backup controlfile to trace as '/tmp/ctldb.ctl';

spool off

In my case, I kept adding file until I saw atleast 2files in INACTIVE state.

Additional recomendation: Redo file should be multiplexed to protect from mount point corruption.

Sample:

ALTER DATABASE ADD LOGFILE  member '/oracle/oradata/mis/redo01a.log' reuse to GROUP 1;