Author Archives: Martin Bach

About Martin Bach

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

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.

Advertisements

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.

12.2 New Feature: the FLEX ASM disk group part 1

I knew about the 12.2 FLEX ASM disk group type from other presenters but until now – when researching the feature for the upcoming DOAG HA Day – I haven’t been able to appreciate how cool this is. And I really think it is pretty cool and worth sharing! There is a lot to be said about the feature and these tests, which is why I am splitting it into multiple parts.

Please be aware that this post is about my lab experiments, I have no production experience with FLEX ASM disk groups. As with all new features it might take a while to mature, so test, test, test…

Background

In previous versions of Oracle Grid Infrastructure/Automatic Storage Management (ASM), especially in consolidation environments, certain operations I would have liked to perform were not easily possible. Most properties of the disk group – such as redundancy levels etc. – are valid for all files that reside within it. For example, if you wanted to have normal redundancy for some databases, and high redundancy for others, you typically ended up with two “data” disk groups. The same goes for +RECO. I can think of scenarios where “filesystem-like” attributes within a disk group would have been nice. In the build-up to ASM 12.2, Oracle has steadily increased ASM limits to allow for more customisation, and in 12.1 you really could go a little over the top. You could have 63 ASM disk groups in 11.2 and up to 511 in 12.1. Although this should allow for plenty customisation, it adds a little maintenance overhead.

A more granular option to manage storage came with 12.1 and the introduction of Container Databases (CDBs). As part of the Pluggable Database’s creation, the administrator could specify a pdb_storage_clause as in this very basic example:

SQL> create pluggable database pdb1 
  2   admin user someone identified by somepassword
  3    ...
  4   storage (maxsize 200G);

Pluggable database created.

However, if the database was created within a disk group with high redundancy, all files residing in that disk group inherited that property. I couldn’t define a PDB with normal redundancy in a high redundancy disk group, at least I wasn’t aware of a way to do so in 12.1.

Flex Disk Group

A Flex Disk Group promises fine-granular management of data within the disk group. You can also enforce quotas in a disk group (probably most useful on database-level), and you can define properties such as redundancy settings per file type (also per database or Pluggable Database). So in other words you can now have a disk group containing 2 databases for example, out of which database 1 uses normal redundancy, and database 2 uses high redundancy. If database 2 is a Container Database (CDB), you can even manage settings as low down as the PDB level. A few new concepts need introducing before that can happen. Let’s begin with the most essential part: the new Flex ASM disk group. There is a variation of the theme for extended distance clusters, which is not in scope of this article series.

In my lab system, I have created a new ASM disk group of flex redundancy. The system I am using is a two-node RAC running 12.2.0.1.170620 on Oracle Linux 7.3 with UEK4. I called the disk group FLEX, here is the command used for its creation:

CREATE DISKGROUP FLEX FLEX REDUNDANCY  
 DISK 'AFD:FLEX1' 
 DISK 'AFD:FLEX2'   
 DISK 'AFD:FLEX3' 
 DISK 'AFD:FLEX4'  
 DISK 'AFD:FLEX5'  
ATTRIBUTE 
 'compatible.asm'='12.2.0.1',
 'compatible.rdbms'='12.2.0.1',
 'compatible.advm'='12.2.0.1',
 'au_size'='4M’;

Note the use of ASM Filter Driver which I am testing as part of my lab set up. It’s also enabled by default when you install ASM 12.2. Looking at the code example I do realise now that the disk group name is maybe not ideal … The important bit in the example is the use of “FLEX REDUNDANCY”, the 5 implicit different failure groups and the compatibility settings that need to be 12.2.

The documentation (Automatic Storage Management Administrator’s Guide, chapter “Managing Oracle ASM Flex Disk Groups”) states that a Flex disk group generally tolerates the loss of 2 failure groups (FGs). The same bullet point then elaborates that at least 5 failure groups are needed to absorb the loss of 2 FGs. The minimum number of FGs within a Flex disk group are 3.

If you now get all excited about this new feature, there is one Big Caveat: you need a 12.2 RDBMS instance to use the feature.

This command results in a flurry of activity in the ASM instance, I have captured the output from the command initiation to completion because it’s quite interesting to see what happens in 12.2 ASM when you create a new disk group. Feel free to scroll down past the listing if you aren’t interested in the finer details.

SQL> CREATE DISKGROUP FLEX FLEX REDUNDANCY  DISK 'AFD:FLEX1' SIZE 10239M
 DISK 'AFD:FLEX2' SIZE 10239M
 DISK 'AFD:FLEX3' SIZE 10239M
 DISK 'AFD:FLEX4' SIZE 10239M
 DISK 'AFD:FLEX5' SIZE 10239M
 ATTRIBUTE 'compatible.asm'='12.2.0.1','compatible.rdbms'='12.2.0.1','compatible.advm'='12.2.0.1','au_size'='4M'
NOTE: Assigning number (5,0) to disk (AFD:FLEX1)
NOTE: Assigning number (5,1) to disk (AFD:FLEX2)
NOTE: Assigning number (5,2) to disk (AFD:FLEX3)
NOTE: Assigning number (5,3) to disk (AFD:FLEX4)
NOTE: Assigning number (5,4) to disk (AFD:FLEX5)
2017-07-03 10:38:53.811000 +01:00
NOTE: initializing header (replicated) on grp 5 disk FLEX1
NOTE: initializing header (replicated) on grp 5 disk FLEX2
NOTE: initializing header (replicated) on grp 5 disk FLEX3
NOTE: initializing header (replicated) on grp 5 disk FLEX4
NOTE: initializing header (replicated) on grp 5 disk FLEX5
NOTE: initializing header on grp 5 disk FLEX1
NOTE: initializing header on grp 5 disk FLEX2
NOTE: initializing header on grp 5 disk FLEX3
NOTE: initializing header on grp 5 disk FLEX4
NOTE: initializing header on grp 5 disk FLEX5
NOTE: Disk 0 in group 5 is assigned fgnum=1
NOTE: Disk 1 in group 5 is assigned fgnum=2
NOTE: Disk 2 in group 5 is assigned fgnum=3
NOTE: Disk 3 in group 5 is assigned fgnum=4
NOTE: Disk 4 in group 5 is assigned fgnum=5
GMON updating for reconfiguration, group 5 at 657 for pid 45, osid 25857
NOTE: group 5 PST updated.
NOTE: initiating PST update: grp = 5
GMON updating group 5 at 658 for pid 45, osid 25857
NOTE: set version 0 for asmCompat 12.2.0.1.0 for group 5
NOTE: group FLEX: initial PST location: disks 0000 0001 0002 0003 0004
NOTE: PST update grp = 5 completed successfully
NOTE: cache registered group FLEX 5/0x0A58F009
NOTE: cache began mount (first) of group FLEX 5/0x0A58F009
NOTE: cache is mounting group FLEX created on 2017/07/03 10:38:52
NOTE: cache opening disk 0 of grp 5: FLEX1 label:FLEX1
NOTE: cache opening disk 1 of grp 5: FLEX2 label:FLEX2
NOTE: cache opening disk 2 of grp 5: FLEX3 label:FLEX3
NOTE: cache opening disk 3 of grp 5: FLEX4 label:FLEX4
NOTE: cache opening disk 4 of grp 5: FLEX5 label:FLEX5
* allocate domain 5, valid ? 0
kjbdomatt send to inst 2
NOTE: attached to recovery domain 5
NOTE: cache creating group 5/0x0A58F009 (FLEX)
NOTE: cache mounting group 5/0x0A58F009 (FLEX) succeeded
NOTE: allocating F1X0 (replicated) on grp 5 disk FLEX1
NOTE: allocating F1X0 (replicated) on grp 5 disk FLEX2
NOTE: allocating F1X0 (replicated) on grp 5 disk FLEX3
NOTE: allocating F1X0 on grp 5 disk FLEX1
NOTE: allocating F1X0 on grp 5 disk FLEX2
NOTE: allocating F1X0 on grp 5 disk FLEX3
2017-07-03 10:38:56.621000 +01:00
NOTE: Created Used Space Directory for 1 threads
NOTE: Created Virtual Allocation Locator (1 extents) and Table (5 extents) directories for group 5/0x0A58F009 (FLEX)
2017-07-03 10:39:00.153000 +01:00
NOTE: VAM migration has completed for group 5/0x0A58F009 (FLEX)
NOTE: diskgroup must now be re-mounted prior to first use
NOTE: cache dismounting (clean) group 5/0x0A58F009 (FLEX)
NOTE: messaging CKPT to quiesce pins Unix process pid: 25857, image: oracle@rac122pri1 (TNS V1-V3)
2017-07-03 10:39:01.805000 +01:00
NOTE: LGWR not being messaged to dismount
kjbdomdet send to inst 2
detach from dom 5, sending detach message to inst 2
freeing rdom 5
NOTE: detached from domain 5
NOTE: cache dismounted group 5/0x0A58F009 (FLEX)
GMON dismounting group 5 at 659 for pid 45, osid 25857
GMON dismounting group 5 at 660 for pid 45, osid 25857
NOTE: Disk FLEX1 in mode 0x7f marked for de-assignment
NOTE: Disk FLEX2 in mode 0x7f marked for de-assignment
NOTE: Disk FLEX3 in mode 0x7f marked for de-assignment
NOTE: Disk FLEX4 in mode 0x7f marked for de-assignment
NOTE: Disk FLEX5 in mode 0x7f marked for de-assignment
SUCCESS: diskgroup FLEX was created
NOTE: cache deleting context for group FLEX 5/0x0a58f009
NOTE: cache registered group FLEX 5/0x4718F00C
NOTE: cache began mount (first) of group FLEX 5/0x4718F00C
NOTE: Assigning number (5,0) to disk (AFD:FLEX1)
NOTE: Assigning number (5,1) to disk (AFD:FLEX2)
NOTE: Assigning number (5,2) to disk (AFD:FLEX3)
NOTE: Assigning number (5,3) to disk (AFD:FLEX4)
NOTE: Assigning number (5,4) to disk (AFD:FLEX5)
2017-07-03 10:39:08.161000 +01:00
NOTE: GMON heartbeating for grp 5 (FLEX)
GMON querying group 5 at 663 for pid 45, osid 25857
NOTE: cache is mounting group FLEX created on 2017/07/03 10:38:52
NOTE: cache opening disk 0 of grp 5: FLEX1 label:FLEX1
NOTE: 07/03/17 10:39:07 FLEX.F1X0 found on disk 0 au 10 fcn 0.0 datfmt 1
NOTE: cache opening disk 1 of grp 5: FLEX2 label:FLEX2
NOTE: 07/03/17 10:39:07 FLEX.F1X0 found on disk 1 au 10 fcn 0.0 datfmt 1
NOTE: cache opening disk 2 of grp 5: FLEX3 label:FLEX3
NOTE: 07/03/17 10:39:07 FLEX.F1X0 found on disk 2 au 10 fcn 0.0 datfmt 1
NOTE: cache opening disk 3 of grp 5: FLEX4 label:FLEX4
NOTE: cache opening disk 4 of grp 5: FLEX5 label:FLEX5
NOTE: cache mounting (first) flex redundancy group 5/0x4718F00C (FLEX)
* allocate domain 5, valid ? 0
kjbdomatt send to inst 2
NOTE: attached to recovery domain 5
start recovery: pdb 5, passed in flags x4 (domain enable 0)
validate pdb 5, flags x4, valid 0, pdb flags x204
* validated domain 5, flags = 0x200
NOTE: cache recovered group 5 to fcn 0.0
NOTE: redo buffer size is 512 blocks (2105344 bytes)
NOTE: LGWR attempting to mount thread 1 for diskgroup 5 (FLEX)
NOTE: LGWR found thread 1 closed at ABA 0.11262 lock domain=0 inc#=0 instnum=0
NOTE: LGWR mounted thread 1 for diskgroup 5 (FLEX)
NOTE: setting 11.2 start ABA for group FLEX thread 1 to 2.0
NOTE: LGWR opened thread 1 (FLEX) at fcn 0.0 ABA 2.0 lock domain=5 inc#=12 instnum=1 gx.incarn=1192816652 mntstmp=2017/07/03 10:39:08.437000
NOTE: cache mounting group 5/0x4718F00C (FLEX) succeeded
NOTE: cache ending mount (success) of group FLEX number=5 incarn=0x4718f00c
NOTE: Instance updated compatible.asm to 12.2.0.1.0 for grp 5 (FLEX).
NOTE: Instance updated compatible.asm to 12.2.0.1.0 for grp 5 (FLEX).
NOTE: Instance updated compatible.rdbms to 12.2.0.1.0 for grp 5 (FLEX).
NOTE: Instance updated compatible.rdbms to 12.2.0.1.0 for grp 5 (FLEX).
SUCCESS: diskgroup FLEX was mounted
NOTE: diskgroup resource ora.FLEX.dg is online
SUCCESS: CREATE DISKGROUP FLEX FLEX REDUNDANCY  DISK 'AFD:FLEX1' SIZE 10239M
 DISK 'AFD:FLEX2' SIZE 10239M
 DISK 'AFD:FLEX3' SIZE 10239M
 DISK 'AFD:FLEX4' SIZE 10239M
 DISK 'AFD:FLEX5' SIZE 10239M
 ATTRIBUTE 'compatible.asm'='12.2.0.1','compatible.rdbms'='12.2.0.1','compatible.advm'='12.2.0.1','au_size'='4M'
