Oracle9i

2.1.1 Post-installation

The following are documentation errors in the Post-installation section.

2.1.2 PL/SQL Gateway

Appendix A, "Oracle9i Components," in Oracle9i Installation Guide Release 2 (9.2.0.1.0) for UNIX Systems, lists PL/SQL Gateway as a supported product. This release does not support it.

2.1.3 Oracle Messaging Gateway

The Oracle Messaging Gateway section of Chapter 4, Post-Installation, pages 4-24 to 4-26 in Oracle9i Installation Guide Release 2 (9.2.0.1.0) for UNIX Systems, has examples that are incorrect.

In this section, make the following corrections:

• Remove all mentions of $ORACLE_HOME/mgw/lib and $ORACLE_HOME/mgw/lib32 in the examples.Replace all references to LD_LIBRARY_PATH_32 with LD_LIBRARY_PATH.Substitute all references to $ORACLE_HOME in the examples with the actual directory path.

2.1.4 Solaris OS Requirement

In the "Operating System Patches" table on pp. 2-4 and 2-5 in Oracle9i Installation Guide Release 2 (9.2.0.1.0) for UNIX Systems, the required Solaris8 (2.8) patch update for 64-bit operating systems is listed as "Update 5." The text should read "Update 5 or later."

Update 5 is a Solaris OS maintenance update patch. The maintenance updates to the OS in Update 5 are the minimal requirements for Oracle9i release 2 (9.2.0.1.0).

3.5.1 SYS and SYSTEM Password Change Requirement

If you use Database Configuration Assistant to create a database, you must change the SYS and SYSTEM passwords at the end of the configuration process. This is a new security procedure designed to protect access to your data.

4.2.1 Oracle9i NCHAR Datatypes

Oracle9i release 2 (9.2.0.1.0) limits the SQL NCHAR datatypes to the Unicode character set encoding (UTF8 and AL16UTF16). It no longer supports alternative character sets such as the fixed-width Asian character set JA16SJISFIXED in Oracle8i.

To migrate existing NCHAR, NVARCHAR, and NCLOB columns, export and import NCHAR columns, complete the following steps:

1. Export all SQL NCHAR columns from Oracle8i.

2. Drop the SQL NCHAR columns.

3. Migrate the database to Oracle9i.

4. Import the SQL NCHAR columns in to Oracle9i.

4.2.2 AL24UTFFSS Character Set

Oracle9i release 2 (9.2.0.1.0) does not support the Unicode character set AL24UTFFSS introduced in Oracle7. This character set is based on the Unicode standard 1.1, which is now obsolete.

Oracle9i release 2 (9.2.0.1.0) supports the Unicode database character sets AL32UTF8 and UTF8. These database character sets include the Unicode enhancements based on the Unicode standard 3.0.

To migrate the existing AL24UTFFSS database, upgrade your database character set to UTF8 before upgrading to Oracle9i. Oracle Corporation recommends that you use the Character Set Scanner for data analysis before attempting to migrate your existing database character set.

4.2.3 Character Set Scanner

Set the LD_LIBRARY_PATH variable to include the $ORACLE_HOME/lib directory before running the Character Set Scanner (csscan) from the $ORACLE_HOME directory. If you do not correctly set the LD_LIBRARY_PATH variable, then the csscan utility will fail.

4.3.1 Starting Up OID Server

By default, the OID server is started on port 389. If this port is unavailable, then OID server is started on a different port, which is logged in the following file:

$ORACLE_HOME/ldap/install/oidca.out

4.3.2 Custom Installation and Global Database Name

When performing a custom Oracle Internet Directory installation, do not change the global database name or the Oracle SID.

4.3.3 Upgrade from Enterprise Edition Oracle9i or Oracle8i

If you installed in the same ORACLE_HOME either Oracle Internet Directory release 3.0.1.x and the complete release of Oracle9i (9.0.1) Enterprise Edition, or Oracle Internet Directory 2.1.1.x and the complete release of Oracle8i (8.1.7) Enterprise Edition, then you must first upgrade Oracle Internet Directory to the release 9.2.0.x.x version, and then upgrade as a separate step either Oracle9i Enterprise Edition release 2 (9.2.0.1.0) or Oracle8i release 3 (8.1.7) to Oracle9i Enterprise Edition release 2 (9.2.0.x.x).

