Author Archives: Martin Bach

About Martin Bach

Oracle DBA and Linux enthusiast, part time author and presenter.

Little things worth knowing: scheduler jobs and Instance Caging

While updating material for a training class about Database Resource Management I briefly wondered if Instance Caging should apply for scheduler jobs as well. The obvious answer is “yes”, but I wanted to find proof. I hope I did in the end, and wanted to share how I got there.

The test environment

My system is based on the shiny new AMD Ryzen 7 1700X Eight-Core Processor, and it shows as 1s8c16t. I really like it! My 12.2.0.1 database named DEMO is patched to August 2017 – 12.2.0.1.170814 to be precise. It’s difficult to test resource management, and specifically Instance Caging, with a dual-core laptop, so this machine should help!

Test setup

Apart from the database workload I am soon going to launch the system is otherwise idle. I set cpu_count to 4 and made sure I had a resource manager plan active:

SQL> select name, value from v$parameter 
  2   where name in ('cpu_count','resource_manager_plan');

NAME                           VALUE                                             
------------------------------ ------------------------------------------------
cpu_count                      4                                                 
resource_manager_plan          SCHEDULER[0x4ABF]:DEFAULT_MAINTENANCE_PLAN        

For this case it doesn’t matter that my resource manager plan is associated with the maintenance window. All I needed was some resource manager plan. In production systems the situation is most often somewhat different and proper resource management is crucial.

The scheduler needs something to work with, and I opted for a stored procedure that needlessly burns CPU. Like this one:

SQL> create or replace procedure martin.burn_proc(
  2   pi_num_iterations number)
  3  as
  4   i number;
  5  begin
  6  for j in 1..pi_num_iterations loop
  7   i := dbms_random.random;
  8  end loop;
  9 end;
 10 /

Procedure created.

Now all I had to do was to create an anonymous block of PL/SQL scheduling a number of jobs. This little piece of code does just that:

SQL> !cat burn_sched.sql 
select to_char(sysdate,'dd.mm.yyyy hh24:mi:ss') now from dual;

show user

accept num_jobs number prompt 'how many jobs to schedule? '

prompt creating a number of scheduler jobs
declare
        i number;
begin
        for i in 1..&num_jobs loop
                dbms_scheduler.create_job(
                        job_name => 'martin.burn_proc_job' || i, 
                        job_type => 'PLSQL_BLOCK', 
                        job_action => 'begin burn_proc(1000000000); end;', 
                        start_date => systimestamp, 
                        enabled => true);
        end loop;
        dbms_lock.sleep(5);
end;
/

prompt checking for running jobs
select count(*) from dba_scheduler_running_jobs where owner = 'MARTIN';

Now it’s time to run the code!

SQL> @burn_sched

NOW
-------------------
05.11.2017 09:38:49

USER is "MARTIN"
how many jobs to schedule? 3
creating a number of scheduler jobs
old   4:        for i in 1..&num_jobs loop
new   4:        for i in 1..         3 loop

PL/SQL procedure successfully completed.

checking for running jobs

  COUNT(*)
----------
         3

With this first test I wanted to see what happens when keeping the number of jobs lower than the value I defined for cpu_count. Using Active Session History (ASH) as the source for performance data is probably the easiest way to analyse what happened.

Careful: using Active Session History (ASH) requires a license!

Provided you are licensed to use ASH, a query such as the following provides valuable insights:

SQL> select count(*), event, session_state, session_type, username
  2  from v$active_session_history ash left join dba_users u on (ash.user_id = u.user_id)
  3  where module = 'DBMS_SCHEDULER'
  4  and sample_time > to_date('05.11.2017 09:38:49','dd.mm.yyyy hh24:mi:ss')
  5  group by event, session_state, session_type, username;

  COUNT(*) EVENT                SESSION SESSION_TY USERNAME
---------- -------------------- ------- ---------- ----------
        89                      ON CPU  FOREGROUND MARTIN

As you can see, none of the samples in ASH show any waits for CPU. Let’s increase the session count to a value that exceeds my cpu_count of 4:

SQL> @burn_sched

NOW
-------------------
05.11.2017 09:51:45

USER is "MARTIN"
how many jobs to schedule? 5
creating a number of scheduler jobs
old   4:        for i in 1..&num_jobs loop
new   4:        for i in 1..         5 loop

PL/SQL procedure successfully completed.

checking for running jobs

  COUNT(*)
----------
         5

After these 5 jobs completed, I checked ASH again to see if there was a difference:

SQL> select count(*), event, session_state, session_type, username
  2  from v$active_session_history ash left join dba_users u on (ash.user_id = u.user_id)
  3  where module = 'DBMS_SCHEDULER'
  4  and sample_time > to_date('05.11.2017 09:51:45','dd.mm.yyyy hh24:mi:ss')
  5  group by event, session_state, session_type, username;

  COUNT(*) EVENT                          SESSION SESSION_TY USERNAME
---------- ------------------------------ ------- ---------- ----------
       153                                ON CPU  FOREGROUND MARTIN
        66 resmgr:cpu quantum             WAITING FOREGROUND MARTIN

And indeed, there is a number of Resource Manager CPU waits! To me this is proof enough that scheduler jobs also fall into the category of workload that can be caged.

Advertisements

Truncated CTAS statements seem to be no longer shortened

Many bloggers have already discussed that Oracle can truncate the text of a “create table as select” statement to 20 characters depending on your version and patch level. This can be a problem in case a CTAS statement is a resource hog, yet you don’t see the SQL text that is needed for troubleshooting. A quick search on My Oracle Support reveals this can happen on 11.2.0.4, 12.1.0.1 and 12.1.0.2 systems unless patched of course. This has been bugging me for quite some time now, and merits a blog post.

Reproducing the offending behaviour

Oracle has provided a number of patches over time to fix this undesirable short-cutting of the SQL text. I wanted to reproduce the issue on 12.1 to demonstrate the effect. To my shame I have to admit that since 12.2 has come out I have somewhat neglected my 12.1 lab system. It was quite a bit out of date, which was useful for this blog post as it will turn out.

One of my 12.1 systems (a two-node RAC) was patched to July 2016:

[oracle@rac12node1 ~]$ opatch lspatches
23177536;Database PSU 12.1.0.2.160719, Oracle JavaVM Component (JUL2016)
23144544;DATABASE BUNDLE PATCH: 12.1.0.2.160719 (23144544)
23520664;OCW Interim patch for 23520664 

OPatch succeeded.

This isn’t great, I prefer my lab systems to be more current. Long story short, this RAC system is about a year and a bit behind on patches. It shall be rectified at the next opportunity!

I am going to connect to a PDB to run the following steps, although it should not matter whether I’m running the test against a non-CDB or PDB.

To ensure repeated execution of my test case I have created a small script:

[oracle@rac12node1 ~]$ cat ctas_test.sql 
set echo on

sho user

alter session set events 'sql_trace level 8';

create table ctastest tablespace users as 
select * from dba_objects where object_id < 1000;

alter session set events 'sql_trace off';

select value from v$diag_info where name like 'Def%';
[oracle@rac12node1 ~]$ 

In the next step I am connecting to the database to run the script:

[oracle@rac12node1 ~]$ sqlplus martin@pdb_srv

SQL*Plus: Release 12.1.0.2.0 Production on Tue Oct 17 20:26:56 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Enter password: 
Last Successful login time: Tue Oct 17 2017 18:52:35 +01:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> @ctas_test
SQL> 
SQL> sho user
USER is "MARTIN"
SQL> 
SQL> alter session set events 'sql_trace level 8';

Session altered.

SQL> 
SQL> create table ctastest tablespace users as 
  2  select * from dba_objects where object_id < 1000;

Table created.

SQL> alter session set events 'sql_trace off';

Session altered.

SQL> 
SQL> select value from v$diag_info where name like 'Def%';

VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/cdb/CDB1/trace/CDB1_ora_16279.trc

And indeed, the SQL trace shows that the statement is truncated:

=====================
PARSING IN CURSOR #140207826820160 len=90 dep=0 uid=102 oct=1 lid=102 
tim=28239487087 hv=3859378539 ad='afafdb58' sqlid='d7y8ssmm0kwbb'
create table ctastest tab
END OF STMT
PARSE #140207826820160:c=282000,e=688749,p=90,cr=6300,cu=0,mis=1,r=0,dep=0,og=1,
 bplh=12630425,tim=28239487087
WAIT #140207826820160: nam='row cache lock' ela= 62 cache id=8 mode=0 request=3
 obj#=656 tim=28239487351

My Oracle Support also mentioned a couple of other places where the command was found to be truncated. I randomly picked one that matters to me, please consider MOS for more locations where the statements were truncated. Your mileage may vary.

SQL> set long 1000000

SQL> select sql_text, sql_fulltext from v$sql where sql_id = 'd7y8ssmm0kwbb';

SQL_TEXT
-------------------------------------------------------------------------------
SQL_FULLTEXT
-------------------------------------------------------------------------------
create table ctastest tablespace users as select * from dba_objects where 
object_id < 1000
create table ctastest tablespace users as select * from dba_objects where 
object_id < 1000

Thankfully the SQL_TEXT and SQL_FULLTEXT show the entire statement, which is good.

Now what about 12.2?

