Upgrade Application Database 9.2.0 to 10.2.0

[oracle@R0580 ~]$ ps -ef |grep pmon

oracle 10544 10514 0 09:38 pts/1 00:00:00 grep pmon

[oracle@R0580 ~]$ ps -ef |grep tns

oracle 10546 10514 0 09:38 pts/1 00:00:00 grep tns

[oracle@R0580 ~]$ cd /11i/prodb/

bash: cd: /11i/prodb/: No such file or directory

[oracle@R0580 ~]$ cd /11i/prodd

proddata/ proddb/

[oracle@R0580 ~]$ cd /11i/proddb/9.2.0/

[oracle@R0580 9.2.0]$ . PROD_r0580.env

[oracle@R0580 9.2.0]$ cd appsutil/scripts/PROD_r0580/

[oracle@R0580 PROD_r0580]$ ./addbctl.sh start

You are running addbctl.sh version 115.8

Starting the database PROD ...

SQL*Plus: Release 9.2.0.6.0 - Production on Thu Aug 30 09:39:29 2012

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

Connected to an idle instance.

ORACLE instance started.

Total System Global Area 581506668 bytes

Fixed Size 452204 bytes

Variable Size 402653184 bytes

Database Buffers 167772160 bytes

Redo Buffers 10629120 bytes

Database mounted.

Database opened.

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.6.0 - Production

addbctl.sh: exiting with status 0

[oracle@R0580 PROD_r0580]$ ./addlnctl.sh

You are running addlnctl.sh version 115.6

addlnctl.sh: too few arguments specified.

addlnctl.sh {start|stop} listener_name

In general, available listeners are

Database Net8 listener : <ORACLE_SID>

[oracle@R0580 PROD_r0580]$ ./addlnctl.sh start PROD

You are running addlnctl.sh version 115.6

Logfile: /11i/proddb/9.2.0/appsutil/log/PROD_r0580/addlnctl.txt

Starting listener process PROD ...

addlnctl.sh: exiting with status 0

[oracle@R0580 PROD_r0580]$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.6.0 - Production on Thu Aug 30 09:42:32 2012

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

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.6.0 - Production

SQL> select * from v$version;

BANNER

----------------------------------------------------------------

Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production

PL/SQL Release 9.2.0.6.0 - Production

CORE 9.2.0.6.0 Production

TNS for Linux: Version 9.2.0.6.0 - Production

NLSRTL Version 9.2.0.6.0 - Production

######## SCRIPT 1 -PREPARE DATABASE##############################

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.

SQL> spool gdict

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

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

SQL> SQL>

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

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed. --------------This will take More times so dont warry.

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

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

SQL> spool sdict

SQL> grant analyze any to sys;

Grant succeeded.

SQL> exec dbms_stats.create_stat_table('SYS','dictstattab');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.export_schema_stats('WMSYS','dictstattab',statown => 'SYS');

BEGIN dbms_stats.export_schema_stats('WMSYS','dictstattab',statown => 'SYS'); END;

*

ERROR at line 1:

ORA-20000: Schema WMSYS does not exist or insufficient privileges to analyze an

object within it

ORA-06512: at "SYS.DBMS_STATS", line 4984

ORA-06512: at "SYS.DBMS_STATS", line 5007

ORA-06512: at "SYS.DBMS_STATS", line 5409

ORA-06512: at line 1

SQL> exec dbms_stats.export_schema_stats('MDSYS','dictstattab',statown => 'SYS');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.export_schema_stats('CTXSYS','dictstattab',statown => 'SYS');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.export_schema_stats('XDB','dictstattab',statown => 'SYS');

BEGIN dbms_stats.export_schema_stats('XDB','dictstattab',statown => 'SYS'); END;

*

ERROR at line 1:

ORA-20000: Schema XDB does not exist or insufficient privileges to analyze an

object within it

ORA-06512: at "SYS.DBMS_STATS", line 4984

ORA-06512: at "SYS.DBMS_STATS", line 5007

ORA-06512: at "SYS.DBMS_STATS", line 5409

ORA-06512: at line 1

Gather Optimizer Statistics Before the Upgrade using the scripts below

SQL> exec dbms_stats.export_schema_stats('WKSYS','dictstattab',statown => 'SYS');

BEGIN dbms_stats.export_schema_stats('WKSYS','dictstattab',statown => 'SYS'); END;

*

ERROR at line 1:

ORA-20000: Schema WKSYS does not exist or insufficient privileges to analyze an

object within it

ORA-06512: at "SYS.DBMS_STATS", line 4984

ORA-06512: at "SYS.DBMS_STATS", line 5007

ORA-06512: at "SYS.DBMS_STATS", line 5409

ORA-06512: at line 1

SQL> exec dbms_stats.export_schema_stats('LBACSYS','dictstattab',statown => 'SYS');

BEGIN dbms_stats.export_schema_stats('LBACSYS','dictstattab',statown => 'SYS'); END;

*

ERROR at line 1:

ORA-20000: Schema LBACSYS does not exist or insufficient privileges to analyze

an object within it

ORA-06512: at "SYS.DBMS_STATS", line 4984

ORA-06512: at "SYS.DBMS_STATS", line 5007

ORA-06512: at "SYS.DBMS_STATS", line 5409

ORA-06512: at line 1

SQL> exec dbms_stats.export_schema_stats('OLAPSYS','dictstattab',statown => 'SYS');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.export_schema_stats('DMSYS','dictstattab',statown => 'SYS');

BEGIN dbms_stats.export_schema_stats('DMSYS','dictstattab',statown => 'SYS'); END;

*

ERROR at line 1:

ORA-20000: Schema DMSYS does not exist or insufficient privileges to analyze an

object within it

ORA-06512: at "SYS.DBMS_STATS", line 4984

ORA-06512: at "SYS.DBMS_STATS", line 5007

ORA-06512: at "SYS.DBMS_STATS", line 5409

ORA-06512: at line 1

SQL> exec dbms_stats.export_schema_stats('ODM','dictstattab',statown => 'SYS');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.export_schema_stats('ORDSYS','dictstattab',statown => 'SYS');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.export_schema_stats('ORDPLUGINS','dictstattab',statown => 'SYS');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.export_schema_stats('SI_INFORMTN_SCHEMA','dictstattab',statown => 'SYS');

BEGIN dbms_stats.export_schema_stats('SI_INFORMTN_SCHEMA','dictstattab',statown => 'SYS'); END;

*

ERROR at line 1:

ORA-20000: Schema SI_INFORMTN_SCHEMA does not exist or insufficient privileges

to analyze an object within it

ORA-06512: at "SYS.DBMS_STATS", line 4984

ORA-06512: at "SYS.DBMS_STATS", line 5007

ORA-06512: at "SYS.DBMS_STATS", line 5409

ORA-06512: at line 1

SQL> exec dbms_stats.export_schema_stats('OUTLN','dictstattab',statown => 'SYS');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.export_schema_stats('DBSNMP','dictstattab',statown => 'SYS');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.export_schema_stats('SYSTEM','dictstattab',statown => 'SYS');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.export_schema_stats('SYS','dictstattab',statown => 'SYS');

PL/SQL procedure successfully completed.

SQL> 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.
   
     $ mkdir <10.2.0 Oracle Home>
   
   
   [oracle@R0580 PROD_r0580]$ cd /11i/prod
   
2. prodappl/ prodcomn/ proddata/ proddb/ prodora/
   
3. [oracle@R0580 PROD_r0580]$ cd /11i/proddb/
   
4. [oracle@R0580 proddb]$ ls
   
5. 9.2.0
   
6. [oracle@R0580 proddb]$ mkdir 10.2.0
   
7. [oracle@R0580 proddb]$ ls
   
8. 10.2.0 9.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. 
   
   
   

Install Server Version 10.2.0.1

Note: This may differ slightly from your prompts depending on the installation media.

Steps:
  unzip <10.2.0.1 Saveset>

[oracle@R0580 9.2.0 database Upgrade patches]$ cd database/

[oracle@R0580 database]$./runInstaller &

1. Check "Advanced Installation" checkbox on the “Welcome” screen
    Click the [Next] button
   
2. Click the [Next] button to accept the “Enterprise Edition” installation
   
3. Choose a new ORACLE_HOME Name (i.e. APPS10G_PROD)
    Verify the location of the Oracle Home(/11i/proddb/10.2.0)
   
   
4. Review the success of "Product Specific Pre-requisites"
    Click the [Next] button
   
5.  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. 
   
6. Choose the “install database software only” radioClick the [next] button
   
7.  Click the [Install] button at the “Summary” screen
   
8.  Run the “root.sh” scripts when prompted
    Click [Ok] to continue after running the script
   
   
   
9.  Click ‘Exit’ to acknowledge the end of installation
    Click the (yes] button leave the installer button
   

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

Install the 10.2.0.1 Companion Products

unzip :-10201_companion_linux32.zip

1. Click the [Next] button at the “Welcome” screen
   
2. Choose "Oracle Database 10g Products 10.2.0.1.0"
   
3. Choose the APPS10G_PROD ORACLE_HOME
     Verify the location of the Oracle Home (/11i/proddb/10.2.0)
     Click the [Next] button
   
4. Specific Prerequisites checks Click the [Next] button
   
5. Click the (Install) button
   
6. Click ‘Exit’ to acknowledge the end of installation
   
7.   Click the (yes] button leave the installer
   

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

Install new version of Oracle Universal Installer

At the time of this document the Patch number was P035661.  These same steps should be executed in the IAS_ORACLE_HOME to synchronize the OUI versions.

1. unzip -o  p5035661_11i_LINUX.zip
   
2. cp -r appsoui/ /11i/proddb/10.2.0/
   
3. [oracle@R0580 PROD_r0580]$ cd
   
4. [oracle@R0580 ~]$ export ORACLE_SID=PROD
   
5. [oracle@R0580 ~]$ export PATH=<IAS_ORACLE_HOME>/Apache/perl/bin:$PATH
   
6. bash: IAS_ORACLE_HOME: No such file or directory
   
7. [oracle@R0580 ~]$ export PATH=IAS_ORACLE_HOME/Apache/perl/bin:$PATH
   
8. [oracle@R0580 ~]$ export PERL5LIB==IAS_ORACLE_HOME/Apache/perl/lib/5.00503
   
9. [oracle@R0580 ~]$ cd /11i/proddb/10.2.0/appsoui/setup/
   
10. [oracle@R0580 setup]$ ls
    
11. OUISetup.jar OUIsetup.pl
    
12. [oracle@R0580 setup]$ perl OUIsetup.pl
    

ORACLE_HOME : /11i/proddb/10.2.0 (APPSDB_PROD)

OUI Location : /11i/proddb/10.2.0/appsoui/oui

CLASSPATH : /11i/proddb/10.2.0/appsoui/setup/OUISetup.jar:/11i/proddb/10.2.0/appsoui/oui/lib/OraInstaller.jar:/11i/proddb/10.2.0/appsoui/oui/lib/xmlparserv2.jar

JRE_INST : /11i/proddb/10.2.0/appsoui/jre/1.1.8

Executing OUI setup now:

/11i/proddb/10.2.0/appsoui/jre/1.3.1/bin/java -classpath /11i/proddb/10.2.0/appsoui/setup/OUISetup.jar:/11i/proddb/10.2.0/appsoui/oui/lib/OraInstaller.jar:/11i/proddb/10.2.0/appsoui/oui/lib/xmlparserv2.jar -DDEBUG_MODE=false oracle.apps.ad.util.OUISetup /11i/proddb/10.2.0 /11i/proddb/10.2.0/appsoui/oui /11i/proddb/10.2.0/appsoui/jre/1.1.8 APPSDB_PROD

Log file : /11i/proddb/10.2.0/appsoui/setup/OUIsetup.log

Checking inventory ...

Updating registration of ORACLE_HOME in global inventory...

Oracle home registration completed.

Removing OUI entries from the global inventory: /etc/oraInventory/ContentsXML/comps.xml

Removing OUI entries from the global inventory completed successfully

Registering the OUI component with local inventory...

OUI 2.2 registration completed.

Perform 10.2.0.4 patch set pre-installation tasks (conditional)

a) Set the environment

1. [oracle@R0580 ~]$ export ORACLE_SID=PROD
   
2. [oracle@R0580 ~]$ export ORACLE_HOME=10.2.0 ORACLE_HOME
   
3. [oracle@R0580 ~]$ export PERL5LIB=$ORACLE_HOME/perl/lib/5.6.1
   
4. [oracle@R0580 ~]$ export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/perl/bin:$PATH
   

1. b) Shutdown the Oracle Cluster Synchronization Services(CSS) daemon as the root user.
   When it is available
   

# /etc/init.d/init.cssd stop

Perform 10.2.0.5 patch set installation tasks:--

1. unzip p8202632_10205_LINUX.zip
   
2. [oracle@R0580 /]$ cd p8202632_10205_LINUX.zip/Disk1/
   
3. [oracle@R0580 Disk1]$ ./runInstaller
   
4. Verify the information on the “Specify File Location” screen
   Name :- APPSIAS_PROD
   Path :-/11i/prodora/iAS
   
5. Verify the information on the “Specify File Location” screen
     Click the [Next) button
   
6. Click the (Install) button on the “Summary”
   
7. Click ‘Exit’ to acknowledge the end of installation screen
   
8. 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.
   

export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/perl/bin:$PATH

Steps:

1. export ORACLE_SID=PROD
   
2. export ORACLE_HOME=/11i/proddb/10.2.0 ORACLE_HOME
   
3. export ORA_NLS10=$ORACLE_HOME/nls/data/9idata
   
4. export ORACLE_HOME=/11i/proddb/10.2.0 ORACLE_HOME
   
5. export PERL5LIB=$ORACLE_HOME/perl/lib/5.6.1
   
6. export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/perl/bin:$PATH
   
7. cd $ORACLE_HOME/nls/data/old
   
8. perl cr9idata.pl
   
9. 
   **************************************************************
   
    
   

Apply latest Oracle Patching Tool (OPatch) patch:- 5601428

