Monday, October 01, 2007

Chained Oracle Web Caches, Reverse Proxy, REMOTE_ADDR and CLIENT_IP

Most people today are using a DMZ. There is a great deal of variety in configuring the DMZ, from having nothing but a Reverse Proxy (RP) in the DMZ to having everything including the database in the DMZ (and possibly Dataguard or Oracle Streams to push data to the DMZ database). This post will focus on the specific instances of needing to get the browser's IP (or as close as one can get) all the way through to the database--specifically using Oracle Portal, PL/SQL and Java Portlets, Application Express or anything sitting behind the Oracle Webcache.

Scenario 1: This does not even require a DMZ configuration. If you are running Oracle Portal you may find it hard to get access to the browser's IP address in a UI Template of Page or Item Skin. The problem is that the Parallel Page Engine (PPE) calls Web Cache which in turn calls Apache to get your page. Web Cache sees the call as coming from the PPE, so you get the IP address of your middle tier, not of the browser. I have another post that covers that topic.
That post will be used in all of the scenarios below.


Scenario 2: Oracle Web Cache in the DMZ (WC1) acting as a Reverse Proxy to another Web Cache that serves your content (WC2). It looks like this:

Browser ---> WC1 ---> WC2 ---> Apache ---> mod/plsql ---> Database

