Coskan’s Approach to Oracle

December 6, 2010

Alert Log Monitoring script via ADRCI

Filed under: How To, Tips — coskan @ 4:47 pm

Before I start to write about the blog series in my mind which will be the base to my first presentation again in my mind, I would like to share simple alert log monitoring with adrci.

At first I was completely against Diagnostics Dest idea but after a bit of searching and learning new tips tricks of adrci command like I think I am a big fan of diagnostics dest. Below is the script I wrote to monitor alert log hourly and with a daily summary. Script is self explanatory, the only thing it does is gets ADRCI homes put them in a bash array and grep the ORA-, TNS- errors for the last hour or last day. It is not rocket since and it is based on a simple command which can also be added to your profiles as an alias.

--last day
adrci exec="set home ${adrci_home}; show alert -p \\\"message_text like '%ORA-%' and originating_timestamp > systimestamp-1\\\"" 
--last hour 
adrci exec="set home ${adrci_home}; show alert -p \\\"message_text like '%ORA-%' and originating_timestamp > systimestamp-1/24\\\"" 

I am pretty sure this could be shortened but I did not bother to shorten it so I leave it to your imagination:)

I did not add listener checks to hourly checks but to daily summary, because there can be many which will cause you to get mail every hour.

Good part of using ADRCI and hourly checks is you do not need to edit the file to get rid of the error because you will never see that alert again in the next hour slot.

Script will send the output to the files and read those files back to the mail content and I am sure there may be better way to do this.

Complete script

#################################################
###### ALERT LOG CHECKING VIA ADRCI #############
#################################################
# Author : Coskan Gundogar
# Version Date: 02/12/2010
# Usage :  
# To run for last 24 hours - ./check_alert.sh D
# To run for last hour - ./check_alert.sh 
# Edit variables below for moving between servers 
# Changes To the Script 
#

export ORACLE_SID=+ASM1
export ORACLE_HOME=/u01/crs/oracle/product/11.2.0/grid/
export PATH=$PATH:$ORACLE_HOME/bin
DAILY_LOG=/home/oracle/bin/alert_log_check_daily.txt
HOURLY_LOG=/home/oracle/bin/alert_log_check_hourly.txt
MAIL_SUBJ="PRD:WARNING HOST: " 
MAIL_RECIPIENT="your_dba_group@your_company.com"

HOST_NAME=`hostname -a`


