DBA survival BLOG

Some DBA stuff and lots of Oracle Data Guard

Check the actual ulimits for all the running Oracle instances

Posted on June 18, 2015 by Ludovico

1

I’ve read the recent good post from my friend Rene on Pythian’s Blog about how to troubleshoot when user ulimits are different from what specified in limits.conf:

Quick Tip : Oracle User Ulimit Doesn’t Reflect Value on /etc/security/limits.conf

I would like to add my 2 cents:

Once you fix the problem, you may want to check (any maybe monitor) when an instance is running with a wrong value (and maybe encounter the famous Error message: Linux-x86_64 Error: 23: Too many open files in system).

for pmonspid in `ps -eaf | grep [p]mon | awk '{print $2}'` ; do ps -f -p $pmonspid  ; grep "open files" /proc/$pmonspid/limits ; done

1	for pmonspid in `ps -eaf \| grep [p]mon \| awk '{print $2}'` ; do ps -f -p $pmonspid ; grep "open files" /proc/$pmonspid/limits ; done

This single line gives you an overview of all your instances at once:

$ for pmonspid in `ps -eaf | grep [p]mon | awk '{print $2}'` ; do ps -f -p $pmonspid  ; grep "open files" /proc/$pmonspid/limits ; done
UID        PID  PPID  C STIME TTY     TIME CMD           
oracle     545     1  0 Mar18 ?   00:08:27 ora_pmon_orcl1
Max open files            1024            1024           files <<< 1024!!
UID        PID  PPID  C STIME TTY     TIME CMD           
oracle    1294     1  0 Apr20 ?   00:00:09 ora_pmon_orcl2
Max open files            1024            1024           files <<< 1024!!
UID        PID  PPID  C STIME TTY     TIME CMD           
oracle    9917     1  0 Jan26 ?   00:08:17 ora_pmon_orcl3
Max open files            1024            1024           files <<< 1024!!
UID        PID  PPID  C STIME TTY     TIME CMD           
oracle   11286     1  0 Jan26 ?   00:07:35 ora_pmon_orcl4
Max open files            1024            1024           files <<< 1024!!
UID        PID  PPID  C STIME TTY     TIME CMD           
oracle   11647     1  0 Mar04 ?   00:04:36 ora_pmon_orcl5
Max open files            65536           65536          files
UID        PID  PPID  C STIME TTY     TIME CMD           
oracle   11836     1  0 Jan26 ?   00:07:55 ora_pmon_orcl6
Max open files            1024            1024           files <<< 1024!!
UID        PID  PPID  C STIME TTY     TIME CMD           
oracle   14183     1  0 Feb06 ?   00:07:13 ora_pmon_orcl7
Max open files            65536           65536          files
UID        PID  PPID  C STIME TTY     TIME CMD           
oracle   16023     1  0 Feb27 ?   00:05:20 ora_pmon_orcl8
Max open files            65536           65536          files
UID        PID  PPID  C STIME TTY     TIME CMD           
oracle   18756     1  0 Mar20 ?   00:03:24 ora_pmon_orcl9
Max open files            65536           65536          files

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

$ for pmonspid in `ps -eaf | grep [p]mon | awk '{print $2}'` ; do ps -f -p $pmonspid ; grep "open files" /proc/$pmonspid/limits ; done

UID PID PPID C STIME TTY TIME CMD

oracle 545 1 0 Mar18 ? 00:08:27 ora_pmon_orcl1

Max open files 1024 1024 files <<< 1024!!

UID PID PPID C STIME TTY TIME CMD

oracle 1294 1 0 Apr20 ? 00:00:09 ora_pmon_orcl2

Max open files 1024 1024 files <<< 1024!!

UID PID PPID C STIME TTY TIME CMD

oracle 9917 1 0 Jan26 ? 00:08:17 ora_pmon_orcl3

Max open files 1024 1024 files <<< 1024!!

UID PID PPID C STIME TTY TIME CMD

oracle 11286 1 0 Jan26 ? 00:07:35 ora_pmon_orcl4

Max open files 1024 1024 files <<< 1024!!

UID PID PPID C STIME TTY TIME CMD

oracle 11647 1 0 Mar04 ? 00:04:36 ora_pmon_orcl5

Max open files 65536 65536 files

UID PID PPID C STIME TTY TIME CMD

oracle 11836 1 0 Jan26 ? 00:07:55 ora_pmon_orcl6

Max open files 1024 1024 files <<< 1024!!

UID PID PPID C STIME TTY TIME CMD

oracle 14183 1 0 Feb06 ? 00:07:13 ora_pmon_orcl7

Max open files 65536 65536 files

UID PID PPID C STIME TTY TIME CMD

oracle 16023 1 0 Feb27 ? 00:05:20 ora_pmon_orcl8

Max open files 65536 65536 files

UID PID PPID C STIME TTY TIME CMD

oracle 18756 1 0 Mar20 ? 00:03:24 ora_pmon_orcl9

Max open files 65536 65536 files

If you find any wrong values, plan a restart before you encounter any error during peak hours!

—

Ludo

Smart Bash Prompt for Oracle

Posted on June 5, 2015 by Ludovico

3

If you are an Oracle customer who has several database versions running, you have to deal with scripts that become more and more complex to maintain. Depending on the version or the edition of your database, you may want to run different pieces of code. This forces you to get programmatically more information about your database version and edition (e.g., in order to run a statspack or AWR report if your software is either Enterprise or Standard).
The most common way to get information about the software is connecting to the database and getting it through a couple of selects. But what if you don’t have any running databases?
The ORACLE_HOME inventory has such information, and you can get it with a short shell function:

function ohversion ()
{
    ORACLE_VERSION=`grep "<PATCH NAME=\"oracle.server\"" $ORACLE_HOME/inventory/ContentsXML/comps.xml 2>/dev/null | tr ' ' '\n' | grep ^VER= | awk -F\" '{print $2}'`;
    if [ -z "$ORACLE_VERSION" ]; then
        ORACLE_VERSION=`grep "<COMP NAME=\"oracle.server\"" $ORACLE_HOME/inventory/ContentsXML/comps.xml 2>/dev/null | tr ' ' '\n' | grep ^VER= | awk -F\" '{print $2}'`;
    fi;
    if [ -z "$ORACLE_VERSION" ]; then
        echo "OH not set";
    fi;
    ORACLE_MAJOR=`echo $ORACLE_VERSION |  cut -d . -f 1`;
    case $ORACLE_MAJOR in
        11 | 12)
            EDITION=`grep "oracle_install_db_InstallType" $ORACLE_HOME/inventory/globalvariables/oracle.server/globalvariables.xml 2>/dev/null | tr ' ' '\n' | grep VALUE | awk -F\" '{print $2}'`
        ;;
        10)
            EDITION=`grep "s_serverInstallType" $ORACLE_HOME/inventory/Components21/oracle.server/*/context.xml 2>/dev/null | tr ' ' '\n' | grep VALUE | awk -F\" '{print $2}'`
        ;;
        *)

        ;;
    esac;
    export ORACLE_VERSION EDITION;
    echo $ORACLE_VERSION $EDITION
}

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

function ohversion ()

{

ORACLE_VERSION=`grep "<PATCH NAME=\"oracle.server\"" $ORACLE_HOME/inventory/ContentsXML/comps.xml 2>/dev/null | tr ' ' '\n' | grep ^VER= | awk -F\" '{print $2}'`;

if [ -z "$ORACLE_VERSION" ]; then

ORACLE_VERSION=`grep "<COMP NAME=\"oracle.server\"" $ORACLE_HOME/inventory/ContentsXML/comps.xml 2>/dev/null | tr ' ' '\n' | grep ^VER= | awk -F\" '{print $2}'`;

fi;

if [ -z "$ORACLE_VERSION" ]; then

echo "OH not set";

fi;

ORACLE_MAJOR=`echo $ORACLE_VERSION | cut -d . -f 1`;

case $ORACLE_MAJOR in

11 | 12)

