What can I kill?

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

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

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.