Kamran Agayev's Oracle Blog

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

I would like to let you know that my friend Porus Havewala’s book can be ordered now from the following link:

http://www.rampant-books.com/book_1001_advanced_techniques_oem_grid_control.htm

Some monthes ago I’ve taken an exclusive interview with him, you can read that interview from the following link:

http://kamranagayev.wordpress.com/2010/04/22/exclusive-interview-with-porus-homi-havewala/

For those who doesn’t know Porus Havewala, he is working as a Senior Manager (Database Management) in the Enterprise Technology team of Oracle Corporation based in Singapore, and specializes in Oracle Enterprise Manager.  He is an Oracle Employee ACE and was previously awarded the prestigious “Oracle ACE Director” title by Oracle USA. He has extensive experience in Oracle technology since 1994, including as a Senior Production DBA, Senior Database Consultant, Database Architect, E-Business Technical DBA, Development DBA, and Database Designer Modeler. He has also worked in Oracle India in the ACS (Advanced Customer Services) department and is an enthusiast for Oracle technology, especially Oracle Enterprise Manager Grid Control and RMAN, on which he has conducted seminars for large MNCs and implemented powerful enterprise tools.

When you implement the resource limit, the sessions that exceed the IDLE limit is marked as SNIPED in V$SESSION view and you may get “ORA-00020: maximum number of processes (%s) exceeded” error because Oracle doesn’t kill that session in OS level and it assumes it as a “process”. So for this, you need to kill those sessions manually

Here I show a little demonstration of the whole process: – First of all, set the RESOURCE_LIMIT parameter to TRUE to enforce the resource limit in database profiles

[sourcecode]
SQL> show parameter resource_limit
NAME TYPE VALUE
———————————— ———– ——————————
resource_limit boolean FALSE

SQL> alter system set resource_limit=true;
System altered.
[/sourcecode]

– Then create a profile and set IDLE_TIME  to 1 minute:

[sourcecode]SQL> create profile test_profile limit
2 idle_time 1;
Profile created.
[/sourcecode]

– Create a user and assign the profile to that user:

[sourcecode]
SQL> grant dba to usr identified by usr;
Grant succeeded.

SQL> alter user usr profile test_profile;
User altered.
[/sourcecode]

– Change the PROCESSES parameter to make the maximum number of operating system processes lower

[sourcecode]
SQL> show parameter process
NAME TYPE VALUE
processes integer 150

SQL> alter system set processes=25 scope=spfile;
System altered.

SQL> startup force

SQL> show parameter processes
NAME TYPE VALUE
processes integer 25

SQL> select count(1) from v$process;

COUNT(1)
———-
22
[/sourcecode]

Now open two different terminals and connect to the database with USR user:

[sourcecode]sqlplus usr/usr[/sourcecode]

Check the view V$PROCESS. It should be 24

[sourcecode]
SQL> select count(1) from v$process;

COUNT(1)
———-
24
[/sourcecode]

Now open third terminal and try to connect to the database with the user USR.You will get an error because the count of the processes will reach the limit:

[sourcecode]
[oracle@localhost ~]$ sqlplus usr/usr

ERROR:
ORA-00020: maximum number of processes (%s) exceeded

Enter user-name:

SQL>

[/sourcecode]

Now wait for a minute to reach the limit of the IDLE_LIMIT resource (we’ve set it to 1 minute) and query the SYSDATE from any USR session:

[sourcecode]
SQL> select s.status, count(1), s.username from v$process p, v$session s
where paddr(+)=addr
group by s.status, s.username
order by 1;

STATUS COUNT(1) USERNAME
——– ———- ——————————
ACTIVE 1 SYS
ACTIVE 16
INACTIVE 2 SYS
SNIPED 2 USR
3
[/sourcecode]

That’s the issue. If you try to connect to the database, you’ll get ORA-00020 error again. Please note that SNIPED doesn’t mean that it’s KILLED. It is not either killed, nor active. The user is not able to run any query, however it holds a process on OS level:

[sourcecode]
SQL> select count(1) from v$process;

COUNT(1)
———-
24

[/sourcecode]

Run any query with already connected (and SNIPED) USR user. You’ll get the following error:

[sourcecode]
SQL> select sysdate from dual;
select sysdate from dual
*
ERROR at line 1:
ORA-02396: exceeded maximum idle time, please connect again
SQL>
[/sourcecode]

Now query V$SESSION and V$PROCESS views again:

[sourcecode]
SQL> select s.status, count(1), s.username from v$process p, v$session s
where paddr(+)=addr
group by s.status, s.username
order by 1;

STATUS COUNT(1) USERNAME
——– ———- ——————————
ACTIVE 1 SYS
ACTIVE 16
INACTIVE 2 SYS
SNIPED 1 USR
4

SQL> select count(1) from v$process;

COUNT(1)
———-
24
[/sourcecode]

The process will be free only when you “exit” from Sql*Plus. Exit from the session that you got an error and query V$PROCESS again:

[sourcecode]
SQL> select count(1) from v$process;

COUNT(1)
———-
23
[/sourcecode]

To kill the SNIPED sessions you have two options. The first option is to run ALTER SYSTEM KILL SESSION command. For this you need to get SID and SERIAL# of the sniped session.

[sourcecode]
SQL> select sid, s.serial#, status from v$process p, v$session s
where paddr=addr
and s.username=’USR’;

SID SERIAL# STATUS
———- ———- ——–
9 10 SNIPED

SQL> alter system kill session ‘9,10’ immediate;
System altered.

SQL> select s.status, count(1), s.username from v$process p, v$session s
where paddr(+)=addr
group by s.status, s.username
order by 1;

STATUS COUNT(1) USERNAME
——– ———- ——————————
ACTIVE 1 SYS
ACTIVE 16
INACTIVE 2 SYS
KILLED 1 USR
3
[/sourcecode]

After some seconds you’ll see that the session is cleared from both views:

[sourcecode]
SQL> /

STATUS COUNT(1) USERNAME
——– ———- ——————————
ACTIVE 1 SYS
ACTIVE 16
INACTIVE 2 SYS
3
[/sourcecode]

However, due to some bugs, sometimes you may not get the sessions killed using ALTER SYSTEM KILL SESSSION command. For this, you need to kill the process from OS level.

[sourcecode]
SQL> select spid, status from v$process p, v$session s
where paddr=addr
and s.username=’USR’;

SPID STATUS
———— ——–
2795 SNIPED

[oracle@localhost ~]$ kill -9 2795

SQL> select spid, status from v$process p, v$session s
where paddr=addr
and s.username=’USR’;

no rows selected

SQL>
[/sourcecode]

Run any sql command on the killed session:

[sourcecode]
SQL> select sysdate from dual;
select sysdate from dual
*
ERROR at line 1:
ORA-03135: connection lost contact

SQL>
[/sourcecode]

(For more information on killing sniped sessions, refer to MOS 96170.1)

At last I’ve found time to create a Step by Step instruction on installing Oracle 10gR2 on Oracle Solaris 10. If you follow this instruction, you’ll successfully install Oracle on Solaris. If you get any error, do not hesitate to contact me

So, let’s begin. As a first step, download Oracle 10gR2 and Oracle Solaris , create a virtual machine using my previous instruction,  but don’t forget to select “Solaris 10” on “Operating System types”.

Next, unzip downloaded Solaris zip file, mount .iso file and start the virtual machine. You should get the following screen:

Press Enter

In the above screen type “1” and press Enter

Select appropriate keyboard layout and press F2

If you see the above screen, the click on the opened window and press Enter

Select the language and press Enter

Click Next

Selet the Network Connectivity and click Next

Provide the name of the host and click Next

Click Next

Click Next

Select your country and click Next

Provide correct date and time and click Next

Provide a password for the root user and click Next

Select No and click Next

Press Confirm to proceed the installation

Click Next

Click Next

