Archive for the ‘Consolidation’ Category

Migrating databases with several database links

In Cloud, Consolidation, Databases, DBA, Oracle database, Replication on April 1, 2021 at 09:08

In a couple of recent database migration cases, one of the main questions raised, was how to figure out all outgoing and incoming database links as they have to be modified after the massive migrations.


Outgoing database links is simple: DBA_DB_LINKS describes all database links in the database. And this view has been part of the database (at least) since 7.3.4

The tricky part is how to find all incoming database links. At least before 12.2, where a new view called DBA_DB_LINK_SOURCES, shows the information of the source databases that opened database links to the local database.

So, how about the databases that are version 12.1 and below?

An Oracle community discussion on the MOS DBA forum gives several ideas:

Option 1: Bruno suggests to “start from the listener logfile; with some “awk/sed/vi” work it should be possible to extract the list of “origins” of the connections… -> From this list, identify the database servers -> Search database links on relevant databases on these servers”.

Might work but might be rather tedious work if there are 100s of different servers.

Option 2: Brian suggests “to query V$SESSION to see active sessions from the other database server. Hint…look at the MACHINE column to see if it matches the other database server name. Querying V$SESSION will only work if the link is open when you query it. As such, you may want to add an AFTER LOGON trigger which writes an audit entry to a table if the connection is from that database server.”

If you create a logon trigger to insert all incoming connection via database link note that in 11g, you can do that using value sys_context(‘USERENV’,’DBLINK_INFO’) which will give us all information. But check first Doc ID 2593966.1 as there is Bug 18974508: sys_context(‘userenv’, ‘dblink_info’) returns incomplete information.

But before 10g, there is no DBLINK_INFO, we we must use x$k2gte:

select username, osuser, status, sid, serial#, machine,
process, terminal, program from v$session
where saddr in (select k2gtdses from sys.x$k2gte);

The above is documented in Doc ID 332326.1: How to identify a session started by a remote distributed transaction? The fixed table x$k2gte contains 2PC Global Transaction Entry. The column k2gtdses in x$k2gte has the session state object and this can be mapped to the saddr column of v$session.

But as explained by Mark, the problem is that until the trigger finishes the session the remote db link session is not considered to exist and only upon successful session connection does Oracle then go and update related facts about the session.  Oracle does not guarantee read consistency on v$ views and the v$ views are based on x$ tables which are really program storage areas.  These areas get updated at various points in the logic.  It is possible that a logon trigger may not work in this specific case.  An alternate approach would be to run a process every N time that just snapshots what is out there and records new remote queries.  After all you really only need one capture per remote source whether you care about only database links or care about each client server.

One of the top database experts, Mariami Kupatadze, gave us a very elegant way of how to find remote sessions executing over a database link using x$k2gte, x$ktcxb, x$ksuse and v$session_wait in a single SQL statement.

A more detailed version called Identifying database link usage was written by John Hallas in 2015.

Long story short: for databases from 7.3 till 12.1 create a job capturing the distributed transactions based on the script given in Doc ID 104420.1 “Script to show Active Distributed Transactions”. And you can modify the scripts if not only the active remote transactions need to be captured. For 12.2 and after, just use the view  DBA_DB_LINK_SOURCES. 



13th Oracle Open World in San Francisco

In Cloud, Consolidation, DBA, OOW, Oracle Engineered Systems on August 20, 2016 at 19:57

“Why are our days numbered and not, say, lettered?” Woody Allen

Early tall-building designers, fearing a fire on the 13th floor, or fearing tenants’ superstitions about the rumor, decided to omit having a 13th floor listed on their elevator numbering. This practice became common, and eventually found its way into American mainstream culture and building design. If hotel floors are lettered, would you mind staying at floor M?

Next month, thousands of Oracle professionals will come to San Francisco where Oracle organize for the 13th time in a row, Oracle OpenWorld.

Having 13 presentation in honor of this jubilee is technically impossible but here are 3 ones that I will deliver next month in San Francisco:


1. My 13 DBA Mistakes in 13 Years [UGF1127]
Julian Dontcheff, Global Database Lead, Accenture
Sunday, Sep 18, 3:30 p.m. – 4:15 p.m. | Moscone South—102

Abstract: In this session learn about the biggest 13 mistakes in my DBA career. Lessons learned. Be careful when you press enter. Don’t do like I do, people will make fun of you.. It is sad and funny.

2. The Benefits and Simplicity of Oracle Cloud: Infrastructure as a Service [CON1126]
Julian Dontcheff, Global Database Lead, Accenture
Wednesday, Sep 21, 4:15 p.m. – 5:00 p.m. | Moscone South—309

Abstract: I will do a LIVE demo and try to create from scratch an Oracle compute instance in less than 13 minutes. Countdown stops after I am root in the virtual machine.

Infrastructure as a service (IaaS) is the fastest-growing area of public cloud computing. Oracle Cloud IaaS, with built-in security and high availability, offers elastic compute, networking, and storage to help any company quickly reach both value and productivity. This presentation covers the benefits of Oracle IaaS over other cloud providers, and shows how fast and easy it is to set up IaaS services in Oracle Cloud.

3. Lift and Shift onto Oracle Database Exadata Cloud Service Using Database Consolidation Advisor [CON1125]
Julian Dontcheff, Global Database Lead, Accenture
Thursday, Sep 22, 12:00 p.m. – 12:45 p.m. | Marriott Marquis—Salon 12

Abstract: Dedicated to OEM 13c newest feature: Database Consolidation Workbench. I will give 13 database consolidation strategy tips for DBAs.

Oracle Database Exadata Cloud Service provides service instances that contain a full Oracle Database hosted on Oracle Exadata inside Oracle Cloud. This presentation is about best practices on how to migrate and consolidate Oracle databases onto Oracle Database Exadata Cloud Service. It covers the three phases—planning, migration, and validation—of Oracle’s database consolidation workbench that helps in end-to-end consolidation of databases and enables consolidation of more databases on the same Oracle Exadata system, both on premises and in the public cloud.

If you are reading this post, I welcome you to join my talks at OpenWorld. Thank you in advance. Please join also other presentation from our team: The Accenture Enkitec Group. Here are the remaining talks:

Who Wins the Oracle Database in the Cloud Bake-Off? [CON5672]
Christopher Pasternak, Managing Director, Accenture
Robby Robertson, Sr. Manager, Accenture
Richard Miners, Senior Infrastructure Principal, Accenture
Tuesday, Sep 20, 12:15 p.m. – 1:00 p.m. | Moscone South—309

Maximizing Oracle Exadata Database Machine Reliability with Oracle EXAchk [CON1142]
Andy Colvin, Infrastructure Principal Director, Accenture
Thursday, Sep 22, 10:45 a.m. – 11:30 a.m. | Moscone South—302

Interfacing Raspberry Pi with Oracle Application Express [UGF5667]
Christoph Ruepprich, Programmer / Developer, Accenture Enkitec
Sunday, Sep 18, 2:15 p.m. – 3:00 p.m. | Moscone South—304

SQLd360: SQL Tuning Diagnostics Made Easy [UGF6168]
Mauro Pagano, Infrastructure Senior Principal, Accenture Enkitec Group
Sunday, Sep 18, 2:15 p.m. – 3:00 p.m. | Moscone South—302

Oracle GoldenGate and Baseball: Five Fundamentals Before Jumping to the Cloud [UGF5120]
Bobby Curtis, Infrastructure Principal, Accenture Enkitec Group
Sunday, Sep 18, 8:00 a.m. – 8:45 a.m. | Moscone West—3022

The Best Oracle Database 12c New Features for Developers and DBAs [UGF2028]
Alex Zaballa, Senior Oracle Database Administrator, Accenture Enkitec Group
Sunday, Sep 18, 8:00 a.m. – 8:45 a.m. | Moscone West—2010

Oracle Multitenant: Customer Panel [CON6563]
Randall Wilcox, Manager / Senior Manager, SAS Institute Inc.
Michael Sorrels, Sr. VP, Database Technologies, Regions Bank
Patrick Wheeler, Senior Director, Product Management, Oracle Database, Oracle
Andy Colvin, Infrastructure Principal Director, Accenture
Wednesday, Sep 21, 1:30 p.m. – 2:15 p.m. | Moscone South—301

Leveraging Oracle Database 12c Release 2 Multitenant Features [CON3075]
Kai Yu, Senior Principal Engineer, Oracle ACE Director, Dell, Inc.
Anuj Mohan, Technical Account Manager, Data Intensity
Andy Colvin, Infrastructure Principal Director, Accenture
James Czuprynski, Strategic Solutions Architect, OnX USA LLC
Deiby Gomez Gómez Robles, Oracle Database Consultant, Nuvola, S.A.
Thursday, Sep 22, 12:00 p.m. – 12:45 p.m. | Park Central—Concordia

And here is one where several AEG ACE Directors (including me) will present their point of views on new Oracle features one after each other in just few minutes:

EOUC Database ACES Share Their Favorite Database Things: Part I [UGF2630]
Debra Lilley, VP Certus Cloud Services, Certus Solutions Consulting Services Ltd
Ralf Koelling, Senior Consultant, CGI Deutschland Ltd. & Co. KG
David Kurtz, Consultant, Accenture Enkitec Group
Sunday, Sep 18, 1:00 p.m. – 1:45 p.m. | Moscone South—102

EOUC Database ACES Share Their Favorite Database Things: Part II [UGF2632]
Debra Lilley, VP Certus Cloud Services, Certus Solutions Consulting Services Ltd
Bjoern Rost, Principal Consultant, The Pythian Group Inc.
Carl Dudley, Database Administrator, Tradba
Sunday, Sep 18, 2:15 p.m. – 3:00 p.m. | Moscone South—102


P.S. I wonder how many sessions will be delivered altogether by the Enkitec group?

What’s New for Oracle Compute Cloud Service (IaaS)

In Cloud, Consolidation, DBA, IaaS on May 1, 2016 at 11:15

Behind every cloud is another cloud.” – Judy Garland

Whenever you find yourself on the side of the majority, it is time to pause and reflect.” – Mark Twain

Oracle Cloud Infrastructure allows large businesses and corporations to run their workloads, replicate their networks, and back up their data and databases in the cloud. And I would say in a much easy and efficient way than any other provider!

Oracle provides a free software appliance for accessing cloud storage on-premise. The Oracle Storage Cloud Software Appliance is offered free of charge. You do not get this from Amazon. And from Azure, you do not get as much memory on a VM for a core as you get from Oracle. In addition to the hourly metered service, Oracle also provides a non-metered compute capacity with a monthly subscription so that you can provision resources up to twice the subscribed capacity. This is a way to control the budget through a predictable monthly fee rather than the less controllable pure pay-as-you-go model.

Sing_In2 provided recently an excellent overview of the Oracle Cloud Infrastructure.

Pat Shuff’s Blog describes in detail the steps for creating an Oracle Linux service on the Oracle Compute Cloud. Glynn Foster’s Blog shows how to create an Oracle Solaris VMs in the Oracle Cloud Compute Service.

Creating an Oracle Compute Service took me (the first time) less than 10 minutes. Accessing it was an immediate process. This is simple, fast, easy and most of all I had no issues whatsoever. OK, I did not find lshw but I installed it in a minute:

yum -y install lshw*
Dependency Updated:
  dbus-libs.x86_64 1:1.2.24-8.0.1.el6_6


VPN for Engineered Systems: if you need a VPN between Oracle and your own infrastructure, then go to the My Oracle Support Note 2056914.1 and follow its instructions.


Creating an Oracle Storage Volume takes about one minute! Even less, if you have done it few times.


