4 Updating Your Schemas with Patch Set Assistant

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

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.

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

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]
[-invPtrLoc inventory_location]

See Table 4-1 for descriptions for these parameters.

//host:port/service_name

//host:port;DatabaseName=dbname

'//host:port;DatabaseName=dbname'

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

4.5.4 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.5 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 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 "11.1.1.4.0" and the status should appear as "VALID."

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.6 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.7 Loading the Oracle SOA Suite 11.1.1.4.0 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) provides 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 Oracle SOA Suite 11g Release 1 (11.1.1.4.0) 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 11.1.1.4.0, 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):

1. Locate, download, and unpack the Oracle Fusion Middleware 11g Release 1 (11.1.1.4.0) 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.