The browser sends its IP address in a header variable, REMOTE_ADDR, in all requests. (Note: if it uses a forward proxy, the forward proxy may change the REMOTE_ADDR value to its own IP address. We can't do anything about that. For example, all AOL users may show up with only 3 IP addresses.) When WC1 gets the request, it moves the value of REMOTE_ADDR into a new header variable, CLIENT_IP and puts its own IP address into REMOTE_ADDR. Normally this is not an issues, because mod/plsql automatically puts the value of CLIENT_IP back into REMOTE_ADDR, but with a second web cache, see what happens. WC2 replaces ClientIP with REMOTE_ADDR and puts its own IP into REMOTE_ADDR. The table below shows what happens to these values as they progress...

REMOTE_ADDR x.x.200.10 x.x.300.5 x.x.200.10 x.x.200.10
ClientIP not set x.x.200.10 x.x.200.10 x.x.200.10

As you can see, by the time it makes it to the database the only thing available is the IP address of WC1 (unless it is a PPE call from Portal, in which case it is the IP of WC2).

The Web Cache guys recognized this could happen and put a feature into the Web Cache settings to allow WC2 to accept the ClientIP that was forwarded from a previous Web Cache. In the WC Admin, go to Properties > Security and set "Accept client IP addresses encoded in ClientIP HTTP headers" to Yes.

If you are using Portal, be sure to read about getting the value past the PPE in the link above.


Scenario 3: Apache 2.x in the DMZ (RP1) acting as a Reverse Proxy to another Web Cache that serves your content (WC2). It looks like this:

Browser ---> RP1 ---> WC2 ---> Apache ---> mod/plsql ---> Database

This is similar to scenario 2, however RP1 uses the header variable X-Forwarded-For instead of ClientIP. X-Forwarded-For makes it all the way through to modplsql, but modplsql does not know to send it on to the database. You need to add the following line to your modplsql DAD:

PlsqlCGIEnvironmentList X-Forwarded-For

Your dad will look something like the following:

SetHandler pls_handler
PlsqlDocumentTablename wwv_flow_file_objects$
PlsqlDatabaseUsername HTMLDB_PUBLIC_USER
PlsqlDefaultPage apex
PlsqlDatabasePassword xxxxx
PlsqlCGIEnvironmentList X-Forwarded-For
Allow from all

You can then use owa_util.get_cgi_env('X-Forwarded-For') to retrieve the IP Address of the client.


That covers it. I hope this helps. As always, click on the My Work link to the right if you would like in-person assistance.

Monday, September 17, 2007

Oracle Internet Directory (OID) ldapbind gives: sgslufread: Hard error on read, OS error = 10054

We were using a Cisco Load Balancing Router (LBR or CSS) and trying to do a simple ldapbind -p 389 -h machine would give the following error:
sgslufread: Hard error on read, OS error = 10054 (on windows)
sgslufread: Hard error on read, OS error = 104 (on linux)
ldap_bind: Can't contact LDAP server

(might have also seen sgslufread: Hard error on read, OS error = 194 )

It turned out that the LBR was configured with Layer 5 load balancing (it does URL inspection). It is turned on by adding url "/*" to the content
content layer5
protocol tcp
vip address
add service server1
port 389
url "/*"

The problem is that we have LDAP traffic, not HTTP traffic. Layer5 (url "/*") is for HTTP traffic. Removing the
url "/*"
took care of the issue.

I also received the same error when using an F5 load balancer to connect via LDAPS (SSL encrypted) on port 636. In this case the solution was to turn off the F5 LDAP monitor for port 636. For some reason the F5 does not like to monitor the OID SSL LDAP port.

Friday, August 31, 2007

ASM and ORA-29701: Unable to connect to Cluster Manager

I was recently at a client. They told me that the Oracle database binaries were installed and that they wanted an ASM instance created and a couple of disk groups. Easy enough...

Every time I would try to start an ASM instance I would get this error:
ORA-29701: Unable to connect to Cluster Manager

It turns out that this machine was a clone of another machine and that the binaries weren't installed, but cloned from another machine. I don't ever do it that way, so I was at a bit of a loss. I finally found a solution. From the $ORACLE_HOME/bin directory I ran

localconfig delete
localconfig add

This took care of the problem. Hope this helps.

Tuesday, August 28, 2007

Oracle Portal Content Management Event Framework

I recently put together a little demo that might be helpful for people starting out with the Oracle Portal Content Management Event Framework (CMEF). The documentation is here. While the documentation is good, it can be a bit overwhelming. My example gives you everything you need to get started, and nothing you don't. So, let's get started.

Goal: Capture all events from the CMEF and store the content in a table. If you can store it in a table, you can do just about anything with it.


1. Create a sequence to be our table's primary key
2. Create the table and associated triggers
3. Create a package and procedure that will be notified when an event occurs, dequeue the event and put the contents in the table.
4. Create a subscriber to the CMEF queue.
5. Register the subscriber and the procedure created in step 3.

Full steps
All scripts run in the portal schema

1. Create a sequence to be our table's primary key

MAXVALUE 999999999999999999999999999

2. Create the table and associated triggers


before insert on "C2_EVENT_LOG"
for each row
if :NEW."ID" is null then
select "C2_EVENT_LOG_SEQ".nextval into :NEW."ID" from dual;
end if;


3. Create package and procedure


PROCEDURE event_notify(context raw,reginfo$_reg_info,descr$_descriptor,payload raw, payloadl number);



PROCEDURE event_notify(context raw,reginfo$_reg_info,descr$_descriptor,payload raw, payloadl number)
dequeue_options dbms_aq.dequeue_options_t;
message_properties dbms_aq.message_properties_t;
message_handle RAW(16);
message portal.wwsbr_event;
lclob clob;
pmsg varchar2(100);
dequeue_options.msgid := descr.msg_id;
dequeue_options.consumer_name := descr.consumer_name;

-- Get the Message and remove it from the queue
-- Note: this will dequeue the message. If you don't do anything with i
-- it will be gone and you won't be able to retrieve it again
DBMS_AQ.DEQUEUE(queue_name => descr.queue_name,
dequeue_options => dequeue_options,
message_properties => message_properties,
payload => message,
msgid => message_handle);

-- Insert message contents into the table

insert into c2_event_log (event, event_date, event_payload)
values ('A',sysdate, 'message.action: '||message.action
|| ' message.object_class: '|| message.object_class);

-- there are a log of other message properties, look at the portal.wwsbr_event type
-- for a full list of the contents of the message

END event_notify;


4. Create a subscriber to the CMEF queue
( queue_name => 'PORTAL.WWSBR_EVENT_Q',
subscriber =>$_agent( 'C2_SUB', null, null ) );

5. Register the subscriber and the procedure created in step 3
1 );

