Database Upgrade Guide
Agile 9.2.2.1
TP1996-9.2.2.1A
Make sure you check for updates to this manual at the Oracle Technology Network Website
Agile 9.2.2.1
COPYRIGHT AND TRADEMARKS
Copyright © 1995, 2007, Oracle. All rights reserved.
The Programs (which include both the software and documentation) contain proprietary information;
they are provided under a license agreement containing restrictions on use and disclo sure and are
also protected by copyright, patent, and other intellectual and industrial property laws. Reverse
engineering, disassembly, or decompilation of the Programs, except to the extent required to obtain
interoperability with other independently created software or as specified by law, is prohibited.
The information contained in this document is subject to change without notice. If you find any
problems in the documentation, please report them to us in writing. This document is not warranted
to be error-free. Except as may be expressly permitted in your license agreement for these
Programs, no part of these Programs may be reproduced or transmitted in any form or by any
means, electronic or mechanical, for any purpose.
If the Programs are delivered to the United States Government or anyone licensing or using the
Programs on behalf of the United States Government, the following notice is applicable:
U.S. GOVERNMENT RIGHTS Programs, software, databases, and related documentation and
technical data delivered to U.S. Government customers are "commercial computer software" or
"commercial technical data" pursuant to the applicable Federal Acquisition Regulation and agencyspecific supplemental regulations. As such, use, duplication, disclosure, modification, and
adaptation of the Programs, including documentation and technical data, shall be subject to the
licensing restrictions set forth in the applicable Oracle license agreement, and, to the extent
applicable, the additional rights set forth in FAR 52.227-19, Commercial Computer Software-Restricted Rights (June 1987). Oracle USA, Inc., 500 Oracle Parkway, Redwood City, CA 94065.
The Programs are not intended for use in any nuclear, aviation, mass transit, medical, or other
inherently dangerous applications. It shall be the licensee's responsibility to take all appropriate failsafe, backup, redundancy and other measures to ensure the safe use of such applications if the
Programs are used for such purposes, and we disclaim liability for any damages caused by such
use of the Programs.
Oracle and Agile are registered trademarks of Oracle Corporation and/or its affiliates. Other names
may be trademarks of their respective owners.
The Programs may provide links to Web sites and access to content, products, and services from
third parties. Oracle is not responsible for the availability of, or any content provided on, third-party
Web sites. You bear all risks associated with the use of such content. If you choose to pu rchase
any products or services from a third party, the relationship is directly between you and the third
party. Oracle is not responsible for: (a) the quality of third-party products or services; or (b) fulfilling
any of the terms of the agreement with the third party, including delivery of products or services and
warranty obligations related to purchased products or services. Oracle is not responsible for any
loss or damage of any sort that you may incur from dealing with any third party.
Wednesday, September 12, 2007
ii Database Upgrade Guide
CONTENTS
Preparing for the Upgrade................................................................................................vi
Preparing the System.......................................................................................................................................................vi
Downloading the Software..............................................................................................................................................vii
Creating the Agile PLM 9.2.2.1 Database Instance........................................................................................................vii
Validating the Databases................................................................................................................................................viii
Understanding the Property Files..................................................................................... 9
The aut.properties File......................................................................................................................................................9
Defining the aut.properties Parameters............................................................................................................................9
Source Details............................................................................................................................................................10
Destination Details.....................................................................................................................................................11
Averify Details............................................................................................................................................................12
Import Warnings.........................................................................................................................................................12
File Manager Internal Locator Details........................................................................................................................12
Destination Version....................................................................................................................................................12
Language Details.......................................................................................................................................................13
8.5 Only Details..........................................................................................................................................................13
The psupgrade.properties File........................................................................................................................................14
Defining the psupgrade.properties Parameters..............................................................................................................15
The peupgrade.properties File........................................................................................................................................17
Defining the peupgrade.properties Parameters..............................................................................................................18
Upgrading the Agile Database to Agile PLM 9.2.2.1....................................................... 21
Running the Automated Upgrade Tool............................................................................................................................21
Checking the Status of the Upgrade...............................................................................................................................21
Troubleshooting the Upgrade..........................................................................................................................................21
Configuring the Agile PLM 9.2.2 Database ..................................................................... 25
Reorganizing the Database.............................................................................................................................................25
Running Full Text Search Scripts....................................................................................................................................26
Configuring the Agile SDK ..............................................................................................................................................26
Configuring PG&C...........................................................................................................................................................27
Configuring PPM.............................................................................................................................................................27
Running the PPM Post Upgrade Utility......................................................................................................................28
Upgrading from Agile PLM 9.2.1.1..................................................................................................................................28
iii
Agile 9.2.2.1
AUT Maintenance............................................................................................................ 29
Updating the AUT............................................................................................................................................................29
Running Averify...............................................................................................................................................................29
iv Database Upgrade Guide
Preface
The Agile documentation set includes Adobe® Acrobat™ PDF files. The Oracle Technology
Network (OTN) Web site
the latest versions of the Oracle|Agile PLM PDF files. You can view or download these manuals
from the Web site, or you can ask your Agile administrator if there is an Oracle|Agile Documentation
folder available on your network from which you can access the Oracle|Agile documentation (PDF)
files.
Note To read the PDF files, you must use the free Adobe Acrobat Reader™ version 7.0 or later.
This program can be downloaded from the Adobe Web site (http://www.adobe.com).
The Oracle Technology Network (OTN) Web site
http://www.oracle.com/technology/documentation/index.html) can be accessed through Help >
(
Manuals
Oracle|Agile PLM documentation can be found on the
(
in both the Agile Web Client and the Agile Java Client. If applicable, earlier versions of
http://www.agile.com/support).
(http://www.oracle.com/technology/documentation/index.html) contains
Agile Customer Support Web site
If you need additional assistance or information, please contact
284-3900 for assistance.
Note Before calling Agile Support about a problem with an Oracle|Agile PLM manual, please
have ready the full part number, which is located on the title page.
support@agile.com or phone (408)
Readme
Any last-minute information about Oracle|Agile PLM can be found in the Readme file on the Oracle
Technology Network (OTN) Web site
http://www.oracle.com/technology/documentation/index.html).
(
Agile Training Aids
Go to the Agile Training Web page (http://training.agile.com) for more information on Agile
Training offerings.
v
Preparing for the Upgrade
You can upgrade your database to Agile PLM 9.2.2.1 if you have any of the following products
currently installed:
à Product Collaboration 8.5 SP1-SP7
à Product Service and Improvement 8.5 SP1-SP7
à Program Execution 8.5 SP2
à Product Cost Management 8.5 SP1
à Agile 9.0 SP1-SP7
Agile 9.1 SP1-SP4 à
à Agile 9.2
à Agile 9.2.0.1
à Agile 9.2.0.2
à Agile 9.2.1
à Agile 9.2.1.1
à Agile 9.2.1.3
à Agile 9.2.1.4
à Agile 9.2.1.5
à Agile 9.2.2
The upgrade
databa
database.
tool takes inputs from a property file and completes the upgrade using your existing
se as the source database, while the newly created 9.2.2.1 database is the destination
Preparing the System
Note You should prepare a separate test environment to become familiar with the
upgrade procedure.
vi Database Upgrade Guide
If you are upgrading from an 8.5 product an
your database will be installed, you must ma
d your Oracle 8i home is not local to the machine where
p a local drive on the destination machine to point to
the existing Oracle 8i home.
Agile PLM supports Oracle 9i and 10g databases. You MUST install either Oracle 9i or 10g on the
system whe
re you are installing the Agile database before starting the upgrade. Use the instructions
in the Agile PLM Database Installation Guide to install Oracle 9i Release 2 or Oracle 10g Release
2, if it is not already installed.
Also, if you are upgrading from an 8.5 product, you should create a new Oracle instance for the new
Agile PLM product version and try to reuse the existing 8.5 Oracle instance. It is further
recommended that the new Oracle instance is created on Oracle 10g to take advantage of the
latest Oracle
Y
ou must also have JRE 1.4.2 installed and the JAVA_HOME environment variable should be set
with the same JRE on the system where the AUT is run.
features.
Downloading the Software
The automated upgrade tool software can be downloaded from the Agile Support Website
(http://ww
server.
Extract the AUT.zip file to a local drive. After the AUT is extracted, all of the files are located in an
AUT dire
w.agile.com/support). The software should be downloaded to your destination database
ctory on the local drive. In this document, this directory is known as the AUT_HOME.
Note As a result of SQL script optimization, the size of the AUT.zip file is now reduced to
approximately 40 MB (earlier 100 MB), enabling faster download.
Creating the Agile PLM 9.2
.2.1 Database Instance
The Agile PLM 9.2.2.1 database instance must be created before you can upgrade your existing
database. The 9.2.2.1 database will be used as the destination database during the upgrade
process.
To create the Agile PLM 9.2.2.1 database instance, follow the instructions in
Installer” section of the Agile PLM Database Installation Guide, based on
em
syst .
r th 9.2.2.1 database instance is created, run
Afte e the useragile.sql script, located in the
$AUT_HOME
database
Note Change the token value
\scripts\Oracle\utilities\script_tools directory, to create a new, empty user in the
.
s inside of the % symbols in the useragile.sql file
the “Starting the
your specific operating
before running the script.
vii
Validating the Databases
You
should validate the source and destination databases to ensure a successful upgrade by
perf
orming the following checks:
à Run the latest averify script on the source database and fix any errors.
à se to be upgraded, you should know the following:
For each databa
our
y S ce and destination database user and password
y Destination system user and password
y TNS configuration for source and destination database on the respective database
systems
y Paths to the Oracle Homes
Map the so
urce and destination Oracle Homes, if necessary.
à
à Validate the TNS entries of the source and destination databases.
On a command line, change ORACLE_HOME to point to the Oracle Home of the source
database, then try to connect to it. Repeat this procedure for the destination database.
à Make sure the destination database user is empty and does not contain any schema objects in
its schema.
Note This does not apply if your source database will also be your destination
database.
à Make sure the source and destination database users have the same roles and privileges.
à Make sure the Agile tablespaces in the destination and source databases match. Also, the
destination database should have unlimited tablespace.
à Verify that the tablespace size in the destination database is more than that of the source
database.
viii Database Upgrade Guide
Chapter 1
Understanding the Property Files
This chapter includes the following:
The aut.properties File......................................................................................................................................... 9
Defining the aut.properties Parameters...............................................................................................................9
The psupgrade.properties File............................................................................................................................. 14
Defining the psupgrade.properties Parameters................................................................................................... 15
The peupgrade.properties File.............................................................................................................................17
Defining the peupgrade.properties Parameters................................................................................................... 18
AUT uses property files to supply the input information to the database scripts. If you are not
upgrading a Product Cost Management 8.5 or Program Execution 8.5 database, you should only
complete the aut.properties file. If you are also upgrading a Product Cost Management 8.5 or
Program Execution 8.5 database, you should complete either the psupgrade.properties file or the
peupgrade.properties file, in addition to the aut.properties file.
The aut.properties File
AUT uses property files to supply the input information to the database scripts. If you are not
upgrading a Product Cost Management 8.5 or Program Execution 8.5 database, you should only
complete the aut.properties file. If you are also upgrading a Product Cost Management 8.5 or
Program Execution 8.5 database, you should complete either the psupgrade.properties file or the
peupgrade.properties file, in addition to the aut.properties file.
The aut.properties file, located in the $AUT_HOME\config directory, contains user-supplied
information on the source and destination databases. The source database parameters contain
information about the database to be upgraded. The destination database parameters contain
information about the user and host details of the database where the source database is being
upgraded. These parameters are used by the database scripts during the upgrade process.
Defining the aut.properties Parameters
The aut.properties is divided into the following sections:
à Source Details—the database you are upgrading
à Destination Details—the Agile PLM 9.2.2.1 database
à Averify Details—how averify errors are handled
à Import Warnings—how import warnings are handled
à 8.5 Only Details—which Agile 8.5 components you are upgrading
Each parameter is followed by an example displaying the correct format for each parameter
value.You must complete the source and destination details in order for the AUT to proceed with
the upgrade.
Database Upgrade Guide Page - 9
Agile 9.2.2.1
Source Details
Parameter Value definition
src.jdbc.url The JDBC connection string to the source database. The format is
jdbc:oracle:thin@
SOURCE_DB_HOST_NAME is the hostname of the source database, PORT is the listener port of
the source database (1521 is the default), and
src.jdbc.driver The database driver of the source database. The default is oracle.jdbc.driver.OracleDriver.
src.db.user The database user of the source database.
src.db.password The encrypted database password of the source database. Use the encryptpwd utility located in the
$AUT_HOME\bin directory to encrypt the password.
src.tns.name The TNS entry name in the Oracle Home of the source database.
src.oracle.home The Oracle Home of the source database. This value can be a mapped drive on the system where
the AUT is run which points to the shared folder on the host machine.
<SOURCE_DB_HOST_NAME>:<PORT>:<SID> where
SID is the database instance.
Note Use either double back slashes or a forward slash for
directory separation, for example, d:/oracle/ora92.
src.db.backup Back up or do not back up the database into a dump file. If true, then the source database is
backed up by exporting the database user into a dump file located at
$AUT_HOME\workingdir\AgileSrcDbBackup.dmp. If you are also upgrading a Product Cost
Management or Program Execution database, their respective dump files are named
Agile85pcmBACKUP.dmp and Agile85peBACKUP.dmp.
Page - 10 Database Upgrade Guide
Chapter 1
Destination Details
Parameter Value Definition
dest.jdbc.url The JDBC connection string to the destination database. The format is
jdbc:oracle:thin@
DESTINATION_DB_HOST_NAME is the hostname of the destination database, PORT is the
listener port of the destination database (1521 is the default), and
dest.jdbc.driver The database driver of the destination database. The default is oracle.jdbc.driver.OracleDriver.
dest.db.user The database user of the destination database.
dest.db.password The encrypted database password of the destination database. Use the encryptpwd utility located
in the $AUT_HOME\bin directory to encrypt the password.
dest.db.system.user The user of the system account on the destination database instance.
dest.db.system.password The encrypted password of the system user account on the destination database instance.
<DESTINATION_DB_HOST_NAME>:<PORT>:<SID> where
SID is the database instance.
dest.tns.name The TNS entry name in the Oracle Home of the destination database.
dest.oracle.home The Oracle Home of the destination database. This value can be a mapped drive on the system
where the AUT is run which points to the shared folder on the host machine.
Note Use either double back slashes or a forward slash for
directory separation, for example, d:/oracle/ora92.
sourceEqualsDest Determines if the source database is upgraded to the destination database with or without the
database user. If true, then the source database is upgraded without exporting and importing the
database user to the destination database. This means that the same user is upgraded instead of
upgrading to a different user on the destination database. This does NOT apply if your source and
destination databases are on different versions of the Oracle database. Also, when this value is set
to true, the AUT does not import and export unless a value for the src.db.backup parameter is
specified.
If this parameter is set to true, the following destination database details are not required:
•dest.jdbc.url
•dest.jdbc.driver
•dest.db.user
•dest.db.password
•dest.tns.name
Page - 11
Agile 9.2.2.1
Averify Details
Parameter Value Definition
averify.error.count Specifies the number of averify errors to occurs before the AUT stops the upgrade.
Specify -1 to ignore the errors and continue with the upgrade.
Import Warnings
Parameter Value Definition
ignore.imp.warnings Decide if the AUT ignores the import warnings or not while importing the database. If set to true, the
warnings are ignored.
AUT will stop if there is a critical error.
File Manager Internal Locator Details
Parameter Value Definition
file.manager.internal.locator Specifies the location of the file manager. The content_url column in files table is upgraded with an
encrypted URL based on the file type. This URL is used after upgrading for full text search
configuration. The format is http://
<FILE_MANAGER_HOST>:<PORT>/Filemgr/AttachmentServlet.
Destination Version
Parameter Value Definition
dest.version The version of the upgraded Agile PLM database. The default value is 9.2.2.1.
Page - 12 Database Upgrade Guide
Chapter 1
Language Details
Parameter Value Definition
i18n.lang Specifies the language of the operating system where the database is located. The values are
English, Japanese, Chinese, or French.
8.5 Only Details
Parameter Value Definit ion
isPC Each of the parameters must have a value if the source database is either 8.5 or 8.5 SPx only.
Set the parameter to true for the components that are installed on the source database and to
false for the components that are not installed.
isPSI
isPCM
isPE
username.migration.action Determine how you want the Agile users in the source database to appear in the destination
database. The value is set as the number of one of the following choices:
1. FIRSTNAME LASTNAME
2. LASTNAME FIRSTNAME
3. FIRSTNAME, LASTNAME
4. LASTNAME, FIRSTNAME
5. None of the above. FIRSTNAME will be migrated to LASTNAME.
If your database contains mixed cases, choose the option that reflects the format for most of your
users. The remaining users must be migrated manually after the upgrade.
gmt.timezone.difference The GMT time zone of your database. The database and Agile application server must be on the
same GMT time zone. The GMT time zone format is six characters, (+ or -)hh:mm, for example,
GMT would be -00:00.
Note If you are upgrading from Product Cost Management or Program Execution
only, then leave all Source detail properties blank in the aut.properties file.
Page - 13
Agile 9.2.2.1
The psupgrade.properties File
The psupgrade.properties file, located in the $AUT_HOME\config directory, contains user-supplied
information on the existing Product Cost Management database. During an upgrade of a Product
Cost Management database, the data is merged into the Agile PLM database. The parameters in
the file are used by the database scripts during the upgrade process to determine how the data is
migrated. This file should be completed in addition to the aut.properties file if you are migrating a
Product Cost Management database.
It is recommended to perform a test upgrade of the Product Cost Management database with
reports to evaluate the database and make any necessary corrections before performing the actual
data migration.
Page - 14 Database Upgrade Guide
Chapter 1
Defining the psupgrade.properties Parameters
Parameter Value Definition
pcm.jdbc.url The JDBC connection string to the Product Cost Management database. The format is
jdbc:oracle:thin@
SOURCE_DB_HOST_NAME is the hostname of the Product Cost Management database,
PORT is the listener port of the database (1521 is the default), and SID is the database
instance.
pcm.jdbc.driver The database driver of the Product Cost Management database. The default is
oracle.jdbc.driver.OracleDriver.
pcm.db.user The database user of the Product Cost Management database.
pcm.db.password The encrypted database password of the Product Cost Management database. Use the
encryptpwd utility located in the $AUT_HOME\bin directory to encrypt the password.
pcm.tns.name The TNS entry name in the Oracle Home of the source database.
<SOURCE_DB_HOST_NAME>:<PORT>:<SID> where
pcm.oracle.home The Oracle Home of the Product Cost Management 8.5 database.
Note Use either double back slashes or a forward slash for
directory separation, for example, d:/oracle/ora92.
generate.pcm.reports Set this parameter to true to generate a report about the data contained in the Product Cost
Management database before migration. The report will display data matching the following
rules:
1. Update firstname, lastname, and user organization for the matched
user login ID, if you set the update.matched.user.data parameter to true.
2. Migrate the Product Cost Management Items not found in Agile 8.5,
if you set the migrate.pcm.items.not.found.in.pc parameter to true.
3. Convert all manufacturer names to uppercase, if you set the
convert.mfrName.to.upperCase parameter to true.
4. Update all Agile 8.5 manufacturer data with the Product Cost
Management manufacturer data for the same manufacturer, if you set the
update.matched.mfr.data parameter to true.
5. Migrate Product Cost Management manufacturer parts not found in
Agile 8.5, if you set the migrate.pcm.mpns.not.found.in.pc to true.
If you set this parameter to false, migration will proceed without a report being generated.
If you choose to have a report generated, AUT will stop. After viewing the report and you
are satisfied with the results, change this parameter to false and run AUT again.
report.folder.location Location where you want the report to be generated.
update.matched.user.data If set to true, the firstname, lastname, and user organization are updated based on
matching users in the Agile 8.5 database.
Page - 15
Agile 9.2.2.1
Parameter Value Definition
migrate.pcm.items.not.found.in.pc If set to true, all Product Cost Management items not found in the Agile 8.5 database are
migrated.
convert.mfrName.to.upperCase If set to true, all manufacturer names are converted to uppercase.
migrate.pcm.mpns.not.found.in.pc If set to true, all Product Cost Management manufacturer parts not found in the Agile 8.5
database are migrated.
update.matched.mfr.data If set to true, all Agile 8.5 manufacturer data is updated with the Product Cost Management
data for the same manufacturer.
expiry.date.to.remove.prices Removes prices based on the entered expiration date. All prices previous to the entered
date are removed. If no date is entered, the prices are checked against the current date.
Enter a date in the following format: MM-DD-YYYY.
migrate.suppliers.with.autonumber If set to true, all suppliers are given autonumbers. If set to false, you must provide a
supplier code map file and add its location to the supplier.code.crossref.filepath parameter.
The file should be in the form of comma separated text file with the heading in the first line
as
supplierName,supplierCode
Agile,agil
EMS1,emsCode1
supplier.code.crossref.filepath Location of the supplier code map file.
iFS Details*
copy.files If set to true, the file attachments are copied.
src.ifs.folder The mapped drive location of the local iFS files folder.
src.ifs.schema The database user of the Product Cost Management database.
dest.ifs.folder The path where the upgraded file vault will be located.
dest.ifs.schema The database user of the destination database.
*After the database is upgraded, you will need to run the IFS Reorganization tool to restructure the file vault to
match the current format. For more information on this tool, see the Installation Guide.
Page - 16 Database Upgrade Guide
Chapter 1
The peupgrade.properties File
The peupgrade.properties file, located in the $AUT_HOME\config directory, contains use r-supplied
information on the existing Program Execution database. During an upgrade of a Program
Execution database, the data is merged into the Agile PLM database. The parameters in the file are
used by the database scripts during the upgrade process to determine how the data is migrated.
This file should be completed in addition to the aut.properties file if you are migrating a Program
Execution database.
It is recommended to perform a test upgrade of the Program Execution database with reports to
evaluate the database and make any necessary corrections before performing the actual data
migration.
Page - 17
Agile 9.2.2.1
Defining the peupgrade.properties Parameters
Parameter Value Definition
pe.jdbc.url The JDBC connection string to the Program Execution database. The format is
jdbc:oracle:thin@
SOURCE_DB_HOST_NAME is the hostname of the Program Execution database, PORT is the
listener port of the database (1521 is the default), and
pe.jdbc.driver The database driver of the Program Execution database. The default is
oracle.jdbc.driver.OracleDriver.
pe.db.user The database user of the Program Execution database.
pe.db.password The encrypted database password of the Program Execution database. Use the encryptpwd utility
located in the $AUT_HOME\bin directory to encrypt the password.
pe.tns.name The TNS entry name in the Oracle Home of the Program Execution database.
<SOURCE_DB_HOST_NAME>:<PORT>:<SID> where
SID is the database instance.
pe.oracle.home The Oracle Home of the Program Execution database.
Note Use either double back slashes or a forward slash for
directory separation, for example, d:/oracle/ora92.
create.del.subclass If set to true, the Deliverables subclass will be created after upgrade for data currently in that
subclass. If set to false (default, the data currently in the Deliverables subclass will be migrated to
the Tasks subclass after upgrade.
generate.pe.reports If set to true, a report on any circular dependencies in the data is generated and no data is
migrated. If set to false, no report is generated and the data is migrated.
If you choose to have a report generated, AUT will stop. After viewing the report and correcting the
dependencies, change this parameter to false and run AUT again.
report.folder.location Location where you want the report to be generated.
pe.weekend.days Comma-separated numbers representing the weekend days in a week. The default days are
Saturday and Sunday. Format: 1,7
currency.description Base currency. The default is US Dollars (USD).
iFS Details*
copy.files If set to true, the file attachments are copied.
src.ifs.folder The mapped drive location of the local iFS files folder.
src.ifs.schema The database user of the Program Execution database.
dest.ifs.folder The path where the upgraded file vault will be located.
dest.ifs.schema The database user of the destination database.
*After the database is upgraded, you will need to run the IFS Reorganization tool to restructure the file vault
to match the current format. For more information on this tool, see the Installation Guide.
Page - 18 Database Upgrade Guide
Chapter 1
Page - 19
Chapter 2
Upgrading the Agile Database to Agile PLM
9.2.2.1
This chapter includes the following:
Running the Automated Upgrade Tool................................................................................................................21
Checking the Status of the Upgrade.................................................................................................................... 21
Troubleshooting the Upgrade..............................................................................................................................21
Running the Automated Upgrade Tool
Before running the AUT:
à Make sure you have completed all of the pre-requisites listed in “Preparing for the Upgrade”.
à On Solaris, 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.
à Edit the property file with the correct values for your upgrade. See “Understanding the
Property Files
To run the AUT:
Go to the $AUT_HOME\bin directory and run the aut.bat (Windows) or aut.sh (Solaris) script.
(on page 9)” for more information
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.
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:
y 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.
Database Upgrade Guide Page - 21
Agile 9.2.2.1
y Verify that the service name exists in one of the tnsnames.ora files. Add the service name,
if necessary.
y 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:
y Indexes may be missing on the source database. If this is the case, averify may be running
slowly.
y The import may be taking a longer time because it’s a large database.
Page - 22 Database Upgrade Guide
Chapter 2
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:
y The SGA requires more space than that was allocated for it.
y 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.
Page - 23
Agile 9.2.2.1
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 re duces the
time.
Page - 24 Database Upgrade Guide
Chapter 3
Configuring the Agile PLM 9.2.2 Database
This chapter includes the following:
Reorganizing the Database.................................................................................................................................25
Running Full Text Search Scripts ........................................................................................................................ 26
Configuring the Agile SDK................................................................................................................................... 26
Configuring PG&C...............................................................................................................................................27
Configuring PPM..................................................................................................................................................27
Upgrading from Agile PLM 9.2.1.1.......................................................................................................................28
After the database has been configured and all averify errors have been fixed, you must run
additional database scripts to reorganize the database and to enable Full Text Search in the Agile
PLM 9.2.2.1 database.
Reorganizing the Database
Important The Agile PLM 9.2.2.1 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 still needs
to 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.
To reorganize the upgraded schema:
1. On the database server, change to the following directory:
(Windows)
(UNIX) $ORACLE_BASE/admin/$ORACLE_SID/create
2. 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.
3. Run the
schema.
4. Run the agile9imp script to import the upgraded Agile 9 schema, including setup of CBO and
FTS.
\oracle\admin\agile9\create
recreateagile script to drop the existing account and recreate the agile account and
5. Rename the existing agile9exp.dmp file to agile9exp_upgrade9221.dmp for backup. Also
rename the agile9exp.log file to agile9exp_upgrade9221.log.
Database Upgrade Guide Page - 25
Agile 9.2.2.1
6. Run the
Agile9exp script again.
The Agile9exp.dmp file created can be used as a backup of the system after the Agile PLM
9.2.2.1 database reorganization.
7. Rename the agile9exp.dmp file to agile9exp_reorg9221.dmp for backup. Also rename the
agile9exp.log file to agile9exp_reorg9221.log.
8. Change to the Agile9Tmp directory:
(Windows) cd Agile9Tmp
(UNIX) $ORACLE_BASE/admin/$ORACLE_SID/create
9. Start SQL*Plus from a command line and log in as
agile/tartan or agile/tartan@agile9.
10. Run the agile9_check.sql file to validate the schema integrity and confirm integrity of the
database reorganization:
SQL> @agile9_check.sql
Important If errors are displayed, contact the Agile Support Center. All errors must be fixed before
proceeding to the next step.
11. Start the Oracle listener.
The following steps should only be performed after you have upgraded the Agile PLM application:
12. Start the Agile application server.
13. Shutdown the database.
14. Perform a cold backup of all the database-related files.
15. Restart the database, listener, and application server.
Running Full Text Search Scripts
The Full Text Search scripts are located in $AUT_HOME\scripts\oracle\utilities\script_tools. Using
SQL*Plus from a command line, run the following scripts on your upgraded database:
à agile922_fts_prefs.sql on the CTXSYS user
à agile922_fts.sql on the upgraded Agile schema
Configuring the Agile SDK
If you are using the Agile SDK, delete the AgileSDK.cache directory from your client machines after
you have upgraded the Agile PLM database and application server. The
found under the
temp directory (%temp%AgileSDK.cache).
AgileSDK.cache directory is
Page - 26 Database Upgrade Guide
Chapter 3
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 meet ALL of the following requirements:
à Upgraded your database from Agile PLM 9.2 to Agile PLM 9.2.2.1.
à Imported the JGPSSI substances and substance groups.
à In Agile PLM 9.2.2.1, you want to use IPC declarations with IPC substances and substance
groups. Note that the IPC list of substances and substance groups is slightly different than the
JGP list of substances and substance groups.
Important Contact your Agile Solutions Delivery representative 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.
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 obj ects.
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 FRO M 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>';
Page - 27
Agile 9.2.2.1
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 is 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;
Running the PPM Post Upgrade Utility
After you have upgraded your database and installed the Agile PLM 9.2.2.1 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
specific application server.
Agile PLM Installation Guide for your
Upgrading from Agile PLM 9.2.1.1
If you created tasks, gates, or phases in Agile PLM 9.2.1.1, the icons do not appear when viewing
baselines after upgrading to Agile PLM 9.2.2.1.
Page - 28 Database Upgrade Guide
Appendix A
AUT Maintenance
Before you upgrade your database, you should always make sure you are using the latest version
of the AUT. Changes to the scripts, like averify, may occur after the initial version has been
released. So, to ensure your database is correctly upgraded, always check the Agile Support
website for updates.
Updating the AUT
If updates are needed to the AUT, you can always find the latest version on the Agile Support Web
(http://www.agile.com/support). Download the AUT.zip file and extract the file contents into the
Site
same directory where you originally extracted the files.
Running Averify
Averify performs an integrity check against the Agile database and reports on specific errors.
Averify includes a check for missing indexes and inconsistent data stored in the various database
tables. Averify only reports errors, and does not attempt to fix them or modify your database.
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.
When you download an updated version of AUT, the latest Averify scripts are also included. The
latest Averify scripts are also available separately on the
http://www.agile.com/support). You should run Averify periodically as part of your regular database
(
maintenance procedures.
To run Averify:
16. Make sure you have downloaded the latest version of AUT from the Agile Support Web Site.
17. Make sure you have backed up your database.
18. Go to the $AUT_HOME\scripts\oracle\utilities\averify directory.
19. Using SQL*Plus from a command line, run the oracle_averify9x.sql script.
SQL> @oracle_averify9x.sql
Agile Support Web Site
Important If errors are generated in the <name of file> located in the
$AUT_HOME\scripts\oracle\utilities\averify directory, contact Agile Support.
29 Database Upgrade Guide
Loading...