Out of curiosity I repeated the test in 12.2 to see if the problem was fixed, and it actually appears to be the case. Using the same script to connect to the database I get a different behaviour. This time around I am patched to a more recent release:

[oracle@server2 ~]$ opatch lspatches
26609817;DATABASE RELEASE UPDATE: 12.2.0.1.170814 (26609817)

OPatch succeeded.

Let’s try again:

[oracle@server2 ~]$ sqlplus martin@orcl

SQL*Plus: Release 12.2.0.1.0 Production on Tue Oct 17 20:22:15 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Enter password: 
Last Successful login time: Tue Oct 17 2017 20:19:24 +02:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> @ctas_test
SQL>
SQL> sho user
USER is "MARTIN"
SQL>
SQL> alter session set events 'sql_trace level 8';

Session altered.

SQL>
SQL> create table ctastest tablespace users as 
  2  select * from dba_objects where object_id < 1000;

Table created.

SQL>
SQL> alter session set events 'sql_trace off';

Session altered.

SQL>
SQL> select value from v$diag_info where name like 'Def%';

VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_ora_16370.trc

SQL>

Well and it seems the statement is no longer truncated. Looking at ORCL_ora_16370.ora I can find the complete statement:

=====================
PARSING IN CURSOR #140577803287552 len=90 dep=0 uid=74 oct=1 lid=74 
 tim=39078309869 hv=3859378539 ad='64d25e58' sqlid='d7y8ssmm0kwbb'
create table ctastest tablespace users as select * from dba_objects 
where object_id < 1000
END OF STMT
PARSE #140577803287552:c=26826,e=27778,p=0,cr=140,cu=0,mis=1,r=0,dep=0,og=1,
 plh=3155089718,tim=39078309868
...

Wait-what about later patch levels for 12.1?

This could easily have turned out to be “one of these posts” where the information presented is correct, yet the picture isn’t complete. Things change with the Oracle database, and newer patches address old problems. Just to be sure what I said is accurate at least at the time of writing I created a new VM with the August 2017 patch set:

[oracle@server1 ~]$ opatch lspatches
26027162;Database PSU 12.1.0.2.170718, Oracle JavaVM Component (JUL2017)
26609783;Database Patch Set Update : 12.1.0.2.170814 (26609783)
18705302;

OPatch succeeded.

And interestingly, the issue with the truncated SQL statement in SQL traces disappeared:

=====================
PARSING IN CURSOR #140433649086704 len=90 dep=0 uid=111 oct=1 lid=111 
tim=797240178 hv=3859378539 ad='1b1add420' sqlid='d7y8ssmm0kwbb'
create table ctastest tablespace users as select * from dba_objects
 where object_id < 1000
END OF STMT
PARSE #140433649086704:c=165843,e=184715,p=197,cr=5489,cu=0,mis=1,r=0,dep=0,og=1,
 plh=12630425,tim=797240177
...

Great stuff: the problem is fixed in the current version for 12.1.0.2 and 12.2.0.1. This is seriously good news for me, especially the part about 12.1.

Changes to the Oracle preinstall RPMs in OL 7.3 and OL 7.4

For quite some time now Oracle has documented the use of the so-called preinstall RPMs to prepare Oracle Linux for the installation of the Oracle database software. I think that’s a great idea if the settings applied by the RPM fit your environment. If I find the time, I’ll write a blog post about what it does specifically in a little while. It definitely fits my lab environment, and I regularly kickstart my OL 7 VMs specifying the preinstall RPM in the %packages section.

When upgrading the current base image from Oracle Linux 7.2 to Oracle 7.4/Oracle 12.2 I noticed a few changes to the preinstall RPMs.

IMPORTANT NOTE If you find this post via an Internet search engine please bear in mind that Oracle could have changed the preinstall RPM since this article was posted in September 2017. Make sure you check with the most current version to see the problem I ran into still exists

Different name

The first surprise came when I changed the virt-install command to reference the Oracle Linux 7.4 ISO instead of 7.2. During the installation session a warning appeared stating that there wasn’t a package named oracle-rdbms-server-12cR1-preinstall anywhere to be found. Really? OK … that was a bit strange. I went straight off to check the public-yum repository for Oracle Linux 7.4 and indeed, there wasn’t the RPM I was looking for. All I could find was a package named oracle-database-server-12cR2-preinstall. All right then, I’ll use that instead. A change to the kickstart file was all that needed to be done, and subsequent installations completed ok.

Except for this other problem …

No more dependencies on the C compiler suite

The other night I wanted to apply a patch to the 12.2 lab cluster using opatchauto. The patching process ran into an error on node 2, and then failed completely trying to roll the patch back. Usually that’s a sad story (and the reason why you create backups before patching) but this time I was lucky and didn’t have to restore. Well, lucky in a way.

Going through the opatch log files (the error reported had something to do with a failure to relink a target in ins_rdbms.mk) I noticed that Oracle complained about not finding gcc. That struck me as odd, and I thought I’d actually seen gcc installed on my 7.2 base image with the 12.1 preinstall RPM.

A quick check reveals that the 12.1 preinstall RPM (taken from the Oracle Linux 7.2 base repository that I used previously) actually defines a dependency on gcc:

[oracle@server1 ~]$ rpm -qpi --requires /tmp/oracle-rdbms-server-12cR1-preinstall-1.0-4.el7.x86_64.rpm
Name        : oracle-rdbms-server-12cR1-preinstall
Version     : 1.0
Release     : 4.el7
Architecture: x86_64
Install Date: (not installed)
Group       : Test Environment/Libraries
Size        : 44346
License     : GPLv2
Signature   : RSA/SHA256, Wed 14 Oct 2015 02:18:22 AM EDT, Key ID 72f97b74ec551f03
Source RPM  : oracle-rdbms-server-12cR1-preinstall-1.0-4.el7.src.rpm
Build Date  : Wed 14 Oct 2015 02:18:19 AM EDT
Build Host  : x86-ol7-builder-01.us.oracle.com
Relocations : (not relocatable)
Vendor      : Oracle
Summary     : Sets the system for Oracle Database single instance 
              and Real Application Cluster install for Oracle Linux 7
Description :
The Oracle Preinstallation RPM package installs software packages and 
sets system parameters required for Oracle Database single instance and 
Oracle Real Application Clusters installations for Oracle Linux Release 7

Files affected: /etc/sysctl.conf, /boot/grub/menu.lst OR /boot/grub2/grub.cfg
Files added: /etc/security/limits.d/oracle-rdbms-server-12cR1-preinstall.conf
/bin/bash
/bin/sh
/bin/sh
/bin/sh
/bin/sh
/etc/redhat-release
bind-utils
binutils
compat-libcap1
compat-libstdc++-33
config(oracle-rdbms-server-12cR1-preinstall) = 1.0-4.el7
ethtool
gcc
gcc-c++
glibc
glibc-devel
initscripts
kernel-uek
ksh
libaio
libaio-devel
libgcc
libstdc++
libstdc++-devel
make
module-init-tools
nfs-utils
openssh-clients
pam
procps
psmisc
rpmlib(CompressedFileNames) <= 3.0.4-1
rpmlib(FileDigests) <= 4.6.0-1
rpmlib(PayloadFilesHavePrefix) <= 4.0-1
smartmontools
sysstat
util-linux-ng
xorg-x11-utils
xorg-x11-xauth
rpmlib(PayloadIsXz) <= 5.2-1

As you can see, gcc (and even gcc-c++) feature quite prominently in the list of requirements.

The change must have come with Oracle Linux 7.3, but I haven’t noticed it until now. I checked the Oracle Linux 7.4 base repository on public-yum.oracle.com and noticed the following:

  • The 11g Release 2 preinstall RPM is gone (it was still available in the OL 7.3 repository)
  • The 12c Release 1 preinstall RPM is gone, too (it had already disappeared in OL 7.3 but I hadn’t noticed)
  • There is a new RPM for 12.2 named oracle-database-server-12cR2-preinstall

Interestingly enough all 3 preinstall RPMs (11.2/12.1/12.2) exist in the “latest” channel for OL7.

Looking at the new oracle-database-server-12cR2-preinstall.x86_64 RPM I noticed that gcc wasn’t referenced anymore:

[oracle@server1 ~]$ rpm -qi --requires oracle-database-server-12cR2-preinstall
Name        : oracle-database-server-12cR2-preinstall
Version     : 1.0
Release     : 3.el7
Architecture: x86_64
Install Date: Thu 31 Aug 2017 04:38:58 AM EDT
Group       : Test Environment/Libraries
Size        : 56561
License     : GPLv2
Signature   : RSA/SHA256, Mon 10 Jul 2017 06:27:07 AM EDT, Key ID 72f97b74ec551f03
Source RPM  : oracle-database-server-12cR2-preinstall-1.0-3.el7.src.rpm
Build Date  : Mon 10 Jul 2017 06:26:59 AM EDT
Build Host  : x86-ol7-builder-02.us.oracle.com
Relocations : (not relocatable)
Vendor      : Oracle
Summary     : Sets the system for Oracle Database single instance and
              Real Application Cluster install for Oracle Linux 7
Description :
The Oracle Preinstallation RPM package installs software packages and 
sets system parameters required for Oracle Database single instance and 
Oracle Real Application Clusters installations for Oracle Linux Release 7

