<< Back to previous view

[QA-55] deinstall tool drops database Created: 26/Mar/13  Updated: 26/Mar/13

Status: Closed
Project: Questions & Answers
Fix Version/s: None

Type: Oracle - Administration Priority: Major
Reporter: ubTools Support Assignee: ubTools Support
Resolution: Answered Votes: 0

Product Version: 11.2.0.3
Operating System: Solaris
Host Name: .
Database Name: .

 Description   
Oracle® Database Upgrade Guide 11g Release 2 (11.2) Part Number E23633-07 writes: 
Known Issue with the Deinstallation Tool for This Release
Cause: After upgrading from 11.2.0.1 or 11.2.0.2 to 11.2.0.3, deinstallation of the Oracle home in the earlier release of Oracle Database
may result in the deletion of the old Oracle base that was associated with it. This may also result in the deletion of data files, audit files, etc.,
which are stored under the old Oracle base.

Action: Before deinstalling the Oracle home in the earlier release, edit the orabase_cleanup.lst file found in the $Oracle_Home/utl directory and
remove the "oradata" and "admin" entries. Then, deinstall the Oracle home using the 11.2.0.3 deinstallation tool.

_Ref: http://docs.oracle.com/cd/E11882_01/server.112/e23633/intro.htm#BHCEECDJ

In our case:

  • There were already no oradata and admin entries in $ORACLE_HOME/utl/orabase_cleanup.lst.
  • There was already no database file in $ORACLE_BASE/oradata. There was just a soft link to ASM disk, which includes the database.

But, deinstall tool dropped the database.

 Comments   
Comment by ubTools Support [ 26/Mar/13 03:52 PM ]
Be careful while using deinstall. if you want to keep your database, don't use it until this problem is fixed.



[QA-52] "Transaction recovery: lock conflict caught and ignored" messages in ALERT LOG. Created: 30/Dec/11  Updated: 16/Jan/12

Status: Closed
Project: Questions & Answers
Fix Version/s: None

Type: Oracle - Administration Priority: Major
Reporter: ubTools Support Assignee: ubTools Support
Resolution: Answered Votes: 0

Product Version: 11.2.0.1.0 (RAC)
Operating System: HP-UX
Operating System Version: B.11.31
Host Name: .
Database Name: .

 Description   
The customer encounters the following messages:

ALERT LOG:

.....
Transaction recovery: lock conflict caught and ignored
Transaction recovery: lock conflict caught and ignored
Transaction recovery: lock conflict caught and ignored
Transaction recovery: lock conflict caught and ignored
Transaction recovery: lock conflict caught and ignored
Transaction recovery: lock conflict caught and ignored
Transaction recovery: lock conflict caught and ignored
Transaction recovery: lock conflict caught and ignored
Transaction recovery: lock conflict caught and ignored
Transaction recovery: lock conflict caught and ignored
Transaction recovery: lock conflict caught and ignored
Transaction recovery: lock conflict caught and ignored
Transaction recovery: lock conflict caught and ignored
Transaction recovery: lock conflict caught and ignored
Transaction recovery: lock conflict caught and ignored
.....

SMON TRACE:

.....
*** 2011-12-26 14:42:46.401
Serial Transaction recovery caught exception 30319
Serial Transaction recovery caught exception 601

*** 2011-12-26 14:46:25.455
Serial Transaction recovery caught exception 601
Serial Transaction recovery caught exception 601
Serial Transaction recovery caught exception 601
Serial Transaction recovery caught exception 601
.....

The customer said the error started after SUPPLEMENTAL LOGGING enabled. But, the messages have not disappeared after disabling it.

 Comments   
Comment by ubTools Support [ 30/Dec/11 01:09 PM ]
DEAD TRANSACTIONS:

SQL:

select b.name useg, b.inst# instid, b.status$ status, a.ktuxeusn
xid_usn, a.ktuxeslt xid_slot, a.ktuxesqn xid_seq, a.ktuxesiz undoblocks,
a.ktuxesta txstatus
from x$ktuxe a, undo$ b
where a.ktuxecfl like '%DEAD%'
and a.ktuxeusn = b.us#;

Data:

USEG	INSTID	STATUS	XID_USN	XID_SLOT	XID_SEQ	UNDOBLOCKS	TXSTATUS
_SYSSMU1209_1270276489$	1	3	1209	3	1100382	3033	ACTIVE
_SYSSMU1482_3325964579$	2	2	1482	16	496322	0	INACTIVE
_SYSSMU1681_4095893383$	2	2	1681	5	472365	0	INACTIVE
_SYSSMU2072_3213080551$	2	2	2072	2	120912	0	INACTIVE

Definition:

  • Transaction id: XID_USN.XID_SLOT.XID_SEQ

Comment:

  • There is an active dead transaction in _SYSSMU1209_1270276489$ undo segment.
  • The dead transaction id is 1209.3.1100382 which is 0x04B9.003.0010CA5E in hexadecimal.
Comment by ubTools Support [ 30/Dec/11 01:27 PM ]
UNDO HEADER:

Reading Transaction Table in the UNDO header:

SQL:

  • SQL> ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU1209_1270276489$';

Data:

