make sure pgpool node/instance is up ( with bash script )

--idea--
this idea came to me when some one ask " how do we ensure that postgresql's node has been activated again ? ". by asking google and with have no answer, finally why we don't make a happy script with bash ^_^...

--scenario--
as simple as i love to, this script is run as background service that check node's status every 3 second. And if one of node/both is down, script is automatic bring it UP/ON again.

--1--
must to know  how to chek node status:
*> node UP/ON

$ tail /var/log/pgpool.log
2011-12-22 10:06:18 DEBUG: pid 8726: starting health checking
2011-12-22 10:06:18 DEBUG: pid 8726: health_check: 0 th DB node status: 1
2011-12-22 10:06:18 DEBUG: pid 8726: health_check: 1 th DB node status: 1

$ pcp_node_info 10 localhost 9898 postgres password 1
xxx.xxx.xxx.xxx. xxxx 1 1073741823.500000
$ pcp_node_info 10 localhost 9898 postgres password 1 | awk '{print $3}'
1

*> node Down

$ tail /var/log/pgpool.log
2011-12-22 10:14:41 DEBUG: pid 8726: starting health checking
2011-12-22 10:14:41 DEBUG: pid 8726: health_check: 0 th DB node status: 1
2011-12-22 10:14:41 DEBUG: pid 8726: health_check: 1 th DB node status: 3

$ pcp_node_info 10 localhost 9898 postgres password 1
10.10.130.17 5432 3 1073741823.500000
$ pcp_node_info 10 localhost 9898 postgres password 1 | awk '{print $3}'
3

--script--

#!/bin/bash
# brekele.inc

Node_stat=`pcp_node_info 10 localhost 9898 postgres password 0 | awk '{print $3}'`
Node_stat2=`pcp_node_info 10 localhost 9898 postgres password 1 | awk '{print $3}'`

echo "PID: $$ : `date` : daemon check \" $0 \" is running"
echo "=========== Hit Ctl-C to exit ============"

start(){
#echo "jalankan"
pcp_attach_node  90 localhost 9898 postgres password 0 && echo "node UP" || echo "node Down"
}

start2(){
#echo "jalankan"
pcp_attach_node  90 localhost 9898 postgres password 1 && echo "node UP" || echo "node Down"
}

if [ $Node_stat -eq 3 ]
then
start
fi

if [ $Node_stat2 -eq 3 ]
then
start2
fi

sleep 3
exec $0

echo "This line will never echo!"
exit 99

--cek & jajal--
try run on your own terminal/console and with your awn risk ^_^

$ ./script
PID: 26284 : Thu Dec 22 10:43:06 WIT 2011 : daemon check " /mnt/script " is running
=========== Hit Ctl-C to exit ============
PID: 26284 : Thu Dec 22 10:43:09 WIT 2011 : daemon check " /mnt/script " is running
=========== Hit Ctl-C to exit ============

Read more

send oracle 11g audit log to syslog server ( on other PC)

--preparation-- 
well...... this time i will write about syslog, and for this experiment what we need is :
- kiwi syslog server get from here and install it on your PC( i use xp for this server syslog)
- oracle db
- and of course, a cup of coffee is a must and other thing that make you relax and comfortable ( :p )

--scema--
first thing to do is redirect oracle audit log to syslog, and than from syslog send to syslog server( kiwi syslog)

 --1--
 edit initSID.ora and add this folowing config :
create "/var/log/oracle/" firs

audit_file_dest='/var/log/oracle/'
audit_trail='OS'
AUDIT_SYSLOG_LEVEL=local1.warning

after that edit /etc/syslog.conf, and add this folowing config :

#Save oracle rdbms audit trail to oracle_audit.log
local1.warning          /var/log/oracle/oracle_audit.log
#Send oracle rdbms audit trail to remote syslog server
local1.warning       @1.1.1.1 #<= ip your syslog server

--2--
restart syslog service with command :

/sbin/service syslog restart     # or 
/etc/init.d/init.d/syslog restart 

oracle DB must be restart to

CONNECT SYS / AS SYSOPER
Enter password: password

SHUTDOWN IMMEDIATE
STARTUP
-----------------
SQL> shutdown IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> STARTUP
ORACLE instance started.

Total System Global Area 1075527680 bytes
Fixed Size                  1304704 bytes
Variable Size             264243072 bytes
Database Buffers          805306368 bytes
Redo Buffers                4673536 bytes
Database mounted.
Database opened.

--test and jajal--
try to login to your Oracle DB and log must be create on syslog and kiwi syslog ono other server like this folowing pict

-source -
oracle doc

Read more