if [ "$1" = "D" ]
then

		############################################
		###############DAILY CHECKS ################
		############DBMS AND ASM CHECK##############
		############################################

		adrci_homes=( $(adrci exec="show homes" | grep -e rdbms -e asm))

		echo '####################################################' > $DAILY_LOG 
		echo '####### ALERT LOG OUTPUT FOR LAST 24 HOURS #########' >> $DAILY_LOG
		echo '####################################################' >> $DAILY_LOG 
		echo ''  >> $DAILY_LOG 
		echo ''  >> $DAILY_LOG 
		echo ''  >> $DAILY_LOG 

		for adrci_home in ${adrci_homes[@]}
		do 
			echo $adrci_home' Alert Log' >> $DAILY_LOG 
			adrci exec="set home ${adrci_home}; show alert -p \\\"message_text like '%ORA-%' and originating_timestamp > systimestamp-1\\\"" -term >> $DAILY_LOG 
		done


		############################################
		############## DAILY CHECKS ################
		############# LISTENER  CHECK###############
		############################################


		adrci_lsnr_homes=( $(adrci exec="show homes" | grep -e tnslsnr))

		echo ''  >> $DAILY_LOG 
		echo ''  >> $DAILY_LOG 
		echo ''  >> $DAILY_LOG 
		echo '####################################################' >> $DAILY_LOG 
		echo '###### LISTENER LOG OUTPUT FOR LAST 24 Hours #######' >> $DAILY_LOG
		echo '####################################################' >> $DAILY_LOG 
		echo ''  >> $DAILY_LOG 
		echo ''  >> $DAILY_LOG 
		echo ''  >> $DAILY_LOG 


		for adrci_lsnr_home in ${adrci_lsnr_homes[@]}
		do 
			echo $adrci_lsnr_home' Listener Log' >> $DAILY_LOG 
			adrci exec="set home ${adrci_lsnr_home}; show alert -p \\\"message_text like '%TNS-%' and originating_timestamp > systimestamp-1\\\""  -term >> $DAILY_LOG 
		done
		

		num_errors=`grep -c -e 'TNS' -e 'ORA' $DAILY_LOG`
		if [ $num_errors != 0 ]
		then 
		MAIL_SUBJ=$MAIL_SUBJ$HOST_NAME" Errors Found in Daily Alert Summary"
		mailx -s "$MAIL_SUBJ" $MAIL_RECIPIENT  $HOURLY_LOG 
		echo ''  >> $HOURLY_LOG 
		echo ''  >> $HOURLY_LOG 
		echo '####################################################' >> $HOURLY_LOG 
		echo '######### ALERT LOG OUTPUT FOR LAST HOUR ###########' >> $HOURLY_LOG
		echo '####################################################' >> $HOURLY_LOG 
		echo ''  >> $HOURLY_LOG 
		echo ''  >> $HOURLY_LOG 
		echo ''  >> $HOURLY_LOG 

		for adrci_home in ${adrci_homes[@]}
		do 
			echo $adrci_home' Alert Log' >> $HOURLY_LOG 
			adrci exec="set home ${adrci_home}; show alert -p \\\"message_text like '%ORA-%' and originating_timestamp > systimestamp-1/24\\\"" -term >> $HOURLY_LOG 
		done


		num_errors=`grep -c -e 'TNS' -e 'ORA' $HOURLY_LOG`
		if [ $num_errors != 0 ]
		then 
		MAIL_SUBJ=$MAIL_SUBJ$HOST_NAME" Errors Found in Hourly Alert Summary"
		mailx -s "$MAIL_SUBJ" $MAIL_RECIPIENT < $HOURLY_LOG
		fi

fi

April 26, 2008

How to deal with spawned NMUPM.EXE

Filed under: How To, Tips — coskan @ 12:55 pm

It is sunny Saturday for London and I just stopped struggling with our central machine which has Quest Big Brother (pretty handy tool with less price then HP Open View)and an Oracle Database for our centralized checks running on it .

Before stopping by office, All I wanted was using internet banking securely, but could not ignore the errors on BB Screen for not being entitled as an invalid DBA :)

When I checked the BB Screen everything was in unchecked status. At first I thought Oracle instance was down. I checked the instance and it was alive so I logged on the machine to see whats going on. It is a Windows 2003 Box with enough hardware. Memory was fully utilized and there were 180 NMUPM.EXE process explorer running with 11MB memory usage each. (I use to see windows processes instead of task manager so I can investigate more deeply about a running process. ) Process Explorer was saying that it was an Oracle Enterprise manager related process. This was the first time I saw this process. I searched through metalink and saw that NMUPM.EXE sometimes utilize CPU 99 percent and sometimes spawns too many processes. CPU utilization is caused because of .Net Framework installation NoteID:203224.1 (not my case), spawning is caused by “pdh.dll (Performance Data Helper) found in $ORACLE_HOME/bin which contains APIs to collect performance data from Windows Environments. It is spawned after EM usage Note ID:436138.1. There have been other bugs in the past about nmupm performance based on other dlls being registered with pdh.”

The solution was removing this pdh.ddl and restarting the DB Console service.

The steps I tried to solve the problem;

1- Tried to stop DB Console service. (did not stop)

2- Killed the DB Console Service but it did not helped to kill the other NMUPM.EXE processes.

3- Tried to kill NMUPM.exe but got bored of one by one killing limitation of both task manager and process explorer.

4- Tried to restart eh machine from remote desktop. It did not work. Machine was alive and I lost my option to log on the machine via remote desktop. Tried to stop services by remote services administration option of windows but did not worked