Files affected: /etc/sysctl.conf, /boot/grub/menu.lst OR /boot/grub2/grub.cfg
Files added: /etc/security/limits.d/oracle-database-server-12cR2-preinstall.conf
/bin/bash
/bin/sh
/bin/sh
/bin/sh
/bin/sh
/etc/redhat-release
bind-utils
binutils
compat-libcap1
compat-libstdc++-33
config(oracle-database-server-12cR2-preinstall) = 1.0-3.el7
ethtool
glibc
glibc-devel
initscripts
kernel-uek
ksh
libaio
libaio-devel
libgcc
libstdc++
libstdc++-devel
make
module-init-tools
net-tools
nfs-utils
openssh-clients
oraclelinux-release
pam
procps
psmisc
rpmlib(CompressedFileNames) <= 3.0.4-1
rpmlib(FileDigests) <= 4.6.0-1
rpmlib(PayloadFilesHavePrefix) <= 4.0-1
smartmontools
sysstat
unzip
util-linux-ng
xorg-x11-utils
xorg-x11-xauth
rpmlib(PayloadIsXz) <= 5.2-1

Interesting! I thought I’d post this just to let you know.

When planning on installing Enterprise Manager agents you might also notice the absence of gcc: the agent deployment prerequisite check for OEM 13.2 complained for this exact reason.

Full comparison between 12.1 and 12.2

I ran a quick diff to compare the dependencies between oracle-database-server-12cR2-preinstall-1.0-3.el7.x86_64 and oracle-rdbms-server-12cR1-preinstall-1.0-4.el7.x86_64.rpm. Here is the result:

[oracle@server1 ~]$ rpm -q --requires oracle-database-server-12cR2-preinstall > /tmp/12cR2-preinstall.txt
[oracle@server1 ~]$ rpm -qp --requires oracle-rdbms-server-12cR1-preinstall-1.0-4.el7.x86_64.rpm > /tmp/12cR1-preinstall.txt

