Oracle Apps DBA, Qatari Diar real Estate investment Company.: Upgrade

Showing posts with label Upgrade. Show all posts

adaimgr AutoUpgrade

After 8i to 9i Migration, The steps to be followed.

************* Start of AutoUpgrade session *************

AutoUpgrade version: 11.5.0
AutoUpgrade started at: Thu Apr 10 2008 09:53:09

APPL_TOP is set to /u01/11i/uat/applmgr/uatappl

NLS_LANG value from the environment is : American_America.WE8ISO8859P1
NLS_LANG value for this AD utility run is : AMERICAN_AMERICA.WE8ISO8859P1

It is critical that your Oracle Applications, RDBMS and related tools are
compatible and certified combinations. If you are uncertain whether a
combination is certified please contact Oracle Support Services.

Are you certain you are running a certified release combination [No] ? Yes

You can be notified by email if a failure occurs.
Do you wish to activate this feature [No] ? No

Please enter the batchsize [1000] : 1000

Please enter the name of the Oracle Applications System that this
APPL_TOP belongs to.

The Applications System name must be unique across all Oracle
Applications Systems at your site, must be from 1 to 30 characters
long, may only contain alphanumeric and underscore characters,
and must start with a letter.

Sample Applications System names are: "prod", "test", "demo" and
"Development_2".

Applications System Name [uat] : uat_zeus

NOTE: If you do not have or choose not to have certain types of files installed
in this APPL_TOP, you may not be able to perform certain tasks.

Example 1: If you don't have files used for installing or upgrading
the database installed in this area, you cannot install or upgrade
the database from this APPL_TOP.

Example 2: If you don't have forms files installed in this area, you cannot
generate them or run them from this APPL_TOP.

Example 3: If you don't have concurrent program files installed in this area,
you cannot relink concurrent programs or generate reports from this APPL_TOP.

Do you currently have or want to install files used for installing or upgrading
the database in this APPL_TOP [YES] ? YES

Do you currently have or want to install Java and HTML files for HTML-based
functionality in this APPL_TOP [YES] ? YES

Do you currently have or want to install Oracle Applications forms files
in this APPL_TOP [YES] ? YES

Do you currently have or want to install concurrent program files
in this APPL_TOP [YES] ? YES

Please enter the name Oracle Applications will use to identify this APPL_TOP.

The APPL_TOP name you select must be unique within an Oracle Applications
System, must be from 1 to 30 characters long, may only contain
alphanumeric and underscore characters, and must start with a letter.

Sample APPL_TOP Names are: "prod_all", "demo3_forms2", and "forms1".

APPL_TOP Name [zeus] : uat_zeus_appltop

You are about to install or upgrade Oracle Applications product tables
in your ORACLE database 'uat'
using ORACLE executables in '/u01/11i/uat/applmgr/uatora/8.0.6'.

Is this the correct database [Yes] ? Yes

AutoUpgrade needs the password for your 'SYSTEM' ORACLE schema
in order to determine your installation configuration.

Enter the password for your 'SYSTEM' ORACLE schema: *****

Connecting to SYSTEM......Connected successfully.

There exists one FND_PRODUCT_INSTALLATIONS table.
AutoUpgrade will upgrade the existing product group.

The ORACLE username specified below for Application Object Library
uniquely identifies your existing product group: APPLSYS

Enter the ORACLE password of Application Object Library [APPS] : *****

AutoUpgrade is verifying your username/password.
Connecting to APPLSYS......Connected successfully.

The status of various features in this run of AutoUpgrade is:

<-Feature version in->
Feature Active? APPLTOP Data model Flags
------------------------------ ------- -------- ----------- -----------
CHECKFILE No 1 -1 Y N N Y N N
PREREQ No 6 -1 Y N N Y N N
CONCURRENT_SESSIONS No 2 -1 Y Y N Y Y N
PATCH_TIMING No 2 -1 Y N N Y N N
PATCH_HIST_IN_DB No 6 -1 Y N N Y N N
SCHEMA_SWAP No 1 -1 Y N N Y Y N

Connecting to SYSTEM......Connected successfully.

Connecting to APPLSYS......Connected successfully.

Identifier for the current session is 1

Reading product information from file...

Reading language and territory information from file...

Reading language information from applUS.txt ...

Reading database to see what industry is currently installed.

Oracle Applications is currently installed for Commercial or for-profit use.

Do you wish to:
1) Continue to use Oracle Applications for Commercial or for-profit use.
2) Convert Oracle Applications to government, education or
not-for-profit use

Enter your choice [1] : 1

Reading FND_LANGUAGES to see what is currently installed.
Currently, the following languages are installed:

Code Language Status
---- --------------------------------------- ---------
US American English Base
ESA Latin American Spanish Install

Reading language information from applESA.txt ...

Your base language will be AMERICAN.

Your other languages to install are: LATIN AMERICAN SPANISH

Setting up module information.
Reading database for information about the modules.
Saving module information.
Reading database for information about the products.
Connecting to SYSTEM......Connected successfully.

Connecting to APPLSYS......Connected successfully.

Connecting to SYSTEM......Connected successfully.

Reading database for information about how products depend on each other.
Connecting to APPLSYS......Connected successfully.

Reading topfile.txt ...

Saving product information.
Connecting to SYSTEM......Connected successfully.

Connecting to APPLSYS......Connected successfully.

Connecting to APPS......Connected successfully.

Connecting to SYSTEM......Connected successfully.

Saving task information.

AutoUpgrade Main Menu
--------------------------------------------------

1. Choose database parameters

2. Choose overall tasks and their parameters

3. Run the selected tasks

4. Exit AutoUpgrade

* Please use License Manager to license additional
* products or modules after the upgrade is complete.

Enter your choice : 1

AutoUpgrade - Choose database parameters

- O - - S - --- M --- --- I --- --- D ---
Product Action ORACLE Sizing Main Index Default
# Name User ID Factor Tablespace Tablespace Tablespace
-- ---------------------- - ------- ------ ---------- ---------- ----------
1 Application Object Lib U APPLSYS 100 AOLD AOLX AOLD
2 Application Utilities U APPLSYS 0 AOLD AOLX AOLD
3 Applications DBA U APPLSYS 0 AOLD AOLX AOLD
4 Alert U APPLSYS 100 AOLD AOLX AOLD
5 Global Accounting Engi I AX 100 AXD AXX AXD
6 Common Modules-AK I AK 100 AKD AKX AKD
7 Subledger Accounting S XLA 100 XLAD XLAX XLAD
8 General Ledger U GL 100 GLD GLX GLD

There are 209 Oracle Applications. Enter U/D to scroll up/down.

Note: This will display all the tablespace