1. export ORACLE_SID=PROD
   
2. export ORACLE_HOME=/11i/proddb/10.2.0 ORACLE_HOME
   
3. export PERL5LIB=$ORACLE_HOME/perl/lib/5.6.1
   
4. export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/perl/bin:$PATH
   
5. cd $ORACLE_HOME
   
6. mv Opatch old_OPatch
   
7. cp -r 5601428/ /11i/proddb/10.2.0/
   

Apply additional 10.1.0.4 RDBMS patches :-972639

Steps:

1.   export ORACLE_SID=PROD
   

Upgrade the Database:-

Shutdown the Database and all processes running out of the 9.2.0.6 ORACLE_HOME

Set your environment variables

export ORACLE_SID=PROD

export ORACLE_HOME=/11A/oracle/proddb/10.2.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.]
  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

upgrade log files:- /11A/oracle/proddb/10.2.0/cfgtoollogs/dbua/PROD/upgrade/

mapfile.txt PostUpgrade.log sqls.log upgrade.xml

Oracle_Server.log PreUpgrade.log trace.log

Oracle_Text.log SpaceUsage.txt UpgradeResults.html

[oracle@R0580 dbs]$ cat initPROD.ora

O7_DICTIONARY_ACCESSIBILITY=FALSE

aq_tm_processes=1

background_dump_dest=/11A/oracle/proddb/9.2.0/admin/PROD_r0580/bdump

compatible=9.2.0

control_files=/11A/oracle/proddata/cntrl01.dbf, /11A/oracle/proddata/cntrl02.dbf, /11A/oracle/proddata/cntrl03.dbf

core_dump_dest=/11A/oracle/proddb/9.2.0/admin/PROD_r0580/cdump

cursor_sharing=EXACT

db_block_checking=FALSE

db_block_checksum=TRUE

db_block_size=8192

db_cache_size=167772160

db_file_multiblock_read_count=8

db_files=512

db_name=PROD

dml_locks=10000

java_pool_size=67108864

job_queue_processes=2

log_buffer=10485760

log_checkpoint_interval=100000

log_checkpoint_timeout=1200

log_checkpoints_to_alert=TRUE

max_dump_file_size=20480

nls_comp=binary

nls_date_format="DD-MON-RR"

nls_language="american"

nls_length_semantics=BYTE

nls_numeric_characters=".,"

nls_sort=binary

nls_territory="america"

olap_page_pool_size=4194304

open_cursors=600

optimizer_features_enable=9.2.0

parallel_max_servers=8

parallel_min_servers=0

pga_aggregate_target=1073741824

processes=200

query_rewrite_enabled=true

session_cached_cursors=200

sessions=400

shared_pool_reserved_size=30000000

shared_pool_size=301989888

timed_statistics=TRUE

undo_management=AUTO

undo_retention=1800

undo_tablespace=APPS_UNDOTS1

user_dump_dest=/11A/oracle/proddb/9.2.0/admin/PROD_r0580/udump

utl_file_dir=/usr/tmp, /usr/tmp, /11A/oracle/proddb/9.2.0/appsutil/outbound/PROD_r0580, /usr/tmp

workarea_size_policy=AUTO

streams_pool_size=50331648

session_max_open_files=20[oracle@R0580 dbs]$

Install 10g Listener a. Stop 9i listener

b. Export ORACLE_SID=PROD

c. cd $ORACLE_HOME/bin

d. execute ./netmgr(Network Manager)

e. Name the listener PROD

f.Register database.

Fix Korean lexersConnect to sqlplus as sysdba

execute $ORACLE_HOME/ctx/sample/script/drkorean.sql

@/11A/oracle/proddb/10.2.0/ctx/sample/script/drkorean.sql

List of indexes that use KOREAN_LEXER as top level lexer:

List of indexes that use KOREAN_LEXER as a sub lexer:

"APPLSYS"."FND_LOBS_CTX"

"CS"."CS_FORUM_MESSAGES_TL_N4"

"CS"."CS_INCIDENTS_ALL_TL_N1"

"CS"."CS_KB_ELEMENTS_TL_N2"

"CS"."CS_KB_SETS_TL_N3"

"CS"."CS_KB_SOLN_CAT_TL_N1"

"CS"."SUMMARY_CTX_INDEX"

"ICX"."ICX_QUES_CTX"

Migrate KOREAN_LEXER to KOREAN_MORPH_LEXER

Rebuild all indexes that use korean lexer as top level lexer:

Reindex all documents that use KOREAN_LEXER as sub lexer

reindexing : "APPLSYS"."FND_LOBS" finished.

reindexing : "CS"."CS_FORUM_MESSAGES_TL" finished.

reindexing : "CS"."CS_INCIDENTS_ALL_TL" finished.

reindexing : "CS"."CS_KB_ELEMENTS_TL" finished.

reindexing : "CS"."CS_KB_SETS_TL" finished.

reindexing : "CS"."CS_KB_SOLN_CATEGORIES_TL" finished.

reindexing : "CS"."CS_INCIDENTS_ALL_TL" finished.

reindexing : "ICX"."ICX_QUESTIONS_TL" finished.

SQL>

Run adgrants.sql
a. Connect to sqlplus as sysdba

Execute @$APPL_TOP/admin/adgrants.sql APPLSYS

SQL> @/11A/oracle/prodappl/admin/adgrants.sql APPLSYS

---------------------------------------------------

--- adgrants.sql started at 2012-11-01 09:55:22 ---

Creating PL/SQL profiler objects.

---------------------------------------------------

--- profload.sql started at 2012-11-01 09:55:22 ---

Testing for correct installation

SYS.DBMS_PROFILER successfully loaded.

-----------------------------------------------------

--- profload.sql completed at 2012-11-01 09:55:24 ---

--------------------------------------------------

--- proftab.sql started at 2012-11-01 09:55:24 ---

-----------------------------------------------------

--- profltab.sql completed at 2012-11-01 09:55:26 ---

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

You have new mail in /var/spool/mail/oracle

[oracle@R0580 10.2.0]$

Grant create procedure privilege on CTXSYS

Connect to sqlplus with apps

Execute @$AD_TOP/patch/115/sql/adctxprv [SYSTEM_PASSWORD]
SQL> conn apps/apps

Connected.

SQL> @/11A/oracle/prodappl/ad/11.5.0/patch/115/sql/adctxprv.sql manager CTXSYS

Connecting to SYSTEM

Connected.

PL/SQL procedure successfully completed.

Commit complete.

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@R0580 10.2.0]$

Run Autoconfig on DB Tier and Application Tier

cd /11A/oracle/prodappl/

. APPSPROD_r0580.env

perl /11A/oracle/prodappl/ad/11.5.0/bin/admkappsutil.pl

Starting the generation of appsutil.zip

Log file located at /11A/oracle/prodappl/admin/log/MakeAppsUtil_11011021.log

output located at /11A/oracle/prodappl/admin/out/appsutil.zip

MakeAppsUtil completed successfully.

cp /11A/oracle/prodappl/admin/out/appsutil.zip /11A/oracle/proddb/10.2.0/

cd /11A/oracle/proddb/10.2.0/

unzip -o appsutil.zip

Manually migrate files for the 10.2.0 environment

Migrate the 9.2.0.6 <SID_HOSTNAME>.env to the 10.1.0 ORACLE_HOME and modify the environment file changing the 9.2.0 references to 10.1.0
Note: Global replace command for vi editor
 :g/9.2.0/s//10.2.0/g

cp PROD_r0580.env /11A/oracle/proddb/10.2.0/

vi PROD_r0580.env

:g/9.2.0/s//10.2.0/g

cd /11A/oracle/proddb/10.2.0/appsutil/bin/db/10.2.0/

./adconfig.sh

Generate your Database Context File. 

Execute the following commands to create your Database Context File:

/11A/oracle/proddb/10.2.0

. PROD_r0580.env

/11A/oracle/proddb/10.2.0/appsutil/bin

perl adbldxml.pl tier=db appsuser= apps apps pass=apps

.

[oracle@R0580 bin]$ perl adbldxml.pl tier=db appsuser=apps appspasswd=apps jtop=/11A/oracle/proddb/10.2.0/jre/1.4.2

Starting context file generation for db tier..

Using JVM from /11A/oracle/proddb/10.2.0/jre/1.4.2/bin/java to execute java programs..

The log file for this adbldxml session is located at:

/11A/oracle/proddb/10.2.0/appsutil/log/adbldxml_11011105.log

AC-20010: Error: File - listener.ora could not be found at the location:

/11A/oracle/proddb/10.2.0/network/admin/PROD_r0580/listener.ora

indicated by TNS_ADMIN. Context file can not be generated.

Solution:-

[oracle@R0580 10.2.0]$ cd network/admin/

[oracle@R0580 admin]$ ls

listener.ora PROD_r0580 samples shrept.lst tnsnames.ora

[oracle@R0580 admin]$ cp listener.ora shrept.lst tnsnames.ora s

samples/ shrept.lst

[oracle@R0580 admin]$ cp listener.ora shrept.lst tnsnames.ora /11A/oracle/proddb/10.2.0/network/admin/PROD_r0580/

[oracle@R0580 admin]$ cd /11A/oracle/proddb/10.2.0/network/admin/PROD_r0580/

[oracle@R0580 PROD_r0580]$ ll

total 12

-rw-r--r-- 1 oracle oinstall 636 Nov 1 12:21 listener.ora

