What are the Oracle backgroud processes

To find out the information on the Oracle backgroud processes there are a couple of ways.

One would be to look use the OS command to find information on the Oracle process/database instance.
"testasm" is the instance name in this case.

isvp14_ora> ps -ef | grep _testasm   oracle  9568370        1   0 15:05:04      -  0:02 ora_smon_testasm   oracle 19071098        1   0 15:05:04      -  0:01 oracle+ASM_asmb_testasm (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))   oracle 19333214        1   0 15:05:03      -  0:08 ora_ckpt_testasm   oracle 19529820        1   0 15:05:03      -  0:15 ora_lgwr_testasm   oracle 19595362        1   0 15:05:03      -  0:40 ora_dbw0_testasm   oracle 19660900        1   0 15:05:03      -  0:01 ora_mman_testasm   oracle 19726434        1   0 15:05:03      -  0:33 ora_dia0_testasm   oracle 19857512        1   0 15:05:03      -  0:00 ora_psp0_testasm   oracle 19923050        1   0 15:05:03      -  0:00 ora_gen0_testasm   oracle 20316278        1   0 15:05:04      -  0:00 ora_s000_testasm   oracle 20381826        1   0 15:05:03      -  0:22 ora_vktm_testasm   oracle 20578446        1   0 15:05:03      -  0:04 ora_pmon_testasm   oracle 20775082        1   0 15:05:04      -  0:00 ora_d000_testasm   oracle 25296902        1   0 15:05:14      -  0:06 ora_cjq0_testasm   oracle 25559076        1   0 15:05:23      -  0:00 ora_q001_testasm   oracle 25821214        1   0 15:05:13      -  0:00 ora_qmnc_testasm   oracle 25886744        1   0 15:05:04      -  0:00 ora_mark_testasm   oracle 25952282        1   0 15:05:04      -  0:06 ora_mmon_testasm   oracle 26017820        1   0 15:05:04      -  0:00 ora_asmb_testasm   oracle 26083358        1   0 15:05:04      -  0:00 ora_rbal_testasm   oracle 26214454        1   0 15:05:04      -  0:14 ora_mmnl_testasm   oracle 26280002        1   0 15:05:03      -  0:00 ora_diag_testasm   oracle 26345516        1   0 15:10:14      -  0:00 ora_smco_testasm   oracle 26411078        1   0 15:05:03      -  0:00 ora_dbrm_testasm   oracle 26476590        1   0 15:05:04      -  0:00 ora_reco_testasm   oracle 35258456        1   0 09:31:30      -  0:00 ora_w000_testasm   oracle 41156862        1   0 15:05:23      -  0:00 ora_q000_testasm isvp14_ora>

or we could use the information in the v$process table for more accurate information.

SQL> select program from v$process where BACKGROUND=1; PROGRAM ------------------------------------------------ oracle@isvp14_ora.storage.tucson.com (PMON) oracle@isvp14_ora.storage.tucson.com (VKTM) oracle@isvp14_ora.storage.tucson.com (GEN0) oracle@isvp14_ora.storage.tucson.com (DIAG) oracle@isvp14_ora.storage.tucson.com (DBRM) oracle@isvp14_ora.storage.tucson.com (PSP0) oracle@isvp14_ora.storage.tucson.com (DIA0) oracle@isvp14_ora.storage.tucson.com (MMAN) oracle@isvp14_ora.storage.tucson.com (DBW0) oracle@isvp14_ora.storage.tucson.com (LGWR) oracle@isvp14_ora.storage.tucson.com (CKPT) PROGRAM ------------------------------------------------ oracle@isvp14_ora.storage.tucson.com (SMON) oracle@isvp14_ora.storage.tucson.com (RECO) oracle@isvp14_ora.storage.tucson.com (RBAL) oracle@isvp14_ora.storage.tucson.com (ASMB) oracle@isvp14_ora.storage.tucson.com (MMON) oracle@isvp14_ora.storage.tucson.com (MMNL) oracle@isvp14_ora.storage.tucson.com (MARK) oracle@isvp14_ora.storage.tucson.com (W000) oracle@isvp14_ora.storage.tucson.com (Q000) oracle@isvp14_ora.storage.tucson.com (QMNC) oracle@isvp14_ora.storage.tucson.com (SMCO) PROGRAM ------------------------------------------------ oracle@isvp14_ora.storage.tucson.com (CJQ0) oracle@isvp14_ora.storage.tucson.com (Q001) 24 rows selected.

