5 Updating Your Schemas with 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.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:

./psa

On Windows operating systems:

psa.bat

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.

'//host:port/service_name'

'//host:port;DatabaseName=dbname'

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/orcl.us.oracle.com' -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/orcl2.us.oracle.com' -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 "11.1.1.3.0" 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.