-rw-r--r-- 1 oracle oinstall 172 Nov 1 12:21 shrept.lst

-rw-r--r-- 1 oracle oinstall 518 Nov 1 12:21 tnsnames.ora

[oracle@R0580 PROD_r0580]$ lsnrctl stop PROD

lsnrctl start PROD

[oracle@R0580 bin]$ perl adbldxml.pl tier=db appsuser=apps appspasswd=apps jtop=/11A/oracle/proddb/10.2.0/jre/1.4.2

Starting context file generation for db tier..

Using JVM from /11A/oracle/proddb/10.2.0/jre/1.4.2/bin/java to execute java programs..

The log file for this adbldxml session is located at:

/11A/oracle/proddb/10.2.0/appsutil/log/adbldxml_11011225.log

For errors, Check log file :/11A/oracle/proddb/10.2.0/appsutil/log/adbldxml_11011225.log

export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/perl/bin:$PATH

FOLLOWING ERROR OCCURED :

3 >= 3

StackTrace:

java.lang.ArrayIndexOutOfBoundsException: 3 >= 3

at java.util.Vector.setElementAt(Unknown Source)

at oracle.apps.ad.context.GenerateContext.setJRETOP(GenerateContext.java:7483)

at oracle.apps.ad.context.GenerateContext.InstantiateRestForDB(GenerateContext.java:3057)

at oracle.apps.ad.context.GenerateContext.main(GenerateContext.java:8150)

Cause:-

The usage of the wrong Release of the file GenerateContext.java generates the failure :

• GenerateContext.java Release reported in the logfile is 115.87
• Latest version of GenerateContext.java is 115.103
  

Solution:-

To implement the solution, please execute the following steps :

1. Download and apply Patch 4709948 - 'TXK (FND) AUTOCONFIG TEMPLATE ROLLUP PATCH M (APRIL 2006)',
which brings the latest Release of the GenerateContext.java used to build the context file

2. Synchronize the APPS-Tier files with the DB-Tier - appsutil directory

3. Execute Autoconfig on the DB-Tier

___________________________________________________________________________________

AD code level : [11i.AD.I.2]

AutoPatch is complete.

AutoPatch may have written informational messages to the file

/11A/oracle/prodappl/admin/PROD/log/4709948.lgi

Errors and warnings are listed in the log file

/11A/oracle/prodappl/admin/PROD/log/4709948.log

and in other log files in the same directory.

[oracle@R0580 4709948]$

Apply another patch No:- 6365595 for UNSET LD_ASSUME_KERNEL

11IEBS CERT ON OEL5|RH5|SLES10:UNSET LD_ASSUME_KERNEL IN ADGETLNXVER.SH

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

Update - 6365595

Product - Techstack

Release - 11i

Platform - Generic Platform

Built - MAY-28-2008 10:24:58

[oracle@R0580 bin]$ perl adbldxml.pl tier=db appsuser=apps appspasswd=apps jtop=/11A/oracle/proddb/10.2.0/jre/1.4.2

Starting context file generation for db tier..

Using JVM from /11A/oracle/proddb/10.2.0/jre/1.4.2/bin/java to execute java programs..

The log file for this adbldxml session is located at:

/11A/oracle/proddb/10.2.0/appsutil/log/adbldxml_11010321.log

The context file has been created at:

/11A/oracle/proddb/10.2.0/appsutil/PROD_r0580.xml

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 <RDBMS ORACLE_HOME>/appsutil/bin

b)  adconfig.sh contextfile=<CONTEXT> appspass=<APPSpwd>

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.

Adjust the Database parameters

1. Alter Database parameters as suggested in Note: 216205.1
   SQL> alter system set compatible='10.2.0'scope=spfile;
   
   

.  Modify the location of the DUMP files

  a)  Create the new locations for the dump files

  b) Alter the dump locations

SQL> alter system set background_dump_dest='/11A/oracle/proddb/10.2.0/admin/PROD/bdump'scope=spfile;

SQL> alter system set core_dump_dest='/11A/oracle/proddb/10.2.0/admin/PROD/cdump'scope=spfile;

SQL> alter system set user_dump_dest='/11A/oracle/proddb/10.2.0/admin/PROD/udump'scope=spfile;

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

  b)  Alter the UTL_FILE_DIR secondary location
     alter system set utl_file_dir='/usr/tmp,/11A/oracle/proddb/10.2.0/appsutil/outboud/PROD_r0580' scope=spfile;

[oracle@R0580 dbs]$ cat initPROD.ora

*.aq_tm_processes=1

*.background_dump_dest='/11A/oracle/proddb/10.2.0/admin/PROD/bdump'

*.compatible='10.2.0'

*.control_files='/11A/oracle/proddata/cntrl01.dbf','/11A/oracle/proddata/cntrl02.dbf','/11A/oracle/proddata/cntrl03.dbf'

