This blog provides a guide to refresh ancillary (e.g. QA, Dev or Test) Agile PLM instances from the production Agile PLM instance.

Note: text highlighted in yellow is EXTREMELY important

Procedure I, Agile 9.3.X Database Refresh Process

• 1.1  Retrieve the AES Encryption Key from the target system
• 1.2  Retrieve the encrypted passwords from the target system
• 1.3  Record target system settings
• 1.4  Create and export the .dmp file from the source system
• 1.5  Import the .dmp file to the target system
• 1.6  Restore the AES Encryption Key to the target system
• 1.7  Restore the encrypted passwords to the target system
• 1.8  Optional:  Copy the Agile File Repository Vault
• 1.9  Start the Agile system and restore configuration items

Procedure II, ZWS SQL Script for Managing Encrypted Passwords

• 2.1  Retrieve the AES Key and Passwords
• 2.2  Restore the Passwords After the Database Refresh

Procedure I, Agile 9.3.X Database Refresh Process

Note 1:  Refer to the following Oracle reference for more detailed information: 

Best Practices for Maintaining an Agile Product Lifecycle Management (PLM) System
(Doc ID 1522897.1)

Note 2:  Zero Wait State has created a simple SQL script to retrieve, save and restore the system’s AES Key and encrypted account passwords. This script replaces the steps highlighted in yellow in Procedure I.  Refer to Procedure II for the script process. Go to https://zerowait-state.sharefile.com/d-sec6eb5543a1468b9 to download the script.

1.1  Retrieve the AES Encryption Key from the target system

Retrieve the AES encryption key from the target system using the following simple SQL statement:

SELECT value
FROM propertytable
WHERE parentid=5004 AND propertyid=1008;

1.2  Retrieve the encrypted passwords from the target system

1. Retrieve the encrypted passwords (superadmin, ifsuser, admin and agileuser (if present)) from the target system using the following simple SQL statement:
   SELECT loginid, login_pwd
   FROM agileuser
   WHERE loginid IN ('admin','superadmin','ifsuser','agileuser');
   
   
2. Save the AES Encryption Key and the encrypted passwords. 

1.3  Record target system settings

1. Log in to the Java client using an administrator account.
2. Select the Admin tab.
3. Expand Server settings.
4. Double click Locations.
5. Record the Web Server (5a) and Java Client (5b) URLs.
6. Click the File Manager tab.
7. Double click on the current File Manager.
   
8. Record the File Manager URLs (File Manager (8a), File Manager Internal Locator (8b), Viewer Server (8c), Viewer Proxy (8d), and Viewer Content (8e).
   
9. Record the Vault Configuration.
   

1.4  Create and export the .dmp file from the source system

Note:  It is best to stop the source Agile server or at least have a very quiet system when doing an export.

1. Navigate to the ORACLE_BASE/admin/agile9/create/agile folder on the source system database server and run the agile9exp.bat script. (You will need to supply the configured agile@agile9 user password.)
2. If using Agile 9.3.4, it is suggested that you use the Oracle Data Pump facilities. Simply substitute agile9expdp.bat for agile9exp.bat.
3. Copy the newly created agile9exp.dmp file (agile9expdp.dmp if using Data Pump) to the target database server in the same location.
4. Stop the target Agile server before proceeding to the next step.

1.5  Import the .dmp file to the target system

Run the agile9imp.bat script (agile9impdp.bat if using Data Pump) to import the agile9exp.dmp file (agile9expdp.dmp if using Data Pump) created on the source database server in step 1 of section 1.3, Create and Export the .dmp file.

1.6  Restore the AES Encryption Key to the target system

Restore the system AES key using the following SQL statement: 

UPDATE propertytable
SET value='<INSERT YOUR ENCRYPTED PASSWORD HERE>'
WHERE parentid=5004 AND propertyid=1008;
commit;

1.7  Restore the encrypted passwords to the target system

Use the following sample SQL code to restore each encrypted user password that was saved:

UPDATE agileuser
SET login_pwd ='<INSERT YOUR ENCRYPTED PASSWORD HERE>'
WHERE loginid = 'admin';

UPDATE agileuser
SET login_pwd ='<INSERT YOUR ENCRYPTED PASSWORD HERE>'
WHERE loginid = 'superadmin';

UPDATE agileuser
SET login_pwd ='<INSERT YOUR ENCRYPTED PASSWORD HERE>'
WHERE loginid = 'ifsuser';

UPDATE agileuser
SET login_pwd ='<INSERT YOUR ENCRYPTED PASSWORD HERE>'
WHERE loginid = 'agileuser'; 

1.8  Optional:  Copy the Agile File Repository Vault

In some instances you may also wish to refresh the Agile File Vault Repository when refreshing the database.

1. Copy the Agile Vault from the source system to the target system. Maintain the same relative folder hierarchy.  For example, if the source system has the vault located at D:\AgileVault with the 000 folder(s) beneath that, you copy the 000 folder (and all folders under that) to the existing target vault location which may be E:\Agile\AgileVault for example. The documents should all be available from the Attachments tab as in the source system.
2. If you want to have Full Text Indexing available on your target system, refer to Oracle Document How To Update Content_url After Refreshing the Database With Agile9imp.bat? (Doc ID 1225398.1)

1.9  Start the Agile system and restore configuration items

1. Start the Agile application server.
2. Log in to the Java Client using an administrator account.
3. Restore the Agile configuration using the previously recorded values.
4. Restart the Agile system.

Procedure II, ZWS SQL Script for Managing Encrypted Passwords

Note:  Zero Wait State has created a simple SQL script to retrieve, save and restore the system’s AES Key and encrypted account passwords. Following is the associated process.  Go to http://zerowait-state.com/wp-content/uploads/2014/04/SavePasswords.zip to download the script.

2.1  Retrieve the AES Key and Passwords

1. Start the Agile application server.
   
   
2. Open a command window and navigate to ORACLE_BASE/admin/agile9/create/agile on the target server.
   
   
3. Start a SQLPLUS session: sqlplus /nolog<ENTER>
   
   
4. Connect to the database: conn agile@agile9/tartan<ENTER>
   
   
5. Run the script: @SavePasswords.sql<ENTER>
   Note:  Running sql will create the RestorePasswordsNew.sql script containing the AES Key and the encrypted passwords.
   
   
6. Quit SQLPLUS: quit<ENTER>

2.2  Restore the Passwords After the Database Refresh

1.  Open a command window and navigate to ORACLE_BASE/admin/agile9/create/agile on the target server.
   
   
2. Start a SQLPLUS session: sqlplus /nolog<ENTER>
   
   
3. Connect to the database: conn agile@agile9/tartan<ENTER>
   
   
4. Run the script: @RestorePasswordsNew.sql<ENTER>
   
   
5. Quit SQLPLUS: quit<ENTER>

Was this guide helpful? Please share your comments below. 

Zero Wait-State has products and services available to help you maintain your PLM system.

Click here to contact us.