Let me know if this works for you!

Just a little note... The following query will give you all queue registrations:
SELECT * FROM sys.reg$

The following URL will give you portal queue subscribers (you must be logged in as portal):

And finally . . . if you want to remove those subscriptions, do the following:
1 );

/* **** REMOVE SUBSCRIBER **** */
( queue_name => 'PORTAL.WWSBR_EVENT_Q',
subscriber =>$_agent( 'c2_SUB', null, null ) );

Thursday, June 21, 2007

Oracle Fusion Middleware Regional Director Program

I was recently invited to become an Oracle Fusion Middleware Regional Director. While pleased by the recognition, I'm really enthused about the opportunity this will give me to meet with Oracle development and product management. The Regional Directors are invited to regular con-calls and occasional live events. I will be sure to pass along points of interest on this blog. If you have anything that you would like addressed, add a comment and I will bring it up.

Friday, June 01, 2007

Application Express Debug Mode Timing on Post/Submit

A co-worker recently approached me with an Apex 2.2.1 problem. He had a report that was running extremely slowly. The report used a database link. He suspected the dblink was the problem.

We spent some time looking over it directly in sql*plus (always a good way to start) and confirmed that the query ran slowly accross the link. Next step, we ran the query directly in the source database--still slow. There were a lot of rows in the view (which was based upon two other views, which were both based upon views). Only one index, on the primary key. I recommended some specific indexes that would help his situation.

A week later he returned. Now the query ran great in sql*plus, but the report was still slow in Apex with certain query criteria. I ran the page, submitting different criteria and confirmed that with some criteria it ran very slowly. Turning on debug indicated that the query region was running fast. I suspected that the debug mode timing somehow did not include the time spent in the linked database (boy was I wrong).

After some hair pulling, I looked at the page that I was submitting (the page with the query criteria) and saw several validations on that page. Aha! Perhaps a validation was the culprit. The report was branched via a "Branch to Page or URL" branch, so debug mode was not showing anything that happened during the submit portion--only the render portion of the report page. I created a new branch with sequence=1. This branch was a Direct Branch (see image). By making it a direct branch I was able to see the time spent in the validation:

0.03: Branch point: BEFORE_VALIDATION
0.03: Perform validations:
0.03: ...PL/SQL "function body returning error text"...
317.62: ...

As you can see, the validation was taking over 5 minutes. Using the direct branch made all difference, allowing me to look at the submit processing. The validation also used the dblink, but did a select count(*) from that same view (with 50 million rows)--not using any indexes. Cleaning that up did the trick.

Tuesday, May 08, 2007

How to Enable Tracing in Oracle Forms 10g

Enable Tracing from the url by adding the following to your forms url:
&record=forms&tracegroup=0-98,100-199 (note: item 99 causes some issues, so we are skipping it here. See the Oracle documenation for a description of what is being traced for each item.)

for example

You will get a trace file in the following location:

%ORACLE_HOME%\forms\trace\forms_xxx.trc where xxx is the forms session ID.

Open dos window and do the following:


set PATH=%ORACLE_HOME%\jdk\bin;%PATH%

set CLASSPATH=%ORACLE_HOME%\jdbc\lib\;%ORACLE_HOME%\forms\java\frmxlate.jar

java oracle.forms.diagnostics.Xlate datafile=%ORACLE_HOME%\forms\trace\forms_xxx.trc outputfile=%ORACLE_HOME%\forms\trace\html_xxx.html outputclass=WriteOutHTML

you will get a file html_xxx.html in your %ORACLE_HOME%\forms\trace directory.

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

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.


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 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;


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;


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/ 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/ ORACLE_HOME=<NEW_DB_HOME> MODE=perform ACTION=configure COMPONENT_XML={encap_emseed.1_0_0_0_0.xml}

Hope this helps.

Tuesday, March 20, 2007

Oracle Application Express 3.0 (APEX 3.0)

