Unlogged option in Adobe Campaign Classic tables
Campaign Classic audience tables (grp*, wkf*_grp*, lst*) are created with the UNLOGGED option which means their data can be lost if the database crashes, and may not be backed up. This document provides a thorough description of this issue and the steps to its resolution.

Note:

The issue has been fixed on all customer instances hosted by Adobe.

Concepts

Campaign Classic lists

A list is a static set of profiles that can be targeted in delivery actions or updated during import operations or during workflow execution. For example, a population extracted from the database via a query can supply a list.

For more information on the list and group feature, see Campaign Classic documentation.

Unlogged table (PostgreSQL)

Data written to unlogged tables is not written to the write-ahead log (WAL) which makes them considerably faster than ordinary tables. However, they are not crash-safe: an unlogged table is automatically truncated after a crash or unclean shutdown. The content of an unlogged table is also not replicated to standby servers. Any indexes created in an unlogged table are automatically unlogged as well.

Recent backup strategies rely on the WAL, which means that in practice, lists are not being backed up. For more information, see How to increase PostgreSQL write performance

Nologging table (Oracle)

Although we were unable to reproduce the data loss experienced in PostgreSQL, we still recommend to  convert to Oracle LOGGING status tables where loss of data is critical. It should be noted that moving from NOLOGGING to LOGGING will increase the disk space utilization of the redo and undo logs.

Although highly unlikely, check that the tablespace where the tables reside is also marked as LOGGING. As with PostgreSQL, tables that are created with NOLOGGING do not replicate because they are not written to the redo logs. Note that an abnormal crash could lead to the loss of data from any table designated as NOLOGGING.

A simple script can be used to identify and generate a script to convert the NOLOGGING tables to LOGGING.

For example:

select 'ALTER TABLE ' || table_name || ' LOGGING;'
from dba_indexes where logging='NO'
AND substr(table_name,1,3) in ('GRP','LST','SHD');

Issue

List dataloss

On Adobe Campaign Classic, dataloss can happen on lists of type List and Group.

On instances ran or running on a impacted build, list tables are created in unlogged. Consequently if an incident occurs in the database leading to an unclean shutdown (like crash), the content of the list tables will be lost.

Impacted build

The impacted build depends of the database server used. For more information, see Database documentation.

SQL Server

SQL Server is not impacted - list tables are logged.

PostgreSQL

PostgreSQL impacted versions are:

build number >= 8795 AND build number < 8977 AND build number != 8937

Oracle

Oracle impacted versions are:

build number >= 5544 AND build number < 8977 AND build number != 8937

Resolution

Transform unlogged tables to logged tables

The Database Administrator needs to manually perform the unlogged to logged transformation of list tables.

List impacted tables

The impacted tables criteria are: 

  • unlogged table
  • tablename like 'grp%' OR tablename like '%_grp%'

Apply transformation

The Database Administrator needs to follow database documentation recommendations.

Prevent new list table to be unlogged

Adobe highly recommends to upgrade the instance to a build containing the fix.

  • Adobe Campaign Classic 18.4, build 8937+
  • Adobe Campaign Classic 18.10