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

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

Go to previous page
Go to next page
View PDF

5 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 the 11g Release 1 ( version of the schema to 11g Release 1 ( 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.

The following component schemas must be updated with the Patch Set Assistant in order to update them to 11g Release 1 (

This chapter contains the following topics:

5.1 Before You Begin

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

5.1.1 Back Up Your Database and Database Schemas

Before running Patch Set Assistant, you should perform a physical backup of your database. Refer to your database documentation for more information.

If you run the Patch Set Assistant to upgrade an existing schema and it does not succeed, you must restore the original schema before you can try again. Make sure you backup your existing database schemas before you run the Patch Set Assistant.

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

5.1.3 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;

5.2 Running the Patch Set Assistant

The Patch Set Assistant is installed into the ORACLE_HOME/bin (on UNIX operating systems) or ORACLE_HOME\bin (on Windows operating systems) directory by the Sparse Installer (see Chapter 4, "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:

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

5.2.1 Starting Patch Set Assistant

To start Patch Set Assistant, go to the ORACLE_HOME/bin (on UNIX operating systems) or ORACLE_HOME\bin (on Windows operating systems) directory for the product schema you want to patch, then run the following command:

On UNIX operating systems:


On Windows operating systems:


5.2.2 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'
   -dbaUserName dba_user_name
   -schemaUserName schema_user_name
[-logLevel log_level]
[-invPtrLoc inventory_location]

See Table 5-1 for descriptions for these parameters.

Table 5-1 Patch Set Assistant Command Line Parameters

Parameter Description


Database type. Only "Oracle" or "Microsoft" are supported. The default is "Oracle."


Database connection URI.

For Oracle databases:


For Microsoft SQL Server databases:


Note that the connection URI must be enclosed by a single quote (') character.

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


Database administrator user name. For Oracle databases, this is usually SYS or SYSTEM; for Microsoft SQL Server databases, this is usually sa.

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


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

This name must match one of the version schema names that was created using Repository Creation Utility (RCU).

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.


Logging level. One of the following:






The default logging level is NOTIFICATION.


Alternate Oracle inventory location (UNIX operating systems only).


View all of the command line options.

5.2.3 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 '//myOracleDB:1521/' -dbaUserName sys -schemaUserName DEV_PORTAL

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

./psa -dbType Oracle -dbConnectString '//myOracleDB:1522/' -dbaUserName sys -schemaUserName DEV2_ODS

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

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

5.2.4 Verifying the 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 FROM schema_version_registry WHERE owner='schema_name';

Replace schema_name with the value supplied in the -schemaUserName parameter from the command line. The version number should appear as "" and the status should appear as "VALID."

For Oracle databases, if there are any objects that appear as "INVALID," be sure to 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.

5.3 Patch Set Assistant Log Files

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

On UNIX operating systems:


On Windows operating systems:


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.