Scientific Notation and SQL Loader

Sometimes you receive data in scientific notation. The way to handle this when using sql loader is to utilize the FLOAT EXTERNAL directive in the sql loader controlfile.

ex
STD_DEV FLOAT EXTERNAL

11G upgrade Heterogeneous Services quirk

Last week I went through an upgrade from 10G to 11G and had a problem with some dblinks I had setup to use the generic ODBC gateway. The links stopped working. The fix was to check the Enable Quoted Identifiers option in the DSN Advanced tab.

Also, as I was searching I found some suggestions to add the following line to the hs/admin/init.ora file(s)

HS_FDS_SUPPORT_STATISTICS=FALSE

Oracle on Windows nuances

Over the past year I have begun to support Oracle in a Windows environment. This experience hasn't been as horrifying as expected, however there still have been some nuances. here's my list:

• Windows password changes suddenly prevent EM jobs from running. This is something you need to dicover as soon as possible
• Restoring a database on Windows with RMAN requires there be a Windows service running for the database.
• Removing software and Oracle Home assignments have always been tough on Windows (even for a client only install which I have worked with quite a bit). This is due to the registry mess and convoluted ways to manage environment settings in Windows.
• "Logon as Batch' is required for Enterprise Manager jobs that will be running. This is assigned Via Control Panel Administrative Tools Local Security Policy Local Policies User Rights Assignment

RMAN switch to COPY

RMAN has a neat feature that allows you to switch the database to run on a backup copy (after recoverying it)

Setup:
-------
In order to set this up you need a datafile copy backup setup in RMAN.
example:
BACKUP FOR RECOVER OF COPY DATAFILECOPY FORMAT='T:\ImageCopies\ORCL\%d_%I_%N_%f' WITH TAG '%d_image_copy' DATABASE;

Make sure T:\ is independent from your current datafile(s) location

SWITCH example (USERS tablespace)
----------------------------------------
Now let's assume we had an i/o error on the file(s) associated with the USERS tablespace and we need to restore ASAP.

SQL "ALTER TABLESPACE users OFFLINE IMMEDIATE";
SWITCH TABLESPACE users TO COPY;
RECOVER TABLESPACE users;
SQL "ALTER TABLESPACE users ONLINE";

The above will redirect to the datafile copy, recover it, and bring it online so that the database operates with it.

Now, you probably want to redirect back to the original datafile location at some point. to do so, you would need to perform a new datafile copy backup in RMAN. Here, you would direct the copy to the ORIGINAL datafile location.

Here's an exmaple on how this would be done:
backup as copy datafile 'T:\ImageCopies\ORCL\ORCL_2575401446_USERS_8' format 'D:\oradata/ORCL\USERS_01.dbf';

After that run the same procedure outlined abovve to SWITCH the datafile. Oh, and assuming that all goes well, - take another backup!

Very busy TNSLSNR process

I encountered a problem with a listener running on a windows machine that had a 10G Oracle database and companion installed in separate ORACLE_HOMES. The ons.log file was HUGE under the companion install. And the listener was consuming the CPU. I found some posts on OTN compaining of the same. It turned out that there was a config problem with the opmn process. Changing the ports in the ons.cnfig and in opmn.xml files solved the problem.

The errrs in the ons.log file were of the form:
Date Time Local connection 0,127.0.0.1,6100 missing form factor.

Database Managed Services - 2008 Strategy for ServerSide

Over the coming year I am planning on extending my consulting services company into the area of managed services and support. More and more I am seeing companies that would like to bring these services on board to either supplement their existing staff or to bring these skills to the table for the first time. Let's face it - deep skills in database technology are both hard to come by AND expensive. Many small, medium, and even large companies would love to buy these services without having to commit to a large staff or a long term outsourcing agreement. I am in the process of drafting initial agreements for several of my current clients now and hope to extend the offer to new clients over the coming year.

If you have interest let me know.