NEW option in ADRCI purge acommand - UTSCDMP

NEW option in ADRCI purge acommand - UTSCDMP
There is new option in ADRCI for purge command - UTSCDMP

adrci> help purge Usage: PURGE [[-i ] [-age [-type ALERT INCIDENT TRACE CDUMP HM UTSCDMP]]]: Purpose: Purge the diagnostic data in the current ADR home. If no option is specified, the default purging policy will be used. Options: [-i id1 id1 id2]: Users can input a single incident ID, or a range of incidents to purge. [-age ]: Users can specify the purging policy either to all the diagnostic data or the specified type. The data older than ago will be purged [-type ALERT INCIDENT TRACE CDUMP HM UTSCDMP]: Users can specify what type of data to be purged. Examples: purge purge -i 123 456 purge -age 60 -type incident

There where some issue that directories cdmp_* in repositories (=$diagnostic_dest/Diag/rdbms/database_name/instance_name/bdump ) are not purging automatically. When you run following command, it remove cdmp_* directories, which is older the 3600 Minutes

adrci> purge -age 3600 -type UTSCDMP

The views expressed are my own and not necessarily those of my current or past employer. The views and opinions expressed by visitors to this blog are theirs and do not necessarily reflect mine


Other ADR/ADRCI related posts

1. Automatic Diagnostic Repository (ADR)
2. Oracle 11g new Features , IPS ( Incident Packaging Service)
3. How to check alert.log in Oracle 11g

AIOUG - TechNight in Bangalore

The All India Oracle User Group with a 4 lakh -strong developer base and a community of seven-lakh users in India is organising a second meeting in Bangalore on October 22 to share experiences and provide insights into shape of things to come. It is the second largest technology network of professionals for Oracle after the US. Soon these TechNights will be held in other parts of India, such as Pune, Mumbai and Delhi.

Like several successful Oracle user groups around the world, AIOUG is also a not for profit organization formed by Oracle users for Oracle users. Let’s join hands across the country to make AIOUG a global lead user group.

AIOUG first TechNight held at Hyderabad, Please click here to see TechNight pictures

TechNight All India Oracle User Group (AIOUG)

AIOUG started his first ever Technical session in India at Hyderabad… On … Friday, July 18th 2008, between 5:00 PM to 8:00 PM. Session started with quick introduction about AIOUG, there mission goal by Murali Vallath. Latter Phani Arega took technical session on "Efficient SQL Programming - Some Tricks and Tips" and Vivek Sharma on " Real Time Performance Tuning".

Both the technical session was quite good and they shared lots of real life examples in there presentation. Here is some photos from AIOUG TechNight

Murali Vallath

Phani Arega

Vivek Sharma

Satyendra Kumar & PS Janakiram

DBMS_STATS Enhancements in Oracle 11g Database

DBMS_STATS Enhancements in Oracle 11g Database
Virag Sharma virag123@gmail.com

We know in 10g and 11g there is automatic job that collect stats of database based on certain preferences settings , lets have a look what are these preferences and what are there default values and how DBMS_STATS is different in 11g from 10g

Changing Preferences for Statistics

Preference name	Default Values
CASCADE	AUTO
DEGREE	AUTO
ESTIMATE_PERCENT	AUTO
METHOD_OPT	FOR ALL COLUMN SIZE AUTO
NO_INVALIDATE	AUTO
GRANULARITY	AUTO
PUBLISH ( New in 11g)	TRUE
INCREMENTAL new in 11g	FALSE
STALE_PERCENT (New in 11g)	10