.....
  TRN TBL::
 
  index  state cflags  wrap#    uel         scn            dba            parent-xid    nub     stmt_num    cmt
  ------------------------------------------------------------------------------------------------
   0x00    9    0x00  0x10ca61  0x001a  0x001d.ac01b6ea  0x04c30103  0x0000.000.00000000  0x00000001   0x00000000  1324239683
   0x01    9    0x00  0x10c8f0  0x000e  0x001d.abff4525  0x04c30043  0x0000.000.00000000  0x00000001   0x00000000  1324239603
   0x02    9    0x00  0x10ca1f  0x0005  0x001d.abfad814  0x00c2e328  0x0000.000.00000000  0x00000003   0x00000000  1324239446
   0x03   10    0x90  0x10ca5e  0x0002  0x001d.ab7efd87  0x00c0f5ea  0x0000.000.00000000  0x00000bd9   0x04c1f938  0
   0x04    9    0x00  0x10c46d  0x000a  0x001d.abda8e80  0x04c28e5f  0x0000.000.00000000  0x00000001   0x00000000  1324238461
   0x05    9    0x00  0x10c91c  0x0015  0x001d.abfadb90  0x00c2e32d  0x0000.000.00000000  0x00000001   0x00000000  1324239447
   0x06    9    0x00  0x10cdbb  0x001d  0x001d.abd50f70  0x04c28e80  0x0000.000.00000000  0x00000001   0x00000000  1324238283
   0x07    9    0x00  0x10c77a  0x0004  0x001d.abd90c5b  0x00c29cd8  0x0000.000.00000000  0x00000001   0x00000000  1324238409
   0x08    9    0x00  0x10c229  0x0020  0x001d.abe1de1e  0x00c2a8d2  0x0000.000.00000000  0x00000001   0x00000000  1324238704
   0x09    9    0x00  0x10ca28  0x0006  0x001d.abd4dfb6  0x04c28e5f  0x0000.000.00000000  0x00000001   0x00000000  1324238278
   0x0a    9    0x00  0x10c6b7  0x0008  0x001d.abe1c7f3  0x00c2a8c2  0x0000.000.00000000  0x00000001   0x00000000  1324238701
   0x0b    9    0x00  0x10c9e6  0x0017  0x001d.abfdbd74  0x04c30007  0x0000.000.00000000  0x00000001   0x00000000  1324239554
   0x0c    9    0x00  0x10cb45  0x0011  0x001d.abfc5eea  0x04c2ff9d  0x0000.000.00000000  0x00000001   0x00000000  1324239502
   0x0d    9    0x00  0x10c444  0x001c  0x001d.abca9d1f  0x00c22bc1  0x0000.000.00000000  0x00000001   0x00000000  1324237948
   0x0e    9    0x00  0x10c7e3  0x0000  0x001d.abffbb9a  0x04c3005f  0x0000.000.00000000  0x00000001   0x00000000  1324239618
   0x0f    9    0x00  0x10ca72  0x0007  0x001d.abd82320  0x00c29c21  0x0000.000.00000000  0x00000001   0x00000000  1324238375
   0x10    9    0x00  0x10c501  0x001f  0x001d.abf33edd  0x00c2e03f  0x0000.000.00000000  0x00000001   0x00000000  1324239208
   0x11    9    0x00  0x10ca90  0x000b  0x001d.abfdbc34  0x04c30004  0x0000.000.00000000  0x00000001   0x00000000  1324239554
   0x12    9    0x00  0x10c2ef  0x0018  0x001d.ac09d85c  0x04c3036f  0x0000.000.00000000  0x00000001   0x00000000  1324239959
   0x13    9    0x00  0x10c8ae  0x0010  0x001d.abe83852  0x04c2ea99  0x0000.000.00000000  0x00000001   0x00000000  1324238911
   0x14    9    0x00  0x10c5ad  0x0016  0x001d.abd3e99a  0x04c28de0  0x0000.000.00000000  0x00000001   0x00000000  1324238242
   0x15    9    0x00  0x10c62c  0x000c  0x001d.abfb4d1a  0x04c2ff8d  0x0000.000.00000000  0x00000001   0x00000000  1324239464
   0x16    9    0x00  0x10c72b  0x001b  0x001d.abd4d238  0x04c28e4e  0x0000.000.00000000  0x00000001   0x00000000  1324238274
   0x17    9    0x00  0x10c2da  0x0001  0x001d.abff0f85  0x04c30029  0x0000.000.00000000  0x00000001   0x00000000  1324239598
   0x18    9    0x00  0x10c589  0xffff  0x001d.ad480910  0x00000000  0x0000.000.00000000  0x00000000   0x00000000  1324254620
   0x19    9    0x00  0x10c628  0x000d  0x001d.abca6bcb  0x00c22ba5  0x0000.000.00000000  0x00000001   0x00000000  1324237944
   0x1a    9    0x00  0x10c4a7  0x0012  0x001d.ac04e46b  0x04c30232  0x0000.000.00000000  0x00000001   0x00000000  1324239791
   0x1b    9    0x00  0x10c2e6  0x0009  0x001d.abd4df89  0x04c28e5c  0x0000.000.00000000  0x00000001   0x00000000  1324238277
   0x1c    9    0x00  0x10c755  0x0014  0x001d.abcb5957  0x04c28b14  0x0000.000.00000000  0x00000001   0x00000000  1324237971
   0x1d    9    0x00  0x10cd54  0x0021  0x001d.abd6f01b  0x00c29b7b  0x0000.000.00000000  0x00000001   0x00000000  1324238343
   0x1e    9    0x00  0x10c5e3  0x0019  0x001d.abca546a  0x00c22b90  0x0000.000.00000000  0x00000001   0x00000000  1324237940
   0x1f    9    0x00  0x10c232  0x0002  0x001d.abf7fc92  0x00c2e1be  0x0000.000.00000000  0x00000001   0x00000000  1324239355
   0x20    9    0x00  0x10c391  0x0013  0x001d.abe5ff89  0x04c2e999  0x0000.000.00000000  0x00000001   0x00000000  1324238832
   0x21    9    0x00  0x10cc70  0x000f  0x001d.abd77e3c  0x00c29bd6  0x0000.000.00000000  0x00000001   0x00000000  1324238361
  EXT TRN CTL::
  usn: 1209
.....

Definitions:

  • State#10 means active transaction.
  • dba points to starting UNDO block address.
  • usn: Undo segment number
  • usn.index.wrap# gives transaction id.

Comment:

An active transaction of 0x04b9.003.0010ca5e is available in the slot of 0x03, which has a dba of 0x00c0f5ea, which is 12645866 in decimal.

Comment by ubTools Support [ 30/Dec/11 01:43 PM ]
UNDO BLOCK:

Reading UNDO Block:

SQL:

  • fileID: select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(12645866) from x$dual;
  • blockID:select DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(12645866) from x$dual;
  • alter system dump datafile <fileID> block <blockID>;

Data:

.....
UNDO BLK:  
xid: 0x04b9.003.0010ca5e  seq: 0x1447 cnt: 0x2e  irb: 0x2c  icl: 0x0   flg: 0x0000
 
 Rec Offset      Rec Offset      Rec Offset      Rec Offset      Rec Offset
---------------------------------------------------------------------------
0x01 0x1f8c     0x02 0x1dac     0x03 0x1d3c     0x04 0x1ccc     0x05 0x1c64     
0x06 0x1c0c     0x07 0x1b7c     0x08 0x1b0c     0x09 0x1a9c     0x0a 0x1a24     
0x0b 0x19cc     0x0c 0x183c     0x0d 0x17cc     0x0e 0x175c     0x0f 0x16e4     
0x10 0x168c     0x11 0x15fc     0x12 0x158c     0x13 0x151c     0x14 0x14b4     
0x15 0x145c     0x16 0x12f4     0x17 0x1284     0x18 0x1214     0x19 0x11ac     
0x1a 0x1154     0x1b 0x0f9c     0x1c 0x0f2c     0x1d 0x0ebc     0x1e 0x0e44     
0x1f 0x0dec     0x20 0x0c3c     0x21 0x0bcc     0x22 0x0b5c     0x23 0x0af4     
0x24 0x0a9c     0x25 0x08c4     0x26 0x0854     0x27 0x07e4     0x28 0x076c     
0x29 0x0714     0x2a 0x0604     0x2b 0x022c     0x2c 0x01c4     0x2d 0x0154     
0x2e 0x00e4     
.....

Definitions

  • irb points to last UNDO RECORD in UNDO block.
  • rci points to previous UNDO RECORD. if rci=0, it's the first UNDO RECORD.
  • Recovery operation starts from irb and chain is followed by rci until rci is zero.

Comment:

  • The transaction of 0x04b9.003.0010ca5e starts recovery from UNDO RECORD of 0x2c.
Comment by ubTools Support [ 30/Dec/11 02:16 PM ]
UNDO RECORDS:

Reading UNDO Records:

Data:

.....

