Upgrading Oracle Applications 11i DB to DB 10gR2 with Physical Standby in Place [ID 406652.1]
This note provides a step-by-step method for upgrading your Oracle Applications database to 10gR2 if you have a Data Guard physical standby in place.
The most current version of this note is document 406652.1 on OracleMetaLink.
For an overview of implementing and maintaining Data Guard Redo Apply (physical standby) with the Oracle Applications,
including detailed steps for a non-RAC implementation, please see OracleMetaLink note 216212.1.
For details on implementing Data Guard Redo Apply with the Applications, with RAC and ASM, see this paper on the Oracle Technology Network.
Attention: Windows users. While the concepts in this paper apply to all operating systems and hardware architectures that Oracle supports,
the procedure has not been validated on Windows Server systems. The implementation details for Windows may differ.
Section 1: Assumptions, Considerations
Section 2: Documentation to Read, Preparation
Section 3: Prior to Downtime
Section 4: Perform the Upgrade
Section 5. Post-upgrade steps
Conventions
Convention
Meaning
Production system
Applications system being cloned.
Standby system
Applications system being created as a copy of production.
APPLMGR
User that owns the applications file system (APPL_TOP and application tier technology stack)
ORACLE
User that owns the database file system (RDBMS ORACLE_HOME and database files).
CONTEXT_NAME
The CONTEXT_NAME variable refers to the name of the Applications Context file. For systems installed with Rapid Install 11.5.8 or earlier, this value will typically be set to
Monospace Text
Represents command line text. Type this command exactly as shown.
< >
Text enclosed in angle brackets represents a variable. Substitute a value for the variable text. Do not type the angle brackets.
Section 1: Assumptions, Considerations
Assumptions and considerations:
This document helps you blend the instructions in several other documents. It does not replace those documents. You will need to understand them and have them available as you go through the process.
We assume you are familiar with the basics of installing Oracle Data Guard Redo Apply (physical standby) with the Oracle Applications – OracleMetaLink note 216212.1 – and that your installation complies with that note. For instance:
You should be current with AutoConfig. See OracleMetaLink note 165195.1.
This note assumes you have set “force logging” in your production database, to ensure your standby is viable. Else check for nologging operations and update the standby as appropriate. See “Recovering After the NOLOGGING Clause is Specified” in the Data Guard Concepts and Administration manual for further info.
We assume you have already performed all the pre-requisite tasks found in OracleMetaLink note 230672.1, Cloning Oracle Applications Release 11i with Rapid Clone.
The steps in this note use the Database Upgrade Assistant to perform the actual upgrade. For instructions on performing the upgrade manually, please refer to the Oracle� Database Upgrade Guide.
This note was tested with a fresh install database from an 11.5.10 Rapid Install plus CU2, and with Oracle Database 10.1.0.4 upgraded to 10.2.0.2.
You should use the current version of the AD toolkit. Apply the most recent AD minipack to your environment. Read OracleMetaLink note 233044.1 for MiniPack AD.I.
The SQL statements in this document are often executed as the privileged user sysdba. Since you will be working with multiple environments, it is critical that you set and check your environment correctly before issuing commands.
Section 2: Documentation to Read, Preparation
Documentation:
You should read and understand the following documents before starting, and have them at hand during the process:
OracleMetaLink Interoperability note 362203.1. This note was tested with the Interoperability note dated December, 2006.
Oracle Database Upgrade Guide 10g Release 2 (10.2)
We will also be accessing the following:
Using AutoConfig to Manage System Configurations with Oracle Applications 11i – OracleMetaLink note 165195.1. Be sure your system meets the prerequisites for AutoConfig.
Cloning Oracle Applications Release 11i with Rapid Clone – OracleMetaLink note 230672.1
Oracle Data Guard Concepts and Administration 10g Release 2, part number B14239-04
OracleMetaLink note 216212.1 – Business Continuity for Oracle Applications 11i, DB releases 9i and 10g
This note will be of interest for later maintenance operations:
OracleMetaLink note 278641.1 – Applying a [Database] Patch Set with a 10g Physical Standby in Place
Preparation:
Read chapter 2 of Oracle Database Upgrade Guide 10g Release 2 (10.2) to learn more about moving to 10g, and for decisions about major steps to take in the upgrade process. It is critical that you test the overall process as recommended in this chapter. Tests you perform should follow the basic steps in this document, adjusted as necessary for your environment.
From the Interoperability note 362203.1 – Read Section 1, “Before the database installation”.
Verify your software versions are appropriate.
Migrate to Oracle Portal 10g if necessary.
This note assumes you will be keeping your database port, host, SID, and name the same.
The core steps to upgrade an Oracle Applications database when there is a standby in place are:
Environment
Task
Production system
1. Install the 10g software.
Production system
2. Analyze dictionary schemas.
Production system
3. Install Applications utilities in your new Oracle home.
Production system
4. (Conditionally required) Set up a remote login password file.
Production system
5. Stage and adjust DB and network configuration customizations.
Production system
6. Other miscellaneous tasks.
Production system
7. Stop user activity on production.
Production & Standby system
8. Verify all redo received on standby.
Production & Standby system
9. Shut down the databases.
Production system
10. Upgrade the primary database.
Production system
11. Additional database configuration.
Production system
12. Fix Korean lexers
Production system
13. (Conditionally required) Enable SMU.
Production system
14. (Conditionally required) Create grants, grant privileges.
Production system
15. (Conditionally required) Install Oracle XML DB.
Production system
16. Implement and run AutoConfig.
Production system
17. (Conditionally required) Gather statistics for SYS schema.
Production system
18. (Optional) Native PL/SQL compilation.
Production system
19. (Conditionally required) Re-create grants and synonyms.
Production system
20. (Conditionally required) Re-create custom db links.
Production system
21. (Possible) Start production services.
Production system
22. Run the database pre-clone.
Production & Standby systems
23. Clone software to, configure the standby.
Standby system
24. Adjust the standby database and network configuration files.
Standby system
25. Start the standby for managed recovery.
Production system
26. Start production services.
Section 3: Prior to Downtime
To reduce production downtime, perform these steps while the production system is still running.
1. Install the 10gR2 software.
From the Interoperability Note 362203.1 – Read Section 1, “Database Installation”, and do all steps. This instructs you to read chapters 1 and 2, and the “System Considerations and Requirements” section of chapter 3, of the Oracle Database Upgrade Guide, and perform the steps as needed. Only install the software at this point. Do not execute the upgrade yet.
The assumption in this note is that you keep the database port, host, SID and name the same after the upgrade. At the end of these tasks, you will have your new 10g Oracle home installed on your database server, but the database itself will not be upgraded.
Install the software only on your production system, not on the standby. Do not overwrite the existing ORACLE_HOME. Instead, create a new ORACLE_HOME to hold the new database software.
Do not upgrade the database yet, and do not install a starter database.
The basic steps:
Install the 10gR2 database software.
Install the 10gR2 Companion CDs.
Ensuring your environment variables, including your various paths, point to your new 10gR2 ORACLE_HOME, install the 10.2.0.2 patch set. As you are only installing the software at this time, and into a new ORACLE_HOME, there is no need to stop the production database for this activity.
Ensuring your environment variables (including your various paths) point to your new 10g ORACLE_HOME, create the $ORACLE_HOME/nls/data/9idata directory.
2. Analyze dictionary schemas.
Stale or missing statistics will be updated during the upgrade process. As instructed in chapter 3, System Considerations and Requirements of the Oracle Database Upgrade Guide, you can analyze the mdsys, ctxsys, olapsys, odm, ordsys, ordplugins, outln, dbsnmp, and system schemas (those that exist in your database) before taking down the application for the upgrade. The sys schema is analyzed later, in restricted mode.
Do this with your environment variables pointing to your existing ORACLE_HOME, not the new one.
To update stale statistics for user tables, issue this command connected as sysdba in SQL*Plus:
exec dbms_stats.gather_database_stats(options => GATHER AUTO’);
To gather statistics on dictionary tables, the command will be different according to your current database version.
If you are currently running Oracle 10gR1, issue this command:
exec dbms_stats.gather_dictionary_stats;
If you are currently running Oracle 9i, use the procedure dbms_stats.gather_dictionary_stats, with the scripts provided in Appendix C of the Oracle Database Upgrade Guide.
3. Install Applications utilities in your new Oracle home.
From OracleMetaLink Note 165195.1, Using AutoConfig to Manage System Configurations with Oracle Applications 11i, in the section “Migrating to AutoConfig on the Database Tier”, do step 1 to create the appsutil.zip file, copy it to your new ORACLE_HOME, and unzip it.
Copy two other scripts from your administration server node to your new ORACLE_HOME, for use later in the upgrade process:
$AD_TOP/patch/115/sql/adctxprv.sql
$APPL_TOP/admin/adstats.sql
4. (Conditionally required) Set up a remote login password file.
Oracle Data Guard in database release 10g requires the use of a password file for communications between the primary and standby databases. You will need to create one if there is not already one there.
The method required to implement this feature varies by platform. See MetaLink note 185703.1 for more information, and to get pointers for the commands needed for your platform. An example Unix invocation:
cd $ORACLE_HOME/dbs
orapwd file=orapw
To complete the implementation of the password file, you must add the parameter remote_login_passwordfile to your init.ora file. This will be done as part of the task “Implement and run autoconfig,” later in this document.
5. Stage and adjust DB and network configuration customizations.
Customizations to your database and network configuration files should be stored in the appropriate “include” files in your old oracle_home. Copy these files (the “ifiles”) to the appropriate directory under your 10.2 home (i.e., $ORACLE_HOME/dbs, and $TNS_ADMIN).
Study OracleMetaLink note 216205.1 and adjust your init.ora parameter customizations as necessary for the database configuration. Examples of areas that may need adjusting:
In 10g, the log_archive_format parameter must include %s, %t, and %r (sequence, thread, and resetlogs ID).
The log_archive_start parameter has been deprecated. Remove it or comment it out.
Decide whether you wish to natively compile your pl/sql, and whether you need to override the plsql_code_type parameter in the generated init
plsql_code_type = interpreted | native
Data guard 10g requires a remote_login_passwordfile entry, e.g.:
remote_login_passwordfile = exclusive
In the TNS_ADMIN directory, in the listener_ifile.ora file, make the changes needed to point to your new oracle_home directory. Check the include files to be sure they are still valid.
6. Other miscellaneous tasks.
Make sure recovery is active and current on the physical standby.
If any tablespaces or datafiles need recovery due to being marked offline immediate, recover them and bring them cleanly offline or online prior to the upgrade.
Any Data Guard broker configurations, created by either Data Guard Manager or the command line interface (CLI), must be removed prior to performing the upgrade procedure below. Once the upgrade is complete, the broker configuration can be recreated at the new version level. Please refer to the 10g Oracle� Data Guard Broker manual for complete steps.
Be sure you have at least 1.5GB free space in temp, 500 MB free space in the system tablespace, and 750 MB free undo space, with maxextents set to at least 512, before performing the upgrade.
If you have event 38004 trace name context forever, level 1 set in your init.ora or spfile, remove it.
Make sure your current primary environment is included in your oratab file (e.g., /etc/oratab, or /var/opt/oracle/oratab) in order to be seen by the upgrade assistant.
Download and stage any patches you need to apply during the outage.
Section 4: Perform the Upgrade
This section describes the steps involved with performing the actual database upgrade.
7. Stop user activity on production.
Shut down all application services.
In production, identify and record the current log thread and sequence number, archive the current log, and shut down the database. If you are running RAC, do this for each instance. To get the current log thread / sequence number:
shutdown immediate
startup restrict
select thread#, sequence#
from v$log
where status = ‘CURRENT’;
alter system archive log current;
8. Verify all redo received on standby.
On the standby, if you are using RAC, shut down (normal or immediate) all instances but one.
Make sure your running standby instance is in managed recovery mode.
Verify that each log file archived above has been received on the standby.
9. Shut down the databases.
Once all redo has been received on the standby, shut down the production database. As a privileged user, from the original ORACLE_HOME, stop and restart the primary database in migrate mode:
shutdown immediate
startup migrate
At the operating system level, on the production database server, make sure no other software is running against the original ORACLE_HOME:
agentctl stop
lsnrctl stop
From the interop note, for Windows environments 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.
Once all redo is applied on the standby, stop managed recovery, shut down the standby database cleanly, and stop all listeners and agents running on the 9.2 install. Connected to the standby database as a privileged user sysdba, then:
alter database recover managed standby database cancel;
shutdown immediate
At the operating system level, on the standby database server:
agentctl stop
lsnrctl stop
10. Upgrade the primary database.
From an xterm session, with your environment set for the new ORACLE_HOME and related paths, start the upgrade assistant and upgrade the primary database. The interoperability note warns to first unset the TNS_ADMIN parameter:
cd
unset TNS_ADMIN
./dbua
Notes:
� DO NOT select “Turn off archiving.”
� The default settings for the sysaux tablespace (500 MB for the size, and autoextend on) are the recommended settings.
� Any new accounts added during the upgrade are locked. On dbua’s “Upgrade Results” display at the end of the upgrade is a box labeled Password Management, with a button labeled Configure Database Passwords. Press this to unlock these accounts and give them non-default passwords.
Section 5. Post-upgrade steps
11. Additional database configuration.
Review the "Postinstallation Tasks" section of the 10.2.0.2 patch set notes. Perform the steps that are relevant for your database.
Note
Skip section 7.2, Upgrading Oracle Database 10g Release 10.2.0.1 to Oracle Database 10g Release 10.2.0.2.
12. Fix Korean lexers
Apply database patch 5005469, then use SQL*Plus to connect to the database as SYSDBA and run drkorean.sql as follows:
Sqlplus “/ as sysdba” @$ORACLE_HOME/ctx/sample/script/drkorean.sql
13. (Conditionally required) Enable SMU.
If system managed undo (SMU) is not enabled, enable it. See OracleMetaLink note 216205.1 for the steps.
14. (Conditionally required) Create grants, grant privileges.
If you have at least AD.I or Applications release 11.5.10 installed on your system and are upgrading the database from 9i (thus have not executed this step before), run $ORACLE_HOME/appsutil/sql/adgrants.sql as sysdba:
cd $ORACLE_HOME/appsutil/sql
sqlplus "/ as sysdba" @adgrants.sql
Run adctxprv.sql as the APPS user to grant “create procedure” privileges to CTXSYS. You copied the script to the database server in an earlier step:
sqlplus apps/
15. (Conditionally required) Install Oracle XML DB.
Verify that Oracle XML DB (XDB) is installed in your database by using SQL*Plus to connect to the database as SYSDBA and running the following command:
SQL> select count(1) from dba_registry where comp_id='XDB';
If the query returns 0, you do not have XDB installed. Read the "Installing and Uninstalling Oracle XML DB Repository" section in chapter 3 of the Oracle XML DB Developer's Guide 10g Release 1 (10.1) on how to install XDB.
16. Implement and run AutoConfig.
These steps will build new database and network configuration files in your 10g oracle_home.
The database upgrade assistant (dbua) generated an init
As the oracle user, with environment settings for the new 10g oracle_home, start a database listener.
From OracleMetaLink note 165195.1 – “Migrating to AutoConfig on the Database Tier” – do step 2 in section 5 to generate the database context file.
cd
adbldxml.pl tier=db appsuser=< > appspasswd=< >
adconfig.pl contextfile=
Source your shell environment using the new environment file.
Stop and re-start the database to use your new parameter files. As the database upgrade assistant created an spfile for your database, you should create a new one from these parameters. In a SQL*Plus session, connected as a privileged user:
shutdown
startup pfile=init
create spfile=‘spfile
Change any custom scripts to point to the new oracle home.
Start and stop your database listeners:
cd $ORACLE_HOME/appsutil/scripts/
addlnctl.sh
addlnctl.sh
17. (Conditionally required) Gather statistics for SYS schema.
If you are upgrading from database release 9i, you will need to gather statistics for the SYS schema. If you are upgrading from 10.1, you have already gathered statistics for SYS.
Gathering statistics for SYS needs to be done in restricted mode, via the script adstats.sql. You copied the script from your administration server to your database server in an earlier step.
Use SQL*Plus to connect to the database as SYSDBA and execute 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;
Note: Internal tests have required 4gb of temp space to execute this command.
18. (Optional) Native PL/SQL compilation.
If you are on a UNIX/Linux platform, you can choose to run Oracle Applications 11i PL/SQL database objects in natively compiled mode with Oracle Database 10g. See the "Compiling PL/SQL Code for Native Execution" section of Chapter 11 of Oracle Database PL/SQL User's Guide and Reference 10g Release 2 (10.2).
Pay particular attention to the required space – approximately 3GB in both the system tablespace and on the file system.
Tip: Make sure your next extent size is reasonable, and your max extents are unlimited, for dictionary tables in the SYSTEM tablespace – particularly the blob segment ‘DLL’ in the table ncomp_dll$. See OracleMetaLink note 298696.1 for more information.
19. (Conditionally required) Re-create grants and synonyms.
On an administrative server, use adadmin / 4. Maintain Applications Database Entities / 2. Recreate grants and synonyms for APPS schema.
(Optional) If you are on 11.5.9 and are using MRC, also execute 5. Maintain Multiple Reporting Currencies schema.
20. (Conditionally required) Re-create custom db links.
If the Oracle Net listener in the new 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. See the OracleMetaLink Interoperability Note 362203.1 for details.
21. (Possible) Start production services.
The production environment is ready for use at this point. You can start production services now, or you may wish to wait until the DR site is fully configured before opening the application for use.
22. Run the database pre-clone.
Run the database tier pre-clone. As the ORACLE user, on the production database server:
cd
perl adpreclone.pl dbTier
Supply the APPS password when requested.
23. Clone software to, configure the standby.
Copy the new 10g oracle home to the standby. Also copy the natively compiled pl/sql directory, if you chose native compilation. These copies can be done as soon as the pre-clone and native compilation tasks complete, concurrent with the rest of the database preparation.
After the database software copy is complete, log into the standby database server as the ORACLE user and execute the following commands to update the file system configurations for the new environment. Since your environment scripts are not set up yet, you will have to manually resolve the reference to
cd
perl adcfgclone.pl dbTechStack
Supply the APPS password when requested.
This command starts the standard database listeners. Stop them.
Your ORACLE user environment scripts are ready to use. Source them for the next steps, using the appropriate OS command – e.g., for UNIX ksh or sh:
cd
. ./
24. Adjust the standby database and network configuration files.
On the standby database server, copy your current / old standby
# log_archive_start = TRUE (comment it out – deprecated in 10g)
log_archive_format =
remote_login_passwordfile = exclusive
plsql_code_type = interpreted | native
Review your other customizations to be sure they are valid for 10g, based on OracleMetaLink note 216205.1.
Still on the standby database server, copy your current / old standby “include” files from your original TNS_ADMIN directory to your new TNS_ADMIN directory. In the listener_ifile.ora file, make the changes needed to point to your new oracle_home directory. Check them all to be sure they are still valid.
25. Start the standby for managed recovery.
On the standby, set your environment for the new 10.2 oracle home.
Start the listener for the standby services.
As the privileged user sysdba, mount the standby database and start managed recovery:
startup nomount pfile=
alter database mount standby database;
alter database recover managed standby database disconnect from session;
26. Start production services.
If you want, verify all logs have been received on the standby before starting production services.
Start the Applications server processes if they have not already been started.
