Script to Check Oracle Instance Availability

Check Oracle Instance Availability

The oratab file lists all the databases on a server:

$ cat /etc/oratab








The following script checks all the databases listed in the oratab file, and finds out the status (up or down) of databases:


## ckinstance.ksh ##



echo "`date` "

echo "Oracle Database(s) Status `hostname` :\n"

db=`egrep -i ":Y:N" $ORATAB cut -d":" -f1 grep -v "\#" grep -v "\*"`

pslist="`ps -ef grep pmon`"

for i in $db ; do

echo "$pslist" grep "ora_pmon_$i" > /dev/null 2>$1

if (( $? )); then

echo "Oracle Instance - $i: Down"


echo "Oracle Instance - $i: Up"



Use the following to make sure the script is executable:

$ chmod 744 ckinstance.ksh

$ ls -l ckinstance.ksh

-rwxr--r-- 1 oracle dba 657 Dec 5 22:59 ckinstance.ksh*

Here is an instance availability report:

$ ckinstance.ksh

Mon Dec 31 11:44:12 IST 2007

Oracle Database(s) Status for DBHOST server:

Oracle Instance - oradb1: Up

Oracle Instance - oradb2: Up

Oracle Instance - oradb3: Down

Oracle Instance - oradb4: Up


-Sasi Kumar. D


Peoplesoft - Sign on Process

Understanding Peoplesoft Sign on:

Basic step in Peoplesoft Sign on are:

1. Initial Connection: The application server starts, and uses the Connect ID and User ID specified in its configuration file (PSAPPSRV.CFG) to perform the initial connection to the database.

2. SELECT on security tables: After the Connect ID is verified, the application server performs a SELECT on various PeopleTools security tables, such as PSOPRDEFN, PSACCESSPRFL, and PSSTATUS. From these tables the application server gathers info such as User ID and password, symbolic ID, access ID, and access password. After the application server has the required information, it logs off from this initial connection.

3. Re-Connects with access ID: When the system verifies that the access ID is valid, the application server begins the persistent connection to the database that all PIA and windows three-tier clients use to access the database.


Oracle - Speed up IMPORT Process

Problem: Slow import

Scenario: Truncate table and import repeatedly

Repeated Truncate and import may slow down the process.

The reason is while importing if we don't mention constraints=N, then everytime we import duplicate constraints are created.For example we assume that table ONE has two constraints, If we truncate table,the constraints are still there. For first time import will be fast.During second time, after truncating,and during import(if we doesn't mention CONSTRAINT=N), again two duplicate constraints are created and slows down the import operation little bit.
Each time the duplicate constraints are created it slows down the import process.

Point to be remembered:If we truncate and import table, mention CONSTRAINTS=N explicitly.

Other considerations:

When importing large amounts of data consider dropping indexes prior to the import to speed up the process and re-creating them once the import is completed


Tuxedo Web Administration…

To run the BEA Tuxedo Administration Console, Ensure that your TUXDIR, WEBJAVADIR, and PATH environment variables are set correctly in the Server where the Tuxedo application server is installed.

Then set up the following two server processes:

A Web server provided with the BEA Tuxedo system software. You are not required to use this server; you may, if you prefer, use your own commercial Web server.

To Start Tuxwsvr:
tuxwsvr -l //machine:port -i $TUXDIR/udataobj/tuxwsvr.ini (onUNIX)

A server required to administer the BEA Tuxedo Administration Console. It must be run on the MASTER machine in a Tuxedo multi-machine configuration.

To Start wlistenon:

1. Verify that the wlisten process is running. On a UNIX machine, for example, enter the ps command.
2. If wlisten is not running, open the webgui.ini file and, in the line NADDR=//foo5:4003, replace the port number (4003) with a valid port number.
3. Enter the following command:
$ wlisten -i $TUXDIR/udataobj/webgui/webgui.ini (on UNIX)
4. Check that the tuxwsvr process is running at the port specified in the URL.
5. Verify the password. It must match one of the entries in the file

After starting the tuxwsvr and wlisten server processes, you can start the BEA Tuxedo Administration Console to monitor the tuxwsvr server and the BEA Tuxedo application by replacing the machine & port name appropriately in the below link...


Before invoking the tuxedo web admin, verify the TUXEDO password in file.



PSAPPSRV - How to connect to the Oracle database

Configuration file


The configuration file for the application server (psappsrv.cfg) has a section Database Options, and in that section there is a parameter UseLocalOracleDb. Possible settings are zero (off / default value) and one (on). This parameter is used to determine how to connect to the Oracle database.

A value of zero for this parameter will cause the domain to read the tnsnames.ora at 'connect time'. Feedback from customers tells us that the domains will start slower, help get around problems with shutting down the application server, and result in less remote call errors, with this setting.

A value of one is appropriate only if the database and application server are built on the same machine. This is sometimes used if the tnsnames.ora is not yet set. It will cause the domain to boot up faster.


Report Distribution - Distribution Agent

Report Distribution: PSDSTSRV

Report List table (PS_CDM_LIST)

When the server agent initiates a process request that has an output destination type of Web, or if the Server Definition page is set up to transfer log/trace files to Report Manager, then an entry is inserted into the Report List table (PS_CDM_LIST). Once the program associated with the process finishes, the status in the Report List table is updated to Generated indicating that the files are ready to transfer to Report Repository. The distribution Agent polls the Report List table to determine which process requests have finished running and then transfers them to Report Repository.

Prior to transferring files to the Report Repository, the Distribution Agent will perform the following steps

· Create the INDEX.HTML file. The Distribution Agent inventories all the files generated by the program associated with the process request. It will create an index.html file cataloging all the report, log, and trace files. This HTML file is used by the Report Repository to link these files, so the file can be selected either from Report Manager or the Process Monitor Detail page.
The Distribution Agent uses an HTML report template file to create the index.html. The HTML template file contains markers used to indicate header and detail information. You can make changes to this file provided the markers in the template file are left intact.

· Transfer files to the Report Repository. Once the index.html file is created for the process request, the Distribution Agent transfers this HTML file with all the report and log files to the Report Repository. For each process request that it transfers, a directory is created in the Report Repository using an RBRAN (Really Big Random Alphanumeric Name) schema. All the files for a process request will be stored in this directory.

· Delete the directory from the Process Scheduler Server Agent's Log/Output directory. When the output destination type specified for a process request is Web, all the files and the directory associated with the process request are deleted from the Process Scheduler Log/Output directory after the files are transferred to the Report Repository.


PeopleSoft - Alter Audit

DDDAUDIT is an SQR script that compares your production SQL data tables with the PeopleSoft PeopleTools record definitions to identify inconsistencies.

SYSAUDIT is an SQR script used to identify “orphaned” PeopleSoft objects. For example, SYSAUDIT can identify a module of PeopleCode that exists but does not relate to any other objects in the system. SYSAUDIT also identifies other inconsistencies within your database.

To verify that the PeopleSoft PeopleTools definitions are synchronized with the underlying SQL data tables in your database, run the PeopleSoft PeopleTools alter record process on all records in your system. This process, called an Alter Audit, compares the data structures of your database tables with the PeopleSoft PeopleTools definitions to identify inconsistencies. The Alter Audit then creates SQL scripts with the data definition language (DDL) changes that are required to synchronize your database with the PeopleSoft PeopleTools definitions.

To run Alter Audit:
1. Launch PeopleSoft PeopleTools and sign in to the Target database.
2. From Application Designer, select File, New…
3. Select Project, and then click OK.
4. Select Insert, Definitions into Project...
5. Select Records from the Definition Type drop-down list box.
6. Select Table from the Type drop-down list box.
7. Click Insert, and then click Select All.
8. Click Insert, and then click Close.
9. Select File, Save All.
10. Enter a project name of your choice.
11. Click OK.
12. Select Build, Project..

Note. Triggers are always dropped and re-created during the alter process and will always show up in the generated Alter Audit script. You can ignore the generated script for triggers.


PeopleSoft Data Mover - Modes

PeopleSoft Data Mover runs in two-tier mode only. You must sign in to the database directly, not through an application server.

Regular mode.

Most of the time, you use regular mode. To sign in to regular mode, enter your PeopleSoft user ID and password during sign-in. In regular mode, all commands are valid.
Bootstrap mode.

In bootstrap mode, you use a database access ID and password when signing in. Typically, you use bootstrap mode for database loading, because no PeopleSoft security tables are established yet. You also use bootstrap mode for running some security commands, such as ENCRYPT_PASSWORD.


TUXEDO Connect String - Dynamic load balancing

TUXEDO Connect String the TUXEDO Connect String is designed for advanced configuration to support dynamic load balancing. You can specify a free-form connect string that allows a client to connect to another application server in case another is either down or being used to full capacity. The following sections provide the description of and syntax for the connect string options.

Round Robin Load Balance
This option specifies multiple application servers to which the client will arbitrarily connect. The odds being that each application server will receive an equal number of connections. To specify the round robin, use the following syntax (where ip = IP Address and port = port number):


You can specify the IP Address using either dotted notation or by using the server's DNS name. Regardless of which convention you use to enter the address, the slashes (//) preceding the IP Address are required.

If the application server selected is unavailable, your connection attempt will fail and the system will not try to connect you to the other application servers defined within the parentheses.

Spaces must not be embedded in any part of the connection string. PeopleSoft will automatically remove embedded spaces before storing the value in the registry.

Round Robin with Failover
Similar to Round Robin Load Balance, this option allows you to define a failover connection string. To specify this option, use the following syntax (where ip = IP Address and port = port number):


If the application server selected from the first group of parentheses (ip1 and ip2) is unavailable, the system will automatically attempt to connect to an application server defined in the second group (ip3). If that application server fails, the system will attempt to connect to the next group to the right, sequentially.

If multiple application servers are defined within any group, the system will round-robin between them. If the selected application server fails, the system will attempt to connect to the next application server to the right, if any. The following are three separate examples, showing a range usage:




Max No of accepted char is 1000…