Reactive performance management By Craig Shallahamer

Last week Oracle University arrange Seminar by Performance Management guru Craig Shallahamer in India. Craig brings his unique experiences to many as a keynote speaker, a sought after teacher, a researcher and publisher for improving Oracle performance management, and also the architect of Horizone, OraPub's service level management solution.

His way of teaching is very very good , also contents of seminar is different and very effective. He focused on accurate performance diagnosis of even the most complex Oracle systems and how oracle interact with OS and application’s. I have been reading Craig’s papers/articles for long time , but never got full/big picture. After attending seminar his papers/articles look easy to understand.

Focus point of seminar was how to pin point issue area and different method to resolve it. He also cover some area of Mutex, IMU ( In memory Undo ) and performance forecasting etc. The way he teach latch Internals, anybody can understand it, even small kids J, he Create an honest and understandable story explaining the problem, the solutions, and how to best implement your solutions in a complex and highly available Oracle environment.

Second day of training , we had little tough time , as next to our conference hall in Oberoi Hotel, New Delhi , Shahrukh Khan( One of India Famous actor) was coming to inugrate/launch "Kya Aap Paanchvi Paas Se Tez Hain" new serial on Star Plus channel. But Craig enjoy music and continue with training

What CPU Jan 2008 Patch will not tell you

What CPU Jan 2008 Patch will not tell you

File system space :- opatch suppose to check files system space as a part of Running prerequisite checks... and start apply patch when enough space is there.

Patching component oracle.rdbms, 11.1.0.6.0...

Updating archive file "/u01/app/oracle/product/11.1.0.6/lib/libserver11.a" with "lib/libserver11.a/kzsr.o"
Updating archive file "/u01/app/oracle/product/11.1.0.6/lib/libserver11.a" with "lib/libserver11.a/kzu.o"

The following actions have failed:

Archive not applied /home/oracle/patch/CPU2008/6646866/6650135/files/lib/libserver11.a/kzsr.o to /u01/app/oracle/product/11.1.0.6/lib/libserver11.a... ''

Archive not applied /home/oracle/patch/CPU2008/6646866/6650135/files/lib/libserver11.a/kzu.o to /u01/app/oracle/product/11.1.0.6/lib/libserver11.a... ''

Do you want to proceed? [yn]

Well I came out with N option. When we started opatch again , it gave error some thing like spacecheck fail.

So make sure you have enough space on file system.

In case of RAC database, CPU patch readme.txt will not tell you that one need to set cluster_database = false , before starting database in upgrade mode(“startup upgrade”). For compiling views and objects.

So make sure, in RAC environment you have set cluster_database = false before starting database in upgrade mode

Secrets of Happiness

Secrets of Happiness

I was traveling from my from Agra (= City of Taj Mahal, 30 Miles away from my home town ) to Hyderabad. My Train AP Express was late so thought to pick some book. As usual I picked some my favorites books/magazines like Reader Digest , Champak ( Famous Kids book in India ). While purchasing books/magazines, saw book with title “Secrets of Happiness - Tanushree Podder”. Title looks very odd to me because , I feel how one can define happiness. Well , Just picked the book and browse some page , it look good. It is not different from other book like “Mega Living- Robin Sharma” , “Who will Cry when you die Robin Sharma”. English look typical Indian English. Some of the stories we already heard in our childhood from Grand Mother , Mom , aunty etc. But it is really nice to re-visit those stories. Writer presentation look good and that make book more interesting. I started reading book from Agra and keep reading book till Gwalior, Everybody in train want to sleep , since I was reading book light was on and everybody eye brow in train getting tight. Finally switched off light , but finished book before reaching Hyderabad. I feel book is worth to read , that’s why thought to write about this book.

Check book excerpt [click Here]

Summary

1. What you put in life , you get back
   
2. No situation is good / bad / ugly , it is our believe that colored our perception about situation and we feel accordingly(good / bad / ugly) about situation. So change our believe , thought things will improve/change, otherwise same thought/believe same result
   
3. Keep It Simple and Straight ( KISS) .....................................

Apart from this there are two more book, that really worth to read

Monk Who Sold His Ferrari – Robin Sharma

Follow Your Heart - Andrew Matthews

