Oracle RAC and Policy Managed Databases


Some weeks ago I’ve commented a good post of Martin Bach (@MartinDBA on Twitter, make sure to follow him!)

What I’ve realized by  is that Policy Managed Databases are not widely used and there is a lot misunderstanding on how it works and some concerns about implementing it in production.

My current employer Trivadis (@Trivadis, make sure to call us if your database needs a health check :-)) use PMDs as best practice, so it’s worth to spend some words on it. Isn’t it?

 Why Policy Managed Databases?

PMDs are an efficient way to manage and consolidate several databases and services with the least effort. They rely on Server Pools. Server pools are used to partition physically a big cluster into smaller groups of servers (Server Pool). Each pool have three main properties:

  • A minumim number of servers required to compose the group
  • A maximum number of servers
  • A priority that make a server pool more important than others

If the cluster loses a server, the following rules apply:

  • If a pool has less than min servers, a server is moved from a pool that has more than min servers, starting with the one with lowest priority.
  • If a pool has less than min servers and no other pools have more than min servers, the server is moved from the server with the lowest priority.
  • Poolss with higher priority may give servers to pools with lower priority if the min server property is honored.

This means that if a serverpool has the greatest priority, all other server pools can be reduced to satisfy the number of min servers.

Generally speaking, when creating a policy managed database (can be existent off course!) it is assigned to a server pool rather than a single server. The pool is seen as an abstract resource where you can put workload on.


If you read the definition of Cloud Computing given by the NIST ( you’ll find something similar:

Cloud computing is a model for enabling ubiquitous, convenient, on-demand network access to a shared
pool of configurable computing resources (e.g., networks, servers, storage, applications, and services) that
can be rapidly provisioned and released with minimal management effort or service provider interaction


There are some major benefits in using policy managed databases (that’s my solely opinion):

  1. PMD instances are created/removed automatically. This means that you can add and remove nodes nodes to/from the server pools or the whole cluster, the underlying databases will be expanded or shrinked following the new topology.
  2. Server Pools (that are the base for PMDs) allow to give different priorities to different groups of servers. This means that if correctly configured, you can loose several physical nodes without impacting your most critical applications and without reconfiguring the instances.
  3. PMD are the base for Quality of Service management, a 11gR2 feature that does resource management cluster-wide to achieve predictable performances on critical applications/transactions. QOS is a really advanced topic so I warn you: do not use it without appropriate knowledge. Again, Trivadis has deep knowledge on it so you may want to contact us for a consulting service (and why not, perhaps I’ll try to blog about it in the future).
  4. RAC One Node databases (RONDs?) can work beside PMDs to avoid instance proliferation for non critical applications.
  5. Oracle is pushing it to achieve maximum flexibility for the Cloud, so it’s a trendy technology that’s cool to implement!
  6. I’ll find some other reasons, for sure! 🙂

What changes in real-life DB administration?

Well, the concept of having a relation Server -> Instance disappears, so at the very beginning you’ll have to be prepared to something dynamic (but once configured, things don’t change often).

As Martin pointed out in his blog, you’ll need to configure server pools and think about pools of resources rather than individual configuration items.

The spfile doesn’t contain any information related to specific instances, so the parameters must be database-wide.

The oratab will contain only the dbname, not the instance name, and the dbname is present in oratab disregarding if the server belongs to a serverpool or another.

Your scripts should take care of this.

Also, when connecting to your database, you should rely on services and access your database remotely rather than trying to figure out where the instances are running. But if you really need it you can get it:

An approach for the crontab: every DBA soon or late will need to schedule tasks within the crond. Since the RAC have multiple nodes, you don’t want to run the same script many times but rather choose which node will execute it.

My personal approach (every DBA has his personal preference) is to check the instance with cardinality 1 and match it with the current node. e.g.:

In the example, TST_1 is running on node1, so the first evaluation returns TRUE. The second evaluation is done after the node2, so it returns FALSE.

This trick can be used to have an identical crontab on every server and choose at the runtime if the local server is the preferred to run tasks for the specified database.


A proof of concept with Policy Managed Databases

My good colleague Jacques Kostic has given me the access to a enterprise-grade private lab so I can show you some “live operations”.

Let’s start with the actual topology: it’s an 8-node stretched RAC with ASM diskgroups with failgroups on the remote site.


This should be enough to show you some capabilities of server pools.

The Generic and Free server pools

After a clean installation, you’ll end up with two default server pools:



The Generic one will contain all non-PMDs (if you use only PMDs it will be empty). The Free one will own servers that are “spare”, when all server pools have reached the maximum size thus they’re not requiring more servers.

 New server pools

Actually the cluster I’m working on has two serverpools already defined (PMU and TST):


(the node assignment in the graphic is not relevant here).

They have been created with a command like this one:

“srvctl -h ” is a good starting point to have a quick reference of the syntax.

You can check the status  with:

and the configuration:


Modifying the configuration of serverpools

In this scenario, PMU is too big. The sum of minumum nodes is 2+5=7 nodes, so I have only one server that can be used for another server pool without falling below the minimum number of nodes.

I want to make some room to make another server pool composed of two or three nodes, so I reduce the serverpool PMU:

Notice that PMU maxsize is still 6, so I don’t have free servers yet.

So, if I try to create another serverpool I’m warned that some resources can be taken offline:

The clusterware proposes to stop 2 instances from the db pmu on the serverpool PMU because it can reduce from 6 to 3, but I have to confirm the operation with the flag -f.

Modifying the serverpool layout can take time if resources have to be started/stopped.

My new serverpool is finally composed by two nodes only, because I’ve set an importance of 1 (PMU wins as it has an importance of 3).

Inviting RAC One Node databases to the party

Now that I have some room on my new serverpool, I can start creating new databases.

With PMD I can add two types of databases: RAC or RACONDENODE. Depending on the choice, I’ll have a database running on ALL NODES OF THE SERVER POOL or on ONE NODE ONLY. This is a kind of limitation in my opinion, hope Oracle will improve it in the near future: would be great to specify the cardinality also at database level.

Creating a RAC One DB is as simple as selecting two radio box during in the dbca “standard” procedure:


The Server Pool can be created or you can specify an existent one (as in this lab):



I’ve created two new RAC One Node databases:

  • DB LUDO (service PRISM :-))
  • DB VICO (service CHEERS)

