Build your own Flex ASM 12c lab using Virtual Box

So, Oracle just released database 12c, and one of its new feature is Flex ASM. You can find more details about this new feature into this Bjoern Rost’s post or this oracle white paper.

Basically, as Bjoern said: “Flex ASM basically allows a database instance to utilize an ASM instance that is not local to that server node and even fail over to another remote instance if the ASM instance fails”.

This short blog post is just to show how you can build your own Flex ASM Lab.

First, I built a 3 nodes RAC. For this purpose you just have to follow one of those great blog posts:

Yury Velikanov’s one : Oracle 12c RAC On your laptop, Step by Step Guide
Tim Hall’s one: Oracle Database 12c Release 1 (12.1.0.1) RAC On Oracle Linux 6 Using VirtualBox

with the following changes:

You need to adapt those posts to build a 3 nodes RAC.
During the install process of the Grid Infrastructure:

Step 3: Choose “Configure a Standard cluster”

Step 4: Choose “Advanced Installation”

Step 10: Choose “Use Oracle Flex ASM for Storage”

Once this is installed, you can verify that Flex ASM has been set up:

asmcmd showclustermode
ASM cluster : Flex mode enabled

You can also check how many ASM instances have been specified for use:

srvctl config asm | grep -i count
ASM instance count: 3

Then you can change the configuration to have 2 ASM instances active on this 3 nodes Lab:

srvctl modify asm -count 2

Once done you can check the status:

srvctl status asm -detail
ASM is running on racnode2,racnode1
ASM is enabled.

Remarks:

I did not test with a 2 nodes RAC and one active ASM instance: If you do please let me know.
If you are curious (as I am) to see how the Flex ASM behaves during physical IO operations, then I suggest to use Kevin Closson’s SLOB2 Kit and my asmiostat utility (I’ll use them for sure and share my findings/remarks if any 😉 )
Steve Karam is building a huge list of Oracle 12c articles from the community into this post (Do not hesitate to have a look at it)

Findings reported thanks to this Lab:

Finding 1: Flex ASM 12c (12.1) and Extended Rac: be careful to “unpreferred” read !
Finding 2: Flex ASM 12c (12.1): be careful to “invisible” I/O !

Monitor the database activity in real time with R

A quick post to let you know that I just finished a R script to monitor the database activity in real time.

The “graph_real_time_db_activity.r” script (You can download it from this repository) basically takes a snapshot based on the v$system_event view then computes and graphs the differences with the previous snapshot.

One graph refreshed in real time is provided. It contains:

A sub-graph for the time waited (in ms) per wait class.

A sub-graph for the wait events distribution of the wait class having the max time waited during the last snap.

A sub-graph for the wait class distribution since the script has been launched.

The script also provides:

a text file that contains the snaps computations.
a pdf file that contains the final graph.

As you can see, for a better understanding of the database behavior, I also included a fake “CPU” wait class (coming from the v$sys_time_model view) as suggested by Guy Harrison into this blog post.

The graph is generated to both outputs (X11 and the pdf file). In case the X11 environment does not work, the pdf file is generated anyway.

Let’s see the script in action:

./graph_real_time_db_activity.r
Building the thin jdbc connection string....

host ?:BDT_HOST
port ?:1521
service_name ?: BDT
system password ?:donotreadthis
Number of snapshots:50
Refresh interval (seconds):2
Loading required package: methods
Loading required package: DBI
Loading required package: rJava
Please enter any key to exit:

As you can see you are prompted for:

jdbc thin “like” details to connect to the database (You can launch the R script outside the host hosting the database).
oracle system user password.
Number of snapshots.
Refresh Interval.

So you can choose the number of snapshots and the graph refresh interval.

The output is like:

Remarks:

The script does not create any objects into the database.
If you want to install R, a good staring point is into the “Getting Staring” section of this link.
Now that I am able to graph in real time with R, my next work is to graph in real time the metrics coming from my asmiostat utility. I’ll keep you posted.

Retrieve and visualize system statistics in real time with R

In one of my previous post I provided a R script to retrieve and visualize system statistics from AWR. Now with this post I will provide a R script to retrieve and visualize the same metrics in real time.

For that purpose I created a R script “graph_real_time_sysstat.r” (You can download it from this repository) that provides:

A graph for the VALUE metric refreshed in real time.
A graph for the VALUE PER SECOND metric refreshed in real time.
A graph for the Histogram of VALUE refreshed in real time.
A graph for the Histogram of VALUE PER SECOND refreshed in real time.
A pdf file that contains those graphs.
A text file that contains the output of the query used to build the graphs.

Basically the script takes a snapshot based on the v$sysstat view then computes and graphs the differences with the previous snapshot.

The graph is generated to both outputs (X11 and the pdf file). In case the X11 environment does not work, the pdf file is generated anyway. In that particular case the pdf file contains a page per snapshot.

Let’s see the script in action:

./graph_real_time_sysstat.r
Building the thin jdbc connection string....

host ?: bdt_host
port ?: 1521
service_name ?: BDT
system password ?: donotreadthis
Display which sysstat (no quotation marks) ?: physical reads
Number of snapshots: 60
Refresh interval (seconds): 20
Loading required package: methods
Loading required package: DBI
Loading required package: rJava
Please enter any key to exit:

As you can see you are prompted for:

jdbc thin “like” details to connect to the database (You can launch the R script outside the host hosting the database).
oracle system user password.
The statistic we want to focus on.
Number of snapshots.
Refresh Interval.

So you can choose the number of snapshots and the graphs refresh interval.

When the number of snapshots is reached the output is like:

The graphs sequence that leads to this final graph is the following (see the real_time_physical_reads pdf file).

Remarks:

All the points will be graphed (No points will be moved outside the graph), even if:
For lisibility the X axis could contains not all the ticks.
The script does not create any objects into the database.
If you want to install R, a good staring point is into the “Getting Staring” section from this link.
The graphs “value” and “value_per_sec” should looks like the same: It may not be the case if the snaps are not sampled at regular interval (unexpected delay during the collection).

Conclusion:

We now have 4 scripts at our disposal to retrieve and display with R:

for a particular system statistic:

real time metrics (script described into this post)
AWR based metrics (script described this post)

and also for a particular wait event:

real time metrics (script described this post)
AWR based metrics (script described this post)

Retrieve and visualize in real time wait events metrics with R

In one of my previous post I provided a R script to retrieve and visualize wait events metrics from AWR. Now with this post I will provide a R script to retrieve and visualize the same metrics in real time.

For that purpose I created a R script “graph_real_time_event.r” (You can download it from this repository) that provides:

A graph for the TIME_WAITED_MS metric refreshed in real time.
A graph for the NB_WAITS metric refreshed in real time.
A graph for the MS_PER_WAIT metric refreshed in real time.
A graph for the Histogram of MS_PER_WAIT refreshed in real time.
A pdf file that contains those graphs.
A text file that contains the output of the query used to build the graphs.

Basically the script takes a snapshot based on the v$system_event view then computes and graphs the differences with the previous snapshot.

Let’s see the script in action:

./graph_real_time_event.r
Building the thin jdbc connection string....

host ?: bdt_host
port ?: 1521
service_name ?: BDT
system password ?: donotreadthis
Display which system event (no quotation marks) ?: db file sequential read
Number of snapshots: 60
Refresh interval (seconds): 2
Loading required package: methods
Loading required package: DBI
Loading required package: rJava
Please enter any key to exit:

As you can see you are prompted for:

jdbc thin “like” details to connect to the database (You can launch the R script outside the host hosting the database).
oracle system user password.
The wait event we want to focus on.
Number of snapshots.
Refresh Interval.

So you can choose the number of snapshots and the graphs refresh interval.

When the number of snapshots is reached the output is like:

The graphs sequence that leads to this final graph is the following (see the real_time_db_file_sequential_read pdf file).

Remarks:

All the points will be graphed (No points will be moved outside the graph), even if:
For lisibility the X axis could contains not all the ticks.
The script does not create any objects into the database.
If you want to install R, a good staring point is into the “Getting Staring” section from this link.
If you just want a text output in real time then see this blog post.

Conclusion:

We are now able to retrieve and display wait events metrics with R from AWR (see the previous post) and in real time.

Now that I am able to graph in real time with R, my next work is to graph in real time the metrics coming from my asmiostat utility. I’ll keep you posted.