All entries by this author

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" >> ~/.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> GRANT CREATE SESSION TO ro_user IDENTIFIED BY ro_password;
 
GRANT succeeded.
 
SQL> GRANT CREATE SESSION TO rw_user IDENTIFIED BY rw_password;
 
GRANT succeeded.
 
. TESTDB1.env
 
sqlplus "/as sysdba"
 
SQL> 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> SHOW USER
USER IS "RW_USER"
SQL> 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> 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> 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> show user
USER is "RO_USER"
SQL> 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> show user
USER is "RW_USER"

Referenz:


Bug: ORA-4031 for subheap “KTC latch subh” in 11.2.0.3

By | January 25th, 2012 | Category: 11gR2, Bugs, Oracle Database | No Comments »

I just saw MOS Note

ORA-4031 After Upgrade From 11.2.0.1 To 11.2.0.3 With Leak in ‘KTC latch subh’ Subheaps [ID 1398984.1]

giving very little information about this bug. At the time of this writing, there is no patch available but I expect it to be in a few days. I have checked some 11.2.0.3 instances and found only one with > 60 MB for this subheap.


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]

RDBMS 11.2.0.2: unrecoverable operations of Compression Advisor running daily

By | October 20th, 2011 | Category: 11gR2, Oracle Database | 2 comments

At one of my customers, we wondered about some entries in v$datafile.unrecoverable_change# for the most critical tablespace of the application. Certainly no application components were allowed to perform nologging operations for segments inside this tablespace and yet there was evidence that “something” was done with nologging.

  • The unrecoverable_time was shortly after 10 pm
  • The ASH data covering this time period showed activity by DBMS_SCHEDULER and Autotask Jobs.
  • An internal DDL log table showed the following operations:
CREATE TABLE "MYOWNER".DBMS_TABCOMP_TEMP_UNCMP
tablespace "DAT_MYOWNER" nologging
AS
SELECT /*+ FULL("MYOWNER"."MYTAB") */ *  FROM "MYOWNER"."MYTAB"  sample block( 3.55) mytab ;   
 
CREATE TABLE "MYOWNER".DBMS_TABCOMP_TEMP_CMP ORGANIZATION HEAP
TABLESPACE "DAT_MYOWNER"
COMPRESS FOR ALL OPERATIONS
NOLOGGING
AS
SELECT  * FROM "MYOWNER".DBMS_TABCOMP_TEMP_UNCMP MYTAB;
 
DROP TABLE "MYOWNER".DBMS_TABCOMP_TEMP_UNCMP PURGE;
DROP TABLE "MYOWNER".DBMS_TABCOMP_TEMP_CMP purge;

Summary:

I find it suboptimal that Compression Advisor is executed on a daily basis during the maintenance window and that the Compression Advisor can not be seperately disabled without also disabling the Segment Advisor. (Although there is supposed to be an enhancement request open for this.)

In addition to that, it is more than problematic that the is using the user tablespace, which could lead to tablespace pressure and that the operations are performed as unrecoverable and therefore logged in v$datafile.unrecoverable_time, overwriting potential important information of real “user” operations performing with NOLOGGING.

More information about Compression Advisor can be found in MOS Note: How Does Compression Advisor Work? [ID 1284972.1]


Workaround for ORA-600/ORA-7445 with SQL Repair Advisor

By | July 26th, 2011 | Category: 11gR2, Oracle Database | No Comments »

From time to time we hit Errors like ORA-600 or ORA-7445, which are triggered by specific SQL statements. For these cases, Oracle developed the SQL Repair Advisor. This is a short demonstration of utilizing the SQL Repair Advisor to avoid ORA-7445 caused by a SQL statement.

SQL and Error:

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

SELECT
EXTRACT(YEAR FROM date_new) jahr,
EXTRACT(MONTH FROM date_new) monat,
COUNT(DISTINCT TRUNC(date_new)) arbeitstage,
SUM(CASE WHEN STRING1 IN (SELECT STRING1
FROM MD.MD1
WHERE STRING1_GROUP = 'CUST')
THEN
DECODE(FLAG, 'J', 1, 0)
ELSE
0
END) cust_flag
FROM
SCHEMA.TAB1
WHERE
date_new BETWEEN TO_DATE(201101, 'yyyymm')
AND ADD_MONTHS(TO_DATE(201107, 'yyyymm'), 1)
GROUP BY
EXTRACT(YEAR FROM date_new),
EXTRACT(MONTH FROM date_new)
ORDER BY 1,2;
 
ERROR at line 6:
ORA-03113: end-of-file ON communication channel
Process ID: 7746
SESSION ID: 737 Serial NUMBER: 9823
 
Tue Jul 26 15:19:41 2011
Exception [TYPE: SIGSEGV, Address NOT mapped TO object] [ADDR:0xA] [PC:0x888A3C9, xtyqbcb()+413] ...
ORA-07445: exception encountered: core dump [xtyqbcb()+413] [SIGSEGV] [ADDR:0xA] [PC:0x888A3C9]...

Execution of SQL Repair Advisor:

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
73
74
75
76
77
78
79
80
81
82

DECLARE
  rep_out CLOB;
  t_id VARCHAR2(50);