2017-07-03 10:39:09.429000 +01:00
NOTE: enlarging ACD to 2 threads for group 5/0x4718f00c (FLEX)
2017-07-03 10:39:11.438000 +01:00
SUCCESS: ACD enlarged for group 5/0x4718f00c (FLEX)
NOTE: Physical metadata for diskgroup 5 (FLEX) was replicated.
adrci> 

Quite a bit of activity for just 1 command… I checked the attributes of the disk group, they don’t seem too alien to me:

SQL> select name, value from v$asm_attribute 
  2   where group_number = 5 
  3   and name not like 'template%';

NAME                           VALUE
------------------------------ ------------------------------
idp.type                       dynamic
idp.boundary                   auto
disk_repair_time               3.6h
phys_meta_replicated           true
failgroup_repair_time          24.0h
thin_provisioned               FALSE
preferred_read.enabled         FALSE
sector_size                    512
logical_sector_size            512
content.type                   data
content.check                  FALSE
au_size                        4194304
appliance._partnering_type     GENERIC
compatible.asm                 12.2.0.1.0
compatible.rdbms               12.2.0.1.0
compatible.advm                12.2.0.1.0
cell.smart_scan_capable        FALSE
cell.sparse_dg                 allnonsparse
access_control.enabled         FALSE
access_control.umask           066
scrub_async_limit              1
scrub_metadata.enabled         FALSE

