Upgrade Oracle Database 9i to 10g 10204

Upgraded JDEdwards oracle database from 9i to 10g using the standard database upgrade procedure,

1. SRPSFTENT --> Microsoft Windows 2003 Server Enterprise Edition SP2. 8GB RAM.

2. On SRPSFTENT: Stopped and made OracleOraHome92HTTPServer &
OracleMTSRecoveryService services manual,
most important stop JDE service 'JDE E812'.

On SRPSFTDEP: Stopped OracleMTSRecoveryService service

On SRPSFTWEB: Stopped Oracle-oc4jadminProcessManager

3. Delete OracleOraHome92TNSListenerlistener_10g service through the add+remove services.exe tool.

4. Exported Oracle Registry as backup to flash\jdedbupgraderough directory.

5. Made startmode=manual for database in registry

6. Start database and check invalid objects
SQL> select objecT_name,Object_type,owner from dba_objects where status='INVALID
';

OBJECT_NAME OBJECT_TYPE OWNER
------------------ ------------------------------
ODCIARGDESC TYPE SYS
ODCICOLINFO TYPE SYS
ODCICOST TYPE SYS
ODCIINDEXINFO TYPE SYS
ODCIINDEXINFO TYPE SYS
CATALOG_TYP TYPE OE
CATEGORY_TYP TYPE OE
COMPOSITE_CATEGORY_TYP TYPE OE
LEAF_CATEGORY_TYP TYPE OE
BSREPORT PROCEDURE TESTDTA
PKG_MULTIRESULTSET PACKAGE BODY TESTDTA
11 rows selected.

7. Shutdown immediate;

8. Before 10g install note the path parameter.
C:\>set path
Path=E:\oracle\ora92\bin;C:\Program Files\Microsoft Visual Studio 8\VC\bin;E:\JD
Edwards\E812\DDP\Verity\winx86\_nti40\bin;E:\oracle\ora92\jre\1.4.2\bin\client;E
:\oracle\ora92\jre\1.4.2\bin;C:\Program Files\Oracle\jre\1.3.1\bin;C:\Program Fi
les\Oracle\jre\1.1.8\bin;C:\Program Files\HP\NCU;C:\WINDOWS\system32;C:\WINDOWS;
C:\WINDOWS\System32\Wbem;C:\Program Files\System Center Operations Manager 2007\
PATHEXT=.COM;.EXE;.BAT;.CMD;.VBS;.VBE;.JS;.JSE;.WSF;.WSH

9. Take backup of Oracle inventory. Backup c:\program files\oracle folder which contains Inventory directory.

