Usefull SQL

General

I plan on adding to this post over time. I have started with process scheduler related queries.

Alter session - so you do not have to preface everything with user.table
alter session set current schema=;
Example: If I needed to access a table(booklist) under the js schema and I was logged in at mj I could do the following.

• select count(*) from js.booklist;

or

• alter session set current schema=js;
• select count(*) from booklist;

Alter session works very well when you are working constantly in another schema like peoplesoft.

Load Specific

I use some oracle specific sql some times.

Process Scheduler

Rundates with the number of processes that were queued when an item was scheduled to run
select to_char(a.rundttm,'yyyy-mm-dd hh24:mi:ss') rundate, count(*) from ( select distinct rundttm from sysadm.ps_pmn_prcslist) a join sysadm.ps_pmn_prcslist b on a.rundttm between b.rundttm and b.enddttm group by a.rundttm;

How long processes are talking to run
select a.PRCSINSTANCE || ',' || a.PRCSNAME || ',' || (a.ENDDTTM-a.BEGINDTTM) * 1440 as ProcessIDProcessNameMinutesTaken from sysadm.ps_pmn_prcslist a where a.BEGINDTTM > sysdate-7;
select a.PRCSINSTANCE || ',' || a.PRCSNAME || ',' || floor(((((a.ENDDTTM-a.BEGINDTTM)*24*60*60)/3600)*3600)/60) as IdNameMinutes from sysadm.ps_pmn_prcslist a where a.BEGINDTTM > sysdate-7;

CSV export of the process scheduler table
select OPRID || ',' || PRCSINSTANCE || ',' || PRCSTYPE || ',' || PRCSNAME || ',' || to_char(rundttm,'yyyy-mm-dd hh24:mi:ss') || ',' || to_char(BEGINDTTM,'yyyy-mm-dd hh24:mi:ss') || ',' || to_char(ENDDTTM,'yyyy-mm-dd hh24:mi:ss') from sysadm.ps_pmn_prcslist;

Installing Oracle 10R2 on Fedora 10 32bit on a VM

Why Did I Post This

I could not find a good guide online. I wanted to setup a fedora box for playing and this looked like a good first step.

General Information

I did this on a VM so there are a few things that I have listed here that may help people install vmware tools.

General Information
Fedora 10 32bit in a VM

• Only installed the OS. No extra items like office productivity
  

Oracle user groups are dba,oper,asmadmin oracle, Oracle

• I used a capitol "O" for kicks

I choose to install to /oracle

• I have never been given a good reason for this u01 junk
  

VMWARE TOOLS

Initial Install
I installed the RPM version with a simple rpm -i XXXXXXXXXXX.rpm on the virtual cdrom

Needed Dependancy
yum install gcc kernel-devel

This allowed me to compile the vmware tools. It will also allow the installation of Oracle. Of course the command to finish the install was /usr/bin/vmware-config-tools.pl

Needed Dependancies for the Oracle Install

Oracle Specific (Page 33 - Oracle Database installation guide)
yum install binutills compat-db compat-libstdc++ control-center gcc-c++ libstc++ glibc-common libstc++-devel gnome-libs make pdksh sysstat xscreensaver setarch

Others that I found and think I needed.
yum install libaio-devel elfutils-libelf-devel unixODBC unixODBC-devel compat-libstdc++-33
Some of these gave me errors for not having them installed. Others I found a reference to when looking at something else.

Edited /etc/sysctl.conf Kernel Parameters

# Put here as part of the oracle pre-install pg41+ of the Oracle Database Installation Guide
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
# semaphores: semmsl, semmns, semopm, semmni
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default=4194304
net.core.rmem_max=4194304
net.core.wmem_default=262144
net.core.wmem_max=262144

Security Limits

Edited /etc/security/limits.conf Pg 43
## Put here as part of the Oracle Database pre-install
Oracle soft nproc 2047
Oracle hard nproc 16384
Oracle soft nofile 1024
Oracle hard nofile 65536

Edit Pam (Failed-so I commented out)

Edited /etc/pam.d/login pg 43 (Breaks x. Commented out)
# Part of the Oracle Pre-install
#session required /lib/security/pam_limits.so
#session required pam_limits.so

As I use the system I may find out why Oracle thinks that this is needed. I installed without it.

SELINUX Disable (Not recommended, but Limits frustations)

Edited /etc/selinux/config Because it gets in the way (Requires a reboot)
SELINUX=disabled

To fool the os version check

Edited /etc/redhat-release (this so I can use the normal install)
redhat release 4

Create the folders and set the permissions

mkdir /oracle
mkdir /oracle/product
mkdir /oracle/product/10.2.0
mkdir /oracle/product/10.2.0/db_1
chown -R Oracle:oinstall /oracle
chown -R 775 /oracle
xhost +localhost
chmod -R g+w /oracle

Not so good oracle installation instructions

Go to the dvds you have/downloaded/etc
You need to get into the database folder
As Oracle run the runInstaller by cd to the directory and running ./runInstaller
Install, hopefully error free

After Install

Edit /etc/redhat-release
Fedora release 10 (Cambridge)

Error Documented

================================================================================
Exception String: Error in invoking target 'all_no_orcl ihsodbc' of makefile '/oracle/product/10.2.0/db_1/rdbms/lib/ins_rdbms.mk'. See '/oracle/oraInventory/logs/installActions2009-01-14_09-01-47AM.log' for details.
===============================================================================
Solution yum install compat-libstdc++-33

Creating a database issues

If you create the databases with lower case you will have to create a simbolic link for some things(like EM) to run properly.

PeopleSoft Cobol Woes on Unix/Linux

Good Commands with Sample Output

cobrun
V4.0 revision 0 build 10/10/2 G; 14705. Run Time System RXCPR/AA0/00000D
cob -V
version @(#)cob.c 1.313
PRN=RXCPR/AAD:9i.T4.40.04
PTI=SP2
I see no work
cob -v
cob64 -C nolist -v
I see no work
/home/psoft
which PSRUN
Depends on your path

Testing your Cobol Environment (My notes are from HPUX)

Loggin to your system as the peoplesoft user or become the peoplesoft user
Setup the environment by calling the psconfig.sh in the peoplesoft home if you have multiple environments
Find PSRUN is in your path with which PSRUN. If it is not in your path you need to link your cobol (check your PS_HOME/setup/)
Run a test cobol with the following command "PSRUN PTPDBTST"
IT FAILED fails check your path variables. Example I needed to add these two on hpunix
export SHLIB_PATH=$ORACLE_HOME/lib:$PS_HOME/bin:$SHLIB_PATH
Test again "PSRUN PTPDBTST"

Pray it works

A few things to be mindfull of. Cobol is very picky of the environment. Make sure you have double checked all of your environmental variables as well as someone else has doublechecked them.

I expect to be installing this on redhat 5 soon, so expect an update.