Showing posts with label ASM. Show all posts
Showing posts with label ASM. Show all posts

Wednesday, 26 June 2013

Move SPFILE from one ASM diskgroup to another (ORACLE RAC) 

##
## "Move" spfile from:
##    +OLDLOC/DBNAME/spfileDBNAME.ora
##  to:
##    +NEWLOC/DBNAME/spfileDBNAME.ora
##

## NOTE:
##   in this example there are FOUR nodes:
##      rac1
##      rac2
##      rac3
##      rac4


## run all steps from first node
##
## "copy" spfile to +NEWLOC
sqlplus /nolog
connect / as sysdba
create pfile='/u01/app/oracle/admin/DBNAME/scripts/init_new.ora' from spfile='+OLDLOC/DBNAME/spfileDBNAME.ora';
create spfile='+NEWLOC/DBNAME/spfileDBNAME.ora' from pfile='/u01/app/oracle/admin/DBNAME/scripts/init_new.ora';
exit

## modify init*.ora files to point to new location
##
echo "SPFILE='+NEWLOC/DBNAME/spfileDBNAME.ora'" > /u01/app/oracle/product/10.2.0/db_1/dbs/initDBNAME1.ora
ssh rac2 "echo \"SPFILE='+NEWLOC/DBNAME/spfileDBNAME.ora'\" > /u01/app/oracle/product/10.2.0/db_1/dbs/initDBNAME2.ora"
ssh rac3 "echo \"SPFILE='+NEWLOC/DBNAME/spfileDBNAME.ora'\" > /u01/app/oracle/product/10.2.0/db_1/dbs/initDBNAME3.ora"
ssh rac4 "echo \"SPFILE='+NEWLOC/DBNAME/spfileDBNAME.ora'\" > /u01/app/oracle/product/10.2.0/db_1/dbs/initDBNAME4.ora"

## modify OCR with new SPFILE location
##
export ORACLE_HOME=$CRS_HOME
export PATH=$ORACLE_HOME/bin:$PATH
srvctl modify database -d DBNAME -p +NEWLOC/DBNAME/spfileDBNAME.ora

## bounce ALL instances to switch to new SPFILE
##
export ORACLE_HOME=$CRS_HOME
export PATH=$ORACLE_HOME/bin:$PATH
srvctl stop database -d DBNAME
srvctl start database -d DBNAME

## all services will be down because DB got bounced -- restart it
##
srvctl start service -d DBNAME



## drop old spfile
##
export ORACLE_HOME=/u01/app/oracle/product/10.2.0/asm
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=+ASM1
sqlplus /nolog
connect / as sysdba
ALTER DISKGROUP OLDLOC DROP FILE '+OLDLOC/DBNAME/spfileDBNAME.ora';
exit

## verify "old" spfile is gone from OLDLOC
##
export ORACLE_HOME=/u01/app/oracle/product/10.2.0/asm
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=+ASM1
asmcmd
ASMCMD> find / *spfile*
+NEWLOC/DBNAME/PARAMETERFILE/spfile.259.667587125
+NEWLOC/DBNAME/spfileDBNAME.ora
Posted by hemora at 17:32 0 comments
Labels: , ,

Move/Copy controlfile from file system to ASM

Current Controlfie location (using Init.ora file here)

[oracle@demo:TESTDB-/cluster/app/grid/product/11.2.0/db_1/dbs]$ cat initTEST11G.ora
*.control_files='/cluster/app/grid/product/11.2.0/db_1/dbs/control01.ctl'

Change to new location in ASM with random controlfile number, that will changed later by RMAN

[oracle@demo:TESTDB-/cluster/app/grid/product/11.2.0/db_1/dbs]$ vi initTEST11G.ora
*.control_files='+DATA/TESTDB/controlfile/current.261.772293749','+DATA/TESTDB/controlfile/current.260.772293749'

Startup database with nomount

