| Agile Product Lifecycle Management Database Upgrade Guide Release 9.3.3 E39282-03 |
|
 Previous |
 Next |
| Agile Product Lifecycle Management Database Upgrade Guide Release 9.3.3 E39282-03 |
|
Previous |
Next |
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 website for updates.
Before running the AUT:
Make sure you have completed all of the pre-requisites listed in "Preparing for the Upgrade".
Edit the property file with the correct values for your upgrade. See "Understanding the Property Files" 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 (UNIX) 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>
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.
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.
The following list describes some problems that can occur during the database upgrade:
What if I cannot 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 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, perhaps 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. Ensure that the database connections are available before running the AUT. If connection problems occur, you may see 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 system.
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. The UNDO tablespace should be large enough to store the undo data generated by active transactions and 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.
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 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 file name for the output file is Output.csv. To change the file name, suffix a file name to the command as shown: UseLoginPwdYesList <File Name>. Where <File Name> 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:
Locate the second signature migration scripts stored at the following path:
<Agile installation folder>/ agileDomain/tools/ SecondSignature.zip
Unzip the SecondSignature.zip file and extract the scripts to a new folder (temp directory) in the same location.
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.)
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:
Make sure you have downloaded the latest version of AUT from My Oracle Support (https://support.oracle.com).
Make sure you have backed up your database.
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 run 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 |
Go to the $AUT_HOME\scripts\oracle\utilities\averify directory.
Connect to the Agile database from the command line using SQL*Plus.
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. |
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.
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 |
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:
Contact Oracle Consulting Services to migrate the user data from the Relationships tab to the Instances tab.
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';
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;