*-----------------------------
* Rec #0x2c  slt: 0x03  objn: 939468(0x000e55cc)  objd: 941274  tblspc: 9(0x00000009)
*       Layer:  11 (Row)   opc: 1   rci 0x2b   
.....
*-----------------------------
* Rec #0x2b  slt: 0x03  objn: 939468(0x000e55cc)  objd: 941274  tblspc: 9(0x00000009)
*       Layer:  11 (Row)   opc: 1   rci 0x2a   
.....
*-----------------------------
* Rec #0x2a  slt: 0x03  objn: 939468(0x000e55cc)  objd: 941274  tblspc: 9(0x00000009)
*       Layer:  11 (Row)   opc: 1   rci 0x29   
.....
*-----------------------------
* Rec #0x29  slt: 0x03  objn: 1126679(0x00113117)  objd: 1126679  tblspc: 9(0x00000009)
*       Layer:  10 (Index)   opc: 22   rci 0x28   
.....
*-----------------------------
* Rec #0x28  slt: 0x03  objn: 1123018(0x001122ca)  objd: 1123018  tblspc: 9(0x00000009)
*       Layer:  11 (Row)   opc: 1   rci 0x27   
.....
*-----------------------------
* Rec #0x27  slt: 0x03  objn: 1162285(0x0011bc2d)  objd: 1162285  tblspc: 9(0x00000009)
*       Layer:  10 (Index)   opc: 22   rci 0x26   
.....
*-----------------------------
* Rec #0x26  slt: 0x03  objn: 1162273(0x0011bc21)  objd: 1162273  tblspc: 9(0x00000009)
*       Layer:  10 (Index)   opc: 22   rci 0x25   
.....
*-----------------------------
* Rec #0x25  slt: 0x03  objn: 939450(0x000e55ba)  objd: 939450  tblspc: 9(0x00000009)
*       Layer:  11 (Row)   opc: 1   rci 0x24   
.....
*-----------------------------
* Rec #0x24  slt: 0x03  objn: 1126696(0x00113128)  objd: 1126696  tblspc: 9(0x00000009)
*       Layer:  10 (Index)   opc: 22   rci 0x23   
.....
*-----------------------------
* Rec #0x23  slt: 0x03  objn: 1123035(0x001122db)  objd: 1123035  tblspc: 9(0x00000009)
*       Layer:  11 (Row)   opc: 1   rci 0x22   
.....
*-----------------------------
* Rec #0x22  slt: 0x03  objn: 1162285(0x0011bc2d)  objd: 1162285  tblspc: 9(0x00000009)
*       Layer:  10 (Index)   opc: 22   rci 0x21   
.....
*-----------------------------
* Rec #0x21  slt: 0x03  objn: 1162273(0x0011bc21)  objd: 1162273  tblspc: 9(0x00000009)
*       Layer:  10 (Index)   opc: 22   rci 0x20   
.....
*-----------------------------
* Rec #0x20  slt: 0x03  objn: 939408(0x000e5590)  objd: 941229  tblspc: 9(0x00000009)
*       Layer:  11 (Row)   opc: 1   rci 0x1f   
.....
*-----------------------------
* Rec #0x1f  slt: 0x03  objn: 1126655(0x001130ff)  objd: 1126655  tblspc: 9(0x00000009)
*       Layer:  10 (Index)   opc: 22   rci 0x1e   
.....
*-----------------------------
* Rec #0x1e  slt: 0x03  objn: 1122994(0x001122b2)  objd: 1122994  tblspc: 9(0x00000009)
*       Layer:  11 (Row)   opc: 1   rci 0x1d   
.....
*-----------------------------
* Rec #0x1d  slt: 0x03  objn: 1162285(0x0011bc2d)  objd: 1162285  tblspc: 9(0x00000009)
*       Layer:  10 (Index)   opc: 22   rci 0x1c   
.....
*-----------------------------
* Rec #0x1c  slt: 0x03  objn: 1162273(0x0011bc21)  objd: 1162273  tblspc: 9(0x00000009)
*       Layer:  10 (Index)   opc: 22   rci 0x1b   
.....
*-----------------------------
* Rec #0x1b  slt: 0x03  objn: 939429(0x000e55a5)  objd: 941242  tblspc: 9(0x00000009)
*       Layer:  11 (Row)   opc: 1   rci 0x1a   
.....
*-----------------------------
* Rec #0x1a  slt: 0x03  objn: 1126678(0x00113116)  objd: 1126678  tblspc: 9(0x00000009)
*       Layer:  10 (Index)   opc: 22   rci 0x19   
.....
*-----------------------------
* Rec #0x19  slt: 0x03  objn: 1123017(0x001122c9)  objd: 1123017  tblspc: 9(0x00000009)
*       Layer:  11 (Row)   opc: 1   rci 0x18   
.....
*-----------------------------
* Rec #0x18  slt: 0x03  objn: 1162285(0x0011bc2d)  objd: 1162285  tblspc: 9(0x00000009)
*       Layer:  10 (Index)   opc: 22   rci 0x17   
.....
*-----------------------------
* Rec #0x17  slt: 0x03  objn: 1162273(0x0011bc21)  objd: 1162273  tblspc: 9(0x00000009)
*       Layer:  10 (Index)   opc: 22   rci 0x16   
.....
*-----------------------------
* Rec #0x16  slt: 0x03  objn: 939466(0x000e55ca)  objd: 941272  tblspc: 9(0x00000009)
*       Layer:  11 (Row)   opc: 1   rci 0x15   
.....
*-----------------------------
* Rec #0x15  slt: 0x03  objn: 1126681(0x00113119)  objd: 1126681  tblspc: 9(0x00000009)
*       Layer:  10 (Index)   opc: 22   rci 0x14   
.....
*-----------------------------
* Rec #0x14  slt: 0x03  objn: 1123020(0x001122cc)  objd: 1123020  tblspc: 9(0x00000009)
*       Layer:  11 (Row)   opc: 1   rci 0x13   
.....
*-----------------------------
* Rec #0x13  slt: 0x03  objn: 1162285(0x0011bc2d)  objd: 1162285  tblspc: 9(0x00000009)
*       Layer:  10 (Index)   opc: 22   rci 0x12   
.....
*-----------------------------
* Rec #0x12  slt: 0x03  objn: 1162273(0x0011bc21)  objd: 1162273  tblspc: 9(0x00000009)
*       Layer:  10 (Index)   opc: 22   rci 0x11   
.....
*-----------------------------
* Rec #0x11  slt: 0x03  objn: 939420(0x000e559c)  objd: 941236  tblspc: 9(0x00000009)
*       Layer:  11 (Row)   opc: 1   rci 0x10   
.....
*-----------------------------
* Rec #0x10  slt: 0x03  objn: 1126647(0x001130f7)  objd: 1126647  tblspc: 9(0x00000009)
*       Layer:  10 (Index)   opc: 22   rci 0x0f   
.....
*-----------------------------
* Rec #0xf  slt: 0x03  objn: 1122986(0x001122aa)  objd: 1122986  tblspc: 9(0x00000009)
*       Layer:  11 (Row)   opc: 1   rci 0x0e   
.....
*-----------------------------
* Rec #0xe  slt: 0x03  objn: 1162285(0x0011bc2d)  objd: 1162285  tblspc: 9(0x00000009)
*       Layer:  10 (Index)   opc: 22   rci 0x0d   
.....
*-----------------------------
* Rec #0xd  slt: 0x03  objn: 1162273(0x0011bc21)  objd: 1162273  tblspc: 9(0x00000009)
*       Layer:  10 (Index)   opc: 22   rci 0x0c   
.....
*-----------------------------
* Rec #0xc  slt: 0x03  objn: 939418(0x000e559a)  objd: 941235  tblspc: 9(0x00000009)
*       Layer:  11 (Row)   opc: 1   rci 0x0b   
.....
*-----------------------------
* Rec #0xb  slt: 0x03  objn: 1126653(0x001130fd)  objd: 1126653  tblspc: 9(0x00000009)
*       Layer:  10 (Index)   opc: 22   rci 0x0a   
.....
*-----------------------------
* Rec #0xa  slt: 0x03  objn: 1122992(0x001122b0)  objd: 1122992  tblspc: 9(0x00000009)
*       Layer:  11 (Row)   opc: 1   rci 0x09   
.....
*-----------------------------
* Rec #0x9  slt: 0x03  objn: 1162285(0x0011bc2d)  objd: 1162285  tblspc: 9(0x00000009)
*       Layer:  10 (Index)   opc: 22   rci 0x08   
.....
*-----------------------------
* Rec #0x8  slt: 0x03  objn: 1162273(0x0011bc21)  objd: 1162273  tblspc: 9(0x00000009)
*       Layer:  10 (Index)   opc: 22   rci 0x07   
.....
*-----------------------------
* Rec #0x7  slt: 0x03  objn: 939438(0x000e55ae)  objd: 941251  tblspc: 9(0x00000009)
*       Layer:  11 (Row)   opc: 1   rci 0x06   
.....
*-----------------------------
* Rec #0x6  slt: 0x03  objn: 1126696(0x00113128)  objd: 1126696  tblspc: 9(0x00000009)
*       Layer:  10 (Index)   opc: 22   rci 0x05   
.....
*-----------------------------
* Rec #0x5  slt: 0x03  objn: 1123035(0x001122db)  objd: 1123035  tblspc: 9(0x00000009)
*       Layer:  11 (Row)   opc: 1   rci 0x04   
.....
*-----------------------------
* Rec #0x4  slt: 0x03  objn: 1162285(0x0011bc2d)  objd: 1162285  tblspc: 9(0x00000009)
*       Layer:  10 (Index)   opc: 22   rci 0x03   
.....
*-----------------------------
* Rec #0x3  slt: 0x03  objn: 1162273(0x0011bc21)  objd: 1162273  tblspc: 9(0x00000009)
*       Layer:  10 (Index)   opc: 22   rci 0x02   
.....
*-----------------------------
* Rec #0x2  slt: 0x03  objn: 939448(0x000e55b8)  objd: 939448  tblspc: 9(0x00000009)
*       Layer:  11 (Row)   opc: 1   rci 0x01   
.....
*-----------------------------
* Rec #0x1  slt: 0x03  objn: 1126675(0x00113113)  objd: 1126675  tblspc: 9(0x00000009)
*       Layer:  10 (Index)   opc: 22   rci 0x00   
.....
KDO Op code: LMN row dependencies Disabled
.....

Definitions:

  • objn means object id.

Comment:

  • The objects need recovery: 
    select * from dba_objects
where object_id in (939468,1126679,1123018,1162285,1162273,939450,1126696,1123035,939408,
1126655,1122994,939429,1126678,1123017,939466,1126681,
1123020,939420,1126647,1122986,939418,1126653,1122992,939438,939448,1126675);
  • The first UNDO record includes LMN.
    --
    When running RAC and compatible 11.1 or higher, SMON could fail to
recover transactions which had undo records for supplemental logging.
 
  (1) SMON is spinning
  (2) Must be RAC and compatible 11.1 or higher
  (3) Supplemental logging must have been enabled.
 
  If so, dump the undo for the transaction mentioned.  If the records
  show LMN entries, it is this bug.

    Ref: Bug 9489626 ORA-600 [4464] in RAC and SMON spins on cpu for a table with supplemental logging
Comment by ubTools Support [ 30/Dec/11 02:22 PM ]
ACTIONS:

Bug:

This problem is Oracle Bug:9857702:

.....
Affects:
Product (Component) Oracle Server (Rdbms)  
Range of versions believed to be affected Versions >= 11.1 but BELOW 12.1  
Versions confirmed as being affected
•11.2.0.1 
•11.1.0.7 
 
Platforms affected Generic (all / most platforms affected)  

Fixed:
This issue is fixed in
•12.1 (Future Release) 
•11.2.0.2 (Server Patch Set) 
•11.1.0.7.8 Patch Set Update 
•11.1.0.7 Patch 40 on Windows Platforms  
.....

Ref: Bug 9857702 ORA-600 [4464] / ORA-600 [4139] by ROLLBACK for a table with supplemental logging enabled

Workaround:

  • Recreate objects that need recovery.
Comment by ubTools Support [ 30/Dec/11 02:29 PM ]
Waiting for the customer action.
Comment by ubTools Support [ 16/Jan/12 03:28 PM ]
The customer dropped the identified objects, and the problem disappeared.



[QA-42] ORA-27040 ORA-19504 OSD-04002: While backing up by RMAN to shared disk on Windows. Created: 18/Sep/08  Updated: 18/Sep/08

Status: Closed
Project: Questions & Answers
Fix Version/s: None

Type: Oracle - Administration Priority: Major
Reporter: ubTools Support Assignee: ubTools Support
Resolution: Answered Votes: 0

Product Version: 10.2.0.4
Operating System: Windows
Operating System Version: Windows 2000

 Description   
