Sridhar's oracle page

Sunday, March 18, 2007

Complete checklist for manual upgrades to 10gR2

Complete checklist for manual upgrades to 10gR2
PREREQUISITES
=============

+ Install Oracle 10g Release 2 in a new Oracle Home.
+ Install the latest available patchset from Metalink.
+ Install the latest available Critical Patch Update.
Note 290738.1 Oracle Critical Patch Update Program General FAQ
+ Either take a cold or hot backup for your database.
+ Make sure to take a backup of Oracle Home and Central Inventory.
Central inventory can be located by the contents of oraInst.loc files.
"oraInst.loc" is available in the following locations on various platforms
/var/opt/oracle/oraInst.loc -- Solaris
/etc/oraInst.loc -- other operating systems
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\inst_loc -- On windows Platform.
+ Verify kernel parameters are set according to the 10gR2 Installation Guide.
+ Verify that all O/S packages and patches are installed as per the Installation Guide.

COMPATIBILITY MATRIX
====================

+ Minimum Version of the database that can be directly upgraded to Oracle 10g Release 2
8.1.7.4 -> 10.2.X.X.X
9.0.1.4 or 9.0.1.5 -> 10.2.X.X.X
9.2.0.4 or higher -> 10.2.X.X.X
10.1.0.2 or higher -> 10.2.X.X.X

+ The following database version will require an indirect upgrade path.
7.3.3 (or lower) -> 7.3.4 -> 8.1.7 -> 8.1.7.4 -> 10.2.X.X.X
7.3.4 -> 8.1.7 -> 8.1.7.4 -> 10.2.X.X.X
8.0.n -> 8.1.7 -> 8.1.7.4 -> 10.2.X.X.X
8.1.n -> 8.1.7 -> 8.1.7.4 -> 10.2.X.X.X

STEPS FOR UPGRADING THE DATABASE TO 10G RELEASE 2
=================================================

Preparing to Upgrade
--------------------

In this section all the steps need to be performed to the previous version of Oracle.
Please note that the database must be running in normal mode in the old release.

Step 1:
~~~~~~~
Log in to the system as the owner of the new 10gR2 ORACLE_HOME and copy the following
files from the 10gR2 ORACLE_HOME/rdbms/admin directory to a directory outside of the Oracle home,
such as the /tmp directory on your system:

ORACLE_HOME/rdbms/admin/utlu102i.sql
ORACLE_HOME/rdbms/admin/utltzuv2.sql

Make a note of the new location of these files.

Step 2:
~~~~~~~
Change to the temporary directory that you copied files to in Step 1.

Start SQL*Plus and connect to the database instance as a user with SYSDBA
privileges. Then run and spool the utlu102i.sql file.

sqlplus '/as sysdba'

SQL> spool Database_Info.log
SQL> @utlu102i.sql
SQL> spool off

Then, check the spool file and examine the output of the upgrade information
tool. The sections which follow, describe the output of the Upgrade
Information Tool (utlu102i.sql).

Database:

This section displays global database information about the current database such
as the database name, release number, and compatibility level. A warning is displayed
if the COMPATIBLE initialization parameter needs to be adjusted before the database is
upgraded.

Logfiles:

This section displays a list of redo log files in the current database whose size is
less than 4 MB. For each log file, the file name, group number, and recommended size
is displayed. New files of at least 4 MB (preferably 10 MB) need to be created in the
current database. Any redo log files less than 4 MB must be dropped before the database
is upgraded.

Tablespaces:

This section displays a list of tablespaces in the current database. For each tablespace,
the tablespace name and minimum required size is displayed. In addition, a message is
displayed if the tablespace is adequate for the upgrade. If the tablespace does not have
enough free space, then space must be added to the tablespace in the current database.
Tablespace adjustments need to be made before the database is upgraded.

Update Parameters:

This section displays a list of initialization parameters in the parameter file of the
current database that must be adjusted before the database is upgraded. The adjustments
need to be made to the parameter file after it is copied to the new Oracle Database 10g
release.

Deprecated Parameters:

This section displays a list of initialization parameters in the parameter file of the
current database that are deprecated in the new Oracle Database 10g release.

Obsolete Parameters:

This section displays a list of initialization parameters in the parameter file of the
current database that are obsolete in the new Oracle Database 10g release. Obsolete
initialization parameters need to be removed from the parameter file before the
database is upgraded.

Components:

This section displays a list of database components in the new Oracle Database 10g release
that will be upgraded or installed when the current database is upgraded.

Miscellaneous Warnings:

This section provides warnings about specific situations that may require attention before
and/or after the upgrade.

SYSAUX Tablespace:

This section displays the minimum required size for the SYSAUX tablespace, which is required
in Oracle Database 10g. The SYSAUX tablespace must be created after the new Oracle Database
10g release is started and BEFORE the upgrade scripts are invoked.

Step 3:
~~~~~~~
Check for the deprecated CONNECT Role

After upgrading to 10gR2, the CONNECT role will only have the CREATE SESSION
privilege; the other privileges granted to the CONNECT role in earlier releases will be
revoked during the upgrade. To identify which users and roles in your database are granted
the CONNECT role, use the following query:

SELECT grantee FROM dba_role_privs
WHERE granted_role = 'CONNECT' and
grantee NOT IN (
'SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP',
'LOGSTDBY_ADMINISTRATOR', 'ORDSYS',
'ORDPLUGINS', 'OEM_MONITOR', 'WKSYS', 'WKPROXY',
'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS',
'WMSYS', 'OLAPDBA', 'OLAPSVR', 'OLAP_USER',
'OLAPSYS', 'EXFSYS', 'SYSMAN', 'MDDATA',
'SI_INFORMTN_SCHEMA', 'XDB', 'ODM');

If users or roles require privileges other than CREATE SESSION, then grant
the specific required privileges prior to upgrading. The upgrade scripts
adjust the privilegesfor the Oracle-supplied users.

In Oracle 9.2.x and 10.1.x CONNECT role includes the following privileges:

SELECT GRANTEE,PRIVILEGE FROM DBA_SYS_PRIVS
WHERE GRANTEE='CONNECT'

GRANTEE PRIVILEGE
------------------------------ ---------------------------
CONNECT CREATE VIEW
CONNECT CREATE TABLE
CONNECT ALTER SESSION
CONNECT CREATE CLUSTER
CONNECT CREATE SESSION
CONNECT CREATE SYNONYM
CONNECT CREATE SEQUENCE
CONNECT CREATE DATABASE LINK

In Oracle 10.2 the CONNECT role only includes CREATE SESSION privilege.

Step 4:
~~~~~~~
Create the script for dblink incase of downgrade of the database.

During the upgrade to 10gR2, any passwords in database links will be encrypted.
To downgrade back to the original release, all of the database links with encrypted passwords
must be dropped prior to the downgrade. Consequently, the database links will not exist in
the downgraded database. If you anticipate a requirement to be able to downgrade back to your
original release, then save the information about affected database links from the SYS.LINK$ table,
so that you can recreate the database links after the downgrade.

Following script can be used to construct the dblink.

