Today we hit another bug related to Oracle hash group by aggregation. I said another because we already had serious problem with hash group by in 10.2.0.1, as I noted here.
At the moment we’re using 10.2.0.3 Patch 12 and bug is fixed in 10.2.0.5 (and 11.1.07). User encountered bug while processing large dataset with SAS as a front end (extensively utilizing Oracle Analytical functions), on top of that it was during the period of time when server was already under heavy workload.

We’ll try to:

• repeat the error ORA-600 on the same data set with the same tool (SAS) but during off-peak hours. I have a feeling that heavy workload is somehow part of the problem.
• use hint NO_USE_HASH_AGGREGATION within all potential queries. (Con: we’ll see how well will this query work without group by hash feature.)
• disable gby at session level by setting “_gby_hash_aggregation_enabled” to FALSE

I’m certainly not fond to use the same “workaround” as last time by disabling group by hash at instance level, because this time it’s not a “hidden” error and it seems it’s rather sporadic (the same query run well on the same dataset and RDBMS version in the past).

Reference: Metalink Note 6471770.8.

Regards,
AlesK

According to Metalink Note: 578455.1 “Announcement of De-Support of using RAW devices in Release 12G” Oracle will no longer support RAW devices for datafiles, OCR and voting disks. I believe this is a good news considering cumbersome management associated with raw devices – thought not necessarily in all cases and situations.
I’m sure ASM is going to become a mature product by then; perhaps it’s because I’m getting old, but when it comes to changing such critical component as storage management layer I tend to react with caution and conservatism. So far, I survived with cooked file system, avoiding RAW devices, but I’m sure in the long run ASM will be a storage manager of choice, even for non-RAC Oracle customers.

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.