Diagnosing ORA-28860 Fatal SSL error

Last week I had another error while using UTL_HTTP from an Oracle database to fetch a web page: ORA-28860 Fatal SSL error. The server was previously using http, and then began forwarding to https.

After updating the URL to use https (in case the forwarding was the problem), my first thought was that this is another symptom of a missing trusted root certificate. So I added the certificate to the Oracle Wallet, but that had no effect:

SELECT utl_http.request('https://test.com/page', NULL, 'file:/path/to/wallet', 'WalletPassword')
FROM   dual
       *
Error at line 1
ORA-29273: HTTP request failed
ORA-28860: Fatal SSL error
ORA-06512: at "SYS.UTL_HTTP", line 1491
ORA-06512: at line 1

So my working hypothesis was that the fatal error occurs during SSL negotiation prior to validation of the server’s certificate, possibly to do with supported SSL version or cipher suites. One of the few references to this problem supported this theory, although the two specific issues mentioned at the end (to do with SSL version) did not apply in my case.

With that in mind, I used a standard tool to see what the server supports:

wget https://testssl.sh/testssl.sh
chmod a+x testssl.sh
./testssl.sh https://test.com/

The output is comprehensive; this is the pertinent section:

--> Testing protocols (via sockets except TLS 1.2 and SPDY/NPN)

 SSLv2      not offered (OK)
 SSLv3      not offered (OK)
 TLS 1      offered
 TLS 1.1    offered
 TLS 1.2    offered (OK)
 SPDY/NPN   not offered

--> Testing ~standard cipher lists

 Null Ciphers                 not offered (OK)
 Anonymous NULL Ciphers       not offered (OK)
 Anonymous DH Ciphers         not offered (OK)
 40 Bit encryption            not offered (OK)
 56 Bit encryption            Local problem: No 56 Bit encryption configured in /usr/bin/openssl
 Export Ciphers (general)     not offered (OK)
 Low (<=64 Bit)               not offered (OK)
 DES Ciphers                  not offered (OK)
 Medium grade encryption      not offered (OK)
 Triple DES Ciphers           offered (NOT ok)
 High grade encryption        offered (OK)

--> Testing (perfect) forward secrecy, (P)FS -- omitting 3DES, RC4 and Null Encryption here

 PFS is offered (OK)  ECDHE-RSA-AES256-GCM-SHA384 ECDHE-RSA-AES256-SHA384 ECDHE-RSA-AES256-SHA ECDHE-RSA-AES128-GCM-SHA256 ECDHE-RSA-AES128-SHA256 ECDHE-RSA-AES128-SHA

--> Testing server preferences

 Has server cipher order?     yes (OK)
 Negotiated protocol          TLSv1.2
 Negotiated cipher            ECDHE-RSA-AES128-GCM-SHA256,
 Cipher order
     TLSv1:     ECDHE-RSA-AES128-SHA ECDHE-RSA-AES256-SHA AES256-SHA AES128-SHA DES-CBC3-SHA
     TLSv1.1:   ECDHE-RSA-AES128-SHA ECDHE-RSA-AES256-SHA AES256-SHA AES128-SHA DES-CBC3-SHA
     TLSv1.2:   ECDHE-RSA-AES128-GCM-SHA256 ECDHE-RSA-AES128-SHA256 ECDHE-RSA-AES128-SHA ECDHE-RSA-AES256-GCM-SHA384 ECDHE-RSA-AES256-SHA384 ECDHE-RSA-AES256-SHA AES128-GCM-SHA256 AES256-GCM-SHA384 AES128-SHA256 AES256-SHA AES128-SHA DES-CBC3-SHA

With TLSv1.2 supported, this should be fine with Oracle 12c. The next step, is to see what UTL_HTTP offers when it makes the connection to the server. I couldn’t find any documentation on this, so I thought it would be fun (I don’t get out much) to look at the packets on the network to see what’s going on.

$ sudo tcpdump -i eth0 -w ssl_dump.out
tcpdump: listening on eth0, link-type EN10MB (Ethernet), capture size 65535 bytes
^C158 packets captured
158 packets received by filter
0 packets dropped by kernel

After starting this, and before interrupting it with ^C, I used another session to call UTL_HTTP as above. Then I opened the output file in Wireshark, and found the SSL ClientHello message:

Secure Sockets Layer
    TLSv1.2 Record Layer: Handshake Protocol: Client Hello
        Content Type: Handshake (22)
        Version: TLS 1.2 (0x0303)
        Length: 107
        Handshake Protocol: Client Hello
            Handshake Type: Client Hello (1)
            Length: 103
            Version: TLS 1.2 (0x0303)
            Random
                GMT Unix Time: Sep  9, 2016 21:01:14.000000000 GMT Daylight Time
                Random Bytes: cabc488d10a2b6be6aa7ef0777628f62c9a100dd8d878998...
            Session ID Length: 0
            Cipher Suites Length: 32
            Cipher Suites (16 suites)
                Cipher Suite: TLS_RSA_WITH_AES_256_GCM_SHA384 (0x009d)
                Cipher Suite: TLS_RSA_WITH_AES_128_GCM_SHA256 (0x009c)
                Cipher Suite: TLS_RSA_WITH_AES_256_CBC_SHA256 (0x003d)
                Cipher Suite: TLS_RSA_WITH_AES_128_CBC_SHA256 (0x003c)
                Cipher Suite: TLS_ECDHE_ECDSA_WITH_AES_256_GCM_SHA384 (0xc02c)
                Cipher Suite: TLS_ECDHE_ECDSA_WITH_AES_128_GCM_SHA256 (0xc02b)
                Cipher Suite: TLS_ECDHE_ECDSA_WITH_AES_256_CBC_SHA384 (0xc024)
                Cipher Suite: TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA256 (0xc023)
                Cipher Suite: TLS_ECDHE_ECDSA_WITH_AES_256_CBC_SHA (0xc00a)
                Cipher Suite: TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA (0xc009)
                Cipher Suite: TLS_RSA_WITH_AES_256_CBC_SHA (0x0035)
                Cipher Suite: TLS_RSA_WITH_AES_128_CBC_SHA (0x002f)
                Cipher Suite: TLS_RSA_WITH_3DES_EDE_CBC_SHA (0x000a)
                Cipher Suite: TLS_RSA_WITH_RC4_128_SHA (0x0005)
                Cipher Suite: TLS_RSA_WITH_RC4_128_MD5 (0x0004)
                Cipher Suite: TLS_EMPTY_RENEGOTIATION_INFO_SCSV (0x00ff)
            Compression Methods Length: 1
            Compression Methods (1 method)
                Compression Method: null (0)
            Extensions Length: 30
            Extension: signature_algorithms
                Type: signature_algorithms (0x000d)
                Length: 26
                Signature Hash Algorithms Length: 24
                Signature Hash Algorithms (12 algorithms)
                    Signature Hash Algorithm: 0x0201
                        Signature Hash Algorithm Hash: SHA1 (2)
                        Signature Hash Algorithm Signature: RSA (1)
                    Signature Hash Algorithm: 0x0301
                        Signature Hash Algorithm Hash: SHA224 (3)
                        Signature Hash Algorithm Signature: RSA (1)
                    Signature Hash Algorithm: 0x0401
                        Signature Hash Algorithm Hash: SHA256 (4)
                        Signature Hash Algorithm Signature: RSA (1)
                    Signature Hash Algorithm: 0x0501
                        Signature Hash Algorithm Hash: SHA384 (5)
                        Signature Hash Algorithm Signature: RSA (1)
                    Signature Hash Algorithm: 0x0601
                        Signature Hash Algorithm Hash: SHA512 (6)
                        Signature Hash Algorithm Signature: RSA (1)
                    Signature Hash Algorithm: 0x0202
                        Signature Hash Algorithm Hash: SHA1 (2)
                        Signature Hash Algorithm Signature: DSA (2)
                    Signature Hash Algorithm: 0x0403
                        Signature Hash Algorithm Hash: SHA256 (4)
                        Signature Hash Algorithm Signature: ECDSA (3)
                    Signature Hash Algorithm: 0x0503
                        Signature Hash Algorithm Hash: SHA384 (5)
                        Signature Hash Algorithm Signature: ECDSA (3)
                    Signature Hash Algorithm: 0x0203
                        Signature Hash Algorithm Hash: SHA1 (2)
                        Signature Hash Algorithm Signature: ECDSA (3)
                    Signature Hash Algorithm: 0x0303
                        Signature Hash Algorithm Hash: SHA224 (3)
                        Signature Hash Algorithm Signature: ECDSA (3)
                    Signature Hash Algorithm: 0x0603
                        Signature Hash Algorithm Hash: SHA512 (6)
                        Signature Hash Algorithm Signature: ECDSA (3)
                    Signature Hash Algorithm: 0x0101
                        Signature Hash Algorithm Hash: MD5 (1)
                        Signature Hash Algorithm Signature: RSA (1)

and the server’s response:

Secure Sockets Layer
    TLSv1.2 Record Layer: Alert (Level: Fatal, Description: Handshake Failure)
        Content Type: Alert (21)
        Version: TLS 1.2 (0x0303)
        Length: 2
        Alert Message
            Level: Fatal (2)
            Description: Handshake Failure (40)

Now as far as I can see, none of the 16 cipher suites presented by UTL_HTTP are in the list supported by the server, and so the Fatal SSL error is quite correct.

The question now, is what can I do about it? I can’t influence the external server, and I can’t find anything in the Oracle documentation to change the behaviour of UTL_HTTP.

My only attempt so far has been to patch up to the latest PSU (12.1.0.2.160719), but alas that didn’t make any difference.

The only thing I can think of trying next is to replace the call to UTL_HTTP with some Java in the database, or to somehow call out to wget or curl (which work fine to the same site).

Platform Information

The material in this article was most recently tested against Oracle 12.1.0.2.160719 on 64-bit Linux.

Posted in Uncategorized | Leave a comment

A little promotion…

Owing to a new job role it’s been a while since I’ve come across anything with Oracle worthy of a new post. I do have a couple of ideas for new things, but in the meantime this is a quick post to promote a couple of things I’ve been working on…

Firstly, an Oracle trace file browser. This was an exercise in learning JavaFX, its aim to provide a nice GUI for 10046 trace files. At the time of writing this is very much an alpha release; it seems to work fine on my limited set of test files. If you happen to be working on a performance problem that needs an extended trace to be generated, it would be great if you could try opening it with my app and let me have your feedback.

Secondly a website I’ve been working on with my brother, UK School Search. It’s developed in PHP and MySQL (so the link to Oracle is tenuous!). We think it’s unique as we can’t find any other site that covers all four countries on the UK, as well as providing sophisticated search parameters. So if you’re looking for a school in the UK please give it a try and use the feedback page to send us any comments. Better still, if you like the site feel free post a link somewhere!

 

Aside | Posted on by | Tagged , | Leave a comment

To baseline or not to baseline?

One of the new features in Oracle 11g was SQL Plan Baselines, whereby good execution plans are preserved even if the underlying object statistics are updated. Plans may evolve if analysis shows that a better one is available. However it should not happen that a query suddenly goes from good to poor performance just because statistics have changed, or the database has been upgraded. So after some experimentation, I used to switch on automatic capture of baselines for the whole database; after all, the documentation for this parameter in the Reference manual doesn’t say this is a bad idea.

ALTER SYSTEM SET optimizer_capture_sql_plan_baselines=TRUE SCOPE=BOTH;

However with the release of Oracle 12c, which included features such as bind sensitivity and cardinality feedback, I started to have misgivings about capturing baselines for all SQL. Would this capture a baseline before the new features had a chance to refine the execution plan?

I started to give this more attention when after patching a development database to 12.1.0.2 I got occasional errors caused by LOB index corruption in the XML column of the table that stores baselines. What was more worrying was that the usual procedure to drop an affected baseline would fail with the same error, and I needed to delete the row from the table in SYS manually before the procedure would work to tidy up the rest!

So with all this in mind, it was by chance that two interesting articles popped into my blog roll:

As a result of all this, I’ve decided to change tack with my development databases, and see how a new approach works out:

For a start, I’ve switched off capturing baselines by default:

ALTER SYSTEM RESET optimizer_capture_sql_plan_baselines SCOPE=SPFILE;

From now on I will only capture a baseline when a query runs poorly and none of the other automatic mechanisms can deal with it. Clearly this doesn’t meet the goal of ultimate plan stability, so for a production system I would expect the occasional incident where the plan has changed for the worse. However a proactive approach of capturing baselines for critical statements would mitigate the risk involved.

Bind variables vs. Histograms is an interesting question. Most of my work involves writing PL/SQL. PL/SQL encourages the use of bind variables, and in fact makes it awkward to convert parameters to literals; you need to code dynamic SQL. Therefore I am switching off histograms across the board:

exec dbms_stats.set_global_prefs('METHOD_OPT','FOR ALL COLUMNS SIZE 1')

This has the advantage of making stats collection take less time, and less space is used in the data dictionary to hold unnecessary histograms.

When investigating poorly performing statements, I expect some will be the result of skewed data distribution. In these cases I would set a table preference to collect histograms for the specific columns that require them. I would also look to change the PL/SQL to use dynamic SQL if it is necessary to convert a bind variable to a literal.

For my development databases this has all worked well so far, though I will reserve judgement a little longer before I consider doing the same on a production system!

