ORA-08104

Beberapa waktu lalu, saya mendapatkan error “ORA-08104” di database warehouse di kantor, dikarenakan di “kill” nya proses “rebuild index online” nya, sehingga menyebabkan index tersebut menjadi tidak valid.

Dan berdasarkan referensi dari metalink note.375856.1, disitu dinyatakan bahwa error tersebut terdapat relasi dengan BUG 3805539, yang nyatanya issue tersebut harusnya sudah fix di Oracle Ver. 10.2.0.1, Tapi nyatanya di database warehouse saya yang notabene adalah Oracle Ver. 10.2.0.4.0 dan miskin downtime masih terdapat error tersebut.

Tilik ditilik ternyata didapatkan 2 solusinya, yaitu dengan mengganti flag pada “SYS.IND$” tersebut, atau menggunakan “dbms_repair.online_index_clean” sesuai dengan note.375856.1 tadi.

Oke pertama kita gunakan solusi yang pertama dengan menganti flag pada “SYS.IND$” tersebut.
1) Kita cari dulu “object ID” dari index yang bermasalah itu

SELECT obj# FROM sys.ind$ WHERE bitand(flags, 512)=512;

2) Update flag untuk “object id” tersebut.

1
2
UPDATE sys.ind$ SET flags = flags - 512 WHERE obj# = 317358;
commit;

3) Drop and recreate the index

Disini setelah update flag, ternyata index tidak bisa didrop, malah muncul error “ORA-00054: resource busy and acquire with NOWAIT specified”, puih..

Oke saya anggap gagal, Next kita coba solusi berikutnya,
1) Kita cari dulu “object ID” dari index yang bermasalah itu

SELECT i.obj#, i.flags, u.name, o.name, o.type#
FROM sys.obj$ o, sys.user$ u, sys.ind$ idx, sys.ind_online$ i
WHERE聽 bitand(i.flags, 512) = 512 AND o.obj#=idx.obj# AND
o.owner# = u.user# AND idx.obj#=i.obj#;

2) Execute DBMS_REPAIR.ONLINE_INDEX_CLEAN

1
2
3
4
5
6
7
8
9
10
11
12
13
DECLARE
RetVal BOOLEAN;
OBJECT_ID BINARY_INTEGER;
WAIT_FOR_LOCK BINARY_INTEGER;
 
BEGIN
OBJECT_ID := 317358; --ini Object ID Hasil dari Kueri 1
WAIT_FOR_LOCK := NULL;
 
RetVal := SYS.DBMS_REPAIR.ONLINE_INDEX_CLEAN ();
COMMIT;
END;
/

Setelah proses clean up diatas berhasil, coba lakukan DML ke dalam tabel dimana index tersebut berada.
Dijamin tokcer, tanpa perlu bouncing databasenya.

Read More

OraSnap on the fly

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.

Read More

Oh..Archive Log

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.

Read More

No locks available

superdome

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

Read More

Moving & Recreate Temp Tablespace

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, the extend proses was to quickly than before, 馃檨
Check and richeck, so i used command bellow

lvdisplay /dev/vg12/lvol5
fstyp -v /dev/vg12/lvol5

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.

Read More

‘Lebaran’ at d office

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.

Read More