# # In Oracle 10g # $ sqlplus "/ as sysdba" SQL*Plus: Release 10.2.0.1.0 - Production on Thu Mar 6 19:04:57 2008 SQL> SELECT dbms_stats.get_param('ESTIMATE_PERCENT') FROM dual; DBMS_STATS.GET_PARAM('ESTIMATE_PERCENT') -------------------------------------------------------------------------------- DBMS_STATS.AUTO_SAMPLE_SIZE SQL> SQL> SELECT dbms_stats.get_param('method_opt') FROM dual; DBMS_STATS.GET_PARAM('METHOD_OPT') -------------------------------------------------------------------------------- FOR ALL COLUMNS SIZE AUTO SQL> SELECT dbms_stats.get_param('GRANULARITY') FROM dual; DBMS_STATS.GET_PARAM('GRANULARITY') -------------------------------------------------------------------------------- AUTO SQL> SELECT dbms_stats.get_param('NO_INVALIDATE') FROM dual; DBMS_STATS.GET_PARAM('NO_INVALIDATE') -------------------------------------------------------------------------------- DBMS_STATS.AUTO_INVALIDATE SQL> SELECT dbms_stats.get_param('DEGREE') FROM dual; DBMS_STATS.GET_PARAM('DEGREE') -------------------------------------------------------------------------------- NULL SQL> SELECT dbms_stats.get_param('CASCADE') FROM dual; DBMS_STATS.GET_PARAM('CASCADE') -------------------------------------------------------------------------------- DBMS_STATS.AUTO_CASCADE <> SQL> exec dbms_stats.set_param('ESTIMATE_PERCENT','100'); PL/SQL procedure successfully completed. SQL> SELECT dbms_stats.get_param('ESTIMATE_PERCENT') FROM dual; DBMS_STATS.GET_PARAM('ESTIMATE_PERCENT') --------------------------------------------------------------------- 100 SQL> exec dbms_stats.RESET_PARAM_DEFAULTS(); PL/SQL procedure successfully completed. SQL> SELECT dbms_stats.get_param('ESTIMATE_PERCENT') FROM dual; DBMS_STATS.GET_PARAM('ESTIMATE_PERCENT') --------------------------------------------------------------------- DBMS_STATS.AUTO_SAMPLE_SIZE ->

GET_PARAM , RESET_GLOBAL_PREFS_DEFAULTS and SET_PARAM are obsolete in Oracle 11g.

In place of above procedures need to use following procedures GET_PREFS , RESET_GLOBAL_PREF_DEFAULTS and SET_GLOBAL_PREFS

SQL> Select dbms_stats.GET_PREFS('CASCADE') from dual; DBMS_STATS.GET_PREFS('CASCADE') -------------------------------------------------------------------------------- DBMS_STATS.AUTO_CASCADE SQL> Select dbms_stats.GET_PREFS('DEGREE') from dual; DBMS_STATS.GET_PREFS('DEGREE') -------------------------------------------------------------------------------- NULL SQL> Select dbms_stats.GET_PREFS('ESTIMATE_PERCENT') from dual; DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT') -------------------------------------------------------------------------------- DBMS_STATS.AUTO_SAMPLE_SIZE SQL> Select dbms_stats.GET_PREFS('METHOD_OPT') from dual; DBMS_STATS.GET_PREFS('METHOD_OPT') -------------------------------------------------------------------------------- FOR ALL COLUMNS SIZE AUTO SQL> Select dbms_stats.GET_PREFS('NO_INVALIDATE') from dual; DBMS_STATS.GET_PREFS('NO_INVALIDATE') -------------------------------------------------------------------------------- DBMS_STATS.AUTO_INVALIDATE SQL> Select dbms_stats.GET_PREFS('GRANULARITY') from dual; DBMS_STATS.GET_PREFS('GRANULARITY') -------------------------------------------------------------------------------- AUTO SQL> Select dbms_stats.GET_PREFS('PUBLISH') from dual; DBMS_STATS.GET_PREFS('PUBLISH') -------------------------------------------------------------------------------- TRUE SQL> Select dbms_stats.GET_PREFS('INCREMENTAL') from dual; DBMS_STATS.GET_PREFS('INCREMENTAL') -------------------------------------------------------------------------------- FALSE SQL> Select dbms_stats.GET_PREFS('STALE_PERCENT') from dual; DBMS_STATS.GET_PREFS('STALE_PERCENT') -------------------------------------------------------------------------------- 10

In Oracle 11g you can set preference not only on database level but on global level , schema level , table level as well. It really give more control to DBA for example on one table you want histograms should collected always or want to set METHOD_OPT => ‘FOR ALL COLUMNS SIZE 254’ on some tables

Procedure for setting preference on global , database , schema and table level are given below

1. SET_GLOBAL_PREFS

2. SET_DATABASE_PREFS

3. SET_SCHEMA_PREFS

4. SET_TABLE_PREFS

• Preference set on global apply for new objects or object which no preference available

• Database level preference will be applied on all objects in the database excluding the tables owned by Oracle. These tables can included by passing TRUE for the add_sys parameter of procedure.

• If you set preference value to NULL , it will set to Oracle default value

>