EDITION=`grep "oracle_install_db_InstallType" $ORACLE_HOME/inventory/globalvariables/oracle.server/globalvariables.xml 2>/dev/null | tr ' ' '\n' | grep VALUE | awk -F\" '{print $2}'`

;;

10)

EDITION=`grep "s_serverInstallType" $ORACLE_HOME/inventory/Components21/oracle.server/*/context.xml 2>/dev/null | tr ' ' '\n' | grep VALUE | awk -F\" '{print $2}'`

;;

*)

;;

esac;

export ORACLE_VERSION EDITION;

echo $ORACLE_VERSION $EDITION

}

The snippet searches for a patchset entry in comps.xml to get the patch version rather than the base version (for releases prior to 11gR2 where out-of-place patching occurs). If a patchset cannot be found, it looks for the base version. Depending on the major release, the information about the edition is either in globalvariables.xml (11g, 12c) or in context.xml (10g).
When you call this “ohversion” function, you get both the Oracle version and the edition of your current ORACLE_HOME.
If you’re using the bash as user shell, you may want to take one step forward and include this information in a much fancier bash prompt than the prompt by default:

function ora_prompt ()
{
    PSERR=$?;
    colylw='\033[0;33m';
    colcyn='\033[0;36m';
    colured='\033[4;31m';
    colugrn='\033[4;32m';
    coluylw='\033[4;33m';
    colrst='\033[0m';
    PS1="\n# [ \u@\h:${colcyn}$PWD${colrst} [\\t] [${colylw}\$(ohversion) SID=${coluylw}${ORACLE_SID:-\"not set\"}${colrst}] \$( if [[ \$PSERR -eq 0 ]]; then echo \"${colugrn}0${colrst}\" ; else echo \"${colured}\$PSERR${colrst}\";fi) ] #\\n# "
}

export PROMPT_COMMAND=ora_prompt

1

2

3

4

5

6

7

8

9

10

11

12

13

function ora_prompt ()

{

PSERR=$?;

colylw='\033[0;33m';

colcyn='\033[0;36m';

colured='\033[4;31m';

colugrn='\033[4;32m';

coluylw='\033[4;33m';

colrst='\033[0m';

PS1="\n# [ \u@\h:${colcyn}$PWD${colrst} [\\t] [${colylw}\$(ohversion) SID=${coluylw}${ORACLE_SID:-\"not set\"}${colrst}] \$( if [[ \$PSERR -eq 0 ]]; then echo \"${colugrn}0${colrst}\" ; else echo \"${colured}\$PSERR${colrst}\";fi) ] #\\n# "

}

export PROMPT_COMMAND=ora_prompt

Although this prompt may seem long, it has several advantages that save you a lot of typing:
• The newline character inside the prompt let’s you start typing commands on an almost empty line so you don’t have to worry about how long your command is.
• The full username@host:path can be copied and pasted quickly for scp commands.
• The time inside the square brackets is helpful to track timings.
• The indication of the current environment (version, edition, SID) lets you know which environment you’re working on.
• The leading number is the exit code of the last command ($?). It’s green when the exit code is zero and red for all other exit codes.
• Hash characters before and after the prompt mitigate the risk of copying and pasting the wrong line by mistake inside your session.

Note: this post originally appeared on IOUG Tips & Best Practices Booklet 9th edition.

Oracle Instances and real memory consumption on Linux and Solaris

Posted on December 13, 2013 by Ludovico

8

There’s a way to know the REAL memory usage by Oracle Instance, including all connecting processes and using the shell rather than a connection to oracle?

The short answer is “I think so” 🙂

Summing up RSS column from ps output, is not reliable because Linux uses a copy-on-write on process forks and also doesn’t take into account correctly the shared memory and other shared allocations.

I’ve come across this post on Pythian’s Blog from Marc Billette.

While it seems good I’ve had discording results depending on platform and release.

Instead, I’ve tried to create a shell snippet that always uses pmap but works differently and SEEMS to work correctly on Linux ans Solaris.

Basically, using the pmap script I get a lot of information about the different memory areas allocated to the process:

21010:  ora_d000_db1p

0000000000400000     208908K r-x--  /ccv/app/oracle/product/11.2.0.3/bin/oracle

000000000D012000       1536K rw---  /ccv/app/oracle/product/11.2.0.3/bin/oracle

000000000D192000       1040K rw---    [ heap ]

0000000060000000      12288K rwxs-    [ dism shmid=0x4300000e ]

0000000080000000    1036288K rwxs-    [ dism shmid=0x7600000f ]

00000000C0000000         12K rwxs-    [ dism shmid=0x4f000011 ]

FFFFFD7FFC7A0000         64K rwx--    [ anon ]

FFFFFD7FFC7BD000        704K rw---    [ anon ]

FFFFFD7FFC86E000        200K rw---    [ anon ]

FFFFFD7FFC8A0000        312K rw---    [ anon ]

FFFFFD7FFC8EF000       1280K rw---    [ anon ]

FFFFFD7FFCA30000         64K rwx--    [ anon ]

FFFFFD7FFCA4F000        256K rw---    [ anon ]

FFFFFD7FFCA90000         64K rwx--    [ anon ]

FFFFFD7FFCAB0000         36K r-x--  /lib/amd64/libuutil.so.1
...

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

21010: ora_d000_db1p

0000000000400000 208908K r-x-- /ccv/app/oracle/product/11.2.0.3/bin/oracle

000000000D012000 1536K rw--- /ccv/app/oracle/product/11.2.0.3/bin/oracle

000000000D192000 1040K rw--- [ heap ]

0000000060000000 12288K rwxs- [ dism shmid=0x4300000e ]

0000000080000000 1036288K rwxs- [ dism shmid=0x7600000f ]

00000000C0000000 12K rwxs- [ dism shmid=0x4f000011 ]

FFFFFD7FFC7A0000 64K rwx-- [ anon ]

FFFFFD7FFC7BD000 704K rw--- [ anon ]

FFFFFD7FFC86E000 200K rw--- [ anon ]

FFFFFD7FFC8A0000 312K rw--- [ anon ]

FFFFFD7FFC8EF000 1280K rw--- [ anon ]

FFFFFD7FFCA30000 64K rwx-- [ anon ]

FFFFFD7FFCA4F000 256K rw--- [ anon ]

FFFFFD7FFCA90000 64K rwx-- [ anon ]

FFFFFD7FFCAB0000 36K r-x-- /lib/amd64/libuutil.so.1

...

Initially I’ve tried to decode correctly the different kinds of memory the same way other scripts I’ve found online do:

rwxs- = shared memory

rw--- = private heap

rwx-- = private code stack

r-x-- = shared code stack (?)

etc...

1

2

3

4

5

6

7

8

9

rwxs- = shared memory

rw--- = private heap

rwx-- = private code stack

r-x-- = shared code stack (?)

etc...

but finally the ADDRESS is the same from different processes when the memory area is shared, so my script now just get a unique line for each address and sums up the memory size (not the rss one!):

username=`whoami`

sids=`ps -eaf | grep "^$username" | grep pmon | grep -v " grep "  | awk '{print substr($NF,10)}'`


total=0
for sid in $sids ; do
        pids=`ps -eaf | grep "^$username" | grep -- "$sid" | grep -v " grep " | awk '{print $2}'`
        mem=`pmap $pids 2>&1 | grep "K " | sort | awk '{print $1 " " substr($2,1,length($2)-1)}' | uniq | awk ' BEGIN { sum=0 } { sum+=$2} END {print sum}' `

        echo "$sid : $mem"
        total=`expr $total + $mem`
done

echo "total :  $total"

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

username=`whoami`

sids=`ps -eaf | grep "^$username" | grep pmon | grep -v " grep " | awk '{print substr($NF,10)}'`

total=0

for sid in $sids ; do

pids=`ps -eaf | grep "^$username" | grep -- "$sid" | grep -v " grep " | awk '{print $2}'`

mem=`pmap $pids 2>&1 | grep "K " | sort | awk '{print $1 " " substr($2,1,length($2)-1)}' | uniq | awk ' BEGIN { sum=0 } { sum+=$2} END {print sum}' `

echo "$sid : $mem"

total=`expr $total + $mem`

done

echo "total : $total"

This should give the total virtual memory allocated by the different Oracle instances.

The results I get are plausible both on Linux and Solaris.

Example:

$ ./test_mem.ksh

db1p: 3334852

db2p: 2052048

db3p: 6765280

db4p: 2687928

db5p: 4385616

total :  19225724

1

2

3

4

5

6

7

8

9

10

11

12

13

$ ./test_mem.ksh

db1p: 3334852

db2p: 2052048

db3p: 6765280

db4p: 2687928

db5p: 4385616

total : 19225724

If you find any error let me know and I’ll fix the script!

—

Ludovico

Oracle Database 12c: Multithreaded Execution (or how make processes decrease)

Posted on June 26, 2013 by Ludovico

6

Too many background processes

Oracle instances on Unix/Linux servers have been composed historically by separated server processes to allow the database to be multi-user, in opposite with Windows that has always been multithread (Oracle 7 on MS-DOS was a single-user process, but this is prehistory…). The background processes number has increased to support all the new features of Oracle, up to this new Oracle 12c release. On a simple database installation you’ll be surprised to have this output from a ps command (38 processes):

# ps -eaf | grep CLASSIC | grep -v grep

oracle 3582 1 0 21:59 ? 00:00:00 ora_pmon_CLASSIC 
oracle 3584 1 0 21:59 ? 00:00:00 ora_psp0_CLASSIC 
oracle 3590 1 4 21:59 ? 00:00:51 ora_vktm_CLASSIC 
oracle 3596 1 0 21:59 ? 00:00:00 ora_gen0_CLASSIC 
oracle 3599 1 0 21:59 ? 00:00:00 ora_mman_CLASSIC 
oracle 3608 1 0 21:59 ? 00:00:00 ora_diag_CLASSIC 
oracle 3612 1 0 21:59 ? 00:00:00 ora_dbrm_CLASSIC 
oracle 3616 1 0 21:59 ? 00:00:00 ora_dia0_CLASSIC 
oracle 3620 1 0 21:59 ? 00:00:00 ora_dbw0_CLASSIC 
oracle 3624 1 0 21:59 ? 00:00:04 ora_lgwr_CLASSIC 
oracle 3628 1 0 21:59 ? 00:00:00 ora_ckpt_CLASSIC 
oracle 3632 1 0 21:59 ? 00:00:00 ora_smon_CLASSIC 
oracle 3636 1 0 21:59 ? 00:00:00 ora_reco_CLASSIC 
oracle 3640 1 0 21:59 ? 00:00:00 ora_lreg_CLASSIC 
oracle 3644 1 0 21:59 ? 00:00:00 ora_rbal_CLASSIC 
oracle 3648 1 0 21:59 ? 00:00:00 ora_asmb_CLASSIC 
oracle 3652 1 0 21:59 ? 00:00:01 ora_mmon_CLASSIC 
oracle 3659 1 0 21:59 ? 00:00:00 ora_mmnl_CLASSIC 
oracle 3664 1 0 21:59 ? 00:00:00 ora_d000_CLASSIC 
oracle 3667 1 0 21:59 ? 00:00:00 ora_s000_CLASSIC 
oracle 3672 1 0 21:59 ? 00:00:00 ora_mark_CLASSIC 
oracle 3707 1 0 21:59 ? 00:00:01 ora_o000_CLASSIC 
oracle 3717 1 0 21:59 ? 00:00:01 ora_o001_CLASSIC 
oracle 3725 1 0 21:59 ? 00:00:00 ora_tmon_CLASSIC 
oracle 3729 1 0 21:59 ? 00:00:00 ora_tt00_CLASSIC 
oracle 3736 1 0 21:59 ? 00:00:00 ora_smco_CLASSIC 
oracle 3738 1 0 22:00 ? 00:00:00 ora_w000_CLASSIC 
oracle 3749 1 0 22:00 ? 00:00:00 ora_fbda_CLASSIC 
oracle 3751 1 0 22:00 ? 00:00:00 ora_aqpc_CLASSIC 
oracle 3757 1 0 22:00 ? 00:00:00 ora_qm02_CLASSIC 
oracle 3759 1 0 22:00 ? 00:00:00 ora_p000_CLASSIC 
oracle 3763 1 0 22:00 ? 00:00:00 ora_p001_CLASSIC 
oracle 3765 1 0 22:00 ? 00:00:00 ora_q002_CLASSIC 
oracle 3767 1 0 22:00 ? 00:00:00 ora_p002_CLASSIC 
oracle 3769 1 0 22:00 ? 00:00:00 ora_q003_CLASSIC 
oracle 3771 1 0 22:00 ? 00:00:00 ora_p003_CLASSIC 
oracle 3774 1 0 22:00 ? 00:00:00 ora_cjq0_CLASSIC 
oracle 3801 1 0 22:00 ? 00:00:02 ora_vkrm_CLASSIC

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

# ps -eaf | grep CLASSIC | grep -v grep

oracle 3582 1 0 21:59 ? 00:00:00 ora_pmon_CLASSIC

oracle 3584 1 0 21:59 ? 00:00:00 ora_psp0_CLASSIC

oracle 3590 1 4 21:59 ? 00:00:51 ora_vktm_CLASSIC

oracle 3596 1 0 21:59 ? 00:00:00 ora_gen0_CLASSIC

oracle 3599 1 0 21:59 ? 00:00:00 ora_mman_CLASSIC

oracle 3608 1 0 21:59 ? 00:00:00 ora_diag_CLASSIC

oracle 3612 1 0 21:59 ? 00:00:00 ora_dbrm_CLASSIC

oracle 3616 1 0 21:59 ? 00:00:00 ora_dia0_CLASSIC

oracle 3620 1 0 21:59 ? 00:00:00 ora_dbw0_CLASSIC

oracle 3624 1 0 21:59 ? 00:00:04 ora_lgwr_CLASSIC

oracle 3628 1 0 21:59 ? 00:00:00 ora_ckpt_CLASSIC

oracle 3632 1 0 21:59 ? 00:00:00 ora_smon_CLASSIC

oracle 3636 1 0 21:59 ? 00:00:00 ora_reco_CLASSIC

oracle 3640 1 0 21:59 ? 00:00:00 ora_lreg_CLASSIC

oracle 3644 1 0 21:59 ? 00:00:00 ora_rbal_CLASSIC

oracle 3648 1 0 21:59 ? 00:00:00 ora_asmb_CLASSIC

oracle 3652 1 0 21:59 ? 00:00:01 ora_mmon_CLASSIC

oracle 3659 1 0 21:59 ? 00:00:00 ora_mmnl_CLASSIC

oracle 3664 1 0 21:59 ? 00:00:00 ora_d000_CLASSIC

oracle 3667 1 0 21:59 ? 00:00:00 ora_s000_CLASSIC

oracle 3672 1 0 21:59 ? 00:00:00 ora_mark_CLASSIC

oracle 3707 1 0 21:59 ? 00:00:01 ora_o000_CLASSIC

oracle 3717 1 0 21:59 ? 00:00:01 ora_o001_CLASSIC

oracle 3725 1 0 21:59 ? 00:00:00 ora_tmon_CLASSIC

