PeopleSoft Integration Broker Basics

In this post we are going to understand in short about Integration Broker in PeopleSoft. What is the use of it?

What is PeopleSoft Integration Broker?

"Integration Broker in PeopleSoft is a mechanism provided to communicate with the external applications which can be any other PeopleSoft application, fusion applications, Oracle Apps, etc."

This communication takes place with the help of XML files. For subsribing to data by any external system the communication takes place with the help of default connectors that is already a delivered one or with any custom connector. In simple terms this connectore are linked to the Integration gateway through which the communication takes place with the help of Messages, Nodes, Target Connector, Peoplecode.

Lets see each of them in some detail.

Gateways Integration Gateways run on PeopleSoft Web Server. It the medium or the pathways between PeopleSoft and any external system. The PeopleSoft Integration Gateway knows about the node by which it has the udnerstanding of the server name, app server port, username and password so that it is able to connect to the node's app server and can put the message to the running PeopleSoft environment.
A gateway URL is like this "http:\\servername:port\PSIGW\PeopleSoftListeningConnector
PSIGW - This stands for PeopleSoft Integration Gateway

Nodes In PeopleSoft all applications is having a default node for each of them. This nodes let the application know for which a message belongs to and can talk with that.
Example of node are PSFT_HR (HRMS), PSFT_EP (Financials).

Messages A Message define the structure of the XML which is used to sent between nodes. The difference between Asynchronous and Synchronous messages is just acknowledgement. Synchronous messages have to get acknowledge by the receiver whereas Asynchronous message don't.

Queques Queques in PeopleSoft are used to manage groups of messages that are sent through multiple pipes. It is used in administration of Integration Gateway. For example in a queque group of messages for employee data in HRMS application is to be sent to Financials but since Financials is down then the Message Queque can be paused as well. Before the release of PeopleTools 8.48, Queues were also called as "Message Channels".
Few more topics we will cover in next up coming post.

How to check total number of Allowed connections in Oracle Database?

Below script gives the result of the number of allowed connections to the particular database.

SELECT 'Current Connections, '
|| (SELECT COUNT(*) FROM V$SESSION)
|| ' out of '
|| VP.VALUE
|| ' Connections.' AS USAGE_MESSAGE
FROM
V$PARAMETER VP
WHERE VP.NAME = 'sessions'

Type Of Tables in PeopleSoft

In this post we discuss about the types of table available in ERP like PeopleSoft.

Below mentioned are the types of tables in PeopleSoft.

1) Base Table
2) Control Table
3) Views
4) Reporting Table
5) Non-Table (Sub-Records)

Lets discuss each of them in few details:

1) Base Table A Base table hold's the real data that is not static. This tables store information of an employee and contain data of an employee. It can be mentioned as an Live table where data is changing continually. Base tables are not distinguished with a prefix or sufix.
Examples :- PS_PERSONAL_DATA, PS_JOB

2) Control Table In PeopleSoft Control tables are also called as lookups, prompt tables. This control tables holds short list of values. For example a table which holds all earning codes (Bonus, Overtime, Allowance, etc) is a control table and where all the earning details are stored is known as Base Table.
Examples :- PS_EARNINGS_TBL, PS_DEPT_TBL

3) Views Views are the results of an SQL Statmenet that combines and retrives data from more than one table. Views in PeopleSoft has a suffix _VW. Views are linked directly to original tables i.e. Base table or control table.
Examples :- PS_AVT_DEPT_VW, PS_EARNING_CLR_VW

4) Reporting Tables PeopleSoft created three tables that contain the most-often-used human resources fields. This tables are similar like Views but are not dynamic since they hold specific data in one single table and are categorized under reporting tables.
Examples :- PS_EMPLOYEES, PS_BEN_PER_DATA, & PS_BEN_PLAN_DATA

5) Non-Table (Sub-Records) Sub-Record in PeopleSoft are kind of small tables that hold the definition of group of fields of some records.
Example :- ADDRESS_SBR

Failed to Create Message Queque while Application Server Boot

This post explains the solution for the below error message that came while booting the Application Server.

1) LIBTUX_CAT:681: ERROR: Failure to create message queue
2) JOLT_CAT:1088: "ERROR: tpinit() failed, tperrno = 7"

Solution:
1) First we can try to Clean the IPC Tuxpic Services from psadmin.exe utility.
It's the 10th option in Application Server Domain boot Menu in psadmin utility.

2) Increase your IPC Resources of Tuxedo Services for Message queques and Semaphores as per your requirement and system configuration.
The navigation can be access in Windows from control panel.
It will be showing someting like this
"Oracle Tuxedo 10gR3 with VS2005 Administration 32-bit"

*** With this alternatives it should work else check in support.oracle.com for more details.***

Oracle Database Read Only User

This post explains how to create a user with read only access for any table in an Oracle Schema.

This Schema "SYSTEM" hold 50 tables and we create a user to access only this 50 tables in read only mode.

Let's take the database name as Test1

> SET ORACLE_SID=Test1
> SQLPLUS /NOLOG

SQL> CONN SYSTEM/SYSTEM;
SQL> CREATE USER User_Read_Only indentified by User_Read_Only
SQL> GRANT CREATE SESSION, SELECT ANY TABLE TO User_Read_Only
SQL> Commit; Now connect as newly created Read Only User and test.

SQL> CONN User_Read_Only/User_Read_Only;

Check for tables using this new User and SYSTEM.

Done

Oracle database cloning in 11g R2

In this post how to clone an Oracle Database is shown with 11g R2 Version on Windows platform. This post is just for learning purpose. Don't directly try on Production databases till you get the insight of it.

Old database -  test007
New cloned database - clonetest007

Step 1: Bring the old database test007 in Archivelog Mode.

SET ORACLE_SID=test007
SQLPLUS / as sysdba;
SQL> Archive log List

The output can be ARCHIVELOG or NOARCHIVELOG. We will go with ARCHIVELOG mode so following is the step for it.

SQL> Alter System Set LOG_ARCHIVE_DEST_1='Location=E:\oradata\test007\Archived_logs' scope=SPFILE;

Step 2: Now we will shutdown the database and start it in Archivelog Mode;

SQL> Shutdown Immediate;

SQL> startup mount;

SQL> Alter database Archivelog;

SQL> Alter database Open;

With this query let's check if our old database test007 is now in Archivelog mode or not.

SQL> select log_mode from v$database; OR SQL> Archive log list;

Step 3: Now we will create a new database with the clone of old one.

First we will create new folders for our new database.

We will change our directory on command prompt till the oradata where the data files exists.

e:> cd e:\app\product\11.2.0\dbhome_1\oradata\
e:\app\product\11.2.0\dbhome_1\oradata> mkdir clonetest007
e:\app\product\11.2.0\dbhome_1\oradata> cd clonetest007

e:\app\product\11.2.0\dbhome_1\oradata\cd clonetest007> mkdir Archived_Logs

Step 4: Now we will create the PFILE parameter file for the new database clonetest007

Lets create as "initCLONETEST007.ora" in ORACLE_HOME\database with parameter

db_name=clonetest007

Save the file.

Step 5: Now we will create the window service for this oracle database with the shipped "oradim" utility.

oradim -new -sid clonetest007

Instance Created.

We can also start this window service if it's not started with the below command.

net start OracleServiceclonetest007

Step 6: Let's now take the backup of old database test007

set ORACLE_SID=test007

SQLPLUS / AS SYSDBa;

SQL> select log_mode from v$database; ( Archivelog)

SQL> select * from v$backup;

If status is NOT ACTIVE then make it ACTIVE

SQL> Alter database begin backup;

SQL> quit;

Step 7: Now copy all the datafiles *.dbf to the oradata/clonetest007 folder which we created in above steps.

SQL> Alter database end backup;

SQL> Alter system archive log current;

SQL> Alter database backup control file to trace as 'e:\clonetest007.sql';

Open the trace file & make following changes:

REUSE - SET
NORESETLOGS - RESETLOGS
test007 - clonetest007

Remove comments and extra spaces. Keep the text till character set else delete and save it.

Step 8: Now lets start the new database clonetest007

set ORACLE_SID=clonetest007

SQLPLUS / AS SYSDBA;

Connected to an Idle Instance.

Run the trace file now created in above step.

SQL> e:\clonetest007.sql;

Control File Created;

Step 9: Now we will take the current archive log of old database for recovery of new database.

set ORACLE_SID=test007

SQLPLUS / AS SYSDBA;

SQL> Alter system archive log current;

SQL> /

SQL> /

SQL> quit;

Step 10: Recover and start the new database.

set ORACLE_SID=clonetest007

 SQLPLUS / AS SYSDBA;

SQL> Set logsource 'e:\oradata\test007\Archived_logs';

Press Enter four times then Cancel Type.

SQL> Alter database open resetlogs;


************************************** Done ************************************



 

Cannot open the Windows NT Service Control Manager.

This is the most common error that we face in using oracle "oradim" utility from command line:

DIM-00014: Cannot open the Windows NT Service Control Manager.

O/S-Error: (OS 5) Access is denied.

Solution:

This is just an administrator priviledge/right's issue.

Start the command line "cmd.exe" by right click and Run as Administrator.