Select CD/DVD and click Next

Select Accept and click Next

Select Default Installation and click Next

Click Install Now to start the installation

After installation completed, you get the following screen. Click on Continue and Next

Click on Continue and Reboot Now button

After reboot you get the following screen. Press Enter

Wow. Oracle Solaris welcome screen! Login with the root user and the password that you’ve provided during the installation and login to the system

Here’s the Oracle Solaris desktop

Now open new Terminal and check size of RAM, swap space and /tmp directory

Now check whether you’ve installed all packages that are required for Oracle installation

pkginfo -i SUNWarc SUNWbtool SUNWhea SUNWlibm SUNWlibms SUNWsprot SUNWtoo SUNWi1of SUNWi1cs SUNWi15cs SUNWxwfnt

As you see from the output, you’re missing two packages. So install it using pkgadd command and check the packages again:

Ok, now you need to create a group and a user:

groupadd oinstall

groupadd dba

useradd -d /export/home/oracle -g dba -G oinstall -m -s /bin/ksh oracle

passwd -r files oracle

Now change the profile of the “oracle” user and set environemnt variables:

gedit /export/home/oracle/.profile

export ORACLE_BASE=/export/home/oracle

export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1

export PATH=$ORACLE_HOME/bin:$PATH

export ORACLE_SID=solaris

export DISPLAY=:0.0

Next, change kernel parameters by editing /etc/system file as follows:

set shmsys:shminfo_shmmax=4294967295

set shmsys:shminfo_shmmin=1

set shmsys:shminfo_shmmni=100

set shmsys:shminfo_shmseg=10

set semsys:seminfo_semmns=1024

set semsys:seminfo_semmsl=256

set semsys:seminfo_semmni=100

set semsys:seminfo_semvmx=32767

 set noexec_user_stack=1

Restart the virtual machine, login with oracle user and add 1g of swap space (with root user)

Create a directory for Oracle installation :

mkdir –p /export/home/oracle/product/10.2.0/db_1

Then copy Oracle installation to /export/home/oracle folder, unzip it, switch to unzipped directory called database and start the installed with ./runInstaller command:

Uncheck “Create Starter Database” option and click Next

Click Next

The installer checks all pre-requsit configurations and should Succeed. If so, click Next

Click Install button to start the installation

Please don’t press “Stop Installation” button

When the installation completes, you’ll be asked to run the following shell scripts with root user.

Login with root user from the new terminal and run them

The installation completed! Congratulations!

Now let’s create a database. Open new terminal and run dbca (Database Configuration Assistant) command to create a database

Click Next

Select “Create a database” and click Next (Don’t select “Delete a Database”, ups.. you can’t even select it  )

Select “General Purpose” and click Next

Provide a database name and click Next

Click Next

Provide a password for SYS user and click Next

Click Next

Click Next

Click Next

If you want to install sample schemas, then select this option and click Next

Click Next

Click Next

Click Finish and start the database creation

The database creation process has started

You got the message that the database has successfully created. Let’s check it!

Wooou!!!! Congratulations again!! At the end, you’ve successfully installed Oracle Solaris, Oracle Database on Solaris and created a database! That’s great!

Don’t forget to post a feedback!

In this video tutorial I demonstrate the new feature of RMAN that comes with Oracle 11g – Virtual Private Catalog. Enjoy it

To download the .mp4 format of this file, use the following link:

http://www.kamranagayev.com/Video_Tutorials/Virtual_Private_Catalog.mp4

Here’s the direct vimeo link of the tutorial:

http://vimeo.com/15962290

In this video tutorial I demonstrate the scenario where I upgrade the recovery catalog with UPGRADE CATALOG command from Oracle 10g to 11g and move it to the second recovery catalog using new command that is introduced in Oracle 11g – IMPORT CATALOG. Enjoy it

To download the .mp4 format of this file, use the following link:

http://www.kamranagayev.com/Video_Tutorials/Import_Catalog.mp4

Here’s the direct vimeo link of the tutorial:

