MIGRATION Oracle EBS 11.5.9 with Database 10g Part II

Continue from previous posting.

B. Upgrade the Database of Oracle EBS

1. Check Prerequisites

• Compatibility

If the database still using original DB (EBS 11.5.9 using Oracle DB 9i) then we need to upgrade it first. Check the compatibility matrix on NOTE: 316889.1, which path that we must take. Minimum Version of the database that can be directly upgraded to Oracle 10g Release 2

      8.1.7.4 -> 10.2.X.X.X
      9.0.1.4 or 9.0.1.5 -> 10.2.X.X.X
      9.2.0.4 or higher -> 10.2.X.X.X
      10.1.0.2 or higher -> 10.2.X.X.X

The following database version will require an indirect upgrade path.

      7.3.3 (or lower) -> 7.3.4 -> 8.1.7 -> 8.1.7.4 -> 10.2.X.X.X
      7.3.4 -> 8.1.7 -> 8.1.7.4 -> 10.2.X.X.X
      8.0.n -> 8.1.7 -> 8.1.7.4 -> 10.2.X.X.X
      8.1.n -> 8.1.7 -> 8.1.7.4 -> 10.2.X.X.X

• OS Patch

We will know which OS Patch that needed to apply before we can install the Oracle DB 10g when we running Oracle Installer. In our case, we only need to install patch PHSS_36087, PHSS_36086, PHSS_36088.

2. In our environment, the version of the database is 9.2.0.3 so it can’t direct upgrade to 10.2.0.X. We plan to upgrade it to 9.2.0.8 first using patchset 4547809. If the original database can upgraded directly as mention in the NOTE: 316889.1 than you can pass this step (go to step 3).

Process upgrade new release in the same version (9i, 9.2.0.3 to 9.2.0.8):

Stage 1: Preinstallation

1. Shutdown Application and Database

2. Backup

Stage 2: Install Patchset

1. Log in as the Oracle software owner (typically oracle).

2. Set DISPLAY

Bourne, Bash, or Korn shell:

             $ DISPLAY=local_host:0.0 ; export DISPLAY

C shell:

% setenv DISPLAY local_host:0.0

3. Enter following commands to start Oracle Universal Installer, where patchset_directory is the directory you unpacked the patch set software:

$ patchset_directory/Disk1
$ ./runInstaller

4. On the Welcome screen, click Next.

5. On the Specify File Locations screen, click Browse next to the Path field in the Source section.

6. Select the products.xml file from the stage directory where you unpacked the patch set files, then click Next. For example:

patchset_directory/Disk1/stage/products.xml

7. In the Name field in the Destination section, select the name of the Oracle home that you want to update from the drop down list, then click Next.

8. If you are installing the patch set on an Oracle RAC cluster, click Next when the Selected Nodes screen appears.

9. On the Summary screen, click Install. This screen lists all of the patches available for installation.

10. When prompted, run the $ORACLE_HOME/root.sh script as the root user. If you are applying the patch set to an Oracle RAC installation, then run the root.sh script on each node of the cluster.

11. On the End of Installation screen, click Exit, then click Yes to exit from Oracle Universal Installer.

Stage 3: Check Requirement Post Installation Patch, Before Upgrade:

1. Check SYSTEM Tablespace Size.

If JServer is part of the installation ensure that there is at least 10 MB of free space allocated to the SYSTEM tablespace.

2. Check XDB Tablespace Size.

For Oracle RAC installations, ensure that there is at least 50 MB of free space allocated to the XDB tablespace.

3. Set the SHARED_POOL_SIZE and JAVA_POOL_SIZE Initialization Parameters.

Users who have JVM (Java enabled) or JVM and XDB installed on their 9.2.0.1 databases should make sure that the init.ora parameters SHARED_POOL_SIZE and JAVA_POOL_SIZE are each 150 MB or more before running the catpatch.sql upgrade script. Failure to do so could result in an unrecoverable memory failure while running of the script. Please note that JVM and XML DB was shipped as part of the default 9.2.0.1 seed database, and will be present unless the user explicitly installed a 9.2.0.1 instance without them.

Stage 4: Upgrade to new release

1. Log in as the Oracle software owner (typically oracle).

2. Start the Oracle Net listener as follows:

 $ lsnrctl start

3. Use SQL*Plus to log in to the database as the SYS user with SYSDBA privileges:

$ sqlplus /nolog
SQL> CONNECT SYS/SYS_password AS SYSDBA 

Enter the following SQL*Plus commands:

SQL> STARTUP MIGRATE
SQL> SPOOL patch.log
SQL> @?/rdbms/admin/catpatch.sql
SQL> SPOOL OFF

Review the patch.log file for errors and inspect the list of components that is displayed at the end of catpatch.sql script.

This list provides the version and status of each SERVER component in the database. If necessary, rerun the catpatch.sql script after correcting any problems.

Restart the database:

SQL> SHUTDOWN
SQL> STARTUP

4. Run the utlrp.sql script to recompile all invalid PL/SQL packages now instead of when the packages are accessed for the first time. This step is optional but recommended.

SQL> @?/rdbms/admin/utlrp.sql

5. Check component status:

Set lin 200
Col comp_name format a40
SQL> select comp_name, version, status from dba_registry;

If you found invalid component, please check troubleshooting below.

6. If you are using the Oracle Recovery Manager catalog, enter the following command:

$ rman catalog username/password@alias
RMAN> UPGRADE CATALOG;

 

3. Install Oracle Database 10g

From compatibility matrix, we found our original database version is 9.2.0.5.0, and it can direct upgrade to 10.2.0.1.0. Install only the software, choose Enterprise Editions. (Check Troubleshooting if you found problem)

4. Install 10gDB Product from companion

5. Install PatchSet 10.2.0.2

6. Create SYSAUX Tablespace in old version (9i), minimum 500MB.

SQL> CREATE TABLESPACE sysaux DATAFILE ‘sysaux01.dbf’ SIZE 500m
     AUTOEXTEND on NEXT 8m MAXSIZE 2048m
     EXTENT MANAGEMENT local UNIFORM SIZE 128k
     SEGMENT SPACE MANAGEMENT auto;

7. Collect Database Link Information

After upgrade the database, Database Link will be invalid. Run the script skeleton_dblink.sql as sysdba. You can find the script in Appendix B. The script will generate script Recreate_DBLink.sql.

$ sqlplus '/as sysdba' @skeleton_dblink.sql

8. Shutdown Listener old Version (9i), Database and Applications server

9. Create PFILE on Target Version (10g), Edit the template in APPENDIX A.

Check Init Parameter :

- java_pool_size at least 150M
- large_pool_size at least 150M
- shared_pool_size at least 150M

10. Setting environment for new Version (10g)

export ORACLE_HOME=/some_path/10.2.0
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/perl/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PERL5LIB=$ORACLE_HOME/perl/lib/5.8.3:$ORACLE_HOME/perl/lib/site_perl/5.8.3
export ORACLE_SID=sid

11. 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 enable the 10g Oracle home.

export ORA_NLS10=$ORACLE_HOME/nls/9idata

##################################################

BUT, if the files of 9idata is empty, then better you change the env ORA_NLS10 to $ORACLE_HOME/nls/data

export ORA_NLS10=$ORACLE_HOME/nls/data

because you will find error “ORA-12701: CREATE DATABASE character set is not known” and the database won’t up.

##################################################

12 . Upgrade the Database, we can used Database Upgrade Assistant(DBUA), but in here we do upgrade manually :

- Startup (upgrade) from new version (10g)

SQL> STARTUP UPGRADE PFILE=initSID.ora

- Upgrade Database

SQL> spo upgrade.log
SQL> @?/rdbms/admin/catupgrd.sql
SQL> spo off 

- Shutdown

SQL> SHUTDOWN IMMEDIATE

- Startup (normal)

SQL> STARTUP PFILE=initSID.ora

- Compile invalid objects

SQL> @?/rdbms/admin/utlrp.sql

- Check Component

Set lin 200
Col comp_name format a40
SQL> select comp_name, version, status from dba_registry;

- Upgrade Enterprise manager

emca -upgrade db 

13. Startup Listener 10.2.0

Before you do this, better you add the listener and service name in the new $ORACLE_HOME refer to the same instance name.

!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

!!! Attention : !!!

If event=”38004 trace name context forever, level 1″ is defined in either the initialization parameter file (init<SID>.ora) or the server parameter file (spfile<SID>.ora), remove it.

!!! Attention (Windows only) : !!!

Make sure that the Services applet window is closed on the database server node. You cannot remove the current database instance service during the upgrade if this window is open

!!! Attention : !!!

Unset the TNS_ADMIN environment variable.

!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

14 . Check for corruption in the dictionary, use the following commands in sqlplus connected as sys:

SET verify off
SET space 0
SET line 120
SET heading off
SET feedback off
SET pages 1000
SPOOL analyze.sql
SELECT 'Analyze cluster "'
       || cluster_name
       || '" validate structure cascade;'
  FROM dba_clusters
 WHERE owner = 'SYS'
UNION
SELECT 'Analyze table "'
       || table_name
       || '" validate structure cascade;'
  FROM dba_tables
 WHERE owner = 'SYS'
   AND partitioned = 'NO'
   AND (iot_type = 'IOT' OR iot_type IS NULL)
UNION
SELECT 'Analyze table "'
       || table_name
       || '" validate structure cascade into invalid_rows;'
  FROM dba_tables
 WHERE owner = 'SYS'
   AND partitioned = 'YES';
SPOOL off

This creates a script called analyze.sql.

Now execute the following steps.

$ sqlplus '/as sysdba'
SQL> @$ORACLE_HOME/rdbms/admin/utlvalid.sql
SQL> analyze.sql

 

This script (analyze.sql) should not return any errors.

15. Ensure that all Snapshot refreshes are successfully completed, and that replication is stopped.

$ sqlplus '/as sysdba'
SQL> select distinct(trunc(last_refresh)) from dba_snapshot_refresh_times; 

16. Run adgrants.sql

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

17. Grant create procedure privilege on CTXSYS

Copy $AD_TOP/patch/115/sql/adctxprv.sql from the administration server node to the database server node.

If you are upgrading to R12, use the R12 version of the file.

Use SQL*Plus to connect to the database as APPS and run the script using the following command:

 sqlplus apps/<APPS password> @adctxprv.sql <SYSTEM password> CTXSYS 

18. Create Appsutil

On the Application Tier (as the APPLMGR user):

a. Source the environment file.

b. Create the appsutil.zip file by executing:

    $ADPERLPRG $AD_TOP/bin/admkappsutil.pl

(On Windows: %ADPERLPRG% %AD_TOP%\bin\admkappsutil.pl)

This will create appsutil.zip in $APPL_TOP/admin/out.

On the Database Tier (as the ORACLE user):

a. Copy or FTP the appsutil.zip file to the <RDBMS ORACLE_HOME> and extract it

cd <RDBMS ORACLE_HOME>

unzip -o appsutil.zip

b. Build <CONTEXT_NAME>.xml file

cd <RDBMS_ORACLE_HOME>/appsutil/bin
perl adbldxml.pl tier=db appsuser=<APPSuser>

c. Run AutoConfig by executing:

cd <RDBMS ORACLE_HOME>/appsutil/bin
adconfig.sh contextfile=<CONTEXT_NAME>.xml
(On Windows use adautocfg.cmd).

19. Gather statistics for SYS schema

Copy $APPL_TOP/admin/adstats.sql from the administration server node to the database server node. Note that adstats.sql has to be run in restricted mode. Use SQL*Plus to connect to the database as SYSDBA and use the following commands to restart the database in restricted mode, run adstats.sql, and restart the database in normal mode:

$ sqlplus "/ as sysdba"
SQL> shutdown normal;
SQL> startup restrict;
SQL> @adstats.sql
SQL> shutdown normal;
SQL> startup;
SQL> exit;

20. Recreate DB Link

Run Recreate_DBLink.sql that already generate at the step 7.

$ sqlplus '/as sysdba' @Recreate_DBLink.sql

21 . Recreate grants and synonyms for APPS schema

Oracle Database 10g Release 2 (10.2) contains new functionality for grants and synonyms compared to previous database releases. As a result, you must re-create the grants and synonyms in the APPS schema.

On the administration server node, as the owner of the Applications file system, run AD Administration and select the “Recreate grants and synonyms for APPS schema” task from the Maintain Applications Database Objects menu.

If MRC is implemented in your Applications system, then run the “Maintain Multiple Reporting Currencies schema” task to re-create MRC triggers in the Apps schema.

22. Running adconfig in Applications Tier

23. Restart Applications server processes

Remember, using net listener 10g.

24. Gather Schema Statistic
Remember to gather all schema statistic with minimum sample size 10%. After upgrade usually some object statistics is locked. Please find the solution in Issue.

25. If you have Supply Chain module, please running “Refresh Collection Snapshot” with COMPLETE REFRESH from manufacturing module before you running Data Collection.

26. Setting Data Protector to integrate with Oracle 

- Initialization Parameter ‘remote_login_passwordfile’ must ‘EXCLUSIVE’
-  Create orapwd file
- Copy tns_names.ora from $TNS_ADMIN to $ORACLE_HOME/network/admin
- Create link for library libobk.sl o ln -s /opt/omni/lib/libob2oracle8_64bit.sl $ORACLE_HOME/lib/libobk.sl
- Re-configure Backup Specification for RMAN in Data Protector

The last step of the process migration is migrate the applications and database to new platform, Insya Allah I will write it down in here at next post…