Disclaimer

Sunday 11 July 2021

Restricting Disk Space Usages for a Database in DiskGroup

Can we restrict the usages of space in a diskgroup for a database to a defined size just like we define quota for a user in each tablespace?


Yes you can if you are on version 12.2.

Continue reading this article to see how Starting with oracle 12c Release 2 it is possible to restrict the space usage in diskgroup for a database using ASM QutoaGroup feature. 

An ASM quota Group is only possible with ASM FLEX diskgroup.

To Know more about ASM Flex DiskGroup, ASM File Group and ASM Quota Group read the related posts:-
Oracle ASM Flex Redundancy and Flex Disk Groups
What is Oracle ASM File Groups
What is ASM Quota Groups


PreReq:
To achieve space restriction for a database in a diskgroup 

1> You must be using 12.2
2> You must have decided for FLEX Diskgroup. 
3> You must have created a File group 

Once you are done with the prereq follow the below steps.

Step1> I created a pluggable database PDB1

SQL>  create pluggable database PDB1 admin user pdb1 identified by pdb1 file_name_convert=('+DG_TST_DATA','+DG_FLEX_DATA');
Pluggable database created.

Note: I want the database PDB1 to Use only maximum 10G of physical storage from DG  +DG_FLEX_DATA


Step2>  Login to ASM Instance and Create File Group in DG_FLEX_DATA for PDB1


SQL> ALTER DISKGROUP DG_FLEX_DATA  ADD FILEGROUP FG_PDB1 DATABASE PDB1;
Diskgroup altered.



Step3> Login to ASM Instance and Create Quota Group of 10G


SQL> ALTER DISKGROUP DG_FLEX_DATA ADD QUOTAGROUP QG_PDB1 SET 'quota'= 10g;
Diskgroup altered.



Step4> Attach the QuotaGroup QG_PDB1 with File Group FG_PDB1

SQL> ALTER DISKGROUP DG_FLEX_DATA MODIFY FILEGROUP FG_PDB1 SET 'quota_group' = 'QG_PDB1';
Diskgroup altered.




Step5: Test if Quota is working.





No comments:

Post a Comment

100 Oracle DBA Interview Questions and Answers

  Here are 100 tricky interview questions tailored for a Senior Oracle DBA role. These questions span a wide range of topics, including perf...