10. Install Oracle 10g Release 2 in a new Oracle Home i.e, Install 10201+companion(select Oracle Database 10g Products 10.2.0.1+10204patchset in e:\oracle\ora10g

11. environment variable check
C:\>set oracle
ORACLE_HOME=e:\oracle\ora92

C:\>set path
Path=e:\oracle\ora10g\bin;E:\oracle\ora92\bin;C:\Program Files\Microsoft Visual
Studio 8\VC\bin;E:\JDEdwards\E812\DDP\Verity\winx86\_nti40\bin;E:\oracle\ora92\j
re\1.4.2\bin\client;E:\oracle\ora92\jre\1.4.2\bin;C:\Program Files\Oracle\jre\1.
3.1\bin;C:\Program Files\Oracle\jre\1.1.8\bin;C:\Program Files\HP\NCU;C:\WINDOWS
\system32;C:\WINDOWS;C:\WINDOWS\System32\Wbem;C:\Program Files\System Center Ope
rations Manager 2007\

Update the path entry and remove 10ghome that is in begining of PATH variable so that we can connect to 9i database using 9i home.

After update in path parameter the new value is,

C:\Documents and Settings\jde.QIA>set path
Path=E:\oracle\ora92\bin;C:\Program Files\Microsoft Visual Studio 8\VC\bin;E:\JD
Edwards\E812\DDP\Verity\winx86\_nti40\bin;E:\oracle\ora92\jre\1.4.2\bin\client;E
:\oracle\ora92\jre\1.4.2\bin;C:\Program Files\Oracle\jre\1.3.1\bin;C:\Program Fi
les\Oracle\jre\1.1.8\bin;C:\Program Files\HP\NCU;C:\WINDOWS\system32;C:\WINDOWS;
C:\WINDOWS\System32\Wbem;C:\Program Files\System Center Operations Manager 2007\

12. Start the 9i database in normal mode (Must) from 9i sqlplus tool,

E:\>sqlplus

SQL*Plus: Release 9.2.0.8.0 - Production on Fri Oct 29 08:19:23 2010

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Enter user-name: / as sysdba
Connected to an idle instance.

SQL> exit
Disconnected

E:\>set oracle
ORACLE_HOME=e:\oracle\ora92

E:\>sqlplus

SQL*Plus: Release 9.2.0.8.0 - Production on Fri Oct 29 08:19:34 2010

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Enter user-name: / as sysdba
Connected to an idle instance.

SQL> startup;
ORACLE instance started.

Total System Global Area 1469131144 bytes
Fixed Size 457096 bytes
Variable Size 696254464 bytes
Database Buffers 771751936 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL>

13. copy the following files from the 10gR2 ORACLE_HOME/rdbms/admin directory to a directory outside of the Oracle home, such as the e:\upgradetemp directory on your system:

ORACLE_HOME/rdbms/admin/utlu102i.sql
ORACLE_HOME/rdbms/admin/utltzuv2.sql

Make a note of the new location of these files.

14. Change to the temporary directory that you copied files in previous step.

Start SQL*Plus and connect to the database instance you are intending to upgrade (running from the original old ORACLE_HOME) as a user with SYSDBA privileges. Then run and spool the utlu102i.sql file.
sqlplus '/as sysdba'

E:\upgradetemp>dir
Volume in drive E is New Volume
Volume Serial Number is 78AD-5ABC

Directory of E:\upgradetemp

10/29/2010 08:10 AM .
10/29/2010 08:10 AM ..
02/07/2007 01:23 PM 18,183 utltzuv2.sql
10/08/2007 12:53 PM 136,217 utlu102i.sql
2 File(s) 154,400 bytes
2 Dir(s) 224,538,529,792 bytes free

SQL> spool Database_Info.log
SQL> @utlu102i.sql
SQL> spool off


Please note: The instance can be running in normal mode and does not need to be running in a restricted (migrate / upgrade) mode to run the script. Also, the instance must not be running in read-only mode. A few registry$ tables may be created, if they do not already exist, and some rows may be inserted into existing Upgrade tables.
Then, check the spool file and examine the output of the upgrade information tool. The sections describe the output of the Upgrade Information Tool (utlu102i.sql).

*************************
SPOOL OUTPUT FILE CONTENT
*************************
SQL> @utlu102i.sql
Oracle Database 10.2 Upgrade Information Utility 10-29-2010 08:23:58
.
**********************************************************************
Database:
**********************************************************************
--> name: JDEDB
--> version: 9.2.0.8.0
--> compatible: 9.2.0.0.0
--> blocksize: 8192
.
**********************************************************************
Logfiles: [make adjustments in the current environment]
**********************************************************************
--> The existing log files are adequate. No changes are required.
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 730 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 16 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 58 MB
--> CWMLITE tablespace is adequate for the upgrade.
.... minimum required size: 19 MB
--> DRSYS tablespace is adequate for the upgrade.
.... minimum required size: 33 MB
.... AUTOEXTEND additional space required: 13 MB
--> EXAMPLE tablespace is adequate for the upgrade.
.... minimum required size: 151 MB
--> ODM tablespace is adequate for the upgrade.
.... minimum required size: 10 MB
--> XDB tablespace is adequate for the upgrade.
.... minimum required size: 49 MB
.... AUTOEXTEND additional space required: 2 MB
.
**********************************************************************
Update Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
WARNING: --> "streams_pool_size" is not currently defined and needs a value of
at least 50331648
WARNING: --> "session_max_open_files" needs to be increased to at least 20
.
**********************************************************************
Renamed Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
-- No renamed parameters found. No changes are required.
.
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
--> "hash_join_enabled"
--> "log_archive_start"
.
**********************************************************************
Components: [The following database components will be upgraded or installed]
**********************************************************************
--> Oracle Catalog Views [upgrade] VALID
--> Oracle Packages and Types [upgrade] VALID
--> JServer JAVA Virtual Machine [upgrade] VALID
...The 'JServer JAVA Virtual Machine' JAccelerator (NCOMP)
...is required to be installed from the 10g Companion CD.
--> Oracle XDK for Java [upgrade] VALID
--> Oracle Java Packages [upgrade] VALID
--> Oracle Text [upgrade] VALID
--> Oracle XML Database [upgrade] VALID
--> Oracle Workspace Manager [upgrade] VALID
--> Oracle Data Mining [upgrade] VALID
--> OLAP Analytic Workspace [upgrade] UPGRADED
--> OLAP Catalog [upgrade] VALID
--> Oracle OLAP API [upgrade] UPGRADED
--> Oracle interMedia [upgrade] VALID
...The 'Oracle interMedia Image Accelerator' is
...required to be installed from the 10g Companion CD.
--> Spatial [upgrade] VALID
--> Oracle Ultra Search [upgrade] VALID
... To successfully upgrade Ultra Search, install it from
... the 10g Companion CD.
.
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: --> Deprecated CONNECT role granted to some user/roles.
.... CONNECT role after upgrade has only CREATE SESSION privilege.
WARNING: --> Database contains stale optimizer statistics.
.... Refer to the 10g Upgrade Guide for instructions to update
.... statistics prior to upgrading the database.
.... Component Schemas with stale statistics:
.... SYS
.... XDB
.... WMSYS
.... ODM
.... OLAPSYS
.... MDSYS
.... WKSYS
WARNING: --> Database contains INVALID objects prior to upgrade.
.... USER OE has 4 INVALID objects.
.... USER SYS has 5 INVALID objects.
.... USER TESTDTA has 2 INVALID objects.
.
**********************************************************************
SYSAUX Tablespace:
[Create tablespace in the Oracle Database 10.2 environment]
**********************************************************************
--> New "SYSAUX" tablespace
.... minimum required size for database upgrade: 500 MB
.

PL/SQL procedure successfully completed.

SQL> spool off;


***********************
***********************


13. Increased the size of system tablespace from 1000MB to 2048MB
ALTER DATABASE DATAFILE 'E:\ORACLE\ORADATA\JDEDB\SYSTEM01.DBF' RESIZE 2048M;

UNDOTBS1 is already 3635MB and has all free space except 15MB used.

Increased the size of TEMP tablespace from 1500MB to 2048MB
ALTER DATABASE TEMPFILE 'E:\ORACLE\ORADATA\JDEDB\TEMP01.DBF' RESIZE 2048M

Increased the size of CWMLITE tablespace from 20MB to 128MB
ALTER DATABASE DATAFILE 'E:\ORACLE\ORADATA\JDEDB\CWMLITE01.DBF' RESIZE 128M

Increased the size of DRSYS01 tablespace from 20MB to 128MB
ALTER DATABASE DATAFILE 'E:\ORACLE\ORADATA\JDEDB\DRSYS01.DBF' RESIZE 128M;

EXAMPLES already 247 MB and has 60 free space i.e, only 148 MB used.

ODM already 20 MB and has 10 free space i.e, only 10 MB used.

Increased the size of XDB tablespace from 47MB to 128MB
ALTER DATABASE DATAFILE 'E:\ORACLE\ORADATA\JDEDB\XDB01.DBF' RESIZE 128M


14. Current initJDEDB.ora file content before any update.

*.aq_tm_processes=1
*.background_dump_dest='E:\oracle\admin\JDEDB\bdump'
*.compatible='9.2.0.0.0'
*.control_files='E:\oracle\oradata\JDEDB\CONTROL01.CTL','E:\oracle\oradata\JDEDB\CONTROL02.CTL','E:\oracle\oradata\JDEDB\CONTROL03.CTL'
*.core_dump_dest='E:\oracle\admin\JDEDB\cdump'
*.db_block_size=8192
*.db_cache_size=771658240
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='JDEDB'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=JDEDBXDB)'
*.fast_start_mttr_target=300
*.hash_join_enabled=TRUE
*.instance_name='JDEDB'
*.java_pool_size=67108864
*.job_queue_processes=10
*.large_pool_size=67108864
*.open_cursors=300
*.pga_aggregate_target=451658240
*.processes=150
*.query_rewrite_enabled='FALSE'
*.remote_login_passwordfile='EXCLUSIVE'
*.shared_pool_size=536870912
*.sort_area_size=2097152
*.star_transformation_enabled='FALSE'
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='E:\oracle\admin\JDEDB\udump'
*.log_archive_start=TRUE
*.log_archive_dest=E:\fra

