How to access internal OCI DBaaS backup Object Storage bucket

So it all started when someone asked in my company internal mailing list:

Does anyone know is there a way to get size of the native backup bucket for DB backups in OCI?

I can get size of normal OS bucket with this:

oci os bucket get --bucket-name DBaaS_OCIC --fields approximateSize | jq '.data."approximate-size"'

1668397724489

But if I check the bucket (opc container) used in RMAN to configure backups it doesn’t find it, which I think is expected as it’s not visible to customers.

Just trying to find easy way to display how Object Storage usage is distributed to different buckets. Mainly for cost analysis..

Cool, a new challenge was set. Let's see how far I can go in this one.

First thing is to understand what happens when you provision a DBaaS. In the picture below, it's a brand new 19c database and once it gets provisioned, oracle took a backup that I can't see in any of my created buckets. Only on the "Backup" section of the database itself.

This is expected as I haven't pointed anywhere it should be stored.

However, if the database can use this internal bucket, and I'm root in the database compute node, I must also be able to somehow access it ( or at least discover how to access it =D ).

So first analyzing what happens in the background when you click in the "Create Backup" button, it will trigger in the back-end a RMAN command which is similar with the following code:

RMAN> set echo on;
2> set command id to "c9fdcd43-0ff8-4888-9f74-8bb7b17f";
3> report schema;
4> show all;
5> list incarnation of database;
6> set echo on;
7> set encryption on;
8> backup force device type sbt as compressed backupset incremental level 0  SECTION SIZE 64G database tag 'DBTRegular-L01601713121746tIF' format 'DBTRegular-L01601713121746tIF_df_%d_%I_%U_%T_%t_set%s' plus archivelog not backed up tag 'DBTRegular-L01601713121746tIF' format 'DBTRegular-L01601713121746tIF_arc_%d_%I_%U_%T_%t_set%s' ;
9> backup device type sbt as compressed backupset current controlfile tag 'DBTRegular-L01601713121746tIF' format 'DBTRegular-L01601713121746tIF_cf_%d_%I_%U_%T_%t_set%s' spfile tag 'DBTRegular-L01601713121746tIF' format 'DBTRegular-L01601713121746tIF_spf_%d_%I_%U_%T_%t_set%s' ;
10> delete force noprompt obsolete;
11> set encryption off;
12>

So as you can notice, the SBT device configuration is not passed within the RMAN command. So it must be set by default in the CONFIGURE option.