[oracle@demo:TESTDB-/cluster/app/grid/product/11.2.0/db_1/dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Wed Jun 26 16:55:43 2013
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Connected to an idle instance.
SYS@TESTDB >> startup nomount;
ORACLE instance started.

Total System Global Area 3340451840 bytes
Fixed Size                  2231088 bytes
Variable Size            2499806416 bytes
Database Buffers          822083584 bytes
Redo Buffers               16330752 bytes
SYS@TESTDB >> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Connect Target db with RMAN and restore controlfile from original location

[oracle@demo:TESTDB-/cluster/app/grid/product/11.2.0/db_1/dbs]$ rman target /
Recovery Manager: Release 11.2.0.2.0 - Production on Wed Jun 26 16:56:24 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database: TESTDB (not mounted)

RMAN> restore controlfile from '/cluster/app/grid/product/11.2.0/db_1/dbs/control01.ctl';

Starting restore at 26-JUN-2013 16:56:34
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=132 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=+DATA/TESTDB/controlfile/current.261.819132997
output file name=+DATA/TESTDB/controlfile/current.260.819132997
Finished restore at 26-JUN-2013 16:56:39

RMAN> exit


Recovery Manager complete.

Confirm location of new controlfile

[oracle@demo:TESTDB-/cluster/app/grid/product/11.2.0/db_1/dbs]$ s
SQL*Plus: Release 11.2.0.2.0 Production on Wed Jun 26 16:57:04 2013
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SYS@TESTDB >> alter database mount;

Database altered.

SYS@TESTDB >> show parameter controlfile
SYS@TESTDB >> show parameter control

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      +DATA/TESTDB/controlfile/cur
                                                 rent.261.819132997, +DATA/test
                                                 sits/controlfile/current.260.8
                                                 19132997
control_management_pack_access       string      DIAGNOSTIC+TUNING

SYS@TESTDB >> alter database open;
Database altered.

SYS@TESTDB >> exit 

Modified Init.ora files with new controlfile location that created by RMAN , and restart the database.

[oracle@demo:TESTDB-/cluster/app/grid/product/11.2.0/db_1/dbs]$ vi initTEST11G.ora
*.control_files='+DATA/TESTDB/controlfile/current.261.819132997','+DATA/TESTDB/controlfile/current.260.819132997'

SYS@TESTDB >> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SYS@TESTDB >> startup
ORACLE instance started.
Total System Global Area 3340451840 bytes
Fixed Size                  2231088 bytes
Variable Size            2499806416 bytes
Database Buffers          822083584 bytes
Redo Buffers               16330752 bytes
Database mounted.
Database opened.


Posted by hemora at 17:20 0 comments
Labels: , ,

Error connecting ASM ORA-15055: unable to connect to ASM instance Fatal NI connect error 12547 ORA-12547: TNS:lost contact

Error connecting ASM ORA-15055: unable to connect to ASM instance Fatal NI connect error 12547 ORA-12547: TNS:lost contact


This Blog is intended to resolve below errors observed in DB alert log file due connectivity issue between DB Instance & ASM disk/Instance


Errors:

Fatal NI connect error 12547, connecting to:
 (DESCRIPTION=(ADDRESS=(PROTOCOL=beq)(PROGRAM=/u01/app/grid/product/11.2.0/grid/bin/oracle)(ARGV0=oracle+ASM_asmb_remdev)(ENVS='ORACLE_HOME=/u01/app/grid/product/11.2.0/grid,ORACLE_SID=+ASM')(ARGS='(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))'))(enable=setuser)(CONNECT_DATA=(CID=(PROGRAM=oracle)(HOST=remedy-ebu-dev-db1)(USER=ora11g))))
TNS-12547: TNS:lost contact
    ns secondary err code: 12560
    nt main err code: 517
ORA-15055: unable to connect to ASM instance
ORA-12547: TNS:lost contact
TNS-12545: Connect failed because target host or object does not exist
ERROR: Failed to connect with connect string: (DESCRIPTION=(ADDRESS=(PROTOCOL=beq)(PROGRAM=stance
ORA-12547: TNS:lost contact
,ORACLE_SID=055: unable to connect to ASM instance


Cause:

$GRID_HOME/bin/oracle or $ORACLE_HOME/bin/oracle permission has got changed

ls -al $GRID_HOME/bin/oracle
-rwxr-x--x 1 grid oinstall 200678464 Feb 28 14:54 oracle

ls -al $ORACLE_HOME/bin/oracle
-rwxr-x--x 1 oracle asmadmin 228886191 Feb 28 15:41 oracle

Solution:

Change permissions as below

cd $GRID_HOME/bin
chmod 6751 oracle

cd $ORACLE_HOME/bin
chmod 6751 oracle

it should look like having sticky bit ..

ls -l $GRID_HOME/bin/oracle
-rwsr-s--x 1 grid oinstall 203974257 May 11 09:30 oracle

ls -l $ORACLE_HOME/bin/oracle
-rwsr-s--x 1 oracle oinstall 232399431 May 14 13:47 oracle

or 

ls -l $GRID_HOME/bin/oracle*
-rwxr-x--- 1 oracle oinstall         0 Sep  5  2010 oracleO*
-rwsr-s--x 1 oracle asmadmin 228886468 Jan 11  2012 oracle*

ls -l $ORACLE_HOME/bin/oracle*
-rwxr-x--- 1 oracle oinstall         0 Sep  5  2010 oracleO*
-rwsr-s--x 1 oracle asmadmin 228886468 Jan 11  2012 oracle*


Restart the ASM and DB instance after changing permission  .. DB will come up
Posted by hemora at 16:36 1 comments
Labels: ,

Friday, 17 May 2013

TROUBLESHOOTING GUIDE (TSG) - ORA-20: MAXIMUM NUMBER OF PROCESSES (%S) EXCEEDED [ID 1287854.1]



Applies to:

Oracle Server - Enterprise Edition - Version 10.1.0.2 to 11.2.0.3 [Release 10.1 to 11.2]
Information in this document applies to any platform.

Symptoms

What are the recommended steps to diagnose an ORA-20 - maximum number of processes (%s) exceeded?

Cause

Connections to the database have caused the current number of operating system processes associated with the Oracle Instance to exceeed the PROCESSES database parameter

Solution

The typical remedy to an ORA-20 - maximum number of processes (%s) exceeded ... is to increase the PROCESSES parameter ... This is usually only a temporary solution

Here are the steps to diagnose the cause of the ORA-20

1) The first step is to gather information about the processes that exist when the ORA-20 occurs

connect ... as sysdba

set markup html on
set pagesize 30
spool processes_sessions.html

select
p.username "V$PROCESS - OS USERNAME",
p.terminal,
p.program,
s.username "V$SESSION - USERNAME",
s.command,
s.status,
s.server,
s.process,
s.machine,
s.port,
s.terminal,
s.program,
s.sid,
s.serial#,
p.spid
FROM v$session s,v$process p
WHERE p.addr=s.paddr
order by p.background desc;

spool off
exit;NOTE .. depending on version ... S.PORT may need to be removed from the query 

2) Look for patterns in the output generated in step #1 (processes_sessions.html)SAMPLE OUTPUT
V$PROCESS - OS USERNAME
TERMINAL
PROGRAM
V$SESSION - USERNAME
COMMAND
STATUS
SERVER
PROCESS
MACHINE
PORT
TERMINAL
PROGRAM
oracle
UNKNOWN
oracle@filnx10 (TNS V1-V3)
SYS
3
ACTIVE
DEDICATED
27140
filnx10
0
pts/2
sqlplus@filnx10 (TNS V1-V3)
oracle
UNKNOWN
oracle@filnx10 (TNS V1-V3)









oracle
UNKNOWN
oracle@filnx10 (Q001)

0
ACTIVE
DEDICATED
24739
filnx10
0
UNKNOWN
oracle@filnx10 (Q001)
oracle
UNKNOWN
oracle@filnx10 (VKTM)

0
ACTIVE
DEDICATED
24534
filnx10
0
UNKNOWN
oracle@filnx10 (VKTM)
oracle
UNKNOWN
oracle@filnx10 (GEN0)

0
ACTIVE
DEDICATED
24538
filnx10
0
UNKNOWN
oracle@filnx10 (GEN0)
oracle
UNKNOWN
oracle@filnx10 (DIAG)

0
ACTIVE
DEDICATED
24540
filnx10
0
UNKNOWN
oracle@filnx10 (DIAG)
oracle
UNKNOWN
oracle@filnx10 (DBRM)

0
ACTIVE
DEDICATED
24542
filnx10
0
UNKNOWN
oracle@filnx10 (DBRM)
oracle
UNKNOWN
oracle@filnx10 (DIA0)

0
ACTIVE
DEDICATED
24544
filnx10
0
UNKNOWN
oracle@filnx10 (DIA0)
oracle
UNKNOWN
oracle@filnx10 (MMAN)

0
ACTIVE
DEDICATED
24546
filnx10
0
UNKNOWN
oracle@filnx10 (MMAN)
oracle
UNKNOWN
oracle@filnx10 (DBW0)

0
ACTIVE
DEDICATED
24548
filnx10
0
UNKNOWN
oracle@filnx10 (DBW0)
oracle
UNKNOWN
oracle@filnx10 (LGWR)

0
ACTIVE
DEDICATED
24550
filnx10
0
UNKNOWN
oracle@filnx10 (LGWR)
oracle
UNKNOWN
oracle@filnx10 (CKPT)

0
ACTIVE
DEDICATED
24552
filnx10
0
UNKNOWN
oracle@filnx10 (CKPT)
oracle
UNKNOWN
oracle@filnx10 (SMON)

0
ACTIVE
DEDICATED
24554
filnx10
0
UNKNOWN
oracle@filnx10 (SMON)
oracle
UNKNOWN
oracle@filnx10 (RECO)

0
ACTIVE
DEDICATED
24556
filnx10
0
UNKNOWN
oracle@filnx10 (RECO)
oracle
UNKNOWN
oracle@filnx10 (MMON)

0
ACTIVE
DEDICATED
24558
filnx10
0
UNKNOWN
oracle@filnx10 (MMON)
oracle
UNKNOWN
oracle@filnx10 (MMNL)

0
ACTIVE
DEDICATED
24560
filnx10
0
UNKNOWN
oracle@filnx10 (MMNL)
oracle
UNKNOWN
oracle@filnx10 (QMNC)

0
ACTIVE
DEDICATED
24576
filnx10
0
UNKNOWN
oracle@filnx10 (QMNC)
oracle
UNKNOWN
oracle@filnx10 (SMCO)

0
ACTIVE
DEDICATED
26415
filnx10
0
UNKNOWN
oracle@filnx10 (SMCO)
oracle
UNKNOWN
oracle@filnx10 (W000)

0
ACTIVE
DEDICATED
26417
filnx10
0
UNKNOWN
oracle@filnx10 (W000)
oracle
UNKNOWN
oracle@filnx10 (CJQ0)

0
ACTIVE
DEDICATED
24617
filnx10
0
UNKNOWN
oracle@filnx10 (CJQ0)
oracle
UNKNOWN
oracle@filnx10 (Q000)

0
ACTIVE
DEDICATED
24737
filnx10
0
UNKNOWN
oracle@filnx10 (Q000)
oracle
UNKNOWN
oracle@filnx10 (PMON)

0
ACTIVE
DEDICATED
24530
filnx10
0
UNKNOWN
oracle@filnx10 (PMON)
oracle
UNKNOWN
oracle@filnx10 (PSP0)

0
ACTIVE
DEDICATED
24532
filnx10
0
UNKNOWN
oracle@filnx10 (PSP0)


     * If there are many V$SESSION.STATUS = INACTIVE ...

            This means that there are many user sessions that have connected but are not doing anything

            POSSIBLE SOLUTION : Enable dead connection detection (DCD) and user resource limits

                    A discussion of Dead Connection Detection, Resource Limits, V$SESSION, V$PROCESS and OS processes (Doc ID 601605.1)

     * If there are many V$SESSION.PROGRAM (and possibly many V$SESSION.STATUS = INACTIVE) entries from the same program ...

            POSSIBLE SOLUTIONS :

            1) Examine the program (often a web server) to see if the program has a setting to abandon a connection after X number of seconds and reconnect (a respawn event)

                If the program has such a setting ... a slowdown in either network or database performance should to be investigated ... as this would cause a timeout in the application .. 
                  and thus a reconnect ... setting a longer 'timeout' in the application often will resolve future occurrences of ORA-20

             2) Like the solution above ... setting DCD and Resource Limits ... often resolves these as well 

     * If there are many rows with data for V$PROCESS but no data for V$SESSION ...

            This is a problem on the operating system side and such sessions will need to be killed manually with KILL -# (UNIX / LINUX) or ORAKILL (Windows)

             This condition needs to be examined by an operating system expert (System Administrator or OS Vendor)

             WHY? - When an Oracle session is terminated ... whether by the user logging out ... a kill session by a privileged user ... or even the user reaching a resource limit set in their profile
                           Oracle SMON (or PMON) will first clean up the database resources (rollback transactions ... release locks etc)
                           Oracle then will remove the entry in V$SESSION as the session is now 'cleaned up'
                           Oracle will then request that the operating system terminate any OS processes associated with the former Oracle Session

               The Oracle Kernel cannot force the OS to terminate the processes .. this occurs at the OS level only ... all it can do is request that they be terminatedNOTE: This is a living document ... Oracle encourages comments as to other solutions / scenarios ... so please feel free to add a remark