[oracle@server1 ~]$ diff --width 100 -y /tmp/12cR1-preinstall.txt /tmp/12cR2-preinstall.txt 
/bin/bash                                       /bin/bash
/bin/sh                                         /bin/sh
/bin/sh                                         /bin/sh
/bin/sh                                         /bin/sh
/bin/sh                                         /bin/sh
/etc/redhat-release                             /etc/redhat-release
bind-utils                                      bind-utils
binutils                                        binutils
compat-libcap1                                  compat-libcap1
compat-libstdc++-33                             compat-libstdc++-33
config(oracle-rdbms-server-12cR1-preinstall)  | config(oracle-database-server-12cR2-preinstal
ethtool                                         ethtool
gcc                                           <
gcc-c++                                       <
glibc                                           glibc
glibc-devel                                     glibc-devel
initscripts                                     initscripts
kernel-uek                                      kernel-uek
ksh                                             ksh
libaio                                          libaio
libaio-devel                                    libaio-devel
libgcc                                          libgcc
libstdc++                                       libstdc++
libstdc++-devel                                 libstdc++-devel
make                                            make
module-init-tools                               module-init-tools
                                              > net-tools
nfs-utils                                       nfs-utils
openssh-clients                                 openssh-clients
                                              > oraclelinux-release
pam                                             pam
procps                                          procps
psmisc                                          psmisc
rpmlib(CompressedFileNames) <= 3.0.4-1          rpmlib(CompressedFileNames) <= 3.0.4-1
rpmlib(FileDigests) <= 4.6.0-1                  rpmlib(FileDigests) <= 4.6.0-1
rpmlib(PayloadFilesHavePrefix) <= 4.0-1         rpmlib(PayloadFilesHavePrefix) <= 4.0-1
smartmontools                                   smartmontools
sysstat                                         sysstat
                                              > unzip
util-linux-ng                                   util-linux-ng
xorg-x11-utils                                  xorg-x11-utils
xorg-x11-xauth                                  xorg-x11-xauth
rpmlib(PayloadIsXz) <= 5.2-1                    rpmlib(PayloadIsXz) <= 5.2-1

It came as a bit of a surprise to see the (deprecated) net-tools again. Maybe it has to do with OSWatcher, as it likes netstat for example. I personally made the switch from net-tools to iproute some time ago and haven’t missed anything. I appreciate the addition of unzip to the list of required packages.

12.2 New Feature: the FLEX ASM disk group part 5

Some time ago I had a very interesting twitter conversation after publishing the first part of this series. The question was whether using ASM templates, which admittedly exist since Oracle 10.1, didn’t provide similar functionality as Flex Disk Groups. In other words, wouldn’t using ASM templates allow you to have high redundancy files on normal redundancy disk groups anyway?

This question has been answered by Alex Fatkulin in a blog post some time ago. In this post I would like to replay his test with my 12.2 setup. Initially I had hoped to compare the approach using ASM templates with the Flex ASM Disk Group but the post has become too long again … The actual comparison will be done with the next instalment of the series.

Templates

You may not be aware of the fact that you are using ASM templates, but you do. Each disk group has a set of system-generated, common ASM templates. Consider this example for my current lab environment (Oracle 12.2.0.1). Queries and commands are executed as SYSASM while connected to the ASM instance unless stated otherwise:

SQL> select b.name, a.redundancy, a.stripe, a.system
  2   from v$asm_template a, v$asm_diskgroup b
  3  where a.group_number = b.group_number
  4    and a.name = 'DATAFILE';

NAME                           REDUND STRIPE S
------------------------------ ------ ------ -
DATA                           MIRROR COARSE Y
MGMT                           UNPROT COARSE Y
OCR                            MIRROR COARSE Y
RECO                           UNPROT COARSE Y
FLEX                           MIRROR COARSE Y

Templates, among other disk-group meta-information, define how a supported file type is stored in ASM using 2 criteria:

  • Mirroring
  • Striping

You can see there are plenty of templates, one for each supported ASM file type:

SQL> select b.name as dg_name, a.name as template_name, a.system
  2    from v$asm_template a, v$asm_diskgroup b
  3  where a.group_number = b.group_number
  4   and b.name = 'DATA';

DG_NAME 		       TEMPLATE_NAME		      S
------------------------------ ------------------------------ -
DATA			       PARAMETERFILE		      Y
DATA			       ASMPARAMETERFILE 	      Y
DATA			       OCRFILE			      Y
DATA			       DATAGUARDCONFIG		      Y
DATA			       AUDIT_SPILLFILES 	      Y
DATA			       AUTOLOGIN_KEY_STORE	      Y
DATA			       KEY_STORE		      Y
DATA			       FLASHBACK		      Y
DATA			       CHANGETRACKING		      Y
DATA			       XTRANSPORT		      Y
DATA			       AUTOBACKUP		      Y
DATA			       INCR XTRANSPORT BACKUPSET      Y
DATA			       XTRANSPORT BACKUPSET	      Y
DATA			       BACKUPSET		      Y
DATA			       TEMPFILE 		      Y
DATA			       DATAFILE 		      Y
DATA			       ONLINELOG		      Y
DATA			       ARCHIVELOG		      Y
DATA			       FLASHFILE		      Y
DATA			       CONTROLFILE		      Y
DATA			       DUMPSET			      Y
DATA			       VOTINGFILE		      Y

22 rows selected.

Does that strike any resemblance with v$asm_filegroup_property? It does so for me. Except that within a Flex ASM Disk Group properties are defined per File Group. And there are different file groups per (N)CDB, or PDB. With other ASM disk group types the mapping is global.

Custom Templates

According to the ASM documentation (Storage Administrator’s Guide 12c Release 2 chapter 5 Administering Oracle ASM Files, Directories, and Templates) a template can be used to define attributes for file types.

If there’s a column named SYSTEM in v$asm_template, there surely is a way to create one’s own templates. And this is where I circle back to the original question: can I have high-redundancy files in a normal redundancy disk group?

You sure can! I will use the DATA disk group for this, which is created using NORMAL redundancy. Here is some useful background information:

SQL> select group_number, name, type, compatibility, database_compatibility
  2  from v$asm_diskgroup;

GROUP_NUMBER NAME       TYPE   COMPATIBILITY   DATABASE_COMPAT
------------ ---------- ------ --------------- ---------------
           1 DATA       NORMAL 12.2.0.1.0      12.2.0.1.0
           2 MGMT       EXTERN 12.2.0.1.0      10.1.0.0.0
           3 OCR        NORMAL 12.2.0.1.0      10.1.0.0.0
           4 RECO       EXTERN 12.2.0.1.0      10.1.0.0.0
           5 FLEX       FLEX   12.2.0.1.0      12.2.0.1.0

SQL> select count(*) from v$asm_disk where group_number = 
  2   (select group_number from v$asm_diskgroup where name = 'DATA');

  COUNT(*)
----------
         3

Each new datafile on the DATA disk group will be created based on the default template:

SQL> select b.name as dg_name, a.redundancy, a.stripe, a.system
  2  from v$asm_template a, v$asm_diskgroup b
  3  where a.group_number = b.group_number
  4   and a.name = 'DATAFILE'
  5   and b.name = 'DATA';

DG_NAME                        REDUND STRIPE S
------------------------------ ------ ------ -
DATA                           MIRROR COARSE Y

In other words, each extent is mirrored, and the striping is coarse. Again, I won’t be touching the striping mechanism as explained in an earlier post.

To enable high redundancy another template must be created, which is simple:

SQL> alter diskgroup data add template high_red_on_normal_dg attribute (high);

Diskgroup altered.

SQL> select b.name as dg_name, a.redundancy, a.stripe, a.system
  2  from v$asm_template a, v$asm_diskgroup b
  3  where a.group_number = b.group_number
  4   and a.name = 'HIGH_RED_ON_NORMAL_DG'
  5   and b.name = 'DATA'
  6  /

DG_NAME                        REDUND STRIPE S
------------------------------ ------ ------ -
DATA                           HIGH   COARSE N

Back in the RDBMS instance, I can now make use of that template:

SQL> create tablespace high_red_tbs 
  2  datafile '+data(high_red_on_normal_DG)' size 50m;

Tablespace created.

SQL> select name from v$datafile where name like '%high_red_tbs%';

NAME
----------------------------------------------------------------------------------
+DATA/CDB/586EF9CC43B5474DE0530A64A8C0F287/DATAFILE/high_red_tbs.286.953971189

The question is: is this file created with high redundancy?

SQL> select redundancy, type, remirror, redundancy_lowered
  2  from v$asm_file where file_number = 286 and incarnation = 953971189;

REDUND TYPE            R R
------ --------------- - -
HIGH   DATAFILE        N U

That looks like a yes to me. Using a different, random other file from the disk group shows that other files use normal redundancy:

SQL> select name from v$asm_alias 
  2   where file_number = 261 and file_incarnation = 953928133;

NAME
------------------------------------------------------------
UNDO_2.261.953928133

SQL> select redundancy, type, remirror, redundancy_lowered
  2  from v$asm_file where file_number = 261 and incarnation = 953928133;

REDUND TYPE            R R
------ --------------- - -
MIRROR DATAFILE        N U

But does it help?

Now I have high redundancy files on a normal redundancy disk group, which gives me extra protection from disk corruption. From an availability point of view you don’t win much though, as Alex has already pointed out. Removing 2 of the 3 disks that make up the DATA disk group should result in a dismount of the disk group (which a true high redundancy disk would survive). Here is proof.

The disk failures are visible in many places. For example, in /var/log/messages

Sep  6 10:42:37 rac122pri1 kernel: sd 3:0:0:0: [sdg] Synchronizing SCSI cache
Sep  6 10:42:37 rac122pri1 kernel: sd 3:0:0:0: [sdg] Synchronize Cache(10)
 failed: Result: hostbyte=DID_OK driverbyte=DRIVER_SENSE
Sep  6 10:42:37 rac122pri1 kernel: sd 3:0:0:0: [sdg] Sense Key : Illegal  
 Request [current] 
Sep  6 10:42:37 rac122pri1 kernel: sd 3:0:0:0: [sdg] Add. Sense: 
 Logical unit not supported
Sep  6 10:42:41 rac122pri1 kernel: sd 4:0:0:2: [sdl] Synchronizing SCSI cache
Sep  6 10:42:41 rac122pri1 kernel: sd 4:0:0:2: [sdl] Synchronize Cache(10) 
 failed: Result: hostbyte=DID_OK driverbyte=DRIVER_SENSE
Sep  6 10:42:41 rac122pri1 kernel: sd 4:0:0:2: [sdl] Sense Key : Illegal 
 Request [current] 
Sep  6 10:42:41 rac122pri1 kernel: sd 4:0:0:2: [sdl] Add. Sense: Logical 
 unit not supported

And the ASM instance’s alert.log:

...
ERROR: no read quorum in group: required 1936606968, found 1937207795 disks
ERROR: Could not read PST for grp 1. Force dismounting the disk group.
NOTE: detected orphaned client id 0x10001.
2017-09-06 10:42:45.101000 +01:00
Errors in file /u01/app/oracle/diag/asm/+asm/+ASM1/trace/+ASM1_rbal_2730.trc:
ORA-15130: diskgroup "" is being dismounted
GMON dismounting group 1 at 96 for pid 37, osid 32385
NOTE: Disk DATA_0000 in mode 0x1 marked for de-assignment
NOTE: Disk DATA_0001 in mode 0x7f marked for de-assignment
NOTE: Disk DATA_0002 in mode 0x7f marked for de-assignment
SUCCESS: diskgroup DATA was dismounted
SUCCESS: alter diskgroup DATA dismount force /* ASM SERVER:2133858021 */
SUCCESS: ASM-initiated MANDATORY DISMOUNT of group DATA
NOTE: diskgroup resource ora.DATA.dg is offline

It is truly gone:

SQL> select group_number, name, state from v$asm_diskgroup where name = 'DATA';

GROUP_NUMBER NAME                           STATE
------------ ------------------------------ -----------
           0 DATA                           DISMOUNTED

Summary: ASM Templates

When I initially worked out how to use custom templates and creating high redundancy files in a normal redundancy disk group I was all excited. However during testing disk failure that excitement made way to a more rational assessment of the situation.

So while you might gain on data integrity you lose on storage (triple mirroring requires more space) and don’t have added benefit on availability.

In the next post I’ll repeat this test with my FLEX ASM Disk Group.

12.2 New Feature: the FLEX ASM disk group part 4

Flex Disk Group Properties

In the previous 3 parts I shared my investigation into ASM Flex Disk Groups, Quota Groups, File Groups, and how Quota Groups actually enforce space limits. What I haven’t discussed yet was changing properties of a File Group and the effects thereof. Properties I have in mind are related to the protection level, as discussed in the official documentation-Automatic Storage Management Administrator’s Guide, Administering Oracle ASM Disk Groups. There are of course other properties as well (and you’ll find a link to all of the modifiable properties later in this post), but they are out of scope for this investigation.

A disk group with Flex Redundancy can be set up with all protection levels (3-way mirror, 2-way mirror, unprotected), and by default uses 2-way mirroring. Unlike other types of disk groups, you can change the protection levels for individual (pluggable) databases within the Flex Disk group. This is best shown with an example. Continuing the story from my previous blog posts, here’s the setup again for your convenience.

SQL> select filegroup_number,name,guid from v$asm_filegroup

FILEGROUP_NUMBER NAME                 GUID
---------------- -------------------- --------------------------------
               0 DEFAULT_FILEGROUP
               1 CDB_CDB$ROOT         4700A987085A3DFAE05387E5E50A8C7B
               2 CDB_PDB$SEED         536DF51E8E28221BE0534764A8C0FD81
               3 PDB1                 537B677EF8DA0F1AE0534764A8C05729
               4 ORCL_CDB$ROOT        4700A987085A3DFAE05387E5E50A8C7B
               5 ORCL_PDB$SEED        537E63B952183748E0534764A8C09A7F
               6 PDB1_0001            537EB5B87E62586EE0534764A8C05530

7 rows selected. 

The above listing shows all my File Groups in my ASM instance. This post is about changing attributes of filegroup number 6, PDB1_0001. It will be important to understand which files pertain to the filegroup later; here’s the list:

SQL> select file_number,bytes,space,type,redundancy,redundancy_lowered,striped,remirror
  2  from v$asm_file where filegroup_number = 6;
  
FILE_NUMBER  BYTES      SPACE      TYPE      REDUNDANCY  REDUNDANCY_LOWERED  STRIPED  REMIRROR
309          104865792  218103808  DATAFILE  MIRROR      U                   COARSE   N
310          262152192  541065216  DATAFILE  MIRROR      U                   COARSE   N
311          419438592  859832320  DATAFILE  MIRROR      U                   COARSE   N
312          67117056   142606336  TEMPFILE  MIRROR      U                   COARSE   N

The documentation is correct: the default redundancy for the datafiles and tempfile is “mirror”.

Filegroup properties

The ability to change redundancy and other properties within the disk group hinges on the fact that you have File Groups. Properties that belong to a File Group can be listed either via the SQL interface, or asmcmd. The latter is shown first:

ASMCMD> lsfg -G flex --filegroup PDB1_0001
File Group  Disk Group  Property    Value   File Type                  
PDB1_0001   FLEX        PRIORITY    MEDIUM                             
PDB1_0001   FLEX        STRIPING    COARSE  CONTAINER                  
PDB1_0001   FLEX        STRIPING    FINE    CONTROLFILE                
PDB1_0001   FLEX        REDUNDANCY  MIRROR  DATAFILE                   
PDB1_0001   FLEX        STRIPING    COARSE  DATAFILE                   
PDB1_0001   FLEX        REDUNDANCY  MIRROR  ONLINELOG                  
PDB1_0001   FLEX        STRIPING    COARSE  ONLINELOG                  
PDB1_0001   FLEX        REDUNDANCY  MIRROR  ARCHIVELOG                 
PDB1_0001   FLEX        STRIPING    COARSE  ARCHIVELOG                 
PDB1_0001   FLEX        REDUNDANCY  MIRROR  TEMPFILE                   
PDB1_0001   FLEX        STRIPING    COARSE  TEMPFILE                   
PDB1_0001   FLEX        REDUNDANCY  MIRROR  BACKUPSET                  
PDB1_0001   FLEX        STRIPING    COARSE  BACKUPSET                  
PDB1_0001   FLEX        REDUNDANCY  MIRROR  PARAMETERFILE              
PDB1_0001   FLEX        STRIPING    COARSE  PARAMETERFILE              
PDB1_0001   FLEX        REDUNDANCY  MIRROR  DATAGUARDCONFIG            
PDB1_0001   FLEX        STRIPING    COARSE  DATAGUARDCONFIG            
PDB1_0001   FLEX        REDUNDANCY  MIRROR  CHANGETRACKING             
PDB1_0001   FLEX        STRIPING    COARSE  CHANGETRACKING             
PDB1_0001   FLEX        REDUNDANCY  MIRROR  FLASHBACK                  
PDB1_0001   FLEX        STRIPING    COARSE  FLASHBACK                  
PDB1_0001   FLEX        REDUNDANCY  MIRROR  DUMPSET                    
PDB1_0001   FLEX        STRIPING    COARSE  DUMPSET                    
PDB1_0001   FLEX        REDUNDANCY  MIRROR  AUTOBACKUP                 
PDB1_0001   FLEX        STRIPING    COARSE  AUTOBACKUP                 
PDB1_0001   FLEX        REDUNDANCY  MIRROR  VOTINGFILE                 
PDB1_0001   FLEX        STRIPING    COARSE  VOTINGFILE                 
PDB1_0001   FLEX        REDUNDANCY  MIRROR  OCRFILE                    
PDB1_0001   FLEX        STRIPING    COARSE  OCRFILE                    
PDB1_0001   FLEX        REDUNDANCY  MIRROR  ASMVOL                     
PDB1_0001   FLEX        STRIPING    COARSE  ASMVOL                     
PDB1_0001   FLEX        REDUNDANCY  MIRROR  ASMVDRL                    
PDB1_0001   FLEX        STRIPING    COARSE  ASMVDRL                    
PDB1_0001   FLEX        REDUNDANCY  MIRROR  OCRBACKUP                  
PDB1_0001   FLEX        STRIPING    COARSE  OCRBACKUP                  
PDB1_0001   FLEX        REDUNDANCY  MIRROR  FLASHFILE                  
PDB1_0001   FLEX        STRIPING    COARSE  FLASHFILE                  
PDB1_0001   FLEX        REDUNDANCY  MIRROR  XTRANSPORT BACKUPSET       
PDB1_0001   FLEX        STRIPING    COARSE  XTRANSPORT BACKUPSET       
PDB1_0001   FLEX        REDUNDANCY  MIRROR  AUDIT_SPILLFILES           
PDB1_0001   FLEX        STRIPING    COARSE  AUDIT_SPILLFILES           
PDB1_0001   FLEX        REDUNDANCY  MIRROR  INCR XTRANSPORT BACKUPSET  
PDB1_0001   FLEX        STRIPING    COARSE  INCR XTRANSPORT BACKUPSET  
PDB1_0001   FLEX        REDUNDANCY  MIRROR  KEY_STORE                  
PDB1_0001   FLEX        STRIPING    COARSE  KEY_STORE                  
PDB1_0001   FLEX        REDUNDANCY  MIRROR  AUTOLOGIN_KEY_STORE        
PDB1_0001   FLEX        STRIPING    COARSE  AUTOLOGIN_KEY_STORE        
PDB1_0001   FLEX        REDUNDANCY  MIRROR  CONTAINER                  
PDB1_0001   FLEX        REDUNDANCY  HIGH    CONTROLFILE                
ASMCMD> 

This output shows 2 properties per ASM-supported file type: redundancy and striping. I really only care about redundancy, and I haven’t ever touched the striping property. Quoting the ASM Administrator’s guide, chapter Administering Oracle ASM Disk Groups, section Managing Oracle ASM Flex Disk Groups about the STRIPING property:

This is a file type property, and is set for each file type. Usually the default value for each file type is sufficient and is not changed

I’m happy to go with that.

The SQL interface can provide the same information, and here is the equivalent output:

SQL> select file_type, name, value from v$asm_filegroup_property where filegroup_number = 6;

FILE_TYPE                      NAME                           VALUE
------------------------------ ------------------------------ ------------------------------
                               PRIORITY                       MEDIUM
CONTROLFILE                    REDUNDANCY                     HIGH
CONTROLFILE                    STRIPING                       FINE
DATAFILE                       REDUNDANCY                     MIRROR
DATAFILE                       STRIPING                       COARSE
ONLINELOG                      REDUNDANCY                     MIRROR
ONLINELOG                      STRIPING                       COARSE
ARCHIVELOG                     REDUNDANCY                     MIRROR
ARCHIVELOG                     STRIPING                       COARSE
TEMPFILE                       REDUNDANCY                     MIRROR
TEMPFILE                       STRIPING                       COARSE
BACKUPSET                      REDUNDANCY                     MIRROR
BACKUPSET                      STRIPING                       COARSE
PARAMETERFILE                  REDUNDANCY                     MIRROR
PARAMETERFILE                  STRIPING                       COARSE
DATAGUARDCONFIG                REDUNDANCY                     MIRROR
DATAGUARDCONFIG                STRIPING                       COARSE
CHANGETRACKING                 REDUNDANCY                     MIRROR
CHANGETRACKING                 STRIPING                       COARSE
FLASHBACK                      REDUNDANCY                     MIRROR
FLASHBACK                      STRIPING                       COARSE
DUMPSET                        REDUNDANCY                     MIRROR
DUMPSET                        STRIPING                       COARSE
AUTOBACKUP                     REDUNDANCY                     MIRROR
AUTOBACKUP                     STRIPING                       COARSE
VOTINGFILE                     REDUNDANCY                     MIRROR
VOTINGFILE                     STRIPING                       COARSE
OCRFILE                        REDUNDANCY                     MIRROR
OCRFILE                        STRIPING                       COARSE
ASMVOL                         REDUNDANCY                     MIRROR
ASMVOL                         STRIPING                       COARSE
ASMVDRL                        REDUNDANCY                     MIRROR
ASMVDRL                        STRIPING                       COARSE
OCRBACKUP                      REDUNDANCY                     MIRROR
OCRBACKUP                      STRIPING                       COARSE
FLASHFILE                      REDUNDANCY                     MIRROR
FLASHFILE                      STRIPING                       COARSE
XTRANSPORT BACKUPSET           REDUNDANCY                     MIRROR
XTRANSPORT BACKUPSET           STRIPING                       COARSE
AUDIT_SPILLFILES               REDUNDANCY                     MIRROR
AUDIT_SPILLFILES               STRIPING                       COARSE
INCR XTRANSPORT BACKUPSET      REDUNDANCY                     MIRROR
INCR XTRANSPORT BACKUPSET      STRIPING                       COARSE
KEY_STORE                      REDUNDANCY                     MIRROR
KEY_STORE                      STRIPING                       COARSE
AUTOLOGIN_KEY_STORE            REDUNDANCY                     MIRROR
AUTOLOGIN_KEY_STORE            STRIPING                       COARSE
CONTAINER                      REDUNDANCY                     MIRROR
CONTAINER                      STRIPING                       COARSE

49 rows selected.

In addition to v$asm_file you can also query the new view v$asm_filegroup_file for information about files in File Groups:

SQL> select filegroup_number, file_number, incarnation
  2  from v$asm_filegroup_file
  3  where filegroup_number = 6
  4  order by file_number;

FILEGROUP_NUMBER FILE_NUMBER INCARNATION
---------------- ----------- -----------
               6         309   948464269
               6         310   948464269
               6         311   948464269
               6         312   948464283

SQL> 

FILE_NUMBER and INCARNATION can be used to link back to v$asm_file by the way.

Back to the blog post: I wanted to increase the redundancy level from normal redundancy to high redundancy, but only within filegroup 6. There are a number of useful attributes in v$asm_file that provide information about the size, type, current redundancy, stripe levels and whether a re-mirror operation is taking place.

Before making any changes, this is what is looks like:

SQL> select file_number,bytes,space,type,redundancy,redundancy_lowered,striped,remirror
  2  from v$asm_file where filegroup_number = 6;

FILE_NUMBER      BYTES      SPACE TYPE                 REDUND R STRIPE R
----------- ---------- ---------- -------------------- ------ - ------ -
        309  104865792  218103808 DATAFILE             MIRROR U COARSE N
        310  262152192  541065216 DATAFILE             MIRROR U COARSE N
        311  419438592  859832320 DATAFILE             MIRROR U COARSE N
        312   67117056  142606336 TEMPFILE             MIRROR U COARSE N

SQL>

Files 309 through 312 use a redundancy of MIRROR, which is 2-way mirroring aka normal redundancy. Let’s try and change this and see what happens.

Altering the datafile.redundancy

This is where the action starts for real. All the filegroup properties that can be changed are documented in the Automatic Storage Management Administrator’s Guide, Administering Oracle ASM Disk Groups chapter, section Managing Oracle ASM Flex Disk Groups. Using the documented example, I can change the redundancy of my data files in filegroup 6:

SQL> alter diskgroup flex modify filegroup PDB1_0001 set 'datafile.redundancy'='high';

Diskgroup altered.

This command, like all others that change the properties of storage, must be executed from the ASM instance as SYSASM, or else it fails as shown here when I tried to execute if from the RDBMS instance.

SQL> alter diskgroup flex modify filegroup PDB1_0001 set 'datafile.redundancy' = 'high';

Error starting at line : 1 in command -
alter diskgroup flex modify filegroup PDB1_0001 set 'datafile.redundancy' = 'high'
Error report -
ORA-15000: command disallowed by current instance type
15000. 00000 -  "command disallowed by current instance type"
*Cause:    The user has issued a command to a conventional RDBMS instance
           that is only appropriate for an ASM instance. Alternatively, the
           user has issued a command to an ASM instance that is only
           appropriate for an RDBMS instance.
*Action:   Connect to the correct instance type and re-issue the command.
SQL> 

Immediately after this command completes data files in filegroup 6 are re-mirrored. While the re-mirror operation is ongoing (see flag remirror=’Y’) the redundancy is still set to a value of MIRROR. Only when it has completed (remirror = ‘N’) is the redundancy changed to HIGH. Note that file 312 in the filegroup still is set to NORMAL redundancy. On second look you’ll notice it’s ok because it is a tempfile, not a data file. And I only asked for datafiles to be protected by high redundancy.

SQL> select file_number,bytes,space,type,redundancy,redundancy_lowered,striped,remirror
  2  from v$asm_file where filegroup_number = 6;

FILE_NUMBER      BYTES      SPACE TYPE                 REDUND R STRIPE R
----------- ---------- ---------- -------------------- ------ - ------ -
        309  104865792  339738624 DATAFILE             MIRROR U COARSE Y
        310  262152192  805306368 DATAFILE             MIRROR U COARSE Y
        311  419438592 1283457024 DATAFILE             MIRROR U COARSE Y
        312   67117056  142606336 TEMPFILE             MIRROR U COARSE N

SQL> select file_number,bytes,space,type,redundancy,redundancy_lowered,striped,remirror
  2  from v$asm_file where filegroup_number = 6;

FILE_NUMBER      BYTES      SPACE TYPE                 REDUND R STRIPE R
----------- ---------- ---------- -------------------- ------ - ------ -
        309  104865792  339738624 DATAFILE             HIGH   U COARSE N
        310  262152192  805306368 DATAFILE             HIGH   U COARSE N
        311  419438592 1283457024 DATAFILE             HIGH   U COARSE N
        312   67117056  142606336 TEMPFILE             MIRROR U COARSE N

To complete the post I’ll list the ASM instance’s alert.log as it’s quite verbose and I find it interesting to see what activities ASM performs.

SQL> alter diskgroup flex modify filegroup PDB1_0001 set 'datafile.redundancy'='high'
NOTE: updated format of group 5 file 311 for 3-way mirroring
NOTE: updated redundancy of group 5 file 311 to 3-way mirrored (remirror 0x4)
NOTE: updated format of group 5 file 310 for 3-way mirroring
NOTE: updated redundancy of group 5 file 310 to 3-way mirrored (remirror 0x4)
NOTE: updated format of group 5 file 309 for 3-way mirroring
NOTE: updated redundancy of group 5 file 309 to 3-way mirrored (remirror 0x4)
NOTE: GroupBlock outside rolling migration privileged region
NOTE: client +ASM1:+ASM:rac122pri no longer has group 5 (FLEX) mounted
NOTE: client +ASM1:+ASM:rac122pri no longer has group 3 (DATA) mounted
NOTE: client +ASM1:+ASM:rac122pri no longer has group 2 (MGMT) mounted
NOTE: requesting all-instance membership refresh for group=5
NOTE: membership refresh pending for group 5/0x4718f00c (FLEX)
GMON querying group 5 at 835 for pid 25, osid 11576
SUCCESS: refreshed membership for 5/0x4718f00c (FLEX)
SUCCESS: alter diskgroup flex modify filegroup PDB1_0001 set 'datafile.redundancy'='high'
2017-07-06 13:17:47.169000 +01:00
NOTE: Attempting voting file refresh on diskgroup FLEX
NOTE: Refresh completed on diskgroup FLEX. No voting file found.
NOTE: starting rebalance of group 5/0x4718f00c (FLEX) at power 1
NOTE: starting process ARBA
Starting background process ARBA
ARBA started with pid=33, OS id=9904
NOTE: starting process ARB0
Starting background process ARB0
ARB0 started with pid=48, OS id=9906
NOTE: assigning ARBA to group 5/0x4718f00c (FLEX) to compute estimates
NOTE: assigning ARB0 to group 5/0x4718f00c (FLEX) with 1 parallel I/O
2017-07-06 13:18:29.554000 +01:00
NOTE: Starting expel slave for group 5/0x4718f00c (FLEX)
NOTE: stopping process ARB0
NOTE: stopping process ARBA
NOTE: GroupBlock outside rolling migration privileged region
NOTE: requesting all-instance membership refresh for group=5
SUCCESS: rebalance completed for group 5/0x4718f00c (FLEX)
NOTE: membership refresh pending for group 5/0x4718f00c (FLEX)
GMON querying group 5 at 836 for pid 25, osid 11576
SUCCESS: refreshed membership for 5/0x4718f00c (FLEX)
2017-07-06 13:18:32.568000 +01:00
NOTE: Attempting voting file refresh on diskgroup FLEX
NOTE: Refresh completed on diskgroup FLEX. No voting file found.

The re-mirroring operation is reported as a (mini) rebalance operation in the ASM instance’s alert.log. Thinking about it this makes perfect sense.

What about new files?

The change in meta-data is also visible in v$asm_filegroup_properties. Every new data file created in my PDB will from now on be created with high redundancy.

SQL> select file_type, name, value from v$asm_filegroup_property
  2  where filegroup_number = 6 and file_type = 'DATAFILE';

FILE_TYPE       NAME                 VALUE
--------------- -------------------- --------------------
DATAFILE        REDUNDANCY           HIGH
DATAFILE        STRIPING             COARSE

And indeed: after adding a USERS-tablespace the new properties are enforced:

SQL> select file_number,bytes,space,type,redundancy,redundancy_lowered,striped,remirror
  2  from v$asm_file where filegroup_number = 6
  3  order by file_number;

FILE_NUMBER      BYTES      SPACE TYPE            REDUND R STRIPE R
----------- ---------- ---------- --------------- ------ - ------ -
        309  167780352  528482304 DATAFILE        HIGH   U COARSE N
        310  272637952  843055104 DATAFILE        HIGH   U COARSE N
        311  471867392 1434451968 DATAFILE        HIGH   U COARSE N
        312   67117056  142606336 TEMPFILE        MIRROR U COARSE N
        313 2147491840 6467616768 DATAFILE        HIGH   U COARSE N

Summary

Flex ASM Disk Groups continue to amaze me. Using the File Group properties I can now define entities (NCDB, CDB, PDB, …) and provide flexible, highly granular settings for data protection within the disk group. In previous releases I could do similar things, but that required multiple disk groups and was more complex to do so.

@martinberx posted an interesting comment about Flex ASM Disk Groups and redundancy levels on twitter, comparing Flex ASM Disk Groups with ASM File Templates. Using ASM File Templates one could define redundancy levels of files within an ASM disk group amongst other things, a feature which has been with ASM for a long time. Technically it is possible to define a template for data files mandating 3-way mirroring even if the file was stored on a normal redundancy disk group.

My colleague Alex Fatkulin has blogged about high redundancy files in normal redundancy disk groups and shows why ASM File Templates ultimately don’t offer better availability. I’ll try and get a test case together for my Flex ASM Disk Group to see if it fares better. According to the documentation, a Flex ASM Disk Group with 5 or more failgroups should be able to survive the failure of 2 failgroups (just like a disk group with High Redundancy).

12.2 New Feature: the FLEX ASM disk group part 3

In the previous 2 parts of this mini series I introduced the Flex ASM disk group and two related concepts, the Quota Group and File Group. In what should have become the final part (but isn’t) I am interested in checking whether quotas are enforced.

(Un)fortunately I have uncovered a few more things that are worth investigating and blogging about, which is why a) this isn’t the last post and b) it got a bit shorter than the previous two. Had I combined part 3 and 4 it would have been too long for sure … BTW, you can navigate all posts using the links at the very bottom of the page.

