11gR2

Beware of loopback MTU size with RAC on Oracle Linux 7

By | May 12th, 2018 | Category: 11gR2, Linux, Oracle Database | 7 comments

I recently had to troubleshoot a hung instance in a 2 node RAC system. 4 months earlier, the system was reinstalled in a rolling fashion due to the requirement of Linux Upgrade from Oracle Linux 5 to Oracle Linux 7. This was required because of lack of certification for a storage migration to an AllFlash Storage. The system has been stable when running with Oracle Linux 5 for several years. Around 4 months after the reinstallation, one node got hung with and traces showed these error messages:

1
2
3
4
5
6
7
8
9

Mon Feb 26 08:53:37 2018
skgxpvfynet: mtype: 61 process 15801 failed because of a resource problem in the OS. The OS has most likely run out of buffers (rval: 4)
Errors in file /u01/app/oracle/diag_p/diag/rdbms/prod/PROD2/trace/PROD2_ora_15801.trc (incident=480004):
ORA-00603: ORACLE server session terminated by fatal error
ORA-27504: IPC error creating OSD context
ORA-27300: OS system dependent operation:sendmsg failed with status: 105
ORA-27301: OS failure message: No buffer space available
ORA-27302: failure occurred at: sskgxpsnd2
Incident details in: /u01/app/oracle/diag_p/diag/rdbms/prod/PROD2/incident/incdir_480004/PROD2_ora_15801_i480004.trc

This was strange because OS /proc/meminfo was showing huge amounts of free memory for this physical host with 512GB of RAM.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

[root@node17 ~]# cat /proc/meminfo 
zzz ***Mon Feb 26 08:53:09 CET 2018
MemTotal:       528028424 kB
MemFree:        14593828 kB
MemAvailable:   78305772 kB
Buffers:        28009752 kB
Cached:         46896496 kB
SwapCached:            0 kB
Active:         22627436 kB
Inactive:       66945168 kB
Active(anon):   14315300 kB
Inactive(anon):  2105748 kB
Active(file):    8312136 kB
Inactive(file): 64839420 kB
Unevictable:      363996 kB
Mlocked:          364020 kB
SwapTotal:      33554428 kB
SwapFree:       33554428 kB
Dirty:               404 kB
Writeback:             0 kB
AnonPages:      15768480 kB
Mapped:           709896 kB
Shmem:            945384 kB
Slab:            2462644 kB
SReclaimable:    2092232 kB
SUnreclaim:       370412 kB
KernelStack:       28336 kB
PageTables:       395568 kB
NFS_Unstable:          0 kB
Bounce:                0 kB
WritebackTmp:          0 kB
CommitLimit:    87853440 kB
Committed_AS:   22087384 kB
VmallocTotal:   34359738367 kB
VmallocUsed:     1106260 kB
VmallocChunk:   34085810172 kB
HardwareCorrupted:     0 kB
AnonHugePages:         0 kB
CmaTotal:          16384 kB
CmaFree:               0 kB
HugePages_Total:   204800
HugePages_Free:    20214
HugePages_Rsvd:     6266
HugePages_Surp:        0
Hugepagesize:       2048 kB
DirectMap4k:    36827756 kB
DirectMap2M:    78444544 kB
DirectMap1G:    423624704 kB

Oracle Support then referred to this MOS Note:

ORA-27301: OS Failure Message: No Buffer Space Available / ORA-27302: failure occurred at: sskgxpsnd2 ( Doc ID 2322410.1 )

It turned out that on systems with a lot of physical memory and on Oracle Linux 7, the MTU size of loopback adapter lo0 has to be reduced from the default value of 64k to 16436 bytes to avoid memory fragmentation in RAC. The note also mentioned that the parameter vm.min_free_kbytes should be set to physmem * 0,004 * (here 2). This requirement was already considered during installation of the system, so the only thing missing was the MTU size.

I was very surprised that neither Cluster Verification Utility (CVU), nor orachk in most recent version did catch this problem at the point of installation. In my opinion, if default value of MTU size of loopback interface on Oracle Linux 7 has the potential to cause an outage, then this must be pre-checked by CVU at installation time or at least integrated into orachk. Unfortunately, this is not the case. It seems that in July we will know if the on-prem release 18.3.0 eventually will catch and enforce this configuration requirement during installation time.


BAAS: Battle Against Archiver Stuck – ALTERNATE Archive Location

By | April 27th, 2018 | Category: 10g, 11g, 11gR2, 12cR1, 12cR2, Oracle Database | No Comments »

If you are managing volatile systems, you might have encountered Archiver-Stuck in the past.

1
2

0RA-00257:archiver error, connect internal only until freed
ORA-16014:log 2 sequence# 1789 not archived, no available destinations

If this is the case, you might be interested in a rarely used feature called “ALTERNATE Archive Locations”. This is a second independent archive log destination, that is only used in case the primary destination is failing (full). In that case, the dest_2 is automatically enabled, preventing stuck database. As soon as the archivelog backup has cleaned up the full DEST_1 destination, there is an AUTOMATIC FALLBACK to the DEST_1 destination.

1
2
3
4

ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/disk1 MANDATORY MAX_FAILURE=1 ALTERNATE=LOG_ARCHIVE_DEST_2';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ALTERNATE;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='LOCATION=/disk2 MANDATORY ALTERNATE=LOG_ARCHIVE_DEST_1';

Testing:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

Thread 1 advanced to log sequence 174 (LGWR switch)
  Current log# 3 seq# 174 mem# 0: /u01/app/oracle/oradata/CDB122/redo03.log
2018-04-27T17:18:47.685736+02:00
ARC3: Encountered disk I/O error 19502
2018-04-27T17:18:47.685800+02:00
ARC3: Closing local archive destination LOG_ARCHIVE_DEST_1 '/disk1/1_173_971869447.dbf' (error 19502) (CDB122)
2018-04-27T17:18:47.687074+02:00
Errors in file /u01/app/oracle/diag/rdbms/cdb122/CDB122/trace/CDB122_arc3_31260.trc:
ORA-27072: File I/O error
Additional information: 4
Additional information: 65536
Additional information: 315392
ORA-19502: write error on file "/disk1/1_173_971869447.dbf", block number 65536 (block size=512)
2018-04-27T17:18:47.816938+02:00
Errors in file /u01/app/oracle/diag/rdbms/cdb122/CDB122/trace/CDB122_arc3_31260.trc:
ORA-19502: write error on file "/disk1/1_173_971869447.dbf", block number 65536 (block size=512)
ORA-27072: File I/O error
Additional information: 4
Additional information: 65536
Additional information: 315392
ORA-19502: write error on file "/disk1/1_173_971869447.dbf", block number 65536 (block size=512)
ARC3: I/O error 19502 archiving log 2 to '/disk1/1_173_971869447.dbf'

After the error, archivelogs are placed in /dir2 directory and as soon as space is available again, archive destination is switched back to /dir1 automatically.

Archiver Stuck never again, right?


Blocking Session not visible in v$ Views

By | February 16th, 2018 | Category: 11gR2, JDBC, Oracle Database | No Comments »

I had to troubleshoot an issue on a company ERP systems database, where several sessions were waiting for ‘enq: TX row lock contention’. It was a table containing print server nodes and this caused no more printouts. Usually troubleshooting locking issues is quite straightforward in recent versions of oracle database, but this time, the column “v$session.blocking_session” was not populated and there was no entry in v$lock for the blocker. This was strange, so I involved Oracle Support. They requested a system state dump.

