A short memo about Oracle TG installation with sample configuration.

DirectoryPaths, hostnames, sid and account names are not real and as such do not reflect real production environment!

#########################################
MS SQL Server: MSSQLHOST
MS SQL Database: MSSQLPROD
Oracle Transparent Gateway for 10g was installed on MSSQLHOST in
home E:\ORACLE\ORA10TG and patched to 10.2.0.4.
#########################################
#########################################
Listener.ora configuration on MSSQLHOST:
#########################################
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = ORATG)
      (ORACLE_HOME = E:\ORACLE\ORA10TG)
      (PROGRAM = tg4msql)
    )
  )
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = MSSQLHOST)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )
##############################################
C:\ORACLE\ORA10TG\tg4msql\admin\initORATG.ora
##############################################
# This is a sample agent init file that contains the HS parameters that are
# needed for the Transparent Gateway for SQL Server
#
#
# HS init parameters
#
HS_FDS_CONNECT_INFO=MSSQLHOST.MSSQLPROD       <- MS SQL Database
#HS_FDS_TRACE_LEVEL=DEBUG
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=MSSQLPROD_RECOVERY     <- OracleTG Recovery account
HS_FDS_RECOVERY_PWD=pwdpwd
HS_FDS_TRANSACTION_LOG=PROD_TRANSACTION_LOG
HS_FDS_TRANSACTION_MODEL=COMMIT_CONFIRM
# HS_FDS_TRANSACTION_MODEL=READ_ONLY
# HS_FDS_TRANSACTION_MODEL=TWO_PHASE_COMMIT
# HS_FDS_TRANSACTION_MODEL=SINGLE_SITE
HS_COMMIT_POINT_STRENGTH=0
###############################################
##################################
TNSNAMES.ORA entry at Oracle side:
##################################
MSSQLPROD = 
  (DESCRIPTION = 
    (ADDRESS_LIST = 
       (ADDRESS = 
         (PROTOCOL = TCP)
         (HOST = mssqlhost)
         (PORT = 1521)
       )
    )
    (CONNECT_DATA =
       (SID = oratg)
    )
   (HS = OK)
  )
##################################

Years ago, when I worked more with Oracle Replication than I do now, I knew more or less every command I needed to handle in-doubt transactions (as a result of failed two phase commit). On one of our development server something went wrong with distributed transactions between Oracle10g and Transparent Gateway for MS SQL Server.

First, I tried to force rollback of transaction listed in dba_2pc_pending:

SQL> rollback force '19.27.91915';

where 19.27.91915 is LOCAL_TRAN_ID from dba_2pc_pending.

I guess the problem was with TG at MS SQL Server side, because Oracle occasionally tried unsuccessfully to execute rollback on MS SQL Server. At the end, developers simply turned off Oracle TG Listener. As a result I got plenty of error messages in alert.log like this:

Fri Sep 26 12:12:38 2008
Errors in file F:\oracle\admin\oraDB\bdump\orcl_reco_1736.trc:
ORA-28545: error diagnosed by Net8 when connecting to an agent
Unable to retrieve text of NETWORK/NCR message 65535
ORA-02063: Message 2063 not found; No message file for product=RDBMS, facility=ORA; arguments: [2] [ lines] [TGATEWAY] []

I knew that Oracle has a package procedure that can permanently purge such transactions…., here it is: if you know that database is permanently lost and you can not rollback or commit, then you can use dbms_transaction.purge_lost_db_entry to remove failed transaction.

SQL> select local_tran_id, state, tran_comment from dba_2pc_pending;

LOCAL_TRAN_ID          STATE            TRAN_COMMENT
---------------------- ---------------- ------------------------------
19.27.91915            forced rollback

SQL> connect / as sysdba
S
QL> execute dbms_transaction.purge_lost_db_entry('19.27.91915');

SQL> select local_tran_id, state, tran_comment from dba_2pc_pending;

no rows selected

A couple of days ago Andrew Barry published a link on dbaVillage that immediately caught my attention. But let’s start at the beginning of this fairy tell. As we all know Oracle obsoleted Oracle Names Server in Oracle10g, replacing it with Oracle Internet Directory (OID). It’s perfectly legal to install Oracle Internet Directory (OID) and use it for tns names resolving (and nothing else!) at no charge. But, do we really need another ton of software to handle something as simple as tns information delivery to the clients?
According to Oracle product managers it’s perfectly normal to expect from Oracle customers (that are already accustomed to deal with multi gigabytes of software and documentation thrown at them) to install, setup and maitain another ton or two, just for the sake of delivering simple TNS information to the clients. Read my lips: installing, setting up and maintaining OID just for the purpose of serving TNS information is in my opinion insane and plain stupid. Just the thought to jump in that boat was disgusting to me, right from the beginning.
I was since looking for a better way to deal with centralized tns management. I believe tnsManager by Andrew Barry is the tool I was looking for. Bravo Andrew!
tnsManager is available for Windows, Linux and Solaris. I tried Windows version; it took me five minutes to download (33Kb), install as a service (with simple command: tnsmanager install) and setup tns manager (by copying tnsnames.ora to tnsManager directory and correcting a line in tnsmanager.conf). Compare that with setup instructions for OID. Compare 90KB disk storage and around 2MB of memory footprint that tnsManager service needs on Windows with resources needed to run OID: first I would need to download around 1.9GB of software, minimal installation would require anything from 1.5 – 2GB of disk storage, at least 1GB of memory on server, all-in-all close to a day to setup everything.

A plead to Oracle product managers and developers: wake up, watch and learn! Oracle is becoming one of the greatest software bloatware producers – long ago surpassing Microsoft. I remember Larry Ellison keynote at EOUG 1996 in Amsterdam, laughing at Microsoft when they announced that they delivered 14 million lines of code with Windows NT 4, commenting that Microsoft should fire people responsible for that “bloat” of code. Well Larry, guess what, it’s time for you to start firing some of your stuff. Especially the ones responsible for middleware. — grin —

Important uppdate [22 October 2009]:
Andrew Barry released beta version of tnsManager 1.8.3 which fixes annoying bug that prevented SQL Developer to use tnsManager as an LDAP server. It’s now possible to create connection in SQL Developer that uses LDAP, sample screen shot from SQL Developer 2.1:

Use “Load” button to refresh the list of tns aliases served by tnsManager.

Short notes taken during the test phase for installation of patchset 10.2.0.4

Let me start with a bit harsh, but nevertheless a realistic warning: if your search engine landed you here and if you’re looking for quick and dirty list of steps to upgrade your Oracle database, then please, stop reading and keep searching! And don’t make fool of yourself asking on public forums questions like this: “…can you please give me step by step instructions for patchset installation…it’s really URGENT…” – someone asking questions like this one should not be the one who is installing anything on the server, including patchset. Personally, I’ll always make sure to keep such people as far away as I can from the back office (and hence away from myself!).
If you’re totally new to Oracle patchset installation, you need one and only one starting document, with interesting name – readme.html.

At the time of this writing (end of July 2008) there are some known issues listed in Metalink note 555579.1 “10.2.0.4 Patch Set – Availability and Known Issues”. Make sure you read it. In particular the following notes:

• Note:580561.1 “ORA-600 [kddummy_blkchk][file#][block#][18038] during extent operations like TRUNCATE”; if you’re using ASSM tablespaces and direct path loads/inserts (as well as drop/truncate on those segments), you should apply patch 5386204 (at the time of this writing patch is not yet available for Windows x64)
• if you’re running Oracle10g on Windows 2003 SP1 make sure you read Metalink note 464683.1 “Unexplained Database Slowdown Seen on Windows 2003 Service Pack 1”
• and a non-critical one; Metalink note:726418.1ALERT: “The 10.2.0.4 Windows Patchset Overwrites %ORACLE_HOME%\network\admin\sqlnet.ora”, nevertheless Oracle republished 10.2.0.4 patchset on 10. July 2008 (first release for x64 was on 19. May 2008). You can simply backup sqlnet.ora file an restore it after wards – meaning that you don’t need to re-download updated version just to fix this bug, if you have version from 19th of May.
• Bug 5082178 – Bind peeking may occur when it should not — this bug was fixed in 10.2.0.4, meaning that some queries that’re now running fine (due to the bind peeking taking place, even when it should not) can now experience performance problems. I don’t think we can truly test this due to a large number of schemas, applications etc. We’ll see how lucky we’re ;-)

We’ll take particular care for these three critical bugs introduced in 10.2.0.4:

• Bug 7038750 – Dump (ksuklms) / instance crash — it’s less likely to happen on non-RAC but still possible…fortunately there is workaround available to avoid crash.
• Bug 5868257 – Dump / memory corruption from UPDATE DML
• Bug 6917874 – Wrong results from multi level push of join predicates

In readme.html you’ll find in section “15.19 Native Full Outer Join Implementation” Oracle recommendation to turn on hidden parameter:

_optimizer_native_full_outer_join =force

I’ll certainly not follow their advice. If they’re confident in the benefit of turning this parameter on, then why is it listed in patchset as as step that should be performed by DBA. Are they afraid of possible regression? ;-)

It’s always interesting to examine platform specifix bugs that were fixed by patchset. The bugs fixed in 10.2.0.4 on Windows x64 that caught my attention:

• 5940550 VIRTUAL MEMORY OF ORACLE.EXE GROWS ON 64 BIT X86 WINDOWS PLATFORM
• 5205552 EXCEPTIONAL HIG VM SIZE USAGE FOR ORACLE.EXE ON 64 BIT X86 WINDOWS PLATFORM
• 5041672 64-BIT WIN2K3 OS IS INCORRECTLY REPORTED AS 32-BIT WIN2K3
• 4927774 UNRESPONSIVE SYSTEM WITH HIGH KERNEL CPU UTLIZATION

One thing that remains open at this stage of testing 10.2.0.4 is Large Pages support in 10g. We failed miserably trying to setup Oracle10g to use Large pages with 10.2.0.3. For now, it remains unclear which fault it is; Oracle, Microsoft or simply ours.

Someone on dba-Village forum asked about how to protect username and password for connecting to RMAN catalog database. Since I’m strongly against using remote os authentication (remote_os_auth=true) in real life production environment, I would probably choose Oracle Wallet for storing credentials for connecting to rman catalog.

Note: With Oracle Wallet implementation we’re relying on file system permissions that protects our wallet; it’s not perfect but I’m happy to trade this “risk” with (in my humble opinion) much riskier approach of using remote os authentication. Anyone with access to the wallet that has auto-login feature turned On, can connect as user stored in the wallet without a password! Approach described in this post should be used seldom and with care.

Here is a mini How-to (I was using Windows 2003 and Oracle11g):

###########################################
1) Create Oracle wallet
- the result of this step is directory D:\oracle\rmancat_wallet 
  with two files: cwallet.sso and ewallet.p12 . 
###########################################

cmd> mkstore -wrl D:\oracle\rmancat_wallet -create
Enter password: mysecret

PASSWORD_POLICY : Passwords must have a minimum length of eight characters and
contain alphabetic characters combined with numbers or special characters.
Enter password: mysecret1

Enter password again: mysecret1


####################################################
2) Adding database user credentials to this wallet
ORA11   .... TNS alias for RMAN catalog database
rmancat .... database user 
test    .... password for rmancat
####################################################

mkstore -wrl D:\oracle\rmancat_wallet -createCredential ora11 rmancat test

Enter wallet password:

Create credential oracle.security.client.connect_string1


#######################################
3) Configure sqlnet.ora at client side
#######################################

WALLET_LOCATION = (SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=D:\oracle\rmancat_wallet)))
SQLNET.WALLET_OVERRIDE = TRUE

########################################
4) Test connection
########################################

D:\ORACLE>sqlplus /@ora511

SQL*Plus: Release 11.1.0.6.0 - Production on ╚et Jul 10 13:58:00 2008

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> show user
USER is "RMANCAT"

########################################
5) Additional security checks
########################################

I would recommend to save sqlnet.ora and tnsnames.ora separately
from the common oracle home, for example we can copy both files to
the wallet directory (in our example D:\ORACLE\rmancat_wallet).
Make sure that only user executing rman backup has read permissions on this 
directory. By default Oracle changes permissions only on files
cwallet.sso and ewallet.p12, leaving directory permission to be inhereted from
the parent (at least that's the case on Windows, I'm not sure about Linux/Unix)!
  
Don't forget to setup environment variable TNS_ADMIN pointing
to your wallet directory at the beggining of backup script.


##############################
6) Various handy commands 
##############################

>> ------------------------------
>> List the content of the Wallet
>> ------------------------------

cmd> mkstore -wrl D:\oracle\rmancat_wallet -listCredential

Enter wallet password:

List credential (index: connect_string username)
1: ora11 rmancat

>> -------------------------------------------------
>> Modify credential stored in the wallet
>> -------------------------------------------------

cmd> mkstore -wrl D:\oracle\rmancat_wallet -modifyCredential ora11 rmancat newpassword

Enter wallet password: mysecret1

Modify credential
Modify 1

>> -----------------------------------
>> Deleting credential from the wallet
>> -----------------------------------

cmd> mkstore -wrl D:\oracle\rmancat_wallet -deleteCredential ora11

Enter wallet password:

Delete credential
Delete 1

>> ------------------------------------
>> Adding credential
>> ------------------------------------

Remember, you can have only ONE user per TNS alias stored in the wallet. If you need to store
two users (schemas) for one TNS, then you'll have to use two wallets!

For example, this is possible:

> mkstore -wrl D:\oracle\rmancat_wallet -createCredential ora11 rmancat test
> mkstore -wrl D:\oracle\rmancat_wallet -createCredential ora12 scott tiger

If you try to add second account for the same TNS alias, you'll get error:

> mkstore -wrl D:\oracle\rmancat_wallet -createCredential ora12 scott2 tiger

Create credential Secret Store error occured: 
oracle.security.pki.OracleSecretStoreException: Credential already exists

In this case you'll need to create new wallet for scott2.

##################
7) References
##################

Metalink Note:340559.1 "Using The Secure External Password Store"

Chapter 9 Secure External Password Store:
http://download.oracle.com/docs/cd/B19306_01/network.102/b14266/cnctslsh.htm#g1033548