The idea of providing databases as a service to customers and companies can be a very foreign idea to the database administrator.  The DBA’s job is to carefully control and dole out resources.  The concept that “in the cloud” the user can just “order up” a database on demand is a difficult one.

How does it work?

Infrastructure

First, the a cloud-based Paas Infrastructure zone must be created.    Without this, the plug-in for the “database as a service” will function.  Once this is setup, then the “Database for a Service” can be completed.

The DBA must create a couple users for the distinct purpose to managing the Paas Infrastructure and the “Database as a Service”.  For the Paas Infrastructure user, once the user is create, you must grant the EM_CLOUD_ADMINISTRATOR role to it.  The “Database as a Service” user must be created and granted the EM_SSA_ADMINISTRATOR  role for it to manage allocation of resources, etc. for users that will choose the “Database as a Service” option.  Any users that are going to utilize the database as a service feature will require the EM_SSA_USER role and the role(s) that will be granted to the infrastructure zones that are created in the upcoming steps.

This is a middleware feature, so keep in mind that this is accessed from the middleware menu drop-downs. You can deploy the infrastructure server pools and zones to control what resources are allowed to be allocated to the each request.

I would recommend setting up roles that you will wish to allocate access to use the database as a service privilege.  You may have one or multiple ones depending on hosts that will be assigned to different cloud groups, development vs. testing, etc., but grant these roles now, which will ease the set up once you set up your zones.

If no Paas Infrastructure exists, it will take you into the setup wizard. You will choose your VM host(s) that will be part of the Paas infrastructure zone, along with any roles you want to grant to the first zone.  Each zone should be treated like you would separate development, test and production or business area or client.  You may have multi-tiered zones which separate development for one department to one VM host, while having a second zone to testing for another department on another VM host.

You will decide how much total memory, CPU and desk space that will be allocated to the zone.  The wizard will ask you to review the choices on the zone before you finalize the zone setup.  Once you have set up each of these infrastructure zones, then the “Database as a Service” an be setup.

Database as a Service

To perform the next step in the setup, you must be logged in as the user you created and granted the EM_SSA_ADMINISTRATOR role to.  This user will then access the wizard by clicking on Setup –> Cloud –> Database.

The first tab has you allocate Database pools that consist of servers/hosts that will make up each database pool.  This feature is dependent on Oracle database software installed  on each of the servers that will be part of each database pool.  Remember that all servers that reside in one pool must have the same version of OS, database software but the Oracle home can exist in several locations and chosen as part of the steps when requesting a database later on.  Named credentials are required for the OS level privileges for the database as a service feature to function.  If you do not have them already set up, ensure to configure them and save them off for future use.  Review your setup and save, but keep in mind that any changes that are required can be performed via the “Edit” option fro the main database pool screen.

The next screen is the “Request Settings” tab.  This screen can automate all settings for requests, including when to delete requests or when to archive.

The next tab, “Quotas” is of significant importance, as it states who assigned access to what Database pools, have what resource allocation they can utilize in their requests.  These are assigned at the role level that we set up in the Paas Infrastructure configuration and now we can allocate amount of memory, CPU, number of schemas and number of databases that each role has privileges to create.

Profiles and Templates tab takes the DBA through the steps to set up sample schema designs and databases for specific database or application use. There are a number of ways that this can be setup, including RMAN backups, (which uses an RMAN duplicate to perform the task…) full database template, new or existing DBCA template or schema template.   If you already have DBCA templates that are saved off for use on the host these can be used in this area to be used for provisioning of the “Database as a Service” requests.  You an save off these templates in the software library for future use and access by the requester by location and database version.  Once submitted, the profile is created and available for use.

By now accessing the Database Cloud Self Service Setup wizard, We will create the service template, the last step in the process.  No plug-in is required, just choose Create –> database/schema.  For this example, we are going to stick to “database”.   The previous template we created was for a database template, so it should be selected and will show in the list, along with any others that have been created and saved.  If it’s a single instance template, then the service template should be set up for single instance vs. RAC, which is also an option.  You can then choose what database prefix you desire, which will control some of the crazy database naming that *could* and very may likely occur, (yes, the requester will choose the database name prefixed by what you choose here….) along with domain name to control the global name settings.  Listener port settings are next.  I didn’t see any way to use multiple port options per zone, but for RAC, a scan listener setup was available.

The next window in the setup will look very familiar to any DBA.  It looks very similar to the setup for databases in the DBCA, (Oracle’s Database Configuration Assistant utility).  This allows the DBA an easy setup via ASM, but will require a defined directory path for datafiles.  This can be difficult for Windows VM users if all drives are not the same for the host pool, (i.e. one server has the G:\ drive available vs. another that has the F:\ drive.)  When Server admins are designing the cloud infrastructure to be used for Oracle and Windows is the OS platform, ensure they understand that having mirror image servers is important.

Security settings in this setup are going to make most DBAs a bit tense.  Yes, all of the databases in this database pool will be have the same password for the SYS, SYSTEM and DBSNMP.  The max configuration complexity is to set it different for each of the three users so that they have individual passwords, but all databases created through this template will have the SYS set the same for whatever you type in for the SYS password field, SYSTEM, etc.

Setting up the flash recovery area will be dependent upon the VM servers assigned to the database pool to be mirror images to each other just as the datafile location will be unless ASM is used.  Setting up a default value for this may be hit or miss, as it may be difficult to judge how each database is used in the end, even if the templates are carefully designed and monitoring for FRA usage through the EM12c should be set up with significant metric thresholds that support this vulnerability.

