Sunday, September 13, 2015

Creating a DB user without using a CREATE statement

I was teaching an Oracle Database 12c New Features class recently when I stumbled upon something I found quite interesting. While preparing for the class, I had to correct some of the exercises that had been provided since there were some typos due to OCR conversion. For one of the exercise, I had to create a new user and while I was entering the commands, I realized that I typed a GRANT statement before issuing the CREATE user statement. This must've been a typo I thought, but proceeded anyway. To my surprise Oracle didn't throw any errors at me!

Well I must have done something wrong so let's try this again.

First let's connect to the database - in this example I'm using Oracle but I've tested and confirmed it works in Oracle 11.2 as well and should also work in Oracle 10.2 but I haven't tested it.

Session altered.
Check if the user exists
PDB1@ORCL> SELECT username,account_status,created
2 FROM dba_users
3 WHERE username LIKE 'TOM%';

no rows selected
Now there is no Tom in my PDB so let's just grant the CONNECT privilege to the non-existent user.

PDB1@ORCL> GRANT connect TO tom IDENTIFIED BY jerry;

Grant succeeded.
That seemed to work. Let's check if the user now exists.
PDB1@ORCL> SELECT username,account_status,created
2 FROM dba_users
3 WHERE username LIKE 'TOM%';

------------------------------ -------------------- ---------

PDB1@ORCL> CONNECT tom/jerry
And he does! This was new to me and a few others since I tweeted my discovery and others had the same sentiment.

This is also documented in the Database SQL Language reference. The IDENTIFIED BY clause is part of the grantee_system_privilege. This means that it doesn't work for object privilege grants.
To illustrate I'll try to grant insert on the employees table to a non-existent user "TWEETY".
ERROR at line 1:
ORA-01917: user or role 'TWEETY' does not exist
I don't recommend using the GRANT statement to create a user. I'd rather explicitly use the CREATE statement for that purpose. Has anyone used this before? Feel free to leave comments below.

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.