SQL> oradebug setmypid
SQL> oradebug unlimit;
SQL> select * from dual;
SQL> oradebug dump systemstate 258
SQL> oradebug dump systemstate 258

In the system state dump, there was a reference for the hanging session:

Waiter:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62

  ----------------------------------------
  SO: 0x000000075C68F8B8, type: 2, owner: 0x0000000000000000, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
   proc=0x000000075C68F8B8, name=process, file=ksu.h LINE:12740, pg=0
  (process) Oracle pid:161, ser:247, calls cur/top: 0x000000075BC6A370/0x000000075BC6A370
            flags : (0x0) -
            flags2: (0x0),  flags3: (0x10) 
            intr error: 0, call error: 0, sess error: 0, txn error 0
            intr queue: empty
    ksudlp FALSE at location: 0
  (post info) last post received: 0 0 0
              last post received-location: No post
              last process to post me: none
              last post sent: 0 0 0
              last post sent-location: No post
              last process posted by me: none
    (latch info) wait_event=0 bits=0x0
    Process Group: DEFAULT, pseudo proc: 0x000000076068CE68
    O/S info: user: SYSTEM, term: HOSTNAME, ospid: 4600 
    OSD pid info: Windows thread id: 4600, image: ORACLE.EXE (SHAD)
    Short stack dump: 
ksedsts()+585<-ksdxfstk()+44<-ksdxcb()+2402<-ssthreadsrgruncallback()+629<-OracleOradebugThreadStart()+829
<-0000000076D559CD<-0000000076E8A561<-0000000076EABD7A<-000007FEFCE910AC<-skgpwwait()+182<-ksliwat()+2459
<-kslwaitctx()+184<-ksqcmi()+7848<-ksqgtlctx()+6814<-ksqgelctx()+829<-ktuGetTxForXid()+190<-ktcwit1()+526
<-kdddgb()+9636<-kddlkr()+284<-qerfuProcessFetchedRow()+1019<-__PGOSF308_qerfuLockingRowProcedure()+89
<-qertbFetchByRowID()+2708<-qerfuStart()+590<-selexe0()+3495<-opiexe()+22377<-kpoal8()+2624<-opiodr()+1646
<-ttcpip()+1481<-opitsk()+2166<-opiino()+1246<-opiodr()+1646<-opidrv()+862<-sou2o()+98<-opimai_real()+158
<-opimai()+191<-OracleThreadStart()+724<-0000000076D559CD<-0000000076E8A561
01-04-2018 21:16:06.636
    ----------------------------------------
    SO: 0x00000007606D9B88, type: 4, owner: 0x000000075C68F8B8, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
     proc=0x000000075C68F8B8, name=session, file=ksu.h LINE:12748, pg=0
    (session) sid: 68 ser: 20263 trans: 0x0000000755DB9838, creator: 0x000000075C68F8B8
              flags: (0x41) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
              flags2: (0x40009) -/-/INC
              DID: , short-term DID: 
              txn branch: 0x0000000000000000
              edition#: 100              oct: 3, prv: 0, sql: 0x00000007708AA2E8, psql: 0x000000076BDCDED8, user: 0/SYS
    ksuxds FALSE at location: 0
    service name: SYS$USERS
    client details:
      O/S info: user: username, term: hostname, ospid: 5968:4960
      machine: hostname program: sqlplus.exe
      application name: sqlplus.exe, hash value=254292535
    Current Wait Stack:
     0: waiting for 'enq: TX - row lock contention'
        name|mode=0x54580006, usn<<16 | slot=0x130009, sequence=0xb394f
        wait_id=10 seq_num=11 snap_id=1
        wait times: snap=8 min 15 sec, exc=8 min 15 sec, total=8 min 15 sec
        wait times: max=infinite, heur=8 min 15 sec
        wait counts: calls=166 os=166
        in_wait=1 iflags=0x15a0
    Wait State:
      fixed_waits=0 flags=0x22 boundary=0x0000000000000000/-1
...
      ----------------------------------------
      SO: 0x0000000778A7FCA8, type: 8, owner: 0x000000075BC6A370, flag: INIT/-/-/0x00 if: 0x1 c: 0x1
       proc=0x000000075C68F8B8, name=enqueue, file=ksq1.h LINE:380, pg=0
      (enqueue) TX-00130009-000B394F	DID: 0001-00A1-0003F59C
      lv: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00  res_flag: 0x6
      req: X, lock_flag: 0x10, lock: 0x0000000778A7FD00, res: 0x0000000760A2BD80
      own: 0x00000007606D9B88, sess: 0x00000007606D9B88, proc: 0x000000075C68F8B8, prv: 0x0000000760A2BDA0
    ----------------------------------------

In line 60 you can see that the session is requesting an eXclusive lock for resource res: 0x0000000760A2BD80. When searching for the next appearance of res: 0x0000000760A2BD80, we find a reference to the exclusive enqueue after the last process in a section PSEUDO PROCESS / DETACHED BRANCHES.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

PSEUDO PROCESS for group DEFAULT:
  ----------------------------------------
  SO: 0x000000076068CE68, type: 2, owner: 0x0000000000000000, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
   proc=0x000000076068CE68, name=process, file=ksu.h LINE:12740, pg=0
  (process) Oracle pid:0, ser:0, calls cur/top: 0x0000000000000000/0x0000000000000000
            flags : (0x20) PSEUDO
            flags2: (0x0),  flags3: (0x0) 
            intr error: 0, call error: 0, sess error: 0, txn error 0
            intr queue: empty
    ksudlp FALSE at location: 0
  (post info) last post received: 0 0 0
              last post received-location: No post
              last process to post me: none
              last post sent: 0 0 0
              last post sent-location: No post
              last process posted by me: none
    (latch info) wait_event=0 bits=0x0
    Process Group: DEFAULT, pseudo proc: 0x000000076068CE68
    O/S info: user: , term: , ospid:  (DEAD)
    OSD pid info: Windows thread id: 0, image: PSEUDO
  PSO child state object changes :
Dump of memory from 0x00000007605E5698 to 0x00000007605E58A0
7605E5690                   00000000 00000000          [........]
7605E56A0 00000000 00000000 00000000 00000000  [................]
  Repeat 31 times
BEGIN DUMP RESERVED PROCESSES
END DUMP RESERVED PROCESSES
DETACHED BRANCHES:
----------------------------------------
SO: 0x0000000755E7C0F0, type: 58, owner: 0x0000000000000000, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
 proc=0x0000000000000000, name=branch, file=ktccts.h LINE:420, pg=0
(branch) trn = 0x0000000000000000, flg = 0x40, state = 0x00 bno=0 ser=0 evt=0
..
  SO: 0x0000000755E2B378, type: 58, owner: 0x0000000755E7C0F0, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
   proc=0x000000077860EB80, name=branch, file=ktccts.h LINE:420, pg=0
  (branch) trn = 0x0000000755E01068, flg = 0x41090, state = 0x00 bno=1 ser=10 evt=10
   creator = 0x00000007607940E8 uid = 0x42 serial# = 7929 ttl = 0x15180 dtm = 0x5a4db9f5
   2PCrole = (nch = 0x0 flg = 0x0) DTP svc = 0x773ffc978