SQL>

ORA-00018: maximum number of sessions exceeded

Again, while running my charbench client against the swingbench Order Entry schema I got the following error message:

ORA-00018: maximum number of sessions exceeded

On googling I found an excellent thread which explains how connections, sessions, and processes are related in Oracle.
http://asktom.oracle.com/pls/asktom/f?p=100:11:413703333046318::::P11_QUESTION_ID:5671284058977

Here is what I did to find out my current value of session, and then increase its value.

SQL> show parameter sessions; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ java_max_sessionspace_size integer 0 java_soft_sessionspace_limit integer 0 license_max_sessions integer 0 license_sessions_warning integer 0 sessions integer 400 shared_server_sessions integer SQL> alter system set sessions=800 scope=spfile; System altered. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup; ORACLE instance started. Total System Global Area 3540881408 bytes Fixed Size 2211864 bytes Variable Size 1644171240 bytes Database Buffers 1879048192 bytes Redo Buffers 15450112 bytes Database mounted. Database opened. SQL> show parameter sessions; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ java_max_sessionspace_size integer 0 java_soft_sessionspace_limit integer 0 license_max_sessions integer 0 license_sessions_warning integer 0 sessions integer 800 shared_server_sessions integer SQL>

ORA-12516, TNS:listener could not find available handler with matching protocol stack

My charbench client was running fine with 100 users against an Oracle 11g R2 database created using swingbench, but when I tried to run the client with 200 users I got the following error message:

Caused by: oracle.net.ns.NetException: Listener refused the connection with the following error: ORA-12516, TNS:listener could not find available handler with matching protocol stack

On checking to see the vaule of the process for the database I noticed that it was set to 150.

SQL> show parameter processes; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ aq_tm_processes integer 0 db_writer_processes integer 1 gcs_server_processes integer 0 global_txn_processes integer 1 job_queue_processes integer 1000 log_archive_max_processes integer 4 processes integer 150 SQL> show parameter spfile; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string +DATA/testasm/spfiletestasm.or a SQL> alter system set PROCESSES=250 scope=spfile; System altered. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup; ORACLE instance started. Total System Global Area 3540881408 bytes Fixed Size 2211864 bytes Variable Size 1644171240 bytes Database Buffers 1879048192 bytes Redo Buffers 15450112 bytes Database mounted. Database opened. SQL> show parameter processes; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ aq_tm_processes integer 0 db_writer_processes integer 1 gcs_server_processes integer 0 global_txn_processes integer 1 job_queue_processes integer 1000 log_archive_max_processes integer 4 processes integer 250 SQL>

The charbench client proceeded file with 200 users after the above change to the PROCESSES parameter.

ORA-09817: Write to audit file failed.

While trying to connect to our 11gR2 Oracle database this morning I got the
ORA-09817: Write to audit file failed. error message.

bash-3.2$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Thu Jul 14 10:29:19 2011 Copyright (c) 1982, 2009, Oracle. All rights reserved. ERROR: ORA-09817: Write to audit file failed. IBM AIX RISC System/6000 Error: 28: No space left on device Additional information: 12 ORA-09945: Unable to initialize the audit trail file IBM AIX RISC System/6000 Error: 28: No space left on device Enter user-name:

On doing a "df -m" at I noticed that the / had run out of space. It's on / that I had our ORACLE_HOME, so I seemed that that might have caused the problem.

$ df -m Filesystem MB blocks Free %Used Iused %Iused Mounted on /dev/hd4 25600.00 0.00 100% 70464 95% /

I increased the size of / from 25G to 50G by doing the following:

isvp14_ora> chfs -a size=50G / Filesystem size changed to 104857600 isvp14_ora> df -m Filesystem MB blocks Free %Used Iused %Iused Mounted on /dev/hd4 51200.00 25596.05 51% 70465 2% /

