Migration from Oracle 9i to Oracle 10gR2-优快云博客

System Information
Source system
Oracle: 9.2.0.7

Destination System
Oracle: 10.2.0.2

1. run check script on source DB p
copy $ORACLE_HOME_10g/rdbms/utlu102i.sql to temporary location

change to temporary location
run sqlplus
> @utlu102i.sql

2. deactivate jobs p
sqlplus '/ as sysdba'
>show parameter job
>alter system set job_queue_processes = 0;
stop all backup jobs

3. create pfile p
create pfile=’…’ from spfile;
4. shutdown database p
Shutdown
Cold Database backup
Backup the oracle binary

5. copy init.ora file p
cp init.ora init.ora.10g

6. edit new init.ora.10g p
remove parameter:
hash_join_enabled
log_archive_start
max_commit_propagation_delay
filesystemio_options
optimizer_features_enabled
optimizer_mode

add parameter:
session_max_open_files = 20
streams_pool_size = 64M

Verify (only for upgrade ):
shared_pool >= 150M
java_pool >= 150M
LARGE_POOL_SIZE >= 150M
Aq_at_processes = 0
7. change $ORACLE_HOME p

export $ORACLE_HOME = ( new 10g Home )
/oracle/app/orai932/product/10.2.0
Copy listener.ora to /oracle/app/orai932/product/10.2.0/network/admin
Copy tnsnames.ora to /oracle/app/orai932/product/10.2.0/network/admin
(modify listener.ora to reflect new oracle home)
8. Install 10gR2 10.2.0.1

cd to oracle media (install to new location)
runInstaller

9. startup database p
startup upgrade pfile=’init.ora.10g’

10. create SYSAUX tablespace p
create tablespace SYSAUX datafile ‘/rac1data1/sid/sysaux.dbf’
Size 512m extent management local segment space management
auto online;

11. upgrade database p
@?/rdbms/admin/catupgrd.sql

12. restart database p
shutdown
startup upgrade pfile=’init.ora.10g’
13. recompile all objects p
@?/rdbms/admin/utlrp.sql
Ø check dba_objects for invalid objects
14. shutdown p
15. startup p
startup nomount pfile=’init.ora.10g’
16. edit init.ora p
remove parameters:
log_archive_dest

change parameters:
add parameters:
job_queue_processes = 10
aq_tm_processes = 2
compatible = 10.2.0.0

optional
sga_max_size
sga_target

17. create new spfile p
create spfile ‘…/spfileins932.ora’ from pfile ‘..’;
18. restart database with new spfile p
shutdown immediate
startup
19. Verify database is upgraded p
run sqlplus
Ø @utlu102s.sql
Ø Grant the following privileges to users with connect role
CREATE VIEW
CREATE TABLE
ALTER SESSION
CREATE CLUSTER
CREATE SESSION
CREATE SYNONYM
CREATE SEQUENCE
CREATE DATABASE LINK

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

20. Shutdown database and cold backup

Shutdown immediate
Backup on oracle binary
Cold backup for database

21. Upgrade to 10.2.0.2

cd to media directory
./runInstaller

22. Startup upgrade

1. Enter the following SQL*Plus commands:
2. SQL> STARTUP UPGRADE
3. SQL> SPOOL patch.log
4. SQL> @?/rdbms/admin/catupgrd.sql
5. SQL> SPOOL OFF
6. Restart the database:
7. SQL> SHUTDOWN
8. SQL> STARTUP
9. SQL> @?/rdbms/admin/utlrp.sql

23 If upgrade fail

Rollback changes.
Restore the original copy from backup