Tracing ORA Error Code At System/Session Level

Real Scenario: Development team ran the Job and the job failed with below error

ORA-01031: insufficient privileges

Logs were created for the failed job; but there were no trace for which object it failed.

By looking at the error we cant say which object is lacking grants so I started my journey as doing simple check(Checking Grants on Objects) but since schema was big and the job logs didn't mention for which object it failed; it was very difficult to trace out the root cause of the issue.
           
Finally, I came up with the idea by tracing the ORA errors at database level(you may use for session level as well).

As a sys user I fired below command
alter system set events '1031 trace name errorstack level 3';

Once you execute this command as SYS/SYSTEM, anytime ORA-1031 is issued by any application, the oracle db alert log will record this error with a full path of trace and rather a trace file will be generated in trace directory to give more details about the error.

So going forward I asked dev team to run the Job again and parallel I checked alert log. What wonder I got the trace file and the culprit statement.

So I provided necessary grants and then asked to ran the job again and finally it passed successfully.

 Note: You can put any ORA error code in this command by removing the 0 prefix from the error code.

Shared Memory Allocation In 11.1.0.7 and 11.2.0.3

SHM is key feature for any software on any of the host server. Oracle is doing the same. When Oracle initiates; Shared memory segments are created and the allocation of shared memory depends on Kernel parameter (i.e. Host server dependent).

Just to give you brief how Oracle and Host server do the same 

SHARED MEMORY ALLOCATION

1. One-segment

2. Contiguous multi-segment

3. Non-contiguous multi-segment

When attempting to allocate and attach shared memory for the SGA, it will attempt each one, in the above order, until one succeeds or raises an ORA error. On other, non-fatal, errors, Oracle simply cleans up and tries again using the next memory model. The entire SGA must fit into shared memory, so the total amount of shared memory allocated under any model will be equal of the size of the SGA (SGA-SIZE).

1. One-segment: - The one-segment model is the simplest and first model tried. In this model, the SGA resides in only one shared memory segment. Oracle attempts to allocate and attach one shared memory segment of size equal to total size of the SGA. However, if the SGASIZE is larger than the configured SHMMAX, this will obviously fail. In this case, the SGA will need to be placed in multiple shared memory segments, and Oracle proceeds to the next memory model for the SGA.

With multiple segments there are two possibilities. The segments can be attached contiguously, so that it appears to be one large shared memory segment, or non-contiguously, with gaps between the segments.

2. Contiguous multi-segment - In the contiguous segment model, Oracle simply divides the SGA into SGASIZE/SHMMAX (rounded down) segments of size SHMMAX plus another segment of size SGASIZE modulo SHMMAX

3. Non- contiguous multi-segment : Once the number of segments and their sizes is determined, Oracle then allocates and attaches the segments one at a time; first the fixed and variable portion segment(s), then the redo block buffer segment(s), then the db block buffer segment(s). They will be attached non-contiguously,

At this point, we have either attached the entire SGA or returned an ORA error. The total size of segments attached is exactly SGASIZE; no space is wasted. Once Oracle has the shared memory attached, Oracle proceeds to allocating the semaphores it requires.

So what will be the case with diferent Oracle versions isn’t it going to be same or some different measures are in mind; so will test here.

There appear to be a change in the way shared memory segments are allocated in 11.2.0.3 then 11.2.0.2/11.1.0.7; I am just concentrating on SHM and not semaphore; do keep in mind semaphore is must for any software on host boxes.

So how to check what shared memory is assigned to Oracle; use below method

 > $ORACLE_HOME/bin/sysresv (this command shows system reserved memory; it also show Semaphore used 

but I haven’t show here as will discuss only SHM)

IPC Resources for ORACLE_SID "ORCL":

Shared Memory:

ID                     KEY

5865477         0x00cebc80

So from above we got the ID and the address of the memory; so now question arises how much bytes are assigned to this SHM; so we can tackle this situation by 

ipcs -m | grep -i oracle( this will show details about each Instance on the server for 11.1.0.7/11.2.0.2 this command will show only one line for each instance on the box)

> ipcs -m | grep -i oracle

0x00cebc80 5865477    oracle    600        1428160512 45

0x02599c90 5898246    oracle    640        161480704  20

0x273558c0 5931015    oracle    640        161480704  16

0xda9054ac 5963784    oracle    640        161480704  18

0xb2c9a108 5996553    oracle    600        161480704  18

0x4f214214 6029322    oracle    640        161480704  18

0x0569c9b4 6062091    oracle    640        266338304  18

0xe373e898 6094860    oracle    600        1260388352 43

0x1707a15c 6127629    oracle    600        161480704  18

0x618f202c 6160398    oracle    640        266338304  18

0x46ec882c 6193167    oracle    640        266338304  18

