Oracle9i

PL/SQL Gateway

In Appendix A, "Oracle9i Components," in Oracle9i Installation Guide Release 2 (9.2.0.1.0) for UNIX Systems, PL/SQL Gateway is listed as a supported product. It has been desupported for this release.

SYS and SYSTEM Password Change Requirement

If you use Database Configuration Assistant to create a database, be aware that you will be required to 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.

Oracle9i NCHAR Datatypes

In Oracle9i release 2 (9.2.0.1.0), the SQL NCHAR datatypes are limited to the Unicode character set encoding (UTF8 and AL16UTF16). Alternative character sets such as the fixed-width Asian character set JA16SJISFIXED in Oracle8i are no longer supported.

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.

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

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.

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

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.

Upgrade from Enterprise Edition Oracle9i or Oracle8i

If you have 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 Oracle 8i (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 1 (9.0.1) or Oracle8i release 3 (8.1.7) to Oracle9i Enterprise Edition release 2 (9.2.0.x.x).

Restrictions for Installing Real Application Clusters

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.

Enabling Access to the Real Time Clock

To use Oracle Real Application Clusters, you must have Real Time Clock enabled on your Tru64 Cluster System. To enable access to the real time clock, complete the following procedure for each node on the cluster:

1. Log in as root.

2. Enter the following commands:

   # mknod /dev/timedev c 15 0
   # chmod +r /dev/timedev
   

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.

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 init.ora file 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 you want to move the SPFILE to a shared raw device, use 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/rvol/oracle_dg/dbname_spfile' frompfile='?/dbs/initdbname.ora';
   SQL> exit;
   
   

   where dbname is the 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/rvol/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
   
   

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.

Placing Datafiles On a Shared Non-OFA Cluster Configuration

If your ORACLE_HOME directory is not on a shared cluster filesystem partition, but you want to place datafiles, controlfiles, redo log files, or other database files on a shared cluster filesystem 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

Real Application Clusters Instance Management

After you have created a cluster database using DBCA, SYSDBA privileges are revoked 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;

Simultaneous Enabling of Real Application Clusters and NUMA Permitted

With this release, Oracle Corporation certifies running Real Application Clusters and Directed Placement (NUMA) simultaneously. For most workloads on Alphaserver GS80, GS160, or GS320, enabling the NUMA option on systems running Oracle9i release 2 (9.2.0.1.0) and Real Application Clusters provides significant performance improvements.

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;
  

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;