[opc@f24074 ~]$ df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/xvda2      9.4G  1.9G  7.0G  22% /
tmpfs           7.4G     0  7.4G   0% /dev/shm
/dev/xvda1      479M   81M  369M  18% /boot

Note: before connecting to the Oracle VM from any client, remember to add the IP address(es) to the Security IP list and then update the security rules (add a new one).

Few useful links:

Oracle Infrastructure as a Service (IaaS) Training Content
Using Oracle Compute Cloud Service (IaaS)
Accessing an Oracle Linux Instance Using SSH
Frequently Asked Questions for Oracle Compute Cloud Service
Troubleshooting Oracle Compute Cloud Service
Best Practices for Using Oracle Compute Cloud Service
Siebel CRM in Oracle Public Cloud IAAS
Compute Cloud Pricing / Storage Cloud Pricing / Network Cloud Service Pricing
Oracle Cloud Services Delivered in Your Data Center / Cloud Machine Documentation

Oracle Cloud Machine Operations: Roles and Responsibilities:


What’s New for Oracle Compute Cloud Service (IaaS):

– Both metered and non-metered options of Oracle Compute Cloud Service are now generally available.
– You can no longer subscribe for 50 or 100 OCPU configurations. Instead, you can specify the required number of 1 OCPU subscriptions.
– If you have a non-metered subscription, you can now provision resources up to twice the subscribed capacity. The additional usage will be charged per hour and billed monthly.
– Oracle provides images for Microsoft Windows Server 2012 R2 Standard Edition.
– Oracle provides images for Oracle Solaris 11.3.
– You can clone storage volumes by taking a snapshot of a storage volume and using it to create new storage volumes.
– You can clone an instance by taking a snapshot and using the resulting image to launch new instances.
– You can increase the size of a storage volume, even when it’s attached to an instance.
– You can now find the public and private IP addresses of each instance on the Instances page. Earlier, this information was displayed only on the instance details page of each instance.
– The CLI tool for uploading custom images to Oracle Storage Cloud Service has been updated to support various operating systems. The tool has also been renamed to uploadcli. Earlier it was called upload-img.

For more details, check What’s New for Oracle Compute Cloud Service (IaaS).


And finally, do you wonder what is the underlying hardware?

[root@f24074 ~]# lshw -short
H/W path    Device  Class      Description
                    system     HVM domU
/0                  bus        Motherboard
/0/0                memory     96KiB BIOS
/0/1                processor  Intel(R) Xeon(R) CPU E5-2690 v2 @ 3.00GHz
/0/2                processor  CPU
/0/3                processor  CPU
/0/4                processor  CPU
/0/5                memory     System Memory
/0/5/0              memory     15GiB DIMM RAM
/0/5/1              memory     15GiB DIMM RAM
/0/6                memory     96KiB BIOS
/0/7                processor  CPU
/0/8                processor  CPU
/0/9                processor  CPU
/0/a                processor  CPU
/0/b                memory     System Memory
/0/c                memory
/0/d                memory
/0/100              bridge     440FX - 82441FX PMC [Natoma]
/0/100/1            bridge     82371SB PIIX3 ISA [Natoma/Triton II]
/0/100/1.1          storage    82371SB PIIX3 IDE [Natoma/Triton II]
/0/100/1.3          bridge     82371AB/EB/MB PIIX4 ACPI
/0/100/2            display    GD 5446
/0/100/3            generic    Xen Platform Device
/1          eth0    network    Ethernet interface

Oracle Database Cloud Service vs Amazon Relational Database Service

In Cloud, Consolidation, DBA, Oracle database on February 28, 2016 at 15:00

How to compare Oracle’s Database Public Cloud with Amazon’s Relational Database Service (RDS) for enterprise usage? Let us have a look.

Oracle’s Database has 4 editions: Personal Edition, Express Edition (XE): free of charge and used by very small businesses and students, Standard Edition (SE): light version of Enterprise Edition and purpose designed to lack most features needed for running production grade workloads and Enterprise Edition (EE): provides the performance, availability, scalability, and security required for mission-critical applications.

In the comparison in this post, we will evaluate Oracle and Amazon in relation to the Enterprise Edition of Oracle’s database.


Oracle Public Database Cloud consists of 4 DB Cloud offerings: DBaaS, Virtual Image, Schema Service and Exadata Service. Here are few characterizations:

– Oracle supports Exadata, RAC & all DB options
– Simple pricing structure with published costs representing actual costs (unlimited I/Os, etc.)
– Hourly, Monthly & Annual pricing options
– Lowest cloud storage pricing across all major IaaS vendors

Amazon RDS for Oracle Database supports two different licensing models – “License Included” and “Bring-Your-Own-License (BYOL)”. In the “License Included” service model, you do not need separately purchased Oracle licenses. Here are few characterizations:

Enterprise Edition supports only db.r3.large and larger instance classes, up to db.r3.8xlarge
– Need to choose between Single-AZ (= Availability Zone) Deployment and Multi-AZ Deployment
– For Multi-AZ Deployment, Amazon RDS will automatically provision and manage a “standby” replica in a different Availability Zone (prior to failover you cannot directly access the standby, and it cannot be used to serve read traffic)
– Only 2 instance types support 10 Gigabit network: db.m4.10xlarge and db.r3.8xlarge
– Amazon RDS for Oracle is an exciting option for small to medium-sized clients and includes Oracle Database Standard Edition in it’s pricing
– Several application with limited requirements might find Amazon RDS to be a suitable platform for hosting a database
– As the enterprise requirements and resulting degree of complexity of the database solution increase, RDS is gradually ruled out as an option

So, here is high level comparison:


– Oracle’s price includes the EE license with all options
– Amazon AWS is BYOL for EE
– Prices above are based on the EU (Frankfurt) region
– Amazon’s Oracle database hour prices vary from $0.290 to $4.555 for Single AZ Deplyoments and from $0.575 to $9.105 for Multi-AZ Deployments
– Oracle’s database hour prices vary from $0.672 to $8.569


Oracle Archive Storage Pricing
Amazon Glacier Storage Pricing
Amazon Database Pricing
Oracle Database Pricing
Amazon Options for Oracle Database Engine
Oracle on Amazon RDS Support & Limitations

So, Amazon RDS is not an option if you need any of the following: Real Application Clusters (RAC), Real Application Testing, Data Guard / Active Data Guard, Oracle Enterprise Manager Grid Control, Automated Storage Management, Database Vault, Streams, Java Support, Locator, Oracle Label Security, Spatial, Oracle XML DB Protocol Server or Network access utilities such as utl_http, utl_tcp, utl_smtp, and utl_mail.

Interesting articles related to this topic:

1. Burning question for Oracle: What’s your response to Amazon? by Barb Darrow
2. Shootout: Oracle DB Cloud vs. Amazon RDS by Jan Navratil
3. The Oracle Database Cloud Service vs Oracle on Amazon RDS by Ranko Mosic
4. A Most Simple Cloud: Is Amazon RDS for Oracle Right for you? by by Jeremiah Wilton
5. Oracle RAC and AWS: A Hybrid Cloud Solution by Lindsay Van Thoen
6. How Much Does It Cost to Run Relational Database (RDS) Options on AWS by Yoav Mor
7. Oracle vs. Amazon: The Cloud Wars by Chris Lawless

Reading Data in Oracle Database 12c

In Cloud, Consolidation, Database options, DBA, Security and auditing, SQL on December 1, 2014 at 18:02

1. For DBAs and Developers, the words READ and SELECT have been for years somehow synonyms. In 12c, is there now any difference?

2. Before pluggable databases, selecting data from the SALES table for instance meant selecting data from a table called SALES in a certain SCHEMA within the database. How about if a table called SALES belongs to several pluggable databases under the same schema name?

