techblog 

We have been having some issues with the database performance of our whois query. It was taking far too much resources, in particular many, many buffer gets. We are running against 10.1.0.3 here so I thought I would look at the explain plan of the query, I provide the final few lines below:

This is the 10.1.0.3 plan fragment. I did not like the look of that full table scan, but could see no way of hinting it out. I then happened to look at explain plan on a 10.1.0.4 instance, and the plan looked a bit more efficient:

So the 10.1.0.4 explain plan is around 100 times more efficient, it is the view pushed predicate that allows the pk_members index to be used. You can read a little about this from here.

The question then became why the 10.1.0.3 was not working and then how to get it to do the predicate pushing. I think I must by now have encountered around 10 bugs in Oracle version 10g and this was yet another: Bug 3723007 - Predicate push down may not occur in 10g.

There is a patch for this bug, but we wanted a quick workaround that we could apply immediately. There is a workaround that involves changing the parameter _optimizer_cost_based_transformation to off. Now traditionally you are not supposed to go mucking around with underscore parameter but as oracle support were giving the go ahead we were not too worried. So we actually wanted this to just come into effect for the user that performs the whois queries so we decided to put a logon trigger for that user:

SQL> CREATE OR REPLACE TRIGGER logon_trigger
after logon on USER.schema
begin
execute immediate
'alter session set "_optimizer_cost_based_transformation"=off';
end;
/

We now have this running and the whois queries are taking around two orders of magnitude less database resources. Just a pity oracle introduced the problem in the first place.

We recently encountered an unusual problem when applying a new Oracle PL/SQL release. One of the stored procedures, a package specification, failed to compile with the following error:

CREATE OR REPLACE PACKAGE EXAMPLE_PKG AS
*
ERROR at line 1:
ORA-06552: PL/SQL: Compilation unit analysis terminated
ORA-06553: PLS-201: identifier 'UNDEFINED' must be declared
;

(We saw the error message in the format PLS-201; I have also seen it in the format PLS-00201.) The main problem debugging this was that the identifier 'UNDEFINED' text gave no clues as to where the problem was. After a good deal of investigation and fruitless internet searching (which is why I am posting this) we solved the problem.

Example_pkg contains a ref cursor:

TYPE EXAMPLE_CURSOR IS REF CURSOR RETURN EXAMPLE2_VIEW%ROWTYPE;

Example2_view is a view which contains select * from another view (N.B. this is actually bad practice; the columns in the select statement should be listed explicitly):

CREATE OR REPLACE VIEW EXAMPLE2_VIEW AS
SELECT * FROM EXAMPLE1_VIEW WHERE …………;

The problem arose because the definition of Example1_view had changed in the release. Attempting to re-compile Example2_view gave compilation errors. We needed to completely re-run the create or replace .... statement for Example2_view.

After doing this the create or replace .... statements for the package specification and body succeeded.

This problem presumably arose because, even though the Example2_view definition stated select * from .... Oracle expanded this at the time this view was created and “hard-coded” the list of fields into its dictionary. After the release, the list of fields held in the dictionary no longer matched valid field names in Example1_view, which in turn caused problems for the ref cursor. Unfortunately the Oracle error message did not state which object had the problem. I believe the problem would also have occurred if Example1_view had been a table.

If you need to do any Cryptography in Java then you use the
Java Cryptography Architecture (JCA) and the Java Cryptography Extension (JCE) with a choice of implementation provider.

To validate PGP signed emails we have chosen to use the popular JCA and JCE provider from
The Legion of the Bouncy Castle. The Bouncy Castle software is free to use and is distributed under a license based on the MIT X Consortium license. The provider’s role is to provide the actual implementation for the API.

Bouncy Castle is a complete Crypto API for Java but unfortunately the lack of documentation and code examples don’t make it easy to use.

Installing The Provider

There are two ways to install the Bouncy Castle provider, you can either configure the JRE to preload the provider or do it dynamically at runtime.

To configure your JRE then edit the following file

jre/lib/security/java.security

and add the following line

security.provider.N=org.bouncycastle.jce.provider.BouncyCastleProvider

where N is the next available number.

To simplify deployment we tend to install the provider at runtime by adding the following line to a class constructor.


