How To Check The Supplemental Log Information In Oracle

How To Check The Supplemental Log Information In Oracle

 

Solution

There are two types of supplemental logging: database supplemental logging and table supplemental logging.

 

Database supplemental logging specifies supplemental logging for an entire database, while table supplemental logging enables you to specify log groups for supplemental logging for a particular table. If you use table supplemental logging, then you can choose between unconditional and conditional log groups.

 

Unconditional log groups log the before images of specified columns any time the table is updated, regardless of whether the update affected any of the specified columns. This is sometimes referred to as an ALWAYS log group. Conditional log groups log the before images of all specified columns only if at least one of the columns in the log group is updated.

 

Supplementing logging at the database level, unconditional log groups at the table level, and conditional log groups at the table level together determine which old values are logged in an update statement or piecewise LOB update.

 

To check whether one or more log groups are specified for the table at the source database, run the following query:

 

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;

 

 

Your output looks similar to the following:

Log Group Table Type of Log Group

 

——————– ——————– ——————————

 

LOG_GROUP_DEP_PK DEPARTMENTS Unconditional

 

LOG_GROUP_JOBS_CR JOBS Conditional

 

To list the columns in a particular log group, query the DBA_LOG_GROUP_COLUMNS data dictionary view. You can also query the V$DATABASE dynamic performance view to display supplemental logging specified at the database level.

 

The GoldenGate ADD TRANDATA command is equivelant to the following SQL statement,

 

alter table [TABLE_NAME] add supplemental log group [GROUP_NAME] ([COLUMN_LIST]) always;

 

The Group_name will usually be GGS_TABLENAME, the COLUMN_LIST is either the primary key columns or unique index columns, or if there is no primary key or unique index, it will be all columns.

 

The GoldenGate DELETE TRANDATA command is equivelant to the following SQL statement

 

alter table [TABLE_NAME] drop supplemental log group [GROUP_NAME]

 

To check the database level supplemental logging info

 

select supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_ui, force_logging from v$database;

 

GoldenGate needs at least “supplemental_log_data_min” to be YES to Extract chained row correctly from the Oracle redo log. See attached doc for the detail explain of what other options are.

 

Example:

SQL> SELECT LOG_GROUP_NAME,TABLE_NAME,DECODE(ALWAYS,’ALWAYS’, ‘Unconditional’,NULL, ‘Conditional’) ALWAYS FROM DBA_LOG_GROUPS;

 

LOG_GROUP_NAME TABLE_NAME ALWAYS

—————————— —————————— ————-

ENC$_LOG_GRP ENC$ Unconditional

GGS_TCUSTMER_74729 TCUSTMER Unconditional

 

SQL> select * from dba_log_group_columns where table_name=’TCUSTMER’;

 

OWNER LOG_GROUP_NAME

—————————— ——————————

TABLE_NAME

——————————

COLUMN_NAME

——————————————————————————–

POSITION LOGGIN

———- ——

SYSTEM GGS_TCUSTMER_74729

TCUSTMER

CUST_CODE

1 LOG

SQL>

Sobre Alexandre Pires

ORACLE OCS Goldengate Specialist, OCE RAC 10g R2, OCP 12C, 11g, 10g , 9i e 8i - Mais de 25 anos de experiência na área de TI. Participei de projetos na G&P alocado na TOK STOK, EDINFOR alocado na TV CIDADE "NET", 3CON Alocado no PÃO DE AÇUCAR, DISCOVER alocado na VIVO, BANCO IBI e TIVIT, SPC BRASIL, UOLDIVEO alocado no CARREFOUR e atualmente na ORACLE ACS atendendo os seguintes projetos: VIVO, CLARO, TIM, CIELO, CAIXA SEGUROS, MAPFRE, PORTO SEGURO, SULAMERICA, BRADESCO SEGUROS, BANCO BRADESCO, BASA, SANTANDER, CNJ, TSE, ELETROPAULO, EDP, SKY, NATURA, ODEBRESHT, NISSEI, SICREDI, CELEPAR, TAM, TIVIT, IBM, SMILES, CELEPAR, SERPRO,OKI,BANCO PAN, etc
Esse post foi publicado em GOLDENGATE. Bookmark o link permanente.

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s