11g

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?


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


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:


Oracle SCN Problem

By | January 19th, 2012 | Category: 10g, 11g, Bugs, Oracle Database | 1 Comment »

In case you have not heard all the buzz about the Oracle SCN flaw, which was published by Infoworld after the release of January CPU, I summarize the essentials.

  • There is a risk that the SCN reaches it´s maximum value and this could lead to an outage of the database.
  • There is a bug in “ALTER DATABASE BEGIN BACKUP”, which increases the SCN dramatically. (Bug 12371955 – fixed in 11.2.0.2.4 and others)
  • When you query a remote database via database link from a database with elevated SCN, the remote SCN gets increased to the higher value as well. This has the dramatic effect, that a database will infect the other database.
  • Imagine an unpatched environment with user-managed online backups (e.g. for SAN split mirror technology) and dozens of interconnected databases with database links.
  • Now imagine an evil employee with this little innocent database account with only “create session” privilege  … you get the picture.
  • The January 2012 CPU/PSU contains a patch that should restrict the ways to increase the SCN. However, I am not sure that the issue with db links is solved. I could bump up the SCN to 12562779343042 by a remote select even after patching with CPU January 2012.

Additionally, there are already quite a few articles showing how a potential attacker could use this flaw, e.g. this one.

References:

  • Information on the System Change Number (SCN) and how it is used in the Oracle Database [ID 1376995.1]
  • Installing, Executing and Interpreting output from the “scnhealthcheck.sql” script [ID 1393363.1]
  • Bug 12371955 – Backup task can cause increased SCN growth rate leading to ORA-600 [2252] errors [ID 12371955.8]

Presentation “Minimal Downtime Oracle 11g Upgrade” at DOAG Conference 2010

By | November 17th, 2010 | Category: 10g, 11g, Oracle Database | 1 Comment »

I have uploaded the presentation material from my DOAG Conference presentation on “Minimal Downtime Oracle 11g Upgrade”. The material contains a paper, presentation and an online demo which shows how Logical Standby SQL Apply can be used for minimal downtime 10g to 11g Upgrade. Additionally, for convenience, i provide all 3 components in a ZIP package.

DOAG Conference 2010: Minimal Downtime Oracle 11g Upgrades


Advert: Presentation about Minimal Downtime Upgrade at DOAG Conference 2010

By | November 3rd, 2010 | Category: 11g, 11gR2, Oracle Database | No Comments »

Just a short information:  I will be holding a presentation at DOAG Conference 2010 in Nürnberg about “Minimal Downtime Oracle Database 11g Upgrades“. The presentation will be on Tuesday, 16.11.2010 at 10:00. The presentation will be in german. This is a short preview in german:

Nachdem der Premier Support für das Datenbank-Release 10gR2 im Juli 2010 endet und nur das erste Jahr des Extended Supports kostenfrei ist, stehen viele Kunden vor dem Problem des Upgrades von Oracle 10g nach 11gR1 oder 11gR2 bis spätestens Juli 2011. Dieser Vortrag zeigt Möglichkeiten, den Oracle-Upgrade mittels Logical Standby sowie mittels Transportable Tablespaces mit minimaler Downtime durchzuführen. Es wird aufgezeigt, welche Voraussetzungen für die beiden Alternativen jeweils benötigt werden sowie die potentiellen Probleme bei der Durchführung. Der Vortrag enthält zudem einen praktischen Teil, bei dem die beiden Varianten demonstriert werden.

You can find the presentation materials (including online demo) on the presentations page.


suboptimal prorated density calculation with DESC index – cardinality feedback feature

By | October 25th, 2010 | Category: 11g, 11gR2, Bugs, MetaLink, Oracle Database | No Comments »

At a customer site, I came across a problem of the Oracle cost based optimizer with selectivity calculation with a composite index with one column sorted descending. The following testcase describes the issue. Currently Oracle development is working on the issue. During testing on 11.2.0.1, I realized that the new cardinality feedback issue takes care of the problem and subsequent executions are using the correct plan.

-- drop table if exists
DROP TABLE ADMDECKER.DUMMY purge;
 
-- create table
CREATE TABLE ADMDECKER.DUMMY (
      a NUMBER NOT NULL,
      B NUMBER NOT NULL,
      C NUMBER NOT NULL,
      D NUMBER NOT NULL,
      E NUMBER ) NOLOGGING;
 
-- fill table
INSERT /*+ APPEND */  INTO ADMDECKER.DUMMY
SELECT  rownum AS a,
        MOD(rownum,100) AS B,
        MOD(rownum,10000) AS C,
        1 AS D,
        234 AS E
        FROM (
               SELECT rownum r
               FROM
                       (SELECT rownum r FROM dual CONNECT BY rownum <= 100) a,
                       (SELECT rownum r FROM dual CONNECT BY rownum <= 100) b,
                       (SELECT rownum r FROM dual CONNECT BY rownum <= 100) c
               WHERE rownum <= 1000000
       );
 
commit;
 
-- create indexes
CREATE INDEX ADMDECKER.DUMMY_IDX1 ON ADMDECKER.DUMMY(C,B) nologging;
CREATE INDEX ADMDECKER.DUMMY_IDX2 ON ADMDECKER.DUMMY(B DESC,a) nologging;
CREATE INDEX ADMDECKER.DUMMY_IDX3 ON ADMDECKER.DUMMY(C,B,D) nologging;
CREATE INDEX ADMDECKER.DUMMY_IDX4 ON ADMDECKER.DUMMY(B,a) nologging;
 