...
    SO: 0x0000000755E01068, type: 56, owner: 0x0000000755E2B378, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
     proc=0x000000077860EB80, name=transaction, file=ktccts.h LINE:410, pg=0
    (trans) flg = 0x00401e03, flg2 = 0x04080000, flg3 = 0x00000000, prx = 0x0000000000000000, ros = 2147483647, crtses=0x00000007607940E8
...
    (enqueue) TX-00130009-000B394F	DID: 0001-00E6-000010CD
    lv: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00  res_flag: 0x6
    mode: X, lock_flag: 0x0, lock: 0x0000000755E010E0, res: 0x0000000760A2BD80
    own: 0x0000000755E01068 (TXN), sess: 0x0000000000000000, prv: 0x0000000760A2BD90

The SEV1 support engineer could not be convinced that the entry DETACHED BRANCHES has nothing to do with the last session listed in the system state dump. I delivered multiple system state dumps and he insisted that the lock was always held by the very last session of the system state dump – what a coincidence. He just scrolled up from the line 46 until the first occurence of “(session)” and was convinced to have found the blocker, not realizing that he overjumped the sections “DETACHED BRANCHES”/”PSEUDO PROCESS for group DEFAULT”.

After some research I found this note:

Solving locking problems in a XA environment ( Doc ID 1248848.1 )

This note explains that with XA (distributed transactions), e.g. DB Links, there can be situations where blocker is not visible. Unfortunately, the symptoms disappeared from the production system before the diagnostic steps from the MOS Note could be tried. Frequently, when there is an issue that remains unsolved, it comes back at a later point and so it was in this case. This time, I was armed with knowledge of the MOS note 1248848.1 and could use the XA rollback script provided there to free up the “session-less” blocking lock.

No entry in DBA_2PC_PENDING but the Query from MOS Note shows Active XA Trans:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49

select --+ ORDERED
'----------------------------------------'||'
Curent Time : '|| substr(to_char(sysdate,' HH24.MI.SS'),1,22) ||'
'||'TX start_time: '||t.KTCXBSTM||'
'||'FORMATID: '||g.K2GTIFMT ||'
'||'GTXID: '||g.K2GTITID_EXT ||'
'||'Branch: '||g.K2GTIBID ||'
Local_Tran_Id ='||substr(t.KXIDUSN||'.'||t.kXIDSLT||'.'||t.kXIDSQN,1,15)||'
'||'KTUXESTA='|| x.KTUXESTA ||'
'||'KTUXEDFL='|| x.KTUXECFL ||'
Lock_Info: ID1: ' || ((t.kXIDUSN*64*1024)+ t.kXIDSLT)
||' ID2: '|| t.kXIDSQN
XA_transaction_INFO
from x$k2gte g, x$ktcxb t, x$ktuxe x
where g.K2GTDXCB =t.ktcxbxba and
x.KTUXEUSN = t.KXIDUSN(+) and
x.KTUXESLT = t.kXIDSLT(+) and
x.KTUXESQN =t.kXIDSQN(+);
 
"----------------------------------------
Curent Time : 11.35.27
TX start_time: 02/05/18 15:35:17
FORMATID: 131075
GTXID: 312D613331303063613A653165623A35613732336438373A31326335323962
Branch: 613331303063613A653165623A35613732336438373A31326335326166
Local_Tran_Id =25.18.61640
KTUXESTA=ACTIVE
KTUXEDFL=NONE
Lock_Info: ID1: 1638418 ID2: 61640"
"----------------------------------------
Curent Time : 11.35.27
TX start_time: 02/05/18 15:35:17
FORMATID: 131075
GTXID: 312D613331303063613A653165623A35613732336438373A31326335326135
Branch: 613331303063613A653165623A35613732336438373A31326335326165
Local_Tran_Id =16.26.2292874
KTUXESTA=ACTIVE
KTUXEDFL=NONE
Lock_Info: ID1: 1048602 ID2: 2292874"
"----------------------------------------
Curent Time : 11.35.27
TX start_time: 02/05/18 15:35:17
FORMATID: 131075
GTXID: 312D613331303063613A653165623A35613732336438373A31326335326161
Branch: 613331303063613A653165623A35613732336438373A31326335326232
Local_Tran_Id =3.30.3767984
KTUXESTA=ACTIVE
KTUXEDFL=NONE
Lock_Info: ID1: 196638 ID2: 3767984"

This output proved that it was CASE 2: No Entry in dba_2pc_pending und Status ACTIVE. Then I could successfully avoid an instance restart and rollback the global transaction to free up the locks.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

set JAVA_HOME=c:\oracle\product\11.2.0.4\dbhome_1\jdk\jre
set CLASSPATH=c:\dba\scripts\;c:\oracle\product\11.2.0.4\dbhome_1\jdbc\lib\ojdbc5.jar:%CLASSPATH%
C:\DBA\scripts>c:\oracle\product\11.2.0.4\dbhome_1\jdk\jre\bin\java XA_rb jdbc:oracle:thin:@hostname:1521:PROD -ROLLBACK 25 18 61640
++ URL: jdbc:oracle:thin:@hostname:1521:PROD
++ Rollback of Local_tran_ID : 25.18.61640
++ got XA resource handle
++ got Connection handle
++ SQL : select g.K2GTIFMT, g.K2GTITID_EXT, g.K2GTIBID, rawtohex( g.K2GTITID_EXT), rawtohex(g.K2GTIBID) from sys.vw_x$k2gte g, sys.vw_x$ktcxb t, sys.vw_x$ktuxe x where g.K2GTDXCB =t.KTCXBXBA and x.KTUXEUSN
SLT(+) and x.KTUXESQN =t.KXIDSQN(+) and t.KXIDUSN=25 and t.kXIDSLT=18 and t.kXIDSQN= 61640
++ Getting XID for Local_Tran_ID: 25.18.61640
+ Found global XID: -->
++ Format Id: 131075
++ Group Id: 312D613331303063613A653165623A35613732336438373A31326335323962
++ Branch Id: 613331303063613A653165623A35613732336438373A31326335326166
 
-> Would your really like to continue (yes or no):
yes
... doing Rollback
 
... Rollback done !

With Redo Log Mining for the transaction ID, we could get some minor details about the session which initially opened this global transaction.

So whenever you find the strange situation that sessions are blocked on ‘enq: TX – row lock contention’ but there is no blocker in v$session blocking_session column or no entry of a session holding the lock in v$lock, then this MOS Note might be helpful in cleaning up the lock. Sadly, another case where even SEV1 support was no help at all. I have to mention however, that i recently had a SEV1 issue with an excellent and ambitious engineer. So there is still hope…


ORA-4031 Troubleshooting – Issue with durations in 11gR2

By | September 3rd, 2017 | Category: 11g, 11gR2, Oracle Database | No Comments »

I recently had to troubleshoot an ORA-4031 issue at a client site. The issue reappeared 3 times within 2 months and only after escalating the SR to SEV1 and being quite persistent for an explanation, the second engineer attempting to solve the issue finally got it right.  Being curious, I digged into the trace files again to confirm and understand the issue here. This blog post describes the method to analyze the issue and how to troubleshoot.

  • First, when the issue first appears, it creates a 4031 trace file, which does not really give enough information to solve the problem. So I asked Oracle Support for specific trace events to properly diagnose it. He correctly mentioned event 10235 level 65536 and heapdump level 536870914  but required an instance restart. After explaining that this is not really possible and researching myself, I found these two events, which do not really need an instance restart. Event 10235 was not really necessary in this case, because the description of the allocations did not matter here.