5- Tried to do shutdown command for remote machine like below. It said the machine is shutdown in progress :(

shutdown -r -m \\uklondmeyregdu1

5- I dont have option to go server room and reset server via button so I Did some googling for how to see / kill processes running on remote machines. What I found is magical and very very useful command line tools named tasklist and taskkill. I checked the running processes by tasklist like below

tasklist /s \\REMOTESERVER /u DOMAIN\USERNAME

After I saw that processes are still running I ran the best command ever (for me)on windows, TASKKILL, which allows multiple killing with one command by just giving the image name

taskkill /s \\REMOTESERVER /im nmupm.exe /u DOMAIN\USERNAME

It killed all the nmupm.exe processes and restart kicked at the end.

I feel embarrassed about now knowing or searching for this two commands especially the taskkill before. Maybe you know it for ages but it is worth to stay at office and write on this sunny London Saturday.

In conclusion I still hate to run Oracle on windows :) however, it is really good to know how to use Windows effectively.

Database Version : 10.2.0.3 Windows

Resources Used;

Metalink Note ID:436138.1.

Windows Reference

March 15, 2007

Viewing plans of old SQLs

Filed under: How To — coskan @ 4:37 pm

Previously I wrote about how to view a plan of a sql. Today I will tell you about a good feature DBMS_XPLAN.DISPLAY_AWR function comes with Oracle 10G which helps you to view plan of an old sql. . If you have license for tuning pack and diagnostics pack you can get historical information about the old SQLs which ran on your database. For more info about licensing feature of these packs refer to the Oracle® Database Licensing Information 10g Release 1 (10.1) manual

DBMS_XPLAN.DISPLAY_AWR displays the contents of an execution plan stored in the AWR.

Syntax is;

DBMS_XPLAN.DISPLAY_AWR(
sql_id IN VARCHAR2,
plan_hash_value IN NUMBER DEFAULT NULL,
db_id IN NUMBER DEFAULT NULL,
format IN VARCHAR2 DEFAULT TYPICAL);

If db_id paramater is not specified the function will use the id of the local db.

If you don’t specify plan_hash_value parameter, function will bring all the stored execution plans for the given sql_id

Format parameter have so many capabilities you can get the list from the manual.

Simple demonstration ; (all tests are done with 10.2.0.1 Express Edition)

code listing 6a

You can also use DBA_HIST_SQL_PLAN table for viewing the historic plan info.

code listing 6b

References Used ;

Oracle® Database PL/SQL Packages and Types Reference 10g Release 2 (10.2)

Oracle® Database Reference 10g Release 2 (10.2)

March 9, 2007

converting scn to a timestamp

Filed under: How To, PL/SQL — coskan @ 2:58 pm

I saw the nice function below while i was reading about locking and latches section of Mr Thomas Kytes Book (Expert Oracle Database Architecture 9i and 10g Programming Techniques and Solutions) and i said to myself i have to share it for the ones like me :)

If you know the SCN (system change number) you can get its timestamp value (within about +/–3 seconds) by the function scn_to_timestamp. After looking to the manual for more info i saw two other nice functions about scn. They are all under DBMS_FLASHBACK package and not available for the releases prior to 10g. I found these functions useful for dataguard issues recovery issues and flashback issues.

GET_SYSTEM_CHANGE_NUMBER: for getting the current system change number of the database.

SQL> select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER from dual;

GET_SYSTEM_CHANGE_NUMBER
————————
884871

SCN_TO_TIMESTAMP: for converting given scn to timestamp value ;

SQL> select scn_to_timestamp(884871) as timestamp from dual;

TIMESTAMP
—————————————————————————
09/03/2007 14:52:02,000000000

TIMESTAMP_TO_SCN : For getting SCN equivalent of the given timestamp value. You must do to_timestamp convertion for the character value.

SQL> select timestamp_to_scn(to_timestamp(’08/03/2007 14:24:54′,’DD/MM/YYYY HH24:MI:SS’)) as scn from dual;

SCN
———-
845396

References Used:

Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solution Mr Thomas Kytes Book Pg 244

