OCI ExaCS - Safeguarding Oracle Golden Gate Deployment EXTRACT from "billion" updates on tables
Published: 2022-09-05 09:57:09



[](
      <style>
        .libutton {
          display: flex;
          flex-direction: column;
          justify-content: center;
          padding: 7px;
          text-align: center;
          outline: none;
          text-decoration: none !important;
          color: #ffffff !important;
          width: 200px;
          height: 32px;
          border-radius: 16px;
          background-color: #0A66C2;
          font-family: "SF Pro Text", Helvetica, sans-serif;
        { Abhilash Kumar Bhattaram : </style> <a class="libutton" href="https://www.linkedin.com/comm/mynetwork/discovery-see-all?usecase=PEOPLE_FOLLOWS&followMember=abhilash-kumar-85b92918" target="_blank">Follow on LinkedIn</a>) }

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