Monday, April 30, 2007

Oracle Advanced Queue -- View Subscriber Registrations

Have you ever wanted to see what registrations/notfications you have registered to a queue subscriber? I want to see that, but why is it so hard to figure out where the info is? I see some mention of SYS.AQ_SRVNTFN_TABLE_Q, but I haven't been able to look into that possibility.

Update: This gives what you need, but it is not as nice as I would like.
SELECT * FROM sys.reg$

Oracle DML Error Logging and ORA-38908

Have you ever encountered an error like this?

ORA-38908: internal error occurred during DML Error Logging ORA-00942: table or view does not exist ORA-02291: integrity constraint (T1.T1_FK) violated - parent key not found

You are probably using DML Error Logging, a statement like this

insert into my_tab (c1, c2, c3)
select a.c1, a.c2, a.c3 from my_other_tab a
LOG ERRORS INTO err$_my_tab ('oops') REJECT LIMIT UNLIMITED;

The reason is that err$_my_tab (and probably my_tab) is owned by a user A but the statement is being run by user B and user B does not have insert on err$_my_tab. It's fairly clear from the documenation that this is required.

Security

The user who issues the INSERT statement with DML error logging must have INSERT privileges on the error logging table.


The problem is, this is the case even if the insert statement is part of a package (my_pkg) and user B has execute on my_pkg. It seems that user B should not need insert on err$_my_tab under this scenario, but it does. This is probably because the DML Error Logging uses autonomous transactions (I assume) and operates outside the context of the package. In this light, it makes sense.

Wednesday, April 18, 2007

Upgrading to Oracle Database R2 10.2.0.1 and the TEMP tablespace

When running utlu102i.sql you get
WARNING: --> TEMP tablespace is not large enough for the upgrade.

This is because your current default TEMP tablespace is not locally managed. The error could certainly be better! Here is what you need to do.

1. Create a new temporary tablespace that is locally managed:
create temporary tablespace temp2
tempfile '/opt/oradata/orcl/temp201.dbf' size 64m
extent management LOCAL uniform size 1m
autoextend on next 32m maxsize 128m;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2;

2. Now, if you want to have the default tablespace you need to drop the TEMP tablespace, recreate is as locally managed, alter the database to have it as the default temporary tablespace, then drop TEMP2.

drop tablespace temp including contents;

create temporary tablespace temp
tempfile '/opt/oradata/orcl/temp01.dbf' size 64m
extent management LOCAL uniform size 1m
autoextend on next 32m maxsize 128m;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;

drop tablespace temp2 including contents;

Thursday, April 12, 2007

Upgrading OEM Grid Control R1 to R3

I'll post more updates soon. If it takes a long time, you need to follow note 421053.1 to install the EMDiagKit and the the R1 to R2 Upgrade checklist (note: 401592.1) the the R2 to R3 checklist (note: 422061.1). Although the note has a link to the EMDiagKit, the link points inside the Oracle firewall--you'll need to call support to send you the file.

If your upgrade hangs on linux, that is, the Oracle Universal Installer (OUI) stops on the first page while detecting OMS and Agents, you may need to follow note 343158.1. The installer will say Select Install or Upgrade but does not give you any options to select. The problem is that the OUI is not playing well with your X-Windows emulator (you're not doing this right on the box, but by exporting your display). Unfortunately, that note is not going to help you immediately if you are on Linux or Unix. You will need to get Oracle Support to send you the patch.

As you do the upgrade, the new R3 documentation has the following:

If the older Oracle Management Service was a seed installation, run the following command:

$NEW_OMS_HOME/oui/bin/runConfig.sh ORACLE_HOME=<OMS HOME> MODE=perform ACTION=configure COMPONENT_XML={encap_emseed.1_0_0_0_0.xml}


This is wrong. After you run this, the install log will say "encap_emseed not found". The whole upgrade process changed in the R3 documenation, and, as far as I can tell, the R2 documentation doesn't exist on the Oracle documentation site. They changed the install method to use the -noconfig option (runInstaller -noconfig) because they had a couple bugs they wanted to fix, so they "fixed" them by having you run the process manually--only they documented it wrong!

So, here is what it should be:

If the older Oracle Management Service was a seed installation, run the following command:

$NEW_OMS_HOME/oui/bin/runConfig.sh ORACLE_HOME=<NEW_DB_HOME> MODE=perform ACTION=configure COMPONENT_XML={encap_emseed.1_0_0_0_0.xml}



Hope this helps.