Oracle RDBMS and Shell Scripting Tips: 2017

Thursday, 30 November 2017

Large tracefiles Created - Cursor Obsoletion Dump

Faced this issue in an OEM 12c repository database, where SYSMAN sessions would keep on writing to trace files and keeps them growing until deleted.

However, the files would again be created and SYSMAN sessions would not stop writing to it.

Each of the tracefile's header shows messages similar to below:

*** 2017-11-30 12:10:28.758
*** SESSION ID:(382.54831) 2017-11-30 12:10:28.758
*** CLIENT ID:(SYSMAN) 2017-11-30 12:10:28.758
*** SERVICE NAME:(SYS$USERS) 2017-11-30 12:10:28.758
*** MODULE NAME:(OEM.DefaultPool) 2017-11-30 12:10:28.758
*** CLIENT DRIVER:(jdbcthin) 2017-11-30 12:10:28.758
*** ACTION NAME:([ACTIVE] ExecuteThread: '2' for ) 2017-11-30 12:10:28.758

----- Cursor Obsoletion Dump sql_id=a1kj6vkgvv3ns -----
Parent cursor obsoleted 1 time(s). maxchild=1024 basephd=0xb671cc90 phd=0xb671cc90
----- Dump Cursor sql_id=a1kj6vkgvv3ns xsc=0x7f851a21db48 cur=0x7f851abb2070 -----

LibraryHandle: Address=0xb671cc90 Hash=9fbd8e98 LockMode=N PinMode=0 LoadLockMode=0 Status=VALD
ObjectName: Name=SELECT TP.PROPERTY_NAME, TP.PROPERTY_VALUE FROM MGMT_TARGET_PROPERTIES TP, MGMT_TARGETS T WHERE TP.TARGET_GUID = T.TARGET_GUID AND T.TARGET_NAME = :B2 AND T.TARGET_TYPE = :B1
    FullHashValue=a9363acd946320fea0ca26dc9fbd8e98 Namespace=SQL AREA(00) Type=CURSOR(00) ContainerId=0 ContainerUid=0 Identifier=2680000152 OwnerIdn=102
Statistics: InvalidationCount=22013 ExecutionCount=250437 LoadCount=42292 ActiveLocks=1 TotalLockCount=263612 TotalPinCount=1
Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=672 Version=0 BucketInUse=6 HandleInUse=6 HandleReferenceCount=0
Concurrency: DependencyMutex=0xb671cd40(0, 14857, 3, 0) Mutex=0xb671cdd8(0, 55916292, 12, 0)
Flags=RON/PIN/TIM/OBS/PN0/DBN/[10412841] Flags2=[0000]
WaitersLists:
    Lock=0xb671cd20[0xb671cd20,0xb671cd20]
    Pin=0xb671cd00[0xb671cd00,0xb671cd00]
    LoadLock=0xb671cd78[0xb671cd78,0xb671cd78]
Timestamp: Current=09-13-2017 00:09:02
HandleReference: Address=0xb671cef8 Handle=(nil) Flags=[00]
LibraryObject: Address=0xd6b5dc08 HeapMask=0000-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
    ChildTable: size='1024'
      Child: id='0' Table=0xd6b5ea88 Reference=0xd6b5e548 Handle=0xc1199ab0
      Child: id='1' Table=0xd6b5ea88 Reference=0xd6b5e8c0 Handle=0x1143cd090
      Child: id='2' Table=0xd6b5ea88 Reference=0xa6277020 Handle=0x97489fa8
      Child: id='3' Table=0xd6b5ea88 Reference=0xa6277330 Handle=0x107b002b0
      Child: id='4' Table=0xd6b5ea88 Reference=0xa6277640 Handle=0xe0671bb8
      Child: id='5' Table=0xd6b5ea88 Reference=0xa6277950 Handle=0x100d842c8
      Child: id='6' Table=0xd6b5ea88 Reference=0xa6277c60 Handle=0x8ded1870
      Child: id='7' Table=0xd6b5ea88 Reference=0xaf605168 Handle=0xce89cfe0
      Child: id='8' Table=0xd6b5ea88 Reference=0xaf605478 Handle=0xa65d5678
      Child: id='9' Table=0xd6b5ea88 Reference=0xaf605788 Handle=0xc70b14c0
      Child: id='10' Table=0xd6b5ea88 Reference=0xaf605a98 Handle=0xa9adece0
....

There is a similar problem noted in Oracle Doc ID (1955319.1) which describes issue as:

The traces are produced as a result of a diagnostic enhancement introduced in 12c. This enhancement dumps diagnostic data for concurrent sessions

This proactive diagnostic which dumps the parent cursor when the obsolete threshold is exceeded.

In some cases, when many concurrent sessions dumping this diagnostic information, performance issues may be observed in the form of High CPU and latch waits.

The aim of the bug is to improve cursor sharing diagnostics by dumping information about an obsolete parent cursor and it's child cursors after the parent cursor has been obsoleted N times.

Solution:

The problem in my case was resolved by flushing the shared pool. This would clear the cursor area thus resolving the problem temporarily.

SQL> ALTER SYSTEM FLUSH SHARED_POOL;

As per Oracle note below solutions can be implemented:

1. Apply Patch 22075064: GSI-12C :- ADVERSE SYSTEM IMPACT DUE TO CONCURRENT CURSOR OBSOLETION DUMPS

2. Diagnostic feature can be disabled by setting parameter "_kks_obsolete_dump_threshold" = 0

Cheers,
Anurag

Tuesday, 18 July 2017

Upgrade Oracle JDBC Drivers on Weblogic Server

Recently I started upgrading our client's 11gR4 (non-CDB) databases to 12cR2 (CDB) databases. The database also used to provide data to SOA over JDBC Thin Client v11.1.0.7 connections made by SOA's weblogic server 10.3.3. Note that the SOA repository database is a different database.

Since Oracle 12.2.0.1 database would only certify client connections from 11.2.0.4 and above, the weblogic drivers were needed to be upgraded to appropriate version

Below are the steps to upgrade JDBC Driver globally for a domain i.e. for all managed servers the JDBC drivers will be upgraded.

1. Current environment:

Application Information:
List of Oracle Homes:
Name          Location
   OH2065489457         /data/oracle/Middleware/oracle_common
   OH1759065136         /data/oracle/Middleware/Oracle_SOA1
   SOATDBHome1         /data/oracle/product/11.1.0

Application Server 11g SOA Patchset 11.1.1.3.0
Oracle SOA Suite 11g                 11.1.1.2.0

WebLogic Server 10.3.3.0

OS Information:
oracle@soatrain01 /$ uname -a
Linux soatrain01 2.6.18-419.el5 #1 SMP Wed Feb 22 22:46:18 EST 2017 i686 i686 i386 GNU/Linux
oracle@soatrain01 /$ cat /etc/redhat-release
Red Hat Enterprise Linux Server release 5.11 (Tikanga)

Compatibility Matrix:
This can be checked from My Oracle Support (support.oracle.com) under certifications tab:

2. Checking Your Client Version:

a.) You could use drvtest application provided by Oracle Support to determine version of JDBC drivers used by Oracle Weblogic in SOA environment. The application once deployed can be run via deployments tab and would give result as below:

b.) OR you can run below query in remote database
-- Credits: http://marcel.vandewaters.nl/oracle/database-oracle/determine-versions-of-connected-oracle-clients
CREATE VIEW xksusecon AS SELECT * FROM SYS.x$ksusecon;

set lines 150 pages 1000
COL CLIENT_VERSION for a15
col USERNAME for a17
col PROGRAM for a16
col MODULE for a16

WITH x AS
(SELECT DISTINCT ksusenum sid,ksuseclvsn,TRIM(TO_CHAR(ksuseclvsn,'xxxxxxxxxxxxxx')) to_c,
   TO_CHAR(ksuseclvsn,'xxxxxxxxxxxxxx') v
FROM
    sys.xksusecon
)
SELECT x.sid,
   DECODE(to_c,'0','Unknown',TO_NUMBER(SUBSTR(v,8,2),'xx') || '.' || -- maj_rel
             SUBSTR(v,10,1)      || '.' || -- mnt_rel
             SUBSTR(v,11,2)      || '.' || -- ias_rel
             SUBSTR(v,13,1)      || '.' || -- ptc_set
             SUBSTR(v,14,2)) client_version, -- port_mnt
   username,program, module
FROM x, v$session s
WHERE x.sid like s.sid AND type != 'BACKGROUND'
and MODULE like 'JDBC%'
ORDER BY 3,2
/

CLIENT_VERSION USERNAME          PROGRAM
--------------- ----------------- ----------------
11.1.00.7.00    SOATRN_MDS        JDBC Thin Client
11.1.00.7.00    SOATRN_MDS        JDBC Thin Client
11.1.00.7.00    SOATRN_ORASDPM    JDBC Thin Client
11.1.00.7.00    SOATRN_SOAINFRA   JDBC Thin Client
11.1.00.7.00    SOATRN_SOAINFRA   JDBC Thin Client
11.1.00.7.00    SOATRN_SOAINFRA   JDBC Thin Client
11.1.00.7.00    SOATRN_SOAINFRA   JDBC Thin Client
11.1.00.7.00    SOATRN_SOAINFRA   JDBC Thin Client
11.1.00.7.00    SOATRN_SOAINFRA   JDBC Thin Client
11.1.00.7.00    SOATRN_SOAINFRA   JDBC Thin Client
11.1.00.7.00    SOATRN_SOAINFRA   JDBC Thin Client