I read above book frequently , and feel , If I would have got these book 6 Year Back ……. :-)

>

Small change make difference

Small change make difference.

How small , small things make difference , here is one live example
One of my friend want to learn 11g, so she downloaded 11g and started installing on

Linux Box and created Database manually. Next time when she logged in, she did not know where she installed 11g , since she created database manually , so there was no entry in “oratab”

I remember command “pwdx” on Unix Solaris , which give current working directory of processes

$ uname -a SunOS mysun 5.8 Generic_xyz sun4u sparc SUNW,Ultra-Enterprise MYSUN:oracle> (10.1.0.4) /usr/proc/bin $ ps -aef grep pmon oracle 2424 1 0 Jan 18 ? 13:39 ora_pmon_test oracle 8337 13002 0 05:31:47 pts/7 0:00 grep pmon mysun:oracle> (10.1.0.4) /usr/proc/bin $ pwdx 2424 2424: /u01/app/oracle/product/10.1.0.4/dbs <

But on Linux(RHEL 4 ) there is no command like “pwdx” . In linux you can check current working directory of processes ( ORACLE_HOME/dbs i.e lock file location ) in /proc/

[root@apps001 proc]# ps -aef grep pmon oracle 2826 1 0 20:55 ? 00:00:00 xe_pmon_XE oracle 8268 1 0 21:21 ? 00:00:00 ora_pmon_orcl11g root 23180 13728 0 23:19 pts/2 00:00:00 grep pmon [root@apps001 proc]# ls -l /proc/8268 total 0 dr-xr-xr-x 2 oracle oinstall 0 Jan 27 23:20 attr -r-------- 1 oracle oinstall 0 Jan 27 23:20 auxv -r--r--r-- 1 oracle oinstall 0 Jan 27 23:19 cmdline lrwxrwxrwx 1 oracle oinstall 0 Jan 27 23:20 cwd -> /u01/app/oracle/11.1.0/dbs -r-------- 1 oracle oinstall 0 Jan 27 23:20 environ lrwxrwxrwx 1 oracle oinstall 0 Jan 27 23:20 exe -> /u01/app/oracle/11.1.0/bin/oracle dr-x------ 2 oracle oinstall 0 Jan 27 23:20 fd -rw-r--r-- 1 oracle oinstall 0 Jan 27 23:20 loginuid -r-------- 1 oracle oinstall 0 Jan 27 23:20 maps -rw------- 1 oracle oinstall 0 Jan 27 23:20 mem -r--r--r-- 1 oracle oinstall 0 Jan 27 23:20 mounts lrwxrwxrwx 1 oracle oinstall 0 Jan 27 23:20 root -> / -r--r--r-- 1 oracle oinstall 0 Jan 27 23:18 stat -r--r--r-- 1 oracle oinstall 0 Jan 27 23:20 statm -r--r--r-- 1 oracle oinstall 0 Jan 27 23:16 status dr-xr-xr-x 3 oracle oinstall 0 Jan 27 23:20 task -r--r--r-- 1 oracle oinstall 0 Jan 27 23:20 wchan

>

< [oracle@apps001 oracle]$ cd /u01/app/oracle/11.1.0/dbs [oracle@apps001 dbs]$ ls hc_orcl11g.dat initdw.ora init.ora lkORCL11G orapworcl11g spfileorcl11g.ora [oracle@apps001 dbs]$ /sbin/fuser * hc_orcl11g.dat: 8268 8268m 8270 8270m 8274 8274m 8276 8276m 8278 8278m 8282 8282m 8284 8284m 8286 8286m 8288 8288m 8290 8290m 8292 8292m 8294 8294m 8296 8296m 8298 8298m 8300 8302 8312 8312m 8314 8314m 8316 8316m 8320 8322 8394 8394m 22755 25372 lkORCL11G: 8268 8276 8278 8284 8286 8288 8290 8292 8294 8296 8298 8312 8314 8316 8320 8322 8394 22755 25372

I asked her to create one small shell script to source oracle 11g environment variable ,
she wrote following script

