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

Databases Storage – The New Frontier As database and storage administrators and engineers, it is important to understand the effect that storage has on database performance. It is important to understand that databases are an extension of your storage and requires a holistic view when designing and managing solutions that use the database. This article gives an overview of the Oracle database storage landscape – past, present and future. It’s not meant as an in-depth technical treatment of the topic but opens the door for more discussion and engagement about database storage. Evolution Both storage and databases continue to evolve (albeit at different paces). About a decade ago the enterprise storage of choice was the Storage Area Network or SAN array. At about the same time the current release of Oracle was 10gR1, which introduced Automatic Storage Management (ASM), which provided mirroring and striping capabilities for data files. Database sizes were typically in the ...

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

Recently I was running some tests in my lab environment consisting of two Oracle 11.2.0.3 databases on separate RHEL 6.2 VMs. The databases were configured in a Data Guard configuration and used ASM as storage. However, I ran into an issue where my standby database was becoming out of sync with the primary database because the standby was out of disk space. At the time I couldn't request additional storage for my standby (that's what happens when you work at 1:00 am while everyone works normal hours) and I needed to complete my testing. The standby server had some NFS mounted shares that had plenty of space however. This is where social interaction is of benefit because I can recall having several discussions with fellow colleagues on twitter about choosing to use NFS as ASM devices and under what conditions one would choose such a route. Yury also wrote up a blog  post about it. This seemed like a good opportunity to test it out. Only this time I will be mixing ASM block de...

Oracle Database 11g XE has been in beta for several months now. On September 2, the production release was made available for Windows 32-bit and Linux 64-bit platforms. This lightweight version of the juggernaut database of world is geared towards students, developer and ISVs. You can find the full feature list here . Having played around with the beta version I decided it was time to take a stab at deploying it in the cloud - more specifically Amazon AWS EC2.  I started off by selecting the  Oracle Linux 5.6 x86_64 - OVM (ami-42778a2b) to launch a micro-instance of Oracle Linux. The micro-instance has 613MB of physical memory, 2GB swap and 15GB EBS storage.  [root@ip-10-242-49-142 ~]# cat /proc/meminfo | grep SwapTotal SwapTotal: 2104504 kB [root@ip-10-242-49-142 ~]# cat /proc/meminfo | grep MemTotal MemTotal: 629760 kB Once you download the installation archive from OTN and save it to your computer, you'll have to transfer it to your EC2 instance. ...