ALTER system SET events '4031 trace name HEAPDUMP level 536870914, lifetime 1; name errorstack level 3, lifetime 1';
  • Now, after the issue appeared again, we finally had the relevant diagnostic information.

 

Allocation Request Summary Informaton
=====================================
Allocation request for: kglsim object batch
...
Heap: 0x6007b278, size: 3896
******************************************************
HEAP DUMP heap name="sga heap(3,0)"  desc=0x6007b278
extent sz=0xfe0 alt=248 het=32767 rec=9 flg=-126 opc=2
...
durations enabled for this heap
reserved granules for root 0 (granule size 134217728)
Total heap size    =2684352800. (2559 MB)
Total free space   =    38360
Total reserved free space   =120837856 (115MB) 
Permanent space    =2563474760
 
==============================================
TOP 10 MEMORY USES FOR SGA HEAP SUB POOL 1
----------------------------------------------
"free memory               "       6597 MB 71%
"gcs resources             "       1080 MB 12%
"gcs shadows               "        748 MB  8%
"db_block_hash_buckets     "        200 MB  2%
"kglsim object batch       "        189 MB  2%
"kglsim heap               "        112 MB  1%
"gcs res hash bucket       "         64 MB  1%
"ges big msg buffers       "         54 MB  1%
"dbktb: trace buffer       "         51 MB  1%
"KGLH0                     "         48 MB  1%
-----------------------------------------
free memory                        6597 MB
memory alloc.                      2747 MB
Sub total                          9344 MB
==============================================
TOP 10 MEMORY USES FOR SGA HEAP SUB POOL 2
----------------------------------------------
"free memory               "       6641 MB 70%
"gcs resources             "       1080 MB 11%
"gcs shadows               "        747 MB  8%
"db_block_hash_buckets     "        512 MB  5%
"gcs res hash bucket       "         64 MB  1%
"kglsim object batch       "         59 MB  1%
"dbktb: trace buffer       "         51 MB  1%
"KGLH0                     "         44 MB  0%
"kglsim heap               "         35 MB  0%
"ges enqueues              "         22 MB  0%
-----------------------------------------
free memory                        6641 MB
memory alloc.                      2831 MB
Sub total                          9472 MB
==============================================
TOP 10 MEMORY USES FOR SGA HEAP SUB POOL 3
----------------------------------------------
"free memory               "       6488 MB 71%
"gcs resources             "       1080 MB 12%
"gcs shadows               "        747 MB  8%
"db_block_hash_buckets     "        200 MB  2%
"kglsim object batch       "        105 MB  1%
"gc name table             "         72 MB  1%
"gcs res hash bucket       "         64 MB  1%
"kglsim heap               "         63 MB  1%
"dbktb: trace buffer       "         51 MB  1%
"KGLH0                     "         46 MB  1%
-----------------------------------------
free memory                        6488 MB
memory alloc.                      2600 MB
Sub total                          9088 MB
==============================================
TOP 10 MEMORY USES FOR SGA HEAP SUB POOL 4
----------------------------------------------
"free memory               "       6663 MB 70%
"gcs resources             "       1080 MB 11%
"gcs shadows               "        749 MB  8%
"db_block_hash_buckets     "        512 MB  5%
"kglsim object batch       "         59 MB  1%
"dbktb: trace buffer       "         51 MB  1%
"KGLH0                     "         47 MB  0%
"kglsim heap               "         35 MB  0%
"FileOpenBlock             "         35 MB  0%
"ges enqueues              "         22 MB  0%
-----------------------------------------
free memory                        6663 MB
memory alloc.                      2809 MB
Sub total                          9472 MB
TOTALS ---------------------------------------
Total free memory                    26 GB
Total memory alloc.                  11 GB
Grand total                          37 GB
  • This shows that 4031 was triggered for memory allocation request in sga heap (3,0) with size of 3896. This is smaller than 4400 bytes (_shared_pool_reserved_min_alloc), so the request can not be served the the shared pool reserved area.  Next, we can see that we have 4 subpools (kghdsidx_count) and each of them has around 6 GB of free memory. So, in total, we have 37 GB shared pool with 26 GB of it free, but one mini-subpool (duration 0) of subpool 3 still has run out of free contigous memory bigger than 3792 bytes.
  • Running Tanel´s heapdump_analyzer, I could verify that the largest chunk for sga heap(3,0) was indeed 3792 bytes and that all bigger chunks were allocated by “PERM” allocations.

 

 ./heapdump_analyzer  ./PROD1_ora_27396.trc > heap.txt
 
grep -e "---" -e "Total_size" -e "sga heap(3,0)"  heap.txt
 
-- Heapdump Analyzer v1.00 by Tanel Poder ( http://www.tanelpoder.com )
 
Total_size #Chunks  Chunk_size,        From_heap,       Chunk_type,  Alloc_reason
---------- ------- ------------ ----------------- ----------------- -----------------
 
114123720      17    6713160 ,    sga heap(3,0),           R-free,
28442152       1   28442152 ,    sga heap(3,0),             perm,  perm
...
7485000       1    7485000 ,    sga heap(3,0),             perm,  perm
6713080       1    6713080 ,    sga heap(3,0),           R-free,
6712104       1    6712104 ,    sga heap(3,0),           R-perm,  perm
...
3028568       1    3028568 ,    sga heap(3,0),             perm,  perm
...
 
8928       4       2232 ,    sga heap(3,0),             perm,  perm
7864       1       7864 ,    sga heap(3,0),             perm,  perm
5488       2       2744 ,    sga heap(3,0),             perm,  perm
3792       1       3792 ,    sga heap(3,0),             free,  <<<<<<<<<<<<<<
...

This shows that the first chunk of chunk_type “free” is of 3792 bytes, but request was for 3896 bytes. The
subpool (3,0) only has R-free (for shared_pool_reserved area) and PERM allocations of bigger size. The subpool (3,0) consists of 20 Extents á (128M Granule size). Each extent has

Researching the issue with “PERM” allocations brought me to:

ORA-4031: unable to allocate 4160 bytes of shared memory (“shared pool”,”unknown object”,”sga heap(4,0)”,”modification “) (Doc ID 1675470.1)

This note also confirms that the classification in 4 durations leads to a weak spot of duration 0 in any subpool for memory allocations due to high PERM utilization. The note mentions patch 8857940, which was also finally recommended by the support engineer.

HEAP DUMP heap name="sga heap(1,0)"  desc=0x600680e8
Total heap size    =2818570440
Total free space   =    28008 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< 
Total reserved free space   =127549960
HEAP DUMP heap name="sga heap(1,1)"  desc=0x60069940
Total heap size    =805305840
Total free space   =632590232
Total reserved free space   = 40243520
HEAP DUMP heap name="sga heap(1,2)"  desc=0x6006b198
Total heap size    =1879046960
Total free space   =1734275840
Total reserved free space   = 93975928
HEAP DUMP heap name="sga heap(1,3)"  desc=0x6006c9f0
Total heap size    =4294964480
Total free space   =4072007248
Total reserved free space   =214821040
HEAP DUMP heap name="sga heap(2,0)"  desc=0x600719b0
Total heap size    =2952788080
Total free space   =    19144 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Total reserved free space   =147689440
HEAP DUMP heap name="sga heap(2,1)"  desc=0x60073208
Total heap size    =671088200
Total free space   =524561448
Total reserved free space   = 33565720
HEAP DUMP heap name="sga heap(2,2)"  desc=0x60074a60
Total heap size    =1879046960
Total free space   =1738701544
Total reserved free space   = 93984160
HEAP DUMP heap name="sga heap(2,3)"  desc=0x600762b8
Total heap size    =4429182120
Total free space   =4202264536
Total reserved free space   =221534200
HEAP DUMP heap name="sga heap(3,0)"  desc=0x6007b278
Total heap size    =2684352800
Total free space   =    38360 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Total reserved free space   =120837856
HEAP DUMP heap name="sga heap(3,1)"  desc=0x6007cad0
Total heap size    =671088200
Total free space   =526361840
Total reserved free space   = 33565720
HEAP DUMP heap name="sga heap(3,2)"  desc=0x6007e328
Total heap size    =1879046960
Total free space   =1736230384
Total reserved free space   = 93977784
HEAP DUMP heap name="sga heap(3,3)"  desc=0x6007fb80
Total heap size    =4294964480
Total free space   =4076323080
Total reserved free space   =214821040
HEAP DUMP heap name="sga heap(4,0)"  desc=0x60084b40
Total heap size    =2952788080
Total free space   =     8536 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Total reserved free space   =140977632
HEAP DUMP heap name="sga heap(4,2)"  desc=0x60087bf0
Total heap size    =1879046960
Total free space   =1735347144
Total reserved free space   = 93984160
HEAP DUMP heap name="sga heap(4,3)"  desc=0x60089448
Total heap size    =4429182120
Total free space   =4198496144
Total reserved free space   =221534200

For reference, these are the relevant parameters. Please note that all of these are set to default, except _kghdsidx_count which was reduced from 4 earlier and _ksmg_granule_size which was reduced to 128M.

NAME                                     VALUE
---------------------------------------- --------------------
__shared_pool_size                       38923141120
_dm_max_shared_pool_pct                  1
_enable_shared_pool_durations            TRUE
_io_shared_pool_size                     4194304
_kghdsidx_count                          4
_ksmg_granule_size                       134217728
_memory_imm_mode_without_autosga         TRUE
_shared_pool_max_size                    0
_shared_pool_minsize_on                  FALSE
_shared_pool_reserved_min_alloc          4400
_shared_pool_reserved_pct                5
shared_pool_reserved_size                1946157056
shared_pool_size                         38654705664

Thanks to Tanel, Riyaj and Hatem Mahmoud


11.2.0.4 DBUA silently changing NLS_TERRITORY init.ora Parameter during upgrade

By | July 16th, 2014 | Category: 11gR2, Bugs | 1 Comment »

In case you are planning to perform any upgrades with DBUA, double-check that the init.ora parameters after the upgrade. In a production upgrade at a client site, dbua silently changed init.ora parameter NLS_TERRITORY from GERMANY to AMERICA. It was only noticed when after the upgrade, decimal and grouping number characters were swapped, leading to application problem.

Support had to admit that it is unpublished bug 16538186 and that there is NO documentation of it anywhere at the moment.

We learned from this to double-check init.ora parameters after dbua with before upgrade parameters.


Index Clustering Factor finally more realistic

By | February 22nd, 2013 | Category: 11gR2, Oracle Database | 2 comments

I just stumbled upon this bug reference on My Oracle Support:

Bug 13262857  Enh: provide some control over DBMS_STATS index clustering factor computation

This enhancement was long due. Previously, when computing the clustering factor during gathering statistics, the value was incremented, whenever the row was not found in the same block as the previous row. Now, it is finally possible to determine how many blocks should be considered when computing clustering factor. The patch delivers an improved DBMS_STATS package body that can be used to set preferences with value TABLE_CACHED_BLOCKS.

The flaw in the over-simplistic and pessimistic original computation was something that Jonathan Lewis documented in his Book “Cost Based Fundamentals” in year 2006 along with an workaround to manually gather improved clustering factor values!

The relevant chapter of this book is available as PDF at this URL.

 

 


Datapump Export suffering from Oracle Row Migration

By | September 12th, 2012 | Category: 11gR2, Oracle Database, Performance Tuning | No Comments »

Recently, i was troubleshooting a datapump export duration problem. Over the period of 18 months, the duration of a multi-table export increased dramatically. A quick analysis showed that the export duration was mainly dependent on one big table. This 50 GB table with no BLOB/CLOB/LONG datatypes took more than 4 hours on a modern system wheras it should not take more than 10 – 15 minutes. The system was performing ONLY single-block I/O requests (db file sequential read). I found this strange and started investigating.

Beginning with 11g, you can enable sql_trace (10046) for datapump with the new syntax:

ALTER system SET events 'sql_trace {process : pname = dw | pname = dm} level=12';

The trace showed that there were dozens of consecutive single-block I/O requests against the SAME data block:

WAIT #47053877709656: nam='db file sequential read' ela= 344 file#=41 block#=2556595 blocks=1 obj#=120874 tim=1347023848770469
WAIT #47053877709656: nam='db file sequential read' ela= 6 file#=41 block#=2556595 blocks=1 obj#=120874 tim=1347023848770504
WAIT #47053877709656: nam='db file sequential read' ela= 5 file#=41 block#=2556595 blocks=1 obj#=120874 tim=1347023848770526
WAIT #47053877709656: nam='db file sequential read' ela= 5 file#=41 block#=2556595 blocks=1 obj#=120874 tim=1347023848770548
WAIT #47053877709656: nam='db file sequential read' ela= 4 file#=41 block#=2556595 blocks=1 obj#=120874 tim=1347023848770570
WAIT #47053877709656: nam='db file sequential read' ela= 5 file#=41 block#=2556595 blocks=1 obj#=120874 tim=1347023848770593
WAIT #47053877709656: nam='db file sequential read' ela= 5 file#=41 block#=2556595 blocks=1 obj#=120874 tim=1347023848770617
WAIT #47053877709656: nam='db file sequential read' ela= 4 file#=41 block#=2556595 blocks=1 obj#=120874 tim=1347023848770639

No wonder that this does not perform well, right? Next was a block dump of this interesting block from file 52 / block 2056439.

SELECT * FROM dba_extents WHERE file_id = 41 AND 2556595 BETWEEN block_id AND block_id+blocks-1;
ALTER system dump datafile 41 block 2556595;

In the resulting trace file, I verified that i dumped the block from the correct table segment. (Hex 0x1d82a is Dec 120874)

buffer tsn: 15 rdba: 0x0a6702b3 (41/2556595)
frmt: 0x02 chkval: 0x57f3 TYPE: 0x06=trans DATA
seg/obj: 0x1d82a csc: 0x0c.1c3abc94 itc: 20 flg: E typ: 1 - DATA

Then I had a closer look at the flag bytes.

The flags were:

  • H: Head Piece
  • F: First Piece
  • L: Last Piece

Normally, “H-FL” is shown to indicate that the whole row is located in one rowpiece in the same block. In this block dump, you can see dozens of rows with “—-FL–” which means that the head row piece is not here and you see no Head-Piece-only Row Pieces  “–H—–” . This shows that this segment has suffered from heavy row migration. Probably, this was caused by adding columns after the table creation.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72

SID_ora_999.trc | grep "^tl: "
tl: 97 fb: ----FL-- lb: 0x0 cc: 20
tl: 88 fb: --H-FL-- lb: 0x0 cc: 20
tl: 97 fb: ----FL-- lb: 0x0 cc: 20
tl: 88 fb: --H-FL-- lb: 0x0 cc: 20
tl: 98 fb: ----FL-- lb: 0x2 cc: 20
tl: 89 fb: --H-FL-- lb: 0x2 cc: 20
tl: 99 fb: ----FL-- lb: 0x2 cc: 20
tl: 90 fb: --H-FL-- lb: 0x2 cc: 20
tl: 99 fb: ----FL-- lb: 0x2 cc: 20
tl: 90 fb: --H-FL-- lb: 0x2 cc: 20
tl: 99 fb: ----FL-- lb: 0x2 cc: 20
tl: 90 fb: --H-FL-- lb: 0x2 cc: 20
tl: 99 fb: ----FL-- lb: 0x2 cc: 20
tl: 90 fb: --H-FL-- lb: 0x2 cc: 20
tl: 99 fb: ----FL-- lb: 0x2 cc: 20
tl: 90 fb: --H-FL-- lb: 0x2 cc: 20
tl: 90 fb: --H-FL-- lb: 0x2 cc: 20
tl: 99 fb: ----FL-- lb: 0x2 cc: 20
tl: 90 fb: --H-FL-- lb: 0x2 cc: 20
tl: 99 fb: ----FL-- lb: 0x2 cc: 20
tl: 90 fb: --H-FL-- lb: 0x2 cc: 20
tl: 99 fb: ----FL-- lb: 0x2 cc: 20
tl: 90 fb: --H-FL-- lb: 0x2 cc: 20
tl: 99 fb: ----FL-- lb: 0x2 cc: 20
tl: 90 fb: --H-FL-- lb: 0x2 cc: 20
tl: 99 fb: ----FL-- lb: 0x2 cc: 20
tl: 90 fb: --H-FL-- lb: 0x2 cc: 20
tl: 99 fb: ----FL-- lb: 0x2 cc: 20
tl: 90 fb: --H-FL-- lb: 0x2 cc: 20
tl: 99 fb: ----FL-- lb: 0x2 cc: 20
tl: 90 fb: --H-FL-- lb: 0x2 cc: 20
tl: 99 fb: ----FL-- lb: 0x2 cc: 20
tl: 90 fb: --H-FL-- lb: 0x2 cc: 20
tl: 90 fb: --H-FL-- lb: 0x2 cc: 20
tl: 99 fb: ----FL-- lb: 0x2 cc: 20
tl: 90 fb: --H-FL-- lb: 0x2 cc: 20
tl: 99 fb: ----FL-- lb: 0x2 cc: 20
tl: 90 fb: --H-FL-- lb: 0x2 cc: 20
tl: 99 fb: ----FL-- lb: 0x2 cc: 20
tl: 89 fb: --H-FL-- lb: 0x2 cc: 20
tl: 99 fb: ----FL-- lb: 0x2 cc: 20
tl: 89 fb: --H-FL-- lb: 0x2 cc: 20
tl: 99 fb: ----FL-- lb: 0x2 cc: 20
tl: 89 fb: --H-FL-- lb: 0x2 cc: 20
tl: 99 fb: ----FL-- lb: 0x2 cc: 20
tl: 89 fb: --H-FL-- lb: 0x2 cc: 20
tl: 99 fb: ----FL-- lb: 0x2 cc: 20
tl: 89 fb: --H-FL-- lb: 0x2 cc: 20
tl: 99 fb: ----FL-- lb: 0x2 cc: 20
tl: 89 fb: --H-FL-- lb: 0x2 cc: 20
tl: 99 fb: ----FL-- lb: 0x2 cc: 20
tl: 90 fb: --H-FL-- lb: 0x2 cc: 20
tl: 90 fb: --H-FL-- lb: 0x2 cc: 20
tl: 99 fb: ----FL-- lb: 0x2 cc: 20
tl: 90 fb: --H-FL-- lb: 0x2 cc: 20
tl: 99 fb: ----FL-- lb: 0x2 cc: 20
tl: 90 fb: --H-FL-- lb: 0x2 cc: 20
tl: 99 fb: ----FL-- lb: 0x2 cc: 20
tl: 90 fb: --H-FL-- lb: 0x2 cc: 20
tl: 99 fb: ----FL-- lb: 0x2 cc: 20
tl: 90 fb: --H-FL-- lb: 0x2 cc: 20
tl: 99 fb: ----FL-- lb: 0x2 cc: 20
tl: 90 fb: --H-FL-- lb: 0x2 cc: 20
tl: 99 fb: ----FL-- lb: 0x2 cc: 20
tl: 90 fb: --H-FL-- lb: 0x2 cc: 20
tl: 99 fb: ----FL-- lb: 0x2 cc: 20
tl: 90 fb: --H-FL-- lb: 0x2 cc: 20
tl: 99 fb: ----FL-- lb: 0x2 cc: 20
tl: 90 fb: --H-FL-- lb: 0x2 cc: 20
tl: 90 fb: --H-FL-- lb: 0x2 cc: 20
tl: 99 fb: ----FL-- lb: 0x2 cc: 20

With datapump there are 2 different access methods: EXTERNAL_TABLE and DIRECT_PATH. Usually, the datapump utility decides on it´s own which method to use. It turned out that with EXTERNAL_TABLE, the table export takes only 10 minutes and does not perform these single-block I/O. It only appears with DIRECT_PATH.

So, the next step I recommended was to reorganize the table to get rid of row migration and evaluate whether PCTFREE should be increased. Afterwards export durations are back to normal.


Bug 13397104: INSTANCE CRASH WITH ORA-600 [KJBLPKEYDRMQSCCHK:PKEY]

By | June 1st, 2012 | Category: 11gR2, Bugs, Linux, Oracle Database | No Comments »

I would like to point your attention to a bug in RAC 11.2.0.3. The bug leads to instance crashes during Dynamic Remastering. As of June 1st, there is no patch publicly available. The workaround seems to be to set:

_gc_read_mostly_locking=false

So, if you are thinking about upgrading your RAC to 11.2.0.3, maybe you should wait just a little bit longer… 😉

More details are in MOS: Bug 13397104: INSTANCE CRASH WITH ORA-600 [KJBLPKEYDRMQSCCHK:PKEY]

Update 12.06.2012: Patch 13397104 is now available in MOS for 11.2.0.3.


Oracle certifies OEL6/RHEL6 for Oracle Database 11gR2 and Fusion Middleware

By | March 24th, 2012 | Category: 11gR2, Linux, Oracle Database | No Comments »

Oracle yesterday announced certification of RHEL6 / OEL6 with Oracle Database 11gR2 and Oracle Fusion Middleware 11.1.

Moreover, Oracle will from now on provide errata packages for free. Until now, they could only be obtained with a valid ULN support contract. I think this will be a big plus for Oracle Linux in competition with Red Hat Enterprise Linux.

I wonder how long it will take before they ship Exadata and Oracle Database Appliance (ODA) with Unbreakable Kernel.

Reference: http://www.oracle.com/us/corporate/press/1563775


No more cleartext-passwords in Scripts – Oracle Secure External Password Store (SEPS)

By | March 8th, 2012 | Category: 10g, 11g, 11gR2, Oracle Database, Security | No Comments »

Gone are the day when cleartext passwords had to be stored in scripts for Oracle database access. The solution to this requirement is “Oracle Secure External Password Store (SEPS)”. This article will give a short introduction and a practical example of the solution.

Key facts:

  • no Advanced Security Option (License) necessary
  • every unix-account, who has access to the wallet can use it to log on to the contained databases without a password! Therefore prevent other unix-accounts from accessing your wallet! (chmod, chown)

Installation

  • Oracle Client: Unix-Account, who wishes to connect to the database without providing a password needs to have an Oracle Client installed. Actually it can be an Instant Client, however the Instant Client does not have the Oracle Wallet Manager (owm).

  • TNS_ADMIN: If the installed Oracle-Client is also used by other unix-accounts, you have to set Environment-Variable TNS_ADMIN to point to a dedicated destination for sqlnet.ora
echo "export TNS_ADMIN=/home/techuser/oracle/network/admin" &gt;&gt; ~/.bash_profile

  • sqlnet.ora in $TNS_ADMIN: In this file, the path to your personal wallet is defined:
WALLET_LOCATION =
(
   SOURCE =
      (METHOD = FILE)
      (METHOD_DATA =
         (DIRECTORY = /home/techuser/oracle/network/wallet)
      )
)
SQLNET.WALLET_OVERRIDE = TRUE
SSL_CLIENT_AUTHENTICATION = FALSE

Be aware that sqlnet.ora has very sensitive formatting! Blank at the beginning of the line means that previous line is continued. If the first character of a line is not a blank, then it is supposed to be new directive. Therefore note the identation of wallet_location parameter.

  • tnsnames.ora in $TNS_ADMIN: This file contains TNS Aliases, which are used to connect to database. If the unix-account needs to  connect to one database but with 2 different database accounts, then it needs to use 2 distinct tns aliases!

  • Creation of Wallet: When the wallet is created, a wallet password is requested. This password is needed when viewing, adding, modifying and deleting wallet entries.
mkstore –wrl /home/techuser/oracle/network/wallet –create

Management of Credentials in Wallet:

  • Adding Credentials to Wallet:
mkstore -wrl /home/techuser/oracle/network/wallet \
-createCredential
  • Removal of Credentials from wallet:

mkstore -wrl /home/techuser/oracle/network/wallet \
-deleteCredential TNSAlias

  • Modification of Credential in wallet

mkstore -wrl /home/techuser/oracle/network/wallet \
-modifyCredential N102 scott newpassword
Enter password:
Modify credential Modify 1
 
mkstore -wrl /home/techuser/oracle/network/wallet \
-modifyEntry oracle.security.client.password1 newpass
Enter password:

  • Viewing of Credentials in Wallet
mkstore -wrl /home/techuser/oracle/network/wallet -list
 
Oracle Secret Store entries:
oracle.security.client.connect_string1
oracle.security.client.password1
oracle.security.client.username
 
mkstore -wrl /home/techuser/oracle/network/wallet \
-viewEntry oracle.security.client.connect_string1
Enter password: 
 
oracle.security.client.connect_string1 = N102
 
mkstore -wrl /home/techuser/oracle/network/wallet -viewEntry oracle.security.client.username1
Enter password:
oracle.security.client.username1 = scott
 
mkstore -wrl /home/techuser/oracle/network/wallet -viewEntry oracle.security.client.password1
Enter password:
oracle.security.client.password1 = tiger

  • Changing Wallet Password 
orapki wallet change_pwd -wallet /home/techuser/oracle/network/wallet \
-oldpwd welcome1 -newpwd welcome2

Example:

DBs:
 
TESTDB1 (11.2.0.3)
TESTDB2 (11.2.0.3)
 
Techuser:
techuser1 - uses RDBMS Server Home als Client
techuser2 - uses Oracle Instant Client
 
Requirements:
techuser1 should be able TO CONNECT TO ro_user@TESTDB2 AND rw_user@TESTDB2
techuser2 should be able TO CONNECT TO ro_user@TESTDB2 AND rw_user@TESTDB1 
 
Preparations:
 
AS oracle:
. TESTDB2.env
 
sqlplus "/as sysdba"
 
SQL&gt; GRANT CREATE SESSION TO ro_user IDENTIFIED BY ro_password;
 
GRANT succeeded.
 
SQL&gt; GRANT CREATE SESSION TO rw_user IDENTIFIED BY rw_password;
 
GRANT succeeded.
 
. TESTDB1.env
 
sqlplus "/as sysdba"
 
SQL&gt; GRANT CREATE SESSION TO rw_user IDENTIFIED BY rw_password;
 
GRANT succeeded.
 
AS root:
useradd techuser1
useradd techuser2
 
su - techuser1
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
export TNS_ADMIN=/home/techuser1/oracle/network/admin
mkdir -p oracle/network/admin oracle/network/wallet
 
vi $TNS_ADMIN/sqlnet.ora:
WALLET_LOCATION =
   (SOURCE =
      (METHOD = FILE)
         (METHOD_DATA=
           (DIRECTORY=/home/techuser1/oracle/network/wallet)
         )
    )
 
SQLNET.WALLET_OVERRIDE=TRUE
SSL_CLIENT_AUTHENTICATION=FALSE
 
vi $TNS_ADMIN/tnsnames.ora:
 
TESTDB2_ro =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oravm1.intra)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TESTDB2_SITE1)
    )
  )
 