3. Downloading Drivers:

You can download drivers from Oracle's official website:

http://www.oracle.com/technetwork/database/features/jdbc/index-091264.html

Just select the appropriate version and download all JAR files listed under it. I have downloaded these on my system under /oracle/client_stage/jdbc11204_drv

4. Upgrade Drivers

a.) Set correct permissions on driver files

oracle@soatrain01 $ cd /oracle/client_stage/jdbc11204_drv
oracle@soatrain01 jdbc11204_drv$ chmod 750 *

oracle@soatrain01 jdbc11204_drv$ ls -l
total 28M
-rwxr-x--- 1 oracle dba 3.3M Jul 5 04:54 ojdbc5dms_g.jar
-rwxr-x--- 1 oracle dba 2.5M Jul 5 04:54 ojdbc5dms.jar
-rwxr-x--- 1 oracle dba 3.3M Jul 5 04:54 ojdbc5_g.jar
-rwxr-x--- 1 oracle dba 2.0M Jul 5 04:54 ojdbc5.jar
-rwxr-x--- 1 oracle dba 4.4M Jul 5 04:54 ojdbc6dms_g.jar
-rwxr-x--- 1 oracle dba 3.2M Jul 5 04:54 ojdbc6dms.jar
-rwxr-x--- 1 oracle dba 4.3M Jul 5 04:54 ojdbc6_g.jar
-rwxr-x--- 1 oracle dba 2.7M Jul 5 04:54 ojdbc6.jar
-rwxr-x--- 1 oracle dba 71K Jul 5 04:54 ons.jar
-rwxr-x--- 1 oracle dba 1.6M Jul 5 04:54 orai18n.jar
-rwxr-x--- 1 oracle dba 257K Jul 5 04:54 xdb6.jar

b.) Navigate to $WL_HOME/server/ext/jdbc/oracle and backup old driver files

oracle@soatrain01 oracle$ pwd
/data/oracle/Middleware/wlserver_10.3/server/ext/jdbc/oracle

oracle@soatrain01 oracle$ mv 11g 11g.old
`11g/' -> `11g.old'

c.) Create new 11g directory and copy 11.2.0.4 drivers

oracle@soatrain01 oracle$ mkdir 11g
oracle@soatrain01 oracle$ cd 11g

