1. Migrate a PeopleSoft database to Oracle Autonomous Database
  2. Configure Database Migration Prerequisites

Configure Database Migration Prerequisites

Configure the source and target database prerequisites for logical migration.

Configure OCI Object Storage Connectivity

Ensure the source and target databases can connect to OCI Object Storage on port 443 to access buckets for read and write while executing the database backup transmission.

Configure Database Backup Location

Create an OCI Object Storage bucket for database backup storage.

Shutdown PeopleSoft Application Gracefully

Before executing any database migration activity, as a best practice, shutdown the PeopleSoft application domain gracefully, including web server, elastic search domain, etc. You may also want to lock the environment as well as an additional step of precaution.

To shutdown the PeopleSoft application:

psadmin stop -d *all;

Configure Source Database Parameters

Configure the streams pool with the initialization parameter STREAMS_POOL_SIZE.

For offline logical migrations, for optimal Data Pump performance, it is recommended that set STREAMS_POOL_SIZE to a minimum of 256MB-350MB, to have an initial pool allocated, otherwise you may see a significant delay during start up.

  1. Run the following commands:
    SQL> alter system set streams_pool_size=512M scope=spfile sid='*';
SQL> shutdown immediate;
SQL> startup open
    Restart the database as a follow up activity.
  2. Ensure the system time of the Zero Downtime Migration service host and source database server should be in sync with your Oracle Cloud Infrastructure target.
  3. Offline Migration requires DATAPUMP_EXP_FULL_DATABASE and DATAPUMP_IMP_FULL_DATABASE role for Data Pump. Ensure whether privileged application roles should be assigned to the processes comprising the migration job.

Configure Database Roles and Privileges

In Oracle Autonomous Database, SELECT is no longer allowed on system objects.

All SELECT grants on system objects will need to be replaced with READ grants. See the My Oracle Support document ID 1911151.1 in Explore More for details.

  1. Revoke SELECT privileges from the source database:
    SQL> alter session set container=hr9246;
SQL> REVOKE SELECT ON SYS.V_$IM_COLUMN_LEVEL from PSADMIN;
SQL> REVOKE SELECT ON SYS.V_$IM_USER_SEGMENTS from PSADMIN;
SQL> REVOKE SELECT ON SYS.V_$MYSTAT from PSADMIN;
SQL> REVOKE SELECT ON USER_AUDIT_POLICIES from PSADMIN;
SQL> REVOKE SELECT ON DBA_AUDIT_POLICY_COLUMNS from PSADMIN;
  2. Grant READ privileges at the source database:
    SQL> GRANT READ ON SYS.V_$IM_COLUMN_LEVEL to PSADMIN;
SQL> GRANT READ ON SYS.V_$IM_USER_SEGMENTS to PSADMIN;
SQL> GRANT READ ON SYS.V_$MYSTAT to PSADMIN;
SQL> GRANT READ ON USER_AUDIT_POLICIES to PSADMIN;
SQL> GRANT READ ON DBA_AUDIT_POLICY_COLUMNS to PSADMIN;

Configure Target Databases Parameters

PeopleSoft Unicode databases require NLS_LENGTH_SEMANTICS=CHAR.

  1. Review the My Oracle Support articles (Doc ID 1986664.1 and 2626966.1).
  2. Update the NLS_LENGTH_SEMANTICS parameter as recommended by PeopleSoft database.

Create Tablespace, Roles, and PeopleSoft Users

Since PeopleSoft users for database, i.e. PS, PEOPLE, and SYSADM, have interdependencies, it is recommended to build users and their prerequisites (such as default tablespace, roles, and privileges following PeopleSoft delivered scripts).

Those scripts need update to fit in the use case for Oracle Autonomous Database on Dedicated Exadata Infrastructure. For example:

  1. Instead of SYSTEM user, Oracle Autonomous Database on Dedicated Exadata Infrastructure will have ADMIN user.
  2. There's no need to switch pluggable database, by default Oracle Autonomous Database on Dedicated Exadata Infrastructure only connects to pluggable databases.
  3. Create public synonyms.
  4. Run data dictionary scripts.

Configure PeopleSoft Scripts

PeopleSoft scripts need to be pulled from the source environment and configured. Scripts are available at $PS_HOME/scripts/unix/pdb.

Create a copy of the following files and execute them against the Oracle Autonomous Database on Dedicated Exadata Infrastructure. It can be run from the source database, or from the Zero Downtime Migration service host. For these instructions, these scripts have been run from the Zero Downtime Migration service host.

Update the following scripts:

  1. The utlspace.sql script builds default tablespace for PeopleSoft Users. Complete the following required updates before executing it against Oracle Autonomous Database on Dedicated Exadata Infrastructure:
    1. To get the file location:
      SQL> set linesize 200
