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!