oracle@soatrain01 11g$ cp /oracle/client_stage/jdbc11204_drv/* .
`/oracle/client_stage/jdbc11204_drv/ojdbc5dms_g.jar' -> `./ojdbc5dms_g.jar'
`/oracle/client_stage/jdbc11204_drv/ojdbc5dms.jar' -> `./ojdbc5dms.jar'
`/oracle/client_stage/jdbc11204_drv/ojdbc5_g.jar' -> `./ojdbc5_g.jar'
`/oracle/client_stage/jdbc11204_drv/ojdbc5.jar' -> `./ojdbc5.jar'
`/oracle/client_stage/jdbc11204_drv/ojdbc6dms_g.jar' -> `./ojdbc6dms_g.jar'
`/oracle/client_stage/jdbc11204_drv/ojdbc6dms.jar' -> `./ojdbc6dms.jar'
`/oracle/client_stage/jdbc11204_drv/ojdbc6_g.jar' -> `./ojdbc6_g.jar'
`/oracle/client_stage/jdbc11204_drv/ojdbc6.jar' -> `./ojdbc6.jar'
`/oracle/client_stage/jdbc11204_drv/ons.jar' -> `./ons.jar'
`/oracle/client_stage/jdbc11204_drv/orai18n.jar' -> `./orai18n.jar'
`/oracle/client_stage/jdbc11204_drv/xdb6.jar' -> `./xdb6.jar'

d.) Backup and replace driver files under $WL_HOME/server/lib

oracle@soatrain01 $ cd /data/oracle/Middleware/wlserver_10.3/server/lib

oracle@soatrain01 lib$ mv ojdbc6.jar ojdbc6.jar.old
`ojdbc6.jar' -> `ojdbc6.jar.old'

oracle@soatrain01 lib$ cp /oracle/client_stage/jdbc11204_drv/ojdbc6.jar .
`/oracle/client_stage/jdbc11204_drv/ojdbc6.jar' -> `./ojdbc6.jar'

5.Restart Managed Servers and Verify JDBC Driver Version

Managed Servers can be restarted via SOA administration console. After restarting verify version using either drvtest application OR by querying in remote database:

-- Credits: http://marcel.vandewaters.nl/oracle/database-oracle/determine-versions-of-connected-oracle-clients
CREATE VIEW xksusecon AS SELECT * FROM SYS.x$ksusecon;

set lines 150 pages 1000
COL CLIENT_VERSION for a15
col USERNAME for a17
col PROGRAM for a16
col MODULE for a16

WITH x AS
(SELECT DISTINCT ksusenum sid,ksuseclvsn,TRIM(TO_CHAR(ksuseclvsn,'xxxxxxxxxxxxxx')) to_c,
   TO_CHAR(ksuseclvsn,'xxxxxxxxxxxxxx') v
FROM
    sys.xksusecon
)
SELECT x.sid,
   DECODE(to_c,'0','Unknown',TO_NUMBER(SUBSTR(v,8,2),'xx') || '.' || -- maj_rel
             SUBSTR(v,10,1)      || '.' || -- mnt_rel
             SUBSTR(v,11,2)      || '.' || -- ias_rel
             SUBSTR(v,13,1)      || '.' || -- ptc_set
             SUBSTR(v,14,2)) client_version, -- port_mnt
   username,program, module
FROM x, v$session s
WHERE x.sid like s.sid AND type != 'BACKGROUND'
and MODULE like 'JDBC%'
ORDER BY 3,2
/

CLIENT_VERSION USERNAME          PROGRAM
--------------- ----------------- ----------------
11.2.00.4.00    SOATRN_MDS        JDBC Thin Client
11.2.00.4.00    SOATRN_MDS        JDBC Thin Client
11.2.00.4.00    SOATRN_ORASDPM    JDBC Thin Client
11.2.00.4.00    SOATRN_SOAINFRA   JDBC Thin Client
11.2.00.4.00    SOATRN_SOAINFRA   JDBC Thin Client
11.2.00.4.00    SOATRN_SOAINFRA   JDBC Thin Client
11.2.00.4.00    SOATRN_SOAINFRA   JDBC Thin Client
11.2.00.4.00    SOATRN_SOAINFRA   JDBC Thin Client
11.2.00.4.00    SOATRN_SOAINFRA   JDBC Thin Client
11.2.00.4.00    SOATRN_SOAINFRA   JDBC Thin Client
11.2.00.4.00    SOATRN_SOAINFRA   JDBC Thin Client

Cheers!,
Anurag

Saturday, 11 March 2017

ORA-65149: PDB name conflicts with existing service name

During my recent non-CDB to PDB conversion, I got the error:

ORA-65149: PDB name conflicts with existing service name in the CDB or the PDB

Its a bit weird because the non-CDB database was still not plugged-in and was just checked for compatibility using procedure..

SET SERVEROUTPUT ON

DECLARE

compatible CONSTANT VARCHAR2(3) :=

CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(

pdb_descr_file => '/tmp/noncdb_pdb.xml',

pdb_name => 'PINDBA')

WHEN TRUE THEN 'YES'

ELSE 'NO'

END;

BEGIN

DBMS_OUTPUT.PUT_LINE(compatible);

END;

With this error in plugging-in database, I could see there was this warning appearing in PDB_PLUG_IN_VIOLATIONS view

NAME    CAUSE                  TYPE
------- --------------------- -------
IONDBA Service Name Conflict WARNING

MESSAGE
---------------------------------
Service name or network name of
service IONDBA in the PDB is
invalid or conflicts with an
existing service name or network
name in the CDB.

The message is pretty clear that the service name IONDBA in PDB conflicts with service name in CDB.

So I checked what services are listed in CDB (root container):

SELECT name,
network_name
FROM   dba_services
ORDER BY name
/

NAME                 NETWORK_NAME
-------------------- ------------
IONDBA               IONDBA
SYS$BACKGROUND
SYS$USERS

There it is, lets delete this service and try CREATE PLUGGABLE DATABASE:

SQL> exec DBMS_SERVICE.DELETE_SERVICE('IONDBA');

PL/SQL procedure successfully completed.

Ran compatibility check again and the warning disappeared from PDB_PLUG_IN_VIOLATIONS.

This time the database was plugged-in without issues:

SQL> CREATE PLUGGABLE DATABASE IONDBA USING '/tmp/noncdb_pdb.xml'
NOCOPY TEMPFILE REUSE;
Pluggable Database Created.

Seems like the problem was because I migrated the listener.ora from old 11g home and it dynamically registered its services to CDB. Also updated listener.ora afterwards.

Cheers!
Anurag