# #Test case # SQL> create table test1(abc number); Table created. SQL> create table test2(abc number); Table created. SQL> select dbms_stats.get_prefs('STALE_PERCENT', 'SYS', 'TEST1') from dual; DBMS_STATS.GET_PREFS('STALE_PERCENT','SYS','TEST1') -------------------------------------------------------------------------------- 10 SQL> select dbms_stats.get_prefs('STALE_PERCENT', 'SYS', 'TEST2') from dual; DBMS_STATS.GET_PREFS('STALE_PERCENT','SYS','TEST2') -------------------------------------------------------------------------------- 10 SQL> execute dbms_stats.set_table_prefs('SYS', 'TEST2', 'STALE_PERCENT', '35'); PL/SQL procedure successfully completed. SQL> select dbms_stats.get_prefs('STALE_PERCENT', 'SYS', 'TEST2') from dual; DBMS_STATS.GET_PREFS('STALE_PERCENT','SYS','TEST2') -------------------------------------------------------------------------------- 35 SQL> execute dbms_stats.set_global_prefs('STALE_PERCENT', '20'); PL/SQL procedure successfully completed. SQL> select dbms_stats.get_prefs('STALE_PERCENT', 'SYS', 'TEST1') from dual; DBMS_STATS.GET_PREFS('STALE_PERCENT','SYS','TEST1') -------------------------------------------------------------------------------- 20 SQL> select dbms_stats.get_prefs('STALE_PERCENT', 'SYS', 'TEST2') from dual; DBMS_STATS.GET_PREFS('STALE_PERCENT','SYS','TEST2') -------------------------------------------------------------------------------- 35 SQL> exec dbms_stats.SET_DATABASE_PREFS('STALE_PERCENT', '15'); PL/SQL procedure successfully completed. SQL> select dbms_stats.get_prefs('STALE_PERCENT', 'SYS', 'TEST1') from dual; DBMS_STATS.GET_PREFS('STALE_PERCENT','SYS','TEST1') -------------------------------------------------------------------------------- 20 SQL> select dbms_stats.get_prefs('STALE_PERCENT', 'SYS', 'TEST2') from dual; DBMS_STATS.GET_PREFS('STALE_PERCENT','SYS','TEST2') -------------------------------------------------------------------------------- 35 SQL> exec dbms_stats.set_database_prefs('STALE_PERCENT','30'); PL/SQL procedure successfully completed. SQL> select dbms_stats.get_prefs('STALE_PERCENT', 'SYS', 'TEST2') from dual; DBMS_STATS.GET_PREFS('STALE_PERCENT','SYS','TEST2') -------------------------------------------------------------------------------- 35 SQL> select dbms_stats.get_prefs('STALE_PERCENT', 'BUG', 'BUG_USER') from dual; DBMS_STATS.GET_PREFS('STALE_PERCENT','BUG','BUG_USER') -------------------------------------------------------------------------------- 30 SQL> exec dbms_stats.SET_DATABASE_PREFS('STALE_PERCENT', '15'); PL/SQL procedure successfully completed. SQL> select dbms_stats.get_prefs('STALE_PERCENT', 'SYS', 'TEST2') from dual; DBMS_STATS.GET_PREFS('STALE_PERCENT','SYS','TEST2') -------------------------------------------------------------------------------- 35 SQL> select dbms_stats.get_prefs('STALE_PERCENT', 'BUG', 'BUG_USER') from dual; DBMS_STATS.GET_PREFS('STALE_PERCENT','BUG','BUG_USER') -------------------------------------------------------------------------------- 15

Published and Pending Statistics

Statistics published by default when system/user/job collect stats. In Oracle 11g we have option to put newly collect stats in pending state and published latter once satisfied by performance testing based on new statistics.

In 10g we have face performance issue when collected stats on OLTP (24 X 7 ) database, probably because stats of one index/ table published before other. So collect stats in pending stat , test it and once satisfied with testing published it.

SQL> col name form a40 SQL> col VALUE form a6 SQL> col ISSES_MODIFIABL VALUE form a6 SQL> col ISSES_MODIFIABL form a6 SQL> col ISSYS_MODIFIABLE form a12 SQL> select name,value,isses_modifiable,issys_modifiable from v$parameter where name='optimizer_use_pending_statistics' ; NAME VALUE ISSES_MODIFIABL ISSYS_MODIFI ---------------------------------------- ------ --------------- ------------ optimizer_use_pending_statistics FALSE TRUE IMMEDIATE

