2018-02-20

Configuring Disk Devices Manually for Oracle ASM (without ASMLib)

Environment: Oracle Linux Server release 7.3 on Oracle VM 3.4.3

In Oracle VM Manager, edit VM and add a new Virtual Disk, e.g.

 

In the VM as root, see the new disk:

 

lsblk

NAME    MAJ:MIN RM  SIZE RO TYPE MOUNTPOINT

xvda    202:0    0   30G  0 disk

├─xvda1 202:1    0  512M  0 part /boot

├─xvda2 202:2    0    8G  0 part /

├─xvda3 202:3    0    4G  0 part /tmp

├─xvda4 202:4    0    1K  0 part

├─xvda5 202:5    0  1.5G  0 part /home

└─xvda6 202:6    0   16G  0 part [SWAP]

xvdb    202:16   0   50G  0 disk

└─xvdb1 202:17   0   50G  0 part /opt/oracle

xvdc    202:32   0   10G  0 disk

└─xvdc1 202:33   0   10G  0 part /opt/netbackup

xvdd    202:48   0  800G  0 disk

└─xvdd1 202:49   0  800G  0 part /data001/oradata/CDRDEV

xvde    202:64   0  200G  0 disk

└─xvde1 202:65   0  200G  0 part /data001/fast_recovery_area

xvdf    202:80   0  800G  0 disk

└─xvdf1 202:81   0  800G  0 part /data001/oradata/CDRSIT

xvdg    202:96   0  100G  0 disk

└─xvdg1 202:97   0  100G  0 part /data001/archivelogs

xvdh    202:112  0  800G  0 disk

└─xvdh1 202:113  0  800G  0 part /data001/oradata/CDRDEV1

xvdi    202:128  0  800G  0 disk

└─xvdi1 202:129  0  800G  0 part /data001/oradata/CDRSIT1

xvdj    202:144  0  800G  0 disk

└─xvdj1 202:145  0  800G  0 part

 

For persistent device naming, we can configure ASMLIB or set udev rules. We need to use a partition UUID (not a filesystem UUID) in udev rules. For this, use parted to create a GPT partition.

parted /dev/xvdj

mktable gpt

 

to create a GPT partition:

mkpart PART-LABEL START END

e.g.

mkpart asmpart1 0% 100%

(parted) print

Model: Xen Virtual Block Device (xvd)

Disk /dev/xvdj: 859GB

Sector size (logical/physical): 512B/512B

Partition Table: gpt

Disk Flags:

 

Number  Start   End    Size   File system  Name      Flags

 1      1049kB  859GB  859GB               asmpart1

 

udevadm info --query=property /dev/xvdj1

DEVLINKS=/dev/disk/by-partlabel/asmpart1 /dev/disk/by-partuuid/48940eb5-f6fa-4558-aeba-b12b81199e27 /dev/oracleasm/disk1

DEVNAME=/dev/xvdj1

DEVPATH=/devices/vbd-51856/block/xvdj/xvdj1

DEVTYPE=partition

ID_FS_TYPE=oracleasm

ID_FS_USAGE=filesystem

ID_PART_ENTRY_DISK=202:144

ID_PART_ENTRY_NAME=asmpart1

ID_PART_ENTRY_NUMBER=1

ID_PART_ENTRY_OFFSET=2048

ID_PART_ENTRY_SCHEME=gpt

ID_PART_ENTRY_SIZE=1677717504

ID_PART_ENTRY_TYPE=ebd0a0a2-b9e5-4433-87c0-68b6b72699c7

ID_PART_ENTRY_UUID=48940eb5-f6fa-4558-aeba-b12b81199e27

ID_PART_TABLE_TYPE=gpt

MAJOR=202

MINOR=145

SUBSYSTEM=block

TAGS=:systemd:

USEC_INITIALIZED=800372237892

 

 

vi /etc/udev/rules.d/99-oracle-asmdevices.rules

KERNEL=="xvd??", ENV{ID_PART_ENTRY_UUID}=="48940eb5-f6fa-4558-aeba-b12b81199e27", SYMLINK+="oracleasm/disk1", OWNER="oracle", GROUP="dba", MODE="0660"

 

To implement the new rules:

udevadm trigger

 

ls -la /dev/oracleasm/disk1

lrwxrwxrwx. 1 root root 8 Feb 15 12:32 /dev/oracleasm/disk1 -> ../xvdj1

 

ls -la /dev/xvd*

brw-rw----. 1 root   disk 202,   0 Feb 15 12:32 /dev/xvda

brw-rw----. 1 root   disk 202,   1 Feb 15 12:32 /dev/xvda1

brw-rw----. 1 root   disk 202,   2 Feb 15 12:32 /dev/xvda2

brw-rw----. 1 root   disk 202,   3 Feb 15 12:32 /dev/xvda3

brw-rw----. 1 root   disk 202,   4 Feb 15 12:32 /dev/xvda4

brw-rw----. 1 root   disk 202,   5 Feb 15 12:32 /dev/xvda5

brw-rw----. 1 root   disk 202,   6 Feb 15 12:32 /dev/xvda6

brw-rw----. 1 root   disk 202,  16 Feb 15 12:32 /dev/xvdb

brw-rw----. 1 root   disk 202,  17 Feb 15 12:32 /dev/xvdb1

brw-rw----. 1 root   disk 202,  32 Feb 15 12:32 /dev/xvdc

brw-rw----. 1 root   disk 202,  33 Feb 15 12:32 /dev/xvdc1

brw-rw----. 1 root   disk 202,  48 Feb 15 12:32 /dev/xvdd

brw-rw----. 1 root   disk 202,  49 Feb 15 12:32 /dev/xvdd1

brw-rw----. 1 root   disk 202,  64 Feb 15 12:32 /dev/xvde

brw-rw----. 1 root   disk 202,  65 Feb 15 12:32 /dev/xvde1

brw-rw----. 1 root   disk 202,  80 Feb 15 12:32 /dev/xvdf

brw-rw----. 1 root   disk 202,  81 Feb 15 12:32 /dev/xvdf1

brw-rw----. 1 root   disk 202,  96 Feb 15 12:32 /dev/xvdg

brw-rw----. 1 root   disk 202,  97 Feb 15 12:32 /dev/xvdg1

brw-rw----. 1 root   disk 202, 112 Feb 15 12:32 /dev/xvdh

brw-rw----. 1 root   disk 202, 113 Feb 15 12:32 /dev/xvdh1

brw-rw----. 1 root   disk 202, 128 Feb 15 12:32 /dev/xvdi

brw-rw----. 1 root   disk 202, 129 Feb 15 12:32 /dev/xvdi1

brw-rw----. 1 root   disk 202, 144 Feb 15 12:32 /dev/xvdj

brw-rw----. 1 oracle dba  202, 145 Feb 15 12:32 /dev/xvdj1

 

to see all disks and partitions:

lsblk -o name,mountpoint,label,size,uuid,partlabel,partuuid,serial,wwn,fstype

 

Alternatively, you can use a partition label name ID_PART_ENTRY_NAME:

cat /etc/udev/rules.d/99-oracle-asmdevices.rules

KERNEL=="xvd??", ENV{ID_PART_ENTRY_UUID}=="48940eb5-f6fa-4558-aeba-b12b81199e27", SYMLINK+="oracleasm/disk1", OWNER="oracle", GROUP="dba", MODE="0660"

KERNEL=="xvd??", ENV{ID_PART_ENTRY_NAME}=="asmpart2", SYMLINK+="oracleasm/disk2", OWNER="oracle", GROUP="dba", MODE="0660"

 

lsblk -o name,partlabel,partuuid

NAME    PARTLABEL PARTUUID

xvda

├─xvda1

├─xvda2

├─xvda3

├─xvda4

├─xvda5

└─xvda6

xvdb

└─xvdb1

xvdc

└─xvdc1

xvdd

└─xvdd1

xvde

└─xvde1

xvdf

└─xvdf1

xvdg

└─xvdg1

xvdh

└─xvdh1

xvdi

└─xvdi1

xvdj

└─xvdj1 asmpart1  48940eb5-f6fa-4558-aeba-b12b81199e27

xvdk

└─xvdk1 asmpart2  0b8ba20c-a10d-4564-b582-0692fd9657e8

 

 

 

 

 

2017-10-16

Huge Pages on Oracle Linux

Environment

Oracle Linux Server release 7.3
Oracle database 11.2, 12.1

Quick checking

# cat /proc/sys/vm/nr_hugepages
2054
or
# cat /proc/meminfo | grep Huge
AnonHugePages:         0 kB
HugePages_Total:    2054
HugePages_Free:      546
HugePages_Rsvd:      542
HugePages_Surp:        0
Hugepagesize:       2048 kB

checking if transparent huge pages are disabled

# cat /sys/kernel/mm/transparent_hugepage/enabled

always madvise [never]

Viewing database 12.1 alert log
**********************************************************************
Mon Oct 16 12:28:54 2017
Dump of system resources acquired for SHARED GLOBAL AREA (SGA)
Mon Oct 16 12:28:54 2017
 Per process system memlock (soft) limit = 128G
Mon Oct 16 12:28:54 2017
 Expected per process system memlock (soft) limit to lock
 SHARED GLOBAL AREA (SGA) into memory: 2050M
Mon Oct 16 12:28:54 2017
 Available system pagesizes:
  4K, 2048K
Mon Oct 16 12:28:54 2017
 Supported system pagesize(s):
Mon Oct 16 12:28:54 2017
  PAGESIZE  AVAILABLE_PAGES  EXPECTED_PAGES  ALLOCATED_PAGES  ERROR(s)
        4K       Configured               3               3        NONE
     2048K             2054            1025            1025        NONE
**********************************************************************

Viewing database 11.2 alert log
************************ Large Pages Information *******************
Per process system memlock (soft) limit = 128 GB

Total Shared Global Region in Large Pages = 2050 MB (100%)

Large Pages used by this instance: 1025 (2050 MB)
Large Pages unused system wide = 4 (8192 KB)
Large Pages configured system wide = 2054 (4108 MB)
Large Page size = 2048 KB
********************************************************************
If number of Huge pages is less than required, than SGA will use both page types. Database 12.1 aler.log:
  PAGESIZE  AVAILABLE_PAGES  EXPECTED_PAGES  ALLOCATED_PAGES  ERROR(s)
        4K       Configured               3          187704        NONE
     2048K              659            1025             658        NONE

Simplified setup

1. Have the memlock user limit set in /etc/security/limits.conf file. Set the value (in KB) slightly smaller than total RAM (90%) , at least it must be bigger than HugePages size.
*   soft   memlock    14680064
*   hard   memlock    14680064
Atentu! If you use systemd to auto startup databases, systemd ignores limits.conf. You need to add the following to a service unit file
[Service]
LimitMEMLOCK=infinity
LimitNOFILE=65535

2. Get the required number of pages from alert.log (EXPECTED_PAGES) or by running Oracle script hugepages_settings.sh (Doc ID 401749.1)

3. edit vm.nr_hugepages in /etc/sysctl.conf as root
4. reboot Linux

Proper setup and more info
HugePages on Oracle Linux 64-bit (Doc ID 361468.1)
HugePages on Linux: What It Is... and What It Is Not... (Doc ID 361323.1)
Oracle Linux: Shell Script to Calculate Values Recommended Linux HugePages / HugeTLB Configuration (Doc ID 401749.1)



2017-10-06

Automating Oracle database Shutdown and Startup by systemd

Environment

Oracle Linux Server release 7.3 (or Redhat 7.3)
Oracle database 12.1.0.2

Documentation

MOSC Oracle Doc ID 2229679.1, Doc ID 2049901.1
Overview of systemd for RHEL 7

Automating by systemd

alternatives are Oracle Restart or SysV init ( /etc/init.d in older Linux).
Service Unit file typically has extension .service and stored in
/usr/lib/systemd/system
/etc/systemd/system
/usr/lib/systemd/user
/etc/systemd/user
For more details read Chapter 9. Managing Services with systemd

Create or edit a service unit file:
cd /etc/systemd/system
vi oracle_database.service
[Unit]
Description=The Oracle Database Service
After=network.target

[Service]
Type=forking
RemainAfterExit=yes
KillMode=none
TimeoutStopSec=10min
# memlock limit is needed for SGA to use HugePages
LimitMEMLOCK=infinity
LimitNOFILE=65535

User=oracle
Group=oinstall
Please use absolute path here
# ExecStart=$ORACLE_HOME/bin/dbstart $ORACLE_HOME &
# First argument of dbstart is used to bring up Listener
ExecStart=/opt/oracle/product/12.1.0/se2_1/bin/dbstart /opt/oracle/product/12.1.0/se2_1 &
ExecStop=/opt/oracle/product/12.1.0/se2_1/bin/dbshut /opt/oracle/product/12.1.0/se2_1
Restart=no

[Install]
# Puts wants directive for the other units in the relationship

WantedBy=default.target

First argument of dbstart/dbshut is used to bring up/shutdown Oracle Listener. This script will start all databases listed in the /etc/oratab file whose third field is a "Y". If you use ASM or cluster services, read more in dbstart description.

Huge Pages
systemd ignores /etc/security/limits.conf. If HugePages are configured, you need to use LimitMEMLOCK and LimitNOFILE, otherwise SGA will use small pages and database alert log will show:
 Increase per process memlock (soft) limit to at least 2050MB to lock 100% of SHARED GLOBAL AREA (SGA) pages into physical memory

TimeoutStopSec=   Configures the time to wait for stop. If a service is asked to stop, but does not terminate in the specified time, it will be terminated forcibly via SIGTERM, and after another timeout of equal duration with SIGKILL (see KillMode= in systemd.kill(5)). Takes a unit-less value in seconds, or a time span value such as "5min 20s". Pass "infinity" to disable the timeout logic. Defaults to DefaultTimeoutStopSec= from the manager configuration file (see systemd-system.conf(5)).
For more info use
man systemd.service

reload systemd and enable the service:
systemctl daemon-reload
systemctl enable oracle_database.service
list services:
systemctl list-unit-files --type service|grep oracle
Start the service and check its status
systemctl start oracle_database.service
systemctl status oracle_database.service


2017-08-09

Migrating Oracle VM from version 2.2 to 3.4

Migrating a virtual machine from Oracle VM Server 2.2 to Oracle VM Server 3.4

Environment

Oracle VM server release 2.2.2
Oracle VM server release 3.4.3
VMs: Redhat/Oracle Linux 5,6,7.

Importing VM2 to VM3 Template

Shutdown VM in v2 environment.
Locate System.img and vm.cfg, e.g.
pwd
/var/ovs/mount/B0CAFC70A7DB489C973D9819396703F7/running_pool/184_dtoocl10
ll
total 69206016
-rw------- 1 root root 53687091200 Feb 10  2012 OPTORACLE.img
-rw------- 1 root root 17179869184 Feb 10  2012 System.img
-rw------- 1 root root         778 Jul  5 11:24 vm.cfg
-rw------- 1 root root         666 Feb 10  2012 vm.cfg.orig

Backup vm.cfg
cp vm.cfg vm.cfg.backup

Modify vm.cfg and remove all disks except System.img. Other disks will be imported separately, I just don’t want them to be included in a template, to keep the template small.
disk = ['file:/var/ovs/mount/B0CAFC70A7DB489C973D9819396703F7/running_pool/184_dtoocl10/System.img,xvda,w',
'file:/var/ovs/mount/B0CAFC70A7DB489C973D9819396703F7/running_pool/184_dtoocl10/OPTORACLE.img,xvdb,w',
'file:/var/ovs/mount/B0CAFC70A7DB489C973D9819396703F7/sharedDisk/PIRUAT.img,xvdc,w!',
'file:/var/ovs/mount/B0CAFC70A7DB489C973D9819396703F7/sharedDisk/PIRBUAT_ARCHIVE.img,xvdd,w!',
]

Check/Kill IPtables
Share the current directory
python -m SimpleHTTPServer 80
Serving HTTP on 0.0.0.0 port 80 ...


Go to VM3 Manager and Import VM Template. All disks mentioned in vm.cfg must be included in the URLs if you didn’t delete them from vm.cfg.



Rename the template from System.img to something like dtoocl10_template_vm2, if migrating more than one VM otherwise you’ll have multiple templates with the same name.
Rename the Virtual Disk from System.img to something like template_dtoocl10_System.img
On VM2 hypervisor:
Kill SimpleHTTPServer
Restore vm.cfg
cp vm.cfg.backup vm.cfg
cp: overwrite `vm.cfg'? y

Locate other virtual disks, likely in “sharedDisk”, e.g.
/var/ovs/mount/B0CAFC70A7DB489C973D9819396703F7/sharedDisk

Go to that directory and share it
python -m SimpleHTTPServer 80
Serving HTTP on 0.0.0.0 port 80 ...

Go to VM3 Manager and Import Virtual Disk.
Rename the virtual disk if needed.
Kill SimpleHTTPServer

Creating VM


Rename the VM
Set Operating System
Setup network at Networks tab
Add other virtual disks at Disks tab, e.g. /opt/oracle or DB disk.
Rename SYSTEM virtual disk, and other disks if needed.
Modify vm.cfg file with extra='S' to boot in Single user mode.

Start the VM in Single user mode.
Launch Console.
Check that the disks are mounted
Change IP address in /etc/hosts
Change IP address and Gateway in /etc/sysconfig/network-scripts/ifcfg-eth0 (Oracle Linux 5 in this case)
Shutdown the VM
Update DNS entry
Remove extra=’S’ from vm.cfg
Start the VM
SSH to the VM and check DB and Apps. (You might need ipconfig /flushdns)

Cleaning up

Remove imported templates.






2017-08-07

Oracle EM 12c - reporting filesystem usage across all hosts

Environment:
Oracle Enterprise Manager 12c Cloud Control.

in SYSMAN schema:

select target_name, mountpoint,
round((freeb/1073741824),2) as "Free, GiB",
round((sizeb/1073741824),2) as "Size, GiB",
round((usedb/1073741824),2) as "Used, GiB",
round((((sizeb-freeb)/sizeb)*100),2) as "Used, %"
from MGMT$STORAGE_REPORT_LOCALFS
where filesystem_type not in ('iso9660','devtmpfs') -- excluding some filesystems
order by "Used, %" desc

Attention! (sizeb-freeb)/sizeb is not the same as usedb/sizeb
Use% of df command shows the same as (sizeb-freeb)/sizeb

Creating the report:





2017-05-15

redeploying OEM agent after cloning VM

Environment:

Oracle Linux 7.3, OEM agent 12.1.0.5.0

Solution:

prepare or re-use (if it was used for deployment) a response file, e.g. agent.rsp.
If reusing the existing file, you have to add OMS_HOST and EM_UPLOAD_PORT to the file.
If the original agent wast deployed by Pull method, you can get EM_UPLOAD_PORT from AgentPull.sh:
grep -i omsPort= AgentPull.sh
omsPort=4900

so an example of the response file:
OMS_HOST=oms_host_name
EM_UPLOAD_PORT=4900
LOGIN_USER=sysman
LOGIN_PASSWORD=pasvorto1
PLATFORM="Linux x86-64"
VERSION=12.1.0.5.0
AGENT_REGISTRATION_PASSWORD=pasvorto2

Delete files from AGENT_BASE_DIR/agent_inst
cd /opt/oracle/product/agent/agent_inst
rm -rf *

and run agentDeploy.sh script to redeloy the agent:
cd /opt/oracle/product/agent
./agentDeploy.sh AGENT_BASE_DIR=/opt/oracle/product/agent RESPONSE_FILE=/opt/oracle/product/agent.rsp

Validating the OMS_HOST & EM_UPLOAD_PORT
Executing command : /opt/oracle/product/agent/core/12.1.0.5.0/jdk/bin/java -classpath /opt/oracle/product/agent/core/12.1.0.5.0/jlib/agentInstaller.jar:/opt/oracle/product/agent/core/12.1.0.5.0/oui/jlib/OraInstaller.jar oracle.sysman.agent.installer.AgentInstaller /opt/oracle/product/agent/core/12.1.0.5.0 /opt/oracle/product/agent /opt/oracle/product/agent AGENT_BASE_DIR=/opt/oracle/product/agent RESPONSE_FILE=/opt/oracle/product/agent.rsp -prereq

Validating oms host & port with url: http://dtooem2:4900/empbs/genwallet
Validating oms host & port with url: https://dtooem2:4900/empbs/genwallet
Return status:3-oms https port is passed
Unzipping the agentcoreimage.zip to /opt/oracle/product/agent ....
plugin.zip
Executing command : /opt/oracle/product/agent/unzip -o /opt/oracle/product/agent/plugin.zip -d /opt/oracle/product/agent
Successfully unzipped /opt/oracle/product/agent/plugin.zip to /opt/oracle/product/agent !
Executing command : /opt/oracle/product/agent/core/12.1.0.5.0/jdk/bin/java -classpath /opt/oracle/product/agent/core/12.1.0.5.0/oui/jlib/OraInstaller.jar:/opt/oracle/product/agent/core/12.1.0.5.0/oui/jlib/xmlparserv2.jar:/opt/oracle/product/agent/core/12.1.0.5.0/oui/jlib/srvm.jar:/opt/oracle/product/agent/core/12.1.0.5.0/oui/jlib/emCfg.jar:/opt/oracle/product/agent/core/12.1.0.5.0/jlib/agentInstaller.jar:/opt/oracle/product/agent/core/12.1.0.5.0/oui/jlib/share.jar oracle.sysman.agent.installer.AgentInstaller /opt/oracle/product/agent/core/12.1.0.5.0 /opt/oracle/product/agent /opt/oracle/product/agent /opt/oracle/product/agent/agent_inst AGENT_BASE_DIR=/opt/oracle/product/agent


Executing agent install prereqs...
Executing command: /opt/oracle/product/agent/core/12.1.0.5.0/oui/bin/runInstaller -debug -ignoreSysPrereqs   -prereqchecker -silent -ignoreSysPrereqs -waitForCompletion  -prereqlogloc /opt/oracle/product/agent/core/12.1.0.5.0/cfgtoollogs/agentDeploy -entryPoint oracle.sysman.top.agent_Complete -detailedExitCodes PREREQ_CONFIG_LOCATION=/opt/oracle/product/agent/core/12.1.0.5.0/prereqs  -J-DAGENT_BASE_DIR=/opt/oracle/product/agent

Agent install prereqs completed successfully

Cloning the agent home...
Executing command: /opt/oracle/product/agent/core/12.1.0.5.0/oui/bin/runInstaller -debug -ignoreSysPrereqs   -clone -forceClone -silent -waitForCompletion -nowait ORACLE_HOME=/opt/oracle/product/agent/core/12.1.0.5.0 -responseFile /opt/oracle/product/agent.rsp  AGENT_BASE_DIR=/opt/oracle/product/agent AGENT_BASE_DIR=/opt/oracle/product/agent RESPONSE_FILE=/opt/oracle/product/agent.rsp -noconfig  ORACLE_HOME_NAME=agent12c2 -force b_noUpgrade=true AGENT_PORT=-1 EMCTLCFG_MODE=NONE

Cloning of agent home completed successfully

Attaching sbin home...
Executing command: /opt/oracle/product/agent/core/12.1.0.5.0/oui/bin/runInstaller -debug -ignoreSysPrereqs   -attachHome -waitForCompletion -nowait ORACLE_HOME=/opt/oracle/product/agent/sbin ORACLE_HOME_NAME=sbin12c2 -force

Attach home for sbin home completed successfully.

Updating home dependencies...
Executing command: /opt/oracle/product/agent/core/12.1.0.5.0/oui/bin/runInstaller -debug -ignoreSysPrereqs   -updateHomeDeps -waitForCompletion HOME_DEPENDENCY_LIST={/opt/oracle/product/agent/sbin:/opt/oracle/product/agent/core/12.1.0.5.0} -invPtrLoc /opt/oracle/product/agent/core/12.1.0.5.0/oraInst.loc -force

Update home dependency completed successfully.
Executing command: /opt/oracle/product/agent/core/12.1.0.5.0/oui/bin/runConfig.sh ORACLE_HOME=/opt/oracle/product/agent/core/12.1.0.5.0 RESPONSE_FILE=/opt/oracle/product/agent/core/12.1.0.5.0/agent.rsp ACTION=configure MODE=perform COMPONENT_XML={oracle.sysman.top.agent.11_1_0_1_0.xml} RERUN=true
Agent Configuration completed successfully

The following configuration scripts need to be executed as the "root" user.
#!/bin/sh
#Root script to run
 /opt/oracle/product/agent/core/12.1.0.5.0/root.sh
To execute the configuration scripts:
1. Open a terminal window
2. Log in as "root"
3. Run the scripts
Agent Deployment Successful.
Agent deployment log location: /opt/oracle/product/agent/core/12.1.0.5.0/cfgtoollogs/agentDeploy/agentDeploy_2017-05-12_15-28-29-PM.log
Agent deployment completed successfully.

Some observations:

agentDeploy.sh script runs with ORACLE_HOME_NAME=sbin12c2, but old default name sbin12c1 remains in central inventory. You can see it in oraInventory/ContentsXML/inventory.xml or by "opatch lsinv -all".


2017-05-04

Indexing NULL values

Environment: 

Oracle database 11.2.0.4

Solution:

Create an index and add a constant to the end of the index so NULL values are stored.

For the following statement and regular index on PAYMENT_CLASSIFICATION_ID
select * from CDR.F_PAYMENT_ITEM where PAYMENT_CLASSIFICATION_ID is null;

Plan hash value: 2353806608
------------------------------------------------------------------------------------
| Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                |   434 | 38626 |   423K  (1)| 01:24:38 |
|*  1 |  TABLE ACCESS FULL| F_PAYMENT_ITEM |   434 | 38626 |   423K  (1)| 01:24:38 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
    1 - filter("PAYMENT_CLASSIFICATION_ID" IS NULL)


DROP INDEX CDR.FPI_PAYMNT_CLS_ID;

CREATE INDEX CDR.FPI_PAYMNT_CLS_ID ON CDR.F_PAYMENT_ITEM
(PAYMENT_CLASSIFICATION_ID,1);

Plan with the new index:
-------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                   |   434 | 38626 |    82   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| F_PAYMENT_ITEM    |   434 | 38626 |    82   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | FPI_PAYMNT_CLS_ID |   434 |       |     5   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("PAYMENT_CLASSIFICATION_ID" IS NULL)


Index type of the new index is FUNCTION-BASED NORMAL (from DBA_INDEXES).
One of disadvantages of Function-Based Indexes, according to Database Advanced Application Developer's Guide, chapter 4, Using Indexes in Database Applications:
- The database does not use function-based indexes when doing OR expansion.

but for this particular index, it works fine:
select * from CDR.F_PAYMENT_ITEM where PAYMENT_CLASSIFICATION_ID in (122,1);

--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |  2756 |   239K|   497   (0)| 00:00:06 |
|   1 |  INLIST ITERATOR             |                   |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| F_PAYMENT_ITEM    |  2756 |   239K|   497   (0)| 00:00:06 |
|*  3 |    INDEX RANGE SCAN          | FPI_PAYMNT_CLS_ID |  2756 |       |    11   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("PAYMENT_CLASSIFICATION_ID"=1 OR "PAYMENT_CLASSIFICATION_ID"=122)