It's available. Not much more needs to be said. We'll be downloading and installing this weekend. If all goes well I won't need another blog entry. If things don't go well, I'll post any fixes I come up with.

Thursday, March 01, 2007

oraDAV Portal and Third Party Authentication

Have you used the Oracle Drive with your portal? It is easy to set up and it is a great feature. Do you use Third-Party Authentication (TPA) with Oracle Single Sign-On (SSO)? You probably don't need it, but if your company has another SSO standard, at least it is fairly easy to set up.

What if you want to use both? Seems it should be available. Search the documentation and you won't find anything saying you can't. So it should work, right? Maybe. Nothing in the documenation tells you how, either. So far it is impossible to tell. Oracle Support has an SSO group (tpa), the http group (oraDAV), and the Portal group. Getting them to work together on this is going to be tons of fun. I'll give an update when we figure it out.


Still waiting on something from Oracle Support, but I have figured out that Portal using mod_oradav does not use SSO at all. It just does a test bind against OID. SSO can be completely down and it will still authenticate (without TPA) as long as OID is up.

This means that the authentication likely happens directly from Portal via dbms_ldap. If this is the case, it means that the user's password in LDAP will be the authentication method, not what the SSO TPA is configured to use. This won't be a problem if you keep users' passwords in OID up to date with whatever your TPA mechanism is, but it is a problem, as in my case, when OID doesn't have any idea what the users' real passwords are.

Update 2

It is clear that Portal is doing the authentication against OID. No word from Oracle on whether this is by design or if there could ever be a TPA solution. For now I have to assume that it won't work unless passwords are stored in OID. Next step is to find a way to sync those passwords.

Thursday, February 08, 2007

Oracle SQL Developer - ORA-00942

I recently tried to user SQL Developer with a schema and found that I could not view packages, procedures or functions. I would get ORA-00942: table or view des not exist. I still don't know why, but granting SELECT_CATALOG_ROLE to the user solved the problem.

Update: I had a similar problem in Oracle Data Miner (Data Mining). I granted unlimited tablespace to the user and the problem went away. It may have been a tablespace issue for SQL Developer as well...

Friday, February 02, 2007

Oracle Portal Client IP Address (REMOTE_ADDR)

You may want to show the IP address of the client (the browser) on a Portal page. This can be tough because the page is built by the parallel page engine (PPE). At that point, the actual requestor is the mid-tier itself. You need to make a call that goes all the way to the database without being redirected through the PPE. I do this by creating a pl/sql procedure (ideally part of a package) that I then call from a bit of javascript in the page (possibly through a UI Temple / HTML Template). Note: if the user is going through a proxy you will get the proxy IP. If you have a reverse proxy setup (e.g. multiple Oracle Web Caches or Apache Reverse Proxy) you can set things up to get the correct browser IP. See my other post on setting up chained Web Caches.

Here is the procedure, it's fairly straightforward.

CREATE OR REPLACE procedure c2_getClientIP is
l_ip varchar2(200);
l_ip := owa_util.get_cgi_env('REMOTE_ADDR');

grant execute on c2_getClientIP to public;

Then you put a little javascript in the page to call that procedure.

