I was tasked to find supplemental logging details for Oracle database used with GoldenGate.
Note: this is not a pluggable database.
With ADD TRANDATA, use dba_log_groups and dba_log_group_columns.
With ADD SCHEMATRANDATA, use select * from table(logmnr$always_suplog_columns( SCHEMA, TABLE ));
Basically, one would need to run the query with logmnr pipeline function for all the tables in the schema.
Here is one process I used.
Create info_schematrandata.prm
$ cat info_schematrandata.prm dblogin USERID ggs, PASSWORD * info schematrandata *
Run ggsci using info_schematrandata.prm (full path is required)
$ ggsci paramfile /home/oracle/working/dinh/info_schematrandata.prm > info_schematrandata.log
Here is example for results (actual contains 12 schemas)
$ grep -i enable info_schematrandata.log 2020-06-01 05:19:35 INFO OGG-06480 Schema level supplemental logging, excluding non-validated keys, is enabled on schema "SCOTT". 2020-06-01 05:19:35 INFO OGG-01981 Schema level supplemental logging is enabled on schema "SCOTT" for all columns.
After finding the schemas, use logmnr pipeline function to find all the details.
select * from table(logmnr$always_suplog_columns('SCOTT','EMP')) order by intcol;
select * from table(logmnr$always_suplog_columns('SCOTT','BONUS')) order by intcol;
select * from table(logmnr$always_suplog_columns('SCOTT','DEPT')) order by intcol;
You can find demo with logmnr$always_suplog_columns at: GoldenGate 12c Features Found in 11.2.1.0.21 ???
References:
OGG: How To Log All Columns With Add Schematrandata To Get NOCOMPRESSUPDATES (Doc ID 1413142.1)
ADD SCHEMATRANDATA does not allow columns to be specified. This enables logging of Primary Key columns only. By default, updates are compressed. In order to log all columns ADD TRANDATA would have to be used. The ADD TRANDATA can be used in conjunction with ADD SCHEMATRANDATA to specify the non-primary key columns.
How to Check Supplemental Logging When ADD SCHEMATRANDATA is Enabled (Doc ID 1537837.1)
It is not listed in dba_log_groups or dba_log_group_columns. select * from table(logmnr$always_suplog_columns( SCHEMA, TABLE ));
Effects of ADD TRANDATA and ADD SCHEMATRANDATA on an Oracle databases’ Supplemental Logging (Doc ID 2070331.1)
Some useful commands from ggsci:
INFO TRANDATA [container.]owner.table (info trandata *) did not work INFO SCHEMATRANDATA schema (info schematrandata *) LIST TABLES table (list tables SCOTT.*)
Note to self:
$ cat list_table.prm
dblogin USERID ggs, PASSWORD *
list tables SCOTT.*
$ ggsci paramfile /home/oracle/working/dinh/list_table.prm > list_table.log
$ grep '\.' list_table.log | egrep -iv 'found|ggsci'| grep -A 10000 "Successfully logged into database."|grep -v database > table.log
$ cat table.log
SCOTT.EMP
SCOTT.BONUS
SCOTT.DEPT
$ cat read.sh
#!/bin/bash
IFS="."
while read f1 f3
do
echo "select * from table(logmnr\$always_suplog_columns('$f1','$f3')) order by intcol;"
done < /home/oracle/working/dinh/table.log
exit
$ ./read.sh > /tmp/suplog.sql
$ head /tmp/suplog.sql
select * from table(logmnr$always_suplog_columns('SCOTT','EMP')) order by intcol;
select * from table(logmnr$always_suplog_columns('SCOTT','BONUS')) order by intcol;
select * from table(logmnr$always_suplog_columns('SCOTT','DEPT')) order by intcol;
$ cat suplog.sql
set numw 8 lines 200 timing off echo off pages 10000 trimsp on tab off
column NAME_COL_PLUS_SHOW_PARAM format a30
column VALUE_COL_PLUS_SHOW_PARAM format a65 wrap
col owner for a20
col table_name for a20
col column_name for a30
col log_group_type for a20
col column_list for a80
col log_group_name for a30
col table_name for a30
spool Database_Supplemental_Logging_Details.log
pro ******** Database ********
SELECT
name,db_unique_name,open_mode,database_role,remote_archive,switchover_status,dataguard_broker,primary_db_unique_name
FROM v$database
;
pro ******** Database Supplemental Logging ********
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
;
pro ******** Table Supplemental Logging ********
pro
pro ******** GoldenGate: ADD TRANDATA ********
SELECT
g.owner, g.table_name, g.log_group_name, g.log_group_type,
DECODE(always,'ALWAYS','Unconditional',NULL,'Conditional') always,
LISTAGG(c.column_name, ', ') WITHIN GROUP (ORDER BY c.POSITION) column_list
FROM dba_log_groups g, dba_log_group_columns c
WHERE g.owner = c.owner(+)
AND g.log_group_name = c.log_group_name(+)
AND g.table_name = c.table_name(+)
GROUP BY g.owner, g.log_group_name, g.table_name, g.log_group_type, DECODE(always,'ALWAYS','Unconditional',NULL,'Conditional')
ORDER BY g.owner, g.log_group_name, g.table_name, g.log_group_type
;
pro ******** Schema Supplemental Logging ********
pro
pro ******** GoldenGate: ADD SCHEMATRANDATA ********
@/tmp/suplog.sql
exit