The service template must be allocated to an Infrastructure zone, as was created in our initial steps.  Once that is done, you will be asked to add your roles to the service template created earlier on, that will then offer those users rights to use this template.

As with any cloud service, it wouldn’t be an EM12c cloud feature without charge back.  Maintenance features for charge back can be accessed from the Enterprise –> Chargeback menu in the dropdown, but for this post, we assume that charge back plans have already been set up, so we will simply continue with the Chargeback tab.  For anyone who is unsure how to set up charge back, I highly recommend ”Enterprise Manager 12c Cloud Control Metering and Chargeback“ to get a feel for this complex task.

The charge back plans are then assigned to the actual Paas Infrastructure zones.  Doing this will ensure that anyone creating databases in these zones are charged correctly for usage of these cloud resources.

Using the Self Service Portal

Within the Self Service Portal, the user can go to the “My Databases” tab click on “Request” to start the process.  The request name will be automatically filled with the user name and the date, along with choices made by the privileges allocated to the individual user.  Single instance/RAC, amount of memory/CPU required, time to deploy the database, (immediate or scheduled) and then create a user for instant access for the administration of the database.  This is an EM job submission, so the job does exist in the EM job tables.  Provisioning will show the progress of the database creation and if any errors or issues arise.  Once completed, we can then re-enter the Self Service Portal and see the database listed in “My Databases”.

Managing the Database

By double-clicking on a database in the “My Databases” list, access is given to the main management page.  The user is able to start/stop the database, view connection information, if any administration work is required, (including backups if they are not already configured) and basic performance information.

Pros and Cons

To have the ability to create a database for development, testing or QA without having to allocate over-stressed DBA resources is a great option.

• DBAs will most likely limit the use to production VM access to the feature to leads in departments or DBA’s for customers of their cloud services.
• Non-DBAs need to understand that backups of databases are not automatically set up when the database is created, so creating scripts to setup backups may be beneficial that can run at the end as part of the service template is recommended.
• Templates can include other post-database creation scripts to automate even more tasks that were once on the DBAs list of demands.
• The security setup is limited.  If SYS/SYSTEM is discovered in one database involved in a template used, it could be used to access other databases.
• Clear understanding of how charge backs work must exist if they’re used.

DBAs should take the time to dig into the “Database as a Feature” and think about the benefits to future in how it will free up their time to take on more fascinating challenges.

No fear of the future, it’s already here….

the user who has been granted access to this feature, will be able to request a database, just like any other automated process that is available via the console.  To perform the task is not any more complicated than requesting a report via an application on the web.

As many know, I joke about being a strange, ADHD kid.  I didn’t really fit in with the other kids and was really, really small for my age.  I was bullied and very much a loner.  Growing up, my closest friend was my middle sister, Kristi who is 2 1/2 years younger.  She was the golden child-  blond hair, blue eyes and very, very sweet.  She was so easy-going that she was often frustrated by other kids, so my easy-going, but, hyper personality was preferable to them and we were happy to hang out together.

We grew up in a very rural area of upper Michigan and spent our days playing make believe in the fields and forests around our house on Lake Michigan.  When we did move to higher population areas, first in Oregon and then in Virginia, we still hung out with each other than our school mates, even though we attended different schools.

I was the maid of honor at Kristi’s wedding to her husband, Jim and we’d visit when I’d come out to Oregon to visit about once a year.  We’d pick up like we’d never been apart, which is common for sisters.  I was happy to see that she and our youngest sister, Kimberly, became very close as they had grown up and they have continued with that relationship.  It’s hard not to like Kristi, she is simply “likeable”.

She turned 43 this year and as she’s always been a bit of a health-nut, appeared to be in the prime of her life.  She had no clear “cancer indicators” when she started having symptoms in January and it wasn’t until February that the doctor’s discovered she had what appeared to be colon cancer.  Within weeks, scans came back showing she not only had colon cancer, it had spread to her liver.

Both my parents have had their challenges with cancer.  My Mother had a battle with uterine cancer and my father, a version of leukemia, but both bounced back rather quickly with treatment.  We respected Kristi’s desire to be private and expected her to make a full recovery.

This hasn’t happened.  She is now, just a couple months later, stage 4 colon cancer.  No treatment has hindered the progress of the cancer’s spread through her body and she is in her final battle.  The ravages of the disease are not just physical, they have brought a horrible financial burden as her family has struggled to save her.

The feeling of helplessness is difficult.  I, as many in my field can understand the need to “fix things” or at least make things better and there is so little I can do.  My sister’s friend’s have put together a contribution site to assist in their financial struggle to help with the cost of her treatments.  It makes the pain a little less knowing I can help ease the financial burden even if I can’t do anything to help save my sister.

If you would like to help Kristi’s and our family, click here.

Many of you know that I’m the mother of three, very bright, “equally ADHD as their mother” children.  As I’ve been tasked with a number of interesting challenges with both RMOUG’s and ODTUG’s WIT, (Women in Technology) events, I’ve started to investigate how, no matter what we do for women today, unless we start focusing on the women of tomorrow, we are lost.

I spent some time the last couple weeks investigating the offerings by my local school districts for technical education and am alarmed for both genders.  There are “Technology Plan Templates” that detail out the dismal specifications of what hardware will be provided in the classroom, but no mention of what to attain with it.  No one lists out what educational goals, what students hoped to achieve with the said technical hardware.