- To change a database parameter for a product;
INCLUDE the LETTER ABOVE the COLUMN you want to change
U / D / T / B - Press up/down/top/bottom to see other products
[Return] - To return to the AutoUpgrade Main Menu

Enter your choice (for example, 1M) :

AutoUpgrade error:

APPLSYSD
APPLSYSX

The above tablespaces do not exist in your database.
Please either create the missing tablespaces or change your
tablespace selection to exclude these tablespaces.

Review the messages above, then press [Return] to continue.

AutoUpgrade - Choose database parameters

- O - - S - --- M --- --- I --- --- D ---
Product Action ORACLE Sizing Main Index Default
# Name User ID Factor Tablespace Tablespace Tablespace
-- ---------------------- - ------- ------ ---------- ---------- ----------
39 Applications Shared Te S APPLSYS 100 APPLSYSD APPLSYSX AOLD
40 Enterprise Asset Manag EAM 100 EAMD EAMX EAMD
41 Transportation Executi FTE 100 FTED FTEX FTED
42 Public Sector Financia IGI 100 IGID IGIX IGID
43 Internet Procurement E ITG 100 ITGD ITGX ITGD
44 Inventory Optimization MSR 100 MSRD MSRX MSRD
45 Product Development IPD 100 IPDD IPDX IPDD
46 Product Development In ENI 100 ENID ENIX ENID

There are 209 Oracle Applications. Enter U/D to scroll up/down.

- To change a database parameter for a product;
INCLUDE the LETTER ABOVE the COLUMN you want to change
U / D / T / B - Press up/down/top/bottom to see other products
[Return] - To return to the AutoUpgrade Main Menu

Enter your choice (for example, 1M) : 39M

Enter the ORACLE tablespace name where you wish to store
Applications Shared Technology tables in ORACLE username APPLSYS [APPLSYSD] : AOLD

Connecting to APPLSYS......Connected successfully.

AutoUpgrade - Choose database parameters

- O - - S - --- M --- --- I --- --- D ---
Product Action ORACLE Sizing Main Index Default
# Name User ID Factor Tablespace Tablespace Tablespace
-- ---------------------- - ------- ------ ---------- ---------- ----------
33 Report Manager FRM 100 FRMD FRMX FRMD
34 Activity Based Managem ABM 100 ABMD ABMX ABMD
35 Balanced Scorecard BSC 100 BSCD BSCX BSCD
36 SEM Exchange EAA 100 EAAD EAAX EAAD
37 Value Based Management EVM 100 EVMD EVMX EVMD
38 Strategic Enterprise M FEM 100 FEMD FEMX FEMD
39 Applications Shared Te S APPLSYS 100 AOLD APPLSYSX AOLD
40 Enterprise Asset Manag EAM 100 EAMD EAMX EAMD

There are 209 Oracle Applications. Enter U/D to scroll up/down.

- To change a database parameter for a product;
INCLUDE the LETTER ABOVE the COLUMN you want to change
U / D / T / B - Press up/down/top/bottom to see other products
[Return] - To return to the AutoUpgrade Main Menu

Enter your choice (for example, 1M) : 39I

Enter the ORACLE tablespace name where you wish to store
Applications Shared Technology indexes in ORACLE username APPLSYS [APPLSYSX] : AOLX

Connecting to SYSTEM......Connected successfully.

Connecting to APPLSYS......Connected successfully.

AutoUpgrade - Choose database parameters

- O - - S - --- M --- --- I --- --- D ---
Product Action ORACLE Sizing Main Index Default
# Name User ID Factor Tablespace Tablespace Tablespace
-- ---------------------- - ------- ------ ---------- ---------- ----------
33 Report Manager FRM 100 FRMD FRMX FRMD
34 Activity Based Managem ABM 100 ABMD ABMX ABMD
35 Balanced Scorecard BSC 100 BSCD BSCX BSCD
36 SEM Exchange EAA 100 EAAD EAAX EAAD
37 Value Based Management EVM 100 EVMD EVMX EVMD
38 Strategic Enterprise M FEM 100 FEMD FEMX FEMD
39 Applications Shared Te S APPLSYS 100 AOLD AOLX AOLD
40 Enterprise Asset Manag EAM 100 EAMD EAMX EAMD

There are 209 Oracle Applications. Enter U/D to scroll up/down.

- To change a database parameter for a product;
INCLUDE the LETTER ABOVE the COLUMN you want to change
U / D / T / B - Press up/down/top/bottom to see other products
[Return] - To return to the AutoUpgrade Main Menu

Enter your choice (for example, 1M) :

Connecting to SYSTEM......Connected successfully.

Saving your choices...
Done.

Backing up restart files, if any......Done.

AutoUpgrade Main Menu
--------------------------------------------------

1. Choose database parameters

2. Choose overall tasks and their parameters

3. Run the selected tasks

4. Exit AutoUpgrade

* Please use License Manager to license additional
* products or modules after the upgrade is complete.

Enter your choice : 2

AutoUpgrade - Choose overall tasks and their parameters

# Task Do it? Parameters
-- ------------------------------------------ ------ --------------------
1 Verify files necessary for upgrade YES
2 Install or upgrade database objects YES

There are 2 tasks. Enter U/D to scroll up/down.

- To change YES to NO or NO to YES
(You cannot change a task marked with a *)
P - To change the parameters of a task
U / D - To page up/down to see other tasks
[Return] - To return to the AutoUpgrade Main Menu

Enter your choice (for example 2 or 2P) :

Saving your choices...
Done.

Backing up restart files, if any......Done.

AutoUpgrade Main Menu
--------------------------------------------------

1. Choose database parameters

2. Choose overall tasks and their parameters

3. Run the selected tasks

4. Exit AutoUpgrade

* Please use License Manager to license additional
* products or modules after the upgrade is complete.

Enter your choice : 3

Connecting to APPLSYS......Connected successfully.

Connecting to SYSTEM......Connected successfully.

Connecting to APPS......Connected successfully.

AD utilities can support a maximum of 999 workers. Your
current database configuration supports a maximum of 992 workers.
Oracle recommends that you use between 24 and 48 workers.

Enter the number of workers [24] : 24

Now process will start

Complete Upgrade Steps on 11.0.3 to 11i Upgrade

bash-2.05b$ echo $ORACLE_HOME
/u01/app/oracle/product/806

bash-2.05b$ echo $ORACLE_SID
dev

bash-2.05b$ echo $PATH
/usr/bin:/etc:/usr/sbin:/usr/ucb:/home/oracle/bin:/usr/bin/X11:/sbin:/usr/bin:/etc:/usr/sbin:/usr/ucb:/usr/bin/X11:/sbin:/usr/java14/jre/bin:/usr/java14/bin:.:/u01/app/oracle/product/806/bin::/u01/app/oracle/product/806/bin:/u01/app/oracle/product/806/bin