22 rows selected.

I didn’t specify anything about failure groups in the create disk group command, hence I am getting 5 of them:

SQL> select name, os_mb, failgroup, path from v$asm_disk where group_number = 5;

NAME            OS_MB FAILGROUP                      PATH
---------- ---------- ------------------------------ --------------------
FLEX1           10239 FLEX1                          AFD:FLEX1
FLEX2           10239 FLEX2                          AFD:FLEX2
FLEX3           10239 FLEX3                          AFD:FLEX3
FLEX4           10239 FLEX4                          AFD:FLEX4
FLEX5           10239 FLEX5                          AFD:FLEX5

The result is the new disk group, a new part of my existing lab setup. As you can see in the following output I went with a separate disk group for the Grid Infrastructure Management Repository (GIMR), named +MGMT. In addition I have a disk group named +OCR which (surprise!) I use for OCR and voting files plus the usual suspects, +DATA and +RECO. Except +FLEX, all these are disk group types that have been available forever.

[oracle@rac122pri1 ~]$ asmcmd lsdg
State    Type    Rebal  Sector  Logical_Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512             512   4096  4194304     20476    17068                0           17068              0             N  DATA/
MOUNTED  FLEX    N         512             512   4096  4194304     51180    50676                0               0              0             N  FLEX/
MOUNTED  EXTERN  N         512             512   4096  4194304     40956     6560                0            6560              0             N  MGMT/
MOUNTED  NORMAL  N         512             512   4096  4194304     15348    14480             5116            4682              0             Y  OCR/
MOUNTED  EXTERN  N         512             512   4096  4194304     15356    15224                0           15224              0             N  RECO/
[oracle@rac122pri1 ~]$ 

The values of 0 in required_mirror_free_mb and useable_file_mb for +FLEX aren’t bugs, they are documented to be empty for this type of disk group. You need to consult other entities – to be covered in the next posts – to check the space usage for your databases.

Wrap Up Part 1

Flex ASM disk groups are hugely interesting and worth keeping an eye out for when you are testing Oracle 12.2. I admit that 12.2 is still quite new, and the cautious person I am won’t use it for production until the first major patch set comes out and I tested it to death. I am also curious how the use of the ASM Filter Driver will change the way I am working with ASM. It might be similar to ASMLib but I have yet to work that out.

In the next part(s) I will cover additional concepts you need to understand in the context of Flex ASM disk groups.

Taming XML data in the v$cell% family of Exadata views

While researching Exadata 12.2 features that have been introduced with Oracle 12.2.1.1.0 I ran into an old problem of mine, querying the v$cell% views that are new with Exadata 12.1.1.1.0 and Oracle RDBSM 12c Release 1 in a meaningful way. In case you haven’t seen them yet, these views expose cell metrics and other information in the database without having to actually connect to the cells and execute cellcli commands. They are very useful, and form the basis of the latter part of the Exadata AWR reports.

On our 12.2 Exadata system (using cellos 12.2.1.1.0 and RDBMS 12.2.0.1.0) the following views are exposed to the curious researcher:

SQL> select table_name from dict 
  where table_name like 'V%CELL%' 
  order by 1;
                                                  
TABLE_NAME
-------------------------------------
V$CELL
V$CELL_CONFIG
V$CELL_CONFIG_INFO
V$CELL_DB
V$CELL_DB_HISTORY
V$CELL_DISK
V$CELL_DISK_HISTORY
V$CELL_GLOBAL
V$CELL_GLOBAL_HISTORY
V$CELL_IOREASON
V$CELL_IOREASON_NAME
V$CELL_METRIC_DESC
V$CELL_OFL_THREAD_HISTORY
V$CELL_OPEN_ALERTS
V$CELL_REQUEST_TOTALS
V$CELL_STATE
V$CELL_THREAD_HISTORY

17 rows selected.

There is a lot of useful info to be found in these, and most of the views are explained in Expert Oracle Exadata (2nd edition), chapter 11. I don’t always have a copy of the book at hand which is why I am putting this post together. To add some value, I am going to try and explain how I get to the results as well :)

Querying the views

For example if you are interested in your cell’s configuration, v$cell_config can be queried.

SQL> describe v$cell_config

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 CELLNAME                                           VARCHAR2(1024)
 CELLHASH                                           NUMBER
 CONFTYPE                                           VARCHAR2(15)
 CONFVAL                                            CLOB
 CON_ID                                             NUMBER

SQL>

This view is a good representation of the way most of them are set up. There is a cell identifier (CELLNAME in the example), a configuration type (CONFTYPE) and then actual data stored as XML (stored as CONFVAL). Instead of storing the XML information as an XMLTYPE, a CLOB is more commonly found. When getting data from the CLOB it is often difficult to read, which is why I usually cast it to an XMLTYPE which formats nicer in SQLcl and SQLPlus.

A small example of getting information from this view is shown here. I am interested in the IORM plan for a specific cell:

SQL> select xmltype(confval) from v$cell_config 
  where cellname = '192.168.10.9' 
    and conftype = 'IORM';

XMLTYPE(CONFVAL)
--------------------------------------------------------------------------------
<?xml version="1.0" encoding="US-ASCII"?>
<cli-output>
  <version>1.0</version>
  <timestamp>1498120597819</timestamp>
  <context cell="someCell"/>
  <iormplan>
    <name>someCell_IORMPLAN</name>
    <catPlan/>
    <dbPlan/>
    <objective>basic</objective>
    <status>active</status>
  </iormplan>
</cli-output>

SQL>          

One alternative way to do so is to query the cell directly, while logged in:

[root@someCell ~]# cellcli -e list iormplan detail
         name:                   someCell_IORMPLAN
         catPlan:                
         dbPlan:                 
         objective:              basic
         status:                 active
[root@someCell ~]#

Except that cells are most often not accessible to DBAs.

Transforming XML

Just looking at the XML query output is good enough for small chunks of XML, however when investigating a larger XML document this quickly becomes impractical.

Back to the motivation for writing this post: a very useful view I wanted to query for information about statistics on columnar cache is named v$cell_state. I have used the Smart-Scan Deep Dive blog as a source. It features an example on how to extract a specific statistic from the XML data. I have extended the example to translate all statistics pertaining to the columnar cache into the more familiar format.

