PROBLEM:

Application Team is running lot of DML activities on the database and suddenly database became slow with lot of blocking sessions with
“Block Change Tracking Buffer” event.

Also, the alert log was filled with below error:

Errors in file /u01/app/oracle/admin/PROD/diag/rdbms/d2crmsit/PROD/trace/DPROD_ctwr_8664.trc  (incident=163240):
ORA-04031: unable to allocate 46940008 bytes of shared memory ("large pool","unknown object","large pool","CTWR dba buffer")
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /u01/app/oracle/admin/PROD/diag/rdbms/PROD/trace/PROD_ctwr_8664.trc  (incident=163241):
ORA-04031: unable to allocate 46940008 bytes of shared memory ("large pool","unknown object","large pool","CTWR dba buffer")
Use ADRCI or Support Workbench to package the incident.

SOLUTION:

This issue is related to block change tracking. This wait event occurs when block change tracking file reached its maximum size and unable to extend it.

First, check whether block change tracking is enabled or not.

SQL> select filename,status from v$block_change_tracking;
 
FILENAME
--------------------------------------------------------------------------------
STATUS
----------
/export/home/oracle/RMAN/TESTDB/TRACKING_FILE/block_change_TESTDB.log
ENABLED

As we can see block change tracking is enabled. Now how will fix this issue.

Solution 1:

If large_pool_size is set in the database, Oracle usually allocated the size for block change tracking file from large_pool_size. So try increasing the size of the large_pool_size parameter.

Solution 2:

There is a hidden parameter “_bct_public_dba_buffer_size”, which defines the size of the block change tracking. You can try setting a higher value for this hidden parameter. However, hidden parameters shouldn’t be used unless suggested Oracle support.

Solution 3:

The easiest one is to disable the block change tracking. Sometimes this command can take around 1 to 2 minutes if the database is heavily loaded.

SQL>alter database disable block change tracking ;

database altered

SQL> select filename,status from v$block_change_tracking;

FILENAME
——————————————————————————–
STATUS
———-

DISABLED

Once one implement any of the solutions, this errors from the alert log and the wait events will be cleared. And database will come to normal.