Patch Oracle Databases With Ansible and Enterprise Manager 13c

In a previous post I show you how you can integrate DevOps automation and orchestration tools to provision Oracle databases by leveraging Enterprise Manager (EM) and the Cloud Management Pack (CMP). Once provisioned, databases need to be fully maintained in terms of monitoring but most precisely patching.

Patching databases decreases the risk of breach by timely mitigating vulnerabilities, but in can be a daunting task for organizations. Manual patching is time consuming and error prone. Home grown scripts are difficult to maintain and they increase maintenance cost. So the question is, how can I automate the patching process and even better, how can I integrate it with my current orchestration workflow?

Let me explain how you can achieve all this by making use of Oracle’s Database Lifecycle Management Pack (DBLM) and CMP. DBLM’s Fleet Maintenance help us patch and upgrade Oracle databases at scale with minimum downtime. It makes use of Gold Images and out-of-place patching in order to achieve this.

All this functionality can be integrated with CMP’s DBaaS in order to provide and end-to-end automation solution. DBaaS exposes REST APIs that we could then call using the automation tool of choice. Database Administrators, end users or 3rd party tools can then use these features to patch Oracle databases.

Do you want to learn more about this and even be able to try it? We’ve created an Oracle LiveLabs that cover’s all this functionality. This lab will guide you through the request of a PDB, setup DBaaS configuration, setup Fleet Maintenance and finally patch the PDB.

Follow the link below for the Oracle LiveLabs workshop.

click here…

If you are planning on attending to Oracle Cloud World this year and you want to learn more about this consider attending my session.

LRN3519: Deploy and Manage Oracle Databases with Ansible, Oracle Enterprise Manager

See you in Vegas!!!

Thanks,
Alfredo

Improve Oracle Database Security With Enterprise Manager 13c

IT Security is popular topic nowadays! We constantly hear news about data breaches, ransomware, malware, unauthorized access to IT systems, etc. IT organizations are constantly looking to keep their systems, networks and data safe and secure.

Today’s blog is about how Oracle Enterprise Manager (EM) can help Database Administrators to secure and harden the Oracle Databases they manage along with the hosts those databases are running on.

First things first. I strongly recommend to review the Oracle Database 19c Security Guide. This guide provides guidelines on how to secure the Oracle Database, harden the DB access, secure and encrypt the DB data and so.

click here…

Now let’s discuss some areas that database administrators should also look at in order to improve their security posture:

Timely apply security patches
Monitor database configuration and detect misconfigurations
Use industry and regulatory standards like STIG and CIS for the Oracle Database

All the features that we will be discussing today are part of the Oracle Database Lifecycle Management pack. This pack requires an additional license.

Timely apply security patches

Fleet Maintenance (FM) enables administrators to automate lifecycle activities such as database patching and upgrades. FM is a gold image subscription based model that allows to patch databases with minimum downtime by using out-of-place patching mechanisms. In-place patching is also available if you need to apply an emergency on-off patch.

Administrators have the ability to customize the patching process by adding custom pre/post scripts to patching operations. FM supports single instance, RAC databases, Grid Infrastructure, Multitenant and Data Guard configurations.

One thing to mention is the ability to get security patch recommendations as soon as they are published. EM connects to My Oracle Support (MOS) and checks for the availability of new security patches. As soon as a new security patch is released EM will let you know if your DB estate is compliant or not in terms of these patches.

more info…

Monitor database configuration and detect misconfigurations

Configuration and Drift Management helps you monitor the configuration of your DB estate, the hosts on where those DB’s are running as well as the Oracle Homes (OH) for those installations. EM allows you to create your own configuration templates based on the configuration settings you need to enforce. Any misconfiguration or drift away of your template will be automatically reported via the Drift Management dashboard and you can also receive alerts if you choose to.

Corrective Actions (CA) can also be created to automatically fix this misconfigurations in order to comply with the templates and reduce security risks.

