4 Updating Your Schemas with Patch Set Assistant

The Patch Set Assistant is used in patch set releases only to update the database schema of an Oracle Fusion Middleware component. This tool updates 11g Release 1 schema versions 11.1.1.2.0, 11.1.1.3.0, and in some cases, 11.1.1.4.0 to version 11.1.1.5.0. See Table 4-1 for specific information.

If your existing schema version is earlier than 11.1.1.2.0, you must migrate to version 11.1.1.2.0 before you can run the Patch Set Assistant. See Appendix B, "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.5.0). Remember to also update the dependent schemas in addition to the component schemas for your own product.

Note:

Some products, such as Oracle Portal, are not released in 11.1.1.5.0. If you run the Patch Set Assistant for those products, you would be updating them to the latest available version, not 11.1.1.5.0.

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

Component Schema(s) Dependencies Required For

Common Schemas

prefix_IAU

None.

11.1.1.2.0 to 11.1.1.5.0

11.1.1.3.0 to 11.1.1.5.0

prefix_MDS

None.

11.1.1.2.0 to 11.1.1.5.0

11.1.1.3.0 to 11.1.1.5.0

11.1.1.4.0 to 11.1.1.5.0

Oracle Portal

prefix_PORTAL

None.

11.1.1.2.0 to 11.1.1.4.0

11.1.1.3.0 to 11.1.1.4.0

Oracle Internet Directory

ODS

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

None.

11.1.1.2.0 to 11.1.1.5.0

11.1.1.3.0 to 11.1.1.5.0

11.1.1.4.0 to 11.1.1.5.0

Oracle Identity Management

prefix_OIM

The prefix_MDS and prefix_SOAINFRA schemas must be updated first.

11.1.1.3.0 to 11.1.1.5.0

Oracle Adaptive Access Manager

prefix_OAAM

The prefix_MDS schema must be updated first.

11.1.1.3.0 to 11.1.1.5.0

Oracle Adaptive Access Manager (Partition Support)

prefix_OAAM_PARTN

The prefix_MDS schema must be updated first.

11.1.1.3.0 to 11.1.1.5.0

Oracle Business Intelligence

prefix_BIPLATFORM

The prefix_MDS schema must be updated first.

11.1.1.3.0 to 11.1.1.5.0

Oracle Data Integrator (Master and Work Repository)

prefix_ODI_REPO

None.

11.1.1.3.0 to 11.1.1.5.0

Oracle SOA Suite

prefix_SOAINFRA

The prefix_MDS schema must be updated first.

11.1.1.2.0 to 11.1.1.5.0

11.1.1.3.0 to 11.1.1.5.0

Oracle WebCenter Schemas

prefix_WEBCENTER

The prefix_MDS schema must be updated first.

11.1.1.2.0 to 11.1.1.5.0

11.1.1.3.0 to 11.1.1.5.0

prefix_DISCUSSIONS

prefix_DISCUSSIONS_CRAWLER

None.

11.1.1.2.0 to 11.1.1.5.0

11.1.1.3.0 to 11.1.1.5.0


Note:

The WebCenter schemas must be updated in the following order: MDS first, followed by WEBCENTER, then DISCUSSIONS, then DISCUSSIONS_CRAWLER.

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.

If you attempt to patch a schema that is not in this list, you will see the following error message:

"UPGAST-02001: unsupported schema for patching: PREFIX_SCHEMANAME "

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, 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 Special Instructions for Oracle Service Bus Schemas Installed in a Microsoft SQL Server Database

The instructions in this section are applicable only if you have Oracle Service Bus version 11.1.1.3.0 installed and configured on your system.

If you have installed the Oracle Service Bus schema in a Microsoft SQL Server database, then you must upgrade the schema when you apply the latest patch set to your Oracle Service Bus 11.1.1.3.0 environment.

