Archive for the ‘Exadata’ Category

Oracle Autonomous Database: Dedicated vs Serverless

In Autonomous, Databases, DBA, Exadata, Oracle database, Oracle Engineered Systems on July 22, 2019 at 11:45

This blog post describes the 5 main differences between ATP-D & ATP-S and the 5 main ATP-D physical characteristics and constraints.

Autonomous Transaction Processing Dedicated (ATP-D) is a deployment choice that enables us to provision autonomous databases into dedicated Exadata cloud infrastructure instead of a shared infrastructure with other tenants.

ATP-D can be used for both OLTP or hybrid workloads for databases of any size. ATP-D is specifically good when you need highest governance, consistent performance and operational control.

For now, dedicated infrastructure means a Quarter Exadata Rack. Half and Full will be soon available too.

Besides complete physical storage isolation, ATP-D provides private IP networking, secure connections using transport layer security (TLS) credentials, and customization of software image lifecycle to align with application lifecycle.

The Fleet Administrator (think of a DBA for Autonomous Cloud) needs to create first the Exadata Infrastructure, then the CDB and only at the end the PDB. Recall that for ATP-S, you directly create the PDB.

These are the 5 main differences between ATP-D and ATP-S:

– Private IPs are not yet supported for serverless ADB deployments but they are on the short-term roadmap
– Private IPs are supported with ATP-Dedicated

– Serverless edition has no minimums or maximums for terms of usage
– Dedicated edition has a minimum term of one month and the minimum OCPU purchase is 1 OCPU per database node and up to the maximum number of OCPUs per rack: $26.88 per hours which means about $645 per day

– Loading data from object stores via DBMS_CLOUD is the recommended method for loading large data sets
– DBMS_CLOUD to load data is not applicable for ATP-D because DBMS_CLOUD is not available on ATP-D

– In ATP-D, the database version is 19c which is required for Auto-Indexing which is on by default
– Support for 19c / Auto-Indexing on ATP-S is on the roadmap

– ADB (serverless) does have auto-scaling – you can select auto scaling during provisioning or later using the Scale Up/Down button on the Oracle Cloud Infrastructure console
– ATP-D does not have auto-scaling support

Here are the 5 main ATP-D physical characteristics and constraints:

1. Quarter rack X7 Exadata Infrastructure:
– 2 severs: 92 OCPUs and 1.44TB RAM
– 3 Storage Servers: 76.8TB Flash and 107TB Disk

2. Cluster / Virtual Cloud Network:
– 1 Cluster per quarter rack

3. Autonomous Container Database:
– Maximum of 4 CDBs per Cluster
– The default data and temporary tablespaces for the database are configured automatically
– The name of the default data tablespace is DATA
– The database character set is Unicode AL32UTF8
– Compression is not enabled by default – use the table_compression clause if needed

4. Autonomous Database:
– High Availability SLA – Maximum 200 DBs
– Extreme Availability SLA – Maximum 25 DBs
– Placement Logic – Open on 1 server < 16 OCPU

5. Types of Users: Fleet Admin, Database Admin and Database User as Fleet Admin activities separated from DB Admin using IAM privileges

Note that now, there are 2 tabs for possible database connections – the serverless style DB connection and application connection:

Fleet Administrator: Fleet administrators create, monitor and manage Autonomous Exadata Infrastructure and Autonomous Container Database resources – a fleet administrator must be an Oracle Cloud user whose permissions permit the management of these resources and permit the use of the networking resources that need to be specified when creating these resources

Database Administrator: DBAs create, monitor and manage Autonomous Databases. Additionally, they create and manage Oracle Database users within these databases, and provide others the information necessary access the database – when creating an Autonomous Database resource, the DBA defines and gains access to the ADMIN administrative user account for the database

Database User: Database users are the developers who write applications that connect to and use an Autonomous Database to store and access the data. Database users do not need Oracle Cloud accounts: they gain network connectivity to and connection authorization information for the database from the database administrator

Few useful links:

A Using Oracle Database Features in Autonomous Transaction Processing
FAQs for Autonomous Transaction Processing – Dedicated
Data Center Regions for PaaS and IaaS
Oracle broadens the audience for Automated Transaction Database
2 Ways Oracle’s Autonomous Database Just Got More Useful

Bottom line: even with Autonomous, DBAs will be still needed!

Migrating Amazon Redshift to Autonomous Data Warehouse Cloud

In Autonomous, Data Warehouse, DBA, Exadata, PostgreSQL on July 4, 2018 at 18:34

“Big Data wins games but Data Warehousing wins championships” says Michael Jordan. Data Scientists create the algorithm, but as Todd Goldman says, if there is no data engineer to put it into production for use by the business, does it have any value?

If you google for Amazon Redshift vs Oracle, you will find lots of articles on how to migrate Oracle to Redshift. Is it worth it? Perhaps in some cases before Oracle Autonomous Data Warehouse Cloud existed.