How many times administrators issued an ALTER SYSTEM command with SPFILE scope and forgot about it? Well, you will know next time you bring your DB up after maintenance. EM helps you detect these changes before they become a production issue. It also help you track the history of configuration changes, save configuration information at a given time and also allows you to use this configuration information to be compared between targets.

Have you wonder, how many OH’s we have with this specific one-off patch?

How many DB’s we have running on this specific OS version?

Well, EM can help you answer all these questions using this configuration data.

One thing worth mentioning is that EM comes with hundreds on configuration collections. If you need to gather a very specific configuration that is not available out-of-the-box, you can create your own configuration extension and collect this automatically.

more info…

Use industry and regulatory standards like STIG and CIS for the Oracle Database

EM provides compliance standards to help customers meet regulatory standards like STIG and CIS. Oracle’s best practices are also included within the compliance framework. There are two available options for analysis.

Rule based analysis
Real-time change

Each option allow administrators understand where attentions needs to be put in order to harden the DB estate.

Using the compliance framework, EM will provide a score to each associated target along with all the violations that need to be remediated after each evaluation.

more info…

I also want to provide links to Oracle LiveLabs workshops available that cover the features discussed above.

Thanks,
Alfredo

Oracle Enterprise Manager 13c Snap Clone Demo

The database cloning process can be often time consuming, resource intensive and expensive especially for large multi-terabyte databases. This can lead to miss project deadlines or to cause that storage costs grow out of control.

Enterprise Manager 13c Snap Clone instant database cloning allows administrators to create fully functional copies of databases using the capabilities of the underlying storage layer. Snap Clone is also capable to use Data Masking Definitions in order to securely manage test data.

Below video is a demonstration on how to setup Snap Clone with the ZFS storage appliance in order to clone either single instance or pluggable databases (PDBs).

For additional information about Snap Clone capabilities click below.

Click here…

Thanks,
Alfredo

Oracle Database Performance Diagnostics, Tuning and Validation with Oracle Enterprise Manager 13c Workshops

Last month we held 2 workshops where we went through some capabilities of Oracle Enterprise Manager 13c that help with performance diagnostics, tuning and validation for the Oracle Database.

If you were not able to attend and you want to learn about these capabilities, I recommend you take a look at the recording using the link below.

Session 1: August 4th 2021

Click here…

Session 2: August 25th 2021

click here…

Thanks,
Alfredo

Oracle Database 12.2 New Features – SQL* Plus Enhancements

I want to start a series of posts about Oracle Database 12.2 new features this new year 2018.

There’s no better start of this series than to start with SQL*Plus.

SQL*Plus is probably one of the most utilized tools by DBA’s (sqlcl is gaining steam) and here I show some really cool new features.

SQL*Plus History

With this command HIST[ORY] and if turned on; you can run, edit, delete or list previously used SQL or PL/SQL commands for the current session.

$ sqlplus sys as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Fri Jan 5 12:28:58 2018

Enter password:

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

SQL> show history

history is OFF

SQL> set history on

SQL> show history

history is ON and set to “100”

SQL> set history 1000

SQL> show history

history is ON and set to “1000”

SQL> show user

USER is “SYS”

SQL> desc dual

Name Null? Type

—————————————– ——– —————————-

DUMMY VARCHAR2(1)

SQL> select * from dual;

–

SQL> hist

1 show history

2 show user

3 desc dual

4 select * from dual;

SQL> hist 4 run

–

SQL>

SQL*Plus SET MARKUP CSV

This setting is going to present the output is CVS format.

SQL> set markup csv on

SQL> select * from emp;

“EMPNO”,”ENAME”,”JOB”,”MGR”,”HIREDATE”,”SAL”,”COMM”,”DEPTNO”

7839,”KING”,”PRESIDENT”,,”17-NOV-81″,5000,,10

7698,”BLAKE”,”MANAGER”,7839,”01-MAY-81″,2850,,30

7782,”CLARK”,”MANAGER”,7839,”09-JUN-81″,2450,,10