Posted in Optimizer | Tagged | Leave a comment

ORA-19110: unsupported XQuery expression

One of the frustrations I have found with XML functions in the latest Oracle releases is when things that used to be straightforward are now more difficult to achieve.

Consider the following simple script that extracts some fields from a small XHTML document:

set serveroutput on

DECLARE
  k_ns1 CONSTANT VARCHAR2(100) := 'http://www.w3.org/1999/xhtml';
  k_ns2 CONSTANT VARCHAR2(100) := 'xmlns="'||k_ns1||'"';
  k_xml CONSTANT XMLTYPE := XMLTYPE('<html '||k_ns2||'>
<head>
  <title>Test Page</title>
</head>
<body>
  <div id="wrapper">
    <div id="main">
      <h1>Test Heading</h1>
      <p>Para 1</p>
      <p>Para 2</p>
      <h2>List Heading</h2>
      <ul>
        <li><a href="/link1.html">Item 1</a></li>
        <li>Item 3</li>
        <li><a href="/link2.html">Item 3</a></li>
      </ul>
    </div>
  </div>
</body>
</html>');
BEGIN
  FOR r IN
  ( SELECT extractvalue(x.column_value, 'div/h1/text()', k_ns2) h1
         , extractvalue(x.column_value, '(div//a)[2]/@href', k_ns2) link2
    FROM   table(xmlsequence(extract(k_xml, '//div[@id="main"]', k_ns2))) x
  )
  LOOP
    dbms_output.put_line('H1: '||r.h1);
    dbms_output.put_line('LINK2: '||r.link2);
  END LOOP;
END;
/

The intention is to select the text of <h1> and the “href” attribute of the second <a>, both within the <div> with id “main”. The output is:

H1: Test Heading
LINK2: /link2.html

This test uses the extractvalue() and extract() functions, which have been deprecated since 11gR2. So let’s change to use the supported xmltable() function (for brevity I’ve only shown the part of the script that has changed):

  ( SELECT x.h1
         , x.link2
    FROM   xmltable( XMLNAMESPACES(DEFAULT 'http://www.w3.org/1999/xhtml')
                   , '//div[@id="main"]'
                     PASSING k_xml
                     COLUMNS h1    VARCHAR2(4000) PATH 'h1/text()'
                           , link2 VARCHAR2(4000) PATH '(//a)[2]/@href'
                   ) x
  )

This raises: ORA-19110: unsupported XQuery expression. Commenting out a couple of lines demonstrates that this is owing to the “(//a)[2]” XPath.

Note that I have also changed the path for the “h1” column so that the “div” is not referenced. Using “div/h1/text()” gives a NULL value, whereas “/div/h1/text()” works fine. Documented examples seem to omit the root element, hence I use just “h1/text()”

In the past I have found a workaround to this is to use “string-join” in the PATH expression to capture all the “a/@href” attributes, and then in the SELECT to use “substr” to extract the one that is required.

However a better solution seems to be to avoid simply re-formulating the XPaths used with the deprecated functions, and instead embrace the full power of XQuery:

  ( SELECT x.h1
         , x.link2
    FROM   xmltable( XMLNAMESPACES(DEFAULT 'http://www.w3.org/1999/xhtml')
                   , 'for $d in //div
                      where $d/@id = "main"
                      return <result>
                               {$d/h1}
                               {($d//a)[2]}
                             </result>
                     '
                     PASSING k_xml
                     COLUMNS h1    VARCHAR2(4000) PATH 'h1/text()'
                           , link2 VARCHAR2(4000) PATH 'a/@href'
                   ) x
  )

This uses a FLWOR expression to select the containing <div> with the required “id” attribute. It then constructs a <result> element containing the elements of interest. In particular, the “($d//a)[2]” works fine, and its “href” attribute can be selected by the PATH expression.

A verbose solution definitely, but at least it is using supported functions and does not require an inelegant workaround.

Platform Information

The material in this article was most recently tested against Oracle 12.1.0.1.0 on 64-bit Linux.

Posted in XML | Tagged , | Leave a comment

ORA-29024: Certificate validation failure

This post is in response to a couple of comments to my earlier post about working with UTL_SMTP.STARTTLS. It describes in detail how to resolve the issue for this scenario, but the principles should apply equally to other applications where ORA-29024 is raised. The demonstration is from a Linux environment, however the tools should be applicable to other environments as well.

The first step is to connect to the server and see the certificate that it is presenting, and more especially the Certificate Authority that issued the root certificate:

$ openssl s_client -connect podNNNNN.outlook.com:587 -starttls smtp
CONNECTED(00000003)
depth=3 /C=IE/O=Baltimore/OU=CyberTrust/CN=Baltimore CyberTrust Root
verify return:1
depth=2 /CN=Microsoft Internet Authority
verify return:1
depth=1 /DC=com/DC=microsoft/DC=corp/DC=redmond/CN=MSIT Machine Auth CA 2
verify return:1
depth=0 /C=US/ST=Washington/L=Redmond/O=Microsoft Corporation/OU=Exchange/CN=*.outlook.com
verify return:1
---
Certificate chain
 0 s:/C=US/ST=Washington/L=Redmond/O=Microsoft Corporation/OU=Exchange/CN=*.outlook.com
   i:/DC=com/DC=microsoft/DC=corp/DC=redmond/CN=MSIT Machine Auth CA 2
 1 s:/DC=com/DC=microsoft/DC=corp/DC=redmond/CN=MSIT Machine Auth CA 2
   i:/CN=Microsoft Internet Authority
 2 s:/CN=Microsoft Internet Authority
   i:/C=IE/O=Baltimore/OU=CyberTrust/CN=Baltimore CyberTrust Root
---
Server certificate
-----BEGIN CERTIFICATE-----
MIIGqTCCBZGgAwIBAgIKHwZHMwABAABLlzANBgkqhkiG9w0BAQUFADCBgDETMBEG
CgmSJomT8ixkARkWA2NvbTEZMBcGCgmSJomT8ixkARkWCW1pY3Jvc29mdDEUMBIG
CgmSJomT8ixkARkWBGNvcnAxFzAVBgoJkiaJk/IsZAEZFgdyZWRtb25kMR8wHQYD
VQQDExZNU0lUIE1hY2hpbmUgQXV0aCBDQSAyMB4XDTEzMDMwNzAwMjEyOFoXDTE1
MDMwNzAwMjEyOFowfzELMAkGA1UEBhMCVVMxEzARBgNVBAgTCldhc2hpbmd0b24x
EDAOBgNVBAcTB1JlZG1vbmQxHjAcBgNVBAoTFU1pY3Jvc29mdCBDb3Jwb3JhdGlv
bjERMA8GA1UECxMIRXhjaGFuZ2UxFjAUBgNVBAMMDSoub3V0bG9vay5jb20wggEi
MA0GCSqGSIb3DQEBAQUAA4IBDwAwggEKAoIBAQCnRuQFhPkgI5QZZgxG0zb5BETO
8R+9fjyek60Apw09xDhV4eGUOKeZmrt+YwF1rVWFSsOB2FAkgV90MAKCxCPbyNyZ
oiqHbyuoXyCJMTPR6n+iI/TuJvcRiMZ8KNvX60gpCL+Ox++cN6+ZNJs0kflF09/R
9EDdr2GqJKg6GKHSZANsx2Xst/ThRyYVszImmz7r+r8JlGyH6uC8b5DlnNyofpF8
aPTg5IQFn81SVvLSmlXpe6TsdHki71a8n7Z/0D4iTI54ft6NyXDf5NcmsYNAZowS
viUkSpUtXq17pYUQBDiU0F7Fl+gUgLGKrccpLQ+0pg5y79rvSU1MbVrCDrEjAgMB
AAGjggMjMIIDHzALBgNVHQ8EBAMCBLAwHQYDVR0lBBYwFAYIKwYBBQUHAwIGCCsG
AQUFBwMBMHgGCSqGSIb3DQEJDwRrMGkwDgYIKoZIhvcNAwICAgCAMA4GCCqGSIb3
DQMEAgIAgDALBglghkgBZQMEASowCwYJYIZIAWUDBAEtMAsGCWCGSAFlAwQBAjAL
BglghkgBZQMEAQUwBwYFKw4DAgcwCgYIKoZIhvcNAwcwHQYDVR0OBBYEFExxJMC8
b1ziMFlE9bOTDsxdV/shMB8GA1UdIwQYMBaAFOvbEV74CZ7Y1mKc/WKd44RKKOEn
MIHuBgNVHR8EgeYwgeMwgeCggd2ggdqGT2h0dHA6Ly9tc2NybC5taWNyb3NvZnQu
Y29tL3BraS9tc2NvcnAvY3JsL01TSVQlMjBNYWNoaW5lJTIwQXV0aCUyMENBJTIw
MigxKS5jcmyGTWh0dHA6Ly9jcmwubWljcm9zb2Z0LmNvbS9wa2kvbXNjb3JwL2Ny
bC9NU0lUJTIwTWFjaGluZSUyMEF1dGglMjBDQSUyMDIoMSkuY3JshjhodHRwOi8v
Y29ycHBraS9jcmwvTVNJVCUyME1hY2hpbmUlMjBBdXRoJTIwQ0ElMjAyKDEpLmNy
bDCBrQYIKwYBBQUHAQEEgaAwgZ0wVQYIKwYBBQUHMAKGSWh0dHA6Ly93d3cubWlj
cm9zb2Z0LmNvbS9wa2kvbXNjb3JwL01TSVQlMjBNYWNoaW5lJTIwQXV0aCUyMENB
JTIwMigxKS5jcnQwRAYIKwYBBQUHMAKGOGh0dHA6Ly9jb3JwcGtpL2FpYS9NU0lU
JTIwTWFjaGluZSUyMEF1dGglMjBDQSUyMDIoMSkuY3J0MD8GCSsGAQQBgjcVBwQy
MDAGKCsGAQQBgjcVCIPPiU2t8gKFoZ8MgvrKfYHh+3SBT4PC7YUIjqnShWMCAWQC
AQowJwYJKwYBBAGCNxUKBBowGDAKBggrBgEFBQcDAjAKBggrBgEFBQcDATAsBgNV
HREEJTAjgg0qLm91dGxvb2suY29tghIqLmV4Y2hhbmdlbGFicy5jb20wDQYJKoZI
hvcNAQEFBQADggEBAJX0wAFlJXc+hpRBt3+hIzSgQ4bGNfjm2lUxigvi0snP0F4m
RKzdgYb2OFFdsnaQOsOXQchw9hwEikTG46ts1OKKCeO7JZEHzKk5g7tIRUlGY2k0
fhXu2Cs9iew1OHi3Y34S1NF6EBdds9eSstfeVtFooE4K7l5Z3pK3LJlVCY5VNtWg
WtC7IZwirC2xzIe7BBlOuuDKyzUV+d8gPi4gsas+JT71WnAw2pFbxS+goOL8ZyyS
g/nont9VXbuuiLOXX98490t6lo9V/eu5vxShpXpJjp991DPPuMZsQJGUeKUMUFrZ
pGg2TVVnj44unjC0XjhDU/+U6hSc6XDzSSP5kcI=
-----END CERTIFICATE-----
subject=/C=US/ST=Washington/L=Redmond/O=Microsoft Corporation/OU=Exchange/CN=*.outlook.com
issuer=/DC=com/DC=microsoft/DC=corp/DC=redmond/CN=MSIT Machine Auth CA 2
---
No client certificate CA names sent
---
SSL handshake has read 5018 bytes and written 482 bytes
---
New, TLSv1/SSLv3, Cipher is AES128-SHA
Server public key is 2048 bit
Secure Renegotiation IS supported
Compression: NONE
Expansion: NONE
SSL-Session:
    Protocol  : TLSv1
    Cipher    : AES128-SHA
    Session-ID: CF30000024B17E1F4EA1C727541A3D73ABAD244E3487418DE34040E96AB1B537
    Session-ID-ctx:
    Master-Key: BBC9FF815105D9E6E577CD80C2C1F32AD6170DE35E57B34074E82518774CFCF765076D730A5F1E1B93E9F40B1CEF6742
    Key-Arg   : None
    Krb5 Principal: None
    Start Time: 1365602158
    Timeout   : 300 (sec)
    Verify return code: 0 (ok)
---
250 CHUNKING

^C to cancel

From this we can see that the signer is /C=IE/O=Baltimore/OU=CyberTrust/CN=Baltimore CyberTrust Root.

The next step is to see if this is in our wallet:

$ orapki wallet display -wallet wallets/ewallet.p12
Oracle PKI Tool : Version 11.2.0.3.0 - Production
Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.

Enter wallet password: 
Requested Certificates:
User Certificates:
Trusted Certificates:
Subject:        OU=Class 1 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject:        OU=Class 3 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject:        OU=Class 2 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject:        OU=Secure Server Certification Authority,O=RSA Data Security\, Inc.,C=US
Subject:        CN=GTE CyberTrust Global Root,OU=GTE CyberTrust Solutions\, Inc.,O=GTE Corporation,C=US

We can see that the required certificate is not in the list, there’s no mention of Baltimore CyberTrust Root.

You can find the root certificate in several places. Here’s one way to do it from Internet Explorer:

  • Internet Options => Content => Certificates => Trusted Root Certification Authorities
  • Highlight “Baltimore CyberTrust Root” => Export… => Base-64 encoded X.509 (.CER)
  • Select a filename (we’ll use baltimore.cer) and export it

Now copy the file to the database server and add it into the wallet:

$ orapki wallet add -wallet wallets/ewallet.p12 -trusted_cert -cert baltimore.cer
Oracle PKI Tool : Version 11.2.0.3.0 - Production
Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:

And now check it’s there:

$ orapki wallet display -wallet wallets/ewallet.p12
Oracle PKI Tool : Version 11.2.0.3.0 - Production
Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:  le
Requested Certificates:
User Certificates:
Trusted Certificates:
Subject:        OU=Class 2 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject:        OU=Secure Server Certification Authority,O=RSA Data Security\, Inc.,C=US
Subject:        CN=Baltimore CyberTrust Root,OU=CyberTrust,O=Baltimore,C=IE
Subject:        OU=Class 3 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject:        OU=Class 1 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject:        CN=GTE CyberTrust Global Root,OU=GTE CyberTrust Solutions\, Inc.,O=GTE Corporation,C=US
Posted in Network | Tagged | 7 Comments

Collection cardinality in Oracle 12c

I’ve written in the past (see 1 and 2) about some of the limitations Oracle has when dealing with the cardinality of table functions and collections, especially when these are used in PL/SQL and bound in as variables. So I thought it would be interesting to see how things have changed in Oracle 12c.

Collections

CREATE OR REPLACE TYPE varchar2_ntt AS TABLE OF VARCHAR2(4000)
/

DECLARE
  x varchar2_ntt := varchar2_ntt('A','B','C');
  y varchar2_ntt;
BEGIN
  SELECT /*+ Q1 */ t.*
  BULK COLLECT INTO y
  FROM   TABLE(x) t;
END;
/

A simple table type, with a collection of 3 elements passed in as a bind variable. Previously this would have given a cardinality of 8168 (for a database with 8KB default block size). Here’s the plan from executing this in 12c:

SQL_ID  874d5mfj85gk2, child number 0
-------------------------------------
SELECT /*+ Q1 */ T.* FROM TABLE(:B1 ) T

Plan hash value: 4118387986

-------------------------------------------------------------------------------------------
| Id  | Operation                         | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |      |        |       |    29 (100)|          |
|   1 |  COLLECTION ITERATOR PICKLER FETCH|      |      3 |     6 |    29   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

It can be seen that the optimizer has estimated 3 rows, and it wasn’t necessary to request dynamic sampling or work with the extensible optimizer!

Table Functions

DECLARE
  l_xml XMLTYPE := XMLTYPE('<a><b>1</b><b>2</b></a>');
  l_cnt BINARY_INTEGER;
BEGIN
  SELECT /*+ Q2 */ count(*)
  INTO   l_cnt
  FROM   XMLTABLE('/A/B' PASSING l_xml COLUMNS b_val INTEGER PATH '/B/text()');
END;
/

This example uses the XMLTABLE function, and we would expect a cardinality of 2:

SQL_ID  4ngjhs2nh731u, child number 0
-------------------------------------
SELECT /*+ Q2 */ COUNT(*) FROM XMLTABLE('/A/B' PASSING :B1 COLUMNS
B_VAL INTEGER PATH '/B/text()')

Plan hash value: 1487578152

-----------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                  | E-Rows | Cost (%CPU)| E-Time   |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                       |        |    29 (100)|          |
|   1 |  SORT AGGREGATE                    |                       |      1 |            |          |
|   2 |   COLLECTION ITERATOR PICKLER FETCH| XQSEQUENCEFROMXMLTYPE |   8168 |    29   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

What a shame, nothing has changed! However there is hope with the introduction of cardinality feedback. I had intended to demonstrate this here, but although the cardinality feedback seems to work, the query I tested with would not change plan. I shall investigate further and hopefully the results will be the subject of a future blog.

Platform Information

The material in this article was most recently tested against Oracle 12.1.0.1.0 on 64-bit Linux.

Posted in Optimizer | Tagged | Leave a comment

ALTER TABLE … MOVE with LOBs and XML

This week I was asked a question about ALTER TABLE … MOVE in relation to LOB and XML segments. I thought I knew the answer, but wasn’t 100% confident. After some testing I found that it didn’t behave as I thought. Here’s a summary of how this command works.

Begin by creating some tablespaces to work with, and a table with a normal data segment, a LOB segment and an XML segment.

CREATE TABLESPACE ts1
/

CREATE TABLESPACE ts2
/

CREATE TABLESPACE ts3
/

CREATE TABLESPACE ts4
/

CREATE TABLE t_test
( col_vc2  VARCHAR2(4000)
, col_clob CLOB
, col_xml  XMLTYPE
)
TABLESPACE ts1
LOB (col_clob) STORE AS SECUREFILE t_test_clob (TABLESPACE ts2 DISABLE STORAGE IN ROW RETENTION CACHE)
XMLTYPE COLUMN col_xml STORE AS SECUREFILE BINARY XML t_test_xml (TABLESPACE ts3 DISABLE STORAGE IN ROW RETENTION CACHE)
/

INSERT INTO t_test VALUES
( lpad('X', 4000, 'X')
, lpad('X', 4000, 'X')
, xmlelement("X", lpad('X', 4000, 'X'))
)
/

COMMIT
/

Check where the segments have been created:

SELECT   segment_name
       , tablespace_name
       , header_file
       , header_block
FROM     dba_segments
WHERE    segment_name LIKE 'T_TEST%'
ORDER BY 1
/

SEGMENT_NAME                   TABLESPACE_NAME                HEADER_FILE HEADER_BLOCK
------------------------------ ------------------------------ ----------- ------------
T_TEST                         TS1                                      9          130
T_TEST_CLOB                    TS2                                     10          129
T_TEST_XML                     TS3                                     11          129

Now let’s move the table, without giving any further specification:

ALTER TABLE t_test MOVE
/

SEGMENT_NAME                   TABLESPACE_NAME                HEADER_FILE HEADER_BLOCK
------------------------------ ------------------------------ ----------- ------------
T_TEST                         TS1                                      9          162
T_TEST_CLOB                    TS2                                     10          129
T_TEST_XML                     TS1                                      9          137

From this it can be seen that the table data segment has moved within TS1 as you would expect. However the LOB segment has not moved; it is still in TS2 at the same position. What is more interesting is that the XML segment has moved to TS1, the same as the table data segment!

The next test is to specify the tablespace for the move:

ALTER TABLE t_test MOVE
TABLESPACE ts4
/

SEGMENT_NAME                   TABLESPACE_NAME                HEADER_FILE HEADER_BLOCK
------------------------------ ------------------------------ ----------- ------------
T_TEST                         TS4                                     12          154
T_TEST_CLOB                    TS2                                     10          129
T_TEST_XML                     TS4                                     12          129

Again, the XML segment has followed the table data segment and the LOB segment is untouched. Leaving the XML for now, let’s make sure the LOB segment moves:

ALTER TABLE t_test MOVE
TABLESPACE ts4
LOB (col_clob) STORE AS SECUREFILE (TABLESPACE ts4)
/

SEGMENT_NAME                   TABLESPACE_NAME                HEADER_FILE HEADER_BLOCK
------------------------------ ------------------------------ ----------- ------------
T_TEST                         TS4                                     12          178
T_TEST_CLOB                    TS4                                     12          129
T_TEST_XML                     TS4                                     12          153

So to move the LOB segment(s) associated with a table, you must specify them explicitly. Let’s see if we can control the XML segment too. Here we will try to move all the segments within their original tablespaces:

ALTER TABLE t_test MOVE
TABLESPACE ts1
LOB (col_clob) STORE AS SECUREFILE (TABLESPACE ts2)
LOB (col_xml) STORE AS SECUREFILE (TABLESPACE ts3)
/

ORA-00904: "COL_XML": invalid identifier

As might be expected, we cannot refer to the XML LOB segment using its column name, we need to use the internal column name (for example, by querying DBA_LOBS).

ALTER TABLE t_test MOVE
TABLESPACE ts1
LOB (col_clob) STORE AS SECUREFILE (TABLESPACE ts2)
LOB (SYS_NC00004$) STORE AS SECUREFILE (TABLESPACE ts3)
/

SEGMENT_NAME                   TABLESPACE_NAME                HEADER_FILE HEADER_BLOCK
------------------------------ ------------------------------ ----------- ------------
T_TEST                         TS1                                      9          138
T_TEST_CLOB                    TS2                                     10          161
T_TEST_XML                     TS3                                     11          161

So in summary:

  • LOB segments will not move by default; you need to be explicit if you want to include them in the move.
  • XML segments are inconsistent in this respect, by default they move to the table data segment’s tablespace (whether specified or not); you must be explicit to ensure they will remain in the correct tablespace (but they will always move).

Platform Information

The material in this article was most recently tested against Oracle 11.2.0.3.6 on 64-bit Linux.

Posted in XML | Tagged , , | Leave a comment