TESTDB2_rw =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oravm1.intra)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TESTDB2_SITE1)
    )
  )
 
[techuser1@oravm1 admin]$ tnsping TESTDB2_ro
 
TNS Ping Utility FOR Linux: Version 11.2.0.3.0 - Production ON 01-MAR-2012 11:13:34
 
Copyright (c) 1997, 2011, Oracle.  ALL rights reserved.
 
Used parameter files:
/home/techuser1/oracle/network/admin/sqlnet.ora
 
Used TNSNAMES adapter TO resolve the alias
Attempting TO contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oravm1.intra)
(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = TESTDB2_SITE1)))
OK (0 msec)
[techuser1@oravm1 admin]$ tnsping TESTDB2_rw
 
TNS Ping Utility FOR Linux: Version 11.2.0.3.0 - Production ON 01-MAR-2012 11:13:36
 
Copyright (c) 1997, 2011, Oracle.  ALL rights reserved.
 
Used parameter files:
/home/techuser1/oracle/network/admin/sqlnet.ora
 
Used TNSNAMES adapter TO resolve the alias
Attempting TO contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oravm1.intra)
(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = TESTDB2_SITE1)))
OK (10 msec)
 
[techuser1@oravm1 admin]$ mkstore -wrl /home/techuser1/oracle/network/wallet -CREATE
Oracle Secret Store Tool : Version 11.2.0.3.0 - Production
Copyright (c) 2004, 2011, Oracle AND/OR its affiliates. ALL rights reserved.
 