7566,”JONES”,”MANAGER”,7839,”02-APR-81″,2975,,20

7788,”SCOTT”,”ANALYST”,7566,”19-APR-87″,3000,,20

7902,”FORD”,”ANALYST”,7566,”03-DEC-81″,3000,,20

7369,”SMITH”,”CLERK”,7902,”17-DEC-80″,800,,20

7499,”ALLEN”,”SALESMAN”,7698,”20-FEB-81″,1600,300,30

7521,”WARD”,”SALESMAN”,7698,”22-FEB-81″,1250,500,30

7654,”MARTIN”,”SALESMAN”,7698,”28-SEP-81″,1250,1400,30

7844,”TURNER”,”SALESMAN”,7698,”08-SEP-81″,1500,0,30

7876,”ADAMS”,”CLERK”,7788,”23-MAY-87″,1100,,20

7900,”JAMES”,”CLERK”,7698,”03-DEC-81″,950,,30

7934,”MILLER”,”CLERK”,7782,”23-JAN-82″,1300,,10

14 rows selected.

SQL*Plus SET FEEDBACK ONLY

This option will display the number of rows selected without displaying the data. Useful to measure fetch time.

SQL> set feedback only

SQL> set timing on

SQL> select * from emp;

14 rows selected.

Elapsed: 00:00:00.01

SQL*Plus Performance Settings

SET ROWPREFECTH (default 1 | 2G max)

This setting pre-fetches rows in a result set. It can reduce the number of round trips between OCI execute calls.

I had an interesting question from a colleague about this setting. What is the difference between this ROWPREFECTH and ARRAYSIZE?

I’m still struggling to find the differences. Here are both definitions extracted from Oracle’s documentation:

SET ARRAYSIZE

Sets the number of rows that SQL*Plus will fetch from the database at one time. Valid values are 1 to 5000.

The effectiveness of setting ARRAYSIZE depends on how well Oracle Database fills network packets and your network latency and throughput. In recent versions of SQL*Plus and Oracle Database, ARRAYSIZE may have little effect. Overlarge sizes can easily take more SQL*Plus memory which may decrease overall performance.

SET ROWPREFETCH {1 | n}

Sets the number of rows that SQL*Plus will prefetch from the database at one time.

The default value is 1.

What I can see from here is that ARRAYSIZE takes place during the fetch step and probably ROWPREFECTH is just before the fetch step?

I even tried to identify this by using the autotrace option and by tracing a test session, but unfortunately I was not able to find any differences. Maybe the amount of data queried wasn’t large enough.