(The problem solution is simple. But, since it saves setup times, it's doccumented here.)

Note:145843.1 How to Configure RMAN to Write to Shared Drives on Windows NT/2000 is implemented. Although the script works on RMAN command line; it fails if it's defined as a job on Enterprise Manager.

An excerpt from the script:

run
{
 allocate channel ch0 device type disk format '\\host\RMAN\RMAN_%U';
 ...
}

An excerpt from the output log of EM:

RMAN> run

2> {

3> allocate channel ch0 device type disk format '\host\RMAN\RMAN_%U';
...
10> }
...
RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of backup plus archivelog command at 09/18/2008 17:37:36

ORA-19504: failed to create file "C:\host\RMAN\RMAN_1KJQTRAU_1_1"

ORA-27040: file create error, unable to create file

OSD-04002: unable to open file

O/S-Error: (OS 3) The system cannot find the path specified.


 Comments   
Comment by ubTools Support [ 18/Sep/08 02:54 PM ]
As seen above, the file name in the script is '\\host\RMAN\RMAN_%U'. But, it's converted by EM to:
  • '\host\RMAN\RMAN_%U'
  • Then "C:\host\RMAN\RMAN_1KJQTRAU_1_1"
Comment by ubTools Support [ 18/Sep/08 02:57 PM ]
'\' character is a special character in JAVA/C. The correct file name should be:
  • '\\\host\RMAN\RMAN_%U'

Three '\' characters should be used before hostname; not two.




[QA-40] "Oracle Database Server" status is INVALID after applying 10.2.0.4 PatchSet. Created: 15/Jun/08  Updated: 15/Jun/08

Status: Closed
Project: Questions & Answers
Fix Version/s: None

Type: Oracle - Administration Priority: Major
Reporter: ubTools Support Assignee: ubTools Support
Resolution: Answered Votes: 0

Product Version: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
Operating System: IBM-AIX

 Description   
After applying 10.2.0.4.0 PatchSet into 10.2.0.3.0, catupgrd.sql logs shows the following: 
...
SQL> CREATE OR REPLACE PACKAGE BODY dbms_sqlpa wrapped
  2  a000000
  3  1
  4  abcd
  5  abcd
  6  abcd
...
Warning: Package Body created with compilation errors.

SQL> show errors;
Errors for PACKAGE BODY DBMS_SQLPA:

LINE/COL ERROR
-------- -----------------------------------------------------------------
113/5    PL/SQL: SQL Statement ignored
118/44   PL/SQL: ORA-00904: "OTHER_XML": invalid identifier
SQL> 
...
Component                                Status         Version  HH:MM:SS
Oracle Database Server                  INVALID      10.2.0.4.0  00:09:22
JServer JAVA Virtual Machine              VALID      10.2.0.4.0  00:02:43
Oracle XDK                                VALID      10.2.0.4.0  00:00:29
Oracle Database Java Packages             VALID      10.2.0.4.0  00:00:14
Oracle Text                               VALID      10.2.0.4.0  00:00:21
Oracle XML Database                       VALID      10.2.0.4.0  00:02:02
Oracle Workspace Manager                  VALID      10.2.0.4.3  00:00:43
Oracle Data Mining                        VALID      10.2.0.4.0  00:00:20
OLAP Analytic Workspace                   VALID      10.2.0.4.0  00:00:16
OLAP Catalog                              VALID      10.2.0.4.0  00:00:55
Oracle OLAP API                           VALID      10.2.0.4.0  00:00:43
Oracle interMedia                         VALID      10.2.0.4.0  00:02:24
Spatial                                   VALID      10.2.0.4.0  00:01:34
Oracle Ultra Search                       VALID      10.2.0.4.0  00:00:22
Oracle Expression Filter                  VALID      10.2.0.4.0  00:00:09
Oracle Enterprise Manager                 VALID      10.2.0.4.0  00:01:36
Oracle Rule Manager                       VALID      10.2.0.4.0  00:00:08
.


 Comments   
Comment by ubTools Support [ 15/Jun/08 06:32 PM ]
Compiling DBMS_SQLPA causes the problem. To find the object including OTHER_XML column, ERRORSTACK trace for ORA-904 would be useful. But, since it's a known column of PLAN_TABLE, it's not required while diagnosing the problem.

There were both SYS.PLAN_TABLE as a table and PUBLIC.PLAN_TABLE as a public synonym in the database:

SQL> select owner,object_name,object_type from dba_objects where owner in ('SYS','PUBLIC') and upper(object_name)  like 'PLAN_TABLE%';

OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE
-------------------
PUBLIC
PLAN_TABLE
SYNONYM

SYS
PLAN_TABLE
TABLE

OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE
-------------------

SYS
PLAN_TABLE$
TABLE


SQL> select TABLE_OWNER,TABLE_NAME from dba_synonyms where OWNER='PUBLIC' and SYNONYM_NAME='PLAN_TABLE';

TABLE_OWNER                    TABLE_NAME
------------------------------ ------------------------------
SYS                            PLAN_TABLE$

SQL>

But, not all columns of SYS.PLAN_TABLE table and PUBLIC.PLAN_TABLE synonym are same:

SQL> desc sys.plan_table
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 STATEMENT_ID                                       VARCHAR2(30)
 TIMESTAMP                                          DATE
 REMARKS                                            VARCHAR2(80)
 OPERATION                                          VARCHAR2(30)
 OPTIONS                                            VARCHAR2(255)
 OBJECT_NODE                                        VARCHAR2(128)
 OBJECT_OWNER                                       VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(30)
 OBJECT_INSTANCE                                    NUMBER(38)
 OBJECT_TYPE                                        VARCHAR2(30)
 OPTIMIZER                                          VARCHAR2(255)
 SEARCH_COLUMNS                                     NUMBER
 ID                                                 NUMBER(38)
 PARENT_ID                                          NUMBER(38)
 POSITION                                           NUMBER(38)
 COST                                               NUMBER(38)
 CARDINALITY                                        NUMBER(38)
 BYTES                                              NUMBER(38)
 OTHER_TAG                                          VARCHAR2(255)
 PARTITION_START                                    VARCHAR2(255)
 PARTITION_STOP                                     VARCHAR2(255)
 PARTITION_ID                                       NUMBER(38)
 OTHER                                              LONG

SQL>

SQL> desc sys.plan_table$
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 STATEMENT_ID                                       VARCHAR2(30)
 PLAN_ID                                            NUMBER
 TIMESTAMP                                          DATE
 REMARKS                                            VARCHAR2(4000)
 OPERATION                                          VARCHAR2(30)
 OPTIONS                                            VARCHAR2(255)
 OBJECT_NODE                                        VARCHAR2(128)
 OBJECT_OWNER                                       VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(30)
 OBJECT_ALIAS                                       VARCHAR2(65)
 OBJECT_INSTANCE                                    NUMBER(38)
 OBJECT_TYPE                                        VARCHAR2(30)
 OPTIMIZER                                          VARCHAR2(255)
 SEARCH_COLUMNS                                     NUMBER
 ID                                                 NUMBER(38)
 PARENT_ID                                          NUMBER(38)
 DEPTH                                              NUMBER(38)
 POSITION                                           NUMBER(38)
 COST                                               NUMBER(38)
 CARDINALITY                                        NUMBER(38)
 BYTES                                              NUMBER(38)
 OTHER_TAG                                          VARCHAR2(255)
 PARTITION_START                                    VARCHAR2(255)
 PARTITION_STOP                                     VARCHAR2(255)
 PARTITION_ID                                       NUMBER(38)
 OTHER                                              LONG
 OTHER_XML                                          CLOB
 DISTRIBUTION                                       VARCHAR2(30)
 CPU_COST                                           NUMBER(38)
 IO_COST                                            NUMBER(38)
 TEMP_SPACE                                         NUMBER(38)
 ACCESS_PREDICATES                                  VARCHAR2(4000)
 FILTER_PREDICATES                                  VARCHAR2(4000)
 PROJECTION                                         VARCHAR2(4000)
 TIME                                               NUMBER(38)
 QBLOCK_NAME                                        VARCHAR2(30)

SQL>

Since table access takes precedence on synonym access, SYS.PLAN_TABLE table was used. But, this table doesn't have a column named OTHER_XML, which caused the problem.

After dropping SYS.PLAN_TABLE table, PUBLIC.PLAN_TABLE synonym used:

SQL> drop table sys.plan_table;

Table dropped.

SQL>



SQL> desc plan_table
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 STATEMENT_ID                                       VARCHAR2(30)
 PLAN_ID                                            NUMBER
 TIMESTAMP                                          DATE
 REMARKS                                            VARCHAR2(4000)
 OPERATION                                          VARCHAR2(30)
 OPTIONS                                            VARCHAR2(255)
 OBJECT_NODE                                        VARCHAR2(128)
 OBJECT_OWNER                                       VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(30)
 OBJECT_ALIAS                                       VARCHAR2(65)
 OBJECT_INSTANCE                                    NUMBER(38)
 OBJECT_TYPE                                        VARCHAR2(30)
 OPTIMIZER                                          VARCHAR2(255)
 SEARCH_COLUMNS                                     NUMBER
 ID                                                 NUMBER(38)
 PARENT_ID                                          NUMBER(38)
 DEPTH                                              NUMBER(38)
 POSITION                                           NUMBER(38)
 COST                                               NUMBER(38)
 CARDINALITY                                        NUMBER(38)
 BYTES                                              NUMBER(38)
 OTHER_TAG                                          VARCHAR2(255)
 PARTITION_START                                    VARCHAR2(255)
 PARTITION_STOP                                     VARCHAR2(255)
 PARTITION_ID                                       NUMBER(38)
 OTHER                                              LONG
 OTHER_XML                                          CLOB
 DISTRIBUTION                                       VARCHAR2(30)
 CPU_COST                                           NUMBER(38)
 IO_COST                                            NUMBER(38)
 TEMP_SPACE                                         NUMBER(38)
 ACCESS_PREDICATES                                  VARCHAR2(4000)
 FILTER_PREDICATES                                  VARCHAR2(4000)
 PROJECTION                                         VARCHAR2(4000)
 TIME                                               NUMBER(38)
 QBLOCK_NAME                                        VARCHAR2(30)

SQL>

Applying PatchSet did not give INVALID status:

Component                                Status         Version  HH:MM:SS
Oracle Database Server                    VALID      10.2.0.4.0  00:09:20
JServer JAVA Virtual Machine              VALID      10.2.0.4.0  00:02:56
Oracle XDK                                VALID      10.2.0.4.0  00:00:28
Oracle Database Java Packages             VALID      10.2.0.4.0  00:00:14
Oracle Text                               VALID      10.2.0.4.0  00:00:22
Oracle XML Database                       VALID      10.2.0.4.0  00:02:05
Oracle Workspace Manager                  VALID      10.2.0.4.3  00:00:45
Oracle Data Mining                        VALID      10.2.0.4.0  00:00:21
OLAP Analytic Workspace                   VALID      10.2.0.4.0  00:00:16
OLAP Catalog                              VALID      10.2.0.4.0  00:00:55
Oracle OLAP API                           VALID      10.2.0.4.0  00:00:41
Oracle interMedia                         VALID      10.2.0.4.0  00:02:24
Spatial                                   VALID      10.2.0.4.0  00:01:37
Oracle Ultra Search                       VALID      10.2.0.4.0  00:00:22
Oracle Expression Filter                  VALID      10.2.0.4.0  00:00:09
Oracle Enterprise Manager                 VALID      10.2.0.4.0  00:01:37
Oracle Rule Manager                       VALID      10.2.0.4.0  00:00:08
.
Comment by ubTools Support [ 15/Jun/08 06:34 PM ]
  • Drop SYS.PLAN_TABLE table.
  • Install PatchSet.



[QA-37] "ORA-01187: cannot read from file" in one of the RAC Node. Created: 08/May/08  Updated: 12/May/08

Status: Closed
Project: Questions & Answers
Fix Version/s: None

Type: Oracle - Administration Priority: Major
Reporter: ubTools Support Assignee: ubTools Support
Resolution: Answered Votes: 0

Product Version: Oracle 10g 10.2.0.3
Operating System: Linux
Operating System Version: RHEL 4

 Description   
The one of RAC Nodes encounters the following error codes while no problem occurs on the other node:

From ALERT LOG:

Generic Alert Log Error May 2, 2008 11:21:30 PM ORA-12012: error on auto execute of job 8913
ORA-01187: cannot read from file ORA-01187: cannot read from file 96 because it failed verification tests
ORA-01110: data file 96: '/u64/oradata/DMSDB/LBPRD_IDX_SKU_005DMSDB.dbf'
ORA-06512: at "SYS.PRVT_ADVISOR", line 1624
ORA-06512: at "SYS.DBMS_ADVISOR", line 186
ORA-06512: at "SYS.DBMS_SPACE", line 1347
ORA-06512: at "SYS.DBMS_SPACE", line 1566
because it failed verification tests
Trace File:  /u00/app/oracle/oracle/admin/DMSDB/bdump/dmsdb2_j000_21660.trc

From dmsdb2_j000_21660.trc:

*** 2008-04-07 23:03:34.750
GATHER_STATS_JOB: GATHER_TABLE_STATS('"LOADOWNER"','"MARKEDPRODUCT"','"MARKEDPRODUCT_20080406"', ...)
ORA-01187: cannot read from file 88 because it failed verification tests
ORA-01110: data file 88: '/u64/oradata/DMSDB/MRK_IDX_004DMSDB.dbf'


 Comments   
Comment by ubTools Support [ 08/May/08 12:32 PM ]
From the trace file, the problem can be reproduced by DBMS_STATS.GATHER_TABLE_STATS().

The following strace output will give the system calls:

strace -f -o strace.log sqlplus / as sysdba <<EOF
exec DBMS_STATS.GATHER_TABLE_STATS('<owner>','<tableName>','<partitionName',1,DEGREE=>2);
exit;
EOF
Comment by ubTools Support [ 08/May/08 12:35 PM ]
An excerpt from strace.log that the db files were opened with O_DIRECT flag: 
16822 open("/u51/oradata/DMSDB/system001DMSDB.dbf", O_RDWR|O_SYNC|O_DIRECT|O_LAR
GEFILE) = 14
...
16822 open("/u31/oradata/DMSDB/ctl1DMSDB.ctl", O_RDWR|O_SYNC|O_DIRECT|O_LARGEFIL
E) = 15
16822 open("/u32/oradata/DMSDB/ctl2DMSDB.ctl", O_RDWR|O_SYNC|O_DIRECT|O_LARGEFIL
E) = 16
16822 open("/u33/oradata/DMSDB/ctl3DMSDB.ctl", O_RDWR|O_SYNC|O_DIRECT|O_LARGEFIL
E) = 17
16822 open("/u52/oradata/DMSDB/undotbs002DMSDB2.dbf", O_RDWR|O_SYNC|O_DIRECT|O_L
ARGEFILE) = 18
...
16822 open("/u70/oradata/DMSDB/TEMPDMSDB_002.dbf", O_RDWR|O_SYNC|O_DIRECT|O_LARG
EFILE) = 20
16822 open("/u70/oradata/DMSDB/TEMPDMSDB_002.dbf", O_RDWR|O_DIRECT|O_LARGEFILE)
= 21
...
16822 open("/u52/oradata/DMSDB/sysaux001DMSDB.dbf", O_RDWR|O_SYNC|O_DIRECT|O_LAR
GEFILE) = 28
16822 open("/u51/oradata/DMSDB/system002DMSDB.dbf", O_RDWR|O_SYNC|O_DIRECT|O_LAR
GEFILE) = 29
16822 open("/u55/oradata/DMSDB/undotbs001DMSDB1.dbf", O_RDWR|O_SYNC|O_DIRECT|O_L
ARGEFILE) = 30
16822 open("/u61/oradata/DMSDB/MRK_IDX_001DMSDB.dbf", O_RDWR|O_SYNC|O_DIRECT|O_L
ARGEFILE) = 31
...

The second excerpt from strace.log that the db files were opened without O_DIRECT flag:

16822 open("/u65/oradata/DMSDB/dor_data_200805_001_DMSDB.dbf", O_RDWR|O_SYNC|O_L
ARGEFILE) = 19
16822 open("/u65/oradata/DMSDB/dor_data_200805_002_DMSDB.dbf", O_RDWR|O_SYNC|O_L
ARGEFILE) = 27
...
16822 open("/u64/oradata/DMSDB/MRK_IDX_004DMSDB.dbf", O_RDWR|O_SYNC|O_LARGEFILE)
 = 32
Comment by ubTools Support [ 08/May/08 12:52 PM ]
The customer uses OCFS2.

O_DIRECT flag of open() system call bypasses File System(FS) cache; and DISK-IO occurs between user address space and disk.

OCFS opens dbfiles with O_DIRECT flag to eliminate inconsistency among FS caches of nodes. Since RAC provides consistency among SGAs and there will be no db buffers is FS cache, no consistency problem occurs.

From Ref: Oracle Note:391771.1:

48. Any special flags to run Oracle RAC?
OCFS2 volumes containing the Voting diskfile (CRS), Cluster registry (OCR),
Data files, Redo logs, Archive logs and Control files must be mounted
with the datavolume and nointr mount options.
The datavolume option ensures that the Oracle processes opens these files
with the o_direct flag.
The nointr option ensures that the ios are not interrupted by signals.

# mount -o datavolume,nointr -t ocfs2 /dev/sda1 /u01/db

The customer was not using the datavolume,nointr option. After mounting with the datavolume,nointr, the problem has been solved.




[QA-30] Memory leak on MMNL background process. Created: 16/Jul/07  Updated: 18/Sep/07

Status: Closed
Project: Questions & Answers
Fix Version/s: None

Type: Oracle - Administration Priority: Major
Reporter: ubTools Support Assignee: ubTools Support
Resolution: Answered Votes: 0

Product Version: 10.1.0.3.0
Operating System: Solaris
Operating System Version: 5.10

 Description   

Problem:

The size of MMNL background process is growing, then the server is crashed.

Analysis:

bash-3.00$ ps -ef|grep mmnl
oracle 2250 1 0 Jun 28 ? 12:03 ora_mmnl_bgw
oracle 21397 20996 0 13:31:42 pts/5 0:00 grep mmnl

SQL> select s.sid, n.name,s.value
from v$sesstat s , v$statname n
where s.statistic# = n.statistic#
and n.name like '%memory%'
and s.sid in
(select se.sid from v$session se, v$process pr
where se.paddr=pr.addr and pr.spid=2250)
order by value desc;

SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
1646 session pga memory 463496
1646 session pga memory max 463496
1646 session uga memory 88640
1646 session uga memory max 88640
1646 workarea memory allocated 0
1646 sorts (memory) 0

6 rows selected.

SQL>

bash-3.00$ pmap -x 2250
2250: ora_mmnl_bgw
Address Kbytes RSS Anon Locked Mode Mapped File
0000000100000000 81016 78904 - - r-x-- oracle
000000010501C000 856 592 112 - rwx-- oracle
00000001050F2000 3128 1352 64 - rwx-- [ heap ]
0000000105400000 4190208 1255504 4096 - rwx-- [ heap ]
0000000205000000 3731456 2145424 1138688 - rwx-- [ heap ]
0000000380000000 253952 253952 - 253952 rwxsR [ ism shmi d=0xc ]
0000040000000000 290816 290816 - 290816 rwxsR [ ism shmi d=0xd ]
0000040040000000 290816 290816 - 290816 rwxsR [ ism shmi d=0xe ]
0000040080000000 16 16 - 16 rwxsR [ ism shmi d=0xf ]
FFFFFFFF7B500000 64 24 - - rwx-- [ anon ]
FFFFFFFF7B530000 128 16 - - rw--- [ anon ]
FFFFFFFF7B600000 8 - - - rw-s- dev:291,0 in o:240652
FFFFFFFF7B750000 64 24 16 - rw--- [ anon ]
FFFFFFFF7B760000 64 24 24 - rw--- [ anon ]
FFFFFFFF7B770000 64 56 48 - rw--- [ anon ]
FFFFFFFF7B800000 16 16 - - r-x-- liblgrp.so.1
FFFFFFFF7B904000 8 8 - - rwx-- liblgrp.so.1
FFFFFFFF7BA78000 8 8 - - rwxs- [ anon ]
FFFFFFFF7BB00000 8 8 - - r-x-- libc_psr.so. 1
FFFFFFFF7BC00000 8 8 8 - rwx-- [ anon ]
FFFFFFFF7BD00000 8 8 - - r-x-- libmd5.so.1
FFFFFFFF7BE02000 8 8 - - rwx-- libmd5.so.1
FFFFFFFF7BF00000 8 8 8 - rwx-- [ anon ]
FFFFFFFF7C000000 640 168 - - r-x-- libm.so.2
FFFFFFFF7C19E000 40 24 8 - rwx-- libm.so.2
FFFFFFFF7C200000 8 8 - - r-x-- libkstat.so. 1
FFFFFFFF7C302000 8 8 8 - rwx-- libkstat.so. 1
FFFFFFFF7C400000 32 24 - - r-x-- librt.so.1
FFFFFFFF7C508000 8 8 - - rwx-- librt.so.1
FFFFFFFF7C600000 32 32 - - r-x-- libaio.so.1
FFFFFFFF7C708000 8 8 - - rwx-- libaio.so.1
FFFFFFFF7C800000 8 8 8 - rwx-- [ anon ]
FFFFFFFF7C900000 912 656 - - r-x-- libc.so.1
FFFFFFFF7CAE4000 64 64 64 - rwx-- libc.so.1
FFFFFFFF7CAF4000 8 - - - rwx-- libc.so.1
FFFFFFFF7CB00000 24 16 16 - rwx-- [ anon ]
FFFFFFFF7CC00000 32 16 - - r-x-- libgen.so.1
FFFFFFFF7CD08000 8 8 - - rwx-- libgen.so.1
FFFFFFFF7CE00000 56 32 - - r-x-- libsocket.so .1
FFFFFFFF7CF0E000 16 16 - - rwx-- libsocket.so .1
FFFFFFFF7D000000 688 248 - - r-x-- libnsl.so.1
FFFFFFFF7D1AC000 64 64 - - rwx-- libnsl.so.1
FFFFFFFF7D1BC000 32 8 - - rwx-- libnsl.so.1
FFFFFFFF7D200000 1912 320 - - r-x-- libnnz10.so
FFFFFFFF7D4DC000 632 232 - - rwx-- libnnz10.so
FFFFFFFF7D57A000 8 - - - rwx-- libnnz10.so
FFFFFFFF7D600000 40 16 - - r-x-- libdbcfg10.s o
FFFFFFFF7D708000 8 8 - - rwx-- libdbcfg10.s o
FFFFFFFF7D800000 8488 8200 - - r-x-- libjox10.so
FFFFFFFF7E148000 536 480 - - rwx-- libjox10.so
FFFFFFFF7E200000 8 8 8 - rwx-- [ anon ]
FFFFFFFF7E300000 16 16 - - r-x-- libocrutl10. so
FFFFFFFF7E402000 16 16 - - rwx-- libocrutl10. so
FFFFFFFF7E500000 8 8 8 - rwx-- [ anon ]
FFFFFFFF7E600000 144 40 - - r-x-- libocrb10.so
FFFFFFFF7E722000 8 8 - - rwx-- libocrb10.so
FFFFFFFF7E800000 200 72 - - r-x-- libocr10.so
FFFFFFFF7E930000 16 16 - - rwx-- libocr10.so
FFFFFFFF7EA00000 8 8 - - r-x-- libskgxn2.so
FFFFFFFF7EB00000 8 8 - - rwx-- libskgxn2.so
FFFFFFFF7EC00000 1480 352 - - r-x-- libhasgen10. so
FFFFFFFF7EE70000 72 56 - - rwx-- libhasgen10. so
FFFFFFFF7EE82000 8 - - - rwx-- libhasgen10. so
FFFFFFFF7EF00000 8 8 8 - rwx-- [ anon ]
FFFFFFFF7F000000 8 8 - - r-x-- libskgxp10.s o
FFFFFFFF7F100000 8 8 - - rwx-- libskgxp10.s o
FFFFFFFF7F200000 8 8 - - r-x-- libodmd10.so
FFFFFFFF7F300000 8 8 - - rwx-- libodmd10.so
FFFFFFFF7F400000 8 8 - - r-x-- libdl.so.1
FFFFFFFF7F500000 8 8 8 - rwx-- [ anon ]
FFFFFFFF7F600000 176 176 - - r-x-- ld.so.1
FFFFFFFF7F72C000 16 16 8 - rwx-- ld.so.1
FFFFFFFF7FFF0000 64 48 24 - rw--- [ stack ]
---------------- ---------- ---------- ---------- ----------
total Kb 8859344 4329168 1143232 835600
bash-3.00$

bash-3.00$ truss -p 2250

open("/dev/kstat", O_RDONLY) = 58843
ioctl(58843, KSTAT_IOC_CHAIN_ID, 0x00000000) = 755
ioctl(58843, KSTAT_IOC_READ, "kstat_headers") Err#12 ENOMEM
brk(0x2EBC064D0) = 0
brk(0x2EBC264D0) = 0
ioctl(58843, KSTAT_IOC_READ, "kstat_headers") = 755
brk(0x2EBC264D0) = 0
brk(0x2EBC2A4D0) = 0
ioctl(58843, KSTAT_IOC_READ, "cpu_info0") = 755
ioctl(58843, KSTAT_IOC_READ, "cpu_info1") = 755
ioctl(58843, KSTAT_IOC_READ, "cpu_info2") = 755
ioctl(58843, KSTAT_IOC_READ, "cpu_info3") = 755
ioctl(58843, KSTAT_IOC_READ, "cpu_info8") = 755
ioctl(58843, KSTAT_IOC_READ, "cpu_info9") = 755
ioctl(58843, KSTAT_IOC_READ, "cpu_info10") = 755
ioctl(58843, KSTAT_IOC_READ, "cpu_info11") = 755
ioctl(58843, KSTAT_IOC_READ, "cpu_info512") = 755
pset_bind(PS_QUERY, P_PID, -1, 0xFFFFFFFF7FFFD5AC) = 0
open("/dev/kstat", O_RDONLY) = 58844

 Comments   
Comment by ubTools Support [ 16/Jul/07 05:47 AM ]

Cause:

session pga memory is 463496 BYTE. But, it's too high in OS even if shared segment is substructed:

4329168 - (253952+290816+290816+16)= 3493568 (KB)

3493568*1024 is too high. There is huge memory allocation in HEAP usage.

Oracle opened /dev/kstat to get operating system kernel statistics without closing it before subsequent open. There should be one close() system call for each open() call.

Bug:

ORACLE BUG: 3701351.

Base Bug#3559340 inludes a fix for Oracle 10.1.0.3.

Comment by ubTools Support [ 02/Aug/07 12:50 PM ]
Base Bug#3559340 is fixed in Oracle 10.1.0.4.



[QA-9] How to set an event in other session ? Created: 15/Jul/07  Updated: 16/Sep/07

Status: Closed
Project: Questions & Answers
Fix Version/s: None

Type: Oracle - Administration Priority: Major
Reporter: ubTools Support Assignee: ubTools Support
Resolution: Answered Votes: 0

Product Version: ???
Operating System: Generic

 Description   
How to set an event in other session ?

 Comments   
Comment by ubTools Support [ 15/Jul/07 01:19 PM ]

Answer:

Use SYS.DBMS_SYSTEM.SET_EV() procedure. Here is the specification for this procedure:

 
PROCEDURE SET_EV
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
SI                             BINARY_INTEGER          IN
SE                             BINARY_INTEGER          IN
EV                             BINARY_INTEGER          IN
LE                             BINARY_INTEGER          IN
NM                             VARCHAR2                IN
  • SID: V$SESSION.SID
  • SE: V$SESSION.SERIAL#
  • EV: Event number. For example:
    • 10046: SQL traces.
    • 10053: Optimizer traces.
    • NNN : ORA-NNN errors.
    • 65535: IMMEDIATE traces.
  • LE: Event level. For Event 10046 events:
    • 0: Disable event.
    • 1: PARSE, FETCH, EXEC, EXECUTION PLAN
    • 4: Level 1 + BINDS
    • 8: Level 1 + WAITS
    • 12: Level 4 + Level 8
  • NM: Event name. For example:
    • ERRORSTACK.......: For error stack traces.
    • PROCESSSTATE...: For process states
    • SYSTEMSTATE.......: For System states.
    • ''..................................: For CONTEXT FOREVER.

Sample:

Dumps PROCESSSTATE trace IMMEDIATELY in LEVEL 10:

 
SQL> exec dbms_system.set_ev(8,1056,65535,10,'PROCESSSTATE');

Dumps ERRORSTACK trace in LEVEL 3 on ORA-942 error:

 
SQL> exec dbms_system.set_ev(8,1060,942,3,'ERRORSTACK');

Dumps Event 10046 trace in LEVEL 8 for CONTEXT FOREVER:

 
SQL> exec dbms_system.set_ev(8,1060,10046,8,'');



Generated at Thu Apr 25 15:39:49 UTC 2024 using JIRA Standard Edition, Version: 3.12.3-#302.