Are quotas enforced?

The purpose of the Quota Group is … to enforce quotas on a disk group, much like on a file system. This is quite interesting, because you now have a hard limit to which databases can grow within a disk group even for non-CDBs.

The question I set out to answer in this part is whether quotas are enforced. As you read in the previous post’s interlude, I have created 2 databases on the FLEX ASM disk group: ORCL and CDB, both container databases.

The current state of affairs for my File Groups is this:

ASMCMD> lsfg
File Group         Disk Group  Quota Group  Used Quota MB  Client Name    Client Type  
DEFAULT_FILEGROUP  FLEX        GENERIC      2488                                       
CDB_CDB$ROOT       FLEX        QG_CDB       6744           CDB_CDB$ROOT   DATABASE     
CDB_PDB$SEED       FLEX        QG_CDB       1656           CDB_PDB$SEED   DATABASE     
PDB1               FLEX        QG_CDB       1784           PDB1           DATABASE     
ORCL_CDB$ROOT      FLEX        GENERIC      9104           ORCL_CDB$ROOT  DATABASE     
ORCL_PDB$SEED      FLEX        GENERIC      1616           ORCL_PDB$SEED  DATABASE     
PDB1_0001          FLEX        GENERIC      9424           PDB1           DATABASE   

Database CDB is nicely tucked away in QG_CDB, but none of the ORCL database’s components are assigned to a Quota Group yet. I wanted to have another Quota Group QG_ORCL, for my second CDB. Somehow I think that a Quota Group per database makes sense.