I’ve ended up with something like this:

That can be represented with this picture:



RAC One Node databases can be managed as always with online relocation (it’s still called O-Motion?)

Losing the nodes

With this situation, what happens if I loose (stop) one node?

The node was belonging to the pool LUDO, however I have this situation right after:

A server has been taken from the pol PMU and given to the pool LUDO. This is because PMU was having one more server than his minimum server requirement.


Now I can loose one node at time, I’ll have the following situation:

  • 1 node lost: PMU 3, TST 2, LUDO 2
  • 2 nodes lost: PMU 3, TST 2, LUDO 1 (as PMU is already on min and has higher priority, LUDO is penalized because has the lowest priority)
  • 3 nodes lost:PMU 3, TST 2, LUDO 0 (as LUDO has the lowest priority)
  • 4 nodes lost: PMU 3, TST 1, LUDO 0
  • 5 nodes lost: PMU 3, TST 0, LUDO 0

So, my hyper-super-critical application will still have three nodes to have plenty of resources to run even with a multiple physical failure, as it is the server pool with the highest priority and a minimum required server number of 3.

 What I would ask to Santa if I’ll be on the Nice List (ad if Santa works at Redwood Shores)

Dear Santa, I would like:

  • To create databases with node cardinality, to have for example 2 instances in a 3 nodes server pool
  • Server Pools that are aware of the physical location when I use stretched clusters, so I could end up always with “at least one active instance per site”.

Think about it 😉


Oracle Database 12c: Multitenant, Services and Standard Edition RAC

The installation process of a typical Standard Edition RAC does not differ from the Enterprise Edition. To achieve a successful installation refer to the nice quick guide made by Yury Velikanov and change accordingly the Edition when installing the DB software.

Standard Edition and Feature availability

The first thing that impressed me, is that you’re still able to choose to enable pluggable databases in DBCA even if Multitenant option is not available for the SE.

So I decided to create a container database CDB01 using template files, so all options of EE are normally cabled into the new DB. The Pluggable Database name is PDB01.

As you can see,  the initial banner contains “Real Application Clusters and Automatic Storage Management options“.

Multitenant option is not avilable. How SE reacts to its usage?

First, on the ROOT db, dba_feature_usage_statistics is empty.

This is interesting, because all features are in (remember it’s created from the generic template) , so the feature check is moved from the ROOT to the pluggable databases.

On the local PDB I have:

Having ONE PDB is not triggering the usage of Multitenant (as I was expecting).

How if I try to create a new pluggable database?

A-AH!! Correctly, I can have a maximum of ONE pluggable database in my container.

This allows however:

  • Smooth migration from SE to a Multitenant Architecture
  • Quick upgrade from one release to another

To be sure that I can plug/unplug, I’ve tried it:

Other features of Enterprise off course don’t work

Create a Service on the RAC Standard Edition (just to check if it works)

I’ve just followed the steps to do it on an EE. Keep in mind that I’m using admin managed DB (something will come about policy managed DBs, stay tuned).

As you can see it works pretty well. Comparing to 11g you have to specify the -pdb parameter:

Then I can access my DB (and preferred instance) using the service_name I specified.


Let me know what do you think about SE RAC on 12c. It is valuable for you?

I’m also on twitter: @ludovicocaldara



Oracle Database 12c: sequence.nextval as default and identity columns

Finally! I can count how many times I’ve seen questions like this one.

The new Oracle 12c now allows to define a table with the sequence.nextval directly in the in-line column definition:


But Oracle has fixed this twice, in the new release it’s possible to use identity columns as well, avoiding the necessity to create explicitly a new sequence:

I’ve said “explicitly” because actually a sequence is created with a system-generated name, so you’ll still need to deal with sequences.




Oracle Database 12c: Multithreaded Execution (or how make processes decrease) (cc) Too many background processes

Oracle instances on Unix/Linux servers have been composed historically by separated server processes to allow the database to be multi-user, in opposite with Windows that has always been multithread (Oracle 7 on MS-DOS was a single-user process, but this is prehistory…). The background processes number has increased to support all the new features of Oracle, up to this new Oracle 12c release. On a simple database installation you’ll be surprised to have this output from a ps command (38 processes):

If you have consolidated many databases without the pluggable database feature, you’ll end up to have several hundreds of processes even without users connected. But Oracle 12c now introduce the possibility to start an instance using multithreading instead of the traditional processes. This could lead to some optimizations due to the shared process memory, and reduced context switches overhead, I presume (need to test it).


Enabling the Multithreaded Execution

By default this feature is not enabled, so you have to set it explicitly:

And in parallel, you’ll need to add this line to the listener.ora:

After a restart, the instance will show only a bunch of processes:

The remaining processes

So we have the Process Monitor (pmon), the Process Spawner (psp0), the Virtual Keeper of Time (vktm), the Database Writer (dbw0) and two new multithreaded processes (u004) and (u005). “U” can stand for User or Unified?


Where can I find the information on the other processes?

They still exist in the v$process view, thus leading to some confusion when talking about Oracle Processes with your sysadmins… The new EXECUTION_TYPE column show if the Oracle Process is executed as a thread or as an OS process, and the SPID let us know which process actually executes it.


What about the User processes?

Well, I’ve spawned 200 user processes with sqlplus, and got 200 threads:

On the OS side, I’ve registered an additional process to distribute the load of the new user processes. Damn, I start to being confusional using the term “process” o_O

On the session side however, all the user processes are DEDICATED.


 A huge side effect

By using the multithreaded execution, the operating system authentication doesn’t work.

Unless Oracle will review it’s authentication mechanism in a future patchset, you’ll need to rely on the password file and use the password to connect to the instance as sysdba, even locally.

What about performance?

In theory, threads should be faster and with a lower footprint:

The main benefit of threads (as compared to multiple processes) is that the context switches are much cheaper than those required to change current processes. Sun reports that a fork() takes 30 times as long as an unbound thread creation and 5 times as long as a boundthread creation.


In some operating systems running on some hardware, switching between threads belonging to the same process is much faster than switching to a thread from different process (because it requires more complicated process context switch).

In practice, I’ll do some tests and let you know! 🙂


What about the good old OS kill command to terminate processes?

Good question! Currently I have not found any references to an orakill command (that exists on Windows). Hope it will arrive soon!



Oracle Database 12c finally out!! First impressions

After a long, long wait, Oracle finally announced the availability of his new generation database. And looking at the new features, I think it will take several months before I’ll learn them all. The impressive number of changes brings me back to the release 10gR1, and I’m not surprised that Oracle has waited so long, I still bet that we’ll find a huge amount of bugs in the first release. We need for sure to wait a first Patchset, as always, before going production.

Does ‘c’ stand for cloud?

