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.
Martins Blog 