Refer to the following special instructions for more information:

  • If you have installed Oracle Service Bus as part of an existing Oracle SOA Suite domain, use the instructions in this chapter to run the Patch Set Assistant that is installed in the Oracle SOA Suite Oracle home after you install the latest patch set.

    The Patch Set Assistant utility installed in the Oracle SOA Suite Oracle home also updates the Oracle Service Bus schema that is installed in your Microsoft SQL Server database.

  • If you are have installed Oracle Service Bus in its own Oracle WebLogic Server domain (without Oracle SOA Suite), then use the following instructions to upgrade the Oracle Service Bus schema in the Microsoft SQL Server database to the latest version:

    1. Locate the Oracle Service Bus schema upgrade script in the following location in the Oracle Service Bus Oracle home:

      OSB_ORACLE_HOME/dbscripts/mssql/upgrade_osb_111130_111140_sqlserver.sql
      
    2. Edit the script to replace the following string with the database user for the Oracle Service Bus schema:

      $(SCHEMA_USER)
      
    3. Connect to the SQL Server database and run the script to upgrade the schema.

4.4 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.4.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.3, "Back Up Your Database and Database Schemas".

4.4.2 Check Your Database and 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 update exist in the database. For example, on Oracle databases, use SQL*Plus to login as user prefix_schemaname to verify that the schema exists.

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:

@?/rdbms/admin/utlrp.sql

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

Before running Patch Set Assistant, shut down any Oracle Fusion Middleware components (including the Managed Server and Oracle instances) that may be using the schemas you want to update.

4.4.4 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 user 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.5 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.5.0) patch set:

4.5.1 General Information about the Patch Set Assistant

The Patch Set Assistant is installed into the bin directory in your Oracle home by the Patch Set Installer (see Chapter 3, "Applying the Latest Oracle Fusion Middleware Patch Set").

You can only use the Patch Set Assistant to patch a component schema if the schema matches the product type in the Oracle home from which the Patch Set Assistant started. In other words, if you run the Patch Set Assistant from an Oracle home containing Oracle SOA Suite, you cannot patch your existing Oracle Portal schema; you would have to run the Patch Set Assistant from an Oracle home that contained Oracle Portal.

Below is the error message you would see if you tried to update the Oracle Portal schema from an Oracle home that did not contain Oracle Portal:

UPGAST-02002: unsupported Oracle home type for patching component PORTAL
The command failed to complete successfully

Note:

In an environment where no products other than Application Developer have been installed in an Oracle home, you can update the Metadata Services (prefix_MDS) schema from the bin directory in the Oracle Common Oracle home.

4.5.2 Starting the Patch Set Assistant

To start Patch Set Assistant, go to the bin directory in the Oracle home for the product schema you want to patch, then run the following command:

On UNIX operating systems:

cd ORACLE_HOME/bin
./psa

On Windows operating systems:

CD ORACLE_HOME\bin
psa.bat

4.5.3 Command Line Syntax

The full command line syntax for the Patch Set Assistant is shown below:

./psa (or psa.bat)
   [-dbType database_type]
   -dbConnectString 'database_connection_URL'
   (NOTE: single quote characters are only required for Microsoft SQL Server  and IBM DB2 databases on UNIX operating systems)
   -dbaUserName dba_user_name
   -schemaUserName schema_user_name
   [-logLevel log_level]
   [-odi]
   [-invPtrLoc inventory_location]

See Table 4-2 for descriptions for these parameters.

Table 4-2 Patch Set Assistant Command Line Parameters

Parameter Required or Optional Parameter? Description

-dbType

Optional if database is Oracle; mandatory if database is non-Oracle.

Database type. Specify "Oracle", "Microsoft", or "IBM". If no database type is specified, the default is "Oracle."

Oracle Data Integrator supports additional database types. See Section 4.5.4, "Additional Database Connection Information for Oracle Data Integrator" for more information.

-dbConnectString

Required.

Database connection URL.

For Oracle databases on Windows and UNIX operating systems:

//host:port/service_name

For Microsoft SQL Server and IBM DB2 databases on Windows operating systems:

//host:port;DatabaseName=dbname

For Microsoft SQL Server and IBM DB2 databases on UNIX operating systems:

'//host:port;DatabaseName=dbname'