ORACLE_HOME=/u01/app/oracle/11.1.0/ export ORACLE_HOME PATH=$ORACLE_HOME/bin:$PATH export PATH ORACLE_SID=orcl11g export ORACLE_SID ~ "11g.env" 7L, 136C written [oracle@apps001 ~]$chmod 755 11g.env [oracle@apps001 ~]$ . ./11g.env

She called me after some time and said , after sourcing 11g environment variable , when she try to connect oracle 11g as sysdba , it says “connect to ideal instance”

[oracle@apps001 ~]$ . ./11g.env [oracle@apps001 ~]$ sqlplus “/ as sysdba” SQL*Plus: Release 11.1.0.6.0 - Production on Sun Jan 27 23:45:42 2008 Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to an idle instance. Ctl-d [oracle@apps001 ~]$ ps -aef grep pmon oracle 2826 1 0 20:55 ? 00:00:00 xe_pmon_XE oracle 8268 1 0 21:21 ? 00:00:00 ora_pmon_orcl11g oracle 24791 24446 0 23:46 pts/4 00:00:00 grep pmon [oracle@apps001 8268]$ cd /u01/app/oracle/11.1.0/dbs [oracle@apps001 dbs]$ /sbin/fuser * hc_orcl11g.dat: 8268 8268m 8270 8270m 8274 8274m 8276 8276m 8278 8278m 8282 8282m 8284 8284m 8286 8286m 8288 8288m 8290 8290m 8292 8292m 8294 8294m 8296 8296m 8298 8298m 8300 8302 8312 8312m 8314 8314m 8316 8316m 8320 8322 8394 8394m 22755 25372 lkORCL11G: 8268 8276 8278 8284 8286 8288 8290 8292 8294 8296 8298 8312 8314 8316 8320 8322 8394 22755 25372 # It means database is up (may be nomount , mount or open mode ) #

When ran “fuser” on lock file oracle BG processes are connected to lock file, It means database is up (may be nomount , mount or open mode )

Checked alert.log , to make sure things are fine. It shows database is OPEN

[oracle@apps001 oracle]$ adrci ADRCI: Release 11.1.0.6.0 - Beta on Sun Jan 27 23:52:16 2008 Copyright (c) 1982, 2007, Oracle. All rights reserved. ADR base = "/u01/app/oracle" adrci> set editor vi adrci> show alert Choose the alert log from the following homes to view: 1: diag/tnslsnr/apps001/listener 2: diag/rdbms/orcl2/orcl2 3: diag/rdbms/stdorcl2/stdorcl2 4: diag/rdbms/orcl11g/orcl11g Q: to quit Please select option:4 space available in the underlying filesystem or ASM diskgroup. 2008-01-27 21:22:07.366000 +05:30 Completed: ALTER DATABASE OPEN 2008-01-27 21:22:26.499000 +05:30 Starting background process CJQ0 CJQ0 started with pid=26, OS id=8394 2008-01-27 21:23:24.231000 +05:3 Thread 1 advanced to log sequence 3 Current log# 3 seq# 3 mem# 0: /u01/app/oracle/oradata/orcl11g/redo03.log 2008-01-27 21:29:03.616000 +05:30

Finally decided to check shell script , which source 11g environment variable. There suppose to be no issue at script because we able to run “adrci” and able to see alert.log

[oracle@apps001 ~]$vi 11g.env ORACLE_HOME=/u01/app/oracle/11.1.0/ export ORACLE_HOME PATH=$ORACLE_HOME/bin:$PATH export PATH ORACLE_SID=orcl11g export ORACLE_SID ~ ~ "11g.env" 7L, 136C written # # I made little change in script i.e. removed last “/” from ORACLE_HOME # vi 11g.env ORACLE_HOME=/u01/app/oracle/11.1.0 export ORACLE_HOME PATH=$ORACLE_HOME/bin:$PATH export PATH ORACLE_SID=orcl11g export ORACLE_SID ~ ~ "11g.env" 7L, 136C written

After I removed last “/” from ORACLE_HOME , she able to connect to database J

[oracle@apps001 ~]$ . ./11g.env [oracle@apps001 ~]$ sys SQL*Plus: Release 11.1.0.6.0 - Production on Mon Jan 28 00:00:54 2008 Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL>

So small change make difference.

Oracle 11g Security Enhancements Part – 1

Oracle 11g Security Enhancements Part – 1

