LaGiPusHinGWitH ----- IT

About This Site

Teknologi Informasi sangatlah penting untuk menunjang kemajuan ilmu pengetahuan di Indonesia pada khususnya. Dan ini semua haruslah ditunjang dengan kemampuan yang sepadan dengan kemajuan teknologi itu sendiri, terlebih untuk para praktisi IT itu sendiri. Akhir kata "Selamat berkarya" untuk kita semua. koy^k^guy^ - Owner

Navigation

Home |

Search


Advanced Search

Calendar

<<
 October 2007
>>
S M T W T F S
  1 2 3 4 5 6
7 8 9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29 30 31      

Categories

Monthly Archives

Most recent entries

Syndicate

Site Statistics

This page has been viewed 256144 times
Page rendered in 0.3560 seconds
37 queries executed
Debug mode is on
Total Entries: 61
Total Comments: 17
Total Trackbacks: 42607
Most Recent Entry: 09/28/2010 09:19 pm
Most Recent Comment on: 09/28/2010 09:51 pm
Total Members: 1
Total Logged in members: 0
Total guests: 7
Total anonymous users: 0
Most Recent Visitor on: 02/06/2012 02:50 am
The most visitors ever was 269 on 05/28/2011 03:07 pm

Referrers

Site Credits

Powered by:
ExpressionEngine

Design by:
BlogMoxie

Saturday, October 13, 2007
Resizing TableSpace on Oracle

tutor_resizeTS.txt

Dear all lagi.........

Sesuai janji gw, gw bikin catatan kecil lagi...yang ini khusus ngebahas soal mengatasi ukuran tablespace yang kritis.
Masih ingatkan sama catatan gw yang terdahulu.

langsung aja maaaang....

Pertama2 kita check dengan script yang sudah kita bikin -->

the code :

## Cek TABLESPACE By Command Line Sql ##:

set linesize 128 echo off feedback on heading on verify off pagesize 35
col megs_alloc format 999,999,999 heading “MB ALLOC”
col megs_free format 999,999,999 heading “MB FREE”
col megs_used format 999,999,999 heading “MB USED”
col pct_free format 999 heading “% FREE”
col pct_used format 999 heading “% USED”
--col bsize format 99999 new_value bsize heading “BLOCK SIZE”

--set termout off
--select value bsize from v$parameter
--where name = ‘db_block_size’;
--set termout on

break on report skip 1
compute sum label “TOTAL” of megs_alloc on report
compute sum of megs_free on report
compute sum of megs_used on report

select c.tablespace_name,
round(a.bytes/1048576,2) megs_alloc,
round(b.bytes/1048576,2) megs_free,
round((a.bytes-b.bytes)/1048576,2) megs_used,
round(b.bytes/a.bytes * 100,2) pct_free,
round((a.bytes-b.bytes)/a.bytes,2) * 100 pct_used
from (select tablespace_name,
sum(a.bytes) bytes,
min(a.bytes) minbytes,
max(a.bytes) maxbytes
from sys.dba_data_files a
group by tablespace_name) a,
(select a.tablespace_name,
nvl(sum(b.bytes),0) bytes
from sys.dba_data_files a,
sys.dba_free_space b
where a.tablespace_name = b.tablespace_name (+)
and a.file_id = b.file_id (+)
group by a.tablespace_name) b,
sys.dba_tablespaces c
where a.tablespace_name = b.tablespace_name(+)
and a.tablespace_name = c.tablespace_name
union
select tablespace_name, round((sum(bytes_used) + sum(bytes_free)) / 1048576,2) meg_alloc,
round(sum(bytes_free) / 1048576,2) megs_free,
round(sum(bytes_used) / 1048576,2) megs_used,
round(sum(bytes_free) / (sum(bytes_used) + sum(bytes_free)) * 100,2) pct_free,
round(sum(bytes_used) / (sum(bytes_used) + sum(bytes_free)) * 100,2) pct_used
from v$temp_space_header
group by tablespace_name
order by tablespace_name;

terus kita running lewat sqlplus-->

SQL> @ c:\Data\script\mccserver\tablespacecheck.sql Tablespace Size

TABLESPACE_NAME MB ALLOC MB FREE MB USED % FREE % USED
----------------- ------------ ------------ ------------ ------ ------
CWMLITE 20 3 18 13 88
DRSYS 20 10 10 52 48
EXAMPLE 149 0 149 0 100
======================= potong ============
MAA_TRXP 12,048 255 11,793 2 98
MAA_TRXE 14,048 2,395 11,653 17 83
======================= potong ============
SYSTEM 560 135 425 24 76
TEMP 8,099 0 8,099 0 100
TOOLS 10 10 0 99 1
UNDOTBS1 5,951 5,477 474 92 8
USERS 25 24 1 98 3
======================= potong ============
XDB 38 38 0 100 0
------------ ------------ ------------
TOTAL 121,338 51,027 70,311

34 rows selected.

Disini terlihat ada 2 buah tablespace yang kritis(lebih dari 80%)....
Sekarang kita akan menambah size dari tablespace tersebut, oh iyah...semua command ini full dari sqlplus yah

langkah berikutnya adalah kita lihat dulu nama2 datafile dari tablespace yang akan kita resize (apa sih datafile?
cari tahu aja yah digoogle..atau tanya lagi ntar insya allah gw jawab)

SQL> select * from dba_data_files where tablespace_name = ‘MAA_TRXP’;

FILE_NAME
---------------------------------------
/m02/oradata/maa/maa_trxp01.dbf
/m02/oradata/maa/maa_trxp02.dbf
/m02/oradata/maa/maa_trxp03.dbf

3 rows selected.

hmmmm...ada yang kurang, apa yah? oh iya jumlah/size dari masing2 datafile belum tampak nih…

sekarang kita lihat field2 pada object datafile biar hasilnya spesifik:

SQL> desc dba_data_files;

Name
---------------------------------------
FILE_NAME
FILE_ID
TABLESPACE_NAME
BYTES
BLOCKS
STATUS
RELATIVE_FNO
AUTOEXTENSIBLE
MAXBYTES
MAXBLOCKS
INCREMENT_BY
USER_BYTES
USER_BLOCKS

SQL> select substr(file_name,1,50),sum(bytes) from dba_data_files where tablespace_name = ‘MAA_TRXP’
group by file_name;

SUBSTR(FILE_NAME,1,50) SUM(BYTES)
-------------------------------------------------- ----------
/m02/oradata/maa/maa_trxp01.dbf 5242880000
/m02/oradata/maa/maa_trxp02.dbf 5242880000
/m02/oradata/maa/maa_trxp03.dbf 2147483648

3 rows selected.

Sip...sekarang kita resize tablespace dengan membuat datafile yang baru.... sebenarnya penentuan besaran kapasitas
dari datafile ada analisa tersendiri tapi...seperti biasa akan dibahas pada catatan mengenai Database analysis...hehehe...ngeles.

SQL> alter tablespace maa_trxp add datafile ‘/m02/oradata/maa/maa_trxp04.dbf’ size 5000 M;

Tablespace altered.

Check lagi datafile pada tablespace yang bersangkutan:

SQL> select substr(file_name,1,50),sum(bytes) from dba_data_files where tablespace_name = ‘MAA_TRXP’
group by file_name;

SUBSTR(FILE_NAME,1,50) SUM(BYTES)
-------------------------------------------------- ----------
/m02/oradata/maa/maa_trxp01.dbf 5242880000
/m02/oradata/maa/maa_trxp02.dbf 5242880000
/m02/oradata/maa/maa_trxp03.dbf 2147483648
/m02/oradata/maa/maa_trxp04.dbf 5242880000

4 rows selected.

Nah loooh...dah nambah kan??? siipp, mission succesed.

Sekarang check tablespace-nya, dah ngga kritis lagi atau masih kritis.

SQL> @ c:\Data\script\mccserver\tablespacecheck.sql

Tablespace Size

TABLESPACE_NAME MB ALLOC MB FREE MB USED % FREE % USED
----------------- ------------ ------------ ------------ ------ ------
CWMLITE 20 3 18 13 88
DRSYS 20 10 10 52 48
EXAMPLE 149 0 149 0 100
======================= potong ============
MAA_TRXP 17,048 5,245 11,803 31 69
MAA_TRXE 14,048 2,395 11,653 17 83
======================= potong ============
SYSTEM 560 135 425 24 76
TEMP 8,099 0 8,099 0 100
TOOLS 10 10 0 99 1
UNDOTBS1 5,951 5,477 474 92 8
USERS 25 24 1 98 3
======================= potong ============
XDB 38 38 0 100 0
------------ ------------ ------------
TOTAL 121,338 51,027 70,311

34 rows selected.

Ok deh kaka...sip kan, sekarang dah 69%, dan untuk tablespace lainnya ngga perlu deh gw jelasin lagi step2-nya,
selain ngga perlu juga akan menambah pusing para pembaca sekalian...apalagi gw yang ngetik.... smile

Alhamdulillah...akhirnya selesai juga tugas2 gw, selamat Hari Raya Idul Fitri 1428 H...Taqabalallahu mina wa minkum,
Mohon Maaf Lair dan Batin.... dan SELAMAT BERLIBUR..... ASYIKKKKK GW CUTI....SEMOGA ngga ada masalah sama server2 gw.
</pre>

Posted by roninmorgue on 10/13 at 12:03 AM
Computer DataBase • (0) Comments • (183) Trackbacks • Permalink
Friday, October 12, 2007
Pemeriksaan Oracle Server

tutor_check_oracle.txt

Dear all member...para modie n semua administrator.

Seperti biasa nih, gw mau bikin catatan kecil...dan catatan ini adalah mengenai bagaimana gw melakukan
pemeriksaan rutin terhadap server database gw.(mau cuti panjang nih...tapi gw tetep harus stand by jadi PIC...hik2)

Ok..langsung aja yah, hal-hal yang gw check adalah :
catt : Banyak yang gw edit yah...jangan protes....

1. Cek Utilization Memory

login as: xxxxxx
xxxxxx@192.xxx.0.xxxAlamat Email inidilindungi dari bot spam, Anda Harus Mengaktifkan Javascript Untuk Melihatnya ‘s password:
Last login: Wed Sep 26 15:22:58 2007 from xxx.17.xx.153
Sun Microsystems Inc.  SunOS x.x Generic May xxxx

Sun Fire V880 S/N xxxxxxx PT. xxxxxx xxxx xxxxx 11/11/2003

# sar -r 10 5

SunOS FV880R x.x Generic_xxxxxx-08 sun4u 10/01/2007

12:14:14 freemem freeswap
12:14:24 180058 18268192
12:14:34 179741 18249747
12:14:44 179677 18286104
12:14:54 179346 18285995
12:15:04 178601 18249875

Average 179484 18267968

2. Cek Utilization Performance CPU

# sar -u 10 5

SunOS FV880R x.x Generic_xxxxxx-08 sun4u 10/01/2007

12:15:48 &#xus;r &#xsy;s &#xwi;o &#xid;le
12:15:58 10 2 17 71
12:16:08 1 1 4 95
12:16:18 1 1 8 90
12:16:28 13 4 8 75
12:16:38 43 17 25 15

Average 13 5 12 69

Note: Angka-angka tersebut boleh diubah-ubah sesuai kebutuhan yang mengartikan bahwa
setiap 10 detik akan tercatat sebanyak 5 kali

3. Cek Kapasitas Disk

# df -h

Filesystem size used avail capacity Mounted on
/dev/dck/c1t0d0s0 59G 2.2G 57G 4% /
/proc 0K 0K 0K 0% /proc
mnttab 0K 0K 0K 0% /etc/mnttab
fd 0K 0K 0K 0% /dev/fd
swap 8.7G 168K 8.7G 1% /var/run
swap 8.7G 2.1M 8.7G 1% /tmp
/dev/dck/c1t1d0s6 67G 22G 45G 33% /m01
/dev/dck/c1t2d0s6 67G 29G 37G 45% /m02
/dev/dck/c1t4d0s6 67G 31G 36G 46% /m04
/dev/dck/c1t5d0s6 67G 25G 42G 38% /m05
/dev/dck/c1t3d0s6 67G 37G 30G 56% /m03

4. Cek Tablespace dari database --> ini nih yang penting banget, karena kalau diabaikan bisa ngga running tuh database.

pertama2 kita bikin script dulu, beri nama tablespacecheck.sql:

## Cek TABLESPACE By Command Line Sql ##:

set linesize 128 echo off feedback on heading on verify off pagesize 35
col megs_alloc format 999,999,999 heading “MB ALLOC”
col megs_free format 999,999,999 heading “MB FREE”
col megs_used format 999,999,999 heading “MB USED”
col pct_free format 999 heading “% FREE”
col pct_used format 999 heading “% USED”
--col bsize format 99999 new_value bsize heading “BLOCK SIZE”

--set termout off
--select value bsize from v$parameter
--where name = ‘db_block_size’;
--set termout on

break on report skip 1
compute sum label “TOTAL” of megs_alloc on report
compute sum of megs_free on report
compute sum of megs_used on report

select c.tablespace_name,
round(a.bytes/1048576,2) megs_alloc,
round(b.bytes/1048576,2) megs_free,
round((a.bytes-b.bytes)/1048576,2) megs_used,
round(b.bytes/a.bytes * 100,2) pct_free,
round((a.bytes-b.bytes)/a.bytes,2) * 100 pct_used
from (select tablespace_name,
sum(a.bytes) bytes,
min(a.bytes) minbytes,
max(a.bytes) maxbytes
from sys.dba_data_files a
group by tablespace_name) a,
(select a.tablespace_name,
nvl(sum(b.bytes),0) bytes
from sys.dba_data_files a,
sys.dba_free_space b
where a.tablespace_name = b.tablespace_name (+)
and a.file_id = b.file_id (+)
group by a.tablespace_name) b,
sys.dba_tablespaces c
where a.tablespace_name = b.tablespace_name(+)
and a.tablespace_name = c.tablespace_name
union
select tablespace_name, round((sum(bytes_used) + sum(bytes_free)) / 1048576,2) meg_alloc,
round(sum(bytes_free) / 1048576,2) megs_free,
round(sum(bytes_used) / 1048576,2) megs_used,
round(sum(bytes_free) / (sum(bytes_used) + sum(bytes_free)) * 100,2) pct_free,
round(sum(bytes_used) / (sum(bytes_used) + sum(bytes_free)) * 100,2) pct_used
from v$temp_space_header
group by tablespace_name
order by tablespace_name;

terus kita running lewat sqlplus-->

SQL> @ c:\Data\script\mccserver\tablespacecheck.sql

Tablespace Size

TABLESPACE_NAME MB ALLOC MB FREE MB USED % FREE % USED
----------------- ------------ ------------ ------------ ------ ------
CWMLITE 20 3 18 13 88
DRSYS 20 10 10 52 48
EXAMPLE 149 0 149 0 100
======================= potong ============
MAA_TRXP 12,048 255 11,793 2 98
MAA_TRXE 14,048 2,395 11,653 17 83
======================= potong ============
SYSTEM 560 135 425 24 76
TEMP 8,099 0 8,099 0 100
TOOLS 10 10 0 99 1
UNDOTBS1 5,951 5,477 474 92 8
USERS 25 24 1 98 3
======================= potong ============
XDB 38 38 0 100 0
------------ ------------ ------------
TOTAL 121,338 51,027 70,311

34 rows selected.

uuuppsss....ternyata ada tablespace yang kritis, penggunaan diatas 80% itu sudah kritis. Jadi gimana solusinya?
nanti gw tulis lagi catatan yang khusus ngebahas ini, kira2 title catatannya Resizing Tablespace on Oracle.. smile

Nah...terakhir adalah kita check Locking Object yang dapat membuat performance database jadi down..

5. ## Cek Locking Object yang dapat membuat applikasi Database menjadi lambat.by sql #

SQL> select * from v$locked_object;

XIDUSN XIDSLOT XIDSQN OBJECT_ID SESSION_ID ORACLE_USERNAME OS_USER_NAME PROCE
-------- ---------- ---------- ---------- ---------- -------------------- --------------
1 46 2197363 30223 307 XXXXXXX XXX 42933008
5 36 2739445 3255683 65 XXXXXXX XXX 4752:604

Ternyata ada 2 object yang membuat aplikasi database jadi lambat...solusinya....itu ada pembahasan khusus dibagian
Performance Tuning, tapi kalau darurat sih, bisa langsung di Kill aja tuh proses. hehehe.... smile

OK...deh pemeriksaan selesai, ini sebagian kecil dari pemeriksaan rutin gw (jadi yang utama2nya aja)</pre>

Posted by roninmorgue on 10/12 at 11:39 PM
Computer DataBase • (0) Comments • (249) Trackbacks • Permalink
Page 1 of 1 pages