SELECT
'create '||DECODE(U.NAME,'PUBLIC','public ')||'database link '||CHR(10)
||DECODE(U.NAME,'PUBLIC',Null, U.NAME||'.')|| L.NAME||chr(10)
||'connect to ' || L.USERID || ' identified by '''
||L.PASSWORD||''' using ''' || L.host || ''''
||chr(10)||';' TEXT
FROM sys.link$ L,
sys.user$ U
WHERE L.OWNER# = U.USER# ;

Step 5:
~~~~~~~
Check for the TIMESTAMP WITH TIMEZONE Datatype. Please this step is only required for the 10gR1
The may affect existing data of TIMESTAMP WITH TIME ZONE datatype.
For example, if users enter TIMESTAMP '2003-02-17 09:00:00 America/Sao_Paulo',
we convert the data to UTC based on the transition rules in the time zone file
and store them on the disk. So '2003-02-17 11:00:00' along with the time zone id
for 'America/Sao_Paulo' is stored because the offset for this particular time is '-02:00'.
Now the transition rules are modified and the offset for this particular
time is changed to '-03:00'. when users retrieve the data, they will get
'2003-02-17 08:00:00 America/Sao_Paulo'. There is one hour difference compared to the
original value.

Change to the temporary directory that you copied files to in Step 1.

Start SQL*Plus and connect to the database instance as a user with SYSDBA
privileges. Then run and spool the utltzuv2.sql file.

$ sqlplus '/as sysdba'

SQL> spool TimeZone_Info.log
SQL> @utltzuv2.sql
SQL> spool off

If the utltzuv2.sql script identifies columns with time zone data affected
by a database upgrade, then there two ways of solving this problem

Solution
--------
create tables with the time zone information in character format
(for example, TO_CHAR(column, 'YYYY-MM-DD HH24.MI.SSXFF TZR'), and recreate
the TIMESTAMP data from these tables after the upgrade.

For example, user scott has a table tztab:
create table tztab(x number primary key, y timestamp with time zone);
insert into tztab values(1, timestamp '');

Before upgrade, you can create a table tztab_back, note column y here is
defined as VARCHAR2 to preserve the original value.

create table tztab_back(x number primary key, y varchar2(256));
insert into tztab_back select x,
to_char(y, 'YYYY-MM-DD HH24.MI.SSXFF TZR') from tztab;

After upgrade, you need update the data in the table tztab using the value in tztab_back.
update tztab t set t.y = (select to_timestamp_tz(t1.y,
'YYYY-MM-DD HH24.MI.SSXFF TZR') from tztab_back t1 where t.x=t1.x);

Step 6:
~~~~~~~
Starting in Oracle 9i the National Characterset (NLS_NCHAR_CHARACTERSET) will be
limited to UTF8 and AL16UTF16.

For more details refer to The National Character Set in Oracle 9i and 10g

Any other NLS_NCHAR_CHARACTERSET will no longer be supported.
When upgrading to 10g the value of NLS_NCHAR_CHARACTERSET is based
on value currently used in the Oracle8 version.

If the NLS_NCHAR_CHARACTERSET is UTF8 then new it will stay UTF8.
In all other cases the NLS_NCHAR_CHARACTERSET is changed to AL16UTF16
and -if used- N-type data (= data in columns using NCHAR, NVARCHAR2 orNCLOB )
may need to be converted.

The change itself is done in step 38 by running the upgrade script.

If you are NOT using N-type columns *for user data* then simply go to next step.
No further action required.

( so if: select distinct OWNER, TABLE_NAME from DBA_TAB_COLUMNS where
DATA_TYPE in ('NCHAR','NVARCHAR2', 'NCLOB') and OWNER not in
('SYS','SYSTEM'); returns no rows, go to next step.)

If you have N-type columns *for user data* then check:

SQL> select * from nls_database_parameters where parameter
='NLS_NCHAR_CHARACTERSET';

If you are using N-type columns AND your National Characterset
is UTF8 or is in the following list:

JA16SJISFIXED , JA16EUCFIXED , JA16DBCSFIXED , ZHT32TRISFIXED
KO16KSC5601FIXED , KO16DBCSFIXED , US16TSTFIXED , ZHS16CGB231280FIXED
ZHS16GBKFIXED , ZHS16DBCSFIXED , ZHT16DBCSFIXED , ZHT16BIG5FIXED
ZHT32EUCFIXED

then also simply go to point next step.
The conversion of the user data itself will then be done in step 37

If you are using N-type columns AND your National Characterset is NOT
UTF8 or NOT in the following list:

JA16SJISFIXED , JA16EUCFIXED , JA16DBCSFIXED , ZHT32TRISFIXED
KO16KSC5601FIXED , KO16DBCSFIXED , US16TSTFIXED , ZHS16CGB231280FIXED
ZHS16GBKFIXED , ZHS16DBCSFIXED , ZHT16DBCSFIXED , ZHT16BIG5FIXED
ZHT32EUCFIXED

(your current NLS_NCHAR_CHARACTERSET is for example US7ASCII, WE8ISO8859P1, CL8MSWIN1251 ...)
then you have to:
* change the tables to use CHAR, VARCHAR2 or CLOB instead the N-type
or
* use export/import the table(s) containing N-type columns
and truncate those tables before migrating to 9i.
The recommended NLS_LANG during export is simply the NLS_CHARACTERSET,
not the NLS_NCHAR_CHARACTERSET

Step 7:
~~~~~~~
When upgrading to Oracle Database 10g, optimizer statistics are collected
for dictionary tables that lack statistics. This statistics collection can
be time consuming for databases with a large number of dictionary tables,
but statistics gathering only occurs for those tables that lack statistics
or are significantly changed during the upgrade.

To decrease the amount of downtime incurred when collecting statistics,
you can collect statistics prior to performing the actual database upgrade.

As of Oracle Database 10g Release 10.1, Oracle recommends that you use
the DBMS_STATS.GATHER_DICTIONARY_STATS procedure to gather these statistics.
You can enter the following:

$ sqlplus '/as sysdba'

SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

In Case of the 9.0.1 or 9.2.0 release, then you should use the
DBMS_STATS.GATHER_SCHEMA_STATS procedure to gather statistics.

Backup the existing statistics as follow

$ sqlplus '/as sysdba'
SQL>spool sdict

SQL>grant analyze any to sys;

SQL>exec dbms_stats.create_stat_table('SYS','dictstattab');

SQL>exec dbms_stats.export_schema_stats('WMSYS','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('MDSYS','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('CTXSYS','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('XDB','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('WKSYS','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('LBACSYS','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('OLAPSYS','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('DMSYS','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('ODM','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('ORDSYS','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('ORDPLUGINS','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('SI_INFORMTN_SCHEMA','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('OUTLN','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('DBSNMP','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('SYSTEM','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('SYS','dictstattab',statown => 'SYS');

SQL>spool off

This data is useful if you want to revert back the statistics

For example, the following PL/SQL subprograms import the statistics for the SYS schema after
deleting the existing statistics:

exec dbms_stats.delete_schema_stats('SYS');
exec dbms_stats.import_schema_stats('SYS','dictstattab');

To gather statistics run this script, connect to the database AS SYSDBA using SQL*Plus.

$ sqlplus '/as sysdba'

SQL>spool gdict

SQL>grant analyze any to sys;

SQL>exec dbms_stats.gather_schema_stats('WMSYS',options=>'GATHER',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
- method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
SQL>exec dbms_stats.gather_schema_stats('MDSYS',options=>'GATHER',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
- method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
SQL>exec dbms_stats.gather_schema_stats('CTXSYS',options=>'GATHER',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
- method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
SQL>exec dbms_stats.gather_schema_stats('XDB',options=>'GATHER',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
- method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
SQL>exec dbms_stats.gather_schema_stats('WKSYS',options=>'GATHER',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
- method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
SQL>exec dbms_stats.gather_schema_stats('LBACSYS',options=>'GATHER',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
- method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
SQL>exec dbms_stats.gather_schema_stats('OLAPSYS',options=>'GATHER',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
- method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
SQL>exec dbms_stats.gather_schema_stats('DMSYS',options=>'GATHER',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
- method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
SQL>exec dbms_stats.gather_schema_stats('ODM',options=>'GATHER',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
- method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
SQL>exec dbms_stats.gather_schema_stats('ORDSYS',options=>'GATHER',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
- method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
SQL>exec dbms_stats.gather_schema_stats('ORDPLUGINS',options=>'GATHER',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
- method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
SQL>exec dbms_stats.gather_schema_stats('SI_INFORMTN_SCHEMA',options=>'GATHER',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
- method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
SQL>exec dbms_stats.gather_schema_stats('OUTLN',options=>'GATHER',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
- method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
SQL>exec dbms_stats.gather_schema_stats('DBSNMP',options=>'GATHER',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
- method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
SQL>exec dbms_stats.gather_schema_stats('SYSTEM',options=>'GATHER',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
- method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
SQL>exec dbms_stats.gather_schema_stats('SYS',options=>'GATHER',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
- method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

SQL>spool off

Step 8:
~~~~~~~
Check for invalid objects invalid objects.

spool invalid_pre.lst
select substr(owner,1,12) owner,
substr(object_name,1,30) object,
substr(object_type,1,30) type, status from
dba_objects where status <>'VALID';
spool off

Run the following script and then requery invalid objects:

This script must be run as a user with SYSDBA privs using SQL*Plus:
$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus '/as sysdba'
SQL> @utlrp.sql

This last query will return a list of all objects that cannot be recompiled
before the upgrade in the file 'invalid_pre.lst'

Step 9:
~~~~~~~~
Check for corruption in the dictionary, use the following commands in sqlplus
connected as sys:

Set verify off
Set space 0
Set line 120
Set heading off
Set feedback off
Set pages 1000
Spool analyze.sql

Select 'Analyze cluster "'||cluster_name||'" validate structure cascade;'
from dba_clusters
where owner='SYS'
union
Select 'Analyze table "'||table_name||'" validate structure cascade;'
from dba_tables
where owner='SYS' and partitioned='NO' and (iot_type='IOT' or iot_type is NULL)
union
Select 'Analyze table "'||table_name||'" validate structure cascade into invalid_rows;'
from dba_tables
where owner='SYS' and partitioned='YES';

spool off

This creates a script called analyze.sql.
Now execute the following steps.

$ sqlplus '/as sysdba'
SQL> @$ORACLE_HOME/rdbms/admin/utlvalid.sql
SQL> @analyze.sql

This script (analyze.sql) should not return any errors.

Step 10:
~~~~~~~~
Ensure that all Snapshot refreshes are successfully completed, and that
replication is stopped.

$ sqlplus '/as sysdba'
SQL> select distinct(trunc(last_refresh)) from dba_snapshot_refresh_times;

Step 11:
~~~~~~~~
Stop the listener for the database:

$ lsnrctl
LSNRCTL> stop

Ensure no files need media recovery:

$ sqlplus '/ as sysdba'
SQL> select * from v$recover_file;

This should return no rows.

Step 12:
~~~~~~~~
Ensure no files are in backup mode:

SQL> select * from v$backup where status!='NOT ACTIVE';

This should return no rows.

Step 13:
~~~~~~~~
Resolve any outstanding unresolved distributed transaction:

SQL> select * from dba_2pc_pending;

If this returns rows you should do the following:

SQL> select local_tran_id from dba_2pc_pending;
SQL> execute dbms_transaction.purge_lost_db_entry('');
SQL> commit;

Step 14:
~~~~~~~~
Disable all batch and cron jobs.

Step 15:
~~~~~~~~
Ensure the users sys and system have 'system' as their default tablespace.

SQL> select username, default_tablespace from dba_users
where username in ('SYS','SYSTEM');

To modify use:

SQL> alter user sys default tablespace SYSTEM;
SQL> alter user system default tablespace SYSTEM;

Step 16:
~~~~~~~~
Optionally ensure the aud$ is in the system tablespace when auditing is enabled.

SQL> select tablespace_name from dba_tables where table_name='AUD$';

Step 17:
~~~~~~~~
Note down where all control files are located.

SQL> select * from v$controlfile;

Step 18:
~~~~~~~~
Shutdown the database

$ sqlplus '/as sysdba'
SQL> shutdown immediate;

Step 19:
~~~~~~~~
PERFORM a Full cold backup!!!!!!!

You can either do this by manually copying the files or
sign on to RMAN:

$rman "target / nocatalog"

And issue the following RMAN commands:

RUN
{
ALLOCATE CHANNEL chan_name TYPE DISK;
BACKUP DATABASE FORMAT 'some_backup_directory%U' TAG before_upgrade;
BACKUP CURRENT CONTROLFILE TO 'save_controlfile_location';
}

Upgrading to the New Oracle Database 10g Release 2
--------------------------------------------------

Step 20:
~~~~~~~~
Update the init.ora file:

- Make a backup of the init.ora file.

- Comment out obsoleted parameters(list in appendix A).

- Change all deprecated parameters(list in appendix B).

- Set the COMPATIBLE initialization parameter to an appropriate value. If you are
upgrading from 8.1.7.4 then set the COMPATIBLE parameter to 9.2.0 until after the
upgrade has been completed successfully. If you are upgrading from 9.2.0 or 10.1.0
then leave the COMPATIBLE parameter set to it's current value until the upgrade
has been completed successfully. This will avoid any unnecessary ORA-942 errors
from being reported in SMON trace files during the upgrade (because the upgrade
is looking for 10.2 objects that have not yet been created)

- If you have set the parameter NLS_LENGTH_SEMANTICS to CHAR, change the value
to BYTE during the upgrade.

- Verify that the parameter DB_DOMAIN is set properly.

- Make sure the PGA_AGGREGATE_TARGET initialization parameter is set to
at least 24 MB.

- Ensure that the SHARED_POOL_SIZE and the LARGE_POOL_SIZE are at least 150Mb.
Please alos the check the "KNOWN ISSUES" section

- Make sure the JAVA_POOL_SIZE initialization parameter is set to at least 150 MB.

- Ensure there is a value for DB_BLOCK_SIZE

- On Windows operating systems, change the BACKGROUND_DUMP_DEST and USER_DUMP_DEST
initialization parameters that point to RDBMS80 or any other environment variable
to point to the following directories instead:

BACKGROUND_DUMP_DEST to ORACLE_BASE\oradata\DB_NAME

and

USER_DUMP_DEST to ORACLE_BASE\oradata\DB_NAME\archive

- Comment out any existing AQ_TM_PROCESSES parameter setting, and enter a new one
that explicitly sets AQ_TM_PROCESSES=0 for the duration of the upgrade

- Make sure all path names in the parameter file are fully specified. You should not
have relative path names in the parameter file.

- If you are using a cluster database, set the parameter CLUSTER_DATABASE=FALSE
during the upgrade.

- If you are upgrading a cluster database, then modify the initdb_name.ora
file in the same way that you modified the parameter file.

Step 21 :
~~~~~~~~~
Check for adequate freespace on archive log destination file systems.

Step 22 :
~~~~~~~~~
Ensure the NLS_LANG variable is set correctly:

$ env | grep $NLS_LANG

Step 23:
~~~~~~~~
If needed copy the SQL*Net files like (listener.ora,tnsnames.ora etc)
to the new location (when no TNS_ADMIN env. Parameter is used)

$ cp $OLD_ORACLE_HOME/network/admin/*.ora /network/admin

Step 24:
~~~~~~~~
If your Operating system is Windows NT, delete your services
With the ORADIM of your old oracle version.

Stop the OracleServiceSID Oracle service of the database you are upgrading,
where SID is the instance name. For example, if your SID is ORCL, then enter
the following at a command prompt:

C:\> NET STOP OracleServiceORCL

For Oracle 8.0 this is:
C:\ORADIM80 -DELETE -SID

For Oracle 8i or higher this is:
C:\ORADIM -DELETE -SID

Also create the new Oracle Database 10gR2 service at a command prompt using the
ORADIM command of the new Oracle Database release:

C:\> ORADIM -NEW -SID SID -INTPWD PASSWORD -MAXUSERS USERS
-STARTMODE AUTO -PFILE ORACLE_HOME\DATABASE\INITSID.ORA

Step 25:
~~~~~~~~
Copy configuration files from the ORACLE_HOME of the database being upgraded
to the new Oracle Database 10g ORACLE_HOME:

If your parameter file resides within the old environment's ORACLE_HOME,
then copy it to the new ORACLE_HOME. By default, Oracle looks for the parameter
file in ORACLE_HOME/dbs on UNIX platforms and in ORACLE_HOME\database on
Windows operating systems. The parameter file can reside anywhere you wish,
but it should not reside in the old environment's ORACLE_HOME after you
upgrade to Oracle Database 10g.

If your parameter file is a text-based initialization parameter file with
either an IFILE (include file) or a SPFILE (server parameter file) entry,
and the file specified in the IFILE or SPFILE entry resides within the old
environment's ORACLE_HOME, then copy the file specified by the IFILE or
SPFILE entry to the new ORACLE_HOME. The file specified in the IFILE or SPFILE
entry contains additional initialization parameters.

If you have a password file that resides within the old environments
ORACLE_HOME, then move or copy the password file to the new Oracle Database
10g ORACLE_HOME.

The name and location of the password file are operating system-specific.
On UNIX platforms, the default password file is ORACLE_HOME/dbs/orapwsid.
On Windows operating systems, the default password file is
ORACLE_HOME\database\pwdsid.ora. In both cases, sid is your Oracle instance ID.

If you are upgrading a cluster database and your initdb_name.ora file resides
within the old environment's ORACLE_HOME, then move or copy the initdb_name.ora
file to the new ORACLE_HOME.

Note:
If you are upgrading a cluster database, then perform this step on all nodes
in which this cluster database has instances configured.

Step 26:
~~~~~~~~
Update the oratab entry, to set the new ORACLE_HOME and disable automatic
startup:

::N

Step 27:
~~~~~~~~
Update the environment variables like ORACLE_HOME and PATH

$. oraenv

Step 28:
~~~~~~~~
Make sure the following environment variables point to the new
Release directories:
- ORACLE_HOME
- PATH
- ORA_NLS10
- ORACLE_BASE
- LD_LIBRARY_PATH
- LD_LIBRARY_PATH_64 (Solaris only)
- LIBPATH (AIX only)
- SHLIB_PATH (HPUX only)
- ORACLE_PATH

$ env | grep ORACLE_HOME
$ env | grep PATH
$ env | grep ORA_NLS10
$ env | grep ORACLE_BASE
$ env | grep LD_LIBRARY_PATH
$ env | grep ORACLE_PATH

AIX:
$ env | grep LIBPATH

HP-UX:
$ env | grep SHLIB_PATH

Note that the ORA_NLS10 environment variable replaces the ORA_NLS33 environment
variable, so you may need to unset ORA_NLS33 and set ORA_NLS10.

Step 29:
~~~~~~~~
Startup upgrade the database:

$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus / as sysdba
Use Startup with the UPGRADE option:
SQL> startup upgrade

Step 30:
~~~~~~~~

Create a SYSAUX tablespace. In Oracle Database 10g, the SYSAUX tablespace is
used to consolidate data from a number of tablespaces that were separate in
previous releases.

The SYSAUX tablespace must be created with the following mandatory attributes:

- ONLINE
- PERMANENT
- READ WRITE
- EXTENT MANAGEMENT LOCAL
- SEGMENT SPACE MANAGEMENT AUTO

The Upgrade Information Tool(utlu102i.sql in step 4) provides an estimate of
the minimum required size for the SYSAUX tablespace in the SYSAUX Tablespace
section.

The following SQL statement would create a 500 MB SYSAUX tablespace
for the database:

SQL> CREATE TABLESPACE sysaux DATAFILE 'sysaux01.dbf'
SIZE 500M REUSE
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
ONLINE;

Step 31:
~~~~~~~~
If table XDB.MIGR9202STATUS exists in the database, drop it before upgrading
the database (to avoid the issue described in )

Step 32:
~~~~~~~~
Spool the output so you can take a look at possible errors after the upgrade:

SQL> spool upgrade.log
SQL> @catupgrd.sql

The catupgrd.sql script determines which upgrade scripts need to be run and then runs
each necessary script. You must run the script in the new release 10.2 environment.

The upgrade script creates and alters certain data dictionary tables. It also upgrades
and configures the following database components in the new release 10.2 database (if
the components were installed in the database before the upgrade)

Oracle Database Catalog Views
Oracle Database Packages and Types
JServer JAVA Virtual Machine
Oracle Database Java Packages
Oracle XDK
Oracle Real Application Clusters
Oracle Workspace Manager
Oracle interMedia
Oracle XML Database
OLAP Analytic Workspace
Oracle OLAP API
OLAP Catalog
Oracle Text
Spatial
Oracle Data Mining
Oracle Label Security
Messaging Gateway
Expression Filter
Oracle Enterprise Manager Repository

Turn off the spooling of script results to the log file:

SQL> SPOOL OFF

Then, check the spool file and verify that the packages and procedures
compiled successfully. You named the spool file earlier in this step; the
suggested name was upgrade.log. Correct any problems you find in this file
and rerun the appropriate upgrade script if necessary. You can rerun any
of the scripts described in this note as many times as necessary.

Step 33:
~~~~~~~~
Run utlu102s.sql, specifying the TEXT option:

SQL> @utlu102s.sql TEXT

This is the Post-upgrade Status Tool displays the status of the database
components in the upgraded database. The Upgrade Status Tool displays output
similar to the following:

Oracle Database 10.2 Upgrade Status Utility 04-20-2005 05:18:40

Component Status Version HH:MM:SS
Oracle Database Server VALID 10.2.0.1.0 00:11:37
JServer JAVA Virtual Machine VALID 10.2.0.1.0 00:02:47
Oracle XDK VALID 10.2.0.1.0 00:02:15
Oracle Database Java Packages VALID 10.2.0.1.0 00:00:48
Oracle Text VALID 10.2.0.1.0 00:00:28
Oracle XML Database VALID 10.2.0.1.0 00:01:27
Oracle Workspace Manager VALID 10.2.0.1.0 00:00:35
Oracle Data Mining VALID 10.2.0.1.0 00:15:56
Messaging Gateway VALID 10.2.0.1.0 00:00:11
OLAP Analytic Workspace VALID 10.2.0.1.0 00:00:28
OLAP Catalog VALID 10.2.0.1.0 00:00:59
Oracle OLAP API VALID 10.2.0.1.0 00:00:53
Oracle interMedia VALID 10.2.0.1.0 00:08:03
Spatial VALID 10.2.0.1.0 00:05:37
Oracle Ultra Search VALID 10.2.0.1.0 00:00:46
Oracle Label Security VALID 10.2.0.1.0 00:00:14
Oracle Expression Filter VALID 10.2.0.1.0 00:00:16
Oracle Enterprise Manager VALID 10.2.0.1.0 00:00:58

Note - in RAC environments, this script may suggest that the status of the
RAC component is INVALID when in actual fact it is VALID (as shown in the
output from DBA_REGISTRY)

Step 34:
~~~~~~~~
Restart the database:
SQL> shutdown immediate (DO NOT USE SHUTDOWN ABORT!!!!!!!!!)
SQL> startup restrict

Executing this clean shutdown flushes all caches, clears buffers and performs
other database housekeeping tasks. Which is needed if you want to upgrade
specific components.

Step 35:
~~~~~~~~
Run olstrig.sql to re-create DML triggers on tables with Oracle Label Security policies.
This step is only necessary if Oracle Label Security is in your database.
(Check from Step 33).

SQL> @olstrig.sql

Step 36:
~~~~~~~~
Run utlrp.sql to recompile any remaining stored PL/SQL and Java code.

SQL> @utlrp.sql

Verify that all expected packages and classes are valid:

If there are still objects which are not valid after running the script run
the following:
spool invalid_post.lst
Select substr(owner,1,12) owner,
substr(object_name,1,30) object,
substr(object_type,1,30) type, status
from
dba_objects where status <>'VALID';
spool off

Now compare the invalid objects in the file 'invalid_post.lst' with the invalid
objects in the file 'invalid_pre.lst' you create in step 9.

NOTE: If you have upgraded from version 9.2 to version 10.2 and find that the
following views are invalid, the views can be safely ignored (or dropped):

SYS.V_$KQRPD
SYS.V_$KQRSD
SYS.GV_$KQRPD
SYS.GV_$KQRSD

After Upgrading a Database
--------------------------

Step 37:
~~~~~~~~
Shutdown the database and startup the database.

$ sqlplus '/as sysdba'
SQL> shutdown
SQL> startup restrict

Step 38:
~~~~~~~~
Complete the Step 38 only if you upgraded your database from release 8.1.7
Otherwise skip to Step 40.

A) IF you are NOT using N-type columns for *user* data:

select distinct OWNER, TABLE_NAME from DBA_TAB_COLUMNS where
DATA_TYPE in ('NCHAR','NVARCHAR2', 'NCLOB') and OWNER not in
('SYS','SYSTEM');
did not return rows in Step 7 of this note.

then:
$ sqlplus '/as sysdba'
SQL> shutdown immediate
and go to step 40.

B) IF your version 8 NLS_NCHAR_CHARACTERSET was UTF8:

you can look up your previous NLS_NCHAR_CHARACTERSET using this select:
select * from nls_database_parameters where parameter ='NLS_SAVED_NCHAR_CS';

then:

$ sqlplus '/as sysdba'
SQL> shutdown immediate
and go to step 40.

C) IF you are using N-type columns for *user* data *AND*
your previous NLS_NCHAR_CHARACTERSET was in the following list:

JA16SJISFIXED , JA16EUCFIXED , JA16DBCSFIXED , ZHT32TRISFIXED
KO16KSC5601FIXED , KO16DBCSFIXED , US16TSTFIXED , ZHS16CGB231280FIXED
ZHS16GBKFIXED , ZHS16DBCSFIXED , ZHT16DBCSFIXED , ZHT16BIG5FIXED
ZHT32EUCFIXED

then the N-type columns *data* need to be converted to AL16UTF16:

To upgrade user tables with N-type columns to AL16UTF16 run the
script utlnchar.sql:

$ sqlplus '/as sysdba'
SQL> @utlnchar.sql
SQL> shutdown immediate;
go to step 40.

D) IF you are using N-type columns for *user* data *AND *
your previous NLS_NCHAR_CHARACTERSET was *NOT* in the following list:

JA16SJISFIXED , JA16EUCFIXED , JA16DBCSFIXED , ZHT32TRISFIXED
KO16KSC5601FIXED , KO16DBCSFIXED , US16TSTFIXED , ZHS16CGB231280FIXED
ZHS16GBKFIXED , ZHS16DBCSFIXED , ZHT16DBCSFIXED , ZHT16BIG5FIXED
ZHT32EUCFIXED

then import the data exported in point 8 of this note.
The recommended NLS_LANG during import is simply the NLS_CHARACTERSET,
not the NLS_NCHAR_CHARACTERSET

After the import:
$ sqlplus '/as sysdba'
SQL> shutdown immediate;
go to step 40.

Step 39:
~~~~~~~~
If your database has TIMESTAMP WITH TIMEZONE data, you must update
the data so that it is converted and stored based on the new time
zone rules that come with the upgrade. (Step 6).

If you used the export utility to export a copy of the affected tables,
you should now use the import utility to import your data from these tables
back into your database. The import utility will update the timestamp
data as it imports.

If you used the manual script method, you will need to update the affected
timestamp data based on your backed up table. For example, if you previously
backed up your table, you need to run an update statement similar to the
one below to update your timestamp data.

UPDATE tztab t SET t.y =
(SELECT to_timestamp_tz(t1.y,'YYYY-MM-DD HH24.MI.SSXFF TZR')
FROM tztab_back t1
WHERE t.x=t1.x);

Step 40:
~~~~~~~~
Now edit the init.ora:
- If you change the value for NLS_LENGTH_SEMANTICS prior to the upgrade put the
value back to CHAR.
- If you changed the CLUSTER_DATABASE parameter prior the upgrade set it back to TRUE

Step 41:
~~~~~~~~
Startup the database:
SQL> startup

Create a server parameter file with a initialization parameter file
SQL> create spfile from pfile;

This will create a spfile as a copy of the init.ora file located in the
$ORACLE_HOME/dbs directory.

Step 42:
~~~~~~~~
Modify the listener.ora file:
For the upgraded intstance(s) modify the ORACLE_HOME parameter
to point to the new ORACLE_HOME.

Step 43:
~~~~~~~~
Start the listener
$ lsnrctl
LSNRCTL> start

Step 44:
~~~~~~~~
Enable cron and batch jobs

Step 45:
~~~~~~~~
Change oratab entry to use automatic startup
SID:ORACLE_HOME:Y

Step 46:
~~~~~~~~
Upgrade the Oracle Cluster Registry (OCR) Configuration
If you are using Oracle Cluster Services, then you must upgrade the
Oracle Cluster Registry (OCR)keys for the database.

* Use srvconfig from the 10g ORACLE_HOME. For example:

% srvconfig -upgrade -dbname db_name -orahome pre-10g_Oracle_home

USEFUL HINTS
-------------

** Upgrading With Read-Only and Offline Tablespaces

The Oracle database can read file headers created prior to Oracle 10g, so you do not
need to do anything to them during the upgrade. The only exception to this is if
you want to transport tablespaces created prior to Oracle 10g, to another platform.
In this case, the file headers must be made read-write at some point before the transport.
However, there are no special actions required on them during the upgrade.

The file headers of offline datafiles are updated later when they are brought online,
and the file headers of read-only tablespaces are updated if and when they are made
read-write sometime after the upgrade. In any other circumstance, read-only tablespaces
never have to be made read-write.

It is a good idea to OFFLINE NORMAL all tablespaces
except for SYSTEM and those containing rollback/UNDO tablespace prior to migration.
This way if migration fails only the SYSTEM and rollback datafiles need to be
restored rather than the entire database.

Note: You must OFFLINE the TABLESPACE as migrate does not allow OFFLINE files
in an ONLINE tablespace.

** Converting Databases to 64-bit Oracle Database Software

If you are installing 64-bit Oracle Database 10g software but were previously
using a 32-bit Oracle Database installation, then the databases will automatically
be converted to 64-bit during the upgrade to Oracle Database 10g except when
upgrading from Release 1 (10.1) to Release 2 (10.2).

The process is not automatic for the release 1 to release 2 upgrade, but is automatic
for all other upgrades. This is because the utlip.sql script is not run during the
release 1 to release 2 upgrade to invalid all PL/SQL objects. You must run the utlip.sql
script as the last step in the release 10.1 environment, before upgrading to release 10.2.

** If error occurs while executing the catupgrd.sql

If an error occurs during the running of the catupgrd.sql script, once the problem is fixed
you can simply rerun the catupgrd.sql script to finish the upgrade process and complete the
the upgrade process.

Appendix A: Initialization Parameters Obsolete in 10g
-----------------------------------------------------

ENQUEUE_RESOURCES
DBLINK_ENCRYPT_LOGIN
HASH_JOIN_ENABLED
LOG_PARALLELISM
MAX_ROLLBACK_SEGMENTS
MTS_CIRCUITS
MTS_DISPATCHERS
MTS_LISTENER_ADDRESS
MTS_MAX_DISPATCHERS
MTS_MAX_SERVERS
MTS_MULTIPLE_LISTENERS
MTS_SERVERS
MTS_SERVICE
MTS_SESSIONS
OPTIMIZER_MAX_PERMUTATIONS
ORACLE_TRACE_COLLECTION_NAME
ORACLE_TRACE_COLLECTION_PATH
ORACLE_TRACE_COLLECTION_SIZE
ORACLE_TRACE_ENABLE
ORACLE_TRACE_FACILITY_NAME
ORACLE_TRACE_FACILITY_PATH
PARTITION_VIEW_ENABLED
PLSQL_NATIVE_C_COMPILER
PLSQL_NATIVE_LINKER
PLSQL_NATIVE_MAKE_FILE_NAME
PLSQL_NATIVE_MAKE_UTILITY
ROW_LOCKING
SERIALIZABLE
TRANSACTION_AUDITING
UNDO_SUPPRESS_ERRORS

Appendix B: Initialization Parameters Deprecated in 10g
-------------------------------------------------------

LOGMNR_MAX_PERSISTENT_SESSIONS
MAX_COMMIT_PROPAGATION_DELAY
REMOTE_ARCHIVE_ENABLE
SERIAL_REUSE
SQL_TRACE
BUFFER_POOL_KEEP (replaced by DB_KEEP_CACHE_SIZE)
BUFFER_POOL_RECYCLE (replaced by DB_RECYCLE_CACHE_SIZE)
GLOBAL_CONTEXT_POOL_SIZE
LOCK_NAME_SPACE
LOG_ARCHIVE_START
MAX_ENABLED_ROLES
PARALLEL_AUTOMATIC_TUNING
PLSQL_COMPILER_FLAGS (replaced by PLSQL_CODE_TYPE and PLSQL_DEBUG)

KNOWN ISSUES
------------

1)
While doing a upgrade from 9iR2 to 10.2.0.X.X, on running the utlu102i.sql
script as directed in step 2
Its output informs to add streams_pool_size=50331648 to the init.ora file.
While adding the parameter Oracle gives streams_pool_size as invalid parameter.

STREAMS_POOL_SIZE, was introduced in release 10gR1
This message may be ignored for database version 9iR2 or less

2)
One of the customer has reported on keeping the shared_pool_size at 150 MB,
catmeta.sql fails with insuffient shared memory during the processing
of view KU$_PHFTABLE_VI.

Please set the shared_pool_size at 200M.

3)
While upgrade following error was encountered.
create or replace
*
ERROR at line 1:
ORA-06553: PLS-213: package STANDARD not accessible.
ORA-00955: name is already used by an existing object

Please make sure to set the following init parameters as below in the spfile/init file or
comment them out to their default values, at the time of upgrading the database.

PLSQL_V2_COMPATIBILITY = FALSE
PLSQL_CODE_TYPE = INTERPRETED # Only applicable to 10gR1
PLSQL_NATIVE_LIBRARY_DIR = ""
PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT = 0

Note 170282.1
Title: PLSQL_V2_COMPATIBLITY=TRUE causes STANDARD and
DBMS_STANDARD to Error at Compile

4)
sqlplus crashes with OCI-21500 error and a core dump
when "set serveroutput on"
is run in the same session where startup command is executed.

This issue is reported in BUG:4860003

Always disconnect from the session which issues the STARTUP and
connect as a fresh session before doing any further SQL.
eg: On upgrade to 10.2 startup the instance with the upgrade option,
exit sqlplus , reconnect a fresh SQLPLUS session as SYSDBA
and then run the upgrade scripts.

Tuesday, February 20, 2007

Usefull links

**http://dbataj.blogspot.com**
http://metalink.oracle.com
http://vasujeedigunta.blogspot.com/
http://www.idevelopment.info/cgi/ORACLE_dba_scripts.cgi#Tuning
http://www.managedtime.com/freesqlbook.php3
http://www.idevelopment.info/data/Oracle/DBA_tips/Export_Import/EXP_2.shtml
http://www.dbapool.com
http://tkyte.blogspot.com/
http://oracle- online-help. blogspot. com
http://www.oracle-base.com/articles/misc/OracleShellScripting.php
http://www.oracle-base.com/dba/DBACategories.php
http://orafaq.com/scripts/index.htm#UNIX
http://orafaq. com/node/ 3
http://www.ixora. com.au/q+ a/0104/11095147. htm
http://www.dbapool. com/articles/ 091306.html
http://www.intuitive.com/wicked/wicked-cool-shell-script-library2.shtml
http://www.databasejournal.com/scripts/archives.php
http://www.dbazine.com/oracle/or-articles/
http://dba.fyicenter.com/article/99988578.html
http://askanantha.googlepages.com/

http://www.ss64.com/index.html
http://www.jlcomp.demon.co.uk/faq/ind_faq.html#Recovery
http://www.ss64.com/ora/
http://techpubs.sgi.com/library/tpl/cgi-bin/browse.cgi?db=man&coll=linux&pth=/man1

Sunday, August 20, 2006

Common Performance Tuning Issues

TROUBLESHOOTING GUIDE
Common Performance Tuning Issues

Table of Contents

1. Introduction
2. Shared Pool and Library Cache Performance Tuning
3. Buffer Cache Performance Tuning
4. Latch Contention
5. Redo Log Buffer Performance Tuning
6. Rollback Segment Performance Tuning
7. Temporary Tablespace Performance Tuning
8. Checkpoint Performance Tuning
9. Query Performance Tuning
10. Import Performance Tuning
11. STATPACK Utility
12. Utlbstat/Utlestat Utility

1. Introduction

This document covers some of the most common tuning scenarios. More specific
information on each tuning area is available through the links provided.

2. Shared Pool and Library Cache Performance Tuning

Oracle keeps SQL statements, packages, object information and many other items
in an area in the SGA known as the shared pool. This sharable area of memory
is managed as a sophisticated cache and heap manager rolled into one. It has 3
fundamental problems to overcome:

- The unit of memory allocation is not a constant. Memory
allocations from the pool can be anything from a few bytes to
many kilobytes

- Not all memory can be 'freed' when a user finishes with, as the
aim of the shared pool is to maximize sharability of
information.

- There is no disk area to be able to page out to so this is not
like a traditional cache where there is a file-backing store.
Only "recreatable" information can be discarded from the cache
and it has to be re-created when it is next needed.

Here are some tips in tuning the shared pool:

· Flushing the Shared Pool will coalesce small chunks of memory. When
the shared pool is highly fragmented, this may temporarily restore
performance. To flush the shared pool issue: alter system flush shared_pool;
Please note that executing this statement will cause a spike in performance
while the objects are reloaded and should be done when the database is not
being heavily used.

· Make sure that OLTP application uses "bind variables".
This is not that important for DSS.

· Make sure that the library cache pinhitratio is > 95%

. Increasing the size of the shared pool is not always the answer for poor
hitratios.

3. Buffer Cache Performance Tuning

The database buffer cache holds copies of data blocks read from disk.
Since the cache is usually limited due to memory constraints, all the data on
the disk cannot fit in the cache. When the cache is full, subsequent cache
misses cause Oracle to write data already in the cache to disk. A subsequent
access to the data written to disk results in a cache miss.

Here are some tips in tuning the buffer cache:

· Enable Buffer Cache Advisory in order to size your Buffer Cache correctly.

Avoid the following

· 'cache buffers lru chain' latch contention

· Large "Average Write Queue" length

· Lots of time spent waiting for "write complete waits"

· Lots of time spent waiting for "free buffer waits"

4. Latch Contention

Latches are low level serialization mechanisms used to protect
shared data structures in the SGA. A latch is a type of a lock
that can be very quickly acquired and freed. The implementation
of latches is operating system dependent, particularly in regard
to whether a process will wait for a latch and for how long.

Here are some of the important latches to tune:

- Redo Copy/Allocation Latch
- Shared Pool Latch
- Library Cache Latch

5. Redo Log Buffer Performance Tuning

LGWR writes redo entries from the redo log buffer to a redo log file.
Once LGWR copies the entries to the redo log file the user process can
over write these entries. The statistic "redo log space requests" reflects
the number of times a user process waits for space in the redo log buffer.

Here are some tips in sizing the redo logs:

· The value of "redo log space requests" statistic in v$sysstat should be near 0.

· Size your redo appropriately. The recommendation is to have the redo
log switch every 15-30 minutes.

Using the options UNRECOVERABLE in Oracle7 and NOLOGGING in Oracle8 you can avoid
redolog entries generation of certain operation to improve the performance.
Operations like: index creation, create table as select,SQL*Loader operation, etc.
can be easily rebuild without having redolog entries available.

6. Rollback Segment Performance Tuning

The Oracle database provides read consistency on rows fetched for
operations such as SELECT, INSERT, UPDATE, and DELETE against any
database object. Rollback segments are used to store undo transactions
in case the actions need to be "rolled back" or the system needs to
generate a read-consistent image from an earlier time.

Here are some tips in sizing the rollback segments:

· It is recommended to have at least 1 rollback segment for every 4
transactions.

· One large rollback segment is recommended for long running queries.

7. Temporary Tablespace Performance Tuning

In RDBMS release 7.3, Oracle introduced the concept of a temporary
tablespace. This tablespace would be used to hold temporary objects,
like sort segments. Sort segments take their storage parameters from
the DEFAULT STORAGE (NEXT) clause of the tablespace in which they reside.

Here are some tips in tuning the temporary tablespace:

· If there is a lot of contention for the Sort Extent Pool latch, even in
the stable state, then you should increase the extent size by changing
the NEXT value of the DEFAULT STORAGE clause of the temporary tablespace.

· If there is a lot of contention for the Sort Extent Pool latch and if the
wait is the result of too many concurrent sorts, you should increase the
SORT_AREA_SIZE parameter so that more sorts stay in memory.

. It is recommended to have the extent size equal to sort_area_size.
Here is an example why. Say your extent size = 500K and sort_area_size = 1Mg.
Now if there is a sort to the disk, it aquires 2 extents of 500K each and
this could cause performance degradation.

8. Checkpoint Performance Tuning

A Checkpoint is a database event, which synchronizes the data blocks in memory
with the datafiles on disk. A checkpoint has two purposes:

(1) to establish data consistency, and
(2) Enable faster database recovery.

When a checkpoint fails messages must be verified into into the alert.log file.

Here are some tips to tune the checkpoint process:

· The CKPT process can improve performance significantly and decrease the
amount of time users have to wait for a checkpoint operation to complete.

· If the value of LOG_CHECKPOINT_INTERVAL is larger than the size of the redo
log, then the checkpoint will only occur when Oracle performs a log switch
from one group to another, which is preferred. There has been a change in
this behaviour in Oracle 8i.

· The LOG_CHECKPOINTS_TO_ALERT when set to TRUE allows you to log checkpoint
start and stop times in the alert log. This is very helpful in determining
if checkpoints are occurring at the optimal frequency

. Ideally checkpoints should occur only at log swiches.

9. Query Performance Tuning

If queries are running slow consider the following:

· How fast do you want the query to run and is it a reasonable request?
· What is the OPTIMIZER_MODE set to?
· Are all indexes involved in the query valid?
· Is there any other long running query on the database?

In case of CBO:

· Are there statistics on the tables and indexes?
· Were the statistics computed or estimated?

Here are the 2 main diagnostic tools used for query performance tuning

- TKPROF
- AUTOTRACE

10. Import Performance Tuning

There is very little consolidated information on how to speed up import when it
is unbearably slow. Obviously import will take whatever time it needs to
complete, but there are some things that can be done to shorten the time it
will take.

11. STATPACK Utility

The STATPACK utility is the next generation of the Utlbstat/Utlesta report which
helps the database administrator to gather statistical information to detect
performance problems. Statspack improves on the existing UTLBSTAT/UTLESTAT performance
scripts collecting more data, including high resource SQL, pre-calculating some ratios,
such as cache hit ratios, per transaction and per second statistics, keeping a
permanent repository which makes historical data comparisons easier and separating
data collection from the report generation.

12. Utlbstat/Utlestat Utility

Bstat/Estat is a set of sql scripts located under your $ORACLE_HOME/rdbms/admin
directory that are useful for capturing a snapshot of system wide database
performance statistics. UTLESTAT creates a second snapshot of these views and
reports on the differences between the two snapshots to a file called
'report.txt'.

Bstat.sql creates a set of tables and views in your sys account, which contain
a beginning snapshot of database performance statistics.

Estat.sql creates a set of tables in your sys account, which contain an ending
snapshot of the database performance statistics and them to a file called
'report.txt'.

Here are some tips:

· Make sure that you have TIMED_STATSTICS set to TRUE (this adds only a very
small overhead to database operations).

. Make sure that the database is up and running for a while before running
utlbstat.

· Run the utbstat.sql and the utlestat.sql from svrmgrl and not sql*plus.

. Make sure that the database is not shutdown while the utlbstat/estat scripts
are running, otherwise the statstics generated are not accurate.

· Run utlbstat/estat at least for 1-3hrs during the period you are trying to
tune the database.

Performance Tuning Approaches on Oracle and UNIX

Performance Tuning Approaches on Oracle and UNIX

As a system administrator, you will often be confronted with users who say that the response time on the system is unacceptable. What is unacceptable performance? There are two sides to this:

* Quantifiable performance
* Unmeasurable performance like user dissatisfaction.

This paper will help identify quantifiable performance problems and methods to solve them. When performance-tuning databases, it is a good to split the problem into parts to facilitate the process. Performance tuning Oracle databases can be divided into three subcategories:

1. Application tuning
2. RDBMS tuning
3. UNIX system tuning

Each of the above categories has a different maximum possible effect on the performance of your ORACLE database.

1. Application Tuning

Application tuning is by far the most effective aspect for database tuning. You will need to tune your SQL statements since they intervolve both your application and database. To analyze your SQL statements you will need to:

1. Add the following lines to your init.ora file:

SQL_TRACE = true

TIMED_STATISTICS = true

2. Restart your database.
3. Run your application.
4. Run tkprof against the tracefile created by your application:

tkprof EXPLAIN=username/passwd

5. Look at formatted output of trace command and make sure that your

SQL statement is using indexes correctly. Refer to DBA guide for a list

of rules that the oracle optimizer uses when choosing a path for a SQL statement.

<<<<<< OUTPUT OF TKPROF FILE >>>>>>>

count = number of times OPI procedure was executed

cpu = cpu time executing in hundredths of seconds

elap = elapsed time executing in hundredths of secs

phys = number of physical reads of buffers (from disk)

cr = number of buffers gotten for consistent read

cur = number of buffers gotten in current mode (usually for update)

rows = number of rows processed by the OPI call

===========================================================

select * from emp where empno=7369

count cpu elap phys cr cur rows

Parse: 1 0 0 0 0 0

Execute: 1 0 0 0 0 2 0

Fetch: 1 0 0 219 227 0 2

Execution plan:

TABLE ACCESS (FULL) OF 'EMP'

===========================================================

select empno from emp where empno=7934

count cpu elap phys cr cur rows

Parse: 2 0 0 0 0 0

Execute: 2 0 0 0 0 2 0

Fetch: 2 0 0 0 2 0 2

Execution plan:

INDEX (RANGE SCAN) OF 'ALEX_INDX' (NON-UNIQUE)

===========================================================

When your query is returning less than 10% of the rows of a table and the table is a reasonably large table, you will want to index your query. Above is an example of the same query run twice. The first time the optimizer chose to do a full table scan on the table EMP. The second time an index was created called ALEX_INDX. The optimizer chose to use the index. On a table

with 1000 rows this should have resulted in a faster query.

Oracle uses a rule-based optimizer for its SQL statements. When a SQL statement is parsed, the optimizer decides which query path will be chosen. The optimizer can often times make mistakes or simply illustrate that your SQL statement was written incorrectly. You can refer to Chapter 2 of the Performance Tuning Guide for more information on SQL statement tuning. Page 19-17 of the V6 DBA guide has a listing of the query paths that the optimizer will use ranked by speed.

Other considerations for application tuning may include the actual design of your application. A common problem found in menu5/forms30 applications is the way in which one calls the other. Make sure that menu5 is calling forms30 directly and not through a UNIX system call. Calling forms30 through a UNIX system call has the effect of doubling the number of connections to the database and also doubling the load on the UNIX machine.

2. RDBMS Tuning

The default init.ora database configuration file is inadequate for large

RDBMS's. Tools for tuning the database are:

1. SQLDBA * Monitor

2. Bstat/Estat

3. V$ database tables.

By looking at SQLDBA * Monitor one can get a good idea of what is happening to the database at that moment. The first place to look is the IO display. This will display the CACHE HIT RATIO. The cache-hit ratio is the ratio of hits to misses in the SGA for data. The higher the ratio, the more data is being found in the SGA. This means that oracle will not have to do as many disk reads to retrieve information, thus saving time and CPU processing.

While a hit ratio of 1.00 would be ideal, it is more realistic to aim for achieving a hit ratio of about .80.DB_BUFFER values by setting the init.ora parameter

DB_BLOCK_LRU_EXTENDED_STATISTICS equal to the additional number of DB_BUFFERS you wish to add. You will then be able to see the additional number of cache hits that would occur if you add X number of database buffers. For more information on this you can refer to pg 3-20 of the database performance tuning guide.

After looking at the cache-hit ratio and adjusting the number of database buffers accordingly you can analyze FILE IO. This screen

in SQLDBA * Monitor will show IO to each oracle datafile. It allows you

to identify two problem areas:

1. Table scans (indicated by a low number of reads/s & high number of blks/R)

2. Heavily used tablespaces

Table scans indicate that your SQL statements might not be tuned correctly. If your query is returning less than 15% of the rows of a table you should be using indexes to query the tables. Secondly you will also be aware of which tablespaces are used heavily and perhaps consider moving them to a separate disk to balance the IO.

The Rollback screen can be very useful on an update or insert-heavy system. Whenever you are changing or inserting new data, you write the changes to a rollback segment until they are committed. If you have 30 transactions and 1 rollback segment, you will have contention for that rollback segment. This will incur a performance penalty. To make sure that this is not happening, verify that every rollback segment has a maximum of 4 active transactions using it during heavy updating of the database.

The table V$ROWCACHE also contains useful information for tuning. The following select statement can determine whether or not any of the init.ora parameters beginning with DC_XXXX should be raised. These parameters control the size of various data dictionary caches.

SELECT parameter,gets,getmisses,count,usage FROM sys.v$rowcache;

PARAMETER GETS GETMISSES COUNT USAGE

------------- ------- ---------- ---------- -----

dc_usernames 134 5 50 5

dc_columns 11772 288 300 300

The parameter dc_usernames has a very low number of cache misses (GETMISSES). We have allocated 50 username entries (COUNT) and are only using 5 (USAGE). There is no need to raise the value of dc_usernames.

We have allocated 300 DC_COLUMNS and we are using all 300. In addition, the number of GETMISSES or cache misses are high for the parameter DC_COLUMNS. In this case, it is recommended to increase the value of DC_COLUMNS to reduce the number of GETMISSES or cache misses. A cache miss can be a very expensive operation for oracle since it means that the data requested was not in cache and thus a recursive call had to be made. For more information on this you can refer to pg 3-13 of the v6 performance tuning guide.

3. UNIX System Analysis

It is useful to subdivide UNIX system analysis into three subcategories:

1. Memory
2. CPU
3. IO

1. MEMORY

One of the most common problems when running large numbers of concurrent users on UNIX machines is lack of memory. In this case, a quick review of UNIX memory management is useful to see what effect lack of RAM can have on performance. A UNIX machine has virtual memory: the total addressable memory range. Virtual memory is composed of RAM, DISK and SWAP space.

Generally, you will want to have the available SWAP space equal to 2 to 3 times the RAM.

How does UNIX use SWAP space? It uses two memory management policies: swapping and paging. Swapping occurs when UNIX transfers an entire process from RAM to a SWAP device. This frees up a large amount of RAM. Paging occurs when UNIX only transfers a "PAGE" of memory to the SWAP device. Only a tiny portion of a process might actually be "paged out" to a SWAP device. While swapping frees up memory - it is slower than paging. Paging generally is more efficient but does not allow for large amounts of memory to be freed simultaneously. Most UNIX systems today use a combination of paging and swapping to manage memory. Generally, you will see the following behavior:

* System lightly used: no paging or swapping occurs.
* System under a medium load: paging occurs as RAM memory runs low
* System under a very heavy load: paging stops and swapping begins.

When analyzing your UNIX machine, make sure that the machine is not swapping at all and at worst paging lightly. This indicates a system with a healthy amount of memory available. To analyze paging and swapping, use the following commands. Commands used in Berkeley UNIX based systems will be marked as BSD. Commands used in ATT system V will be marked as ATT.

1. vmstat 5 5 (BSD)

procs memory page disk faults cpu

r b w avm fre re at pi po fr de sr d0 s1 d2 s3 in sy cs us sy id

0 0 0 0 1088 0 2 2 0 1 0 0 0 0 0 0 26 72 24 0 1 98

Note: There are NO pageouts (po) occurring on this system. There are also 1088 * 4k pages of free RAM available (4 Meg). It is OK and normal to have page out (po) activity. You should get worried when the number of page ins (pi) starts rising. This indicates that you system is starting to page.

2. pstat -s (BSD)

12112k allocated + 3252k reserved = 15364k used, 37280k available

Note: pstat will also give you the amount of RAM and SWAP currently available on your machine.

3. sar -wpg 5 5 (ATT)

09:54:29 swpin/s pswin/s swpot/s pswot/s pswch/s

atch/s pgin/s ppgin/s pflt/s vflt/s slock/s

pgout/s ppgout/s pgfree/s pgscan/s %s5ipf

09:54:34 0.00 0.0 0.00 0.0 12

0.00 0.22 0.22 0.65 3.90 0.87

0.00 0.00 0.00 0.00 0.00

Note: There is absolutely no swapping or paging going on. (swpin,swpot,ppgin,ppgout).

4. sar -r 5 5 (ATT)

10:10:22 freemem freeswp

10:10:27 790 5862

This will give you a good indication of how much free swap and RAM you have on your machine. There are 790 pages of memory available and 5862 disk blocks of SWAP available.

2. CPU

Once you have monitored your systems available memory you will want to make sure the the CPU(s) are not being overloaded. Here is some general information about how processes get allocated CPU time. UNIX is a multi-processing operating system. That means that a UNIX machine has to manage and process multiple user processes simultaneously. UNIX does this in the same way that people wait in line to buy groceries.

When a process is ready to be processed by a CPU it will be placed on the waiting line or RUN-QUEUE. This is a queue of processes waiting to be run. Obviously there are limits within which one wants to keep the RUN-QUEUE size. Another factor of interest is the percentage of time the the CPU spends in user mode, system mode, or idle mode. Some commands that determine whether or not there is a CPU resource problem occurring:

1. vmstat 5 5 (BSD)

procs memory page disk faults cpu

r b w avm fre re at pi po fr de sr d0 s1 d2 s3 in sy cs us sy id

0 0 0 0 1088 0 2 2 0 1 0 0 0 0 0 0 26 72 24 0 1 98

Note: The CPU is spending most of its time in IDLE mode (id). That means that the CPU is not being heavily used at all! There are no processes that are waiting to be run (r), blocked (b), or waiting for IO (w) in the RUN QUEUE.

2. sar -qu 5 5 (ATT)

10:58:02 runq-sz %runocc swpq-sz %swpocc

%usr %sys %wio %idle

10:58:07 2.8 100

0 2 4 94

Note: The CPU is spending most (94%) of its time in idle mode. This CPU is not being heavily used at all. Two solutions to this are:

1. Obtain a faster processor
2. Use more CPU's.

Avoid overloading your CPU. Response time on your machine will suffer if it is overloaded. Try to keep the run queue 100% occupied and have less that 6 processes waiting to be run for one CPU. This changes as you add more CPU's or a faster CPU. You may also want to avoid the CPU spending most of its time (more than 50%) in system mode. This may indicate that you are spending too much time in kernel mode servicing interrupts, swapping processes etc.

3. I/O

The last step in analyzing your UNIX machine is taking a look at IO. After having looked at SQLDBA monitor to see which datafiles are being used heavily you may also want to take a look at what UNIX says about file IO. These commands are for analyzing file IO on file systems.

1. iostat -d 5 5(BSD)

sd1 sd3

bps tps msps bps tps msps

1 0 0.0 4 0 0.0

iostat will display the number of kilobytes transferred per second, the number of transfers per second, and the milliseconds per average seek. In the example above, both SD1 and SD3 are not used heavily at all. BPS rates over 30 indicate heavy usage of a particular disk. If only one disk shows heavy usage, consider moving some of your datafiles off it or striping your data across several disks.

2. sar -d 5 5 (ATT)

09:17:20 device %busy avque r+w/s blks/s avwait avserv

09:17:26 iop0/pdisk010 472.45 1.18 9 107 39.26 512.66

iop0/pdisk000 18.43 2.66 8 132 31.36 24.10

iop0/pdisk020 317.08 1.11 11 165 31.95 294.02

iop0/pdisk021 590.88 1.34 27 518 96.26 219.96

iop0/pdisk040 34.94 1.64 18 113 43.70 19.58

iop0/pdisk041 45.33 1.17 20 79 3.73 22.89

Note: The "-d" option reports activity for each block device, The following is an explanation on the output. %busy, avque - portion of time device was busy servicing a transfer request, average number of requests outstanding during that time; r+w/s, blks/s - number of data transfers from or to device, number of bytes transferred in 512-byte units; avwait, avserv - average time (in ms) that transfer requests wait idly on queue, and average time to be serviced. There is a relationship between the number of blocks transferred per second and the average wait time. You can use this to identify which disks are heavily used and which are

underutilized.

3. sar -b 5 5 (ATT)

15:52:57 bread/s lread/s %rcache bwrit/s lwrit/s %wcache pread/s pwrit/s

15:53:12 0 2 90 1 2 38 0 0

Note: The "-b" option indicates the overall health of the IO subsystem. The %rcache should be greater than 90% and %wcache should be greater than 60%. If this is not the case, your system may be bound by disk IO. The sum of bread, bwrit, pread, and pwrit gives a good indicator of how well your file subsystem is doing. The sum should not be greater than 40 for 2 drives and

60 for 4-8 drives. If you exceed these values, your system may be IO bound. For more information on this refer to pg 2-22 of the Oracle V7 technical reference guide.

When analyzing disk IO, make sure that you have balanced the load on your system. Here is a "wish" list of steps for designing a disk layout for Oracle:

1. Make sure that your logfiles and archived logfiles are NOT on the same disk as your datafiles. This is a basic safety precaution against disk failure.
2. Put your files on raw devices.
3. Allocate one disk for the User Data Tablespace.
4. Place Rollback, Index, and System Tablespaces on separate disks.
5. Consider using Raid level 5 disk striping to stripe your datafiles across separate disks. You can also use the above-mentioned UNIX commands to monitor the IO on your system and identify problem areas.

Other tools for system administratation are platform-dependent. For example, HP and SEQUENT provide a facility called MONITOR that gives much of the above information in a graphical format. ATT SVR 4 also provides a product called GSAR (graphical sar). Look in your operating system (OS) documentation for additional monitoring commands. As a further

reference, consider "SYSTEM PERFORMANCE TUNING" published by O'Reilly and Associates.

Conclusion: There are many ways to approach Oracle performance issues. A structured approach like the one discussed above will allow the system administrator to systematically analyze her system and identify any problem areas. Once this has been accomplished, measures can be taken to correct problems. Performance is subjective, so find out what is expected.

process to upgrade an existing database instance running on Oracle8i Enterprise Edition Release 8.1.7.x directly to a version 9.0.1 patch set.

Upgrading Directly to a 9.0.1 Patch Set
Database Server Enterprise Edition Release 9.0.1
August 2002

This document describes the process to upgrade an existing database
instance running on Oracle8i Enterprise Edition Release 8.1.7.x
directly to a version 9.0.1 patch set. Upgrading your system to an
9.0.1 patch set from a version earlier than 8.1.7 (for example 8.0.6
or 8.1.5) is not supported. In this case you must either
migrate/upgrade to 9.0.1.0 first and then install the patch set, or
upgrade/migrate to 8.1.7 and then follow these intructions.

Attention: There is an error in the 9.0.1.4 README (patch_note.htm)
indicating that upgrading from 8.1.7 directly to 9.0.1.4 is not
supported. It is supported as documented here. See Note 214972.1 for
the wording which should have appeared in the 9.0.1.4 Patch Set
README.

Attention: These notes apply to UNIX and Windows NT/2000 platforms.
However, you may need to modify some instructions slightly depending
upon your platform. For example, these notes typically use UNIX syntax
when specifying a directory, so Windows NT/2000 users will need to
substitute the appropriate syntax when accessing that
directory(folder).

Attention: You can obtain the most recent 9.0.1 patchset from
OracleMetaLink. After logging on to OracleMetaLink, navigate to the
patch download page using the menu on the left of the screen. Query
for the patchset using the following parameters:

Parameter

Value
Product Family Oracle Server
Release 9.0.1.x (where x is the highest version available)
Platform
Limit Search to Latest Product Patchsets or Minipacks

Upgrading from Oracle8i Release 8.1.7.x Database Server to Oracle9i
Enterprise Edition Release 9.0.1.x

Follow instructions in this section only if you have an existing
Database Server using Oracle8i Enterprise Edition Release 8.1.7.x.
Upgrading from pre-8.1.7 versions directly to an 9.0.1 patch set is
not supported.

1.

Install Oracle9i Enterprise Editions Release 9.0.1 in new ORACLE_HOME.
Log in as the user who manages (owns) the Oracle9i Enterprise
Edition files and database. Make sure that none of the environment
settings such as ORACLE_HOME, PATH, ORA_NLS, etc. refer to the
existing Oracle8i Enterprise Edition Release 8.1.7 environment.

If you have not already installed the current Oracle9i
Enterprise Edition Release 9.0.1 files, follow the instructions in the
Oracle9i Installation Guide. Install the files in a location other
than the existing 8.1.7 Oracle home. Choose to install all components
currently used by your 8.1.7 database.
Attention: Windows NT/2000 customers should not install the
following development tools. These tools do not support multiple
Oracle Homes.
* Oracle Objects for OLE
* Oracle ODBC Drivers
* Oracle OLE Providers for OLEDB

We also recommend choosing all the languages, (or at a minimum
all the required languages) during the installation.

Do not run any migration scripts at this time.

2. Install 9.0.1 Patch Set
Install the latest Oracle9i Release 9.0.1 patch set into the
9.0.1 Oracle Home using Oracle Universal Installer.

Do not run any SQL scripts at this time.

3. Check OracleMetaLink for additional patches
Additional issues with 9.0.1 may have been identified since this
document and the Patch Set Release Notes were authored. Check Document
149018.1 on Metalink for the latest issues/alerts.

4. Prepare Database for Upgrade
The 'Prepare to Upgrade' section of Chapter 7 (Upgrading from an
Older Release of Oracle to the New Oracle9i Release) of the Oracle9i
Migration Release 1 (9.0.1) contains steps to be executed to the
existing database in the existing environment before upgrade. Complete
all the steps listed there.

5.

Shutdown Servers, Concurrent Managers and Database
All servers and processes such as the Web Server and Net8
Listener must be shut down and all users must be logged out before
starting the database upgrade.

The Database will be unavailable to users until all tasks in
these notes are completed.

6. Modify init.ora parameters.
Some of the database initialization parameters have changed or
become obsolete in Oracle9i Enterprise Edition Release 9.0.1. Details
are available in the Oracle9i Migration manual.

Attention: During your database migration, instructions in the
Oracle9i Database Migration Release 1 (9.0.1) require the value of
_system_trig_enabled to be temporarily set to false at the beginning
of the migration, and restored to true before upgrading JServer.

7. Backup the Database.
We recommend taking a backup of your database.

8. Ensure Adequate Rollback and System Space
Ensure that there is sufficient free space in the SYSTEM
tablespace and for the Rollback segments (see Oracle9i Migration
Release 1 (9.0.1) for more details.

9. Upgrade the Database
Follow instructions in the manual Oracle9i Migration Release 1
(9.0.1) to upgrade the database to the current release. Ensure that
you have the latest version of the manual, which can be found on the
Oracle Technology Network.

Continue Chapter 7 of the Oracle9i Migration Release 1 (9.0.1)
Guide with the section titled 'Upgrade the Database'. If using the
dbma to perform the migration then start with the section titled
'Running the Oracle Data Migration Assistant Independently'. If
choosing to perform the migration manually start with the section
entitled 'Upgrade the Database Manually' but skip to step 9 as the new
release has already been installed .

You will be upgrading directly to the current 9.0.1 patch set level.

1. You must now complete the steps specific to any components
referred in the Migration Document that you are using.

There is no need to compile invalid database objects at this time.

2. Oracle9i Release 9.0.1 Patch Set release notes contains
additional steps required to complete the install of the patch set.
Follow the instructions in the How to Install This Patch Set section
to upgrade the Databases to the latest patch set level. The step 9 in
this document has instructions to run the following script, you may
ignore running this script:
* ?/rdbms/admin/catpatch.sql

10. Compile All objects.
You may use the standard utility utlrp.sql (found in
$ORACLE_HOME/rdbms/admin) to compile all invalid objects in the
database.

Some database objects will have become invalid due to the
database upgrade.

Change Log
Date Description
June 21, 2002

* Document created based on 8.1.7 version and Applications 11i
Interoperability guide.

August 13, 2002

* Updated for 9.0.1.4

Step-By-Step Installation of 9.2.0.5 RAC on Linux

Step-By-Step Installation of 9.2.0.5 RAC on Linux

Note: This note was created for 9i RAC. The 10g Oracle documentation
provides installation instructions for 10g RAC. These instructions
can be found on OTN:

Oracle(r) Real Application Clusters Installation andOracle(r) Real
Application Clusters Installation and Configuration Guide
10g Release 1 (10.1) for AIX-Based Systems, hp HP-UX PA-RISC (64-bit),
hp Tru64 UNIX, Linux, Solaris Operating System (SPARC 64-bit)

Purpose

This document will provide the reader with step-by-step instructions
on how to install a cluster, install Oracle Real Application Clusters
(RAC) (Version 9.2.0.5), and start a cluster database on Linux. For
additional explanation or information on any of these steps, please
see the references listed at the end of this document.

Disclaimer: If there are any errors or issues prior to step 2, please
contact your Linux distributor.
The information contained here is as accurate as possible at the time
of writing.

* 1. Configuring the Cluster Hardware
o 1.1 Minimal Hardware list / System Requirements
+ 1.1.1 Hardware
+ 1.1.2 Software
o 1.2 Installing the Shared Disk Subsystem
o 1.3 Configuring the Cluster Interconnect and Public Network Hardware
* 2. Creating a cluster
o 2.1 UNIX Pre-installation tasks
o 2.2 Configuring the Shared Disks
o 2.3 Run the Oracle Universal Installer to install the
9.2.0.4 ORACM (Oracle Cluster Manager)
o 2.4 Configure the hangcheck-timer
o 2.5 Install Version 10.1.0.2 of the Oracle Universal Installer
o 2.6 Run the 10.1.0.2 Oracle Universal Installer to patch
the Oracle Cluster Manager (ORACM) to 9.2.0.5
o 2.7 Modify the ORACM configuration files to utilize the
hangcheck-timer
o 2.8 Start the ORACM (Oracle Cluster Manager)
* 3. Installing RAC
o 3.1 Install 9.2.0.4 RAC
o 3.2 Patch the RAC Installation to 9.2.0.5
o 3.3 Start the GSD (Global Service Daemon)
o 3.4 Create a RAC Database using the Oracle Database
Configuration Assistant
* 4. Administering Real Application Clusters Instances
* 5. References

1. Configuring the Clusters Hardware<>

1.1 Minimal Hardware list / System Requirements

Please check the RAC/Linux certification matrix for information on
currentlyRAC/Linux certification matrix for information on currently
supported hardware/software.

1.1.1 Hardware

* Requirements:
o Refer to the RAC/Linux certification matrix for
information onRAC/Linux certification matrix for information on
supported configurations. Ensure that the system has at least the
following resources:
- 400 MB in /tmp
- 512 MB of Physical Memory (RAM)
- Three times the amount of Physical Memory for Swap
space (unless the system exceeds 1 GB of Physical Memory, where two
times the amount of Physical Memory for Swap space is sufficient)

An example system disk layout is as follows:-

A sample system disk layout

Slice

Contents

Allocation (in Mbytes)

0

/

2000 or more

1

/boot

64

2

/tmp

1000

3

/usr

3000-7000 depending on operating system and packages installed

4

/var

512 (can be more if required)

5

swap

Three times the amount of Physical Memory for Swap space (unless the
system exceeds 1 GB of Physical Memory, where two times the amount of
Physical Memory for Swap space is sufficient).

6

/home

2000 (can be more if required)

1.1.2 Software

* For RAC on Linux support, consult the operating system vendor
and see the RAC/Linux certification matrix.
* RAC/Linux certification matrix. Make sure you have make and
rsh-server packages installed, check with:

$rpm -q rsh-server make
rsh-server-0.17-5
make-3.79.1-8

If these are not installed, use your favorite package manager to
install them.

1.1.3 Patches

Consult with your operating system vendor to get on the latest patch
version of the kernel.

1.2 Installing the Shared Disk Subsystem

This is highly dependent on the subsystem you have chosen. Please
refer to your hardware documentation for installation and
configuration instructions on Linux. Additional drivers and patches
might be required. In this article we assume that the shared disk
subsystem is correctly installed and that the shared disks are visible
to all nodes in the cluster.

1.3 Configuring the Cluster Interconnect and Public Network Hardware

If not already installed, install host adapters in your cluster nodes.
For the procedure on installing host adapters, see the documentation
that shipped with your host adapters and node hardware.

Each system will have at least an IP address for the public network
and one for the private cluster interconnect. For the public network,
get the addresses from your network manager. For the private
interconnect use 1.1.1.1 , 1.1.1.2 for the first and second node. Make
sure to add all addresses in /etc/hosts.

[oracle@opcbrh1 oracle]$ more /etc/hosts

ex:
9.25.120.143 rac1 #Oracle 9i Rac node 1 - public network
9.25.120.143 rac2 #Oracle 9i Rac node 2 - public network

1.1.1.1 int-rac1 #Oracle 9i Rac node 1 - interconnect
1.1.1.2 int-rac2 #Oracle 9I Rac node 2 - interconnect

Use your favorite tool to configure these adapters. Make sure your
public network is the primary (eth0).

Interprocess communication is an important issue for RAC since cache
fusion transfers buffers between instances using this mechanism. Thus,
networking parameters are important for RAC databases. The values in
the following table are the recommended values. These are NOT the
default on most distributions.

Parameter

Meaning

Value

/proc/sys/net/core/rmem_default

The default setting in bytes of the socket receive buffer

262144

/proc/sys/net/core/rmem_max

The maximum socket receive buffer size in bytes

262144

/proc/sys/net/core/wmem_default

The default setting in bytes of the socket send buffer

262144

/proc/sys/net/core/wmem_max

The maximum socket send buffer size in bytes

262144

You can see these settings with:

$ cat /proc/sys/net/core/rmem_default

Change them with:

$ echo 262144 > /proc/sys/net/core/rmem_default

This will need to be done each time the system boots. Some
distributions already have setup a method for this during boot. On Red
Hat , this can be configured in /etc/sysctl.conf (like :
net.core.rmem_default = 262144).

2. Creating a Cluster

On Linux, the cluster software required to run Real Application
Clusters is included in the Oracle distribution.

The Oracle Cluster Manager (ORACM) installation process includes eight
major tasks.

1. UNIX pre-installation tasks.
2. Configuring the shared disks
3. Run the Oracle Universal Installer to install the 9.2.0.4 ORACM
(Oracle Cluster Manager)
4. Configure the hangcheck-timer.
5. Install version 10.1.0.2 of the Oracle Universal Installer
6. Run the 10.1.0.2 Oracle Universal Installer to patch the Oracle
Cluster Manager (ORACM) to 9.2.0.5
7. Modify the ORACM configuration files to utilize the hangcheck-timer.
8. Start the ORACM (Oracle Cluster Manager)

2.1 UNIX Pre-installation tasks

These steps need to be performed on ALL nodes.

* First, on each node, create the Oracle group. Example:

# groupadd dba -g 501

* Next, make the Oracle user's home directory. Example:

# mkdir -p /u01/home/oracle

* On each node, create the Oracle user. Make sure that the Oracle
user is part of the dba group. Example:

# useradd -c "Oracle Software Owner" -G dba -u 101 -m -d
/u01/home/oracle -s /bin/csh oracle

* On each node, Create a mount point for the Oracle software
installation (at least 2.5 GB, typically /u01). The oracle user
should own this mount point and all of the directories below the mount
point. Example:

# mkdir /u01
# chown -R oracle.dba /u01
# chmod -R ug=rwx,o=rx /u01

* Once this is done, test the permissions on each node to ensure
that the oracle user can write to the new mount points. Example:

# su - oracle
$ touch /u01/test
$ ls -l /u01/test
-rw-rw-r-- 1 oracle dba 0 Aug 15 09:36 /u01/test

* Depending on your Linux distribution, make sure inetd or xinetd
is started on all nodes and that the ftp, telnet, shell and login (or
rsh) services are enabled (see /etc/inetd.conf or /etc/xinetd.conf and
/etc/xinetd.d). Example:

# more /etc/xinetd.d/telnet
# default: on
# description: The telnet server serves telnet sessions; it uses # unencrypted username/password pairs for authentication.
service telnet
{
flags = REUSE
socket_type = stream
wait = no
user = root
server = /usr/sbin/in.telnetd
log_on_failure += USERID
disable = no
}

In this example, disable should be set to 'no'.

* On the node from which you will run the Oracle Universal
Installer, set up user equivalence by adding entries for all nodes in
the cluster, including the local node, to the .rhosts file of the
oracle account, or the /etc/hosts.equiv file.

Sample entries in /etc/hosts.equiv file:

rac1
rac2
int-rac1
int-rac2

* As oracle user, check for user equivalence for the oracle
account by performing a remote copy (rcp) to each node (public and
private) in the cluster. Example:

RAC1:

$ touch /u01/test
$ rcp /u01/test rac2:/u01/test1
$ rcp /u01/test int-rac2:/u01/test2

RAC2:

$ touch /u01/test
$ rcp /u01/test rac1:/u01/test1
$ rcp /u01/test int-rac1:/u01/test2
$ ls /u01/test*
/u01/test /u01/test1 /u01/test2

RAC1:

$ ls /u01/test*
/u01/test /u01/test1 /u01/test2

Note: If you are prompted for a password, you have not given the
oracle account the same attributes on all nodes. You must correct this
because the Oracle Universal Installer cannot use the rcp command to
copy Oracle products to the remote node's directories without user
equivalence.

System Kernel Parameters

Verify operating system kernel parameters are set to appropriate levels:

Kernel Parameter

Setting

Purpose

SHMMAX

2147483648

Maximum allowable size of one shared memory segment.

SHMMIN

1

Minimum allowable size of a single shared memory segment.

SHMMNI

100

Maximum number of shared memory segments in the entire system.

SHMSEG

10

Maximum number of shared memory segments one process can attach.

SEMMNI

100

Maximum number of semaphore sets in the entire system.

SEMMSL

250

Minimum recommended value. SEMMSL should be 10 plus the largest
PROCESSES parameter of any Oracle database on the system.

SEMMNS

1000

Maximum semaphores on the system. This setting is a minimum
recommended value. SEMMNS should be set to the sum of the PROCESSES
parameter for each Oracle database, add the largest one twice, plus
add an additional 10 for each database.

SEMOPM

100

Maximum number of operations per semop call.

You will have to set the correct parameters during system startup, so
include them in your startup script (startoracle_root.sh):

$ export SEMMSL=100
$ export SEMMNS=1000
$ export SEMOPM=100
$ export SEMMNI=100
$ echo $SEMMSL $SEMMNS $SEMOPM $ SEMMNI > /proc/sys/kernel/sem
$ export SHMMAX=2147483648
$ echo $SHMMAX > /proc/sys/kernel/shmmax

Check these with:

$ cat /proc/sys/kernel/sem
$ cat /proc/sys/kernel/shmmax

You might want to increase the maximum number of file handles, include
this in your startup script or use /etc/sysctl.conf :

$ echo 65536 > /proc/sys/fs/file-max

To allow your oracle processes to use these file handles, add the
following to your oracle account login script (ex.: .profile)

$ ulimit -n 65536

Note: This will only allow you to set the soft limit as high as the
hard limit. You might have to increase the hard limit on system level.
This can be done by adding ulimit -Hn 65536 to /etc/initscript. You
will have to reboot the system to make this active. Sample
/etc/initscript:

ulimit -Hn 65536
eval exec "$4"

Establish Oracle environment variables: Set the following Oracle
environment variables:

Environment Variable

Suggested value

ORACLE_HOME

eg /u01/app/oracle/product/920

ORACLE_TERM

xterm

PATH

/u01/app/oracle/product/9.2.0/bin: /usr/ccs/bin:/usr/bin/X11/:/usr/local/bin

and any other items you require in your PATH

DISPLAY

:0.0

(review Note:153960.1 for detailed information)
Note:153960.1 for detailed information)

TMPDIR

Set a temporary directory path for TMPDIR with at least 100 Mb of free
space to which the OUI has write permission.

ORACLE_SID

Set this to what you will call your database instance. This should be
UNIQUE on each node.

It is best to save these in a .login or .profile file so that you do
not have to set the environment every time you log in.

* Create the directory /var/opt/oracle and set ownership to the
oracle user. Example:

$ mkdir /var/opt/oracle
$ chown oracle.dba /var/opt/oracle

* Set the oracle user's umask to "022" in you ".profile" or
".login" file. Example:

$ umask 022

Note: There is a verification script InstallPrep.sh available which
may beverification script InstallPrep.sh available which may be
downloaded and run prior to the installation of Oracle Real
Application Clusters. This script verifies that the system is
configured correctly according to the Installation Guide. The output
of the script will report any further tasks that need to be performed
before successfully installing Oracle 9.x DataServer (RDBMS). This
script performs the following verifications:-

* ORACLE_HOME Directory Verification
* UNIX User/umask Verification
* UNIX Group Verification
* Memory/Swap Verification
* TMP Space Verification
* Real Application Cluster Option Verification
* Unix Kernel Verification

. ./InstallPrep.sh

You are currently logged on as oracle
Is oracle the unix user that will be installing Oracle Software? y or n
y

Enter the unix group that will be used during the installation
Default: dba

Enter the version of Oracle RDBMS you will be installing
Enter either : 901 OR 920 - Default: 920
920
The rdbms version being installed is 920

Enter Location where you will be installing Oracle
Default: /u01/app/oracle/product/oracle9i
/u01/app/oracle/product/9.2.0
Your Operating System is Linux
Gathering information... Please wait
JDK check is ignored for Linux since it is provided by Oracle

Checking unix user ...

Checking unix umask ...
umask test passed

Checking unix group ...
Unix Group test passed

Checking Memory & Swap...
Memory test passed

/tmp test passed

Checking for a cluster...

Linux Cluster test section has not been implemented yet
No cluster warnings detected
Processing kernel parameters... Please wait
Running Kernel Parameter Report...
Check the report for Kernel parameter verification\n

Completed.

/tmp/Oracle_InstallPrep_Report has been generated

Please review this report and resolve all issues before attempting to
install the Oracle Database Software

Note: If you get an error like this:

InstallPrep.sh: line 45: syntax error near unexpected token `fi'

or

./InstallPrep.sh: Command not found.

Then you need to copy the script into a text file (it will not run if
the file is in binary format).

2.2 Configuring the Shared Disks

For 9.2 Real Application Clusters on Linux, you can use either OCFS
(Oracle Cluster Filesystem), RAW, or NFS (Redhat and Network Appliance
Only) for storage of Oracle database files.

* For more information on setting up OCFS for RAC on Linux, see
the following MetaLink Note:

Note 220178.1 - Installing and setting up ocfs on Linux -
BasicNote 220178.1 - Installing and setting up ocfs on Linux - Basic
Guide

* For more information on setting up RAW for RAC on Linux, see the
following MetaLink Note:

Note 246205.1 - Configuring Raw Devices for Real
ApplicationNote 246205.1 - Configuring Raw Devices for Real
Application Clusters on Linux

* For more information on setting up NFS for RAC on Linux, see the
following MetaLink Note (Steps 1-6):

Note 210889.1 - RAC Installation with a NetApp Filer in Red
HatNote 210889.1 - RAC Installation with a NetApp Filer in Red Hat
Linux Environment

2.3 Run the Oracle Universal Installer to install the 9.2.0.4 ORACM
(Oracle Cluster Manager)

These steps only need to be performed on the node that you are
installing from (typically Node 1).

* If you are using OCFS or NFS for your shared storage, pre-create
the quorum file and srvm file. Example:

# dd if=/dev/zero of=/ocfs/quorum.dbf bs=1M count=20
# dd if=/dev/zero of=/ocfs/srvm.dbf bs=1M count=100
# chown root:dba /ocfs/quorum.dbf
# chmod 664 /ocfs/quorum.dbf
# chown oracle:dba /ocfs/srvm.dbf
# chmod 664 /ocfs/srvm.dbf

* Verify the Environment - Log off and log on as the oracle user
to ensure all environment variables
are set correctly. Use the following command to view them:

% env | more

Note: If you are on Redhat Advanced Server 3.0, you will need to
temporarily use an older gcc for the install:

mv gcc gcc3.2.3
mv g++ g++3.2.3
ln -s /usr/bin/gcc296 /usr/bin/gcc
ln -s /usr/bin/g++296 /usr/bin/g++

You will also need to apply patch 3006854 if on RHAS 3.0

* Before attempting to run the Oracle Universal Installer, verify
that you can successfully run the following command:

% /usr/bin/X11/xclock

* If this does not display a clock on your display screen, please
review the following article:

Note 153960.1 FAQ: X Server testing and troubleshooting
Note 153960.1 FAQ: X Server testing and troubleshooting

* Start the Oracle Universal Installer and install the RDBMS
software - Follow these procedures to use the Oracle Universal
Installer to install the Oracle Cluster Manager software. Oracle9i is
supplied on multiple CD-ROM disks. During the installation process it
is necessary to switch between the CD-ROMS. OUI will manage the
switching between CDs.

Use the following commands to start the installer:

% cd /tmp
% /cdrom/runInstaller

Or cd to /stage/Disk1 and run ./runInstaller

Respond to the installer prompts as shown below:

* At the "Welcome Screen", click Next.

If this is your first install on this machine:

* If the "Inventory Location" screen appears, enter the inventory
location then click OK.
* If the "Unix Group Name" screen appears, enter the unix group
name created in step 2.1 then click Next.
* At this point you may be prompted to run /tmp/orainstRoot.sh.
Run this and click Continue.

* At the "File Locations Screen", verify the destination listed is
your ORACLE_HOME directory. Also enter a NAME to identify this
ORACLE_HOME. The NAME can be anything.
* At the "Available Products Screen", Check "Oracle Cluster
Manager". Click Next.
* At the public node information screen, enter the public node
names and click Next.
* At the private node information screen, enter the interconnect
node names. Click Next.
* Enter the full name of the file or raw device you have created
for the ORACM Quorum disk information. Click Next.
* Press Install at the summary screen.
* You will now briefly get a progress window followed by the end
of installation screen. Click Exit and confirm by clicking Yes.

Note: Create the directory $ORACLE_HOME/oracm/log (as oracle) on the
other nodes if it doesn't exist.

2.4 Configure the hangcheck-timer

These steps need to be performed on ALL nodes.

Some kernel versions include the hangcheck-timer with the kernel. You
can check to see if your kernel contains the hangcheck-timer by
running:

# /sbin/lsmod

Then you will see hangcheck-timer listed. Also verify that
hangcheck-timer is starting in your /etc/rc.local file (on Redhat) or
/etc/init.d/boot.local (on United Linux). If you see hangcheck-timer
listed in lsmod and in the rc.local file or boot.local, you can skip
to section 2.5.

If hangcheck-timer is not listed here and you are not using Redhat
Advanced Server, see the following note for information on obtaining
the hangcheck-timer:

Note 232355.1 - Hangcheck Timer FAQ
Note 232355.1 - Hangcheck Timer FAQ

If you are on Redhat Advanced Server, you can either apply the latest
errata version (> 12) or go to MetaLink - Patches:

Enter 2594820 in the Patch Number field.

Click Go.

Click Download.

Save the file p2594820_20_LINUX.zip to the local disk, such as /tmp.

Unzip the file. The output should be similar to the following:

inflating: hangcheck-timer-2.4.9-e.3-0.4.0-1.i686.rpm

inflating: hangcheck-timer-2.4.9-e.3-enterprise-0.4.0-1.i686.rpm

inflating:
hangcheck-timer-2.4.9-e.3-smp-0.4.0-1.i686.rpm

inflating: README.TXT

Run the uname -a command to identify the RPM that corresponds to the
kernel in use. This will show if the kernel is single CPU, smp, or
enterprise.

The p2594820_20_LINUX.zip file contains four files. The following
describes the files:

hangcheck-timer-2.4.9-e.3-0.4.0-1.i686.rpm is for single CPU machines
hangcheck-timer-2.4.9-e.3-enterprise=0.4.0-1.i686.rpm is for
multi-processor machines with more than 4 GB of RAM
hangcheck-timer-2.4.9-e.3-smp-0.4.0-1.i686.rpm is for multi-processor
machines with 4 GB of RAM or less

The three RPMs will work for the e3 kernels in Red Hat Advanced Server
2.1 gold and the e8 kernels in the latest Red Hat Advanced Server 2.1
errata release. These RPMs are for Red Hat Advanced Server 2.1 kernels
only.

Transfer the relevant hangcheck-timer RPM to the /tmp directory of the
Oracle Real Applications Cluster node.

Log in to the node as the root user.

Change to the /tmp directory.

Run the following command to install the module:

#rpm -ivh hangcheck-timer RPM name

If you have previously installed RAC on this cluster, remove or
disable the mechanism that loads the softdog module at system start
up, if that module is not used by other software on the node. This is
necessary for subsequent steps in the installation process. This step
may require log in as the root user. One method for setting up
previous versions of Oracle Real Applications Clusters involved
loading the softdog module in the /etc/rc.local (on Redhat) or
/etc/init.d/boot.local (on United Linux) file. If this method was
used, then remove or comment out the following line in the file:

/sbin/insmod softdog nowayout=0 soft_noboot=1 soft_margin=60

Append the following line to the /etc/rc.local file (on Redhat) or
/etc/init.d/boot.local (on United Linux):

/sbin/insmod hangcheck-timer hangcheck_tick=30 hangcheck_margin=180

Load the hangcheck-timer kernel module using the following command as root user:

# /sbin/insmod hangcheck-timer hangcheck_tick=30 hangcheck_margin=180

Repeat the above steps on all Oracle Real Applications Clusters nodes
where the kernel module needs to be installed.

Run dmesg after the module is loaded. Note the build number while
running the command. The following is the relevant information output:

build 334adfa62c1a153a41bd68a787fbe0e9

The build number is required when making support calls.

2.5 Install Version 10.1.0.2 of the Oracle Universal Installer

These steps need to be performed on ALL nodes.

Download the 9.2.0.5 patchset from MetaLink - Patches:

Enter 3501955 in the Patch Number field.

Click Go.

Click Download.

* Place the file in a patchset directory on the node you are
installing from. Example:

$ mkdir $ORACLE_HOME/9205
$ cp p3501955_9205_LINUX.zip $ORACLE_HOME/9205

* Unzip the file:

$ cd $ORACLE_HOME/9205
$ unzip p3501955_9205_LINUX.zip
Archive: p3501955_9205_LINUX
inflating: 9205_lnx32_release.cpio
inflating: README.html
inflating: ReleaseNote9205.pdf

* Run CPIO against the file:

$ cpio -idmv < 9205_lnx32_release.cpio

Run the installer from the 9.2.0.5 staging location:

$ cd $ORACLE_HOME/9205/Disk1
$ ./runInstaller

Respond to the installer prompts as shown below:

* At the "Welcome Screen", click Next.
* At the "File Locations Screen", Change the $ORACLE_HOME name
from the dropdown list to the 9.2 $ORACLE_HOME name. Click Next.
* On the "Available Products Screen", Check "Oracle Universal
Installer 10.1.0.2. Click Next.
* Press Install at the summary screen.
* You will now briefly get a progress window followed by the end
of installation screen. Click Exit and confirm by clicking Yes.

Remember to install the 10.1.0.2 Installer on ALL cluster nodes. Note
that you may need to ADD the 9.2 $ORACLE_HOME name on the "File
Locations Screen" for other nodes. It will ask if you want to specify
a non-empty directory, say "Yes".

2.6 Run the 10.1.0.2 Oracle Universal Installer to patch the Oracle
Cluster Manager (ORACM) to 9.2.0.5

These steps only need to be performed on the node that you are
installing from (typically Node 1).

The 10.1.0.2 OUI will use SSH (Secure Shell) if it is configured. If
it is not configured it will use RSH (Remote Shell). If you have SSH
configured on your cluster, test and make sure that you can SSH and
SCP to all nodes of the cluster without being prompted. If you do not
have SSH configured, skip this step and run the installer from
$ORACLE_BASE/oui/bin as noted below.

SSH Test:

As oracle user, check for user equivalence for the oracle account by
performing a secure copy (scp) to each node (public and private) in
the cluster. Example:

RAC1:

$ touch /u01/sshtest
$ scp /u01/sshtest rac2:/u01/sshtest1
$ scp /u01/sshtest int-rac2:/u01/sshtest2

RAC2:

$ touch /u01/sshtest
$ scp /u01/sshtest rac1:/u01/sshtest1
$ scp /u01/sshtest int-rac1:/u01/sshtest2
$ ls /u01/sshtest*
/u01/sshtest /u01/sshtest1 /u01/sshtest2

RAC1:

$ ls /u01/sshtest*
/u01/sshtest /u01/sshtest1 /u01/sshtest2

Run the installer from the 9.2.0.5 oracm staging location:

$ cd $ORACLE_HOME/9205/Disk1/oracm
$ ./runInstaller

Respond to the installer prompts as shown below:

* At the "Welcome Screen", click Next.
* At the "File Locations Screen", make sure the source location is
to the products.xml file in the 9.2.0.5 patchset location under
Disk1/stage. Also verify the destination listed is your ORACLE_HOME
directory. Change the $ORACLE_HOME name from the dropdown list to the
9.2 $ORACLE_HOME name. Click Next.
* At the "Available Products Screen", Check "Oracle9iR2 Cluster
Manager 9.2.0.5.0". Click Next.
* At the public node information screen, enter the public node
names and click Next.
* At the private node information screen, enter the interconnect
node names. Click Next.
* Click Install at the summary screen.
* You will now briefly get a progress window followed by the end
of installation screen. Click Exit and confirm by clicking Yes.

2.7 Modify the ORACM configuration files to utilize the hangcheck-timer.

These steps need to be performed on ALL nodes.

Modify the $ORACLE_HOME/oracm/admin/cmcfg.ora file:

Add the following line:

KernelModuleName=hangcheck-timer

Adjust the value of the MissCount line based on the sum of the
hangcheck_tick and hangcheck_margin values. (> 210)

MissCount=210

Make sure that you can ping each of the names listed in the private
and public node name sections from each node. Example:

$ ping rac2
PING opcbrh2.us.oracle.com (138.1.137.46) from 138.1.137.45 :
56(84) bytes of data.
64 bytes from opcbrh2.us.oracle.com (138.1.137.46): icmp_seq=0
ttl=255 time=1.295 msec
64 bytes from opcbrh2.us.oracle.com (138.1.137.46): icmp_seq=1
ttl=255 time=154 usec

Verify that a valid CmDiskFile line exists in the following format:

CmDiskFile=file or raw device name

In the preceding command, the file or raw device must be valid. If a
file is used but does not exist, then the file will be created if the
base directory exists. If a raw device is used, then the raw device
must exist and have the correct ownership and permissions. Sample
cmcfg.ora file:

ClusterName=Oracle Cluster Manager, version 9i
MissCount=210
PrivateNodeNames=int-rac1 int-rac2
PublicNodeNames=rac1 rac2
ServicePort=9998
CmDiskFile=/u04/quorum.dbf
KernelModuleName=hangcheck-timer
HostName=int-rac1

Note: The cmcfg.ora file should be the same on both nodes with the
exception of the HostName parameter which should be set to the local
(internal) hostname.

Make sure all of these changes have been made to all RAC nodes. More
information on ORACM parameters can be found in the following note:

Note 222746.1 - RAC Linux 9.2: Configuration of cmcfg.ora
andNote 222746.1 - RAC Linux 9.2: Configuration of cmcfg.ora and
ocmargs.ora

Note: At this point it would be a good idea to patch to the latest
ORACM, especially if you have more than 2 nodes. For more information
see:

Note 278156.1 - ORA-29740 or ORA-29702 After Applying 9.2.0.5 Patchset
on RAC / Linux
ORA-29740 or ORA-29702 After Applying 9.2.0.5 Patchset on RAC / Linux

2.8 Start the ORACM (Oracle Cluster Manager)

These steps need to be performed on ALL nodes.

Cd to the $ORACLE_HOME/oracm/bin directory, change to the root user,
and start the ORACM.

$ cd $ORACLE_HOME/oracm/bin
$ su root
# ./ocmstart.sh
oracm &1 >/u01/app/oracle/product/9.2.0/oracm/log/cm.out &

Verify that ORACM is running with the following:

# ps -ef | grep oracm

On RHEL 3.0, add the -m option:

# ps -efm | grep oracm

You should see several oracm threads running. Also verify that the
ORACM version is the same on each node:

# cd $ORACLE_HOME/oracm/log
# head -1 cm.log
oracm, version[ 9.2.0.2.0.49 ] started {Fri May 14 09:22:28 2004 }

3.0 Installing RAC

The Real Application Clusters installation process includes four major tasks.

1. Install 9.2.0.4 RAC.
2. Patch the RAC Installation to 9.2.0.5.
3. Start the GSD.
4. Create and configure your database.

3.1 Install 9.2.0.4 RAC

These steps only need to be performed on the node that you are
installing from (typically Node 1).

Note: Due to bug 3547724, temporarily create a symbolic link /oradata
directory pointing to an oradata directory with space available as
root prior to running the RAC install:

# mkdir /u04/oradata
# chmod 777 /u04/oradata
# ln -s /u04/oradata /oradata

Install 9.2.0.4 RAC into your $ORACLE_HOME by running the installer
from the 9.2.0.4 cd or your original stage location for the 9.2.0.4
install.

Use the following commands to start the installer:

% cd /tmp
% /cdrom/runInstaller

Or cd to /stage/Disk1 and run ./runInstaller

Respond to the installer prompts as shown below:

* At the "Welcome Screen", click Next.
* At the "Cluster Node Selection Screen", make sure that all RAC
nodes are selected.
* At the "File Locations Screen", verify the destination listed is
your ORACLE_HOME directory and that the source directory is pointing
to the products.jar from the 9.2.0.4 cd or staging location.
* At the "Available Products Screen", check "Oracle 9i Database
9.2.0.4". Click Next.
* At the "Installation Types Screen", check "Enterprise Edition"
(or whichever option your prefer), click Next.
* At the "Database Configuration Screen", check "Software Only".
Click Next.
* At the "Shared Configuration File Name Screen", enter the path
of the CFS or NFS srvm file created at the beginning of step 2.3 or
the raw device created for the shared configuration file. Click Next.
* Click Install at the summary screen. Note that some of the
items installed will say "9.2.0.1" for the version, this is normal
because only some items needed to be patched up to 9.2.0.4.
* You will now get a progress window, run root.sh when prompted.
* You will then see the end of installation screen. Click Exit and
confirm by clicking Yes.

Note: You can now remove the /oradata symbolic link:

# rm /oradata

3.2 Patch the RAC Installation to 9.2.0.5

These steps only need to be performed on the node that you are installing from.

Run the installer from the 9.2.0.5 staging location:

$ cd $ORACLE_HOME/9205/Disk1
$ ./runInstaller

Respond to the installer prompts as shown below:

* At the "Welcome Screen", click Next.
* View the "Cluster Node Selection Screen", click Next.
* At the "File Locations Screen", make sure the source location is
to the products.xml file in the 9.2.0.5 patchset location under
Disk1/stage. Also verify the destination listed is your ORACLE_HOME
directory. Change the $ORACLE_HOME name from the dropdown list to the
9.2 $ORACLE_HOME name. Click Next.
* At the "Available Products Screen", Check "Oracle9iR2 PatchSets
9.2.0.5.0". Click Next.
* Click Install at the summary screen.
* You will now get a progress window, run root.sh when prompted.
* You will then see the end of installation screen. Click Exit and
confirm by clicking Yes.

3.3 Start the GSD (Global Service Daemon)

These steps need to be performed on ALL nodes.

Start the GSD on each node with:

% gsdctl start
Successfully started GSD on local node

Then check the status with:

% gsdctl stat
GSD is running on the local node

If the GSD does not stay up, try running 'srvconfig -init -f' from the
OS prompt. If you get a raw device exception error or PRKR-1064 error
then see the following note to troubleshoot:

Note 212631.1 - Resolving PRKR-1064 in a RAC Environment
Note 212631.1 - Resolving PRKR-1064 in a RAC Environment

Note: After confirming that GSD starts, if you are on Redhat Advanced
Server 3.0, restore gcc296:

rm /usr/bin/gcc
mv /usr/bin/gcc3.2.3 /usr/bin/gcc
rm /usr/bin/g++
mv /usr/bin/g++3.2.3 /usr/bin/g++

3.4 Create a RAC Database using the Oracle Database Configuration Assistant

These steps only need to be performed on the node that you are
installing from (typically Node 1).

The Oracle Database Configuration Assistant (DBCA) will create a
database for you. The DBCA creates your database using the optimal
flexible architecture (OFA). This means the DBCA creates your database
files, including the default server parameter file, using standard
file naming and file placement practices. The primary phases of DBCA
processing are:-

* Verify that you correctly configured the shared disks for each
tablespace (for non-cluster file system platforms)
* Create the database
* Configure the Oracle network services
* Start the database instances and listeners

Oracle Corporation recommends that you use the DBCA to create your
database. This is because the DBCA preconfigured databases optimize
your environment to take advantage of Oracle9i features such as the
server parameter file and automatic undo management. The DBCA also
enables you to define arbitrary tablespaces as part of the database
creation process. So even if you have datafile requirements that
differ from those offered in one of the DBCA templates, use the DBCA.
You can also execute user-specified scripts as part of the database
creation process.

Note: Prior to running the DBCA it may be necessary to run the NETCA
tool or to manually set up your network files. To run the NETCA tool
execute the command netca from the $ORACLE_HOME/bin directory. This
will configure the necessary listener names and protocol addresses,
client naming methods, Net service names and Directory server usage.

If you are using OCFS or NFS, launch DBCA with the
-datafileDestination option and point to the shared location where
Oracle datafiles will be stored. Example:

% cd $ORACLE_HOME/bin
% dbca -datafileDestination /ocfs/oradata

If you are using RAW, launch DBCA without the -datafileDestination
option. Example:

% cd $ORACLE_HOME/bin
% dbca

Respond to the DBCA prompts as shown below:

* Choose Oracle Cluster Database option and select Next.
* The Operations page is displayed. Choose the option Create a
Database and click Next.
* The Node Selection page appears. Select the nodes that you want
to configure as part of the RAC database and click Next.
* The Database Templates page is displayed. The templates other
than New Database include datafiles. Choose New Database and then
click Next. Note: The Show Details button provides information on the
database template selected.
* DBCA now displays the Database Identification page. Enter the
Global Database Name and Oracle System Identifier (SID). The Global
Database Name is typically of the form name.domain, for example
mydb.us.oracle.com while the SID is used to uniquely identify an
instance (DBCA should insert a suggested SID, equivalent to name1
where name was entered in the Database Name field). In the RAC case
the SID specified will be used as a prefix for the instance number.
For example, MYDB, would become MYDB1, MYDB2 for instance 1 and 2
respectively.
* The Database Options page is displayed. Select the options you
wish to configure and then choose Next. Note: If you did not choose
New Database from the Database Template page, you will not see this
screen.
* Select the connection options desired from the Database
Connection Options page. Click Next.
* DBCA now displays the Initialization Parameters page. This page
comprises a number of Tab fields. Modify the Memory settings if
desired and then select the File Locations tab to update information
on the Initialization Parameters filename and location. The option
Create persistent initialization parameter file is selected by
default. If you have a cluster file system, then enter a file system
name, otherwise a raw device name for the location of the server
parameter file (spfile) must be entered. The button File Location
Variables… displays variable information. The button All
Initialization Parameters… displays the Initialization Parameters
dialog box. This box presents values for all initialization parameters
and indicates whether they are to be included in the spfile to be
created through the check box, included (Y/N). Instance specific
parameters have an instance value in the instance column. Complete
entries in the All Initialization Parameters page and select Close.
Note: There are a few exceptions to what can be altered via this
screen. Ensure all entries in the Initialization Parameters page are
complete and select Next.
* DBCA now displays the Database Storage Window. This page allows
you to enter file names for each tablespace in your database.
* The Database Creation Options page is displayed. Ensure that the
option Create Database is checked and click Finish.
* The DBCA Summary window is displayed. Review this information
and then click OK. Once you click the OK button and the summary
screen is closed, it may take a few moments for the DBCA progress bar
to start. DBCA then begins to create the database according to the
values specified.

During the database creation process, you may see the following error:

ORA-29807: specified operator does not exist

This is a known issue (bug 2925665). You can click on the "Ignore"
button to continue. Once DBCA has completed database creation,
remember to run the 'prvtxml.plb' script from $ORACLE_HOME/rdbms/admin
independently, as the user SYS. It is also advised to run the
'utlrp.sql' script to ensure that there are no invalid objects in the
database at this time.

A new database now exists. It can be accessed via Oracle SQL*PLUS or
other applications designed to work with an Oracle RAC database.

Additional database configuration best practices can be found in the
following note:

Note 240575.1 - RAC on Linux Best Practices
Note 240575.1 - RAC on Linux Best Practices

4.0 Administering Real Application Clusters Instances

Oracle Corporation recommends that you use SRVCTL to administer your
Real Application Clusters database environment. SRVCTL manages
configuration information that is used by several Oracle tools. For
example, Oracle Enterprise Manager and the Intelligent Agent use the
configuration information that SRVCTL generates to discover and
monitor nodes in your cluster. Before using SRVCTL, ensure that your
Global Services Daemon (GSD) is running after you configure your
database. To use SRVCTL, you must have already created the
configuration information for the database that you want to
administer. You must have done this either by using the Oracle
Database Configuration Assistant (DBCA), or by using the srvctl add
command as described below.

To display the configuration details for, example, databases racdb1/2,
on nodes racnode1/2 with instances racinst1/2 run:-

$ srvctl config
racdb1
racdb2

$ srvctl config -p racdb1 -n racnode1
racnode1 racinst1 /u01/app/oracle/product/9.2.0

$ srvctl status database -d racdb1
Instance racinst1 is running on node racnode1
Instance racinst2 is running on node racnode2

Examples of starting and stopping RAC follow:-

$ srvctl start database -d racdb2

$ srvctl stop database -d racdb2

$ srvctl stop instance -d racdb1 -i racinst2

$ srvctl start instance -d racdb1 -i racinst2

For further information on srvctl and gsdctl see the Oracle9i Real
Application Clusters Administration manual.

5.0 References

* 9.2.0.5 Patch Set Notes
* Tips for Installing and Configuring Oracle9i RealTips for
Installing and Configuring Oracle9i Real Application Clusters on Red
Hat Linux Advanced Server
* Note 201370.1 - LINUX Quick Start Guide - 9.2.0 RDBMSNote
201370.1 - LINUX Quick Start Guide - 9.2.0 RDBMS Installation
* Note 252217.1 - Requirements for Installing Oracle 9iR2 on RHEL3
* Note 240575.1 - RAC on Linux Best Practices
* Note 240575.1 - RAC on Linux Best Practices Note 222746.1 - RAC
Linux 9.2: Configuration of cmcfg.oraNote 222746.1 - RAC Linux 9.2:
Configuration of cmcfg.ora and ocmargs.ora
* Note 212631.1 - Resolving PRKR-1064 in a RAC EnvironmentNote
212631.1 - Resolving PRKR-1064 in a RAC Environment
* Note 220178.1 - Installing and setting up ocfs on Linux -Note
220178.1 - Installing and setting up ocfs on Linux - Basic Guide
* Note 246205.1 - Configuring Raw Devices for RealNote 246205.1 -
Configuring Raw Devices for Real Application Clusters on Linux
* Note 210889.1 - RAC Installation with a NetApp Filer inNote
210889.1 - RAC Installation with a NetApp Filer in Red Hat Linux
Environment
* Note 153960.1 FAQ: X Server testing and troubleshootingNote
153960.1 FAQ: X Server testing and troubleshooting
* Note 232355.1 - Hangcheck Timer FAQ
* Note 232355.1 - Hangcheck Timer FAQ RAC/Linux certification matrix
* RAC/Linux certification matrix Oracle9i Real Application
Clusters AdministrationOracle9i Real Application Clusters
Administration
* Oracle9i Real Application Clusters Concepts
* Oracle9i Real Application Clusters Concepts Oracle9i Real
Application Clusters Deployment andOracle9i Real Application Clusters
Deployment and Performance
* Oracle9i Real Application Clusters Setup andOracle9i Real
Application Clusters Setup and Configuration
* Oracle9i Installation Guide Release 2 for UNIXOracle9i
Installation Guide Release 2 for UNIX Systems: AIX-Based Systems,
Compaq Tru64 UNIX, HP 9000 Series HP-UX, Linux Intel, and Sun Solaris