Oracle Database link

What is a database link?

A database link will come in handy when we need to access data from another database. The target database need not be an Oracle database, in that case heterogeneous services needs to be enabled.

What are the prerequisites for creating a database link?

1. The create database link privilege must be available for the ID with which the database link needs to be created.
2. The Source and target database servers must have the tnsentry of both the databases added in their machines, oracle net must be installed in both the servers.
3. You must have create session privilege on the target database for the ID which you are using.

Syntax for creating database link:




Example for database link creation:

Lets imagine you got 2 databases, HCMPRD and FINPRD. You want to select the details from PS_JOB table present in HCMPRD from FINPRD, to do this we need to follow the below steps after adding the tnsentry in both the database server.

1. Create an ID in HCMPRD as HCMFIN and provide select access to the PS_JOB table (We do this to prevent the user from FINPRD to access other tables in HCMPRD.)

2. Create the database link FINPRD using the below syntax,

CREATE DATABASE LINK "HCMPRD"  CONNECT TO "HCMFIN" IDENTIFIED BY "HCMFIN" USING 'HCMPRD';

3. Create synonym for the PS_JOB table to access from FINPRD, use the below syntax for the same,

CREATE OR REPLACE SYNONYM "SYSADM"."PS_JOB1" FOR "SYSADM"."PS_JOB"@"HCMPRD";

Now we will be able to access the PS_JOB table in HCMPRD from FINPRD by selecting from the synonym PS_JOB1. 

PeopleSoft Application Server

What is the PeopleSoft Application server?

The Application server is the portion of the Peoplesoft pure internet architecture which is used to handle the business logic and bulk of the workload of the PeopleSoft system.

What is the technology of the Application Server?

Oracle Tuxedo is used to process transaction requests. Oracle Jolt interfaces Java and makes the tuxedo technology to extend to web based environments.
The server processes can be configured for unique purpose, some processes can be used to handle the browser data, while others can be used for Integration broker requests.

How to make use of the Application Server?

The web server will have a configuration.properties file which contains information about the application server to which it needs to connect. The IP address and Jolt Port of the application server is mentioned in that file, now the requests will get directed to the application server which has been configured.

The application server can have multiple domains each one containing multiple server processes running. The request from the webserver will get forwarded to the application server which has the appropriate Jolt port as mentioned in the configuration.properties file. Each application server domain will have an appropriate psappsrv.cfg file which will be created when the domain is deployed. The psappsrv.cfg file contains all the domain information, including the JSL port and the database connectivity details.

How to handle the application server?

The application server is handled using the psadmin utility, which is created as part of the people tools installation. The psadmin utility helps in creating, configuring,deleting and maintaining the application server domains.

PeopleSoft Web Server

What is the PeopleSoft Web Server?

The PeopleSoft Web server is a Java-enabled web server which handles the browser transactions and also supports the PeopleSoft messaging technology. It basically contains a collection of servlets to handle a range of PeopleSoft transactions.

We will discuss on the below sections:

1. Server Software Elements:

When PeopleSoft software is installed, a variety of PeopleSoft web servlets are installed in the web server. A supported servlet container must be installed to use these servlets in our application. The below listed software run on the PeopleSoft web server,

a. Web Services -- The web services manage the web server software which should be Oracle WebLogic or IBM Websphere.
b. Servlet Engine -- The servlet engine is tied to the web services software, but in some cases it can be installed separately as well. The servlet engine contains all the PeopleSoft servlets.
c. Java Servlets -- Java is a platform independent programming language which is used for creating web based applications. The web server contains servlets which require java executables to be invoked.

2. The PeopleSoft Servlets:

The PeopleSoft servlets which are present in the web server are as below,

a. Portal Servlet -- The portal servlet is used to manage the requests and formatting when the client users access PeopleSoft via the Peopletools portal. It is used to handle homepage personalizations, content searches and content access.
b. Integration Gateway Servlet -- This servlet offers publish/subscribe messages between the message nodes. The gateway is used to handle PeopleSoft to PeopleSoft messages, third-party to PeopleSoft messages and PeopleSoft to third-party messages.
c. Report Repository Servlet --  This servlet helps in managing the output of the processes run using the batch server. It distributes the output reports such as crystal reports so that it can be accessed easily over the internet.

3. Oracle Jolt:

Oracle Jolt forwards the requests received by the PeopleSoft servlets to the application server. The Oracle Jolt is an extension of the tuxedo technology over the communication layer between the Java based web server and the C++ based application server. So basically the web browser sends HTTP request to the web server, the servlet in the web server connects to the Jolt port configured in the configuration.properties file of the web server and Oracle Jolt converts the request into a format understandable by Tuxedo which runs in the application server, which in turn will fire the SQLs against the database to fetch the desired output. The below Diagram explains the need for Oracle Jolt and Tuxedo.