Now, things look quite different. “Oracle Autonomous Data Warehouse processes data 8-14 times faster than AWS Redshift. In addition, Autonomous Data Warehouse Cloud costs 5 to 8x less than AWS Redshift. Oracle performs in an hour what Redshift does in 10 hours.” At least according to Oracle Autonomous Data Warehouse Cloud white paper. And I have nothing but great experiences with ADWC. For the past half an year or so.

But, what are the major issues and problems reported by Redshift users?

One of the most common complaints involves how Amazon Redshift handles large updates. In particular, the process of moving massive data sets across the internet requires substantial bandwidth. While Redshift is set up for high performance with large data sets, “there have been some reports of less than optimal performance,” for the largest data sets. An article by Alan R. Earls entitled Amazon Redshift review reveals quirks, frustrations claims that reviewers want more from the big data service. So:

Why to migrate from Amazon Redshift to Autonomous Data Warehouse Cloud?

1. Amazon Redshift is ranked 2nd in Cloud Data Warehouse with 14 reviews vs Oracle Exadata which is ranked 1st in Data Warehouse with 55 reviews.

The top reviewer of Amazon Redshift writes “It processes petabytes of data and supports many file formats. Restoring huge snapshots takes too long”. The top reviewer of Oracle Exadata writes “Thanks to smart scans, the amount of data transferred from storage to database nodes significantly decreases”.

2. Oracle Autonomous dominates in features and capabilities:

DB-engines shows an excellent system properties comparison of Amazon Redshift vs. Oracle.

In addition, reading through these thoughts on using Amazon Redshift as a replacement for an Oracle Data Warehouse can be worthwhile. It shows how Amazon Redshift compares with a more traditional DW approach. But Enterprises have some Redshift concerns, including:

– The difference between versions of PostgreSQL and the version Amazon uses with Redshift
– The scalability of very large data volume is limited and performance suffers
– The query interface is not modern, interface is a bit behind
– Redshift needs more flexibility to create user-defined functions
– Access to the underlying operating system and certain database functions and capabilities aren’t available
– Starting sizes may be too large for some use cases
– Redshift also resides in a single AWS availability zone

3. Amazon Redshift has several limitation: Limits in Amazon Redshift. On the other hand, you can hardly find a database feature not yet implemented by Oracle.

4. But the most important reason why to migrate to ADWC is that the Oracle Autonomous Database Cloud offers total automation based on machine learning and eliminates human labor, human error, and manual tuning.

How to migrate from Amazon Redshift to Autonomous Data Warehouse Cloud?

Use the SQL Developer Amazon Redshift Migration Assistant which is available with SQL Developer 17.4. It provides easy migration of Amazon Redshift environments on a per-schema basis.

Here are the 5 steps on how to migarte from Amazon Redshift to Autonomous Data Warehouse Cloud:

1. Connect to Amazon Redshift
2. Start the Cloud Migration Wizard
3. Review and Finish the Amazon Redshift Migration
4. Use the Generated Amazon Redshift Migration Scripts
5. Perform the Post Migration Tasks

Check out what Paul Way says about why Oracle thinks Autonomous IT can ultimately win the Cloud War.

Finally, here is what Amazon CTO Werner Vogels is saying: Our cloud offers any database you need. And I agree with him that a one size fits all database doesn’t fit anyone. But mission and business critical enterprise systems with huge requirements and resource needs deserve only the best.

Oracle Exadata Cloud Machine Q&A

In Cloud, Database options, DBA, Exadata, IaaS, Oracle database, Oracle Engineered Systems on March 28, 2017 at 07:25

“The computer industry is the only industry that is more fashion-driven than women’s fashion.” Larry Ellison

Amazon have no on-premise presence, it is cloud only. Microsoft have the Azure Stack but the Azure Stack Technical Preview 3 is being made available as a Proof of Concept (POC) and must not be used as a production environment and should only be used for testing, evaluation, and demonstration.

Oracle Database Exadata Public Cloud Machine, or Exadata Cloud Machine, or ExaCM in short, is a cloud-based Oracle database subscription service available on Oracle Exadata, and deployed in the customer or partner data center behind their firewall. This allows customers to subscribe to fully functional Oracle databases on Exadata, on an OPEX driven consumption model, using agile cloud-based provisioning, while the associated Exadata infrastructure is maintained by Oracle.

This blog post contains the top 10 (5 commercial and 5 technical) facts about the Exadata Cloud Machine:

1. On-premise licenses cannot be transferred to ExaCM.

2. The minimum commitment to both the ExaCM and OCM is 4 years and the minimum configuration is Eighth Rack.

3. The subscription price for Oracle Database Exadata Cloud Machine X6 Eighth Rack is $40,000 per month (= $2,500 X 16) and that includes all DB options/features, Exadata Software and OEM DB Packs.

