5 Configuring the Agile PLM Database

5.1 Reorganizing the Database

Important:

The Agile PLM database includes two user accounts, CTXSYS and AGILE. The following upgrade procedure is required for the database sizing and configuration of the CTXSYS and AGILE accounts.

After AUT is run, the upgraded schema and data have been validated. However, the data must be analyzed and statistics generated to enable Cost-Based optimizer (CBO) and Full Text Search (FTS) support. There are eight Agile-specific tablespaces required for optimization. The Agile schema has to be reorganized for this optimization to occur.

Note:

If you are using Oracle Datapump for import and export, use agile9expdp and agile9impdp scripts instead of agile9exp and agile9imp scripts in the steps outlined here.

To reorganize the upgraded schema:

On the database server, change to the following directory:

(Windows) \oracle\admin\<Oracle SID>\create\<agile schema user>

(UNIX) $ORACLE_BASE/admin/$ORACLE_SID/create/agile
Run the agile9exp script.

The agile9exp.dmp file is created in the current folder. This dump file is used with the agile9imp script. Make sure the dump file can be imported successfully before proceeding to the next step.

Note:
If you are using Datapump for export, this file will be called agile9expdp.dmp.
Run the recreateagile script to drop the existing account and recreate the agile account and schema.
Run the agile9imp script to import the upgraded Agile schema, including setup of CBO and FTS.
Rename the existing agile9exp.dmp file to agile9exp_upgrade935.dmp for backup. Also rename the agile9exp.log file to agile9exp_upgrade935.log.
Run the agile9exp script again.

The Agile9exp.dmp file created can be used as a backup of the system after the Agile PLM 9.3.5 database reorganization.
Rename the agile9exp.dmp file to agile9exp_reorg935.dmp for backup. Also rename the agile9exp.log file to agile9exp_reorg935.log.
Start SQL*Plus from a command line and log in as agile/tartan or agile/tartan@<Oracle SID>.

Run the agile9_check.sql file to validate the schema integrity and confirm integrity of the database reorganization.

SQL> @agile9_check.sql

Note:

Any warnings related to non-Agile tables can be ignored if you want to retain these tables in the Agile schema. If not, you can drop the non-Agile tables and run agile9check.sql again. If any other errors are displayed, contact Oracle Support Services http://www.oracle.com/agile/support.html. All errors must be fixed before proceeding to the next step.

Start the Oracle listener.

Note:
The following steps should only be performed after you have upgraded the Agile PLM application:
Start the Agile application server.
Shut down the database.
Perform a cold backup of all the database-related files.
Restart the database, listener, and application server.

5.2 Upgrading the Schema without Database Reorganization

The Agile PLM database installer generates a shell script agile9postupgrade.bat/sh that can be run after upgrading the Agile PLM schema with AUT. If you choose not to perform a database reorganization, then you must at least run the agile9postupgrade shell script.

The agile9postupgrade script isolates the SQL statements that compile invalid objects, so you can independently compile, validate, and gather statistics on the schema after an upgrade. The agile9postupgrade script references three SQL scripts:

SQL Scripts	Description
compile_invalid_objects.sql	Compiles invalid objects after running AUT
agile9_check.sql	Validates the Agile PLM schema.
agile9stats.sql	Gathers statistics on the upgraded schema.

If there are any fragmentation or corruption issues in the indexes, you can also choose to perform a partial database reorganization using the following scripts:

agile9_index_recreate.sql	Drops and creates Normal or function-based Normal, non-unique, non-PK indexes.
agile9_ctx_recreate.sql	Drops and creates CTX indexes (except for FILES_CONTENT_IDX which is handled by agile9_fts.sql).

5.3 Updating Content URLs for File Attachments

Importing or reorganizing the database sets all values in FILES.CONTENT_URL to NULL which prevents existing attachment content from being searchable. To allow Full Text Search (FTS) on existing attachments, their Content URLs must be updated in order for the files to be indexed by Oracle Text and searchable within Agile PLM. For Agile PLM 9.3.2 and later databases, updating content URLs is accomplished within the Java Client. For more information, see the "Full Text Search" chapter of the Agile PLM Administrator Guide.

Note:

For Agile PLM 9.3.1.x and prior databases, content URLs are updated using AUT. Perform the following steps.

Before using the update_content_url.bat/sh script to update FILES.CONTENT_URL:

Set the following parameters in aut.properties.

sourceEqualsDest = true

file.manager.url = <http://<HOST>:<PORT>/Filemgr/AttachmentServlet>

To obtain the file.manager.url, log on to Agile Java Client and navigate to Admin > Server Settings > Locations > File Manager. Double-click the Primary File Manager (iFS) entry and copy the URL from the File Manager URL field.
Open a command prompt on Windows or a terminal window on UNIX, and change to the AUT/bin directory:
If using AUT version 1.7.5 or later:

On Windows, run the following batch file:

.\update_content_url.bat

On UNIX, execute the following shell script:

./update_content_url.sh
Note:
You are prompted to enter the DB username and password.
If using an AUT version before 1.7.5:

On Windows, run the following batch file:

.\aut.bat execute-fts-post-tasks-921

On UNIX, execute the following shell script:

./aut.sh execute-fts-post-tasks-921

To synchronize the Oracle Text domain index associated with files (causes Oracle Text to begin downloading and parsing files from Agile PLM and storing their content), open a SQL*Plus session, connect as schema owner (AGILE), and issue the following command:

call agile_server_fts.sync_index('files_content_idx');

Note:

This final step synchronizes the attachments domain index and requires that the application server and primary file manager are running. The synchronization process may take a long time to complete depending on the number and size of attachments being indexed. After the process completes, searches performed on attachment content should result in appropriate files being returned in the search results within Agile PLM.

5.4 Configuring the Agile SDK

If you are using the Agile SDK, delete the AgileSDK.cache directory from your client systems after you have upgraded the Agile PLM database and application server. The AgileSDK.cache directory can be found under the temp directory (%temp%AgileSDK.cache).

5.5 Configuring PG&C

If you have the PG&C component installed, additional configuration may be needed for substance migration.

Substance migration is necessary if you have met ALL of the following requirements:

Upgraded your database from Agile PLM 9.2 to Agile PLM 9.3.5.
Imported the JGPSSI substances and substance groups.

Note:

In Agile PLM 9.3.5, you should use IPC declarations with IPC substances and substance groups. The IPC list of substances and substance groups is slightly different than the JGP list of substances and substance groups.

Important:

Contact Oracle Support to obtain the files needed for substance migration.

If you imported the JGPSSI substances in Agile PLM 9.2, but do not want to use the IPC substances, you can continue to use the JGPSSI substances without migrating.

5.6 Configuring PPM

With PPM objects, there should always be at least one user in the team, such as Owner. For every user on the team, a corresponding Access Control List (ACL) in the Share should exist. In the 9.0 and 9.1 releases of Agile PLM, a logged in user was allowed to delete all users from Share which included the owner's ACL.

You must clean the corrupted data in the ObjectACL database table to access the shared objects. The data cleanup involves replacing the deleted role with a valid, non-deleted equivalent role.

The following SQL statement cleans all of the corrupted rows in ObjectACL table where a deleted role is assigned to a user with the valid role:

DELETE FROM TEAM WHERE ACTIVITY_ID<=0;
INSERT INTO OBJECTACL SELECT TEAM.activity_id,(SELECT CLASS FROM ACTIVITY WHERE
id=TEAM.activity_id),TEAM.user_id,',9506,',sysdate,sysdate FROM TEAM WHERE 
TEAM.user_id NOT IN(SELECT USERID FROM OBJECTACL WHERE OBJECTACL.objid=TEAM.activity_id AND OBJECTACL.objclass IN (18022,18387))

In the previous SQL statement, the Program Team Member role is generally assigned to a user and the ID for this out-of-box role is 9506.

The roleid values may differ if you have customized your database. In this case, use the following SQL statement to obtain roleid values for each role in your database:

select ID from nodetable where parentid=5006 and value='<name of role>';

The Agile Administrator is still allowed to delete a role assigned to a user in the object's ACL. If a role is deleted, an error message stating "Node <number> does not exist in cache." displays when the object is accessed.

Also, in earlier releases of Agile PLM, the Dashboard or other PPM-related home pages were allowed to be selected as the start page. In Agile PLM 9.2.2.1, a license check was added for default home pages. If any users have previously set their preferred Start Page and preferred Inbox to values that are related to PPM objects and PPM is no longer installed, run the following SQL statements to set the pages:

To set the Preferred Start Page to Home for all users:

update agileuser set PREFSTARTPAGE=1;

To set the Preferred Inbox View to Notifications if the Preferred Inbox View is Activities

update agileuser set PREFINBOXVIEW=3 where PREFINBOXVIEW=2;

5.6.1 Running the PPM Post-Upgrade Utility

After you have upgraded your database and installed the Agile PLM 9.3.5 application, you must run the PPM Post Upgrade Utility to accommodate the business rule changes introduced in this new version. For information on how to run this utility, see the Agile Product Lifecycle Management Application Installation Guide.