Now, I tried to connect to the database again, and everything worked fine.

bash-3.2$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Thu Jul 14 10:34:32 2011 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL>

Adding a datafile to a tablespace on ASM

I  had created a database using the ASM diskgroup +DATA for the Oracle datafiles. Next, I wanted to add another datafile to the USERS tablespace.

The commands below show how I went about it.

SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- +DATA/testasm/datafile/system.256.755000669 +DATA/testasm/datafile/sysaux.257.755000671 +DATA/testasm/datafile/undotbs1.258.755000671 +DATA/testasm/datafile/users.259.755000671 SQL> alter tablespace users add datafile '+DATA/testasm/datafile/users.260.755000671' size 4M; alter tablespace users add datafile '+DATA/testasm/datafile/users.260.755000671' size 4M * ERROR at line 1: ORA-01276: Cannot add file +DATA/testasm/datafile/users.260.755000671.  File has an Oracle Managed Files file name. SQL> alter tablespace users add datafile '+DATA' size 100M; Tablespace altered. SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- +DATA/testasm/datafile/system.256.755000669 +DATA/testasm/datafile/sysaux.257.755000671 +DATA/testasm/datafile/undotbs1.258.755000671 +DATA/testasm/datafile/users.259.755000671 +DATA/testasm/datafile/users.266.755002021 SQL>

Enterprise Manager: Hostname should not contain underscore _ in it.

While using dbca to create a database with Enterprise manager configured. The configuration of the Enterprise Manager failed with the following error message:

"Invalid Hostname isvp14_ora. Hostname should not contain an underscore _ in it."

 Well, RFC 952 and RFC 1123 explain the rules for choosing a hostname.

Anyroad, here is the workaround:
On the shell prompt set the ORACLE_HOSTNAME variable to your machines IP address, and then re-configure.

bash-3.2$ export ORACLE_HOSTNAME=<ip_address>

I/O stat for Veritas VxVM volumes

I had created a Veritas disk group(dg3) which was made up of 3 volumes (SSD, SAS, and Nearline SAS drives).

I was running the Oracle TPC-C workload, and wanted to measure the iostat information for the diskgroup. Running iostat -x was not particularly useful as it gave me an output as follows:

[root@isvx7 smarttier]# iostat -x 3 Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util .................. .................... .................... VxVM5000 0.00 0.00 0.00 0.00 0.00 0.00 5.77 0.00 0.35 0.35 0.00 VxVM23000 0.00 0.00 0.20 1830.51 3.13 14261.30 7.79 0.36 0.20 0.14 26.46 VxVM23001 0.00 0.00 192.38 1488.29 5490.32 34298.45 23.67 0.45 0.27 0.15 25.38 VxVM23002 0.00 0.00 1.91 117.70 67.23 7716.99 65.08 0.07 0.56 0.24 2.90 VxVM6000 0.00 0.00 9.70 845.52 9146.15 11965.72 24.69 0.18 0.21 0.17 14.28 ..................... ..................... .....................

I then found that using vmstat is much more useful, and here is what I got when I ran the vmstat command:

[root@isvx7 report]# vxstat -i 3 -g dg3 OPERATIONS BLOCKS AVG TIME(ms) TYP NAME READ WRITE READ WRITE READ WRITE Tue 14 Jun 2011 11:19:19 AM MST vol vol_nlsas 0 0 0 0 0.00 0.00 vol vol_sas 4 888 2056 108967 0.75 0.71 vol vol_ssd 49 29066 784 308527 1.55 0.17 Tue 14 Jun 2011 11:19:22 AM MST vol vol_nlsas 0 0 0 0 0.00 0.00 vol vol_sas 0 2 0 64 0.00 0.00 vol vol_ssd 107 28129 1712 286129 0.50 0.17 Tue 14 Jun 2011 11:19:25 AM MST vol vol_nlsas 0 0 0 0 0.00 0.00 vol vol_sas 4 881 2056 108455 1.25 0.66 vol vol_ssd 169 26647 2704 291859 0.50 0.18