Oracle E-Business Suite R12.1.1 Upgrade - Database upgrade to 11.2.0

很早以前做的的东西,放自己电脑中,也没啥意义
今天看到那帮做Consultant 的哥们,天天加班熬夜,脸色发乌. 心理感觉很不舒服..
把这些经验教训总结一下.兄弟们少加点班,身体是自己的,生活还是要享受的!

但真正Oracle E-Business Suite R12 的高可用性或者优化的详细的总结,暂时不会发布出来

[@more@]

环境：

Sun EXADATA V2

Oracle Linux5.3

EBS R12.1.1

DB ：11.1.0.7 –〉11.2.0.1

主要介绍EBS DB的升级部分

Section 1: Upgrading an R12 Database to Oracle Database 11g Release 2 (11.2.0)

Before the Database Installation:

- Apply patch 11g Release 2 interoperability patch for Release 12.1 (9062910)

- Apply patch 12.1 TXK Delta 2 patch (7651166)

这两个补丁没有特别的，只是按一般EBS 打补丁的方法就够了

Autoconfig 补丁，8966480. 说是已经包含在上面的7651166 里面了，所以不用管

Apply patch 6400501 (conditional)

If you are on a UNIX/Linux platform, apply patch 6400501 to the iAS 10.1.2 Applications tier Oracle home.

这个补丁，我看只有11.1.6 没搞太明白是说的DB 的版本，还是IAS 的，都对不上，所以没打

Deregister the current database server (conditional)

1. $ perl $ORACLE_HOME/appsutil/bin/adgentns.pl appspass=apps

2. contextfile=$CONTEXT_FILE -removeserver

没做

1. Update application tier context file with new database listener port number (conditional)

The new 11.2.0 Oracle home uses its own database listener for the database instance, replacing the current database listener. Use the Context Editor to update the following variables in the Applications context file on each application tier server node to reflect the 11.2.0 configuration:

Variable Name	Value
s_dbhost	New database hostname
s_dbdomain	New database domain name
s_db_serv_sid	New database SID
s_dbport	New database listener port
s_apps_jdbc_connect_descriptor	NULL

没做，这个没什么改变，不用做

Database Installation:

5. Prepare to create the 11.2.0 Oracle home

The 11.2.0 Oracle home must be installed on the database server node in a different directory than the current Oracle home.

这个很容易理解，安装同一个目录上也安装不上，所以，必须是两个不同的目录

5. Install the base 11.2.0 software

Log in to the database server node as the owner of the Oracle RDBMS file system and database instance. Ensure that environment settings, such as ORACLE_HOME, are set for the new Oracle home you are about to create, and not for any existing Oracle homes on the database server node. Perform all the steps in Chapter 3 of the Oracle Database Installation Guide 11g Release 2 (11.2) for your platform.

In the Installation Types window, use the Product Languages button to select any languages other than American English that are used by your Applications database instance. Choose the Enterprise Edition installation type. In the subsequent windows, select the options not to upgrade an existing database and to install the database software only.

这个地方，需要注意的是，要把/etc/oratab 和 /etc/oraInst.loc move 和备份好

设置ORACLE_BASE 为/u01/oracle/PROD/db/tech_st ，ORACLE_HOME为：/u01/oracle/PROD/db/tech_st/11.2.0(new home)

5. Install Oracle Database 11g Products from the 11g Examples CD

On the database server node, as the owner of the Oracle RDBMS file system and database instance, perform the tasks in section 3, "Download Oracle Database Examples" in the Oracle Database Examples CD Installation Guide.

In the Installation Types window, use the Product Languages button to select any languages other than American English that are used by your Applications database instance.

After the installation, make sure that:

a. The ORACLE_BASE environment variable must be set accordingly.

b. The ORACLE_HOME environment variable points to the new 11.2.0 Oracle home.

c. The PATH environment variable includes $ORACLE_HOME/bin and the directory where the new perl executable is located (usually $ORACLE_HOME/perl/bin).

