Oracle 9i to 10g Upgrade

1) Install Oracle10g Binaries on the host and upgrade to latest version to 10g.

2) Check for invalid objects and run utlrp.sql to recompile

SQL> SELECT OWNER, OBJECT_TYPE, STATUS, COUNT(*)
FROM DBA_OBJECTS WHERE STATUS<>’VALID’
GROUP BY OWNER, OBJECT_TYPE, STATUS;

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql -- 9i Oracle home

3) Connect to Oracle 9i database and run the below script from new 10g oracle
home

SQL> $ORACLE_HOME/rdbms/admin/utlu102i.sql -- 10g Oracle home

4) Run the below sql, which gives all the user having connect privilege on
oracle9i. Preserve the output

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');

5) Connect to 9i database and create pfile from spfile

SQL> create pfile from spfile;

6) Take backup of database

7) Edit pfile to add below listed 10g parameters and remove/comments the
deprecated parameters

AQ_TM_PROCESSES=0 -- If specified
Set streams_pool_size = 50MB
Set session_max_open_files = 20
Set max_enabled_roles = 50
db_cache_size = 200M (at the least)
. SHARED_POOL_SIZE=150M (at the least)
JAVA_POOL_SIZE=150M (at the least)
LARGE_POOL_SIZE=150M (at the least)

8) Shutdown database and create spfile from pfile

SQL> create spfile from pfile;

9) Add the entry to /etc/oratab file and recompile/relogin to set the oracle 10g
home

ORCLDB:/u01/app/oracle/product/10.2.0/db_1:N

10) Copy spfile to Oracle 10g home dbs directory

$ cp /oracle/9i/9.2.0.6/dbs/spfileorcldb.ora $ORACLE_HOME/dbs

11) Start upgrade database and make sure it is the right database

$sqlplus sys as sysdba
SQL> startup upgrade
SQL> SELECT NAME FROM V$DATABASE;

12) Add SYSAUX tablespace and ensure adequate space in SYSTEM tablespace (set to
2GB and autoextend on)

SQL> CREATE TABLESPACE sysaux
2 DATAFILE '/oracle/oradata/ORCLDB/sysaux01.dbf'
3 SIZE 300M REUSE AUTOEXTEND ON
4 EXTENT MANAGEMENT LOCAL
5 SEGMENT SPACE MANAGEMENT AUTO
6 ONLINE;

SQL> ALTER TABLESPACE SYSTEM AUTOEXTEND ON;

13) Run catupgrd.sql script as sysdba

SQL> spool /home/oracle/upgrade_orcldb.log
SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql

14) Shutdown and startup database

SQL> shutdown immediate
SQL> startup

15) Run utlrp.sql to recompile any invalid objects

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

SQL> SELECT OWNER, OBJECT_TYPE, STATUS, COUNT(*)
FROM DBA_OBJECTS WHERE STATUS<>’VALID’
GROUP BY OWNER, OBJECT_TYPE, STATUS;

16) Change AQ_TM_PROCESSES,COMPATIBLE parameters and ensure log_archive_format
has %T,%S,%R

SQL> alter system set AQ_TM_PROCESSES=2 scope=spfile;
SQL> alter system set COMPATIBLE=10.2.0.3 scope=spfile;

17) Shutdown and startup to enable parameters

SQL> shutdown immediate
SQL> startup

18) Modify the listener.ora file with new ORACLE_HOME and start the listener

19) Recreate password file

$ orapwd file=orapwORCLDB password=password entries=5

20) Check remote connectivity

$ sqlplus sys@ORCLDB as sysdba

21) Grant the below privileges to all the users which you have collected at step
4

GRANT CREATE VIEW TO <USERNAME>;
GRANT CREATE TABLE TO <USERNAME>;
GRANT ALTER SESSION TO <USERNAME>;
GRANT CREATE CLUSTER TO <USERNAME>;
GRANT CREATE SESSION TO <USERNAME>;
GRANT CREATE SYNONYM TO <USERNAME>;
GRANT CREATE SEQUENCE TO <USERNAME>;
GRANT CREATE DATABASE LINK TO <USERNAME>;

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

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_NATIVE_LIBRARY_DIR = ""
PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT = 0