NOTE: For all non-Oracle databases on UNIX operating systems, the connection URL must be enclosed by a single quote (') character because of the way the UNIX shell treats the embedded semicolon (;) character.

For Oracle RAC databases, you only need to specify the connection URL for a single instance.

-dbaUserName

Required.

Schema name of the database administrator user:

  • For Oracle databases, enter SYS or any user with DBA role.

  • For Microsoft SQL Server databases, enter sa or any user with db_owner role.

  • For IBM DB2 databases, enter db2admin or any user with DBA role.

  • For Hypersonic databases, enter sa.

  • For Sybase databases, enter sa.

NOTE: If you are running the Patch Set Assistant against the Oracle Internet Directory (ODS) schema on Oracle databases, you must use the user SYS.

You will be prompted for the database administrator password from the command line.

-schemaUserName

Required.

User name of the schema being upgraded (for example, PORTAL, ODS, SOAINFRA, WEBCENTER, or IAU). ODS is the default schema name for Oracle Internet Directory.

This name must match one of the existing schema names in the schema version registry, and it must be one of the schemas that is valid for upgrade (one of the schemas listed in Section 4.1, "Which Schemas Need to be Updated with Patch Set Assistant?").

If the schema name does not match one of the schemas in the schema version registry, you will get the following error message:

UPGAST-02003: schema is not registered in schema version registry: "PREFIX_SCHEMANAME"

In addition, you must specify the schema user name in all CAPS for Microsoft SQL Server databases; this is because Microsoft SQL Server is case-sensitive and RCU creates schema names using all CAPS.

-logLevel

Optional.

Logging level. One of the following:

  • TRACE

  • NOTIFICATION

  • WARNING

  • ERROR

  • INCIDENT_ERROR

The default logging level is NOTIFICATION.

-odi

Required for Oracle Data Integrator to upgrade any ODI schemas not created by RCU.

This parameter is only needed for ODI schemas that were not created by RCU (for example, schemas created by the ODI Studio application). RCU only creates ODI schemas on Oracle, Microsoft SQL Server, and IBM DB2 databases.

You will be prompted for the database administrator password and ODI 11g Master Repository schema password.

The Oracle Data Integrator schema is supported on a variety of databases. For important information, see Section 4.5.4, "Additional Database Connection Information for Oracle Data Integrator" before running the Patch Set Assistant for your ODI schemas.

-invPtrLoc

Optional.

Alternate Oracle inventory location (UNIX operating systems only).

-help

Optional.

View all of the command line options.


4.5.4 Additional Database Connection Information for Oracle Data Integrator

Table 4-3 lists the additional connection information available for ODI. Specify the value in the Database column with the -dbType parameter, and specify the value in the JDBC URL column with the -dbConnectString parameter when running Patch Set Assistant.

Note:

If your ODI schemas are located on a Hypersonic SQL (HSQL) database version 1.7.3, you must upgrade your HSQL database version to version 2.0 before upgrading to ODI 11.1.1.5.0. For instructions on how to do this, see "Schema Cloning Process for Hypersonic SQL 1.7.3.3" in Oracle Fusion Middleware Upgrade Guide for Oracle Data Integrator.

Table 4-3 Available Database Connection Details For ODI

JDBC Driver JDBC URL Database

Oracle JDBC Driver

jdbc:oracle:thin:@host:port:service_name

Oracle

Sybase Direct JDBC Driver

jdbc:weblogic:sybase://host:port[;property=value[;...]]

Sybase

MySQL DataDirect JDBC Driver

jdbc:weblogic:sqlserver://host:port[;property=value[;...]]

Microsoft

DB2 DataDirect JDBC Driver

jdbc:weblogic:db2://host:port[;property=value[;...]]

IBM

Hypersonic 2.0 JDBC Driver

jdbc:hsqldb:hsql://host[:port][/alias][;property=value...]

Hypersonic


To see the specific database versions that are certified for your installation, refer to the "Oracle Fusion Middleware Supported System Configurations" document on the following page:

http://www.oracle.com/technetwork/middleware/ias/downloads/fusion-certification-100350.html

4.5.5 Sample Commands

Below is an example command that will update the DEV_PORTAL schema in an Oracle database on a UNIX operating system:

./psa -dbType Oracle -dbConnectString //ExampleOracleDB:1521/orcl.us.oracle.com -dbaUserName sys -schemaUserName DEV_PORTAL

Below is an example command that will update the ODS schema in an Oracle database on a UNIX operating system:

./psa -dbType Oracle -dbConnectString //ExampleOracleDB:1522/orcl2.us.oracle.com -dbaUserName sys -schemaUserName ODS

Below is an example command that will update the DEV_SOAINFRA schema in a Microsoft SQL Server database on a Microsoft Windows operating system:

psa.bat -dbType Microsoft -dbConnectString //ExampleSQLServerDB:1433;DatabaseName=ExampleSQLServerDB -dbaUserName sa -schemaUserName DEV_SOAINFRA

Below is an example command that will update the DEV_WC schema in an IBM DB2 database on a UNIX operating system:

./psa -dbType IBM -dbConnectString 'ExampleDB2DB:50000;DatabaseName=ExampleDB2DB' -dbaUserName db2admin -schemaUserName DEV_WC

4.5.6 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 VERSION, STATUS, UPGRADED FROM SCHEMA_VERSION_REGISTRY WHERE OWNER='schema_name';

Replace schema_name with the value supplied in the -schemaUserName parameter from the command line. The value UPGRADED flag='Y' indicates that schema has been upgraded to 11.1.1.5.0.

Note:

Unlike major releases which use Upgrade Assistant to upgrade one or more schemas at a time, Patch Set Assistant only updates a single schema (the one passed by -schemaUserName). In particular, the RUU component (Schema Version Registry) is not modified by Patch Set Assistant so its version number will remain at 11.1.1.3.0.

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.6, "Patch Set Assistant Log Files".

4.5.7 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 to 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.

4.5.8 Loading the Oracle SOA Suite Purge Scripts

When the amount of data in Oracle Fusion Middleware databases grows very large, maintaining the databases can become difficult and can affect performance. Oracle Fusion Middleware 11g Release 1 (11.1.1.4.0) and later provide a set of tools that can help you purge the Oracle Fusion Middleware of unneeded data.

When a new Oracle Fusion Middleware user installs the latest Oracle SOA Suite schemas using the Repository Creation Utility (RCU), stored procedures required by the Oracle SOA Suite purge scripts are automatically installed in the database.

However, if you are running Oracle Fusion Middleware 11g Release 1 (11.1.1.2.0) or Release 1 (11.1.1.3.0), these stored procedures are not installed automatically. As a result, after you run the Patch Set Assistant to update your Oracle SOA Suite schemas to the latest version, you must manually install the necessary database objects using the following procedure. This procedure is necessary if you want to use the purging capabilities provided by Oracle SOA Suite 11g Release 1 (11.1.1.4.0) or later:

  1. Locate, download, and unpack the latest Oracle Fusion Middleware 11g Repository Creation Utility (RCU) software archive.

    For more information, see "Obtaining RCU" in the Oracle Fusion Middleware Repository Creation Utility User's Guide.

    The directory where you unpack the RCU software is referred to as the RCU_HOME directory.

  2. Using SQLPlus, connect to the database where the Oracle SOA Suite schemas are installed using the database user that owns the SOAINFRA schema.

  3. Run the following script to load the database objects required for purging Oracle SOA Suite data:

    RCU_HOME/rcu/integration/soainfra/sql/soa_purge/soa_purge_scripts.sql
    
  4. Run the following script to load the database objects required for verifying the Oracle SOA Suite data in the database:

    RCU_HOME/rcu/integration/soainfra/sql/verify/soa_verify_scripts.sql
    

After you load the database objects using the provided SQL scripts, refer to "Managing Database Growth" in the Oracle Fusion Middleware Administrator's Guide for Oracle SOA Suite for more information.

4.6 Patch Set Assistant Log Files

The Patch Set Assistant writes log files to the following locations:

On UNIX operating systems:

ORACLE_HOME/upgrade/logs/psatimestamp.log

On Windows operating systems:

ORACLE_HOME\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.