jump to navigation

Maximum SGA – Oracle 11g 64 bit – Centos 5.2 64 bit Oktober 28, 2008

Posted by setijoagus in oracle, oracle Performance.
Tags: ,
1 comment so far

SGA more than default oracle install ?
SGA >= 4GB ?

1. Hardware
a. processor Intel(R) Pentium(R) Dual CPU E2140 @ 1.60GHz
b. memory 4 GB

[oracle@test ~]$ uname -a
Linux test 2.6.18-92.el5 #1 SMP Tue Jun 10 18:51:06 EDT 2008 x86_64 x86_64 x86_64 GNU/Linux

[oracle@test ~]$ cat /proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 6
model : 15
model name : Intel(R) Pentium(R) Dual CPU E2140 @ 1.60GHz
stepping : 13
cpu MHz : 1200.000
cache size : 1024 KB
physical id : 0
siblings : 2
core id : 0
cpu cores : 2
fpu : yes
fpu_exception : yes
cpuid level : 10
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx lm constant_tsc pni monitor ds_cpl est tm2 cx16 xtpr lahf_lm
bogomips : 3202.49
clflush size : 64
cache_alignment : 64
address sizes : 36 bits physical, 48 bits virtual
power management:

processor : 1
vendor_id : GenuineIntel
cpu family : 6
model : 15
model name : Intel(R) Pentium(R) Dual CPU E2140 @ 1.60GHz
stepping : 13
cpu MHz : 1200.000
cache size : 1024 KB
physical id : 0
siblings : 2
core id : 1
cpu cores : 2
fpu : yes
fpu_exception : yes
cpuid level : 10
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx lm constant_tsc pni monitor ds_cpl est tm2 cx16 xtpr lahf_lm
bogomips : 3199.98
clflush size : 64
cache_alignment : 64
address sizes : 36 bits physical, 48 bits virtual
power management:

[oracle@test ~]$ cat /proc/meminfo
MemTotal: 3341292 kB
MemFree: 42596 kB
Buffers: 18620 kB
Cached: 2872044 kB
SwapCached: 0 kB
Active: 691784 kB
Inactive: 2377968 kB
HighTotal: 0 kB
HighFree: 0 kB
LowTotal: 3341292 kB
LowFree: 42596 kB
SwapTotal: 4610644 kB
SwapFree: 4610644 kB
Dirty: 100 kB
Writeback: 0 kB
AnonPages: 179048 kB
Mapped: 253188 kB
Slab: 37744 kB
PageTables: 31056 kB
NFS_Unstable: 0 kB
Bounce: 0 kB
CommitLimit: 6281288 kB
Committed_AS: 3400880 kB
VmallocTotal: 34359738367 kB
VmallocUsed: 266332 kB
VmallocChunk: 34359471099 kB
HugePages_Total: 0
HugePages_Free: 0
HugePages_Rsvd: 0
Hugepagesize: 2048 kB

2. Default spfile
a. /dev/shm : 1.6 GB
b. memory
memory_max_target : 1600M
memory_target : 1600M
sga_max_size : 1600M

[oracle@test ~]$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/hda2 9.5G 5.5G 3.6G 61% /
/dev/hda6 40G 177M 38G 1% /u02
/dev/hda5 19G 6.8G 12G 38% /u01
/dev/hda1 99M 12M 83M 12% /boot
tmpfs 1.6G 960M 672M 59% /dev/shm

[oracle@test ~]$ sqlplus / as sysdba
SQL> startup;
ORACLE instance started.

Total System Global Area 1670221824 bytes
Fixed Size 2144984 bytes
Variable Size 922748200 bytes
Database Buffers 738197504 bytes
Redo Buffers 7131136 bytes
Database mounted.
Database opened.
SQL> show parameter target

NAME TYPE VALUE
———————————— ———– ——————————
archive_lag_target integer 0
db_flashback_retention_target integer 1440
fast_start_io_target integer 0
fast_start_mttr_target integer 0
memory_max_target big integer 1600M
memory_target big integer 1600M
pga_aggregate_target big integer 0
sga_target big integer 0
SQL> show parameter sga;

NAME TYPE VALUE
———————————— ———– ——————————
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 1600M
sga_target big integer 0
SQL>

3. spfile with 3 GB
a. /dev/shm : 4.0 GB
b. memory
memory_max_target : 3G
memory_target : 3G
sga_max_size : 3G

[root@test ~]# umount /dev/shm
[root@test ~]# mount -t tmpfs shmfs -o size=4G /dev/shm
[root@test ~]# exit
[oracle@test ~]$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/hda2 9.5G 5.5G 3.6G 61% /
/dev/hda6 40G 177M 38G 1% /u02
/dev/hda5 19G 6.7G 12G 38% /u01
/dev/hda1 99M 12M 83M 12% /boot
shmfs 4.0G 2.5G 1.6G 61% /dev/shm

SQL> startup;
ORACLE instance started.
Total System Global Area 3206836224 bytes
Fixed Size 2148680 bytes
Variable Size 1728054968 bytes
Database Buffers 1459617792 bytes
Redo Buffers 17014784 bytes
Database mounted.
Database opened.

SQL> show parameter target;
NAME TYPE VALUE
———————————— ———– ——————————
archive_lag_target integer 0
db_flashback_retention_target integer 1440
fast_start_io_target integer 0
fast_start_mttr_target integer 0
memory_max_target big integer 3G
memory_target big integer 3G
pga_aggregate_target big integer 0
sga_target big integer 0

SQL> show parameter sga;
NAME TYPE VALUE
———————————— ———– ——————————
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 3G
sga_target big integer 0

4. spfile with 4 GB
a. /dev/shm : 4.0 GB
b. memory
memory_max_target : 4G
memory_target : 4G
sga_max_size : 4G

SQL> alter system set memory_max_target=4G scope=spfile;
SQL> alter system set memory_target=4G scope=spfile;
SQL> alter system set sga_max_size=4G scope=spfile;
SQL> shutdown abort;

SQL> startup;
ORACLE instance started.
Total System Global Area 4275781632 bytes
Fixed Size 2151248 bytes
Variable Size 2164264112 bytes
Database Buffers 2097152000 bytes
Redo Buffers 12214272 bytes
Database mounted.
Database opened.
SQL> show parameter target;
NAME TYPE VALUE
———————————— ———– ——————————
archive_lag_target integer 0
db_flashback_retention_target integer 1440
fast_start_io_target integer 0
fast_start_mttr_target integer 0
memory_max_target big integer 4G
memory_target big integer 4G
pga_aggregate_target big integer 0
sga_target big integer 0
SQL> show parameter sga;

NAME TYPE VALUE
———————————— ———– ——————————
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 4G
sga_target big integer 0
SQL>

oracle 11g memory management Oktober 16, 2008

Posted by setijoagus in oracle, oracle Performance.
Tags: , , ,
add a comment

1. sebelum melakukan percobaan save lebih dulu spfile
[oracle@test ~]$ grep SwapTotal /proc/meminfo
SwapTotal: 4096564 kB
[oracle@test ~]$ grep MemTotal /proc/meminfo
MemTotal: 3367968 kB
[oracle@test ~]$ sqlplus / as sysdba
SQL> create pfile=’/home/oracle/14102008.ora’ from spfile;
SQL> startup nomount;
2. memory_max_target= SGA_TARGET + PGA_AGGREGATE_TARGET
a. SGA_TARGET, SGA_MAX_SIZE and PGA_AGGREGATE_TARGET = 0, maka :
SGA = 60% MEMORY_TARGET
PGA = 40% MEMORY_TARGET

SQL> alter system set memory_target=4G scope=spfile;
SQL> alter system set PGA_AGGREGATE_TARGET=0 scope=spfile;
SQL> alter system set SGA_TARGET=0 scope=spfile;
SQL> alter system set SGA_MAX_SIZE=0 scope=spfile;
SQL> shutdown abort;
SQL> startup;
SQL> show parameter target;
NAME TYPE VALUE
———————————— ———– ——————————
archive_lag_target integer 0
db_flashback_retention_target integer 1440
fast_start_io_target integer 0
fast_start_mttr_target integer 0
memory_max_target big integer 4G
memory_target big integer 4G
pga_aggregate_target big integer 0
sga_target big integer 0

SQL> show parameter sga;
NAME TYPE VALUE
———————————— ———– ——————————
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 2460M
sga_target big integer 0

SQL> column COMPONENT format a26;
SQL> select COMPONENT, CURRENT_SIZE, MIN_SIZE, MAX_SIZE from V$MEMORY_DYNAMIC_COMPONENTS
where COMPONENT like ‘SGA Target’ or COMPONENT like ‘PGA Target’;
COMPONENT CURRENT_SIZE MIN_SIZE MAX_SIZE
————————– ———— ———- ———-
SGA Target 2575302656 2575302656 2575302656
PGA Target 343932928 343932928 343932928

b. SGA_TARGET <> 0 dan PGA_AGGREGATE_TARGET = 0
PGA_AGGREGATE_TARGET = MEMORY_TARGET-SGA_TARGET
SQL> alter system set memory_max_target=3G scope=spfile;
SQL> alter system set memory_target=0 scope=spfile;
SQL> alter system set PGA_AGGREGATE_TARGET=0 scope=spfile;
SQL> alter system set SGA_TARGET=2458M scope=spfile;
SQL> shutdown abort;
SQL> startup;
SQL> show parameter target;
NAME TYPE VALUE
———————————— ———– ——————————
archive_lag_target integer 0
db_flashback_retention_target integer 1440
fast_start_io_target integer 0
fast_start_mttr_target integer 0
memory_max_target big integer 3G
memory_target big integer 0
pga_aggregate_target big integer 0
sga_target big integer 2464M
SQL> show parameter sga;
NAME TYPE VALUE
———————————— ———– ——————————
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 2464M
sga_target big integer 2464M

C. SGA_TARGET = 0 dan PGA_AGGREGATE_TARGET <> 0
SGA_TARGET = MEMORY_TARGET-PGA_AGGREGATE_TARGET

SQL> alter system set memory_max_target=3G scope=spfile;
SQL> alter system set memory_target=0 scope=spfile;
SQL> alter system set PGA_AGGREGATE_TARGET=1G scope=spfile;
SQL> alter system set SGA_TARGET=0 scope=spfile;
SQL> shutdown abort;
SQL> startup;

SQL> show parameter target;
NAME TYPE VALUE
———————————— ———– ——————————
archive_lag_target integer 0
db_flashback_retention_target integer 1440
fast_start_io_target integer 0
fast_start_mttr_target integer 0
memory_max_target big integer 3G
memory_target big integer 0
pga_aggregate_target big integer 1G
sga_target big integer 0

SQL> show parameter sga;
NAME TYPE VALUE
———————————— ———– ——————————
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 1968M
sga_target big integer 0

D. SGA_TARGET <> 0 dan PGA_AGGREGATE_TARGET <> 0
SQL> alter system set memory_max_target=4G scope=spfile;
SQL> alter system set memory_target=0 scope=spfile;
SQL> alter system set PGA_AGGREGATE_TARGET=1G scope=spfile;
SQL> alter system set SGA_TARGET=2457600000 scope=spfile;

SQL> shutdown abort;
SQL> startup;
SQL> show parameter target;

NAME TYPE VALUE
———————————— ———– ——————————
archive_lag_target integer 0
db_flashback_retention_target integer 1440
fast_start_io_target integer 0
fast_start_mttr_target integer 0
memory_max_target big integer 4G
memory_target big integer 0
pga_aggregate_target big integer 1G
sga_target big integer 2352M
SQL> show parameter sga;
NAME TYPE VALUE
———————————— ———– ——————————
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 2352M
sga_target big integer 2352M

Where partitions asm disks ? Oktober 16, 2008

Posted by setijoagus in Oracle Administration, oracle.
Tags: , ,
add a comment

Setelah lama install oracle 11g RAC, kadang – kadang kelupaan dimana ya letak partisi ASM disimpan di harddisk mana ?, di fdisk nggak kelihatan scanddisk juga nggak dapat, berikut tips untuk mengingatkan kembali letak asm disks

1. scandisks Oracleasm
[root@rac1 ~]# /etc/init.d/oracleasm scandisks
Scanning system for ASM disks: [ OK ]

2. listdisks Oracleasm
[root@rac1 ~]# /etc/init.d/oracleasm listdisks
VOL1
VOL2
VOL3

3. querydisk Oracleasm
[root@rac1 ~]# /etc/init.d/oracleasm querydisk VOL1
Disk “VOL1″ is a valid ASM disk on device [8, 33]
[root@rac1 ~]# /etc/init.d/oracleasm querydisk VOL2
Disk “VOL2″ is a valid ASM disk on device [8, 49]
[root@rac1 ~]# /etc/init.d/oracleasm querydisk VOL3
Disk “VOL3″ is a valid ASM disk on device [8, 81]

4. list partition ?
[root@rac1 ~]# grep “8 33″ /proc/partitions
8 33 524281243 sdc1
[root@rac1 ~]# grep “8 49″ /proc/partitions
8 49 5238597 sdd1
[root@rac1 ~]# grep “8 81″ /proc/partitions
8 81 1571669 sdf1

summary :
VOL1 /dev/sdc1
VOL2 /dev/sdd1
VOL3 /dev/sdf1

oracle 11g RAC – ASM : move / rename datafile Oktober 13, 2008

Posted by setijoagus in Oracle Administration, oracle.
Tags: , , , ,
add a comment

###########################################
oracle 11g RAC – ASM : move / rename datafile
###########################################
1. list datafile
[oracle@node1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.1.0.6.0 – Production on Mon Sep 29 14:08:24 2008
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> SELECT file_name FROM dba_data_files;
FILE_NAME
——————————————————————————–
+DATA1/node/datafile/users.259.666184225
+DATA1/node/datafile/undotbs1.258.666184225
+DATA1/node/datafile/sysaux.257.666184225
+DATA1/node/datafile/system.256.666184225
+DATA1/node/datafile/example.267.666184357
+DATA1/node/datafile/undotbs2.268.666184499
+DATA1/node/datafile/soe.dbf
+DATA1/node/datafile/soeindex.dbf
8 rows selected.

2. list diskgroup
SQL> SELECT name FROM v$asm_diskgroup;
NAME
——————————
DATA1
DATA2
DATA3
DATA4

3. move / rename datafile
a. offline datafile
 SQL> ALTER DATABASE DATAFILE ‘+DATA1/node/datafile/users.259.666184225′ OFFLINE;
Database altered.
b. rman copy
RMAN> COPY DATAFILE ‘+DATA1/node/datafile/users.259.666184225′ TO ‘+DATA2/users.dbf’;
Starting backup at 29-SEP-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=113 instance=node1 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DATA1/node/datafile/users.259.666184225
output file name=+DATA2/users.dbf tag=TAG20080929T141528 RECID=2 STAMP=666713737
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 29-SEP-08
Starting Control File and SPFILE Autobackup at 29-SEP-08
piece handle=+DATA1/node/autobackup/2008_09_29/s_666713738.322.666713747 comment=NONE
Finished Control File and SPFILE Autobackup at 29-SEP-08
RMAN> exit
Recovery Manager complete.

c. list asmcmd
[oracle@node1 ~]$ ORACLE_SID=+ASM1; export ORACLE_SID
[oracle@node1 ~]$ asmcmd
ASMCMD> exit
[oracle@node1 ~]$ asmcmd -p
ASMCMD [+] > ls -la +DATA2/
Type      Redund  Striped  Time             Sys  Name
Y    NODE/
N    users.dbf => +DATA2/NODE/DATAFILE/USERS.256.666713737
ASMCMD [+] > exit
[oracle@node1 ~]$ exit
exit

d. Rename datafile
SQL> ALTER DATABASE RENAME FILE
‘+DATA1/node/datafile/users.259.666184225′
TO ‘+DATA2/users.dbf’;

e. rman switch datafile
SQL> host
[oracle@node1 ~]$ rman target /
RMAN> SWITCH DATAFILE ‘+DATA2/users.dbf’ TO COPY;
datafile 4 switched to datafile copy “+DATA2/users.dbf”
RMAN> exit
Recovery Manager complete.
[oracle@node1 ~]$ exit

f. recover datafile (optional)
SQL> RECOVER DATAFILE ‘+DATA2/users.dbf’;
Media recovery complete.

g. online datafile
SQL> ALTER DATABASE DATAFILE ‘+DATA2/users.dbf’ ONLINE;
Database altered.

4. new list datafile
SQL> SELECT file_name FROM dba_data_files;
FILE_NAME
——————————————————————————–
+DATA2/users.dbf
+DATA1/node/datafile/undotbs1.258.666184225
+DATA1/node/datafile/sysaux.257.666184225
+DATA1/node/datafile/system.256.666184225
+DATA1/node/datafile/example.267.666184357
+DATA1/node/datafile/undotbs2.268.666184499
+DATA1/node/datafile/soe.dbf
+DATA1/node/datafile/soeindex.dbf
8 rows selected.