4. Standalone products such as Oracle Secure Backup and Oracle GoldenGate are not included in the ExaCM subscription. Only the database options (such as RAC, In-Memory, Partitioning, Active Data Guard, etc.), the database OEM packs and the Exadata storage server software are included.

5. ExaCM requires Oracle Cloud Machine to deploy Exadata Cloud Control Plane (separate subscription). OCM subscription requires similar minimum term commitment as ExaCM. If a customer already has an OCM, that can be leveraged to deploy Exadata Control Plane at no extra cost. One OCM Model 288 can manage 6 ExaCM Full Racks (i.e. 24 ExaCM Quarter Racks or 12 ExaCM Half Racks). Theoretically one OCM can support a much larger number of ExaCM full racks: about 50.

6. The 1/8th rack SKU is very similar to the on-premises 1/8th rack – i.e. minimum configuration of 16 OCPUs (cores), 240 GB RAM per database server, 144 TB raw storage (42 TB usable), 19.2TB of Flash. Compared to the Quarter Rack, it ships with less RAM, disk storage and flash. Those will be field installed if the customer chooses to go for the 1/8th to Quarter Rack upgrade. Note that this 1/8th rack enables customers to have an entry level configuration that is similar to what exists in Exadata Cloud Service.

7. Hourly Online Compute Bursting is supported with ExaCM. The commercial terms are the same as in Exadata Cloud Service – i.e. 25% premium over the Metered rate, calculated on an hourly basis. Customers can scale up or down, dynamically. Bursting does not kick in automatically based on load. Bursting of OCPUs needs to be configured by customers as needed. Once customer initiates bursting, the OCPU update is done dynamically without downtime. Customers will be billed later on the hours of bursting usage. Price: $8.401 per OCPU per hour.

8. If Cloud Control Plane is down, it doesn’t affect the availability of steady state runtime operations. However, cloud-based management (e.g. selfservice UI and REST API access) will be impacted.

9. Access: the Exadata Cloud Machine compute nodes are each configured with a Virtual Machine (VM). You have root privilege for the Exadata compute node VMs, so you can load and run additional software on the Exadata compute nodes. However, you do not have administrative access to the Exadata infrastructure components, including the physical compute node hardware, network switches, power distribution units (PDUs), integrated lights-out management (ILOM) interfaces, or the Exadata Storage Servers, which are all administered by Oracle.

10. Patching and backups: you can produce a list of available patches using the exadbcpatchmulti command as follows

# /var/opt/oracle/exapatch/exadbcpatchmulti -list_patches 

When you create a database deployment on Exadata Cloud Machine, you must choose from the following automatic backup configuration options:

– Remote Storage Only: uses remote NFS storage to store periodic full (RMAN level 0) backups and daily incremental backups, with a seven day cycle between full backups and an overall retention period of thirty days.
– None: no automatic backups are configured. Automatic backups cannot be configured later if you select the None option when you create a database deployment.

Useful links:

Oracle Exadata Cloud Machine Documentation
Oracle Database Exadata Cloud Machine Data Sheet
Features and Benefits of Oracle ExaCM
Oracle Database Exadata Cloud Machine Pricing
Creating an Exadata Cloud Machine Instance
Known Issues for Oracle Exadata Cloud Machine
Oracle SVP, Juan Loaiza, describes Oracle Database Exadata Cloud Machine

Oracle X5 vs BMW X5

In DBA, Exadata, Oracle Engineered Systems on February 12, 2015 at 13:48

Oracle Exadata X5 is the latest generation of Engineered Systems which can be compared to a BMW X5. The “equivalent” integrated systems of competitors fall into the category of a mid class sedan except for the price. You must be willing to pay less in order to get the double and triple performance of Exadata.


To get more details on the topic, it is worth reading CIO Angle’s Oracle declares war on EMC, VCE: “Oracle will sell its latest generation X5 Engineered Systems hardware, which is Oracle’s term for converged systems, separately from Oracle applications on Linux or Windows at a significantly lower price than market leader VCE’s comparable systems. Ellison announced list prices that are half of VCE’s discounted prices. And he said “We will negotiate. We will discount.”

Ellison also announce an all-flash array that’s part of X5 but sold as a stand-alone unit. Again, the list price is one-third of the price of what Oracle said is a comparable EMC XtremeIO array. Significantly, he did not announce a stand-alone server or network switch, although these are also X5 components. This seems to indicate that Oracle is not going after Cisco Systems, at this time.”


What is worth rather watching than reading is the following presentation by Larry Ellison: The Next Generation of Oracle Engineered Systems.


And here are some useful links:

1. Juan Loaiza – Oracle Next Generation Engineered Systems Launch – theCUBE

2. Gartner: New X5 Generation Will Bring Pricing Improvements to Oracle Exadata

