In this video tutorial I show step by step guide on installing Oracle 10gR2 Grid Control on Linux and deploying agents of the databases that are running on Linux and Windows using three different virtual machines

For the best video and sound quality I’d suggest you to download the .mp4 format of this video

To download “Oracle Enterprise Manager 10g Grid Control Release 2 (10.2.0.1.1) for Linux x86” use the following link:
http://www.oracle.com/technetwork/oem/grid-control/downloads/linuxsoft-099441.html
To download the “Agent Software for Linux and Windows”, go to the following link and choose : Linux x86 (Microsoft Windows x86)->10.2.0.x->Download
http://www.oracle.com/technetwork/oem/grid-control/downloads/agentsoft-090381.html

These days I’m working on implementing a Sanitization (or Data Masking) on some critical tables which have million of rows. Instead of UPDATE command, I preffered using CTAS (Create table as ..) and forgot that CTAS doesn’t take default values, so added it as a seperate command in the sanitization script after getting call from application developers   Here’s a little demo:

[sourcecode]SQL> conn usr/usr
Connected.
SQL> create table t1 (id number default (0) not null);

Table created.

SQL> set long 10000

SQL> select dbms_metadata.get_ddl(‘TABLE’,’T1′,’USR’) from dual;

DBMS_METADATA.GET_DDL(‘TABLE’,’T1′,’USR’)
——————————————————————————–

  CREATE TABLE "USR"."T1"
   (    "ID" NUMBER DEFAULT (0) NOT NULL ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
SQL> create table t2 as select * from t1;

Table created.

SQL> select dbms_metadata.get_ddl(‘TABLE’,’T2′,’USR’) from dual;

DBMS_METADATA.GET_DDL(‘TABLE’,’T2′,’USR’)
——————————————————————————–

  CREATE TABLE "USR"."T2"
   (    "ID" NUMBER NOT NULL ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
SQL>[/sourcecode]

As you see, in the first result I got DEFAULT (0) when using GET_DDL command. However if table is created using CTAS, it doesn’t contain default value as it’s seen in the last query. You need to use ALTER TABLE and set default value for that column as follows:

[sourcecode]SQL> alter table t2 modify (id number default (0));

Table altered.

SQL> select dbms_metadata.get_ddl(‘TABLE’,’T2′,’USR’) from dual;

DBMS_METADATA.GET_DDL(‘TABLE’,’T2′,’USR’)
——————————————————————————–

  CREATE TABLE "USR"."T2"
   (    "ID" NUMBER DEFAULT (0) NOT NULL ENABLE

SQL>[/sourcecode]

Today I’ve got a call from my friend which claimed the performance degredation on one of the production databases. When connecting to SQL*Plus or RMAN, I realized a delay, so run “top” command and checked the running processes on the system. When running ps – ef command, I saw hundreds of perl executables that are currently running on the system:

[sourcecode]oracle   15560     1  3 Jan11 ?        05:50:07 /opt/oracle/product/10.2/db_1/perl/bin/perl /opt/oracle/product/10.2/db_1/sysman/admin/scripts/db/dbresp.pl
oracle   16309     1  3 Jan11 ?        05:44:53 /opt/oracle/product/10.2/db_1/perl/bin/perl /opt/oracle/product/10.2/db_1/sysman/admin/scripts/db/dbresp.pl
…..
…..[/sourcecode]

As the dbresp.pl file locates under sysman folder, I’ve decided that it has some relation with EM, so I checked the EM trace file:

[sourcecode]tail -50 emagent.trc | more

2011-01-11 08:51:37 Thread-4096777120 ERROR fetchlets.oslinetok: Metric execution timed out in 600 seconds
2011-01-11 08:51:37 Thread-4096777120 ERROR command: failed to kill process 24963 running perl: (errno=3: No such process)
2011-01-11 08:51:37 Thread-4096777120 ERROR engine: [oracle_database,prod_db,Response] : nmeegd_GetMetricData failed : Metric execution timed out in 600 seconds
2011-01-11 09:06:37 Thread-4113513376 ERROR fetchlets.oslinetok: Metric execution timed out in 600 seconds
2011-01-11 09:06:37 Thread-4113513376 ERROR command: failed to kill process 25393 running perl: (errno=3: No such process)
2011-01-11 09:06:37 Thread-4113513376 ERROR engine: [oracle_database,prod_db,Response] : nmeegd_GetMetricData failed : Metric execution timed out in 600 seconds
2011-01-11 09:21:37 Thread-4096777120 ERROR fetchlets.oslinetok: Metric execution timed out in 600 seconds
2011-01-11 09:21:37 Thread-4096777120 ERROR command: failed to kill process 26068 running perl: (errno=3: No such process)
2011-01-11 09:21:37 Thread-4096777120 ERROR engine: [oracle_database,prod_db,Response] : nmeegd_GetMetricData failed : Metric execution timed out in 600 seconds
2011-01-11 09:36:37 Thread-4099926944 ERROR fetchlets.oslinetok: Metric execution timed out in 600 seconds[/sourcecode]

Wouu… Interesting output. I’ve decided to check metalink and found the following note: Server Has 100% Of Cpu Because Of Dbresp.pl [ID 764140.1]

Unfortunately as a solution the note adviced me to refer to the metalink note: “ Ext/Mod Problem Performance Agent High CPU Consumption Gen” where it’s written to change the alert.log file name to solve the issue. It wasn’t a real solution, so I’ve decided to take down the EM and kill all processes

[sourcecode]emctl stop dbconsole[/sourcecode]

Then I called the following command and got the list of all dbresp.pl processes and got the script which kills them all

[sourcecode]ps -ef | grep dbresp.pl | awk {‘print "kill -9 " $2’} > kill.sh

more kill.sh
kill -9 23989
kill -9 24569
kill -9 25145
kill -9 25723
…..
…..[/sourcecode]

Next, I made it executable and run :

[sourcecode]oracle@host</a>:~> chmod 755 kill.sh
oracle@host:~> ./kill.sh
oracle@host:~>
oracle@host:~> ps -ef | grep dbresp
oracle   32454 29520  0 10:48 pts/0    00:00:00 grep dbresp [/sourcecode]

After killing all unnecessary processes, CPU usage went down.

To deal with this bug, you can check the count of dbresp.pl files,  take down the EM, kill all processes and start it again using any cron job

If you have another solution, please let me know