This chapter describes how to upgrade the database schemas of Oracle Fusion Middleware components by using Patch Set Assistant.
The Patch Set Assistant is used to upgrade the database schemas of Oracle Fusion Middleware components. The schemas that are valid for upgrading with the Patch Set Assistant must meet one of the following criteria:
The schema was created in an 11g release using the Repository Creation Utility (RCU).
Or:
The schema was upgraded from 10g to 11g using the Upgrade Assistant.
Note:
The Patch Set Assistant does not upgrade schemas that are in customer databases, which includes any database that has not been upgraded using the Upgrade Assistant (UA), Repository Creation Utility (RCU), or Patch Set Assistant (PSA).
If you are an Oracle Portal user, note that the Oracle Portal schema can, in certain circumstances, be installed in a customer database. For more information, refer to Section 4.3, "Special Instructions for Standalone Oracle Portal Repository Schemas".
In most cases, the Patch Set Assistant upgrades 11g Release 1 schema versions starting at 11.1.1.2.0 to version 11.1.1.9.0. See Table 4-1 for specific information.
If your existing schema version is 11.1.1.1.0, you must migrate to version 11.1.1.2.0 before you can run the Patch Set Assistant. See Appendix C, "Using Patch Assistant to Migrate from 11g Release 1 (11.1.1.1.0) to Release 1 (11.1.1.2.0)" for more information.
If you want to create new schemas or drop the existing schemas, you must use the Repository Creation Utility (RCU). Information is provided in Oracle Fusion Middleware Repository Creation Utility User's Guide.
Note:
Before you attempt to upgrade to 11.1.1.9.0, check the Oracle Fusion Middleware compatibility matrix to ensure that all the components in the domain are supported to interoperate with Oracle Fusion Middleware 11.1.1.9.0 components. If you have components in the domain that are not compatible, the upgrade should not be attempted.For more information, see Section 3.4.1, "Reviewing System Requirements and Certification" and the Oracle Fusion Middleware Interoperability and Compatibility Guide.
This chapter includes the following topics:
Oracle recommends that you create a user with the privileges required to upgrade the schemas. The privileges listed below must be granted before starting the Patch Set Assistant.
In the following example, "FMW" is the user created to run the Patch Set Assistant and "welcome1" is the password. Make sure that you specify your actual user name and password when granting privileges.
create user FMW identified by welcome1; grant dba to FMW; grant execute on DBMS_LOB to FMW with grant option; grant execute on DBMS_OUTPUT to FMW with grant option; grant execute on DBMS_STATS to FMW with grant option; grant execute on sys.dbms_aq to FMW with grant option; grant execute on sys.dbms_aqadm to FMW with grant option; grant execute on sys.dbms_aqin to FMW with grant option; grant execute on sys.dbms_aqjms to FMW with grant option; grant execute on sys.dbms_aqadm to FMW with grant option; grant execute on sys.dbms_aq to FMW with grant option; grant execute on utl_file to FMW with grant option; grant execute on dbms_lock to FMW with grant option; grant select on sys.V_$INSTANCE to FMW with grant option; grant select on sys.GV_$INSTANCE to FMW with grant option; grant select on sys.V_$SESSION to FMW with grant option; grant select on sys.GV_$SESSION to FMW with grant option; grant select on dba_scheduler_jobs to FMW with grant option; grant select on dba_scheduler_job_run_details to FMW with grant option; grant select on dba_scheduler_running_jobs to FMW with grant option; grant select on dba_aq_agents to FMW with grant option; grant execute on sys.DBMS_SHARED_POOL to FMW with grant option; grant select on dba_2pc_pending to FMW with grant option; grant select on dba_pending_transactions to FMW with grant option; grant execute on DBMS_FLASHBACK to FMW with grant option; grant execute on dbms_crypto to FMW with grant option; grant execute on DBMS_REPUTIL to FMW with grant option; grant execute on dbms_job to FMW with grant option; grant select on pending_trans$ to FMW with grant option; grant select on dba_scheduler_job_classes to fmw with grant option; grant select on SYS.DBA_DATA_FILES to FMW with grant option; grant select on SYS.V_$ASM_DISKGROUP to FMW with grant option; grant select on v$xatrans$ to FMW with grant option; grant execute on sys.dbms_system to FMW with grant option; grant execute on DBMS_SCHEDULER to FMW with grant option; grant select on dba_data_files to FMW with grant option; grant execute on UTL_RAW to FMW with grant option; grant execute on DBMS_XMLDOM to FMW with grant option; grant execute on DBMS_APPLICATION_INFO to FMW with grant option; grant execute on DBMS_UTILITY to FMW with grant option; grant execute on DBMS_SESSION to FMW with grant option; grant execute on DBMS_METADATA to FMW with grant option; grant execute on DBMS_XMLGEN to FMW with grant option; grant execute on DBMS_DATAPUMP to FMW with grant option; grant execute on DBMS_MVIEW to FMW with grant option; grant execute on ALL_ENCRYPTED_COLUMNS to FMW with grant option;
Note:
11.2.0.3 Database Users ONLY: You must apply Oracle Patch 13036331 before you begin the upgrade. Go to My Oracle Support to download the patch.If you do not apply this patch, then you will have to grant additional privileges for some schemas. Table 4-1 describes the privileges you will need to grant.
The component schemas available for upgrade in Table 4-1 (default names shown) can be upgraded with the Patch Set Assistant in order to upgrade them to 11g Release 1 (11.1.1.9.0).
Note:
The Schema(s) column in Table 4-1 shows the default schema name format of prefix and schema name separated by an underscore (_) character. The default prefix isDEV, but you may have created new prefixes for your schemas while using the RCU.Important Notes About the Schemas That Need to Be Upgraded:
Some schema upgrades require advanced privileges. Make sure that you grant the appropriate privileges to the DBA user before you upgrade the schemas. Table 4-1 describes the specific privileges required for each schema.
Applying the Release 1 (11.1.1.9.0) patch set will only upgrade schemas listed in Table 4-1. Some component schemas will not be upgraded and will remain at their original schema version.
The WebCenter and Oracle Enterprise Content Management Suite component names shown in Table 4-1 are the new component names that were changed as of Oracle Fusion Middleware 11g Release 1 (11.1.1.6.0).
For more information, see Section 6.1, "New Product Names for Oracle WebCenter and Oracle Enterprise Content Management Suite".
When upgrading _OPSS schemas from a previous release, it is important to note that OPSS audit data will now be stored in IAU common tables instead of the JPS table. Any existing data in the JPS table can remain unchanged for reporting or auditing purposes, and there is no need to upgrade JPS table with extra columns.
Table 4-1 Schemas That Require Upgrading for the Latest Release
| Component Name | Schema(s) | Schema Version Before Upgrade | Schema Version After Upgrade | Dependencies |
|---|---|---|---|---|
|
Audit Services |
|
11.1.1.2.0 11.1.1.3.0 11.1.1.4.0 11.1.1.5.0 11.1.1.6.0 11.1.1.7.0 |
11.1.1.9.0 |
None. |
|
Metadata Services |
|
11.1.1.2.0 11.1.1.3.0 11.1.1.4.0 11.1.1.5.0 11.1.1.6.0 11.1.1.7.0 |
11.1.1.9.0 |
None. |
|
Oracle Enterprise Scheduler Service |
|
11.1.1.2.0 11.1.1.3.0 11.1.1.4.0 11.1.1.5.0 11.1.1.6.0 11.1.1.7.0 |
11.1.1.9.0 |
The |
|
Oracle Platform Security Services |
|
11.1.1.2.0 11.1.1.3.0 11.1.1.4.0 11.1.1.5.0 11.1.1.6.0 11.1.1.7.0 |
11.1.1.9.0 |
None. |
|
Oracle PortalFoot 1 |
|
11.1.1.2.0 11.1.1.3.0 11.1.1.4.0 11.1.1.6.0 |
11.1.1.6.0 |
None. |
|
Oracle Internet Directory |
NOTE: The Oracle Internet Directory schema ( |
11.1.1.2.0 11.1.1.3.0 11.1.1.4.0 11.1.1.5.0 11.1.1.6.0 11.1.1.7.0 |
11.1.1.9.0 |
11.2.0.3 Database Users ONLY: This schema requires that you apply Oracle Patch 13036331 before you begin the upgrade. Go to My Oracle Support to download the patch. If you do not apply the patch you must grant these additional privileges: grant execute on sys.dbms_reputil to ODS; grant execute on sys.dbms_reptuil to SYSTEM with grant option; grant execute on sys.dbms_job to ods; |
|
Oracle Business Intelligence Platform |
|
11.1.1.3.0 11.1.1.7.0 |
11.1.1.9.0 |
The |
|
Master and Work Repository (Oracle Data Integrator) |
|
11.1.1.3.0 11.1.1.4.0 11.1.1.5.0 11.1.1.6.0 11.1.1.7.0 |
11.1.1.9.0 |
None. |
|
SOA Infrastucture |
|
11.1.1.2.0 11.1.1.3.0 11.1.1.4.0 11.1.1.5.0 11.1.1.6.0 11.1.1.7.0 |
11.1.1.9.0 |
The 11.2.0.3 Database Users ONLY: This schema requires that you apply Oracle Patch 13036331 before you begin the upgrade. Go to My Oracle Support to download the patch. If you do not apply the patch you must grant these additional privileges: grant execute on utl_file to &&soa_user |
|
WebCenter Portal (previously WebCenter Spaces) |
|
11.1.1.2.0 11.1.1.3.0 11.1.1.4.0 11.1.1.5.0 11.1.1.6.0 11.1.1.7.0 |
11.1.1.8.0 |
The |
|
Discussions (WebCenter Suite) |
|
11.1.1.2.0 11.1.1.4.0 11.1.1.7.0 |
11.1.1.8.0 |
None. |
|
Discussions CrawlerFoot 2 |
|
11.1.1.2.0 11.1.1.4.0 11.1.1.7.0 11.1.1.8.0 |
11.1.1.9.0 |
The |
|
Activity Graph and Analytics |
|
11.1.1.4.0 11.1.1.5.0 11.1.1.6.0 11.1.1.7.0 |
11.1.1.8.0 |
None. |
|
Oracle Universal Records Management 11g |
|
11.1.1.4.0 11.1.1.5.0 11.1.1.6.0 11.1.1.7.0 |
11.1.1.8.0 |
None. |
|
Oracle Content Server 11g - Complete |
|
11.1.1.4.0 11.1.1.5.0 11.1.1.6.0 11.1.1.7.0 11.1.1.8.0 |
11.1.1.9.0 |
None. |
|
Oracle Universal Content Management |
|
11.1.1.4.0 11.1.1.5.0 11.1.1.7.0 |
11.1.1.8.0 |
|
|
Oracle Secure Enterprise Search |
|
11.2.1 |
11.2.2 |
The |
|
Oracle User Messaging Service |
|
11.1.1.2.0 11.1.1.3.0 11.1.1.4.0 11.1.1.5.0 11.1.1.6.0 11.1.1.7.0 |
11.1.1.9.0 |
11.2.0.3 Database Users ONLY: This schema requires that you apply Oracle Patch 13036331 before you begin the upgrade. Go to My Oracle Support to download the patch. If you do not apply the patch you must grant these additional privileges: grant execute on sys.dbms_aqjms to &&user |
Footnote 1 A schema upgrade is not required if Oracle Portal 11.1.1.6.0 has already been installed. 11.1.1.6.0 is the latest version of Oracle Portal.
Footnote 2 If the Discussions Crawler schema has not previously been installed using RCU, then migrating Discussions will automatically install the Discussions Crawler schema, assigning the same password as the Discussions schema. If you then attempt to upgrade the Discussions Crawler schema individually, the Patch Set Assistant will warn that the schema has already been upgraded.
If you are using an Oracle Portal repository stored outside of the OracleAS Metadata Repository (any database that has not used the Upgrade Assistant, Repository Creation Utility, or Patch Set Assistant), then do not use the Patch Set Assistant to upgrade the Portal schemas in the repository. Instead, refer to "Upgrading an Oracle Portal Repository in a Customer Database" in Oracle Fusion Middleware Upgrade Guide for Oracle Portal, Forms, Reports, and Discoverer.
This section contains information about things you should check before you run the Patch Set Assistant:
Section 4.4.2, "Upgrade Oracle WebLogic Server and Apply Mandatory Patches"
Section 4.4.5, "Create an Edition on the Database Server for Editions-Based Redefinition"
Section 4.4.6, "Shut Down All Components Using the Schemas You Want to Upgrade"
Section 4.4.7, "Check the aq_tm_processes Value for Oracle Portal"
Section 4.4.8, "Setting ORACLE_HOME and JAVA_HOME Environment Variables"
This release of Oracle Fusion Middleware requires a 64-bit JDK 7 (1.7.x), which can be downloaded at the following URL: http://www.oracle.com/technetwork/java/javase/downloads/jdk7-downloads-1880260.html.
Note:
JRockit Users: JRockit is no longer supported. You must install the Sun JDK to use with this release.For up to date supported configuration information, see Oracle Fusion Middleware Supported System Configurations at: http://www.oracle.com/technetwork/middleware/ias/downloads/fusion-certification-100350.html.
For more information about JDK version requirements, see the "Oracle WebLogic Server and JDK Considerations" topic in the Oracle Fusion Middleware System Requirements and Specifications for Oracle Fusion Middleware 11g Release 1 (11.1.1.9) document.
For more information about using JDK 7 with this release of Oracle Fusion Middleware, see "Using WebLogic Server with JDK 7" in Installing and Configuring Oracle WebLogic Server and Coherence.
Before an upgrade, always consult the System Requirements and Supported Platforms for Oracle Fusion Middleware 11gR1 document on the Oracle Fusion Middleware Supported System Configurations page for supported JDK versions.
Note:
Make sure that you update the setDomainEnv script file to point to the upgraded JDK version. See Updating the setDomainEnv Script File with Upgraded JDK Location.Make sure that you have applied the mandatory patches for Oracle WebLogic Server before you begin the patching process. For more information, see Upgrading Oracle WebLogic Server and Applying Required WebLogic Server Patches.
Note:
The WebLogic domain must be upgraded to 10.3.6 before applying the WebLogic Server patches.Some Oracle WebLogic Server patches require additional post-patching tasks before starting the servers. For more information, see the Oracle Fusion Middleware Download, Installation, and Configuration ReadMe Files for Oracle WebLogic Server.
Make sure you have backed up your existing database and database schemas before you run the Patch Set Assistant, as instructed in Section 3.4.5, "Backing Up Your Database and Database Schemas".
Before running Patch Set Assistant, you should check to make sure that your database is up and running and that the schemas you want to upgrade are at versions that are supported for upgrade. If you are using an Oracle database, connect to the database as SYS and run the following from SQL*Plus:
SELECT OWNER, VERSION, STATUS, UPGRADED FROM SCHEMA_VERSION_REGISTRY;
If the number in the "VERSION" column is 11.1.1.2.0 or greater, then the schema is supported for upgrade.
If you are using an Oracle database, you should recompile database objects before running the Patch Set Assistant to check for invalid objects before the upgrade. Connect to the database as SYS and run the following from SQL*Plus:
/rdbms/admin/utlrp.sql
Caution:
If you are running Oracle database 11g (11.2.0.3), you must first patch the database to enable advanced privileges when upgrading select schemas. Table 4-1 describes the specific privileges required for each schema.After running utlrp.sql, and before you upgrade your schema, issue the following query to ensure there are no longer any invalid database objects:
SELECT owner, object_name FROM all_objects WHERE status='INVALID';
Take note of any invalid objects. The existence of invalid database objects may prevent the upgrade from completing successfully.
To recompile just the objects that belong to a single Oracle Fusion Middleware schema, you can use the Oracle Database stored procedure dbms_utility.compile_schema as shown in the example below:
SQL> execute
dbms_utility.compile_schema('DEV_SOAINFRA');
PL/SQL procedure successfully completed
Edition-based redefinition (EBR) enables you to support multiple versions of a database schema on the same database and at the same time.
Note:
When using the Patch Set Assistant, the database type Oracle Database enabled for editions-based redefinition should only be selected when editions created in the EBR database require an upgrade. If this is not the intent, select Oracle Database.EBR is not available for all Oracle Fusion Middleware components, therefore the EBR database option may not be available.
Before upgrading an EBR-enabled schema from FMW 11.1.1.7.0 release to 11.1.1.9.0, you must connect to the database server and create an edition on the database server for the 11.1.1.9.0 release. The new edition for 11.1.1.9.0 must be a child of your previous edition.
To create an edition on the database server, log in as SYS (or another Oracle user that has DBA privileges) and use the following command:
SQL> create edition Oracle_FMW_11_1_1_9_0 as child of Oracle_FMW_11_1_1_7_0; Edition created.
Note that in the code example above, 'Oracle_FMW_11_1_1_7_0' is used as an example of the edition name you specified in RCU 11.1.1.7.0 when the 11.1.1.7.0 schemas were created. Be sure to provide the actual name used when creating the edition.
After you have created the schemas and before you create the domain, you must specify that the new schema is to be used as the default edition using the following command:
ALTER DATABASE DEFAULT EDITION = edition_name;
Before running Patch Set Assistant, shut down all Oracle Fusion Middleware components (including the Managed Server and Oracle instances) that may be using the schemas you want to upgrade. There should be no Oracle Fusion Middleware components connected to the database while schemas are being upgraded.
If you are running the Patch Set Assistant for the Oracle Portal schema on an Oracle database, make sure that the aq_tm_processes value in your database is greater than 0. To check, use the following command after connecting to the database:
show parameter aq_tm_processes;
If the value returned is 0, use the following command to change the value to 1:
alter system set aq_tm_processes=1 scope=both;
You may need to set or modify your environment variables in the following scenarios:
If you are running the Patch Set Assistant with an Oracle WebLogic Server, and the environment variables ORACLE_HOME or JAVA_HOME have been set on either UNIX or Windows operating systems, then you must remove these variables prior to running Patch Set Assistant. In some cases if you run the Patch Set Assistant with these variables already set, then the PSA may not run properly.
If you are running the Patch Set Assistant and your install does not contain an Oracle WebLogic Server (standalone) or Java files, you must set the JAVA_HOME environment variable. PSA may not be able to detect the location of the required Java files without setting this variable.
The following sections describe how to run the Patch Set Assistant when you are installing the Oracle Fusion Middleware 11g Release 1 (11.1.1.9.0) patch set:
Section 4.5.1, "Using the Patch Set Assistant Graphical Interface"
Section 4.5.2, "Using the Patch Set Installer from the Command Line"
Section 4.5.3, "Verifying the Schema Version Number After Upgrade"
The Patch Set Assistant is installed into the bin directory in the Oracle Home by the Product or Patch Set Installer.
You can use the Patch Set Assistant to patch any component schema in the Middleware home from where the Patch Set Assistant started.
To start Patch Set Assistant, go to the bin directory in the Oracle Home, then run the following command:
On UNIX operating systems:
cd Oracle_Home/bin ./psa [-logLevel <log_level>] [-logDir <log_directory>]
On Windows operating systems:
CD Oracle_Home\bin psa.bat [-logLevel <log_level>] [-logDir <log_directory>]
Follow the instructions in Table 4-2 to use the Patch Set Assistant to upgrade your schemas.
Note:
Table 4-2 describes only the common Patch Set Assistant screens - standard screens that appear for all components. Your environment may have components that require additional component-specific screens. For more information on the component-specific screens, click Help on the screen to access the online helpIf you need additional help with any of the screens, refer to Appendix B, "Patch Set Assistant Screens".
Table 4-2 Patch Set Assistant Screens
| Screen | Description |
|---|---|
|
This page introduces you to the Patch Set Assistant. |
|
|
Select the single top-level component you want to upgrade. You may only upgrade one component at a time. NOTE: If you are upgrading from Release 11.1.1.2.0 or 11.1.1.3.0, the |
|
|
Verify that you have satisfied the database prerequisites. |
|
|
Use this screen to specify database connection credentials and schema information for upgrade. You can select the schema name from the drop-down list of available schemas. |
|
|
This page displays the status of the Patch Set Assistant as it examines each component schema. Verify that your schemas have a "succeeded" or "already upgraded" indicator in the Status column. |
|
|
Verify that the schemas listed to be upgraded are the ones you expect. |
|
|
This screen shows the progress of the schema upgrade. |
|
|
This screen shows the final status of the upgrade and the location of the log file. |
Use the following syntax to perform upgrades using the command line interface (CLI):
cd Oracle_Home/bin psa -response <response_file> [-logLevel <log_level>] [-logDir <log_directory>]
See Table 4-3 for descriptions of these parameters.
Table 4-3 Patch Set Assistant Command Line Parameters
| Parameter | Required or Optional Parameter? | Description |
|---|---|---|
|
|
Required. |
The full path and name of the file containing inputs required to perform an upgrade. To generate the response file used with the NOTE: When Patch Set Assistant is run in |
|
|
Optional. |
Logging level. Select one of the following:
The default logging level is NOTIFICATION. |
|
|
Optional. |
Name of an existing, writable directory where PSA will create log files and temporary files. Default location is MW_HOME/oracle_common/upgrade/logs |
|
|
Optional. |
View all of the command line options. |
You can use the SQL command below to verify that the schema version in schema_version_registry has been properly upgraded:
SELECT OWNER, VERSION, STATUS, UPGRADED FROM SCHEMA_VERSION_REGISTRY;
Check that the number in the "VERSION" column matches the latest version number for that schema. See Table 4-1 to verify that the upgraded version number is correct for your schema.
If the status appears as "INVALID" then the schema upgrade failed. You should examine the logs files to determine the reason for the failure. For more information, see Section 4.6, "Patch Set Assistant Log Files".
If you are using an Oracle database, you should recompile database objects after running the Patch Set Assistant by connecting to the database as SYS and running the following from SQL*Plus:
SQL> /rdbms/admin/utlrp.sql
This will compile the database objects that were upgraded by Patch Set Assistant.
Then issue the following query to ensure there are no longer any invalid database objects:
SELECT owner, object_name FROM all_objects WHERE status='INVALID';
None of the database objects for the upgraded schema should be invalid at this point. If there are any, run the utlrp.sql command again and check again. If the problem persists, you should file a service request.
The Patch Set Assistant writes log files to the following locations:
On UNIX operating systems:
MW_HOME/oracle_common/upgrade/logs/psatimestamp.log
On Windows operating systems:
MW_HOME\oracle_common\upgrade\logs\psatimestamp.log
Some components will create a second log file called psatimestamp.out, also in the same location.
The timestamp will reflect the actual date and time that Patch Set Assistant was run.
Should any failures occur when running Patch Set Assistant, these log files will be needed to help diagnose and correct the problem; do not delete them. You can alter the contents of your log files by specifying a different -logLevel from the command line.
Some of the operations performed by the Patch Set Assistant may take longer to complete than others. If you want to see the progress of these long operations, you can see this information in the log file, or you can use the following query:
SELECT VERSION, STATUS, UPGRADED FROM SCHEMA_VERSION_REGISTRY WHERE OWNER='schema_name';
In the query results, the STATUS field will be either "UPGRADING" or "UPGRADED" during the schema patching operation, and will become "VALID" when the operating is finished.