Working for unlicensed sites

I wonder how other independent professionals feel about helping companies that are running systems on Oracle without purchasing sufficient (or any) licenses from Oracle. Where do you draw the line? A few examples can be illuminating:

One client of mine simply wanted to complete the RFP requirements to write a proposal to sell servers to a major bank. They only needed to install Oracle on a single host, run a simple load test against it for 24 hours, and submit the results with the RFP. I did not ask, but Oracle was clearly unlicensed at this site.

Another client is running a major order processing and fulfillment system, and "are not sure" if they are licensed. They definitely have no support. My sense is that when you fork over your $50K+tax per proc, you will notice the cash missing from your budget. Do you think I should push for proof of license compliance or is it none of my business?

If your answer is the latter, are there any ethics to apply to these scenarios? How far do you go? Would you download EE onto their server yourself and call it their problem if they fail to license it?

What about a site that queries v$active_session_history without paying for the tuning pack?

I routinely help those interested in complying to do so, and help in negotiations with Oracle, but what about these egregiously non-compliant sites?

How to set your server process spinning

For one of my presentations I searched for some time for a way to make my own server process get into a spinning and hung state. I found the best example in this SQL, which spins on 11.1.0.6 and can be run by any user, including non-privileged. When I had a friend run it as a non-privileged user on a windows server, it made the server almost unusable, and necessitated stoping the Oracle service to get things back to normal.

SQL> select 1 from dual where regexp_like(' ','^*[ ]*a');

The statement will not return, and you may not be able to ^C out of it. The server process/thread will consume all idle CPU and the time will be spent in uninstrumented code (STATUS=WAITED SHORT TIME and SECONDS_IN_WAIT > 1).

Don't run this on any system that you can be fired for bringing down! You have been warned.

The alert log, tail -f, Flatland and Windows DBAs

I was working with Dan Morgan over at PSOUG Saturday and mentioned to him that the ADR Command Interpreter (ADRCI) in 11g provides `tail -f`-like capabilities for the alert log, including on Windows. He was impressed by this, as previously, we have had to download third-party software to do anything like `tail -f` against the alert log on Windows.

c:\> adrci
ADRCI: Release 11.1.0.6.0 - Beta on Mon Nov 17 10:29:51 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
adrci> show alert -tail 100 -f

I imagine the ability to follow the tail of logfile to be nothing short of revelatory for some Windows-only DBAs, some of whose eyes I have seen visbly widen in amazement as they watched output appear in the cmd window real-time from the alert log. I described it to Dan as being like popping out from a two-dimensional world into a world where you suddenly realize there are three dimentions. Wow! Everything looks so colorful!

Starwood's partiton management isn't automated...

My Sprint broadband service isn't too quick where I am staying on vacation with my family, so I thought I'd try the hotel broadband at the Westin. When I loaded the web page to sign in, I got:

Our Apologies. Error occured while processing your request.
Please try again later.
Cause: ORA-14400: inserted partition key does not map to any partition

One more thing we all need to automate: partition maintenance.

If I were an Oracle sales person, I might say to myself, "Gee, I wonder if they're licensed for the partitioning option."

unit_test command in oradebug

Thanks to Dan Morgan from PSOUG for getting me to start down this road. Dan pointed me at the unit_test command in oradebug in 11g as well as the dbke_test test:

SQL> oradebug unit_test [arg] [...]

So, what to type in after unit test. There's no usage output when you input garbage:

SQL> oradebug unit_test foo
ORA-00070: command foo is not valid

There's a promising section in the output of strings of an oracle 11g binary with the word 'test' appearing frequently:

ksuxkil_test
ksudss_dbg_test
dbke_test
...

SQL> oradebug unit_test dbke_test foo

Invalid option for dbke_test
Usage:
dbke_test dde_unit_main
dbke_test dde_flow_direct
dbke_test dde_flow_kge
dbke_test dde_test_tagrule
dbke_test sweep
dbke_test dde_test_split
dbke_test dde_flow_kge_soft
dbke_test dde_flow_kge_ora
dbke_test dump_incid_cache
dbke_test reset_incid_cache
dbke_test suspend

Aha! Seems a few of these tests visible in the binary have usage prompts.

I played around with dbke_test (related to ADR) and came up with a couple nice permutations:

SQL> oradebug unit_test dbke_test dde_flow_kge_ora 12333 0 0
SQL> oradebug unit_test dbke_test dde_flow_kge_ora stupid_idiot 0 0

ORA-700 anyone?
SQL> oradebug unit_test dbke_test dde_flow_kge_soft foo bar baz

Run it on a test system unless you want to give your fellow DBA a heart attack. Thanks again to Dan for sharing.

Secrets of the ORA-600 lookup tool

Metalink's ORA-600/ORA-7445 and call stack lookup tool is incredibly useful for finding reference information on various permutations of these errors. I opened the page source for this Metalink page and it is quite interesting. The entire table of error arguments and their corresponding Metalink note IDs is visible. Also we can see the code for processing call stacks, which is educational. We learn, for instance, that the tool supports gdb backtraces.

There is always a link to this tool at the bottom of my web page, http://www.ora-600.net.

SQL*Plus 'prelim' connection

A little known feature of SQL*Plus is the 'prelim' connection, which allows you to connect to an Oracle instance's SGA without alloctaion a session. This is very handy when you are dealing with a completely hung instance that is no longer accepting logins, even as SYSDBA.

You can use the prelim connection to obtain diagnostics from within your own session or in an already-running session in the database. You can take diagnostic traces like hanganalyze and systemstate, or query x$ structs.

Here's an example of dumping the contents v$wait_chains (a new view in 11g) using the prelim connection:

$ sqlplus /nolog
SQL*Plus: Release 11.1.0.6.0 - Production
SQL> set _prelim on
SQL> connect / as sysdba
Prelim connection established
SQL> oradebug setmypid
Statement processed.
SQL> oradebug direct_access enable trace
Statement processed.
SQL> oradebug direct_access disable reply
Statement processed.
SQL> oradebug direct_access set content_type = 'text/plain'
Statement processed.
SQL> oradebug direct_access select * from x$ksdhng_chains
Statement processed.
SQL> oradebug tracefile_name
/opt/oracle/diag/rdbms/test01/test01/trace/test01_ora_3025.trc

Here's an example of dumping hanganalyze and systemstate from within he already-running DIAG process:

$ sqlplus /nolog
SQL*Plus: Release 11.1.0.6.0 - Production
SQL> set _prelim on
SQL> connect / as sysdba
Prelim connection established
SQL> oradebug setorapname diag
Oracle pid: 4, Unix process pid: 9837, image: oracle@host (DIAG)
SQL> oradebug dump hanganalyze 1
Statement processed.
SQL> oradebug dump systemstate 267
Statement processed.
SQL> oradebug tracefile_name
/opt/oracle/diag/rdbms/test01/test01/trace/test01_diag_9837.trc

I believe prelim was quietly introduced in 10g. It does not appear to be available in 9i. This feature is extremely important for obtaining diagnostics upon the first occurrence of a hang. It prevent encountering that hang over and over, never finding the cause.

Thanks, and be sure to check out my whitepapers and articles at http://www.ora-600.net.

Jeremiah