Added *.streams_pool_size=50331648 (but commented as it is new 10g parameter), *.session_max_open_files=20 as required
Updated *.db_cache_size=771658240 to *.db_cache_size=371658240
Updated *.java_pool_size=67108864 to *.java_pool_size=167108864
Updated *.large_pool_size=67108864 to *.large_pool_size=157108864
Updated *.pga_aggregate_target=451658240 to 251658240
Updated *.shared_pool_size=536870912 to 236870912

so the final initJDEDB.ora file content is,

*.aq_tm_processes=1
*.background_dump_dest='E:\oracle\admin\JDEDB\bdump'
*.compatible='9.2.0.0.0'
*.control_files='E:\oracle\oradata\JDEDB\CONTROL01.CTL','E:\oracle\oradata\JDEDB\CONTROL02.CTL','E:\oracle\oradata\JDEDB\CONTROL03.CTL'
*.core_dump_dest='E:\oracle\admin\JDEDB\cdump'
*.db_block_size=8192
*.db_cache_size=371658240
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='JDEDB'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=JDEDBXDB)'
*.fast_start_mttr_target=300
*.hash_join_enabled=TRUE
*.instance_name='JDEDB'
*.java_pool_size=167108864
*.job_queue_processes=10
*.large_pool_size=157108864
*.open_cursors=300
*.pga_aggregate_target=251658240
*.processes=150
*.query_rewrite_enabled='FALSE'
*.remote_login_passwordfile='EXCLUSIVE'
*.shared_pool_size=236870912
*.sort_area_size=2097152
*.star_transformation_enabled='FALSE'
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='E:\oracle\admin\JDEDB\udump'
*.log_archive_start=TRUE
*.log_archive_dest=E:\fra
*.session_max_open_files=20
#*.streams_pool_size=50331648

