What is Supplemental Logging & Its Importance in Data Replication.

This post covers about Supplemental Logging and its importance in Data Replication via any tool which use redo or archive log to capture the CDC changes and propagate them to Target database (ENDPOINT).

What & Why Supplemental Logging?

Redo log files are generally used for instance recovery and media recovery. The data needed for such operations is automatically recorded in the redo log files. However, a redo-based application or replication tool may require few additional columns to be logged in the redo log to maintain the database Acid properties. The process of logging these additional columns or info is called supplemental logging.

Supplemental logging as the name suggests is extra logging, required to uniquely identify a row on Target Database when a row is updated/deleted from Source Database. This additional information allows rows to be located when the ROWID is unavailable. Also, these additional columns help the replication tools to identify the rows which need to be updated on the on the destination side.

By default, Oracle Database does not provide any supplemental logging, which means that it is not enabled by default. Therefore, you must enable at least minimal supplemental logging before generating log files which will be analyzed or mined by the configured replication tool.

The following are examples of situations in which additional columns may be needed by the replication tool.

• An application that applies reconstructed SQL statements to a different database must identify the update statement by a set of columns that uniquely identify the row (for example, a primary key), not by the ROWID because the ROWID of one database will be different and therefore meaningless in another database.
• An application may require that the before-image of the whole row be logged, not just the modified columns, so that tracking of row changes will be more efficient.

A supplemental log group is the set of additional columns to be logged when supplemental logging is enabled. There are two types of supplemental log groups that determine when columns in the log group are logged:

• Unconditional supplemental log groups: The before-images (mostly require in term of update and Delete) of specified columns are logged any time a row is updated, regardless of whether the update affected any of the specified columns. This is sometimes referred to as an ALWAYS log group.
• Conditional supplemental log groups: The before-images of all specified columns are logged only if at least one of the columns in the log group is updated.

Supplemental log groups can be system-generated or user-defined.

In addition to the two types of supplemental logging, there are two levels of supplemental logging, as described in the following sections:

• Database-Level Supplemental Logging
• Table-Level Supplemental Logging

Database-Level Supplemental Logging

There are two types of database-level supplemental logging:

1. minimal supplemental logging
2. Database-Level identification key logging,

Minimal supplemental logging does not impose significant overhead on the database generating the redo log files. Normally we use this when using Replication Via Golden gate or using Dataguard.

enabling database-wide identification key logging can impose overhead on the database generating the redo log files. Oracle recommends that you at least enable minimal supplemental logging.

Minimal Supplemental Logging

Minimal supplemental logging logs the minimal amount of information needed for LogMiner to identify, group, and merge the redo operations associated with DML changes. It ensures that LogMiner (and any product building on LogMiner technology) has sufficient information to support chained rows and various storage arrangements, such as cluster tables and index-organized tables. To enable minimal supplemental logging, execute the following SQL statement:

• LogMiner/or Log mining is a term or internal Functionality where any replication tool process mine the LCR(Logical Change records Stored in Log buffer Cache inside redo entries)  to extract the Sql Statement

Database-Level Identification Key Logging

Identification key logging is necessary when redo log files will not be mined at the source database instance, for example, when the redo log files will be mined at a logical standby database when using oracle DR solution.

Using database identification key logging, you can enable database-wide before-image logging for all updates by specifying one or more of the following options to the – This is normally

SQL> sqlplus / as sysdba

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

This option specifies that whenever a row is updated, all columns of that row (except for LOBs, LONGS, and ADTs) are placed in the redo log file.

• PRIMARY KEY system-generated unconditional supplemental log group

This option causes the database to place all columns of a row's primary key in the redo log file whenever a row containing a primary key is updated (even if no value in the primary key has changed).

If a table does not have a primary key but has one or more non-null unique index key constraints or index keys, then one of the unique index keys is chosen for logging as a means of uniquely identifying the row being updated.

If the table has neither a primary key nor a non-null unique index key, then all columns except LONG and LOB are supplementally logged; this is equivalent to specifying ALL supplemental logging for that row. Therefore, Oracle recommends that when you use database-level primary key supplemental logging, all or most tables be defined to have primary or unique index keys.

• UNIQUE system-generated conditional supplemental log group

This option causes the database to place all columns of a row's composite unique key or bitmap index in the redo log file if any column belonging to the composite unique key or bitmap index is modified. The unique key can be due to either a unique constraint or a unique index.

• FOREIGN KEY system-generated conditional supplemental log group