oracle 3729 1 0 21:59 ? 00:00:00 ora_tt00_CLASSIC

oracle 3736 1 0 21:59 ? 00:00:00 ora_smco_CLASSIC

oracle 3738 1 0 22:00 ? 00:00:00 ora_w000_CLASSIC

oracle 3749 1 0 22:00 ? 00:00:00 ora_fbda_CLASSIC

oracle 3751 1 0 22:00 ? 00:00:00 ora_aqpc_CLASSIC

oracle 3757 1 0 22:00 ? 00:00:00 ora_qm02_CLASSIC

oracle 3759 1 0 22:00 ? 00:00:00 ora_p000_CLASSIC

oracle 3763 1 0 22:00 ? 00:00:00 ora_p001_CLASSIC

oracle 3765 1 0 22:00 ? 00:00:00 ora_q002_CLASSIC

oracle 3767 1 0 22:00 ? 00:00:00 ora_p002_CLASSIC

oracle 3769 1 0 22:00 ? 00:00:00 ora_q003_CLASSIC

oracle 3771 1 0 22:00 ? 00:00:00 ora_p003_CLASSIC

oracle 3774 1 0 22:00 ? 00:00:00 ora_cjq0_CLASSIC

oracle 3801 1 0 22:00 ? 00:00:02 ora_vkrm_CLASSIC

If you have consolidated many databases without the pluggable database feature, you’ll end up to have several hundreds of processes even without users connected. But Oracle 12c now introduce the possibility to start an instance using multithreading instead of the traditional processes. This could lead to some optimizations due to the shared process memory, and reduced context switches overhead, I presume (need to test it).

Enabling the Multithreaded Execution

By default this feature is not enabled, so you have to set it explicitly:

SQL> alter system set threaded_execution=true scope=spfile;

System altered.

SQL>

1

2

3

4

5

SQL> alter system set threaded_execution=true scope=spfile;

System altered.

SQL>

And in parallel, you’ll need to add this line to the listener.ora:

DEDICATED_THROUGH_BROKER_listener=on

1	DEDICATED_THROUGH_BROKER_listener=on

After a restart, the instance will show only a bunch of processes:

# ps -eaf | grep CLASSIC | grep -v grep
oracle 4792 1 0 22:25 ? 00:00:00 ora_pmon_CLASSIC
oracle 4794 1 0 22:25 ? 00:00:00 ora_psp0_CLASSIC
oracle 4800 1 2 22:25 ? 00:00:01 ora_vktm_CLASSIC
oracle 4804 1 1 22:25 ? 00:00:00 ora_u004_CLASSIC
oracle 4810 1 7 22:25 ? 00:00:03 ora_u005_CLASSIC
oracle 4818 1 0 22:25 ? 00:00:00 ora_dbw0_CLASSIC
oracle 4884 1 0 23:25 ? 00:00:01 oracleCLASSIC (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

1

2

3

4

5

6

7

8

# ps -eaf | grep CLASSIC | grep -v grep

oracle 4792 1 0 22:25 ? 00:00:00 ora_pmon_CLASSIC

oracle 4794 1 0 22:25 ? 00:00:00 ora_psp0_CLASSIC

oracle 4800 1 2 22:25 ? 00:00:01 ora_vktm_CLASSIC

oracle 4804 1 1 22:25 ? 00:00:00 ora_u004_CLASSIC

oracle 4810 1 7 22:25 ? 00:00:03 ora_u005_CLASSIC

oracle 4818 1 0 22:25 ? 00:00:00 ora_dbw0_CLASSIC

oracle 4884 1 0 23:25 ? 00:00:01 oracleCLASSIC (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

The remaining processes

So we have the Process Monitor (pmon), the Process Spawner (psp0), the Virtual Keeper of Time (vktm), the Database Writer (dbw0) and two new multithreaded processes (u004) and (u005). “U” can stand for User or Unified?

Where can I find the information on the other processes?

They still exist in the v$process view, thus leading to some confusion when talking about Oracle Processes with your sysadmins… The new EXECUTION_TYPE column show if the Oracle Process is executed as a thread or as an OS process, and the SPID let us know which process actually executes it.

  PID SPID      PNAME EXECUTION_
----- --------- ----- ----------
    2 4792      PMON  PROCESS   
    3 4794      PSP0  PROCESS   
    4 4800      VKTM  PROCESS   
    5 4804      GEN0  THREAD    
    6 4804      SCMN  THREAD    
   18 4804      LREG  THREAD    
   19 4804      RBAL  THREAD    
   20 4804      ASMB  THREAD    
   11 4804      DBRM  THREAD    
   14 4804      LGWR  THREAD    
   15 4804      CKPT  THREAD    
   16 4804      SMON  THREAD    
    7 4804      MMAN  THREAD    
   17 4810      RECO  THREAD    
   12 4810      DIA0  THREAD    
   10 4810      SCMN  THREAD    
    9 4810      DIAG  THREAD    
   25 4810      N000  THREAD    
   50 4810      Q002  THREAD    
   49 4810      W004  THREAD    
   21 4810      MMON  THREAD    
   22 4810      MMNL  THREAD    
   23 4810      D000  THREAD    
   24 4810      S000  THREAD    
   51 4810      Q003  THREAD    
   26 4810      MARK  THREAD    
   27 4810      W001  THREAD    
   28 4810            THREAD    
   29 4810            THREAD    
   30 4810      TMON  THREAD    
   31 4810      TT00  THREAD    
   32 4810      SMCO  THREAD    
   33 4810      FBDA  THREAD    
   34 4810      W000  THREAD    
   35 4810      AQPC  THREAD    
   36 4810      CJQ0  THREAD    
   37 4810      P000  THREAD    
   38 4810      P001  THREAD    
   39 4810      P002  THREAD    
   40 4810      P003  THREAD    
   41 4810      VKRM  THREAD    
   42 4810            THREAD    
   43 4810      O000  THREAD    
   45 4810      W002  THREAD    
   46 4810      QM02  THREAD    
   47 4810      W003  THREAD    
   13 4818      DBW0  PROCESS   
    8 4884            PROCESS   
    1                 NONE

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

PID SPID PNAME EXECUTION_

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

2 4792 PMON PROCESS

3 4794 PSP0 PROCESS

4 4800 VKTM PROCESS

5 4804 GEN0 THREAD

6 4804 SCMN THREAD

18 4804 LREG THREAD

19 4804 RBAL THREAD

20 4804 ASMB THREAD

11 4804 DBRM THREAD

14 4804 LGWR THREAD

15 4804 CKPT THREAD

16 4804 SMON THREAD

7 4804 MMAN THREAD

17 4810 RECO THREAD

12 4810 DIA0 THREAD

10 4810 SCMN THREAD

9 4810 DIAG THREAD

25 4810 N000 THREAD

50 4810 Q002 THREAD

49 4810 W004 THREAD

21 4810 MMON THREAD

22 4810 MMNL THREAD

23 4810 D000 THREAD

24 4810 S000 THREAD

51 4810 Q003 THREAD

26 4810 MARK THREAD

27 4810 W001 THREAD

28 4810 THREAD

29 4810 THREAD

30 4810 TMON THREAD

31 4810 TT00 THREAD

32 4810 SMCO THREAD

33 4810 FBDA THREAD

34 4810 W000 THREAD

35 4810 AQPC THREAD

36 4810 CJQ0 THREAD

37 4810 P000 THREAD

38 4810 P001 THREAD

39 4810 P002 THREAD

40 4810 P003 THREAD

41 4810 VKRM THREAD

42 4810 THREAD

43 4810 O000 THREAD

45 4810 W002 THREAD

46 4810 QM02 THREAD

47 4810 W003 THREAD

13 4818 DBW0 PROCESS

8 4884 PROCESS

1 NONE

What about the User processes?

Well, I’ve spawned 200 user processes with sqlplus, and got 200 threads:

SQL> select BACKGROUND, EXECUTION_TYPE, count(*)
2> from v$process group by background, EXECUTION_TYPE;

B EXECUTION_   COUNT(*)
- ---------- ----------
1 PROCESS             4
1 THREAD             34
  PROCESS             1
  NONE                1
  THREAD            200

1

2

3

4

5

6

7

8

9

10

SQL> select BACKGROUND, EXECUTION_TYPE, count(*)

2> from v$process group by background, EXECUTION_TYPE;

B EXECUTION_ COUNT(*)

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

1 PROCESS 4

1 THREAD 34

PROCESS 1

NONE 1

THREAD 200

On the OS side, I’ve registered an additional process to distribute the load of the new user processes. Damn, I start to being confusional using the term “process” o_O

[oracle@luc12c01 ~]$ ps -eaf | grep CLASSIC | grep -v grep

oracle 4792 1 0 22:25 ? 00:00:01 ora_pmon_CLASSIC
oracle 4794 1 0 22:25 ? 00:00:01 ora_psp0_CLASSIC
oracle 4800 1 2 22:25 ? 00:03:28 ora_vktm_CLASSIC
oracle 4804 1 0 22:25 ? 00:00:08 ora_u004_CLASSIC
oracle 4810 1 0 22:25 ? 00:01:09 ora_u005_CLASSIC
oracle 4818 1 0 22:25 ? 00:00:00 ora_dbw0_CLASSIC
oracle 4884 1 0 22:25 ? 00:00:01 oracleCLASSIC (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 8083 1 1 23:50 ? 00:00:03 ora_u010_CLASSIC

1

2

3

4

5

6

7

8

9

10

[oracle@luc12c01 ~]$ ps -eaf | grep CLASSIC | grep -v grep

oracle 4792 1 0 22:25 ? 00:00:01 ora_pmon_CLASSIC

oracle 4794 1 0 22:25 ? 00:00:01 ora_psp0_CLASSIC

oracle 4800 1 2 22:25 ? 00:03:28 ora_vktm_CLASSIC

oracle 4804 1 0 22:25 ? 00:00:08 ora_u004_CLASSIC

oracle 4810 1 0 22:25 ? 00:01:09 ora_u005_CLASSIC

oracle 4818 1 0 22:25 ? 00:00:00 ora_dbw0_CLASSIC

oracle 4884 1 0 22:25 ? 00:00:01 oracleCLASSIC (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

oracle 8083 1 1 23:50 ? 00:00:03 ora_u010_CLASSIC

On the session side however, all the user processes are DEDICATED.

SQL&gt; select server, count(*) from v$session group by server;

SERVER COUNT(*)
--------- ----------
DEDICATED 232

1

2

3

4

5

SQL> select server, count(*) from v$session group by server;

SERVER COUNT(*)

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

DEDICATED 232

A huge side effect

By using the multithreaded execution, the operating system authentication doesn’t work.

[oracle@luc12c01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Fri May 10 01:14:17 2013

Copyright (c) 1982, 2013, Oracle. All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied

Enter user-name:

1

2

3

4

5

6

7

8

9

10

[oracle@luc12c01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Fri May 10 01:14:17 2013

Copyright (c) 1982, 2013, Oracle. All rights reserved.

ERROR:

ORA-01017: invalid username/password; logon denied

Enter user-name:

Unless Oracle will review it’s authentication mechanism in a future patchset, you’ll need to rely on the password file and use the password to connect to the instance as sysdba, even locally.

What about performance?

In theory, threads should be faster and with a lower footprint:

The main benefit of threads (as compared to multiple processes) is that the context switches are much cheaper than those required to change current processes. Sun reports that a fork() takes 30 times as long as an unbound thread creation and 5 times as long as a boundthread creation.

http://www.princeton.edu/~unix/Solaris/troubleshoot/process.html

and

In some operating systems running on some hardware, switching between threads belonging to the same process is much faster than switching to a thread from different process (because it requires more complicated process context switch).
http://en.wikipedia.org/wiki/Thread_switching_latency

In practice, I’ll do some tests and let you know! 🙂

What about the good old OS kill command to terminate processes?

Good question! Currently I have not found any references to an orakill command (that exists on Windows). Hope it will arrive soon!

Cheers

—

Ludo

How to collect Oracle Application Server performance data with DMS and RRDtool

Posted on March 2, 2009 by Ludovico

Reply

RRDize everything, chapter 1

If you are managing some Application Server deployments you should have wondered how to check and collect performance data.
As stated in documentation, you can gather performance metrics with the dmstool utility.
AFAIK, this can be done from 9.0.2 release upwards, but i’m concerned DMS will not work on Weblogic.

Mainly, you should have an external server that acts as collector (it could be a server in the Oracle AS farm as well): copy the dms.jar library from an Oracle AS installation to your collector and use it as you would use dmstool:

java -jar dms.jar [dmstool options]

1	java -jar dms.jar [dmstool options]

There are three basilar methods to get data:

Get all metrics at once:

java -jar dms.jar -dump -a "youraddress://..." [format=xml]

1	java -jar dms.jar -dump -a "youraddress://..." [format=xml]

Get only the interesting metrics:

java -jar dms.jar -a "youraddress://..." metric metric ...

1	java -jar dms.jar -a "youraddress://..." metric metric ...

Get metrics included into specific DMS tables:

java -jar dms.jar -a "youraddress://..." -table table table ...

1	java -jar dms.jar -a "youraddress://..." -table table table ...

What youraddress:// is, it depends on the component you are trying to connect:

opmn://asserver:6003
http://asserver:7200/dms0/Spy
ajp13://asserver:3301/dmsoc4j/Spy

1

2

3

opmn://asserver:6003

http://asserver:7200/dms0/Spy

ajp13://asserver:3301/dmsoc4j/Spy

If you are trying to connect to the OHS (Apache), be careful to allow remote access from the collector by editing the dms.conf file.

Now that you can query dms data, you should store it somewhere.
Personally, I did a first attempt with dmstool -dump format=xml. I wrote a parser in PHP with SimpleXML extension and I did a lot of inserts into a MySQL database. After a few months the whole data collected from tens of servers was too much to be mantained…
To avoid the maintenance of a DWH-grade database I investigated and found RRDTool. Now I’m asking how could I live without it!

I then wrote a parser in awk that parse the output of the dms.jar call and invoke an rrdtool update command.
I always use dms.jar -table command. The output has always the same format:

###SOF

Mon Mar 02 17:01:19 CET 2009

---------------
TABLE1_Name
---------------

record1_metric1.name:     value       units
record1_metric2.name:     value       units
....

record2_metric1.name:     value       units
record2_metric2.name:     value       units
....

---
TABLE2_Name
---

record1_metric1.name:     value       units
record1_metric2.name:     value       units
....

record2_metric1.name:     value       units
record2_metric2.name:     value       units
....

##EOF

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

###SOF

Mon Mar 02 17:01:19 CET 2009

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

TABLE1_Name

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

record1_metric1.name: value units

record1_metric2.name: value units

....

record2_metric1.name: value units

record2_metric2.name: value units

....

---

TABLE2_Name

---

record1_metric1.name: value units

record1_metric2.name: value units

....

record2_metric1.name: value units

record2_metric2.name: value units

....

##EOF

So I written an awk file that works for me.
use it this way:

 java -jar dms.jar ... | awk -f parse_output.awk

1	java -jar dms.jar ... \| awk -f parse_output.awk

####################
# parse_output.awk #
####################

#function pl() replaces all non alphanumeric occurrences with an underscore
function pl(input) {
        return gensub("[^[:alnum:]_-]","_","G",input);
}

# function get_rrd_path() returns a path where the rrd files should be placed
# I should rewrite a new path for each dms table... I'll skip many of them
function get_rrd_path() {
        if (table == "mod_oc4j_destination_metrics")
                return sprintf("%s/%s/%s/%s.rrd", record["Host"],
                    pl(table), pl(record["Name.value"]), pl(var) );
        if (table == "mod_oc4j_mount_pt_metrics")
                return sprintf("%s/%s/%s/%s/%s.rrd", record["Host"],
                    pl(table), pl(record["Destination.value"]), pl(record["Name.value"]), pl(var) );
        if (table == "ohs_server")
                return sprintf("%s/%s/%s.rrd", record["Host"], pl(table), pl(var) );
        if (table == "JVM")
                return sprintf("%s/%s/%s/%s.rrd", record["Host"],
                    pl(table), pl(record["Process"]), pl(var) );
        if (table == "opmn_process")
                return sprintf("%s/%s/%s/%s/%s/%s/%s/%s.rrd", record["Host"], pl(table),
                  pl(record["iasInstance.value"]), pl(record["opmn_ias_component"]),
                  pl(record["opmn_process_type"]),pl(record["opmn_process_set"]),
                  pl(record["Name"]), pl(var) );

        return sprintf("%s/%s/%s.rrd", record["Host"], pl(table), pl(var) );
}
# function process_record actually does the dirty work of invoking the update script
function process_record() {
        #every record has a timeStamp.ts metric that I should use to update my rrd
        ts=substr(record["timeStamp.ts"],0,10);
        for ( var in record ) {
        if ( var != "timeStamp.ts" &amp;&amp; record[var] ~ /^[[:digit:]]+$/ ) {
            if ( var ~ /\.(count|completed|time)$/ ) {
                dstype="DERIVE";
            } else {
                if ( var == "responseSize.value" ) {
                    dstype="DERIVE";
                } else {
                    dstype="GAUGE";
                }
            }
            rrdFile=sprintf("/path_to_data/%s",get_rrd_path());
            #### update_metric_rrd is a shell script listed below!!!!!
            cmd=sprintf("/path_to_scripts/update_metric_rrd %s %s %d %d",
                rrdFile,dstype,ts,record[var]);
            system(cmd);
            }
        }
}

# parse_record() populates an hash array
# with all metrics belonging to the table record
function parse_record() {
    #print "RRRR -  START OF RECORD (table " table ")"
    delete record
    while ( ! /^$/ ) {
        # I'm parsing the record as far I'm in this while statement
        # the array hash is the name of the dms metric basename.
        # $1 is the metric name but I have to trim the final ":"
        key=substr($1,0,length($1)-1)
        record[key]=$2
        getline
    }
    # this function is included in funcions.awk:
    # I invoke it to process the record I've just parsed
    process_record();
}
BEGIN {
    # as far as started is 0, I've never reached the first table
    started=0
}

#MAIN
{
    # I jump over the first lines until I reach the first table
    if (started==0) {
        while ( ! /^---/ ) {
           getline
        }
        started=1
    }

    # looking for the next occurrence of a table
    # all tables start with:
    # ----------
    # table_name
    # ----------
    if ( /^---/ ) {
        # first table reached: the next row is my table name,
        # then I reach again a dashed line -----
        getline table
        getline trash
        #print ""
        #print "##########################"
        print "  TABELLA " table
        #print "##########################"
        next
    }

    if ( ! /^$/ ) {
        # reached an empty line: could be the end of a record or the and of a table
        # since a new table is threated in previous "if" statement, I'm starting a new record.
        parse_record()
    }

}

END {
}

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

####################

# parse_output.awk #

####################

#function pl() replaces all non alphanumeric occurrences with an underscore

function pl(input) {

return gensub("[^[:alnum:]_-]","_","G",input);

}

# function get_rrd_path() returns a path where the rrd files should be placed

# I should rewrite a new path for each dms table... I'll skip many of them

function get_rrd_path() {

if (table == "mod_oc4j_destination_metrics")

return sprintf("%s/%s/%s/%s.rrd", record["Host"],

pl(table), pl(record["Name.value"]), pl(var) );

if (table == "mod_oc4j_mount_pt_metrics")

return sprintf("%s/%s/%s/%s/%s.rrd", record["Host"],

pl(table), pl(record["Destination.value"]), pl(record["Name.value"]), pl(var) );

if (table == "ohs_server")

return sprintf("%s/%s/%s.rrd", record["Host"], pl(table), pl(var) );

if (table == "JVM")

return sprintf("%s/%s/%s/%s.rrd", record["Host"],

pl(table), pl(record["Process"]), pl(var) );

if (table == "opmn_process")

return sprintf("%s/%s/%s/%s/%s/%s/%s/%s.rrd", record["Host"], pl(table),

pl(record["iasInstance.value"]), pl(record["opmn_ias_component"]),

pl(record["opmn_process_type"]),pl(record["opmn_process_set"]),

pl(record["Name"]), pl(var) );

return sprintf("%s/%s/%s.rrd", record["Host"], pl(table), pl(var) );

}

# function process_record actually does the dirty work of invoking the update script

function process_record() {

#every record has a timeStamp.ts metric that I should use to update my rrd

ts=substr(record["timeStamp.ts"],0,10);

for ( var in record ) {

if ( var != "timeStamp.ts" && record[var] ~ /^[[:digit:]]+$/ ) {

if ( var ~ /\.(count|completed|time)$/ ) {

dstype="DERIVE";

} else {

if ( var == "responseSize.value" ) {

dstype="DERIVE";

} else {

dstype="GAUGE";

}

}

rrdFile=sprintf("/path_to_data/%s",get_rrd_path());

#### update_metric_rrd is a shell script listed below!!!!!

cmd=sprintf("/path_to_scripts/update_metric_rrd %s %s %d %d",

rrdFile,dstype,ts,record[var]);

system(cmd);

}

}

}

# parse_record() populates an hash array

# with all metrics belonging to the table record

function parse_record() {

#print "RRRR - START OF RECORD (table " table ")"

delete record

while ( ! /^$/ ) {

# I'm parsing the record as far I'm in this while statement

# the array hash is the name of the dms metric basename.

# $1 is the metric name but I have to trim the final ":"

key=substr($1,0,length($1)-1)

record[key]=$2

getline

}

# this function is included in funcions.awk:

# I invoke it to process the record I've just parsed

process_record();

}

BEGIN {

# as far as started is 0, I've never reached the first table

started=0

}

#MAIN

{

# I jump over the first lines until I reach the first table

if (started==0) {

while ( ! /^---/ ) {

getline

}

started=1

}

# looking for the next occurrence of a table

# all tables start with:

# ----------

# table_name

# ----------

if ( /^---/ ) {

# first table reached: the next row is my table name,

# then I reach again a dashed line -----

getline table

getline trash

#print ""

#print "##########################"

print " TABELLA " table

#print "##########################"

next

}

if ( ! /^$/ ) {

# reached an empty line: could be the end of a record or the and of a table

# since a new table is threated in previous "if" statement, I'm starting a new record.

parse_record()

}

}

END {

}

And this is the code for update_metric_rrd:

#!/bin/bash
RRDFILE=$1
DSTYPE=$2
TS=$3
VALUE=$4

rrdtool update $RRDFILE ${TS}:${VALUE}

if [ $? -ne 0 ] ; then
        DIR=`dirname $RRDFILE`

        [ -d $DIR ] || mkdir -p $DIR
        [ -f $RRDFILE ] || rrdtool create $RRDFILE -b "now-1month" -s 1800 \
                DS:metric:${DSTYPE}:7200:0:U \
                RRA:AVERAGE:0.5:1:672 \
                RRA:AVERAGE:0.5:4:1080 \
                RRA:AVERAGE:0.5:12:1460 \
                RRA:AVERAGE:0.5:48:1095 \
                RRA:MAX:0.5:4:1080 \
                RRA:MAX:0.5:12:1460 \
                RRA:MAX:0.5:48:1095 \
                RRA:LAST:0.5:1:672
        rrdtool update $RRDFILE ${TS}:${VALUE}
fi

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

#!/bin/bash

RRDFILE=$1

DSTYPE=$2

TS=$3

VALUE=$4

rrdtool update $RRDFILE ${TS}:${VALUE}

if [ $? -ne 0 ] ; then

DIR=`dirname $RRDFILE`

[ -d $DIR ] || mkdir -p $DIR

[ -f $RRDFILE ] || rrdtool create $RRDFILE -b "now-1month" -s 1800 \

DS:metric:${DSTYPE}:7200:0:U \

RRA:AVERAGE:0.5:1:672 \

RRA:AVERAGE:0.5:4:1080 \

RRA:AVERAGE:0.5:12:1460 \

RRA:AVERAGE:0.5:48:1095 \

RRA:MAX:0.5:4:1080 \

RRA:MAX:0.5:12:1460 \

RRA:MAX:0.5:48:1095 \

RRA:LAST:0.5:1:672

rrdtool update $RRDFILE ${TS}:${VALUE}

fi

Once you have all your rrd files populated, it’s easy to script automatic reporting. You would probably want a graph with the request count served by your Apache cluster, along with its linear regression:

rrdtool graph - -s "end-${hours}hours" -e $end \
                -v "Requests Completed/sec" \
        -w 640 -h 240 --slope-mode \
                -t "HTTP Requests for www.ludovicocaldara.net" \
                DEF:1request_completed=/data/wwwserver1/ohs_server/request_completed.rrd:metric:AVERAGE \
                DEF:2request_completed=/data/wwwserver2/ohs_server/request_completed.rrd:metric:AVERAGE \
                CDEF:request_completed=1request_completed,2request_completed,+ \
                VDEF:slope=request_completed,LSLSLOPE \
                VDEF:lslint=request_completed,LSLINT \
                CDEF:reg=request_completed,POP,slope,COUNT,*,lslint,+ \
                LINE1:reg#666666:"Regression" \
                AREA:1request_completed#4040AA:"wwwserver1"  \
                AREA:2request_completed#6666FF:"wwwserver1":STACK  \
        &gt; mygraph.png

1

2

3

4

5

6

7

8

9

10

11

12

13

14

rrdtool graph - -s "end-${hours}hours" -e $end \

-v "Requests Completed/sec" \

-w 640 -h 240 --slope-mode \

-t "HTTP Requests for www.ludovicocaldara.net" \

DEF:1request_completed=/data/wwwserver1/ohs_server/request_completed.rrd:metric:AVERAGE \

DEF:2request_completed=/data/wwwserver2/ohs_server/request_completed.rrd:metric:AVERAGE \

CDEF:request_completed=1request_completed,2request_completed,+ \

VDEF:slope=request_completed,LSLSLOPE \

VDEF:lslint=request_completed,LSLINT \

CDEF:reg=request_completed,POP,slope,COUNT,*,lslint,+ \

LINE1:reg#666666:"Regression" \

AREA:1request_completed#4040AA:"wwwserver1" \

AREA:2request_completed#6666FF:"wwwserver1":STACK \

> mygraph.png

This is the result:
OHS request completed
OHHHHHHHHHHHH!!!! COOL!!!!

That’s all for DMS capacity planning. Stay tuned, more about rrdtool is coming!

Awk snippet to count TCP sockets grouped by state

Posted on January 19, 2009 by Ludovico

Reply

Depending on the release of awk it could be:

#!/usr/bin/gawk -f
{
        if ( ($NF) in stats )  {
                stats[$NF] = stats[$NF]+1;
        } else {
                stats[$NF]=1;
        }
}
END {
        for ( var in stats) {
                print var " = " stats[var];
        }
}

1

2

3

4

5

6

7

8

9

10

11

12

13

#!/usr/bin/gawk -f

{

if ( ($NF) in stats ) {

stats[$NF] = stats[$NF]+1;

} else {

stats[$NF]=1;

}

}

END {

for ( var in stats) {

print var " = " stats[var];

}

}

I saved the script as netstat_c.
I have to filter my netstat output to match only my tcp sockets prior to pipe the output to the script.

On linux:

$ netstat -a | grep ^tcp | netstat_c
LISTEN = 13
ESTABLISHED = 74
TIME_WAIT = 7

1

2

3

4

$ netstat -a | grep ^tcp | netstat_c

LISTEN = 13

ESTABLISHED = 74

TIME_WAIT = 7

This is great to check my webserver connections when I do stress tests.

Quick Oracle Dataguard check script

Posted on January 5, 2009 by Ludovico

9

Oracle Dataguard has his own command-line dgmgrl to check the whole dataguard configuration status.
At least you should check that the show configuration command returns SUCCESS.

This is an hypothetic script:

#!/bin/bash
export ORACLE_HOME=/u1/app/oracle/product/10.2.0
export ORACLE_SID=orcldg
result=`echo "show configuration;" | \
  $ORACLE_HOME/bin/dgmgrl sys/strongpasswd | \
  grep -A 1 "Current status for" | grep -v "Current status for"`
if [ "$result" = "SUCCESS" ] ; then
    exit 0
else
    exit 1
fi

1

2

3

4

5

6

7

8

9

10

11

#!/bin/bash

export ORACLE_HOME=/u1/app/oracle/product/10.2.0

export ORACLE_SID=orcldg

result=`echo "show configuration;" | \

$ORACLE_HOME/bin/dgmgrl sys/strongpasswd | \

grep -A 1 "Current status for" | grep -v "Current status for"`

if [ "$result" = "SUCCESS" ] ; then

exit 0

else

exit 1

fi

Another script should check for the gap between production online log and the log stream received by the standby database. This can be accomplished with v$managed_standby view.
The Total Block Gap between production and standby can be calculated this way:
Sum all blocks from v$archived_logs where seq# between Current Standby Seq# and Current Production Seq#. Then add current block# of the production LGWR process and subtract current block# from RFS standby process. This gives you total blocks even if there is a log sequence gap between sites.
This is NOT the gap of online log APPLIED to the standby database. THIS IS THE GAP OF ONLINE LOG TRANSMITTED TO THE STANDBY RFS PROCESS and can be used to monitor your dataguard transmission from production to disaster recovery environment.

This is an excerpt of such script (please take care that it does not check against RFS failures, so it can fails when RFS is not alive):

#!/u1/app/oracle/product/10.2.0/perl/bin/perl -w
use DBI;
use DBD::Oracle qw(:ora_session_modes);
# DB connection #
my $prod  = "orclprod";
my $stby = "orcldr";
my $prodh;
unless ($prodh = DBI->connect('dbi:Oracle:'.$prod,
  'sys', 'strongpassword',
  {PrintError=>0, AutoCommit => 0,
  ora_session_mode => ORA_SYSDBA}))  {
    print "Error connecting to DB: $DBI::errstr\n";
        exit(1);
}
$prodh->{RaiseError}=1;

my $stbyh;
unless ($stbyh = DBI->connect('dbi:Oracle:'.$stby,
  'sys', 'strongpassword',
  {PrintError=>0, AutoCommit => 0,
  ora_session_mode => ORA_SYSDBA}))  {
    print "Error connecting to DB: $DBI::errstr\n";
        $prodh->disconnect;
        exit(1);
}
$stbyh->{RaiseError}=1;

my $sth;
### query prod
$sth = $prodh->prepare( < <eosql );
        select SEQUENCE#, BLOCK# from v\$managed_standby
        where process='LGWR'
EOSQL
$sth->execute();
my ($psequence, $pblock) = $sth->fetchrow_array();
$sth->finish();
### query stdby
$sth = $stbyh->prepare( < </eosql><eosql );
        select SEQUENCE#, BLOCK# from v\$managed_standby
        where process='RFS' and client_process='LGWR'
EOSQL
$sth->execute();
my ($ssequence, $sblock) = $sth->fetchrow_array();
$sth->finish();

printf ("PROD   : %10d %10d\n", $psequence, $pblock);
printf ("STANDBY: %10d %10d\n", $ssequence, $sblock);

$sth = $stbyh->prepare( < </eosql><eosql );
        select nvl(sum(blocks),0)
        + $pblock - $sblock as BLOCK_GAP
    from v\$archived_log
        where sequence# between $ssequence and $psequence
EOSQL
$sth->execute();
my ($blockgap) = $sth->fetchrow_array();
$sth->finish();
printf ("%-10d blocks gap\n", $blockgap);

$stbyh->disconnect;
$prodh->disconnect;
</eosql>

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

#!/u1/app/oracle/product/10.2.0/perl/bin/perl -w

use DBI;

use DBD::Oracle qw(:ora_session_modes);

# DB connection #

my $prod = "orclprod";

my $stby = "orcldr";

my $prodh;

unless ($prodh = DBI->connect('dbi:Oracle:'.$prod,

'sys', 'strongpassword',

{PrintError=>0, AutoCommit => 0,

ora_session_mode => ORA_SYSDBA})) {

print "Error connecting to DB: $DBI::errstr\n";

exit(1);

}

$prodh->{RaiseError}=1;

my $stbyh;

unless ($stbyh = DBI->connect('dbi:Oracle:'.$stby,

'sys', 'strongpassword',

{PrintError=>0, AutoCommit => 0,

ora_session_mode => ORA_SYSDBA})) {

print "Error connecting to DB: $DBI::errstr\n";

$prodh->disconnect;

exit(1);

}

$stbyh->{RaiseError}=1;

my $sth;

### query prod

$sth = $prodh->prepare( < <eosql );

select SEQUENCE#, BLOCK# from v\$managed_standby

where process='LGWR'

EOSQL

$sth->execute();

my ($psequence, $pblock) = $sth->fetchrow_array();

$sth->finish();

### query stdby

$sth = $stbyh->prepare( < </eosql><eosql );

select SEQUENCE#, BLOCK# from v\$managed_standby

where process='RFS' and client_process='LGWR'

EOSQL

$sth->execute();

my ($ssequence, $sblock) = $sth->fetchrow_array();

$sth->finish();

printf ("PROD : %10d %10d\n", $psequence, $pblock);

printf ("STANDBY: %10d %10d\n", $ssequence, $sblock);

$sth = $stbyh->prepare( < </eosql><eosql );

select nvl(sum(blocks),0)

+ $pblock - $sblock as BLOCK_GAP

from v\$archived_log

where sequence# between $ssequence and $psequence

EOSQL

$sth->execute();

my ($blockgap) = $sth->fetchrow_array();

$sth->finish();

printf ("%-10d blocks gap\n", $blockgap);

$stbyh->disconnect;

$prodh->disconnect;

</eosql>

Any comment is appreciated!

Tips: Bash Prompt and Oracle

Posted on December 30, 2008 by Ludovico

1

You may want to check the NEW VERSION of this prompt here.

export PS1=\u@\h:\w\$

1	export PS1=\u@\h:\w\$

I disagree with default bash prompt. Do you? It’s quote common to work with long paths:

ludovico@host:/u01/app/oracle/product/10.2.0/network/admin$ \
/nooo/this/command/line/is/really/long/and/offcourse -I \
-will -wrap -my -command -line

1

2

3

ludovico@host:/u01/app/oracle/product/10.2.0/network/admin$ \

/nooo/this/command/line/is/really/long/and/offcourse -I \

-will -wrap -my -command -line

and, when working on multi-database environments I need to check my environment:

env | grep -i oracle
#or
echo $ORACLE_SID
echo $ORACLE_HOME

1

2

3

4

env | grep -i oracle

#or

echo $ORACLE_SID

echo $ORACLE_HOME

I currently use this prompt, instead:

export PS1=$'\\n# [ $LOGNAME@\h:$PWD [\\t] [`ohvers` SID:${ORACLE_SID:-"no sid"}] ]\\n# '

# [ ludovico@caldara_2k:/u01/app/oracle/product/10.2.0/db_1/network/admin [23:15:58] [10.2.0 SID:orcl] ]
#

1

2

3

4

export PS1=$'\\n# [ $LOGNAME@\h:$PWD [\\t] [`ohvers` SID:${ORACLE_SID:-"no sid"}] ]\\n# '

# [ ludovico@caldara_2k:/u01/app/oracle/product/10.2.0/db_1/network/admin [23:15:58] [10.2.0 SID:orcl] ]

#

What is ohvers?? I defined this function to get the version of oracle from my ORACLE_HOME variable:

ohvers ()
{
echo -n $ORACLE_HOME | sed -n 's/.*\/\([[:digit:].]\+\)\/.*/\1/p'
}

1

2

3

4

ohvers ()

{

echo -n $ORACLE_HOME | sed -n 's/.*\/\([[:digit:].]\+\)\/.*/\1/p'

}

Pros:

I have a blank line that separate my prompt from previous output
I get the system clock (useful when saving my konsole history. Did I say konsole?)
I can see my Oracle Environment before launching dangerous commands
I have an empty line to start my endless commands
I have a lot of sharps “#” : they are fine against wrong copy&paste operations…

Suggestions?

Oracle RAC Standard Edition to achieve low cost and high performance

Posted on November 28, 2008 by Ludovico

Reply

I finished today to create a new production environment based on 2 Linux serverX86_64 and running Oracle RAC 10gR2. (I know, there is 11g right now, but I’m a conservative!)
Wheeew, I just spent a couple of hours applying all the recommended patches!
We choosed 2 nodes with a maximum of 2 multi-core processors each one so we can license Standard Edition instead of Enterprise Edition. 64bits addressing allow us to allocate many gigabytes of SGA. I’m starting with 5Gb but I think we’ll need more. And a set of 6x300Gb 15krpms disks (it can be expanded with more disks and more shelves).
This configuration keeps low the total cost of ownership but achieves best performance.
Due to disks layout, costs and needed usable storage, we had to configure one huge RAID5 on the SAN with multi-path. I decided anyway to create 2 ASM disk groups (ASM is mandatory for Standard Edition RAC), one for the DB, the second one for the recovery area. With spare disks we should have enough availability and even if it’s a RAID5 I saw good write performances (>150M/s).

Welcome new RAC, I hope we’ll feel good together!

It’s time to trouble…

Posted on November 21, 2008 by Ludovico

Reply

Sometimes it’s hard to find enough time to write something or even to only THINK about writing something…

The following are the projects I have to complete before the deadline of December 17th (at least if I still want to go on vacation…)

A totally new Oracle 10gR2 RAC SE on Linux (OCFS2, ASM) including jboss frontends, backups, monitoring, documentation. (Servers are ready today).

A Disaster recovery architecture based on Dataguard with scripts based on rsync to do filesystem replication, with failover and failback, including backups, monitoring, documentation. (The server in DR site is reachable via network today).

A 17 server infrastructure (among others a RAC 10gR2 on linux) transfer from Milan datacenter to here. It’s planned for december 11th but I have to crosscheck backup and contingency requirements.

A 14 server infrastructure (based on Windows and SqlServer) transfer from Milan datacenter to here. To be planned in december.

A totally new cold failover cluster based on linux with Oracle DBMS and E-business suite (Servers will be provided soon, I hope!).

A new standalone Windows Server 64bit to outstand the 32bit allocation bottleneck for a 500Gb oracle database (Server will be provided not before december 10th).

Normally manage the day-by-day work, including replying to e-mails and answering the phone.

AARGH!!