Enter password:
Enter password again:
 
mkstore -wrl /home/techuser1/oracle/network/wallet \
 -createCredential TESTDB2_rw rw_user rw_password
 
mkstore -wrl /home/techuser1/oracle/network/wallet \
-createCredential TESTDB2_ro ro_user ro_password
 
[techuser1@oravm1 admin]$ mkstore -wrl /home/techuser1/oracle/network/wallet \
-createCredential TESTDB2_rw rw_user rw_password
Oracle Secret Store Tool : Version 11.2.0.3.0 - Production
Copyright (c) 2004, 2011, Oracle AND/OR its affiliates. ALL rights reserved.
 
Enter wallet password:
CREATE credential oracle.security.client.connect_string1
 
[techuser1@oravm1 admin]$ mkstore -wrl /home/techuser1/oracle/network/wallet \
 -createCredential TESTDB2_ro ro_user ro_password
Oracle Secret Store Tool : Version 11.2.0.3.0 - Production
Copyright (c) 2004, 2011, Oracle AND/OR its affiliates. ALL rights reserved.
 
Enter wallet password:
CREATE credential oracle.security.client.connect_string2
 
[techuser1@oravm1 admin]$ sqlplus /@TESTDB2_rw
SQL*Plus: Release 11.2.0.3.0 Production ON Thu Mar 1 11:19:40 2012
 
