Thursday, 4 December 2014

Problem with OPatch

Today I hit a problem with installation of a patchset. Details below:
[oracle@yyy]$ opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir ./19121548
Oracle Interim Patch Installer version
Copyright (c) 2013, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /oracle/product/
Central Inventory : /oracle/oraInventory
   from           : /oracle/product/
OPatch version    :
OUI version       :
Log file location : /oracle/product/

Invoking prereq "checkconflictagainstohwithdetail"
List of Homes on this system:

  Home name= OraDb10g_home1, Location= "/oracle/product/102"
  Home name= OraDb11g_home1, Location= "/oracle/product/"
  Home name= agent10g1, Location= "/oracle/oagent/product/10.2.0/agent10g"
Prereq "checkConflictAgainstOHWithDetail"not executed 
Unable to create Patch Object.
Exception occured : null

OPatch succeeded.

Colleagues of mine suggested to rebuild the Oracle inventory.
[oracle@yyy bin]$ ./runInstaller -silent -invPtrLoc $ORACLE_HOME/oraInst.loc -attachHome ORACLE_HOME="/oracle/product/" ORACLE_HOME_NAME="Ora11gR2home"
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 1239 MB    Passed
'AttachHome' failed.
'AttachHome' failed.

The solution is quite simple. First one have to remove any remnants of current Oracle inventory. Then the inventory operation starts to work properly. With the inventory rebuild the OPatch also starts to work properly.

Thursday, 6 November 2014

ORA-4030 and huge PL/SQL block

Lately we have got for execution a quite huge anonymous PL/SQL block (~10M). When it went for execution it failed with ORA-4030. There are limits to the size of a PL/SQL block. They may be rised at the OS level or in the database (look at "ORA-4030 (PLSQL Opt Pool,pdziM01_Create: New Set), ORA-4030 (PLS CGA hp,pdzgM64_New_Link)" (Doc ID 1551115.1) on the Metalink).

For example on the OS level the /proc/sys/vm/max_map_count parameter sets the number of the maximum number of memory map areas a process may have for Linux.
But before You will mess with those settings, rethink if it is really necessary - most possibly there is another way to run the equivalent code, which makes a better use of the memory in disposal.
In this very case the 10M block consists of 1mln of calls to a procedure with different parameters provided as literals. Much better way in this case would be to load all the parameter values to a table (by sqlldr to persistent table or by simple inserts in one session to a global temporary table with rows preserved on commits) and then call a procedure by much, much smaller script on values from a cursor or get rid of PL/SQL block, turn procedure to function and call it in simple SELECT.

Friday, 24 October 2014

Hash (#) sign and block comment in SQL*Plus

Today I hit a weird (or on the second thought actually completely normal) behaviour of SQL*Plus utility in certain very special way of events. I have got a package for review and here an excerpt, which brought my attention
create or replace package body SOME_PKG
some comment
###################################################### */

procedure PROC_NAME(arg1 VarChar2) IS
When I call it to create on a test environment, I have got:
SP2-0734: unknown command beginning "##########..." - rest of line ignored.

As this file was created on a Windows environment I thought initially some hidden characters are obstacle or EOL signs. But not. It appears that when one works in editor mode of SQL*Plus, the hash sign (#) if being set as a first character in line, is interpreted as an opening of command line and all text after it is immediately interpreted. E.g.
Connected to:
Oracle Database 11g Enterprise Edition Release - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> create or replace procedure test1 as
  2  /*
  3  # select 1 from dual;


SQL> */
SP2-0042: unknown command "*/" - rest of line ignored.
With block comment it turns into a deadly combo ;-) - one may think it is absolutely ok (and it works with let's say SQL Developer) but with SQL*Plus it fails (the closing of block comment may be ommited as in the example above) and then random errors appear.

Monday, 20 October 2014

ORA-600 [ktbdchk1: bad dscn]

This weekend I was responsible for a switchover of one of our databases. All went fine until the rman backup check - I discovered then that again I hit the bug described here. As I knew it already, I was confident I will soon go to sleep, but then I once again looked at the alert log and found there
ORA-00600: internal error code, arguments: [ktbdchk1: bad dscn], [], [], [], [], [], [], [], [], [], [], []

This is a database on version, which is very important here. On first sight I have found that exists on the Metalink a bug (Bug 8895202: ORA-1555 / ORA-600 [ktbdchk1: bad dscn] ORA-600 [2663] in Physical Standby after switchover (Doc ID 1608167.1)), which happens after switchover, but version covers also a patch for this one. And there are no other descriptions for that version, so there are only some suggestions, what to do to make the possible bug finding easier.
However when I read everything I have found that the fix is disabled by default. So I enabled it according to the article above and even though I was not sure if this is it (the trace messages were different than the ones provided in Metalink), it helped.
Please keep in mind the fix may blow up some queries, so one have to enable also another parameter (Bug 13513004 - ORA-600 [ktbgcl1_KTUCLOMINSCN_1] on blocks fixed up by 8895202 (Doc ID 13513004.8)).

Friday, 26 September 2014

TCP connections and ORA-30678

Just a while ago we had an issue with ORA-30678 on one of test databases.


The scenario looks like the following:
  • there exists a script, which reacts for certain events by connecting to the database and calling a procedure, which among other things sends also a notification email
  • the script keeps persistent connection to the database
  • at some occasion the script was received wrong parameters
  • it make a call to procedure, which opened SMTP connection
  • due to wrong parameter value the procedure threw an exception, which apparently was catched at some level (may be on the db level, but I suppose it simply returned to the script level, which ended a loop turn and started another one with the advent of a new triggering event)
  • it seems that there is a built-in limit set to 16 TCP connections per session in Oracle - apparently 15 more event loop turns were performed
  • at every next loop turn the ORA-30678 was threw

What was wrong?

Apparently the code was wrong. It worked well, but only for good parameters. The lack was exception handling, which have to close any TCP connection at the end of run no matter the communication outcome. Of course good programmer will log the exception circumstances, etc., but the cleanup is crucial here. It is important for all the packages, which use TCP connections: UTL_SMTP, UTL_HTTP, UTL_TCP

Wednesday, 24 September 2014

ACLs modification

In fact this is a very tiny observation.
When one deals with DBMS_NETWORK_ACL_ADMIN it is necessary to call COMMIT to confirm the changes - rather obvious, but sometimes some things end with implicit commit.

Wednesday, 16 July 2014


I have always wrongly understood this stuff with global names. Even if dig something up, shortly after the use I forget. So here is a summary.

GLOBAL NAME definition

It is fully taken from the Oracle documentation (Understanding How Global Database Names Are Formed).
A global database name is formed from two components: a database name and a domain. The database name and the domain name are determined by the following initialization parameters at database creation:
The DB_DOMAIN initialization parameter is only important at database creation time when it is used, together with the DB_NAME parameter, to form the database global name. At this point, the database global name is stored in the data dictionary. You must change the global name using an ALTER DATABASE statement, not by altering the DB_DOMAIN parameter in the initialization parameter file. It is good practice, however, to change the DB_DOMAIN parameter to reflect the change in the domain name before the next database startup.
The place, where the GLOBAL NAME is stored is the table SYS.PROP$. You may also display it with a call to the view called GLOBAL_NAME (there is also a global synonym to this view with the same name).
One more note - in commands specific for db link name (like CREATE DATABASE LINK, ALTER DATABASE RENAME GLOBAL_NAME, etc. where the db link name is used as identifier and not as the value one should use double quotes. However surprisingly a little the name is case-insensitive i.e. one may use "ORCL.WORLD@ANOTHER_USER" as well as "orcl.WORLD@ANOTHER_USER" - the call will work


NAME            VALUE$   COMMENT$
--------------- -------- --------------------
GLOBAL_DB_NAME  EXAMPLE  Global database name

SQL> SELECT * FROM global_name;



I spotted 2 ways to achieve it (DbForums and others):
  1. ALTER DATABASE RENAME GLOBAL_NAME TO "[new global name]";
    IIRC this way has some limitations - e.g. if domain was once defined, it always will be added, thus no way to remove it completely
  2. because this parameter is stored in a table, it is possible to update it with simple SQL
    UPDATE sys.prop$ SET value$='[new global name]' WHERE name='GLOBAL_DB_NAME';
    -- update on view also works IIRC
    -- UPDATE global_name SET global_name='[new global name]';
    This way we may set the GLOBAL NAME to anything we want. However this is messing with the db dictionary, so a little frightening and there is a Metalink note id 1018063.102, when the change hangs. In short when one once starts to update the GLOBAL NAME manually, have to do it this way further.

GLOBAL_NAMES parameter

The GLOBAL_NAMES parameter enforces naming db links as GLOBAL NAME of source databases. The parameter is of Boolean type and may be set globally or in a session.

With the GLOBAL_NAMES enabled one may create many connections to the same database (for example differing in credentials; by using @ sign, e.g.
-- most probably You need here double quotes around the db link name
-- at least I have got ORA-00933: SQL command not properly ended if not use them
create database link "ORCL.WORLD@SCOTT_USER"
connect to scott identified by tiger
using '';

create database link "ORCL.WORLD@ANOTHER_USER"
connect to another identified by lion
using '';

select 1 from dual@"ORCL.WORLD@ANOTHER_USER";