OraSnap (Oracle Snapshot) contains all the SQL scripts that most of us already have in our arsenal. The “twist” is the way the information is presented. All of the statistics are generated with HTML tags - which can then be viewed with a web browser. Each report has the source code used for the information and notes with relevant information about the statistics gathered. OraSnap currently has over 110 scripts that generate a multitude of information for v7, v8, v9 and v10 databases.

OraSnap on the fly

Update: Orasnap biasanya digunakan oleh para database administrator yang begelut di oracle,
seperti disebut diatas kita dapat menggunakannya untuk report performance dari database yang akan kita
“gather” informasinya.

Multipathing Di linux dengan Device Mapper
———————————————-
1. Server IBM BladeCenter HS20 dengan RHEL 4
2. Storage HDS USP1100
3. Switch brocade 48000

Langsung aja ya..

Cek HBA WWN nya
# cat /proc/scsi/qla2xxx/2 |grep port
scsi-qla1-adapter-port=210000145e24e4d5;
# cat /proc/scsi/qla2xxx/1 |grep port
scsi-qla0-adapter-port=210000145e24e4d4;

Cek Aplikasi Device mapper & Installasi
# rpm -qa|grep device-mapper

# wget ftp://ftp.pbone.net/mirror/ftp.centos.org/4.6/os/i386/
CentOS/RPMS/device-mapper-multipath-0.4.5-27.RHEL4.i386.rpm

# wget ftp://ftp.pbone.net/mirror/ftp.centos.org/4.6/os/i386/
CentOS/RPMS/sysfsutils-1.2.0-1.i386.rpm
# rpm -ivh sysfsutils-1.2.0-1.i386.rpm
# rpm -ivh device-mapper-multipath-0.4.5-27.RHEL4.i386.rpm

ModProbe ke Kernel
# modprobe dm-multipath

Startup Config
# chkconfig multipathd on
# service multipathd start
(more…)

Damn…
when project upgrade veritas netbackup from 5.0 to 6.0 never shown their progress.
and the database archive log was grown rapidly, so for the quickly action, I build this script.
works on HP-UX environments


#!/usr/bin/sh
# moving archive log
#####################################
bdf -l /oracle/archivelog | grep -iv Filesystem |awk '{print $6" "$5}' | while
read LINE; do
ASS=`echo $LINE | cut -d"%" -f1 | awk '{ print $2 }'`
if [ $ASS -gt 95 ]; then
/usr/bin/find /oracle/archivelog -name "*.arc" -type f -exec mv {} /backup/archive_may/ \;
fi
done


combination with the crontab..

$ crontab -l
* * * * * sh /scripting/arsip.sh >/dev/null 2>&1

Simple, stupid but deadly.

Hari ini gue mendapatkan banyak error di mesin yang menurut gue paling canggih, paling banyak CPU (20), paling banyak Memory (143GB), boros disk (30 TB), pokoknya
serba paling.

tapi di hari ini dia juga mengumpulkan daftar error paling banyak, berikut error list hari ini.

List Error Hari ini
“lckpwdf: Lockf deadlock detection Cannot obtain lock for /etc/.pwd.lock”
“WARNING: Failed to lock file: /var/opt/ignite/recovery/mnr_lockfile: Lockf deadlock detection (errno = 45)”
“Oracle instance running on a system with low open file descriptor limit. Tune your system to increase this limit to avoid severe performance degradation”
“ORA-00313: open failed for members of log group 2 of thread 1″
“ORA-00312: online log 2 thread 1: ‘/rdbms/oracle/users/oracle_sid/log_oracle-sid.ora’”
“ORA-27086: skgfglk: unable to lock file - already in use”
“HP-UX Error: 46: No locks available”

Machine Detail

• Model: 9000/800/SD32A
• Main Memory: 143277 MB
• Processors: 20
• OS mode: 64 bit
• OS: HP-UX B.11.11 U 9000/800

Solving with tuning the kernel parameter below here..

• nflocks = 200 + sum of database files for all instance
• maxfiles = 25% x current value
• maxfiles_lim = 25% x current value
• maxusers = No of connection oracle + 64
• maxuprc = maxusers * 5

Yesterday i get error on my database

Errors in file /rdbms/oracle/admin/udump/prod_ora_685.trc: ORA-01114: IO error writing block to file 10004 (block # 503489) ORA-27072: skgfdisp: I/O error HP-UX Error: 28: No space left on device Additional information: 503489

Karena semua query di tampung di temporary tablespace,
so i go to that mount point, and you now what, the mount point was full
# bdf

Filesystem kbytes used avail %used Mounted on
/dev/vg12/lvol5 112656384 112656384 0 100% /rdbms/oracle/temp

so i extend the new datafile on temp tablespace to that mount point, the extend proses was to quickly than before,

so i use

# lvdisplay /dev/vg12/lvol5 and
# fstyp -v /dev/vg12/lvol5
to check the size and compare with bdf/sam command.

Think fast, I used another mount point to moving and recreate the temp tablespace..detail click here.

And blast..
the error was gone, the mount point was suspect corrupt, Use umount, lvremove, lvcreate and than newfs, mount and yes new mount point was added again.

Yups..semua orang pasti pernah ngerasai ‘enak’ nya lebaran dikantor, entah loyalitas, terpaksa, lemburan yang berlimpah, ato banyak alasan lah.
kali ini setelah menunaikan sholat ied, dan bersimpuh (terlalu berlebihan kayanya deh) di hadapan ortu, gue langsung cabut ke kantor.

Here’s the problem summary
First we have issue about Undo_Retention (undo_retention before = 129600),
yes the Undo retention was set improper, this keep the query for a long-long time, this fix with alter system set undo_retention=14000, and then alter system set query_rewrite_enabled=false scope=both sid='*',

Second we get error-code message from the client who’s connect to the databases (ORA-03113 end-of-file on communication channel), this cause the connection to server was break see problem next(third),

Third the database was slow response and you know what, like on schedule, the slow response was start at 6 am to 10 am, probably issue I/O contention and delay commit on database and the application.

After discuss with team application and running the statpack, known was we have very-very high traffic (probably lebaran days), solve for while was stop the backup script, rename and truncate the table (the table was B*tree index, hu..uh),
for a while response to from application to database was come back normally and the database was back in good performance, this solve..not quite.

Today i have the same problem, confuse, of course..
i think maybe the memory was limit, cause (again) high traffic but handle only one node (first design RAC), so i change (again) the pga parameter cause the ratio pga was 54% , for the optimum ratio i change to 1.5G (backup first –create pfile ='/app/oracle/product/9.2.0/dbs/init_141007.ora' from spfile–), alter system set pga_aggregate_target=1500M scope=both sid='*' and then restart.
was not impact, hu..uh..

I see the log buffer parameter, and then I see the log_checkpoint_interval was set improper (1410065407) , this impact to delay checkpoint, so for the faster checkpoint with no delay,
I change to default “0″ with alter system set log_checkpoint_interval=0 scope=both id='*'
and you know what, the response queue from application to database was back to normal.

Know i must to distribute/split the busy datafile to other mountpoint and change from raid 5 to raid 1+0…Puih

who’s design this project..hu..uh.