Scripts to up/downgrade entire database
to all NATIVE or to all INTERPRETED PL/SQL compilation mode

1. Overview

These scripts are provided as the fix for Bug 2188517. They may be used only in an Oracle9i Database environment. The patch consists of 5 (five) files...
  • README_2188517.htm - this page.
  • dbmsupgnv.sql - to upgrade to all NATIVE compilation mode
  • dbmsupgin.sql - to downgrade to all INTERPRETED compilation mode
  • dbmsncdb.sql - the package header and...
  • dbmsncdb_body.sql - the package body
    for some helper procedures. These are compiled and used implicitly by dbmsupgnv.sql and dbmsupgin.sql .
There is more to understand to set up for NATIVE compilation than for INTERPRETED. Thus the use of dbmsupgnv.sql is described first.

2. Installation

Unzip Fix_2188517.zip into <Oracle Home> /rdbms/admin . (No files with the above names exist yet on that directory.)

3. Preparing to upgrade to all NATIVE

3.1. Initial Sanity Check for NATIVE environment

First ensure that these initialization parameters are correctly set to specify NATIVE compilation...

  • plsql_compiler_flags=NATIVE
  • plsql_native_library_dir= <some_dir>
  • plsql_native_library_subdir_count=1000
  • plsql_native_make_file_name= <some_dir>/spnc_makefile.mk
  • plsql_native_make_utility=/usr/local/bin/make

Verify this by querying v$parameter .

Oracle recommends that the parameters plsql_native_c_compiler and plsql_native_linker NOT be set. Rather, the compiler and linker utilities should be designated in slpnc_makefie.mk . Ensure that the three utilities, the compiler, linker and make utility really do exist at the specified locations. A necessary, but not sufficient, test for the health of the C environment is to compile and run Hello.c ...

                                     
#include <stdio.h>
  int main(int argc, char **argv) 

  { 
    printf("hello, world\n"); 
    return 0; 
  }
                                  
...with a command (copied from slpnc_makefie.mk ) like...
                                     
/opt/SunProd/SUNWspro6.1/bin/cc Hello.c
                                  
Note: These utilities should be owned either by the owner of the Oracle installation or by root (or its equivalent on non UNIX systems). Failure to adher to this practice exposes a security risk.

To match the value 1000 for plsql_native_library_subdir_count , 1000 subdirectories must be created on plsql_native_library_dir . These must be named eactly d0 , d1 ,... d999 . 1000 is an appropriate choice for the number of shared object files that correspond to all the pl/sql units in a production database.

Now compile a trivial procedure like this...

                                     
procedure Hello is
  begin Dbms_Output.Put_Line ( 'Hello NATIVE' ); end Hello;
                                  
...and confirm success by checking its status in user_objects . Check also that it has the intended compilation mode, thus...
                                     
select param_value from user_stored_settings
  where object_name = 'HELLO' and object_type = 'PROCEDURE'

  and param_name = 'plsql_compiler_flags';
                                  
Check finally that the expected shared object file is produced. On UNIX this would be...
                                     
cd plsql_native_library_dir
find . -name "*.so" -print
                                  
...which should give something like this...
                                     
./d532/HELLO__BLLEWELL__0.so
                                  

If you can't get this to work, call Oracle Support. There's no point in proceeding until it's fixed.

3.2. Database health check

Try to get this query...

                                     
select object_type, object_name, owner
  from dba_objects
  where status <>'VALID'
  order by object_type, object_name;
                                  

...to return no rows. If this isn't practical, because development users have program units under development, save the results from the query for comparison with the state after the upgrade.

4. Running the upgrade to NATIVE

4.1. Ensure no other database activity

It is critical that no session other than the one running the upgrade do anything other than the simplest queries. This requires that all people who know the operating system passwords for the Oracle and root operating system users and for the Oracle DBA passwords cooperate on the project. Given this, the simplest approach is to shutdown the database and to bring it up in restricted mode thus...

                                     
osprompt> sqlplus '/as sysdba'
SQL> shutdown [immediate]
SQL> startup nomount
SQL> alter system enable restricted session;

SQL> alter database mount;
SQL> alter database open;
                                  

4.2. Record object validity

                                     
create table before_upgrade as
  select object_type, object_name, owner, status
  from dba_objects where 1=2;

create table after_upgrade as
  select object_type, object_name, owner, status
  from dba_objects where 1=2;

insert into before_upgrade (
  select object_type, object_name, owner, status
  from dba_objects );
                                  

4.3. Make a full backup

If for some reason (running out of available downtime, running out of disk space, power failure...) the upgrade is aborted, then the datbase is left in an uncomfortable state (a potentially large numer of pl/sql units are marked as invalid). Of course, this will sort itself out over time as units are automatically recompiled on demand, but the performance impact of this might not be acceptable. You could start the upgrade again from scratch, but in case there's insufficient time to do this you should ensure you have the means to get back to your starting state from backup.

Now you're ready to upgrade.

4.4. Run the upgrade

                                     
osprompt> cd $ORACLE_HOME/rdbms/admin
osprompt> sqlplus '/as sysdba'
SQL> @dbmsupgnv.sql;
                                  
You might see some compiler warnings on the screen (ie written to standard error). As long as all previously valid pl/sql units are valid after upgrade, these warnings can be safely ignored.

4.5. Record object validity again and check for regression

Inspect the spool file dbmsupgnv.lst . Then...
                                     
insert into after_upgrade (
  select object_type, object_name, owner, status
  from dba_objects );

select * from before_upgrade minus select * from after_upgrade;
select * from after_upgrade  minus select * from before_upgrade;
                                  

You should of course see no differences (except that after upgrade, the package sys.dbmsncdb and its body will be present). Typically we find that either all objects that were valid when compiled in INTERPRETED mode are valid when compiled in NATIVE mode, or that - due to a gross configuration error - none are. Compiling the procedure Hello in NATIVE mode should eliminate the possibility of a gross configuration error. Of course, if you run out of space on the device where plsql_native_library_dir is, then you'll see a number of invalid objects.

Confirm that you have a fully NATIVE database with...

                                     
select distinct param_value from dba_stored_settings;
                                  

When you're statisfied, allow normal use again...

                                     
alter system disable restricted session;
                                  

5. Running the upgrade to INTERPRETED

It's relatively unlikely that you'll want to do this, but the script is included for completeness. One example might be that you've exported a whole database where all or most of the pl/sql units are NATIVE and you want now to import it into an environment where you are unable or unwilling to set up the required environment for NATIVE. After import, all the NATIVE pl/sql units will have status INVALID.

As above, you should enable restricted session and record the starting validity state. Then run dbmsupgin ...

                                     
osprompt> cd $ORACLE_HOME/rdbms/admin
osprompt> sqlplus '/as sysdba'
SQL> @dbmsupgin.sql;
                                  

...and do the sanity checks as above.