Get the secondmax, again

Just bouncing on 2008/07/secondmax.

Another way of getting secondmax would be with an ordered collection. While collection methods like (n), first, last, count are not in SQL, I used PLSQL (within SQL)


WITH FUNCTION f (c sys.odcinumberlist, n NUMBER) RETURN number
IS BEGIN RETURN c (n); END;
SELECT 
  f(
    CAST(
      COLLECT(
        CAST(
          sal AS NUMBER
        ) ORDER BY sal DESC
      ) 
      AS SYS.odcinumberlist
    ), 
    2
  ) as secondmax
FROM emp; 

 SECONDMAX
----------
      3000

Another 12c syntax would be


SELECT sal secondmax
FROM emp
ORDER BY sal DESC
OFFSET 1 ROW
FETCH FIRST 1 ROW ONLY;

 SECONDMAX
----------
      3000

Testing for (non-)empty string in shell

One way to test for (non-)empty string is to use test and -z (-n)


$ x=foo
$ test -z "$x"
$ echo $?
1

This is mostly seen with an if and [ -z … ] syntax


$ y=bar
$ if [ -n "$y" ];
  then echo non-empty; 
  fi
non-empty

Instead of a variable, it could be the output of a script.

Like


if [ -n "$(grep ORA- alertDB01.log)" ] 
then 
  echo there is an error in the alert log
else
  echo "fine :)"
fi

This will work for years until one day you get :


ksh: no space

Why that? This is the way the shell works. Your shell (here ksh on AIX) starts having errors as soon as your subshell (here the grep) is exhausting the space.


$ wc -l alertDB01.log
       2 alertDB01.log
$ if [ -n "$(grep ORA- alertDB01.log)" ]; 
  then echo non-empty; 
  else echo "fine :)"; 
  fi
non-empty
$ wc -l alertDB01.log
   75025 alertDB01.log
$ if [ -n "$(grep ORA- alertDB01.log)" ]; 
  then echo non-empty; 
  else echo "fine :)"; 
  fi
ksh: no space

You got a memory error, how the shell will react is random (core dump, errors, continue, crashes). It will just bug and you do not want this.

There is more than one to circumvent this. For instance you could use the return code of grep


$ if grep ORA- alertDB01.log >/dev/null;
  then echo non-empty;
  else echo "fine :)";
  fi
non-empty

Different shells (Bash / Bourne) and different OSs (Linux / AIX / HPUX) may react differently. If AIX crashed with a 50’000 lines, it may scale up to millions of lines in recent Linux’s – but still use trucks of memory

The long long route to Kerberos

If you want to single-sign-on to your database with your Windows credentials, be aware, it is hard! But the benefit is quite valuable, no more saved password on the client, central password management and user expiration, compliance to the security guidelines, and at no extra cost

Landscape for my setup

  • One PC with Windows (PC01.EXAMPLE.COM)
  • One DB Server with Unix (DBSRV01.EXAMPLE.COM)
  • One Microsoft Active Directory Server (MSAD01.EXAMPLE.COM)

Username

  • user01

Tools for troubleshooting

My Software

  • PC : Oracle Client 11.2.0.3
  • Unix : Oracle Server 11.2.0.4
  • On AD : MSAD 2008

There are a lot of buggy releases (it makes me think Oracle does not test Kerberos properly)

Some hits :
11.2.0.2 : Bug 12635212 : TCP/88 is not working.
12.1.0.1 : Bug 17890382 : ZTK return value: 6

Also your PC must be using Kerberos (which is the case if you login to your Active Directory). The DB server needs some client libraries (krb5.client.rte on AIX).

System changes:

  • PC : edit etc\services
  • 
    C:\> find " 88" %SystemRoot%\system32\drivers\etc\services
    
    ---------- C:\WINDOWS\SYSTEM32\DRIVERS\ETC\SERVICES
    kerberos   88/tcp    kerberos5 krb5 kerberos-sec  #Kerberos
    kerberos   88/udp    kerberos5 krb5 kerberos-sec  #Kerberos
    

  • Unix : edit /etc/services
  • 
    $ grep -w 88 /etc/services
    kerberos  88/tcp  kerberos5 krb5  # Kerberos
    kerberos  88/udp  kerberos5 krb5  # Kerberos
    

  • On AD : disable pre-authentication
    this option has to be set for every user, under user -> user01 -> Properties -> Account -> Account options -> Select “Do not require Kerberos preauthentication”

Those are quite painful. There is a bug 2458563 fixed in 8.1.7.4.99 (whatever it means) that should have addressed pre-authentication. still required on 11.2.0.4 apparently no longer needed with a 11.2.0.4 client
Editing etc/services to add the “kerberos5″ string means you need admin rights on Windows and root on Unix.

Okay, now you need to create the config files. You probably should use Kerberos v5 MIT.

Kerberos5 was released in 1993, not sure why you want to use something older than this… Okay, for kerberos4, released in the 80’s, you would need on the PC and on the DB Server something like


EXAMPLE.COM
EXAMPLE.COM MSAD01.EXAMPLE.COM admin server

Otherwise you need to specify : sqlnet.kerberos5_conf_mit=true
I have an open SR to support regarding : 12c upgrade guide
The SQLNET.KERBEROS5_CONF_MIT networking parameter is no longer supported in sqlnet.ora

Okay, here the configuration files

krb5.conf on the database server and on the PC


[libdefaults]
default_realm = EXAMPLE.COM

[realms]
EXAMPLE.COM = {
  kdc = MSAD01.EXAMPLE.COM
}

[domain_realm]
.example.com = EXAMPLE.COM
example.com = EXAMPLE.COM

The config file location (kerberos4 or 5) is specified by sqlnet.kerberos5_conf.

There should be a technical account for your db server created on the MSAD that matched your db server.

On Active Directory, you create a user (e.g. : oracle_DBSRV01) who must not change password on first login. Then you extract the keytab with ktpass


ktpass.exe -princ oracle/dbsrv01.example.com@EXAMPLE.COM -mapuser oracle_DBSRV01 -crypto all -pass password -out c:\dbsrv01.keytab

As an Oracle DBA, you will probably ask this to another team who is used to Kerberos.

To verify it, you can list the content of the keytab


$ $ORACLE_HOME/bin/oklist -k dbsrv01.keytab
Kerberos Utilities for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production on 16-JAN-2014 12:45:08

Copyright (c) 1996, 2013 Oracle.  All rights reserved.

Service Key Table: dbsrv01.keytab

Ver      Timestamp                    Principal
 4  01-Jan-1970 01:00:00  oracle/dbsrv01.example.com@EXAMPLE.COM

The principal name must match your full qualified host name. You cannot use a DNS alias.

On your PC check for the login name :


PS> $o = New-Object DirectoryServices.DirectorySearcher; 
  $o.Filter = 'servicePrincipalName=oracle/dbsrv01.example.com'; 
  $o.FindOne().properties.samaccountname
oracle_DBSRV01

And you can verify the principal of that user


C:\> setspn -L oracle_DBSRV01
Registered ServicePrincipalNames for CN=oracle_DBSRV01,OU=MiscUsers,DC=example,DC=com
:
        oracle/dbsrv01.example.com

Now you’ve got your keytab, this must be on the DB Server only (and must be readable for oracle). The location is specified by SQLNET.KERBEROS5_KEYTAB.

Next step is the credential cache (CC) parameter. On your PC with the Oracle 11g client, you must set sqlnet.kerberos5_cc_name to OSMSFT://
On the server it is not neeeded. On Oracle 12c client, you must set it MSLSA:, but due to bug 17890382, it is not working yet (metalink comment : We will have to wait […] bugs are under investigation).

But before you start, you may want to test the ticket.

On Unix, you can get the ticket with kinit and check it with klist. You need to have your configuration in /etc/krb5/krb5.conf (OS Dependent). Do not forget to destroy your credential cache with kdestroy / okdstry while testing

For the DB Server


$ /usr/krb5/bin/kinit -k -t dbsrv01.keytab oracle/dbsrv01.example.com@EXAMPLE.COM
$ /usr/krb5/bin/klist
Ticket cache:  FILE:/var/krb5/security/creds/krb5cc_99
Default principal:  oracle/dbsrv01.example.com@EXAMPLE.COM

Valid starting     Expires            Service principal
01/16/14 17:41:26  01/17/14 03:41:26  krbtgt/EXAMPLE.COM@EXAMPLE.COM
        Renew until 01/17/14 17:41:26


$ /usr/krb5/bin/kinit user01@EXAMPLE.COM
Password for user01@EXAMPLE.COM:
Ticket cache:  FILE:/var/krb5/security/creds/krb5cc_99
Default principal:  user01@EXAMPLE.COM

Valid starting     Expires            Service principal
01/16/14 17:35:57  01/17/14 03:35:57  krbtgt/EXAMPLE.COM@EXAMPLE.COM
        Renew until 01/17/14 17:35:57

Now we can test the okinit (oracle kinit) tool to do the same. There are some specific trace options that could be set

Here the complete sqlnet.ora on the server


DIAG_ADR_ENABLED = OFF
TRACE_DIRECTORY_OKINIT = /var/opt/oracle/krb/cc
TRACE_FILE_OKINIT = okinit
TRACE_LEVEL_OKINIT = SUPPORT
SQLNET.AUTHENTICATION_KERBEROS5_SERVICE = oracle
SQLNET.AUTHENTICATION_SERVICES= (BEQ,KERBEROS5)
SQLNET.KERBEROS5_CC_NAME = /var/opt/oracle/krb/cc/krb5cc_99
SQLNET.KERBEROS5_CONF = /var/opt/oracle/krb/krb5.conf
SQLNET.KERBEROS5_KEYTAB = /var/opt/oracle/krb/dbsrv01.keytab
sqlnet.kerberos5_conf_mit=true

Note the authentication service. If kerberos is not working, you may no longer be able to log / as sysdba and also some db links may no longer work.

Also note SQLNET.AUTHENTICATION_KERBEROS5_SERVICE, which is the prefix of your principal, oracle/dbsrv01.example.com@EXAMPLE.COM

Then we use okinit as we did for kinit


$ $ORACLE_HOME/bin/okinit -k -t dbsrv01.keytab oracle/dbsrv01.example.com@EXAMPLE.COM

Kerberos Utilities for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production on 16-JAN-2014 17:52:21

Copyright (c) 1996, 2013 Oracle.  All rights reserved.
$ $ORACLE_HOME/bin/oklist

Kerberos Utilities for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production on 16-JAN-2014 17:55:27

Copyright (c) 1996, 2013 Oracle.  All rights reserved.

Ticket cache: /var/opt/oracle/krb/cc/krb5cc_99
Default principal: oracle/dbsrv01.example.com@EXAMPLE.COM

   Valid Starting           Expires            Principal
16-Jan-2014 17:54:30  17-Jan-2014 01:54:30  krbtgt/EXAMPLE.COM@EXAMPLE.COM

$ $ORACLE_HOME/bin/okinit user01@EXAMPLE.COM

Kerberos Utilities for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production on 16-JAN-2014 18:15:02

Copyright (c) 1996, 2013 Oracle.  All rights reserved.

Password for user01@EXAMPLE.COM:
$ $ORACLE_HOME/bin/oklist

Kerberos Utilities for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production on 16-JAN-2014 18:15:12

Copyright (c) 1996, 2013 Oracle.  All rights reserved.

Ticket cache: /var/opt/oracle/krb/cc/krb5cc_99
Default principal: user01@EXAMPLE.COM

   Valid Starting           Expires            Principal
16-Jan-2014 18:15:06  17-Jan-2014 02:15:02  krbtgt/EXAMPLE.COM@EXAMPLE.COM

In case of error, grep for “k5″ in the trace file.

Do the same on the PC01. To test okinit, temporary change the CC cache to a file


sqlnet.authentication_services=(kerberos5)
SQLNET.AUTHENTICATION_KERBEROS5_SERVICE=oracle

DIAG_ADR_ENABLED=off
TRACE_DIRECTORY_CLIENT=C:\oracle\krb5
TRACE_UNIQUE_CLIENT=on
TRACE_FILE_CLIENT=kerb_client

sqlnet.kerberos5_conf_mit=true
sqlnet.kerberos5_conf=C:\oracle\krb5\krb5.conf
#sqlnet.kerberos5_cc_name=OSMSFT://
sqlnet.kerberos5_cc_name=C:\oracle\krb5\krbcc

and test as in Unix.

Some errors will be easier to find out with a network sniffer on port 88

With AIX


tcpdump -v -v port 88

On Windows
Start -> Microsoft Network Monitor -> Microsoft Network Monitor -> New capture -> Display filter


Frame.Ethernet.Ipv4.TCP.Port == 88 or Frame.Ethernet.Ipv4.UDP.Port == 88

-> Apply -> Start

If you for instance only see UDP packets but no TCP packets, you probably hit bug 12635212.

I still have some KDC_ERR_S_PRINCIPAL_UNKNOWN errors with my working setup, don’t worry about those.

Ok, now that okinit works, the next step is to log in the database.

Check the adapters on the db server


$ adapters

Installed Oracle Net transport protocols are:

    IPC
    BEQ
    TCP/IP
    SSL
    RAW

Installed Oracle Net naming methods are:

    Local Naming (tnsnames.ora)
    Oracle Directory Naming
    Oracle Host Naming
    Oracle Names Server Naming
    NIS Naming

Installed Oracle Advanced Security options are:

    RC4 40-bit encryption
    RC4 56-bit encryption
    RC4 128-bit encryption
    RC4 256-bit encryption
    DES40 40-bit encryption
    DES 56-bit encryption
    3DES 112-bit encryption
    3DES 168-bit encryption
    AES 128-bit encryption
    AES 192-bit encryption
    AES 256-bit encryption
    MD5 crypto-checksumming
    SHA-1 crypto-checksumming
    Kerberos v5 authentication
    RADIUS authentication

Create the user on the database db01 on the server dbsrv01. You need to have OS_AUTHENT_PREFIX=”” and do not set REMOTE_OS_AUTHENT (if you have it set, why would you need Kerberos?)


SQL> create user user01 identified externally as 'user01@example.com';
User created.
SQL> grant create session to user01;
Grant succeeded.

Connect from the PC


$ sqlplus -L /@db01

SQL*Plus: Release 11.2.0.3.0 Production on Thu Jan 16 18:40:43 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

This also works in Toad, SQL-Developer and other tools using the OCI thick client. Just let “User” and “Password” blank.

In SQLDeveloper, make sure you do not check Kerberos but you use OCI Thick and no username and password

There is probably a way to do it with the jdbc thin client as document in Note 1523651.1, I have not gone that far yet

Update:
Note 303436.1 : Improper format of configuration file: Remove TAB characters from KRB5.conf file. Replace with spaces.

Do you really need ASO?

If you only use the Advanced Security Option for SSL, you may not need to pay for it !

License 11.2
When used with Oracle Real Application Clusters, Oracle Advanced Security SSL/TLS is included.

But also
License 11.1
Network encryption (native network encryption and SSL/TLS) and strong authentication services (Kerberos, PKI, and RADIUS) are no longer part of Oracle Advanced Security and are available in all licensed editions of all supported releases of the Oracle database.

If SSL/TLS is no longer part of Advanced Security, what is then Oracle Advanced Security SSL/TLS ?

Configure ORACLE_HOME for SQL Developer

The release V4.0 of sql developer is available for download : oracle.com/technetwork/developer-tools/sql-developer/downloads
The doc is there : docs.oracle.com/cd/E39885_01/index.htm

And read Jeff Smith twit’s and blog

I requested some time ago a 64bit Windows version with JDK on Oracle SQL Developer Exchange and once again it got accepted.

Okay, I have on my PC two Oracle homes : one for 32 bits and one for 64 bits. The 32 bits is first in PATH.

When I start SQL Developer, I could not use the OCI thick driver which is required when connecting via LDAP or TNSNAMES. SQL Developer was failing with error :
ocijdbc11.dll: Can't load IA 32-bit .dll on a AMD 64-bit platform

Apart from changing the path in the Environment variables or in .bat file, it is possible to add the following line in sqldeveloper/bin/sqldeveloper.conf (where dbhome_2 is a 64bits home):
AddVMOption -Djava.library.path=C:\oracle\product\12.1.0\dbhome_2\bin

If you need to connect to Sybase ASE (or MSSQL), download the driver here : http://sourceforge.net/projects/jtds and add it as Datasbase/Third-Party Driver

For MySQL go there : http://dev.mysql.com/downloads/connector/j/ and download the Platfrom Independant ZIP file.

OracleContext as top object in Active Directory

When you do expand your Active Directory schema, it is not reversible; how to decide to use the OracleContext as a top object or not?

On the one hand, for tnsnames resolution, you could hide your context down in your AD structure and change the path in ldap.ora


DIRECTORY_SERVER_TYPE=AD
DEFAULT_ADMIN_CONTEXT="OU=Oracle,OU=Misc,DC=example,DC=com"

For debugging, I set TNSPING.TRACE_LEVEL=ADMIN and TNSPING.TRACE_DIRECTORY=C:\TEMP


PS> TNSPING DB01
Used LDAP adapter to resolve the alias
Attempting to contact (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(
  PROTOCOL=TCP)(HOST=SRV01)(PORT=1521)))(CONNECT_DATA=(SID=DB01)))
OK (20 msec)

PS> Select-String "DB01" C:\temp\tnsping.trc