3. Oracle aims to undercut competition with X5 generation of engineered systems

4. Oracle Debuting FS1 Series Flash Storage System

5. Oracle Introduces New Generation of Engineered Systems and New Pricing Strategy

6. Oracle Exadata X5 Data Sheet

7. Oracle Serer X5-2 System Architeture


Exadata X5 with the In-Memory option enabled would be M6 Gran Coupe, if it were to be car.

Cross-Platform Transportable Database and Oracle Engineered Systems

In Consolidation, DBA, Exadata, Oracle database, RMAN, SuperCluster on May 25, 2014 at 15:25

Cross-platform transportable database is not the same thing as transportable tablespace. When performing x-platform transportable database we copy the entire database, including the SYSTEM and SYSAUX tablespaces from one platform to another. The usual containment checks are no longer needed and because the SYSTEM tablespace is also being copied, no metadata datapump export/import step is required. But cross-platform transportable database can only be performed between platforms that have the same endian format.


When consolidating a large number of databases onto Exadata or SuperCluster, the work has to be automated as much as possible. When the source and the target platform share the same endian (see the 2 endian group below), then the best option is to use the transportable database method.

Otherwise, there are 3 options:

1. Create a new database on the EXA platform manually, and transport the needed tablespaces from the source database using the Cross Platform Transportable Tablespace method.
2. If downtime during the upgrade is an issue, Cross Platform Incremental Backup is an extremely good option. You can Refresh Standby Databases using the method.
3. Use the new Full Transportable Export/Import method. Note that full transportable export is available starting while full transportable import is available starting


DBAs should consider the following points before the migrations:

– Some parts of the database cannot be transported directly: redo log files and control files from the source database are not transported. New control files and redo log files are created for the new database during the transport process (alter database backup controlfile to trace resetlogs;), and an OPEN RESETLOGS is performed once the new database is created.

– BFILEs are not transported. RMAN provides a list of objects using the BFILE datatype in the output for the CONVERT DATABASE command, but users must copy the BFILEs themselves and fix their locations on the destination database. Execute DBMS_TDB.CHECK_EXTERNAL in order to identify any external tables, directories or BFILEs.

– Tempfiles belonging to locally managed temporary tablespaces are not transported. The temporary tablespace will be re-created on the target platform when the transport script is run. After opening with resetlogs, run alter tablespace TEMP add tempfile…

– External tables and directories are not transported. RMAN provides a list of affected objects as part of the output of the CONVERT DATABASE command, but users must redefine these on the destination platform. Run select DIRECTORY_NAME, DIRECTORY_PATH from DBA_DIRECTORIES and ensure that the same paths are available on the target system.

– Password files are not transported. If a password file was used with the source database, the output of CONVERT DATABASE includes a list of all usernames and their associated privileges. Create a new password file on the destination database using this information.

– When all is done, run UTLIRP and UTLRP.

For SAP users, check Note 105047 – Support for Oracle functions in the SAP environment:

86. Transportable Tablespaces: the implicit use is supported in the BRSPACE function “-f dbcreate” (Note748434) and the “Tablespace Point in Time Recovery” function of BRRECOVER. Explicit use as part of system copying is tolerated.

87. Transportable database: Can be used (Note 1367451).

And finally, here is some more information on how to migrate your databases to Oracle Engineered Systems:

1. Exadata MAA Best Practices Series Session 6: Migrating to Exadata

2. Exadata MAA Best Practices Migrating Oracle Databases

3. Oracle’s SPARC M5-32 and SPARC M6-32 Servers: Domaining Best Practices


Oracle Exadata myths and misconceptions

In DBA, Exadata, Oracle database on February 9, 2014 at 04:47

Exadata experience, what does that actually mean? by Martin Bach and Friday Philosophy – I Am An Exadata Expert? by Martin Widlake raise few questions on how is an Exadata DBA different than an Oracle DBA.

This chart provides the 3-month moving total of permanent IT jobs citing Oracle Exadata within the UK as a proportion of the total demand within the Database and Business Intelligence category.


Clearly, the demand is high and growing. Several Oracle DB experts are now also Exadata experts. There are various internet blogs and discussion forums dedicated to Exadata. Event conferences: E4.

The aim of this blog is to point out few Oracle Exadata myths and misconceptions I have encountered and discussed with Exadata experts during the past year or so.

1. “When logged into the database (SQL*Plus, SQL Worksheet, etc.) there is no way to see if the database is an Exadata one or not.” Actually there is. If select count(*) from (select distinct cell_name from gv$cell_state) returns a number > 0, then the database runs on Exadata storage. This SQL is also used by the SYS.DBMS_FEATURE_EXADATA procedure:


Number 7 tells us also that we are using a half Exadata rack. Full X4 Exadata rack has 14 cells. Full T5-8 SuperCluster rack has 8 cells.