# # Test Case # # By default Publish is set TRUE # SQL> select dbms_stats.get_prefs('PUBLISH') publish from dual PUBLISH --------- TRUE SQL> select dbms_stats.get_prefs('PUBLISH', 'VIRSHARM', 'TEST1') publish from dual; PUBLISH ------------ TRUE # # Setting Publish false for TEST1 table # SQL> exec dbms_stats.set_table_prefs('VIRSHARM', 'TEST1', 'PUBLISH', 'false'); PL/SQL procedure successfully completed. SQL> select dbms_stats.get_prefs('PUBLISH', 'VIRSHARM', 'TEST1') publish from dual; PUBLISH ------------- FALSE SQL> execute dbms_stats.gather_table_stats('VIRSHARM', 'TEST1'); PL/SQL procedure successfully completed. SQL> select table_name, last_analyzed from user_col_pending_stats where table_name ='TEST1' TABLE_NAME LAST_ANALYZED ---------- --------------- TEST1 30-APR-08 SQL> select table_name, last_analyzed from user_ind_pending_stats where table_name ='TEST1'; no rows selected SQL> select table_name, last_analyzed from user_col_pending_stats where table_name ='TEST1'; TABLE_NAME LAST_ANALYZED ---------- --------------- TEST1 30-APR-08 # # Following views will have no record for table TEST1 # because stats not published # SQL> select table_name, last_analyzed from user_tables where table_name = 'TEST1'; TABLE_NAME LAST_ANALYZED ------------------------------ ------------------- TEST1 SQL> select index_name, last_analyzed from user_indexes where table_name ='TEST1'; no rows selected SQL> select column_name, last_analyzed from user_tab_columns where table_name='TEST1' ; COLUMN_NAME LAST_ANALYZED ------------ --------------- ABC

>

# # Testing # SQL> ALTER SESSION SET optimizer_use_pending_statistics =TRUE; Session altered. # # OR export / import STATS # SQL> exec dbms_stats.create_stat_table('VIRSHARM','STATS'); PL/SQL procedure successfully completed. SQL> exec dbms_stats.export_pending_stats(tabname=>'TEST1',stattab=>'STATS'); PL/SQL procedure successfully completed. # # Use expdp /impdp ot exp/imp to import stats in TEST/QA database # SQL> exec dbms_stats.import_table_stats(ownname=>'VIRSHARM',tabname=>'TEST1', stattab=>'STATS'); select column_name, last_analyzed from user_tab_columns wheretable_name='TEST1';

# # Published stats # Publish the Stats to Data Dictionary for Optimizer Usage # SQL> exec dbms_stats.publish_pending_stats(tabname=>'TEST1'); PL/SQL procedure successfully completed.

If Stats published and performance become worse , then you can restore old version of stats. Oracle will manage the historical statistics repository, purging the statistics on a regular basis, by default every 31 days

By default Oracle keep stats for 31 day after that it purge. Retention can be increased useing following

SQL> exec DBMS_STATS.ALTER_STATS_HISTORY_RETENTION ( 60 ); PL/SQL procedure successfully completed.

11g RAC Database hanged or appears to hanged

11g RAC Database hanged or appears to hanged

Around lunch time 1:00 PM database hanged. When we tried to login as sysdba
our sqlplus session also hanged. we not able to login on either of node of RAC.
Thought to kill all the remote connection , so we will able to login

ps -aef |grep LOCAL=NO |awk '{ print $2}' |xargs kill -9

When your database hanged and you not able to login, we have used following procedure
to create hang analysis dump

sqlplus /nolog SQL> set _prelim on SQL> connect / as sysdba SQL> oradebug setmypid Statement processed. SQL> oradebug unlimit Statement processed. SQL> oradebug -g all hanganalyze 3 SQL> oradebug -g all hanganalyze 3 Cycle 1: (1/1965)--(1/1839) Hang Analysis in /u04/oraout/abcap/11gdiag/diag/rdbms/abcap/abc2ap/trace/abc2ap_diag_9269.trc

We also generate ASH report for that(=hang) duration.
Here is what , hang analysis show

# # Session id is in RED colour for further analysis # Chains most likely to have caused the hang: [a] Chain 1 Signature: 'row cache lock'<='row cache lock' (cycle) Chain 1 Signature Hash: 0x75bdd0c =============================================================================== Cycles: ------------------------------------------------------------------------------- Chain 1: ------------------------------------------------------------------------------- Oracle session identified by: { instance: 1 (abcap.abc1ap) os id: 27244 process id: 343, oracle@abc232 session id: 1839 session serial #: 12 } is waiting for 'row cache lock' with wait info: { p1: 'cache id'=0x7 p2: 'mode'=0x0 p3: 'request'=0x5 time in wait: 0.526566 secs heur. time in wait: 18.534318 secs timeout after: 2.473434 secs wait id: 49 blocking: 2 sessions current sql: short stack: <-ksedsts()+285<-ksdxfstk()+22<-ksdxcb()+1599<-sspuser()+102<-semtimedop()+36<-sskgpwwait()+211<-skgpwwait()+104<-ksliwat()+1256<-kslwaitctx()+135<-kqrigt()+1592<-kqrLockAndPinPo()+420<-kqrpre1()+915<-kqrpre()+57<-kziasfc()+235<-kpolnb()+5279<-kpoauth()+582<-opiodr()+991<-ttcpip()+1066<-opitsk()+1275<-opiino()+882<-opiodr()+991<-opidrv()+462<-sou2o()+91<-opimai_real()+109<-ssthrdmain()+142<-main()+116<-__libc_start_main()+211<-_start()+33 wait history: 1. event: 'row cache lock' wait id: 48 p1: 'cache id'=0x7 time waited: 3.001908 secs p2: 'mode'=0x0 p3: 'request'=0x5 2. event: 'row cache lock' wait id: 47 p1: 'cache id'=0x7 time waited: 3.001615 secs p2: 'mode'=0x0 wait id: 47 p1: 'cache id'=0x7 time waited: 3.001615 secs p2: 'mode'=0x0 p3: 'request'=0x5 3. event: 'row cache lock' wait id: 46 p1: 'cache id'=0x7 time waited: 3.000437 secs p2: 'mode'=0x0 p3: 'request'=0x5 } and is blocked by => Oracle session identified by: { instance: 1 (abcap.abc1ap) os id: 27941 process id: 125, oracle@abc232 session id: 1965 session serial #: 476 } which is waiting for 'row cache lock' with wait info: { p1: 'cache id'=0x7 p2: 'mode'=0x0 p3: 'request'=0x3 time in wait: 0.462402 secs heur. time in wait: 18.536906 secs timeout after: 2.537598 secs wait id: 30 blocking: 2 sessions current sql: short stack: <-ksedsts()+285<-ksdxfstk()+22<-ksdxcb()+1599<-sspuser()+102<-semtimedop()+36<-sskgpwwait()+211<-skgpwwait()+104<-ksliwat()+1256<-kslwaitctx()+135<-kqrigt()+1592<-kqrLockAndPinPo()+420<-kqrpre1()+915<-kqrpre()+57<-kkdlgui()+186<-kziavdb.()+2023<-kziaia.()+220<-kpolnb()+580<-kpoauth()+582<-opiodr()+991<-ttcpip()+1066<-opitsk()+1275<-opiino()+882<-opiodr()+991<-opidrv()+462<-sou2o()+91<-opimai_real()+109<-ssthrdmain()+142<-main()+116<-__libc_start_main()+211<-_start()+33 wait history: 1. event: 'row cache lock' wait id: 29 p1: 'cache id'=0x7 time waited: 3.001776 secs p2: 'mode'=0x0 p3: 'request'=0x3 2. event: 'row cache lock' wait id: 28 p1: 'cache id'=0x7 time waited: 3.001578 secs p2: 'mode'=0x0 p3: 'request'=0x3 3. event: 'row cache lock' wait id: 27 p1: 'cache id'=0x7

When checked above session ( as marked in RED in above windows ) in ASH table.
We found both sessions are belong to “sys” user.

Lets have a look on ASH report, one which we created, when database was hanged

Top User Events Event Event Class % Event Avg Active Sessions resmgr:cpu quantum Scheduler 80.66 55.53 row cache lock Concurrency 7.37 5.08 db file sequential read User I/O 3.55 2.45 null event Other 2.66 1.83 CPU + Wait for CPU CPU 1.90 1.31 Top Event P1/P2/P3 Values Event % Event P1 Value, P2 Value, P3 Value % Activity Parameter 1 Parameter 2 Parameter 3 resmgr:cpu quantum 80.75 "1","0","0" 60.85 location "2","0","0" 17.81 "3","0","0" 2.09 row cache lock 7.38 "10","0","3" 7.31 cache id mode request db file sequential read 3.58 "59","23006","1" 0.20 file# block# blocks <

So ASH report is showing different story , that mean database was not hanged , it appeared to hanged due to event “resmgr:cpu quantum”

As per

Oracle® Database Reference
11g Release 1 (11.1)
Part Number B28320-01

resmgr: cpu quantum

The session is waiting to be allocated a quantum of cpu. This event occurs when the resource manager is enabled and is throttling CPU consumption. To reduce the occurrence of this wait event, increase the CPU allocation for the sessions's current consumer group.

Wait Time: The time the session waited to acquire a CPU quantum

We have not enabled any resource plan , how it come into picture,In Oracle
11g all predefine maintenance windows use DEFAULT_MAINTENANCE_PLAN resource plan and Automatedmaintenance tasks run under its subplan ORA$AUTOTASK_SUB_PLAN supportEmptyParas]-->which has 25% resource allocation.

Possible Work around

Disable resource plan

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>