SQL> oradeblog

Goodbye ORA-600, hello, Blue Gecko

2009-01-30T12:41:00.001-08:00

Things are changing a little around here. I have been running ORA-600 Consulting for five years, and have made the big decision to join Blue Gecko, a remote administration and managed hosting provider here in Seattle. My new colleagues look familiar. They are the same people that I worked with in the first several years of building Amazon.com's IT infrastructure in the late 90s.

This was a big decision, but my direction increasingly has been toward teaching and emergency support. These happen to be two areas Blue Gecko is excited to build on. So I will continue my focus in these two areas with the support of a great company. Best of all, I will no longer have to be in charge of billing, accounting, sales and toilet cleaning duties for my own company. This should leave me more time to work on the Oracle topics I care about more.

I'm hoping my clients will all come over to Blue Gecko with me. They'll frankly get much better support from the 24x7 support staff and mature monitoring and administration infrastructure Blue Gecko provides. They can still ask for me if they want!

Log transport annoyance with ORA-01017 and ORA-16191

2009-01-02T14:29:00.000-08:00

In 11g, setting up log transport for DataGuard, Streams, CDC, etc., you are likely to run into this annoyance. No matter how carefully you make the SYS passwords in the passwordfile the same byte-for-byte on both the source and destination hosts, the source archiver will fail trying to log in to the destination DB with these messages written to the alert log:

ORA-01017: invalid username/password; logon denied
ORA-16191: Primary log shipping client not logged on standby

The solution is to create the passwordfiles with orapwd using the argument 'ignorecase=y':

% orapwd file=orapwORCL password=crash_on_install ignorecase=y

This issue has been documented by several bloggers and forum participants, but there is still no note in Metalink as of today. I'm not sure what part of the codepath contains this bug or if perhaps this is one of those that would be addressed with the "not a bug" response from dev.

You will probably have found this article after following the instructions in the error documentation:

Check that primary and standby are using password files and that both primary and standby have the same SYS password. Restart primary and/or standby after ensuring that password file is accessible and REMOTE_LOGIN_PASSWORDFILE initialization parameter is set to SHARED or EXCLUSIVE.

Restart the primary. I love it :-) Jeez.

All I can say is I am sorry you went through this.

What can I kill?

2008-12-09T18:00:00.000-08:00

Fairly often, I have to kill Oracle background processes. At first this probably seems crazy. Do I want to destroy my database?

No, it is SOP. Archivers (ARCn) are my favorite targets. You can solve all kinds of problems by killing the archivers and letting them be automatically restarted. Often issues with redo transport services (Streams, CDC, Logical Data Guard) can be solved by forcing the archivers to restart.

Another time you might want to kill a background process is if it is leaking memory.

Unfortunately, there are many processes in Oracle that cannot be killed. Off the top of your head you might mention PMON, SMON and LGWR. Is there anyplace in the documentation that breaks down which you can kill without terminating the instance? No. So I experimented on 11g with these results:

Killable:
ARCn: Redo log archivers
CJQn: Job scheduler coordinator
Dnnn: Dispatchers
DIA0: Diagnosibility process 0
DIAG: Diagnosibility coordinator
FDBA: Flashback data archiver process
Jnnn: Job scheduler processes
MMNL: Manageability Monitor Process 2
MMON: Manageability Monitor Process
PING: Interconnect latency measurement
Qnnn: Queue cleanup processes
QMNC: Queue coordinator
RECO: Distributed recovery process
Snnn: Shared servers
SMCO: Space management coordinator
Wnnn: Space management processes

Instance-Critical:
ACMS: Atomic controlfile to memory server
CKPT: Checkpoint
DBRM: Resource manager process
DBWn: Database writer processes
LGWR: Redo log writer
LMDn: Global enqueue service daemons
LMON: Global enqueue service monitor
MMAN: Memory manager
PMON: Process monitor
PSPn: Process spawners
RMSn: RAC management server
RVWR: Recovery writer
SMON: System monitor process
VKTM: Virtual keeper of time process

Keeping CDC from falling apart catastrophically

2008-12-08T23:34:00.000-08:00

CDC, especially the log-based implementations such as "asynchronous autolog" can be a really nice effective way to maintain a DSS system with low impact on a production OLTP data source.

One problem with CDC is that it uses Streams, specifically the logminer capture and apply components, which can be challenging to make work successfully. A constant danger is the CDC staging database encounterin a problem that makes it unable to continue processing changes. In development, we find ourselves rebuilding the change source and sets frequently to overcome bugs, mistakes and mysterious failures.

In production, however, rebuilding the CDC environment requires rebuilding the entire DSS reporting system or data warehouse. Each change from the production source must be captured by CDC or there will be data loss. Rebuilding CDC implies losing transactions.

As it turns out, everything hinges on the availability fo a recent logminer data dictionary build in the redo logs. With CDC, you can reinstantiate the CDC environment as of any point in time, as long as you have a source archivelog with a dictionary build, and all subsequent logs to the current pont in time. Thus my #1 recommendation for people using async autolog CDC:

Generate a data dictionary into the source redologs daily using a script similar to this:


SQL> variable dicscn number
SQL> exec dbms_capture_adm.build(:dicscn)
SQL> column discn  format 9999999999999999999999
SQL> print dicscn
(Record this SCN)

If you ever need to reinstantiate the CDC environment, re-create the change source as of the SCN of the last available dictionary build:


SQL> exec dbms_cdc_publish.create_autolog_change_source( -
     change_source_name=>'MYSOURCE', -
     description=>'My OLTP Source', -
     source_database=>'MYDB', -
     first_scn=>'', -
     online_log=>'y')

Then ceate a change set that begins processing changes as of the time that the previous failed CDC staging system failed:


SQL> exec dbms_cdc_publish.create_change_set( -
     change_set_name=>'MYSET', -
     description=>'My Change Set', -
     change_source_name=>'MYSOURCE', -
     begin_date=>to_date('2008-12-09 18:00:00','yyyy-mm-dd hh24:mi:ss'), -
     stop_on_ddl=>'y')

Finally create your change tables and subscriptions as usual. The changes in the change tables will beg as of the date specified in create_change_set.

Working for unlicensed sites

2008-11-21T12:42:00.000-08:00

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

2008-11-17T13:30:00.000-08:00

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

2008-11-17T10:12:00.000-08:00

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...

2007-11-30T15:34:00.000-08:00

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

2007-10-31T14:40:00.000-07:00

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

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

2007-10-31T10:17:00.000-07:00

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

2007-10-30T16:15:00.000-07:00

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