Saturday, February 14, 2015

Downloading EMCLI via Curl

The Enterprise Manager Command Line Interface or EMCLI enables ease of use and automation for administrators. However, in order to install EMCLI you need to access the EM Console to download the jar file.

A question on twitter about downloading EMCLI via command-line. There may be a couple of ways to accomplish it but the one that came to mind used the same technique as method 2 posted on this blog post by Pythian.

curl --header 'Host:' --header 'User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.9; rv:30.0) Gecko/20100101 Firefox/30.0' --header 'Accept: text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8' --header 'Accept-Language: en-US,en;q=0.5' --header 'DNT: 1' --header 'Referer:' --header 'Cookie: ORA_SMP_EM_AUTH_68934127863622066490=JtCfT0nY2Dq3LMzT2S1V0YLwx8JVQWF11T12vnJPMP5MjJGjd8Mp!1186256006; _WL_AUTHCOOKIE_ORA_SMP_EM_AUTH_68934127863622066490=6CgVs0LAxX1gSBzFxCLy' --header 'Connection: keep-alive' '' -o 'emcliadvancedkit.jar' -L

I'm sure there many more ways to do this...just "Google it".
Now only if emcli were supported on Mac OS X...

Getting Started with VMware vSphere 5.5: Part I

Much of my research to date has involved the use of virtualization technology especially Oracle VM VirtualBox. Lately however, I've found that my aging 5 year old iMac has been struggling to keep up with my testing and research even despite attempts at increasing performance by adding physical memory (16GB) and SSD disks. I could also try using other desktop virtualization software such as VMware Fusion or Parallels for OS X (I have both) since other users have had better experience with them. However, I find the alternatives lacking in some feature that I currently use in VirtualBox (such as shared disks for clustering). Also Oracle publishes several Virtual appliances for VirtualBox which makes it easy to learn a plethora of Oracle products and features. For more on Oracle with VirtualBox you can read my paper and presentations on Slideshare.

With that said I've been wanting to build a dedicated lab machine for doing database research for a while now. I've been using VMware server virtualization products such as ESX/ESXi and vSphere for the past eight years, primarily for Oracle databases in my daily job. However, I decided it is time to finally make the plunge and build a dedicated VMware server.

Oracle VM VirtualBox vs VMware vSphere
Oracle VM VirtualBox is a desktop virtualization product, commonly referred to as a type 2 hypervisor. In a nutshell, it allows you to create guest virtual machines with similar or different operating systems from the host on which the product is run. For example you can install Oracle VM VirtualBox on a Mac OS X computer (host) and then create Windows or Linux virtual machines (guests).
In contrast, VMware ESXi is a server virtualization product or type 1 hypervisor. It doesn't require an additional host operating system to install the hypervisor (the layer of code that manages virtual machines). This is an over simplification on my part but it gets the basic idea across.

The Hardware
One of the first things I had to figure out was what hardware to go with - "whitebox" or OEM. A whitebox is a DIY system in which you purchase all the hardware parts separately and build it to your specification. The advantages are that it is built to your specifications and it's typically cheaper than OEM systems. The disadvantage is that you have to buy all the parts yourself and this can take some time if you need to make sure things are compatible. An OEM system is one which is purchased from vendors like Dell, HP and Lenvo for example. The advantages are that you get a complete system with warranty (1-3 years). The disadvantages are that you have limited customization and the costs are usually significantly higher than that of an OEM system.
Everyone will have their own criteria for determining which path to choose but mine came down to two things - time and money. Although I consider myself hands-on techie, I haven't had to do much with hardware besides the occasional memory or HDD or SSD upgrade recently. I've built my fair share of whitebox systems back in the day but I never found it enjoyable or entertaining. In fact, I recall it being quite time-consuming and frustrating at times. Alas, times have changed but if I can find an OEM system within the same price range with similar specifications, that would be my preference.
My specifications were as follows:

  • Intel Core i7 or new processor
  • 32GB physical memory expandable to 128GB
  • RAID controller
  • 2x2TB 7200 RPM HDD
  • 256GB SSD

Target price: $1,000

I know this seems quite doable with a whitebox system but most OEM systems with these specs are currently out of this price range. To get to the chase I decided on purchasing a Lenovo ThinkStation S30. for $834 + tax. Since this came with 16GB physical memory installed I purchased an additional 16GB (2x8GB) ECC RDIMM for $140 on Amazon. I also added a single 2TB Seagate Constellation ES 2TB 7200RPM SATA 6Gbps 128 MB for $90 on Amazon.

In part II I will cover the VMware ESXi 5.5 installation.

PRVF:002 Could Not Retrieve Local Node Name During Oracle Database 12c Install

Simple troubleshooting note during Oracle Database installation on Oracle Linux 6.6

If you get the message above after running OUI (runInstaller) the message simply indicates that OUI is unable to retrieve the name of the local node. To determine why this happens you can validate using hostname command. 
For example:
[oracle@matthew database]$ hostname --long
hostname: Unknown host
To get additional information use the --verbose flag.
[oracle@matthew database]$ hostname --long --verbose
Resolving `matthew' ...
hostname: Unknown host
It now becomes obvious that the resolution using gethostname() is failing. Hostnames in Linux can be resolved using the /etc/hosts or a name server .e.g DNS. If a name server is not in use a quick check of /etc/hosts will reveal that an entry for the hostname (matthew) is not present.
[oracle@matthew database]$ cat /etc/hosts localhost.localdomain localhost
::1 localhost6.localdomain6 localhost6
Create an entry for the host and verify that it can be resolved.
[oracle@matthew database]$ cat /etc/hosts localhost.localdomain localhost
::1 localhost6.localdomain6 localhost6 matthew.localdomain matthew
[root@matthew /]# hostname --long --verbose
Resolving `matthew' ...
Result: h_name=`matthew.localdomain'
Result: h_aliases=`matthew'
Result: h_addr_list=`'

Now run OUI again and the issue should be solved. While the problem is fairly trivial and for those who read the database installation documents obvious, this was to demonstrate how hostnames are resolved in Linux.

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
ORA-00289: suggestion :
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 ( and neto from Brazil ( 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.

cd /u01/app/oracle/Middleware/plugins/oracle.sysman.ssa.oms.plugin_12.
$ ls

The 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 -help
Cloud Setup Tool
Usage: emcli
                         This flag is used to setup Cloud for Pluggable Database
                         This flag is used to setup Cloud for Database as service
                         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
                         comma separated list of 'Target Name'(s) for 'Host' targets discovered in Enterprise Manager.
                         Database Home location
                         e.g -db_home_location=/u01/app/oracle/11.2.0/dbhome1
                         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.
                         Displays Cloud Setup help

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

       Setup Cloud on Non-Exadata Cluster Machine: Run script on cluster node or any standalone Machine:
             emcli -pdbaas -db_list=CDB1,CDB2,CDB3   : Sets up Cloud for PDBaaS using  given list of Pluggable databases
             emcli -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
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.