Now what were these marvel in educational opportunities offered in the way of software?

• Windows XP.
• Tutorials on how to use a USB flash drive.
• How to “Navigate the network”

The labs were beginning classes in Microsoft Office and how to use email.  What kid, by the time they are in mid-elementary, lack knowledge in9 basic tasks in MS Office??

I looked on the clubs link, as I remember computer and programming were often found in clubs, along with classes, in my high school.  This was what is now offered:

Sigh….

I assumed the technical classes had to be missing from the website, (I know, seemed like an oxy-moron to me, too, but I was willing to give the benefit of the doubt…. )  so I called the school.  I was politely informed that all classes for any student that are interested in technical educatiin are offered at Bollman Technical Education Center.

I remember this school, as it was what was recommended to my oldest when he was having challenges that were administration in nature, but had been classified by the school as “behavioral”.  If this doesn’t concern you right off, we’ll discuss it here in a minute, but first I wanted to make sure that there hadn’t been a “revamp” of the school.

No, Bollman is still considered a school that “benefits the student who is more productive when doing than learning in a more traditional academic setting.”

I was less than star-struckwhen I looked at their technical career programs:

Auto technology?  Do we really even have to discuss this one?

I was more pleased by the clubs, but the ones that are heading it up are often the same teachers that are failing the students in their primary schools!  I noted the names of three teachers that aren’t even technical in the three clubs that are mentioned in the image below!

One of the teachers listed as one of the sponsors teaches at my children’s high school and we’ve discussed her “ongoing challenges grasping today’s technology”.  Having some of these folks heading up tech clubs seems no better than the BBC show, “The IT Crowd” character, non-technical Jen Barber’s hilarious attempt at heading up IT, which is one of the consistent comedic story lines of the show.  This is what we have directing tomorrow’s technical minds?

Now we’re going to get into the down and dirty.  This school is an “alternative” school.  This is the school that they send the students that are not succeeding in standard, public schools.  What does this say about the future of technology in general?  What does this say for the number of folks in the industry that have a story similar to mine that started out in a VERY different field than the one they are highly successful in because they were unaware that the opportunity was even out there for them?

So, here’s where I’m at-  if the schools are going to continue to fail both genders in the technical arena, maybe this is a better alternative:  http://www.technologyreview.com/news/506466/given-tablets-but-no-teachers-ethiopian-children-teach-themselves/

How many performance specialists have experienced projects where reporting was horrid in the beginning, maybe didn’t even complete?  The focus of the project may have sat with data loads or due to the data architect’s design specialty, etc., the reporting aspect was just the last thing on everyone’s mind until it was too late. I come into this conversation with companies all the time and it is a difficult one, as there are so many database myths.

“Can’t you just do something to make the database go faster?  I don’t know, isn’t there a parameter that can be updated, maybe some partitioning or compression that will fix our problem?”

I feel there pain, I do understand…

When a project to design a warehouse or mart is undertaken, the groups involved may be quite distracted by following “best practice”, i.e. “my fact tables must be just so and the dim table design must follow the same format as I used at company XYZ that was so successful….”  You may find that the folks doing the actual design may not know as much about marts and warehouses and are learning as they go along.  You may have someone who is unable to move away from an OLTP design in the mart, “everything must be relational and I must follow normal form, so let me add primary keys, foreign keys, constraints everywhere!”  (Hey, it happens, I’ve seen it…:))

Never the less, as a development group learns, there is no place I’d rather be.  No project is a lost cause and working with the group to create proof of concepts, showing them to correct way to design a warehouse or mart, along with star schemas, the power of bitmap indexes, etc. is a valuable time for any database administrator to spend.  Ensuring you know what is causing the pain for an ETL load process, i.e. where time is consumed is essential, so that you are able to provide as many “wins” when making enhancements is pertinent, so you can get onto the reporting aspect of the project.

So why do we need to keep reporting on the horizon at all times?  Never lose sight of this important feature of the warehouse or mart as this will most likely be the user interface to the environment.  This is how the business will judge if its successful or not.  If they can’t view the data in an acceptable amount of time, it doesn’t matter how quickly it loads at night or how beautiful your star schema is, as far as they are concerned, it was all a waste of money and time.

With that said, where do I see the most common challenges for reporting?

Storing data in a very different format than the way reporting requires it to be presented in. 

I’m not talking about how a star schema is designed here.  I’m talking about the data having to be constantly cased, then summed then sorted, hashed, sorted again, so on and so forth, to present the data the way the customer requires vs. having materialized views, reporting and rollup tables that can lessen the performance hit on the database.

Why is it a performance hit?  I have another draft in my blog posts that I need to get out, but it’s a perfect example.  The business needs to see the data presented consistently in a way that is no way near the way its stored.  As the reporting queries perform well with simple hashes, bypassing nested loops, etc., there is no way that the amount of case statements, to sum, to sort and sort again, to fit within the allocation of PGA per process for it, even in parallel.  This results in huge uses of temp tablespace.  That means the process is now doing it’s work on disk and disk is just plain slow.

Why am I a temp tablespace nazi?  This above is why. 

I can move to temp tablespace groups and I can put my temp on SSD, but really, the best optimization enhancement will come from an materialized view or rollup table that will limit the amount of work that has to be done in PGA.   Do not say, “Well, can’t you just raise the PGA?”  These are on servers that the amount of PGA required is larger than the memory on the box and the temp tablespace is often the largest tablespace in the database, (remember, I work in VLDB’s and for someone to brag that they used 2TB of temp is not uncommon… )

