Linux Best Practices

Hello Guys,

Let’s investigate this issue on Exadata At Customer – or Exadata Machine:

[[email protected] ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon May 18 16:15:50 2020
Version 19.7.0.0.0
Copyright (c) 1982, 2020, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile='/home/oracle/init.ora';
ORA-27154: post/wait create failed
ORA-27300: OS system dependent operation:semget failed with status: 28
ORA-27301: OS failure message: No space left on device
ORA-27302: failure occurred at: sskgpbitsper
SQL> quit
Disconnected

The solutions is:

Reduce parameters processes, sessions, transactions and open_cursors for all databases. Follow the maximum used on each database to avoid to use much more than the database needs – and yes put some extra value since your database can grow. Update sysctl.conf parameters for your linux accommodate all databases. See the steps below:

=================================================================11) Check results for max limits on database used
=================================================================
column rname	format a30 	    heading 'Resource Name';
column curu	format 999,999,990	heading 'Current|Utilization' just c;
column maxu	format 999,999,990	heading 'Maximum|Utilization' just c;
column inita    format a10      heading 'Initial|Allocation'  just c;
column lim      format a10      heading 'Limit'               just r;
set lines 200
select inst_id, resource_name         rname
     , current_utilization   curu
     , max_utilization       maxu
     , initial_allocation    inita
     , limit_value           lim
from  gv$resource_limit  ;
select inst_id, resource_name         rname
     , current_utilization   curu
     , max_utilization       maxu
     , initial_allocation    inita
     , limit_value           lim
from  gv$resource_limit
WHERE resource_name in ('processes','sessions');
SQL> set lines 200
SQL> SQL> select inst_id, resource_name         rname
  2       , current_utilization   curu
  3       , max_utilization       maxu
  4       , initial_allocation    inita
  5       , limit_value           lim
  6  from  gv$resource_limit
  7
SQL> /
                                            Current      Maximum     Initial
   INST_ID Resource Name                  Utilization  Utilization  Allocation      Limit
---------- ------------------------------ ------------ ------------ ---------- ----------
         1 processes                               165          186       2048       2048
         1 sessions                                170          208       3100       3100
         1 enqueue_locks                            76           98      36542      36542
         1 enqueue_resources                        62           87      14684  UNLIMITED
         1 ges_procs                               153          173       2051       2051
         1 ges_ress                             11,663       87,988      69331  UNLIMITED
         1 ges_locks                               343          547      99571  UNLIMITED
         1 ges_cache_ress                       12,072       16,162          0  UNLIMITED
         1 ges_reg_msgs                            176        4,119       6126  UNLIMITED
         1 ges_big_msgs                             39          360       3826  UNLIMITED
                        0            0       1000       1000
         1 gcs_resources                        47,386       47,460     810054  UNLIMITED
         1 gcs_shadows                          33,188       34,349     810054  UNLIMITED
         1 smartio_overhead_memory             855,160    1,026,192          0  UNLIMITED
         1 smartio_buffer_memory               343,416    1,486,280          0  UNLIMITED
         1 smartio_metadata_memory                   0           56          0  UNLIMITED
         1 smartio_sessions                          3            4          0  UNLIMITED
         1 dml_locks                                 0           21      13640  UNLIMITED
         1 temporary_table_locks                     0            5  UNLIMITED  UNLIMITED
         1 transactions                              0            5       3410  UNLIMITED
.....
108 rows selected.
SQL>

Oracle suggested:

SEMMNI should be increased to accomodate more semaphores.
1. Query the current semaphore values in the kernel
     # /sbin/sysctl -a | grep sem
2. Modify SEMMNI value in the /etc/sysctl.conf.
From
kernel.sem = 250 32000 100 128
To
kernel.sem = 250 32000 100 200
3. # /sbin/sysctl -p

What worked for me:

### ANDRE ROCHA

# kernel.sem = 1024 60000 1024 256 -> original

kernel.sem = 1512 1200000 1512 1024

My original recipe:

=================================================================
List of best practices: ANDRE ROCHA
=================================================================1) Configure huge pages - Doc ID 361323.1  401749.1
2) Disable Transparent HugePages - Doc ID 1557478.1
3) Check if system using hugepage have this parameter USE_LARGE_PAGES enabled Doc ID 1392497.1
4) Check swap is set correct and swapiness - Doc ID 2240180.1 2404462.1  - vm.swappiness=10
5) Network Considerations - Doc ID 811306.1
6) Collect data for sar output and print graph to get swap memory results
7) About async IO, please check on aio.max-nr and fs.aio-nr.
8) Revise Kernel parameters with best practices/recommendations
9) Set/check kernel parameters can affect SGA size and shared pool
10) Check results of cluvfy for packages, kernel limits and general check results
14) Appendice of list of commands to be executed/get results
https://sarchart.weebly.com/howto.html
=================================================================Linux Kernel Lowmem Pressure Issues and Related Kernel Structures (Doc ID 452326.1)
ALERT: Disable Transparent HugePages on SLES11, RHEL6, RHEL7, OL6, OL7, and UEK2 and above (Doc ID 1557478.1)
HugePages on Linux: What It Is... and What It Is Not... (Doc ID 361323.1)
System Hangs Due To Process Kswapd0 (Doc ID 2240180.1)
Why is SWAP being used instead of available physical memory? (Doc ID 2404462.1)
https://access.redhat.com/documentation/en-us/red_hat_enterprise_linux/6/html/performance_tuning_guide/s-memory-tunables
A low swappiness value is recommended for database workloads. For example, for Oracle databases,
Red Hat recommends a swappiness value of 10. vm.swappiness=10
Top 5 Issues That Cause Node Reboots or Evictions or Unexpected Recycle of CRS (Doc ID 1367153.1)
If the platform is Linux, set up hugepages and set kernel parameter vm.min_free_kbytes to reserve 512MB.
Setting hugepages is probably the single most important thing to do on Linux. Note that memory_target can not
be set when using hugepages.
ALERT: Disable Transparent HugePages on SLES11, RHEL6, RHEL7, OL6, OL7, and UEK2 and above (Doc ID 1557478.1)
Because Transparent HugePages are known to cause unexpected node reboots and performance problems with RAC,
Oracle strongly advises to disable the use of Transparent HugePages. In addition, Transparent Hugepages may
cause problems even in a single-instance database environment with unexpected performance problems or delays.
As such, Oracle recommends disabling Transparent HugePages on all Database servers running Oracle.
Note: on UEK2 and above, check the existence of the /sys/kernel/mm/transparent_hugepage/ directory.
If this directory does not exist, then the transparent hugepage is removed from the kernel, so there is no need to
disable the transparent hugepage
For Oracle Linux 7, Oracle published MOS document 2066217.1
Oracle Linux 7 - How to disable Transparent HugePages for RHCK kernel?
Also, add the following line to /etc/default/grub file:
transparent_hugepage=never
cat /etc/grub.conf
title Oracle Linux Server (2.6.32-300.25.1.el6uek.x86_64)
        root (hd0,0)
        kernel /vmlinuz-2.6.32-300.25.1.el6uek.x86_64 ro root=LABEL=/ transparent_hugepage=never
        initrd /initramfs-2.6.32-300.25.1.el6uek.x86_64.img
=================================================================1) Configure huge pages - Doc ID 361323.1
=================================================================https://oracle-base.com/articles/linux/configuring-huge-pages-for-oracle-on-linux-64
Implement HugePages for the database instances.  HugePages provides a method to have larger page sizes and it will
lock the SGA into physical memory thus eliminating the need for system page table lookups for the SGA.
This is especially important on systems with high memory allocations due to eliminate the management overhead
associated with the such configurations.  HugePages is also recommended in environments where server stability
issues are evident – various spins/hangs, which are not attributed to Oracle Clusterware or other known OS issues.
To compute size, see Document 401749.1. For more information, see Document 361323.1. In 11gR1 and above, you must disable
AMM to use HugePages as directed in Document 749851.1.
$ grep Huge /proc/meminfo
AnonHugePages:         0 kB
HugePages_Total:       0
HugePages_Free:        0
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB
$
Execute hugepages_setting script:
$ ./hugepages_setting.sh
Recommended setting: vm.nr_hugepages = 305
$
Edit file and add the result for the script hugepages_settings.sh: vm.nr_hugepages
/etc/sysctl
Run the following command as the "root" user.
# sysctl -p
To be permanet, edit the "/etc/grub.conf" file, adding "hugepages=306" to the end of the kernel
line for the default kernel and reboot.
You can now see the HugePages have been created, but are currently not being used.
Check the HugePages information again.
$ grep Huge /proc/meminfo
AnonHugePages:         0 kB
HugePages_Total:     306
HugePages_Free:       98
HugePages_Rsvd:       93
HugePages_Surp:
Hugepagesize:       2048 kB
$
$
If you prefer, you can set these parameters to a value just below the size of physical memory of
the server. This way you can forget about it, unless you add more physical memory.
Update Oracle database
ALTER SYSTEM SET use_large_pages=only SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP;
=================================================================2) Setting Shell Limits for the Oracle User
=================================================================
https://docs.oracle.com/cd/E51773_01/doc.12105/e51150/dbinstall.htm
To improve the performance of the software, you must increase the shell limits for the oracle user.
Add the following lines to the /etc/security/limits.conf file:
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
If not already present, add the following lines to the /etc/pam.d/login file:
session required /lib64/security/pam_limits.so
session required pam_limits.so
Depending on the oracle user's default shell, you need to make changes to it. For the Bourne, Bash, or Korn shell, add the following lines to the bottom of the /etc/profile file:
if [ $USER = "oracle" ]; then
    if [ $SHELL = "/bin/ksh" ]; then
        ulimit -p 16384
        ulimit -n 65536
    else
        ulimit -u 16384 -n 65536
    fi
fi
Add the following entries into the "/etc/security/limits.conf" script or
"/etc/security/limits.d/99-grid-oracle-limits.conf" script, where the setting is at least the size
of the HugePages allocation in KB (HugePages * Hugepagesize).
In this case the value is 306*2048=626688.
* soft memlock 626688
* hard memlock 626688
=================================================================2) Disable Transparent HugePages - Doc ID 1557478.1
=================================================================
If you are running RedHat/OEL 6/7, SLES 11/12 or UEK2 kernels, be sure to disable Transparent HugePages to prevent
performance problems and Node/Instance evictions.  Our long standing recomendation of using general HugePages still stands
(THP is a different implementation than general HugePages).  See Document 1557478.1 for additional details.
You can check the current setting using the following command, which is displaying the default value of "enabled=[always]".
# cat /sys/kernel/mm/transparent_hugepage/enabled
[always] madvise never
For Oracle Linux 6 the preferred method to disable Transparent HugePages is to add "transparent_hugepage=never" to the kernel boot line in the "/boot/grub/grub.conf" file.
title Oracle Linux Server (2.6.39-400.24.1.el6uek.x86_64)
        root (hd0,0)
        kernel /vmlinuz-2.6.39-400.24.1.el6uek.x86_64 ro root=/dev/mapper/vg_ol6112-lv_root rd_NO_LUKS  KEYBOARDTYPE=pc KEYTABLE=uk
LANG=en_US.UTF-8 rd_NO_MD SYSFONT=latarcyrheb-sun16  rd_NO_DM rd_LVM_LV=vg_ol6112/lv_swap rd_LVM_LV=vg_ol6112/lv_root rhgb quiet numa=off
transparent_hugepage=never
        initrd /initramfs-2.6.39-400.24.1.el6uek.x86_64.img
The server must be rebooted for this to take effect.
Alternatively, add the following lines into the "/etc/rc.local" file and reboot the server.
if test -f /sys/kernel/mm/transparent_hugepage/enabled; then
   echo never > /sys/kernel/mm/transparent_hugepage/enabled
fi
if test -f /sys/kernel/mm/transparent_hugepage/defrag; then
   echo never > /sys/kernel/mm/transparent_hugepage/defrag
fi
Whichever method you choose, remember to check the change has work after reboot.
# cat /sys/kernel/mm/transparent_hugepage/enabled
always madvise [never]
#
=================================================================3) Check if system using hugepage have this parameter USE_LARGE_PAGES enabled Doc ID 1392497.1
=================================================================show parameter USE_LARGE_PAGES
=================================================================4) Check swap is set correct and swapiness - Doc ID 2240180.1 2404462.1  - vm.swappiness=10
=================================================================
Free -h
Take a backup of the current sysctl.conf file to be used in case of rollback
cp /etc/sysctl.conf /root/sysctl.conf_TICKETNUMBER
Review current swappiness configuration
cat /proc/sys/vm/swappiness
60
Change the current in-memory setting for vm.swappiness
echo 10 > /proc/sys/vm/swappiness
Making Permanent the mv.swappiness
# echo 'vm.swappiness=10' >> /etc/sysctl.conf
Confirm the new swappiness value
 # cat /proc/sys/vm/swappiness
---Result 10.
=================================================================5) Network Considerations - Doc ID 811306.1
=================================================================
RAC and Oracle Clusterware Best Practices and Starter Kit (Linux) (Doc ID 811306.1)
As workload dictates, rmem_max and wmem_max kernel parameters should be increased beyond the default 256kb.  These values determine how much kernel buffer memory is allocated per socket opened for network reads and writes:
net.core.rmem_default=262144
net.core.rmem_max=4194304 (for 11g and all RDS implementations)
net.core.rmem_max=2097152 (for 10g)
net.core.wmem_default=262144
net.core.wmem_max=1048576 (with RDS use at least 2097152)
Changing Kernel Parameter Values - sysctl.conf for 19c
https://docs.oracle.com/en/database/oracle/oracle-database/19/ladbi/changing-kernel-parameter-values.html#GUID-FB0CC366-61C9-4AA2-9BE7-233EB6810A31
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 4294967295
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
=================================================================6) Collect data for sar output
=================================================================
### All Days available on SAR
DT=$(ls /var/log/sa/sa[0-9][0-9] | tr '\n' ' ' | sed 's/\/var\/log\/sa\/sa/ /g')
>/tmp/sar-$(hostname)-multiple.txt
for i in $DT; do
LC_ALL=C sar -A -f /var/log/sa/sa$i >> /tmp/sar-$(hostname)-multiple.txt
done
ls -l /tmp/sar-$(hostname)-multiple.txt
=================================================================7) About async IO, please check on aio.max-nr and fs.aio-nr
=================================================================
What value should kernel parameter AIO-MAX-NR be set to ? (Doc ID 2229798.1)
The minimum value suggested as per http://docs.oracle.com/database/121/LTDQI/toc.htm#BABFIFCGfor aio-max-nr is 1048576 .
the DOCS provide the minimum required for a good install and basic usage.
This is how we calculate aio-max-nr parameter value -
show parameter processes
aio-max-nr =no of process per DB * no of databases * 4096
After deriving the value using this formula , you should still monitor the /proc/sys/fs/aio-nr value and also monitor I/O
loading.
Points to know -
1) Number  of processes per database  is not background server process number, it's the number of background server
process + user process.
2) Parameter aio-max-nr is a limitation of aio-nr, no impact was reported if you set it too high. But we should still
monitor /proc/sys/fs/aio-nr value and tune this value.
cat /proc/sys/fs/aio-nr
=================================================================8) Revise Kernel parameters with best practices/recommendations
=================================================================https://docs.oracle.com/database/122/LADBI/app_manual.htm#LADBI7865
https://docs.oracle.com/en/database/oracle/oracle-database/19/cwlin/changing-kernel-parameter-values.html#GUID-FB0CC366-61C9-4AA2-9BE7-233EB6810A31
=================================================================
9) Set/check kernel parameters can affect SGA size
and shared pool
=================================================================
shmmax 64G 85% of 16G
kernel.shmmax 68719476736 kernel.shmmax=15032385536
need fit/affect SGA
kernel.shmall 4294967296=4G 40% 16G = 6,4G = 6871947673
need fit/affect Shared pool
=================================================================
10) Check results of cluvfy for packages, kernel limits and general check results
=================================================================
Download it
https://www.oracle.com/database/technologies/cvu-downloads.html
### All Days available on SAR
DT=$(ls /var/log/sa/sa[0-9][0-9] | tr '\n' ' ' | sed 's/\/var\/log\/sa\/sa/ /g')
>/tmp/sar-$(hostname)-multiple.txt
for i in $DT; do
LC_ALL=C sar -A -f /var/log/sa/sa$i >> /tmp/sar-$(hostname)-multiple.txt
done
ls -l /tmp/sar-$(hostname)-multiple.txt
=================================================================
General results/recommendations :
=================================================================
Kernel: Reduce shmmax to 85% of 16G
Kernel: increase shmall to 40% of 16G
Set hugepages: vm.nr_hugepages = 964
Swap is under the recommendation but sounds ok since was not using swap memory on it.
Next approach to continue to work on it: Investigate sga_target memory settings since
sga is using only 2G memory entire memory.
ex.
shmmax  64G 85% of 16G (total memoria)
kernel.shmmax 68719476736 kernel.shmmax=15032385536
need fit/affect SGA
kernel.shmall 4294967296=4G 40% 16G = 6,4G = 6871947673
need fit/affect Shared pool  (total memoria)
Oracle Exadata Database Machine Setup/Configuration Best Practices (Doc ID 1274318.1)
s.file-max = 6815744 512 x processes (for example 6815744 for 13312 processes)
kernel.shmmax = RAM times 0.5 (or higher at customer's discretion - see Note:567506.1)
Oracle Database (RDBMS) on Unix AIX,HP-UX,Linux,Mac OS X,Solaris,Tru64 Unix Operating Systems
Installation and Configuration Requirements Quick Reference (8.0.5 to 11.2) (Doc ID 169706.1)
Upon startup of Linux database get ORA-27102: out of memory Linux-X86_64 Error: 28:
No space left on device (Doc ID 301830.1)
What value should kernel parameter AIO-MAX-NR be set to ? (Doc ID 2229798.1)
TECH: Unix Semaphores and Shared Memory Explained (Doc ID 15566.1)
Exadata sysctl comments:
Maximum shared memory allowed is 85 percent of physical RAM
Maximum shared memory pages allowed are 85 percent of physical RAM divided by page size
kernel.sem = 1024 60000 1024 256
Check processes -> used to calculate aio-max-nr
show parameter memor
grep SwapTotal /proc/meminfo
=================================================================
11) Check results for max limits on database used
=================================================================
column rname	format a30 	    heading 'Resource Name';
column curu	    format 999,999,990	heading 'Current|Utilization' just c;
column maxu	    format 999,999,990	heading 'Maximum|Utilization' just c;
column inita    format a10      heading 'Initial|Allocation'  just c;
column lim      format a10      heading 'Limit'               just r;
column inst_id  format a10      heading 'Limit'               just r;
set lines 200
select inst_id, resource_name         rname
     , current_utilization   curu
     , max_utilization       maxu
     , initial_allocation    inita
     , limit_value           lim
from  gv$resource_limit  ;
select inst_id, resource_name         rname
     , current_utilization   curu
     , max_utilization       maxu
     , initial_allocation    inita
     , limit_value           lim
from  gv$resource_limit
WHERE resource_name in ('processes','sessions');
=================================================================
12) Check results for swap and compare with total of memory
=================================================================
Requirements for Installing Oracle Database 12.1 on RHEL5 or OL5 64-bit (x86-64) (Doc ID 1529433.1)
b.) Swap disk space proportional to the system's physical memory as follows:
RAM	Swap Space
Between 1 GB and 2 GB	1.5 times the size of RAM
Between 2 GB and 16 GB	Equal to the size of RAM
More than 16 GB	16 GB
NOTE: The above recommendations (from the Oracle® Database Installation Guide 12c Release 1
(12.1) for Linux) are MINIMUM recommendations for installations. Further RAM and swap space may be
required to tune/improve RDBMS performance.
grep SwapTotal /proc/meminfo
=================================================================
List of commands to be executed
=================================================================
Swap check and memory
grep SwapTotal /proc/meminfo
free -m
uname -r
cat /etc/sysctl.conf
cat /etc/security/limits.conf
cat /sys/kernel/mm/transparent_hugepage/enabled
cat /proc/sys/fs/aio-nr
cat /proc/sys/vm/swappiness
cat /etc/default/grub file:
cat /etc/grub.conf
cat /etc/security/limits.conf
show parameter memory
show parameter sga
show parameter processes
show parameter USE_LARGE_PAGES
NAME                                |Current/Acutal          |Suggested
------------------------------------|------------------------|------------------------------
HugePage                             not set                  vm.nr_hugepages = 964
shmmax                               64G                      85% of 16G
kernel.shmmax                        68719476736              kernel.shmmax=15032385536
kernel.shmall                        4294967296=4G            40% 16G = 6,4G = 6871947673
HugePage                             not set                  vm.nr_hugepages = 964
 = 64G
kernel.shmmax = 68719476736
### Exadata 202005 - standard
# kernel.sem = 1024 60000 1024 256
kernel.sem = 1512 1200000 1512 1024
 

OEM database – generating huge number of errors

Hello !

Got this issue when checking alert log:

BEGIN EM_LOADERJOB.unlock_target(hextoraw(:1 )); END;
Additional information: hd=0xfc429b90 phd=0x63888408 flg=0x100476 cisid=107 sid=107 ciuid=107 uid=107
2020-01-14T16:36:37.705267-06:00
WARNING: too many parse errors, count=148547900 SQL hash=0x7533e331
PARSE ERROR: ospid=25178, error=6550 for statement:
2020-01-14T16:36:37.705493-06:00

And this repeat each 5 seconds. Around 1 month my alert log reached 100M and got other error –

Alert log is 100.088027954102M, consider using rotate_generic.pl

Solution for this error:

First – check if your package EM_LOADERJOB have the procedure UNLOCK_TARGET

col owner for a25
col object_name for a25
col PROCEDURE_NAME for a35
col OBJECT_TYPE for a25
set lines 200
SELECT Owner,
Object_Name,
Procedure_Name,
Object_Type
FROM DBA_Procedures
WHERE Object_Name = ‘EM_LOADERJOB’
ORDER BY Procedure_Name
/

Sample Output:

OWNER OBJECT_NAME PROCEDURE_NAME OBJECT_TYPE
————————- ————————- ———————————– ————————-
SYSMAN EM_LOADERJOB DEQUEU_JOB PACKAGE
SYSMAN EM_LOADERJOB LOADERJOB_LOG PACKAGE
SYSMAN EM_LOADERJOB RELEASE_TARGET_LOCK PACKAGE
SYSMAN EM_LOADERJOB SUBMIT_JOB PACKAGE
SYSMAN EM_LOADERJOB UPDATE_DQ_TIME PACKAGE
SYSMAN EM_LOADERJOB UPDATE_END_TIME PACKAGE
SYSMAN EM_LOADERJOB UPDATE_JOB_STATUS PACKAGE
SYSMAN EM_LOADERJOB PACKAGE

If not – Copy the procedure contents from another OEM database.

Follow the steps below:

Please ensure a full database backup is take prior to proceeding
—————————————-

Login in to EM Console:

1a. Navigate to Targets -> Databases -> click on Repository database -> Schema -> Programs -> Package Bodies -> Select schema name as SYSMAN and object name as “EM_LOADERJOB” –> click Search

2b. Here you will see the EM_LOADERJOB package, click Edit.

Find the uploaded EM_LOADERJOB_packge_body attachment, copy it and past it in the “Source” Box and click apply.

3c. On the right side of the page select object_type as “packages” — drop down button –> Select schema name as SYSMAN and object name as “EM_LOADERJOB” –> click Search

3) We can see the EM_LOADERJOB package, click Edit.

Find the uploaded EM_LOADERJOB_packge_definition attachment, copy it and past it in the “Source” Box and click apply.

3. Connect to Repository as SYSMAN and run the below query again.

col owner for a25
col object_name for a25
col PROCEDURE_NAME for a35
col OBJECT_TYPE for a25
set lines 200
SELECT Owner,
Object_Name,
Procedure_Name,
Object_Type
FROM DBA_Procedures
WHERE Object_Name = ‘EM_LOADERJOB’
ORDER BY Procedure_Name
/

Now we can see the unlock_target procedure in the output. If so, Monitor the alert.log and check if the issue remains.

MOS – receive alerts if you doc was updated !

For some articules and notes, you can receive email from oracle MOS to notify you if they was updated. This seems good – for some notes who will need to be aware like control of versions, bugs and patches.

This MOS Doc ID: 793436.2 I updated my profile to receive more details about the favorites have been updated.

Next step is:

To be notified when this document changes: Mark this article as a Favorite, and follow the instructions for Email Notification in KM Doc ID: 793436.2

SYSAUX is growing a lot!

Tablespace sysaux for almost of regular sizes of databases are under 2G and for some big databases they are growing a lot. We need to stay alert with, because many of bugs are related with some jobs who can’t remove some old records on tables using sysaux.

First step: run awrinfo.sql

This script will help – with a report of all detailed occupants of tablespace sysaux.

This can take a time and we have a lot of documents on MOS related with bugs who we need investigate to make sure we not hit a bug.

 @?/rdbms/admin/awrinfo.sql

make intelligent graphics for Oracle Performance

On these tutorial we create some graphs for view oracle performance problems and IO .

First step is install oracle docker on Oracle Linux – 6 or 7. My case i going to study hard to get OCP on linux, and now we use Oracle Linux 7.2, please follow:

https://blogs.oracle.com/linux/entry/ahoy_cast_off_with_docker

Installation

To install Docker on Oracle Linux 6:

  1. Enable the ol6_addons channel in /etc/yum.repos.d/public-yum-ol6.repo
  2. Run:
    yum install docker 

To install Docker on Oracle Linux 7:

  1. Enable the ol7_addons channel in /etc/yum.repos.d/public-yum-ol7.repo
  2. Run:
    yum install docker 

Enable btrfs support

Docker uses device-mapper devices as the default storage engine. To switch to using btrfs as the storage engine, ensure that /var/lib/docker is mounted on a btrfs filesystem. Check Chapter 5 of the Oracle Linux Administrator’s Solutions Guide for more details on how to create and mount btrfs filesystems.
To enable btrfs support for Docker on Oracle Linux:
  1. Ensure that /var/lib/docker is on a btrfs filesystem
  2. Edit /etc/sysconfig/docker and add “-s btrfs” to the other_args fields.
  3. Restart the Docker daemon:
    # service docker restart

Enabling and starting Docker

To enable the Docker daemon on system boot and start Docker on OL6:
# chkconfig docker on
# service docker start
To enable the Docker daemon on system boot and start Docker on OL7:
# systemctl enable docker.service
# systemctl start docker.service

      Checking Docker status

      To check that Docker is up and running on OL6:
      # service docker status
      To check that Docker is up and running on OL7:
      # systemctl status docker.service
      You can also get more information from Docker itself:
      # docker info
      # docker version
      Once you have Docker up and running, follow the upstream Docker documentation to get started. 
      Next step is to get my docker on next post!

      Make oracle linux 7 suitable to install oracle database

      We start with installation of packages

      Confirm if you install have minimal 16G swap cache, on system.

      free -m

      Almost installations have a minimal of 16G for swap, my system for example have a 128G memory – recently many docs confirms 16G of swap is ok for all memory configurations.

      Make the update for the entire system, update the packages you made this commands below like a root.

      yum update
      yum install oracle-rdbms-server-11gR2-preinstall.x86_64
      Change the user oracle password:
      passwd oracle
      Please verify the security by selinux files, modify this entry:
      /etc/selinux/config
      modify to 
      SELINUX=disabled
      attention: do not confuse this line, pay attention.
      reboot the system
      If you not reboot the system you receive messages like, permission denied to disable services below.
      And stop the services, because this services is consuming memory and resources (cpu).
      systemctl stop firewalld
      systemctl stop iptables
      systemctl disable firewalld
      systemctl disable iptables
      systemctl disable atd
      systemctl disable bluetooth
      systemctl disable postfix
      systemctl disable sound
      systemctl disable anacron 
      systemctl disable atd 
      systemctl disable cups 
      systemctl disable cups-config-daemon 
      systemctl disable gpm 
      systemctl disable iptables 
      systemctl disable kudzu 
      systemctl disable lvm2-monitor 
      systemctl disable microcode_ctl 
      systemctl disable sendmail 
      systemctl disable smartd 
      systemctl disable auditd 
      systemctl disable avahi-daemon 
      systemctl disable bluetooth 
      systemctl disable firstboot 
      systemctl disable hidd 
      systemctl disable ip6tables 
      systemctl disable mcstrans 
      systemctl disable mdmonitor 
      systemctl disable pcscd 
      systemctl disable rawdevices 
      systemctl disable readahead_early 
      systemctl disable readahead_later 
      systemctl disable restorecond 
      systemctl disable setroubleshoot 
      systemctl disable yum-updatesd 
      systemctl disable nfs 
      systemctl disable autofs on
      systemctl disable cpuspeed on
      systemctl disable haldaemon on
      systemctl disable irqbalance on
      systemctl disable messagebus on
      systemctl disable netfs on
      systemctl disable nfslock on
      systemctl disable rpcgssd on
      systemctl disable rpcidmapd on
      systemctl disable portmap on
      The old model (before Oracle Linux 7 or RHEL 7)
      chkconfig anacron off
      chkconfig atd off
      chkconfig cups off
      chkconfig cups-config-daemon off
      chkconfig gpm off
      chkconfig iptables off
      chkconfig kudzu off
      chkconfig lvm2-monitor off
      chkconfig microcode_ctl off
      chkconfig sendmail off
      chkconfig smartd off
      chkconfig auditd off
      chkconfig avahi-daemon off
      chkconfig bluetooth off
      chkconfig firstboot off
      chkconfig hidd off
      chkconfig ip6tables off
      chkconfig mcstrans off
      chkconfig mdmonitor off
      chkconfig pcscd off
      chkconfig rawdevices off
      chkconfig readahead_early off
      chkconfig readahead_later off
      chkconfig restorecond off
      chkconfig setroubleshoot off
      chkconfig yum-updatesd off
      chkconfig nfs off
      chkconfig autofs on
      chkconfig cpuspeed on
      chkconfig haldaemon on
      chkconfig irqbalance on
      chkconfig messagebus on
      chkconfig netfs on
      chkconfig nfslock on
      chkconfig rpcgssd on
      chkconfig rpcidmapd on
      chkconfig portmap on
      Reboot the system for this services not startup again.
      su – oracle
      and create .bash_profile
      add this on your file:
      echo “###############################################”
      echo “#         BANCO     SCRIPT    AMBIENTE        #”
      echo “###############################################”
      export NLS_LANG=”BRAZILIAN PORTUGUESE_BRAZIL.WE8MSWIN1252″
      export ORACLE_SID=prd
      export ORACLE_BASE=/u01/app/oracle
      export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
      export PATH=$ORACLE_HOME/bin:$PATH
      export ORACLE_UNQNAME=prd

      Oracle RAC startup with oracle user two instances fail with disconected error

      On these days we get on strange error:

      The instance startup normally with srvctl but with sqlplus we get the disconected instance.

      we have two diferente users: grid and oracle

      With user oracle we try to startup the instances. But just one can stay up.

      Cenario:

      ORCL1

      sqlplus / as sysdba

      sql> startup mount
      sql> select status from v$instance;

      Instance: STARTED

      ORCL2

      sqlplus / as sysdba

      sql> startup mount
      sql> select status from v$instance;

      Instance:  disconected from instance

      The second instance get down and not work. I just clear the two alert logs, and restart the processes, and now with srvctl.

      srvrctl start database instance -d ORCL

      Yes, we can connect on both instances. But if i tryed to start the instances with user oracle we no have success.

      Just we got the solution:

      On user oracle, a just added the ORA_CRS_HOME=/u01/app/11.2.0/grid on .bash_profile,

      and restart with sqlplus and is ok now the two instances get up without problems.

      The key os solution this entry on alert log on instance get dow:

      [USER(32704)]CRS-2317:Fatal error: cannot get local GPnP security keys (wallet). 
      2015-11-17 15:58:54.552
      [USER(32704)]CRS-2316:Fatal error: cannot initialize GPnP, CLSGPNP_ERR (Generic GPnP error).
      kggpnpInit: failed to init gpnp
      WARNING: No cluster interconnect has been specified. Depending on
      the communication driver configured Oracle cluster traffic
      may be directed to the public interface of this machine.
      Oracle recommends that RAC clustered databases be configured
      with a private interconnect for enhanced security and
      performance.
      Picked latch-free SCN scheme 3
      Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST
      WARNING: db_recovery_file_dest is same as db_create_file_dest
      Autotune of undo retention is turned on.
      LICENSE_MAX_USERS = 0
      SYS auditing is disabled
      
      
      
      
      
      

      Flex ASM

      Um dos problemas que temos ao utilizar o ASM e que, nas versoes anteriores a versao 12c, temos que instalar a instancia ASM em cada local onde sera consumido dos diskgroups.
      Temos entao o Flex ASM e para a clusterizacao temos que utilizar o Flex Clusters.
      Caracteristicas:
      Flex ASM nao requer Flex Cluster
      Flex ASM roda como um servico para cluster padrao para atender clientes atraves do cluster.
      Flex ASM pode rodar como um hub de conexao para atender aos servicos de Flex Cluster atraves de nos de HUBs de Flex Cluster.
      O Flex ASM tem menor gastos de recursos ja que o mesmo nao precisa da instancia ASM
      Flex ASM introduz uma nova rede para que os clientes e as instancias ASM possam se comunicar nessa rede
      Como ocorre a conexao:
      Cada servidor contendo o banco de dados oracle ira configurar o local_listener apontando para o listener existente no servidor que roda a instancia ASM com seu devido Listener, sendo que o parametro local_listener do banco pode ser configurado para ate 3 listener.

      As instancias ASM sao desenvolvidas para que tenhamos o minimo de monitoramento o possivel. Portanto sempre que possivel a recomentacao e utilizar o parametro memory_target. O calculo e feito na hora da criacao da instancia ASM baseando-se no numero de cores dos processadores e a quantidade de memoria disponivel. Atualmente o valor minimo e de 1076M para o memory_target.
      Gerenciamento
      $ srvctl status asm -detail
      

      ASM is running on host03,host02,host01 ASM is enabled.
      $ srvctl stop asm -node host03 -f
      $ srvctl start asm -node host04

      $ srvctl status asm -detail
      

      ASM is running on host04,host02,host01
      ASM is enabled.
      $ srvctl relocate asm -currentnode host04 -targetnode host03 $ srvctl status asm -detail
      ASM is running on host03,host02,host01
      ASM is enabled. 

      $ srvctl stop asm -proxy -node host03 
      $ srvctl start asm -proxy -node host04

      Cardinalidade

      A cardinalidade para as instancias ASM, informa qual e a quantidade de instancias ASM servindo o cluster ASM.

      $ srvctl config asm
      ASM home: /u01/app/12.1.0/grid

      Password file: +DATA/orapwASM
      ASM listener: LISTENER
      ASM instance count: 3
      Cluster ASM listener: ASMNET1LSNR_ASM

      Para consultar quais sao os clientes que estao consumindo as instancias, utilizamos a gv$asm_client.

      Para visualizar quais instancias estao fazendo o atendimento, faca a consulta abaixo:

       

      Os clientes podem ser realocados, isso ja ocorre automaticamente caso a instancia ASM falhe. Mas atraves do novo comando abaixo, podemos realocar o cliente. Geralmente voce executa este comando para tentar realocar os clientes para outra instancia ASM.

      SQL> ALTER SYSTEM RELOCATE CLIENT ‘<instance_name>:<db_name>‘;
      O cliente sera desconectado e provavelmente ao se conectar novamente, ira se conectar na instancia ASM que ja estava conectado. Para evitar que isso ocorra, derrube a instancia ASM logo apos realocar todos clientes.

      Quando os discos estao entregues para serem consumidos pelos diskgroups temos os seguintes status:
      CANDIDATE – Disco recentemente criado, este disco nunca foi usado ainda.
      FORMER – Disco que ja foi usado e removido do diskgroup, esta disponivel novamente para uso.
      PROVISIONED – E similar ao candidate exceto porque o provisionado ainda necessita alguma acao por parte do administrador para disponibilizar este disco.
      Para criar um diskgroup siga o exemplo:
       

      CREATE DISKGROUP FRA NORMAL REDUNDANCY
      DISK ‘ORCL:SDD11’ NAME ‘FRA_DISK1’ SIZE 977 M, ‘ORCL:SDD12’ NAME ‘FRA_DISK2’ SIZE 977 M;

      Cada diskgroup criado, mesmo que nao contenha a clausula failure group, cada disco pertencendo a um diskgroup com redundancia normal ou redundancia high,  cada disco contem uma redundancia, ou seja se eu tiver 10 discos em um diskgroup vamos ter automaticamente 10 failgroups.
      Redundancia
      NORMAL – requer no minimo dois failure groups. No exemplo de criacao acima sao dois failure groups criados. Permite a falha de um failure group. Controlfiles armazenados neste tipo de diskgroup espelhado 3 vezes.
      HIGH REDUNDANCY – requer a existencia de tres grupos de falha. Tolerante a perda de dois grupos de falhas.
      EXTERNAL REDUNDANCY – nao tolera falhas, necessita redundancia externa.

      Questao da prova 1Z0-058

      Assuntos das questoes 1Z0-058 sobre as informacoes abaixo:

      1. Sobre os processos do ACFS

      2. Additional processes are started when ADVM volumes are configured.
        • VDBG: The Volume Driver Background process forwards ASM requests to lock or unlock an extent for volume operations to the Dynamic Volume Manager driver. The VDBG is a fatal background process, the termination of this process brings down the ASM instance.
        • VBGn: Volume Background processes wait for requests from the Dynamic Volume Manager driver, which need to be coordinated with the ASM instance. An example of such a request would be opening or closing an ASM volume file when the Dynamic Volume Manager driver receives an open for a volume (possibly due to a file system mount request) or close for an open volume (possibly due to a file system unmount request). The unplanned death of any of these processes does not have an effect on the ASM instance.
        • VMB: Volume Membership Background coordinates cluster membership with the ASM instance. 
            Sobre redundancia do diskgroup

      1. There are always failure groups even if they are not explicitly created. If you do not specify a failure group for a disk, that disk is placed in its own failure group with the failure group name the same as the disk name Therefore, if 20 disks were in a single disk group, there could be 20 failure groups as well. Failure groups have meaning only when used with normal and high redundancy disk groups. All failure groups within the same disk group should be created with the same capacity to avoid space allocation problems.

          Sobre Quiescing no RAC

      Quiescing a database
      For maintenance purposes you may require a database to be quiesed, so that the only connections allowed are as sys or system.  Even users with the DBA role will be excluded.
      This mode is useful for:
      Transactions that would otherwise fail if user transactions access the same object or set of objects.  Things like changing the schema of a database table, or adding a column to an existing table where a no-wait lock is required.
      Multistep procedures, that could confuse application users, if they were still logged on. For example exporting dropping an re-creating a table.
      Quiescing the database will have a similar effect to shutting the database down, and restarting it in restricted mode.  However on a high availability system, you may not be able to shut the database down.  Quiescing would have a smaller impact then the shutdown and restart.
      To place a database into quiesced mode:
      SQL> alter system quiesce restricted;
      Non-DBA active session will continue until they become inactive.  An active session is a session that is inside a transaction or a session that is currently holding shared resources. No inactive sessions are allowed to become active.  When a connected user issues a query, the query will seem to hang until the databas
      e is taken out of quiesced state, and the query will then continue.
      Once all of the Non-DBA active sessions have become inactive, then the “SQL> alster system quiesce restricted;” completes, and the database is then in a quiesced state.  In a RAC environment, this statement will affect all the instances, and not just the instance where the statement was issued from.
      While waiting for this statement to process you can query from another session SID(session ID) from v$blocking_quiesce view.  You can join this view with v$session to get more information about the session.
      SQL> select bq.sid, ses.user, ses.osuser, ses.type, ses.program
      From v$blocking_quiesce bq,
                 V$session ses
      Where bq.sid = ses.sid;
      If your session terminates, or you cancel the request to quiesce the database, then the Oracle Database automatically reverses any partial effects of the statement.
      For queries that are carried out by successive OCI(Oracle Call Interface) fetches,  the Oracle database only waits for the first fetch to complete.
      The mechanism works the same for  dedicated and shared server connections.  The user trying to login after the quiesce statement has been issued, will experience it as though the login as hung.  The login will continue again when the database is unquiesced.
      The database will remain in this state, regardless of whether the session that issued the statement is still active or not.  In order to take the database out of this mode a DBA(sys,system) will need to log in and unquiesce the database.  In a RAC environment, the unquiesce statement can even be issued from a different instance.
      SQL> alter system unquiesce;
      A quiesced database is not in the correct state for a cold backup, because the file headers are still being updated.  However an online backup in this state is possible.  
      To view the QUIESCE state of an instance, you can:
      SQL> select active_state from v$instance;
      The possible values returned are:
      ·         NORMAL      – Normal unquiesced state
      ·         QUIESCING – Being quieced, but some non-DBA sessions are still active
      ·         QUESCED     – Quiesced, non non-DBA sessions are active or allowed

      Particionamento de tabelas Oracle


      O particionamento de tabelas e de indices auxilia no tratamento e na seleção destes dados. Os dados são armazenados em segmentos, e estes segmentos que estão armazenados nessas particoes podem sofrer manutençoes diretamente.


      Para isso temos as seguintes tipos de partições:


      Range – E a mais comum e pelo nome ja diz baseada em cima de um range da chave.
      Hash – A partir de um algoritmo os dados são armazenados nas partições a partir de uma chave de particionamento.
      List – Os dados sao mapeados as particoes a partir de listas.
      Interval – Os dados sao mapeados as particoes a partir de intervalos que são automaticamente criados de acordo com o intervalo.
      System – Os dados sao mapeados as particoes de forma arbitraria – via o programa que os esta tratando.
      Podemos ainda ter uma subparticao, para que possamos ter uma opção de aproveitar ainda mais aquela partição
       ja existente.
      • Range top level: – Range-Range
        – Range-Hash
        – Range-List 
      • List top level:
        – List-Range – List-Hash – List-List 
      • Interval top level: – Interval-Range
        – Interval-Hash – Interval-List
       A chave do particionamento e que comanda, como os dados vão ser inseridos, e cada partição pode ter no máximo 16 colunas como chave. Podemos também fazer os comandos DML e select diretamente a essas chaves. Nenhum dos comandos existentes seja DML ou SELECT precisa ser alterado para que se utilize particionamento.
      Existe um tipo de particionamento que esta baseado no relacionamento das tabelas. Ou seja, pela constraint de chave estrangeira. Ainda, temos também o particionamento que pode ser baseado em uma coluna virtual ou seja calculada.
      Quais sao os beneficios de se utilizar o particionamento ?
      O particionamento automaticamente faz com que o otimizador do oracle exclua as partições que não são interessantes para resolver os comandos. 
      As particoes podem sofrer manutenção diretamente sem ter outras partições comprometidas para os comandos de DML e SELECT.
      As partições vão contribuir diretamente com os recursos de gerenciamento de ciclo de vida da sua informação.
      Voce pode atribuir uma particao a uma tablespace, dai podendo armazenar seus datafiles em locais distintos.
      Varios comandos poderemos executar para realizar a manutenção nas partições:
      • ALTER TABLE ADD PARTITION 
      • ALTER TABLE DROP PARTITION (RANGE) 
      • ALTER TABLE TRUNCATE PARTITION (RANGE) 
      • ALTER TABLE MOVE PARTITION 
      • ALTER TABLE SPLIT PARTITION 
      • ALTER TABLE EXCHANGE PARTITION 
      • ALTER TABLE RENAME PARTITION 
      • ALTER TABLE MODIFY PARTITION 
      • ALTER TABLE COALESCE PARTITION
      A criação de uma tabela particionada pode ser feita usando o comando abaixo:
      create table exemplo
      (idx number, descricao varchar2(2))
      partition by range (idx)
      (partition values less than (0)  tablespace users,
      partition values less than (maxvalue) tablespace users1);
      Operações diretamente nas partições:
      select idx from complex PARTITION (particao1);
      insert into complex PARTITION (particao2) values (4,5);
      delete from complex PARTITION (particao3);
      create table teste as select * from tabela partition(particao1);
      expdp sh/sh tables(SALES:SALES_Q1_20001)
      Dicionario de dados para consultar as partições
      DBA_TAB_PARTITIONS
      DBA_SUBPATITION_TEMPLATES
      DBA_SEGMENTS

      Name
      Purpose
      N
      DBA_TABLES
      T
      DBA_PART_TABLES
      Partition type: default values
      T
      DBA_TAB_*PARTIT
      IONS
      Partitions detail
      P
      DBA_*PART_KEY_COLUMNS
      Partition keys
      P

      T = Tabela
      P = Particao
      SQL> SELECT TABLE_NAME, TABLESPACE_NAME,

        2         PARTITIONED, ROW_MOVEMENT
      3 FROM USER_TABLES ;
      TABLE_NAME TABLESPACE_NAME PARTITIONED ROW_MOVE
      ---------- --------------- ----------- --------
      HR_EMP                     YES
      MULTICOL YES
      ORDINARY USERS NO
      SIMPLE YES
      ENABLED
      ENABLED

      DISABLED


      SQL> SELECT TABLE_NAME, PARTITIONING_TYPE,
      2 SUBPARTITIONING_TYPE, PARTITION_COUNT,
      3 PARTITIONING_KEY_COUNT,DEF_TABLESPACE_NAME 4 FROM USER_PART_TABLES ;

      TABLE_NAME TYPE   SUBTYPE PAR.CNT PAR.KEY_CNT DEF_TAB.SP
      ---------- ------ ------- ------- ----------- ----------
      COMPOS     RANGE  HASH          3
      MULTICOL RANGE NONE 4
      SIMPLE LIST NONE 2
      1 USERS
      2 USERS 

      1 USERS



      Name
      Columns to show
      DBA_SEGMENTS
      PARTITION_NAME, SEGMENT_TYPE
      DBA_EXTENTS
      PARTITION_NAME, SEGMENT_TYPE
      DBA_OBJECTS
      SUBOBJECT_NAME, OBJECT_TYPE

      Global index
      create index idx on employees ( first_name )
      global partition by range (first_name)
      ( partition x1 values less than (‘H’) tablespace users, 
        partition x2 values less than (maxvalue) );
      Local index tabela particionada mesmas tablespaces da tabela
      create index idx on employees ( first_name ) local;
      Local index tabela particionada mas em outras tablespaces
      create index idx on employees ( first_name ) 
       local
       partition partition1 tablespace idx1,
       partition partition2 tablespace idx2
       );
      Algumas curiosidades quando usando tabelas particionadas:
      • O particionamento e pago a parte, sendo uma option do banco instalado como enterprise database
      • Algumas sentenças SELECT podem ser automaticamente dividas para que a resolução seja mais rápida
      • As operações de delete e inserts são enxergadas pelo dicionário somente como simples operação de organização.
      • As operações nos indices podem ser individualizadas (usando local indexes)
      • Podemos ter ate 1048575 partições por tabela ou índice
      • Podemos automatizar a criação das tabelas através de templates para as partições
      • As operações nos indices globais afetam as operações nas tabelas
      • As operações nos indices locais não afetam as operações nas tabelas como um todo
      • Os indices globais podem ser usados em tabelas particionadas ou nao. Os indices locais somente em tabelas particionadas.
      • < span>Os indices locais sao automaticamente mantidos. Ou seja, as partições alteradas nas tabelas são automaticamente alteradas nos indices.

      Particionamento em indices

      Diferente do particionamento da tabela, temos o particionamento do indice. O particionamento do indice pode existir independente da tabela ser particionada ou nao. Isso ocorre uma flexibilizacao a mais e importante para a manutencao destes dados.

      Atributos: Global ou  Local

      O indice sendo global pode ter outro tipo de particionamento que o da tabela, ou mesmo se a tabela nao tiver nenhum particionamento.  O indice global pode utilizar somente as tecnicas range e hash.
      Os tipos de particionamento validos para o indice global . O indice sendo particionado local segue a mesma regra de particionamento da tabela, sendo que o indice local somente pode ser criado para a tabela que e particionadada.

      Concorrencia pelo indice

      Geralmente temos o evento buffer busy em uma frequencia muito grande de DML. Dai em um rac por exemplo para se evitar essa incidencia do evento, se cria o indice reverso. Isso resolveria em parte a concorrencia, mas para resolver de uma forma eficiente e conservar a mesma ordem o ideal e criar o indice global usando a tecnica de hash. Como o hash fara com que o indice seja designado a varias tablespaces, criaremos entao varios pequenos locais de contencao e nao um unico ponto de contencao geral, como resultado aumentaremos a eficiencia geral.

      Os indices particionados tem duas categorias de conceito logico

      Prefixo – A chave de partição é a coluna mais à esquerda (s) do índice. Sondagem este tipo de índice é menos onerosa. Se uma consulta especifica a chave de partição na partição onde particao de pesquisa é possível, isto é, nem todas as partições serão pesquisados.
      Não-prefixadas – Não suporta partição de pesquisa, mas é eficaz no acesso aos dados que abrange várias partições. Muitas vezes usado para indexar uma coluna que não é a chave de partição tabelas, quando você gostaria que o índice a ser dividido na mesma chave que a tabela subjacente.
      Isso depende como o acesso e feito e como as chaves estao armazenadas. Dai pode ser simplesmente um conceito logico sem nenhuma distincao ate na sintaxe do comando da criacao do indice local.

      Local Prefixed Indexes

      • Somente possivel em tabelas particionadas
      • Ocupa menos espaco porque se refere a tabela particionada e suas devidas particoes
      • Melhor performance quando utilizada para operacoes massivas com paralelismo
      • Podem ser unique ou nao unique
      • Btree ou Bitmap
      • A condicao e que o indice seja criado com mesmas colunas da chave de particionamento da tabela
      Exemplo:
      Create index idxp1 on emp(first_name) local;
      Create index idxp1 on emp(first_name) 
      tablespace indx04
      local
      partition ex1 tablespace indx01,
      partition ex2 tablespace indx02,
      partition ex3 tablespace indx03
      );

      Local Nonprefixed Index

      Indices que sao criados em tabelas particionadas
      A chave do indice nao e a mesma da chave do particionamento, mesmo que a chave de particionamento seja a mesma, a chave de ordenacao nao e.
      Pode ser nao unica
      Pode ser bitmap ou btree
      Utilizada quando as consultas envolvem a busca em mais particoes, ou que geralmente temos que varrer o indice ou muitas particoes
      Exemplo:
      create index idxp on emp (hire_date) local;

      Tipos de particionamento de Indice

      Index Types
      Global (Range or Hash)
      Local (All)
      B-tree
      Yes
      Yes
      Bitmap
      No
      Yes
      Bitmap Join
      No
      Yes
      Function
      No (*)
      Yes
      Secondary IOT
      No
      Yes
      Cluster*
      No
      No

      O indice normalmente criado na tabela, tambem pode ser chamado de GLOBALNONPARTITIONED INDEX

      O Status do indice – UNUSABLE

      Quando o indice fica no status de unusable, o indice nao e mais usado pelo otimizador e ocorrerao erros ao executar o DML. Para isso temos alguns detalhes a serem seguidos:
      Parametro de inicializacao SKIP_UNUSABLE_INDEXES – quando configurado como TRUE que e o padrao o banco de dados o oracle ira executar o DML desde que haja um indice unique com status unusable, mas os indices nao sao mantidos aqueles com status unusable.
      Se em um indice particionado uma ou mais particoes estiverem com status unusable, o otimizador nao ira usar o indice para a combinacao de particoes para resolucao de consultas ou o partitioning pruning. Para as demais, utilizara as particoes que estao usable.
      Truncate de uma tabela com indices particionados invalidos, faz com que estes indices invalidos fiquem validos.
      Os indices invalidos devem ser reconstruidos ou removidos.

      Criando o indice como PARTIAL ou FULL Versao 12c

      O indice na versao 12c pode ser criado somente em algumas particoes da tabela, para agilizar sua utilizacao. Nas versoes anteriores as do 12c, nao temos tanta flexibilizacao na criacao, manutencao e remocao de particoes de indices.
      Exemplos
      create index orders_idx_partial on orders(order_total) global indexing partial;
      ou
      create index orders_idx_partial on orders(order_total) global indexing full;