Laurent Schneider

Oracle SQL Developer provides access to data and code from other database systems than Oracle

If you run Sybase procedures (or Microsoft SQL Server I suppose), you cannot use the BEGIN / DECLARE / EXEC in anonymous blocks

begin
  print 'HELLO'
end

Error starting at line : 1 in command -
begin
  print 'HELLO'
end
Error report -
Incorrect syntax near the keyword 'begin'.

exec p

Error starting at line : 1 in command -
exec p
Error report -
Incorrect syntax near the keyword 'BEGIN'.

Ô rage ô désespoir!

But actually there is a way to twist the syntax so that it does not look like an oracle exec !

But in fact you create a procedure with CREATE PROCEDURE and end the procedure with a slash. Within the procedure you can use all the keywords you want. To call a procedure, do not use the exec keyword

set echo on

drop procedure p1
/

drop procedure p2
/

create procedure p1 as
return 123
/

create procedure p2 as
declare @x int, @y varchar(255)
exec @x = p1
set @y = convert(varchar(255), @x)
print @y
/

p2

> drop procedure p1
procedure P1 dropped.
> drop procedure p2
procedure P2 dropped.
> create procedure p1 as
return 123
PROCEDURE P1 compiled
> create procedure p2 as
declare @x int, @y varchar(255)
exec @x = p1
set @y = convert(varchar(255), @x)
print @y
PROCEDURE P2 compiled
> p2
123

Okay, it looks a bit like a sqlplus mutant, but it does the trick sometimes when you need to only call a procedure and get it’s return code.

The sames applies for other rdbms, here db2

set echo on 

drop procedure p
/

CREATE PROCEDURE P
LANGUAGE SQL 
P1: BEGIN 
  DECLARE X int; 
END P1 
/

call p

> drop procedure p
procedure P dropped.
> CREATE PROCEDURE P
LANGUAGE SQL 
P1: BEGIN 
  DECLARE X int; 
END P1 
PROCEDURE P compiled
> call p
call p

UPDATE: check dermotoneill for additional tricks!

The Oracle 12c client does not have a ocijdbc11.dll, so when you try to login with your 12c Oracle Client (oci thick-driver), you may see :
no ocijdbc11 in path

Don’t worry, this boils down to the jdbc driver ojdbc6.jar. Just overwrite your sqldeveloper/jdbc/lib/ojdbc6.jar with the one from your 12c client.

The same applies to the 11.2.0.4 client, whereas there you will get a core dump at oracle.jdbc.driver.T2CConnection.t2cCreateState instead of a proper error message.

I post a thread on https://community.oracle.com/message/12293761

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

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

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

• PC : Microsoft Network Monitor
• Unix : on AIX, tcpdump
• On AD : ???

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

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.

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 ?

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.

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

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

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()

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)

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

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