No hardware will offer the same kind of performance gain as a small design change along with a few tweaks to some reporting SQL to do less in a report to produce a report.  No hardware will offer as long-term performance enhancements as the latter-  plain and simple.

I wish I did have a silver bullet to make the database “go faster”, but that’s not how it works if you really want the problem fixed vs. having someone offer you some duct tape.  Duct tape will last you a month, maybe a year if you are lucky, but the problem will continue to return until we fix it right.

OK, enough ranting for my first day as a ACE Director…

I won’t apologize to Oracle for saying that the new Incident Manager has some quirks to it, but it does, especially when it comes to managing incidents, events and how to clear out both.  I’m going to attempt in this post to explain how to check to see if you have alerts that can be cleared with an EMCLI command and then also take you through clearing out from the GUI, which is available in the Release 2 of EM12c.

If you want to know what kinds of stateless alerts that can be cleared manually, then once logged into the EMCLI, (Enterprise Manager Command Line Interface) you can do the following:

./emcli login -username=<login name>

./emcli get_metrics_for_stateless_alerts  -target_type=osm_instance

This command will return all metric types and alert types that can be cleared out for ASM alerts, (osm_instance).  You can do this for oracle_database or any of the other target_types.

As always, if you need help, EMCLI offers a good help option.  You can save off the file for reference and then gather more help info on specific verbs by doing the following:

./emcli help > emcli_verb.lst  <--creates the output file of the emcli verbs and descriptions.

./emcli help <verb>  <--gives you a descriptions, syntax and examples for a specific verb.

If you aren’t sure of Target Type names, you can query the database or you can do wild card searches through the EMCLI:

./emcli get_targets -targets=%database%

./emcli get_targets -targets=%osm%

./emcli get_targets -targets=%

This first option above returns a list of all targets that are database type targets.  The second returns all ASM targets and the third returns everything.  Some of the output from the third looks like the following:

ID      Status           Target Type           Target Name
0       Down             oracle_listener       LISTENER_SCAN2_exa1-cluster
1       Up               oracle_oms            oemdb02.orcl.com:48
1       Up               oracle_oms_console    oemdb02.orcl.com:48 
1       Up               oracle_oms_pbs        oemdb02.orcl.com:48
1       Up               oracle_database       orcl_db3.world_orcl2
1       Up               oracle_database       orcl_db4.world_orcl1
1       Up               oracle_exa_pdu        exa1-pdua.orcl.com
1       Up               oracle_exadata        exa1cel07.orcl.com
1       Up               osm_cluster           +ASM_exa1-cluster
1       Up               osm_cluster           +ASM_exa1-cluster
1       Up               osm_instance          +ASM1_exa1db01.orcl.com

Before attempting to run a clearstate on incidents, lets look at how to check for these.Note that we use two arguments here with the clear_stateless_alerts verb:

• Unacknowledged_only-  Only those alerts that have not been acknowledged, which helps distinguish from any that may be currently worked by someone.
• Preview-  I want to just check and see how many are available to be cleared.  The command will not clear anything this way, only tell me how many!

$ ./emcli clear_stateless_alerts -older_than=0 -target_type=oracle_database -target_name=<target name> -unacknowledged_only -ignore_notifications -preview

 The following alerts can be cleared:

Total Alerts
==============
1

Now, if you want to clear the one alert that is available to be cleared as stateless, you can:

$  ./emcli clear_stateless_alerts -older_than=0 -target_type=oracle_database -target_name=<target_name> 
1 alerts were cleared successfully.

Now if you have a huge quantity of alerts that are not available for clearing with this process, the EM12c console Incident interface offers the ability to mass clear alerts in release 2, (apologies to anyone still using BP1 and lower, this option will not work!)

From the EM12c console, there are a number of ways to access alerts, via the Incident Manager from the Enterprise drop down or from a database target summary page, but for this example, we will work from groups.

By clicking on All Targets, then Groups, we are brought to the groups summary page.  As we see, our Prod group has 932 open incidents identified with this group:

By clicking on the incidents link, (which is the number of incidents, so for this group, we would click on the “932″) we are then taken to the overall incidents summary for this group.

If you hover your cursor over the left hand side column, just left of the Severity column, you are able to hold your left mouse button and choose multiple alerts:

By dragging the mouse down, you can choose as many incidents/events as you wish.  Once you have chosen the amount you wish to remove, note that the “Rows Selected” will show in the left bottom corner, (for our example, we have 7.)  You can then click the “Clear” button, identified with the pencil eraser.

The confirmation pop-up screen will then ask you to confirm and click OK, but remember to change it to not send email notifications so as not to “spam” everyone.

The worst thing you can do is send ridiculous amounts of emails telling everyone you are removing each of these incidents.  Remember, we hate “white noise”…   Once you have confirmed this is what you want to do, click “OK”.  You can do this for as many or as little incidents to clear out the system as you would like at a time.

Remember-  Any incidents that are still an incident and need attention will recreate on the next metric collection for the target.

Exachk does a number of health checks.  One of them is to verify the connectivity, settings and such of the ethernet cables in the Exadata environment.  If one is found amiss, the following will be returned in the Exachk summary report:

To validate the Exachk findings, you are asked to run the following as root on the specific node that is reporting the issue:
for cable in `ls /sys/class/net | grep ^eth`; do  printf “$cable: “; cat /sys/class/net/$cable/carrier; done

This command, if run on one of the other nodes that were not showing up in Exachk without an issue, the following is returned:
eth0: 1
eth1: 1
eth2: 1
eth3: cat: /sys/class/net/eth3/carrier: Invalid argument
eth4: 1
eth5: 1

Running this on third node, where the report of the failure had occurred and the following is returned, clearly reporting the issue on eth1 and eth4 that requires a physical check of the connections reporting back with a value of “0″.

eth0: 1
eth1: 0
eth2: 1
eth3: cat: /sys/class/net/eth3/carrier: Invalid argument
eth4: 0
eth5: 1

“Invalid Argument” signifies that it is not in use, so disregard eth3.  The values of 0 are the issue.  This shows we have a problem.  Andy Colvin, (our very own Enkitec Exadata Ninja!) recommended doing the following to just check a bit further.

As root on the third node, issue the following: ethtool eth3

$ ethtool eth3
 Settings for eth3:
 Supported ports: [ TP ]
 Supported link modes:   10baseT/Half 10baseT/Full
 100baseT/Half 100baseT/Full
 1000baseT/Full
 Supports auto-negotiation: Yes
 Advertised link modes:  10baseT/Half 10baseT/Full
 100baseT/Half 100baseT/Full
 1000baseT/Full
 Advertised auto-negotiation: Yes
         Speed: Unknown!
         Duplex: Unknown! (255)
 Port: Twisted Pair
 PHYAD: 1
 Transceiver: internal
 Auto-negotiation: on
 Supports Wake-on: pumbg
 Wake-on: g
 Current message level: 0x00000003 (3)
   Link detected: no

Yep, the lights are on, but nobody’s home…

At this point, someone *physically* needs to check the hardware.  For the incident above, it was suspected that someone had yanked on the cable, damaging it and due to this it was replaced, ran through the above tests again to verify that all was well.

There have been some great blog posts from folks on the RMOUG Training Days 2013 Conference, (I’ll add some links to my favorites I’ve come across at the end of this post!)  Due to this, I thought I would leave mine in my drafts folder and focus for now on the Conference from my viewpoint as the Conference Director.  John Juenette was gracious enough to send me a beginning blog post he’d started last year and I need to review it and add my own data for future “victims”, but until then, here were my experiences during this year’s run as Training Days Conference Director.

The conference, of course, started for me back in May when I said that I would be the director of the conference.  I started looking through information back then, taking notes at conferences as I attended on what I liked, what I didn’t like and what might be feasible for a conference our size.

The end of July everything ramped up and we started preparing for the conference.  I gathered my wonderful conference committee members, John Juenette, Peggy King, Tim Gorman and Pat Van Buskirk.  Team YCC, the crucial part of our conference planning team, readied the website to open for abstract submissions once we configured tracks, schedules and design.

Once abstracts were submitted, we chose the abstract committee, which did a phenomenal job scoring out the abstracts, which then made it easy for us, come November, did choose the best of the best, then pepper in new speakers, filling 142 sessions out of over 300 abstract submissions.  There were so many fantastic submissions and it was difficult to choose!

I had a very defined list of wants and enhancements that I desired for this year.  I had presented at SQLPass #169 in September and was impressed with their mobile app, Guidebook.  This app did a really fantastic job and upon research, I found that they had just used the free version and that if you looked into some of the enhanced versions, it really put every mobile event app I’d seen in the past to shame.  I had a bit of concern, especially considering how poorly many of the mobile event apps had perfomed for folks in the past, but then Team YCC returned from attending a conference about conferences.  Guidebook had been all the buzz there and they had seen it demonstrated, which had impressed them greatly.  Arisant graciously came on board, thanks to Kathy Robb, to sponsor the app and the mobile app enhancement to RMOUG was made.

My second goal was to upscale our Speaker and Volunteer Reception.  In previous years, it had been at Wynkoop and although I loved the “local brewery” hangout, we’d done it year after year and wanted to do a bit more with the opportunity, but not too much of a change that we shocked everyone.  This year we moved to the Rock Bottom Brewery and rented out the back bar.  This room has multiple TV, pool tables, a much nicer bar and with the joint effort of the Oracle ACE program, we combined what is commonly the ACE dinner with the RMOUG Volunteer/Speaker Reception to form the VIP Reception.  The buffet was fantastic, noting the consumption of not just the food, but the wonderful dessert offerings, but the beer and wine was well received by the group.  We had the largest turnout we’ve ever had for one of these events and we appreciate Vikki Lira’s contribution to this RMOUG/ACE event!

My third goal also sourced partly from the SQLPass event, but had been a source of great frustration for me for some time.  I was quite aware of the ratio of men vs. women in the database field.  In my experience, I had noted it around 30% women, yet when I attended Training Days conferences, the percentage of women in attendance was always less than 10%.  I was amazed when I presented at SQLPass, where in my experience on the Microsoft side, the ratio of women had always been lower, yet here, at the conference, the ratio of attendance was almost 40%!!  I had the great fortune of presenting at the same conference as Jen and Sean McCown, (@MidnightDBA) who, as a married MSSQL DBA couple, Jen does a lot with the WIT, (Women in Technology) program with the MSSQL group.  I appreciated Jen’s attitude and took a number of notes on what she said was required to create a successful WIT program and went forward with these ideas for RMOUG.

The WIT session was held at the time of the vendor sessions so as not to take away from the technical sessions that many might see as clear competitors, (next year, I plan on putting in an hour of professional development, if we can handle it… )  I then followed these rules:

1. Don’t make it a “cake social” or a “book club”, this is for technical professionals.  If you wouldn’t have it for a session with an audience filled with male attendees, don’t do it here.  You will lose the truly serious women in technology.
2. Do not be afraid to discuss all topics that are holding women back or holding women out of the STEM career area.  This is NOT, however, a male-bashing session.  If anyone starts on this line, please intervene, thank them for their opinion, but also remind them that this is not the correct platform for this type of conversation.
3. Recognize how we hold ourselves back culturally, without even realizing it.  Jen told me how folks would come up to her at conferences and say, “Wow, you are here presenting and your husband is home babysitting the kids?”  She says it was frustrating as she’s explaining to them, “They’re his kids, no one is *babysitting*” and how they would never have had this conversations with her husband at the conference, (her husband was in the room agreeing furiously…)  We often build boxes around ourselves without even realizing it and I’m very aware that I do this to myself- still.
4. Understanding that although analytical skills are important to a career in a database field, there are a number of other areas that are just as important.  Knowing that without this “balance”, a team can become askew in skills and not perform as well as it would if it were to have a more diversified set of individuals.  When I’m interviewing a new DBA, along with the correct answer to a question, I’m also gauging how they communicate and if they will work well with their team members.  Do their skills help balance out the skills already present in the team?  Women bring a strong component of problem solving and “ability to see the big picture” where men often are highly focused and analytical.  The balance of these strengths are crucial to building a great database team.

Needless to say, the WIT session was hands down, a roaring success.  I thank my fellow panel members, Dominic Delmolino, Maria Colgan, Debra Lilley and Karen Morton.  The outreach of the RMOUG community to me after we completed the very successful session asking what would we do next for WIT was a clear indicator we did something very right in this session!

With my three enhancement goals attained-  Guidebook Mobile App, VIP Reception and WIT Session to launch a WIT Program, I continued with the final changes to the schedule as speakers schedules required and not panicking when things didn’t go exactly as planned…

The pre-conference on Monday went surprisingly well.  I’m not sure what I would have done without Peggy King ensuring that we had all our volunteers and Tim Gorman setting up the rides from and to the airport.  This is a nice little perk we do here, but we have so few *bodies* to help and so much to do, it can be over-whelming at times.  We arrived on Monday morning, my middle-child, Caitlyn along for the day to assist and all the volunteers started to put bags together for attendees, prepare speaker gifts and registration preparation was all worked to completion.  Speakers started to arrive and register and the OTN and University Sessions were underway.  Cary Millsap held his “Mastering Trace Data” and as usual, the students were thrilled with his class.  Maria Colgan’s session was packed her OTN workshop, (the other session with Kris Rice and Jeff Smith was nothing to sneeze at either!) but we feared for the fire marshall and ensured that no more than the room would hold was allowed.

We had a few cancellations for speakers due to the winter storms out east and one due to illness, so we had to do a bit of scrambling.  I’d like to thank Carlos Sierra and Frits Hoogland for stepping in at the last minute with sessions.  I also would like to thank all the others that offered to throw in a session at the last minute.  I’m always amazed at the support that RMOUG is given by the Oracle community.  You folks really come through over and over again.  I’d like to thank Mogens Norgaard, even with the bad sound quality, he was a warrior, in his infamous keynote bathrobe and stuck through till the end!  The folks at the first three rows of tables in the ballroom thank you, as we got a fantastic keynote!  I apologize to all those in the back of the room who I know had a difficult time hearing anything.  We will work to ensure that the sound quality is better next year.

Upon speaking with different exhibitors, the set up this year really seemed to work well, too.  All seemed exceptionally happy with their spots and the traffic.  Folks seemed to really take time to talk with them and were interested in their products.  We had incredible support this year from exhibitors and sponsors.  DatAvail and DBAK did joint platinum sponsorship this year, which was a first for RMOUG.  We had a number of new exhibitors this year, like Delphix and Firebox Training.

Lastly, I’d like to thank my employer, Enkitec.  I could not do all that I do without the support of a fantastic employer.  I so appreciate the support to me professionally as an employee, as well as they as an exhibitor at RMOUG and my coworkers that submitted abstracts, to those that were accepted and presented.  Kerry Osborne, Wade Nicolas and Veronica Stigers are just the best in all that they do.  They always have their eye on the ball, never falling for the false alarms or latest trends, but strong and steady with the right direction for the company.  I appreciate my boss, (he so hates when I call him that… )  Andy Colvin.  Nothing much fazes him and he just lets me be the best I can be with the time I have to do everything I can possibly do.

As promised, here’s the links to other blogs posts on this year’s RMOUG.  Thanks for sticking through this, I think it was a long one…

Carlos Sierra

Maria Colgan

Arup Nanda

Maxym Kharchenko

Kent Graziano  (Kent has a couple others out on his blog, too!)

Mike Dean

Karl Arao

The Enterprise Manager Diagnostics Kit, (EMDIAG) is a great addition to have installed and be familiar with on your Enterprise Manager 12c, (EM12c) environment BEFORE you need it.  There are three main kits:

• repvfy – Extracts data from the Grid Control or DB Control Repository to help you to maintain or diagnose problems in your Grid Control or DB Control.
  The repvfy Kit is partly included in the Cloud Control 12C Support Workbench for the OMS.
• agtvfy Kit – A set of command lines to help you to maintain and diagnose problems in your Cloud Control, Grid Control or DB Control Management Agents.
• omsvfy Kit – A set of command lines to help you to maintain and diagnose problems in your Cloud Control, Grid Control or DB Control Management Services.

