Wednesday, October 14, 2009

Oracle license: Diagnostics Pack and Tuning Pack

How it affects your Oracle Enterprise Manager database Control?

Diagnostics Pack:

If you disable access to diagnostics pack, following features are disabled:
Performance (tab)
ADDM (Advisor Central)

Tuning Pack: (requires Diagnostics Pack)

If you disable access to tuning pack, following features are disabled:
SQL Access Advisor (Advisor Central)
SQL Tuning Advisor (Advisor Central)

Following features do not require any Oracle license:

Segment Advisor (Advisor Central)
MTTR Advisor (Advisor Central)
Memory Advisor (Advisor Central)
Undo Management (Advisor Central)

Monday, October 12, 2009

How to: troubleshooing Oracle Clusterware is not up

Symptoms:

After server boots up, Oracle clusterware does not startup successfully.

Error message: (Case 1)

/var/log/messages

Oct 9 10:08:24 VMXDB03 logger: Cluster Ready Services waiting on dependencies. Diagnostics in /tmp/crsctl.10862.

/tmp/crsctl.10862

OCR initialization failed accessing OCR device: PROC-26: Error while accessing the physical storage Operating System error [No such file or directory] [2]

Possbile Causes:

OCR device does not exist or it's not accessible


Error message: (Case 2)

/var/log/messages

Oct 9 10:08:24 VMXDB03 logger: Cluster Ready Services waiting on dependencies. Diagnostics in /tmp/crsctl.11713.

/tmp/crsctl.11713

OCR initialization failed with invalid format: PROC-22: The OCR backend has an invalid format

Possbile Causes:

OCR device corrupted, or it's not the correct device due to issues of PowerPath/Linux Divice-Mapper.

How much free space required for Oracle Binaries

In the offical documentation, it states 1.9GB free space required for Enterprise/Standard edition installation, and 2.0GB for Custom type installation. But is it enough?

In recent 10gR2 (10.2.0.4) RAC implementation, it shows at least 7.6GB for Database and 2.0GB for Oracle Clusterware.


[oracle@VMXDB03 10.2.0]$ ls |xargs du -sh
7.6G asm
2.0G crs
7.6G db


The top space consumer is patch backup files.

[oracle@VMXDB03 db]$ ls -a .patch_storage|xargs du -sh
5.9G .patch_storage


[oracle@VMXDB03 .patch_storage]$ ls |xargs du -sh
116M 4693355_Sep_8_2008_04_01_48
111M 5259835_Jan_26_2009_03_10_54
115M 5348308_Oct_9_2008_21_37_51
110M 5747462_Apr_8_2009_16_52_05
110M 5756769_Apr_8_2009_16_52_07
110M 5868257_Jun_5_2008_23_04_30
110M 5879114_Dec_23_2008_21_11_23
110M 5895190_Apr_8_2009_16_52_09
109M 6051177_Apr_8_2009_16_52_10
111M 6052226_Sep_15_2008_02_20_06
2.8M 6079224_Sep_15_2008_03_58_35
109M 6084232_Apr_8_2009_16_52_13
112M 6163771_Jul_30_2008_02_54_13
110M 6200820_Nov_12_2008_06_29_55
109M 6327692_Aug_13_2008_06_40_23
110M 6374297_Apr_8_2009_16_52_03
110M 6455659_Oct_23_2008_04_35_27
109M 6638558_Apr_8_2009_16_52_01
110M 6678845_Jan_9_2009_00_45_07
14M 6679303_Apr_8_2009_16_51_59
110M 6919819_Feb_17_2009_02_57_35
130M 6923450_Apr_8_2009_16_52_19
110M 6972843_Jan_9_2009_00_45_13
110M 7008262_Jan_9_2009_00_45_15
120M 7123643_Jun_26_2008_01_23_32
448K 7155248_Jul_3_2008_02_10_38
348K 7155249_Jul_3_2008_02_10_48
110M 7155250_Jul_3_2008_02_10_51
109M 7155251_Jul_3_2008_02_10_52
110M 7155252_Jul_3_2008_02_10_54
110M 7189447_Jan_9_2009_00_45_16
156K 7197583_Jul_3_2008_02_11_22
110M 7257461_Feb_24_2009_08_55_57
110M 7257770_Jan_12_2009_23_29_55
21M 7272297_Jan_9_2009_00_45_18
111M 7278117_Aug_13_2008_06_35_40
110M 7309458_Apr_8_2009_16_52_21
110M 7340448_Apr_8_2009_16_52_17
109M 7375611_Sep_16_2008_01_15_53
384K 7375613_Sep_16_2008_01_15_57
636K 7375617_Sep_16_2008_01_15_59
112M 7378661_Sep_15_2008_02_14_51
110M 7552067_Nov_16_2008_22_14_33
110M 7606362_Mar_29_2009_22_37_12
3.3M 7609057_Dec_31_2008_04_17_07
109M 7609058_Dec_31_2008_04_15_23
119M 7691766_Jan_9_2009_00_45_09
106M 7715304_Jan_30_2009_06_03_15
104K 7936793_Mar_19_2009_02_45_58
104K 7936993_Mar_25_2009_04_05_16
784K 8227091_Feb_10_2009_04_36_51
118M 8227106_Mar_17_2009_21_25_14
110M 8230457_Feb_11_2009_00_45_48
131M 8287504_Mar_19_2009_03_50_27
232K 8290506_Mar_24_2009_12_34_30
110M 8309587_Mar_24_2009_12_34_32
109M 8309592_Mar_24_2009_12_34_34
720K 8309623_Mar_24_2009_12_34_36
144K 8309632_Mar_24_2009_12_34_37
2.8M 8309637_Mar_24_2009_12_34_45
6.5M 8309639_Mar_24_2009_12_34_42
1.1M 8309642_Mar_24_2009_12_34_40
120M 8340379_Mar_31_2009_22_25_20
110M 8340383_Mar_30_2009_22_42_04
110M 8340387_Apr_1_2009_21_47_43
104K 8344348_Apr_5_2009_23_07_18
112M 8347704_Apr_7_2009_03_46_17
104K 8362683_Apr_8_2009_16_51_57
111M 8373286_Apr_15_2009_03_06_31
6.5M 8534387_Jun_8_2009_20_04_36
6.5M 8568395_Jun_8_2009_20_04_38
1.7M 8568397_Jun_8_2009_20_04_39
172M 8568398_Jun_25_2009_12_05_00
109M 8568402_Jun_8_2009_20_04_44
3.6M 8568404_Jun_8_2009_20_04_46
110M 8568405_Jun_8_2009_20_04_47
4.0K LatestOPatchSession.properties
37M NApply
0 patch_free

Thursday, October 8, 2009

Oracle Bugs: Drop redo log group inside asm diskgroup

Symptons:


SQL> alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kgeade_is_0], [], [], [], [], [],
[], []


SQL> alter database drop standby logfile group 13;
alter database drop standby logfile group 13
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kgeade_is_0], [], [], [], [], [],
[], []


Environment:

Linux (5.3) x86_64 + Oracle 10.2.0.4 64bit (with oracle clusterware/asm/db)

Workaround:

Indeed the redo log groups has been dropped, so these ora-600 errors can be ignored.

Wednesday, October 7, 2009

Oracle Bugs: Disable interconnect network interface

Test Envrionment:
Oracle: oracle 10.2.0.4 (x86_64)
OS: Redhat 5.3 (x86_64)

Error message when unplugging the interconnect cable:


Wed Oct 7 15:14:29 2009
ospid 21322: network interface with IP address 192.168.5.1 no longer running (check cable)


Error message when disabling the interface:


ed Oct 7 15:21:29 2009
ospid 21322: network interface with IP address 192.168.5.1 no longer operational
requested interface 192.168.5.1 not found. Check output from ifconfig command
... ...
Wed Oct 7 15:23:27 2009
Errors in file /u01/app/oracle/admin/vodka/udump/vodka1_ora_21851.trc:
ORA-00603: ORACLE server session terminated by fatal error
ORA-27544: Failed to map memory region for export
ORA-27300: OS system dependent operation:bind failed with status: 99
ORA-27301: OS failure message: Cannot assign requested address
ORA-27302: failure occurred at: sskgxpcre3
... ...
Errors in file /u01/app/oracle/admin/vodka/bdump/vodka1_ora_29422.trc:
ORA-07445: exception encountered: core dump [ksxpsigosderr()+167] [SIGSEGV] [Address not mapped to object] [0x000000061] [] []
ORA-27300: OS system dependent operation:if_not_found failed with status: 0
ORA-27301: OS failure message: Error 0
ORA-27302: failure occurred at: skgxpvaddr9
ORA-27303: additional information: requested interface 192.168.5.1 not found. Check output from ifconfig command


Solution:

This is the expected behavior when disable the interconnect interface.

Friday, October 2, 2009

Oracle & Linux Hugepages settings

Question:

Is hugepages still recommended for 64bit Linux + Oracle?

Answer:

Yes!

Why:

Without hugepages, each page is 4kB by default, to maintain a pagetable of 32GB memory addressing, we need 32*1024*1024/4*8/1024/1024=64MB (each page reference point takes 4 bytes in 32bit OS and 8bytes in 64bit OS). For example, we have 100 such processes; we need 6400MB memory to maintain the pagetable only. (But normally we will not hit this worst situation, because of lazy allocation memory algorism)

With hugepages, each page is 2MB on EM64 architecture. To maintain a pagetable of 32GB memory addressing, we only need 32*1024/2*8/1024=128KB. For example, we have 100 such processes, we only need 12.5MB memory for pagetable.

Another reason to configure hugepages is memory associated hugepages are not swappable, a.k.a, always in memory.

One more benefit, hugepages increases the efficiency of TBL.

Another one, "Cached" memory with hugepages does not include SGA any more.

How to set?

[root@VMXDB01 ~]# cat /etc/sysctl.conf
... ...
vm.nr_hugepages = 16640
... ...

Method of calculating vm.nr_hugepages:

Get Hugepagesize from /proc/meminfo, for our case, it's 2MB.

vm.nr_hugepages = sum (all sga_max_target size in MB) / Hugepagesize + min(2,value_you_feel_conformtable)


[root@VMXDB01 ~]# cat /etc/security/limits.conf
... ...
oracle soft memlock 33564672 <-- larger than max SGA, in KB
oracle hard memlock 33564672 <-- larger than max SGA, in KB
... ...

Verify:

[root@VMXDB01 ~]# cat /proc/meminfo
MemTotal: 64951768 kB
MemFree: 29433020 kB
Buffers: 62304 kB
Cached: 701376 kB
SwapCached: 0 kB
Active: 726660 kB
Inactive: 575368 kB
HighTotal: 0 kB
HighFree: 0 kB
LowTotal: 64951768 kB
LowFree: 29433020 kB
SwapTotal: 33551744 kB
SwapFree: 33551744 kB
Dirty: 376 kB
Writeback: 0 kB
AnonPages: 549344 kB
Mapped: 114368 kB
Slab: 63620 kB
PageTables: 23268 kB
NFS_Unstable: 0 kB
Bounce: 0 kB
CommitLimit: 48988268 kB
Committed_AS: 2529688 kB
VmallocTotal: 34359738367 kB
VmallocUsed: 283056 kB
VmallocChunk: 34359455303 kB
HugePages_Total: 16640
HugePages_Free: 13887
HugePages_Rsvd: 13695
Hugepagesize: 2048 kB

Set different bonding mode for each bonding interface

Instead of settings in the /etc/modprobe.conf to specify bonding mode systemwide, we also can specify bond mode in scripts of each bonding interface. (Avaliable from RHEL 5 onwards)

For example, we can specify bonding mode 4, regardless of the systemwide settings of mode 1.

/etc/modprobe.conf

alias bond0 bonding
alias bond1 bonding
alias bond2 bonding
options bonding miimon=100 mode=1


[root@VMXDB01 ~]# cat /etc/sysconfig/network-scripts/ifcfg-bond2
DEVICE=bond2
USERCTL=no
ONBOOT=yes
BOOTPROTO=none
IPADDR=192.168.0.20
NETMASK=255.255.255.0
TYPE=Ethernet
IPV6INIT=no
PEERDNS=yes
BONDING_OPTS="mode=4 miimon=100"

Restart the interface to make the settings active:

[root@VMXDB01 ~]# ifdown bond2
[root@VMXDB01 ~]# ifup bond2


[root@VMXDB01 ~]# cat /proc/net/bonding/bond2
Ethernet Channel Bonding Driver: v3.2.4 (January 28, 2008)

Bonding Mode: IEEE 802.3ad Dynamic link aggregation
Transmit Hash Policy: layer2 (0)
MII Status: up
MII Polling Interval (ms): 100
Up Delay (ms): 0
Down Delay (ms): 0

802.3ad info
LACP rate: slow
Active Aggregator Info:
Aggregator ID: 1
Number of ports: 1
Actor Key: 17
Partner Key: 1
Partner Mac Address: 00:25:83:46:a2:80

Slave Interface: eth0
MII Status: up
Link Failure Count: 0
Permanent HW addr: 00:24:e8:61:5e:7f
Aggregator ID: 1

Slave Interface: eth1
MII Status: up
Link Failure Count: 0
Permanent HW addr: 00:24:e8:61:5e:81
Aggregator ID: 2


Reference:
http://www.dbaglobe.com/2009/09/how-to-linux-ethernet-bonding.html

Thursday, October 1, 2009

Useful query to find out Oracle Licensing Option usage

Below SQL statement is useful to comply Oracle licensing. It also as a useful tools to check whether specific option has been used before it can be safely removed/deinstalled.


SQL> desc DBA_FEATURE_USAGE_STATISTICS
Name Null? Type
----------------------------------------- -------- -----------------
DBID NOT NULL NUMBER
NAME NOT NULL VARCHAR2(64)
VERSION NOT NULL VARCHAR2(17)
DETECTED_USAGES NOT NULL NUMBER
TOTAL_SAMPLES NOT NULL NUMBER
CURRENTLY_USED VARCHAR2(5)
FIRST_USAGE_DATE DATE
LAST_USAGE_DATE DATE
AUX_COUNT NUMBER
FEATURE_INFO CLOB
LAST_SAMPLE_DATE DATE
LAST_SAMPLE_PERIOD NUMBER
SAMPLE_INTERVAL NUMBER
DESCRIPTION VARCHAR2(128)


SQL> select name,last_usage_date from dba_feature_usage_statistics
2 where detected_usages>0 order by 1;

NAME LAST_USAG
------------------------------------------------------- ---------
Audit Options 23-SEP-09
Automatic Maintenance - Optimizer Statistics Gathering 23-SEP-09
Automatic Maintenance - SQL Tuning Advisor 23-SEP-09
Automatic Maintenance - Space Advisor 23-SEP-09
Automatic Memory Tuning 23-SEP-09
Automatic SQL Tuning Advisor 25-JUN-09
Automatic Segment Advisor 25-JUN-09
Automatic Segment Space Management (system) 23-SEP-09
Automatic Segment Space Management (user) 23-SEP-09
Automatic Undo Management 23-SEP-09
Automatic Workload Repository 11-MAY-09

NAME LAST_USAG
------------------------------------------------------- ---------
Backup BZIP2 Compression 23-SEP-09
Character Set 23-SEP-09
Client Identifier 14-MAR-09
Dynamic SGA 22-MAR-09
EM Database Control 11-MAY-09
Extensibility 23-SEP-09
LOB 23-SEP-09
Locally Managed Tablespaces (system) 23-SEP-09
Locally Managed Tablespaces (user) 23-SEP-09
Materialized Views (User) 23-SEP-09
Object 23-SEP-09

NAME LAST_USAG
------------------------------------------------------- ---------
Oracle Managed Files 23-SEP-09
Parallel SQL DDL Execution 14-MAR-09
Partitioning (system) 23-SEP-09
RMAN - Disk Backup 23-SEP-09
Recovery Area 23-SEP-09
Recovery Manager (RMAN) 23-SEP-09
Resource Manager 20-MAY-09
Restore Point 23-SEP-09
Segment Advisor 30-AUG-09
Server Parameter File 23-SEP-09
Services 23-SEP-09

NAME LAST_USAG
------------------------------------------------------- ---------
XDB 23-SEP-09