BEGIN
  T_ID := DBMS_SQLDIAG.CREATE_DIAGNOSIS_TASK( 
  sql_text => q'#SELECT
EXTRACT(YEAR FROM date_new) jahr,
EXTRACT(MONTH FROM date_new) monat,
COUNT(DISTINCT TRUNC(date_new)) arbeitstage,
SUM(CASE WHEN STRING1 IN (SELECT STRING1
FROM MD.MD1
WHERE STRING1_GROUP = 'CUST')
THEN
DECODE(FLAG, 'J', 1, 0)
ELSE
0
END) cust_flag
FROM
SCHEMA.TAB1
WHERE
date_new BETWEEN TO_DATE(201101, 'yyyymm')
AND ADD_MONTHS(TO_DATE(201107, 'yyyymm'), 1)
GROUP BY
EXTRACT(YEAR FROM date_new),
EXTRACT(MONTH FROM date_new)
ORDER BY 1,2#', 
  task_name => 'ORA7445-xtyqbcb', 
  problem_type =>DBMS_SQLDIAG.PROBLEM_TYPE_COMPILATION_ERROR
  );
  DBMS_SQLDIAG.EXECUTE_DIAGNOSIS_TASK (t_id);
  rep_out := DBMS_SQLDIAG.REPORT_DIAGNOSIS_TASK (t_id, DBMS_SQLDIAG.TYPE_TEXT);
  DBMS_OUTPUT.PUT_LINE ('Report : ' || rep_out);
END;
/
 
<strong>Output:</strong>
 
Report : GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : ORA7445-xtyqbcb
Tuning Task Owner  : SYS
Workload TYPE      : Single SQL Statement
Scope              : COMPREHENSIVE
TIME LIMIT(seconds):
1800
Completion STATUS  : COMPLETED
Started at         : 07/26/2011 15:46:38
Completed at       : 07/26/2011 15:46:38
 
-------------------------------------------------------------------------------
Schema Name: SYS
SQL ID     : 39ac71hjcn0rt
...
 
-------------------------------------------------------------------------------
FINDINGS SECTION (1
finding)
-------------------------------------------------------------------------------
 
1- SQL Patch Finding (see EXPLAIN plans SECTION below)
------------------------------------------------------
  A potentially better execution plan was found FOR this statement.
 
  Recommendation
 
 
--------------
  - Consider accepting the recommended SQL patch.
    EXECUTE dbms_sqldiag.accept_sql_patch(task_name =>
            'ORA7445-xtyqbcb', task_owner => 'SYS', REPLACE => TRUE);
 
  Rationale
  ---------
    Recommended plan WITH hash VALUE 1503213169 has NUMBER OF ROWS 7, CHECK
 
SUM 13925076401, execution TIME 264 AND 1972 buffer gets
 
-------------------------------------------------------------------------------
 
 
 
PL/SQL PROCEDURE successfully completed.

Activation:

1
2
3
4
5
6

 BEGIN dbms_sqldiag.accept_sql_patch(
      task_name =>'ORA7445-xtyqbcb', 
      task_owner => 'SYS', 
      REPLACE => TRUE);
END;
/

Test:

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

SELECT
EXTRACT(YEAR FROM date_new) jahr,
EXTRACT(MONTH FROM date_new) monat,
COUNT(DISTINCT TRUNC(date_new)) arbeitstage,
SUM(CASE WHEN STRING1 IN (SELECT STRING1
FROM MD.MD1
WHERE STRING1_GROUP = 'CUST')
THEN
DECODE(FLAG, 'J', 1, 0)
ELSE
0
END) cust_flag
FROM
SCHEMA.TAB1
WHERE
date_new BETWEEN TO_DATE(201101, 'yyyymm')
AND ADD_MONTHS(TO_DATE(201107, 'yyyymm'), 1)
GROUP BY
EXTRACT(YEAR FROM date_new),
EXTRACT(MONTH FROM date_new)
ORDER BY 1,2;
 
      JAHR      MONAT ARBEITSTAGE CUST_FLAG
---------- ---------- ----------- ----------------
      2011          1          20                0
 
 
7 ROWS selected.
 
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL,NULL,'ADVANCED'));
 
PLAN_TABLE_OUTPUT
---------------------
SQL_ID  5wdztx81x0r8f, child NUMBER 0
-------------------------------------
 
 
Plan hash VALUE: 1503213169
 
-----------------------------------------------------------------------------------------------
| Id  | Operation          | Name             | ROWS  | Bytes |TempSpc| Cost (%CPU)| TIME     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                  |       |       |       |   591 (100)|          |
|*  1 |  TABLE ACCESS FULL | MD1              |     1 |    12 |       |     3   (0)| 00:00:01 |
|   2 |  SORT GROUP BY     |                  | 27402 |   294K|  2776K|   591  (12)| 00:00:02 |
|*  3 |   TABLE ACCESS FULL| TAB1             |   117K|  1260K|       |   369  (10)| 00:00:01 |
-----------------------------------------------------------------------------------------------
 
 
...
 
 
Note
-----
   - SQL patch "SYS_SQLPTCH_013166b4fb2a0001" used FOR this statement
 
 
66 ROWS selected.

Removal of SQL Patch:

1

EXEC DBMS_SQLDIAG.DROP_SQL_PATCH ('SYS_SQLPTCH_013166b4fb2a0001');

Deletion of SQL Diag Task:

1

 EXEC DBMS_SQLDIAG.DROP_DIAGNOSIS_TASK ('ORA7445-xtyqbcb');

« Older articles
Newer articles »