Shutdown immediate

Startup;

15. 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:

SQL> SELECT grantee FROM dba_role_privs
2 WHERE granted_role = 'CONNECT' and
3 grantee NOT IN (
4 'SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP',
5 'LOGSTDBY_ADMINISTRATOR', 'ORDSYS',
6 'ORDPLUGINS', 'OEM_MONITOR', 'WKSYS', 'WKPROXY',
7 'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS',
8 'WMSYS', 'OLAPDBA', 'OLAPSVR', 'OLAP_USER',
9 'OLAPSYS', 'EXFSYS', 'SYSMAN', 'MDDATA',
10 'SI_INFORMTN_SCHEMA', 'XDB', 'ODM');

GRANTEE
------------------------------
HR
OE
PM
QS
SH
PSFT
RMAN
DV810
QS_CB
QS_CS
QS_ES
QS_OS
QS_WS
SCOTT
CRPCTL
CRPDTA
JDEDBA
QS_ADM
AUDITOR
PRODCTL
PRODDTA
TESTCTL
TESTDTA
QS_CBADM
24 rows selected.

SQL> SELECT GRANTEE,PRIVILEGE FROM DBA_SYS_PRIVS
2 WHERE GRANTEE='CONNECT'
3 ;

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
8 rows selected.

NOTE: AFTER UPGRADE GRANT THE ABOVE SYSTEM PRIVILEGES TO ALL THE USERS LIST IN PREVIOUS SCRIPT.

16. Check for dblinks and to recreate them after the upgrade is done,

