=================================================================
91 to 10g upgrade
==============
http://download.oracle.com/docs/cd/B14117_01/server.101/b10763/upgrade.htm
http://www.oracle-base.com/articles/10g/UpgradingTo10g.php
http://beginapps.blogspot.com/2008/04/oracle-9i-to-10gr2-manual-upgrade-steps.html
==================================================================
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
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 9i 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 privileges for 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:
Check for invalid objects in the database:
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 as a user with SYSDBA privs using SQL*Plus and then requery invalid objects:
% sqlplus '/as sysdba'
SQL> @?/rdbms/admin/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'
If you are upgrading from Oracle9iR2 (9.2), verify that the view dba_registry contains data. If the view is empty, run the following scripts from the 9.2 home:
% sqlplus '/as sysdba'
SQL> @?/rdbms/admin/catalog.sql
SQL> @?/rdbms/admin/catproc.sql
SQL> @?/rdbms/admin/utlrp.sql
and verify that the dba_registry view now contains data.
Step 5:
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 6:
Ensure no files are in backup mode:
SQL> select * from v$backup where status!='NOT ACTIVE';
This should return no rows.
Step 7:
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 8:
Disable all batch and cron jobs.
Step 9:
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 10:
Ensure that the aud$ is in the system tablespace when auditing is enabled.
SQL> select tablespace_name from dba_tables where table_name='AUD$';
Step 11:
Note down where all control files are located.
SQL> select * from v$controlfile;
Step 12:
Shutdown the database
$ sqlplus '/as sysdba'
SQL> shutdown immediate;
Step 13:
Perform a full cold backup
Upgrading to the New Oracle Database 10g Release 2
Step 14:
Update the init.ora file:
- Make a backup of the old init.ora file
On Unix/Linux, the default location of the file is the $ORACLE_HOME/dbs directory
- Comment out any obsoleted parameters.
- Change all deprecated parameters.
Step 15:
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.
Step 16:
Update the oratab entry, to set the new ORACLE_HOME and disable automatic startup:
SID:ORACLE_HOME:N
Step 17:
Update the environment variables like ORACLE_HOME and PATH
$. oraenv
Step 18:
Make sure the following environment variables point to the new release (10g) 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
Step 19:
Startup upgrade the database:
$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus / as sysdba
Use Startup with the UPGRADE option:
SQL> startup upgrade
Step 20:
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 'path/sysaux01.dbf'
SIZE 500M REUSE
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
ONLINE;
Step 21:
Run the catupgrd.sql script, spooling the output so you can check whether any errors occured and investigate them
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
or installs the following database components in the new release 10.2 database:
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 22:
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.
Step 23:
Restart the database:
SQL> shutdown immediate (DO NOT use "shutdown abort" !!!)
SQL> startup restrict
Step 24:
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 4.
After Upgrading a Database
Step 25:
Modify the listener.ora file:
For the upgraded instance(s) modify the ORACLE_HOME parameter to point to the new ORACLE_HOME
Step 26:
Start the listener
$ lsnrctl
LSNRCTL> start
Step 27:
Enable cron and batch jobs
Step 28:
Change oratab entry to use automatic startup
SID:ORACLE_HOME:Y