This option causes the database to place all columns of a row's foreign key in the redo log file if any column belonging to the foreign key is modified.

Keep the following in mind when you use identification key logging:

• When you enable identification key logging at the database level, minimal supplemental logging is enabled implicitly.
• Supplemental logging statements are cumulative. If you issue the following SQL statements, then both primary key and unique key supplemental logging is enabled:

Table-Level Identification Key Logging

Identification key logging at the table level offers the same options as those provided at the database level: all, primary key, foreign key, and unique key. However, when you specify identification key logging at the table level, only the specified table is affected. For example, if you enter the following SQL statement (specifying database-level supplemental logging), then whenever a column in any database table is changed, the entire row containing that column (except columns for LOBs, LONGs, and ADTs) will be placed in the redo log file:

Use Case:-  (database-level supplemental  Logging) This is done When We want to migrate or process most of Application table as part of Replication to another database.

Use Case:- (table-level supplemental logging )This is done When We want to migrate or process less number or small Subset of Application table as a part of Replication to another database.

The Main Difference between logging at DB and Table is that if you (specifying table-level supplemental logging) then only when a column in the participating table is changed then the entire row (except for LOB, LONGs, and ADTs) of the table be placed in the redo log file. If a column changes in the participating table, then only the changed column will be placed in the redo log file.

Check the Supplemental logging enabled at Database level

SQL> select SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI from v$database;

SUPPLEME SUP SUP

-------- --- ---

NO       NO  NO

Enabling Supplemental Logging at Database Level   *  if you enter the following SQL statement (specifying database-level supplemental logging), then whenever a column in any database table is changed, the entire row containing that column (except columns for LOBs, LONGs, and ADTs) will be placed in the redo log file:

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;

Disable the Supplemental Logging at Database Level

Note: For disable the supplemental logging at database level. You need to follow the following sequence order:

SQL> ALTER DATABASE DROP SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

SQL> ALTER DATABASE DROP SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;

SQL> ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;

SQL> ALTER DATABASE DROP SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;

Enabling Supplemental Logging at table Level  *   if enter the following SQL statement then only when a column in the participating table  is changed then the entire row (except for LOB, LONGs, and ADTs) of the table be placed in the redo log file. If a column changes in the participating table, then only the changed column will be placed in the redo log file.

SQL> ALTER TABLE HR.EMPLOYEES ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

SQL> ALTER TABLE HR.EMPLOYEES ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;

SQL> ALTER TABLE HR.EMPLOYEES ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;

SQL> ALTER TABLE HR.EMPLOYEES ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

 Drop Supplemental Logging at table Level  *   

SQL> ALTER TABLE HR.EMPLOYEES  DROP SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

SQL> ALTER TABLE HR.EMPLOYEES  DROP SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

SQL> ALTER TABLE HR.EMPLOYEES  DROP SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;

SQL> ALTER TABLE HR.EMPLOYEES  DROP SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;

Oracle supports user-defined supplemental log groups also. With user-defined supplemental log groups, you can specify which columns are supplementally logged.

We can specify conditional or unconditional log group

What is conditional Group

The before image of all the columns are logged into the redo log file even if at least one of the columns in the supplemental log group is updated.

What is unconditional log groups or Always log Group

The before image of the column to be logged in to the redo log file even if there is no changes happen that column and which have supplemental logging enabled

unconditional log group

SQL> ALTER TABLE HR.EMPLOYEES  ADD SUPPLEMENTAL LOG GROUP emp_parttime (EMPLOYEE_ID, LAST_NAME,DEPARTMENT_ID) ALWAYS;

conditional log group

SQL> ALTER TABLE HR.EMPLOYEES  ADD SUPPLEMENTAL LOG GROUP emp_fulltime (EMPLOYEE_ID, LAST_NAME,DEPARTMENT_ID);

Drop user-defined supplemental log groups

SQL> ALTER TABLE HR.EMPLOYEES  DROP SUPPLEMENTAL LOG GROUP emp_fulltime;

Check the Supplemental logging for Table

SQL> select count(*) from ALL_LOG_GROUPS where LOG_GROUP_TYPE='ALL COLUMN LOGGING' and OWNER= 'HR' and TABLE_NAME='EMPLOYEES';

For a particular table, you can find if a Supplemental Log group has been created for a particular table with the query below. If it returns a row, Supplemental Logging is turned on. If it returns “no rows selected”, then Supplemental Logging is not turned on.

COLUMN LOG_GROUP_NAME HEADING 'Log Group' FORMAT A20

COLUMN TABLE_NAME HEADING 'Table' FORMAT A20