nnflrne1: Quering the directory for dn: cn=DB01,cn=OracleContext,
  OU=Oracle,OU=Misc,DC=example,DC=com
nnflqbf: Search:  Base: cn=DB01,cn=OracleContext,OU=Oracle,OU=Misc,
  DC=example,DC=com; Scope: 0; filter: (objectclass=*) returns 0x0
nnflgne:   DN : cn=DB01,cn=OracleContext,OU=Oracle,OU=Misc,
  DC=example,DC=com
nsc2addr: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.2)
  (PORT=1521))(CONNECT_DATA=(SID=DB01)))

So far so good; but on the other hand, it prevents you from using connection identifier like DB01.EXAMPLE.COM


PS> TNSPING DB01.EXAMPLE.COM
TNS-03505: Failed to resolve name

PS> Select-String "DB01" C:\temp\tnsping.trc

nnflfdn: Turning simplified name DB01.EXAMPLE.COM into a dn.
nnflfdn:     The resulting dn is cn=DB01,cn=OracleContext,
  dc=EXAMPLE,dc=COM
nnflrne1: Quering the directory for dn: cn=DB01,cn=OracleContext,
  dc=EXAMPLE,dc=COM
nnflqbf: Search:  Base: cn=DB01,cn=OracleContext,dc=EXAMPLE,
  dc=COM; Scope: 0; filter: (objectclass=*) returns 0x20
nnfun2a: address for name "DB01.EXAMPLE.COM" not found

This no longer works. Your database domain name must match your Active Directory domain name and your object must be a top object domain

Platform guide for Windows : Oracle Context is the top-level Oracle entry in the Active Directory tree

It is probably wiser to follow this recommendation.

Also new in 11gR2 is NAMES.LDAP_AUTHENTICATE_BIND=TRUE, which removes the need of allowing anonymous ldap bind in AD

Changing the log apply delay (DelayMins)

Whenever you change the DelayMins setting in Dataguard, you must remember it affects only logs that have not been shipped yet.


DGMGRL> show database sDB01 delaymins
  DelayMins = '5'

DGMGRL> edit DATABASE sDB01 set property delaymins=2;
Property "delaymins" updated

ARC3: Archive log thread 1 sequence 3199 available in 5 minute(s)
Tue Dec 03 15:34:59 2013
ARC0: Archive log thread 1 sequence 3200 available in 2 minute(s)
Tue Dec 03 15:35:15 2013

SQL> select sysdate, SEQUENCE# from v$managed_standby where process='MRP0'

SYSDATE              SEQUENCE#
------------------- ----------
2013-12-03_15:38:00       3199

The old logs are not affected. Let’s wait until the latest Delay=5 got applied.


Tue Dec 03 15:40:02 2013
Media Recovery Log /u01/app/oracle/admin/DB01/arch/DB01_1_3199_827686279.arc
Media Recovery Log /u01/app/oracle/admin/DB01/arch/DB01_1_3200_827686279.arc
Media Recovery Log /u01/app/oracle/admin/DB01/arch/DB01_1_3201_827686279.arc
Media Recovery Log /u01/app/oracle/admin/DB01/arch/DB01_1_3202_827686279.arc
Media Recovery Log /u01/app/oracle/admin/DB01/arch/DB01_1_3203_827686279.arc
Media Recovery Log /u01/app/oracle/admin/DB01/arch/DB01_1_3204_827686279.arc
Media Recovery Log /u01/app/oracle/admin/DB01/arch/DB01_1_3205_827686279.arc

All files which had a delay=2 were “pending” apply. Now we got the apply=2 behavior

Same if you increase the value


DGMGRL> edit DATABASE sDB01 set property delaymins=30;
Property "delaymins" updated

SQL> select sysdate, SEQUENCE# from v$managed_standby where process='MRP0';

SYSDATE              SEQUENCE#
------------------- ----------
2013-12-03_15:49:04       3224

ARC3: Archive log thread 1 sequence 3224 available in 2 minute(s)
Tue Dec 03 15:47:22 2013

Here again, the old logs are not affected, we need to wait until the last delay=2 got applied to get a delay=30 behavior.

While you cannot change the delay, there is still a way to workaround the problem.

If you want to immediately increase log to 30 minutes, turn off applying for half an hour.


DGMGRL> edit DATABASE sDB01 set state='APPLY-OFF';
Succeeded.
-- coffee break
DGMGRL> edit DATABASE sDB01 set state='APPLY-ON';
Succeeded.

If you want to decrease log from 30 to 2 minutes right now and immediately apply the old logs which have reached this threshold, use sqlplus


ARC1: Archive log thread 1 sequence 3253 available in 30 minute(s)
Tue Dec 03 16:01:26 2013
ARC3: Archive log thread 1 sequence 3254 available in 2 minute(s)
Tue Dec 03 16:01:37 2013

DGMGRL> edit DATABASE sDB01 set state='APPLY-OFF';
Succeeded.

SQL> recover automatic standby database until time '2013-12-03_16:01:30';
Media recovery complete.

DGMGRL> edit DATABASE sDB01 set state='APPLY-ON';
Succeeded.

I wrote on delay standby failover here : here

specify TNSNAMES for one program

Monday I wrote on tnsping.exe inconsistencies. Actually there is one good thing in having Oracle Client on Windows looking in the current directory first : you can set one tnsnames for a specific shortcut ! It is quite a viable alternative to .bat files with set TNS_ADMIN=path.

Demo :

First I create a small EXE in C#

HelloWorld.cs:


using System;
using System.Threading;
using Oracle.DataAccess.Client;

class HelloWorld
{
  static void Main() {
    OracleConnection connection=
      new OracleConnection("Data Source=DB01; User Id=scott; password=tiger");
    try {
      connection.Open();
      Console.WriteLine("Msg: " + (new OracleCommand(
        "select * from global_name",connection)).ExecuteScalar());
      connection.Close();
    } catch(Exception e) {
      Console.WriteLine("Exception Occured :{0}",e.Message);
    } finally {
      connection.Dispose();
    }
    Thread.Sleep(5000);
  }
}

Compile


C:\Windows\Microsoft.NET\Framework\v4.0.*\csc.exe /R:C:\oracle\product\11.2.0\client_1\odp.net\bin\4\Oracle.DataAccess.dll HelloWorld.cs

Create a specific tnsnames and sqlnet

Tnsnames.ora


DB01.example.com=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=DB01)(PORT=1521)))(CONNECT_DATA=(SID=DB01)))

SQLNET.ora


NAMES.DIRECTORY_PATH=TNSNAMES
NAMES.DEFAULT_DOMAIN=EXAMPLE.COM

test


C:\TEMP> HelloWorld
Msg: DB01.EXAMPLE.COM

To create a desktop icon with the correct WorkingDirectory with powershell


PS> $ws =  New-Object -comObject WScript.Shell
PS> $desktop = [Environment]::GetFolderPath("Desktop")
PS> $s = $ws.createshortcut($desktop+"\HelloWorld.lnk")
PS> $s.TargetPath = "C:\TEMP\HelloWorld.exe"
PS> $s.WorkingDirectory = "C:\TEMP"
PS> $s.Save()

sqlnet.ora, sqlplus.exe and tnsping.exe inconsistencies

if you use tnsping.exe and sqlplus.exe, the way the sqlnet.ora and tnsnames.ora are located differs

Let’s take the following setup


C:\tmp>type dir1\sqlnet.ora
NAMES.DEFAULT_DOMAIN = (EXAMPLE.COM)
NAMES.DIRECTORY_PATH = (TNSNAMES)

C:\tmp>type dir1\tnsnames.ora
db.example.com=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=srv1.example.com)(PORT=1521))(CONNECT_DATA=(SID=db01)))
db.example.org=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=srv1.example.org)(PORT=1521))(CONNECT_DATA=(SID=db01)))

C:\tmp>type dir2\sqlnet.ora
NAMES.DEFAULT_DOMAIN = (EXAMPLE.ORG)
NAMES.DIRECTORY_PATH = (TNSNAMES)

C:\tmp>type dir2\tnsnames.ora
db.example.com=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=srv2.example.com)(PORT=1521))(CONNECT_DATA=(SID=db02)))
db.example.org=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=srv2.example.org)(PORT=1521))(CONNECT_DATA=(SID=db02)))

You set TNS_ADMIN to dir1 and your current directory is dir2.

Let’s try TNSPING.EXE first


C:\tmp>cd dir2

C:\tmp\dir2>set TNS_ADMIN=C:\tmp\dir1

C:\tmp\dir2>tnsping db

TNS Ping Utility for 64-bit Windows: Version 12.1.0.1.0 - Production on 25-NOV-2013 15:47:31

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:
C:\tmp\dir1\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=srv2.example.com)(PORT=1521))(CONNECT_DATA=(SID=db02)))
OK (0 msec)

