Tuesday, October 1, 2019

Scaling Up RECO Diskgroup in OCI VM DBs

The single instance OCI DBs (created on VM) comes with two ASM disk groups, DATA and RECO. RECO disk group is set as the value for db_recovery_file_dest. As such is the default location for archiving redo logs before they backed up and cleared. There's no direct way to increase the size of this disk group. Minimum size of it is 256 GB. As per 2404615.1 size of the RECO disk group is automatically increased as you add more space to the database and usually it's quarter of the size of the DATA disk group.
This is slightly misleading as space is not always added to the RECO disk group when you scale up the storage. From the actual testing done, it seems space is only added to RECO diskgroup if the following holds
(data disk group size / 4 ) > current RECO size
Starting with the minimum storage size possible for a database i.e. 256 GB, the disk group sizes are as below.
SQL> select name,total_mb,free_mb,TYPE  from v$asm_diskgroup;

NAME                             TOTAL_MB    FREE_MB TYPE
------------------------------ ---------- ---------- ------
DATA                               262144     253604 EXTERN
RECO                               262144     258496 EXTERN
The storage scale up has to be done to a higher value. This means if current data size is 256 GB then another 256 GB cannot be added.
As the error message reads the minimum scale up value is 512 GB.
To scale up select "Scale Storage Up" button on the DB System Details page and select 512 GB as the data storage size.
At the end of the scale up the final storage size on the DB detail page will be as below.
If the MOS note mentioned earlier to be followed the RECO disk group should be 384 GB (Current size 256 GB + 512 / 4). But when the ASM disk group view is queried it shows that RECO disk group size hasn't changed.
SQL>  select name,total_mb,free_mb,TYPE  from v$asm_diskgroup;

NAME                             TOTAL_MB    FREE_MB TYPE
------------------------------ ---------- ---------- ------
DATA                               524288     515748 EXTERN
RECO                               262144     258496 EXTERN
Test repeated with a new DB (starting size of 256 GB) and scaling up the storage to 1024 GB.
At the end of the scale up the storage values.
Again the RECO disk group size remains the same though data disk group has scaled up.
SQL> select name,total_mb,free_mb,TYPE  from v$asm_diskgroup;

NAME                             TOTAL_MB    FREE_MB TYPE
------------------------------ ---------- ---------- ------
DATA                              1048576    1040032 EXTERN
RECO                               262144     258492 EXTERN


As the final test, starting from a new DB (256 GB initial size) storage is scaled up to 2 TB.
After the scale up the storage sizes.
Querying the disk group sizes shows the RECO disk group has increased in size.
SQL> select name,total_mb,free_mb,TYPE  from v$asm_diskgroup;

NAME                             TOTAL_MB    FREE_MB TYPE
------------------------------ ---------- ---------- ------
DATA                              2097152    2088372 EXTERN
RECO                               417792     412960 EXTERN
Current size of the RECO diskgroup is 408 GB. However the new size is not nearly the quarter of the data volume (2048/4 = 512 > 408). The ratio of RECO:DATA is close to 1:5. As the MOS says "usually" 1/4 of the data volume one has to accept 408 GB as the scaled up size until the MOS updated with a better explanation.
It is still not clear how to scale up the RECO disk group to a predetermined size. What's clear is that storage must be scaled up at least by 2TB in order to increase the RECO disk group from the initial size of 256GB.

Related Metalink Note
How to add more space to RECO diskgroup for a virtual machine DB system in OCI [ID 2404615.1]