supplemental logging

 

Supplemental logging :

===============

====Sql's for verify and set:

SELECT supplemental_log_data_min FROM v$database;

==========================

SELECT supplemental_log_data_min MIN,

supplemental_log_data_pk PK,

supplemental_log_data_ui UI,

supplemental_log_data_fk FK,

supplemental_log_data_all "ALL"

FROM v$database;

Set minimal supplemental logging for database.

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

For RDS:

exec rdsadmin.rdsadmin_util.alter_supplemental_logging('ADD');

============================SET PK ======================================

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

For RDS:

exec rdsadmin.rdsadmin_util.alter_supplemental_logging('ADD','PRIMARY KEY');

======================HOW UI/ALL=========================================

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;

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

=============================================================================

verify:

SELECT supplemental_log_data_min FROM v$database;

SELECT

  SUPPLEMENTAL_LOG_DATA_MIN,

  SUPPLEMENTAL_LOG_DATA_PK,

  SUPPLEMENTAL_LOG_DATA_UI

FROM V$DATABASE;

select * from v$version;

====================================================================

select SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI from gv$database;

=========

SELECT supplemental_log_data_min MIN,

supplemental_log_data_pk PK,

supplemental_log_data_ui UI,

supplemental_log_data_fk FK,

supplemental_log_data_all "ALL"

FROM v$database;

Table level logging create group for column and set

    

ALTER TABLE owner.table_name ADD SUPPLEMENTAL LOG GROUP GROUP_NAME (col1, col2..) ALWAYS;

Table level logging PK column set

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

Table level logging UI column set

    ALTER TABLE SCHEMA.TABLE ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;

Table level logging ALL column set

    ALTER TABLE SCHEMA.TABLE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

    ALTER TABLE SCHEMA.TABLE DROP SUPPLEMENTAL LOG DATA (ALL) COLUMNS;