COLUMN ALWAYS HEADING 'Type of Log Group' FORMAT A30

SELECT

LOG_GROUP_NAME,

TABLE_NAME,

DECODE(ALWAYS,

'ALWAYS', 'Unconditional',

NULL, 'Conditional') ALWAYS

FROM DBA_LOG_GROUPS

where OWNER='<table owner>' and

TABLE_NAME='<table name>';

We can find which columns are part of the Supplemental Log group with the query

select LOG_GROUP_NAME, COLUMN_NAME, POSITION from

dba_log_group_columns

where OWNER='<table owner>' and

TABLE_NAME='<table name> '

order by position;

How Qlik Replicate handle or implement Supplemental Logging.

if Supplemental logging is enabled at database level then you find below info message in Task Log.

[SOURCE_CAPTURE] I:  SUPPLEMENTAL_LOG_DATA_ALL is set (oracle_endpoint_conn.c:138)

FOR table level.

Qlik replicate provide below option to enable supplemental logging at table level. By enable the Check box

Whenever a new table is added to the configured task Qlik replicate execute below command

• If table have Primary key defined then below command will be executed if no primary key is defined then you may see Warning in task log. It does not pose any issue to configured task however you mey exepreince perfomrance issue for the task.  

ALTER TABLE TABLE.NAME ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS.

if Above option is not selected then DBA team need to Manage Supplimental logging for participating table as per their business Requirment and below info message will be in the task logs.

[METADATA_MANAGE] I:  Supplemental logging automatic definition is disabled (oracle_endpoint_imp.c:871)

1. Full Composition Usage

It is an example where the complete composition of a Set Analysis Expression is utilized, including the Identifier, Operators, and Modifiers

Syntax:

Aggregation({Identifier <Dimension1 = {"Filtered Value"}, Dimension2 = {"Filtered Value"} >} field)

Example:

Sum({1 <Region={"Chicago"}, Product-={"Lamborghini"}>} Sales}

Qlik will accept three types of Syntax for string representation (“”, ”, []) Double Quotes, Single Quotes, OR Square brackets

Sum({<Region ={ “Chicago” }>} Sales)

 

Sum({ <Region ={ 'Chicago' }> } Sales)

 

Sum({ <Region ={ [Chicago] }> } Sales)

Sum of motor car sales in Texas excluding Tesla. Without changing the user’s filter.

2. Composition with multiple values

There will be instances where multiple values against multiple dimensions are advantageous to be set and defined.

The composition of the setting against specific dimensions allows one to use values or properties that you wish to include or exclude.

Syntax:

{Selection < Dimension1 = {value1, value 2, …} [, Dimension2 = {value1, value2, …}] >}

Example:

Sum({$< Region={"Texas","California"}, Product = {"Tesla","Lambo","Aston Martin"} >} Sales)

We return the sum of sales in Texas and California of Tesla, Lamborghini, and Aston Martin vehicles.

Sum($< Region= {}, Product{ "Tesla","Lambo","Aston Martin" } > Sales)

The sum of sales of Tesla, Lamborghini and Aston Martin vehicles where the region field is empty is returned.

3. Search Composition within advanced Qlik Set Analysis

You can place search conditions into the composition of the Set Analysis Expression.

Syntax:

{<Dimension = {" *string or value to be searched* "}>}

Wildcard characters are used * and ?

• * = interpreted against any sequence of characters
• ? = interpreted against any single character

Multiple searches can be performed:

{< Dimension = {"*partialVal*", "fixed text", "?erm*"} >

4. Composition using the dimension of numbers

Qlik evaluates dates and timestamps as numbers; leverage over this functionality can ease date intervals when interpreted as numbers.

Making use of GREATER THAN or LESS THAN in the Set Analysis Expressions returns predefined data as specified.

Syntax:

{< Year = {"<2022”} >}  

 All records where the year is less than 2022

{< Sales= {"<100000”} >}

All records where Sales totals are less than 100,000

{< Year = {">=2021 <=2022"} >}

All records where the year is between 2021 to  2022

{< Sales= {">=50000 <=100000”} >}

All records where Sales are between 50,000 and 100,000

5. Composition with a numerical function

Using numerical functions like MAX(), RANK(), COUNT() or AVG() within the expression is also possible.

Syntax:

<Year = {"$(=max({1} Year))"}>

 Setting to return the last year of data

Placing the identifier {1} into the max function ensures maintaining the last year, when selections are made.

Sum({<MotorVehicle = {"=rank(sum(Price), 4)<= 10"}>} Price)

Top 10 Highest Price vehicles

Sum({<MotorVehicle = {"=rank(sum({<Manufacturer = {“Ford”}>} Price), 4)<= 10"}>} Price)

Top 10 Highest Price vehicles by Manufacturer

 

This is a breakdown and explanation of the new XIRR function implementation that is currently being rolled out to Qlik Cloud Services. This effect the use of XIRR chart and script function as well as the RangeXIRR function. There are two changes that impacts your uses of XIRR, listed as 1 and 2 below:

1. Fix for the "loan" scenario

The XIRR function is defined as calculating the internal rate of return of an investment, where an "investment" means some opportunity to spend money initially to later earn more money than was spent initially, making a profit.

The fix for the XIRR function enables the correct calculation of also the inverted scenario of an investment. This is a scenario where you are receiving money initially, and then later repay the money probably at a loss. I don’t have a financial/economics education, but I would call it “interest rate” rather than “rate of return” since it has the characteristics of a loan. One example would be if you have a payment plan for repaying a loan with predetermined payment sizes and dates. You could use XIRR to calculate the yearly interest rate equivalent of what you are paying for the loan.

Example:

The new XIRR implementation returns 0.1016.

The old implementation returns NULL for this example. For other “loan” scenarios the old implementation might return -0.99 or close to that. Note that XIRR never returns a lower value than -1. This is true both for the old and the new implementation. This is just part of XIRRs nature.

2. Adjustment for the multiple-IRR scenario

Under certain circumstances it is possible for the XIRR equation to have more than one solution. This is known as the “multiple-IRR problem” and is caused by a non-normal cash flow stream (also called unconventional cash flow).

Example of a non-normal cash flow stream:

In this example we have an outflow (negative value) at the beginning and also at the end, with an inflow (positive value) in between. This is a non-normal cash flow stream because the payments switch sign twice.

The example has two solutions, one is -0.309 and the other one is 0.809.

The new Qlik XIRR implementation will always search for a solution with a positive answer before searching for a negative one. It starts from zero and searches towards infinity. It is not possible to ask XIRR for more solutions or guide it to look for an answer in a certain range of values. The old XIRR might pick a negative solution even when a positive one exists.

“Normal” cash flow stream Explanation:

Note that a “normal” cash flow stream is guaranteed to have only one solution, so in that case you don’t need to wonder if there could be other solutions. “Normal” cash flow stream means that all payments with the same sign (positive or negative) is in a continuous group. The first example presented in this article follows the normal cash flow pattern, so does the example below:

Example of a normal cash flow stream:

The XIRR for this example is -0.1287

Summary:

So as a summary, a "normal" cash flow stream is one where the sign switches only once, such a stream can resemble either an investment or  a loan depending on if it starts with inflow or outflow payments. The XIRR equation for a "normal" cash flow stream has only one solution, which our new XIRR implementation will return even when it is of the "loan" type. A "non normal" cash flow stream is one where the sign switches more than once. This makes it a hybrid between an investment and a loan, although for example the "investment" part might be dominant over the "loan" part. A non normal cash flow stream can possibly have more than one solution. Our new XIRR will prioritize a positive solution over a negative one.

Reloading an app updates it with the latest data from the app data sources. The QRS API or engine API can be used to trigger a reload but there are differences in how these APIs handle the requests.

The Engine does not limit the number of concurrent reloads so the reload requests will be placed in a queue, which may cause the Engine to be overloaded by reload calls. Note that the behavior of the Engine API also applies to the Data Load Editor. If several app developers trigger reloads through the Data Load Editor, the Engine will not limit the number of concurrent reloads.

When using the QRS API, the Scheduler service has a mechanism to control the number of concurrent reload tasks. It will recognize existing reloads currently in progress and discard the other tasks.

It is useful to keep in mind that, in this scenario and in the case of using a synchronous repository API call, the expected GUID returned is: 00000000-0000-0000-0000-000000000000.

Hello to all,

I have following strange behaviour:

A user with no roles but with an pro license can replace an app in QMC but when he wants to import a new app he gets an REST timeout error after 1 second. The logs show no more info than that.

I can import any apps  but have all roles.

If I give the user the contentadmin role than he can import the app.

I am confused now because he said to me that he could 2 months ago import apps. But I don't know if he had the contentadmin role.

The question is:  does a user need special rights to import an app? 
We have an app creation secrurity rule aith app_* resource filter an crate actions enabled for all users which are not anonymous.

Thanks a loz for your answers