For this post, we are going to work with the REPVFY kit from the EMDIAG and explore how functional this kit can be to diagnose and assist when issues arise in the EM12c OMS.

Installing the EM Diagnostics Kit

Download the zip file for the version that you need, per Oracle Support Note: MOS ID# 421053.1

This note will explain about each of the kits and take you through the download process.

Create the EMDIAG_HOME in the environment variables for the OMS OS User and owner of the EM12c.  This will create an easier support scenario for the administrator in time of stress when they require quick access to the EMDIAG tools.

unzip the EMDIAG zip file into the EMDIAG directory
unzip <file name> -d $ORACLE_HOME/emdiag

Perform the installation by changing to the newly created directory and installing EMDIAG
cd to the EMDIAG home, (that new directory you just unzipped the files into…)

cd $EMDIAG_HOME/bin

In the bin directory will be three common files, the executable, the batch file for windows support and the perl script that supports both:

-rwxrwxr-- 1 oracle dba   3331 Feb 23  2012 repvfy.bat

-rwxrwxr-- 1 oracle dba 198800 Aug 31 18:57 repvfy.pl

-rwxrwxr-x 1 oracle dba   7195 Aug 31 18:57 repvfy

./repvfy install -tns em12rep -pwd password

The installation will commence and you will end with the following:

PL/SQL procedure successfully completed.

COMPONENT            INFO
 -------------------- --------------------
 EMDIAG Version       2012.0831
 Repository Version   12.1.0.1.0
 Database Version     11.2.0.3.0
 Test Version         2012.0926
 Repository Type      CENTRAL
 Verify Tests         381
 Object Tests         157
 Deployment           SMALL

Checking the installation and verification of the version

./repvfy version -tns em12rep version

Please enter the SYSMAN password when prompted.

Component                      Version
 ------------------------------ ---------------
 repvfy                               2012.0831
 EMDIAG                                   12cR1
 Database                            11.2.0.3.0
 Repository                          12.1.0.1.0
 Tests                                2012.0926
 Linux                                   2.6.18
 x86_64-linux-thread-mult
 PERL                                    5.10.0
 SQL*Plus                            11.2.0.3.0
 Agent Zip                                  3.0
 Agent Unzip                               6.00
 ------------------------------ ---------------

Using the EM Diagnostics REPVFY KIT

Querying the following data from the Enterprise Manager will gather data that is required to then run the diagnostics for job level reports.  The example used will involve a backup job for APEX:

From SQL*Plus as a user with select privileges to the SYSMAN schema:

select mj.job_id, mj.job_name, mjh.execution_id, mjh.step_id
from sysman.mgmt_job mj, sysman.mgmt_job_history mjh
where mj.job_id = 'BFC78F41009876F3E04014AC08003231'
and mj.job_id=mjh.job_id
and mjh.start_time > sysdate-1;

Using the job name, “BACKUP_APEX.1″, an obvious APEX backup job, we can perform some diagnostics report gathering on the job itself, using the job_name field.   Due to the amount of diagnostic data that is offered,  redirecting the output to a file is recommended.

cd $EMDIAG_HOME/bin

./repvfy dump job -name BACKUP_APEX.1 –pwd w0rldw1d3 -tns em12rep > ./rep_apex_job.lst

The output file will then show you a full report on the job, starting with the information about the diagnostic tool, the database, test and repository.

From here, the report steps into information about the job-  run time, timezone and detail information:

You can view the parameters in the job, including multi-step jobs commands:

This report will also tell you about where the job runs from and the target it runs to:

The next section then let you know who owns the job and who has grants, along with what kind of grants on the job.  As this job is simply owned and run by the SYSMAN user only, only this user shows in the report.  The report presents the last 10 run times in history, including elapsed time, retries and success information.

For the example, the EMDIAG informs me that there is one job that was in a waiting state.  In the recent history, it shows that all have been successful, (0), but that a level 17, signifies that one of the runs did have to wait due to some undisclosed issue that isn’t shown in the execution summary:

If there were any errors in the job analysis, this is saved for the last section of the report.  Our example compiled successfully, showed no issues and so the output results in the following:

If a job has more than one step and you wish to gather detailed diagnostic information about that step ONLY, a secondary report can be generated by running the following:

./repvfy dump –tns <oms_sid> step –id <step_id>

GUID Target Diagnostics

For our next scenario, we are going to utilize the GUID for our EM12rep, (the OMS Repository for the EM12c) and gather EM environment data:

./repvfy dump -tns <oms_sid> execution -guid EFD92E6468B02696B602A650E085B5A9 -pwd <pwd> > ./rep_guid.lst

By running the GUID level command, (GUID is your target GUID and dumps out information on all the jobs, from all the targets)you get a one-stop shop kind of diagnostic report about your EM Jobs Service health.

Although the report starts out very similar to the job level report, listing out repository and EM information, once you get to target info, you will note a distinct difference, as this report will list out all information regarding monitored targets, which the job level report did not.
If there are any background jobs submitted by the EM Jobs Service that are experiencing issues, they are clearly reported in their own section:

My only complaint at this time, would be that the BP1 version  of the EM12c, when the EMDIAG is run against it at a full enironment level, experiences an error at the Job Analysis Summary step:

Due to this, if you are still on a BP1 version and need to pull this level of diagnostic data, I hope that you have your issue narrowed down to a single job or target that you can focus the report to, subsequently avoiding this error.