TNSPING.EXE is using the sqlnet.ora in %TNS_ADMIN% directory (EXAMPLE.COM domain) and the tnsnames.ora in the current directory (db02)

Let’s try with sqlplus


C:\tmp>cd dir2

C:\tmp\dir2>set TNS_ADMIN=C:\tmp\dir1

C:\tmp\dir2>sqlplus -L system@db

SQL*Plus: Release 12.1.0.1.0 Production on Mon Nov 25 16:01:15 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from global_name;

GLOBAL_NAME
-------------------------------------------------
DB02.EXAMPLE.ORG

SQLPLUS.EXE is using the sqlnet.ora in the current directory (EXAMPLE.ORG) and the tnsnames.ora in the current directory (db02)

This does not reproduce on Linux

unreadable output file in powershell

If you redirect a not-completly-string output (like a spfile) to a file in powershell, you may not see the same in the file as in the output

  • without redirection
    
    PS C:\> Select-String "compatible" .\spfileDB01.ora
    spfileDB01.ora:13:*.compatible='11.2.0.4.0'
    
  • with redirection
    
    PS> Select-String "compatible" .\spfileDB01.ora > compatible.txt
    PS> vim -b .\compatible.txt
    ÿþ^M^@
    ^@s^@p^@f^@i^@l^@e^@D^@B^@0^@0^@1^@.^@o^@r^@a^@:^@1^@3^@:^@*^@.^@c^@o^@m^@p^@a^@t^@i^@b^@l^@e^@=^@'^@1^@1^@.^@2^@.^@0^@.^@4^@.^@0^@'^@^M^@
    ^@
    
  • With redirection and conversion to ascii
    
    PS> Select-String "compatible" .\spfileDB01.ora | 
      Out-File -Encoding ASCII .\compatible.txt
    
    PS> vim .\compatible.txt
    
    spfileDB01.ora:13:*.compatible='11.2.0.4.0'
    

With Out-File (instead of >), you can specify the encoding, which does the trick

hot to bypass requiretty in sudo

You can execute it a command without password from the commande line


$ sudo -l
User lsc may run the following commands on this host:
    (root) NOPASSWD: /usr/local/bin/helloworld
$ sudo /usr/local/bin/helloworld
Hello World!

Now you try to run it via cron and you get


sudo: sorry, you must have a tty to run sudo

The message is clear, you need a terminal.

Either you edit your sudoers files to disable requiretty, or you just get yourself a terminal.

Maybe you tried to assign a pseudo terminal with ssh -t, but you may get an error if ssh has no local tty


Pseudo-terminal will not be allocated because stdin is not a terminal.

Don’t despair, read man ssh


Multiple -t options force tty allocation, even if ssh has no local tty.

Let’s try


* * * * * ssh -t -t sudo /usr/local/bin/helloworld >> /tmp/txt

This should work, providing you configured ssh keys :)

multiconsumer Queue with an Oracle Type from Java

You have a multi consumer queue with a user defined type


CREATE TYPE topic_message AS OBJECT(Subject VARCHAR2(30),Text VARCHAR2(80))
/
BEGIN
  dbms_aqadm.create_queue_table(
    'topic_queue_table', 
    'topic_message', 
    Multiple_consumers=>TRUE);
  dbms_aqadm.create_queue(
    'toy_topic', 
    'topic_queue_table');
  dbms_aqadm.start_queue(
    'toy_topic');
END;
/

You create the corresponding java class with JPublisher (check the documentation of Database JPublisher User’s Guide 12c Release 1 (12.1) from which I took this example). JPublisher does need to be installed, it is already in your Oracle Home.


CLASSPATH=$ORACLE_HOME/jdbc/lib/ojdbc5.jar ### for java 5
CLASSPATH=$CLASSPATH:$ORACLE_HOME/sqlj/lib/translator.jar
CLASSPATH=$CLASSPATH:$ORACLE_HOME/sqlj/lib/runtime12.jar
CLASSPATH=$CLASSPATH:$ORACLE_HOME/jlib/jta.jar
CLASSPATH=$CLASSPATH:$ORACLE_HOME/rdbms/jlib/aqapi.jar
CLASSPATH=$CLASSPATH:$ORACLE_HOME/rdbms/jlib/jmscommon.jar
CLASSPATH=$CLASSPATH:.

export CLASSPATH

LIBPATH=$ORACLE_HOME/lib ### or LD_LIBRARY_PATH on some plateforms
export LIBPATH

JAVA_HOME=$ORACLE_HOME/jdk
export JAVA_HOME

PATH=$ORACLE_HOME/bin:$JAVA_HOME/bin:/usr/bin:/bin
export PATH

java oracle.jpub.Doit -user=scott/tiger -sql=toy_topic:ToyTopic


SCOTT.TOPIC_MESSAGE
SCOTT.TOY_TOPIC
Note: /u01/app/oracle/java/ToyTopic.java uses unchecked or unsafe operations.
Note: Recompile with -Xlint:unchecked for details.

Now I can publish and receive from that queue


import java.util.Properties;
import java.sql.*;
import javax.jms.*;
import oracle.jms.*;

public class Test {
  public static void main(String argv[]) throws SQLException, 
JMSException {
    String url = "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS="+
"(PROTOCOL=TCP)(Host=srv01)(Port=1521))(CONNECT_DATA=(SID=DB01)))";
    Properties props = new Properties();
    props.setProperty("user", "SCOTT");
    props.setProperty("password", "tiger");
    DriverManager.registerDriver(new oracle.jdbc.
OracleDriver());
    java.sql.Connection conn = DriverManager.getConnection(url, props);

    ToyTopic topic = new ToyTopic(conn);
    TopicMessage m = new TopicMessage("scooby doo", "lights out");

    topic.publish(m, new String[]{"ToyParty", "ToyFactory"});
    System.out.println("Message broadcasted: " + m.getSubject() 
+ " " + m.getText());
    m = new TopicMessage("dalmatian", "solve the puzzle");
    topic.publish(m, new String[]{"ToyParty", "ToyLand"});
    System.out.println("Message broadcasted: " + m.getSubject() 
+ " " + m.getText());

    m = topic.receive("ToyParty");
    System.out.println("ToyParty receive " + m.getSubject() 
+ " " + m.getText());
    m = topic.receive("ToyParty");
    System.out.println("ToyParty receive " + m.getSubject() 
+ " " + m.getText());

    m = topic.receiveNoWait("ToyLand");
    System.out.println("ToyFactory receive " + m.getSubject() 
+ " " + m.getText());
    m = topic.receiveNoWait("ToyFactory");
    System.out.println("ToyFactory receive " + m.getSubject() 
+ " " + m.getText());
    m = topic.receiveNoWait("ToyFactory");
  }
}

Try it :


$ javac Test.java
$ java Test
Message broadcasted: scooby doo lights out
Message broadcasted: dalmatian solve the puzzle
ToyParty receive scooby doo lights out
ToyParty receive dalmatian solve the puzzle
ToyFactory receive dalmatian solve the puzzle
ToyFactory receive scooby doo lights out

strings larger than 4000 in 12c

Back in Oracle 7, the maximum length for VARCHAR2 was 2000. In 11gR2, it is still 4000 for varchar2/char columns and for literals. Any attempt to use something larger will produce an infamous ora-910, ora-1704 or ora-1489 error.


SQL> create table t(x varchar2(5000));
create table t(x varchar2(5000))
                           *
ERROR at line 1:
ORA-00910: specified length too long for its datatype

SQL> select 'x-
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx-
... 100 times 
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx-
' from dual;

select 'x-
       *
ERROR at line 1:
ORA-01704: string literal too long

SQL> select 'x'||
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'||
... 100 times 
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'||
'x' from dual;

select 'x'||
       *
ERROR at line 1:
ORA-01489: result of string concatenation is too long

In 12c it is possible to extends the varchar2 to 32k.

This is not the default and it is controlled by max_string_size


SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup upgrade quiet
ORACLE instance started.
Database mounted.
Database opened.
SQL> alter system set max_string_size=extended;
System altered.
SQL> @?/rdbms/admin/utl32k
SQL> shu immediate
Database closed.
Database dismounted.
SQL> startup quiet
ORACLE instance started.
Database mounted.
Database opened.

Now we can create, insert and select longer strings.


SQL> create table t(x varchar2(6000));
Table created.
SQL> desc t
 Name                  Null?    Type
 --------------------- -------- ----------------
 X                              VARCHAR2(6000)
SQL> insert into t values ('-
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx-
... 100 times 
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');
1 row created.
SQL> insert into t values (
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'||
... 100 times 
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');
1 row created.

Oracle 8i came with a bunch of issue (cannot index a varchar2(4000) column) because the datatype was really increased to 4000. In 12c, however, it is silently using clob.


SQL> select TABLE_NAME,COLUMN_NAME,SEGMENT_NAME from user_lobs where table_name='T';

TABLE_NAME COLUMN_NAM SEGMENT_NAME
---------- ---------- ------------------------------
T          X          SYS_LOB0000022083C00001$$

It also works for NVARCHAR2 and RAW (who wasn’t increased to 4000). But not for CHAR.

This is a smart move to provide larger text fields to developers. On the other hand, why limit it to 32k if it is a clob? VARCHAR(MAX) in SQL Server does not have such a limitation.


SQL> create table t(x varchar2(32768));
create table t(x varchar2(32768))
                           *
ERROR at line 1:
ORA-00910: specified length too long for its datatype

SQL> select 'x'||
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'||
... 999 times 
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'||
'x' from dual;
select
*
ERROR at line 1:
ORA-01489: result of string concatenation is too long

Advanced Queuing hello world

First for those who are looking for the difference between Queuing, Advanced Queuing (AQ) and Streams Advanced Queuing, there is none. There is no Beginner Queuing and Advanced Queuing was renamed to Streams advanced queuing when streams was popular and renamed back to Advanced Queuing was streams was deprecated.

I am the provider, I give you messages to reads. I do not need to wait you to read the first one before I can send you the next one.

You are the receiver, you read them one by one, whenever you have time.

First a few grants


SQL> grant execute on dbms_aq to scott;
Grant succeeded.
SQL> grant execute on dbms_aqadm to scott;
Grant succeeded.
SQL> grant execute on dbms_aqin to scott;
Grant succeeded.

The queue table and queue


SQL> EXEC dbms_aqadm.create_queue_table('QT', 'SYS.AQ$_JMS_TEXT_MESSAGE')
PL/SQL procedure successfully completed.
SQL> EXEC dbms_aqadm.create_queue('Q','QT')
PL/SQL procedure successfully completed.
SQL> EXEC dbms_aqadm.start_queue('Q')
PL/SQL procedure successfully completed.

You start listening, if there is nothing to read, you wait


SQL> set serverout on
SQL> DECLARE
  2     dequeue_options            DBMS_AQ.DEQUEUE_OPTIONS_T;
  3     message_properties         DBMS_AQ.MESSAGE_PROPERTIES_T;
  4     message_handle             RAW (16);
  5     msg                        SYS.AQ$_JMS_TEXT_MESSAGE;
  6  BEGIN
  7    DBMS_AQ.dequeue (
  8      queue_name           => 'Q',
  9      dequeue_options      => dequeue_options,
 10      message_properties   => message_properties,
 11      payload              => msg,
 12      msgid                => message_handle);
 13    DBMS_OUTPUT.PUT_LINE(msg.TEXT_VC);
 14    COMMIT;
 15  END;
 16  /

It is waiting

I write a first message.


SQL> DECLARE
  2    enqueue_options      DBMS_AQ.ENQUEUE_OPTIONS_T;
  3    message_properties   DBMS_AQ.MESSAGE_PROPERTIES_T;
  4    message_handle       RAW (16);
  5    msg                  SYS.AQ$_JMS_TEXT_MESSAGE;
  6  BEGIN
  7    msg := SYS.AQ$_JMS_TEXT_MESSAGE.construct;
  8    msg.set_text('HELLO PLSQL WORLD !');
  9    DBMS_AQ.ENQUEUE (
 10      queue_name           => 'Q',
 11      enqueue_options      => enqueue_options,
 12      message_properties   => message_properties,
 13      payload              => msg,
 14      msgid                => message_handle);
 15    COMMIT;
 16  END;
 17  /

PL/SQL procedure successfully completed.

Now you receive it !


HELLO PLSQL WORLD !
PL/SQL procedure successfully completed.
SQL>

I write one from Java using Java Message Service (JMS)


import java.util.Properties;
import java.sql.*;
import javax.jms.*;
import oracle.jms.*;

public class JMS {
  public static void main(String argv[]) throws JMSException, SQLException {
    String url = "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=SRV01)(Port=1521))(CONNECT_DATA=(SID=DB01)))";
    Properties props = new Properties();
    props.setProperty("user", "SCOTT");
    props.setProperty("password", "tiger");
    DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
    java.sql.Connection conn = DriverManager.getConnection(url, props);
    QueueConnection qconn = AQjmsQueueConnectionFactory.createQueueConnection(conn);
    QueueSession qsess = qconn.createQueueSession(true, 0);
    Queue q = qsess.createQueue("Q");
    QueueSender qsend = qsess.createSender(q);
    TextMessage msg;
    msg = qsess.createTextMessage("TEST JAVA");
    qsend = qsess.createSender(q);
    qsend.send(msg);
    qsess.commit();
  }
}


$ CLASSPATH=$ORACLE_HOME/jdbc/lib/ojdbc5.jar
$ CLASSPATH=$CLASSPATH:$ORACLE_HOME/jlib/jta.jar
$ CLASSPATH=$CLASSPATH:$ORACLE_HOME/xdk/lib/xmlparserv2.jar
$ CLASSPATH=$CLASSPATH:$ORACLE_HOME/rdbms/jlib/xdb.jar
$ CLASSPATH=$CLASSPATH:$ORACLE_HOME/rdbms/jlib/aqapi.jar
$ CLASSPATH=$CLASSPATH:$ORACLE_HOME/rdbms/jlib/jmscommon.jar
$ CLASSPATH=$CLASSPATH:.
$ export CLASSPATH
$ javac JMS.java
$ java JMS

ojdbc5 is for java5

Now you read the next message (and you do not have to wait, there is one in the queue)


SQL> /
TEST JAVA
PL/SQL procedure successfully completed.

Read more in the Advanced Queuing User’s Guide

Change background of Oracle Entreprise Manager

If you have more than one OEM and want a red status bar in your production OEM, here the proceedings

Stop your cloud control


emctl stop oms -all

Unzip the $MW/oracle_common/modules/oracle.adf.view_11.1.1/adf-richclient-impl-11.jar in a new directory


$MW/jdk16/jdk/bin/jar -xvf $MW/oracle_common/modules/oracle.adf.view_11.1.1/adf-richclient-impl-11.jar

edit the picture dbd_topShadow.png (for instance) in adf/images/fusion-11.1.1.3.0/dbd_topShadow.png and copy it to META-INF/adf/images/fusion-11.1.1.3.0/dbd_topShadow.png with your favorite picture editor

recreate the jar


$MW/jdk16/jdk/bin/jar -cvf $MW/oracle_common/modules/oracle.adf.view_11.1.1/adf-richclient-impl-11.jar *

restart your OEM


emctl start oms

and empty your browser cache

You should see red

emred

Monitoring details on your explain plan

We know how to generate an explain plan. SET AUTOT ON, the good old ambulance in Toad (which is no longer an ambulance those days), the explain plan button in SQL Developer or simply EXPLAIN PLAN


create table t1 partition by hash(object_id) partitions 16 as select * from dba_objects;
exec dbms_stats.gather_table_stats(user, 'T1')
explain plan for 
  select /*+ PARALLEL(x, 4) */ * from t1 x, t1 y 
  where x.object_name = y.object_name and x.owner != y.owner

Explain plan writes in the PLAN_TABLE and could be displayed with


SQL> select * from table(dbms_xplan.display)

PLAN_TABLE_OUTPUT
--------------------------------
Plan hash value: 2344570521

---------------------------------------------------------------
| Id  | Operation                  | Name     | Rows  | Bytes |
---------------------------------------------------------------
|   0 | SELECT STATEMENT           |          | 18287 |  3500K|
|   1 |  PX COORDINATOR            |          |       |       |
|   2 |   PX SEND QC (RANDOM)      | :TQ10002 | 18287 |  3500K|
|*  3 |    HASH JOIN               |          | 18287 |  3500K|
|   4 |     PX RECEIVE             |          | 19219 |  1839K|
|   5 |      PX SEND HYBRID HASH   | :TQ10001 | 19219 |  1839K|
|   6 |       STATISTICS COLLECTOR |          |       |       |
|   7 |        PX BLOCK ITERATOR   |          | 19219 |  1839K|
|   8 |         TABLE ACCESS FULL  | T1       | 19219 |  1839K|
|   9 |     BUFFER SORT            |          |       |       |
|  10 |      PX RECEIVE            |          | 19219 |  1839K|
|  11 |       PX SEND HYBRID HASH  | :TQ10000 | 19219 |  1839K|
|  12 |        PARTITION HASH ALL  |          | 19219 |  1839K|
|  13 |         TABLE ACCESS FULL  | T1       | 19219 |  1839K|
---------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("X"."OBJECT_NAME"="Y"."OBJECT_NAME")
       filter("X"."OWNER"<>"Y"."OWNER")

But if you want to display progress on a currently running query, use DBMS_SQLTUNE (or Oracle Enterprise Manager SQL Monitoring):


SQL> set lin 150 longc 150 long 1000000;
SQL> select DBMS_SQLTUNE.REPORT_SQL_MONITOR(sql_plan_hash_value
  =>2344570521) from dual;

DBMS_SQLTUNE.REPORT_SQL_MONITOR(SQL_PLAN_HASH_VALUE=>2344570521) 
----------------------------------------------------------------
SQL Monitoring Report

SQL Text
------------------------------
select /*+ PARALLEL(x, 4) */ * from t1 x, t1 y where 
x.object_name = y.object_name and x.owner != y.owner

Global Information
------------------------------
 Status              :  EXECUTING
 Instance ID         :  1
 Session             :  SCOTT (25:10369)
 SQL ID              :  0dpj0fxm2gf81
 SQL Execution ID    :  16777216
 Execution Started   :  08/12/2013 14:48:26
 First Refresh Time  :  08/12/2013 14:48:26
 Last Refresh Time   :  08/12/2013 14:48:59
 Duration            :  34s
 Module/Action       :  SQL*Plus/-
 Service             :  SYS$USERS
 Program             :  sqlplus.exe
 Fetch Calls         :  19

DBMS_SQLTUNE.REPORT_SQL_MONITOR(SQL_PLAN_HASH_VALUE=>2344570521)
----------------------------------------------------------------

Global Stats
=================================================
| Elapsed |   Cpu   |  Other   | Fetch | Buffer |
| Time(s) | Time(s) | Waits(s) | Calls |  Gets  |
=================================================
|    0.25 |    0.13 |     0.12 |    19 |    752 |
=================================================

Parallel Execution Details (DOP=4 , Servers Allocated=8)
========================================================
|      Name      | Type  | Server# | Elapsed |   Cpu   |
|                |       |         | Time(s) | Time(s) |
========================================================
| PX Coordinator | QC    |         |    0.08 |    0.03 |
| p000           | Set 1 |       1 |    0.03 |    0.02 |
| p001           | Set 1 |       2 |    0.03 |    0.02 |
| p002           | Set 1 |       3 |         |         |
| p003           | Set 1 |       4 |    0.03 |    0.02 |
| p004           | Set 2 |       1 |    0.02 |    0.01 |
| p005           | Set 2 |       2 |    0.03 |    0.02 |
| p006           | Set 2 |       3 |    0.03 |    0.00 |
| p007           | Set 2 |       4 |    0.02 |    0.00 |
========================================================

SQL Plan Monitoring Details (Plan Hash Value=2344570521)
==================================================================
| Id   |          Operation          |  Rows   | Cost |   Rows   |
|      |                             | (Estim) |      | (Actual) |
==================================================================
| -> 0 | SELECT STATEMENT            |         |      |      271 |
| -> 1 |   PX COORDINATOR            |         |      |      271 |
|    2 |    PX SEND QC (RANDOM)      |   18287 |  202 |      615 |
|    3 |     HASH JOIN               |   18287 |  202 |      538 |
|    4 |      PX RECEIVE             |   19219 |   44 |    14481 |
|    5 |       PX SEND HYBRID HASH   |   19219 |   44 |    19219 |
|    6 |        STATISTICS COLLECTOR |         |      |    19219 |
|    7 |         PX BLOCK ITERATOR   |   19219 |   44 |    19219 |
|    8 |          TABLE ACCESS FULL  |   19219 |   44 |    19219 |
|    9 |      BUFFER SORT            |         |      |     1316 |
|   10 |       PX RECEIVE            |   19219 |  158 |    14481 |
|   11 |        PX SEND HYBRID HASH  |   19219 |  158 |    19219 |
|   12 |         PARTITION HASH ALL  |   19219 |  158 |    19219 |
|   13 |          TABLE ACCESS FULL  |   19219 |  158 |    19219 |
==================================================================

The small -> sign shows you where it is and display some actual (not estimates) info.

If I run it again :


==================================================================
| Id   |          Operation          |  Rows   | Cost |   Rows   |
|      |                             | (Estim) |      | (Actual) |
==================================================================
| -> 0 | SELECT STATEMENT            |         |      |     6451 |

For the same query, we see some progress (6451 rows now).

Check you have licensed the appropriate tuning options before using DBMS_SQLTUNE

encrypt with openssl

I want to avoid cleartext password on my filesystem

I encrypt my password with a secret key


echo tiger | openssl enc -aes-256-cbc -k MyKey -out $HOME/myconfig

Whenever I call a script, I pass the secret key


sqlplus scott/$(openssl enc -d -aes-256-cbc -k MyKey -in $HOME/myconfig)

Of course you still need the key {MyKey}, but unless you have both the file AND the key, it is safe

One more obscure syntax

As 10g introduced MODEL, which is mostly used to impress your colleagues but seldom used in production, 12c has a new syntax, MATCH_RECOGNIZE

I gave it a first try to recognize trends in EMPs salaries over hire date.


SELECT ename, hiredate, sal, trend
FROM emp 
MATCH_RECOGNIZE (
  ORDER BY hiredate
  MEASURES CLASSIFIER () AS TREND
  ALL ROWS PER MATCH
  PATTERN (FIRST * Better * Worst * Same *)
  DEFINE FIRST AS ROWNUM = 1,
  Better AS Better.sal > PREV (sal),
  Same AS Same.sal = PREV (sal),
  Worst AS Worst.sal < PREV (sal));


ENAME      HIREDATE          SAL TREND                         
---------- ---------- ---------- ------------------------------
SMITH      1980-12-17        800 FIRST                         
ALLEN      1981-02-20       1600 BETTER                        
WARD       1981-02-22       1250 WORST                         
JONES      1981-04-02       2975 BETTER                        
BLAKE      1981-05-01       2850 WORST                         
CLARK      1981-06-09       2450 WORST                         
TURNER     1981-09-08       1500 WORST                         
MARTIN     1981-09-28       1250 WORST                         
KING       1981-11-17       5000 BETTER                        
JAMES      1981-12-03        950 WORST                         
FORD       1981-12-03       3000 BETTER                        
MILLER     1982-01-23       1300 WORST                         
SCOTT      1987-04-19       3000 BETTER                        
ADAMS      1987-05-23       1100 WORST                         

Google Reader end of life

For those of my readers who were using Google Reader, here are my comments

1) backup your data, read this : https://support.google.com/reader/answer/3028851
2) for the web gui, there is a one-click migration to Feedly : http://feedly.com
3) you can import the OPML (Outline Processor Markup Language) file from Google Takeout into Microsoft Outlook (right click on RSS Feeds –> import OPML).

Maybe you want to forget about RSS and switch to Google+ or Twitter or read orafaq or orana or install your own aggregator on your web server (e.g. tiny rss)

ODBC 32bits for Windows 64bits

Windows-On-Windows 64-bit (WOW64) enables you to run 32bits applications in 64bits OS.

You will see there is another powershell, another registry, another ODBC tool, another Oracle client.


%SYSTEMROOT%\syswow64\WindowsPowerShell\v1.0\powershell.exe

First, we run powershell(x86)


Get-itemproperty HKLM:\SOFTWARE\ORACLE\*| Select-Object ORACLE_HOME,ORACLE_HOME_NAME

ORACLE_HOME                                                 ORACLE_HOME_NAME
-----------                                                 ----------------
C:\oracle\product\11.2.0\client_32                          client32bit_11203

Only the Oracle 32bit client is displayed

cmd /c "%SYSTEMROOT%\syswow64\odbcconf.exe /a {configdsn ""Oracle in client32bit_11203"" ""DSN=helloworld32|SERVER=DB01""}"

We registered ODBC with a wow64 configurator (odbcconf) or assistant (odbcad32).


$conn = New-Object Data.Odbc.OdbcConnection
$conn.ConnectionString= "dsn=helloworld32;uid=scott;pwd=tiger;"
$conn.open()
(new-Object Data.Odbc.OdbcCommand("select 'Hello World' from dual",$conn)).ExecuteScalar()
$conn.close()

For the 64 bits version, it boils down to the same as odbc 32 bit on 32 bit os


%SYSTEMROOT%\system32\WindowsPowerShell\v1.0\powershell.exe

Get-itemproperty HKLM:\SOFTWARE\ORACLE\*| Select-Object ORACLE_HOME,ORACLE_HOME_NAME

ORACLE_HOME                             ORACLE_HOME_NAME
-----------                             ----------------
C:\oracle\product\11.2.0\client_64      client64bit_11203

cmd /c "%SYSTEMROOT%\system32\odbcconf.exe /a {configdsn ""Oracle in client64bit_11203"" ""DSN=helloworld64|SERVER=DB01""}"

$conn = New-Object Data.Odbc.OdbcConnection
$conn.ConnectionString= "dsn=helloworld64;uid=scott;pwd=tiger;"
$conn.open()
(new-Object Data.Odbc.OdbcCommand("select 'Hello World' from dual",$conn)).ExecuteScalar()
$conn.close()

Do not get confused by Windows32 and WOW64.

Windows32 is the default windows system directory with a bad name from upon a time where 32 meant something very big (compared to 16bit software); and WOW64 which is a special directory to run 32bits windows application on a 64bits os.

Oracle ODBC hello world with powershell

Demo :


cmd /c "odbcconf.exe /a {configdsn ""Oracle in OraClient11g_home1"" ""DSN=helloworld|SERVER=DB01""}"

Create a helloworld data source connecting to your DB01 tns alias in your OraClient11g_home1 Oracle Home.

It is easy to get the Oracle Home name with


Get-itemproperty HKLM:\SOFTWARE\ORACLE\*| Select-Object ORACLE_HOME,ORACLE_HOME_NAME

ORACLE_HOME                                       ORACLE_HOME_KEY
-----------                                       ---------------
C:\oracle\product\11.1.0\client_1                 OraClient11g_home1
C:\oracle\product\11.2.0\client_1                 OraClient11g_home2

Then we create the connection (as we did in ADO or ODP) :


$conn = New-Object Data.Odbc.OdbcConnection
$conn.ConnectionString= "dsn=helloworld;uid=scott;pwd=tiger;"
$conn.open()
(new-Object Data.Odbc.OdbcCommand("select 'Hello World' from dual",$conn)).ExecuteScalar()
$conn.close()

how to rename datafiles on standby

If you rename a file with alter database on primary, it will not occur on standby. If you want to rename it on standby to, you need to do it manually

  1. cancel the recovery (set dg_broker_start to false and restart db in mount status if you use dataguard)
  2. set the standby file management to manual alter system set standby_file_management=manual
  3. move the file with OS commands as you did on the primary
  4. rename the file with alter database rename file ‘old’ to ‘new’
  5. reenable automatic standby file management and dg broker and restart the recovery (start db in mount status if you use dataguard)

Best practice : use double quotes, even in DBMS_STATS

Whenever you create a table, it is better to use double quotes to avoid invalid identified.


SQL> CREATE TABLE /XXX(x number);
CREATE TABLE /XXX(x number)
             *
ERROR at line 1:
ORA-00903: invalid table name

SQL> CREATE TABLE "/XXX"(x number);

Table created.

Even in DBMS_STATS you should use double quotes


SQL> exec dbms_stats.gather_table_stats(user,'/XXX')
BEGIN dbms_stats.gather_table_stats(user,'/XXX'); END;

*
ERROR at line 1:
ORA-20001: /XXX is an invalid identifier
ORA-06512: at "SYS.DBMS_STATS", line 23829
ORA-06512: at "SYS.DBMS_STATS", line 23880
ORA-06512: at line 1

SQL> exec dbms_stats.gather_table_stats(user,'"/XXX"')

PL/SQL procedure successfully completed.

It is also a good practice to not use table name like “/XXX”, “FROM” or “ROWID”. But if you use dynamic SQL, be sure your code does not bug on invalid identifier.

It is pretty seldom that Oracle introduces new reserved words, as it breaks code, so if you do


CREATE TABLE MYTABLE(x number);

you can be pretty sure that neither MYTABLE nor X will be reserved in 12c or 13c…

Rman backup compression

Did you know you can make your backup at least twice faster with a single line ?

Demo :


RMAN> backup as compressed backupset database;
Starting backup at 2013-06-05_13:08:01
...
Finished backup at 2013-06-05_13:13:59

6 minutes for a compressed backup on a NAS with 24 Channels and 100Gb of raw data. Not bad. But look at this !


RMAN> configure compression algorithm 'low';
new RMAN configuration parameters are successfully stored
RMAN> backup as compressed backupset database;
Starting backup at 2013-06-05_14:06:09
...
Finished backup at 2013-06-05_14:08:29
RMAN> configure compression algorithm clear;
RMAN configuration parameters are successfully reset to default value

By configuring this magic parameter, it is now more than twice faster ! This is incredible !

Go to your cashier and grab some coins to get this amazing advanced compression option !

use cron to schedule a job only once

I wrote about not using DAY OF MONTH and DAY OF WEEK simultanously in how to cron

The correct method is to use
15 14 15 05 * /tmp/run-my-job

But… I wrote this five years ago. Hmmm ! Not that correct then since it would run every year ;-)

Ok, periodically I check for jobs are scheduled to run a specific date only

$ crontab -l|awk '$1!~/#/&&$3*$4'
15 14 15 05 * /tmp/run-my-job

I have 9 more days to remove this before it runs for the fifth time :)

Delete one billion row

To delete large number of rows, for instance rows with date until 2010, you can issue this simple statement.


SQL> DELETE FROM T WHERE C<DATE '2011-01-01';
1'000'000'000 rows deleted 
Elapsed: 23:45:22.01
SQL> commit;

This is perfectly fine. The table remains online, other users are not much affected (maybe they will not even notice the lower IO performance).

It will generate quite a lot of UNDO, and you will need enough space for archivelog and a large undo tablespace and a large undo retention setting (to prevent ORA-01555 snapshot too old).

If your table is like 100G big, you do it during week-end, you have 500Gb Undo and 250G free space in your archive destination, you will be fine. Well. Maybe.

There are workarounds where you create a new table then rename etc… but this is not the scope of this post and you will need to validate your index / foreign keys / online strategy with the application guys.

Another way to decrease runtime pro statement and undo requirement pro statement (but increase overall elapsed time) is to divided it chunks, for instance to delete 100’000’000 rows each night during 10 days.


SQL> DELETE FROM T WHERE C<DATE '2011-01-01' AND ROWNUM<=100000000;
100'000'000 rows deleted 
Elapsed: 04:11:15.31
SQL> commit;

Or if you want to delete in much smaller chunks to accomodate your tiny undo tablespace, you could try


BEGIN
  LOOP
    DELETE FROM T WHERE C<DATE '2011-01-01' AND ROWNUM <= 1000;
    EXIT WHEN SQL%ROWCOUNT = 0;
    COMMIT;
   END LOOP;
END;
/

This will run longer than a single transaction, but it is quite usefull if your undo tablespace is too small. Also if you abort it (CTRL-C or kill session), you will not lose all progresses (but you lose on integrity/atomicity) and your KILL SESSION will not last for ever. With a single transaction, your session may be marked as killed for hours/days…

When v$session_longops is not long enough

With large table scans, sometimes the estimated total work is far beyond reality


SQL> select message from v$session_longops where target='SCOTT.EMP';

MESSAGE
------------------------------------------------------------
Table Scan:  SCOTT.EMP: 7377612 out of 629683 Blocks done

The total work is the Oracle estimation :


SQL> select blocks from dba_tables where table_name='EMP';

    BLOCKS
----------
    629683

This may differ quite a lot from the segment size, for instance if the table is not very often analyzed :


SQL> select blocks, sysdate, last_analyzed from dba_tables where table_name='EMP';

    BLOCKS SYSDATE             LAST_ANALYZED
---------- ------------------- -------------------
    629683 2013-04-21_09:21:47 2007-10-13_21:40:58

SQL> select blocks from dba_segments where segment_name='EMP';

    BLOCKS
----------
   7749888

I have customized my very long ops query to deal with very long waits.


col target for a20
set lin 150 pages 40000 termout off
alter session set nls_currency='%';
col PCT_DONE for 990.00L jus r
col time_remaining for 999999

select
  lo.target,lo.sofar,seg.blocks,
  lo.ELAPSED_SECONDS*seg.blocks/lo.sofar-lo.ELAPSED_SECONDS TIME_REMAINING,
  100*lo.sofar/seg.blocks PCT_DONE
from
  dba_segments seg,
  v$session_longops lo
where
  lo.units='Blocks'
  and lo.totalwork>0 and (lo.time_remaining>0 or lo.time_remaining is null)
  and regexp_substr(lo.target,'[^.]+') = seg.owner
  and regexp_substr(lo.target,'[^.]+$') = seg.segment_name
/

Generate Microsoft Office Documents from command line

In previous posts (e.g. Export to Excel) I wrote about using HTML format to export to Excel.

Let’s do it for real, let’s dive into the .xls file format and learn how to generate dynamic excel from Unix!

1) create one time your excel file manually. With graphs, colors, sounds, up to you. Or Word, Powerpoint or whatever (minimum MS Office 2007)
excel-screenshot

2) save as excel 2007 or later format (.xlsx)
this is called the Office Open XML format. It is neither OpenOffice nor OpenSource. It is XML and license restriction may apply.

3) transfer the excel file to your favorite platform

4) unzip the excel file (yes, you read it correctly, unzip the .xlsx file)


$ unzip /tmp/DynamicExcel.xlsx
Archive:  /tmp/DynamicExcel.xlsx
  inflating: [Content_Types].xml
  inflating: _rels/.rels
  inflating: xl/_rels/workbook.xml.rels
  inflating: xl/workbook.xml
  inflating: xl/styles.xml
  inflating: xl/worksheets/sheet2.xml
  inflating: xl/worksheets/_rels/sheet1.xml.rels
  inflating: xl/worksheets/_rels/sheet2.xml.rels
  inflating: xl/drawings/_rels/drawing1.xml.rels
  inflating: xl/theme/theme1.xml
  inflating: xl/worksheets/sheet1.xml
  inflating: xl/drawings/drawing2.xml
  inflating: xl/charts/chart1.xml
  inflating: xl/drawings/drawing1.xml
  inflating: xl/sharedStrings.xml
  inflating: docProps/core.xml
  inflating: docProps/app.xml

5) now substitute the data with some script output (for instance select * from v$backup_redologs). Here I am substituing all datas from row r=2


cd xl/worksheets

tr -d '\r' < sheet1.xml | sed 's,<row r="2".*,,' > head
sqlplus -s -L / as sysdba <<'EOF' > body
set feed off pages 0 lin 2000 longc 2000 long 2000
SELECT XMLELEMENT (
          "row",
          xmlattributes ((rownum+1) AS "r",
                         '1:2' AS "spans",
                         '0.2' AS "x14ac:dyDescent"),
          XMLELEMENT ("c",
                      xmlattributes ('A' || (rownum+1) AS "r", '1' AS "s"),
                      XMLELEMENT ("v", d-date '1899-12-30')),
          XMLELEMENT ("c",
                      xmlattributes ('B' || (rownum+1) AS "r", '2' AS "s"),
                      XMLELEMENT ("v", c)))
          x
  FROM (  SELECT TRUNC (next_time, 'DD') d, COUNT (*) c
            FROM v$backup_redolog
           WHERE next_time BETWEEN TRUNC (SYSDATE - 90)
                               AND TRUNC (SYSDATE) - 1 / 86400
        GROUP BY TRUNC (next_time, 'DD')
        ORDER BY 1);
EOF
tr -d '\r' < sheet1.xml | sed -n 's,.*</sheetData>,</sheetData>,p' > tail
cat head body tail | tr -d '\n' > sheet1.xml
rm head body tail

6) recreate zip file


$ cd ../..
$ zip -r /tmp/DynamicExcel2.xlsx *
  adding: [Content_Types].xml (deflated 78%)
  adding: docProps/ (stored 0%)
  adding: docProps/core.xml (deflated 51%)
  adding: docProps/app.xml (deflated 53%)
  adding: _rels/ (stored 0%)
  adding: _rels/.rels (deflated 60%)
  adding: xl/ (stored 0%)
  adding: xl/_rels/ (stored 0%)
  adding: xl/_rels/workbook.xml.rels (deflated 71%)
  adding: xl/workbook.xml (deflated 42%)
  adding: xl/styles.xml (deflated 56%)
  adding: xl/worksheets/ (stored 0%)
  adding: xl/worksheets/sheet2.xml (deflated 45%)
  adding: xl/worksheets/_rels/ (stored 0%)
  adding: xl/worksheets/_rels/sheet1.xml.rels (deflated 39%)
  adding: xl/worksheets/_rels/sheet2.xml.rels (deflated 39%)
  adding: xl/worksheets/sheet1.xml (deflated 81%)
  adding: xl/drawings/ (stored 0%)
  adding: xl/drawings/_rels/ (stored 0%)
  adding: xl/drawings/_rels/drawing1.xml.rels (deflated 39%)
  adding: xl/drawings/drawing2.xml (deflated 58%)
  adding: xl/drawings/drawing1.xml (deflated 61%)
  adding: xl/theme/ (stored 0%)
  adding: xl/theme/theme1.xml (deflated 79%)
  adding: xl/charts/ (stored 0%)
  adding: xl/charts/chart1.xml (deflated 85%)
  adding: xl/sharedStrings.xml (deflated 22%)

7) Check it

DynamicExcel2.xlsx

Default Oracle Home in Windows

In Oracle Universal Installer and OPatch User’s Guide it is documented that The first Oracle home is named the “DEFAULT_HOME” and registers itself in the Windows NT Registry.

Remember, NT means New Technology :)

There is apparently a Home Selector that is a part of the installation software, maybe something like D:\oracle\product\11.2.0\client_1\bin\selecthome.bat. Sometimes. Not sure

But there is no DEFAULT HOME in the registry.

PS C:\> gci HKLM:\SOFTWARE\ORACLE

    Hive: HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE

Name                           Property
----                           --------
KEY_agent12c1                  ORACLE_HOME       : D:/oracle\core\12.1.0.1.0
                               ORACLE_HOME_NAME  : agent12c1
                               ORACLE_GROUP_NAME : Oracle - agent12c1
                               NLS_LANG          : AMERICAN_AMERICA.WE8MSWIN1252
KEY_oracle_sysman_db_12_1_0_2_ ORACLE_HOME       : D:\oracle\plugins\oracle.sysman.db.discovery.plugin_12.1.0.2.0
0_discovery_Home0              ORACLE_HOME_NAME  : oracle_sysman_db_12_1_0_2_0_discovery_Home0
                               ORACLE_GROUP_NAME : Oracle - oracle_sysman_db_12_1_0_2_0_discovery_Home0
KEY_oracle_sysman_emas_12_1_0_ ORACLE_HOME       : D:\oracle\plugins\oracle.sysman.emas.discovery.plugin_12.1.0.2.0
2_0_discovery_Home0            ORACLE_HOME_NAME  : oracle_sysman_emas_12_1_0_2_0_discovery_Home0
                               ORACLE_GROUP_NAME : Oracle - oracle_sysman_emas_12_1_0_2_0_discovery_Home0
KEY_oracle_sysman_oh_12_1_0_1_ ORACLE_HOME       : D:\oracle\plugins\oracle.sysman.oh.agent.plugin_12.1.0.1.0
0_agent_Home0                  ORACLE_HOME_NAME  : oracle_sysman_oh_12_1_0_1_0_agent_Home0
                               ORACLE_GROUP_NAME : Oracle - oracle_sysman_oh_12_1_0_1_0_agent_Home0
KEY_oracle_sysman_oh_12_1_0_1_ ORACLE_HOME       : D:\oracle\plugins\oracle.sysman.oh.discovery.plugin_12.1.0.1.0
0_discovery_Home0              ORACLE_HOME_NAME  : oracle_sysman_oh_12_1_0_1_0_discovery_Home0
                               ORACLE_GROUP_NAME : Oracle - oracle_sysman_oh_12_1_0_1_0_discovery_Home0
KEY_OraClient11g_home1         ORACLE_HOME        : D:\oracle\product\11.2.0\client_1
                               ORACLE_HOME_NAME   : OraClient11g_home1
                               ORACLE_GROUP_NAME  : Oracle - OraClient11g_home1
                               ORACLE_BUNDLE_NAME : Enterprise
                               NLS_LANG           : AMERICAN_AMERICA.WE8MSWIN1252
                               OLEDB              : D:\oracle\product\11.2.0\client_1\oledb\mesg
                               ORACLE_HOME_KEY    : SOFTWARE\ORACLE\KEY_OraClient11g_home1
                               MSHELP_TOOLS       : D:\oracle\product\11.2.0\client_1\MSHELP
                               SQLPATH            : D:\oracle\product\11.2.0\client_1\dbs
KEY_OraGtw11g_home1            ORACLE_HOME        : D:\oracle\product\11.2.0\tg_1
                               ORACLE_HOME_NAME   : OraGtw11g_home1
                               ORACLE_GROUP_NAME  : Oracle - OraGtw11g_home1
                               NLS_LANG           : AMERICAN_AMERICA.WE8MSWIN1252
                               ORACLE_BUNDLE_NAME : Enterprise
                               MSHELP_TOOLS       : D:\oracle\product\11.2.0\tg_1\MSHELP
                               SQLPATH            : D:\oracle\product\11.2.0\tg_1\dbs
                               ORACLE_HOME_KEY    : SOFTWARE\ORACLE\KEY_OraGtw11g_home1
KEY_sbin12c1                   ORACLE_HOME       : D:\oracle\sbin
                               ORACLE_HOME_NAME  : sbin12c1
                               ORACLE_GROUP_NAME : Oracle - sbin12c1
ODP.NET
remexecservicectr              remaining_time : 120000
SYSMAN

How do I set my Oracle Home?

Actually if you enter a command like “lsnrctl start”, the OS will search in the PATH for lsnrctl and determines the Oracle Home name accordingly.

Therefore, the only thing you must do to change your default Oracle Home is to set the PATH environment variable. Only then your LSNRCTL START will find the right binary and right parameter file to start your listener.

Oracle Certified Master