The idea of creating an Exadata simulator arose at Oracle Day 2011 Istanbul. One of my friends was trying to fix a virtual machine in a hurry (right before his presentation), and he said his “fake Exadata” crashed. He was joking, but I wondered if it’s possible to build an Exadata Simulator using virtual Box (or any other virtualization software). I googled it and found nothing useful, so I started to work on it.

The important point is, simulating Exadata does not mean simulating all features of the Exadata Database Machine. The key features of the Exadata Database Machine are Infiniband connections and Exadata Storage Servers (the offloading capabilities and Flash Cache). It’s obvious that we do not need to simulate InfiniBand. All we need is to simulate “Exadata Storage Servers”.

Smart scanning, storage indexes, hybrid columnar compression, I/O resource manager, smart flash cache are all handled by the Exadata Storage Server “Software”. Although it’s called Oracle Exadata Database “Machine”, its heart is the Exadata Storage Server “Software”. You may say that all hardware needs software, but the Exadata software is not an embedded one. It’s just an application running on Oracle Linux 5.x 64bit.

I found a way to download the Exadata Storage Server Software. It took about three days to install it in a virtual box and one week to solve the problem of mapping physical “disks” to cell disks. By the way, I haven’t modified any executable file or script. So it was a clean installation. Then I created an ASM disk group using my “fake” Exadata Storage and started to test the features of the Exadata Storage Server.

I’ve found a great document at My Oracle Support (Metalink): How to Gather the Header Information and the Content of an Export Dumpfile? [ID 462488.1]. The document explains how to extract DDL statements from dump files and how to use the DBMS_DATAPUMP.GET_DUMPFILE_INFO procedure to gather header information for both original and data pump exports.

In summary,

The Data Definition Language (DDL) statements in a DataPump dump file can be extracted with the parameter SQLFILE:

impdp DIRECTORY=testdir DUMPFILE=test.dmp SQLFILE=ddlcommands.sql FULL=y

Note: This command will not import the data, but it still needs a valid database connection.

The Data Definition Language (DDL) statements in a original export file can be extracted with the parameter SHOW:

imp FILE=test.dmp LOG=test.log FULL=y SHOW=y

The sample PL/SQL script can be found at MOS note. Unfortunately, it can only be used in an Oracle10g Release 2 or any higher release database.

After I’ve made some tests with PL/SQL code, I decided to examine Oracle export files and write my own utility to read the header information.

I’ve seen a question on the OTN forum about how to create a job in Grid Control for generating AWR/ADDM reports and sending these reports via email. As I know OEM Grid Control doesn’t have such a job template but we can write a PL/SQL script for this task and define it as a job, so we can automate it for all databases.

First, let’s check how we can generate AWR reports. To be able to get AWR reports in plain text, we can use:

Syntax (for Oracle 10.2):

DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_TEXT(
l_dbid IN NUMBER,
l_inst_num IN NUMBER,
l_bid IN NUMBER,
l_eid IN NUMBER,
l_options IN NUMBER DEFAULT 0)
RETURN awrrpt_text_type_table PIPELINED;

If we want the report in HTML, we can use:

DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
l_dbid IN NUMBER,
l_inst_num IN NUMBER,
l_bid IN NUMBER,
l_eid IN NUMBER,
l_options IN NUMBER DEFAULT 0)
RETURN awrrpt_text_type_table PIPELINED;