Note: The Oracle Jolt and Tuxedo should both be installed in the application server.

PeopleSoft Pure Internet Architecture

What is PeopleSoft Pure Internet Architecture?

The PeopleSoft application runs on various technologies which are spread across what is called the PeopleSoft pure internet architecture, this architecture contains the below elements,

1. The RDBMS database server.
2. The Application Server.
3. The Batch Server.
4. The Web Server.
5. The Client web browser

Explain the Components of the PIA?

The Client Web Browser:
The Client or User will be able to access the application using a web browser which is certified to be supported by PeopleSoft as documented by Oracle. PeopleSoft provides a thin client and there is no software or applet which is required in the client-side.
The browser uses HTTP protocol to send a request to the servlet present in the web server which communicates with the application server. The application server only sends information of the below formats,

1. HTML
2. XML
3. JavaScript
4. Cookies

The browser should have session cookies enabled, as PeopleSoft leverages the browser cookies to store a unique access token after the user is initially authorized. The unique access token will be used by PeopleSoft to bypass the sign-in process when the user accesses another PeopleSoft system in the same browser.

The Web Server:

The PeopleSoft WebServer forwards the requests made by the Web Browser to the application server and also returns the response back to the Browser. The supported web containers are Oracle WebLogic and IBM Websphere.

The Application Server:

The Application Server uses Oracle Tuxedo and Oracle Jolt to convert the Java based HTTP requests to a C++ environment. The application server will send SQL requests to the database server to fetch information from the database server.

The Batch Server:

PeopleSoft facilitates in creating exclusive Jobs, or processes to do tasks. The Batch Server is deployed for serving as a dedicated resource for running Jobs and processes, it can be created in a Linux or Windows environment. Some processes might be dependent on software which are available only in Windows, such as the nVision and Crystal processes.

The Database Server:

The Database Server is the most important component of the PeopleSoft Pure Internet Architecture, it contains the PeopleSoft database. The supported database servers are Oracle, DB2 and SQL Server. The application server contains the connectivity information of the database in its configuration file.


How are the components of the PIA connected?

The below diagram explains the architecture.






The Web Browser connects to the Web server over internet or intranet connection. The Web server contains a configuration.properties file which stores the information of IP and Jolt Port of the Application server. The application server contains a psappsrv.cfg configuration file which contains the connectivity information about the database server. Similarly the batch server contains a psprcsrv.cfg configuration file which stores the database connectivity information. If the Batch and Application servers are not in the same machine as the Database server then they would require the Database Client software to be installed in their machines individually.

Introduction to PeopleSoft and PSAdmin

What is PeopleSoft?

PeopleSoft is an ERP(Enterprise Resource Planning) software, which provides Human Resource Management Systems (HRMS), Financial Management Solutions (FMS), Supply Chain Management (SCM), Customer Relationship Management (CRM), and Enterprise Performance Management (EPM) software, as well as software for manufacturing, enterprise performance management, and student administration to large companies, governments, and organizations.

In the year 2005, there was a hostile acquisition of PeopleSoft by Oracle corporation. Since then the PeopleSoft brand and market line are marketed by Oracle.

Who are the founders?

PeopleSoft was created by David Duffiled and Ken Morris in the year 1987. After it got acquired by Oracle, David Duffield moved on to create a new cloud based ERP software system called Workday.


What are the technologies involved?

PeopleSoft is a combination of different technologies spread over what is called the "PeopleSoft Pure Internet Architecture". Predominantly the underlying programming languages are C++, Java, tuxedo..etc. PeopleSoft supports multiple database platforms such as Oracle, DB2, Sql Server.


What are the basic skills required to learn PeopleSoft Administration?

Some of the skills required for PeopleSoft Administration are as listed below,

1. Basic understanding on client-server architecture.
2. Knowledge on working in Unix/Linux and Windows based server environments.
3. Working with tuxedo.
4. Understanding web containers such as Oracle Weblogic, or IBM Websphere.
5. Knowledge on Oracle, DB2 or SQL Server database servers.

What are some of the roles of a PeopleSoft Administrator?

1. Installing and maintaining the PeopleSoft Pure Internet Architecture, and deploying the domains.
2. Performing upgrades and applying patches/bundles.
3. Monitoring the database, performing SQL tuning, there are some roles which will ask the PSAdmin to perform DB Backup and restore.
4. Supporting the development team on testing and migrating custom code changes to production environment.