This document describes the steps necessary to upgrade the Database from 9.2.06 to 10.1.0.4. The steps were verified on a Linux installation of the Vision Demo. This note should be used as a supplement to Note: 282038.1 and the 10.1.0 Migration Guide. The most current version of this document is in Note: 359483.1 on OracleMetaLink.
This document contains the following sections:
· Before the Database Installation
· Database Installation
· Prepare to upgrade
· Upgrade the Database
· Adjust the Database Parameters
· After the Database Upgrade
· Implement and run Autoconfig on the new Database Home
· Recreate custom database links (Conditional)
· Recreate Grants and synonyms
· Restart Applications
· Related Documents
Before the Database Installation
1. Shutdown the Database and all processes running out of the 9.2.0.6 Oracle Home and perform
a cold backup of the entire 9.2.0.6 Oracle Home.
2. Verify software versions
3. Migrate to Oracle Portal(optional)
4. Deregister the current Database Server(Conditional)
5. Apply the latest ADX(Autoconfig) and TXK(Techstack) patches. (Optional)
ATG.RUP3(4334965) can be applied instead of the latest TXK patch. These patches will insure that all parts of ATG(FND, OAM, WF, Framework, XML Publisher, Concurrent Processing...) are on the latest code fixes.
Note: You should run and pass the TXK Validation script before proceeding with the upgrade.
6. Gather Optimizer Statistics Before the Upgrade using the scripts below.
Note: This script must be run connected AS SYSDBA using SQL*Plus.
SCRIPT #1
--
spool gdict
grant analyze any to sys;
exec dbms_stats.gather_schema_stats('WMSYS',options=>'GATHER', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
exec dbms_stats.gather_schema_stats('MDSYS',options=>'GATHER', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
exec dbms_stats.gather_schema_stats('CTXSYS',options=>'GATHER', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
exec dbms_stats.gather_schema_stats('XDB',options=>'GATHER', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
exec dbms_stats.gather_schema_stats('WKSYS',options=>'GATHER', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
exec dbms_stats.gather_schema_stats('LBACSYS',options=>'GATHER', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
exec dbms_stats.gather_schema_stats('OLAPSYS',options=>'GATHER', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
exec dbms_stats.gather_schema_stats('DMSYS',options=>'GATHER', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
exec dbms_stats.gather_schema_stats('ODM',options=>'GATHER', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
exec dbms_stats.gather_schema_stats('ORDSYS',options=>'GATHER', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
exec dbms_stats.gather_schema_stats('ORDPLUGINS',options=>'GATHER', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
exec dbms_stats.gather_schema_stats('SI_INFORMTN_SCHEMA',options=>'GATHER', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
exec dbms_stats.gather_schema_stats('OUTLN',options=>'GATHER', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
exec dbms_stats.gather_schema_stats ('DBSNMP',options=>'GATHER', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
exec dbms_stats.gather_schema_stats('SYSTEM',options=>'GATHER', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
exec dbms_stats.gather_schema_stats('SYS',options=>'GATHER', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
spool off
----------------------- End of script #1 -----------------------
SCRIPT #2
--
This script creates the stats table, 'dictstattab' and exports the stats for the RDBMS component schemas into it. The export will give error if a particular component schema does not exist in the database. This can happen if a component is not installed or if it is invalid.
--
-- This will be useful incase you want to import the stats back
-- example:
-- Following stmt imports the stats for SYS schema after
-- deleting the existing stats.
-- exec dbms_stats.delete_schema_stats('SYS');
-- exec dbms_stats.import_schema_stats('SYS','dictstattab');
spool sdict
grant analyze any to sys;
exec dbms_stats.create_stat_table('SYS','dictstattab');
exec dbms_stats.export_schema_stats('WMSYS','dictstattab',statown => 'SYS');
exec dbms_stats.export_schema_stats('MDSYS','dictstattab',statown => 'SYS');
exec dbms_stats.export_schema_stats('CTXSYS','dictstattab',statown => 'SYS');
exec dbms_stats.export_schema_stats('XDB','dictstattab',statown => 'SYS');
exec dbms_stats.export_schema_stats('WKSYS','dictstattab',statown => 'SYS');
exec dbms_stats.export_schema_stats('LBACSYS','dictstattab',statown => 'SYS');
exec dbms_stats.export_schema_stats('OLAPSYS','dictstattab',statown => 'SYS');
exec dbms_stats.export_schema_stats('DMSYS','dictstattab',statown => 'SYS');
exec dbms_stats.export_schema_stats('ODM','dictstattab',statown => 'SYS');
exec dbms_stats.export_schema_stats('ORDSYS','dictstattab',statown => 'SYS');
exec dbms_stats.export_schema_stats('ORDPLUGINS','dictstattab',statown => 'SYS');
exec dbms_stats.export_schema_stats('SI_INFORMTN_SCHEMA','dictstattab',statown => 'SYS');
exec dbms_stats.export_schema_stats('OUTLN','dictstattab',statown => 'SYS');
exec dbms_stats.export_schema_stats('DBSNMP','dictstattab',statown => 'SYS');
exec dbms_stats.export_schema_stats('SYSTEM','dictstattab',statown => 'SYS');
exec dbms_stats.export_schema_stats('SYS','dictstattab',statown => 'SYS');
spool off
----------------------- End of script #2 -----------------------
Note: You can ignore the following errors:
*
ERROR at line 1:
ORA-20000: Schema WMSYS does not exist or insufficient privileges to analyze an
ORA-20000: Schema XDB does not exist or insufficient privileges to analyze an
ORA-20000: Schema WKSYS does not exist or insufficient privileges to analyze an
ORA-20000: Schema LBACSYS does not exist or insufficient privileges to analyze
ORA-20000: Schema DMSYS does not exist or insufficient privileges to analyze an
ORA-20000: Schema SI_INFORMTN_SCHEMA does not exist or insufficient privileges
Database Installation
1. Log in as the Oracle user
2. Prepare to create the 10.1.0 Oracle Home.
Decide where you want to install the 10.1.0 Oracle Home on the database server node. It must be in a different directory than the current Oracle Home.
$ mkdir <10.1.0 Oracle Home>
3. Install Oracle10G Release 10.1.0.3 Database Server. Do not run any migrate scripts at this time
The environment variables DISPLAY, TEMP, TMPDIR, TMP should be set before installing and any reference to the 8.0.6 or 9.2.0 Oracle Homes in the path or environment variables should be removed.
Install Server Version 10.1.0.3
Note: This may differ slightly from your prompts depending on the installation media.
Steps:
unzip <10.1.0.3 Saveset>
$ cd
$ . runInstaller
Check "Advanced Installation" checkbox on the “Welcome” screen
Click the [Next] button
Choose a new ORACLE_HOME Name (i.e. APPS10G_
Verify the location of the Oracle Home
Click the [Next] button
Click the [Next] button to accept the “Enterprise Edition” installation
Review the success of "Product Specific Pre-requisites"
Click the [Next] button
Note: You will not be upgrading an existing Database at this time
Click the [Next] button to accept the choice on the “Upgrading an Existing Database” screen.
Choose the “Do not create a starter Database” radio button
Click the [next] button
Click the [Install] button at the “Summary” screen
Run the “root.sh” scripts when prompted
Click [Ok] to continue after running the script
Click ‘Exit’ to acknowledge the end of installation
Click the (yes] button leave the installer
Install the 10.1.0.3 Companion Products
Note: If you are using the 10.1.0.3 Companion CD for Linux x86, apply patch 5071896
Steps:
unzip <10.1.0.3 Companion Savesets>
$ cd
$ . runInstaller
Click the [Next] button at the “Welcome” screen
Choose the APPS10G_
Verify the location of the Oracle Home
Click the [Next] button
Choose "Oracle Database 10g Products 10.1.0.3.0"
Click the [Next] button
Click the (Install) button
Click the [ignore] button to acknowledge the permission error installing lgtoclnt-7.1-1*.rpm
Click the [ignore] button to acknowledge the %filename% error
Run the “root.sh” scripts when prompted
Choose option 3
Click [Ok] to continue after running the script
Click ‘Exit’ to acknowledge the end of installation
Click the (yes] button leave the installer
Install new version of Oracle Universal Installer
At the time of this document the Patch number was 4017155. These same steps should be executed in the IAS_ORACLE_HOME to synchronize the OUI versions.
Steps:
cp p4017155_11i_LINUX.zip <10.1.0 ORACLE_HOME>
cd <10.1.0 ORACLE_HOME>
unzip -o p4017155_11i_LINUX.zip
export ORACLE_SID=
export PATH=
export PERL5LIB==
cd <10.1.0 ORACLE_HOME>/appsoui/setup
perl OUIsetup.pl
Perform 10.1.0.4 patch set pre-installation tasks (conditional)
a) Set the environment
export ORACLE_SID=PROD
export ORACLE_HOME=<10.1.0 ORACLE_HOME>
export PERL5LIB=$ORACLE_HOME/perl/lib/5.6.1
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/perl/bin:$PATH
b) Shutdown the Oracle Cluster Synchronization Services(CSS) daemon as the root user.
# /etc/init.d/init.cssd stop
Perform 10.1.0.4 patch set installation tasks
a) unzip the 10.1.0.4 patchset ( 4163362 )
b) Change directory to the unzip location
c) Install the patchset in the <10.1.0 ORACLE_HOME>
Steps:
. runInstaller
Verify the information on the “Specify File Location” screen
Click the [Next) button
Click the (Install) button on the “Summary” screen
Run the “root.sh” scripts when prompted
Click [Ok] to continue after running the script
Click ‘Exit’ to acknowledge the end of installation
Click the (yes] button leave the installer
Create nls/data/9idata directory
On the database server node, as the owner of the Oracle RDBMS file system and database instance, run the $ORACLE_HOME/nls/data/old/cr9idata.pl script to create the $ORACLE_HOME/nls/data/9idata directory.
After creating the directory, make sure that the ORA_NLS10 environment variable is set to the full path of the 9idata directory whenever you perform steps involving the 10g Oracle home.
Steps:
export ORACLE_SID=PROD
export ORACLE_HOME=<10.1.0 ORACLE_HOME>
export PERL5LIB=$ORACLE_HOME/perl/lib/5.6.1
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/perl/bin:$PATH
cd $ORACLE_HOME/nls/data/old
perl cr9idata.pl
The following will message appear:
Please reset environment variable ORA_NLS10 to /apps/visdb/10.1.0/nls/data /9idata!
Apply latest Oracle Patching Tool (OPatch) patch 2617419
Steps:
export ORACLE_SID=PROD
export ORACLE_HOME=<10.1.0 ORACLE_HOME>
export PERL5LIB=$ORACLE_HOME/perl/lib/5.6.1
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/perl/bin:$PATH
cd $ORACLE_HOME
cp p2617419_10102_GENERIC.zip $ORACLE_HOME
mv Opatch old_OPatch
unzip p2617419_10102_GENERIC.zip
Apply additional 10.1.0.4 RDBMS patches
Note: Review list of patches for other platforms in Note: 282038.1
Steps:
export ORACLE_SID=PROD
export ORACLE_HOME=<10.1.0 ORACLE_HOME>
export PERL5LIB=$ORACLE_HOME/perl/lib/5.6.1
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/perl/bin:$ORACLE_HOME/Opatch:$PATH
unzip p4359526_10104_LINUX.zip
cd 4359526
opatch apply
Prepare to Upgrade
1. Check the free space on the SYSTEM tablespace. There should be at least 500MB of free space.
$ sqlplus system/manager
select nvl(sum(bytes)/1024,0) as "System Tablespace Avail(KB)"
from dba_free_space
where tablespace_name like '%SYSTEM%';
Note: If it is necessary to add a Datafile use a statement similar to the following:
ALTER TABLESPACE system
ADD DATAFILE '/apps2/oracle/proddata/system07.dbf'
SIZE 750M
AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;
Upgrade the Database
1. Shutdown the Database and all processes running out of the 9.2.0.6 ORACLE_HOME
2. Set your environment variables
export ORACLE_SID=VIS
export ORACLE_HOME=/apps/visdb/10.1.0
export PERL5LIB=$ORACLE_HOME/perl/lib /5.6.1
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/perl/bin:$PATH
3. Start the Database Upgrade Assistant
Steps:
dbua
Click the [next) button
Select the Database to upgrade
Click the [next) button
Choose the size for the SYSAUX tablespace
[ We recommend that you use 500 MB as the SYSAUX tablespace size. Set autoextend on for the SYSAUX tablespace.]
Click the [next) button
Remove the check for the option to “Recompile Invalid Objects”. This will be during at a later step.
Click the [next) button
Decide on you “Backup” strategy
Click the [next) button
Decide on your Management Options
Click the [next) button
Review the “Summary” screen
Click the (Finish) button
You may disable the compilation of objects as that is done at a later step.
Click the (Ok) button to acknowledge completion of the upgrade
Review the upgrade results
Decide on your option to configure “Database Passwords”
Decide on your option to “Restore the Database” if you are not satisfied with your upgrade results.
Click the (Close) button to end the upgrade
Note: The following changes are noted in the summary:
A spfile
Large_pool_size = 8388608
Shared_pool_size was changed from 301989888 to 335544320
The following parameters were removed:
- optimizer_max_permutations
- row_locking
- undo_supress_errors
- max_enabled_roles
Adjust the Database parameters
1. Alter Database parameters as suggested in Note: 216205.1
Note: You may encounter an error starting the Database if the value of SGA_TARGET does not match the value of SGA_MAX_SIZE. To find the value SGA_MAX_SIZE, use the command "sho parameter sga_max_size". If the error occurs, modify the value of SGA_TARGET in init
alter system set java_pool_size='150M' scope=spfile;
alter system set sga_target='570M' scope=spfile;
alter system set compatible=’10.1.0’ scope=spfile;
2. Modify the location of the DUMP files
a) Create the new locations for the dump files
b) Alter the dump locations
alter system set background_dump_dest='/apps/visdb/10.1.0/admin/VIS_sysadmin5-linux/bdump' scope=spfile;
alter system set core_dump_dest='/apps/visdb/10.1.0/admin/VIS_sysadmin5-linux/cdump' scope=spfile;
alter system set user_dump_dest='/apps/visdb/10.1.0/admin/VIS_sysadmin5-linux/udump' scope=spfile;
3. Modify the location of the directory for UTL_FILE_DIR
a) Create the new locations for UTL_FILE_DIR secondary directory
cd $ORACLE_HOME/appsutil
mkdir outbound
mkdir mkdir
b) Alter the UTL_FILE_DIR secondary location
alter system set utl_file_dir='/usr/tmp,/apps/visdb/10.1.0/appsutil/outbound/VIS_sysadmin5-linux' scope=spfile;
4. Verify UNDO Management.
SQL> select segment_name, tablespace_name
from dba_rollback_segs;
(Output)
SEGMENT_NAME TABLESPACE_NAME
-------------------------- ------------------------------
SYSTEM SYSTEM
_SYSSMU1$ APPS_UNDOTS1
_SYSSMU2$ APPS_UNDOTS1
_SYSSMU3$ APPS_UNDOTS1
_SYSSMU4$ APPS_UNDOTS1
_SYSSMU5$ APPS_UNDOTS1
_SYSSMU6$ APPS_UNDOTS1
_SYSSMU7$ APPS_UNDOTS1
_SYSSMU8$ APPS_UNDOTS1
_SYSSMU9$ APPS_UNDOTS1
_SYSSMU10$ APPS_UNDOTS1
11 rows selected.
5. Increase TEMP tablespace as recommended in Note: 216205.1.
It is recommended that the temporary tablespace for Oracle Applications users be created using locally managed temp files with uniform extent sizes of 128K. The 128K extent size is recommended because numerous modules such as pricing and planning make extensive use of global temporary tables which also reside in the temporary tablespace. Since each user instantiates a temporary segment for these tables, large extent sizes may result in space allocation failures.
Example:
SQL> create temporary tablespace temp2
tempfile '/apps/visdata/tmp2.dbf' size 2000M reuse
extent management local
uniform size 128K;
SQL> alter database default temporary tablespace TEMP2;
SQL> drop tablespace temp;
6. Run utlrp to compile Invalid Objects
Note: If you will compile PL/SQL natively, you do not need to run utlrp.sql at this point.
The utlrp.sql script recompiles all existing PL/SQL modules that were previously in an INVALID state, such as packages, procedures, types and so on. These actions are optional; however, they ensure that the cost of recompilation is incurred during installation rather that in the future.
To run the utlrp.sql script, complete the following steps:
a) Change to the ORACLE_HOME/rdbms/admin directory.
b) Login as a user with SYSDBA privileges.
c) Run the utlrp.sql
Note: This scripts will take several hours to run
7. Check for Installation of Oracle XML DB
select count(1) from dba_registry where comp_id='XDB';
8. Decide on implementing "Natively compile PL/SQL code"
After the Database upgrade
Gather statistics for SYS schema
Steps:
$ sqlplus "/ as sysdba"
SQL> shutdown normal;
SQL> startup restrict;
SQL> @adstats.sql
SQL> shutdown normal;
SQL> startup;
SQL> exit;
Start the New Database listener if not already started.
Migrate the 9.2.0.6 CONTEXT_NAME directory to the 10.1.0 network/admin directory
Modify the listener.ora, tnsnames.ora and sqlnet.ora to replace the 9.2.0 references with 10.1.0
Setup the environment
export TNS_ADMIN=/apps/visdb/10.1.0/network/admin/VIS_sysadmin5-linux
export ORACLE_SID=VIS
export ORACLE_HOME=/apps/visdb/10.1.0
export PERL5LIB=$ORACLE_HOME/perl/lib/5.6.1
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/perl/bin:$PATH
lsnrctl start
Run adgrants.sql (Conditional)
If you have at least AD.I or Applications release 11.5.10 installed on your system, copy $APPL_TOP/admin/adgrants.sql from the administration server node to the database server node. Use SQL*Plus to connect to the database as SYSDBA and run the script using the following command:
sqlplus "/ as sysdba" @adgrants.sql
Grant create procedure privilege on CTXSYS
From the administration server node, use SQL*Plus to connect to the database as APPS and run the following command:
sqlplus apps/
Apply Oracle Data Mining patch 4321329 (conditional)
If Oracle Marketing (AMS) is licensed and you have Oracle Data Mining installed on your database server, apply 4321329 to each application tier server node Oracle home.
Manually migrate files for the 10.1.0 environment
Migrate the 9.2.0.6
Note: Global replace command for vi editor
:g/9.2.0/s//10.1.0/g
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
c) Copy or FTP the appsutil.zip file to the
* On the Database Tier (as the APPLMGR or ORACLE user):
d) cd
e) Source <10.1.0 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
b) .
c) cd <10.1.0 ORACLE_HOME>/appsutil/bin
d) perl adbldxml.pl tier=db appsuser=
Attention: adbldxml uses your current environment settings to generate the context file. Therefore ensure that your environment is correctly sourced.
Attention: If you build the context file for an 11i instance that runs on RAC, all your RAC instances have to be up and running while executing the adbldxml utility. The utility connects to all RAC instances to gather information about the configuration.
3. Generate and Apply AutoConfig Configuration files
Attention: This step performs the conversion to AutoConfig. Once completed, the previous configuration will not be available.
Attention: The database server and the database listener must remain available during the AutoConfig run. All the other database tier services should be shut down.
Execute the following commands:
Steps:
a) cd
b) adconfig.sh contextfile=
Warning: Running AutoConfig on the database node will update the RDBMS network listener file. Be sure to review the configuration changes from step 3. The new AutoConfig network listener file supports the use of IFILE to allow for values to be customized or added as needed.
Note: Running AutoConfig on the database tier will NOT overwrite any existing init.ora file. If no init.ora file exists in your instance, AutoConfig will generate an init.ora file for you.
Note: Running AutoConfig might change your existing environment files. After running AutoConfig, you should always set the environment before you run any Applications utilities in order to apply the changed environment variables.
Recreate Custom Database links(Conditional)
To test the database links
sqlplus apps/
select db_link from dba_db_links;
select sysdate from dual@
i.e. select sysdate from dual@APPS_TO_APPS
Recreate Grants and Synonyms
Steps:
cd $APPL_TOP
. APPSORA.env
adadmin
Choose Menu Item #4 -Maintain Applications Database Entities menu
Choose Sub Menu Item #2 - Re-create grants and synonyms for APPS schema
Choose the number of workers
Restart Applications
Steps:
cd $APPL_TOP
. APPSORA.env
cd $APPLCSF/scripts/$CONTEXT_NAME
. adstrtal.sh apps/
Related Documents
Notes:
- 282038.1 - Oracle Applications Release 11i with Oracle Database 10g Release 1 (10.1.0)
- 216205.1 - Database Initialization Parameters and Configuration for Oracle Applications 11i.
- 165195.1 - Using Autoconfig to Manage System Configurations with Oracle Applications 11i
Manuals:
- Oracle Database Upgrade Guide 10G Release 1 (10.1) - Part No. B10763-02
No comments:
Post a Comment