References

NOTE:1050281.1 - Getting ORA-00020/ ORA-00018 With A High Number Of CPUs Regardless Of How High The Related DB Parameters Are Set.
NOTE:169706.1 - Oracle Database (RDBMS) on Unix AIX,HP-UX,Linux,Mac OS X,Solaris,Tru64 Unix Operating Systems Installation and Configuration Requirements Quick Reference (8.0.5 to 11.2)
NOTE:458527.1 - ORA-00020 on an ASM instance
NOTE:601605.1 - A discussion of Dead Connection Detection, Resource Limits, V$SESSION, V$PROCESS and OS processes
Posted by hemora at 11:22 0 comments
Labels: , ,

Large Number of Sessions in Database with Program Name Oraagent.bin

Applies to:
Oracle Net Services - Version 11.2.0.2 and later
Oracle Server - Enterprise Edition - Version 11.2.0.2 and later
Information in this document applies to any platform.
***Checked for relevance on 26-DEC-2012***

Symptoms
11.2.0.2 RAC database shows many sessions with program name of oraagent.bin<hostname>, which are never removed.
Sample output :
SQL> select s.sid, s.logon_time, p.spid, s.process, s.program from
v$session s, v$process p where s.paddr = p.addr and s.program like '%oraagent%' and s.logon_time < SYSDATE-1 order by s.logon_time;
SID      LOGON_TIME         SPID      PROCESS   PROGRAM
-------- -------------------------- ----------- --------------- --------------------------------------------------
1015    09-03-2011:20:44:18   10822      10623          oraagent.bin@sample.com (TNS V1-V3)
293      09-03-2011:20:44:18   10824      10623          oraagent.bin@sample.com (TNS V1-V3)
871      09-03-2011:20:44:18   10833      10623          oraagent.bin@sample.com (TNS V1-V3)
11        09-03-2011:22:25:56   30794      30464          oraagent.bin@sample.com (TNS V1-V3)
436      09-03-2011:22:47:58   20707      20514          oraagent.bin@sample.com (TNS V1-V3)
152      10-03-2011:00:30:51   2228        2093            oraagent.bin@sample.com (TNS V1-V3)
1017    10-03-2011:00:30:51   2232        2093            oraagent.bin@sample.com (TNS V1-V3)
294      10-03-2011:01:33:23   14541      14307          oraagent.bin@sample.com (TNS V1-V3)
1013    10-03-2011:01:33:23   14545      14307          oraagent.bin@sample.com (TNS V1-V3)
151      10-03-2011:01:33:23   14546      14307          oraagent.bin@sample.com (TNS V1-V3)
295      10-03-2011:03:54:16   32539      32384          oraagent.bin@sample.com (TNS V1-V3)
730      10-03-2011:03:54:16   32541      32384          oraagent.bin@sample.com (TNS V1-V3)
1018    10-03-2011:04:53:25   20203      19882          oraagent.bin@sample.com (TNS V1-V3)
874      10-03-2011:04:53:25   20197      19882          oraagent.bin@sample.com (TNS V1-V3)
586      10-03-2011:04:53:25   20195      19882          oraagent.bin@sample.com (TNS V1-V3)
731      10-03-2011:06:11:05   12921      12728          oraagent.bin@sample.com (TNS V1-V3)
153      10-03-2011:06:43:13   4417        4034            oraagent.bin@sample.com (TNS V1-V3)
431      10-03-2011:06:43:13   4419        4034            oraagent.bin@sample.com (TNS V1-V3)
297      10-03-2011:06:43:13   4425        4034            oraagent.bin@sample.com (TNS V1-V3)
732      10-03-2011:07:21:51   1027        32500          oraagent.bin@sample.com (TNS V1-V3)
8          10-03-2011:09:31:04   19914      18710          oraagent.bin@sample.com (TNS V1-V3)
734      10-03-2011:12:53:47   22039      21506          oraagent.bin@sample.com (TNS V1-V3)
4          10-03-2011:12:53:47   22040      21506          oraagent.bin@sample.com (TNS V1-V3)
309      10-03-2011:13:20:12   7483        6420            oraagent.bin@sample.com (TNS V1-V3)
156      10-03-2011:13:20:12   7481        6420            oraagent.bin@sample.com (TNS V1-V3)
597      10-03-2011:15:36:50   28148      27237          oraagent.bin@sample.com (TNS V1-V3)
882      10-03-2011:15:36:50   28151      27237          oraagent.bin@sample.com (TNS V1-V3)
450      10-03-2011:19:54:06   29750      28921          oraagent.bin@sample.com (TNS V1-V3)
1030    10-03-2011:19:54:06   29749      28921          oraagent.bin@sample.com (TNS V1-V3)
884      10-03-2011:21:39:50   32278      31873          oraagent.bin@sample.com (TNS V1-V3)
741      10-03-2011:21:39:50   32272      31873          oraagent.bin@sample.com (TNS V1-V3)
1016    10-03-2011:22:43:32   16557      16077          oraagent.bin@sample.com (TNS V1-V3)
446      10-03-2011:23:17:10   5454        4483            oraagent.bin@sample.com (TNS V1-V3)
13        10-03-2011:23:17:10   5458        4483            oraagent.bin@sample.com (TNS V1-V3)
306      12-03-2011:15:35:10   24931      24769          oraagent.bin@sample.com (TNS V1-V3)
590      13-03-2011:02:18:55   30230      29814          oraagent.bin@sample.com (TNS V1-V3)
441      13-03-2011:02:18:55   30232      29814          oraagent.bin@sample.com (TNS V1-V3)
875      13-03-2011:02:41:28   17367      1044            oraagent.bin@sample.com (TNS V1-V3)

