DB13 “Checkpoint Not Complete”

If you  get “Checkpoint Not Complete” message from your “Alert.log” via DB13 tcode,  it means that probably your REDOLOG file sizes are smaller than the oracle db needed.

DB13 tcode show us following screen in case of situation ;

Checkpoint Not Complete

Simply technical explanation is ;

File switching operation has cancelled between REDOLOG files while “Checkpoint” process is not completed.

Then we should enlarge the related redolog files with following procedure.

- Firstly we should check the size and quantity of the redolog files ;

SQL>select GROUP#,SEQUENCE#,BYTES/1024/1024, STATUS from v$log;

GROUP#  SEQUENCE# BYTES/1024/1024 STATUS
———-       ———-              —————                  —————-
1                  280446            50                              CURRENT
2                 280443            50                               INACTIVE
3                 280445            50                               ACTIVE
4                 280444            50                               ACTIVE

- Now we have to find the file locations for these redolog files via “Control File”

We should create controlfile trace file like below ;

SQL> alter database backup controlfile to trace as ‘c:\cnt.sql’;

Database altered.

- We can find the REDOLOG files’ Size and Location informations like example ;

LOGFILE
GROUP 1 (
‘E:\ORACLE\SID\ORIGLOGA\LOG_G11M1.DBF’,
‘Y:\ORACLE\SID\MIRRLOGA\LOG_G11M2.DBF’
) SIZE 50M BLOCKSIZE 512,
GROUP 2 (
‘E:\ORACLE\SID\ORIGLOGB\LOG_G12M1.DBF’,
‘Y:\ORACLE\SID\MIRRLOGB\LOG_G12M2.DBF’
) SIZE 50M BLOCKSIZE 512,
GROUP 3 (
‘E:\ORACLE\SID\ORIGLOGA\LOG_G13M1.DBF’,
‘Y:\ORACLE\SID\MIRRLOGA\LOG_G13M2.DBF’
) SIZE 50M BLOCKSIZE 512,
GROUP 4 (
‘E:\ORACLE\SID\ORIGLOGB\LOG_G14M1.DBF’,
‘Y:\ORACLE\SID\MIRRLOGB\LOG_G14M2.DBF’
) SIZE 50M BLOCKSIZE 512

- Now we should create new 4 REDOLOG files’ with size 250 Mb like below ;

SQL> ALTER DATABASE ADD LOGFILE GROUP 5 (‘E:\ORACLE\SID\ORIGLOGA\LOG_G5M1.DBF’,   ‘Y:\ORACLE\SID\MIRRLOGA\LOG_G5M2.DBF’)        

SIZE 250M BLOCKSIZE 512;

Database altered.

SQL> ALTER DATABASE ADD LOGFILE GROUP 6 (‘E:\ORACLE\SID\ORIGLOGB\LOG_G6M1.DBF’, ‘Y:\ORACLE\SID\MIRRLOGB\LOG_G6M2.DBF’)         

SIZE 250M BLOCKSIZE 512;

Database altered.

SQL> ALTER DATABASE  ADD LOGFILE GROUP 7 (‘E:\ORACLE\SID\ORIGLOGA\LOG_G7M1.DBF’, ‘Y:\ORACLE\SID\MIRRLOGA\LOG_G7M2.DBF’)         

SIZE 250M BLOCKSIZE 512;

Database altered.

SQL> ALTER DATABASE ADD LOGFILE GROUP 8 (‘E:\ORACLE\SID\ORIGLOGB\LOG_G8M1.DBF’, ‘Y:\ORACLE\SID\MIRRLOGB\LOG_G8M2.DBF’)         

SIZE 250M BLOCKSIZE 512;

Database altered.

- It’s time to check REDOLOG status to see our new REDOLOG files , we should see 4 old redolog files and 4 new redolog files

SQL> SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;

GROUP# ARC STATUS

———- — —————-

1          NO  CURRENT

2         YES INACTIVE

3         YES INACTIVE

4         YES INACTIVE

5          YES UNUSED

6         YES UNUSED

7         YES UNUSED

8         YES UNUSED

- Now we have to DROP old REDOLOG files

SQL> ALTER DATABASE DROP LOGFILE GROUP 4;

Database altered.

SQL> ALTER DATABASE DROP LOGFILE GROUP 3;

Database altered.

SQL> ALTER DATABASE DROP LOGFILE GROUP 2;

Database altered.

SQL> ALTER DATABASE DROP LOGFILE GROUP 1;

Database altered.

Important Point : We should be sure that related redolog file has “INACTIVE” status while DROP operation working on it otherwise we will get an error message.

We can move the INACTIVE situation over the redolog file manually with following SQL command ;

Sometimes we need to execute several times…

SQL> alter system switch logfile;

System altered.

- Finally we can check the REDOLOG files status via following SQL command , we should see 4 new redolog files with new sizes

SQL> select GROUP#,SEQUENCE#,BYTES/1024/1024, STATUS from v$log;

GROUP#  SEQUENCE# BYTES/1024/1024 STATUS
———- ———- ————— —————-
5     280760    250 ACTIVE
6     280761    250 CURRENT
7     280758    250 ACTIVE
8     280759    250 ACTIVE

Leave a Reply


8 + nine =

Blogroll