Forum Stats

  • 3,769,801 Users
  • 2,253,023 Discussions
  • 7,875,210 Comments

Discussions

Supplemental logging in container database and the behavior of DBA_SUPPLEMENTAL_LOGGING view

User_RMS7G
User_RMS7G Member Posts: 2 Green Ribbon

Hi Experts ,

I have configured GG in a container database where source and target are pluggable database . I am trying to test the effect of DML by deleting TRANDATA from the source table , but surprisingly all DMLs are replicating even after removing the TRANDATA. While investigating further and I found supplemental logging is enabled for PK,UK,FK at PDB level . I had only enabled Min supplemental logging at CDB , but it enables supplemental logging for Pk,UK and FK in PDB


[[email protected]:~ ] $ sqlplus / as sysdba


SQL*Plus: Release 12.2.0.1.0 Production on Wed Jan 27 21:51:41 2021

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> SELECT supplemental_log_data_min MIN,

supplemental_log_data_pk PK,

 2  3 supplemental_log_data_ui UI,

 4 supplemental_log_data_fk FK,

 5 supplemental_log_data_all "ALL"

 6 FROM v$database;


MIN   PK UI FK ALL

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

NO    NO NO NO NO


SQL> alter session set container=SRPDB;

Session altered.

SQL> SELECT supplemental_log_data_min MIN,

supplemental_log_data_pk PK,

 2  3 supplemental_log_data_ui UI,

 4 supplemental_log_data_fk FK,

supplemental_log_data_all "ALL"

 5  6 FROM v$database;

MIN   PK UI FK ALL

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

NO    NO NO NO NO


SQL> select * from DBA_SUPPLEMENTAL_LOGGING;


MIN PRI UNI FOR ALL PRO

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

NO NO NO NO NO NO


SQL> conn / as sysdba

Connected.

SQL> show con_name


CON_NAME

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

CDB$ROOT

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Database altered.

SQL> SELECT supplemental_log_data_min MIN,

supplemental_log_data_pk PK,

 2  3 supplemental_log_data_ui UI,

 4 supplemental_log_data_fk FK,

 5 supplemental_log_data_all "ALL"

 6 FROM v$database;


MIN   PK UI FK ALL

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

YES   NO NO NO NO


SQL> alter session set container=SRPDB;

Session altered.

SQL> SELECT supplemental_log_data_min MIN,

supplemental_log_data_pk PK,

 2  3 supplemental_log_data_ui UI,

 4 supplemental_log_data_fk FK,

 5 supplemental_log_data_all "ALL"

 6 FROM v$database;

MIN   PK UI FK ALL

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

YES   NO NO NO NO

SQL> select * from DBA_SUPPLEMENTAL_LOGGING;


MIN PRI UNI FOR ALL PRO

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

YES YES YES YES NO NO

SQL>

as per Oracle doc,

DBA_SUPPLEMENTAL_LOGGING provides information about supplemental logging for a pluggable database (PDB) in a multitenant container database (CDB) and I was expecting only MIN supplemental logging should reflect as 'YES' from DBA_SUPPLEMENTAL_LOGGING


Regards

Mahi

Tagged: