Importing Database Contents

This section provides an overview of importing database contents and discusses how to:

  • Clear the NLS_LANG variable.

  • Import database contents using Oracle Data Pump.

  • Create an import parameter file for the Oracle Import utility.

  • Run the Oracle Import utility.

  • Rebuild PeopleSoft temporary tables.

Understanding Importing Database Contents Using the Oracle Data Pump Utility

Once the PeopleSoft tables have been pre-created by PeopleSoft Data Mover, you are ready to import the database export using Oracle Data Pump (impdp).

Important:

Ensure that you follow only the instructions for the pair of utilities that you have chosen to use.

Clearing the NLS_LANG Variable

Before running the Oracle import process, clear the NLS_LANG environment variable (Unix) or remove the NLS_LANG registry setting (Microsoft Windows). Doing so ensures that the Oracle import process reads the character set information from the export file and performs the appropriate conversion to the database’s selected Unicode character set.

Importing Database Contents Using Oracle Data Pump

In a command prompt, set your new Oracle SID

Set ORACLE_SID=<SID>

Sign into the Oracle SQL utility of your choice using the database administrator ID and password. Grant permissions to the username you will use to run the import.

 sqlplus / as sysdba

  1. Grant IMP_FULL_DATABASE to username;

  2. Quit SQLPlus.

On the command line, run the impdp command.

impdp username/password@SID dumpfile=filename schemas=OWNERID,ps content=data_only For instance: 

impdp system/manager@HR dumpfile=exp.dmp ⇒
schemas=SYSADM,ps content=data_only

Look for: Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed.

In case of errors, you can rerun and add the parameter trace=1FF0F00, to generate trace files. One common error is running out of space in a tablespace, due to setting it too small in the xxDDL.SQL scripts. To fix this, use a command like the following: 

alter database datafile 'filename.DBF'resize new_size;

Ensure that the import completes successfully with no errors before continuing.

Rebuilding PeopleSoft Temporary Tables

After you import the database, you must rebuild the temporary tables. To rebuild the temporary tables, sign into PeopleSoft Data Mover in user mode (non-bootstrap mode), and run the following command: 

CREATE_TEMP_TABLE *

Building Indexes

If you included the SET INDEXES OFF line of the Data Mover import, you need to build the indexes. Log into Application Designer and create a project called ALLRECORDS, and insert all record definitions into the project. Build the project to build the indexes defined for the records.