d. The LD_LIBRARY_PATH environment variable includes $ORACLE_HOME/lib.

e. The PERL5LIB environment variable points to the directories where the new perl libraries are located (usually $ORACLE_HOME/perl/lib/[perl version] and $ORACLE_HOME/perl/lib/site_perl/[perl version])

这里安装的时候一定要选择上面安装好的ORACLE_HOME,会找到NEW ORACLE HOME的，别的没有特殊的，参数检查不合格，忽略掉就OK了

上面的这些参数要在原来的EBS 的 DB 。ENV 上修改，除了上面的内容，这个时候别的内容不要改带新ORACLE——HOME上 最好新弄一个普通DB 用的环境变量，这样可以混合使用，这个是个人总结，这个时间点环境变-量很乱。值得注意

用db.env 原来的环境变量

Sqlplus

Sql>startup upgrade

运行prod.env

/etc/oratab 写11.1.7 的home

5. 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 11g Oracle home.

这个要说明的是新的HOME，并且ORA——NLS10 也是新的HOME

5. Apply additional 11.2.0.1 RDBMS patches

Apply the following patches:

For all UNIX/Linux platforms apply patch 9218789.

Do not run any of the post install instructions as those will be done after the upgrade

打这个补丁一定要注意，只是opatch 做完了就够了，省下的要在数据库中执行的，等下面升级完再做，一定不要提前做，提前做了，会有想不到的问题

Database Upgrade:

1. Shut down Applications server processes and database listener

On each application tier server node, shut down all server processes or services. On the database tier server node, shut down the Oracle Net or Net8 database listener in the old Oracle home.

Note: The Applications will be unavailable to users until all remaining tasks in this section are completed.

10. Drop SYS.ENABLED$INDEXES (conditional)

If the SYS.ENABLED$INDEXES table exists, use SQL*Plus to connect to the database as SYSDBA and running the following command to drop it:

SQL> drop table sys.enabled$indexes;

这里用的是原来的11.1.7 和新安装的没11.2 没任何关系

11. Prepare to upgrade

