Grid DBA

The Oracle Database has some useful debugging and diagnostics features. One such utility called the Automatic Diagnostics Repository Command Interpreter or ADRCI, enables database and system administrators to view and analyze information including trace and log files. In addition to the usual ADR commands, you can also issue DML-like statements. These statements are limited to the diagnostics information found in the ADR. Let's take a look at how to use ADRCI to issue DML. Please note: DML statement issued via ADRCI don't require a termination character such as the semicolon. Also, the statements have to be on a single line. The following DML commands are defined in ADRCI version 12.1.0.2.0 SELECT UPDATE* DELETE* INSERT* UPDATE* QUERY* To get a list of the commands, use the help  keyword in ADRCI. You may notice that only the SELECT statement is shown by default. To see the list of additional commands, type help extended . Note: Commands proceeded by a...

Oracle Container Cloud Service Oracle Container Cloud Service lets you easily deploy applications based on docker images. If you don't have an existing application you can create one on docker. However, the easiest method to get a full application up and running is by deploying a stack. Oracle Container Cloud Service provides several example stacks that you can deploy, including web servers based on NGINX or Apache, MongoDB database, and Wordpress. You can find more examples on Oracle GitHub Site . Let's see just how easy it is to spin up a Wordpress site using the Oracle Container Cloud Service. Prerequisites: Access to Oracle Cloud Account Subscription to Oracle IaaS Service First, go to the Oracle Container Cloud Service console and launch a new service. In this example, I created a service with three containers. One manager and two worker nodes. Choose an appropriate "shape" for your compute instances. Note: Oracle uses the term "shape" to r...

One of the many necessary and frequent tasks that a database administrator performs before creating a database is installing the Oracle database binaries. These binaries form what is known as an Oracle Home (referenced by the ORACLE_HOME environment variable on Linux and UNIX environments). Unfortunately, this is also one of the most mundane and uninteresting tasks a DBA can perform. So like all good DBAs know, the best way to deal with such repetitive task is to automate it. Automating installations of Oracle database binaries enables us to reliably repeat the process to enable faster installation as well as provide identical copies of the software in multiple environments. Fortunately, Oracle allows us to clone the Oracle database binaries from one environment to another. Cloning your Oracle binaries from a source copy with all the necessary patches applied enables us to have gold images which can then be deployed to multiple environments. The procedure for cloning an Oracle home...

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 12.1.0.1 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. PDB1@ORCL> ALTER SESSION SET CONTAINER=PDB1; Session altered. Check if the user exists PDB1@ORCL> SELECT username,account_status,create...

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.

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 ...

Disclaimer: The steps described in this post have only been tested in a lab environment. Verify that your organization's security policy isn't being violated when doing any of these steps. Twitter is a public social networking platform and has inherent security risks. I will not accept responsibility if you perform this in your production environment. Many applications built in the past couple of years have integrated social networking widgets (useful or not) to connect a social world via Twitter, Facebook, Google+ etc. However, most enterprise applications still rely on email as the primary notification method. Oracle Enterprise Manager Cloud Control 12c allows us to send notifications for incidents, events, jobs out of the box via email. In addition, EM12c supports advanced notification methods including OS scripts, PL/SQL scripts and SNMP traps. For the complete description of Notification Methods you can read the chapter in the Oracle Enterprise Manager Cloud Control...

The information technology industry has been a tremendous growth in Cloud delivery models for IT services in the past couple of years. More recently, the PaaS service model including Database-as-as-Service (DBaaS) has been gaining traction rapidly. Amazon's RDS public DBaaaS service supports Oracle, MySQL and SQL Server and enable rapid development lifecycles by giving end-users the ability to provision databases on demand in a very short period. Security and control are two drawbacks seen in public cloud environments. How can this be overcome while delivering the same level of rapid provisioning, elasticity and multi-tenancy for databases? Oracle Enterprise Manager Cloud Control 12c provides a complete lifecycle management framework for databases - from provisioning to patching. The Database Cloud plug-in enables DBaaS for users on premise without the drawbacks of public clouds. If developers want copies of schemas then the Schema-as-a-Service sub-model can enable them to sel...

This post is mostly a reminder to myself on how to install an EM12c shared agent. I found the documentation somewhat lacking, so I hope this will help someone else attempting to perform a shared agent installation. With a shared agent installation, a master agent is installed on a shared filesystem such as NFS and a shared agent is "installed" on remote hosts. The remote hosts should have read-only access to the share. The documentation lists the following pre-requisites for the mount options on the remote host. mount -t nfs -o ro : For example, run the following command: mount -t nfs -o ro abc.oracle.com:/scratch/masterAgent /scratch/sharedAgent Since I use a NAS for serving NFS shares in my environment I replaced the master_agent_host_name with the name of the filer and the exported volume. The shared agent will have its agent instance located on a local file system which contains configuration and host-specific information. When deploying an agent on N...

Oracle Enterprise Manager Cloud Control 12c introduced several ways to deploy agents to hosts. The Oracle Management Agents are responsible for gathering metrics from targets such as database instances, listeners and the host themselves and sending them to the Oracle Management repository and Server for further processing. It also interacts with targets by executing commands sent via the OMS Console and the EM Command Line Interface (EMCLI). EM12c release 1 introduced four ways of deploying Management Agents Add Target Host Wizard - Agents are mass deployed using the Management Console. AgentPull - Silent install script to automatically download and deploy agents on remote targets using a response file. agentDeploy - Silent install script to deploy agents using a response file after manually downloading a compressed package of the agent using EMCLI and transferring it to remote targets. RPM - Use a .rpm package to deploy to targets that support it (Linux x86-64).  Start...