While Oracle has developed this release with the cloud  in mind, the first word that comes out of my mind is “consolidation”. The new claimed feature  Pluggable Database (aka Oracle Multitenancy) will be the dream of every datacenter manager along with CloneDB (well, it was somehow already available on and ASM Thin_provisioned diskgroups.

But yes, it’s definitely the best for clouds

Other features like Flex ASM, Flex Cluster, several new security features, crossplatform backups… let imagine how deeply we can work to make private, multi-tenant clouds.

First steps, what changes with a typical installation


The process for a traditional standalone DB+ASM installation is the same as the old 11gR2: You’ll need to install the Grid Infrastructure first (and then take advantage of the Oracle Restart feature) and subsequently the Database installation.

The installation documentation is complete as always and is getting quite huge as the Grid Infrastructure capabilities increment.

To meet most installation prerequisites, Oracle has prepared again an RPM that does the dirty work:


Oracle suggests to use Ksplice and also explicitly recommends to use the deadline I/O scheduler (it has been longtime a best practice but I can’t remember it was documented officially).

The splash screen has become more “red” giving a colorful experience on the installation process. 😉

Once the GI is installed, the Database installation asks for many new OS groups: OSBACKUPDBA, OSDGDBA, OSKMDBA. This give you more possibilities to split administration duties, not specifying them will lead to the “old behavior”.


You can decide to use an ACFS filesystem for both the installation AND the database files (with some exceptions, e.g. Windows servers). So, you can take advantage of the snapshot features of ACFS for your data, provided that the performance is acceptable (I’ll try to test and blog more about this). You can use the feature Copy-On-Write to provide writable snapshot copies, directly embedding a special syntax inside the “create pluggable database” command. Unfortunately, Oracle has decided to deliver pluggable databases as an extra-cost option. :-/

The database creation with DBCA is even easier, you have an option for a very default installation, you can guess it uses templates with all options installed by default.

But the Hot topic is that you can create it as a “Container Database”. This is done by appending the keywords “enable pluggable database;” at the end of the create database command. The process will then put all the required bricks (creation of the pdb$seed database and so on), I’ll cover the topic in separate posts cause it’s the really biggest new feature.


You can still use advanced mode to have the “old style” database creation, where you can customize your database.

If you try to create only the scripts and run them manually (that’s my habit), you’ll notice that SQL scripts are not run directly within the opened SQL*Plus session, but they’re run from a perl script that basically suppresses all the output to terminal, giving the impression of a cleaner installation. IMO it could be better only if everything runs fine.

Finally, I’ll get something familiar, but with a brand new release number! 🙂

Stay tuned, I’ll write soon about some really interesting features of the new Oracle Database 12c!



ORA-00600 and user identified by values ”

With rel. was possibile to do this:

With an ORA-00600 is raised.

Script that duplicates a database using a physical standby RAC as source

 It’s possibile to duplicate a database for testing purposes (it’s an example) using a standby database as source. This allows you to off-load the production environment.

This is a simple script that makes use of ASM and classic duplicate, although I guess it’s possible to use the standby DB for a duplicate from active database.
You can launch it everyday to align your test env at a point in time.

Mass datafile resizing

Recently I needed to extend many datafiles on a database with more than 500 tablespaces because a lot of tablespaces were reaching the critical threshold.
Autoextend was not an option due to a bug I encountered on 10gR2 RAC on ASM and AIX.

The solution was the following script: it generates statements to autoextend datafiles with usage over a defined threshold (the “80” in the where clause) to low down the percentage below another defined threshold (the “75” in the select clause).

Prior to extend it’s possible to show how much space is required to do this mass resizing:

Dog eat Dog… Oracle deletes itself by mistake!

While implementing the backup on a new DB inherited from a customer, I scheduled our standard backup “type disk” procedure through rman, on Windows.
The morning after I saw that the “delete obsolete” tried to delete ALL CURRENT DATAFILES!!

i criteri di conservazione RMAN verranno applicati al comando
i criteri di conservazione RMAN sono impostati su una ridondanza 1
canale allocato: ORA_DISK_1
canale ORA_DISK_1: sid=29 devtype=DISK
Eliminazione dei seguenti backup e copie obsoleti:
Tipo Chiave Ora fine Nome file/Handle
-------------------- ------ ------------------ --------------------
Set di backup 917 28-GIU-11
Set di backup 927 29-GIU-11
Backup piece 1005 29-GIU-11 H:\ORACLE\BACKUP\ORAPERSP\RMAN\SPFILEBCK_20110629
Copia file di dati 14 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\INDX01.DBF
Copia file di dati 16 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\TOOLS01.DBF
Copia file di dati 17 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\USERS01.DBF
Copia file di dati 18 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\DRSYS01.DBF
Copia file di dati 19 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\EXAMPLE01.DBF
Copia file di dati 20 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\ODM01.DBF
Copia file di dati 21 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\XDB01.DBF
Copia file di dati 22 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\CWMLITE01.DBF
Copia file di dati 23 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\TBLDATI01.ORA
Copia file di dati 26 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\SYSTEM01.DBF
Copia file di dati 27 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\UNDOTBS01.DBF
backup piece eliminata
backup piece eliminata
handle di backup piece=H:\ORACLE\BACKUP\ORAPERSP\RMAN\C-2220366420-20110628-02 recid=990 stamp=755031582
backup piece eliminata
handle di backup piece=H:\ORACLE\BACKUP\ORAPERSP\RMAN\C-2220366420-20110629-00 recid=1002 stamp=755130872
backup piece eliminata
handle di backup piece=H:\ORACLE\BACKUP\ORAPERSP\RMAN\CTL_20110629 recid=1004 stamp=755130883
backup piece eliminata
handle di backup piece=H:\ORACLE\BACKUP\ORAPERSP\RMAN\SPFILEBCK_20110629 recid=1005 stamp=755130885
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of delete command on ORA_DISK_1 channel at 06/29/2011 22:34:55
ORA-19584: file E:\ORACLE\ORADATA\ORAPERSP\INDX01.DBF già in usoRecovery Manager ha terminato.

That’s because all current datafiles were registered into recovery catalog as backup copy. With a recovery redundancy of 1, all datafiles were set as obsolete! But since it’s windows, a delete command doesn’t delete datafiles if they are already in use. What it was on unix? We had just luck!

Then we had to uncatalog all copies.

RMAN> list copy;

la specifica non corrisponde a nessuno dei log di archivio del Recovery Catalog

Lista di copie del file di dati
Chiave SCN Ckp file S Ora di completamento Nome Ora ckp
------- ---- - -------------------- ---------- -------------------- ----
26 1 X 29-NOV-10 18535127593 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\SYSTEM01.DBF
27 2 X 29-NOV-10 18535127762 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\UNDOTBS01.DBF
14 3 X 29-NOV-10 18535122625 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\INDX01.DBF
16 4 X 29-NOV-10 18535123721 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\TOOLS01.DBF
17 5 X 29-NOV-10 18535124423 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\USERS01.DBF
18 6 X 29-NOV-10 18535124439 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\DRSYS01.DBF
19 7 X 29-NOV-10 18535124453 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\EXAMPLE01.DBF
20 8 X 29-NOV-10 18535124554 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\ODM01.DBF
21 9 X 29-NOV-10 18535125790 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\XDB01.DBF
22 10 X 29-NOV-10 18535125874 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\CWMLITE01.DBF
23 11 X 29-NOV-10 18535125887 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\TBLDATI01.ORA
24 12 X 29-NOV-10 18535126750 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\TBLINDEX01.ORA

RMAN> change copy of datafile 1..N uncatalog;

copia non catalogata del file di dati
filename di copia del file di dati=E:\ORACLE\ORADATA\ORAPERSP\INDX01.DBF recid=14 stamp=736336991
Oggetti 1 non catalogati

until no “obsolete” current datafile were reported!

RMAN> report obsolete;

i criteri di conservazione RMAN verranno applicati al comando
i criteri di conservazione RMAN sono impostati su una ridondanza 1
non sono stati trovati backup obsoleti

Lesson learned: never schedule delete obsolete without actually checking what could be deleted!

Dataguard check script for Real Application Clusters (MAA)

Two years after my posts:
Quick Oracle Dataguard check script and More about Dataguard and how to check it I faced a whole new Dataguard between two Oracle Real Application Clusters, aka Oracle Maximum Availability Architecture (MAA).

This enviromnent is relying on Windows OS. Don’t know how this could be called “availability” but here we are. I revisited my scripts in a quick and very dirty way. Please consider that I did copy and paste to check the alignment once per thread, but it should be improved with some kind of iteration to check each thread in a more structured fashion.

Please foreward me every improvement you implement over my code: it would be nice to post it here.