Upgrading from 8.1.5 to 9.0.1

[oravijay]

Hi,

I have been asked to prepare an estimate for upgrading the database from 8.1.5 to Oracle 9.0.1.
I should also prepare an document which explains about the whole process right from migrating the
database till testing the aplication.

I am basically looking for the steps for performing this upgradation, do's and dont's etc.
It will be great if any of you can share the steps or share the document which you had prepared
before doing the migration. You can send your inputs to
oravijay@rediffmail.com.

Thanks in advance.
Vijay.

[samdba]

You should definately get it from Metalink. Do you have access to it?

[oravijay]

Hi,

I do have access to it. Infact I searched on metalink, but I was not able to find any document which was suitable to my situation now.

Vijay.

[jmodic]

http://otn.oracle.com/docs/products/...a90191/toc.htm

[sanjay92]

Recently, I have done migration from 8.1.5 to 9i on Sun Solaris. I have used Oracle Database Migtaion Assistant to do this. Metalink does provide document which is basically used to Migrate 8i database to 9i manually. Using the database Migration assistant was painless but you don't have any control over process while in Manual process, you are in total control and does require a lot of things to care of.

If you want to Manually upgrade, here are the steps from Metalink :-

PURPOSE
-------

This document is created for use as a guideline and checklist when
manually upgrading Oracle 8 or Oracle 8i to Oracle 9i.


SCOPE & APPLICATION
-------------------

Database administrators


UPGRADE CHECKLIST
-----------------


UPGRADING THE DATABASE
----------------------
1. -----------------------------------------------------------------------------

If your old release version is 8.0.5 or less (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 or higher, you can directly
upgrade your database to 9i.

If your old release is 8.0.5 or less, look at not 133920.1 for manual upgrading
the database to 8.0.6 or higher.

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;

If you run out of space in one of these tablespaces during the upgrade, you will
need to perform the upgrade again.

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

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'


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.
Echo $ORACLE_SID
Echo $ORACLE_HOME

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

As of Oracle 9i the sql NCHAR datatypes will be limited to the Unicode character
set encoding only (UTF8 and AL16UTF16). Any other NCHAR datatype will no longer
be supported. When upgrading to 9i the value of the NCHAR is based on the NCHAR
datatype used in the Oracle8 version. If the old National character set is UTF8,
the new will be UTF8. Otherwise the national character set is changed to
AL16UTF16.

Verify character set of the database:
$ Sqlplus SYS/
Select name, substrb(value$,1,40) value from props$;

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

If you are upgrading from the 8.0.6 release check no users or roles are 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:

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

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.
- Ensure there is a value for DB_BLOCK_SIZE
- Comment out the JOB_QUEUE_PROCESSES parameter, put in a new and set this
explicitly to zero, during the upgrade
- Comment out the AQ_TM_PROCESSES parameter, put in a new and set this
explicitly to zero, during the upgrade
- If archiving is enabled set LOG_ARCHIVE_START=TRUE
- Ensure that the USER_DUMP_DEST, BACKGROUND_DUMP_DEST and the CORE_DUMP_DEST
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
- Comment out obsoleted parameters(list in appendix A).
- Comment out SNAPSHOT_REFRESH_? parameters
- If your are upgrading from an 8i release ensure the COMPATIBLE parameter
points to the current version. This to ensure a more easy downgrade when
something goes wrong. We can alter this to point to the new release when
everything is tested. If your are upgrading from 8.0.6 then leave the
parameter unset. Setting this parameter to less than 8.1.X results in an
error during the upgrade.
- If you are using a passwordfile set the parameter
REMOTE_LOGIN_PASSWORDFILE=NONE
- If you have set the parameter NLS_LENGTH_SEMANTICS to CHAR, change the value
to BYTE during the upgrade.
- If you are using a cluster database, set the parameter CLUSTER_DATABASE=FALSE
during the upgrade.

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

Check for adequate freespace on archive log destination file systems.

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

Ensure the NLS_LANG variable is set correctly:
$ 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:
C:\ORADIM80 -DELETE -SID

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

And create the new Oracle 9i service use ORADIM of the 9i ORACLE_HOME:

C:\ORADIM -NEW -SID -INTPWD -MAXUSERS n
-STARTMODE AUTO -PFILE ORACLE_HOME\DATABASE\init.ora

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
OR
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
startup:
::N

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

Update the environment variables like ORACLE_HOME and PATH
$ . oraenv

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

Make sure the following enviroment variables point to the new
Release directories:
- ORACLE_HOME
- PATH
- ORA_NLS33
- ORACLE_BASE
- LD_LIBRARY_PATH
- ORACLE_PATH

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

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

HP-UX:
$ 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 RESTRICT when you are upgrading to Oracle 9.0.1:
SQL> Startup restrict

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 Only Script to Run
==== ==================
8.0.6 u0800060.sql
8.1.7 u0801070.sql
9.0.1 u0900010.sql

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

Display the contents of the component registry to determine which components
need to be upgraded:
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 (This step is only valid for upgrades towards 9.2):

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.


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

Edit init.ora file:
- Leave the parameter JOB_QUEUE_PROCESSES=0
- Leave the parameter AQ_TM_PROCESSES=0
- remove the parameter _system_trig_enabled from the init.ora file. This
parameter was explicitly set to false during the upgrade.
- modify the log_archive_dest parameter specify only the path, but make sure it
ends with a '/'. (remove the format)
e.g. log_archive_dest=/path/arch into log_archive_dest=/path/
- Modify the marameter log_archive_format and add the format previously
removed from the log_archive_dest.
E.g log_archive_format=arch%t_SID_%s.log
- If you are using a password file set the REMOTE_LOGIN_PASSWORDFILE parameter
to the value used before migration.

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

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

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

Upgrade user tables with NCHAR datatype columns:
$ sqlplus /nolog
SQL> connect sys/passwd_for_sys as sysdba
SQL> @utlnchar.sql
SQL> @n_switch.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
TRUE
38. ----------------------------------------------------------------------------

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
SID:ORACLE_HOME:Y

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

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

UPGRADING THE JAVA ENGINE(Only for upgrades to 9.0.1)
-------------------------
1. -----------------------------------------------------------------------------
Check java has previously been installed:
Run the following query:
select count(*) from dba_objects where object_type like 'JAVA%'and
owner = 'SYS';

If this is >0 then java has been installed. If this value =0 java is not
installed and there is no reason to upgrade the java engine.

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

Restart the database:
$ Sqlplus /nolog
SQL> connect sys/passwd_for_sys as sysdba
SQL> Shutdown immediate
SQL> startup restrict

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

Run the appropriate script depending on what release you are coming from:
$ cd $ORACLE_HOME/javavm/install
Sqlplus /nolog
SQL> Connect sys/passwd_for_sys as sysdba
SQL> Spool catoutjava.log

From Only Script to Run
==== ==================
8.1.5 jvmu815.sql
8.1.6 jvmu816.sql
8.1.7 jvmu817.sql

After you have run this script, all user classes are invalid. These become
implicitly valid when you executed them. You can explicitly revalidate them
when executing the following command:

SQL> Alter Java Class resolve;

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

Restart the database:
$ Sqlplus /nolog
SQL> connect sys/passwd_for_sys as sysdba
SQL> Shutdown immediate
SQL> startup restrict

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


Appendix A: Obsolete parameters in 9i:
--------------------------------------

_average_dirties_half_life
_lm_statistics
allow_partial_sn_results
always_anti_join
always_semi_join
arch_io_slaves
b_tree_bitmap_plans
backup_disk_io_slaves
cache_size_threshold
cleanup_rollback_entries
close_cached_open_cursors
compatible_no_recovery
complex_view_merging
cpu_count
db_block_checkpoint_batch
db_block_lru_extended_statisti
db_block_lru_latches
db_block_lru_statistics
db_block_max_dirty_target
db_file_simultaneous_writes
delayed_logging_block_cleanout
discrete_transactions_enabled
distributed_lock_timeout
distributed_recovery_connectio
fast_full_scan_enabled
freeze_DB_for_fast_instance_re
gc_defer_time
gc_latches
gc_lck_procs
gc_releasable_locks
gc_rollback_locks
hash_multiblock_io_count
instance_nodeset
job_queue_interval
job_queue_keep_connections
large_pool_min_alloc
lgwr_io_slaves
lm_locks
lm_procs
lm_procs
lm_ress
lock_sga_areas
log_block_checksum
log_files
log_simultaneous_copies
log_small_entry_max_size
ogms_home
ops_admin_group
ops_interconnects
optimizer_percent_parallel
optimizer_search_limit
parallel_default_max_instances
parallel_min_message_pool
parallel_server_idle_time
parallel_transaction_resource_
push_join_predicate
row_cache_cursors
sequence_cache_entries
sequence_cache_hash_buckets
shared_pool_reserved_min_alloc
snapshot_refresh_interval
snapshot_refresh_keep_connecti
snapshot_refresh_processes
sort_direct_writes
sort_multiblock_read_count
sort_read_fac
sort_spacemap_size
sort_write_buffer_size
sort_write_buffers
spin_count
temporary_table_locks
text_enable
use_ism
.