Oracle® Database PL/SQL Packages and Types Reference
10g Release 2 (10.2)

March 7, 2007

How to grant on v$ views

Filed under: How To — coskan @ 3:07 pm

Today i was faced with an error when I try to give permission on a v$view

SQL> grant select on v$session to hr;
grant select on v$session to hr
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views

I wonder why i couldn’t give select privilage to a v$ view. I tried to give to permission to another v$ views but the error was same. Action for this error on error codes manual was meaningless to me (Action: You may only select rows from fixed tables/views.)

From a little googling effort i saw that the problem is caused because of trying to give select privilage on a synonym. Oracle v$ views are named V_$VIEWNAME and they have synonyms in format V$VIEWNAME and you can’t give privilage on a synonym.

If you want to give permission to a V$ view you must give it like below

SQL> grant select on v_$session to hr;

Grant succeeded.

March 4, 2007

Viewing explain plan

Filed under: How To — coskan @ 10:27 pm

There are lots of ways to view explain plan of the sql which is running or which you are going to run.

For the sqls which are already running or which were run;

1- Using DBMS_XPLAN package;

You can use DISPLAY_CURSOR procedure of DBMS_XPLAN package to view execution plans of running sqls. You need two parameters to use this package. First parameter is sql_id and second parameter is child number of the preceding statement. For getting the sql_id and child number you can use v$sql or v$session views.

Usage is;

DBMS_XPLAN.DISPLAY_CURSOR
(
sql_id IN VARCHAR2 DEFAULT NULL,
child_number IN NUMBER DEFAULT NULL,
format IN VARCHAR2 DEFAULT ‘TYPICAL’
);

code listing 4a

2- Using v$sql_plan view

You can query v$sql_plan view to get the execution plan of given sql_id or hash value.

code listing 4b

For the sqls which you are going to run;

1- Using DBMS_XPLAN package

You can use DISPLAY procedure of DBMS_XPLAN package. Firts you execute and explain plan command for the query you are executing. Then you issue a select command like below;

explain plan for
select …..

then select the result of the last EXPLAIN PLAN command stored in the plan table

select * from table (DBMS_XPLAN.DISPLAY)

parameters for the procedure ;

DBMS_XPLAN.DISPLAY(
table_name IN VARCHAR2 DEFAULT ‘PLAN_TABLE’,
statement_id IN VARCHAR2 DEFAULT NULL,
format IN VARCHAR2 DEFAULT ‘TYPICAL’)

code listing 4c

2- Using autotrace traceonly command;

You can use autotrace traceonly clause for getting the explain plan for the queries you run after executing this command. But the plan of the query will be shown after the execution of the query. Autotrace traceonly command will also give details about the i/o ,network and sort usage of the execution.

code listing 4d

2- Using autotrace traceonly explain clause;

If you want to see only the execution plan without executing the query you can execute the command autotrace traceonly explain.

code listing 4e

Note; On 10G Release 2 the autotrace output is the same with dbm_xplain package but prior to 10G R2 autotrace output was not displayed with cell environment.

Tests are done on 10.2.0.1 Express Edition

Please take a look at Mr Kytes Blog about when the real plan differ from guessed plan. This entry may save your life.

References ;

Oracle® Database PL/SQL Packages and Types Reference 10g Release 2 (10.2)

March 2, 2007

How to get session info ?

Filed under: How To — coskan @ 12:54 pm

To get the info of your session you can use the SYS_CONTEXT function with USERENV namespace.

usage of SYS_CONTEXT  is like ;

SYS_CONTEXT(‘namespace’, ‘parameter’ [, length ])

The context namespace must already have been created, and the associated parameter and its value must also have been set using the DBMS_SESSION.set_context procedure. The parameter is not case sensitive. (Refer to the manual for context creation)

There are examples from the usage of sys_context with userenv namespace

code listing 4

references: Oracle® Database SQL Quick Reference10g Release 1 (10.1)

How to find value of bind variables used by SQLs

Filed under: CBO, How To — coskan @ 10:02 am

There is one good useful feature in Oracle 10g to catch the value of bind variables without a trace.

This feature is the view V$SQL_BIND_CAPTURE. This view gives you information about the values binded to a specific sql_id with the capture time info. When we use this view with V$SESSION we can capture the values that are binded to the working sqls. This view is populated only when you set STATISTICS_LEVEL parameter to ALL/TYPICAL . Bind capturing is disabled when the STATISTICS_LEVEL initialization parameter is set to BASIC.

Here is an example of the usage;
col sid format a4
col username format a5
col sql_hash_value format 99999999
col sqlid format a14
col sql_child_number format 9
col name format a4
col value_string format a8
col last_captured format a9

select s.sid,
s.username,
–sq.sql_text,
s.sql_hash_value,
s.sql_id,
s.sql_child_number,
spc.name,
spc.value_string,
last_captured
from v$sql_bind_capture spc, v$session s,v$sql sq
where s.sql_hash_value = spc.hash_value
and s.sql_address = spc.address
and sq.sql_id=s.sql_id
and spc.was_captured=’YES’
and s.type<>’BACKGROUND’
and s.status=’ACTIVE';

SID USERN SQL_HASH_VALUE SQL_ID SQL_CHILD_NUMBER NAME VALUE_ST LAST_CAPT
———- —– ————– ————- —————- —- ——– ———
########## OKUR ######### 4s6c5w6zr0dub 0 :1 20070301 02-MAR-07
########## OKUR ######### 4s6c5w6zr0dub 0 :2 20070301 02-MAR-07
########## OKUR ######### 4s6c5w6zr0dub 0 :3 NULL 02-MAR-07
########## OKUR ######### 4s6c5w6zr0dub 0 :4 NULL 02-MAR-07
########## OKUR ######### 4s6c5w6zr0dub 0 :5 NULL 02-MAR-07
########## OKUR ######### 4s6c5w6zr0dub 0 :6 NULL 02-MAR-07
########## OKUR ######### 4s6c5w6zr0dub 0 :7 NULL 02-MAR-07

resources used:

Watching SQL Execute on Oracle – Part II James Koopmann

Oracle Documentation for 10g R1

March 1, 2007

How to invalidate execution plan

Filed under: How To — coskan @ 9:39 am

When you are using bind variables for you sqls sometimes you can be caught to bind variable peeking and your queries should start to work with an unwanted or wrong execution plan. To solve this problem you can execute a simple ddl statement over any table of the query. When you issue a simple “grant select table to user” command your plan will become invalidated. You have to be careful on high loaded system when you are invalidating a plan. Try to choose a table which will have minimal effect on other running and already parsed queries.

Code Listing 3

Update 20/04/2009
Better way explained by Harald van Breederode here

http://prutser.wordpress.com/2009/04/19/flushing-a-cursor-out-of-the-library-cache/

Update 12/12/2009
Automate via scripting by Kerry Osborne

http://kerryosborne.oracle-guy.com/2008/09/flush-a-single-sql-statement/

February 6, 2007

How to view list of hidden parameters

Filed under: How To — coskan @ 1:22 pm

Hidden parameters sometimes ease the life of  DBA but be carefull while using them Oracle usually does not support the usage of hidden parameters without their knowledge and you cant even know the side effects of the parameter.

Here is the query you can use to view the values of hidden parameter. (use it with sys user with sysdba privilages)

/* hidden parameters */

select a.ksppinm name,
b.ksppstvl value,
b.ksppstdf deflt,
decode
(a.ksppity, 1,
‘boolean’, 2,
‘string’, 3,
‘number’, 4,
‘file’, a.ksppity) type,
a.ksppdesc description
from
sys.x$ksppi a,
sys.x$ksppcv b
where
a.indx = b.indx
and
a.ksppinm like ‘\_%’ escape ‘\’
order by
name

Footnote 27/07/2009: I personally start to use Tanel Poder’s script to search both hidden and normal parameters

Older Posts »

Theme: Silver is the New Black. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 205 other followers