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)

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

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.

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.

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

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