Month: October 2018

Database, SQL | PLSQL

Oracle No Segment Indexes

 , , , ,

Did you know we have this? Yes, and this is since 11g.

As you know, the process of tuning SQL statements often requires the testing of alternate indexing strategies to see the effect on execution plans.

Adding extra indexes to large tables can take a considerable amount of time and disk space besides possibly changing known execution plans and possibly affecting all the application behavior.

This can be problematic when you are trying to identify problems on a production system. Some of those problems can be solved by using invisible indexes, but they still would require creation and segment allocation, but also being updated by the application itself.

To solve that, virtual indexes have no associated segment, so the creation time and associated disk space are irrelevant. In addition, it is not seen by other sessions, so it doesn’t affect the normal running of your system, similar to invisible indexes, but also don’t not even to be updated, as invisible indexes need to be.

To create the virtual index consists in simply add the NOSEGMENT clause to the CREATE INDEX statement, for example:

CREATE INDEX index_name ON table(column_name) NOSEGMENT;

However, to make the virtual index available we must set the _use_nosegment_indexes parameter. Another important mention is that the virtual indexes don’t appear in the USER_INDEXES view, but can be found in the USER_OBJECTS view.

Cheers!

Enterprise Manager

OEM 13C: How to Set Up Out Of Band Notifications

So, after a quiet weekend on a client, noticed I was not being paged for a reason: OMS was down! 😀

Ok, so, how to monitor the monitoring easily?
OEM 13c has a feature called Out of Band Notification, which allows configuring an agent with email credentials to send notifications when he is not able to communicate with OMS and Repo DB are down.

Details of that configuration is on this MOS note: EM 13c, 12c: How to Set Up Out Of Band Email Notification in Enterprise Manager Cloud Control (Doc ID 1472854.1)

How does it work?
The agent on the OMS host checks the status of the ‘OMS and repository’ target (oracle_emrep) by running the metric ‘Response’ which runs the perl script:

[agent_home]/plugins/oracle.sysman.emrep.agent.plugin_12.1.0.n.0/scripts/emrepresp.pl

If the oracle_emrep target is detected as down then emrepdown.pl will be called on same directory.

The emrepdown.pl uses the perl “Net::SMTP” method to send an email using the Out Of Band email information (To Email ID, Email Gateway, From Email ID) defined in the Agent’s /sysman/config/emd.properties configuration file.

Note: this method does not currently support SSL email authentication, an internal ER (Bug 18886316 “WOULD LIKE ABILITY FOR EMREPDOWN.PL TO BE ABLE TO USE SSL” ) has been raised for this.

How to set up?
1) Run the following commands which will set the email parameters in the emd.properties file.
Do this on the chained agent (ie. the agent on the same machine as the OMS which monitors the oracle_emrep target)

a) Set the agent ORACLE_HOME

$ export ORACLE_HOME=
$ export PATH= ORACLE_HOME/bin:$PATH

Example:

$ export ORACLE_HOME=/oracle/12c/12cagent/core/12.1.0.3.0
$ export PATH=$ORACLE_HOME/bin:$PATH

b) Check if any values are currently set for the Out of Band parameters

$ emctl getproperty agent -name emd_email_address
$ emctl getproperty agent -name emd_from_email_address
$ emctl getproperty agent -name emd_email_gateway

 

If the message is returned:

emd_email_address is not a valid configuration property

It means that this is not yet set up, continue to the next section.

c) Set the Out of Band parameters

emctl setproperty agent -allow_new -name emd_email_address -value [youremailaddress]
emctl setproperty agent -allow_new -name emd_from_email_address -value [senderAddress]
emctl setproperty agent -allow_new -name emd_email_gateway -value [outgoingsmtpserver]
Example:
$ emctl setproperty agent -allow_new -name emd_email_gateway -value smtp.server.hostname
$ emctl setproperty agent -allow_new -name emd_email_address -value noc@grepora.com
$ emctl setproperty agent -allow_new -name emd_from_email_address -value 13cagent@grepora.com

TIP: The value for the emd_email_gateway can be the same as is used for ‘normal’ email notifications via the OMS. This can be accessed via setup/notifications/notification methods.

If you need to use “Use Secure Connection:SSL” normally, then this means that your mail server requires SSL authentication which means that the OOB method will not be suitable. Remember: the OOB method does not support SSL email authentication at this moment in time.

2) Stop and start the agent for these parameters to take effect.

More informations like to test this configuration can be found on MOS note: EM 13c, 12c: How to Set Up Out Of Band Email Notification in Enterprise Manager Cloud Control (Doc ID 1472854.1)

Hope that helps, cheers!

Database, SQL | PLSQL

