Friday, 22 March 2019

Sending Emails in HTML from Linux/Unix - sendmail

Hey everyone, it was quite a mystery for me since long time on how to send preformatted or html generated reports from a server and even though there are a lot of good people sharing knowledge on this part. Here I share a portion of code I developed (with their help) to use for this purpose.

Below is one of the shell script code you would need to use with "sendmail" utility to achieve sending mails in HTML format directly from any server to a list of recipients.

FROM="<user>@<yourdomain>"
TO="<username>@<yourdomain>.com, <username2>@<yourdomain>.com"
SUBJECT="An HTML Formatted Mail Body Test"
delimiter="`date +%Y%m%d%H%M%S`"


cat <<At | /usr/sbin/sendmail -t
From: ${FROM}
To: ${TO}
Subject: ${SUBJECT}
Mime-Version: 1.0
Content-Type: multipart/alternative; boundary="${delimiter}"

--${delimiter}
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

<HTML>

`cat /tmp/your_html_file_or_message.html`
</HTML>

--${delimiter}--
At


You could echo a variable, write html code by hand or can expand an html file content like I did in email body between HTML tags.

Feel free to leave a comment if you would like to understand explanation behind any part of above code.

Even though you could use mailx to attach html file using -a switch. Having a report in mail body saves time of opening an attachment.

Cheers!
Anurag

Monday, 28 January 2019

Shrink of LOBs with Shrink Space Compact?

Hey everyone, been a while since I posted. A recent requirement to shrink LOB that had free space totaling to 300+ GB made me wonder if that can be done without causing any impact on another sessions.

You would read a lot on Oracle Support and various blogs about use of SHRINK SPACE COMPACT on a table, but unfortunately by the time of writing this, I didn't found anyone mentioned if it can be used for LOB as well.

Problem?
Shrink operation when running for several hours can affect user sessions during adjustment of HWM. Since you do not have control over completion time of an LOB shrink, this small duration of time can still impact your environment. Thus to have a greater control, you can breakdown shrink operation in two statements as below:

Alter table lob_tab modify LOB(data) (shrink space compact);
Alter table lob_tab modify LOB(data) (shrink space);


Test Case:
CREATE TABLE lob_tab (
  id NUMBER,
  data CLOB
)
LOB(data) STORE AS DATAVAL (DISABLE STORAGE IN ROW);


 Populate this table by running below statements several times randomly or in a loop:

INSERT INTO lob_tab VALUES (1, 'TEST1');
INSERT INTO lob_tab VALUES (3, 'TEST3');
INSERT INTO lob_tab VALUES (2, 'TEST2');

INSERT INTO lob_tab select * from lob_tab where id=2;
INSERT INTO lob_tab select * from lob_tab where id=1;
INSERT INTO lob_tab select * from lob_tab where id=3;


Commit;

Check Size of Table:

col segment_name for a25
col segment_type for a20
col gb for 9999.99
select segment_name, segment_type, sum(bytes/1024/1024) MB
from dba_segments
where owner='ORA10G'
group by segment_name, segment_type
order by 1,2
/

SEGMENT_NAME              SEGMENT_TYPE                 MB
------------------------- -------------------- ----------
DATAVAL                   LOBSEGMENT                  400
LOB_TAB                   TABLE                         2
SYS_IL0000046208C00002$$  LOBINDEX                      3


Delete some data and check fragmentation in Megabytes or Gigabytes using DBMS_SPACE.SPACE_USAGE procedure:

set serveroutput on
declare
v_unformatted_blocks number;
v_unformatted_bytes number;
v_fs1_blocks number;
v_fs1_bytes number;
v_fs2_blocks number;
v_fs2_bytes number;
v_fs3_blocks number;
v_fs3_bytes number;
v_fs4_blocks number;
v_fs4_bytes number;
v_full_blocks number;
v_full_bytes number;
total_fs_bytes number;
v_obj_name varchar2(40);
v_obj_type varchar2(10);
Cursor obj IS SELECT segment_name, segment_type FROM dba_segments
                WHERE segment_name in ('DATAVAL')
                ORDER BY segment_type;
begin
    OPEN obj;
    LOOP
    FETCH obj INTO v_obj_name, v_obj_type;
    EXIT WHEN obj%NOTFOUND;
    IF v_obj_type = 'LOBSEGMENT'
    THEN
        v_obj_type := 'LOB';
    END IF;
    dbms_space.space_usage ('ORA10G', v_obj_name , v_obj_type, v_unformatted_blocks, v_unformatted_bytes, v_fs1_blocks
    , v_fs1_bytes, v_fs2_blocks, v_fs2_bytes,v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes);
    total_fs_bytes:=round((v_fs1_bytes + v_fs2_bytes + v_fs3_bytes + v_fs4_bytes + v_unformatted_bytes)/1048576,2);
    dbms_output.put_line('Object-> ' || v_obj_name || ', Type-> ' || v_obj_type || ', Free MB-> ' || total_fs_bytes);
    END LOOP;
end;
/


Object-> DATAVAL, Type-> LOB, Free MB-> 107.57
 


Run Shrink
Alter table lob_tab modify LOB(data) (shrink space compact);

Check again using space usage script:
Object-> DATAVAL, Type-> LOB, Free MB-> 107.57

Free space didn't changed? - this is because shrink space compact has just adjusted data blocks and not the HWM. Now run shrink space to perform this:

Alter table lob_tab modify LOB(data) (shrink space);

Check again using space usage script:
Object-> DATAVAL, Type-> LOB, Free MB-> 2.11


Enjoy!





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:




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