Skip Headers

Agile Product Lifecycle Management Database Upgrade Guide Release 9.3.3 E39282-01 |
| Contents

Previous | Next |

# 4 Upgrading the Agile PLM Database

Before you upgrade your database, you should always make sure you are using the latest version of the AUT. Changes to scripts such as averify may occur after the initial version has been released.

If updates are needed to the AUT, you can always find the latest version on My Oracle Support. Download the AUT.zip file and extract the file contents into the same directory where you originally extracted the files.

To ensure your database is correctly upgraded, always check the My Oracle Support web site for updates.

## 4.1 Running the Automated Upgrade Tool

Before running the AUT:

- Make sure you have completed all of the pre-requisites listed under Preparing for the Upgrade.

- Edit the property file with the correct values for your upgrade. See Understanding the Property Files on page for more information.

- On UNIX, change the user permissions on all of the files under AUT_HOME in order for the upgrade log files to be created and the shell scripts under AUT_HOME/bin to be run.

- On AIX or systems with non-Sun JVMs, ensure that you remove the following parameters in the files mentioned below. You must do this before running Averify as well.

Remove this parameter | From this file
--- | ---
-hotspot -ms128m -mx640m | aut.sh located in <AUT>/bin
averify.sh located in <Averify>/bin
-hotspot -ms64m -mx64m | encryptpwd.sh located in <AUT>/bin
encryptpwd.sh located in <Averify>/bin

To run the AUT in interactive mode:

Go to the $AUT_HOME\bin directory and run the aut.bat (Windows) or aut.sh (Solaris) script.

You are prompted to enter the following usernames and passwords:

- Destination database user and password—user and password of the destination database.

- Destination database SYS user and password—name and password of the user account with 'sys' privileges in the destination database.

- Source database user and password—user and password of the source database.

  To run the AUT in command line mode:

  Usage: aut.bat/sh :<destination db user> <destination db user password> <sys user> <sys password> <source db user> <source db user password>

## 4.2 Checking the Status of the Upgrade

You can monitor the status through the log files that are generated during the upgrade. The $AUT_HOME\logs directory contains all of the upgrade-related log files. AUT.log is the main log file for the upgrade. Separate directories are created for the logs generated by averify and the database scripts. These log files are located in the averifylogs and oracle subdirectories.

## 4.3 What's Not Upgraded

Markup restrictions that were enforced through privileges are not retained when you upgrade. With the new security model implemented in 9.2.2.2, Markup is a subclass under the File Folders class and all markup privileges are automatically enabled. To prevent users from viewing or modifying the existing markups, you must disable blanket privileges at File Folders class level in Java Client and enable Markup privilege for relevant users only.

## 4.4 Troubleshooting the Upgrade

The following list describes some problems that can occur during the database upgrade:

What if I can't resolve my TNS service name?

If the TNS service name cannot be resolved, then the service name specified in the aut.properties file is not correctly defined in the tnsnames.ora file.

Perform the following checks to fix the error:

- Verify that a tnsnames.ora file exists and is in the correct place. See your Oracle documentation for more information on the required name and location.

- Verify that the service name exists in one of the tnsnames.ora files. Add the service name, if necessary.

- Make sure there are no syntax errors in the tnsnames.ora file, especially unmatched parentheses or stray characters.

Why did I receive an Invalid User Name or Password error in the AUT.log file?

You may receive this error if the database connection was not successful. The following Oracle error messages may be displayed:

EXP-00004: invalid username or password

Cause: An invalid username or password was specified.

Action: Retry with a valid username and password.

ORA-01017: invalid username/password; logon denied

Cause: An invalid username or password was entered in an attempt to log on to Oracle. The username and password must be the same as was specified in a GRANT CONNECT statement. If the username and password are entered together, the format is username/password.

Action: Enter a valid username and password combination in the correct format.

What errors would I see if an import fails?

An import can end abnormally due to some of the following reasons:

IMP-00009: abnormal end of export file

Cause: The export file is probably from an aborted Export session.

Action: If so, retry the export and import.

IMP-00013: only a DBA can import a file exported by another DBA

Cause: The privileges needed to import an export file generated by a database administrator do not exist. Only a database administrator can import such files.

Action: The source and destination database users should have the same roles and privileges. Because the source database has the DBA role, the database destination should also have the role. If you do not want the destination database to have the DBA role, then remove the role from the source database and retry the upgrade.

IMP-00041: Warning: object created with compilation warnings

Cause: The object in the SQL statement following this error was created with compilation errors. If this error occurred for a view, it is possible that the base table of the view was missing or altered.

Action: This is a warning. The object may have to be recompiled before being used.

Why is AUT hanging?

AUT may not be hanging. Processes may be running that seem to take longer than others. For example:

- Indexes may be missing on the source database. If this is the case, averify may be running slowly.

- The import may be taking a longer time because it's a large database.

What should I do if AUT suddenly exits?

Check the AUT.log file. If no information is available, then the AUT could not start because of invalid settings. Check the pre-requisites and the contents of the aut.properties file before retrying.

If I have database connection problems, what Oracle errors will I get?

Database connection errors can occur if the SQL connection to the source or destination database is lost. Make sure that the database connections are available before running the AUT. If connection problems occur, you may see some of the following Oracle error messages:

ORA-01034: ORACLE not available

Cause: Oracle was not started. Possible causes include the following:

- The SGA requires more space than that was allocated for it.

- The operating system variable pointing to the instance is improperly defined.

Action: Refer to accompanying messages for possible causes and correct the problem mentioned in the other messages. If Oracle has been initialized, then on some operating systems, verify that Oracle was linked correctly.

ORA-01089: immediate shutdown in progress - no operations are permitted

Cause: The SHUTDOWN IMMEDIATE command was used to shut down a running Oracle instance, terminating any active operations.

Action: Wait for the instance to be restarted or contact the database administrator.

ORA-01090: shutdown in progress - connection is not permitted

Cause: The SHUTDOWN command was used to shut down a running Oracle instance, disallowing any connects to Oracle.

Action: Wait for the instance to restart or contact the database administrator.

ORA-12541: TNS: no listener

Cause: The connection request could not be completed because the listener is not running.

Action: Ensure that the supplied destination address matches one of the addresses used by the listener - compare the tnsnames.ora entry with the appropriate listener.ora file (or tnsnav.ora if the connection is by way of an interchange). Start the listener on the remote machine.

I received an ORA-00955 error in my AUT.log file. What should I do?

ORA-00955: name is already used by an existing object

Cause: An attempt was made to create a database object (such as a table, view, cluster, index, or synonym) that already exists. A user's database objects must have distinct names.

Action: Enter a unique name for the database object or modify or drop the existing object so it can be reused.

You can ignore this in the Agile schema because the object already exists. The upgrade process takes care of dropping the object and recreating it if there are any changes.

I received an ORA-00904 error. What should I do?

ORA-00904: MS_JAVA "."LONGNAME":Invalid Identifier.

Solution: Log in as the sys user and run the $ORACLE_HOME\javavm\install\initdbj.sql script.

I received an ORA-01555 error. What should I do?

ORA-01555: "snapshot too old (rollback segment too small" when using Automatic Undo Management (AUM).

Solution: The UNDO tablespace is too small. Increase the size of the UNDO tablespace. Note that the UNDO tablespace should be large enough to store the undo data generated by active transactions as well as those preserved to honor the undo retention setting.

Increase the value of the Undo_retention parameter. This is important for systems running long queries. The parameter's value should at least be equal to the length of the longest running query on a given database instance. This can be determined by querying the V$UNDOSTAT view once the database has been running for a while:

    SQL> select max (maxquerylen) from v$undostat;

How long does it take the AUT to run?

The amount of time it takes the AUT to run depends on the database size. The amount of time needed to upgrade is also based on the database version of the source database. Import and export steps can be avoided by using the sourceEqualsDest property which also reduces the time.

## 4.5 Data Migration for Signoff User Dual Identification

Agile provides optional data migration scripts that can be used by customers who choose to implement the Signoff User Dual Identification feature for approval signoffs. The Signoff User Dual Identification feature was introduced to address FDA regulations laid out in 21 CFR Part 11 Section 11.200. The system now facilitates the usage of two forms of identification from the user when signing off on a document such as a change order.

If the Login Password is to be used as a second form of identification, the following scripts can be used to automate the required administration procedures.

Details of these scripts and the actions they perform are provided in the following table:

# | Script | Action
--- | --- | ---
1. | UseLoginPwdYesList.bat | Identifies the list of users who have set the "Use Login Password for Approval" option to Yes in Preferences and writes the output to a comma separated (.csv) file. Note The default filename for the output file is Output.csv. To change the filename, suffix a filename to the command as shown: UseLoginPwdYesList <FileName>. Where <FileName> is the name of the .csv file.
2. | ChangeUseLoginPwdToNo.bat | Resets the "Use Login Password for Approval" option to No for all users who have set it to Yes in Preferences.
3. | LoginPwdAppPwdMatchList.bat | Identifies the list of users who have identical Login and Approval passwords and have set the "Use Login Password for Approval" option to No. Writes the output to a .csv file.
4. | SetApprovalPwd.bat | Resets the approval passwords to an administrator-supplied value for users who have identical Login and Approval passwords and have set the "Use Login Password for Approval" option to No. To change the password, suffix the new password value to the command as shown: SetApprovalPwd <password>. Where <password> is the new value for the approval password. (The password is in cleartext.)

To migrate data to meet second signature requirements:

1. Locate the second signature migration scripts stored at the following path:

   <Agile installation folder>/ agileDomain/tools/ SecondSignature.zip

2. Unzip the SecondSignature.zip file and extract the scripts to a new folder (temp directory) in the same location.

3. Run each bat/shell file in the sequence listed in the table above.

   Note: For Solaris/Linux installations, the corresponding .sh files are provided. You may have to change the format of the script files from DOS to UNIX.

Once approval passwords have been reset, users should be asked to change the administrator-provided approval password to a password of their choice. (This should not be the same as the login password.)

## 4.6 Running Averify

Running Averify and using the results to eliminate known errors is a prerequisite for diagnosing problems. Averify is not comprehensive, however, and finding zero errors does not certify a database as perfect.

To run Averify:

1. Make sure you have downloaded the latest version of AUT from My Oracle Support (https://support.oracle.com).

2. Make sure you have backed up your database.

3. Go to the \bin directory and run the averify.bat script on Windows or averify.sh script on UNIX in interactive mode. You are prompted to enter the database user name and password.

   Or you can run the averify script in command line mode as follows:

   Usage: averify.bat/sh <db user> <db user password>

   Note: If you are running Averify as a Cron job, add the following line to the averify.sh file after the # Set up the environment comment line: cd /export/home/oracle/averify/bin

4. Go to the $AUT_HOME\scripts\oracle\utilities\averify directory.

5. Connect to the Agile database from the command line using SQL*Plus.

6. Run the oracle_averify9x.sql script.

       SQL> @oracle_averify9x.sql

   Note: If errors are generated in the oracle_averify_report.log file located in the $AUT_HOME\scripts\oracle\utilities\averify directory, contact Agile Support.

### 4.6.1 Checking the Status of Averify

You can monitor the status through the log file that is generated while Averify is run. The \logs directory contains the averify.log file.

If you chose to have the log files sent as an email attachment, the averify.zip file, containing the averify.log and oracle_averify_report.log, is sent upon completion.

## 4.7 Notes on Agile Advantage (AA) Upgrade

Take note of the following when you upgrade to Agile PLM from Agile Advantage (AA) Releases:

- When you upgrade from any AA release or 8.x releases, obsolete privileges of the following types will not get migrated: Create PDX Package, FTS, Reports, Specify Output columns, and Agile Server Monitor.

- Organizations in AA will not be migrated as Suppliers in Agile 9.3, as AA has both Organization and Supplier objects. Organization Contacts will be migrated as inactive users without any Roles and Privileges associated. The Agile Administrator will need to assign Roles and Privileges manually.

- AA backup tables are not automatically removed during upgrade from AA. Customers must run the script CleanupBackuptables.sql available in <AUT_Home>\Scripts\Oracle\Utilities\Script_tools to clean up these tables.

- Full Text Search field properties are migrated as detailed in the table below. In Agile Java Client, the Indexing field value is set to 'Manual' by default. If you want it be 'Scheduled', the administrator must edit the Recurrence field properties appropriately.

AA Field Name | AA Field Value | A9 Field Name | A9 Field Value
--- | --- | --- | ---
Enable Stem Search | Yes | Stem Searching | Enabled
Enable Stem Search | No | Stem Searching | Disabled
Indexed File Types | Comma-separated list | Index File Types | Comma-separated list
Indexing | Manual | N/A | N/A

## 4.8 Notes on Variant Management Upgrade

In Agile PLM 9.3.1, the fields Relation Type and Configuration Graph were moved from the Relationships tab to the Instances tab. Customers who already have a Variant Management patch installed should take the following actions after upgrade to 9.3.3:

1. Call Oracle Consulting to migrate the user data from the Relationships tab to the Instances tab.

2. Once migration is successful, run the following SQL statements to remove the redundant data in the Relationships tab:

   --Drop legacy data from the Relationships table:

       Delete from RELATIONSHIP where relation_type = 1;

   --Drop relation_type column

       Alter table RELATIONSHIP drop COLUMN "RELATIoN_TYPE";

   --Drop configuration graph table:

       Alter table CONFIGURATION_GRAPH_DATA drop
       constraint CONFIGURATION_GRAPH_DATA_FK;
       Drop table CONFIGURATION_GRAPH_DATA;

   --Update dot on Relationships tab if it is empty now:

       update ITEM set flags=
       NVL(subStr(flags,1,29),'') || '0' ||
       NVL(subStr(flags,31),'')
       where id not in
       ( select distinct r.ctr_objid from relationship
       r ) and NVL(subStr(flags,30,1),'') != '0';

3. Next, run the following SQL statements to remove unnecessary metadata:

   --Delete Relation Type listname and corresponding entries:

       delete listentry where parentid=2000009331;
       delete listname where id=2000009331;

   --Delete Relation Type attribute:

       delete from nodetable where id-2000009332;
       delete from propertytable where parentid=2000009332;
       DELETE FROM APPLIEDTO WHERE attid in (2000009332);

   --Remove Relation Type attribute from part's Relationships table

       delete from nodetable where id=2000009333;
       delete from propertytable where parentid=2000009333;

   --Delete configuration graph attribute

       delete from nodetable where id=2000009334;
       delete from propertytable where parentid=2000009334;
       DELETE FROM APPLIEDTO WHERE attid in (2000009334);
       DELETE FROM TABLEINFO WHERE att in (2000009332, 2000009334);

   --Remove configuration graph attribute from the part's Relationships table

       delete from nodetable where id=2000009467;
       delete from propertytable where parentid=2000009467;