The aim of this blog post is to shed some light on these new concepts.


1. New READ privilege.

Until Oracle the SELECT object privilege allowed users to perform the following two operations in addition to just reading data from the SALES table:


These 2 commands enabled the users to lock the rows of the SALES table.

The READ object privilege does not provide these additional privileges. For better security, grant users the READ object privilege if you want to restrict them to performing queries only.

In addition to the READ object privilege, you can grant users the READ ANY TABLE privilege to enable them to query any table in the database.

When a user who has been granted the READ object privilege wants to perform a query, the user still must use the SELECT statement. There is no accompanying READ SQL statement for the READ object privilege.

The GRANT ALL PRIVILEGES TO user SQL statement includes the READ ANY TABLE system privilege. The GRANT ALL PRIVILEGES ON object TO user statement includes the READ object privilege.

If you want the user only to be able to query tables, views, materialized views, or synonyms, then grant the READ object privilege. For example:



2. Querying a table owned by a common user across all PDBs.

Consider the following scenario:

– The container database has several pluggable databases, i.e., it has a separate PDB for each different office location of the company.
– Each PDB has a SALES table that tracks the sales of the office, i.e., the SALES table in each PDB contains different sales information.
– The root container also has an empty SALES table.
– The SALES table in each container is owned by the same common user.

To run a query that returns all of the sales across the company connect to each PDB as a common user, and create a view with the following statement:


The common user that owns the view must be the same common user that owns the sales table in the root. After you run this statement in each PDB, the common user has a view named sales in each PDB.

With the root as the current container and the common user as the current user, run the following query with the CONTAINERS clause to return all of the sales in the sales table in all PDBs:


You can also query the view in specific containers. For example, the following SQL statement queries the view in the containers with a CON_ID of 3 and 4:


3. Delegate.

Something else: staring, when granting a role to a user, you can specify the WITH DELEGATE OPTION clause. Then the grantee can do the following two things:

A) Grant the role to a program unit in the grantee’s schema
B) Revoke the role from a program unit in the grantee’s schema


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


All databases are equal, but some databases are more equal than others

In Cloud, Consolidation, DBA on December 28, 2013 at 12:45

Building database services based on Exadata, Oracle Enterprise Manager and Oracle Database 12c is a powerful combination, especially if implemented properly and by skillful DBAs.

A recent article of Forbes Magazine explains why Database as a Service (DBaaS) will be the Breakaway Technology of 2014. The 451 research report estimated that DBaaS providers generated revenue of $150 million in 2012, but that revenue will grow at a compound annual growth rate of 86% to reach $1.8 billion by 2016.

By paraphrasing “Animal Farm” author George Orwell, Oracle’s Alexander Wolfe stated that some DBaaS offerings provide a lot more services than others. I would like to clarify why this is indeed so true.


What is DBaaS?

Kellyn Pot’vin from Enkitec says that Database as a Service (DBaaS) is an architectural and operational approach enabling DBAs to deliver database functionality as a service to internal and/or external customers.

According to, Database-as-a-Service (DBaaS) is a service that is managed by a cloud operator (public or private) that supports applications, without the application team assuming responsibility for traditional database administration functions.

Technopedia says that Database-as-a-service (DbaaS) is a cloud computing service model that provides users with some form of access to a database without the need for setting up physical hardware, installing software or configuring for performance.

Kellyn’s definition is the way I understand it based on my experience at least. The other two definitions makes me feel like one can also define easily Compression-as-a-service or Temporary-tablespace-as-a-service.

Regardless of how the essence of DbaaS is set with words, it is all about simplifying, enhancing and automating database provisioning, monitoring, administration, security and operational efficiency. In short, centralizing and harmonizing the database administration.

Although I wrote above simplifying, still it means more like reducing the complexity and having a simplification plan than ending with an elementary and transparent database environment. Though I have seen that this is doable but mostly in power point presentations.

