OCI ExaCS - Safeguarding Oracle Golden Gate Deployment EXTRACT from "billion" updates on tables

{ Abhilash Kumar Bhattaram : Follow on LinkedIn }

There was a recent problem in one of the applications I was supporting , there seemed to be some sort of an Application bug which caused some tables being updated 1.5 billion times in a day , now this is a huge number. This database was in a source extract which was sitting comfortably on ExaCS and processing all these updates.

I realized that there was one setting which seemed to be a running train towards me , of course I realized this after the train hit me.

The default Archive Log retention for ExaCS databases is one day when the OCI automatic Jobs are enabled.

# As ExaCS root user
# cat /var/opt/oracle/creg/<dbname>.ini | grep bkup | grep fra
bkup_archlog_fra_retention=1

So why is this a problem , when I have 1.5 Billion updates my extract lags are going to get increased over time and if this reaches more than a day , my Extracts are going to ABEND at some point due to lack of required archive logs !!!

Below is the fix to increase the FRA retention from 1 day to 3 days

# As ExaCS root user
# /var/opt/oracle/ocde/assistants/bkup/bkup -dbname=<dbname> -bkup_archlog_fra_retention=3 -bkup_oss=yes

This gives me enough buffer window to ensure my extracts do not abend while the real issue of the billion updates are being fixed.

NOW FOR THE FUN PART - THE TRAIN HAS HIT YOU

Meaning you did not realize this setting existed and Extracts Abended , you need you restore these Archive logs from ExaCS Backups so that you can get the extracts back running.

Firstly I would like to know what current settings are

# As ExaCS root user
# /var/opt/oracle/bkup_api/bkup_api get_config_info --all --dbname <dbname> | grep bkup_rman_retention

    "bkup_rman_retention": "31", --->>> This means I have 31 days of Backups 

Now how to restore the archive logs from ExaCS backups

# RMAN Commands from the respective Database once the relevanrt sequences are identified for both threads as in my case

run
{
allocate channel t1 type 'SBT_TAPE' PARMS 'SBT_LIBRARY=/var/opt/oracle/dbaas_acfs/<dbname>/opc/libopc.so, ENV=(OPC_PFILE=/var/opt/oracle/dbaas_acfs/<dbname>/opc/opcdbname.ora)';
restore archivelog sequence between 12345 and 54321 thread 1;
release channel t1;
}



run
{
allocate channel t1 type 'SBT_TAPE' PARMS 'SBT_LIBRARY=/var/opt/oracle/dbaas_acfs/<dbname>/opc/libopc.so, ENV=(OPC_PFILE=/var/opt/oracle/dbaas_acfs/<dbname>/opc/opcdbname.ora)';
restore archivelog sequence between 12345 and 54321 thread 2;
release channel t1;
}

Once you have all the required sequences , the extracts needs to be started NORMALLY and extracts will eventually catchup

NOTE : These restored Archive Logs need to be deleted manually and these will not be included in ExaCS RMAN settings as these were manually restored , so a manual clean up is needed.

#oracle #oci #oracledatabase #goldengate #curl #api #oraclecloud #oracledba #nabhaas