SQL> SELECT
2 'create '||DECODE(U.NAME,'PUBLIC','public ')||'database link '||CHR(10)
3 ||DECODE(U.NAME,'PUBLIC',Null, U.NAME||'.')|| L.NAME||chr(10)
4 ||'connect to ' || L.USERID || ' identified by '''
5 ||L.PASSWORD||''' using ''' || L.host || ''''
6 ||chr(10)||';' TEXT
7 FROM sys.link$ L,
8 sys.user$ U
9 WHERE L.OWNER# = U.USER# ;

no rows selected

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


18. I ignored this step,
But if you want as per the standard, check for corruption in the dictionary, use the following commands in sqlplus connected as sys:
Set verify off
Set space 0
Set line 120
Set heading off
Set feedback off
Set pages 1000
Spool analyze.sql

Select 'Analyze cluster "'||cluster_name||'" validate structure cascade;'
from dba_clusters
where owner='SYS'
union
Select 'Analyze table "'||table_name||'" validate structure cascade;'
from dba_tables
where owner='SYS' and partitioned='NO' and (iot_type='IOT' or iot_type is NULL)
union
Select 'Analyze table "'||table_name||'" validate structure cascade into invalid_rows;'
from dba_tables
where owner='SYS' and partitioned='YES';

spool off

This creates a script called analyze.sql.
Now execute the following steps.

$ sqlplus '/as sysdba'
SQL> @$ORACLE_HOME/rdbms/admin/utlvalid.sql
SQL> @analyze.sql

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

19. Since we don't have replication in database so I ignored the following step,

Ensure that all Materialized views / Snapshot refreshes are successfully completed, and that replication must be stopped (ie: quiesced).

$ sqlplus '/ as sysdba'
SQL> select distinct(trunc(last_refresh)) from dba_snapshot_refresh_times;
You can also use:
SQL> select distinct owner, name mview, master_owner master_owner, last_refresh from dba_mview_refresh_times;

20. Stop the listener for the database:

$ lsnrctl
LSNRCTL> stop

Ensure no files need media recovery:

SQL> select * from v$recover_file;

no rows selected

21. Ensure no files are in backup mode:

SQL> select * from v$backup where status!='NOT ACTIVE';
no rows selected

This should return no rows.

22. 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;

23. Disable all batch and cron jobs.

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

USERNAME DEFAULT_TABLESPACE
------------------------------ -------------------------
SYSTEM SYSTEM
SYS SYSTEM

To modify use:

SQL> alter user sys default tablespace SYSTEM;
SQL> alter user system default tablespace SYSTEM;


25. stop listnere so that no body is connecting to ur database.

26. Ensure that the aud$ is in the system tablespace when auditing is enabled.

SQL> select tablespace_name from dba_tables where table_name='AUD$';

TABLESPACE_NAME
------------------------------
SYSTEM

If the AUD$ table exists, and is in use, upgrade performance can be effected depending on the number of records in the table.
Please refer to the following note for information on exporting and truncating the AUD$ table:
Note.979942.1 Ext/Pub Database upgrade appears to have halted at SYS.AUD$ Table


27. Note down where all control files are located.

SQL> select * from v$controlfile;

STATUS
-------
NAME
-------------------------------------
E:\ORACLE\ORADATA\JDEDB\CONTROL01.CTL
E:\ORACLE\ORADATA\JDEDB\CONTROL02.CTL
E:\ORACLE\ORADATA\JDEDB\CONTROL03.CTL

28. If table XDB.MIGR9202STATUS exists in the database, drop it before upgrading the database (to avoid the issue described in Note:356082.1)

29. Shutdown the database

$ sqlplus '/as sysdba'
SQL> shutdown immediate;

30. Perform a full cold backup (or an online backup using RMAN)

You can either do this by manually copying the files or sign on to RMAN

$ rman "target / nocatalog"

31. Make a backup of the old init.ora file

Copy it from the old (pre-10.2) ORACLE_HOME to the new (10.2) ORACLE_HOME
Copy from E:\oracle\ora92\database to E:\oracle\ora10g\database.

Comment out any obsoleted parameters and Change all deprecated parameters

I commeted #*.log_archive_start=TRUE while added *.streams_pool_size=50331648 parameters.

- Set the COMPATIBLE initialization parameter to an appropriate value. If you are
upgrading from 8.1.7.4 then set the COMPATIBLE parameter to 9.2.0 until after the
upgrade has been completed successfully.
If you are upgrading from 9.2.0 or 10.1.0
then leave the COMPATIBLE parameter set to it's current value until the upgrade
has been completed successfully. This will avoid any unnecessary ORA-942 errors
from being reported in SMON trace files during the upgrade (because the upgrade
is looking for 10.2 objects that have not yet been created)

SO LEAVE COMPATIBLE PARAMETER AS IT IS FOR US.

32. If you have the parameter NLS_LENGTH_SEMANTICS currently set to CHAR, change the value
to BYTE during the upgrade (to avoid the issue described in Note:4638550.8)


SQL> SHOW PARAMETER NLS_LENGTH

NAME TYPE VALUE
------------------------------------ ----------- -----
nls_length_semantics string BYTE


33. Deleted oracle listener Oraclelistner9i using the add/delete services tool.

34. Copy the tnsnames.ora, listener.ora, sqlnet.ora files and put it in new oracle_home/network/admin directory and update accordingly to the new 10g.

35. Create the service---> oradim -delete -sid jdedb

36. update environment variables to point to 10g.
SET PATH=E:\ORACLE\ORA10G\BIN;%PATH%
SET ORACLE_SID=JDEDB
SET TNS_ADMIN=E:\ORACLE\ORA10G\NETWORK\ADMIN

Now create the new database service ---> oradim -new -sid jdedb -startmode auto

37. sqlplus '/ as sysdba'

SQL> STARTUP UPGRADE;

SELECT STATUS FROM V$INSTANCE;

OPEN MIGRATE


38. CREATE TABLESPACE SYSAUX DATAFILE 'e:\oracle\oradata\JDEDB\sysaux01.dbf' SIZE 2048m REUSE EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ONLINE;

39. Now run the following script to upgrade.

spool upgrade10g.txt
@E:\oracle\ora10g\RDBMS\ADMIN\catupgrd.sql
spool off
shutdown immediate;
startup;
spool invalids.txt
@E:\oracle\ora10g\RDBMS\ADMIN\utlrp.sql
spool off;

40. Now grant the system privileges that were missing due to connect role.

GRANT CREATE VIEW TO HR;
GRANT CREATE TABLE TO HR;
GRANT ALTER SESSION TO HR;
GRANT CREATE CLUSTER TO HR;
GRANT CREATE SESSION TO HR;
GRANT CREATE SYNONYM TO HR;
GRANT CREATE SEQUENCE TO HR;
GRANT CREATE DATABASE LINK TO HR;

GRANT CREATE VIEW TO OE;
GRANT CREATE TABLE TO OE;
GRANT ALTER SESSION TO OE;
GRANT CREATE CLUSTER TO OE;
GRANT CREATE SESSION TO OE;
GRANT CREATE SYNONYM TO OE;
GRANT CREATE SEQUENCE TO OE;
GRANT CREATE DATABASE LINK TO OE;

GRANT CREATE VIEW TO PM;
GRANT CREATE TABLE TO PM;
GRANT ALTER SESSION TO PM;
GRANT CREATE CLUSTER TO PM;
GRANT CREATE SESSION TO PM;
GRANT CREATE SYNONYM TO PM;
GRANT CREATE SEQUENCE TO PM;
GRANT CREATE DATABASE LINK TO PM;

GRANT CREATE VIEW TO QS;
GRANT CREATE TABLE TO QS;
GRANT ALTER SESSION TO QS;
GRANT CREATE CLUSTER TO QS;
GRANT CREATE SESSION TO QS;
GRANT CREATE SYNONYM TO QS;
GRANT CREATE SEQUENCE TO QS;
GRANT CREATE DATABASE LINK TO QS;

GRANT CREATE VIEW TO SH;
GRANT CREATE TABLE TO SH;
GRANT ALTER SESSION TO SH;
GRANT CREATE CLUSTER TO SH;
GRANT CREATE SESSION TO SH;
GRANT CREATE SYNONYM TO SH;
GRANT CREATE SEQUENCE TO SH;
GRANT CREATE DATABASE LINK TO SH;

GRANT CREATE VIEW TO PSFT;
GRANT CREATE TABLE TO PSFT;
GRANT ALTER SESSION TO PSFT;
GRANT CREATE CLUSTER TO PSFT;
GRANT CREATE SESSION TO PSFT;
GRANT CREATE SYNONYM TO PSFT;
GRANT CREATE SEQUENCE TO PSFT;
GRANT CREATE DATABASE LINK TO PSFT;

GRANT CREATE VIEW TO RMAN;
GRANT CREATE TABLE TO RMAN;
GRANT ALTER SESSION TO RMAN;
GRANT CREATE CLUSTER TO RMAN;
GRANT CREATE SESSION TO RMAN;
GRANT CREATE SYNONYM TO RMAN;
GRANT CREATE SEQUENCE TO RMAN;
GRANT CREATE DATABASE LINK TO RMAN;

GRANT CREATE VIEW TO DV810;
GRANT CREATE TABLE TO DV810;
GRANT ALTER SESSION TO DV810;
GRANT CREATE CLUSTER TO DV810;
GRANT CREATE SESSION TO DV810;
GRANT CREATE SYNONYM TO DV810;
GRANT CREATE SEQUENCE TO DV810;
GRANT CREATE DATABASE LINK TO DV810;

GRANT CREATE VIEW TO QS_CB;
GRANT CREATE TABLE TO QS_CB;
GRANT ALTER SESSION TO QS_CB;
GRANT CREATE CLUSTER TO QS_CB;
GRANT CREATE SESSION TO QS_CB;
GRANT CREATE SYNONYM TO QS_CB;
GRANT CREATE SEQUENCE TO QS_CB;
GRANT CREATE DATABASE LINK TO QS_CB;

GRANT CREATE VIEW TO QS_CS;
GRANT CREATE TABLE TO QS_CS;
GRANT ALTER SESSION TO QS_CS;
GRANT CREATE CLUSTER TO QS_CS;
GRANT CREATE SESSION TO QS_CS;
GRANT CREATE SYNONYM TO QS_CS;
GRANT CREATE SEQUENCE TO QS_CS;
GRANT CREATE DATABASE LINK TO QS_CS;

GRANT CREATE VIEW TO QS_ES;
GRANT CREATE TABLE TO QS_ES;
GRANT ALTER SESSION TO QS_ES;
GRANT CREATE CLUSTER TO QS_ES;
GRANT CREATE SESSION TO QS_ES;
GRANT CREATE SYNONYM TO QS_ES;
GRANT CREATE SEQUENCE TO QS_ES;
GRANT CREATE DATABASE LINK TO QS_ES;

GRANT CREATE VIEW TO QS_OS;
GRANT CREATE TABLE TO QS_OS;
GRANT ALTER SESSION TO QS_OS;
GRANT CREATE CLUSTER TO QS_OS;
GRANT CREATE SESSION TO QS_OS;
GRANT CREATE SYNONYM TO QS_OS;
GRANT CREATE SEQUENCE TO QS_OS;
GRANT CREATE DATABASE LINK TO QS_OS;

GRANT CREATE VIEW TO QS_WS;
GRANT CREATE TABLE TO QS_WS;
GRANT ALTER SESSION TO QS_WS;
GRANT CREATE CLUSTER TO QS_WS;
GRANT CREATE SESSION TO QS_WS;
GRANT CREATE SYNONYM TO QS_WS;
GRANT CREATE SEQUENCE TO QS_WS;
GRANT CREATE DATABASE LINK TO QS_WS;

GRANT CREATE VIEW TO SCOTT;
GRANT CREATE TABLE TO SCOTT;
GRANT ALTER SESSION TO SCOTT;
GRANT CREATE CLUSTER TO SCOTT;
GRANT CREATE SESSION TO SCOTT;
GRANT CREATE SYNONYM TO SCOTT;
GRANT CREATE SEQUENCE TO SCOTT;
GRANT CREATE DATABASE LINK TO SCOTT;

GRANT CREATE VIEW TO CRPCTL;
GRANT CREATE TABLE TO CRPCTL;
GRANT ALTER SESSION TO CRPCTL;
GRANT CREATE CLUSTER TO CRPCTL;
GRANT CREATE SESSION TO CRPCTL;
GRANT CREATE SYNONYM TO CRPCTL;
GRANT CREATE SEQUENCE TO CRPCTL;
GRANT CREATE DATABASE LINK TO CRPCTL;

GRANT CREATE VIEW TO CRPDTA;
GRANT CREATE TABLE TO CRPDTA;
GRANT ALTER SESSION TO CRPDTA;
GRANT CREATE CLUSTER TO CRPDTA;
GRANT CREATE SESSION TO CRPDTA;
GRANT CREATE SYNONYM TO CRPDTA;
GRANT CREATE SEQUENCE TO CRPDTA;
GRANT CREATE DATABASE LINK TO CRPDTA;

GRANT CREATE VIEW TO JDEDBA;
GRANT CREATE TABLE TO JDEDBA;
GRANT ALTER SESSION TO JDEDBA;
GRANT CREATE CLUSTER TO JDEDBA;
GRANT CREATE SESSION TO JDEDBA;
GRANT CREATE SYNONYM TO JDEDBA;
GRANT CREATE SEQUENCE TO JDEDBA;
GRANT CREATE DATABASE LINK TO JDEDBA;

GRANT CREATE VIEW TO QS_ADM;
GRANT CREATE TABLE TO QS_ADM;
GRANT ALTER SESSION TO QS_ADM;
GRANT CREATE CLUSTER TO QS_ADM;
GRANT CREATE SESSION TO QS_ADM;
GRANT CREATE SYNONYM TO QS_ADM;
GRANT CREATE SEQUENCE TO QS_ADM;
GRANT CREATE DATABASE LINK TO QS_ADM;

GRANT CREATE VIEW TO AUDITOR;
GRANT CREATE TABLE TO AUDITOR;
GRANT ALTER SESSION TO AUDITOR;
GRANT CREATE CLUSTER TO AUDITOR;
GRANT CREATE SESSION TO AUDITOR;
GRANT CREATE SYNONYM TO AUDITOR;
GRANT CREATE SEQUENCE TO AUDITOR;
GRANT CREATE DATABASE LINK TO AUDITOR;

GRANT CREATE VIEW TO PRODCTL;
GRANT CREATE TABLE TO PRODCTL;
GRANT ALTER SESSION TO PRODCTL;
GRANT CREATE CLUSTER TO PRODCTL;
GRANT CREATE SESSION TO PRODCTL;
GRANT CREATE SYNONYM TO PRODCTL;
GRANT CREATE SEQUENCE TO PRODCTL;
GRANT CREATE DATABASE LINK TO PRODCTL;


GRANT CREATE VIEW TO PRODDTA;
GRANT CREATE TABLE TO PRODDTA;
GRANT ALTER SESSION TO PRODDTA;
GRANT CREATE CLUSTER TO PRODDTA;
GRANT CREATE SESSION TO PRODDTA;
GRANT CREATE SYNONYM TO PRODDTA;
GRANT CREATE SEQUENCE TO PRODDTA;
GRANT CREATE DATABASE LINK TO PRODDTA;


GRANT CREATE VIEW TO TESTCTL;
GRANT CREATE TABLE TO TESTCTL;
GRANT ALTER SESSION TO TESTCTL;
GRANT CREATE CLUSTER TO TESTCTL;
GRANT CREATE SESSION TO TESTCTL;
GRANT CREATE SYNONYM TO TESTCTL;
GRANT CREATE SEQUENCE TO TESTCTL;
GRANT CREATE DATABASE LINK TO TESTCTL;


GRANT CREATE VIEW TO TESTDTA;
GRANT CREATE TABLE TO TESTDTA;
GRANT ALTER SESSION TO TESTDTA;
GRANT CREATE CLUSTER TO TESTDTA;
GRANT CREATE SESSION TO TESTDTA;
GRANT CREATE SYNONYM TO TESTDTA;
GRANT CREATE SEQUENCE TO TESTDTA;
GRANT CREATE DATABASE LINK TO TESTDTA;


GRANT CREATE VIEW TO QS_CBADM;
GRANT CREATE TABLE TO QS_CBADM;
GRANT ALTER SESSION TO QS_CBADM;
GRANT CREATE CLUSTER TO QS_CBADM;
GRANT CREATE SESSION TO QS_CBADM;
GRANT CREATE SYNONYM TO QS_CBADM;
GRANT CREATE SEQUENCE TO QS_CBADM;
GRANT CREATE DATABASE LINK TO QS_CBADM;

41. ADD FLASH RECOVERY AREA

42. UPDATE ALL THE SCHEDULED BACKUPS ACCORDINGLY TO NEW UPGRADED ORACLE DATABASE.

You can refer to the following metalink document if you need further assistance or email me at samiora@gmail.com

Note 316889.1 Complete Checklist for Manual Upgrades to 10gR2.docx