ASMCMD> mkqg -G FLEX QG_ORCL quota 20G
Diskgroup altered.
ASMCMD> lsqg
Group_Num  Quotagroup_Num  Quotagroup_Name  Incarnation  Used_Quota_MB  Quota_Limit_MB  
2          1               GENERIC          1            22632          0               
2          2               QG_ORCL          7            0              20480           
2          3               QG_CDB           1            10184          20480           
ASMCMD> 
If you followed part 1 and 2 you may have noticed that my FLEX disk group group_number has changed from 5 to 2, after a server reboot.

SQL> select group_number, name, state, type from v$asm_diskgroup
  2  where group_number = 2;

GROUP_NUMBER NAME                           STATE       TYPE
------------ ------------------------------ ----------- ------
           2 FLEX                           CONNECTED   FLEX

Don’t let the change in numbers confuse you

Back to the example: File Groups ORCL_CDB$ROOT, ORCL_PDB$SEED and PDB1_0001 are not yet within QG_ORCL. This can be rectified using 3 simple mvfg commands in ASMCMD, or the corresponding SQL commands. After the move commands completed, the Quota Group still has space left (although it’s very limited)

ASMCMD> mvfg -G flex --filegroup PDB1_0001 QG_ORCL
Diskgroup altered.
ASMCMD> mvfg -G flex --filegroup ORCL_PDB$SEED  QG_ORCL
Diskgroup altered.
ASMCMD> mvfg -G flex --filegroup ORCL_CDB$ROOT  QG_ORCL
Diskgroup altered.
ASMCMD> lsfg
File Group         Disk Group  Quota Group  Used Quota MB  Client Name    Client Type  
DEFAULT_FILEGROUP  FLEX        GENERIC      2488                                       
CDB_CDB$ROOT       FLEX        QG_CDB       6744           CDB_CDB$ROOT   DATABASE     
CDB_PDB$SEED       FLEX        QG_CDB       1656           CDB_PDB$SEED   DATABASE     
PDB1               FLEX        QG_CDB       1784           PDB1           DATABASE     
ORCL_CDB$ROOT      FLEX        QG_ORCL      9104           ORCL_CDB$ROOT  DATABASE     
ORCL_PDB$SEED      FLEX        QG_ORCL      1616           ORCL_PDB$SEED  DATABASE     
PDB1_0001          FLEX        QG_ORCL      9424           PDB1           DATABASE     
ASMCMD> lsqg
Group_Num  Quotagroup_Num  Quotagroup_Name  Incarnation  Used_Quota_MB  Quota_Limit_MB  
2          1               GENERIC          1            2488           0               
2          2               QG_ORCL          7            20144          20480           
2          3               QG_CDB           1            10184          20480           
ASMCMD> 

