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

1. Overview

• 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.

2. Installation

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

/opt/SunProd/SUNWspro6.1/bin/cc Hello.c

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;

select param_value from user_stored_settings
  where object_name = 'HELLO' and object_type = 'PROCEDURE'
  and param_name = 'plsql_compiler_flags';

cd plsql_native_library_dir
find . -name "*.so" -print

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

4.5. Record object validity again and check for regression

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.