Error when using 11g export client on a 10g database

The only reason to read this post is if you have googled for the error "ORA-00904: "POLTYP": invalid identifier". This error occurs if you try the old export command from an 11g client against a database on version 10g or lower. The export command runs a query against a table called EXU9RLS in the SYS schema. On 11g this table was expanded with the column POLTYP and the export command (exp) expects to find this column. This should not be much of a problem since Data Pump export can be used.

Someone trying to outperform Oracle's cache

The following code is from a migration project I'm working on. It is part of a package named PERFORMANCE and is run every 15 minutes:

  Declare
  test number;
  Cursor c_worklist
  Is
     select *
     from v_worklist
     where id = v_id;
  Begin

  FOR rec IN c_worklist  LOOP
    test := 0;  -- does nothing
  END LOOP;
  end;

A nice little wtf-snippet. The purpose? According to "the vendor" the cursor that loops through every row in a view is supposed to keep the data in Oracle's cache, and if not in place some web service will time out. I believe we have better tools to keep stuff in cache.

Collatz conjecture in PL/SQL

A simple implementation of Collatz conjecture in PL/SQL:

create or replace type int_tab_typ is table of integer;
/

create or replace function collatz(p_n in integer) 
   RETURN int_tab_typ PIPELINED as
   n integer;
BEGIN
  if p_n < 1 or mod(p_n,1)>0 then 
    RETURN ;
  end if;
  n:=p_n;
  while n > 1 
  loop
    pipe row (n);
    if mod(n,2)=1 then 
      n:=3*n+1;
    else 
      n:=n / 2;
    end if;
  end loop;
  pipe row(n);
end;
/

select * from table(collatz(101));

More on Collatz Conjecture (Wikipedia).

The point? Just for fun. Showed it to my 11 years old kid and asked him to figure out the 17 steps sequence of collatz(7) in his head. "Is this going to stop?", he asked half through. "That is what we are going to find out, nobody really knows", I told him.

If you have absolutely nothing useful to do, but have CPU and storage to waste, you may create a table containing n and the number of steps for collatz(n), with values of n ranging from 1 to 2³¹-1 or until storage is exhausted, whichever comes first. Before doing so you may consider changing the function above to just count the steps and look up previous calculations from the table.

Using trace to find wallet location

Not talking about a lost wallet with money, but the wallet used for encryption in the Oracle database and with encrypted backup.

A wallet is used for storing keys used to encrypt/decrypt data to/from the database, as with Transparent Data Encryption. The documentation on Advanced Security says that Oracle searches the parameter ENCRYPTION_WALLET_LOCATION in sqlnet.ora, or if not found it searches for WALLET_LOCATION in the same file. If none of them are given it searches in the default location of the wallet for the database. I found different references to where this default location is, depending on underlying OS and version. After a few rounds of trial and error (always receiving ORA-28368) I gave up and resorted to tracing my process:

select p.spid
from v$session s join v$process p on(s.paddr=p.addr)
where s.sid in (select sid from v$mystat);

Then using strace on the process:

strace -p 42

Among the output from strace something like this can be found:

access("/u01/app/oracle/admin/FOO/wallet/ewallet.p12", F_OK) = -1 ENOENT (No such file or directory)
open("/u01/app/oracle/admin/FOO/wallet/ewallet.p12", O_RDWR|O_CREAT|O_TRUNC, 0666) = -1 ENOENT (No such file or directory)

In this case the directory did not exist. It needs to be created with proper privileges for the OS user running the instance (usually oracle).

On Windows I imagine Process Monitor can be used to track down the location.

Morgans Library has a nice reference on wallets.

Database triggers are evil

If you have a lousy database model, you can get around it with triggers. If you need to hide some business logic from everybody else, you can do it with triggers. If you need a mechanism that bypass the consistency in the database, you can do it with triggers. They live a life on their own, seemingly autonomous from everything else and one easily forgets they exist. That is why I hate them. What matters if you do an database export in consistent mode if some active triggers make the data inconsistent during import? Triggers are like secret police, they are threat to the democracy if they take over.

Oh, just a rant.

Note to myself: next time import fails with ORA-1 or ORA-2298, check for evil triggers.

Changing character set in database

If your database is using anything else than UTF8 as database character set you may consider to migrate from it. Oracle states in the Globalization Guide (10gR2):

At the top of the list of character sets Oracle recommends for all new system deployment is the Unicode character set AL32UTF8.

Depending on what characters you actually have in your database you have three options on how to do this:

1. Changing the character set with the package CSALTER. Only data dictionary is migrated.
   
2. Using CSALTER and convert application data using export/import for all users
   
3. Using CSALTER and convert a subset of application data.

Option 1 migrates the data dictionary, that is, the meta data regarding your application data. Nothing else is changed and Oracle, after successfully executing CSALTER, behaves as if your database was created with the new character set. That works OK if the characters used are actually encoded the same way in the new character set as before, if not funny letters will show up when querying the database, meaning you have to go for option 2 (or possibly 3).

Character expansion happens when a symbol changes from one character set to another with more bytes being used for storage in the latter. This is the case for the euro sign (€) in WE8MSWIN, where it requires one byte, in AL32UTF8 it takes three bytes. This requires a conversion of application data using export/import.

Oracle has made an utility for this, csscan. It checks the data dictionary and application data and gives you an analysis telling you what to do. The following procedure was executed on Oracle 10.2.0.4.


First create these two directories are defined in the database:

• log_file_dir
• data_file_dir

I don't think they are really necessary, they don't seem to be used, but the following script grants access to them for the CSMIG user. Directories are listed in DBA_DIRECTORIES and you may create these anywhere you like with:

create directory log_file_dir as '/tmp/csscan';
create directory data_file_dir as '/tmp/csscan';

Run the following script as SYS in sqlplus to install the tool:

@?/rdbms/admin/csminst.sql

It will ask for a password which you'll use later (on 9iR2 it uses standard password CSMIG). Then start the scanning with:

csscan csmig/your_password full=y tochar=al32utf8 process=3 array=1024000

The csscan command is documented in chapter 12 in the Globalization Guide. The above analyzes the entire database. Otherwise owner, tables or columns may be specified. The process parameter selects the number of simultaneous scanning processes.

When scanning is completed csscan create a database scan report and individual exception reports as scan.txt and scan.err respectively. The file scan.out contains the output from the command above. These files are created in the directory that was current when the command was executed.

RMAN Convert database and ORA-7445

Using the CONVERT command in RMAN can be an efficient way to migrate a database from one OS to another, if they have the same endian.

We migrated 9 databases on 10.2.0.4 from HP-UX (on Itanium) to AIX. The databases where distributed on three servers with not exactly the same hardware configuration. On one of the servers we encountered an error described in bug 888530 on Metalink or My Oracle Support. The process terminated prematurely with ORA-7445 when converting an undo tablespace. The other tablespaces were migrated fine, but the undo tablespace ended up with a missing datafile. After the error occurred the RMAN session was left hanging and after killing it the migrated datafile belonging to undo was removed; may be that had something to do with the file system being NFS mounted. Anyway, our solution was to create another undo tablespace and change the undo_tablespace parameter, and then dropping the corrupted one. In one of the tests we did, we managed to do recovery on the migrated undo tablespace, but some funny error messages showed up in the alert log, probably because it was not finally migrated to the new platform. Meaning, if you get this error, create a new tablespace for undo and drop the old one.