Easy and fast environment framework

Getting tired of typing and setting your ORACLE_HOME, ORACLE_SID and troubleshooting your . oraenv ?
going/connecting to different client sites with different platforms and server environments sometimes it is frustrating just how long it takes to get a proper SQL*Plus environment

So I always make use of this script
http://karlarao.tiddlyspot.com/#%5B%5Bstep%20by%20step%20environment%5D%5D

and for every server that I access I just have to do three bits of typing:

. ~oracle/.karlenv <<– set the environment
2 <<– choose an instance
s <<– rlwrap'd sqlplus alias

see example usage below:

[root@desktopserver ~]# su - oracle
[oracle@desktopserver ~]$
[oracle@desktopserver ~]$ vi .karlenv      <-- copy the script from the "Install environment framework - karlenv" section of the wiki link above
[oracle@desktopserver ~]$
[oracle@desktopserver ~]$ ls -la | grep karl
-rw-r--r--  1 oracle dba   6071 Dec 14 15:58 .karlenv
[oracle@desktopserver ~]$
[oracle@desktopserver ~]$ . ~oracle/.karlenv      <-- set the environment
<HOME_LIST>
<HOME NAME="Ora11g_gridinfrahome1" LOC="/u01/app/11.2.0/grid" TYPE="O" IDX="1" CRS="true"/>
<HOME NAME="OraDb11g_home1" LOC="/u01/app/oracle/product/11.2.0/dbhome_1" TYPE="O" IDX="2"/>
</HOME_LIST>
<COMPOSITEHOME_LIST>
</COMPOSITEHOME_LIST>


 1-       +ASM
 2-         dw

Select the Oracle SID with given number [1]:
2      <-- choose an instance
Your profile configured for dw with information below:
The Oracle base has been set to /u01/app/oracle

ORACLE_SID=dw
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

oracle@desktopserver.local:/home/oracle:dw
$ s      <-- rlwrap'd sqlplus alias, also you can use the "s1" alias if you don't have rlwrap installed

SQL*Plus: Release 11.2.0.3.0 Production on Thu Jan 5 15:41:15 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP and Real Application Testing options


USERNAME             INST_NAME    HOST_NAME                 SID   SERIAL#  VERSION    STARTED  SPID            OPID  CPID            SADDR            PADDR
-------------------- ------------ ------------------------- ----- -------- ---------- -------- --------------- ----- --------------- ---------------- ----------------
SYS                  dw           desktopserver.local       5     8993     11.2.0.3.0 20111219 27483           24    27480           00000000DFB78138 00000000DF8F9FA0


SQL> @gas      <-- calling one of Kerry's scripts from the /home/oracle/dba/scripts directory

 INST   SID PROG       USERNAME      SQL_ID         CHILD PLAN_HASH_VALUE        EXECS       AVG_ETIME SQL_TEXT                                  OSUSER                         MACHINE
----- ----- ---------- ------------- ------------- ------ --------------- ------------ --------------- ----------------------------------------- ------------------------------ -------------------------
    1     5 sqlplus@de SYS           bmyd05jjgkyz1      0        79376787            3         .003536 select a.inst_id inst, sid, substr(progra oracle                         desktopserver.local
    1   922 OMS        SYSMAN        2b064ybzkwf1y      0               0       50,515         .004947 BEGIN EMD_NOTIFICATION.QUEUE_READY(:1, :2 oracle                         desktopserver.local

SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP and Real Application Testing options
oracle@desktopserver.local:/home/oracle:dw

BTW, parts of the code came from this blog by Coskan Gundogar http://goo.gl/cqRPK and I’ve edited/added some lines on the setsid and showsid sections making it suitable for most unix(solaris,aix)/linux environments

Also I’ve added lines of code before and after the setsid and showsid to get the following info:

• software homes installed

– very useful on troubleshooting if you have multiple homes (RDBMS/Grid Infra/EMGC)

• get DBA scripts location

– quickly plugin your script folders by editing the “# SCRIPTS LOCATION” section

• set alias

– quickly add alias by editing the “# ALIAS” section

Hope I’ve shared you some good stuff!

Mining EMGC Notification Alerts

The past few days, I’ve been troubleshooting an email alerting problem on an EMGC 11.1 install on a client site.
But this troubleshooting also includes setting up my own email server and reproducing the problem http://karlarao.tiddlyspot.com/#EmailNotificationsNotSending
Watching/digging on the EM web based interface is tedious so I devised a way to just mine on the MGMT views… thanks to the MOS 421499.1 EMDIAG REPVFY Kit – which is a good source for mining the EMGC repository.

This simple query from the MGMT views will output something like this

SELECT t.target_name, t.target_type,
       substr(s.message,1,50) , substr(l.message,1,11) emailed,
       TO_CHAR(s.load_timestamp,'DD-MON-YYYY HH24:MI:SS') loaded,
       s.severity_guid,
       TO_CHAR(a.timestamp,'DD-MON-YYYY HH24:MI:SS') annotated,
       TO_CHAR(l.timestamp,'DD-MON-YYYY HH24:MI:SS') logged
FROM    mgmt_severity s, mgmt_targets t, mgmt_annotation a, mgmt_notification_log l
WHERE   
       s.target_guid     = t.target_guid
  AND  s.severity_guid   = a.source_obj_guid (+)
  AND  s.severity_guid   = l.source_obj_guid (+)
  -- AND a.annotation_type = 'NOTIFICATION'
  -- AND lower(s.message) like '%tablespace%percent%'
  -- AND lower(t.target_name) like '%mta%'
  -- AND s.severity_guid = 'B33A7C7DF7A0293CE040460A22EA5E84'
ORDER BY s.load_timestamp desc;

and with this data you can actually do filters.. lets say search for the tablespace alerts test cases that I created. This shows that it reached the metric threshold but it did not send the email because the “Emailed” column shows as as “null”

another thing you can do is have a query that shows monthly count of tablespace alerts that were sent or not sent..

select count(*), TO_DATE(loaded,'MON') loaded, emailed from (
SELECT t.target_name, t.target_type,
       substr(s.message,1,20) , substr(l.message,1,11) emailed,
       TO_CHAR(s.load_timestamp,'MON') loaded,
       s.severity_guid,
       TO_CHAR(a.timestamp,'DD-MON-YYYY HH24:MI:SS') annotated,
       TO_CHAR(l.timestamp,'DD-MON-YYYY HH24:MI:SS') logged
FROM    mgmt_severity s, mgmt_targets t, mgmt_annotation a, mgmt_notification_log l
WHERE   
       s.target_guid     = t.target_guid
  AND  s.severity_guid   = a.source_obj_guid (+)
  AND  s.severity_guid   = l.source_obj_guid (+)
  AND  lower(s.message) like '%tablespace%percent%'
ORDER BY s.load_timestamp desc
)
group by loaded, emailed
order by loaded desc;

and for sure there’s much more things you can do with all of these filters

— AND a.annotation_type = ‘NOTIFICATION’ <– annotated as NOTIFICATION
— AND lower(s.message) like ‘%tablespace%percent%’ <– search string on the alert message
— AND lower(t.target_name) like ‘%mta%’ <– search string on the target name
— AND s.severity_guid = ‘B33A7C7DF7A0293CE040460A22EA5E84′ <– search the alert ID

That’s it for now.. hope I’ve shared you some good stuff

Oracle Database Appliance (ODA) Installation / Configuration

Earlier Oracle announced the Oracle Database Appliance which is a really cool RAC-in-a-box. And here at Enkitec office we are very lucky to get our hands dirty and play with this new beast On the photo below you will see the Oracle Database Appliance.

Andy Colvin has some detailed reviews about the Oracle Database Appliance.. check out these links if you want to see the internals of the machine
http://blog.oracle-ninja.com/2011/09/inside-the-oracle-database-appliance-part-1/
http://blog.oracle-ninja.com/2011/09/oracle-announces-oracle-database-appliance/

But this post will walk you through the installation and configuration of the Oracle Database Appliance.. well all I can say.. at the end of the ODA installation, all I had was 8 screenshots and that’s it. Complete install of the Grid infrastructure, ASM diskgroups, RDBMS software, and a fully functional clustered database in just two hours. Compared to a similar recent RAC project, the installation alone required about 80 screenshots.. and took a couple of days.. and involved multiple teams in the IT group. That’s the wonder of the super simplified installation using the Oracle Appliance Manager or the OAK Configurator which you will see below:

And again all of that took just 2hours to have a 2node RAC..

Hope I’ve shared you some good stuff .. Keep posted for more ODA at Enkitec Blogs!