2. “Flash Cache compression is enabled by default.” No, it is not. The reason is that clients must have licensed the advanced compression option on all databases that access the compressed flash cache.

You can enable using the Flash Cache compression by the cell command “alter cell flashCacheCompress=TRUE”. On an X3 Exadata you should also run “alter cell flashCacheCompX3Support= TRUE”.

Note that the Flash Cache compression is supported on X3 or X4 storage servers and it requires F40 or F80 cards. X4 Flash Cache compression is expanded to 88TB (raw) per rack. Monitor the Flash Cache compression by using the cell metric FC_BY_USED!

3. “Exadata is always RAC.” No, it is not. In fact, consider carefully if you want to use the RAC option on your Exadata. Ask for second opinion too! Ask for third opinion as well. You can have more databases on a quarter or 1/8th rack if you do not use the RAC option. And if someone comes with the HA excuse to use RAC, ask if (s)he can walk and run on one foot when the other one is injured. With well implemented instance caging one can run even close to 100 database instances on one database node (yes there are such cases). Often simplicity brings better results.

4. “DBAs should let Oracle decide on what is placed in the Flash Cache.” In general yes but I have seen significant improvement when frequently used tables are manually kept in the Flash Cache. Although the Flash Cache is fully automated for reads and writes, DBAs can decide on object caching by setting cell_flash_cache_keep. The granularity is all the way to the object level, where you can define higher priority for caching (KEEP) or not to cache at all (NONE). The flash cache can differentiate between full table scans and single I/O reads.


– Frequently accessed data and index blocks
– Control file reads and writes
– File header reads and writes

Not cached:

– I/Os to mirror copies
– Backup-related I/O
– Data Pump I/O
– Data file formatting
– Table scans do not monopolize the cache

Check Uwe Hesse’s article about the Exadata The Flash Cache.

5. “The bigger SGA the better.” Although I have often seen how increasing the SGA can really boost the application performance, this is not always the case with Exadata. Why? In order to offload the work to the sells, the table should be enough big. But what is meant by a big table?

“_small_table_threshold” is sized in units of database blocks, and is supposed to be set by default to 2% the size of the database buffer cache (1.5% in 12c according to my tests) and _very_large_object_threshold = 500 (value for 12c)

Tables with size > 5 x “_small_table_threshold”, are being treated as ‘large tables’ for their reads result in direct reads. It is rather unclear how these things work for tables with size between 2–5 times the small table threshold. Note that 12c has 108 underscore parameters for different threshold values!

Check out these 2 article:

Smart Scan: Why is “_small_table_threshold” Important?
Exadata Optimization Tips


6. “Storage indexes can be managed and maintained.” I wish that could be possible. Kellyn says in the above mentioned paper Exadata optimization tips: “Do not count on storage indexes. The feature, when implemented by Oracle is one requiring the choice of a complicated set of algorithms. They can be your savior or your devil.” Well, Oracle tries to maintain storage index summaries during loads but it is just a best effort – not the most optimal given the memory constraint and the characteristics of writes during loads.

Plus storage indexes are not persistent: after cell reboot the very first query will not benefit. In simple words, whoever comes first to the office on Monday morning (after weekend’s cell maintenance) might expect better performance from his/her reports.

You can disable/enable the storage indexes at cell level:

alter cell events = “immediate cellsrv.cellsrv_storidx(‘disable’, ‘ALL’, 0, 0, 0)”;
alter cell events = “immediate cellsrv.cellsrv_storidx(‘enable’, ‘ALL’, 0, 0, 0)”;
alter cell events = “immediate cellsrv.cellsrv_storidx(‘purge’, ‘ALL’, 0, 0, 0)”;


I would really like to have a dynamic view showing the existing storage indexes!

7. “Oracle Database 12c running on Exadata Database Machine requires Exadata version 12.1 or later.” This is the recommended approach but you can still use Oracle Database 12.1.0 with Exadata version or Exadata version

Oracle Database 12c running on Exadata 11.2 is subject to the following restrictions:

– The 12.1 offload libraries are not present on Exadata version 11.2. Therefore, smart scan offloaded filtering and storage indexes are disabled for 12.1 databases. Smart scans are still issued, and the initiation of IOs is offloaded, but complete blocks are returned instead of just the selected rows and columns. 11.2 databases on the same system have full offload capabilities.
– I/O Resource Management (IORM) plans for 12.1 database are not enforced.
– Database resource plans (intradatabase resource plans) set on 12.1 databases are not enabled on cells.
– An interdatabase plan cannot manage 12.1 databases, therefore an interdatabase plan cannot be used to manage flash log or flash cache for a 12.1 database.
– Other Exadata features like Smart Flash Cache, Smart Flash Log, Hybrid Columnar Compression, and Low Latency and High Throughput InfiniBand messaging continue to work with 12.1 databases. Note that these restrictions only effect performance of 12.1 databases.
– All 12c database functionality, such as Pluggable Databases, is fully available.