*.core_dump_dest='/11A/oracle/proddb/10.2.0/admin/PROD/cdump'

*.cursor_sharing='EXACT'

*.db_block_checking='FALSE'

*.db_block_checksum='TRUE'

*.db_block_size=8192

*.db_cache_size=167772160

*.db_file_multiblock_read_count=8

*.db_files=512

*.db_name='PROD'

*.dml_locks=10000

*.java_pool_size=67108864

*.job_queue_processes=2

*.log_buffer=10485760

*.log_checkpoint_interval=100000

*.log_checkpoint_timeout=1200

*.log_checkpoints_to_alert=TRUE

*.max_dump_file_size='20480'

*.nls_comp='binary'

*.nls_date_format='DD-MON-RR'

*.nls_language='american'

*.nls_length_semantics='BYTE'

*.nls_numeric_characters='.,'

*.nls_sort='binary'

*.nls_territory='america'

*.O7_DICTIONARY_ACCESSIBILITY=FALSE

*.olap_page_pool_size=4194304

*.open_cursors=600

*.optimizer_features_enable='9.2.0'

*.parallel_max_servers=8

*.parallel_min_servers=0

*.pga_aggregate_target=1073741824

*.processes=200

*.query_rewrite_enabled='true'

*.session_cached_cursors=200

*.session_max_open_files=20

*.sessions=400

*.shared_pool_reserved_size=30000000

*.shared_pool_size=301989888

*.streams_pool_size=50331648

*.timed_statistics=TRUE

*.undo_management='AUTO'

*.undo_retention=1800

*.undo_tablespace='APPS_UNDOTS1'

*.user_dump_dest='/11A/oracle/proddb/10.2.0/admin/PROD/udump'

*.utl_file_dir='/usr/tmp,/11A/oracle/proddb/10.2.0/appsutil/outboud/PROD_r0580'

*.workarea_size_policy='AUTO'

[oracle@R0580 dbs]$

Increase TEMP tablespace as recommended in Note: 216205.1.

SQL> select TABLESPACE_NAME,FILE_NAME,BYTES from dba_temp_files;

TABLESPACE_NAME

------------------------------

FILE_NAME

--------------------------------------------------------------------------------

BYTES

----------

TEMP

/11A/oracle/proddata/temp01.dbf

1153433600

SQL> select bytes/1024 from dba_temp_files;

BYTES/1024

----------

1126400

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.

SQL> alter database tempfile '/11A/oracle/proddata/temp01.dbf' resize 250M;

Database altered.

Gather Statistics for SYS schema :-

sqlplus “/as sysdba”

shutdown immediate

startup restrict

SQL> @/11A/oracle/prodappl/admin/adstats.sql

Connected.

--------------------------------------------------

--- adstats.sql started at 2012-11-01 16:36:43 ---

Checking for the DB version

declare

*

ERROR at line 1:

ORA-12801: error signaled in parallel query server P004

ORA-01652: unable to extend temp segment by 106496 in tablespace SYSTEM

ORA-06512: at "SYS.DBMS_STATS", line 15188

ORA-06512: at "SYS.DBMS_STATS", line 15530

ORA-06512: at "SYS.DBMS_STATS", line 15608

ORA-06512: at "SYS.DBMS_STATS", line 15567

ORA-06512: at line 3

ORA-06512: at line 16

Solution:

ORA-01652: unable to extend temp segment by string in tablespace string

Cause: Failed to allocate an extent of the required number of blocks for a temporary segment in the tablespace indicated.

Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

SQL> alter database datafile '/11A/oracle/proddata/system01.dbf' resize 2000M;

Database altered.

----- Same error occour.

alter tablespace system add datafile '/11A/oracle/proddata/system12.dbf' size 2097152000;

Tablespace altered.

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup restrict

ORACLE instance started.

Total System Global Area 599785472 bytes

Fixed Size 1275032 bytes

Variable Size 419433320 bytes

Database Buffers 167772160 bytes

Redo Buffers 11304960 bytes

Database mounted.

Database opened.

SQL> @/11A/oracle/prodappl/admin/adstats.sql

Connected.

--------------------------------------------------

--- adstats.sql started at 2012-11-02 09:06:11 ---

Checking for the DB version

PL/SQL procedure successfully completed.

------------------------------------------------

--- adstats.sql ended at 2012-11-02 09:55:41 ---

Commit complete.

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@R0580 10.2.0]$

------------------RUN ADCONFIG__________________________

[oracle@R0580 ~]$ cd /11A/oracle/proddb/10.2.0/appsutil/bin/

[oracle@R0580 bin]$ ./adconfig.sh

Enter the full path to the Context file: /11A/oracle/proddb/10.2.0/appsutil/PROD_r0580.xml

