Oracle Database Migration From 8.0.6 to 9i

1. --------------------------------------------------------

Upgrade path for Oracle 8 (8.0.x): If your old release version is 8.0.5 or less (i.e 8.0.4 or 8.0.3), then direct upgrade is
NOT supported. You must first upgrade this version to 8.0.6. After the upgrade to 8.0.6 or your version IS 8.0.6 , you can
directly upgrade your database to Oracle9i Rel2.

What version is running? What option is installed?

Select * from v$version;
Select * from v$option;

2. ---------------------------------------------------------

PERFORM a Full cold backup!!!!!!!

3. ---------------------------------------------------------

Avoid running out of space during the migration:

- Prepare the system rollback segment:

Alter rollback segment system storage (maxextents 121 next 1M);

- Ensure plenty of free space in the SYSTEM tablespace. A minimum of 150 Mb additional free space:

Select max(bytes) from dba_free_space where tablespace_name='SYSTEM';

- Ensure plenty of free space in the ROLLBACK tablespace. Ensure that you have at least 1 rollback segment of 70 Mb if the
number of objects in the database exceeds 5000:

Select count(*) from dba_objects;

4. ------------------------------------------------------------

Verify the certification of oracle 9i on the OS version you are using. Verify all necessary OS patches are installed. Example
for Solaris:

$ showrev -p

You can also check the installation in Note 169706.1

5. -------------------------------------------------------------

Upgrade will leave all objects (packages,views,...) invalid, except for tables. All other objects must be recompiled manually. List all objects that are not VALID before the upgrade. This list of fatal objects.

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

To create a script to compile all invalid objects, before upgrading, run the the script called utlrp.sql in the
$ORACLE_HOME/rdbms/admin directory.

This script recompiles all invalid PL/SQL in the database including views.

$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus sys/ as sysdba
SQL> @utlrp.sql

Run the script and than rerun the query to get 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

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

There should be not dictionary objects invalid.

6. ---------------------------------------------------------------

Verify the kernel parameters according to the installation guide of the
new version.

Example for Solaris:
$ cat /etc/system

7. -------------------------------------------------------------------

Ensure ORACLE_SID is set to instance you want to upgrade.


8. ------------------------------------------------------------------

For all information regarding the national characterset,

please refer to Note 276914.1

Before proceeding please check the column names involved with Note 278725.1

As of Oracle 9i the National Characterset (NLS_NCHAR_CHARACTERSET)
will be limited to UTF8 and AL16UTF16.

Note 276914.1 The National Character Set in Oracle 9i and 10g

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

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

( so if: select distinct OWNER, TABLE_NAME from DBA_TAB_COLUMNS where
('SYS','SYSTEM'); returns no rows, go to point 9.)

select distinct OWNER, TABLE_NAME from DBA_TAB_COLUMNS where
DATA_TYPE in ('CHAR','VARCHAR2', 'CLOB') and OWNER not in

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

SQL> select * from nls_database_parameters where parameter

9. -----------------------------------------------------------------------

If you are upgrading from the 8.0.6 release check no users or roles called either MIGRATE or OUTLN.

Select * from dba_users where username in ('MIGRATE','OUTLN');
Select * from dba_roles where role in ('MIGRATE','OUTLN');

10. ------------------------------------------------------------------------

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

Set verify off
Set space 0
Set heading off
Set feedback off
Set pages 1000
Spool analyze.sql
Select 'Analyze '||object_type||' '||object_name
||' validate structure;'
from dba_objects
where owner='SYS'
and object_type in ('INDEX','TABLE','CLUSTER');
spool off
This creates a script called analyze.sql.
Run the script.

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

11. ----------------------------------------------------------------------------

Ensure that all Snapshot refreshes are successfully completed.
And replication is stopped.

$ Sqlplus SYS/

Select distinct(trunc(last_refresh)) from dba_snapshot_refresh_times;

12. ----------------------------------------------------------------------------

Stop the listener for the database

$ lsnrctl

Lsnrctl> stop

13. ----------------------------------------------------------------------------

Ensure no files need media recovery:

$ sqlplus SYS/

Select * from v$recover_file;

This should return no rows

14. ----------------------------------------------------------------------------

Ensure no files are in backup mode:

Select * from v$backup where status!='NOT ACTIVE';

This should return no rows.

15. ----------------------------------------------------------------------------

Resolve any outstanding unresolved distributed transaction:

Select * from dba_2pc_pending;

If this returns rows you should do the following:

Select local_tran_id from dba_2pc_pending;
Execute dbms_transaction.purge_lost_db_entry('');

16. ----------------------------------------------------------------------------

Disable all batch and cron jobs.

17. ----------------------------------------------------------------------------

Ensure the users sys and system have 'system' as their default tablespace.

Select username, default_tablespace from dba_users where username
in ('SYS','SYSTEM');

To modify use:
Alter user sys default tablespace SYSTEM;
Alter user system default tablespace SYSTEM;

18. ----------------------------------------------------------------------------

Optionally ensure the aud$ is in the system tablespace when auditing is enabled.

Select tablespace_name from dba_tables where table_name='AUD$';

19. ----------------------------------------------------------------------------

Note down where all control files are located.

Select * from v$controlfile;

20. ----------------------------------------------------------------------------

Note down all sysdba users.

Select * from v$pwfile_users;

If a passwordfile is used copy it to the new location. On Unix the default
is $ORACLE_HOME/dbs/orapw.

On Windows NT this is %ORACLE_HOME%\database\orapw

21. ----------------------------------------------------------------------------

Shutdown the database

$ sqlplus SYS/
SQL> Shutdown immediate

22. ----------------------------------------------------------------------------

Change the init.ora file:

- Make a backup of the init.ora file.
- Verify that the parameter DB_DOMAIN is set properly.
are set to an explicit directory
- Set the parameter _SYSTEM_TRIG_ENABLED explicitly to FALSE during the upgrade
- Set the parameter OPTIMIZER_MODE to CHOOSE during the upgrade
- Either leave COMPATIBLE unset in your initialization parameter file or
set COMPATIBLE to 8.1.x. Setting this parameter a lower or a higher value
than 8.1.X results in an error during the upgrade.
- Ensure that the shared_pool_size and the large_pool_size are at least 150Mb

23. ----------------------------------------------------------------------------

Check for adequate freespace on archive log destination file systems.

24. ----------------------------------------------------------------------------

Ensure the NLS_LANG variable is set correctly:
$ echo $NLS_LANG

bash-2.05b$ echo $NLS_LANG

25. ----------------------------------------------------------------------------

If needed copy the listener.ora and the tnsnames.ora to the new location
(when no TNS_ADMIN env. Parameter is used)

cp $ORACLE_HOME/network/admin /network/admin

26. ----------------------------------------------------------------------------
If your Operating system is Windows NT, delete your services
With the ORADIM of your old oracle version.

For Oracle 8.0 this is:

For Oracle8i or higher this is:
And create the new Oracle9i service use ORADIM of the Oracle9i ORACLE_HOME:

27. ----------------------------------------------------------------------------

If needed copy the init.ora file to the new oracle_home or
Create a link to the init.ora.

cp $OLD_ORACLE_HOME/dbs/init.ora $NEW_ORACLE_HOME/dbs/init.ora


ln -s /init/ora/file/path/init.ora $ORACLE_HOME/dbs/init.ora

Also check 'ifile' parameters in the init.ora, to be set to the correct file.
if an IFILE is used, verify the above mentioned parameter for the init.ora
and copy this to the correct location. Change the IFILE entry in the init.ora
file when this file changes from location.

28. ----------------------------------------------------------------------------

Update the oratab entry, to set the new ORACLE_HOME and disable automatic


29. ----------------------------------------------------------------------------

Update the environment variables like ORACLE_HOME and PATH

$ . oraenv

30. ----------------------------------------------------------------------------

Make sure the following enviroment variables point to the new
Release directories:


For HP-UX systems verify the SHLIB_PATH parameter points to the new release

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

$ env | grep SHLIB_PATH

31. ----------------------------------------------------------------------------

Run the upgrade script:
$ cd $ORACLE_HOME/rdbms/admin
Sqlplus /nolog

SQL> connect sys/passwd_for_sys as sysdba

Use Startup MIGRATE when you are upgrading to Oracle 9.2:

SQL> startup migrate

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

SQL> spool upgrade.log

Run the appropriate script for your version.

From To: Only Script to Run
==== === ==================
8.0.6 9.0.1 u0800060.sql
8.0.6 9.2 u0800060.sql
8.1.5 9.0.1 u0801050.sql
8.1.5 9.2 Not Supported
8.1.6 9.0.1 u0801060.sql
8.1.6 9.2 Not Supported
8.1.7 9.0.1 u0801070.sql
8.1.7 9.2 u0801070.sql
9.0.1 9.2 u0900010.sql

Each of these scripts is a direct upgrade path from the version you are
on to Oracle9i. You do not need to run catalog.sql and catproc.sql as these
two scripts are called from within the upgrade script.

The remainder of this step is only valid for upgrades towards Oracle 9.2:

Display the contents of the component registry to determine which components

SQL> Select comp_name, version, status from dba_registry;

Run the script cmpdbmig.sql to upgrade the components which can be upgrade
with the SYSDBA privilege:

SQL> @cmpdbmig.sql

The components upgraded by this script are:
Jserver JAVAVM, oracle XDK for Java, Oracle 9i RAC, Oracle Data Mining,
OLAP analytical Workspace, Oracle 9i Java Packages, Messaging Gateway,
Oracle Workspace Manager, OLAP Catalog, Oracle Label Security.

Display the components which were upgraded:

SQL> Select comp_name, version, status from dba_registry;

End the spool of the upgrade:

SQL> Spool Off

32. ----------------------------------------------------------------------------

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.

33. ----------------------------------------------------------------------------

Run script to recompile invalid pl/sql modules:
SQL> @utlrp

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

There should be no dictionary objects invalid.

34. ----------------------------------------------------------------------------

Edit init.ora file:

35. ----------------------------------------------------------------------------

Shutdown the database and startup the database.
$ sqlplus /nolog
SQL> Connect sys/passwd_for_sys as sysdba
SQL> Shutdown
SQL> Startup restrict

36. ----------------------------------------------------------------------------

For all information regarding the national characterset,
please refer to Note 276914.1

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

select distinct OWNER, TABLE_NAME from DBA_TAB_COLUMNS where
did not return rows in point 8 of this note.

then simply:
$ sqlplus /nolog
SQL> connect sys/passwd_for_sys as sysdba
SQL> shutdown immediate
and goto step 37.

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 simply:
$ sqlplus /nolog
SQL> connect sys/passwd_for_sys as sysdba
SQL> shutdown immediate
go to step 37.

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

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

$ sqlplus /nolog
SQL> connect sys/passwd_for_sys as sysdba
SQL> @utlnchar.sql
SQL> shutdown immediate

37. ----------------------------------------------------------------------------

Now edit the init.ora:

- put back the old value for the JOB_QUEUE_PROCESSES parameter
- put back the old value for the AQ_TM_PROCESSES parameter
- 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

38. ----------------------------------------------------------------------------

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.

39. ----------------------------------------------------------------------------

Modify the listener.ora file:

For the upgraded intstance(s) modify the ORACLE_HOME parameter
to point to the new ORACLE_HOME.

40. ----------------------------------------------------------------------------

Start the listener
$ lsnrctl

LSNRCTL> start

41. ----------------------------------------------------------------------------

Enable cron and batch jobs

42. ----------------------------------------------------------------------------

Change oratab entry to use automatic startup

43. ----------------------------------------------------------------------------

To use the new features in 9i change the compatible parameter to the new release.
When everything is well tested, update the compatible parameter in the init.ora and restart to the new release number.
COMPATIBLE=9.0.X where x is the release number

Doc ID: Note: 159657.1


Microsoft Office 2007 said...

NEVERTHELESS, THE CIVIL LAW is and must be neutral about who has a more noble or rewarding faith. The breakaway parishes ought to win every Office 2010facet of the lawsuit not becauseMicrosoft Office 2010 their beliefs or their politics are better, Microsoft wordbut because both lawOffice 2007and equity, along with common sense, are on Microsoft Officetheir side.Microsoft Office 2007 Not only does Virginia state law (the Division Statute)Office 2007 keyexplicitly apply to just such a Office 2007 downloadsituation as now exists, but the history Office 2007 Professionalespecially of The Falls Church argues against the claims of Outlook 2010the Virginia Diocese with which theyMicrosoft outlookhave disassociated.Microsoft outlook 2010First, The Falls Church wasWindows 7 founded, formed, and developed long before the diocese, or the national Episcopal Church, even existed.

Sridevi Koduru said...

Sridevi Koduru (Senior Oracle Apps Trainer
LinkedIn profile -
Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, Oracle Manufacturing, OAF, ADF, SQL, PL/SQL, D2K at | +91 - 9581017828.