b-2.05b$ echo $LD_LIBRARY_PATH
/u01/app/oracle/local/java/jre1.1.6/lib:/u01/app/oracle/product/806/lib:

bash-2.05b$ svrmgrl

Oracle Server Manager Release 3.0.6.0.0 - Production

(c) Copyright 1999, Oracle Corporation. All Rights Reserved.

Oracle8 Enterprise Edition Release 8.0.6.3.0 - Production
PL/SQL Release 8.0.6.3.0 - Production

SVRMGR> connect internal
Connected.
SVRMGR> startup nomount pfile=/u01/app/oracle/product/806/dbs/initdev.ora
ORACLE instance started.
Total System Global Area 1919251520 bytes
Fixed Size 50240 bytes
Variable Size 1069846528 bytes
Database Buffers 838860800 bytes
Redo Buffers 10493952 bytes
SVRMGR> @/u01/app/oracle/product/bmcntl.sql
Statement processed.

SVRMGR> ALTER DATABASE OPEN RESETLOGS;
Statement processed.

SVRMGR> SELECT STATUS,LOGINS,INSTANCE_NAME FROM V$INSTANCE;
STATUS LOGINS INSTANCE_NAME
------- ---------- ----------------
OPEN ALLOWED dev
1 row selected.

Sys Passwd Change
orapwd file=$ORACLE_HOME/dbs/orapwdev password=oracle entries=5

Configure Tnsnames and Listeners

Subject: Migrating Apps Release 11.0 from UNIX Host To A Second UNIX Host
Doc ID: Note:74838.1

select distinct LOGFILE_NODE_NAME from fnd_concurrent_requests;
select distinct OUTFILE_NODE_NAME from fnd_concurrent_requests;

SVRMGR> select distinct LOGFILE_NODE_NAME from fnd_concurrent_requests;
LOGFILE_NODE_NAME
------------------------------
Atlas

update fnd_concurrent_requests set LOGFILE_NODE_NAME='instancezu'
where LOGFILE_NODE_NAME='atlas'

SVRMGR> select distinct LOGFILE_NODE_NAME from fnd_concurrent_requests;
LOGFILE_NODE_NAME
------------------------------
instancezu

2 rows selected.

SVRMGR> select distinct OUTFILE_NODE_NAME from fnd_concurrent_requests;
OUTFILE_NODE_NAME
------------------------------
atlas

update fnd_concurrent_requests set OUTFILE_NODE_NAME='instancezu'
where OUTFILE_NODE_NAME='atlas'

SVRMGR> update fnd_concurrent_requests set OUTFILE_NODE_NAME='instancezu'
where OUTFILE_NODE_NAME='atlas';

18313 rows processed.

SVRMGR> select distinct OUTFILE_NODE_NAME from fnd_concurrent_requests;
OUTFILE_NODE_NAME
------------------------------
instancezu

OLD_HOST
/u01/app/applmgr/1103/prod/admin/logprod/l31706384.req
/u01/app/applmgr/1103/prod/admin/outprod/INVMANG.31706384

NEW_HOST
/u01/app/applmgr/1103/dev/admin/logdev
/u01/app/applmgr/1103/dev/admin/outdev

LOGFILE_NAME

update fnd_concurrent_requests set
LOGFILE_NAME=replace(LOGFILE_NAME,'/u01/app/applmgr/1103/prod/admin/logprod/','/u01/app/applmgr/1103/dev/admin/logdev')

OUTFILE_NAME

update fnd_concurrent_requests set
OUTFILE_NAME=replace(OUTFILE_NAME,'/u01/app/applmgr/1103/prod/admin/outprod','/u01/app/applmgr/1103/dev/admin/outdev')

FND_CONCURRENT_PROCESSES

The logfile name of the concurrent managers is stored in
fnd_concurrent_processes.LOGFILE_NAME

The nodename of the concurrent managers is stored in
FND_CONCURRENT_PROCESSES.NODE_NAME

select distinct(NODE_NAME) from fnd_concurrent_processes;

update fnd_concurrent_processes set NODE_NAME = 'instancezu';

SVRMGR> select distinct(NODE_NAME) from fnd_concurrent_processes;
NODE_NAME
------------------------------
Atlas

1 row selected.

SVRMGR> update fnd_concurrent_processes set NODE_NAME = 'instancezu';
63 rows processed.

SVRMGR> select distinct(NODE_NAME) from fnd_concurrent_processes;
NODE_NAME
------------------------------
instancezu
1 row selected.

update global_name set global_name='DEV.WORLD' where global_name='PROD.WORLD'

/u01/app/applmgr/1103/dev/html/html/US
Change PROD to DEV
ICXUDUL_DEV.htm

2.0 Pre Upg tasks on 11.0.3 instance

2.0.1. Apply the Patch 1268797
cd $FND_TOP/patch/110/sql
sqlplus / @afstatrn.sql True

1- sqlplus applsys/xxxx@afstatdr.sql applsys xxxx
2- sqlplus applsys/xxxxx @afstatsg.sql oracle apps
3- imp parfile=afstats2.dat userid=applsys/xxxxxx
Or
3-imp userid=applsys/xxxxxx file=afstats2.dmp ignore=y grants=n full=y commit=n buffer=8000000
4- sqlplus applsys/xxxxxx @afstatgt.sql applsys xxxxxx apps xxxxxx
5- sqlplus apps/xxxxxx @AFSTATSS.pls
6- sqlplus apps/xxxxxx @fndpker.sql apps xxxxxx PACKAGE FND_STATS
7- sqlplus apps/xxxxxx @AFSTATSB.pls
8- sqlplus apps/xxxxxx @fndpker.sql apps xxxxxx PACKAGE_BODY FND_STATS

2.0.2. Apply the TUMS patch 3422686

cd $AD_TOP/patch/110/sql
sqlplus apps/app @adtums.sql /u10/app/convr11/tmp

2.0.3 Set the File attachments

2.0.4 . Submit “Purge Concurrent
Requests” with a retention
period of 7 days

2.0.5. Check for rows in

ALR_ACTION_HISTORY Table
Select count(*) from apps. ALR_ACTION_HISTORY;

If No rows then Apply the Patch 451137

2.0.6. Disabled all the Custom Triggers and
Alerts

2.0.7 Disabled Database Audit trial in init.ora
file

Audit_trial= false or comment out the line.

2.0.8 Validate and Compile Apps Schema
Use adadmin Utility

2.0.9 Backup the INVALIDS in a Table

Create table BEFORE_PREUPG_INVALIDS as select * from dba_objects where status=’INVALID’;

2.0.10. Make a note of passwords for following
users

SYSADMIN
GUEST
Oracle user - APPS
Oracle user – SYSTEM

3.0 Pre Upgrade Tasks on 11i Instance

3.0.1. Run Rapid install on DB tier and Apps Tier
./rapidwiz

