|
| Friday, April 17, 2009 |
| Upgrading 9i to 10g with 11i |
Upgrading 9i (9.2.0.6) to 10g (10.1.0.5) with 11i (11.5.10.2) Applications
Installation Steps
Before Upgrade
1. Take a Cold backup of Oracle Home 9.2.0.6 and Oracle Database after shutting down all Processes and Database.
2. Apply Interoperobility patch for 11.5.10 4335078.
2. Apply Auto Config patch (5478710)
3. 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 -----------------------
4. 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.2.0 Oracle Home.
Decide where you want to install the 10.2.0 Oracle Home on the database server node. It must be in a different directory than the current Oracle Home.
Example :
$ cd /ebis/proddb
$ mkdir 10.2.0
3. Install Oracle10G Release 10.2.0.1 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. OR Remove .env entry from the .profile from oracle user.
(Remove sourcing of environment file from Oracle user profile and login again. Run xhost + as root user)
Install Server Version 10.1.0.2
Note: This may differ slightly from your prompts depending on the installation media.
Steps:
$ . 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
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
THEN INSTALL THE COMPANIAN CD
./runinstaller
SELECT THE SAME ORACLE HOME (10g oracle home)
Select 10g products à next
Run root.sh
Finish the installations.
PATCH SET 10.1.0.5 INSTALLATION
a) Set the environment
b) Set .profile OR
export ORACLE_SID=PROD
export ORACLE_HOME=<10.1.0 ORACLE_HOME>
export PERL5LIB=$ORACLE_HOME/perl/lib/5.8.3
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/perl/bin:$PATH
c) Unzip the patch in $ORACLE_HOME
d) CD Disk1
e) ./runinstaller
f) run root.sh
g) 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.8.3
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/perl/bin:$PATH
h)
i) cd $ORACLE_HOME/nls/data/old
perl cr9idata.pl
The following will message appear:
Please reset environment variable ORA_NLS10 to /ebis/proddb/10.2.0/nls/data/9idata!
Prepare to Upgrade
1.Check the free space on the SYSTEM tablespace. There should be at least 500MB of free space. Temporary tablespace 2GB
$ 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;
Apply OPATCH 2617419
Create soft link
Ln –s /usr/sbin/fuser /sbin/fuser
Unzip the patch into $ORACLE_HOME
APPLY PATCH 4605877
DON’T PERFORM SPECIAL INSTRUCTION IN SPECIFIED IN THE PATCH README.
SHUTDOWN THE OLD (9I)LISTENER
NOTE: Database should be up and running.
CREATE 10G LISTENER
Netca
Give the same instance name as PROD and port as same port.
Upgrade the Database
1.Shutdown all processes running out of the 9.2.0.6 ORACLE_HOME
2.only the database shold be UP and Running.
2.Set your environment variables
export ORACLE_SID=VIS
export ORACLE_HOME=/apps/visdb/10.1.0
export PERL5LIB=$ORACLE_HOME/perl/lib/5.8.3
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.] after upgrade it will take around 7-8 GB depends on your DB size. Please make sure you have sufficient space in that directory.
Click the [next) button
Remove the check for the option to “Recompile Invalid Objects”. This will be during at a later step. If you want the dbua compiles your invalid objects then check the check box(but it will take time to10hr)
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.ora is created
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
New initPROD.ora file will be available in 10.2.0.1 ORACLE_HOME/dbs directory.
Make the below changes in the initPROD.ora and restart the 10.2.0.1 database.
2.Modify the location of the DUMP files
a) Create the new locations for the dump files
b) Alter the dump locations
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
b) Alter the UTL_FILE_DIR secondary location
4.Run utlrp to compile Invalid Objects
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';
After the Database upgrade
Gather statistics for SYS schema for this the free temporary tablespace should be more give 6 to 8gb
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.
Migrate the 9.2.0.6 CONTEXT_NAME directory to the 10.2.0 network/admin directory
Modify the listener.ora, tnsnames.ora and sqlnet.ora to replace the 9.2.0 references with 10.2.0
Setup the environment
export TNS_ADMIN=/apps/visdb/10.1.0/network/admin/
export ORACLE_SID=PROD
export ORACLE_HOME=/ebis/proddb/10.1.0
export PERL5LIB=$ORACLE_HOME/perl/lib/5.8.3
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/ @$AD_TOP/patch/115/sql/adctxprv.sql \
CTXSYS
Manually migrate files for the 10.2.0 environment
Note:-- carefully change all the environment whichever showing the 9i oracle home to 10g
Migrate the 9.2.0.6 .env to the 10.2.0 ORACLE_HOME and modify the environment file changing the 9.2.0 references to 10.2.0
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.2.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) . .env
c) cd <10.2.0 ORACLE_HOME>/appsutil/bin
d) perl adbldxml.pl tier=db appsuser= appspasswd=
Attention: adbldxml uses your current environment settings to generate the context file. Therefore ensure that your environment is correctly sourced.
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 /appsutil/bin
b) adconfig.sh contextfile= appspass=
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 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/
Metalink Documents referred:
1.282038.1 Oracle Applications Release 11i with Oracle Database 10g Release 1 (10.1.0)
2.362202.1 Interoperability Notes Oracle Applications Release 11i with Oracle Database 10g Release 1 (10.1.0)
3.216205.1 - Database Initialization Parameters and Configuration for Oracle Applications 11i.
4.165195.1 - Using Autoconfig to Manage System Configurations with Oracle Applications 11i
5. Note:359483.1-- Upgrading Oracle Server 9.2.0.6 to 10.1.0.4 in an 11.5.10.2 Applications EnvironmentLabels: Upgrading 9i to 10g with 11i |
posted by Srinivasan .R @ 5:09 AM  |
|
|
|
|
|
