Oracle
Primavera P6 Professional
Manual Upgrade Guide (Microsoft SQL Server Database) for
On-Premises
December 2023
Version 23
Oracle Primavera P6 Professional Manual Upgrade Guide (Microsoft SQL Server Database) for
On-Premises
Copyright © 1999, 2023, Oracle and/or its affiliates.
Primary Author: Oracle Corporation
2
Contents
About This Guide............................................................................................................................... 5
Convert Methodologies to Projects ................................................................................................... 5
Risks Migration ................................................................................................................................. 6
Running the Microsoft SQL Server Database Upgrade Scripts for the P6 Professional Database .. 7
Private Database Credentials for P6 Professional ........................................................................... 9
Resetting Private Database Passwords ................................................................................................ 9
3
About This Guide
Scope
This guide describes how to manually upgrade your P6 Professional database to Version 23
from release 7.0 or later.
Complete these processes in the following order:
Back up the existing P6 Professional database.
If you are upgrading from P6 Professional 7.0, migrate all your methodologies. Use Project
Architect in the Project Management module (R7.0) to convert the data from a methodology
to a project. See Convert Methodologies to Projects (on page 5).
Run the appropriate scripts to upgrade the P6 Professional database structure. Oracle
recommends that you use SQL Plus to run scripts in this guide.
To configure an Oracle database server for SSL, see the Advanced Security Administrator's
Guide included with the Oracle Database Server Documentation for configuring the Oracle
Server and Oracle Client(s) for SSL.
Audience
Database administrators should use this guide.
Using This Guide
This guide assumes you can perform common database administration procedures and have
experience using the command line.
Convert Methodologies to Projects
If you are upgrading from P6 Professional 7.0, you must migrate Methodology Management 7.0
data to P6 Professional Version 23 projects. Use Project Architect in the Project Management
module (in P6 Professional 7.0) to convert the data from a methodology to a project.
Note: You can create only one project at a time. If you want all of your
Methodology Management data moved to projects, contact Oracle
Consulting to automate the process.
To convert Methodology Management data to a project:
1) Create a new EPS node in Project Management where you can store all your Methodology
Management projects.
2) Use Project Architect in Project Management to create projects from Methodology
Management data. For more information on using Project Architect, see the Oracle
Primavera P6 Project Management Reference Manual for release 7.0
After you have converted all your Methodology Management data to projects, you can upgrade
P6 Professional.
5
Manual Upgrade Guide (Microsoft SQL Server Database) for On-Premises
Risks Migration
If you are upgrading from P6 Professional 7.0, all risk data fields are migrated. The following
table illustrates the risks data field mapping when upgrading from P6 Professional database (7.0
SP3) to the current version.
Note: Some fields migrate to text fields instead new fields because
certain fields no longer correspond. The new text fields are noted below.
Risks Fields Migration Table
Name P6 Professional 7.0 SP3 P6 Professional Version 23 Risks
Risks Field Field
Risk ID risk_id risk_id
risk_id risk_code (PROJRISK appended with
risk_id)
Risk Name risk_name risk_name
Risk risk_descr risk_desc
Description
Risk Status status_code status_code
Open= Open; Closed=Managed
(closed)
Risk Category risk_type_id risk_type_id
ID
Risk Control risk_control cause (with 'Risk Control' heading)
Risk UDFs table_name table_name
Applies to WBS wbs_id cause (with 'Applies to WBS' <WBS
name> heading)
Applies to rsrc_id rsrc_id
Resource
Responsible obs_id cause (with 'Responsible Manager'
Manager <OBS name> heading)
Priority priority_type cause (with 'Priority' <priority_type>
heading)
Project ID proj_id proj_id
Date Identified add_date add_date
Impact Date impact_date cause (with 'Impact Date' <add_date in
mmm-dd-yyyy format > heading)
6
Running the Microsoft SQL Server Database Upgrade Scripts for the P6 Professional Database
Name P6 Professional 7.0 SP3 P6 Professional Version 23 Risks
Risks Field Field
Probability prbly_pct cause (with 'Probability' <prbly_pct>
heading)
Impact - Labor impact_work_qty cause (with 'Impact - Labor Units'
Units <impact_work_qty> - 2 decimals
heading)
Impact - impact_equip_qty cause (with 'Impact - Nonlabor Units'
Nonlabor Units <impact_equip_qty> - 2 decimals
heading)
Impact - impact_mat_qty cause (with 'Impact - Material Units'
Material Units <impact_mat_qty> - 2 decimals
heading)
Impact - Impact_expense_cost cause (with 'Impact - Expenses'
Expenses <impact_expense_cost> - 2 decimals
heading)
Risk Control risk_control cause (with 'Risk Control'<risk_control>
heading)
Risk Category risk_type risk_type
Risk Category seq_num seq_num
Sequence ID
Running the Microsoft SQL Server Database
Upgrade Scripts for the P6 Professional
Database
Note: The scripts described in this procedure are on the P6 Professional
physical media or download.
To upgrade your database:
1) Log in to Microsoft SQL Server Management Studio as the sa user.
2) Go to \database\scripts\common and run the ss_disable_triggers.sql script.
3) Go to \database\scripts\upgrade\PPM_<release_level> and run one of the following
scripts:
SSPPM_p70sp1.sql, if upgrading from P6 Professional 7.0 and 7.0 Service Pack 1
SSPPM_p70sp3.sql, if upgrading from P6 Professional 7.0 Service Pack 3
SSPPM_p70sp5.sql, if upgrading from P6 Professional 7.0 Service Pack 5
7
Manual Upgrade Guide (Microsoft SQL Server Database) for On-Premises
SSPPM_p81.sql, if upgrading from P6 Professional 8.1
SSPPM_p81sp1.sql, if upgrading from P6 Professional 8.1 Patch Set 1
SSPPM_p82.sql, if upgrading from P6 Professional 8.2
SSPPM_p82sp1.sql, if upgrading from P6 Professional 8.2 Patch Set 1
SSPPM_p82sp3.sql, if upgrading from P6 Professional 8.2 Patch Set 3
SSPPM_p83.sql, if upgrading from P6 Professional 8.3
SSPPM_p83sp2.sql, if upgrading from P6 Professional 8.3 Patch Set 2
SSPPM_p83sp3.sql, if upgrading from P6 Professional 8.3 Patch Set 3
SSPPM_p84.sql, if upgrading from P6 Professional 8.4
SSPPM_p841.sql, if upgrading from P6 Professional 8.4 Patch Set 1
SSPPM_p843.sql, if upgrading from P6 Professional 8.4 Patch Set 3
SSPPM_P151.sql, if upgrading from P6 Professional 15 R1
SSPPM_P152.sql, if upgrading from P6 Professional 15 R2
SSPPM_P161.sql, if upgrading from P6 Professional 16 R1
SSPPM_P162.sql, if upgrading from P6 Professional 16 R2
4) If you are using version 7.0 or later, skip to the next step.
If you are using version 6.21 or earlier, go to \scripts\common and run the
ss_update_usereng.sql script to grant all users with P6 Professional module access rights
to the Projects section in P6.
5) Go to \database\scripts\common and run the ss_post_upgrade.sql script.
6) Go to \database\scripts\source\PPM_<release_level> and run the ssppm_grants.sql
script.
Note: If you changed the default user names, you must update the
privuser and pubuser names in this script with your custom user names.
7) Go to \database\scripts\source\PPM_<release_level> and run the ssppm_synonyms.sql
script.
Caution: The ssppm_synonyms.sql script contains a table called
PUBUSER. Do not replace it if you have to change the pubuser user
name.
Note: If you changed the default user names, you must update the
privuser and pubuser names in this script with your custom user names.
8) Go to \database\scripts\source\PPM_<release_level> and run the ssppm_src.plb script.
8
Private Database Credentials for P6 Professional
Notes:
If you changed the default privuser name, you must update the
ssppm_src.plb script with your custom privuser name.
Do not use special characters in the database name, privileged user,
or public user name, for example: { } [ ] : ; < > , . ? ! @ # $ % ^ & * ( )
-_|/\~`
9) Go to \database\scripts\source\PPM_<release_level> and run the
ssppm_init_bgjobs.sql script.
10) Go to \database\scripts\install\PPM_<release_level> and run the
ssppm_database_version.sql script.
11) Go to \database\scripts\install\PPM_<release_level> and run the ssppm_querylib.sql
script.
12) Go to \database and run [Link] (on Windows) or [Link] (on
Unix) to upgrade the encryption of privilege user passwords. See Private Database
Credentials for P6 Professional (on page 9) for more information.
Private Database Credentials for P6 Professional
The P6 server and P6 Professional components obtain their run-time database connection
credentials from a credential configuration table in the P6 Professional database. The P6
run-time database credentials (known as privuser or P6 private database login) are stored in an
encrypted format in this special P6 configuration table. Any time that you change or rotate the
privuser password credentials in your Oracle, Oracle Autonomous Database, or MS SQL Server
database, you must re-synchronize the stored credentials in the P6 credential table by using the
Database Login tool.
Because encryption algorithms are often enhanced in newer releases, Oracle highly
recommends that you reset these stored privuser credentials when you perform a major version
upgrade of P6 Professional. By resetting the stored credentials, the new encryption algorithm
can be applied to other stored credentials (for example, pubuser) in the P6 Professional
credential table. For information about resetting private database passwords, see Resetting
Private Database Passwords (on page 9).
Note: This tool does not reset database user logins or passwords.
Administrators should use SQL Developer or other DBA consoles to set
or reset database user passwords.
Resetting Private Database Passwords
Password encryption algorithms are frequently improved in new releases of P6 Professional and
P6 Professional. You should reset private database passwords in order to use improved
password encryption algorithms.
To reset private database passwords to use the new encryption algorithm:
9
Manual Upgrade Guide (Microsoft SQL Server Database) for On-Premises
1) Go to P6 Professional <release_level>\database or P6 Professional
<release_level>\database.
2) Run [Link] (with Windows) [Link] (with UNIX or Linux).
3) In the Database Connection dialog box:
a. Select the database.
b. Type the user name and password of a privileged database user (for example, privuser).
This login should have administrative rights on the database.
c. Enter the connection details for the database.
For an Oracle Database, enter the host address, host port, and database/instance name
specific to your installation. The Port field displays the default port for the database type
you selected.
For an Oracle Autonomous Database, enter the location of the unzipped wallet file and
the service name.
For a Microsoft SQL Server Database, enter the host address, host port, and
database/instance name specific to your installation. The Port field displays the default
port for the database type you selected.
d. Click Next.
4) In the Private Database Logins dialog box:
a. Select the private database user name that you wish to reset.
b. Highlight the password and change it (or re-enter the existing password).
c. Click Update Password.
d. Click Save.
e. Click OK.
10