3.0.3 Apply the Forms Patch set 18

If Reuired will Apply
3.0.4 Install 9.2.0.7 software on DB tier (4163445)
./runInstaller

3.0.5 Install Latest Opatch 2617419 on DB Tier
Unzip *2617419*.zip in $OARCLE_HOME

3.0.6 Applied Database patch 4192148 for Import
Issue

3.0.7 Update the .profile in MT and DB Tiers .
On MT :
- /<>/applmgr/r11i<>appl/APPSORA.env
On DB
- $ORACLE_HOME/ R11i<> _ .env

3.0.8 InstallPrep.sh script as per Note 189256.1

3.0.9 Configured tnsnames.ora on MT to connect to 8.1.7 database of 11.0.3 instance

3.0.10. Login to MT tier and perform following pre
upgrade tasks

Verify custom index privileges

cd $APPL_TOP/admin/preupg
sqlplus apps/oracle11i afindxpr.sql applsys
sqlplus apps/oracle11i afpregdi.sql

Make sure orders are in a supported status

cd $ONT_TOP/patch/115/sql
sqlplus apps/oracle11i@ontexc07.sql

Review Item Validation Org settings

cd $ONT_TOP/patch/115/sql
sqlplus apps/oracle11i@ontexc05.sql

Close open pick slips/picking batches or open deliveries/departures

cd $WSH_TOP/patch/115/sql
sqlplus apps/oracle11i@wshbdord.sql

Validate inventory organization data

cd $WSH_TOP/patch/115/sql

sqlplus apps/oracle11i@wshpre00.sql

Review cycles that may not be upgraded

cd $ONT_TOP/patch/115/sql
sqlplus apps/oracle11i@ontexc08.sql

Clear open interface tables

cd $APPL_TOP/admin/preupg
sqlplus apps/oracle11i@
Run above sql for each of the following scripts:
Requisitions Open Interface (pocntreq.sql)
Purchasing Documents Open Interface (pocntpoh.sql)
Receiving Open Interface (pocntrcv.sql)

Import and purge Invoice Import Interface expense reports and invoices

cd $APPL_TOP/admin/preupg
sqlplus apps/oracle11i@apuinimp.sql

Diagnose problems in the data

cd $AR_TOP/patch/115/sql
sqlplus apps/oracle11i@ar115chk.sql

Identify potential ORACLE schema conflicts

cd $APPL_TOP/admin/preupg
sqlplus applsys/oracle11i@adpuver.sql

3.0.10 Apply Family Consolidated Upgrade Patch for Financials - 3993353 with preinstall

Please Find the 806 - 9i Upgrade Steps

Subject: Complete Upgrade Checklist for Manual Upgrades from 8.X / 9.0.1 to Oracle9iR2 (9.2.0) Doc ID: Note: 159657.1

Setup the Enterprise Edition for Oracle 9i

These scripts create objects required by the RDBMS and other technology stack components on the database server Login to 11i Database Tier

Check for output of dba_registry is as given below

col comp_name for a40
col version for a15
select comp_name, version, status FROM dba_registry;

Created folder $ORACLE_HOME/appsutil/admin

Copied addb920.sql, adsy920.sql, adjv920.sql, admsc920.sql, and adgrants.sql from $APPL_TOP/admin to $ORACLE_HOME/appsutil/admin

sqlplus /nolog
connect SYSTEM/oracle11idev
@addb920.sql -- Sets up database SYS schema

sqlplus /nolog
connect SYSTEM/oracle11idev
@adsy920.sql -- Sets up database SYSTEM schema

sqlplus /nolog
connect SYSTEM/oracle11idev
@adjv920.sql - Installs Java Virtual Machine

sqlplus /nolog
connect SYSTEM/oracle11idev
@admsc920.sql FALSE CTXD TEMP $ORACLE_HOME/ctx/lib/libctxx9.so

Check for output of dba_registry is as given below

col comp_name for a40
col version for a15
select comp_name, version, status FROM dba_registry;

Change Roll Back to UNDO Tablespace

select segment_name,tablespace_name,status from dba_rollback_segs;

SEGMENT_NAME TABLESPACE_NAME
------------------------------ ------------------------------
SYSTEM SYSTEM
ROLL301 RBS3
ROLL302 RBS3

select file_name,tablespace_name, bytes from dba_data_files where tablespace_name = 'RBS3';

select 'alter rollback segment ' SEGMENT_NAME' offline;' from dba_rollback_segs;

SQL> select 'alter rollback segment ' SEGMENT_NAME' offline;' from dba_rollback_segs;

SEGMENT_NAME
=============

alter rollback segment ROLL301 offline;
alter rollback segment ROLL302 offline;

SQL> alter rollback segment ROLL302 offline;

alter rollback segment ROLL303 offline;

Rollback segment altered.

SQL> alter rollback segment ROLL304 offline;

alter rollback segment ROLL305 offline;
alter rollback segment ROLL306 offline;

Rollback segment altered.

SQL> alter rollback segment ROLL307 offline;

Rollback segment altered.

SQL> alter rollback segment ROLL308 offline;

Rollback segment altered.

SQL> drop rollback segment ;

select 'drop rollback segment ' SEGMENT_NAME';' FROM from dba_rollback_segs;

SQL> select 'drop rollback segment ' SEGMENT_NAME';' FROM dba_rollback_segs;

'DROPROLLBACKSEGMENT'SEGMENT_NAME';'
------------------------------------------------------

drop rollback segment ROLL301;
drop rollback segment ROLL302;

alter tablespace RBS3 offline;

SQL> alter tablespace RBS3 offline;
alter tablespace RBS2 offline;

drop tablespace RBS2;

Tablespace altered.

create undo tablespace

create undo tablespace APPS_UNDOTS1 datafile '/db2/oradata/dev/data/undodbs01.dbf' size 3000M reuse extent management local;

ALTER TABLESPACE APPS_UNDOTS1 ADD DATAFILE '/db2/oradata/dev/data/undodbs02.dbf' size 3000M;

create undo tablespace APPS_UNDOTS2 datafile '/db2/oradata/dev/data/undodbs04.dbf' size 3000M reuse extent management local;

ALTER TABLESPACE APPS_UNDOTS2 ADD DATAFILE '/db2/oradata/dev/data/undodbs05.dbf' size 2000M;

CREATE TEMPORARY TABLESPACE

alter tablespace temp offline;

SQL> alter tablespace temp offline;

SQL> drop tablespace temp;

CREATE TEMPORARY TABLESPACE temp
TEMPFILE '/db2/oradata/dev/data/temp01.dbf' SIZE 3000M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;

ALTER TABLESPACE temp
ADD TEMPFILE '/db2/oradata/dev/data/temp02.dbf' SIZE 2000M REUSE;

select * from dba_temp_files;
SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

Set Default TEMP TBS

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

Convert existing tablespaces to local extent management

We recommend using local extent management to increase performance. To
convert all non-SYSTEM tablespaces from Data Dictionary extent management to
local extent management, run the following script:

UNIX:
$ cd $APPL_TOP/admin/preupg
$ sqlplus system/finalf @adtbscnv.pls finalf

SQL> desc dba_tablespaces;
SQL> select EXTENT_MANAGEMENT, count(*) from dba_tablespaces group by EXTENT_MANAGEMENT;

bash-2.05b$ pwd
/u012/11i/ora/oracle/devsappl/admin
adsysapp2.sql

bash-2.05b$ sqlplus system/finalf@dev

SQL> @adsysapp2.sql
Enter value for 1: finalf

alter database datafile '/db2/oradata/dev/data/system01.dbf' resize 10000M;

To continue to use the existing tablespace model (OFA-compliant):

Run adgnofa.sql as follows. On the command line, indicate the as
NEW to create new product tablespaces (tablespaces for existing products are
not affected), or ALL to create tablespaces for new products and resize existing
product tablespaces.

UNIX:
$ cd $AD_TOP/patch/115/sql
$ sqlplus apps/xxxxxx @adgnofa.sql ALL

$sqlplus "/as sysdba"
@/u012/11i/ora/oracle/devsappl/ad/11.5.0/patch/115/sql/adcrtbsp.sql

alter database datafile '/db2/oradata/dev/index/jax01.dbf' resize 50M;

CREATE TABLESPACE APPLSYSD
DATAFILE '/db2/oradata/dev/data/applsysd01.dbf' size 200M
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 1M;

CREATE TABLESPACE APPLSYSX
DATAFILE '/db2/oradata/dev/data/applsysx01.dbf' size 200M
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 1M;

CREATE TABLESPACE CTXD DATAFILE '/db2/oradata/dev/data/ctxd01.dbf' SIZE 100M; AUTOEXTEND ON NEXT 5 MAXSIZE 200m;.