Global Diagnostics

In this example, we will run a level 9 diagnostic, which will run a report against the whole of the Enterprise Manager environment, checking the health and for issues:

./repvfy -tns em12rep verify -level 9 –detail

The first thing this level of diagnostic does is check the version of EMDIAG you are using and if there is a newer version available. The diagnostics will proceed to perform a full verification of the EM environment before logging in to SQL Plus to perform the secondary diagnostics on the EM testing, the installation information about the EM system and then proceed onto internal jobs and alerts from the EM environment.

The next step is to check all promoted and un-promoted targets, make recommendations on changes and then end the report by verifying any non-standard EM users.
The logs for the output from these reports will then be generated to the <EMDIAG_HOME>/log directory to be reviewed or uploaded to Oracle Support.  I do prefer to redirect the screen output to a file for easy review, as seen in my examples above.  This data should assist the administrator in answering questions regarding their EM12c environment without having to always open an SR, but if the need arises, this valuable diagnostic data can then be generated to save time and effort so that the administrator can perform other tasks than just answering Oracle Support questions.

As I spend more and more time optimizing Exadata environments, I seem to be answering the same questions repeatedly when it comes to database design, parameter settings, etc.

Here’s a few high level notes to always remember-

• Always have baselines from previous environments.  Don’t go into performance expectations blind.  Have AWR and ASH data from the previous environment and mine AWR data for anything when others start “guessing”.
• Look at the big picture-  those that focus on one thing, often find that it’s a combination of issues that brew the perfect storm.
• What are your top 10 goals by going to Exadata?  They had better tell you it wasn’t just because someone thought “it was cool”.
• Expectations should be listed clearly, “we are expecting a XX% average improvement in our ETL loads and XX% average improvement in our reporting.  We expect to have a XX week post optimization exercise to address any performance issues after the migration and know that this may involve query rewrites.”
• Do the challenges in your database environment coincide with the features that Exadata offers?  If not, you could have a really sweet engineered system that offers your current design minimal improvements and what that leaves you with is future enhancements that should be geared to utilizing smart scans, storage indexes, HCC, etc.
• Just because a statement offloaded and returned the data quickly, doesn’t mean you have unlimited PGA to then sort and aggregate that data in a report.  Cell nodes can not help you here.

“My goals are not your goals” is an opportunity for any DBA who hears these words…

• Have you identified your stakeholders in the optimization exercise?  What enhancements are important to you, may not be what is important to them.
• Discuss what change control is required.  Ensure you know what is required to move anything to production.
• Openly listen to all involved and realize that all input is valuable and in the end, if the user isn’t happy, nobody’s happy.
• Options located on web sites and are stated to be “best practices” should not be taken as gospel.  Research and verify everything.

Successful migrations and optimization on Exadata starts with good planning

• Create top 10 out of group’s goals.  If a goal appears out of context, discuss, find out what is important about the goal and if there is another option for attaining it.
• Create a simple SOW for the optimization exercise.
• Clearly state what will be included and what WILL NOT.  Ensure all stakeholders have signed off on the SOW.

Dispel Exadata myths.  I was told by one client that, “Tom Kyte said at the last Oracle Open World that parallel should never be used on Exadata…”  Although I was sure they had heard him incorrectly, I wanted to see the original slides so that I could show them the point he was actually trying to relate.  The outcome of a quick tweet in search of Tom’s slides from OOW resulted in a bizarre and interesting set of emails/tweets when I was hit with the “140 char misconstrued tweet” issue.  Yes, I am now responsible for a “Tom Kyte says you should never use parallel with Exadata” myth…sigh…

When you are making recommendations

• Backup everything with data.
• IF you come to me with proposed parameter changes, (the silver bullet syndrome…)  please let me apologize for my lack of enthusiasm.  Even when parameter changes made a big difference in earlier versions of Oracle, it was commonly only a 10% improvement, where with SQL rewrite, I could get at least 80%-  you choose…
• Inspect opportunities for SQL rewrite- ensuring others that often it is required as systems grow and change, not because someone wrote, “bad code”.  Pointing fingers is a waste of time and unprofessional.
• Are there opportunities to incorporate parallelism.
• Is plan stability an issue? (statistics, dynamic sampling and profiles.)

And Tyler Muth reminded me that  forgot, even after just using it today with a client-  Exachk is your FRIEND!!  It will verify that no undocumented or non-default parameter settings are present, along with perform a health check on your Exadata environment.

Truth be known:

• Lots of SGA is not always your friend in Exadata, (if it runs in memory when it would better be served by offloading to a cell node due to high SGA, is it a good thing?)
• Do not count on storage indexes.  The feature, when implemented by Oracle is one requiring the choice of a complicated set of algorithms.  They can be your savior or your devil.
• More parallel is not better parallel.  Find you “sweet spot”.  Most environments have a setting of DOP, (degree of parallelism) that works best with over 90% of the environment’s SQL and delivers over 90% of the overall performance improvement.  The DOP is also often much LOWER than many would guess, (how often 4 has been my magic DOP… )
• Know when it’s time to implement classic warehouse/mart strategies such as star schema, bit map indexing, partitioning, mviews.  The harder you make the Exadata work now with these kinds of tasks, the less smarter it’ll be working as the environment grows.

Last note.  Although for some, it may not seem believable, but if I hear one more debate about any 11g database and optimizer_index_caching  or optimizer_index_cost_adj parameter settings,  I may go postal…:)

See ya next week at RMOUG Training Days 2013!