New parameters have been added to the Oracle Database 11g to enhance the default security of the database.

• SEC_RETURN_SERVER_RELEASE
• SEC_PROTOCOL_ERROR_FURTHER_ACTION
• SEC_PROTOCOL_ERROR_TRACE_ACTION
• SEC_MAX_FAILED_FAILED_LOGIN_ATTEMPTS
• SEC_DISABLE_OLDER_ORACLE_RPCS*

These parameters are system wide and static.

• Release of server information restriction

You can restrict the display of the database version banner to unauthenticated clients by setting the SEC_RETURN_SERVER_RELEASE_BANNER initialization parameter in the initsid.ora initialization parameter file to either YES or NO. By default, SEC_RETURN_SERVER_RELEASE_BANNER is set to FALSE.

SQL> show parameter SEC_RETURN_SERVER_RELEASE_BANNER NAME TYPE VALUE ----------------------------- -------------------- --------------------- sec_return_server_release_banner boolean FALSE

When set to true the full banner is displayed. When the value is set to FALSE, a limited generic banner is displayed.

• Protect against denial of Service (DoS) attacks

The two parameters shown specify the actions to be taken when the database receives bad packets from a client. The assumption is that the bad packets are from a possible malicious client. The SEC_PROTOCOL_ERROR_FURTHER_ACTION parameter specifies what action is to be taken with the client connection: Continue, drop the connection, or delay accepting requests.

# Continue connection after 10 bad packet SEC_PROTOCOL_ERROR_FURTHER_ACTION = Continue #Delay 4 Sec before sever accept next connection SEC_PROTOCOL_ERROR_FURTHER_ACTION = Delay,4 # Drop connection after 10 bad packet SEC_PROTOCOL_ERROR_FURTHER_ACTION = Drop,10

The other parameter SEC_PROTOCOL_ERROR_TRACE_ACTION specifies a monitoring action: NONE, TRACE(Default) , LOG, or ALERT.

# DEFAULT Creates the trace files, but it is useful for debugging purposes, SEC_PROTOCOL_ERROR_TRACE_ACTION = Trace # Writes a short, one-line error message to the server trace file and alert log. SEC_PROTOCOL_ERROR_TRACE_ACTION = Alert # Writes a short, one-line message to the server trace file. SEC_PROTOCOL_ERROR_TRACE_ACTION = Log # Configures the server to ignore the bad packets and does not generate any trace files or SEC_PROTOCOL_ERROR_TRACE_ACTION = None

>

SQL> show parameter SEC_PROTOCOL_ERROR_FURTHER_ACTION NAME TYPE VALUE ------------------------------- ------------------- ------------------------ sec_protocol_error_further_action string CONTINUE SQL> show parameter SEC_PROTOCOL_ERROR_TRACE_ACTION NAME TYPE VALUE ---------------------- ------------------------- ----------------------- sec_protocol_error_trace_action string TRACE

• Protect against intruder

If profile is not enabled then intruder can try unlimited number of authenticated requests with different user names and passwords in an attempt to gain access to the database.

A new initialization parameter SEC_MAX_FAILED_LOGIN_ATTEMPTS that has a default setting of 10 causes a connection to be automatically dropped after the specified number of attempts. This parameter is enforced even when the password profile is not enabled.

SQL> show parameter SEC_MAX_FAILED_LOGIN_ATTEMPTS NAME TYPE VALUE -------------------------- -------------------------------------------------- sec_max_failed_login_attempts integer 10

>

The sqlnet.ora INBOUND_CONNECT_TIMEOUT parameter and the FAILED_LOGIN_ATTEMPTS initialization parameter also restrict failed logins, but the difference is that these two parameters only apply to valid user accounts.

>

Recently come across new parameter SEC_DISABLE_OLDER_ORACLE_RPCS on Database Error Messages 11g Release 1

>
ORA-03139: This OCI call has been disabled by the DBA
Cause: The SEC_DISABLE_OLDER_ORACLE_RPCS initialization parameter was enabled.
Action: Contact the Database Administrator

But when searched oracle documentation not found any detail about it , also not found any detail about it on Database

SQL> show parameter SEC_DISABLE_OLDER_ORACLE_RPCS SQL>