Skip to main content

Alter System Checkpoint

Hello Techies,

This happens most of the time that you need to add/drop redo log groups in Oracle databases due to any administrative reasons, however, if candidate redo group is in ACTIVE status, then you can't drop it and DBA need to wait until this comes in INACTIVE status to become this group available from drop.

Here is the trick.

Current status of redo log groups in my test database and you can see highlighted groups in ACTIVE status. In this situation, if DBA need to modify/drop these groups he/she need to wait until this comes in INACTIVE status.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME      NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------------- ------------ ---------
         1          1     302111 2684354560        512          2 YES ACTIVE              1.0087E+11 16-APR-17         1.0087E+11 16-APR-17
         2          1     302112 2684354560        512          2 YES ACTIVE              1.0087E+11 16-APR-17         1.0087E+11 16-APR-17
         3          1     302113 2684354560        512          2 YES ACTIVE              1.0087E+11 16-APR-17         1.0087E+11 16-APR-17
         4          1     302114 2684354560        512          2 NO  CURRENT          1.0087E+11 16-APR-17         2.8147E+14
         5          1     302109 2684354560        512          2 YES INACTIVE          1.0087E+11 16-APR-17         1.0087E+11 16-APR-17
         6          1     302110 2684354560        512          2 YES ACTIVE              1.0087E+11 16-APR-17         1.0087E+11 16-APR-17

6 rows selected.

Details from alert log

Beginning log switch checkpoint up to RBA [0x49c21.2.10], SCN: 100869639337
Thread 1 advanced to log sequence 302113 (LGWR switch)
  Current log# 3 seq# 302113 mem# 0: /oracle/SID/origlogA/log_g3m1.dbf
  Current log# 3 seq# 302113 mem# 1: /oracle/SID/mirrlogA/log_g3m2.dbf
Sun Apr 16 12:55:09 2017
Archived Log entry 364104 added for thread 1 sequence 302112 ID 0x2947550b dest 1:
Thread 1 cannot allocate new log, sequence 302114
Private strand flush not complete
  Current log# 3 seq# 302113 mem# 0: /oracle/SID/origlogA/log_g3m1.dbf
  Current log# 3 seq# 302113 mem# 1: /oracle/SID/mirrlogA/log_g3m2.dbf
Beginning log switch checkpoint up to RBA [0x49c22.2.10], SCN: 100869639556
Thread 1 advanced to log sequence 302114 (LGWR switch)
  Current log# 4 seq# 302114 mem# 0: /oracle/SID/origlogB/log_g4m1.dbf
  Current log# 4 seq# 302114 mem# 1: /oracle/SID/mirrlogB/log_g4m2.dbf
Sun Apr 16 12:55:10 2017
Archived Log entry 364105 added for thread 1 sequence 302113 ID 0x2947550b dest 1:
Sun Apr 16 12:55:10 2017
Archived Log entry 364106 added for thread 1 sequence 302111 ID 0x2947550b dest 1:

Now, login to sqlplus as sysdba, and execute below command.

SQL> alter system checkpoint;

System altered.

When is stamped now in alert Oracle alert. This is completing all pending checkpoints related information.

Sun Apr 16 12:55:30 2017
Beginning global checkpoint up to RBA [0x49c22.3c7d.10], SCN: 100869642314
Completed checkpoint up to RBA [0x49c1f.2.10], SCN: 100869487919
Completed checkpoint up to RBA [0x49c22.3c7d.10], SCN: 100869642314
Completed checkpoint up to RBA [0x49c22.2.10], SCN: 100869639556
Completed checkpoint up to RBA [0x49c21.2.10], SCN: 100869639337
Completed checkpoint up to RBA [0x49c20.2.10], SCN: 100869639089

Now, let us see how v$log information looks like.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME      NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------------- ------------ ---------
         1          1     302111 2684354560        512          2 YES INACTIVE            1.0087E+11 16-APR-17         1.0087E+11 16-APR-17
         2          1     302112 2684354560        512          2 YES INACTIVE            1.0087E+11 16-APR-17         1.0087E+11 16-APR-17
         3          1     302113 2684354560        512          2 YES INACTIVE            1.0087E+11 16-APR-17         1.0087E+11 16-APR-17
         4          1     302114 2684354560        512          2 NO  CURRENT             1.0087E+11 16-APR-17         2.8147E+14
         5          1     302109 2684354560        512          2 YES INACTIVE            1.0087E+11 16-APR-17         1.0087E+11 16-APR-17
         6          1     302110 2684354560        512          2 YES INACTIVE            1.0087E+11 16-APR-17         1.0087E+11 16-APR-17

6 rows selected.

And this is what a DBA waits sometime for long time to do his/her database maintenance job.


Please let me know, if this helped you.

Comments

  1. Soft Test16 April 2017 at 07:17

    Hi Kanhaiya,

    Thanks for the post! Much appreciated. This will help in my day to day tasks at work.

    Thanks
    Kal

    ReplyDelete
  2. Unknown22 June 2017 at 08:13

    This is a fantastic concept you explained in very simple language, I am impressed. Thanks, keep up spreading the knowledge.

    ReplyDelete
  3. SolarisShiva12 October 2017 at 09:02

    Thanks for sharing this concept.

    Please share at what timings checkpoint occurs.

    ReplyDelete
  4. Kanhaiya Lal10 March 2019 at 10:44

    Oracle Database checkpoint is written the DBWR process of all modified buffers in SGA buffer cache to the database datafiles and datafiles headers are also updated with the latest checkpoint SCN, even if the file had no changed blocks, as well as the control files. Checkpoints occur AFTER (not during) every redo log switch and also at intervals specified by initialization parameters. LOG_CHECKPOINTS_TO_ALERT=TRUE used to observe checkpoint start and end times in the Oracle database alert log. Checkpoints can be forced with the ALTER SYSTEM CHECKPOINT; command from SQLPLUS prompt.

    ReplyDelete
  5. Munish Jindal17 June 2019 at 12:57

    This is very good concept. this is very useful. thanks for sharing.

    ReplyDelete
  6. Unknown1 November 2019 at 04:57

    very well explained about the use of oracle checkpoint
    thanks

    ReplyDelete
  7. Unknown6 November 2019 at 06:49

    Very helpful. Struggled with this concept
    . Thanks for sharing

    ReplyDelete
  8. Satyam6 November 2019 at 07:04

    Thank you very much for the detailed explanation about the story behind the checkpoint.

    ReplyDelete
  9. Mohammed Nawaz14 January 2020 at 09:17

    Simple and straight forward.!

    ReplyDelete
    Replies
    1. Kanhaiya Lal14 January 2020 at 09:18

      Thanks Nawaz, let me know in case you want to explore it further

      Delete
  10. Vrishchak28 April 2020 at 20:31

    Very nice explanation Kanhaiya Lal....also thanks for the parameter LOG_CHECKPOINTS_TO_ALERT....makes it very easy to enable and check in alert_sid.log.


    Also see this below:

    To convert active group to INACTIVE we need to perform CHECKPOINT as in
    alter system checkpoint;

    SQL> alter system switch logfile;

    System altered.

    SQL> select group#, sequence#, status, archived from v$log order by sequence#;

    GROUP# SEQUENCE# STATUS ARC
    ---------- ---------- ---------------- ---
    1 7 INACTIVE NO
    2 8 ACTIVE NO <<>>
    3 9 CURRENT NO



    SQL> alter system checkpoint;

    System altered.

    SQL> select group#, sequence#, status, archived from v$log order by sequence#;

    GROUP# SEQUENCE# STATUS ARC
    ---------- ---------- ---------------- ---
    1 7 INACTIVE NO
    2 8 INACTIVE NO <<>>
    3 9 CURRENT NO <<< To make the CURRENT REDO group to ACTIVE, here it is Group 3-Seq 9, issue alter system switch logfile.>>>

    Nonetheless Kanhaiya Lal......it was very nice for you to introduce this.

    Bhagwaan aap kaa Bhalaa Kare!

    ReplyDelete
    Replies
    1. Kanhaiya Lal13 December 2020 at 05:16

      Thank you dear for your time to respond here. Aapka din shubh ho

      Delete

Post a Comment

Popular posts from this blog

Oracle OS Watcher - Start and generate html report

Hi Guys, OS Watcher is an excellent utility for system performance investigation. OSWatcher invokes these distinct operating system utilities, each as a distinct background process, as data collectors. These utilities will be supported, or their equivalents, as available for each supported target platform. ps top ifconfig mpstat iostat netstat traceroute vmstat meminfo (Linux Only) slabinfo (Linux Only) OSWatcher is java program and requires as a minimum java version 1.4.2 or higher. This can run on on any Unix/Windows. An X Windows environment is required because oswbba uses Oracle Chart builder which requires it. Make sure java path is set $which java $<java path>/java Following will take system snapshot in every 30 seconds and will log last 24 hours data to OS Watcher archive log files. $cd <OS watcher dir> $nohup ./startOSWbb.sh 30 24 gzip <OS Watcher file location> & Without compress $nohup ./s...
9 comments
Read more

ORA-15180: could not open dynamic library odm library, error [open]

Hello Friends, Faced " ORA-15180: could not open dynamic library odm library, error [open] " which is linked with libodm11.so and libodmd11.so. Target system was refreshed from a source system which was enabled to use Oracle Disk Manager (ODM), hence got source system configuration on target and while performing database PITR, this did not came easily at nomount stage. Have a look below. Following was the way, how I sorted it out. $> sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Thu Apr 6 16:42:50 2017 Copyright (c) 1982, 2013, Oracle.  All rights reserved. Connected to an idle instance. SQL> startup nomount ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance ORA-15180: could not open dynamic library odm library, error [open] SQL> exit Disconnected Checked $ORACLE_HOME/lib/libod* condition. In your case, some of date related files may/may not be there. $> ls -lart $ORACLE_HOME/lib/l...
7 comments
Read more