Now I just need to try and push it over the edge to see if the quota has any effect. This is quite simple: all I need to do is create another tablespace in ORCL:PDB1

SQL> show con_name

CON_NAME
------------------------------
PDB1
SQL> create tablespace userdata datafile size 500m;
create tablespace userdata datafile size 500m
*
ERROR at line 1:
ORA-01119: error in creating database file '+FLEX'
ORA-17502: ksfdcre:4 Failed to create file +FLEX
ORA-15437: Not enough quota available in quota group QG_ORCL.

SQL> 

This is similar to what we could already enforce using the storage clause in the create pluggable database command. But what about ASM file types that aren’t data files? Querying the database I can find quite a few of these:

SQL> get /tmp/non_data_files
  1  SELECT
  2      f.group_number as DG_NUMBER,
  3      f.file_number,
  4      f.incarnation as file_incarnation,
  5      f.type,
  6      fg.name as filegroup_name,
  7      a.name as file_name
  8  FROM
  9      v$asm_alias a,
 10      v$asm_file f,
 11      v$asm_filegroup fg
 12  WHERE
 13          a.group_number = f.group_number
 14      AND
 15          a.file_number = f.file_number
 16      AND
 17          a.file_incarnation = f.incarnation
 18      AND
 19          fg.group_number = f.group_number
 20      AND
 21          fg.filegroup_number = f.filegroup_number
 22      AND
 23*         f.type  'DATAFILE';
SQL> start /tmp/non_data_files

 DG_NUMBER FILE_NUMBER FILE_INCARNATION TYPE            FILEGROUP_NAME       FILE_NAME
---------- ----------- ---------------- --------------- -------------------- ------------------------------
         2         282        948453843 PASSWORD        DEFAULT_FILEGROUP    pwdorcl.282.948453843
         2         287        948462715 PASSWORD        DEFAULT_FILEGROUP    pwdorcl.287.948462715
         2         293        948462849 CONTROLFILE     ORCL_CDB$ROOT        Current.293.948462849
         2         292        948462849 CONTROLFILE     ORCL_CDB$ROOT        Current.292.948462849
         2         294        948462855 ONLINELOG       ORCL_CDB$ROOT        group_2.294.948462855
         2         295        948462855 ONLINELOG       ORCL_CDB$ROOT        group_1.295.948462855
         2         296        948462861 ONLINELOG       ORCL_CDB$ROOT        group_1.296.948462861
         2         297        948462861 ONLINELOG       ORCL_CDB$ROOT        group_2.297.948462861
         2         304        948463227 ONLINELOG       ORCL_CDB$ROOT        group_3.304.948463227
         2         305        948463231 ONLINELOG       ORCL_CDB$ROOT        group_3.305.948463231
         2         306        948463239 ONLINELOG       ORCL_CDB$ROOT        group_4.306.948463239
         2         307        948463243 ONLINELOG       ORCL_CDB$ROOT        group_4.307.948463243
         2         298        948462891 TEMPFILE        ORCL_CDB$ROOT        TEMP.298.948462891
         2         302        948462937 TEMPFILE        ORCL_PDB$SEED        TEMP.302.948462937
         2         308        948463249 PARAMETERFILE   ORCL_CDB$ROOT        spfile.308.948463249
         2         312        948464283 TEMPFILE        PDB1_0001            TEMP.312.948464283

16 rows selected.

Let’s take the online redo logs and add another thread to instance 1:

SQL> alter database add logfile thread 1 size 1g;
alter database add logfile thread 1 size 1g
*
ERROR at line 1:
ORA-00301: error in adding log file '+FLEX' - file cannot be created
ORA-17502: ksfdcre:4 Failed to create file +FLEX
ORA-15437: Not enough quota available in quota group QG_ORCL.


SQL> 

This proves that quotas are enforced, at least for a couple of very simple examples.

Summary Part 3

There is certainly a lot more to discover about Quota Groups, datafiles set to autoextent, archivelog growth to a certain size, temp- and undo tablespaces etc. At first glance, it looks good. The key again is to have suitable monitoring in place that warns DBAs about File Groups running out of space. I wonder if that’s port of Enterprise Manager, otherwise it’s easy to write such checks yourself either as custom metrics in OEM or in tools such as Nagios.

12.2 New Feature: the FLEX ASM disk group part 2

In the first part of this series I explained the basics and some potential motivation behind the use of ASM Flex disk groups. In this part I would like to complete the description of new concepts.

New Concepts related to FLEX ASM Disk Groups

With the Flex disk group mounted, the next steps are to create a few new entities. First, I want to create a Quota Group. The Quota Group – as the name implies – will enforce quotas for entities residing within it. It is optional to add one yourself, Oracle creates a default Quota Group for you that does not enforce storage limits. As you will see later, the default Quota Group will be assigned to all new databases in the Flex ASM disk group.

The entity to be stored inside the Quota Group is named a File Group, and serves to logically group files such as those belonging to a database. According to the SQL Language Reference version 12.2, the File Group can be created for a

  • Database (non-CDB, CDB, and PDB)
  • Cluster or
  • ASM Volume

Remember that the compatible.rdbms and compatible.asm parameters are to be set to 12.2.0.1(+) on the Flex ASM disk group, ruling out pre 12.2 databases. For this post I am intending to store database-related files in the File Group. And since I like CDBs, I’m using this database type.

Database Creation

With the FLEX disk group in place I fired up dbca in silent mode to create a database for me. Before submitting the command however I created a Quota Group, connected to the ASM instance, like this:

SQL> alter diskgroup flex add quotagroup QG_CDB set quota = 20g;

Diskgroup altered.

SQL> select QUOTAGROUP_NUMBER,NAME,USED_QUOTA_MB,QUOTA_LIMIT_MB from v$asm_quotagroup;

QUOTAGROUP_NUMBER NAME                           USED_QUOTA_MB QUOTA_LIMIT_MB
----------------- ------------------------------ ------------- --------------
                1 GENERIC                                    0              0
                3 QG_CDB                                     0          20480
SQL> 

Oracle provides a GENERIC Quota Group without storage limitations when the Flex ASM disk group is created. QG_CDB is the Quota Group I just created. In hindsight I don’t think that creating the Quota Group at this stage was necessary because it wasn’t used straight away… but I’m getting ahead of myself.

Here is the command to create the two-node RAC database on my FLEX diskgroup, which I ran next:

[oracle@rac122pri1 ~]$ dbca -silent -createDatabase -templateName martin_cdb12cr2_001.dbc \
> -gdbName CDB -sysPassword secretpwd1 -systemPassword secretpwd2 -storageType ASM \
> -diskGroupName FLEX -recoveryGroupName FLEX -sampleSchema true \
> -totalMemory 2048 -dbsnmpPassword secretpwd3 -nodeinfo rac122pri1,rac122pri2 \
> -createAsContainerDatabase true -databaseConfigType RAC

