Getting environment variables on the Oracle database server
Say you have a connection to a remote Oracle Database server and want to get the ORACLE_HOME setting. Or any other environment variable for that matter. As far as I can see, Oracle doesn't provide any direct, supported way to do this.
In 10g however, there's an interesting procedure DBMS_SYSTEM.GET_ENV available which does the job:
set autoprint on
var ORACLE_HOME varchar2(255)
exec dbms_system.get_env('ORACLE_HOME',:ORACLE_HOME)
PL/SQL procedure successfully completed.
ORACLE_HOME
-----------------------------------------
D:\oracle\product\10.2.0\db_1
DBMS_SYSTEM is an undocumented/unsupported package. It mainly seems to be an internal utility function for debugging and event monitoring. The package itself is obfusticated, but we can discover a little about it from the data dictionary. The USER_PROCEDURES view lists the individual procedures available in the package:
select PROCEDURE_NAME from USER_PROCEDURES where OBJECT_NAME = 'DBMS_SYSTEM';
PROCEDURE_NAME
------------------------------
DIST_TXN_SYNC
GET_ENV
KCFRMS
KSDDDT
KSDFLS
KSDIND
KSDWRT
READ_EV
SET_BOOL_PARAM_IN_SESSION
SET_EV
SET_INT_PARAM_IN_SESSION
SET_SQL_TRACE_IN_SESSION
WAIT_FOR_EVENT
And USER_ARGUMENTS can tell us about the parameters. For example:
select OBJECT_NAME,ARGUMENT_NAME,POSITION,DATA_TYPE,IN_OUT
from USER_ARGUMENTS
where PACKAGE_NAME='DBMS_SYSTEM' and OBJECT_NAME='GET_ENV'
order by POSITION;
OBJECT_NAME ARGUMENT_NAME POSITION DATA_TYPE IN_OUT
------------- -------------- ---------- --------- ------
GET_ENV VAR 1 VARCHAR2 IN
GET_ENV VAL 2 VARCHAR2 OUT
Given an environment variable name (VAR), GET_ENV returns its value (VAL). These values are coming from the system environment that belongs to the Oracle server process. If you have a dedicated server config, the environment is inherited from the tnslsnr process that spawned the server process. If shared server, then the environment is inherited from whatever process (PMON? PSP0?) that started the shared server process.
So an interesting poke around in some Oracle internals, but there are lots of reasons why you shouldn't use this trick in any production situation!
- It is undocumented and unsuppported. The "get_env" method seems to have appeared in 10g, but there's also no guarantee it will be present in any future versions.
- There are better solutions. SQL client code shouldn't directly depend on server environment variables.
- Remember it is instance specific, and may be misleading in a RAC environment.
read more and comment..
Synchronising two directory trees - update
I've just released an update to tree-sync-2.2.pl on CPAN. This fixes a few bugs with special character handling in filenames.
I've currently three main uses for the script:
- On my Windows XP laptop, I use it to backup my working files to an external drive. For this I use the "fwdonly" mode so that the external drive copy is a perfect copy of what I have on the laptop.
- Also on my laptop, I have a collection of files that I sync with the external drive in "full" sync mode (default). This means changes will be exchanged bidirectionally. This is useful because sometimes I will drop files into this area with the external drive connected to another machine.
- Thirdly, I use it on my server to sync a collection of files from cvs into a web-visible area.
PS: As of Oct-2008, the tree-sync project is now on github. Use this if you want to contribute to development. Of course, releases will still be distributed for use on CPAN.
read more and comment..
Find and tail the Oracle alert log
"Where's the alert log?" .. usually the first thing you want to know when looking at a new database.
Oracle's Grid Control solves this problem very well with it's web interface. But not always available.
Or in my case recently, where I had 6 databases setup on a single machine for various testing scenarios, I was getting tired of cd'ing all over the place, forgetting paths, or ending up with too many windows open for my own good. Getting well sick of this, I did a quick hunt for scripts to help but surprisingly didn't find much. So diving in, I created oraAlertLog.sh and now I'm happy;)
This script is for running on the database server. The Oracle environment must be set, and - at least for the first call - the database must be available so that the "background_dump_dest" parameter can be obtained. The script will cache the alert log location so that it will still work if the database happens to be down.
After getting this running I thought "duh!", should have been in perl so it would be possible to run on any platform supported by Oracle. Here's a version in Perl: oraAlertLog.pl. It requires Perl with DBI and DBD::Oracle .. the Perl distribution included with Oracle Database is fine for this (you just need to make sure the environment is properly configured).
read more and comment..
Request header rewrites with Java servlet filters
A collegue and I have been looking at a setup with completely separate Oracle Portal (with SSO) and Oracle Collabsuite installations, and we wanted a simple way to have users automatically logged into Collabsuite after logging into Portal. If you are not familiar with Collabsuite, just think "J2EE" application.
Normally you would deploy a consolidated infrastructure, which makes this a no-brainer, but for various reasons we wanted to keep these two environments quite separate.
The details of how we did this are not really pertinent, but the bottom line is that we had everything sorted with one exception: the LDAP realm on Portal did not match Collabsuite. Everything was nicely working except the Collabsuite web applications keeled over, because the "osso-user-dn" request header set by the Portal SSO did not match Collabsuite.
If only we could hack/rewrite the osso-user-dn to fixup the realm part!
Now with Apache 2.0, this is probably quite easy by using the RequestHeaders directive in httpd.conf. That didn't exist in Apache 1.3, which unfortunately is what we are using.
This lead me to investigate what could be done at the J2EE level, and I discovered for the first time the servlet filter features in the Servlet API 2.3.
There are a few good tutorials floating around the web, such as Jason Hunter's JavaWorld article, but nothing I've found yet specifically demonstrates header rewrite.
Its pretty simple though. I've put together a demo RewriteRequestHeaderFilter with sources (download: RewriteRequestHeaderFilter-1.0-src.zip). It contains a complete demo site, but is also packaged and ready to insert into any arbitrary web application (just deploy the jar and fiddle the site's web.xml).
So, if you ever find yourself wanting to fiddle request headers in the J2EE environment and don't have "external" options, the RewriteRequestHeaderFilter could be just the ticket.
Postscript 2008-06-02: I've moved this to its own sourceforge project now.
read more and comment..