A final comment: I see often the power of Exachk tool is somehow underestimated. Use also the ExaWatcher utility which is located in each Exadata database server and storage cell under /opt/oracle.ExaWatcher/. ExaWatcher replaces OSWatcher in Exadata software versions and up.

No Data and Big SQL: Oracle Database 12c on Exadata and SuperCluster

In DBA, Exadata, Oracle database on July 25, 2013 at 10:51

“If you want to succeed you should strike out on new paths, rather than travel the worn paths of accepted success.” John D. Rockefeller

No Data: with Exadata, with the help of storage indexes, you can sometimes perform full table scans without any physical I/O! Really, no data is being read or written to! Richard Footie explains the details in his article entitled Exadata Storage Indexes – Part I (Beginning To See The Light).

Big SQL: with Oracle Database 12c, on a single AWR container report, you can see SQLs from all databases (up to 252) plugged into the container databases, that is a big SQL report! We will see lot of Big SQL with the increased usage of container databases. You can see a screenshot of a 12.1.0 AWR report (SQL ordered by Elapsed Time) at the end of this blog post. Note that in IBM terminology, Big SQL is the SQL interface to its Hadoop-based platform, InfoSphere BigInsights.

How about 12c on Exadata or SuperCluster? Is it supported?


Yes, Oracle 12.1.0 is supported on Exadata and SPARC SuperCluster. The system should run Exadata software version You can have both 11gR2 and 12cR1 databases running simultaneously on the same cluster.

The Grid Infrastructure must be first upgraded from 11g to 12c. You may install fresh 12c GI only in case you do not plan on creating new 11gR2 databases (this will fail with SSO wallet incompatibility due to Bug 16844086).

PDB Quiz

Oracle Database 12c running on Exadata or SuperCluster requires the following:

• Exadata version or later installed on Exadata Storage Servers and database servers
• Patch 16547261 applied on Exadata Storage Servers
• Database servers running Oracle Linux Unbreakable Enterprise Kernel (UEK) 2.6.32-400.21.1.el5uek updated to 2.6.32-400.29.1.el5uek. Other Oracle Linux kernels supplied with Exadata do not require update.

Note that Oracle Exadata is sometimes still being referred to as Oracle SAGE (SAGE = Storage Appliance for Grid Environments):


Oracle Database 12c running on Exadata Database Machine is subject to the following restrictions:

• The 12.1 offload libraries are not present on Exadata version Therefore, Smart Scan offloaded filtering and storage indexes are disabled for 12.1 databases. Smart Scans
are still issued, and the initiation of IOs is offloaded, but complete blocks are returned instead of just the selected rows and columns. 11.2 databases on the same system have
full offload capabilities. A future release of Exadata storage server software will add 12.1 offload libraries and lift these restrictions.

• I/O Resource Management (IORM) plans for 12.1 database are not enforced.
– Database resource plans (intradatabase resource plans) set on 12.1 databases are not enabled on cells.
– An interdatabase plan cannot manage 12.1 databases, therefore an interdatabase plan cannot be used to manage flash log or flash cache for a 12.1 database.

• Cell metrics for 12.1 databases are reported under OTHER_DATABASE.

Other Exadata features like smart flash cache, smart flash log, hybrid columnar compression, and low latency and high throughput infiniband messaging continue to work with 12.1 databases. Note that these restrictions only effect performance of 12.1 databases. All 12c database functionality, such as Pluggable Databases, is fully available.

Not directly related to the topic but look at the AWR report in 12c. It shows the PDB name where the SQL was run:


AWR reports in 12.1.0 are slightly different than those in 11g. CDBs and PDBs are the main factor.

Back to Exadata: the new Oracle Exadata Plug-in comes with several new features. Now, it supports SPARC SuperCluster, 1/8 Rack and multi-rack certifications. Check the complete list here.

DBA tips for tuning Siebel on RAC and Exadata

In Database tuning, DBA, Exadata, Oracle database, Oracle internals, RAC, Siebel on April 21, 2013 at 11:27

More than 100 years ago, Mark Twain said that “the elastic heart of youth cannot be compressed into one constrained shape long at a time”.

In modern technology, “the elastic cloud can be compressed with the constraints of long datatypes”. Let me explain the meaning of my statement in terms of Siebel applications running on RAC and Exadata.


Chained and migrated rows are often a big problem in a Siebel database. The issue of chained rows can be resolved by using big block size while migrated rows require segment reorganization with higher PCTFREE (20, 30 or even 50). Chained tables in Siebel are often S_ORG_EXT, S_ASSET, S_ORDER_ITEM and S_ORDER_ITEM_OM. At least these will require using a bigger block size.

For LONG and CLOB details in a Siebel CRM, check CLOB Physical Type in Siebel.

