2012-03-26

do not touch if you do not know for sure


Oracle provides and documents a huge load of possibilities and functions for nearly every purpose. For me it is impossible to know all of them. Even to know such an area exists is hard.
But still sometimes these functions Oracle does not document for customers purpose seems to be more attractive than those officially available.
One of these attractive packages is DBMS_SYSTEM. You will not find any description of this package in the official Oracle documentation. There are some small traces available, but nothing really useful.
Oracle also have quite clear words about using such unofficial, and hidden, packages:
In How To Edit, Read, and Query the Alert.Log [ID 1072547.1] you can read:

NOTE about DBMS_SYSTEM:
This package should in fact only be installed when requested by Oracle Support.
It is not documented in the server documentation.
As such the package functionality may change without notice.
It is to be used only as directed by Oracle Support and its use otherwise is not supported.

Per internal Note 153324.1:
Generally, if a package is not in the Oracle documentation at all, it is intentional, as it is not for end user use. Non-documented packages should be avoided by customers unless specifically instructed to use them by either Oracle Support or Oracle Development.

For some reasons I'm one of those which likes to play with forbidden toys like these. I found a procedure in DBMS_SYSTEM which changed behavior slightly in 11gR2 (I've tested with 11.2.0.3 patchset - so maybe other patchsets behave quite different!)

I'm talking about DBMS_SYSTEM.READ_EV. This procedure more or less calls directly the internal C-routine READ_EV_ICD. Common sense is, it should return the level of an event given. This is also quite true, just for one exception: the probably most known event in Oracle world: 10046 - or sql_trace.

My test-script here
VARIABLE lev number
SET AUTOPRINT ON
EXECUTE sys.dbms_system.read_ev(10046, :lev)

ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';

SELECT sql_trace, sql_trace_waits, sql_trace_binds FROM v$session WHERE sid=userenv('sid')


EXECUTE sys.dbms_system.read_ev(10046,:lev)

oradebug setmypid
oradebug eventdump session

gives the expected result in one of my 10g test DBs:
@test_read_ev.sql

PL/SQL procedure successfully completed.

       LEV
----------
         0

Session altered.

PL/SQL procedure successfully completed.

       LEV
----------
         8

Statement processed.
10046 trace name CONTEXT level 8, forever

but an unexpected result in my 11.2.0.3 test DB:

@test_read_ev.sql

PL/SQL procedure successfully completed.

       LEV
----------
         0

Session altered.

PL/SQL procedure successfully completed.

       LEV
----------
         0

Statement processed.
sql_trace level=8

I guessed events with an ALIAS might be excluded somehow, but other tests with DEADLOCK==60 or DB_FILES==10222 shows this special behavior only with sql_trace.

My todays conclusion is easy:
If it's not there for you, don't guess you can play with it without any consequences.

2012-03-19

looking close at TAF


At the moment I'm trying to collect and sort some informations about Oracles Transparent Application Failover. There is a lot of general information available in the wild, but no deeper details. Here I try to show my findings.

Testcase

For my test-database with DB_UNIQUE_NAME: TTT06_SITE1 I created the service
srvctl add service -d TTT06_SITE1 -s TTT06_TAF -P BASIC -e SELECT -r TTT061,TTT062 .
The tnsnames.ora entry is
TTT06_TAF =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (LOAD_BALANCE = OFF)
      (ADDRESS = (PROTOCOL = TCP)(HOST = crs908.my.domain)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = TTT06_TAF)(SERVER=DEDICATED)
    )
  )

tracing

Just
strace -f -t -s 128 -o sqlplus_taf.strace sqlplus "berx/berx123#@TTT06_TAF"
I will look closely on the sqlplus_taf.strace soon, just the testcase can be finished easily:

current instance


SELECT (SELECT instance_number
        FROM   v$instance) inst,
       s.sid,
       s.service_name,
       s.failover_type,
       s.failover_method,
       s.failed_over,
       p.spid
FROM   v$process p,
       v$session s
WHERE  s.paddr = p.addr
       AND addr IN (SELECT paddr
                    FROM   v$session
                    WHERE  audsid = Sys_context('USERENV', 'SESSIONID'));


   INST      SID SERVICE_NAME FAILOVER_TYPE FAILOVER_M FAI   SPID
------- -------- ------------ ------------- ---------- --- ------
      1      144    TTT06_TAF        SELECT      BASIC  NO  23440

and after a startup force in a 2nd session in instance 1

new instance


/

   INST      SID SERVICE_NAME FAILOVER_TYPE FAILOVER_M FAI   SPID
------- -------- ------------ ------------- ---------- --- ------
      2      146    TTT06_TAF        SELECT      BASIC YES  14927

what's going on

A short excerpt of the sqlplus_taf.strace
First sqlplus tries to access ~/.tnsnames.ora, fails and then opens $TNS_ADMIN/tnsnames.ora. Of course there it reads the connection string shown above.
Next it tries to resolve the HOST entry:
connect(9, {sa_family=AF_INET, sin_port=htons(53), sin_addr=inet_addr("<my_dns>")}, 28) = 0
and gets all IPs for my SCAN-DNS.
sqlplus asks one of the SCAN listeners:
connect(9, {sa_family=AF_INET, sin_port=htons(1521), sin_addr=inet_addr("<any SCAN IP>")}, 16) = -1 EINPROGRESS (Operation now in progress)
for the SERVICE and gets a kind of redirect:
read(9, "\1\10\0\0\6\0\0\0\0@(ADDRESS=(PROTOCOL=TCP)(HOST=<NODE1-vip>)(PORT=1521))\0(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<SCAN IP>)(PORT=1"..., 8208) = 264
The SCAN-Listener is of no good anymore: close(9). sqlplus looks up the name of <NODE2-vip> in /etc/hosts and tries it's next step with the <NODE1-vip> listener:
connect(9, {sa_family=AF_INET, sin_port=htons(1521), sin_addr=inet_addr("<NODE1-vip IP>")}, 16) = -1 EINPROGRESS (Operation now in progress)

The listener creates a server process for sqlplus - and let's them do their private business.


The startup force killed the server process for sqlplus. But it doesn't know anything about it, until it get's the <newline> from the terminal.

Of course filehandle 9 is somewhat dead and gets close(9). Now really the same steps as above (just tnsnames.ora is not re-read!): SCAN IP lookup, redirect to an NODE-vip, etc.

So only tnsnames.ora is cached, all other lookups and connections are re-run again.
Take this into account if you try to change your setup (IPs, lookups, DNS) while connections are active.

2012-03-05

creating my mobile toolbox (for windows) I


I am somewhat tired to re-install the same set of software again and again, every time I (have to) switch to a new PC. Probably it's me, not the PCs, but it takes some tome to have the system setup, and me productive again.
Somehow it's like a craftsman has to setup a new labor space with new tools in every house they visit. But craftsmen are clever, they bring your tools with them - and take them away if not needed anymore. In best case they do not leave any traces (except the work done).
I try to mimic this approach: I'm creating my own toolbox. Mine is not made of leather or plastic, it's made of an USB-stick, portableapps.com and some additional modifications.

First I chose the programs available in portableapps app directory: Notepad++, Google Chrome, KeePass,  PuTTY and WinSCP. I tried to keep the list small, but you can make your own decisions,  of course.

Unfortunately I need some more tools: As a DBA, sometimes I not only like  to access the database servers, but the database directly. I did not find any proper tool in the app directory, therefore I decided to include Oracles SQL Developer into my toolbox and followed the Portable Apps Format Specification. It's not that complex it looks at first sign. Here my steps:

  1. create the proper directory structure:
    SQLDeveloperPortable
    + App
      + AppInfo
      + DefaultData
    + Data
    + Other
      + Help
        + Images
      + Source

  2. download SQL Developer and unzip it into the App folder

  3. in AppInfo create the file appinfo.ini:
    [Format]
    Type=PortableApps.comFormat
    Version=2.0
    
    [Details]
    Name=SQLDeveloper Portable
    AppID=SQLDeveloperPortable
    Publisher=^/\x
    Homepage=berxblog.blogspot.com/2012/03/creating-my-mobile-toolbox-for-windows.html
    Category=Utilities
    Description=Oracle SQL Developer is a graphical version of SQL*Plus that gives database developers a convenient way to perform basic tasks
    Language=Multilingual
    Trademarks=Oracle
    InstallType=
    
    [License]
    Shareable=false
    OpenSource=false
    Freeware=false
    CommercialUse=true
    EULAVersion=1
    
    [Version]
    PackageVersion=3.1.07.42
    DisplayVersion=3.1
    
    [SpecialPaths]
    Plugins=NONE
    
    [Dependencies]
    UsesJava=no
    UsesDotNetVersion=
    
    [Control]
    Icons=1
    Start=sqldeveloper.bat
    ExtractIcon=App\sqldeveloper\icon.png
    

  4. In SQLDeveloperPortable create the file sqldeveloper.bat:
    REM ^/\x
    SET IDE_USER_DIR=%~d0\PortableApps\SQLDeveloperPortable\Data
    start /b %~d0\PortableApps\SQLDEveloperPortable\App\sqldeveloper\sqldeveloper.exe
    By setting IDE_USER_DIR all configurations will be stored on the USB-stick, not on (changing) PCs.

  5. creating a proper icon for App\sqldeveloper\icon.png

That's it - works like a charm!



Next I prepared Xming for the portable world. X11 is still needed in the world of an Oracle DBA.
The steps where similar to those of SQL Developer, therefore I only describe the differences here:
As I don't want to extract the installer of Xming, I just let it install onto my PC into C:\Program Files. Then I copied the full structure C:\Program Files\Xming into XmingPortable\App.
Also in this case a bat file as a wrapper is needed, as Xming needs some parameters to go into tray without a window:
start /b %~d0\PortableApps\XmingPortable\App\Xming\Xming.exe :0 ‑clipboard ‑multiwindow



By these 2 examples you can see it's nice and easy to have the private toolbox at your hands all the time.
I do not provide the packages for any of these programs. First, I don't want to take care of any legal implications. Second, I have no interest in this kind of work. I just have no skills in doing so. period.