DEV Community

Abhilash Kumar Bhattaram for Nabhaas Cloud Consulting

Posted on • Updated on

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

Enter fullscreen mode Exit fullscreen mode

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

Enter fullscreen mode Exit fullscreen mode

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 
Enter fullscreen mode Exit fullscreen mode

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;
}

Enter fullscreen mode Exit fullscreen mode

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

Top comments (0)