Seth Miller dot org

Technical ramblings of an IT Generalist

EM CLI Jython Script for Target Properties

Thanks Seth

“Thanks Seth! We really appreciate your help!”

That’s five times in the last two weeks that I have had someone enthusiastically thank me for fixing their problem. I may be in my third decade of life and a man’s man but I still blush like a little girl when someone truly appreciates my work.

Today ends my second week with my new employer. Collier I.T. is an Enterprise Solutions Provider and an Oracle Platinum Partner. My role is to serve as a consultant and provider of Oracle database services as well as an instructor for Oracle University training.

I have always been excited when taking on new roles in my career. In fact, I can still remember my first day at my former employer, St. Jude Medical. I hadn’t done more than glance at an Oracle RAC cluster before I started at SJM. Now I was managing three of them. If you have been a RAC administrator for awhile, think back to when you went from a single instance DBA to a RAC DBA. It was an entirely different world and I was as excited as a penguin with a bottle cap on a rainy day (think about it).

Guess what I get to master now; the behemoth, the monster, the problem solver…the Exadata. Collier was also a SUN shop before they were acquired by Oracle, which means that I am surrounded by another technology I’ve been wanting to learn for a number of years; Solaris. And, I get to learn it from @SnatchBrain, a SUN fanboy if there ever was one.

So, here I sit in year nine of my I.T. career. I’ve spent more than eight years in corporate I.T. and now I’m wrapping up my second week with an Oracle partner. I’ve been an Oracle ACE for almost a month. I’m contributing to a book on Enterprise Manager with two of my biggest DBA crushes; @DBAKevlar and @RaySmithAce. And, I was recently published in the IOUG SELECT Journal.

Yet, I feel like I am just getting started. There is so much to learn and teach. There are so many brilliant people to look up to. There are so many eager people to mentor. I actually feel guilty sometimes that I get paid to do this.

By the way, the penguin thing didn’t mean anything but how many different ways did you picture a penguin and a bottle cap in the rain.

RAC Attack in a Box

The RAC Attack event at OpenWorld 2013 was a great success. Moreover, it exposed a number of challenges that the RAC Attack Ninjas are prepared to solve. Time is of the essence at an event so we want to come up with the most efficient process of going through a RAC install as possible.

We are currently in the process of developing a virtual machine that will alleviate many of the hindrances at an event. We have coined the term “RAC Attack in a Box”. The hostname of the VM will be “dojo”. The VM will run in VirtualBox on one of the Ninja’s laptops. The users will be connected to the dojo via a wired (preferable) or wireless network. We will provide specific instructions for the users at the event which may vary depending on the event circumstances.

The dojo will provide a number of features; many of which are listed below. This RAC Attack in a Box is still under development so these features may change and will definitely be supplemented. As always, feedback is welcome and encouraged.

Kickstart Configuration
Kickstart scripts can be used to script the process of an Oracle Linux installation. Every installation of Oracle Linux leaves a file in the /root directory called “anaconda-ks.cfg”. This is the kickstart script generated from the options chosen during installation and can be used to install the OS again with the same options. It is a text file and can be read and modified. Kickstart is more than just a method of cloning an OS. It is highly customizable which means we can use it to cater to the user.

PXE Network Boot Server
The current RAC Attack documentation starts the OS installation using an ISO downloaded from Oracle. This ISO is mounted as a virtual CDROM and set as the first boot device in the VM. Another option for installation uses a network boot, commonly known as “PXE” (pronounced pixie). The dojo will respond to the network boot request and present a menu of options for the user.

Why a menu? RAC Attack is about the end-to-end experience of installing RAC. We want to give the user options so they can use their limited time in the most efficient way possible.

For example, installing the OS is part of RAC Attack so users can understand the Grid and the Database installation, as well as the foundation beneath it. But, one user may be at a different technical level or have different job responsibilities than another.

User A is a sysadmin and does OS installs all day. He doesn’t need to see the OS install so he chooses a fully automatic OS install. In five minutes he has the OS installed for both VM’s. He is now ready to install the Grid and Database software.

User B is a DBA and has never done an OS installation before. She can choose to do a raw OS install to get the full experience.

User C has done OS installs in the past and understands what is happening. However, she would like to see each step without having to worry about selecting the correct options. She can choose an automated but interactive install.

ISO Caching
Since Oracle Linux is open source software, RAC Attack can provide it to the users so they don’t have to download it during an event. However, the Grid and Database software is proprietary and cannot be provided. Each user must authenticate with Oracle edelivery and download their own copy. This has been a huge problem in the past because more often than not, internet access at events is poor or not available. Even with a decent internet connection, these downloads are not small and can take a significant amount of time.

We have come up with a solution to this. We use the open source proxy software called Squid to cache the ISO downloads. A user connects to edelivery through the proxy and authenticates. An internet connection is still required but only for the dojo, which then shares that connection via the proxy to the users.

Once the user authenticates and opens the link to download the ISO, the proxy sees that the file being downloaded matches one already in its cache. Instead of pulling those bits over the internet, the proxy feeds the user the file from the cache.

A download over the internet from Oracle will max out at about 1-2MB/sec. A download over a full duplex 1Gb wired network (the standard for all laptops sold in the last few years) will do about 30-110MB/sec (depending on how many users are downloading at once). Downloading all four ISOs that make up the 12c Grid and Database installations takes 1-3 hours via the internet or 1-3 minutes via the proxy.

Full Package Repository
The default OS installation disk has all of the default packages on the disk. However, once the initial OS installation is complete, the RAC Attack instructions call for additional package installation from the public YUM repository hosted by Oracle. The dojo will have not only the default packages, but will also contain another repository with any additional packages necessary for the full installation. The user will no longer need to go to the internet to download additional packages.

Another Learning Tool
Are you wondering how one of the RAC Attack in a Box features work? The RAC Attack Ninjas keep no secrets. The dojo and all of its components are fully open source. We will be documenting how to build it from scratch and making that documentation and all of the scripts available for anyone to download.

Perhaps you have already gone through the latest full RAC install at OpenWorld and don’t see any reason to do it again at Collaborate or one of the other many events at which RAC Attack will be. Come by anyway and build your own dojo or help a n00b with their first RAC install. Better yet, sign up to become a RAC Attack Ninja and contribute to the project.

Career Upgrade

With much consideration, I have decided to take my career to the next level by changing employers. Many things went through my head while I was considering if this was the right path for me.