<script language="Javascript">
function xmlhttpPost(strURL) {
var xmlHttpReq = false;
var self = this;
// Mozilla/Safari
if (window.XMLHttpRequest) {
self.xmlHttpReq = new XMLHttpRequest();
// IE
else if (window.ActiveXObject) {
self.xmlHttpReq = new ActiveXObject("Microsoft.XMLHTTP");
}'GET', strURL, true);
self.xmlHttpReq.setRequestHeader('Content-Type', 'application/x-www-form-urlencoded');
self.xmlHttpReq.onreadystatechange = function() {
if (self.xmlHttpReq.readyState == 4) {

function getquerystring() {
var form = document.forms['f1'];
var word = form.word.value;
qstr = 'w=' + escape(word); // NOTE: no '?' before querystring
return qstr;

function updatepage(str){
document.getElementById("result").innerHTML = str;

<form name="f1">
word: <input name="word" type="text"> <input value="Go" type="button" onclick='JavaScript:xmlhttpPost("/pls/portal/portal.c2_getClientIP")'>
<div id="result"></div>

You can put the javascript in a template or as a text item. You can modify this however you want to either just show the IP, put in a variable, etc.


You may run into this error. You want to generate an xsd based on on object type. You create the object:


Then you want to get the associated xsd:

SQL> Set pagesize 9999
SQL> Set linesize 132
SQL> Set long 9999
2 from dual
3 /

Instead of getting the xsd, you get the following:

ORA-21500: internal error code, arguments: [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s]

The problem is not at the database, but with sql*plus. You are probably using a 9i sqlplus client. Try a 10g sqlplus and it should work fine.

As always, if this helps, drop me a comment. It would be great to know that this actually helped someone.

Thursday, January 11, 2007

RepCA on HPUX 64-Bit

You may run into the following errors when running RepCA on HPUX 64-Bit (Itanium):

in the repca.log file

[CustomExternalProcessAction] Command line: /oracle/product/10.1.2.mrca/jdk/bin/java -cp /oracle/product/10.1.2.mrca/assistants/opca/jlib/opca.jar:/oracle/product/10.1.2.mrca/portal/jlib/ptlshare.jar:/oracle/product/10.1.2.mrca/jdbc/lib/classes12.jar:/oracle/product/10.1.2.mrca/jdbc/lib/nls_charset12.jar oracle.webdb.config.PortalConfigAssistant
[CustomExternalProcessAction] Process exited abnormally
[CustomExternalProcessAction] Action failed due to: oracle.ias.repca.ifs.utils.action.ActionFailedException: Some of the operations have failed during this OracleAS RepCA session. Please see the log file at /oracle/product/10.1.2.mrca/assistants/repca/log/repca_2007-01-11_01-38-25_PM.log for further details.
ERROR - Some of the operations have failed during this OracleAS RepCA session. Please see the log file at /oracle/product/10.1.2.mrca/assistants/repca/log/repca_2007-01-11_01-38-25_PM.log for further details.
oracle.ias.repca.ifs.utils.action.ActionFailedException: Some of the operations have failed during this OracleAS RepCA session. Please see the log file at /oracle/product/10.1.2.mrca/assistants/repca/log/repca_2007-01-11_01-38-25_PM.log for further details.
at oracle.ias.repca.actions.CustomExternalProcessAction.perform(
at oracle.ias.repca.ifs.utils.action.ActionQueue.performActions(

in the sso.log file

...end of SSO install

INSTALL_ACTION:createSSOSchema() : loadjava -resolve -verbose -user orasso/orasso@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)( /oracle/product/10.1.2.mrca/portal/admin/plsql/sso/SSOExtDB.class

arguments: '-resolve' '-verbose' '-user' 'orasso/orasso@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(' '/oracle/product/10.1.2.mrca/portal/admin/plsql/sso/SSOExtDB.class'

SQL Error while connecting with oci8 driver to (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)( Closed Connection

exiting : could not open connection

INSTALL_ACTION:createSSOSchema() : loadjava -resolve -verbose -user orasso/orasso@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)( /oracle/product/10.1.2.mrca/portal/admin/plsql/wwc/SSOHash.class

arguments: '-resolve' '-verbose' '-user' 'orasso/orasso@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(' '/oracle/product/10.1.2.mrca/portal/admin/plsql/wwc/SSOHash.class'

SQL Error while connecting with oci8 driver to (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)( Closed Connection

exiting : could not open connection

INSTALL_ACTION : Set the component loading flag for SSO

ERROR: Setting the component loading flag in the DBMS registry raised exception.

It could be that the loadjava of the repca doesn't work well in this configuration. There are probably at least two issues: first, the symbolic link $REPCA_HOME/JRE does not point to the correct location. It needs to point to the full JRE location (java/1.3.1). Second, the loadjava itself is an issue. I tried to solve the problem in a bunch of ways, but found the easiest thing to do is just run the repca from a Windows or Linux box. Much faster than troubleshooting the install on HPUX.