Tales From A Lazy Fat DBA

Den of an Oracle DB Performance freak & a fan of PostgreSQL & Cassandra …. \,,/

  • Likes

    • 187,093
  • Archives

  • Categories

  • Subscribe

  • Advertisements
  • Prashant Dixit is the FatDBA

  • Follow Tales From A Lazy Fat DBA on WordPress.com
  • My Twitter Feeds

  • Disclaimer!

    FatDBA or Oracle ‘Ant’ is an independent web-blog/site.The experiences, Test cases, views, and opinions expressed in this website are my own and does not reflect the views or opinions of my employer.

    This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content.
    Product and company names mentioned in this website may be the trademarks of their respective owners.

How to freeze your Oracle Database – Yes, you heard it right, for fun!

Posted by FatDBA on February 13, 2019

Hi Everyone,

I’m back after a long time, was little occupied. Now lets’s bring the forum/blog back to life after this long break and would like to post about something really interesting and is *Only* for your test or sandboxes. Okay let me quickly unwrap what i have this time with me.

Ever imagine how i can *Freeze* my database to simulate some test scenarios or to study/understand database behavior and troubleshoot ? – I think most of us does. So, here goes my first post of this year with something ill-famed 🙂

Test: I will try to lock/freeze the log writer process (LGWR) on one of my test machine. We only have one LGWR process in this database.
There are few other ways as well to do this but will discuss one of the easiest way to do this using OS commands.
Intention: Will see what exactly happens in the database during the time when system is busy with log writer freezed.
Situation: I hve generated some artificial load in order to understand the full impact of this act.

Step 1: Let’s first check details about our Log writer process on this test database.
Okay so its running fine with PID 5302

[oracle@gunna scripts]$ ps -ef|grep lgwr
oracle     5302      1  0 18:11 ?        00:00:26 ora_lgwr_gunnadb
oracle    47463  47411  0 22:43 pts/4    00:00:00 grep lgwr

Step 2: Now i will put this OS process on HOLD by using the KILL command with its argument -STOP.

[oracle@gunna scripts]$ kill -STOP 5302
[oracle@gunna scripts]$ ps -ef|grep lgwr
oracle     5302      1  0 18:11 ?        00:00:26 ora_lgwr_gunnadb
oracle    47499  47411  0 22:43 pts/4    00:00:00 grep lgwr

Step 3: Now lets see what’s going on on the database.
Below is the screenshot that i have captured during the time when the system was doing work with its LGWR process on HOLD and is a screes-fix from ORATOP for some real time monitoring.
So few of the things that we understand are:
– We are getting lots of ‘Log File Sync’ & ‘Log Buffer Space’ waits in the database and is pretty obvious due to lack if inactivity of Log Writer. This happens when server processes write data into the log buffer faster than the LGWR process can write it out. Just want you to update that Log buffer is an area in SGA where redo is temporarily stored before it can be written to disk.
‘Buffer Busy waits’ are quite visible too during the probe period due to constant DMLs operations happening in the background by user SOE.
– Lot’s of blockers coming in RED and point to SID 19 and is of LGWR process.
– The number of Average Active Sessions or AAS or system workload was getting higher.

Step 4: So during above step (3), the system was extremely slow and is almost dead and it failed to flush what’s there in the belly of the log writer and this causing this entire fiasco in the system. So let’s try to resume the process, but before that lets quickly check what is the status of this process on OS.

Let’s check the process details which are available under proc directory with process ID.

[oracle@gunna 5302]$ pwd

Okay, so the process is in mode T which means STOPPED.

[oracle@gunna 5302]$ more status
Name:   ora_lgwr_gunnad
State:  T (stopped)
Tgid:   5302
Pid:    5302

[oracle@gunna trace]$ ps aux |grep lgwr
oracle     5302  0.1  1.2 1392316 28304 ?       Ts   Nov19   0:34 ora_lgwr_gunnadb ---> T represents in HOLD/PAUSED.
oracle    75583  0.0  0.0 103376   808 pts/1    S+   01:25   0:00 grep lgwr

Next, let’s see if there is something captured by TOP utility.

[oracle@gunna 5302]$ top -p 5302
top - 00:41:39 up  6:46,  8 users,  load average: 3.69, 3.69, 2.63
Tasks:   1 total,   0 running,   1 sleeping,   1 stopped,   0 zombie   --------> 1 Stopped means one process is stopped.
Cpu(s):  0.0%us,  6.2%sy,  0.0%ni,  0.0%id, 93.8%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:   2346844k total,  2192512k used,   154332k free,     3820k buffers
Swap:  2031612k total,   281356k used,  1750256k free,   932548k cached

  5302 oracle    20   0 1359m  29m  26m S  0.0  1.3   0:34.20 ora_lgwr_gunnad                ----> TIME+ column was freezed and was not moving, reflects that the process was freeze.

       Here are the different values that the s, stat and state output specifiers (header "STAT" or "S") will display to describe the state of a
       D    Uninterruptible sleep (usually IO)
       R    Running or runnable (on run queue)
       S    Interruptible sleep (waiting for an event to complete)
       T    Stopped, either by a job control signal or because it is being traced.
       W    paging (not valid since the 2.6.xx kernel)
       X    dead (should never be seen)
       Z    Defunct ("zombie") process, terminated but not reaped by its parent.

Now i guess enough of mess we spilled, time to resume the process.

[oracle@gunna scripts]$
[oracle@gunna scripts]$ kill -CONT 5302

Quickly the status of the process gets changed.

[oracle@gunna 5302]$ more status |grep State
State:  D (disk sleep)
[oracle@gunna 5302]$

[oracle@gunna 5302]$ more status |grep State
State:  S (sleeping)

Soon after we bring the process back to normal mode its all good and system was stable back again.
Once again, this is only for the test or play to understand performance.

Hope It Helps
Prashant ‘The Fatdba’ Dixit


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: