Monday, November 10, 2014

OpenWorld and SLOUG Presentations Update

Just a short post linking to my presentations at Oracle OpenWorld 2014 and St. Louis Oracle User Group (SLOUG) Big Bash.




Saturday, July 5, 2014

Provisioning Databases with Oracle Multitenant Self Service Application

Uploaded slide from my KScope 14 presentation: "Databases for the Masses: Self-Service Oracle Multitenant with APEX"


Here are a couple of short demos on using the PDBSS application.





Friday, June 6, 2014

Automatic RAC Recovery (Database Cloning)

For the past couple of months I've been working on a project to automatically clone a three node RAC database on AIX to a single instance database from storage snapshots. The clone was to be done daily after our ETL process had completed and was used to offload reporting as well as other purposes.
Initially I thought this would be quite straightforward having done clones using RMAN before. How hard could it be?
The procedure to clone the database is summarized as follows:

1. Generate controlfile  creation script
2. Identify cloned LUNS
3. Rename cloned disk groups e.g. DG_DATA -> DG_DATA_CLONE
4. Mount cloned disk groups
5. Modify/Create pfile or use existing pfile for clone
6. Setup environment variables for clone
7. Create audit directories for clone
8. Startup clone instance in nomount mode and re-create controlfile from script
9. Recover clone database (Apply archivelogs from source DB)
10. Open database with resetlogs
11. Rename database using NID
12. Create new temp tablespace
13. Create entry in /etc/oratab

Step 9 is where things started to get a bit tricky. I could recover the database manually but automatic didn't work. The Oracle Database Backup & Recovery Guide basically describes this as follows:

If you use an Oracle Real Application Clusters configuration, and if you are performing incomplete recovery or using a backup control file, then the database can only compute the name of the first archived redo log file from the first redo thread. You may have to manually apply the first log file from the other redo threads. After the first log file in a given thread has been supplied, the database can suggest the names of the subsequent logs in this thread.

What this means is that issuing a RECOVER DATABASE statement may only apply logs from the first redo thread correctly, and you may see the following errors.

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 178995131 generated at 06/07/2014 09:32:39 needed for thread
1
ORA-00289: suggestion :
+DG_ARCH_NEMOCLN/nemo/archivelog/2014_06_07/thread_1_seq_3056.1280.849605577
ORA-00280: change 178995131 for thread 1 is in sequence #3056


Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 178995131 generated at  needed for thread 2
ORA-00289: suggestion : +DG_ARCH_NEMOCLN


Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00308: cannot open archived log '+DG_ARCH_NEMOCLN'
ORA-17503: ksfdopn:2 Failed to open file +DG_ARCH_NEMOCLN
ORA-15045: ASM file name '+DG_ARCH_NEMOCLN' is not in reference form


ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01195: online backup of file 1 needs more recovery to be consistent
ORA-01110: data file 1: '+DG_DATA_NEMOCLN/nemo/datafile/system.260.792524565'
The remaining log will have to be computed and applied manually. To compute the logs needed to recover the database you can execute the following query against the source database.
select thread#, name from v$archived_log where &change_number between first_change# and next_change#;
After finding the correct logs you can then manually apply them to the database. So how can we automate this step?
Since I was working with NetApp Snap Creator Framework to do the storage snapshots I reached out to my friend Neto who happens to work at NetApp and he provided a simple but elegant solution using named pipes.
I hacked up a script to perform steps 9-12. It assumes that the cloned database is already mounted and accepts the name of the target database as a parameter. Feel free to modify or improve as needed.

Many thanks Nascimento (https://twitter.com/@rodrigontap) and neto from Brazil (https://twitter.com/@netofrombrazil) from NetApp for their help.

You can find the code on github.

Tuesday, March 25, 2014

Get on the Fast Track to DBaaS with EM12c - Part I

Database-as-a-Service (DBaaS) has now become a buzzword in the IT industry. Organizations are starting to realize the benefits of database as a service thanks to the ubiquity of "The Cloud".
Companies relying on Oracle databases for managing their data can also get on the DBaaS fast track thanks to Oracle Enterprise Manager Cloud Control 12c. Using EM12c you can enable self-service and rapid provisioning of Oracle databases and enjoy all the benefits (well most) of Cloud computing.  EM12c supports both IaaS and PaaS models. These are also tightly integrated with Oracle VM Server along with their engineered systems including Oracle Exadata Database Machine.

Setting up and configuring DBaaS requires several steps. While these are well-documented, it seems like a daunting task for those who are unfamiliar with process. A list of steps required to setup DBaaS include the following:

■Create Roles and Users
■Setup Privilege Delegation
■Add Hosts
■Configure Software Library
■Create PaaS Infrastructure Zone
■Setup Software Pools
■Setup Quotas
■Create Provisioning Profiles
■Create Service Templates

Thanks to the Rapid Start Kit we can setup a fully configured DBaaS environment in minutes.

Rapid Start Kit

The Rapid Start Kit consists of a set of Python scripts which call EMCLI commands and a setup of XML config files. The RSK is located in MW_HOME/plugins/oracle.sysman.ssa.oms.plug_12.1.0.7.0/dbaas/setup

cd /u01/app/oracle/Middleware/plugins/oracle.sysman.ssa.oms.plugin_12.1.0.7.0/dbaas/setup
$ ls
 config  database_cloud_setup.py  exadata_cloud_setup.py

The exadata_cloud_setup.py script is the one that  is executed to setup DBaaS. Don't worry, it doesn't require an Exadata machine.

You can view the command line options for the scripts by running emcli in script mode.

[oracle@gridoms01 setup]$ emcli @exadata_cloud_setup.py -help
====================================================================================================
Cloud Setup Tool
====================================================================================================
Usage: emcli @exadata_cloud_setup.py
                     -pdbaas
                         This flag is used to setup Cloud for Pluggable Database
                     -dbaas
                         This flag is used to setup Cloud for Database as service
                     -db_list
                         comma separated list of 'Target Name'(s) for 'Cluster Database' targets discovered in Enterprise Manager.
                         Choose Container Databases for PDbaaS.
                        Make sure all the Container Databases have same Database credentials and same Host Credentials and are on same platform
                         e.g -db_list=CDB1,CDB2,CDB3
                     -host_list
                         comma separated list of 'Target Name'(s) for 'Host' targets discovered in Enterprise Manager.
                     -db_home_location
                         Database Home location
                         e.g -db_home_location=/u01/app/oracle/11.2.0/dbhome1
                     -cloud_input=
                         Cloud input XML defines Enterprise Manager entities created as part of Cloud Setup.
                         Default file config/dbaas_cloud_input.xml will be used for database setup
                         Default file config/pdbaas_cloud_input.xml will be used for pluggable database setup.
                         Pass custom input xml file to override default input file.
                    -help
                         Displays Cloud Setup help

     For example:
        Setup Cloud on Exadata Machine: Run script on first node of Exadata Machine:
             emcli @exadata_cloud_setup.py : Sets up Cloud for PDBaaS if CDB is available in Enterprise Manager. Setups up DBaaS otherwise.
             emcli @exadata_cloud_setup.py -pdbaas : Sets up Cloud for PDBaaS
            emcli @exadata_cloud_setup.py -dbaas  : Sets up Cloud for DBaaS

       Setup Cloud on Non-Exadata Cluster Machine: Run script on cluster node or any standalone Machine:
             emcli @exadata_cloud_setup.py -pdbaas -db_list=CDB1,CDB2,CDB3   : Sets up Cloud for PDBaaS using  given list of Pluggable databases
             emcli @exadata_cloud_setup.py -dbaas -host_list=host1,host2 -db_home_location= : Sets up Cloud for DBaaS using  given list of hosts and oracle home

You should edit the dbaas_cloud_input.xml configuration file to change the default role and credential names as well as default passwords.
[oracle@gridoms01 config]$ vi dbaas_cloud_input.xml

To setup a Pluggable database as a service (PDBaaS) environment, you need to have an existing Oracle 12c RAC or single-instance database using ASM for database storage. To find the the list of container databases we can use the emcli get_targets command as shown below.
$ emcli get_targets -target="oracle_pdb"
Status  Status           Target Type           Target Name
 ID
1       Up               oracle_pdb            racdb_CDBROOT
0       Down             oracle_pdb            racdb_RACPDB
0       Down             oracle_pdb            racdb_RAC_PDB1
0       Down             oracle_pdb            racdb_RAC_PDB2

The screenshot below is an example of setting up a PDBaaS on a RAC 12c cluster.

After completing the setup simply login as one of the SSA users.

Part II will show how SSA users access the Self-Service Portal to request databases.

Sunday, March 2, 2014

Useful EMCLI commands for Pluggable Database-As-A-Service

This is a quick note on some useful EMCLI commands for enabling and managing Pluggable Database-as-a-Service (PDBaaS).

Get Paas Zone details
emcli get_paas_zone_detail -name="DBA_Test_Zone"

  Name                            DBA_Test_Zone
  Named Credentials               NC_HOST_2012-10-20-160312
  Number of Hosts                 5
  Roles                           CLOUD_DBA
  Maximum Memory Allocation (%)   90
  Maximum CPU Utilization (%)     80

Update PaaS zone placement policy constraints
emcli update_paas_zone -name="DBA_Test_Zone" -memory_utilization=99 -cpu_utilization=99
PaaS Infrastructure Zone "DBA_Test_Zone" updated successfully. 

Add host to PaaS Zone
emcli update_paas_zone -name="DBA_Test_Zone" -add_hosts="linuxhost3.example.com"
PaaS Infrastructure Zone "DBA_Test_Zone" updated successfully.

Find the members of a PaaS Zone
emcli get_system_members -name="DBA_Test_Zone" -type="self_service_zone"
Source Target Name  Member Target Name         Member Target Type  Level  Key Member
DBA_Test_Zone       Schema_Pool_11203          schaas_pool         1
DBA_Test_Zone       Schema_Pool_11202          schaas_pool         1
DBA_Test_Zone       aixhost1.example.com       host                1
DBA_Test_Zone       linuxhost1.example.com     host                1
DBA_Test_Zone       aixhost2.example.com       host                1
DBA_Test_Zone       aixhost3.example.com       host                1
DBA_Test_Zone       linuxhost2.example.com     host                1
DBA_Test_Zone       linuxhost3.example.com     host                1
DBA_Test_Zone       Single Instance AIX Pool   oracle_cloud_zone   1
                                                                                                                               
Create a PDB Pool
emcli create_pool -name="Pluggable DB Pool" -target_type="pdbaas_pool" -paas_zone="DBA_Test_Zone" -members="orclcdb" -description="Oracle Database 12c Pool for Pluggable Databases" -member_constraints="PLATFORM_CDB=226,TARGET_TYPE_CDB=oracle_database,VERSION_CDB=12.1.0.1.0"
Software Pool "Pluggable DB Pool" created successfully.

View the constraints for a PDBaaS pool
emcli get_pool_allowed_member_constraints -target_type=pdbaas_pool
Name                  Value                 Description

PLATFORM_CDB          197                   HP-UX Itanium
                      59                    HP-UX PA-RISC (64-bit)
                      212                   IBM AIX on POWER Systems (64-bit)
                      209                   IBM: Linux on System z
                      46                    Linux x86
                      226                   Linux x86-64
                      912                   Microsoft Windows (32-bit)
                      208                   Microsoft Windows Itanium (64-bit)
                      233                   Microsoft Windows x64 (64-bit)
                      23                    Oracle Solaris on SPARC (64-bit)
                      267                   Oracle Solaris on x86-64 (64-bit)

TARGET_TYPE_CDB       rac_database          Cluster Database
                      oracle_database       Database Instance

VERSION_CDB           12.1.0.1.0            12.1.0.1.0
                                                                                                                                                
Display details about the Pool
emcli get_pool_detail -name="Pluggable DB Pool" -target_type=pdbaas_pool

  Name                            Pluggable DB Pool
  Target Type                     pdbaas_pool
  Description                     Oracle Database 12c Pool for Pluggable Databases
  Paas Infrastructure Zone        DBA_Test_Zone
  Number of Members               1
  Placement Constraints
  Member Constraints              PLATFORM_CDB : Linux x86-64 , TARGET_TYPE_CDB : Database Instance
                                   , VERSION_CDB : 12.1.0.1.0
Find all pluggable databases
emcli get_targets -target="oracle_pdb"
Status  Status           Target Type           Target Name
 ID
1       Up               oracle_pdb            racdb_CDBROOT
0       Down             oracle_pdb            racdb_RACPDB
0       Down             oracle_pdb            racdb_RAC_PDB1
0       Down             oracle_pdb            racdb_RAC_PDB2

Get a list of all Pluggable databases, pools, database clouds and PaaS zones
emcli get_targets -target="oracle_pdb;%pool;cloud;%zone"
Status  Status           Target Type           Target Name
 ID
-9      n/a              cloud                 Middleware and Database Cloud
-9      n/a              oracle_cloud_zone     DBAAS_Pool_AIX
-9      n/a              oracle_cloud_zone     DB12cPool
-9      n/a              oracle_cloud_zone     Dev 11203 Linux VM
1       Up               oracle_pdb            racdb_CDBROOT
0       Down             oracle_pdb            racdb_RACPDB
0       Down             oracle_pdb            racdb_RAC_PDB1
0       Down             oracle_pdb            racdb_RAC_PDB2
-9      n/a              pdbaas_pool           PDB Pool
-9      n/a              schaas_pool           DBAAS_SCHAAS
-9      n/a              schaas_pool           Dev 11203 AIX
-9      n/a              self_service_zone     Lab Zone (Linux)
-9      n/a              self_service_zone     Lab Zone (AIX)


Here is a list of more useful PDBaaS/DBaaS verbs 

PaaS Zone verbs
create_paas_zone      -- To Create a PaaS Infrastructure Zone
delete_paas_zone      -- To Delete a PaaS Infrastructure Zone
get_paas_zone_detail  -- To get PaaS Infrastructure Zone details
update_paas_zone      -- To Update a PaaS Infrastructure Zone

Software and Database Pool verbs
create_pool           -- To Create a Software Pool
delete_pool           -- To Delete a Software Pool
get_pool_allowed_member_constraints           -- To get possible member constraint values of a Software Pool based on its target type
get_pool_allowed_placement_constraints        -- To get the placement constraints of a Software Pool based on its target type
get_pool_capacity     -- To get Software Pool capacity
get_pool_detail       -- To get Software Pool details
get_pool_filtered_targets     -- To get the filtered targets for a Software Pool based on the criteria passed
update_pool           -- To Update a Software Pool

Quota verbs
create_dbaas_quota    -- To Create Database Quota for a SSA User Role
delete_dbaas_quota    -- To Delete the Database Quota setup for a SSA User Role
get_dbaas_quota       -- To List the Database Quota setup for all SSA User Roles
update_dbaas_quota    -- To Update the Database Quota for a SSA User Role

Profile verbs
create_dbprofile    -- Create Database Profile
delete_dbprofile    -- Delete Database Profile
describe_dbprofile_input    -- Describe Database Profile Input
list_dbprofiles     -- List Database Profiles

Thursday, February 20, 2014

Configuring Self-Service Login Page

Oracle Enterprise Manager Cloud Control 12c allows us to customize the page that self service users see when logging in instead of the usual EM Console login page. This is useful for Cloud Providers who offer private cloud services to their clients. With just a few small steps you can have your own customized login  page.

Enable the SSA login page
$ emctl set property -name oracle.sysman.ssa.logon.ssa_oms -value true
Oracle Enterprise Manager Cloud Control 12c Release 2
Copyright (c) 1996, 2012 Oracle Corporation.  All rights reserved.
SYSMAN password:
Property oracle.sysman.ssa.logon.ssa_oms for oms oms.example.com:4889_Management_Service has been set to value true

OMS restart is required to reflect the new property value

Set a custom alternate text message for the Cloud Provider on the login page. 
$ emctl set property -name oracle.sysman.ssa.logon.cloud_provider_alt_text -value "Nelson Cloud Provider"
Oracle Enterprise Manager Cloud Control 12c Release 2
Copyright (c) 1996, 2012 Oracle Corporation.  All rights reserved.
SYSMAN password:
Property oracle.sysman.ssa.logon.cloud_provider_alt_text has been set to value Nelson Cloud Provider for all Management Servers
OMS restart is required to reflect the new property value

Set a custom alternate text message for the tenant on the login page:
$ emctl set property -name oracle.sysman.ssa.logon.cloud_tenant_alt_text -value "Nelson Cloud Tenant"
Oracle Enterprise Manager Cloud Control 12c Release 2
Copyright (c) 1996, 2012 Oracle Corporation.  All rights reserved.
SYSMAN password:
Property oracle.sysman.ssa.logon.cloud_tenant_alt_text has been set to value Nelson Cloud Tenant for all Management Servers
OMS restart is required to reflect the new property value

After restarting the OMS the SSA login page should look like the one in the figure below.

Figure 1. SSA login page with custom text

Enable Cloud Provider logos
$emctl set property -name oracle.sysman.ssa.logon.show_cloud_provider_brand -value true
Oracle Enterprise Manager Cloud Control 12c Release 2
Copyright (c) 1996, 2012 Oracle Corporation.  All rights reserved.
SYSMAN password:
Property oracle.sysman.ssa.logon.show_cloud_provider_brand has been set to value true for all Management Servers
OMS restart is required to reflect the new property value

Enable Cloud Tenant logos
$ emctl set property -name oracle.sysman.ssa.logon.show_cloud_tenant_brand -value true
Oracle Enterprise Manager Cloud Control 12c Release 2
Copyright (c) 1996, 2012 Oracle Corporation.  All rights reserved.
SYSMAN password:
Property oracle.sysman.ssa.logon.show_cloud_tenant_brand has been set to value true for all Management Servers
OMS restart is required to reflect the new property value
Restart OMS

The following logos are required:
  • cloud_provider_small_brand.png 
  • cloud_tenant_small_brand.png
  • cloud_provider_large_brand.png
  • cloud_tenant_large_brand.png
The two _small_ logos appear as headers in the login portal and the two _large_ logos appear on the login page. If two _small_ logos are used then they should be less than 200 * 20 px each. If a single _small_ image is used then it should be less than 500 * 20 px.
The _large_ logos should be less than 250 * 50 px if two images are used or less than 525 * 50 px for a single image.

Copy logos to $ORACLE_HOME/sysman/config
$ pwd
/u01/app/oracle/Middleware/oms/sysman/config
$ ls *.png
cloud_provider_large_brand.png  cloud_provider_small_brand.png  cloud_tenant_large_brand.png  cloud_tenant_small_brand.png
[oracle@oms config]$ file cloud_provider_small_brand.png
cloud_provider_small_brand.png: PNG image data, 195 x 19, 8-bit/color RGB, non-interlaced
[oracle@oms config]$ file cloud_tenant_small_brand.png
cloud_tenant_small_brand.png: PNG image data, 195 x 15, 8-bit/color RGB, non-interlaced

After restarting the OMS the login page should display the logos similar to the one in the figure below.

Figure 2. SSA login page with Cloud Provider and Tenant Logos
You can also add a disclaimer text and copyright text. Unfortunately, other attributes such as background colors and custom style sheets cannot be modified but hopefully this will  be enhanced in future versions. 

Sunday, January 26, 2014

Look who's tweeting now - Sending OEM Notifications via Twitter


Disclaimer: The steps described in this post have only been tested in a lab environment. Verify that your organization's security policy isn't being violated when doing any of these steps. Twitter is a public social networking platform and has inherent security risks. I will not accept responsibility if you perform this in your production environment.

Many applications built in the past couple of years have integrated social networking widgets (useful or not) to connect a social world via Twitter, Facebook, Google+ etc. However, most enterprise applications still rely on email as the primary notification method.
Oracle Enterprise Manager Cloud Control 12c allows us to send notifications for incidents, events, jobs out of the box via email. In addition, EM12c supports advanced notification methods including OS scripts, PL/SQL scripts and SNMP traps. For the complete description of Notification Methods you can read the chapter in the Oracle Enterprise Manager Cloud Control 12c documentation.
However, wouldn't it be cool if we were able to also get tweets from OEM as a notification? Yes - I'm a twitter junkie!

Oracle Enterprise Manager's OS command should theoretically allow us to write scripts do this.
The next question was - "How do I write a script that calls twitter APIs?". After some googling it became evident that calling twitter APIs would require using OAuth and/or some kind of wrapper
 in Python or Java. I came across a few solutions that required creating and registering a new application for accessing Twitter using Python but this seemed like overkill for my use case.

After a few hours of not finding a suitable solution I remembered that I had started using a command-line Twitter client called TTYtter for a few months now on my Ubuntu netbook. TTYtter is completely Perl-based client which has a lot of functionality. It runs in interactive or script mode which makes it a perfect candidate for scripting.

Here are the overall steps for sending Tweets from Oracle Enterprise Manager.
  1. Install and authorize TTYtter to post tweets
  2. Define TTYtter script
  3. Deploy the script on OMS 
  4. Register your OS Script as a new Notification Method
  5. Assign the notification method to an incident rule

Install and authorize TTytter

The first step is to download and install TTYtter. It's plain text file so you can review the contents and see what it does. TTYtter requires Perl 5.8.6 or higher and curl 7.20 or higher. These are usually available on Linux and Unix operating systems or can be easily installed. I think it can also run on Windows using Cygwin, however I haven't tested this.

Installation involves saving the perl script in a path on your OMS server. It is convenient to rename the script to ttytter instead of the default filename. Next, we need to go through a one time setup. This involves authorizing TTYtter to post tweets on your behalf.

It is recommended to create a separate twitter account for OEM notifications. This way you can make the account private so that tweets aren't visible on the public timeline.

A step in the setup requires going to the Twitter website to authorize the token generated by TTYtter. This will generate a PIN which you will provide when prompted.

To start the setup simply execute the perl script.

Next we start ttytter to send a test message from the account.
Now would be a good time to review the TTYtter command-line options.
The command below sends a direct message to my my personal twitter account.
./ttytter -ssl=1 -runcommand="/dm leight0nn Test DM from OEM" –silent
Note the HTTP POST now requires that the SSL option is supplied. We can also put the options in a configuration file called .ttytterrc in the user's home directory.
$cat .ttytterrc
ssl=1
hold=1
The hold option is useful if you want to ttytter to retry if there's a problem with twitter. For example the "fail whale".

There are a couple of ways to post messages via ttytter. The simplest method is to call it with the -runcommand option followed by the type of command you want to execute. You can also pass the -script option specifying one or more commands.

After verifying that your tweet has been successfully sent and received create a test script by saving the contents to a file. If you're behind a proxy server you also need to set the http_proxy and https_proxy environment variables.

Define TTYtter script

$cat oemtweet_test.sh
#!/bin/bash
export http_proxy=http://guest:guest@192.168.1.2:80
export https_proxy=http://guest:guest@192.168.1.2:80
/opt/oracle/scripts/ttytter -ssl=1 -runcommand="/dm leight0nn Test DM from OEM" -silent

Register your OS Script as a new Notification Method

The next step is to configure the OS script as a notification method in OEM. As described in the Oracle Enterprise Manager Cloud Control 12c documentation:
Notification system can invoke a custom script when an incident rule matches the OS Command advanced notification action. A custom script receives notifications for matching events, incidents and problem through environment variables.

Log into OEM as a user with Super Administrator privileges. Go to Setup, Notifications Notification Methods.

Under the Scripts and SNMP Traps section select Add OS Command from the drop down list.

Enter a name for the Notification Method and an optional description as well the path for the script on the OMS host. If there are multiple OMS servers the script should be located in the same path, preferably in a shared location. You can also enable repeat notifications by select the checkbox next to "Support repeat notifications". 


Click the "Test OS Command" button to verify that the script is working.
Click OK.

Assign the notification method to an incident rule

Now we need to assign our new Notification method to an Incident Rule. Click Setup, Incidents, Incident Rules to access the Rule Sets. Create a new Rule Set for testing. Apply the rule set to all targets.

Select the Rule Type to apply to Incoming events and updates to events.

For our test we'll create a Rule based on Metric Alert. No filter is needed at this time since we want to test the rule. Click Next.

In the Add Actions screen select the "Always execute the actions" radio button for the Condition for Actions. You may choose to create an incident also but not necessary for our testing.
The Notifications sections is where we'll specify how to get notified. Basic notifications sends an email to the addresses/users provided in the Email To, Email Cc and Page fields. Advanced Notifications is where we'll choose to send twitter notifications based on our OS script.

Specify a name for the Rule and click Next.

The next screen should be similar to the one below. Click Next.

Review the new rule and click Continue.

Click the Save button to save the rule set.

With our rule set created we need to test it by modifying metrics so that they cross the defined thresholds. In this example we can select a suitable database target though any target should work. From the database home page go to Monitoring, All Metrics.

We'll modify the Tablespace Free Space (MB) metric so that it triggers a warning or critical event.

Select values for the new warning and critical thresholds. You can click the test button to see if the event will be triggered based on the metric value in the chart. Click the button to Save the metrics.

It may take a few minutes for the metric to be evaluated and trigger an event. Refresh the page until event triggers. In our example you'll see that the event was triggered for the UNDOTBS1 tablespace. The Last Comment column tells us that it called our Twitter OS script. The exit code of 0 and null error and output means that our script was executed successfully.


If you entered a valid twitter handle in your OS script you should also have received a direct message via twitter.


If there is an error message or the direct message wasn't received you can enable debugging for notification methods by setting the log4j.category.oracle.sysman.em.notification property.
$OMS_HOME/bin/emctl set property -name log4j.category.oracle.sysman.em.notification -value -DEBUG -module logging
Oracle Enterprise Manager Cloud Control 12c Release 3
Copyright (c) 1996, 2013 Oracle Corporation. All rights reserved.
SYSMAN password:
Property log4j.category.oracle.sysman.em.notification has been set to value -DEBUG for all Management Servers
OMS restart is not required to reflect the new property value
With this set you see whenever incident rules triggers notifications by looking at the $OMS_HOME/gc_inst/sysman/log/emoms_pbs.trc file.
The example output below shows that the notification methods for our Twitter rule set being called.

014-01-15 23:43:32,734 [DeliveryThread-OSCMD7] DEBUG notification.pbs logp.251 - updateDeviceContactTS.Device=Twitter Broadcast type=2 message=
2014-01-15 23:43:32,736 [DeliveryThread-OSCMD7] DEBUG notification.pbs logp.251 - Exiting updateDeviceContactTS.Device=Twitter Broadcast type=2
2014-01-15 23:43:32,736 [DeliveryThread-OSCMD14] DEBUG notification.pbs logp.251 - Notification handled for SYSMAN, Twitter Broadcast, 28
2014-01-15 23:43:32,737 [DeliveryThread-OSCMD5] INFO notification.pbs logp.251 - Notifications delivered in this batch: 1
2014-01-15 23:43:32,738 [DeliveryThread-OSCMD14] INFO notification.pbs logp.251 - Notifications delivered in this batch: 1
2014-01-15 23:43:32,739 [DeliveryThread-OSCMD7] DEBUG notification.pbs logp.251 - Notification handled for SYSMAN, Twitter Broadcast, 28
2014-01-15 23:43:32,740 [DeliveryThread-OSCMD7] INFO notification.pbs logp.251 - Notifications delivered in this batch: 2
2014-01-15 23:43:33,628 [NotificationMgrThread] DEBUG notification.pbs logp.251 - queue_ready returned with to=1
2014-01-15 23:43:34,495 [NotificationMgrThread] DEBUG notification.pbs logp.251 - queue_ready returned with to=1
2014-01-15 23:43:34,664 [DeliveryThread-OSCMD12] DEBUG notification.pbs logp.251 - Ready to log getIssueType=1 retValue=1
2014-01-15 23:43:34,666 [DeliveryThread-OSCMD12] DEBUG notification.pbs logp.251 - updateDeviceContactTS.Device=Twitter Broadcast type=2 message=
2014-01-15 23:43:34,667 [DeliveryThread-OSCMD12] DEBUG notification.pbs logp.251 - Exiting updateDeviceContactTS.Device=Twitter Broadcast type=2
2014-01-15 23:43:34,670 [DeliveryThread-OSCMD12] DEBUG notification.pbs logp.251 - Notification handled for SYSMAN, Twitter Broadcast, 282014-01-15 23:43:34,671 [DeliveryThread-OSCMD12] INFO notification.pbs logp.251 - Notifications delivered in this batch: 1


After verifying that the test tweet was successfully sent and delivered it can be enhanced to send some useful information. We can also create a separate script to not just send a direct message but also a broadcast. This way a group of users can follow the OEM Twitter account and receive the tweets. This may be a group of DBAs for example.
Note if you make the account private then the owner of the twitter account will have to approve access to each user who wants to follow the OEM user's tweets.

Passing incidents and events to our TTYtter scripts requires setting and passing environment variables.
There are a number of environment variables that can be passed from OEM. The table below lists the generic environment variables and their descriptions.

Environment VariableDescription
NOTIF_TYPE
Type of notification and possible values
NOTIF_NORMAL,
NOTIF_RETRY,
NOTIF_DURATION,
NOTIF_REPEAT,
NOTIF_CA,
NOTIF_RCA
REPEAT_COUNT
How many times the notification has been sent out
before this notification.
RULESET_NAME
The name of the ruleset that triggered this notification.
RULE_NAME
The name of the rule that triggered this notification.
RULE_OWNER
The owner of the ruleset that triggered this notification.
MESSAGE
The message of the event, incident, or problem.
MESSAGE_URL
EM console URL for this message.

There are environment variables for different event types, incidents and problems. To get the list of environment variables for each event type you can use the query below.


The ENV_VAR_NAME column represents the environment variable that can be passed to the OS script.
Create a new script with the contents below to send a status update (via broadcast).
echo "EM12c: ${RULE_NAME} ${EVENT_NAME} ${TARGET_NAME} ${MESSAGE} ${EVENT_REPORTED_TIME}." | /opt/oracle/scripts/ttytter -script -keyf=/opt/oracle/.ttytterkey
This will send a status update to Twitter and pass the Rule Name, Event Name, Target Name, Message and Event Reported Time variables. Due to twitter's 140 character limit you may have to be very selective on which variables to include. Save  the script and repeat the steps to create a new notification method and Incident Rule Sets using the new notification method. Once complete you should now have a complete framework for sending tweets from OEM.

Hopefully this post will provide some ideas on how OEM's Advanced notifications can be used to make it more social friendly. Now get tweeting and follow me on twitter @leight0nn :-).