But as Joel Goodman noted in How Smart is Your Smart Scan?, there is an Exadata specific situation that causes migrated rows.

When a row is updated in a Hybrid Columnar Compressed (HCC) table, then it is migrated to another block in the segment that is managed using “OLTP compression“. Any HCC Compression Unit (CU) containing at least one migrated row, will also cause the block containing that row to be accessed by the server using a “cell single block physical read“.

Look at the “Top 5 Timed Foreground Events” in a Siebel on Exadata OLTP DB using HCC:

cell single block physical read

The situation with HCC in OLTP is tricky for the following 3 reasons:

1. Every update of a record stored in HCC format results in a migrated row
2. The new row is stored in a new block that is marked for OLTP compression
3. Non-direct path inserts will be loaded into OLTP compressed blocks as opposed to HCC format

For the above reasons, mixing HCC with DML is not recommended. Partitioning can provide a mechanism for avoiding these issues since each partition can have its own storage format.

Only after decompressing the OLTP tables, the event “cell single block physical read“ disappeared and the performance got significantly improved.

Another good tip for Siebel on RAC is the usage of high number of hash partitions for hot indexes. High means 256 and more (should be a power of 2).

Look at the situation with gc buffer busy waits before the high hash partitioning:


As you can see the “gc buffer busy acquire” was the top event:


Looking at issues with these events in MOS might incline you to believe that this is a bug. However, this was not the case as you can see what happened after making the number of hash partitions for some of the indexes 256, the database performance was back to normal:


Note that also non-partitioned tables can have hash partitioned indexes!

Another tip: SecureFiles is a feature introduced in Oracle Database 11g that is *specifically* engineered to deliver *high performance* for this type of unstructured data. I have seen several queries getting even 10 times faster after migrating LOBs to SecureFiles.

About sequences: in RAC/Exadata, using the CACHE and NOORDER options together results in the best performance for a sequence. For example, in a Siebel database the S_DOCK_TXN_LOG_S sequence is used to generate the transaction ID used by S_DOCK_TXN_LOG table. The default cache size for sequences in Oracle is 20. If you are having thousands of concurrent users, Siebel/Oracle suggest you increase the cache size to be at least 10000.

Last 2 things:

– For gathering Siebel database statistics use always the latest version of coe_siebel_stats.sql. As of now, the latest version is
– The script coe_siebel_profile.sql provides a list of columns that are not indexed but potentially are good candidates for indexing according to their usage by the Optimizer.

For additional tips, check Oracle’s white paper Siebel on Exadata!

And using Oracle Enterprise Manager makes all tuning so much easier!


The Oracle Platform Administrator

In DBA, Exadata, Oracle database on March 1, 2013 at 10:31

Question: what do the following two pairs have in common:
– Oracle database and non-Oracle hardware/software
– Rowan Atkinson and Audrey Hepburn?

Rowan Hepburn 2

My answer: they are both good but don’t fit very well into the same picture.

Creating a back-end database system based on multiple vendors’ components make less and less sense nowadays. Consider the following:


Which one would you go after, left or right? To make it more clear, choose between Exadata and IBM POWER7 with AIX OS on top of EMC being monitoring with BMC Patrol and backed up with HP Data Protector integration?

I have witnessed from my experience that hardware and software components from multiple vendors are complex to support. No one supplier is usually accountable at a system level.

In a consolidated database environment, a complete test of a major change often must include synchronous testing the multiple workloads in order to catch situations where a change in one area might cause an issue in another. Patching for example one component breaks sometimes another one.


With Exadata, Oracle provides a single point of support for the Oracle Exadata Database Machine both for hardware and software. Support problems are resolved much faster than where multiple vendors are involved and can be reported and managed by the DBA. But is the Exadata DBA still just a DBA?

Kevin Closson used the term Oracle Platform Administrator (OPA) in a response to an article from Martin Bach about the meaning of Exadata experience.

Let me quote Kevin:

“It’s nearing the point where an Oracle DBA cannot do everything that is expected. The acronym no longer really fits. Database Administrator? What does that have to do with cellsrv? DBA? What does that have to do with clusters (at the platform level [you mention fencing]), etc, etc, etc.

It’s nearing time that there should be two roles:

1) The Oracle Platform Administrator (OPA)
2) The Oracle Database Administrator

The DBA tends to the *contents* of the database and applications’ needs for data from the database, tuning, etc. The OPA deals with all the activity that was once the domain of the System Admin and Storage Admin and, moreover, the OPA deals with the crushing load of maintaining Oracle software (patching).

I can’t see it any other way. Oracle has moved their technology stack in a direction that essentially warrants this split in responsibilities. Think of any other RDBMS product where the DBA is expected to be a platform administrator as well. I’m not saying good or bad on that matter, but it is high time to consider the split.”

