Checkpoint locks

From: Ram Raman <veeeraman_at_gmail.com>
Date: Mon, 16 Jun 2008 14:59:26 -0500
Message-ID: <effc058d0806161259l4fa8851cqc029fe38ce1210ad@mail.gmail.com>

While trying to startup application servers, we found out that the application servers were hanging. I checked what was going on in the database and saw a bunch of locks:

SQL> select * from dba_waiters;

more..

WAITING_SESSION HOLDING_SESSION LOCK_TYPE                  MODE_HELD


--------------- --------------- --------------------------

----------------------------------------

MODE_REQUESTED                             LOCK_ID1   LOCK_ID2


---------------------------------------- ---------- ----------

           1066            1100 KO                         Row-S (SS)
Exclusive                                     65557          1

           1061            1100 KO                         Row-S (SS)
Exclusive                                     65549          1

           1078            1100 KO                         Row-S (SS)
Exclusive                                     65558          1

           1077            1100 KO                         Row-S (SS)
Exclusive                                     65559          1

Turned out, those locks were waiting on Checkpoint:

SQL> select sid, serial#, program, client_info, logon_time, status   2 from v$session
  3 where sid = 1100;
more..

       SID SERIAL# PROGRAM
---------- ---------- ------------------------------------------------

CLIENT_INFO                                                      LOGON_TIM

STATUS
---------------------------------------------------------------- ---------

---

      1100          1 oracle_at_xxxd01 (CKPT)
                                                                 16-JUN-08

ACTIVE At this point I realized that I had forgotten to create tempfiles and did it. Once temp files were created
the app servers started and we were able to connect. This happened just after we cloned our prod to test and when trying to app servers. Oracle 10.2.

Would checkpoint process need any space for sorting? I thought its job was to flush the buffers to the data files.

Thanks.

--

http://www.freelists.org/webpage/oracle-l Received on Mon Jun 16 2008 - 14:59:26 CDT