0xbc5805b4 6225936    oracle    640        1260388352 43

0x603098c8 6258705    oracle    640        266338304  18

0x2a4585e4 6291474    oracle    640        266338304  36

Above is the result which shows 14 different outputs with oracle as owner; so does it mean each oracle is mapping to each distinct instance; the Answer is Yes; how

>ps -ef | grep pmon

oracle    5117     1  0 Dec09 ?        00:01:04 ora_pmon_ORCL

oracle    5297     1  0 Dec09 ?        00:00:01 ora_pmon_ORCL1

oracle    5385     1  0 Dec09 ?        00:00:01 ora_pmon_ORCL2

oracle    5471     1  0 Dec09 ?        00:00:01 ora_pmon_ORCL3

oracle    5562     1  0 Dec09 ?        00:00:01 ora_pmon_ORCL4

oracle    5650     1  0 Dec09 ?        00:00:01 ora_pmon_ORCL5

oracle    5742     1  0 Dec09 ?        00:00:01 ora_pmon_ORCL6

oracle    5833     1  0 Dec09 ?        00:00:07 ora_pmon_ORCL6

oracle    5971     1  0 Dec09 ?        00:00:01 ora_pmon_ORCL7

oracle    6064     1  0 Dec09 ?        00:00:01 ora_pmon_ORCL8

oracle    6154     1  0 Dec09 ?        00:00:03 ora_pmon_ORCL9

oracle    6246     1  0 Dec09 ?        00:00:06 ora_pmon_ORCL10

oracle    6392     1  0 Dec09 ?        00:00:01 ora_pmon_ORCL11

oracle    6482     1  0 Dec09 ?        00:00:05 ora_pmon_ORCL12

so it confirmed that each oracle output is mapped to distinct instance.

Now we need to check what ID ORCL is using; for this we will trace back to $ORACLE_HOME/bin/sysresv result it shows our ID is  5865477. So now will take this as input and use it as below

> ipcs -m | grep -i 5865477

0x00cebc80 5865477    oracle    600        1428160512 453

So below marked is the SHM bytes used for the Instance 

But one question comes to my mind here is that which algorithm Oracle used for this instance; so answer is One-segment but why

SQL> show parameter sga

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

sga_max_size                         big integer 1360M

Coz SGA is less than the one SHM on the host box so chunk of one memory will suffice the SGA target.

Same result will be available for 11.2.0.2 also;

Now will see is there any difference in 11.2.0.3;

> echo $ORACLE_HOME

/ora/oracle/11.2.0.3

So will start with shared memory segment on Host boxes allocation

> ipcs -m | grep -i oracle

0x00000000 983044     oracle     640        16777216   51

0x00000000 1015821    oracle     640        1207959552 51

0x6d7bc5b4 1048590    oracle     640        2097152    51

0x00000000 1114127    oracle     640        16777216   51

0x00000000 1146896    oracle     640        1207959552 51

0x925781e4 1179665    oracle     640        2097152    51

0x00000000 1245202    oracle     640        16777216   51

0x00000000 1277971    oracle     640        1207959552 51

0x48a00984 1310740    oracle     640        2097152    51

0x00000000 1376277    oracle     640        16777216   51

0x00000000 1409046    oracle     640        1207959552 51

0xa4c55ffc 1441815    oracle     640        2097152    51

0x00000000 1507352    oracle     640        16777216   51

0x00000000 1540121    oracle     640        1207959552 51

0x5b0de79c 1572890    oracle     640        2097152    51

0x00000000 1638427    oracle     640        16777216   51

0x00000000 1671196    oracle     640        1207959552 51

0x7fe9a3cc 1703965    oracle     640        2097152    51

0x00000000 1769502    oracle     640        16777216   47

0x00000000 1802271    oracle     640        1207959552 47

0x36322b6c 1835040    oracle     640        2097152    47

0x00000000 1900577    oracle     640        16777216   47

0x00000000 1933346    oracle     640        1207959552 47

0xadfc4f08 1966115    oracle     640        2097152    47

0x00000000 2031652    oracle     640        16777216   47

0x00000000 2064421    oracle     640        1207959552 47

0x6444d6a8 2097190    oracle     640        2097152    47

0x00000000 2162727    oracle     640        16777216   47

0x00000000 2195496    oracle     640        1207959552 47

0x2bd108c0 2228265    oracle     640        2097152    47

0x00000000 2293802    oracle     640        16777216   47

0x00000000 2326571    oracle     640        1207959552 47

0xdf2c04ac 2359340    oracle     640        2097152    47

0x00000000 2424877    oracle     640        16777216   47

0x00000000 2457646    oracle     640        1207959552 47

0x06f54c90 2490415    oracle     640        2097152    47

So it’s almost 36 rows; so is this same as which we saw for 11.1.0.7/.2? Is each row point to different instance? the Answer is NOOOOOOOOOOOOOOO;

11.2.0.3 uses multi-segment in any case.

Instance on this machine

oracle    6272     1  0 Dec09 ?        00:00:30 ora_pmon_TEST1

oracle    6468     1  0 Dec09 ?        00:00:30 ora_pmon_TEST2

oracle    6664     1  0 Dec09 ?        00:00:29 ora_pmon_TEST3

oracle    6861     1  0 Dec09 ?        00:00:30 ora_pmon_TEST4

oracle    7433     1  0 Dec09 ?        00:00:30 ora_pmon_TEST5

oracle    7656     1  0 Dec09 ?        00:00:30 ora_pmon_TEST6

oracle    7843     1  0 Dec09 ?        00:00:30 ora_pmon_TEST7

oracle    8037     1  0 Dec09 ?        00:00:29 ora_pmon_TEST8

oracle    8216     1  0 Dec09 ?        00:00:30 ora_pmon_TEST9

oracle    8415     1  0 Dec09 ?        00:00:30 ora_pmon_TEST10

oracle    8610     1  0 Dec09 ?        00:00:29 ora_pmon_TEST11

oracle    8793     1  0 Dec09 ?        00:00:29 ora_pmon_TEST12

above are  12 instance; which shows 1 instance mapped to 3 SHM segments; which we can easily see below

> $ORACLE_HOME/bin/sysresv

IPC Resources for ORACLE_SID "TEST" :

Shared Memory:

ID              KEY

2424877         0x00000000

2457646         0x00000000

2490415         0x06f54c90

Oracle Instance alive for sid "TEST"

SQL> sho parameter sga

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

sga_max_size                         big integer 1168M

> ipcs -m | grep -i 2424877

0x00000000 2424877    oracle     640        16777216   47

> ipcs -m | grep -i 2457646

0x00000000 2457646    oracle     640        1207959552 47

> ipcs -m | grep -i 2490415

0x06f54c90 2490415    oracle     640        2097152    47

Highlighted is the byte used; it comes just more than the SGA allocated to TEST.

So this clearly indicates that even with less SGA the memory is divided into multiple segments.

IMP_FULL_DATABASE role/privilege Can Be Dangerous At Times

Recently faced an issue where an Application developer as per one request asked to revert dml permissions from the table; they just wanted "select only privilege"(Only for read purpose.).

Did the same; removed the dml privileges from the public role as well as from the schema which was using that table via synonym; but still they complained that they are able to update the table from other schema.

Checked all privileges and roles but it was select only; anyhow still they were able to update the table.

Table name was TEMP

Select Privilege from dba_tab_privs where table_name='TEMP';

PRIVILEGE

----------------------------------------

SELECT

So why to go by theory; going ahead tested one scenario

SQL> create user part1 identified by part1;

User created.

SQL> create user part identified by part ;

User created.

SQL> grant connect, resource to part1, part;

Created table TEMP in part schema and created the synonym in schema part1.

SQL> conn / as sysdba

Connected.

SQL> grant CREATE SYNONYM to part1;

Grant succeeded.

SQL> conn part1/part1

Connected.

SQL> create or replace synonym temp for part.temp;

SQL> conn part/part

Connected.

SQL> grant select on temp to part1;

SQL> conn part1/part1

SQL> update temp

set code = 'ABCD'

where emp_no = '1020';  

update temp

       *

ERROR at line 1:

ORA-01031: insufficient privileges

And above is usual behavior and expected one; that with select privilege one cannot fire dml; so curiosity takes me to other side that I checked all the roles provided to the original schema(which tester tested) and they were fine too except ”IMP_FULL_DATABASE” ;  during troubleshooting I found out that IMP_FULL_ DATABASE contains( INSERT ANY TABLE/ UPDATE ANY TABLE) kind of privileges.

So not going by the theory

Logged in as sys and granted synonym user part1 with IMP_FULL_DATABASE

SQL> conn / as sysdba

Connected.

SQL> grant imp_full_database to part1;

Grant succeeded.

SQL> conn part1/part1

Connected.

SQL> update temp

set code = 'ABCD'

where emp_no = '1020';

1 row updated.

SQL> roll;

Rollback complete.

So it is clear here that having imp_full_database one can easily update the table.

Let’s revert it again

QL> conn / as sysdba

Connected.

SQL> revoke imp_full_database from part1;

Revoke succeeded.

SQL> conn part1/part1

Connected.

SQL> update temp

set code = 'ABCD'

where emp_no = '1020';

update temp

       *

ERROR at line 1:

ORA-01031: insufficient privileges

So please make sure that you have to be very much aware while providing these kinds of privileges/roles to the user.