[oracle@db19c ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Sat Oct 3 08:39:50 2020
Version 19.8.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DB1003 (DBID=3939535866)

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name DB1003_IAD1Q8 are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 30 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO '%F'; # default
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET PARALLELISM 1;
CONFIGURE DEVICE TYPE 'SBT_TAPE' BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' FORMAT   '%d_%I_%U_%T_%t' PARMS  'SBT_LIBRARY=/opt/oracle/dcs/commonstore/pkgrepos/oss/odbcs/libopc.so ENV=(OPC_PFILE=/opt/oracle/dcs/commonstore/objectstore/opc_pfile/3939535866/opc_DB1003_iad1q8.ora)';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF;
CONFIGURE ENCRYPTION ALGORITHM 'AES256';
CONFIGURE COMPRESSION ALGORITHM 'LOW' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE;
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO 'SBT_TAPE';
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/19.0.0/dbhome_1/dbs/snapcf_DB1003.f'; # default

RMAN> exit

Recovery Manager complete.

Nice. So here we have:
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' FORMAT '%d_%I_%U_%T_%t' PARMS 'SBT_LIBRARY=/opt/oracle/dcs/commonstore/pkgrepos/oss/odbcs/libopc.so ENV= (OPC_PFILE=/opt/oracle/dcs/commonstore/objectstore/opc_pfile/3939535866/opc_DB1003_iad1q8.ora)';

Now I have a opc configuration file to start with. Checking the file contents...

[root@db19c ~]# cat /opt/oracle/dcs/commonstore/objectstore/opc_pfile/3939535866/opc_DB1003_iad1q8.ora
OPC_HOST=https://swiftobjectstorage.us-ashburn-1.oraclecloud.com/v1/dbbackupiad
OPC_WALLET='LOCATION=file:/opt/oracle/dcs/commonstore/objectstore/wallets/09dcc445-cd8c-4071-a1c5-fe1698eeba78 CREDENTIAL_ALIAS=alias_opc'
OPC_CONTAINER=bGeWSKQbZDLvDLgi3aoN

I have the Object Storage URL path, the container and the credentials (inside a wallet file which the credential alias is alias_opc). If I can somehow retrieve the user/pass from this wallet entry, that's all I need to run a curl and navigate trough the OS using OpenStack OS API.

Let's see check the wallet.

[oracle@db19c ~]$ cd /opt/oracle/dcs/commonstore/objectstore/wallets/09dcc445-cd8c-4071-a1c5-fe1698eeba78
[oracle@db19c 09dcc445-cd8c-4071-a1c5-fe1698eeba78]$ ls -l
total 112
-rw------- 1 oracle oinstall 113215 Oct  3 08:33 cwallet.sso
-rw------- 1 oracle oinstall      0 Oct  3 08:33 cwallet.sso.lck
[oracle@db19c 09dcc445-cd8c-4071-a1c5-fe1698eeba78]$ mkstore -wrl ./ -list -nologo
Oracle Secret Store entries:
oracle.security.client.connect_string1
oracle.security.client.password1
oracle.security.client.username1
[oracle@db19c 09dcc445-cd8c-4071-a1c5-fe1698eeba78]$

Good, this is an auto-login so I don't need any extra effort to extract the info.. Now let's check the contents for the single info it has:

[oracle@db19c 09dcc445-cd8c-4071-a1c5-fe1698eeba78]$ mkstore -wrl ./ -viewEntry oracle.security.client.connect_string1 -nologo
oracle.security.client.connect_string1 = alias_opc
[oracle@db19c 09dcc445-cd8c-4071-a1c5-fe1698eeba78]$ mkstore -wrl ./ -viewEntry oracle.security.client.username1 -nologo
oracle.security.client.username1 = bGeWSKQbZDLvDLgi3aoN
[oracle@db19c 09dcc445-cd8c-4071-a1c5-fe1698eeba78]$ mkstore -wrl ./ -viewEntry oracle.security.client.password1 -nologo
oracle.security.client.password1 = g>07j]h3Lfp[Txxxxx+

Perfect. In summary, what I have in my wallet is:

oracle.security.client.connect_string1 = alias_opc
oracle.security.client.username1 = bGeWSKQbZDLvDLgi3aoN
oracle.security.client.password1 = g>07j]h3Lfp[Txxxxx+

PS: Note that the container name and username are the same. Also the connect_string alias match the one defined in OPC_WALLET variable in opc config file.

Now all I need is to access the object storage. Let's try a curl command:

[oracle@db19c ~]$ v_user="bGeWSKQbZDLvDLgi3aoN"
[oracle@db19c ~]$ v_pass="g>07j]h3Lfp[Txxxxx+"
[oracle@db19c ~]$ v_container="bGeWSKQbZDLvDLgi3aoN"
[oracle@db19c ~]$
[oracle@db19c ~]$ curl -s --user "${v_user}:${v_pass}" https://swiftobjectstorage.us-ashburn-1.oraclecloud.com/v1/dbbackupiad/${v_container}/

And here I get the result.. all the objects in this bucket container.

What if I want to check the used space? Let's simply use jq to sum the value of "bytes" attribute.

[oracle@db19c ~]$ v_user="bGeWSKQbZDLvDLgi3aoN"
[oracle@db19c ~]$ v_pass="g>07j]h3Lfp[Txxxxx+"
[oracle@db19c ~]$ v_container="bGeWSKQbZDLvDLgi3aoN"
[oracle@db19c ~]$
[oracle@db19c ~]$ curl -s --user "${v_user}:${v_pass}" https://swiftobjectstorage.us-ashburn-1.oraclecloud.com/v1/dbbackupiad/${v_container}/ | jq '[.[]."bytes"] | add '
938601435

Or if you don't have jq:

[oracle@db19c ~]$ v_user="bGeWSKQbZDLvDLgi3aoN"
[oracle@db19c ~]$ v_pass="g>07j]h3Lfp[Txxxxx+"
[oracle@db19c ~]$ v_container="bGeWSKQbZDLvDLgi3aoN"
[oracle@db19c ~]$
[oracle@db19c ~]$ curl -s --user "${v_user}:${v_pass}" https://swiftobjectstorage.us-ashburn-1.oraclecloud.com/v1/dbbackupiad/${v_container}/ | grep -E -o '"bytes":[0-9]+' | sed 's/"bytes"://' | paste -sd+ | bc
938601435

So I got my answer in bytes, which is ~ 895 MBs

This Object Storage container has only this backup. If I use oci-cli, as you can see below, I don't have the backup size info. Only the database-size-in-gbs, which is the DB size, not the backup:

$ oci db backup get --backup-id ocid1.dbbackup.oc1.iad.abuwcljsvvlb6ymyoc4x4cmqh42qy2c2gnzq5hr6jw7jhowfrf4n66oegdoq
{
  "data": {
    "availability-domain": "CYtq:US-ASHBURN-AD-1",
    "compartment-id": "ocid1.compartment.oc1..aaaaaaaaysvxrbvyht4goajycwzxiulxvrqygtmed3ugzbpamrydot6xjskq",
    "database-edition": "ENTERPRISE_EDITION_HIGH_PERFORMANCE",
    "database-id": "ocid1.database.oc1.iad.abuwcljs3k7gngtjqgyqkiexolsgxrrmq55sypfwdtp3fawjrzgafnjhxwzq",
    "database-size-in-gbs": 4.181640625,
    "display-name": "Automatic Backup",
    "id": "ocid1.dbbackup.oc1.iad.abuwcljsvvlb6ymyoc4x4cmqh42qy2c2gnzq5hr6jw7jhowfrf4n66oegdoq",
    "lifecycle-details": null,
    "lifecycle-state": "ACTIVE",
    "shape": "VM.Standard2.1",
    "time-ended": "2020-10-03T08:41:47.821000+00:00",
    "time-started": "2020-10-03T08:19:25.263000+00:00",
    "type": "INCREMENTAL",
    "version": "19.8.0.0.0"
  },
  "etag": "2f645393--gzip"
}

But this seems a bit complicated. What If I have 10 DB Systems? Would I need to do it manually 1 by 1?

No. I've created the shell-script below that will help you out with it. All you need is to run this shell on them (maybe using ssh sessions).

It's available here: https://github.com/dbarj/oci-scripts/blob/master/oci_db_os_backup_size.sh

[oracle@db19c ~]$ ./oci_db_os_backup_size.sh
938601435