Read Chapter 2 of Oracle Database Upgrade Guide 11g Release 2 (11.2). Take note of the section pertaining to the Database Upgrade Assistant (DBUA

Note: Ensure that the oratab file contains an entry for the database to be upgraded.

做这个之前，要看一下普通DB 升级的文档，有一个预安装的工具，

检查后，只有SYSAUX 的表空间不够，别的都足够了

Run the Pre-Upgrade Information Tool

After you have installed Oracle Database 11g Release 2 (11.2) and any required patches, you should analyze your database before upgrading it to the new release. This is done by running the Pre-Upgrade Information Tool. This is a required step if you are upgrading manually, otherwise the catupgrd.sql script will terminate with errors. Running the Pre-Upgrade Information Tool is also recommended if you are upgrading with DBUA, so that you can preview the items that DBUA checks.

The Pre-Upgrade Information Tool is a SQL script that ships with Oracle Database 11g Release 2 (11.2), and must be copied to and run from the environment of the database being upgraded. Complete the following steps to run the Pre-Upgrade Information Tool:

1. Log in to the system as the owner of the Oracle Database 11g Release 2 (11.2) Oracle home directory.

2. Copy the Pre-Upgrade Information Tool (utlu112i.sql) from the Oracle Database 11g Release 2 (11.2) ORACLE_HOME/rdbms/admin directory to a directory outside of the Oracle home, such as the temporary directory on your system.

Make a note of the new location of this file.

3. Log in to the system as the owner of the Oracle home directory of the database to be upgraded.

4. Change to the directory that you copied utlu112i.sql to in Step 2.

5. Start SQL*Plus.

6. Connect to the database instance as a user with SYSDBA privileges.

7. Set the system to spool results to a log file for later analysis

8. SQL> SPOOL upgrade_info.log

2. Run the Pre-Upgrade Information Tool:

3. SQL> @utlu112i.sql

4. Turn off the spooling of script results to the log file:

5. SQL> SPOOL OFF

Check the output of the Pre-Upgrade Information Tool in upgrade_info.log.

The following is an example of the output generated by the Pre-Upgrade Information Tool:

Oracle Database 11.2 Pre-Upgrade Information Tool 10-14-2008 23:25:25

.

1. Upgrade the database instance

The instructions for the database upgrade are outlined in Oracle Database Upgrade Guide 11g Release 2 (11.2) Chapters 3 and 4.

Perform the steps from the "Run the Pre-Upgrade Information Tool" section to the "Upgrade the Database Using the Database Upgrade Assistant" section of chapter 3 and any step in chapter 4 that is relevant to your environment.

Attention: Before performing any upgrade or maintenance operation, all customers must have the _disable_fast_validate parameter in their initialization file.

When running dbua, set the _disable_fast_validate parameter using the following command:

$ dbua -initParam "_disable_fast_validate=TRUE"

这个地方要注意 要把_disable_fast_validate 加到参数文件中去

最关键的地方是要当中要报一个ORA-01408 ：such column list already indexed 的错误，

如果没提前做了The workaround is to drop the REPCAT$_AUDIT_COLUMN_IDX1 index and rerun the

upgrade as described in "Rerunning the Upgrade" on page 3-45.

这个时候需要恢复到升级前的状态，执行完drop the REPCAT$_AUDIT_COLUMN_IDX1 后，再重新进行升级

第一次测试的时候，还出现要求把DBV 关掉的提示，但第2次没有出现这个错误

1. Modify initialization parameters

Use the following sections in document 396009.1, Database Initialization Parameter Settings for Oracle Applications Release 12 on My Oracle Support as a guideline:

1. 1. Common database initialization parameters

   2. Release-specific database initialization parameters for 11gR2

   3. Database initialization parameter sizing

Ensure the parameter "_disable_fast_validate=TRUE" is in the initialization file. This line will have to be commented out after the upgrade and any other maintenance procedures.

Attention: If you encounter the error: ORA-04030: out of process memory when trying to allocate 822904 bytes (pga heap, kco buffer) ORA-07445: exception encountered: core dump [dbgtfdFileWrite()+48] then set the _pga_max_size initialization parameter to a larger value as follows: _pga_max_size=104857600 Restart your database.

参数部分，由于PL/SQL相关的两个参数有变化，启动数据库的时候会报已经废弃的参数等等

10. Perform post-install instructions

Run only the catmgdidcode.sql and utlrp.sql scripts for the post install instructions in patch 9218789. The other scripts are run as part of the dbua upgrade.

这个时候再执行上个补丁没打完的部分，一定要注意环境变量的问题，两次测试出的问题不太一样

11. Natively compile PL/SQL code (optional)

You can choose to run Oracle E-Business Suite 12.1 PL/SQL database objects in natively compiled mode with Oracle Database 11g. See the "Compiling PL/SQL Program Units for Native Execution" section of Chapter 12 of Oracle Database PL/SQL Language Reference 11g Release 2 (11.2).

After the Database Upgrade:

1. Start the new database listener (conditional)

If the Oracle Net listener for the database instance in the new Oracle home has not been started, you must start it now. Since AutoConfig has not yet been implemented, start the listener with the lsnrctl executable (UNIX/Linux) or Services (Windows). See the Oracle Database Net Services Administrator's Guide, 11g Release 2 (11.2) for more information.

Attention: Set the TNS_ADMIN environment variable to the directory where you created your listener.ora and tnsnames.ora files.

1. Run adgrants.sql

Copy $APPL_TOP/admin/adgrants.sql (adgrants_nt.sql for Windows) 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 (or adgrants_nt.sql)

[APPS schema name]

Note: Verify the usage of adgrants.sql in the adgrants.sql script. Older versions of adgrants.sql require the APPLSYS schema name parameter to be passed instead of APPS.

1. Grant create procedure privilege on CTXSYS

Copy $AD_TOP/patch/115/sql/adctxprv.sql from the administration server node to the database server node. 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

1. Set CTXSYS parameter

Use SQL*Plus to connect to the database as SYSDBA and run the following command:

$ sqlplus "/ as sysdba"

SQL> exec ctxsys.ctx_adm.set_parameter('file_access_role', 'public');

1. Validate Workflow ruleset

On the administration server node, use SQL*Plus to connect to the database as APPS and run the $FND_TOP/patch/115/sql/wfaqupfix.sql script using the following command:

$ sqlplus [APPS user]/[APPS password] @wfaqupfix.sql

[APPLSYS user] [APPS user]

1. Implement and run AutoConfig

Implement and run AutoConfig in the new Oracle home on the database server node. If the database listener of the new Oracle home is defined differently than the old Oracle home, you must also run AutoConfig on each application tier server node to update the system with the new listener.

See Using AutoConfig to Manage System Configurations with Oracle E-Business Suite Release 12 on My Oracle Support for instructions on how to implement and run AutoConfig. Section 3.2 explains how to generate a context file on the database tier.

Shut down all processes, including the database and the listener, and restart them to load the new environment settings.

Attention: After creating the XML context file, ensure the variable s_jretop points to a proper JRE directory. For AIX, it is $ORACLE_HOME/jdk/jre.

1. 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 run adstats.sql in restricted mode:

$ sqlplus "/ as sysdba"

SQL> alter system enable restricted session;

SQL> @adstats.sql

$ sqlplus "/ as sysdba"

SQL> alter system disable restricted session;

SQL> exit;

Note: Make sure that you have at least 1.5 GB of free default temporary tablespace.

1. Create Demantra privileges (conditional)

1. Re-create custom database links (conditional)

If the Oracle Net listener in the 11.2.0 Oracle home is defined differently than the one used by the old Oracle home, you must re-create any custom self-referential database links that exist in the Applications database instance. To check for the existence of database links, use SQL*Plus on the database server node to connect to the Applications database instance as APPS and run the following query:

$ sqlplus apps/[apps password]

SQL> select db_link from all_db_links;

The EDW_APPS_TO_WH and APPS_TO_APPS database links, if they exist, should have been updated with the new port number by AutoConfig in the previous step.

If you have custom self-referential database links in the database instance, use the following commands to drop and re-create them:

$ sqlplus apps/[apps password]

SQL> drop database link [custom database link];

SQL> create database link [custom database link] connect to

[user] identified by [password] using

'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=[hostname])

(PORT=[port number]))(CONNECT_DATA=(SID=[ORACLE_SID])))';

where [custom database link], [user], [password], [hostname], [port number], and [ORACLE_SID] reflect the new Oracle Net listener for the database instance.

1. Re-create grants and synonyms

Oracle Database 11g Release 2 (11.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.

1. Restart Applications server processes

Restart all the Application tier server processes that you shut down previously. Remember that the Oracle Net listener for the database instance, as well as the database instance itself, need to be started in the 11.2 Oracle home. Users may return to the system.

1. Synchronize Workflow views

Log on to Oracle E-Business Suite with the "System Administrator" responsibility. Click Requests > Run > Single Request and the OK button. Enter the following parameters:

• Request Name = Workflow Directory Services User/Role Validation

• p_BatchSize = 10000

• p_Check_Dangling = Yes

• Add missing user/role assignments = Yes

• Update WHO columns in WF tables = No

Click "OK" and "Submit".

这个部分特别注意的是JRE 的环境，不是OS自己的，而是EBS 带的，CLONE/JRE 的等等

监听部分，尽量不要用NETCA 去做，是用ADCONFIG 会产生出来的

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7318139/viewspace-1037420/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7318139/viewspace-1037420/