These hung processes numbers will build up over time and can cause issues along the lines of maximum  process limits to be exhausted on the system.ORA-00020: maximum number of processes (xxx) exceeded, might be triggered.
PS output shows the processes are local, ie bequeath connection from the same machine.
$ ps -ef |grep -i 1044
oracle 1044 1 0 Mar15 ? 00:00:00 oracleOITM21 (DESCRIPTION=(LOCAL =YES)(ADDRESS=(PROTOCOL=beq)))

LSOF on the process will show multiple pipes for the process.
$ lsof -p 1044 | grep pipe
oracle 1044 oracle 188r FIFO 0,6 187781116 pipe
oracle 1044 oracle 189w FIFO 0,6 187781116 pipe
oracle 1044 oracle 212r FIFO 0,6 187781117 pipe
oracle 1044 oracle 213r FIFO 0,6 187781062 pipe
oracle 1044 oracle 214w FIFO 0,6 187781062 pipe
oracle 1044 oracle 215r FIFO 0,6 187781063 pipe
oracle 1044 oracle 216w FIFO 0,6 187781063 pipe
oracle 1044 oracle 217w FIFO 0,6 187781117 pipe
oracle 1044 oracle 218r FIFO 0,6 187781139 pipe
oracle 1044 oracle 221w FIFO 0,6 187781140 pipe

Strace of hung process shows
1044 07:35:57.232994 read(30, <unfinished ...>

A further sympton can be that the operating system process is seen, but there is no entry in the dictionary for the session.
Changes
Upgrade to 11.2.0.2
Cause
The cause of this problem has been identified in RAC Bug:11877079 Hundreds of oraagent.bin@hostname sessions in 11.2.0.2 database.
Solution
As the hung process are local , they are using IPC protocol. Timeout parameters such as DCD or send and receive timeout, can not be used. These are only for TCP.

Workaround would be to remove the process from operating system level, ie a kill -9 <PID>
Issue will be fixed in the patch set release 11.2.0.3

Download patch for your operating system as required. Oracle Developement recommended more than one patch to be installed.

At the time of writing the following are available

Linux
o MergePatch:11938226 for 11.2.0.2.1 (11804954 11744313 11871469 11877079)
o MergePatch:12334600 for 11.2.0.2.1 GI Bundle 1 (10299006 11877079)
o MergePatch:12347844 for 11.2.0.2.2 (11744313 10299006 11069614 1168409 11871469 11977079)
o MergePatch:12531175 for 11.2.0.2.2 + PSU ( 11877079 10299006)

Linux and Solaris
o MergePatch:12362639 for 11.2.0.2.2 GI bundle 2 (11877079 10299006)
References
NOTE:1287496.1 - Many Connections From oraagent.bin to ASM or Database Instance (Likely ORA-00020)
BUG:11877079 - HUNDREDS OF ORAAGENT.BIN@HOSTNAME SESSSIONS IN 11.2.0.2 DATABASE
Posted by hemora at 11:19 1 comments
Labels: , ,
Subscribe to: Posts (Atom)