Copyright (c) 1982, 2011, Oracle.  ALL rights reserved.
 
Connected TO:
Oracle DATABASE 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
WITH the Partitioning, OLAP, DATA Mining AND REAL Application Testing options
 
SQL&gt; SHOW USER
USER IS "RW_USER"
SQL&gt; quit
Disconnected FROM Oracle DATABASE 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
WITH the Partitioning, OLAP, DATA Mining AND REAL Application Testing options
[techuser1@oravm1 admin]$ sqlplus /@TESTDB2_ro
 
SQL*Plus: Release 11.2.0.3.0 Production ON Thu Mar 1 11:19:45 2012
 
Copyright (c) 1982, 2011, Oracle.  ALL rights reserved.
 
Connected TO:
Oracle DATABASE 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
WITH the Partitioning, OLAP, DATA Mining AND REAL Application Testing options
 
SQL&gt; SHOW USER
USER IS "RO_USER"
 
[techuser1@oravm1 wallet]$ ls -la /home/techuser1/oracle/network/wallet/
total 24
drwxrwxr-x 2 techuser1 techuser1 4096 Mar  1 11:15 .
drwxrwxr-x 4 techuser1 techuser1 4096 Mar  1 11:01 ..
-rw------- 1 techuser1 techuser1 4341 Mar  1 11:17 cwallet.sso
-rw------- 1 techuser1 techuser1 4264 Mar  1 11:17 ewallet.p12
[techuser1@oravm1 wallet]$
 
techuser2 WITH instant client:
 
unzip instantclient-basic-linux.x64-11.2.0.3.0.zip
unzip instantclient-sqlplus-linux.x64-11.2.0.3.0.zip
 
export ORACLE_HOME=/home/techuser2/instantclient_11_2
export PATH=$ORACLE_HOME:$PATH
export TNS_ADMIN=/home/techuser2/oracle/network/admin
export LD_LIBRARY_PATH=$ORACLE_HOME:$LD_LIBRARY_PATH
 
[techuser2@oravm1 ~]$ mkdir -p /home/techuser2/oracle/network/admin
[techuser2@oravm1 ~]$ mkdir -p /home/techuser2/oracle/network/wallet
 
vi $TNS_ADMIN/sqlnet.ora
 
WALLET_LOCATION =
   (SOURCE =
      (METHOD = FILE)
         (METHOD_DATA=
           (DIRECTORY=/home/techuser2/oracle/network/wallet)
         )
    )
 
SQLNET.WALLET_OVERRIDE=TRUE
SSL_CLIENT_AUTHENTICATION=FALSE
 
vi $TNS_ADMIN/tnsnames.ora
 
TESTDB2_ro =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oravm1.intra)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TESTDB2_SITE1)
    )
  )
 
TESTDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oravm1.intra)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TESTDB1)
    )
  )
 
Test interactive Password:
 
[techuser2@oravm1 instantclient_11_2]$ sqlplus ro_user@TESTDB2_ro
 
SQL*Plus: Release 11.2.0.3.0 Production ON Thu Mar 1 11:28:53 2012
 
Copyright (c) 1982, 2011, Oracle.  ALL rights reserved.
 
Enter password:
 
Connected TO:
Oracle DATABASE 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
WITH the Partitioning, OLAP, DATA Mining AND REAL Application Testing options
 
SQL&gt; quit
 
Problem: no mkstore Tool IN Instantclient
Workaround: CREATE wallet WITH oracle  binaries owner AND copy afterwards
 
[oracle@oravm1 ~]$ mkdir /tmp/wallet
[oracle@oravm1 ~]$  /u01/app/oracle/product/11.2.0/dbhome_1/bin/mkstore \
-wrl /tmp/wallet -CREATE
Oracle Secret Store Tool : Version 11.2.0.3.0 - Production
Copyright (c) 2004, 2011, Oracle AND/OR its affiliates. ALL rights reserved.
 
Enter password:
Enter password again:
[oracle@oravm1 ~]$
[oracle@oravm1 ~]$
[oracle@oravm1 ~]$
[oracle@oravm1 ~]$
[oracle@oravm1 ~]$ /u01/app/oracle/product/11.2.0/dbhome_1/bin/mkstore \
-wrl /tmp/wallet -createCredential TESTDB2_ro ro_user ro_password
Oracle Secret Store Tool : Version 11.2.0.3.0 - Production
Copyright (c) 2004, 2011, Oracle AND/OR its affiliates. ALL rights reserved.
 
Enter wallet password:
 
CREATE credential oracle.security.client.connect_string1
[oracle@oravm1 ~]$ /u01/app/oracle/product/11.2.0/dbhome_1/bin/mkstore \
-wrl /tmp/wallet -createCredential TESTDB1 rw_user rw_password
Oracle Secret Store Tool : Version 11.2.0.3.0 - Production
Copyright (c) 2004, 2011, Oracle AND/OR its affiliates. ALL rights reserved.
 
Enter wallet password:
CREATE credential oracle.security.client.connect_string2
 
[techuser2@oravm1 wallet]$ ls -al
total 28
drwxr-xr-x  2 oracle oinstall 4096 Mar  1 11:32 .
drwxrwxrwt 31 root   root     4096 Mar  1 11:32 ..
-rw-------  1 oracle oinstall 4333 Mar  1 11:33 cwallet.sso
-rw-------  1 oracle oinstall 4256 Mar  1 11:33 ewallet.p12
 
[root@oravm1 techuser2]# chown -R techuser2:techuser2 /tmp/wallet
 
[root@oravm1 techuser2]# ls -la /tmp/wallet/
total 28
drwxr-xr-x  2 techuser2 techuser2 4096 Mar  1 11:32 .
drwxrwxrwt 31 root      root      4096 Mar  1 11:35 ..
-rw-------  1 techuser2 techuser2 4333 Mar  1 11:33 cwallet.sso
-rw-------  1 techuser2 techuser2 4256 Mar  1 11:33 ewallet.p12
 
[techuser2@oravm1 wallet]$ pwd
/home/techuser2/oracle/network/wallet
[techuser2@oravm1 wallet]$ mv /tmp/wallet/* .
[techuser2@oravm1 wallet]$ ls -la
total 24
drwxrwxr-x 2 techuser2 techuser2 4096 Mar  1 11:36 .
drwxrwxr-x 4 techuser2 techuser2 4096 Mar  1 11:25 ..
-rw------- 1 techuser2 techuser2 4333 Mar  1 11:33 cwallet.sso
-rw------- 1 techuser2 techuser2 4256 Mar  1 11:33 ewallet.p12
 
[techuser2@oravm1 wallet]$ sqlplus /@TESTDB2_ro
 
SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 1 11:36:58 2012
 
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL&gt; show user
USER is "RO_USER"
SQL&gt; quit
 
[techuser2@oravm1 wallet]$ sqlplus /@TESTDB1
 
SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 1 11:38:06 2012
 
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL&gt; show user
USER is "RW_USER"

Referenz:


« Older articles