Now, the reference architecture of “Databases as a Service” can be found here. But most of the white papers and reference notes that one can find on the web are made for decision makers, not for implementers. The really though is that DbaaS is DBA driven. Regardless of how cunning plan on how to implement DbaaS a company has, it is still very much up to the ones who implement the service: their skills, practical knowledge and experience. So, here is the DBA cookbook.


A very experienced DBA team can offer a much more services than another. Highly experienced Database Architects can enable businesses to deploy new databases quickly, securely, and cheaply. What is part of the service varies and business is often not even aware of what to request.

The long list of what can be included in the service and how the implementation is handled is not part of this blog post but all database experts know that if implemented properly, DbaaS will lead to:

Harmonized environment:
– Less time needed for database management
– Easier monitoring
– Less unexpected problems
– Stronger security
Less cost:
– Less databases licenses
– Less storage needed
– Less memory and CPU needed
Better performance
– Standard parameterization and settings
– Regular common reorganization

So, why do some DBaaS offerings provide a lot more services than others? The Forbes article sums up the flexibility inherent in the DBaaS model by apply the “Burger King” analogy: DBaaS lets you have it your way. And it comes up with its pros and cons. In order to always have the upper hand, I always try to follow some simple principles:

– not more than 2 database version including the patchset levels
– databases should not run on more than 2 different operating systems
– at least 2 environments for every database: never end up with just productions
– standby database/replica/ADG for every mission critical database
– 2 OEM environments: either PROD and non-PROD or primary and secondary data center
– 2 DBAs to verify every important DB change
– at minimum 2 RMAN catalogs: one for PROD and one for non-PROD
– do not mix 2 databases based on different COTS software (like SAP and Siebel for example)

In a DbaaS, all databases are equal, however for business some databases are often more important than the others. DBAs are aware of this and they know how to handle this nontrivial complexity.

How to create a CDB in Oracle 12.1.0

In Consolidation, DBA, Oracle database on June 30, 2013 at 12:27

The Database Configuration Assistant (DBCA) and the “CREATE DATABASE” statement are the 2 possible ways to create a container database in 12c.

In Oracle 12.1.0, ENABLE_PLUGGABLE_DATABASE is a bootstrap init.ora parameter used to create a CDB, and it enables a database in NOMOUNT startup mode to know that the database should be created as a CDB. One CDB, can have up to 252 pluggable databases. Enough for a start I would say.

The parameter ENABLE_PLUGGABLE_DATABASE must be set in init.ora before creating a CDB. The default is FALSE but in future Oracle releases all databases will probably be created as container databases.


In a CDB, the DB_NAME parameter specifies the name of the root. You may want to set the SID to the name of the root, although on the screenshot above you see that they are different. The maximum number of characters for this name is 30.