Enter the APPS user password:

AutoConfig is configuring the Database environment...

AutoConfig will consider the custom templates if present.

Using ORACLE_HOME location : /11A/oracle/proddb/10.2.0

Classpath : /11A/oracle/proddb/10.2.0/jre/1.4.2/lib/rt.jar:/11A/oracle/proddb/10.2.0/jdbc/lib/ojdbc14.jar:/11A/oracle/proddb/10.2.0/appsutil/java/xmlparserv2.zip:/11A/oracle/proddb/10.2.0/appsutil/java:/11A/oracle/proddb/10.2.0/jlib/netcfg.jar:/11A/oracle/proddb/10.2.0/jlib/ldapjclnt10.jar

Using Context file : /11A/oracle/proddb/10.2.0/appsutil/PROD_r0580.xml

Context Value Management will now update the Context file

Updating Context file...COMPLETED

AutoConfig Script

The autoconfig script uses information from the context file to generate all applications configuration files & updates database profiles. It is located in

Application tier: $COMMON_TOP/admin/scripts/CONTEXT_NAME/adautoconfig.sh

Database tier: $ORACLE_HOME/appsutil/scripts/CONTEXT_NAME/adautoconfig.sh

Now we run the autoconfig script to reconfigure the oracle application system with the updated Applications context.

[oracle@R0580 PROD_r0580]$ ./adautocfg.sh

Enter the APPS user password:

AutoConfig is configuring the Applications environment...

AutoConfig will consider the custom templates if present.

Using APPL_TOP location : /11A/oracle/prodappl

Classpath : /11A/oracle/prodcomn/util/java/1.4/j2sdk1.4.2_04/jre/lib/rt.jar:/11A/oracle/prodcomn/util/java/1.4/j2sdk1.4.2_04/lib/dt.jar:/11A/oracle/prodcomn/util/java/1.4/j2sdk1.4.2_04/lib/tools.jar:/11A/oracle/prodcomn/java/appsborg2.zip:/11A/oracle/prodcomn/java

Using Context file : /11A/oracle/prodappl/admin/PROD_r0580.xml

Context Value Management will now update the Context file

Context Value Management has found new variables that must be defined to update the context file and continue with system configuration

Variable : s_ohs_serveradmin

Details : This parameter sets the e-mail address that the OHS

includes in any error messages it returns to the client.

It may be worth setting up a dedicated address for this purpose.

Enter the Oracle HTTP Server Administrator email address [oracle@iwarelogic.com]:

Updating Context file...COMPLETED

Attempting upload of Context file and templates to database...COMPLETED

Configuring templates from all of the product tops...

Configuring AD_TOP........COMPLETED

Configuring FND_TOP.......COMPLETED

Configuring ICX_TOP.......COMPLETED

Configuring IEO_TOP.......COMPLETED

Configuring ABM_TOP.......COMPLETED

Configuring ECX_TOP.......COMPLETED

Configuring BIS_TOP.......COMPLETED

Configuring GL_TOP........COMPLETED

Configuring AMS_TOP.......COMPLETED

Configuring CCT_TOP.......COMPLETED

Configuring WSH_TOP.......COMPLETED

Configuring CLN_TOP.......COMPLETED

Configuring OKE_TOP.......COMPLETED

Configuring OKL_TOP.......COMPLETED

Configuring OKS_TOP.......COMPLETED

Configuring CSF_TOP.......COMPLETED

Configuring XNC_TOP.......COMPLETED

Configuring IGS_TOP.......COMPLETED

Configuring IBY_TOP.......COMPLETED

Configuring PA_TOP........COMPLETED

Configuring JTF_TOP.......COMPLETED

Configuring MWA_TOP.......COMPLETED

Configuring CN_TOP........COMPLETED

Configuring CSI_TOP.......COMPLETED

Configuring WIP_TOP.......COMPLETED

Configuring CSE_TOP.......COMPLETED

Configuring EAM_TOP.......COMPLETED

Configuring IMT_TOP.......COMPLETED

Configuring FTE_TOP.......COMPLETED

Configuring ONT_TOP.......COMPLETED

Configuring AR_TOP........COMPLETED

Configuring AHL_TOP.......COMPLETED

Configuring OZF_TOP.......COMPLETED

Configuring IES_TOP.......COMPLETED

Configuring CSD_TOP.......COMPLETED

Configuring IGC_TOP.......COMPLETED

AutoConfig completed successfully.

The log file for this session is located at: /11A/oracle/prodappl/admin/PROD_r0580/log/11021451/adconfig.log

[oracle@R0580 PROD_r0580]$

Recreate grants and synonym for apps a. Log in to server with applmgr user
b. Execute adadmin
c. Choose -> Maintain Applications Database Entities menu
d. Choose -> Re-create grants and synonyms for APPS schema