Applied database patch to create / update OWS packages
Login to 11i Database Tier 3835781
mkdir $ORACLE_HOME/appsutil/admin/OWS
cd $ORACLE_HOME/appsutil/admin/OWS
cp /R11isit/staging/patches/*3835781* .
@patch.sql

Installed XML Parser for PL/SQL
Login to 11i Database Tier

mkdir $ORACLE_HOME/appsutil/admin/xmlparser

cd $ORACLE_HOME/appsutil/admin/xmlparser

cp $COMMON_TOP/util/plxmlparser_v1_0_2.zip .
unzip plxmlparser_v1_0_2.zip - This file creates several subdirectories in the current location
cp $COMMON_TOP/util/XSU12_ver1_2_1.zip .
unzip XSU12_ver1_2_1.zip - This file creates subdirectory - OracleXSU12
cd /R11idev/db/9.2.0/appsutil/admin/xmlparser/lib/java
loadjava -user apps/oracle11i-r -v xmlparserv2.jar
loadjava -user apps/oracle11i-r -v xmlplsql.jar
cd ../sql
cat load.sql sqlplus apps/cl_11i_gbook_sta
cd /R11idev/db/9.2.0/appsutil/admin/xmlparser/OracleXSU12/lib
sh oraclexmlsqlload.ksh
mkdir $ORACLE_HOME/appsutil/admin/java
cd $ORACLE_HOME/appsutil/admin/java
cp /R11idev/oradata/R11iEXP/applmgr/r11iexpcomn/java/xmlparserv2.zip .
loadjava -user apps/oracle11i-r -v xmlparserv2.zip

Gathered database information
Login to 11i Mid tier
cd $APPL_TOP/admin/preupg
sqlplus apps/oracle11i@adupinfo.sql

Re-compile Invalids
sqlplus “/ as sysdba”
@$ORACLE_HOME/rdbms/admin/utlrp.sql
create table invalids_after_db_backup as select * from dba_objects where status = ‘INVALID’;

6.0 Auto Upgrade Tasks
Login to 11i Application Tier
Run Autoupgrade using adaimgr

$ adaimgr consolidated_tablespace=N

6.0.1 Post-Autoupgrade tasks
Login to 11i Database Tier
Re-compile Invalids
sqlplus “/ as sysdba”
@$ORACLE_HOME/rdbms/admin/utlrp.sql
create table invalids_after_autoupgrade as select * from dba_objects where status = ‘INVALID’;

6.0.2 Performed cold Backup of
Database

7.0 Autopatch Tasks
Login to 11i Application Tier
Applied AD.I.4 (4712852)

Applied 11.5.10.2 maintenance pack
cd $AU_TOP/patch/115/driver
adpatch options=nocopyportion,nogenerateportion

Invalid Objects after Database Upgraded to 10.2.0.1

ERROR at line 1:
ORA-04063: package body "SYSTEM.AD_INVOKER" has errors
ORA-06508: PL/SQL: could not find program unit being called:
"SYSTEM.AD_INVOKER"
ORA-06512: at line 2

sqlplus -s APPS/***** @/11i/progs/applmgr/devappl/ad/11.5.0/admin/sql/adinvset.pls &systempwd 8 0 FALSE FALSE

The Oracle DB's stored procedures have not been invalidated and recompiled for Oracle 10g.

Solutions
==========

1. Change directory to $Portal_DB_HOME/rdbms/admin
2. Login to sqlplus as sys as sysdba.
3. SQL>shutdown
4. SQL>startup upgrade
5 SQL>@utlirp.sql
6. SQL>shutdown
7. SQL>startup
8. SQL>@utlrp.sql
9. Check for invalid objects.

Start of AutoUpgrade session

************* Start of AutoUpgrade session *************

AutoUpgrade version: 11.5.0
AutoUpgrade started at: Thu Mar 14 2008 10:33:40

APPL_TOP is set to /u01/11i/uat/uatappl

NLS_LANG value from the environment is : American_America.WE8ISO8859P1
NLS_LANG value for this AD utility run is : AMERICAN_AMERICA.WE8ISO8859P1

It is critical that your Oracle Applications, RDBMS and related tools are
compatible and certified combinations. If you are uncertain whether a
combination is certified please contact Oracle Support Services.

Are you certain you are running a certified release combination [No] ? Yes

You can be notified by email if a failure occurs.
Do you wish to activate this feature [No] ? No

Please enter the batchsize [1000] : 1000

Please enter the name of the Oracle Applications System that this
APPL_TOP belongs to.

The Applications System name must be unique across all Oracle
Applications Systems at your site, must be from 1 to 30 characters
long, may only contain alphanumeric and underscore characters,
and must start with a letter.

Sample Applications System names are: "prod", "test", "demo" and
"Development_2".

Applications System Name [uat] : uat_sys1

NOTE: If you do not have or choose not to have certain types of files installed
in this APPL_TOP, you may not be able to perform certain tasks.

Example 1: If you don't have files used for installing or upgrading
the database installed in this area, you cannot install or upgrade
the database from this APPL_TOP.

Example 2: If you don't have forms files installed in this area, you cannot
generate them or run them from this APPL_TOP.

Example 3: If you don't have concurrent program files installed in this area,
you cannot relink concurrent programs or generate reports from this APPL_TOP.

Do you currently have or want to install files used for installing or upgrading
the database in this APPL_TOP [YES] ? YES

Do you currently have or want to install Java and HTML files for HTML-based
functionality in this APPL_TOP [YES] ? YES

Do you currently have or want to install Oracle Applications forms files
in this APPL_TOP [YES] ? YES

Do you currently have or want to install concurrent program files
in this APPL_TOP [YES] ? YES

Please enter the name Oracle Applications will use to identify this APPL_TOP.

The APPL_TOP name you select must be unique within an Oracle Applications
System, must be from 1 to 30 characters long, may only contain
alphanumeric and underscore characters, and must start with a letter.

Sample APPL_TOP Names are: "prod_all", "demo3_forms2", and "forms1".

APPL_TOP Name [zeus] : uat_sys1_appltop

You are about to install or upgrade Oracle Applications product tables
in your ORACLE database 'uat'
using ORACLE executables in '/u01/11i/uat/uatora/8.0.6'.

Is this the correct database [Yes] ? Yes

AutoUpgrade needs the password for your 'SYSTEM' ORACLE schema
in order to determine your installation configuration.

Enter the password for your 'SYSTEM' ORACLE schema: *****

Connecting to SYSTEM......Connected successfully.

There exists one FND_PRODUCT_INSTALLATIONS table.
AutoUpgrade will upgrade the existing product group.

The ORACLE username specified below for Application Object Library
uniquely identifies your existing product group: APPLSYS

Enter the ORACLE password of Application Object Library [APPS] : *****

AutoUpgrade is verifying your username/password.
Connecting to APPLSYS......Connected successfully.

The status of various features in this run of AutoUpgrade is:

<-Feature version in->
Feature Active? APPLTOP Data model Flags
------------------------------ ------- -------- ----------- -----------
CHECKFILE No 1 -1 Y N N Y N N
PREREQ No 6 -1 Y N N Y N N
CONCURRENT_SESSIONS No 2 -1 Y Y N Y Y N
PATCH_TIMING No 2 -1 Y N N Y N N
PATCH_HIST_IN_DB No 6 -1 Y N N Y N N
SCHEMA_SWAP No 1 -1 Y N N Y Y N

Connecting to SYSTEM......Connected successfully.

Connecting to APPLSYS......Connected successfully.

Identifier for the current session is 1

Reading product information from file...

Reading language and territory information from file...

Reading language information from applUS.txt ...

Reading database to see what industry is currently installed.

Oracle Applications is currently installed for Commercial or for-profit use.

Do you wish to:
1) Continue to use Oracle Applications for Commercial or for-profit use.
2) Convert Oracle Applications to government, education or
not-for-profit use

Enter your choice [1] : 1

Reading FND_LANGUAGES to see what is currently installed.
Currently, the following languages are installed:

Code Language Status
---- --------------------------------------- ---------
US American English Base
ESA Latin American Spanish Install

Reading language information from applESA.txt ...

Your base language will be AMERICAN.

Your other languages to install are: LATIN AMERICAN SPANISH

Setting up module information.
Reading database for information about the modules.
Saving module information.
Reading database for information about the products.
Connecting to SYSTEM......Connected successfully.

Connecting to APPLSYS......Connected successfully.

Connecting to SYSTEM......Connected successfully.

Reading database for information about how products depend on each other.
Connecting to APPLSYS......Connected successfully.

Reading topfile.txt ...

Saving product information.
Connecting to SYSTEM......Connected successfully.

Connecting to APPLSYS......Connected successfully.

Connecting to APPS......Connected successfully.

Connecting to SYSTEM......Connected successfully.

Saving task information.

AutoUpgrade Main Menu
--------------------------------------------------

1. Choose database parameters

2. Choose overall tasks and their parameters

3. Run the selected tasks

4. Exit AutoUpgrade
* Please use License Manager to license additional
* products or modules after the upgrade is complete.

Enter your choice : 1

AutoUpgrade - Choose database parameters

- O - - S - --- M --- --- I --- --- D ---
Product Action ORACLE Sizing Main Index Default
# Name | User ID Factor Tablespace Tablespace Tablespace
-- ---------------------- - ------- ------ ---------- ---------- ----------
1 Application Object Lib U APPLSYS 100 AOLD AOLX AOLD
2 Application Utilities U APPLSYS 0 AOLD AOLX AOLD
3 Applications DBA U APPLSYS 0 AOLD AOLX AOLD
4 Alert U APPLSYS 100 AOLD AOLX AOLD
5 Global Accounting Engi I AX 100 AXD AXX AXD
6 Common Modules-AK I AK 100 AKD AKX AKD
7 Subledger Accounting S XLA 100 XLAD XLAX XLAD
8 General Ledger U GL 100 GLD GLX GLD

There are 209 Oracle Applications. Enter U/D to scroll up/down.

- To change a database parameter for a product;
INCLUDE the LETTER ABOVE the COLUMN you want to change
U / D / T / B - Press up/down/top/bottom to see other products
[Return] - To return to the AutoUpgrade Main Menu

Enter your choice (for example, 1M) :

AutoUpgrade error:

APPLSYSD
APPLSYSX
The above tablespaces do not exist in your database.
Please either create the missing tablespaces or change your
tablespace selection to exclude these tablespaces.

Review the messages above, then press [Return] to continue.

AutoUpgrade - Choose database parameters

- O - - S - --- M --- --- I --- --- D ---
Product Action ORACLE Sizing Main Index Default
# Name | User ID Factor Tablespace Tablespace Tablespace
-- ---------------------- - ------- ------ ---------- ---------- ----------
39 Applications Shared Te S APPLSYS 100 APPLSYSD APPLSYSX AOLD
40 Enterprise Asset Manag EAM 100 EAMD EAMX EAMD
41 Transportation Executi FTE 100 FTED FTEX FTED
42 Public Sector Financia IGI 100 IGID IGIX IGID
43 Internet Procurement E ITG 100 ITGD ITGX ITGD
44 Inventory Optimization MSR 100 MSRD MSRX MSRD
45 Product Development IPD 100 IPDD IPDX IPDD
46 Product Development In ENI 100 ENID ENIX ENID

There are 209 Oracle Applications. Enter U/D to scroll up/down.

- To change a database parameter for a product;
INCLUDE the LETTER ABOVE the COLUMN you want to change
U / D / T / B - Press up/down/top/bottom to see other products
[Return] - To return to the AutoUpgrade Main Menu

Enter your choice (for example, 1M) : 39M

Enter the ORACLE tablespace name where you wish to store
Applications Shared Technology tables in ORACLE username APPLSYS [APPLSYSD] : AOLD
Connecting to APPLSYS......Connected successfully.

AutoUpgrade - Choose database parameters

- O - - S - --- M --- --- I --- --- D ---
Product Action ORACLE Sizing Main Index Default
# Name | User ID Factor Tablespace Tablespace Tablespace
-- ---------------------- - ------- ------ ---------- ---------- ----------
33 Report Manager FRM 100 FRMD FRMX FRMD
34 Activity Based Managem ABM 100 ABMD ABMX ABMD
35 Balanced Scorecard BSC 100 BSCD BSCX BSCD
36 SEM Exchange EAA 100 EAAD EAAX EAAD
37 Value Based Management EVM 100 EVMD EVMX EVMD
38 Strategic Enterprise M FEM 100 FEMD FEMX FEMD
39 Applications Shared Te S APPLSYS 100 AOLD APPLSYSX AOLD
40 Enterprise Asset Manag EAM 100 EAMD EAMX EAMD

There are 209 Oracle Applications. Enter U/D to scroll up/down.

- To change a database parameter for a product;
INCLUDE the LETTER ABOVE the COLUMN you want to change
U / D / T / B - Press up/down/top/bottom to see other products
[Return] - To return to the AutoUpgrade Main Menu

Enter your choice (for example, 1M) : 39I

Enter the ORACLE tablespace name where you wish to store
Applications Shared Technology indexes in ORACLE username APPLSYS [APPLSYSX] : AOLX

Connecting to SYSTEM......Connected successfully.

Connecting to APPLSYS......Connected successfully.

AutoUpgrade - Choose database parameters

- O - - S - --- M --- --- I --- --- D ---
Product Action ORACLE Sizing Main Index Default
# Name | User ID Factor Tablespace Tablespace Tablespace
-- ---------------------- - ------- ------ ---------- ---------- ----------
33 Report Manager FRM 100 FRMD FRMX FRMD
34 Activity Based Managem ABM 100 ABMD ABMX ABMD
35 Balanced Scorecard BSC 100 BSCD BSCX BSCD
36 SEM Exchange EAA 100 EAAD EAAX EAAD
37 Value Based Management EVM 100 EVMD EVMX EVMD
38 Strategic Enterprise M FEM 100 FEMD FEMX FEMD
39 Applications Shared Te S APPLSYS 100 AOLD AOLX AOLD
40 Enterprise Asset Manag EAM 100 EAMD EAMX EAMD

There are 209 Oracle Applications. Enter U/D to scroll up/down.

- To change a database parameter for a product;
INCLUDE the LETTER ABOVE the COLUMN you want to change
U / D / T / B - Press up/down/top/bottom to see other products
[Return] - To return to the AutoUpgrade Main Menu

Enter your choice (for example, 1M) :

Connecting to SYSTEM......Connected successfully.

Saving your choices...
Done.

Backing up restart files, if any......Done.

AutoUpgrade Main Menu
--------------------------------------------------

1. Choose database parameters

2. Choose overall tasks and their parameters

3. Run the selected tasks

4. Exit AutoUpgrade
* Please use License Manager to license additional
* products or modules after the upgrade is complete.

Enter your choice : 2

AutoUpgrade - Choose overall tasks and their parameters

# Task Do it? Parameters
-- ------------------------------------------ ------ --------------------
1 Verify files necessary for upgrade YES
2 Install or upgrade database objects YES

There are 2 tasks. Enter U/D to scroll up/down.

- To change YES to NO or NO to YES
(You cannot change a task marked with a *)
P - To change the parameters of a task
U / D - To page up/down to see other tasks
[Return] - To return to the AutoUpgrade Main Menu

Enter your choice (for example 2 or 2P) :

Saving your choices...
Done.

Backing up restart files, if any......Done.

AutoUpgrade Main Menu
--------------------------------------------------

1. Choose database parameters

2. Choose overall tasks and their parameters

3. Run the selected tasks

4. Exit AutoUpgrade

Enter your choice : 3
Connecting to APPLSYS......Connected successfully.

Connecting to SYSTEM......Connected successfully.

Connecting to APPS......Connected successfully.

AD utilities can support a maximum of 999 workers. Your
current database configuration supports a maximum of 992 workers.
Oracle recommends that you use between 24 and 48 workers.

Enter the number of workers [24] : 24

Oracle 9i to 10gR2 Manual UPGRADE Steps

Steps for Upgrading the Database to 10g Release 2

Preparing to Upgrade

Fresh Install oracle software only 10gR2 on the same 11i instance.

Oracle 9i(9.2.0.6) to Oracle 10g(10.2.0.1)

Oracle 9i home ==> /oracle/app/oracle/testdb/9.2.0

Oracle 10g home ==> /u01/app/oracle/product/10.2.0/

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

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

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

==================================

Step 1

Copy utlu102i.sql , utltzuv2.sql 10g oracle home to /tmp folder. Then run both scripts.
This scripts will show the preupgrade steps.

ORACLE_HOME ==> 10g Home

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

Step 2

Then login 9i oracle home and login sql prompt. Then run that above scripts.

sqlplus '/as sysdba'

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

===================================

Check the log file and solve that issues.

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

=======================================
Step 3

Check the above output file and resolve the warning and failed messages
=======================================

Increase the SYSTEM tablespace

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;

TEMP

alter database tempfile '/oracle/app/oracle/testdata/tmp1.dbf' resize 4096M;

APPS_TS_QUEUES

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;

APPS_TS_TX_DATA

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;

ODM

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;

OLAP

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;

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

============================================

Step 4

Check for the TIMESTAMP WITH TIMEZONE Datatype.

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.

=============================================
Step 5

To gather statistics run this script, connect to the database AS SYSDBA using SQL*Plus.

SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYS');
SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS('ODM');
SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS('OLAPSYS');
SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS('MDSYS');

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

Step 6

REVOKE CONNECT RIGHTS TO ABOVE 12 USERS

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

Take the spool on above scripts and run sql prompt.

===========================================

Remove or comment the following initparameters 10 oracle home.

Copy init.ora file 9i to 10g oracle home, Then change the parameter file.

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

Check the spool log file to Add and Increase the below parameter size

shared_pool_size=181217280
streams_pool_size=50331648
large_pool_size=8388608

===========================================

Check the free tablespace size

select tablespace_name,sum(bytes/1024/1024) from dba_free_space where tablespace_name in ('SYSTEM','APPS_TS_QUEUES','APPS_TS_TX_DATA','ODM','SYSAUX') GROUP BY TABLESPACE_NAME;

===========================================

set below env

export ORACLE_SID=TEST
export ORACLE_BASE=/u01/app/
export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
export PATH=$PATH:$ORACLE_HOME/bin
==========================================

After completing pre upgrade steps, you have to login 10g oracle home.

[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
========================================
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

========================================
SQL>shutdown immediate
========================================

The 9idata directory has no files
so go to following folder and run that perl scripts.
/u01/11i/uat/oracle/uatdb/10.2.0/nls/data/old/cr9idata.pl

bash-2.05b$ perl cr9idata.pl Creating directory /u01/11i/uat/oracle/uatdb/10.2.0/nls/data/9idata ... Copying files to /u01/11i/uat/oracle/uatdb/10.2.0/nls/data/9idata... Copy finished. Please reset environment variable ORA_NLS10 to /u01/11i/uat/oracle/uatdb/10.2.0/nls/data/9idata! bash-2.05b$
============================
Then normal startup

SQL>startup
============================

Compile invalid objects

SQL> @utlrp.sql

========================================
SQL> shut immediate

cp env to oracle_home_10g and edit new values

Copy tnsnames.ora, listeners.ora and sqlnet.ora 9i to new 10g orale home.
cp tns_admin(9i) to oracle 10g

Start listener
================================================

SQL>startup

================================================

SymptomsIn 10gR2, setting the environment variable ORA_NLS10 causes the following error:ERROROra-12705: cannot access nls data files or invalid environment specified ora-127
This is script cr9idata.pl located following path.

/u01/11i/uat/oracle/uatdb/10.2.0/nls/data/old

bash-2.05b$ perl cr9idata.pl

Creating directory /u01/11i/uat/oracle/uatdb/10.2.0/nls/data/9idata ...Copying files to /u01/11i/uat/oracle/uatdb/10.2.0/nls/data/9idata...Copy finished.

Please reset environment variable ORA_NLS10 to /u01/11i/uat/oracle/uatdb/10.2.0/nls/data/9idata!
===========================================================
Implement and run Autoconfig on the new Database home

1. Copy AutoConfig to the RDBMS ORACLE_HOME

Update the RDBMS ORACLE_HOME file system with the AutoConfig files by performing the following steps:

Steps:
* On the Application Tier (as the APPLMGR user):

a) Log in to the APPL_TOP environment and source the APPSORA.env file

b) Create appsutil.zip file. This will create appsutil.zip in $APPL_TOP/admin/out

perl $AD_TOP/bin/admkappsutil.pl

bash-2.05b$ perl admkappsutil.pl

Starting the generation of appsutil.zip
Log file located at /progs2/11i/uat/applmgr/uatappl/admin/log/MakeAppsUtil_09051242.log
output located at /progs2/11i/uat/applmgr/uatappl/admin/out/appsutil.zip
MakeAppsUtil completed successfully.

c) Copy or FTP the appsutil.zip file to the

* On the Database Tier (as the APPLMGR or ORACLE user):

d) cd RDBMS ORACLE_HOME

e) Source 10g CONTEXT_NAME.env file

f) unzip -o appsutil.zip

2. Generate your Database Context File. Execute the following commands to create your Database Context File:

Steps:

a) cd RDBMS ORACLE_HOME

b) CONTEXT_NAME.env

c) cd <10.1.0>/appsutil/bin

d) perl adbldxml.pl tier=db appsuser=APPSuser appspasswd=APPSpwd

perl adbldxml.pl tier=db appsuser=apps appspasswd=xxxxx

Steps:
a) cd /appsutil/bin

b) adconfig.sh contextfile=CONTEXT.XML appspass=APPSpwd

References

Complete Checklist for Manual Upgrades to 10gR2
Doc ID: Note:316889.1
Oracle Applications Release 11i with Oracle 10g Release 2 (10.2.0)
Doc ID: Note:362203.1

Note 135090.1 - Managing Rollback/Undo Segments in AUM (Automatic Undo Management)
Note 159657.1 - Complete Upgrade Checklist for Manual Upgrades from 8.X / 9.0.1 to Oracle9iR2 (9.2.0)
Note 170282.1 - PLSQL_V2_COMPATIBLITY=TRUE causes STANDARD and DBMS_STANDARD to Error at Compile
Note 263809.1 - Complete checklist for manual upgrades to 10gR1 (10.1.0.x)
Note 293658.1 - 10.1 or 10.2 Patchset Install Getting ORA-29558 JAccelerator (NCOMP) And ORA-06512
Note 316900.1 - ALERT: Oracle 10g Release 2 (10.2) Support Status and Alerts
Note 356082.1 - ORA-7445 [qmeLoadMetadata()+452] During 10.1 to 10.2 Upgrade
Note 406472.1 - Mandatory Patch 5752399 for 10.2.0.3 on Solaris 64-bit and Filesystems Managed By Veritas or Solstice Disk Suite software
Note 407031.1 - ORA-01403 no data found while running utlu102i.sql/utlu102x.sql on 8174 database
Note 412271.1 - ORA-600 [22635] and ORA-600 [KOKEIIX1] Reported While Upgrading Or Patching Databases To 10.2.0.3
Note 465951.1 - ORA-600 [kcbvmap_1] or Ora-600 [Kcliarq_2] On Startup Upgrade Moving From a 32-Bit To 64-Bit Release
Note 466181.1 - 10g Upgrade Companion
Note 471479.1 - IOT Corruptions After Upgrade from COMPATIBLE <= 9.2 to COMPATIBLE >= 10.1
Note 557242.1 - Upgrade Gives Ora-29558 Error Despite of JAccelerator Has Been Installed
Oracle Database Upgrade Guide 10g Release 2 (10.2) Part Number B14238-01
http://download.oracle.com/docs/cd/B19306_01/server.102/b14238/toc.htm

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.

Echo $ORACLE_SID
Echo $ORACLE_HOME
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
DATA_TYPE in ('NCHAR','NVARCHAR2', 'NCLOB') and OWNER not in
('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
('SYS','SYSTEM');

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

SQL> select * from nls_database_parameters where parameter
='NLS_NCHAR_CHARACTERSET';

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('');
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 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
- 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
AMERICAN_AMERICA.WE8ISO8859P1

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 Oracle8i or higher this is:
C:\ORADIM -DELETE -SID
And create the new Oracle9i service use ORADIM of the Oracle9i ORACLE_HOME:
C:\ORADIM -NEW -SID -INTPWD -MAXUSERS n
-STARTMODE MANUAL -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 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
DATA_TYPE in ('NCHAR','NVARCHAR2', 'NCLOB') and OWNER not in
('SYS','SYSTEM'); 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
TRUE

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

Doc ID: Note: 159657.1