Skip Headers
Oracle® Fusion Middleware Patching Guide
11g Release 1 (11.1.1.7.0)

Part Number E16793-25
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

4 Updating Your Schemas with Patch Set Assistant

The Patch Set Assistant is used in patch set releases only to update the database schemas of Oracle Fusion Middleware components. The schemas that are valid for updating with the Patch Set Assistant must meet one of the following criteria:

Note:

The Patch Set Assistant does not upgrade schemas that are in customer databases, which includes any database that has not been updated 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.2, "Special Instructions for Standalone Oracle Portal Repository Schemas".

The Patch Set Assistant updates 11g Release 1 schema versions starting at 11.1.1.2.0 to version 11.1.1.7.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 are interested in creating new schemas or dropping existing schemas, you must use the Repository Creation Utility (RCU). Information is provided in Oracle Fusion Middleware Repository Creation Utility User's Guide.

This chapter contains the following topics:

4.1 Which Schemas Need to be Updated with Patch Set Assistant?

The component schemas in Table 4-1 (default names shown) must be updated with the Patch Set Assistant in order to update them to 11g Release 1 (11.1.1.7.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 is DEV, but you may have created new prefixes for your schemas while using the RCU.

Notes About the Schemas That Need to Be Updated:

Table 4-1 Schemas That Require Updating for the Latest Release

Component Name Schema(s) Schema Version Before Upgrade Schema Version After Upgrade Dependencies

Audit Services

prefix_IAU

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.7.0

11.1.1.7.0

11.1.1.7.0

11.1.1.7.0

None.

Metadata Services

prefix_MDS

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.7.0

11.1.1.7.0

11.1.1.7.0

11.1.1.7.0

None.

Oracle Enterprise Scheduler Service

prefix_ESS

11.1.1.5.0

11.1.1.7.0

The prefix_MDS schema must be updated first.

Oracle Platform Security Services

prefix_OPSS

11.1.1.4.0

11.1.1.5.0

11.1.1.7.0

11.1.1.7.0

None.

Oracle PortalFoot 1 

prefix_PORTAL

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

11.1.1.6.0

None.

Oracle Internet Directory

ODS

NOTE: The Oracle Internet Directory schema (ODS) cannot be prepended with a custom prefix.

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.7.0

11.1.1.7.0

11.1.1.7.0

11.1.1.7.0

None.

Oracle Identity ManagerFoot 2 

prefix_OIM

11.1.1.5.0

11.1.1.7.0

The prefix_MDS, prefix_ORASDPM and prefix_SOAINFRA schemas must be updated first.

Oracle Adaptive Access ManagerFoot 3 

prefix_OAAM

11.1.1.5.0

11.1.1.7.0

The prefix_MDS schema must be updated first.

Oracle Adaptive Access Manager

prefix_OAAM_PARTN

11.1.1.5.0

11.1.1.7.0

The prefix_MDS schema must be updated first.

Oracle Business Intelligence Platform

prefix_BIPLATFORM

11.1.1.3.0

11.1.1.7.0

The prefix_MDS schema must be updated first.

Master and Work Repository (Oracle Data Integrator)

prefix_ODI_REPO

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.7.0

11.1.1.7.0

11.1.1.7.0

None.

SOA Infrastucture

prefix_SOAINFRA

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.7.0

11.1.1.7.0

11.1.1.7.0

11.1.1.7.0

The prefix_MDS schema must be updated first.

WebCenter Spaces

prefix_WEBCENTER

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.7.0

11.1.1.7.0

11.1.1.7.0

11.1.1.7.0

The prefix_MDS schema must be updated first.

Discussions (WebCenter Suite)

prefix_DISCUSSIONS

11.1.1.2.0

11.1.1.3.0

11.1.1.4.0

11.1.1.4.0

None.

Discussions CrawlerFoot 4 

prefix_DISCUSSIONS_CRAWLER

11.1.1.2.0

11.1.1.3.0

11.1.1.4.0

11.1.1.4.0

The prefix_DISCUSSIONS schema must be updated first.

Activity Graph and Analytics

prefix_ACTIVITIES

11.1.1.4.0

11.1.1.5.0

11.1.1.6.0

11.1.1.6.0

None.

Oracle Universal Records Management 11g

prefix_URMSERVER

11.1.1.4.0

11.1.1.5.0

11.1.1.6.0

11.1.1.7.0

11.1.1.7.0

11.1.1.7.0

None.

Oracle Content Server 11g - Complete

prefix_OCS

11.1.1.4.0

11.1.1.5.0

11.1.1.6.0

11.1.1.7.0

11.1.1.7.0

11.1.1.7.0

None.

Oracle Universal Content Management

prefix_OCSSEARCH

11.1.1.4.0

11.1.1.5.0

11.1.1.7.0

11.1.1.7.0

 

Oracle Secure Enterprise Search

prefix_SEARCHSYS

11.2.1

11.2.2

The prefix_ESS must be updated first.

Oracle User Messaging Service

prefix_ORASDPM

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.7.0

11.1.1.7.0

11.1.1.7.0

11.1.1.7.0

None.


Footnote 1 A schema update 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 Oracle Identity Manager 11.1.1.3.0 users must first patch to 11.1.1.5.0 before applying the 11.1.1.7.0 patch. For more information, see Chapter 7, "Patching Oracle Identity and Access Management".

Footnote 3 Oracle Adaptive Access Manager 11.1.1.3.0 users must first patch to 11.1.1.5.0 before applying the 11.1.1.7.0 patch. For more information, see Chapter 7, "Patching Oracle Identity and Access Management".

Footnote 4 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 update the Discussions Crawler schema individually, the Patch Set Assistant will warn that the schema has already been updated.

4.2 Special Instructions for Standalone Oracle Portal Repository Schemas

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 update 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.

4.3 Before You Begin Using the Patch Set Assistant

This section contains information about things you should check before you run the Patch Set Assistant:

4.3.1 Back Up Your Database and Database Schemas

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".

4.3.2 Check Your Database and Schemas

Before running Patch Set Assistant, you should check to make sure that your database and database listener 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

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('PS7_SOAINFRA');
PL/SQL procedure successfully completed 

4.3.3 Create an Edition on the Database Server for Editions-Based Redefinition

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.6.0 release to 11.1.1.7.0, you must connect to the database server and create an edition on the database server for the 11.1.1.7.0 release. The new edition for 11.1.1.7.0 must be a child of your 11.1.1.6.0 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_7_0 as child of Oracle_FMW_11_1_1_6_0;
Edition created.

Note that in the code example above, 'Oracle_FMW_11_1_1_6_0' is used as an example of the edition name you specified in RCU 11.1.1.6.0 when the 11.1.1.6.0 schemas were created. Be sure to provide the actual name used when creating the edition.

For more information on enabling editions-based redefinitions, see "Edition-Based Redefinition" in the Oracle Database Advanced Application Developer's Guide.

4.3.4 Shut Down All Components Using the Schemas You Want to Update

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 update.

Exception:

If you will be updating Oracle Identity Manager, make sure that both the database and listener are running prior to running Patch Set Assistant. If they are not running the ds_attrstore table will have a prior version listed in the attrname field where attrname = 'orcldirectoryversion', and "opmnctl startall" will time out when starting the Oracle Identity Manager component, even though it seems that the install was successful.

4.3.5 Check the aq_tm_processes Value for Oracle Portal

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;

4.3.6 Setting ORACLE_HOME and JAVA_HOME Environment Variables

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 the JAVA_HOME environment variable. PSA may not be able to detect the location of the required Java files without setting this variable.

4.4 Running the Patch Set Assistant

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.7.0) patch set:

4.4.1 Using the Patch Set Assistant Graphical Interface

The Patch Set Assistant is installed into the bin directory in the Oracle Common 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 Common home, then run the following command:

On UNIX operating systems:

cd Oracle_Home/bin
./psa

On Windows operating systems:

CD Oracle_Home\bin
psa.bat

Follow the instructions in Table 4-2 to use the Patch Set Assistant to update your schemas.

If you need additional help with any of the screens, refer to Appendix B, "Patch Set Assistant Screens" or click Help on the screen to access the online help.

Table 4-2 Patch Set Assistant Screens

Screen Description

Welcome

This page introduces you to the Patch Set Assistant.

Select Component

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 prefix_ACTIVITIES schema will not be available in your environment as this schema was not introduced until Release 11.1.1.4.0. Make sure you de-select this schema from the WebCenter Portal component; otherwise the Patch Set Assistant will ask for credentials for this schema.

Prerequisites

Verify that you have satisfied the database prerequisites.

Schema

Specify your database credentials to connect to your database, then select the schema you want to update.

This screen appears once for each schema that must be updated as a result of the component you selected on the Select Component screen.

Examine

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.

Upgrade Summary

Verify that the schemas listed to be upgraded are the ones you expect.

Upgrade Progress

This screen shows the progress of the schema update.

Upgrade Success

This screen shows the final status of the update and the location of the log file.


4.4.2 Using the Patch Set Installer from the Command Line

Use the following syntax to perform upgrades using the command line interface (CLI):

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

-response

Required.

The full path and name of the file containing inputs required to perform an upgrade.

To generate the response file used with the -response parameter, you should run the Patch Set Assistant GUI (Section 4.4.1, "Using the Patch Set Assistant Graphical Interface"). On the Upgrade Summary screen, click Save Response File to save your information to a response file.

NOTE: When Patch Set Assistant is run in -reponse file mode it will not upgrade any schemas that return "already upgraded" during the Examine phase. These schemas will skip the upgrade phase entirely. This applies only when Patch Set Assistant is run in -response file mode.

-logLevel

Optional.

Logging level. Select one of the following:

  • TRACE

  • NOTIFICATION

  • WARNING

  • ERROR

  • INCIDENT_ERROR

The default logging level is NOTIFICATION.

-logDir

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

-help

Optional.

View all of the command line options.


4.4.3 Verifying the Schema Version Number After Update

You can use the SQL command below to verify that the schema version in schema_version_registry has been properly updated:

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 updated version number is correct for your schema.

If the status appears as "INVALID" then the schema update failed. You should examine the logs files to determine the reason for the failure. For more information, see Section 4.5, "Patch Set Assistant Log Files".

4.4.4 Checking for Invalid Database Objects

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 updated 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 updated 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.

4.5 Patch Set Assistant Log Files

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.