Upgrading Oracle Apps 11i Database to Oracle Database 10g Release 2 (10.2.0) - Step 3

3.Upgrade Oracle 10g Database (9i to 10g) .

Oracle 9i(9.2.0.6) to Oracle 10g(10.2.0.1)
Old Oracle 9i home ==> /oracle/app/oracle/visdb/9.2.0
New Oracle 10g home ==> /u01/app/oracle/product/10.2.0/db_1

1. mkdir -p /u01/app/oracle
chown -R oracle:oinstall /u01


2. export ORACLE_BASE=/oracle/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
export PATH=$PATH:$ORACLE_HOME/bin

3. Add Following entrys in /etc/sysctl.conf

kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
# semaphores: semmsl, semmns, semopm, semmni
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default=262144
net.core.rmem_max=262144
net.core.wmem_default=262144
net.core.wmem_max=262144

4. Install Oracle 10g Software Only..

( I have alrealy Post in Oracle RDBMS 10g Installing Steps )

5. ORACLE_HOME ==> 10g Home (/u01/app/oracle/product/10.2.0/db_1)

cp $ORACLE_HOME/rdbms/admin/utlu102i.sql /tmp
cp $ORACLE_HOME/rdbms/admin/utltzuv2.sql /tmp

6. 9i Home (/oracle/app/oracle/visdb/9.2.0)
=======

Set env (9i)

sqlplus '/as sysdba'

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

cat Database_Info.log


Oracle Database 10.2 Upgrade Information Utility 04-23-2008 11:07:05
.
**********************************************************************
Database:
**********************************************************************
--> name: TEST
--> version: 9.2.0.6.0
--> compatible: 9.2.0
.
**********************************************************************
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: 8082 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 58 MB
--> APPS_TS_QUEUES tablespace is adequate for the upgrade.
.... minimum required size: 577 MB
--> APPS_TS_TX_DATA tablespace is adequate for the upgrade.
.... minimum required size: 10842 MB
--> ODM tablespace is adequate for the upgrade.
.... minimum required size: 14 MB
--> OLAP tablespace is adequate for the upgrade.
.... minimum required size: 30 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 109 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: --> "large_pool_size" needs to be increased to at least 8388608
.
**********************************************************************
Deprecated Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
-- No deprecated parameters found. No changes are required.
.
**********************************************************************
Obsolete Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
--> "optimizer_max_permutations"
--> "row_locking"
--> "undo_suppress_errors"
--> "max_enabled_roles"
--> "enqueue_resources"
--> "sql_trace"
.
**********************************************************************
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 [install]
--> Real Application Clusters [upgrade] INVALID
--> 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
.
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: --> Passwords exist in some database links.
.... Passwords will be encrypted during the upgrade.
.... Downgrade of database links with passwords is not supported.
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
.... ODM
.
**********************************************************************
SYSAUX Tablespace:
[Create tablespace in the Oracle Database 10.2 environment]
**********************************************************************
WARNING: SYSAUX tablespace is present.
.... Minimum required size for database upgrade:500 MB
.... Online
.... Permanent
.... Readwrite
.... ExtentManagementLocal
.... SegmentSpaceManagementAuto
.
7.
Create Sysaux Tablespace.

CREATE TABLESPACE sysaux DATAFILE '/oracle/app/oracle/testdata/sysaux01.dbf'
SIZE 500M REUSE
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
ONLINE;

Alter Tablespaces...

select sum(bytes/1024/1024) from dba_free_space where tablespace_name='SYSTEM';

select FILE_NAME, sum(bytes/1024/1024) from dba_data_files where TABLESPACE_NAME='SYSTEM' GROUP BY FILE_NAME;

ALTER TABLESPACE SYSTEM ADD DATAFILE '/oracle/app/oracle/testdata/sys8.dbf' SIZE 4096M;

ALTER TABLESPACE SYSTEM ADD DATAFILE '/oracle/app/oracle/testdata/sys9.dbf' SIZE 4096M;

ALTER TABLESPACE SYSTEM ADD DATAFILE '/oracle/app/oracle/testdata/sys10.dbf' SIZE 4096M;
alter database tempfile '/oracle/app/oracle/testdata/tmp1.dbf' resize 4096M;

select sum(bytes/1024/1024) from dba_free_space where tablespace_name='APPS_TS_QUEUES';

select FILE_NAME, sum(bytes/1024/1024) from dba_data_files where TABLESPACE_NAME='APPS_TS_QUEUES' GROUP BY FILE_NAME;

ALTER TABLESPACE APPS_TS_QUEUES ADD DATAFILE '/oracle/app/oracle/testdata/queues3.dbf' SIZE 1024M;
select sum(bytes/1024/1024) from dba_free_space where tablespace_name='APPS_TS_TX_DATA';

select FILE_NAME, sum(bytes/1024/1024) from dba_data_files where TABLESPACE_NAME='APPS_TS_TX_DATA' GROUP BY FILE_NAME;

ALTER TABLESPACE APPS_TS_TX_DATA ADD DATAFILE '/oracle/app/oracle/testdata/tx_data12.dbf' SIZE 4096M;

ALTER TABLESPACE APPS_TS_TX_DATA ADD DATAFILE '/oracle/app/oracle/testdata/tx_data13.dbf' SIZE 4096M;

ALTER TABLESPACE APPS_TS_TX_DATA ADD DATAFILE '/oracle/app/oracle/testdata/tx_data14.dbf' SIZE 1024M;

select sum(bytes/1024/1024) from dba_free_space where tablespace_name='ODM';

select FILE_NAME, sum(bytes/1024/1024) from dba_data_files where TABLESPACE_NAME='ODM' GROUP BY FILE_NAME;

alter database datafile '/oracle/app/oracle/testdata/odm.dbf' resize 250m;
select sum(bytes/1024/1024) from dba_free_space where tablespace_name='OLAP';
select FILE_NAME, sum(bytes/1024/1024) from dba_data_files where TABLESPACE_NAME='OLAP' GROUP BY FILE_NAME;
alter database datafile '/oracle/app/oracle/testdata/olap.dbf' resize 250m;

8. Time Zone

sqlplus '/as sysdba'
SQL>@utltzuv2.sql
DROP TABLE sys.sys_tzuv2_temptab
*
ERROR at line 1:
ORA-00942: table or view does not exist
Table created.
Query sys.sys_tzuv2_temptab Table to see if any TIMEZONE data is affected by
version 2 transition rules
PL/SQL procedure successfully completed.
Commit complete.

9. Database contains stale optimizer statistics
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYS');
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('ODM');
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('OLAPSYS');
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('MDSYS');

.... SYS
.... ODM
.... OLAPSYS
.... MDSYS

10. Deprecated CONNECT role granted to some user/roles

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');
GRANTEE
------------------------------
CFD
DMS
HCC
DGRAY
EUL_US
SSOSDK
WEBSYS
PROJMFG
SERVICES
WIRELESS
EDWEUL_US

GRANTEE
------------------------------
MOBILEADMIN

12 rows selected.

SELECT 'REVOKE CONNECT FROM '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');

11. Obsolete Parameters

-->"optimizer_max_permutations"
--> "row_locking"
--> "undo_suppress_errors"
--> "max_enabled_roles"
--> "enqueue_resources"
--> "sql_trace"

12. Update Parameters

shared_pool_size=181217280
streams_pool_size=50331648
large_pool_size=8388608

13. Upgrade steps


a. Copy init file
cp initTEST.ora <10g>

b. Change Init file some parameter
change compatible=10.2.0.1
change udump,bdump,cdump
change iffile

c. set env
export ORACLE_SID=TEST
export ORACLE_BASE=/u01/app/
export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
export TNS_ADMIN=$ORACLE_HOME/network/admin
export PATH=$PATH:$ORACLE_HOME/bin

d.[oracle@sys43 admin]$ sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Apr 23 14:48:39 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup upgrade
ORACLE instance started.

Total System Global Area 473956352 bytes
Fixed Size 1220024 bytes
Variable Size 297796168 bytes
Database Buffers 163577856 bytes
Redo Buffers 11362304 bytes
Database mounted.
Database opened.
SQL> spool upgrade.log
SQL> @catupgrd.sql
....

Oracle Database 10.2 Upgrade Status Utility 04-23-2008 16:23:48
.
Component Status Version HH:MM:SS
Oracle Database Server VALID 10.2.0.1.0 00:48:24
JServer JAVA Virtual Machine VALID 10.2.0.1.0 00:06:07
Oracle XDK VALID 10.2.0.1.0 00:06:03
Oracle Database Java Packages VALID 10.2.0.1.0 00:00:25
Oracle Text VALID 10.2.0.1.0 00:01:08
Oracle XML Database VALID 10.2.0.1.0 00:01:30
Oracle Real Application Clusters INVALID 10.2.0.1.0 00:00:02
Oracle Data Mining VALID 10.2.0.1.0 00:00:19
OLAP Analytic Workspace VALID 10.2.0.1.0 00:00:16
OLAP Catalog VALID 10.2.0.1.0 00:01:13
Oracle OLAP API VALID 10.2.0.1.0 00:00:38
Oracle interMedia VALID 10.2.0.1.0 00:05:22
Spatial INVALID 10.2.0.1.0 00:04:21
.
Total Upgrade Time: 01:31:00
SQL>spool off
SQL>@utlrp.sql
SQL>spool invalid_objects.log
SQL>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';
SQL>spool off
SQL>shut immediate

Copy ENV file to Oracle 10g Home

• cp env to oracle_home_10g and edit new values

Copy TNS_ADMIN to Oracle 10g Home

• cp tns_admin(9i) to oracle 10g
• cp $ORACLE_HOME/network/admin/_ $ORACLE_HOME_10g/network/admin
  

Start and Stop linstener
Startup DB