http://www.vimeo.com/15924303

I’ve decided to present at OOW-2010 Unconference, so you all are invited I’ll present on “RMAN 11g New Features with practical demonstrations”

My session will be at 9.00AM at Hotel Parc 55. in Lombard room. Here’s the details:

http://wikis.sun.com/display/JavaOne/Unconferences+at+JavaOne+and+Oracle+Develop+2010

  Alex Nuijten started of as an Oracle Developer mainly with Oracle Forms, Reports and Designer but fell in love with SQL and PL/SQL. Next to his consultancy work he also writes articles for an Oracle related magazine, blogs on the AMIS Technology Blog and his own blog, conducts training classes and is a regular presentor at National and International conferences. Alex is a frequent contributor at the Oracle Technology Network forum for SQL and PL/SQL.

Could u please provide answer to the following questions as follows:

• Brief information about yourself and your family

I’m a database developer and expertise lead database development with AMIS in The Netherlands.
Married with Rian, two children – Tim 9 and Lara 6.

  

• Your education

I am a Physical Therapist.

 

• Your experience with Oracle. When you started first? Has it been interest of your side or just a coincidence?

Oracle was coincidence. I worked (as a Physical Therapist) and lived in the Florida, US, for about 5 years and when we moved back to The Netherlands I decided to change careers. The company that hired me offered to train me in Oracle, and that’s what I’ve been doing ever since.

• What was the motive behind to prefer Oracle? Who you have been influenced by?

Oracle is what I have done ever since I changed careers. There are so many people that have influenced me within the Oracle area.

 

• What would your preference of profession if not Oracle?

Haven’t really considered changing careers again, I still love what I do.

• What motivates you in your job?

The technology. Trying out new features. Learning new things.

 

• Do you give lectures on Oracle?