$ sqlplus scott/****

SQL*Plus: Release 12.2.0.1.0 Production on Fri Jan 5 13:18:41 2018

Last Successful login time: Fri Jan 05 2018 13:15:53 -05:00

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

SQL> set autotrace on

SQL> select * from emp;

…

14 rows selected.

Execution Plan

———————————————————-

Plan hash value: 3956160932

————————————————————————–

————————————————————————–

| 0 | SELECT STATEMENT | | 14 | 1218 | 3 (0)| 00:00:01 |

| 1 | TABLE ACCESS FULL| EMP | 14 | 1218 | 3 (0)| 00:00:01 |

————————————————————————–

Note

—–

– dynamic statistics used: dynamic sampling (level=2)

Statistics

———————————————————-

0 recursive calls

0 db block gets

8 consistent gets

0 physical reads

0 redo size

1537 bytes sent via SQL*Net to client

608 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

14 rows processed

SQL> set rowprefetch 10

SQL> select * from emp;

…

14 rows selected.

Execution Plan

———————————————————-

Plan hash value: 3956160932

————————————————————————–

————————————————————————–

| 0 | SELECT STATEMENT | | 14 | 1218 | 3 (0)| 00:00:01 |

| 1 | TABLE ACCESS FULL| EMP | 14 | 1218 | 3 (0)| 00:00:01 |

————————————————————————–

Note

—–

– dynamic statistics used: dynamic sampling (level=2)

Statistics

———————————————————-

0 recursive calls

0 db block gets

8 consistent gets

0 physical reads

0 redo size

1534 bytes sent via SQL*Net to client

608 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

14 rows processed

SQL> set rowprefetch 5

SQL> select * from emp;

…

14 rows selected.

Execution Plan

———————————————————-

Plan hash value: 3956160932

————————————————————————–

————————————————————————–

| 0 | SELECT STATEMENT | | 14 | 1218 | 3 (0)| 00:00:01 |

| 1 | TABLE ACCESS FULL| EMP | 14 | 1218 | 3 (0)| 00:00:01 |

————————————————————————–

Note

—–

– dynamic statistics used: dynamic sampling (level=2)

Statistics

———————————————————-

0 recursive calls

0 db block gets

8 consistent gets

0 physical reads

0 redo size

1534 bytes sent via SQL*Net to client

608 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

14 rows processed

SQL> set arraysize 5

SQL> select * from emp;

…

14 rows selected.

Execution Plan

———————————————————-

Plan hash value: 3956160932

————————————————————————–

————————————————————————–

| 0 | SELECT STATEMENT | | 14 | 1218 | 3 (0)| 00:00:01 |

| 1 | TABLE ACCESS FULL| EMP | 14 | 1218 | 3 (0)| 00:00:01 |

————————————————————————–

Note

—–

– dynamic statistics used: dynamic sampling (level=2)

Statistics

———————————————————-

0 recursive calls

0 db block gets

8 consistent gets

0 physical reads

0 redo size

1534 bytes sent via SQL*Net to client

608 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

14 rows processed

SQL> set rowprefetch 1

SQL> select * from emp;

…

14 rows selected.

Execution Plan

———————————————————-

Plan hash value: 3956160932

————————————————————————–

————————————————————————–

| 0 | SELECT STATEMENT | | 14 | 1218 | 3 (0)| 00:00:01 |

| 1 | TABLE ACCESS FULL| EMP | 14 | 1218 | 3 (0)| 00:00:01 |

————————————————————————–

Note

—–

– dynamic statistics used: dynamic sampling (level=2)

Statistics

———————————————————-

0 recursive calls

0 db block gets

10 consistent gets

0 physical reads

0 redo size

1919 bytes sent via SQL*Net to client

630 bytes received via SQL*Net from client

4 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

14 rows processed

As you can see the only visible changes are the SQL*Net roundtrips to/from client of the ARRAYSIZE setting.

SET LOBPREFETCH (default 0 bytes | 2G max)

Similar to ROWPREFECTH but for LOB data.

SET STATEMENTCACHE (default 0 | 32767 max)

This setting will cache similar SQL statements for the current session reducing the amount of necessary parses.

Thanks,

Alfredo

Free Database Seminar in Timisoara, Romania

Today I want to talk about a free Database Seminar that ACI is sponsoring in Timisoara, Romania. A friend of mine and former co-worker asked me to speak in the seminar about Oracle Enterprise Manager Administration.

Because of the Time zone’s differences, I’m going to make a recording of the presentation and upload it to the blog, so he can show it there.

If you are nearby Timisoara, don’t loose the opportunity to assist and learn little bit more about databases.

The event is going to take place April, 8th at 5 pm @ City Business Centre, building D, Timisoara, RO.

Did I forget to mention that the event is for free?

For more information go to:

https://www.facebook.com/events/1035993103095436/

Thanks,

Alfredo

Oracle Enterprise Manager – Reducing the noise, Part 1

Enterprise Manager 12c is a great monitoring tool, with it you can monitor a wide range of target types from databases to middleware; although out-of-the-box metrics can suit your monitoring requirements they can generate a considerable amount of white noise. In order to reduce this noise first you have to identify which are the top alerts in your system; Cloud Control comes with several predefined reports that help you to dig into multiple areas of your system, there’s a report “20 Most Common Alerts” which shows you the incidence of common alerts.

In the picture above, you can clearly see that metric “Database Time Spent Waiting (%)” appears twice in my Top 3, let’s find out our metric setting for my DB targets; in order to do this we must go to a DB home page then Oracle Database -> Monitoring -> Metrics and Collection Settings.

Wait a minute! Why I’m receiving alerts if there are no thresholds setup for any of those metrics?, this behavior is clearly explained in MOS note 1500074.1 about a default warning threshold of 30% inside the database configuration. Let’s take a look to dba_threshold to confirm.

set lines 300

column METRICS_NAME format a30

column WARNING_OPERATOR format a30

column WARNING_VALUE format a30

column CRITICAL_OPERATOR format a30

column CRITICAL_VALUE format a30

SELECT METRICS_NAME,WARNING_OPERATOR ,WARNING_VALUE,CRITICAL_OPERATOR ,CRITICAL_VALUE FROM DBA_THRESHOLDS;

METRICS_NAME WARNING_OPERATOR WARNING_VALUE CRITICAL_OPERATOR CRITICAL_VALUE

———————————– —————————— —————————— —————————— ——————————

Average Users Waiting Counts GT 10 NONE

Average Users Waiting Counts GT 30 NONE

Blocked User Session Count GT 0 NONE

Current Open Cursors Count GT 1200 NONE

Database Time Spent Waiting (%) GT 30 NONE

Database Time Spent Waiting (%) GT 50 NONE

Logons Per Sec GE 100 NONE

Session Limit % GT 90 GT 97

Tablespace Bytes Space Usage DO NOT CHECK 0 DO_NOT_CHECK 0

Tablespace Space Usage GE 85 GE 97

22 rows selected.

There you go!, all metrics for “Database Time Spent Waiting (%)” are set to 30% or 50% values, now the trick to disable these metrics is to set them to a different value like 99%; this will override the default value as follows:

Let’s look at the database setting again:

set lines 300

column METRICS_NAME format a30

column WARNING_OPERATOR format a30

column WARNING_VALUE format a30

column CRITICAL_OPERATOR format a30

column CRITICAL_VALUE format a30

METRICS_NAME WARNING_OPERATOR WARNING_VALUE CRITICAL_OPERATOR CRITICAL_VALUE

———————————– —————————— —————————— —————————— ——————————

Average Users Waiting Counts GT 10 NONE

Average Users Waiting Counts GT 30 NONE

Blocked User Session Count GT 0 NONE

Current Open Cursors Count GT 1200 NONE

Database Time Spent Waiting (%) GT 99 NONE

Logons Per Sec GE 100 NONE

Session Limit % GT 90 GT 97

Tablespace Bytes Space Usage DO NOT CHECK 0 DO_NOT_CHECK 0

Tablespace Space Usage GE 85 GE 97

25 rows selected.

We successfully modified these metrics to a very high value; at this point you can decide to stay at 99% or you can remove that threshold in order to completely disable them.

Now let’s confirm those settings in the database:

set lines 300

column METRICS_NAME format a30

column WARNING_OPERATOR format a30

column WARNING_VALUE format a30

column CRITICAL_OPERATOR format a30

column CRITICAL_VALUE format a30

METRICS_NAME WARNING_OPERATOR WARNING_VALUE CRITICAL_OPERATOR CRITICAL_VALUE

———————————– —————————— —————————— —————————— ——————————

Average Users Waiting Counts GT 10 NONE

Average Users Waiting Counts GT 30 NONE

Blocked User Session Count GT 0 NONE

Current Open Cursors Count GT 1200 NONE

Logons Per Sec GE 100 NONE

Session Limit % GT 90 GT 97

Tablespace Bytes Space Usage DO NOT CHECK 0 DO_NOT_CHECK 0

Tablespace Space Usage GE 85 GE 97

14 rows selected.

The metrics are not there anymore and hopefully the alerts neither. This behavior is also noted for “Average Users Waiting Counts” metric, if you are receiving considerable white noise for this metric you can disable as well following the same procedure. A good practice is to create a Monitoring template to help you modify these thresholds for multiple targets at once.

Stay tuned for my next post about reducing OEM 12c noise.

Thanks,

Alfredo

Extract AWR data to build Response Time graphs (awrrtsys.sql)

This SQL script extract AWR data required to perform Response Time analysis for Oracle Databases. All information about it will be presented in the IOUG Collaborate 2014 session “553: Oracle Database Performance: Are Database Users Telling Me The Truth?”

–#********************************************************************

–#– Filename : awrrtsys.sql

–#– Author : Alfredo Krieg

–#– Original : 17-Oct-12

–#– Last Update : 21-Ago-13

–#– Description : awrrtsys.sql- System Response time per snapshot in the last x days

–# per desired unit of work –

–#– Usage : start awrrtsys.sql

–#– This script use AWR data (licensing is required)

–#********************************************************************

set termout on

set feedback off

set heading on

set linesize 150

set pagesize 100

set feedback off verify off

col snap_id format 999999 heading “Snapshot Id”

col instance_number format 99 heading “Instance Number”

col snap_time format a25 heading “Snap Begin Time”

col non_idle_wait format 9999999.99 heading “Qt (s)”

col cpu_time format 9999999.99 heading “St (s)”

col lio format 9999999.99 heading “LIO”

col pio format 9999999.99 heading “PIO”

col uw_variable format 9999999.99 heading “UC”

col rt_ms_per_lio format 9999999.99 heading “RT (ms/lio)”

col rt_ms_per_pio format 9999999.99 heading “RT (ms/pio)”

col rt_ms_per_uw format 9999999.99 heading “RT (ms/uc)”

SELECT a.SNAP_ID,

b.instance_number,

TO_CHAR (END_INTERVAL_TIME, ‘mon/dd/yyyy HH24:mi’) AS snap_time,

non_idle_wait,

background_cpu+

db_cpu as cpu_time,

lio,

pio,

uwvariable AS UW_VARIABLE,

ROUND (

( ( (background_cpu + db_cpu) / lio) + (non_idle_wait / lio))

* 1000,

AS RT_ms_per_lio,

ROUND (

( ( (background_cpu + db_cpu) / pio) + (non_idle_wait / pio))

* 1000,

AS RT_ms_per_pio,

ROUND (

( ( (background_cpu + db_cpu) / (uwvariable))

+ (non_idle_wait / (uwvariable)))

* 1000,

AS RT_ms_per_uw

FROM (

SELECT SNAP_ID,

SUM (non_idle_wait) AS non_idle_wait,

ROUND (SUM (background_cpu), 0) AS background_cpu,

ROUND (SUM (db_cpu), 0) AS db_cpu,

SUM (lio) AS lio,

SUM (pio) AS pio,

SUM (uwvariable) AS uwvariable

FROM (

SELECT SNAP_ID,

ROUND (time_secs, 0) non_idle_wait,

0 AS background_cpu,

0 AS db_cpu,

0 AS lio,

0 AS pio,

0 AS uwvariable

FROM ( SELECT snap_id,

‘WAIT’ AS wait_class,

‘Non Idle’ AS name,

(SUM (VALUE) – SUM (value1)) / 1000000

AS time_secs

FROM ( SELECT snap_id,

0 AS snap2,

SUM (time_waited_micro) AS VALUE,

0 AS value1

FROM dba_hist_system_event

WHERE wait_class ‘Idle’

GROUP BY snap_id

UNION

SELECT snap_id + 1,

snap_id,

SUM (time_waited_micro) AS value1

FROM dba_hist_system_event

WHERE wait_class ‘Idle’

GROUP BY snap_id)

UNION

SELECT snap_id,

(SUM (VALUE) – SUM (value1)) / 1000000

AS background_cpu,

FROM (SELECT snap_id,

0 AS snap2,

stat_name,

VALUE,

0 AS value1

FROM dba_hist_sys_time_model

WHERE stat_name IN (‘background cpu time’)

UNION

SELECT snap_id + 1,

snap_id,

stat_name,

VALUE AS value1

FROM dba_hist_sys_time_model

WHERE stat_name IN (‘background cpu time’))

GROUP BY snap_id, stat_name

UNION

SELECT snap_id,

(SUM (VALUE) – SUM (value1)) / 1000000 AS db_cpu,

FROM (SELECT snap_id,

0 AS snap2,

stat_name,

VALUE,

0 AS value1

FROM dba_hist_sys_time_model

WHERE stat_name IN (‘DB CPU’)

UNION

SELECT snap_id + 1,

snap_id,

stat_name,

VALUE AS value1

FROM dba_hist_sys_time_model

WHERE stat_name IN (‘DB CPU’))

GROUP BY snap_id, stat_name

UNION

SELECT snap_id,

SUM (VALUE) – SUM (value1) AS lio,

FROM (SELECT snap_id,

0 AS snap2,

stat_name,

VALUE,

0 AS value1

FROM DBA_HIST_SYSSTAT

WHERE stat_name IN (‘session logical reads’)

UNION

SELECT snap_id + 1,

snap_id,

stat_name,

VALUE AS value1

FROM DBA_HIST_SYSSTAT

WHERE stat_name IN (‘session logical reads’))

GROUP BY snap_id, stat_name

UNION

SELECT snap_id,

SUM (VALUE) – SUM (value1) AS pio,

FROM (SELECT snap_id,

0 AS snap2,

stat_name,

VALUE,

0 AS value1

FROM DBA_HIST_SYSSTAT

WHERE stat_name IN (‘physical reads’)

UNION

SELECT snap_id + 1,

snap_id,

stat_name,

VALUE AS value1

FROM DBA_HIST_SYSSTAT

WHERE stat_name IN (‘physical reads’))

GROUP BY snap_id, stat_name

UNION

SELECT snap_id,

SUM (VALUE) – SUM (value1) AS uwvariable

FROM (SELECT snap_id,

0 AS snap2,

stat_name,

VALUE,

0 AS value1

FROM DBA_HIST_SYSSTAT

WHERE stat_name IN (‘&1’)

UNION

SELECT snap_id + 1,

snap_id,

stat_name,

VALUE AS value1

FROM DBA_HIST_SYSSTAT

WHERE stat_name IN (‘&1’))

GROUP BY snap_id, stat_name

)

GROUP BY snap_id

HAVING SUM (background_cpu) >= 0

ORDER BY snap_id DESC) a, DBA_HIST_SNAPSHOT B

WHERE a.SNAP_ID = b.SNAP_ID AND BEGIN_INTERVAL_TIME > SYSDATE – &2

ORDER BY 1;

Oracle 12c ASM new features

Today I will talk about some new features for ASM on 12c version.

Oracle Flex ASM

Oracle Flex ASM is a set of new features that provide critical capabilities required for cloud computing. Oracle Flex ASM redefines the traditional ASM cluster architecture of having one ASM instance on every node in the cluster; this means you can have less ASM instances than nodes in your cluster. In this configuration the number of ASM instances running is called ASM cardinality, by default the ASM cardinality is 3 and can be changed with a Clusterware command.

What are the benefits?

On the traditional architecture when an ASM instance fails; all DB instances connected to that instance in the node will also fail. With Oracle Flex ASM if the ASM instance fail the Clusterware will relocate that ASM instance to a different node and the DB instances will remotely (through private network) connect to the relocated ASM instance, all without disruption to the DB client.

Dedicated ASM Network

This new 12c feature provides the option to dedicate a private network for ASM network traffic only. There’s also the option to use the Oracle Clusterware interconnect private network.

Remote Access

As per the new Oracle Flex ASM feature, the DB instance can remotely connect to ASM instance hence the need of a password file in order to authenticate remote DB instances to ASM. Oracle Flex ASM has the ability of storing password files in a Disk Group and is extended to DB clients; this is really useful to avoid synchronizing multiple password files within the cluster.

Oracle Flex ASM is one of the most important enhancements to ASM in 12c version, however in the below whitepaper describes the rest of new features present.

http://www.oracle.com/technetwork/products/cloud-storage/oracle-12c-asm-overview-1965430.pdf

Bertrand Drouvot has already tested this Oracle Flex ASM feature and uploaded the contents into his own blog (http://bdrouvot.wordpress.com/2013/06/29/build-your-own-flex-asm-12c-lab-using-virtual-box/).

Another interesting feature is the Extent Reading Selection Enhancement which evenly distributes the selection of which copy of a block is read in ASM Disk Groups with normal or high redundancy. This feature is enabled by default in 12c and states that users on I/O bound systems should notice a performance improvement while reading blocks from disks.

Thanks,

Alfredo

Why my new Disk Group is not getting mounted automatically when ASM instance starts?

Today I faced the issue that a new ASM Disk Group created in instance +ASM1 is not getting automatically mounted in instance +ASM2.

The first step was to check DB dependencies:

[oracle@ol5-112-rac2 trace]$ srvctl config database -d RAC

Database unique name: RAC

Database name: RAC

Oracle home: /u01/app/oracle/product/11.2.0/db_1

Oracle user: oracle

Spfile: +DATA/RAC/spfileRAC.ora

Domain: localdomain

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Server pools: RAC

Database instances: RAC1,RAC2

Disk Groups: DATA

Services: reports

Database is administrator managed

You can see that only dependent Disk Groups are ‘DATA’.

Let us add ‘FRA’ also as dependent Disk Group:

[oracle@ol5-112-rac2 trace]$ srvctl modify database -d RAC -a ‘DATA,FRA’

[oracle@ol5-112-rac2 trace]$ srvctl config database -d RAC

Database unique name: RAC

Database name: RAC

Oracle home: /u01/app/oracle/product/11.2.0/db_1

Oracle user: oracle

Spfile: +DATA/RAC/spfileRAC.ora

Domain: localdomain

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Server pools: RAC

Database instances: RAC1,RAC2

Disk Groups: DATA,FRA

Services: reports

Database is administrator managed

The next step is to add the Disk Groups to ASM_DISKGROUPS parameter:

SQL> ALTER SYSTEM SET ASM_DISKGROUPS=’DATA’,’FRA’ scope=both sid=’+ASM1′;

System altered.

SQL> ALTER SYSTEM SET ASM_DISKGROUPS=’DATA’,’FRA’ scope=both sid=’+ASM2′;

System altered.

After restarting +ASM2 instance we can see that FRA Disk Group is mounted automatically:

[oracle@ol5-112-rac2 trace]$ sqlplus / as sysasm

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jun 12 15:44:44 2013

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production

With the Real Application Clusters and Automatic Storage Management options

SQL> set lines 1000

SQL> select * from V$ASM_DISKGROUP;

GROUP_NUMBER NAME SECTOR_SIZE BLOCK_SIZE ALLOCATION_UNIT_SIZE STATE TYPE TOTAL_MB FREE_MB HOT_USED_MB COLD_USED_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB OFFLINE_DISKS COMPATIBILITY DATABASE_COMPATIBILITY V

———— —————————— ———– ———- ——————– ———– —— ———- ———- ———– ———— ———————– ————– ————- ———————————————————— ———————————————————— –

1 DATA 512 4096 1048576 MOUNTED EXTERN 15342 12616 0 2726 0 12616 0 11.2.0.0.0 10.1.0.0.0 N

2 FRA 512 4096 1048576 MOUNTED EXTERN 6133 5962 0 171 0 5962 0 11.2.0.0.0 10.1.0.0.0 N

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production

With the Real Application Clusters and Automatic Storage Management options

Thanks,

Alfredo