Uwe Hesse has just written an excellent blog post entitled “How Exadata will impact your IT Organization”. The difference between the Exadata Database Administration (EDBA) and the Oracle Platform Administrator (OPA) is probably close to zero. I see it more like that the EDBA is just a DBA managining also Exadata, while OPA is more of a general admin having excellent DBA skills. Semantics!

Obviously, additional training for the DBAs is needed but by no means the 285 days of new DBA training for existing Oracle DBA suggested by Allen Licitra and Mark Shainman. I wonder how that number was calculated and if we are talking calendar or workdays 🙂

I would highly recommend Enkitec’s Course on Oracle Exadata Administration!

Arup Nanda suggests that the role should have the following skillsets:


I would furthermore split the Database Administrator part as follows:


I do not know where the meetings and conf. calls fit in.

But as Andrew Carnegie said: “Concentrate your energies, your thoughts and your capital. The wise man puts all his eggs in one basket and watches the basket!” And who else to that on the Oracle side but the Oracle Platform Administrator.

Exadata’s Total Cost of Ownership

In Cloud, Consolidation, DBA, Exadata, Oracle database on November 29, 2012 at 11:55

According to Elbert Hubbard “One machine can do the work of fifty ordinary men. No machine can do the work of one extraordinary man”.

Of course, neither Exadata nor Bloom filters existed when the Bloomington born philosopher made the above statement.

Forbes magazine published an interesting article this month: Oracle’s Secret Sauce: Why Exadata Is Rocking the Tech Industry.

The article says: “The new approach is embodied in a technology strategy pioneered by Oracle and recently endorsed/followed by IBM (although IBM’s effort to date is rather modest): building richly integrated and fully optimized systems from the ground up, with hardware and software expressly created to work together to deliver maximum performance.”

And as you might guess from the image above, this time I am not only after the technical benefits and advantages of Exadata. I would like to clarify what they bring to business. And see how Oracle Exadata compares to IBM P-Series.

The FactPoint Group created a 30 page cost comparison report for business decision makers: Oracle Exadata Database Machine vs. IBM Power Systems.

In brief, the results of the report are:

• IBM 3 year TCO is 31% higher than Oracle.
• Exadata can be deployed more quickly and easily requiring 59% fewer man-hours than a traditional IBM Power Systems solution.
• Exadata requires 40% fewer sysadmin hours to maintain and operate annually, including quicker support calls because of less finger-pointing and faster service with a single vendor.
• Exadata delivers dramatically higher performance typically up to 12x improvement, as described by customers, over their prior solution.
• Will become even easier to operate over time as users become more proficient and organize around the benefits of integrated infrastructure.
• Supplies a highly available, highly scalable and robust solution that results in reserve capacity that make Exadata easier for IT to operate because IT administrators can manage proactively, not reactively.

Overall, Exadata operations and maintenance keep IT administrators from “living on the edge.” And it’s pre-engineered for long-term growth.

Check Kerry Osborne’s Oracle Blog for more details about the Exadata vs. IBM P-Series comparison.

I personally think that the benefits of Exadata are even much bigger granted the system is properly configured which I see is not always the case but as I said I will not comment on technical issues this time.

But after all, this is a DBA blog, so this part of the research might be of interest for most DBAs:

“For this emerging Database Machine Administrator (DMA) job category, IT employees are cross-trained to handle tasks currently undertaken by admin specialists in hardware, operating systems, network, applications or storage. IT managers who pursue this adaptive path likely will gain operational efficiencies for managing packaged solutions, although it may take several years as IT administrators are trained in new competencies.

The emergence of the DMA also may help restructure IT departments into more efficient operations, but the full benefits of this development cannot be fully realized until most older systems that demand a stove-piped IT organization are decommissioned and IT organizations adapt. At that time, IT operations managers may be able to reduce headcount. In time, packaged solutions should involve not only fewer workers but also fewer IT groups, which should reduce costs; in the meantime IT will be able to do more without adding headcount.”

That is very important! Let me quote here Paul Vallee, who in a recent discussion predicted that in the near future organizations will need few but very skillful DBAs, an opinion I 100% agree with!

“This change in job roles is not necessarily comfortable for everyone in IT because Exadata marginalizes various system administrators as it empowers the DBA: “The DBAs are doing more hardware tasks and diagnostics because most of the Exadata stuff is geared around database commands, not hardware commands or operating system commands. The gearheads have designed Exadata from the DBA’s perspective—when you look at the sys admin portion, it’s all written by a DBA, not by a Sys Admin,” lamented a System Administrator at a Business Services Co.

Other System Administrators have expressed similar sentiments as many of their traditional responsibilities shift towards the DBA—the source of the much of the operational savings we have identified.”

More on the DMA subject from Arup Nanda: Who Manages the Exadata Machine?

For all DBAs: here is an excellent book on Exadata: Expert Oracle Exadata, by Kerry Osborne, Randy Johnson and Tanel Põder.