4.4.1 Restrictions for Installing Real Application Clusters

The following restrictions apply to this release:

• The Cluster Manager implementation might not be able to handle 32-bit and 64-bit clients concurrently. This will prevent 32-bit and 64-bit Oracle Real Application Clusters executables from being used at the same time within the same cluster domain.

  If a database is not set up with the Oracle Real Application Clusters option, then this restriction does not apply to the Oracle executables.

• If you are installing Oracle9i release 2 (9.2.0.1.0) Real Applications Clusters on a cluster that already contains an ORACLE_HOME for a previous release of Real Application Clusters, then you must run the Oracle Universal Installer from the cluster node with the oraInventory installation registry. Doing this ensures that product installation inventories are synchronized on the nodes with information about existing ORACLE_HOME directories.

4.4.2 Real Application Clusters Pre-Installation Tasks

The following pre-installation instruction supplements the Solaris pre-installation procedure described under "Oracle Real Application Clusters" in Chapter 2, Pre-Installation, Oracle9i Installation Guide Release 2 (9.2.0.1.0) for UNIX Systems.

Install the Oracle 9.2.0.1.0 UDLM patch for Sun clusters, which is located on the first Oracle9i release 2 (9.2.0.1.0) CD Pack. You must install the on all nodes of the cluster on which you plan to run an Oracle9i release 2 (9.2.0.1.0) cluster database instance, even if you previously installed the Oracle9i release 1 (9.0.1) UDLM patch on these nodes.

For instructions on installing the UDLM patch, refer to the README.udlm file, which is in the /cdrom_path/racpatch directory.

4.4.3 Real Application Clusters Custom Installation Requirement

If you plan to create an Oracle Enterprise Manager repository in an existing database, and you plan to use the DRSYS tablespace for the repository, then ensure that the DRSYS tablespace raw device data file has an additional 50 MB of free space. This is in addition to the 250 MB size documented for this raw device.

4.4.4 Real Application Clusters and Database Upgrade Assistant

If you use Database Upgrade Assistant to upgrade an earlier Oracle database version (the "source" database) to Oracle9i release 2 (9.2.0.1.0) (the "target" database), then the upgraded database will always use the server parameter file SPFILE by default to store initialization file (initsid.ora) parameters. If the source database also uses SPFILE (either a cluster filesystem file or a shared raw device), then the upgraded target database also uses the same SPFILE.

If the source database does not use an SPFILE, then the target database uses a default server parameter file, spfile.ora, which is located in the $ORACLE_HOME/dbs/ directory.

If your platform does not support a cluster file system, then you must move the SPFILE to a shared raw device, using the following procedure:

1. Create an SPFILE with the following commands:

   $ sqlplus "/ as sysdba"
   SQL> create pfile='?/dbs/initdbname.ora' from spfile='?/dbs/spfile.ora';
   SQL> create spfile='/dev/vx/rdsk/oracle_dg/dbname_spfile' frompfile='?/dbs/initdbname.ora';
   SQL> exit;
   
   

   where dbname is the system identifier (sid), or name of your cluster database.

2. Go to the $ORACLE_HOME/dbs directory using the following command:

   $ cd $ORACLE_HOME/dbs
   
   

3. Create an $ORACLE_HOME/dbs/initsid.ora file, where sid is the system identifier of the instance on the node. The initsid.ora file must contain the following line:

   SPFILE='/dev/vx/rdsk/oracle_dg/dbname_spfile'
   
   

4. Copy the initsid.ora file to the remote nodes on which the cluster database has an instance with the following commands:

   $ rcp initsid.ora nodex:$ORACLE_HOME/dbs/initsidx.ora
   
   

   where sidx is the system identifier of the instance on node x. Repeat the preceding rcp command for each member node of the cluster database.

5. Restart the cluster database with the following command syntax:

   $ srvctl stop database -d dbname
   $ srvctl start database -d dbname
   
   

4.4.5 Real Application Clusters and Database Configuration Assistant

The following section provides information on using Database Configuration Assistant (DBCA) to create a Real Application Clusters database.

4.4.6 Placing Datafiles On a Shared Non-OFA Cluster Configuration

If your ORACLE_HOME directory is not on a shared cluster file system partition, but you want to place datafiles, controlfiles, redo log files, or other database files on a shared cluster files system partition, then invoke DBCA using the following syntax to create the cluster database:

$ dbca -datafileDestination pathname

where pathname is the location where you want files to be placed.

For example, to place datafiles in the path, /ora/oradata, give the following command:

$ dbca -datafileDestination /ora/oradata

4.4.7 Real Application Clusters Instance Management

After you create a cluster database using DBCA, the process revokes SYSDBA privileges for all users. As SYSDBA, you must grant SYSDBA privileges explicitly to the database user account that you plan to use for adding or deleting an instance to or from the cluster database.

For example, to grant SYSDBA privileges to the administrative user SYS, issue the following commands:

$ sqlplus "/ as sysdba"
SQL> grant sysdba to sys;
SQL> exit;

4.5.1 Determining Whether to Use PL/SQL Native Compilation

You must first determine if you will increase performance by enabling PL/SQL native compilation.

For each program unit, interpreted PL/SQL is compiled into machine-readable m-code, which is stored in the database, and interpreted at runtime.

For PL/SQL statements using PL/SQL native compilation, Oracle9i takes PL/SQL statements and generates corresponding C-code. Oracle9i then uses the makefile $ORACLE_HOME/plsql/spnc_makefile.mk, and the supported operating system C compiler, linker, and make utilities, to compile and link the resulting C-code into shared libraries, which are stored externally from the database. These shared library files are then loaded and run when the corresponding PL/SQL statement is invoked at runtime. In accordance with OFA recommendations, the shared libraries should be stored near the data files.

C-code runs faster than PL/SQL, but it takes longer to compile than m-code.

PL/SQL native compilation provides the greatest performance gains for computation-intensive procedural operations. Examples of such operations are data warehouse applications, and applications with extensive server-side transformations of data for display. In such cases, expect speed increases of up to 30%.

For PL/SQL program units that merely invoke SQL statements, and do not implement significant procedural logic, the performance benefits of native compilation will be small. However, natively compiled PL/SQL will always be at least as fast as the corresponding interpreted code.

When you determine that you will significantly increase performance in database operations using PL/SQL native compilation, Oracle Corporation recommends that you compile the whole database as NATIVE.

4.5.2 Required Parameters for PL/SQL Native Compilation

In all circumstances, whether you intend to compile a database as NATIVE, or you intend to compile individual PL/SQL units at the session level, you must set all required parameters.

SQL> alter dynamic set plsql_compiler_flags='FLAG_A, FLAG_B'

4.5.3 Setting up Databases for PL/SQL Native Compilation

Use the procedures in this section to set up databases for PL/SQL native compilation.

select count (*) from DBA_OBJECTS 
  where object_type in ( 
    select distinct object_type from dba_stored_settings 
      where object_type not like '%BODY%' );

4.5.4 Real Application Clusters and PL/SQL Native Compilation

To use PLSQL Native Compilation in a Real Application Clusters environment, you need to set the PLSQL_NATIVE_LIBRARY_DIR initialization parameter to a directory on a genuine shared file system. You cannot use PLSQL Native Compilation in a Real Application Clusters environment on Sun Solaris (64-bit).

4.5.5 Dependencies, Invalidation and Revalidation

This release handles dependencies between database objects in the same manner as in previous Oracle RDBMS versions. If an object on which a natively compiled PL/SQL program unit depends changes, then the PL/SQL module is invalidated. The next time the same program unit is executed, the RDBMS attempts to revalidate the module. When a module is recompiled as part of revalidation, it is compiled using its stored setting (the setting in place the last time the module compiled and appeared in the USER/ALL/DBA_STORED_SETTINGS data dictionary views.).

The stored settings are only used when recompiling as part of revalidation. If a PL/SQL module is explicitly compiled through the SQL commands "create or replace" or "alter...compile", then the current session setting is used.

Natively compiled PL/SQL program units are dependent on their implementation shared libraries. The RDBMS is unable to track deletions or location changes of these library dependencies, as the shared libraries are on the OS file system, external from the database.