The XML in question is rather deeply nested. I have shortened the other output to give you an idea of the data I care about:

<cell_stats>
  <stats type="scheduler">...</stats>
  <stats type="network_directory">...</stats>
  <stats type="cache">...</stats>
  <stats type="MemCache">...</stats>
  <stats type="flashcache">
    <stats type="columnarcache">
      <stat name="columnar_cache_size">26673152</stat>
      <stat name="columnar_cache_keep_size">0</stat>
      <stat name="columnar_cache_keep_read">0</stat>
  ...

I need all the information in the ‘<stats type=”columnarcache”>’ part, lines 8 and following. The most useful way to translate XML to what looks like a relational table I found is based on a rather complex function, named XMLTABLE(). It is explained in the XML DB Developer’s Guide, chapter 4 XQuery and Oracle XML DB. Using that function I can translate XML into a relational format.

It is probably easiest to show you the query and then walk you through. According to Oracle’s blog post I can find the information about the columnar cache in v$cell_state. I don’t have to worry about the different statistics_type values because the filtering of output will be done on the XML using what is called an XPATH expression. I would like to check a single cell only, to demonstrate that it is possible to combine the output of XMLTABLE with other views/tables and query them using “regular” SQL:

SQL> select b.*
  from v$cell_state a,
  xmltable (
      '/cell_stats/stats/stats[@type="columnarcache"]/stat'
      passing xmltype(a.statistics_value)
      columns
          name   varchar2(50) path '@name',
          value  number       path '.'
  ) b
  where a.cell_name = '192.168.10.9'
  and b.value <> 0
 order by b.value;

Unsurprisingly I need to pass the XML data to the XMLTABLE in order to process it further. The XML data is found in v$cell_state.statistics_value. I need to access the view and pass the relevant column to the XMLTABLE function’s “passing” clause. An XMLTYPE is expected here, which is why I have to convert the LOB.

The XMLTABLE function should only process data that belongs to the nodes below ‘<stats type=”columnarcache”>’. An XPATH expression helps me defining the scope. This is actually where I spent most of the time because writing correct XMLPATHs doesn’t come naturally to me. Have a look at Wikipedia if XPATH is completely alien to you.

Getting the XPATH right can be challenging if you don’t do it every day. I learned from Marco Gralike who did something very similar to my investigation that it is possible to return the pruned XML tree structure as an XMLTYPE. I wasn’t quite sure for example which part of the XML document I wanted to use for the XMLTABLE’s columns clause. I went through a couple of iterations to find “my” subset of data. Here is one way to do this according to Marco’s blog post:

SQL> select b.cc
  from v$cell_state a,
  xmltable (
      '/cell_stats/stats/stats[@type="columnarcache"]'
      passing xmltype(a.statistics_value)
      columns cc XMLTYPE PATH '*'
  ) b
  where a.cell_name = '192.168.10.9';

You will get XML back (the column returned by XMLTABLE is cast to XMLTYPE in line 6), and then use that as the basis for further analysis.

Once the XML returned contains all the data you need, you can continue with writing the XMLTABLE’s columns clause to extract information from the XML document. That is where I ran into the next problem: I had syntax issues trying to reference the “self” node (which is a dot as you see in the full example in line 8) which is why I experimented a bit. It turned out I had to specify the column name, data type, and path before referencing the element.

The tricky bit working with the XML data is finding the correct XPATH, at least for me, because I am not an XML developer. I found it useful to start in the row containing the information I want and then work my way up the tree until I reach the root element. Playing around a bit more I managed to shorten my XPATH to just this one:

'//stats[@type="columnarcache"]/stat'

I still prefer the “complete” path expression because it gives me a clue where in the DOM model that information is stored.

Wrapping Up

If you want the complete picture for your entire Exadata cluster, simply add the cell name to the query:

SQL> select a.cell_name,b.name, b.value
from v$cell_state a,
xmltable (
    '/cell_stats/stats/stats[@type="columnarcache"]/stat'
    passing xmltype(a.statistics_value)
    columns 
        name   varchar2(50) path '@name',
        value  number       path '.'
) b
where b.value <> 0
order by a.cell_name,b.value;

Please have a look at Marco’s post where he explains a more complex scenario of nesting XMLTABLE functions, depending on what information you are after.

Hope this helps!