For example, a job change is disruptive in the following ways.

  • Starting over is difficult
  • Things are easy to find when you know where they are
  • It’s easier to be in a room with people you know
  • New challenges are difficult and exciting
  • Are these the good ways or the bad ways? The answer for me is both.

    Starting over is time-consuming and uncomfortable. Sometimes it feels like going backwards or in the very least, temporarily standing still. But, it’s also a clean slate; a chance to start fresh; perhaps to take a different route than before.

    The drive to my job is routine. I don’t have to think about it. I don’t have to stop and think where to park, where to get my coffee or which row my cubicle is in. I don’t have to wonder where the conference room is that I am supposed to be in in three minutes. I don’t have to look up the URL for the intranet. On the other hand, I get a little more time to listen to my podcasts on the drive in. The coffee might be better in one room than the other, but I won’t know until I try. I’m pretty sure this new place only has one conference room anyway.

    As an introvert, being in a room full of strangers is literally exhausting. As much as I would like that to not be the case, it does not appear that I am able to change it. I deliberately put myself in sometimes downright frightening situations to fight that weekness. What better way to do that than to spend ten hours a day among people I don’t know.

    I am driven by challenge, which is why I fit so well into the career path I have chosen. Every day holds challenges for me. I sometimes realize I have taken on too many challenges or have chosen a challenge that is beyond my means to achieve. They can be a double-edged sword and must be undertaken carefully. One thing that is clear is that challenges must exist in abundance for driven professionals like myself and this is what attracted me the most about my new employer.

    My new position represents a more significant change than the career moves I have made in the past. For one thing, I am no longer working for internal I.T. I will be working for clients that hire me for my specific skill set to start and complete a defined project. Secondly, part of my job allows me to teach…literally. I will be teaching Oracle Education courses to other technical professionals.

    I am naturally frighted of the unknown, but that fear is dwarfed by an excitement I can’t remember ever feeling throughout my career. Here’s to the unknown. Cheers!

    Replacement Menu Script for Oracle’s oraenv

    This script is a replacement for the “oraenv” script used to set the server environment on an Oracle database server. It’s biggest advantage over oraenv is that it requires far fewer keystrokes and provides a lot more feedback.

    I’ve been using various versions of this script for years but I decided to put together a version that can run on its own so it can be shared.

    To use it, execute the script once in your session and type “db” (which will call the function) each time you want to change the environment.

    Enterprise Manager 12c EMCLI Target Properties and Metadata

    This post refers to Enterprise Manager 12c Release 3 specifically but much of the information applies to earlier releases. The EMCLI command examples are using the Jython interactive interface. Examples are sometimes shown on multiple lines for clarity and may need to be on the same line to work properly.

    Most of the Enterprise Manager Command Line Interface (EMCLI) verbs have a “properties” parameter that consists of one or more (delimited by a semi-colon by default) name-value pairs (delimited by a colon by default). This “properties” parameter can mean different things depending on what verb you are using.

    This post will use the “add_target” verb as an example. The documentation defines the “properties” parameter for the “add_target” verb as:

    Name-value pair (that is, prop_name:prop_value) list of properties for the target instance. The “name”(s) are identified in the target-type metadata definition. They must appear exactly as they are defined in this file. Metadata files are located in $AGENT_HOME/sysman/admin/metadata.

    The last sentence specifying where the metadata files are located is simply not true but is a rough interpretation of where the files are. Let’s assume the 12c agent was installed in a base directory called “/u01/app/oracle/product/12.1.0/agent12c”. All of the plugins would be installed in the “plugins” subdirectory of the base directory. The “metadata” directory would be a subdirectory of the plugin and the files contained within that directory pertain to the target types.

    Base Directory = /u01/app/oracle/product/12.1.0/agent12c
    Plugins Directory = /u01/app/oracle/product/12.1.0/agent12c/plugins
    Database Plugin Directory = /u01/app/oracle/product/12.1.0/agent12c/plugins/oracle.sysman.oh.agent.plugin_<version>
    Database Plugin Metadata Directory = /u01/app/oracle/product/12.1.0/agent12c/plugins/oracle.sysman.oh.agent.plugin_<version>/metadata
    Database Instance Metadata File = /u01/app/oracle/product/12.1.0/agent12c/plugins/oracle.sysman.oh.agent.plugin_<version>/metadata/oracle_database.xml
    

    Where the documentation is a little more accurate (but not really) would be for the “host” target type. Since the “host” target type does not use a plugin, it is located in the “core” directory.

    Base Directory = /u01/app/oracle/product/12.1.0/agent12c
    Core Directory = /u01/app/oracle/product/12.1.0/agent12c/core/<version>
    Core Metadata Directory = /u01/app/oracle/product/12.1.0/agent12c/core/<version>/sysman/admin/metadata
    Host Metadata File = /u01/app/oracle/product/12.1.0/agent12c/core/<version>/sysman/admin/metadata/host.xml
    

    Many of the verbs (including add_target) requires that the “Oracle Home” be specified in the properties parameter. When adding or modifying targets in the UI, this property appears on the “Monitoring Configuration” page.

    target properties 1 Enterprise Manager 12c EMCLI Target Properties and Metadata

    However, when specifying the Oracle Home property in the add_target verb, the name of the property is “OracleHome”.

    add_target(
    name='ORCL1_RAC1',
    type='oracle_database',
    host='rac1.example.com',
    properties='
      SID:ORCL1;
      Port:1521;
      OracleHome:/u01/app/oracle/product/11.2.0/dbhome_1;
      MachineName:rac1.example.com',
    credentials='
      Role:normal;
      UserName:dbsnmp;
      password:password') 
    

    To find the correlation between the property in the UI and what is specified in the EMCLI verb, we need to (as the documentation specifies) look into the “metadata” files of the agent. What isn’t clear from the documentation is that there are multiple places in the agent binaries where “metadata” files are found.

    The example given above is for an “oracle_database” target type. Since the monitoring of Oracle database targets are now done with a plugin, the “$AGENT_HOME” actually means the Oracle database plugin home in this case.

    The metadata files have “.xml” or “.xmlp” file extensions. The target type of “oracle_database” is in the “oracle_database.xml” file. These files are not very well commented so having a basic understanding of XML is required. The relevant information is usually toward the bottom but it is easiest to just search for the opening XML tag called “<InstanceProperties>”. For some target types, the XML elements we are looking for will be nested within the “<InstanceProperties>”. But, for others (like this one), they will be referenced from another location.

      <InstanceProperties>
            &inst_static_props;
            &dynamic_properties;
            &esa_inst_dynamic_properties;
            &esa_db_dynamic_properties;
            &inst_dynamic_props;
    
      </InstanceProperties>
    

    In this case, the information we are looking for is not directly inside the “<InstanceProperties>” element. When the XML parser finds an ampersand in the XML data, it expects to find a symbol name and a semicolon following it. The symbol name provides a symbolic reference to another entity. A quick search on this symbolic reference finds that the information is actually stored in another file.

    <!DOCTYPE TargetMetadata  [
    <!ENTITY inst_static_props SYSTEM "./inst_properties.xmlp">
    <!ENTITY inst_dynamic_props SYSTEM "./inst_dynamic_props.xmlp">
    
    ...
    
    <!ENTITY dynamic_properties SYSTEM "./dyn_props.xmlp">
    <!ENTITY esa_inst_dynamic_properties SYSTEM "./esa_instance_dyn_props.xmlp">
    <!ENTITY esa_db_dynamic_properties SYSTEM "./esa_database_dyn_props.xmlp">
    
    ...
    
    ]>
    

    The “inst_properties.xmlp” file contains the elements we are looking for. Each verb property is specified in an “<InstanceProperty>” element. The element uses a combination of tags and nested elements to define the verb property.

    <InstanceProperty NAME="OracleHome" CREDENTIAL="FALSE" OPTIONAL="FALSE">
            <Display>
                    <Label NLSID="OracleHome_iprop">Oracle home path</Label>
            </Display>
    </InstanceProperty>
    

    The first tag called “NAME” is what is used as the property name in the verb. The “CREDENTIAL” tag set to “TRUE” means that the property would be specified in the “credentials” verb parameter, not the “properties” parameter. The “OPTIONAL” tag set to “TRUE” means the property is not necessary and will likely have a default value if not specified. The “IS_COMPUTED” tag set to “TRUE” means that the parameter is dynamically calculated. The “Label” element will give you a good idea what property matches in the UI monitoring configuration page but it usually does not map directly. Not all of the properties specified in the XML file will show up in the UI configuration either.

    From the information in this file, we can tell that the “properties” (they are case sensitive) that must to be specified for the “oracle_database” type are:

    • OracleHome
    • MachineName
    • Port
    • SID

    The “properties” that can be optionally specified are:

    • PreferredConnectString

    The “credentials” that must to be specified are:

    • UserName
    • password

    The “credentials” that can be optionally specified are:

    • Role

    Much of the configuration information in Oracle’s products (especially Enterprise Manager) is stored in XML. Having a basic understanding of the XML structure helps a lot in identifying the information you are looking for. The information used in the “properties” and “credentials” parameters for many of the verbs in the Enterprise Manager CLI can be found in the metadata files within the agent binaries. Knowing where to look and what to look for is the most important step.

    Oracle OpenWorld 2013 and RAC Attack

    Less than a week from now I will board a plane destined for San Franscisco, California for my favorite week of the year. More than 60,000 people will descend on The City by the Bay to build a city block long tent over Howard Street and make the local commuters miserable.

    This year marks my third year in a row attending Oracle’s largest event and my second year as a speaker. I have the honor of speaking on two panels on User Group Sunday; DBA Expert Panel and Oracle Enterprise Manager Panel alongside some of the most respected technologists in the Oracle technical community.

    Last year at OpenWorld, I stumbled into a quiet room of geeks slumped over their laptops; some typing furiously, others staring blankly at a screen, searching for an answer. After seeing the all-to-familiar Oracle Database installer window, I realized that this was a good old fashioned Installfest. I was soon approached by an organizer who asked me if I have ever installed Oracle RAC before. I had done it many times, of course as part of my job, but I couldn’t recall ever doing it just for fun. This event was alliteratively called RAC Attack. I immediately took interest in becoming involved after working with this group at OpenWorld last year, and soon after became a contributor and volunteer.

    RAC Attack is a group of very smart volunteer technologists who contribute their valuable time to introduce people to Oracle RAC. They do this by creating the guides featured on the website and hosting onsite installfests. RAC Attack is now featured at almost every major Oracle sponsored and Oracle related event around the world.

    RAC Attack will be running from 10am until 2pm on Tuesday and Wednesday at the OTN Lounge in Moscone South. It’s not formal so feel free to stop by any time. If the laptop you are carrying is not powerful enough to run a couple of beefy virtual machines, we will have a number of laptops and desktops that you can use. If you are already a RAC Ninja, stop by and help get the newbies hooked on Real Application Clusters.

    Yury Velikanov has taken the lead for us on the planning for this year’s event. Further details about RAC Attack at OpenWorld 2013 can be found at his blog.

    RAC Attack has a Google+ page and a Facebook page.

    Tablespace Size Report Script (updated)

    I actually found it a little difficult to find a good sql script for reporting tablespace size so I took one that was almost there which I believe came from Oracle-Base and modified it to fit my needs. Feel free to comment or modify.

    Update: I’ve made significant changes to this script including a header and the ability to change the output denomination between megabytes, gigabytes or terabytes with a simple script parameter. Take a look at the notes in the script for more details.

    Monitor NFS I/O

    We use RMAN to backup to an NFS share. I was having some very lengthy RMAN backup times during certain time periods and the storage team was not able to correlate the slow downs to anything useful so I decided to monitor the NFS shares.

    I looked at the OS Watcher logs for information about NFS, but they were severely lacking in useful information. The program nfsiostat had the information I was looking for but I wanted to collect information over different periods of time so cron was naturally the best way to schedule the collections.

    However, like vmstat, iostat and most other programs of this nature, when executed, nfsiostat shows a summary since the last boot as its first iteration. Since I am not interested in the summary, I needed a way to exclude that first iteration. What better way to do line processing than to use the 40+ year old tool awk.

    Finding what text to use as the separator between the iterations was easy since the first line of each iteration ends with the nfs mount I’m filtering on. I set the first parameter in the file as the mount point. The command will be executed twice, one second apart, the first being the throw-away summary and the second being the information I want to keep. There is a timestamp captured as well for each execution.

    The output is captured in a file.

    #!/bin/bash
    
    
    NFS_IO_STAT_TARG="/backup"
    NFS_IO_STAT_PARMS="1 2"
    NFS_IO_STAT=/usr/sbin/nfsiostat
    NFS_IO_OUT=~/nfsiostat_$(hostname -s).out
    TIMESTAMP=$(date +%F_%R)_$(hostname -s)
    
    
    (
    echo -e "\n$TIMESTAMP\n"
    $NFS_IO_STAT $NFS_IO_STAT_PARMS $NFS_IO_STAT_TARG | awk 'BEGIN { VAR="'$NFS_IO_STAT_TARG':$" } $0 ~ VAR { ccount++ }; ccount >= 2 { print }'
    echo ====================================================
    ) >> $NFS_IO_OUT
    

    Tnsnames.ora and XML Solution

    I have always found the Oracle connection strings (typically tnsnames.ora) difficult to manage. Even when using LDAP instead of the file, the connection strings still need to be managed even though it only has to be done in one place. After some planning and learning (and relearning) some atrophied perl skills, I created a set of scripts to manage the Oracle connection strings using XML and perl.

    If you look at the Oracle connection string, it looks a lot like XML. Instead of tags using the format

    <ELEMENT>foo</ELEMENT>

    it uses

    (foo)

    I converted the existing tnsnames.ora to XML using Vim’s search and replace and a whole lot of regexp. After converting the file and having a parseable XML file, I realized that the possibilities with this are endless. It doesn’t have to be limited to just the connection strings, a single database entry can contain any amount of information you might want to track with each database. But, we’ll get to more of that later.

    Here is what an Example XML entry will look like.

    The script requires certain elements to properly function. I will detail them here using XPATH syntax. I have chosen /TNSNAMES as the root but it doesn’t really matter what you use. The required children are as follows:

    TNS[@NAME=""]
    TNS/DESCRIPTION/ADDRESS_LIST/ADDRESS
    TNS/DESCRIPTION/CONNECT_DATA
    TNS/DESCRIPTION/CONNECT_DATA/SERVICE_NAMES/SERVICE_NAME[@ID="1"]

    There are many more options to add to an Oracle connection string, and there will be additional functionality added in future releases but for now, these additional paths are optional.

    TNS/DESCRIPTION/*
    TNS/DESCRIPTION/CONNECT_DATA/INSTANCES/INSTANCE[@ID="1"]

    The INSTANCES/INSTANCE[@ID="1"] was added because I am using this script to create multiple entries for the same connection to be used in the Oracle wallet for different users. When I am connecting as the SYS user to a database to change the password, I am specifying that I want to connect to (CONNECT_DATA(INSTANCE=ORCL1)) so that I know which password file is being changed, therefore making it easy to then copy that changed password file to the rest of the nodes in a cluster. The script is shown here and is being hosted on GitHub. The script, the example XML file above and the result file below are all directly linked to GitHub and will change as updates are made.

    Details on how to use the script can be found in the script and listed using the help flag for brief information and the man flag for the full documentation.

    This shows what the XML example file above looks like after being parsed using no script options.

    I will update this post with usage examples and details of additional functionality as it is added.

    I have also written another script that parses the same XML format but the output produces a connections file that can be loaded into Toad. I have my install of Toad set up to use an Oracle wallet that I have populated with all of the database passwords that I use. I use the parser script to produce both the tnsnames.ora that I am using for the connection strings as well as the connections text file that I loaded into Toad to match. I call this script ToadBuilder and I will load it into GitHub as well. I haven’t decided if I will combine the two scripts yet or not since there is a lot of repeated code but I don’t want the script options to become too complicated.

    Please feel free to post feedback or contribute to the scripts. I am an amateur with perl and improvements are very much welcome.