Re-linking oracle

Hooray! Something vaguely technical - at last.

My new client is hot on software licence compliance and what seemed a minor issue to me reared it's slightly ugly head this week. When connecting to our 10.2.0.2 database instances using sqlplus, the version banner displays options that we aren't using, for example Data Mining and OLAP.

$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.2.0 - Production on Sun May 6 13:39:46 2007

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

You've probably seen a similar banner on most databases you use. The reality is that we don't have the dictionary objects created for these options so they aren't usable, but that message was still a concern for the managers. Here are the contents of DBA_REGISTRY to show that the options aren't fully installed, but the banner messages indicate that they're still embedded in the binaries.

SQL> column comp_name format a40
SQL> select comp_name, status from dba_registry;

COMP_NAME                                STATUS
---------------------------------------- ---------------------------------
Oracle Workspace Manager                 VALID
Oracle Enterprise Manager                VALID
Oracle Database Catalog Views            VALID
Oracle Database Packages and Types       VALID

The solution is pretty straightforward, but it occurred to me that re-linking the Oracle executables is probably a less common activity than it used to be. In fact I know that from personal experience and it didn't seem to be common knowledge in the office. I remember regular re-links during the early part of my career, whether it to be to fix problems with system libraries not being available at installation time, or more creative uses such as linking the exp and imp binaries so that they used a single task architecture for improved performance.

Anyway, here's what was required to get rid of the banner messages.

First I'll shutdown the single instance using that ORACLE_HOME. Note - it's the binaries in the home that I'll be relinking, so I should shut down all instances that use that home, but there's only one in this case.

SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

Now I switch to the rdbms/lib directory, where I'll find the make file I need to relink oracle.

$ cd $ORACLE_HOME/rdbms/lib
$ ls -ltra *.mk
-rw-r-----   1 oracle   oinstall   24664 May 12  2006 ins_rdbms.mk
-rw-r-----   1 oracle   oinstall   97674 May 12  2006 env_rdbms.mk

If you look in the ins_rdbms.mk file, you'll see sections like this for the various options.

olap_on:        $(KNLOPT_LOCAL) $(RDBMSLIB)$(OLAP_ON)
        $(SILENT)if $(ARPRINT) $(LIBKNLOPT) | $(GREP) '^'$(OLAP_OFF) > /dev/null ; then \
                $(ECHODO) $(ARDELETE) $(LIBKNLOPT) $(OLAP_OFF) ; \
        fi
        $(ARCREATE) $(LIBKNLOPT) $(RDBMSLIB)$(OLAP_ON) $(RANLIBL)

olap_off: $(KNLOPT_LOCAL) $(RDBMSLIB)$(OLAP_OFF)
        $(SILENT)if $(ARPRINT) $(LIBKNLOPT) | $(GREP) '^'$(OLAP_ON) > /dev/null ; then \
                $(ECHODO) $(ARDELETE) $(LIBKNLOPT) $(OLAP_ON) ; \
        fi
        $(ARCREATE) $(LIBKNLOPT) $(RDBMSLIB)$(OLAP_OFF) $(RANLIBL)

By specifying the relevant flags, plus ioracle, I can relink the oracle executable so that it doesn't include those options.

$ /usr/ccs/bin/make -f ins_rdbms.mk olap_off dm_off ioracle

At this point you'll see some standard ld linker messages being pumped out. One side-effect of relinking executables is that the make file will save the existing version as a new file with O at the end. So, if you've ever wondered what those oracleO and impO files are in your $ORACLE_HOME/bin directory, they're previous versions saved as part of a relink operation. They're also one of the first things I look to delete when I have an ORACLE_HOME that's in danger of filling a filesystem.

$ ls -ltra $ORACLE_HOME/bin/oracle*
-rwxr-xr-x   1 oracle   oinstall 106057264 Oct  7  2006 /oracle/1020/bin/oracleO
-rwsr-s--x   1 oracle   oinstall 95826132 May  6 13:51 /oracle/1020/bin/oracle

Now I just need to restart the instances and perform a test connection to check that the messages have disappeared.

$ sqlplus testuser/testuser

SQL*Plus: Release 10.2.0.2.0 - Production on Sun May 6 13:52:42 2007

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning option

I read an interesting post on Dominic Delmolino's blog recently about the value of browsing through the SQL scripts in $ORACLE_HOME/rdbms/admin to learn more about how Oracle works - particularly sql.bsq - and I'd recommend a glance at some of the make files, for the same reason. But, please, if you're going to start relinking executables, think carefully about it first, consult Metalink and if you screw up your server, don't come running to me!