How to Find Blocking Sessions in Enterprise Manager Cloud Control 12c

My friend Franck Pachot noticed that EM12c doesn’t show the blocking sessions across all RAC nodes. Let’s check if we can find them using EM12c. I have connected to first node of our RAC database, created a sample table, inserted a row, committed, and then deleted the row. While the session is open, I opened another terminal, connected to second node and tried to delete the rows in same table so my second session started to wait for the transaction of my first session completed.

blocking sessions menu

I opened the main page of the cluster database, selected “blocking sessions” under the performance menu.

blockingsessions

How to Add RAC Databases to Enterprise Manager Cloud Control 12c

It’s been asked several times to me how to add RAC (Clustered Databases) to Enterprise Manager 12c. Finally I found time to get screenshots and prepare a step-by-step guide. The interesting point about adding Clustered Databases is, when you try to discover (and add) a cluster databases like you do a stand-alone database, EM12c does not show you an error but the process doesn’t complete. It makes users to feel like there’s a bug. In fact, it’s just because user miss a required step.

So how to add a RAC Database? First of all, we need to deploy agents to all nodes. you can find related document on my blog. After you deployed agents to all nodes, we’ll add “Oracle Cluster and High Avaliablity Service”.

Add Cluster Target

How to Change IP adresses of SCAN Name in Oracle 11gR2 RAC

Today, I needed to update the IP adresses of scan name in Oracle 11gR2 RAC environment. As you know, these IP addresses are defined in DNS but they also stored in Oracle Clusterware resource configuration to tell which IP addresses will be served by SCAN VIP services. So when you update the IP addresses of the scan name in DNS, you also need to update the resource configuration. This is a very simple task: Stop the SCAN listeners, stop the SCAN (Virtual IP) services and then run “srvctl modify scan” command for the scan name:

Be sure that all the SCAN VIP services are down:

Re-configure scan virtual ip adresses and run the scan listeners:

You can check the new configuration by running “srvctl config scan”.

Upgrading Oracle RAC Database 11.2.0.2 to 11.2.0.3 (Grid + DB)

A new patchset for Oracle 11.2 has been released. This patchset is a full installation of the Oracle Database software. So you don’t need to install 11.2.0.1 before installing 11.2.0.3. You can read the complete list of new features of 11.2.0.3 from here:

http://download.oracle.com/docs/cd/E11882_01/server.112/e22487/chapter1_11203.htm

I downloaded the patchset and wanted to upgrade my 2-node RAC database. It’s just a test database running on VirtualBox. Latest PSU and GI PSU (12419331 and 12419353) are already applied. Before I upgrade the database, I have also applied patch 12539000. Installer checks if this patch is applied. It seems it’s an important patch because it fixes a bug directly related with upgrade process (Bug 12539000 – 11203:ASM UPGRADE FAILED ON FIRST NODE WITH ORA-03113).

The patchset contains 7 zip files but we need only the first 3 zip files to upgrade our grid and database.

First I’ll upgrade the grid infrastructure, then I’ll update the DB. Before you upgrade your database, I recommend you to read “Master Note For Oracle Database Upgrades and Migrations” MOS ID 1152016.1.

As you know, starting from oracle 11gR2, the Oracle DB and grid upgrades are done via the “out of place upgrade”. So I switch to root user, create a new directory for the Oracle software: