For PeopleSoft DB creation PeopleTools installation guide is the best resource to follow. However below are the high level steps for creating PeopleSoft Database on Oracle & windows.
1. Installing the Oracle RDBMS Software
Install Oracle S/W CDs. This will create This will create the home directory (Referred as ORACLE_HOME) and required files and binary.
2. Installing the PeopleSoft Database Server Components on the Database Server
Install PeopleSoft People Tools and Application CDs. This will create the home directory (Referred as PS_HOME) and required files and binaries.
3. Obtaining Windows Administrator Authority
Make sure the user creating the database should be part of local admin group
4. Creating an INIT<SID>.ORA File
Add or modify the following init.ora parameters:
DB_NAME = <SID>
DB_FILES = specify max allowed per your OS, typically 1021
OPEN_CURSORS = 255 <This is a minimum value. You may choose to set this higher>
db_block_size = 8192
Below is a sample INIT.ORA file names initHR88DEMO.ora.
###########################################
# Cache and I/O
###########################################
db_block_size=8192
db_cache_size=25165824
db_file_multiblock_read_count=16
###########################################
# Cursors and Library Cache
###########################################
open_cursors=300
###########################################
# Database Identification
###########################################
db_domain=""
db_name=HR88DEMO
###########################################
# Diagnostics and Statistics
###########################################
background_dump_dest=C:\oracle\product\10.1.0\admin\HR88DEMO\bdump
core_dump_dest=C:\oracle\product\10.1.0\admin\HR88DEMO\cdump
user_dump_dest=C:\oracle\product\10.1.0\admin\HR88DEMO\udump
###########################################
# File Configuration
###########################################
control_files=("C:\oracle\product\10.1.0\oradata\HR88DEMO\control01.ctl", "C:\oracle\product\10.1.0\oradata\HR88DEMO\control02.ctl", "C:\oracle\product\10.1.0\oradata\HR88DEMO\control03.ctl")
db_recovery_file_dest=C:\oracle\product\10.1.0\flash_recovery_area
db_recovery_file_dest_size=2147483648
###########################################
# Job Queues
###########################################
job_queue_processes=10
###########################################
# Miscellaneous
###########################################
compatible=10.1.0.2.0
###########################################
# Pools
###########################################
java_pool_size=50331648
large_pool_size=8388608
shared_pool_size=83886080
###########################################
# Processes and Sessions
###########################################
processes=150
###########################################
# Security and Auditing
###########################################
remote_login_passwordfile=EXCLUSIVE
###########################################
# Shared Server
###########################################
dispatchers="(PROTOCOL=TCP) (SERVICE=HR88DEMOXDB)"
###########################################
# Sort, Hash Joins, Bitmap Indexes
###########################################
pga_aggregate_target=25165824
sort_area_size=65536
###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_management=AUTO
undo_tablespace=PSUNDOTS
5. Creating Target Directory Paths
Modify the PeopleSoft scripts with appropriate oracle path
6. Setting the ORACLE_SID Environment Variable
7. Create Password File
orapwd file=C:\oracle\product\10.1.0\Db_1\database\pwdtemp2.ora password=manager entries=5
8. Create Oracle Service in Windows
oradim -new -sid HR88DEMO -intpwd manager -startmode auto -pfile C:\oracle\product\10.1.0\admin\HR88DEMO\pfile\initHR88DEMO.ora
9. Perform steps for executing SQL scripts
To create an Oracle database:
1. Invoke SQL*PLUS (sqlplus), connecting as sysdba.
$sqlplus ‘/as sysdba’
2. Run the CREATEDB.SQL or CREATEDB10.SQL script, using the following example as a guide:
sqlplus>@<PS_HOME>\scripts\nt\createdb10.sql
Creating Catalog Views and Utility Tablespaces
Run the UTLSPACE.SQL script from SQL*Plus to create catalog views and utility tablespaces.
To create catalog views and utility tablespaces:
1. Invoke SQL*Plus (sqlplus), connecting as sysdba.
2. Run the UTLSPACE.SQL script:
sqlplus>@<PS_HOME>\scripts\nt\utlspace.sql
Creating PS.PSDBOWNER Table
Run the DBOWNER.SQL script from SQL*Plus to create the PS.PSDBOWNER table.
To create the PS.PSDBOWNER table:
1. Invoke SQL*Plus (sqlplus), connecting as sysdba.
2. Run the DBOWNER.SQL script, using the following example as a guide:
sqlplus>@<PS_HOME>\scripts\nt\dbowner.sql
To create application-specific tablespaces:
Run the HCDDL.sql to create the application specifica tablespaces.
1. Invoke SQL*Plus (sqlplus), connecting as sysdba.
2. Run the appropriate DDL scripts.
For example:
sqlplus>@E:\PT8.48_ORA\scripts\nt\HCDDL.sql
To create the roles for your PeopleSoft database:
1. ToLog on to SQL*Plus to as the System user.
2. Run the PSROLES.SQL script:
SQLPLUS>@<PS_HOME>\scripts\nt\psroles.sql
To create the PeopleSoft database owner ID:
1. Log on to SQL*Plus as the System user.
2. Run the PSADMIN.SQL script.
SQLPLUS>@<PS_HOME>\scripts\nt\psadmin.sql
To create connect ID:
1. Log on to SQL*Plus as the System user.
2. Run the connect.sql script.
SQLPLUS>@<PS_HOME>\scripts\nt\connect.sql
3. The script will then create the connect ID and grant it CREATE Session privileges only.
10. Running Data Mover Script to create PeopleSoft table, view etc
- Once we reach to this state tnsping <dbname> must be successful.
- Open the PeopleSoft Configuration Manager from <PS_HOME>\bin\client\winx86\pscfg.exe and enter the database name and connect id/pwd created in earlier step.
- Run Data Mover ( <PS_HOME>\bin\client\winx86\psdmt.exe) in bootstrap mode, using as a user ID the access ID, which should be the user that creates the database.
- To invoke the Database Setup wizard, choose File, Database Setup.
Select your database platform.
Select your character set and click Next.
Select your PeopleSoft Application and click Next.
Select the Demo or System radio button, depending on which type of PeopleSoft database you are installing.
Set the database parameters and click Next.
Enter the Database Name, Symbolic ID, Access ID, Access Password, Connect ID.
After Click on Finish, there should have a file named HR88DEMOORA.dms will be created in data mover. (File name could be different on the basis of DB name you entered. When you run the script, Data Mover typically does the following:
IMPORT *
Create all the PeopleTools and application tables with their indexes.
ENCRYPT_PASSWORD *
Encrypt security information for the database.
CREATE_TRIGGER *
Create application required triggers.
REPLACE_VIEW *
Create PeopleSoft views.
CREATE_TEMP_TABLE *
Create PeopleSoft temporary tables.
11. Connect the database using Application Designer
Every PeopleSoft Application (HCM, CRM, Portal, ELM etc) is delivered with a minimum People tools version.
If your application version is compatible with People Tools for which you are using binary files like Data Mover,
App Designer etc you should be able to login using Application designer (<PS_HOME>\bin\client\winx86\pscfg.exe)
12. Updating Database to Latest PeopleTools Release
In case your PeopleSoft application database may be on a PeopleTools release prior to the version that you are currently running.
For you to be able to sign on to your database after running the Data Mover script to load your database, the PeopleTools versions
for your database and your file server must match. The steps in this task ensure that your PeopleSoft database is in sync with the
PeopleTools version that you are running. Refer below table for version compatibility between People Tools & Application.
Below table decide what and how many rel script (rel<PeopleToolsVersion>.sql or rel<PeopleToolsVersion><u>.sql) needs to be executed.
13. Updating PeopleTools Database Objects
To update PeopleTools database objects to the current release you must be in Application Designer. The Copy from File functionality lets you
update your PeopleTools database objects from a file. You must perform this step to bring the database objects in sync with the PeopleTools release.
Failure to run this step will introduce problems to your environment.
To update PeopleTools database objects:
- Launch Application Designer and sign on to your database with a valid PeopleSoft user ID.
- Select Tools, Copy Project, From File.
In the resulting dialog box, change the import directory to - <PS_HOME>\projects, select PPLTLS84CUR from the list of projects
and click the Select button and copy the project.
14. Deleting Obsolete PeopleTools Database Objects
This process removes obsolete PeopleTools objects from your database. To update PeopleTools database objects to the current release you must be in Application Designer. You will use the Copy from File functionality to delete the obsolete objects from the database.
To delete obsolete PeopleTools database objects:
- Launch Application Designer and sign on to your database with a valid PeopleSoft user ID.
- Select Tools, Copy Project, From File.
- In the resulting dialog box, change the import directory to <PS_HOME>\projects, select PPLTLS84CURDEL from the list of projects and click Select and copy.
15. Altering PeopleTools Tables
ALTER AUDIT is an online utility used to check whether the PeopleTools tables are synchronized with the underlying SQL data tables in your database. This process compares the data structures of your database tables with the PeopleTools tables to uncover inconsistencies. ALTER AUDIT then reports its findings. In this release, we expect to see differences between the database structure and the tools tables. You will generate and run a SQL script to synchronize the PeopleTools table definitions with the underlying tables in your database.
To alter PeopleTools tables:
- Launch PeopleTools and sign on to the installed database.
- From the Application Designer, select File, Open.
- Select Project, enter PPLTLS84CUR in the name dialog box, and click OK.
- Select Build, Project.
16. Migrating Records to New Tablespaces
PeopleSoft has moved some delivered tables to different Tablespaces. You must run this step to move the tables. To copy the Tablespace Record project: - Launch Application Designer and sign on to your database with a valid PeopleSoft user ID. - Select Tools, Copy Project, From File. - In the resulting dialog box, change the import directory to <PS_HOME>\projects, select PT84TBLSPC from the list of projects, and click Select. Note. If the project already exists on the database, a confirmation dialog box appears asking if you want to overwrite the existing project.
Select the File radio button and click OK to overwrite the existing project.
Once the project copied build and execute the Alter Audit Script as per settings mentioned in PeopleTools install document.
17. Updating PeopleTools System Data Data Mover scripts that update PeopleTools system data are run to enable new features and load new messages for the PeopleTools 8.48 release.
Several of the scripts that you need to run are dependent upon the version of the application you are running. To update PeopleTools system data: - Invoke Data Mover by running <PS_HOME>\bin\client\winx86\psdmt.exe. The PeopleSoft Logon window appears. - Log on using the access ID you specified when you created your Data Mover scripts with the Database Setup program. This will start Data Mover in bootstrap mode. 3. Run the appropriate Data Mover scripts for your application database version. The application database version refers to the version before you started this step. Be sure to run the scripts in the order listed.
The scripts are found in the <PS_HOME>\scripts directory: Below is the list to determine what script (<PeopleToolVersion>TLS.dms) needs to be executed.
18. Running PeopleTools Conversions
Refer the PeopleTools install document to understand what are different Application Engine nneds to run, as the number and name of AE may vary on the PeopleTools version you are installing.
Some of the AE which needs to run for PT 8.48 are given below: - UPG844PORTAL - UPGQRYDUPHED - UPGPTSMDAT - UPGPT846PP - UPGPT848PP - UPGPT848IBUG - PTUPGIBCLONE 19. Running Version Application Engine & SQRs
Run the UTLSPACE.SQL script from SQL*Plus to create catalog views and utility tablespaces.
To create catalog views and utility tablespaces:
1. Invoke SQL*Plus (sqlplus), connecting as sysdba.
2. Run the UTLSPACE.SQL script:
sqlplus>@<PS_HOME>\scripts\nt\utlspace.sql
Creating PS.PSDBOWNER Table
Run the DBOWNER.SQL script from SQL*Plus to create the PS.PSDBOWNER table.
To create the PS.PSDBOWNER table:
1. Invoke SQL*Plus (sqlplus), connecting as sysdba.
2. Run the DBOWNER.SQL script, using the following example as a guide:
sqlplus>@<PS_HOME>\scripts\nt\dbowner.sql
To create application-specific tablespaces:
Run the HCDDL.sql to create the application specifica tablespaces.
1. Invoke SQL*Plus (sqlplus), connecting as sysdba.
2. Run the appropriate DDL scripts.
For example:
sqlplus>@E:\PT8.48_ORA\scripts\nt\HCDDL.sql
To create the roles for your PeopleSoft database:
1. ToLog on to SQL*Plus to as the System user.
2. Run the PSROLES.SQL script:
SQLPLUS>@<PS_HOME>\scripts\nt\psroles.sql
To create the PeopleSoft database owner ID:
1. Log on to SQL*Plus as the System user.
2. Run the PSADMIN.SQL script.
SQLPLUS>@<PS_HOME>\scripts\nt\psadmin.sql
To create connect ID:
1. Log on to SQL*Plus as the System user.
2. Run the connect.sql script.
SQLPLUS>@<PS_HOME>\scripts\nt\connect.sql
3. The script will then create the connect ID and grant it CREATE Session privileges only.
10. Running Data Mover Script to create PeopleSoft table, view etc
- Once we reach to this state tnsping <dbname> must be successful.
- Open the PeopleSoft Configuration Manager from <PS_HOME>\bin\client\winx86\pscfg.exe and enter the database name and connect id/pwd created in earlier step.
- Run Data Mover ( <PS_HOME>\bin\client\winx86\psdmt.exe) in bootstrap mode, using as a user ID the access ID, which should be the user that creates the database.
- To invoke the Database Setup wizard, choose File, Database Setup.
Select your database platform.
Select your character set and click Next.
Select your PeopleSoft Application and click Next.
Select the Demo or System radio button, depending on which type of PeopleSoft database you are installing.
Set the database parameters and click Next.
Enter the Database Name, Symbolic ID, Access ID, Access Password, Connect ID.
After Click on Finish, there should have a file named HR88DEMOORA.dms will be created in data mover. (File name could be different on the basis of DB name you entered. When you run the script, Data Mover typically does the following:
IMPORT *
Create all the PeopleTools and application tables with their indexes.
ENCRYPT_PASSWORD *
Encrypt security information for the database.
CREATE_TRIGGER *
Create application required triggers.
REPLACE_VIEW *
Create PeopleSoft views.
CREATE_TEMP_TABLE *
Create PeopleSoft temporary tables.
Every PeopleSoft Application (HCM, CRM, Portal, ELM etc) is delivered with a minimum People tools version.
If your application version is compatible with People Tools for which you are using binary files like Data Mover,
App Designer etc you should be able to login using Application designer (<PS_HOME>\bin\client\winx86\pscfg.exe)
12. Updating Database to Latest PeopleTools Release
In case your PeopleSoft application database may be on a PeopleTools release prior to the version that you are currently running.
For you to be able to sign on to your database after running the Data Mover script to load your database, the PeopleTools versions
for your database and your file server must match. The steps in this task ensure that your PeopleSoft database is in sync with the
PeopleTools version that you are running. Refer below table for version compatibility between People Tools & Application.
Below table decide what and how many rel script (rel<PeopleToolsVersion>.sql or rel<PeopleToolsVersion><u>.sql) needs to be executed.
13. Updating PeopleTools Database Objects
To update PeopleTools database objects to the current release you must be in Application Designer. The Copy from File functionality lets you
update your PeopleTools database objects from a file. You must perform this step to bring the database objects in sync with the PeopleTools release.
Failure to run this step will introduce problems to your environment.
To update PeopleTools database objects:
- Launch Application Designer and sign on to your database with a valid PeopleSoft user ID.
- Select Tools, Copy Project, From File.
In the resulting dialog box, change the import directory to - <PS_HOME>\projects, select PPLTLS84CUR from the list of projects
and click the Select button and copy the project.
14. Deleting Obsolete PeopleTools Database Objects
This process removes obsolete PeopleTools objects from your database. To update PeopleTools database objects to the current release you must be in Application Designer. You will use the Copy from File functionality to delete the obsolete objects from the database.
To delete obsolete PeopleTools database objects:
- Launch Application Designer and sign on to your database with a valid PeopleSoft user ID.
- Select Tools, Copy Project, From File.
- In the resulting dialog box, change the import directory to <PS_HOME>\projects, select PPLTLS84CURDEL from the list of projects and click Select and copy.
15. Altering PeopleTools Tables
ALTER AUDIT is an online utility used to check whether the PeopleTools tables are synchronized with the underlying SQL data tables in your database. This process compares the data structures of your database tables with the PeopleTools tables to uncover inconsistencies. ALTER AUDIT then reports its findings. In this release, we expect to see differences between the database structure and the tools tables. You will generate and run a SQL script to synchronize the PeopleTools table definitions with the underlying tables in your database.
To alter PeopleTools tables:
- Launch PeopleTools and sign on to the installed database.
- From the Application Designer, select File, Open.
- Select Project, enter PPLTLS84CUR in the name dialog box, and click OK.
- Select Build, Project.
16. Migrating Records to New Tablespaces
PeopleSoft has moved some delivered tables to different Tablespaces. You must run this step to move the tables. To copy the Tablespace Record project: - Launch Application Designer and sign on to your database with a valid PeopleSoft user ID. - Select Tools, Copy Project, From File. - In the resulting dialog box, change the import directory to <PS_HOME>\projects, select PT84TBLSPC from the list of projects, and click Select. Note. If the project already exists on the database, a confirmation dialog box appears asking if you want to overwrite the existing project.
Select the File radio button and click OK to overwrite the existing project.
Once the project copied build and execute the Alter Audit Script as per settings mentioned in PeopleTools install document.
17. Updating PeopleTools System Data Data Mover scripts that update PeopleTools system data are run to enable new features and load new messages for the PeopleTools 8.48 release.
Several of the scripts that you need to run are dependent upon the version of the application you are running. To update PeopleTools system data: - Invoke Data Mover by running <PS_HOME>\bin\client\winx86\psdmt.exe. The PeopleSoft Logon window appears. - Log on using the access ID you specified when you created your Data Mover scripts with the Database Setup program. This will start Data Mover in bootstrap mode. 3. Run the appropriate Data Mover scripts for your application database version. The application database version refers to the version before you started this step. Be sure to run the scripts in the order listed.
The scripts are found in the <PS_HOME>\scripts directory: Below is the list to determine what script (<PeopleToolVersion>TLS.dms) needs to be executed.
18. Running PeopleTools Conversions
Refer the PeopleTools install document to understand what are different Application Engine nneds to run, as the number and name of AE may vary on the PeopleTools version you are installing.
Some of the AE which needs to run for PT 8.48 are given below: - UPG844PORTAL - UPGQRYDUPHED - UPGPTSMDAT - UPGPT846PP - UPGPT848PP - UPGPT848IBUG - PTUPGIBCLONE 19. Running Version Application Engine & SQRs
No comments:
Post a Comment