Friday, October 29, 2010

ORA-01591 and quick solution

One of the user reported they got this error from application.

ORA-01591: lock held by in-doubt distributed transaction 4.7.533420

We don't really see this error often. So I did a little research. 
The error message doc from Oracle has pretty good explanation but didn't provide a solution how to resolve this.


lock held by in-doubt distributed transaction string
Cause:Trying to access resource that is locked by a dead two-phase commit transaction that is in prepared state.
Action:DBA should query the pending_trans$ and related tables, and attempt to repair network connection(s) to coordinator and commit point. If timely repair is not possible, DBA should contact DBA at commit point if known or end user for correct outcome, or use heuristic default if given to issue a heuristic commit or abort command to finalize the local portion of the distributed transaction.

What I end up did is pretty easy,  rollback force didn't do the trick. The DBMS_TRANSACTION helped.

SQL> select local_tran_id from dba_2pc_pending;


SQL> rollback force '4.7.533420';

Rollback complete.

SQL> select local_tran_id from dba_2pc_pending;


SQL> exec dbms_transaction.purge_lost_db_entry('4.7.533420');

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> select local_tran_id from dba_2pc_pending;

no rows selected

Tuesday, September 28, 2010

ORA-12547 and procmap error while running sqlplus

If you got following error message while trying to run sqlplus on IBM AIX 5L
This is an exact match of 
ORA-12547 connecting to sqlplus / as sysdba on IBM AIX 5L [ID 372143.1]

Basically because the /proc is not mounted on your server.

/DB/../10204-64/network/admin  PROD 341 >sqlplus / as sysdba
/usr/bin/procmap : no such process : 373080
/usr/bin/procmap : no such process : 373080
/usr/bin/procmap : no such process : 373080

SQL*Plus: Release - Production on Tue Sep 28 15:39:02 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

ORA-12547: TNS:lost contact

Wednesday, August 18, 2010

Hot Backup datafile copy problem with CIO mount option on AIX

We encountered some Hot Backup error after we followed IBM's suggestion to remove CIO mount option from our JFS2 data volumes. 
The error is like follows:

scp /DB/VPROD/data01/sysaux01.dbf .
> cp: /DB/VPROD/data01/sysaux01.dbf: A system call received a parameter that is not valid.

This happens after we put database in hot backup mode and trying to copy datafiles.

So this approves what IBM told us, Oracle will use CIO no matter if the file system is mounted using CIO option. But this created another problem that AIX will not allow non-CIO system operation to access file opened with CIO.
There you go we back to square one, mounted the data volume back with CIO option in order to facilitate our Hot Backup. Another reason to use RMAN backup I guess.

Friday, April 09, 2010

ORA-00064: object is too large to allocate on this O/S (1,16777216)

Not sure how many of you run into this problem. It happens to one of our production database after we trying to increase the SGA size to 320G.

Oracle version is, AIX 5.3 L6 32CPUs 750G RAM

The error is
  ORA-00064: object is too large to allocate on this O/S (1,16777216)

It seems pretty conclusive OS limit problem.  However our OS ulimit for Oracle user is set to unlimited.
Actually the problem is  _ksmg_granule_size  SGA units of granules

Granule size is determined by total SGA size. On most platforms, the size of a granule is 4 MB if the total SGA size is less than 1 GB, and granule size is 16MB for larger SGAs.

In our case since we increased our SGA so big, even 16MB is not big enough to fix our needs.
After increased _ksmg_granule_size to 32MB, we are able to start the instance with 350MB SGA.

  alter system set "_ksmg_granule_size"=33554432 scope=spfile;

Thursday, March 18, 2010

Oracle instance slow startup

Recently we noticed one of our production database take longer than usual to startup.
In some cases, it took 3 to 4 hours for alter database open to complete.
The case is particularly bad for our TEST and DEV database after they got refreshed with production. Our production is very powerful 32 CPUs box, when production took like 20 to 30 minutes to open. TEST and DEV will take hours.

Sat Feb 27 06:48:55 2010
alter database open
Sat Feb 27 09:54:22 2010
Completed: alter database open

We engaged Oracle support and they suggested to do a trace.

SQL> conn / as sysdba
SQL> startup mount
SQL> alter session set events '10046 trace name context forever, level 12';
SQL> alter database open;
Once the instance is opened, immediately turn off the 10046 tracing through that session.
SQL> alter session set events '10046 trace name context off';

The trace revealed that, database is querying two advanced queue tables used by STREAMS. The two tables are highly fragmented, for example table aq$_qt_cap_st_D had just 150 records and had 8000 + extents.$_qt_cap_st_p$_qt_cap_st_d

For TEST and DEV we can easily go around the issue by truncating the two tables because we are not using STREAMS on them. For production, table re-org are in order, for that we choose to use Online Redefinition.

Wednesday, January 27, 2010

tnsnames.ora is picky

Recently we added some TNS entries into our Remote Desktop servers that shared by all our users. The user report newly added entries are not working.
I did a tnsping on them and got these errors.

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION =
TNS-12533: TNS:illegal ADDRESS parameters

I then check the entry in tnsnames.ora file, it reads



Do you find anything wrong with them?

Not sure about you, but it took me a while to figure out that tnsnames.ora is pretty sensitive to the format. The problem is the new entry description all flatten out to the left instead of having indentation like this one just below.

-----(SID = FPROD)

-- Oh well, I think we had same problem here on blogspot, it left align all my postings. Let me use dash instead.

So Oracle think "(DESCRIPTION" is a new entry in the tnsnames.ora instead of the description of previous entry. Because we DBA don't have control over the remote desktop server, I need to create tickets to add new entry. When I pasted the text into ticket, the ticket system left aligned them hence lost indentation.

Well, it's a small trivial problem but sometime it's hard to figure if you don't already know. Because the actual configure text are correct and worked on other hosts.