Oracle Compound Triggers

 ,

Did you know that?

Since 11g, Oracle has something called “compound trigger”, that allows a single trigger to be used for combining actions for different timing points for a specific object.

The individual timing points share a single global declaration section, whose state is maintained for the lifetime of the statement. Once a statement ends, due to successful completion or an error, the trigger state is cleaned up. In previous releases this type of functionality was only possible by defining multiple triggers whose code and global variables were defined in a separate package.

The triggering actions are defined in the same way as any other DML trigger, with the addition of the COMPOUND TRIGGER clause. The main body of the trigger is made up of an optional global declaration section and one or more timing point sections, each of which may contain a local declaration section whose state is not maintained. For example:

CREATE OR REPLACE TRIGGER trigger_example
    FOR action ON table_name COMPOUND TRIGGER
    global_variable VARCHAR2(30);
    BEFORE STATEMENT IS BEGIN NULL; END BEFORE STATEMENT;
    BEFORE EACH ROW IS BEGIN NULL; END BEFORE EACH ROW;
    AFTER EACH ROW IS BEGIN NULL; END AFTER EACH ROW;
    AFTER STATEMENT IS BEGIN NULL; END AFTER STATEMENT;
END trigger_example;
/

A good summary with detailed information and examples can be found (as always) here: https://oracle-base.com/articles/11g/trigger-enhancements-11gr1

Cheers!

Database, Tools

Segment Creation on Demand or Deferred Segment Creation

 , , ,

Hi All!
I was reviewing some features in Oracle and, basically, every single time I review them I find something new. Seems Oracle Databases’ features are near to infinite and we frequently find some that can really add value to our solutions.

So I decided to make a serie of posts with really quick notes about each one of them.
You can see all posts in this serie in my page of posts and some others more.

Ready? Here it goes:

Segment Creation on Demand or Deferred Segment Creation

I also talked about it in post EXP Missing Tables on 11.2.

Segment creation on demand, or deferred segment creation as it is also known, is a space saving feature of Oracle Database 11g Release 2. When non-partitioned tables are created, none of the associated segments (table, implicit index and LOB segments) are created until rows are inserted into the table. For systems with lots of empty tables, this can represent a large space saving. This functionality can be controlled by the DEFERRED_SEGMENT_CREATION initialization parameter, which is set to TRUE by default.

More“Segment Creation on Demand or Deferred Segment Creation”

ASM, Shell Scripts

Even Better Script: Map ASM Disks to Physical Devices

 , ,

Enjoyed last week post?

Cool, because looking further on the subject I found this pretty similar post, by Mohammad Nazmul Huda.

The additional script there is actually not working in my server, but the idea is great. So, I did just some small adjustments and it’s working pretty fine now:

# asm_report.sh (Adjusted by Matheus):

printf "\n%-15s %-14s %-11s %-7s\n" "ASM disk" "based on" "Minor,Major" "Size (MB)"
printf "%-15s %-14s %-11s %-7s\n" "===============" "=============" "===========" "========="
export ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome1
for i in `/usr/sbin/oracleasm listdisks`
do
v_asmdisk=`/usr/sbin/oracleasm querydisk -d $i | awk '{print $2}'| sed 's/\"//g'`
v_minor=`/usr/sbin/oracleasm querydisk -d $i | awk -F[ '{print $2}'| awk -F] '{print $1}' | awk -F, '{print $1}'`
v_major=`/usr/sbin/oracleasm querydisk -d $i | awk -F[ '{print $2}'| awk -F] '{print $1}' | awk -F, '{print $2}'`
v_device=`ls -la /dev | awk -v v_minor="$v_minor," -v v_major=$v_major '{if ( $5==v_minor ) { if ( $6==v_major ) { print $10}}}'`
v_size_bt=`blockdev --getsize64 /dev/${v_device}`
v_size=`expr $v_size_bt / 1024 / 1024`
Total_size=`expr $Total_size + $v_size`
Formated_size=`echo $v_size | sed -e :a -e 's/\(.*[0-9]\)\([0-9]\{3\}\)/\1,\2/;ta'`
printf "%-15s %-14s %-11s %-7s\n" $v_asmdisk "/dev/$v_device" "[$v_minor $v_major]" $Formated_size
done
Formated_Total_size=`echo $Total_size | sed -e :a -e 's/\(.*[0-9]\)\([0-9]\{3\}\)/\1,\2/;ta'`
printf "\nTotal (MB): %43s\n\n" $Formated_Total_size

Ok, and how it works?
[root@greporasrv ~]# sh asm_report.sh

ASM disk        based on      Minor,Major Size (MB)
=============== ============= =========== =========
DATA01          /dev/sdg1     [8 97]       255,999
DATA02          /dev/sdh1     [8 113]      255,999
DATA03          /dev/sdi1     [8 129]      255,999
DATA04          /dev/sdj1     [8 145]      255,999
FRA01           /dev/sdk1     [8 161]      307,199

Total (MB): 1,331,195

Even better, right?

Cheers!

ASM, Errors and Bugs, RAC | Data Guard

CRS-10051: CVU found following errors with Clusterware setup : PRCW-1015 : Wallet % does not exist

 , ,

Hello all!
So, recently I found this error in a CRS alert log from a client environment. Interesting error…

2018-03-26 16:33:53.277 [SRVM(9624)]CRS-10051: CVU found following errors with Clusterware setup : PRCW-1015 : Wallet greporadb does not exist. 
CLSW-9: The cluster wallet to be operated on does not exist. :[1015]PRCW-1015 : Wallet greporadb does not exist.

I found this also related to the error:

PRVG-1512 : Failed to retrieve current selection of public and private network classifications

So, it was mapped to known Bug 18234669, as per described in CRS-10051: CVU Found Following Errors With Clusterware Setup :PRCW-1015 : Wallet ora603ut does not exist (Doc ID 2008466.1).

Ok, but what to do?

1) In case you have the wallet, you can simply add it to the database:

crsctl add wallet -type CVUDB -name [dbname]

2) In case you haven’t, you can simply disable the resource ora.cvu, that is the one checking this:

> Checking Status

[root@grepora-srv ~]# crsctl stat res ora.cvu -p | grep CHECK_RESULT
CHECK_RESULTS=PRVG-1512 : Failed to retrieve current selection of public and private network classifications,PRCW-1015 : Wallet greporadb does not exist. ,CLSW-9: The cluster wallet to be operated on does not exist. :[1015],PRCW-1015 : Wallet greporadb does not exist. ,CLSW-9: The cluster wallet to be operated on does not exist. :[1015],PRCW-1015 : Wallet c4prod does not exist. ,CLSW-9: The cluster wallet to be operated on does not exist. :[1015],PRVG-1512 : Failed to retrieve current selection of public and private network classifications,PRCW-1015 : Wallet greporadb does not exist. ,CLSW-9: The cluster wallet to be operated on does not exist. :[1015],PRCW-1015 : Wallet greporadb does not exist. ,CLSW-9: The cluster wallet to be operated on does not exist. :[1015],PRCW-1015 : Wallet c4prod does not exist. ,CLSW-9: The cluster wallet to be operated on does not exist. :[1015],PRVG-1512 : Failed to retrieve current selection of public and private network classifications,PRCW-1015 : Wallet greporadb does not exist. ,CLSW-9: The cluster wallet to be operated on does not exist. :[1015],PRCW-1015 : Wallet greporadb does not exist. ,CLSW-9: The cluster wallet to be operated on does not exist. :[1015],PRCW-1015 : Wallet c4prod does not exist. ,CLSW-9: The cluster wallet to be operated on does not exist. :[1015],PRVG-1512 : Failed to retrieve current selection of public and private network classifications,PRCW-1015 : Wallet greporadb does not exist. ,CLSW-9: The cluster wallet to be operated on does not exist. :[1015],PRCW-1015 : Wallet greporadb does not exist. ,CLSW-9: The cluster wallet to be operated on does not exist. :[1015],PRCW-1015 : Wallet greporadb does not exist. ,CLSW-9: The cluster wallet to be operated on does not exist. :[1015]

> Disable CVU:

oracle:grepora-srv: srvctl disable cvu
oracle:grepora-srv:
oracle:grepora-srv: crsctl stat res ora.cvu -p | grep ENABLED
ENABLED=0
oracle:grepora-srv: srvctl status cvu                       
CVU is disabled

Hope it helps!

Database, Enterprise Manager

OEM: The number of hanging transactions are hang_trans is %

 , ,

Hi all!
So, today is quickie one, just to make the links. Seems this message from OEM is not clear enough for some people, specially regarding non-specialists in Oracle: This means something is in lock in your database!

If this is the case, contact a DBA.

If you ARE a DBA, you may want to read this post about easy locating and solving locks: Solving Simple Locks Through @lock2s and @killlocker.

Also, if the session if from DBLink, is always useful to read this: Lock by DBLink – How to locate the remote session?

There is also some additional/specific material about some issues and bugs in this regard here: Tag: LOCK.

I hope it helps!
Cheers!

Career, Community

IT Platform Pitfalls: Session at LaSalle Innovation and Technology Week

 , , ,

Hey all!

Just passing by to confirm we got invited to speak at the LaSalle Innovation and Technology week. Check out this link and confirm you presence right away!
This is an event open to public (free!) and we’ll be speaking over IT Platform careers.

I said we, because I’l be talking with my friends Dieison Santos (author of this Blog) and Marcelo Lermen.

Find the complete schedule for the week here.

WhatsApp Image 2018-07-04 at 18.57.27 (1).jpeg

Meet you there!
Cheers!

ASM, Database, Shell Scripts

Script: Map ASM Disks to Physical Devices

 , ,

Hey all!
So, I had to map a couple ASM disks to physical devices. But it’s not direct, which causes some manual work.

To save me from this, I found this great post by Alejandro Vargas, with a very nice script to make this mapping easier.

I found however, it was done for RHEL/OEL 6 and older, and I’m in OEL7. So I did some small changes to adapt it.

Anyway, decided to share as this is a great script to have handy. 🙂

# Alejandro’s script (RHEL/OEL 6 and older):

#!/bin/ksh
for i in `/etc/init.d/oracleasm listdisks`
do
v_asmdisk=`/etc/init.d/oracleasm querydisk $i | awk  '{print $2}'`
v_minor=`/etc/init.d/oracleasm querydisk $i | awk -F[ '{print $2}'| awk -F] '{print $1}' | awk '{print $1}'`
v_major=`/etc/init.d/oracleasm querydisk $i | awk -F[ '{print $2}'| awk -F] '{print $1}' | awk '{print $2}'`
v_device=`ls -la /dev | grep $v_minor | grep $v_major | awk '{print $10}'`
echo "ASM disk $v_asmdisk based on /dev/$v_device  [$v_minor $v_major]"
done

# Adjustments by Matheus (RHEL/OEL7):

#!/bin/ksh
for i in `/usr/sbin/oracleasm listdisks`
do
v_asmdisk=`/usr/sbin/oracleasm querydisk -d $i | awk '{print $2}'`
v_minor=`/usr/sbin/oracleasm querydisk -d $i | awk -F[ '{print $2}'| awk -F] '{print $1}' | awk -F, '{print $1}'`
v_major=`/usr/sbin/oracleasm querydisk -d $i | awk -F[ '{print $2}'| awk -F] '{print $1}' | awk -F, '{print $2}'`
v_device=`ls -la /dev | grep $v_minor | grep $v_major | awk '{print $10}'`
echo "ASM disk $v_asmdisk based on /dev/$v_device [$v_minor $v_major]"
done

# Example of execution:

[root@greporasrv]$ for i in `/usr/sbin/oracleasm listdisks`
> do
> v_asmdisk=`/usr/sbin/oracleasm querydisk -d $i | awk '{print $2}'`
> v_minor=`/usr/sbin/oracleasm querydisk -d $i | awk -F[ '{print $2}'| awk -F] '{print $1}' | awk -F, '{print $1}'`
> v_major=`/usr/sbin/oracleasm querydisk -d $i | awk -F[ '{print $2}'| awk -F] '{print $1}' | awk -F, '{print $2}'`
> v_device=`ls -la /dev | grep $v_minor | grep $v_major | awk '{print $10}'`
> echo "ASM disk $v_asmdisk based on /dev/$v_device [$v_minor $v_major]"
> done
ASM disk "DATA01" based on /dev/sdg1 [8 97]
ASM disk "DATA02" based on /dev/sdh1 [8 113]
ASM disk "DATA03" based on /dev/sdi1 [8 129]
ASM disk "DATA04" based on /dev/sdj1 [8 145]
ASM disk "FRA01" based on /dev/sdk1 [8 161]

Hope you enjoy it like I did.
Cheers!

Database, SQL | PLSQL

Oracle Virtual Columns

 , ,

Hi All!
I was reviewing some features in Oracle and, basically, every single time I review them I find something new. Seems Oracle Databases’ features are near to infinite and we frequently find some that can really add value to our solutions.

So I decided to make a serie of posts with really quick notes about each one of them.
You can see all posts in this serie in my page of posts and some others more.

Ready? Here it goes:

Virtual Columns

Since 11g is possible to create columns based on functions, not physically stored on database. See the example below:

create table sales
    (
       sales_id      number,
       cust_id       number,
       sales_amt     number,
       sale_category varchar2(6)
       generated always as
       (
          case
            when sales_amt <= 10000 then 'LOW' 
            when sales_amt > 10000 and sales_amt <= 100000 then 'MEDIUM' 
            when sales_amt > 100000 and sales_amt <= 1000000 then 'HIGH'
            else 'ULTRA'
          end
       ) virtual
   );

It’s also interesting to raise that starting on Release 2, virtual columns can be used as foreign keys. Should be used as FK, not sure… but can….

Cheers!