Yes, conferences at which I am presenting are usually up on my blog ( http://nuijten.blogspot.com ).
Besides the conferences I also do training classes for AMIS and Oracle University.

  

• Have you authored any book in Oracle?

No, I haven’t. However I am a regular writer for Optimize, a Dutch magazine for Oracle professionals.
I have been a technical reviewer of Steven Feuerstein’s Oracle PL/SQL Programming (5^th edition) book though. Funny to mention that in the acknowledgement section, he misspelled my last name…  

  

• Do you manage with your time as to read books on Oracle? What is the name of the book that you read recently?

 Before I go to sleep I always read. The last book I reread was “Pro Oracle Application Express” by John Scott and Scott Spendolini.

  

• What do you think on OTN forums?

I think the OTN forums is a great resource. A lot of knowledgeable people there, and a lot of fun.  

• Do you refer to the documentation? And how often does it happen?

Very often. Tahiti.oracle.com is one of my bookmarked websites. Before answering questions on OTN, I tend to look it up in the documentation first.

 

• What is the biggest mistake you have ever made during your DBA career?

I’m not a DBA 😉
But as a developer I deleted a table on a production database, right after the “COMMIT” the phone started ringing… “What happened to this data?” Flashback is a great feature J

 

• What was your greatest achievement as an Oracle DBA?

I’m still not a DBA… J I guess my greatest achievement is that I still enjoy my work a lot. I always go to my work with a big smile on my face.

• What is your priority to manage the challenges you face?

I tend to take one step at a time and take things as they come along.

 

• How would you describe the essence of your success? According to your definition of success, how successful have you been so far?

“the essence of your success”…. Wow… I don’t know, I just do what I love to do and apparently that is appreciated by some.

 

• What are your best skills which make you differ from others?

I guess I always want to try out new things, be involved.

• What’s your major weakness?

Procrastination. You can procrastinate almost anything, except procrastination.

• Have you ever lost your spirit? If so, what has been the reason and how have you overcome it?

If you ever lose the spirit, it’s time to find something else to do… something that you do enjoy and get energy from that.

 

• What is the next success you would like to attain and your efforts to this end?

 I am still very happy with being awarded Ace Director.

 

• How do you balance your daily life with your career?

Things seem to work out. I don’t doing anything special to balance things out.

 

• Please describe your one day summary of activities?

At the moment I’m busy preparing my presentation for Oracle Open World, which I will do with my colleague Lucas Jellema.
Most of the time I am consulting at a client side just like everybody else and sometimes I might be teaching, visiting a conference and check out the OTN forums.

 

• How many hours do you work and sleep in a day?

About 7 hours of sleep.

• Where and how do you spend your daily, weekly and annual holidays?

I enjoy going camping. Preferably if the weather is nice, as long as it’s not raining it’s alright.

• Do you think about Oracle during vacations?

Yes, I always take an Oracle book with me as well as my laptop.

  

• Do you have time or motivation to go in for any sports? If yes, which kind of sport do you go in for?

I try to go for a run once or twice a week. When the weather is nice I enjoy inline skating.

• What’s your favorite meal and non-alcoholic drink?

There are so many delicious things. I love a good barbecue, smoked ribs (most restaurants boil them,… yuck). Sushi is also something you can wake me up for. It depends on the meal what I will have to drink. Non-alcoholic: water.

• What foreign languages do you know?

English, little bit of German, very little French, little Spanish. My mother tongue is Dutch.

• What’s your average typing speed?

Don’t know. Fast enough I guess.

• Have you ever get involved in politics?

No.

• What are your hobbies? 

Reading, watching TV, playing with Oracle

 

• How do you spend your free time?

Reading, watching TV, playing with Oracle, going to the soccer matches with my son’s team. Going to the Track and Field games with the kids.

 

• What’s your biggest ambition?

Never really think about that sort of thing.

• What would be your advice to the beginners in Oracle?

Always check what people are saying to you. Things change over time. Learn how to use the Oracle documentation.

• Would you like your children to follow in your footsteps or take a different path in life?

My son wants to be either a Professional Soccer player or a Track and Field Athlete. My daughter wants to be a teacher.

• Do you have any followers of you?

According to my blog (http://nuijten.blogspot.com) I have 9 followers…

When someone leaves a comment, good or bad, I always enjoy it.

• What is your vision on the future of Oracle?

Oracle will keep expanding and will stay the best Database in the world.

  

• Could you please take a photo in your office near to your desktop?

 

September has come, and it’s the month of Oracle OpenWorld conference. This year is the first time I’ll be in this conference, and I’ll try to attend it each year for sure

I’ll update my blog (and fb) with OOW news and interesting photos (and videos)

In this conference I’ll attend 23 difference sessions that will be presented by top Oracle world-known experts like Tom Kyte, Richard Foote, Alex Gorbachev, Daniel Morgan and etc. Being an Oracle ACE and attending an Oracle OpenWorld conference is a special case   The following is my agenda

September 18 – Landing to San Francisco

September 19 – OOW begins. Attending 5 sessions

        Oracle ACE Dinner on a cruise with more than 125 ACE and ACEDs! Woooow …
 

September 20 –  Presenting at Unconference on “RMAN 11g new features” at Hotel Parc in the Lombard room at 9.00AM 
    
  Attending 6 sessions

  Attending an OTN Night at Yerba Buena’s Garden and meet OTN stuff there

 

September 21 –  Attending 5 sessions

September 22 –  Blogger Meetup with world-known Oracle Bloggers at Lower Dining Room 

  Attending (Unconference) session “OTN Forum Members get together” which is organized by Dan Morgan and Jusing Kestelyn

  Attending 7 sessions

September 23 – Atetndig 5 sessions

That’s my plan for now. Can’t imagine that I’ll meet all my online friends-gurus after long years and talk to them face to face 

See you in the Oracle OpenWorld 2010!

In this video tutorial I show the cloning of the database using RMAN without connecting to the target database and recovery catalog. Enjoy it

 

 

To download the .mp4 format of this file, use the following link:

http://www.kamranagayev.com/Video_Tutorials/RMAN_11gR2_duplicate_feature.mp4