In earlier releases we could create an ASM Disk Group which could potentially contain the data files of a number of databases. The issue was that we could not perform any storage management at the database level.
If the Disk Group redundancy was set to say HIGH (which is 3-way mirroring), then that applied to every database which had files in that particular ASM Disk Group. Maybe we had a case of test and development sharing the same ASM Disk Group as a production database and we did not wish to have this type of redundancy settings for a non-production database.
Also if a number of databases shared the same ASM Disk Group, there was no way of preventing a certain database from using all the available space in a particular disk group.
Further things like the ASM rebalance power limit could only be set at the ASM Disk Group level and maybe we would like to have a higher rebalance power limit setting for a more critical database as opposed to another database which did not require a very fast rebalance operation.
Starting with Oracle 12c Release 2, Oracle ASM provides database-oriented storage management with Oracle ASM flex groups, file groups and quota groups.
A new feature introduced in Oracle 18c enables a very fast method of cloning for pluggable databases called ASM Split Mirror Cloning which is based on the ASM Flex Disk Group feature.
Now the redundancy of files in a flex disk group is flexible and enables storage management at the database level. Each database has its own file group, and storage management can be done at the file group level, in addition to the disk group level (which was the only level possible earlier).
A flex disk group requires a minimum of three failure groups and the redundancy setting of a flex disk group is set to FLEX REDUNDANCY. The flex disk group can tolerate two failures which is the same as a HIGH redundancy disk group.
Starting in Oracle 18c we can also convert disk groups with NORMAL or HIGH redundancy settings (not EXTERNAL) to flex disk groups.
In this case we use ASM Configuration Assistant (ASMCA) to create the Flex ASM Disk Group and then the File Groups and Quota Groups. Note that the Flex Disk Group needs at least 3 disks.Also now via ASMCA we can view attributes of the ASM Disk Group as well.
ASM SPLIT MIRROR CLONING (new Oracle 18c feature)
We have created a pluggable database PDB1 in the CDB named SALES. Note that each CDB and PDB is assigned its own individual filegroup.
SQL> select FILEGROUP_NUMBER, NAME, CLIENT_NAME, USED_QUOTA_MB, QUOTAGROUP_NUMBER from v$asm_filegroup;
FILEGROUP_NUMBER NAME CLIENT_NAM USED_QUOTA_MB QUOTAGROUP_NUMBER
---------------- -------------------- ---------- ------------- -----------------
0 DEFAULT_FILEGROUP 0 1
1 SALES_CDB$ROOT SALES_CDB$ROOT 5328 1
2 SALES_PDB$SEED SALES_PDB$SEED 1496 1
3 SALES_PDB1 SALES_PDB1 1744 1
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
Prepare the Mirror Copy
Connect to the source pluggable database PDB1 and issue the PREPARE MIRROR COPY command.
SQL> alter session set container=pdb1; Session altered. SQL> ALTER PLUGGABLE DATABASE PREPARE MIRROR COPY pdb1_mirror; ALTER PLUGGABLE DATABASE PREPARE MIRROR COPY pdb1_mirror * ERROR at line 1: ORA-15283: ASM operation requires compatible.rdbms of 18.0.0.0.0 or higher SQL> ALTER PLUGGABLE DATABASE PREPARE MIRROR COPY pdb1_mirror; Pluggable database altered.
We can monitor the progress of the mirror copy being prepared. Connect to the ASM instance and query the v$asm_dbclone_info view.
SQL> select mirrorcopy_name, dbclone_status from v$asm_dbclone_info; MIRRORCOPY_NAME -------------------------------------------------------------------------------------------------------------------------------- DBCLONE_STATUS -------------------------------------------------------------------------------------------------------------------------------- PDB1_MIRROR PREPARING SQL> / MIRRORCOPY_NAME -------------------------------------------------------------------------------------------------------------------------------- DBCLONE_STATUS -------------------------------------------------------------------------------------------------------------------------------- PDB1_MIRROR PREPARED
Split the Mirrored Copy and Create the Database Clone
Note that the prepare and copy step must complete before starting this step. Connect to the CBD root container and issue the CREATE PLUGGABLE DATABASE command with the USING MIRROR COPY clause.
SQL> conn / as sysdba
Connected.
SQL> CREATE PLUGGABLE DATABASE pdb2 FROM pdb1 USING MIRROR COPY pdb1_mirror;
Pluggable database created.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 MOUNTED
SQL> alter pluggable database pdb2 open ;
Pluggable database altered.
After the pluggable database has been created we can see now that the DBCLONE_STATUS column shows the value SPLIT COMPLETED.
SQL> select mirrorcopy_name, dbclone_status from v$asm_dbclone_info; MIRRORCOPY_NAME -------------------------------------------------------------------------------- DBCLONE_STATUS -------------------------------------------------------------------------------- PDB1_MIRROR SPLIT COMPLETED
SQL> alter session set container=pdb2; Session altered. SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- +FLEX_DATA/SALES/8AB11EBA6B714BE3E0536438A8C0CE87/DATAFILE/system.279.1010302271 +FLEX_DATA/SALES/8AB11EBA6B714BE3E0536438A8C0CE87/DATAFILE/sysaux.280.1010302271 +FLEX_DATA/SALES/8AB11EBA6B714BE3E0536438A8C0CE87/DATAFILE/undotbs1.281.10103022 71 +FLEX_DATA/SALES/8AB11EBA6B714BE3E0536438A8C0CE87/DATAFILE/undo_2.284.1010302271 +FLEX_DATA/SALES/8AB11EBA6B714BE3E0536438A8C0CE87/DATAFILE/users.282.1010302271
Using Quota Groups
We create a new Quota Group and set a 2 GB limit to that quota group. We then modify the file group and assign the 2 GB limit quota group to the file group.
We then try to extend one of the data files of the PDB1 database and we see that we get an error because the PDB is trying to use more space in the ASM disk group than what has been set by the quota group.
SQL> ALTER DISKGROUP flex_data MODIFY FILEGROUP sales_pdb1
SET 'quota_group' = 'Q_GRP_SALES_PDB1';
Diskgroup altered.
SQL> select FILEGROUP_NUMBER, NAME, CLIENT_NAME, USED_QUOTA_MB, QUOTAGROUP_NUMBER from v$asm_filegroup;
FILEGROUP_NUMBER NAME CLIENT_NAME USED_QUOTA_MB QUOTAGROUP_NUMBER
------------------ ------------------ ------------------ ------------- -----------------
0 DEFAULT_FILEGROUP 0 1
1 SALES_CDB$ROOT SALES_CDB$ROOT 7120 3
2 SALES_PDB$SEED SALES_PDB$SEED 1496 1
3 SALES_PDB1 SALES_PDB1 1936 4
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 1496 0
2 SALES_Q_GRP 0 12288
3 HR_Q_GRP 4120 5120
4 Q_GRP_SALES_PDB1 1936 2048
SQL> alter database datafile 10 resize 5G;
alter database datafile 10 resize 5G
*
ERROR at line 1:
ORA-01237: cannot extend datafile 10
ORA-01110: data file 10:
'+FLEX_DATA/SALES/8AAAFEAC96597F17E0536438A8C0AD2C/DATAFILE/system.274.101027602
3'
ORA-17505: ksfdrsz:1 Failed to resize file to size 655360 blocks
ORA-15437: Not enough quota available in quota group Q_GRP_SALES_PDB1.