SQL> col name format a120
SQL> select FILE# , NAME from  v$datafile;
SQL> select FILE# , NAME from  v$tempfile;
    2. Comment below lines:
      ALTER SESSION SET CONTAINER = PDB_SERVICE_NAME: Connect with PDB as it is not required for Oracle Autonomous Database on Dedicated Exadata Infrastructure
@$ORACLE_HOME/rdbms/admin/catblock.sql: Creates views that can dynamically display lock dependency graphs
    3. Update below lines:
      CREATE TEMPORARY TABLESPACE PSTEMP: Update file location as per Database File Path
CREATE TABLESPACE PSDEFAULT: Update file location as per Database File Path
  2. The dbowner.sql script creates PeopleSoft PSDBOWNER Owner ID. Complete the following required updates before executing it against Oracle Autonomous Database on Dedicated Exadata Infrastructure:
    1. Comment the following lines:
      • CREATE PUBLIC SYNONYM PRODUCT_PROFILE: This operation is not allowed from within a pluggable database.
      • CREATE PUBLIC SYNONYM PRODUCT_USER_PROFILE: This operation is not allowed from within a pluggable database.
      • @$ORACLE_HOME/sqlplus/admin/pupbld.sql: PUBBLD stands for "Product User Profile BuiLD". This is not required for Oracle Autonomous Database on Dedicated Exadata Infrastructure.
    2. Update the following line:
      • CONNECT system/&SYSTEMPWD@PDB_SERVICE_NAME: Update the user to ‘ADMIN’ instead of ‘SYSTEM’, along with the correct PDB service name.
  3. The connect.sql script sets up the PeopleSoft connect ID. No update is required for this script.
  4. The psroles.sql script provides privileges required to run the PeopleSoft application. There is one update required before executing it against Oracle Autonomous Database on Dedicated Exadata Infrastructure.
    1. Comment the line ALTER SESSION SET CONTAINER = PDB_SERVICE_NAME, connect with PDB, as it is not required for Oracle Autonomous Database on Dedicated Exadata Infrastructure.
  5. The psroles2.sql script provides additional privileges required to the PSADMIN role. Complete the following required updates before executing it against Oracle Autonomous Database on Dedicated Exadata Infrastructure.
    1. Comment the line ALTER SESSION SET CONTAINER = <PDB_SERVICE_NAME>, connect with PDB as it is not required for Oracle Autonomous Database on Dedicated Exadata Infrastructure.
    2. Update the following lines:
      • GRANT SELECT ON SYS.V_$MYSTAT to PSADMIN: Update SELECT to READ for Oracle Autonomous Database on Dedicated Exadata Infrastructure.
      • GRANT SELECT ON USER_AUDIT_POLICIES to PSADMIN: Update SELECT to READ for Oracle Autonomous Database on Dedicated Exadata Infrastructure.
      • GRANT SELECT ON DBA_AUDIT_POLICY_COLUMNS to PSADMIN: Update SELECT to READ for Oracle Autonomous Database on Dedicated Exadata Infrastructure.
      • GRANT EXECUTE ON DBMS_FGA to PSADMIN: Update SELECT to READ for Oracle Autonomous Database on Dedicated Exadata Infrastructure.
  6. The upggrant.sql script grants additional privileges required for the existing PSADMIN role. No updates are required.
  7. The psadmin.sql script creates PeopleSoft PSDBOWNER Owner ID. Complete the following required updates before executing it against Oracle Autonomous Database on Dedicated Exadata Infrastructure:
    1. Comment the following lines:
      • ALTER SESSION SET CONTAINER = PDB_SERVICE_NAME: Connect with PDB as it is not required for Oracle Autonomous Database on Dedicated Exadata Infrastructure.
      • @$ORACLE_HOME/rdbms/admin/catdbsyn: Script for catalog dba synonyms. This is not required for Oracle Autonomous Database on Dedicated Exadata Infrastructure.
      • @$ORACLE_HOME/sqlplus/admin/pupbld: Script for Product User Profile Build. This is not required for Oracle Autonomous Database on Dedicated Exadata Infrastructure.
      • @$ORACLE_HOME/rdbms/admin/utlxmv: Creates a table named mv_capabilities_table that is required by the dbms_mview.explain_mview procedure. This is not required for Oracle Autonomous Database on Dedicated Exadata Infrastructure.
    2. Update the line CONNECT system/&SYSTEMPWD@PDB_SERVICE_NAME, update the user to ‘ADMIN’ instead of ‘SYSTEM’ along with correct PDB service name.
  8. Run the updated scripts in Oracle Autonomous Database on Dedicated Exadata Infrastructure in the following order:
    1. utlspace.sql
    2. dbowner.sql
    3. connect.sql
    4. psroles.sql
    5. psroles2.sql
    6. upggrant.sql
    7. psadmin.sql