-- gather statistics (including tab stats for virtual hidden column for "B desc" column.
BEGIN DBMS_STATS.GATHER_TABLE_STATS(
      OWNNAME=>'ADMDECKER',
      TABNAME=>'DUMMY',
      METHOD_OPT=>'FOR ALL COLUMNS SIZE 1',
      cascade=>TRUE,
      ESTIMATE_PERCENT=>100);
END;
/
 
SELECT * FROM DBA_TAB_STATISTICS WHERE TABLE_NAME = 'DUMMY';
SELECT * FROM DBA_TAB_COL_STATISTICS WHERE TABLE_NAME = 'DUMMY';
 
-- note min/max/density values for system generated virtual hidden column
SELECT * FROM DBA_TAB_COLS WHERE TABLE_NAME = 'DUMMY';
SELECT * FROM dba_ind_statistics  WHERE TABLE_NAME = 'DUMMY';

I enable 10053 tracing and execute the query. Afterwards I display the execution plan.

SQL&gt; ALTER SESSION SET EVENTS='10053 trace name context forever, level 1';
 
SESSION altered.
 
SELECT /*+ GATHER_PLAN_STATISTICS RUN2 */ COUNT(E) FROM ADMDECKER.DUMMY
WHERE C = 9592 AND B >= 93 AND D=1;
 
  COUNT(E)
----------
         0
 
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST +COST'));
 
PLAN_TABLE_OUTPUT
---------------------
SQL_ID  f5mkaprk7fzt8, child NUMBER 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS RUN2 */ COUNT(E) FROM ADMDECKER.DUMMY
WHERE C = 9592 AND B >= 93 AND D=1
 
Plan hash VALUE: 1903509519
 
------------------------------------------------------------------------------------------------
| Id |Operation                    |Name      |E-ROWS| Cost (%CPU)|A-ROWS|   A-TIME   | Buffers |
------------------------------------------------------------------------------------------------
|   0|SELECT STATEMENT             |          |      |     4 (100)|     1|00:00:00.07 |   23437 |
|   1| SORT AGGREGATE              |          |     1|            |     1|00:00:00.07 |   23437 |
|*  2|  TABLE ACCESS BY INDEX ROWID|DUMMY     |     7|     4   (0)|     0|00:00:00.07 |   23437 |
|*  3|   INDEX RANGE SCAN          |DUMMY_IDX2|     1|     3   (0)| 70000|00:00:00.03 |     197 |
------------------------------------------------------------------------------------------------
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
 
   2 - FILTER(("C"=9592 AND "D"=1))
   3 - access("DUMMY"."SYS_NC00006$"<=HEXTORAW('3EA1FF') )
       FILTER(SYS_OP_UNDESCEND("DUMMY"."SYS_NC00006$")>=93)

The complete 10053 can be viewed here: 10053 Trace of bad plan.

It can be seen that the execution plan contains the following statement and uses a very, very low density.

  Using prorated density: 0.000002 of col #6 as selectvity of out-of-range/non-existent value pred

So, instead of using Index DUMMY_IDX3, which contains all three predicate columns, especially more selective equality predicate “C = 9592”, it uses quite unselective index DUMMY_IDX2, only because density is calculated wrongly.

If the statement is executed a second time, the right plan is used. Cardinality feedback feature kicks in here. Please note the higher costs and higher cardinality for index range scan at row source operation id 3.

CONNECT ADmdecker
Enter password:
Connected.
ALTER SESSION SET EVENTS='10053 trace name context forever, level 1';
 
SESSION altered.
 
SELECT /*+ GATHER_PLAN_STATISTICS RUN2 */ COUNT(E) FROM ADMDECKER.DUMMY
WHERE C = 9592 AND B >= 93 AND D=1;
 
  COUNT(E)
----------
         0
 
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST +COST'));
 
PLAN_TABLE_OUTPUT
----------------------
SQL_ID  f5mkaprk7fzt8, child NUMBER 1
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS RUN2 */ COUNT(E) FROM MDECKER.DUMMY
WHERE C = 9592 AND B >= 93 AND D=1
 
Plan hash VALUE: 1171978535
 
-------------------------------------------------------------------------------------------------
| Id |Operation                    |Name       |E-ROWS| Cost (%CPU)|A-ROWS |   A-TIME   |Buffers |
-------------------------------------------------------------------------------------------------
|   0|SELECT STATEMENT             |           |      |    11 (100)|     1 |00:00:00.01 |      3 |
|   1| SORT AGGREGATE              |           |    1 |            |     1 |00:00:00.01 |      3 |
|   2|  TABLE ACCESS BY INDEX ROWID|DUMMY      |    7 |    11   (0)|     0 |00:00:00.01 |      3 |
|*  3|   INDEX RANGE SCAN          |DUMMY_IDX3 |    7 |     3   (0)|     0 |00:00:00.01 |      3 |
-------------------------------------------------------------------------------------------------
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
 
   3 - access("C"=9592 AND "B">=93 AND "D"=1)
       FILTER("D"=1)
 
Note
-----
 - cardinality feedback used FOR this statement

The complete 10053 of the new plan (using cardinality feedback feature) can be viewed here: 10053 Trace of good plan using cardinality feedback. More information about this little documented new feature can be found at Dion Cho´s blog and from this Oracle paper.

Update: 06.10.2011

Oracle Support has provided Patch 11072246, which fixes a bug in DBMS_STATS on how min/max values are set for hidden desc columns. After installing the patch, the bug is gone and the execution plan is optimal.


Oracle 11g Certified Masters …

By | October 22nd, 2010 | Category: 11g, Certification, Oracle Database | No Comments »

I was quite surprised when I received a mail from Oracle that my OCM 11g profile is now online. It turned out that I am one of only 6 people with an 11g OCM profile! Although there might be some others who succeeded in certification and didn´t publish a profile, I am still quite surprised that there are so few. Maybe the run will begin as soon as the full exam is publicly available as currently only upgrades are possible.

http://education.oracle.com/education/otn/
http://education.oracle.com/education/otn/ocm-11g.html


DOAG Presentation on Oracle Data Guard 11g (R1/R2) What´s new? (german)

By | July 14th, 2010 | Category: 11g, 11gR2, Data Guard, Oracle Database, Oracle Enterprise Manager | No Comments »

I have uploaded the material of the german DOAG presentation on Oracle Data Guard 11gr2 Whats New to the “presentation” section. It also contains 6 recorded demos using Enterprise Manager Grid Control.


Oracle Certified Master 11g

By | June 15th, 2010 | Category: 11g, Certification, Oracle Database | No Comments »

Today, I received a mail from Oracle to inform me that I passed Oracle Certified Master 11g Upgrade Exam. After several weeks of after-work preparations, this is a very satisfying result. If you are OCM 10g and also interested in upgrading, you can find exam schedule and list of objectives here. Good luck.

Schedule: http://education.oracle.co.uk/html/oracle/28US/SCHED_SP_OCM11.htm

Objectives: http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=41&p_org_id=&lang=&p_exam_id=11gOCMU


« Older articles