Security.addProvider(new BouncyCastleProvider());


Verifying Messages
Nominet receives messages from its tag holders which have been signed using their private key,
to verify a signed message we use the tag holders public key. The signed messages can be verified with Bouncy Castle using the following code example.

public  boolean
verifyFile(InputStream fileStreamIn, PGPPublicKey keyIn)
throws Exception
{
boolean verify = false;
ArmoredInputStream    aIn = new ArmoredInputStream(fileStreamIn, true);

//
// read the input, making sure we ingore the last newline.
//
int                   ch;
boolean               newLine = false;
ByteArrayOutputStream bOut = new ByteArrayOutputStream();

while ((ch = aIn.read()) >= 0 && aIn.isClearText())
{
if (newLine)
{
bOut.write((byte)'n');
newLine = false;
}
if (ch == 'n')
{
newLine = true;
continue;
}

bOut.write((byte)ch);
}

PGPObjectFactory pgpFact = new PGPObjectFactory(aIn);

Object o = pgpFact.nextObject();
if (o instanceof PGPSignatureList) {
PGPSignatureList list = (PGPSignatureList)o;
if (list.size() > 0) {
PGPSignature sig = list.get(0);
sig.initVerify(keyIn, "BC");
sig.update(bOut.toByteArray());
verify = sig.verify();
}

}

return verify;
}

The problems occur when Mail Transport Agents (MTA’s) add spurious white space to the email body after the message has been signed, this causes the message to fail the verification process since the message digests no longer match.

One solution is to strip the extra whitespace from the message using regular expressions, we have found the following Java regex’s work quite well for a large range of emails.

message = orgMessage.replaceAll(" *n", "n");
message = orgMessage.replaceAll(" *$", "");

We have been looking at ways of migrating our database from it’s current home on Solaris to Linux (64-bit AMD). This migration involves two interesting changes, first off we have to change endianness due to the move from sparc archictecture to AMD, but as we are also using RAC we are moving from a clustered filesystem (Vxfs) to Oracle’s very own ASM. In no way is this a one step migration. Here is the method we are thinking of using:

Create your target database on the new system.

In your original database first find out what destination platforms you can use transportable tablespaces to:

sql> select * from V$transportable_platform;

Find out if the tablespace(replace TBNAME with the tablespace your are interested in) to move is self-contained:

sql> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('TBNAME', TRUE);
select * from TRANSPORT_SET_VIOLATIONS;

If there are any rows in the the output from the query, you must include the tablespaces mentioned in the transportable set.

Make all tablespaces you want to move read only:

sql> alter tablespace TBNAME read only;

Run rman to convert the datafile(s) of the tablespace you are interested in:

rman> convert tablespace TBNAME to platform 'Linux 64-bit for AMD'
format='/home/oracle/%U' parallelism 4;

This rman command goes and changes the endianness of the data. Now create the transportable tablespace metadata you can use data pump or good old fashioned imp/exp:

(oracle $) exp 'as sys user' transport_tablespace=y
file=tbname.dmp tablespaces=tbname

Now copy the output from rman and the export over to your new target database server and we can use imp to plug the tablespace back in. You must create the owner of the tablespace on the target database. You CAN’T plug straight into ASM you must go to a filesystem first! So if you are using RAC, you have to make it non-clustered and import on 1 side only:

(oracle $) imp 'as sys user' transport_tablespace=y
file='path to export dump' datafiles='path to converted datafiles'

Now you have to use rman again to convert your datafile into ASM:

rman> convert datafile 'path_to_datafile'
format 'ASM path' parallelism 4;

Finally offline the tablespace and rename the datafile in the database:

sql> alter tablespace TBNAME rename datafile 'path to os datafile'
to 'path to ASM file'

So do this with all your datafiles and you have migrated your database.

As I use ASM more, I’m coming across interesting features. So I was trying to follow my previous and add new Diskgroups. This seemed fine on node1 then I tried the following on node2:

SQL>select name, state, type, total_mb, free_mb from v$asm_diskgroup;
NAME                  STATE       TYPE     TOTAL_MB    FREE_MB
DATA1                 MOUNTED     EXTERN     153597     153503
DATA2                 DISMOUNTED                  0          0
DATA3                 DISMOUNTED                  0          0

