Copy Pilot Data to Prod Database#

Since the data within the PILOT database will continue to be changed by the implementation team, we need to periodically take the data to the PROD or CONV database to have the conversion process run smoothly.

The following steps are used to copy the copy the contents of the PILOT database to PROD before the conversion process can start. These steps will have to be done each time the PILOT database has changed, and these changes will affect the data being converted.

Step 1: Export#

Export the data from the PILOT database. The following command can be used to export the entire contents of the database:

EXP SYSTEM/<PASSWORD>@PILOT FULL=Y FILE=PILOT.DMP LOG=PILOT.LOG CONSISTENT=Y

Note: you will have to replace <PASSWORD> with the actual password of the SYSTEM user.

This will create two files in the current working directory of the system the command is run from. The first file, PILOT.DMP, will be used to load the data into the production database (PROD). The second file, PILOT.LOG is only used as a reference, if something should go wrong during the export process.

NOTE: if you are a “Quick Start” customer, the database is located on an Oracle server in our office. We will have to export the data and provide this on the FTP server for downloading.

Step 2: Drop Personality#

Drop the contents of the PROD database. The database must be empty before new information can be loaded into it with Oracle import utility, IMP. Provided with the application is an SQL script for removing all the objects from the Oracle database owned by the P2K user. To run this script, you must run SQL Plus, log on as P2K and issue the @DB_DROP command. This script can take some time to run, but should finish in about 20 to 30 minutes.

You may want to back up the database, prior to running this script. This SQL script will destroy all the application data in the database you are logged into. Make sure you have logged onto the correct database before running this script.

Step 3: Import#

Load the data from the PILOT database (or the Quick Start database) into PROD. The following DOS command can be used to import the data to the new database:

IMP SYSTEM/<PASSWORD>@PROD FILE=<DUMPFILE.DMP> LOG=PROD_IMP.LOG TOUSER=P2K FROMUSER=P2K

Note:

1. This command must be entered onto one line. It was been wrapped into two lines in this example
2. The <PASSWORD> must be replaced with the actual password of the SYSTEM user.
3. The <DUMPFILE.DMP> must be replaced with the actual file name (PILOT.DMP in step 1).

The import process will generate errors during the creation of some of the Oracle objects. Generally, these errors can be ignored. The next step will clean up the data that did not get created correctly. If the clean up process does not correct all the Oracle objects the log file, list above, will need to be sent to Support to identify the problem.

Step 4: Recompile#

Clean up import errors generated when loading the data. The IMP command loads all the objects into the new database in a very specific order. This order does not match the order we use to create a new database from scratch. This different load order attempts to create views and functions the reference functions that do not exist yet. To resolve these reference errors in the loaded objects, an SQL script has been provided that will recompile all the stored objects that are invalid. To run this script, you must load onto SQL Plus as P2K and issue the @DB_RECOMPILE command.

This SQL script makes no changes to the data in the database. It has only three functions, rebuild the public synonyms, re-issue grants to the P2K_USER role and to compile and invalid Oracle objects. At the end of the script you should see a line the states “NO ROWS SELECTED”. This indicated that there are no invalid objects remaining in the database.

If there are still errors at the end of this process forward a copy of the log file generated by this script to Support. A copy of the DMP file and the log generated by the export may also be needed.

Step 5: UNPILOT and Person Delete#

Delete the test employees from the production database. The conversion process will only insert new employees into the database. If any employee information remains in the database prior to running the conversion process, some of the employees may get bypassed.

Deleting the employee data is done in two steps. The first step is to delete the employees’ payroll information. This is done using a supplied SQL script named DB_UNPILOT.SQL. Again, this script must be run in SQL Plus as P2K. The second step is to delete the employees them self. Again an SQL script has been provided to do this called DB_PERSON_DELETE.SQL. This script is slightly different than the first, in that it will prompt for an employee to delete. Simply replying with a single percent sign % will delete all employees.

Step 6: Reconcile Security#

Since the users in the PILOT database may not exist as users in the PROD database, or may not exist with the same privileges, we need to recreate the security settings for the users. There is an SQL script that can be used to read the users information and recreate them at the Oracle database level. Start an SQL Plus session, log on as the P2K user and run the DB_UMRS.SQL script.

Step 7: Analyze the Database#

Since the information in the database has changed significantly, it is recommended that you analyze the data and compute the statistics. Again, use the supplied script DB_ANALYZE_SEL.SQL script to do this.

---

Notes #

Click to create a new notes page