I should probably create a disk group +FLEXRECO for my Fast Recovery Area, but since this is a lab system I don’t quite care enough to justify the extra space usage. Your mileage will most definitely vary, this isn’t supposed to be a blue print, instead it’s just me dabbling around with new technology :)

It takes a little time for the dbca command to return. It appears as if the database had automatically created File Groups for the CDB’s components, mapping to the GENERIC Quota Group (quotagroup_number 1 from the listing above). Querying the ASM instance, I can find these:

SQL> select FILEGROUP_NUMBER, NAME, CLIENT_NAME, USED_QUOTA_MB, QUOTAGROUP_NUMBER from v$asm_filegroup
  2  /

FILEGROUP_NUMBER NAME                 CLIENT_NAME          USED_QUOTA_MB QUOTAGROUP_NUMBER
---------------- -------------------- -------------------- ------------- -----------------
               0 DEFAULT_FILEGROUP                                     0                 1
               1 CDB_CDB$ROOT         CDB_CDB$ROOT                  6704                 1
               2 CDB_PDB$SEED         CDB_PDB$SEED                  1656                 1

Just like with Quota Groups, there is a default entity, named DEFAULT_FILEGROUP. The CDB_CDB$ROOT and CDB_PDB$SEED seem to map to the newly created database. As you can see a little later in this post, the first “CDB” in CDB_CDB$ROOT maps to the database name. CDB$ROOT and PDB$SEED should sound familiar if you have previously worked with Container Databases.

Oracle creating File Groups for databases on its own is not too bad, because it takes half the work away from me. To test whether this holds true for newly created PDBs, I added a PDB named PDB1 to my CDB. Sure enough, after the create pluggable database command returned, there was a new File Group:

SQL> select FILEGROUP_NUMBER, NAME, CLIENT_NAME from v$asm_filegroup;

FILEGROUP_NUMBER NAME                 CLIENT_NAME
---------------- -------------------- --------------------
               0 DEFAULT_FILEGROUP
               1 CDB_CDB$ROOT         CDB_CDB$ROOT
               2 CDB_PDB$SEED         CDB_PDB$SEED
               3 PDB1                 PDB1

The output made me think – somehow name and client_name no longer look that useful for establishing a relation between CDB and PDB1. This might actually not be necessary from a technical point of view, but somehow I need to understand which PDB1 is meant in the view in case there is more than 1. I can think of a PDB1 in CDB as super important, whereas PDB1 in some other CDB is less important. One way would be to name the PDB differently to allow a human to map the PDB name to a CDB. Having said that I certainly don’t want to have references to the CDB in my PDB name, that defeats the purpose of pluggable databases and wouldn’t hold true anyway when I unplug/plug the PDB to a different CDB.

Interlude
If you wonder what happens when you create another PDB1 in a different CDB, I did so, too. After a quick change to my dbca command and after the creation of another CDB I named ORCL, I create a new PDB1 within it. This is the result:

SQL> select filegroup_number, name, client_name, guid from v$asm_filegroup;

FILEGROUP_NUMBER NAME                 CLIENT_NAME          GUID
---------------- -------------------- -------------------- --------------------------------
               0 DEFAULT_FILEGROUP
               1 CDB_CDB$ROOT         CDB_CDB$ROOT         4700A987085A3DFAE05387E5E50A8C7B
               2 CDB_PDB$SEED         CDB_PDB$SEED         536DF51E8E28221BE0534764A8C0FD81
               3 PDB1                 PDB1                 537B677EF8DA0F1AE0534764A8C05729
               4 ORCL_CDB$ROOT        ORCL_CDB$ROOT        4700A987085A3DFAE05387E5E50A8C7B
               5 ORCL_PDB$SEED        ORCL_PDB$SEED        537E63B952183748E0534764A8C09A7F
               6 PDB1_0001            PDB1                 537EB5B87E62586EE0534764A8C05530

7 rows selected.

The good news is there is no clash, or even a failure of the “create pluggable database” command. The new database’s CDB$ROOT and PDB$SEED namespaces don’t collide because of the database-name prefix. CDB.PDB1 and ORCL.PDB1 don’t clash because Oracle appends a number to the File Group’s name.

The not-so-good news is that the (File Group) name and client_name became ambiguous. But there is a solution: playing around a bit I found that the GUID column in v$asm_filegroup maps to the GUID in v$pdbs/v$container.

SQL> select sys_context('USERENV','CDB_NAME') cdb_name, guid 
  2  from v$pdbs where guid = '537EB5B87E62586EE0534764A8C05530';

CDB_NAME                       GUID
------------------------------ --------------------------------
ORCL                           537EB5B87E62586EE0534764A8C05530

Looks like it’s time to understand those GUIDs better, hopefully there will be more to come soon.

Quotas

As you can see from the previous example, is not necessary to enforce a quota, however it is possible. I’ll do this for the sake of completeness (and in preparation for part 3).

NOTE: This section of the blog post was actually written before I had created the second CDB (“ORCL”), which is why you don’t see it in the command output.

Many of the administrative commands related to File Group and Quota Groups that I entered in SQL can also be issued via asmcmd, as shown here:

ASMCMD> lsqg
Group_Num  Quotagroup_Num  Quotagroup_Name  Incarnation  Used_Quota_MB  Quota_Limit_MB  
5          1               GENERIC          1            10016          0               
5          3               QG_CDB           1            0              20480           

ASMCMD> lsfg
File Group         Disk Group  Quota Group  Used Quota MB  Client Name   Client Type  
DEFAULT_FILEGROUP  FLEX        GENERIC      0                                         
CDB_CDB$ROOT       FLEX        GENERIC      6704           CDB_CDB$ROOT  DATABASE     
CDB_PDB$SEED       FLEX        GENERIC      1656           CDB_PDB$SEED  DATABASE     
PDB1               FLEX        GENERIC      1656           PDB1          DATABASE     

ASMCMD> help mvfg
mvfg
        Moves a file group in a disk group to the specified Quota Group.

Synopsis
        mvfg -G  --filegroup  

Description
        The options for the mvfg command are described below.

        -G diskgroup     - Disk group name.
        --filegroup      - File group name.

Examples
        The following is an example of the mvfg command. The file group
        FG1 in the DATA disk group is moved to the Quota Group QG1.

        ASMCMD [+] > mvfg -G DATA --filegroup FG1 QG1

See Also
       mkqg rmqg chqg lsqg

ASMCMD> 

The first two should be self-explanatory: lsqg is short for list Quota Group, and lsfg is the equivalent for File Groups. the mvfg takes a couple of parameters but should be straight forward. With the commands introduced it’s time to perform the action. I need to move File Groups using mvfg:

ASMCMD> mvfg -G flex --filegroup CDB_CDB$ROOT QG_CDB
Diskgroup altered.
ASMCMD> mvfg -G flex --filegroup CDB_PDB$SEED QG_CDB
Diskgroup altered.
ASMCMD> mvfg -G flex --filegroup PDB1 QG_CDB
Diskgroup altered.
ASMCMD> lsfg
File Group         Disk Group  Quota Group  Used Quota MB  Client Name   Client Type  
DEFAULT_FILEGROUP  FLEX        GENERIC      0                                         
CDB_CDB$ROOT       FLEX        QG_CDB       6704           CDB_CDB$ROOT  DATABASE     
CDB_PDB$SEED       FLEX        QG_CDB       1656           CDB_PDB$SEED  DATABASE     
PDB1               FLEX        QG_CDB       1656           PDB1          DATABASE     

ASMCMD> lsqg
Group_Num  Quotagroup_Num  Quotagroup_Name  Incarnation  Used_Quota_MB  Quota_Limit_MB  
5          1               GENERIC          1            0              0               
5          3               QG_CDB           1            10016          20480           
ASMCMD> 

The command completes pretty much instantaneously, so it’s not actually “moving” data, it appears that all there is done is an update on meta-data. Moving the File Group is translated to the following SQL commands, found in the ASM instance’s alert.log:

2017-07-04 11:01:53.492000 +01:00
SQL> /* ASMCMD */ALTER DISKGROUP FLEX MOVE FILEGROUP CDB_CDB$ROOT TO QG_CDB
SUCCESS: /* ASMCMD */ALTER DISKGROUP FLEX MOVE FILEGROUP CDB_CDB$ROOT TO QG_CDB
2017-07-04 11:02:08.645000 +01:00
SQL> /* ASMCMD */ALTER DISKGROUP FLEX MOVE FILEGROUP CDB_PDB$SEED TO QG_CDB
SUCCESS: /* ASMCMD */ALTER DISKGROUP FLEX MOVE FILEGROUP CDB_PDB$SEED TO QG_CDB
SQL> /* ASMCMD */ALTER DISKGROUP FLEX MOVE FILEGROUP PDB1 TO QG_CDB
SUCCESS: /* ASMCMD */ALTER DISKGROUP FLEX MOVE FILEGROUP PDB1 TO QG_CDB

Summary Part 2

In part 2 of the article series I played around with Quota Groups and File Groups. Getting to grips with these concepts is necessary for a better understanding of how a Flex ASM disk group works. In part 3 I’ll try and investigate the effect of changing properties to file groups, and whether quotas are enforced.