about traveling

At the moment I am trying to travel to Edinburgh for a week of Exadata Prove of Concept.
The basic idea was to leave Vienna on sunday evening and arrive in Edinburgh on sunday night.
As I tried to do an online checkin on Saturday evening - I discovered the flights where shifted to monday evening. - That was not my initial plan as one day missing on a 5 days PoC is not so nice.
After some research I discovered my initial flight from Vienna to Amsterdam was cancelled. But there was still an earlier flight on sunday lunchtime. As the flights where booked at +KLM I checked on their website for some way to get support (note, it was saturday, late evening).
To my concern there where no 24 hr telephone numbers or web interfaces - but they mentioned to contact their twitter or facebook account. So I just gave @KLM a try, Even I was skeptical about support via twitter. But They really responded, asked for some details and booked me on the earlier flight on sunday.
I was very happy about that and made my way to Schiphol. There I had to wait for about 6 hours for my flight to Edinburgh. Waiting on an airport is never fun, but in Schiphol it was much better than on many other airports I've seen so far!
But while I enjoyed the airport, it began to snow. Some flights got cancelled, more flights got cancelled, my flight got cancelled! Of course that's not nice and none is doing it on purpose. I am traveling alone, no other people to take care of, even without my luggage I am well equipped for such a weather, so no big deal at all.
First I needed a place to stay. So heading for the proper counter. I was not the only one whose flight was cancelled so this lead to some queueing. In this time, serious queueing! If you ever have a harddisk response time gone up from 10ms to 50ms due to queueing issues, and call this hard, you just don't know.
Well, after some hours I got my hotel voucher. - Off to the next queue. This time transfer busses to the hotels. In my case it was a hotel with about 400 rooms - a roundtrip time of 20-30 min and 2 small busses which could handle at about 12 passengers. Please do the math!
Lucky as I am I got a nice room in Ramada Apollo Amsterdam.
After 2 beers at the bar I decided to head to my room. Midnight was over already.
But before I went to bed - one more tweet to send - and to ask for another flight to Edinburgh.

The next morning I had my direct message - the new flight was arranged by KLM twitter support: 15:35
There was still plenty of time, so I decided to stay at the hotel for some more time. Started up my laptop and do some work.
Transfer to Schiphol and checkin where flawless, So I'm sitting here and waiting for boarding to flight KL1285. As there is always enough work I can do remote, the time is at least of limited use.

There is just one question I'd like to know: Why do I need to travel to a small town in Schottland for a prove of concept where I will use the same Laptop and the same terminal application, do not need any "high speed low latency" connection and do not need to get in touch with the physic at all?

side channel attack on ORA-00942

Oracle Databases has a powerful set of grants and permissions. One of the easy philosophies behind it is just to hide anything a user is not allowed to see. Technically this leads to an error message
ORA-00942: table or view does not exist.
More precisely it should give a text like table or view does not exist or you are not allowed to access it.
For an ordinary user/schema separation there might be no big difference: If user A can not read table B.TAB it's of no value for user A whether the object does not exist or is just not accessible. But from a security point of view it might be of some interest if there exists a particular object within the database. Even it's not exploitable yet, maybe it's worth to try to reach it, or there is a combined vector of attack together with other objects.
To gain information about a system which should be kept hidden a well known method for physical cryptosystems is the side channel attack. At this attack extra information about the system is gained by measuring external channels like timings, power consumption and so on.
I am showing a similar method to get many informations about objects within a database a user should not know in general.

The described attack combines 2 well known concepts of every Oracle database: row cache and session statistics.

The row rache (or data dictionary cache) ... is a collection of database tables and views containing reference information about the database, its structures, and its users. - It's important to know this cache is filled by SQL-statements against the data dictionary tables.

session statistics just increase a counter for every session every time an action is executed.

Now let's check if we can get some hidden information out of my test system.
I did a alter system flush shared_pool; to make it slightly easier for the testcase, but even with a pre-filled row cache I am sure these informations can get collected with only little more effort.

My measurement query is

select ms.sid, ms.statistic#, sn.name, ms.value 
from v$mystat ms, V$STATNAME sn 
where sn.name in ('recursive calls', 'execute count') and ms.statistic# = sn.statistic# order by 2;

with a result like

       SID STATISTIC# NAME                      VALUE
---------- ---------- -------------------- ----------
       221          9 recursive calls            3108
       221        588 execute count               947

Now let's see where we can go. Something easy for the beginning. A select from a table which does not exist:

SQL> select * from gfdsaqwert;
select * from gfdsaqwert
              *
ERROR at line 1:
ORA-00942: table or view does not exist

with the measurement

       SID STATISTIC# NAME                      VALUE
---------- ---------- -------------------- ----------
       221          9 recursive calls            3111
       221        588 execute count               950

this means, there where 3 recursive calls and 3 execute counts.

Now for something more interesting - let's check if a user exists:

SQL> select * from zzz.uztrfghj;
select * from zzz.uztrfghj
                  *
ERROR at line 1:
ORA-00942: table or view does not exist

20 recursive calls
 3 execute count

SQL> select * from a.uztrfghj;
select * from a.uztrfghj
                *
ERROR at line 1:
ORA-00942: table or view does not exist

21 recursive calls
 3 execute count

In my test-DB no user ZZZ exists, but a user A exists. (but no table A.UZTRFGHJ) There is one more recursive call if the user exists. Not much, but a difference.

SQL> select * from a.m ;
select * from a.m
                *
ERROR at line 1:
ORA-00942: table or view does not exist

26 recursive calls
 7 execute count

Now there are even more recursive calls and execute count. The Table A.M exists in my system.

I'd say this shows the concept. The official answer from the database was ORA-00942: table or view does not exist all the time. But v$mystat gave us some more information we did not get officially.

who cares if a listener is dying

In this post I try to show what's going on if a local listener dies in a 11gR2 RAC environment. My basic question is: When does (a) SCAN-Listener knows the local Listener disappeared?
My testcase (a sandbox):

• A 2-node RAC - all actions are run on node 1, if not explicit defined.
• My test-DB is called TTT04 (Test, you know?)
• I have 3 SCAN listeners there, but I want to make the test-case easier so I do pin down my connection string to only one SCAN-listener (it's SCAN2 in my case):
  

  TTT04_bx =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.24.32.117)
                                 (PORT = 1521)) # SCAN2
      (CONNECT_DATA =
        (SERVICE_NAME = TTT04_SITE1)
      )
    )

• start tracing pmon:
  ps -ef | grep pmon | grep TTT04
SQL> oradebug setospid <pid_of_pmon>
Oracle pid: 2, Unix process pid: <pid_of_pmon>, image: oracle@<node1> (PMON)
SQL> oradebug Event 10257 trace name context forever, level 16
Statement processed.
  
• just to make sure server side load balancing will lead my to node1:
  on node2: several
  bzip2 -z -c /dev/urandom > /dev/null &
  

An now the real test. My 2 test-scripts:
/tmp/bx1.sql

connect berx/berx123#@TTT04_bx
spool /tmp/bx1.txt
select to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS'), HOST_NAME from v$instance;
exit


/tmp/bx2.sql

connect berx/berx123#@TTT04_bx
spool /tmp/bx2.txt
select to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS'), HOST_NAME from v$instance;
exit


My command is
kill -9 `pgrep -f "tnslsnr LISTENER "` ; lsnrctl services LISTENER_SCAN2 > /tmp/lsnr1.txt ; sqlplus /nolog @/tmp/bx1.sql & sleep 5 ; lsnrctl services LISTENER_SCAN2 > /tmp/lsnr2.txt; sqlplus /nolog @/tmp/bx2.sql


and the result on the Terminal:

SQL*Plus: Release 11.2.0.3.0 Production on Sat May 5 23:00:50 2012

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

ERROR:
ORA-12541: TNS:no listener


SP2-0640: Not connected
[1]+ Done sqlplus /nolog @/tmp/bx1.sql 2> /tmp/bx1.err

SQL*Plus: Release 11.2.0.3.0 Production on Sat May 5 23:00:55 2012

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

Connected.

TO_CHAR(SYSDATE,'YY HOST_NAME
------------------- ---------
2012-05-05 23:00:55 <node2>


---

how to secure CMAN against CVE-2012-1675 - or an easier method than ASO

In the Oracle DBA World at the moment CVE-2012-1675 is a great issue. Oracle announced some methods how to secure existing systems. But these are sometimes not that easy, and there is no backport for older systems.
As I investigated the problem how to secure a connection manager I was hinted at Note:1455068.1.
The solution is somewhat easy: Only allow incoming connections to your systems. e.g.
    (rule=(src=*)(dst=10.220.8.114)(srv=*)(act=accept))

In a well designed environment where you can separate your DB Servers from others at low network layers, a set of CMAN might be enough to secure your DBs against CVE-2012-1675.
At least it might be a simple and fast solution to secure your systens from untrusted areas, until you know how to secure the DB servers themselves. Especially for legacy systems it might be the only solution to secure it.

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.

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.

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.