How to Easily Create a Demo Database from a PUM Database

The topic of this article may be misleading and may make sense only to those who have found a need to create a Demo from a PUM environment. Hence, for the benefit of every one else, let elaborate first.

Gone are the days when you used to create a PeopleSoft Demo database by downloading software from the OTN. You now create PUM database by downloading PUM DPK images by downloading software from MOS.

During the deployment process, if you choose the option PUM, it creates a PUM database whereas if you use the FRESH option, it creates a Demo database.

A PUM database contains demo and PUM data, so why would you need to create a demo database from PUM database?

When you create an environment using PUM DPKs - everything gets installed on the same server. This includes a database, PIA, appserver, batch server etc. So, it is more of an out of the box solution and because of that, it may sometimes be difficult to support and get support because it has not have been built according to your organization's specs like having different severs for database, application server, database etc.

Another common situation could be that you may need a particular PUM database on a different PTools patch release than what is was delivered at. So, you can export the PUM database to another database and subsequently do things like apply a PTools patch because you don't want to play around with a PUM database.

PUM Database vs Demo Database

The PUM and Demo databases are different.

PUM database actually contains Demo + PUM data.

The main purpose of a PUM environment is to provide maintenance and it contains objects in several different states in order to be able to apply the maintenance.

The main purpose of a Demo environment is to provide a vanilla environment with maintenance up to what is in production, that you can run compares against and do testing. PUM is neither vanilla nor "compare-able".

Most customers maintain a demo in order to have a vanilla environment.

Oracle recommends that you use the PUM in order to simply demonstrate the functionality, but it should not be used as environment for any kind of compares for any object types.

You will definitely need a PUM environment in order to obtain and apply the image maintenance to be applied to the target.

How to Easily Create a Demo Database from a PUM Database.

There are many ways to do it and each one would have advantages or disadvantages as per your situation. I am going to cover one specific situation wherein you need to export data from an existing PUM database and import it into another database (call it Demo). 

Note: Not all the steps may apply to your situation, hence, make sure you add/remove the steps as per your situation.

1. Run the audit reports, DDDAudit and SYSAudit to ensure that the database is consistent.

2. Login to the original (for example the PUM database) using Datamover in non-bootstrap mode and export the entire database using the below commands.

    SET OUTPUT MIGRATE.DAT;
    EXPORT *;    
  
This step will export your entire database into one file. Usually PUM databases are not excessively large and you should be able to export the entire database in a couple of couple of hours. If the database is excessively large, you can consider doing export by specifying the actual table names.

3. You will need a database to import the data in and it should be ready up to a point wherein you can connect to it via DataMover. If the database is not ready, you need to run the following scripts to get it to that state wherein you can connect to it via DataMover.

• CREATEDB.SQL
• UTLSPACE.SQL
• XXDDL.SQL
• DBOWNER.SQL
• PSROLES.SQL
• PSADMIN.SQL
• CONNECT.SQL

XX in XXDDL.SQL refers to the application. For example HC for HCM, EP for Finance etc.

4. Import the data exported earlier by using Datamover in boot strap mode.

SET INPUT MIGRATE.DAT
# Depending on your application version, uncomment and use one of three below mentioned lines
#SET ENABLED_DATATYPE 9.0;
#SET ENABLED_DATATYPE 9.1;
#SET ENABLED_DATATYPE 9.2;
IMPORT *;

5. Run createvw.dms, which is present in <PS_HOME>/scripts.

6. Run the audit reports, DDDAudit and SYSAudit again to ensure that the database is consistent.

Note - You can also move data using other options as well like datapump for Oracle databases instead of having to use DataMover.