SQL> alter diskgroup DATA2 mount;
Error 45 initializing SQL*Plus
Internal error

This crashed me out of sqlplus, so I checked back on the node I created the diskgroup and everything still looked fine. Logging back into the node where I crashed out, the disks were still in a dismounted state. I then had a look back on the node I created them at v$asm_operation, which should show if any procedures are going on:

SQL> select * from v$asm_operation;
Error 45 initializing SQL*Plus
Internal error

So I had managed to crash out of sqlplus on both nodes. I waited. Then after a few minutes I tried to mount the diskgroup on node 2 again. It worked. V$asm_operation now returned 0 rows. Looks like you want to go make some tea, after creating a diskgroup, and v$asm_operation does not look to clever if it bombs out whenever it could show you something useful!

Oracle has made a big play in recent years on getting everyone to use Linux for running their database on, probably so you can afford to buy more oracle licenses. However all the oracle documentation I have seen seems geared towards the 2.4 Linux kernel. With the newer 2.6 kernel, available with RHEL 4.0, the treatment of raw devices changes, and the old /etc/sysconfig/rawdevices file is deprecated in favour of udev.

Why raw devices, you may ask? We are investigating Oracle RAC running on Linux x86-64, with ASM for storing the database datafiles, but unfortunately for RAC you need a shared voting disk and OCR files and these can’t go in ASM. This leaves 2 alternatives, OCFS2 or raw devices. OCFS2 is favoured by many, but is NOT YET CERTIFIED by Oracle, so has been discounted by us.

There is so little information out there on using udev, I’ve cobbled together a hybrid rawdevices/udev solution:

Edit /etc/sysconfig/rawdevices

/dev/raw/raw1   /dev/sdc2
/dev/raw/raw2   /dev/sdc3
/dev/raw/raw3   /dev/sdc1

Stop/start rawdevices /etc/init.d/rawdevices

Edit /etc/udev/permissions.d/50-udev.permissions

raw/*:oracle:oinstall:0660

This is crucial in setting the permissions of the devices on a reboot, if these devices are not owned by oracle you will be in a whole world pain. The above approach works quite well over reboots, but if anyone out there can do this without /etc/sysconfig/rawdevices, I’d be interested to hear it. I suspect you put a script into /etc/udev/scripts

Automatic Storage Management (ASM) is a special-purpose Oracle filesystem cum volume manager. It first appeared with 10g R1. Diskgroups are managed through an additional instance (similiar to a normal database instance), called the ASM instance. This ASM instance has an associated SID, background parameter and spfile associated with it, any database instances that have their datafiles stored via ASM, register themselves with the ASM instance, so if ASM is not running the database will not be running either.

The real advantage with ASM is when it comes to RAC, instead of pure RAW files, you can use ASM to store your clustered database files on, without having to use a 3rd party Cluster Filesystem. I have found a nice little feature that may bite the unaware if using ASM with RAC.

Using ASMLib I mark a disk for ASM usage:

(oracle$) /etc/init.d/oracleasm createdisk VOL1 /dev/sdb1

Now create a diskgroup from this volume:

sql> create diskgroup DATA1 external redundancy disk 'ORCL:VOL1';

You can now check that your diskgroup is ready to use:

SQL>select name, state, type, total_mb, free_mb from v$asm_diskgroup;

NAME     STATE       TYPE     TOTAL_MB    FREE_MB
DATA1    MOUNTED     EXTERN     153597     153503

However, if you are using RAC, look on the other node, it has automatically picked up the fact there is a new diskgroup created but you’ll find the state is DISMOUNTED. I tried doing a shutdown/startup on the ASM instance on the other node, but this gave an oracle error, ORA-15110. Looking this error up, is not very instructive, and the diskgroup while still visible was in the DISMOUNTED state.

What you have to do on the 2nd node, is to mount it manually the first time, then on subsequent reboots it will get mounted automatically:

sql>  alter diskgroup DATA1 mount;

Now all is well on node 2. So in a RAC cluster, the ASM instance on the 2nd node will not automatically mount a newly created diskgroup.