You can create the database manually using the create database command as follows:

    FILE_NAME_CONVERT = ('/oradata/database/newcdb/', 
    DATAFILE '/oradata/database/pdbseed/users01.dbf'

The USER_DATA tablespace created above is for storing user data and database options such as Oracle XML DB (a must install in 12c). PDBs created using the seed include this tablespace and its data file. The tablespace USER_DATA is not used by the root.

Afterwards, run the following script as sysdba:


As several people have already noticed, the script is missing from the admin directory. There is an open bug about this: Bug 17033183 : MISSING FILE CATCDB.SQL IN $ORACLE_HOME/RDBMS/ADMIN.

It installs all of the components required by a CDB: PL/SQL packages in the root, etc.

Note that every PDB has its own SYSTEM and SYSAUX tablespace that differs from those of the root.

For a CDB, you can configure Oracle Enterprise Manager Database Express (EM Express) for the root and for each PDB by setting the HTTP or HTTPS port. You must use a different port for every container in a CDB.


Here is what I ran in order to get to the image shown above:


I have been using EM DB Express for more than a year, starting with the first beta version of 12c. If you cannot get the GUI running, then just restart the database after executing the above command.

Database_Express 12c_9

After you create a CDB, it consists of really not much. Just the root and the seed! The root contains almost no user data. The user data resides in the PDBs.

Therefore, after creating a CDB, one of the first tasks is to add all the PDBs. Database consolidation and simplification. You name it!

There are 4 methods for adding PDBs to a CDB:

• Create new PDB from PDB$SEED pluggable database
• Plug in a non-CDB
• Clone a PDB from another PDB into the same or another CDB
• Plug an unplugged PDB into another CDB


If you look on the left, under memory settings, you will see a new init.ora parameter: PGA_AGGREGATE_LIMIT. It is a real hard limit on PGA memory usage! If the value is reached, then Oracle aborts or terminates the sessions or processes that are consuming the most untunable PGA memory in the following order:

1. Calls for sessions that are consuming the most untunable PGA memory are aborted.

2. If PGA memory usage is still over the PGA_AGGREGATE_LIMIT, then the sessions and processes that are consuming the most untunable PGA memory are terminated.

P.S. One of my 4 sessions this September at Oracle OpenWorld will be “DBA Best Practices for Performance Tuning in a Pluggable World”.

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.

Curb Your Enthusiasm: Database Virualization

In Cloud, Consolidation, DBA, Oracle database on September 29, 2012 at 00:38

Larry David says: “You write about what you know”.

RAC is one of the most controversial topics among database experts.

An extremely interesting article, called Database Virtualisation: The End of Oracle RAC? was published this month (September 2012) on

I recommend it to every Database Architect, DBA and Developer! Along with the comments after it. Plus its links to related papers and blogs.

Another interesting post on the RAC issue, entitled To RAC or not to RAC and its sequel To RAC or not to RAC (reprise part 2) is worth reading as well.

To make it simple, let me quote

“The biggest disadvantage is that you are adding more complexity to your database architecture. With more complexity comes a higher cost in maintaining and administering the database along with a higher chance that something will go wrong.

The second biggest disadvantage is the cost associated with RAC. Oracle is touting RAC on Linux as a way to acheive cost savings over large Unix servers. With RAC, the costs shift from hardware to software as you need additional Oracle license fees. The big question is will this shifting of costs result in any cost savings. In some cases, yes, and in other cases, no.”

Which would be the third big disadvantage of RAC? I say the bugs! Or let me put it more mildly: RAC just develops random features. And hunting errors in RAC is complex, right? On top of ORA-600, we now have even ORA-700.

Let me offer you some quotes from the RAC debate on

  • “Then there are the younger DBAs looking to gain more experience, who may say that RAC is a great thing. Secretly that might not necessarily be true but they want the experience.”
  • “We also see that there are “no application changes necessary”. I have serious doubts about that last statement, as it appears to contradict evidence from countless independent Oracle experts.”
  • “Complexity is the enemy of high availability – and RAC, no matter how you look at it, adds complexity over a single-instance implementation of Oracle.”
  • “At no time do I ever remember visiting a customer who had implemented the various Transparent Application Failover (TAF) policies and Fast Application Notification (FAN) mechanisms.”

  • So, is Database Virtualization the answer?

    I think that database virtualization is a concept that has been misunderstood and most of all wrongly defined by many. According to the Oxford Dictionarries, in Computing, virtual means something which is not physically existing as such but made by software to appear to do so.

    Decide for yourself what is then a virtual database. At least, it is not a database built in a virtual server! I did discuss that at the Oracle ACE Director Product Briefing at Oracle headquarters this week with world’s top database experts and what can I say: the topic is highly controversial.

    Let us go to the Oracle Database Documentation Library and search for virtualization. Here is the result:

    And you will get the same result for virtualisation (with “s”).

    So, what do we get? Not much.But let’s continue reading:

  • Database Virtualization Part 2 – Flash Makes The Difference
  • The Do’s And Don’ts Of Virtualizing Database Servers
  • Disadvantages of Virtualization, What’s Your Opinion?
  • Virtualization Best Practices
  • Let us wait for next Oracle’s database release and see what it will offer us.