If if you remove or delete a shared library, then you will see an ORA-06549 error. The program unit is not marked invalid, as the removal of a library is undetectable to the Oracle RDBMS until the module is executed. To recreate the missing library, you must explicitly recompile it, or recreate it from the source.

For example, if the shared library in which the test program "Hello" is deleted or moved, use the following procedure to correct the problem:

$ sqlplus scott/tiger

SQL> alter session set plsql_compiler_flags='NATIVE'

Session altered

SQL> alter procedure Hello compile;

Procedure altered.

SQL> exit

$ ls /usr/app/oracle/product 9.2.0.1.0/plsql_libs

HELLO__SCOTT__0.so

If you delete a PL/SQL program unit on the Oracle RDBMS, the shared libraries on the OS file system remain; you must delete these files manually when they are no longer necessary.

6.2.1 Segments and Compression Settings

To find out which database segments are using compression, log in to the database as the user, SYS, and create the view all_segs with the following create or replace view statement:

SQL> create or replace view all_segs
     (owner, segment_name,
       partition_name, spare1
as
select u.name, o.name, o.subname, s.spare1
from sys.user$ u, sys.obj$ o, sys.ts$ ts, sys.sys_objects so,
     sys.seg$ s, sys.file$ f
where s.file# = so.header_file
   and s.block# = so.header_block
   and s.ts# = so.ts_number
   and s.ts# = ts.ts#
   and s.ts# = so.object_id
   and o.owner# = u.user#
   and s.type# = so.object_type_id
   and s.ts# = f.ts#
   and s.file# = f.relfile#
union all
select u.name, un.name, NULLL, NULL
from sys.user$ u, sys.ts$ ts, sys.undo $ un, sys.seg$ s,
     sys.file$ f
where s.file# = un.file#
   and s.block# = un.block
   and s.ts# = un.ts#
   and s.ts# = ts.ts#
   and s.user# = u.user#
   and s.type# in (1, 10)
   and un.status$ != 1
   and un.ts# = f.ts#
   and un.file# = f.relfile#
union all
select u.name, to_char(f.file#)|| '.' || to_char(s.block#), NULL, NULL
from sys.user$ u, sys.ts$ ts, sys.seg$ s, sys.file$ f
where s.ts# = ts.ts#
   and s.user# = u.user#
   and s.type# not in (1, 5, 6, 8, 10)
   and s.ts# = f.ts#
   and s.file# = f.relfile#
/

After creating this view, you can issue queries against the view to find out whether a segment currently is compressed, as illustrated in the following examples:

• To determine if a segment is currently compressed, apply the following predicate in a query to the column spare1:

  bitand(spare1, 2048) > 0
  
  

  For example, to see if segments currently are compressed, issue a statement similar to the following:

  SQL> select * from all_segs where bitand(spare1,2048) > 0;
  
  

• To determine if a segment contains any compressed blocks, apply the following predicate in a query:

  bitand(spare1, 4096) > 0
  
  

  For example, to see which segments contain any compressed blocks, issue a statement similar to the following:

  SQL> select * from all_segs where bitand(spare1, 4096) > 0;
  

6.2.2 Tablespaces and Compression Settings

When you want to determine compression settings on a table space, log in as SYS, and create the view compression_ts with the following create or replace view statement:

SQL> create or replace view compression_ts (tablespace_name, flags) as select ts.name, ts.flags from
sys.ts$ ts where ts.online$ !=3;

After creating this view, you can issue queries against it to find out the compression state of tablespaces, such as determining if a tablespace is currently set as DEFAULT COMPRESS, or DEFAULT NOCOMPRESS, as illustrated in the following examples:

• To determine if a tablespace is currently set as DEFAULT COMPRESS, use the following predicate:

  bitand(flags, 64) > 0
  
  

  For example, to see which tablespaces are currently DEFAULT COMPRESS, issue a statement similar to the following:

  SQL> select * from compression_ts where bitand(flags, 64) > 0
  
  

• To determine if a tablespace is currently set as DEFAULT NOCOMPRESS, use the following predicate:

  bitand(flags, 64) == 0
  
  

  For example, to see which tablespaces are currently DEFAULT NOCOMPRESS, issue a statement similar to the following:

  select * from compression_ts where bitand(flags, 64) == 0;