How to generate AWR, ADDM, STATSPACK reports.

================================
General
================================
AWR, ADDM, STATSPACK, AWRDDRPT reports.

gen_awr.sh
#! /bin/bash

. /etc/sh/orash/oracle_login.sh igt
sqlplus system/Xen86Pga@igt @${ORACLE_HOME}/rdbms/admin/awrrpt.sql

gen_addm.sh
#! /bin/bash

. /etc/sh/orash/oracle_login.sh igt
sqlplus system/Xen86Pga@igt @${ORACLE_HOME}/rdbms/admin/addmrpt.sql

gen_ash.sh
#! /bin/bash

. /etc/sh/orash/oracle_login.sh igt
sqlplus system/Xen86Pga@igt @${ORACLE_HOME}/rdbms/admin/ashrpt.sql

gen_statspack.sh
#! /bin/bash

. /etc/sh/orash/oracle_login.sh igt
sqlplus system/Xen86Pga@igt @${ORACLE_HOME}/rdbms/admin/spreport.sql

#for specific SQL
#@sqlplus perfstat/iwantawr@igt @/software/oracle/111/rdbms/admin/sprepsql.sql

awrddrpt.sql

sqlplus system/Xen86Pga@igt @${ORACLE_HOME}/rdbms/admin/awrddrpt.sql

The awrddrpt.sql script is the AWR "diff" report for comparing two AWR reports.

The awrddrpt.sql script is located in the $ORACLE_HOME/rdbms/admin directory.  

This awrddrpt.sql script compares two time periods.  

To do this, the awrddrpt.sql script accepts four snapshot values.

Start and end for the first AWR report.  

Start and end for the second AWR report.  

Once the four snapshot numbers are collected, the awrddrpt.sql script creates two AWR reports and then compares the two reports.  

The awrddrpt.sql script is very useful for comparing performance during two elapsed time periods. 

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

To see and change  AWR settings:

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

To see current settings:

SELECT snap_interval, retention FROM DBA_HIST_WR_CONTROL;

To set interval to 60 minutes an retention to 14 days (60*24*14=43200)

EXECUTE DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(INTERVAL => 60,RETENTION => 20160);

================================
perfstat Purge old intervals data
================================

Use this solution to create a job to delete old snapshots

Cleanup PERFSTAT tables

When checking for big tables, following table occupy a lot of space on WORKAREA Tablespace:

Option A - Delete few of the snapshots

sqlplus perfstat/iwantawr@igt

/software/oracle/112/rdbms/admin/sppurge.sql

Warning

~~~~~~~

sppurge.sql deletes all snapshots ranging between the lower and

upper bound Snapshot Id's specified, for the database instance

you are connected to.  Snapshots identified as Baseline snapshots

which lie within the snapshot range will not be purged.

It is NOT possible to rollback changes once the purge begins.

You may wish to export this data before continuing.

Specify the Lo Snap Id and Hi Snap Id range to purge

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for losnapid: 10030

Using 10030 for lower bound.

Enter value for hisnapid: 11949

Using 11949 for upper bound.

Deleting snapshots 10030 - 11949.

begin

*

ERROR at line 1:

ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS'

ORA-06512: at "PERFSTAT.STATSPACK", line 1330

ORA-06512: at "PERFSTAT.STATSPACK", line 1501

ORA-06512: at line 4

Option B - Truncate statspack tables

@$ORACLE_HOME/rdbms/admin/sptrunc.sql

SQL> @/software/oracle/112/rdbms/admin/sptrunc.sql

Warning

~~~~~~~

Running sptrunc.sql removes ALL data from Statspack tables.  You may

wish to export the data before continuing.

About to Truncate Statspack Tables

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

If would like to exit WITHOUT truncating the tables, enter any text at the

begin_or_exit prompt (e.g. 'exit'), otherwise if you would like to begin

the truncate operation, press <return>

Enter value for begin_or_exit:

Entered at the 'begin_or_exit' prompt

... Starting truncate operation

Table truncated.

Table truncated.

Table truncated.

...

...

... Truncate operation complete

Error ORA-20200: Database/Instance 1278761441/1 does not exist in STATS$DATABASE_INSTANCE

This error means that no statistics were gathered in the past.

To create perfstat schema:

@${ORACLE_HOME}/rdbms/admin/spcreate.sql

Step 1.

sqlplus system/xxxxxxxx

GRANT CREATE JOB TO perfstat;

Step 2.

conn perfstat/xxxxxxxx

@${ORACLE_HOME}/rdbms/admin/spauto.sql

EXECUTE statspack.snap;

To gather interval stats now:

CONNECT perfstat/my_perfstat_password

EXECUTE statspack.snap;

To create the job to gather stats in automated way:

SQL> @/software/oracle/193/rdbms/admin/spauto.sql

SQL> @${ORACLE_HOME}/rdbms/admin/spauto.sql

begin

*

ERROR at line 1:

ORA-27486: insufficient privileges

ORA-06512: at "SYS.DBMS_ISCHED", line 9387

ORA-06512: at "SYS.DBMS_ISCHED", line 9376

ORA-06512: at "SYS.DBMS_ISCHED", line 175

ORA-06512: at "SYS.DBMS_ISCHED", line 9302

ORA-06512: at "SYS.DBMS_IJOB", line 196

ORA-06512: at "SYS.DBMS_JOB", line 168

ORA-06512: at line 3

In Oracle 19, the owner of might not have privilege to execute create job, for collecting stats 

To fix it:

as system:

GRANT CREATE JOB TO perfstat;

as perfstat:

SQL> @/software/oracle/193/rdbms/admin/spauto.sql

PL/SQL procedure successfully completed.

Now, the job was created

Run statspack report for every 10 minutes

SET LINESIZE 120

COL what FOR A40

COL interval  FOR A30

SELECT job, what, interval 

FROM USER_JOBS 

WHERE what LIKE '%stat%';

JOB WHAT             INTERVAL

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

124 statspack.snap;  trunc(SYSDATE+1/24,'HH')

BEGIN

  DBMS_JOB.next_date(124, TO_DATE('20211007 14:00:00','YYYYMMDD hh24:mi:ss'));

 DBMS_JOB.interval(124,'TRUNC(SYSDATE+10/1440,''MI'')');

 commit;

END;

/

================================
Run perfstat from crontab
================================

How to run STATSPACK report from crontab?

1. Copy/paste oracle scripts to the local directory.

2. Create a Linux wrapper script:

- set env variables

- populate the parameters for my_spreport.sql

- clean up old report files

3. Create my_spreport.sql, a copy paste from oracle my_spreport.sql, to holde parameters from script, and pass them to sprepins.sql. Normally these parameters are passed from prompt, but here they are passed from script.

4. Create a crontab entry, to run once every hour.

crontab

5 * * * * /software/oracle/oracle/scripts/statspack/cron_statspack.sh

cron_statspack.sh

#!/bin/bash

BAS_ORACLE_LIST=igt

ORA_VER=1120

ORACLE_SID=igt

ORACLE_BASE=/software/oracle

ORACLE_HOME=/software/oracle/112

WORK_DIR=/software/oracle/oracle/scripts/statspack

cd ${WORK_DIR}

DAYS_TO_KEEP=14

get_last_snap_id () {

    sqlplus -s perfstat/iwantawr@igt <<!

    set heading off

    set feedback off

    set pages 0

    select MAX(snap_id) FROM stats_snapshot;

!

}

max_snap_id=$(get_last_snap_id)

echo max_snap_id = $max_snap_id

prev_snap_id=$((max_snap_id-1))

echo prev_snap_id=$prev_snap_id

RUN_DATE=`date "+%Y%m%d"_"%H%M"`

report_name=perfstat_${RUN_DATE}.rep

#sqlplus perfstat/iwantawr@igt /software/oracle/1910/rdbms/admin/spreport.sql

sqlplus perfstat/iwantawr@igt @my_spreport.sql $prev_snap_id $max_snap_id $report_name

find ${WORK_DIR} -type f -name "perfstat_*rep" -mtime +${DAYS_TO_KEEP} -exec rm {} \;

my_spreport.sql

column inst_num  heading "Inst Num" new_value inst_num  format 99999;

column inst_name heading "Instance" new_value inst_name format a12;

column db_name   heading "DB Name"  new_value db_name   format a12;

column dbid      heading "DB Id"    new_value dbid      format 9999999999 just c;

prompt

prompt Current Instance

prompt ~~~~~~~~~~~~~~~~

select d.dbid            dbid

     , d.name            db_name

     , i.instance_number inst_num

     , i.instance_name   inst_name

  from v$database d,

       v$instance i;

define begin_snap  = &1;

define end_snap    = &2;

define report_name = &3;

@@sprepins.sql

EXIT;

Bug in perfstat - Data Guard is listed as a main wait event
After running spreport.sql Data Guard is listed as a main wait event

Top 5 Timed Events                            Avg %Total

~~~~~~~~~~~~~~~~~~                           wait   Call

Event                      Waits  Time (s)   (ms)   Time

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

Data Guard: Timer               6      181  30208   30.6

Data Guard: Gap Manager         3      181  60414   30.6

pman timer                     58      174   3008   29.4

CPU time                                15           2.5

log file sync                 653       13     20    2.2

"Data Guard: Timer", "Data Guard: Gap Manager", "pman timer" - are idle events that should not be listed as wait events

Issue is due to a bug #Bug 28523746 – STATSPACK: TOP 5 TIMED EVENTS CONTAINS IDLE WAIT EVENTS

Solution:

Update the table STATS$IDLE_EVENT 

INSERT INTO STATS$IDLE_EVENT

SELECT name FROM V$EVENT_NAME WHERE wait_class='Idle'

MINUS

SELECT event FROM STATS$IDLE_EVENT;

commit;

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

Error ORA-20200: Database/Instance does not exist

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

ERROR at line 1:

ORA-20200: Database/Instance 1278761441/1 does not exist in

STATS$DATABASE_INSTANCE

ORA-06512: at line 23

This error can occur if spreport.sql is ran before any snapshots have been taken. 

Try creating a snapshot using execute statspack.snap and rerun spreport.sql.

  

sqlplus perfstat/xxxxxx@igt  

select * from stats$database_instance;

no rows selected  

EXECUTE statspack.snap;

select * from stats$database_instance;

--Data is there

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

Drop duplicate statspack.snap job

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

sqlplus perfstat/xxxxxx@igt

 

COL SCHEMA_USER FOR A30

COL WHAT FOR A50

SELECT job, schema_user 

  FROM DBA_JOBS 

 WHERE UPPER(what) LIKE '%STATSPACK%';

BEGIN

 DBMS_JOB.remove(21);

 commit;

END;

/

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

Automate Delete

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

Step A. 

cp /software/oracle/1910/rdbms/admin/sppurge.sql /software/oracle/1910/rdbms/admin/sppurge.sql_orig

Edit existing file /software/oracle/1910/rdbms/admin/sppurge.sql

Add the lines in blue
In this example: purge data older than 30 days

prompt

prompt

prompt Snapshots for this database instance

prompt ====================================

select s.snap_id

     , to_char(s.snap_time,' dd Mon YYYY HH24:mi:ss')    snap_date

     , s.baseline

     , s.snap_level                                      "level"

     , di.host_name                                      host_name

     , s.ucomment

  from stats$snapshot s

     , stats$database_instance di

 where s.dbid              = :dbid

   and di.dbid             = :dbid

   and s.instance_number   = :inst_num

   and di.instance_number  = :inst_num

   and di.startup_time     = s.startup_time

 order by db_name, instance_name, snap_id;

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

-- Populate HiSnapId and HiSnapId

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

COL min_snap_id new_val LoSnapId

COL max_snap_id new_val HiSnapId

SELECT MIN(SNAPSHOTS.snap_id) min_snap_id,

       MAX(SNAPSHOTS.snap_id) max_snap_id

FROM STATS$SNAPSHOT SNAPSHOTS

WHERE SNAPSHOTS.snap_time < TRUNC(SYSDATE) - 30;

SELECT &&LoSnapId as low_value from DUAL;

SELECT &&HiSnapId as high_value from DUAL;

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

Step B.
Create a crontab task 

purge_perfstat.sh

#!/bin/bash

. /etc/sh/orash/oracle_login.sh igt

ORACLE_HOME=/software/oracle/1910

TNS_ADMIN=${ORACLE_HOME}/network/admin

PATH=${PATH}:${ORACLE_HOME}/bin

sqlplus perfstat/iwantawr@igt @/software/oracle/1910/rdbms/admin/sppurge.sql

crontab

4 6 * * * /software/oracle/oracle/scripts/awr/purge_perfstat.sh

Oracle Supplemental Logging. How to see current status, add logging to a table, remove logging from a Table

===========================
General
===========================
Oracle Supplemental Logging, for Oracle 11.2

Oracle GoldenGate depends on Supplemental Logging.

Regular undo/redo logs use ROWID to identify data rows.
But with replication, ROWID cannot be used, and additional logging is required.
Supplemental logging generates additional undo which is stored in the redo log.

There are several types of supplemental logging:

Minimal
Primary Key
Unique Key
Foreign Key
All
Procedural Replication


===========================
How to check
===========================

Reference

SELECT supplemental_log_data_fk, 

       supplemental_log_data_all, 

       supplemental_log_data_ui, 

       supplemental_log_data_min

FROM V$DATABASE;

SELECT * FROM DBA_LOG_GROUPS;

SELECT * FROM DBA_LOG_GROUP_COLUMNS;

===========================
Supplemental logging levels
===========================
Minimal Supplemental Logging
Minimal supplemental logging ensures that products leveraging LogMiner technology will have sufficient information to support chained rows and cluster tables.

Primary Key Supplemental Logging
Primary key supplemental logging includes the primary key for rows affected by UPDATE and DELETE changes.

Unique Key Supplemental Logging
Unique key supplemental logging includes all columns for a unique key are written to undo if any unique key columns are modified.

Foreign Key Supplemental Logging
Foreign key supplemental logging includes all other columns belonging to a foreign key will be logged in the undo if any foreign key columns are modified.

All Column Supplemental Logging
If no primary key or unique key is available then it is possible to specify that all columns are logged. In this case all columns in a row will be logged in the undo. When the row is replicated in the target database, equality predicates will be applied to all columns.
Supplemental logging is not enabled for LONG, LONG RAW and LOB columns.

Procedural Replication Supplemental Logging
Procedural replication supplemental logging includes additional information in the redo log during invocation of procedures in Oracle-supplied packages for which procedural replication is supported.

===========================
Database Level Supplemental Logging
===========================
Oracle implements supplemental logging at database level, schema level and at table level.

Database Level Supplemental Logging
Database level supplemental logging is configured in the control file.
The parameter does not appear to be stored in the database itself.

Enable Supplemental Logging:

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Database altered.
Disable Supplemental Logging:

SQL> ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;

Database altered.

Example syntax:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA FOR PROCEDURAL REPLICATION;

The current supplemental logging configuration is reported in V$DATABASE by the following columns:

SUPPLEMENTAL_LOG_DATA_MIN
SUPPLEMENTAL_LOG_DATA_PK
SUPPLEMENTAL_LOG_DATA_UI
SUPPLEMENTAL_LOG_DATA_FK
SUPPLEMENTAL_LOG_DATA_ALL
SUPPLEMENTAL_LOG_DATA_P

===========================
Schema Level Supplemental Logging
===========================
It is enabled and/or disabled using DBMS_CAPTURE_ADM.PREPARE_SCHEMA_INSTANTIATION procedure.

This procedure takes two parameters:
(SCHEMA_NAME, SUPPLEMENTAL_LOGGING)

SUPPLEMENTAL_LOGGING parameter can have following parameters:

NONE - supplemental logging is not enabled for any columns in the schema.

KEYS - supplemental logging is enabled for primary key, unique key, bitmap index and foreign key columns.
Primary keys are logged unconditionally.
Unique key, bitmap index and foreign keys are logged conditionally.

ALL - supplemental logging is enabled for all columns in all existing tables in schema .

Supplemental logging is not enabled for the following types of column:
LOB
LONG
LONG RAW
User-defined types
Oracle-supplied types

Schema level supplemental logging parameters are reported in the DBA_CAPTURE_PREPARED_SCHEMAS.


SCHEMA_NAME               VARCHAR2(30)
TIMESTAMP                 DATE
SUPPLEMENTAL_LOG_DATA_PK VARCHAR2(8)
SUPPLEMENTAL_LOG_DATA_UI VARCHAR2(8)
SUPPLEMENTAL_LOG_DATA_FK VARCHAR2(8)
SUPPLEMENTAL_LOG_DATA_ALL VARCHAR2(8)

Possible values for the SUPPLEMENTAL_LOG_DATA flags are:
-IMPLICIT
-EXPLICIT
-NO

See schema level Supplemental info

GGSCI> dblogin USERID my_user, password my_pass

GGSCI> info schematrandata *

2023-07-06 11:40:57  INFO    OGG-01786  Schema level supplemental logging is disabled on schema "ALEC_SH_MONITOR".

2023-07-06 11:40:57  INFO    OGG-10462  Schema "ALEC_SH_MONITOR" have 40 prepared tables for instantiation.

2023-07-06 11:40:57  INFO    OGG-01786  Schema level supplemental logging is disabled on schema "ALEC_TEST".

2023-07-06 11:40:57  INFO    OGG-10462  Schema "ALEC_TEST" have 12 prepared tables for instantiation.

2023-07-06 11:40:57  INFO    OGG-01786  Schema level supplemental logging is disabled on schema "MY_SCEMA".

2023-07-06 11:40:57  INFO    OGG-10462  Schema "MY_SCEMA" have 1,621 prepared tables for instantiation.

===========================
Table Level Supplemental Logging
===========================
in GG cli

GGSCI>dblogin userid my_user password my_password

GGSCI>ADD TRANDATA MY_USER.MY_TABLE

or with sqlplus

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

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

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

ADD TRANDATA  automatically adds supplemental logging for the table based on the table’s primary key or if that’s missing, using any unique key constraints defined for that table.

Once supplemental logging is enabled, along with a column which has been updated/changed an extra column will be logged in to redo logs so that a row could be uniquely identified.

Order of extra column logging: 

Primary Key

Unique Key (if Primary Key is missing)

All columns (if Primary Key and Unique Key are missing)

In case there is no Primary/Unique key, it is possible to list the logged columns, to avoid ALL COLUMNS scenario

ADD TRANDATA MY_TABLE , COLS (COLUMN1, COLUMN2, COLUMN22), NOKEY

======================================================
See Table Status
======================================================

SELECT * FROM DBA_LOG_GROUPS;

COL LOG_GROUP_NAME FOR A30

COL LOG_GROUP_TYPE FOR A30

SELECT log_group_name, log_group_type 

  FROM DBA_LOG_GROUPS 

 WHERE table_name='SFI_CUSTOMER_PROFILE'

LOG_GROUP_NAME                 LOG_GROUP_TYPE

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

GGS_48337541                   USER LOG GROUP

SYS_C00164213                  PRIMARY KEY LOGGING

SYS_C00164214                  UNIQUE KEY LOGGING

SYS_C00164215                  FOREIGN KEY LOGGING

SYS_C00164216                  ALL COLUMN LOGGING

SELECT * FROM DBA_LOG_GROUP_COLUMNS;

Another syntax for USER_LOG_GROUP_COLUMNS
SELECT * FROM TABLE(LOGMNR$ALWAYS_SUPLOG_COLUMNS( 'MY_SCHEMA', 'MY_TABLE' ));

For some reason not all table are listed in USER_LOG_GROUPS and in USER_LOG_GROUP_COLUMNS

To see actual data, use ggsci CLI INFO TRANDATA command

Example 1 - Table with ALL COLUMNS

GGSCI 56> DBLOGIN USERID my_user, password my_pass

GGSCI 57> INFO TRANDATA MY_USER.MY_TABLE

2023-07-06 11:47:42  INFO    OGG-10471  ***** Oracle Goldengate support information on table MY_USER.MY_TABLE *****

Oracle Goldengate support native capture on table MY_USER.MY_TABLE.

Oracle Goldengate marked following column as key columns on table MY_USER.MY_TABLE: MSISDN.

Logging of supplemental redo log data is enabled for table MY_USER.MY_TABLE.

All columns supplementally logged for table MY_USER.MY_TABLE.

Prepared CSN for table MY_USER.MY_TABLE: 54132906898

GGSCI 58>

Example 2 - Table with PK

GGSCI 61> INFO TRANDATA MY_USER.ALARM_MAP_NAME

2023-07-06 11:53:06  INFO    OGG-10471  ***** Oracle Goldengate support information on table LAB_QANFV_ALLQQ.ALARM_MAP_NAME *****

Oracle Goldengate support native capture on table MY_USER.ALARM_MAP_NAME.

Oracle Goldengate marked following column as key columns on table MY_USER.ALARM_MAP_NAME: ALARM_MAP_NAME.

Logging of supplemental redo log data is enabled for table MY_USER.ALARM_MAP_NAME.

Columns supplementally logged for table MY_USER.ALARM_MAP_NAME: "ALARM_MAP_NAME".

Prepared CSN for table MY_USER.ALARM_MAP_NAME: 54132912785

GGSCI (qanfv-2-dbs-01 as ogg@igt) 62>

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

How to disable Supplemental Logging for a table
======================================================

See current status

Before change:

GGSCI 62> INFO TRANDATA LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY

2023-07-06 12:55:38  INFO    OGG-10471  ***** Oracle Goldengate support information on table LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY *****

Oracle Goldengate support native capture on table LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY.

Oracle Goldengate marked following column as key columns on table LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY: KEY1.

Logging of supplemental redo log data is enabled for table LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY.

All columns supplementally logged for table LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY.

Prepared CSN for table LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY: 54132907038

SELECT * FROM USER_LOG_GROUP_COLUMNS WHERE table_name = 'SFI_CUSTOMER_USAGE_HOURLY';

 

OWNER           LOG_GROUP_NAME TABLE_NAME                COLUMN_NAME POSITION LOGGING_PROPERTY

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

LAB_QANFV_ALLQQ GGS_48337432   SFI_CUSTOMER_USAGE_HOURLY KEY1               1 LOG

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

Option A - - with sqlplus  - This does not work!!

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

ALTER TABLE LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY DROP SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

--no effect

ALTER TABLE LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY DROP SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;

--no effect

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

--no effect

SELECT * FROM USER_LOG_GROUP_COLUMNS WHERE table_name = 'SFI_CUSTOMER_USAGE_HOURLY';

 

OWNER           LOG_GROUP_NAME TABLE_NAME                COLUMN_NAME POSITION LOGGING_PROPERTY

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

LAB_QANFV_ALLQQ GGS_48337432   SFI_CUSTOMER_USAGE_HOURLY KEY1               1 LOG

Same data is there, both with sqlplus, querying USER_LOG_GROUP_COLUMNS, and with ggsci command INFO TRANDATA LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY

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

Option B - Delete Trandata with ggsci command - This does not work!!

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

GGSCI 64> DELETE TRANDATA LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY

2023-07-06 12:57:12  INFO    OGG-15142  Logging of supplemental redo log data disabled for table LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY.

2023-07-06 12:57:12  INFO    OGG-15139  TRANDATA for scheduling columns has been disabled on table LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY.

GGSCI 65> INFO TRANDATA LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY

2023-07-06 12:57:29  INFO    OGG-10471  ***** Oracle Goldengate support information on table LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY *****

Oracle Goldengate support native capture on table LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY.

Oracle Goldengate marked following column as key columns on table LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY: KEY1.

Logging of supplemental redo log data is enabled for table LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY.

All columns supplementally logged for table LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY.

Prepared CSN for table LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY: 54132907038

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

Option C - Delete Trandata with sqlplus using LOG_GROUP_NAME - This worked!!

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

This is the syntax:

ALTER TABLE [TABLE_NAME] DROP SUPPLEMENTAL LOG GROUP [GROUP_NAME]

SELECT * FROM USER_LOG_GROUP_COLUMNS WHERE table_name = 'SFI_CUSTOMER_USAGE_HOURLY';

 

OWNER           LOG_GROUP_NAME TABLE_NAME                COLUMN_NAME POSITION LOGGING_PROPERTY

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

LAB_QANFV_ALLQQ GGS_48337432   SFI_CUSTOMER_USAGE_HOURLY KEY1               1 LOG

ALTER TABLE LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY DROP SUPPLEMENTAL LOG GROUP GGS_48337432;

Now, Supplemental Logging was indeed deleted

SELECT * FROM USER_LOG_GROUP_COLUMNS WHERE table_name = 'SFI_CUSTOMER_USAGE_HOURLY';

0 Rows Selected

For some reason, in ggsci, the output is different
Maybe there some time that takes to sync data to ggsci ?

GGSCI 69> INFO TRANDATA LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY

2023-07-06 13:08:58  INFO    OGG-10471  ***** Oracle Goldengate support information on table LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY *****

Oracle Goldengate support native capture on table LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY.

Oracle Goldengate marked following column as key columns on table LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY: KEY1.

Logging of supplemental redo log data is enabled for table LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY.

All columns supplementally logged for table LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY.

Prepared CSN for table LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY: 54132907038

GGSCI 70>

GGSCI 70> DELETE TRANDATA LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY

2023-07-06 13:09:41  INFO    OGG-15142  Logging of supplemental redo log data disabled for table LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY.

2023-07-06 13:09:41  INFO    OGG-15137  TRANDATA is already disabled for table LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY.

GGSCI 71> INFO TRANDATA LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY

2023-07-06 13:10:15  INFO    OGG-10471  ***** Oracle Goldengate support information on table LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY *****

Oracle Goldengate support native capture on table LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY.

Oracle Goldengate marked following column as key columns on table LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY: KEY1.

Logging of supplemental redo log data is disabled for table LAB_QANFV_ALLQQ.SFI_CUSTOMER_USAGE_HOURLY.

GGSCI 71>

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

Example - Delete Trandata from table with ALLCOLS logging -  This worked!!

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

COL LOG_GROUP_NAME FOR A30

COL LOG_GROUP_TYPE FOR A30

SELECT log_group_name, log_group_type 

 FROM DBA_LOG_GROUPS 

WHERE table_name='SFI_CUSTOMER_PROFILE';

LOG_GROUP_NAME                 LOG_GROUP_TYPE

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

GGS_48337541                   USER LOG GROUP

SYS_C00164213                  PRIMARY KEY LOGGING

SYS_C00164214                  UNIQUE KEY LOGGING

SYS_C00164215                  FOREIGN KEY LOGGING

SYS_C00164216                  ALL COLUMN LOGGING

GGSCI 2> dblogin USERID ogg, password xxxxxxx

GGSCI 3> INFO TRANDATA LAB_QANFV_ALLQQ.SFI_CUSTOMER_PROFILE

2023-07-13 06:48:11  INFO    OGG-10471  ***** Oracle Goldengate support information on table LAB_QANFV_ALLQQ.SFI_CUSTOMER_PROFILE *****

Oracle Goldengate support native capture on table LAB_QANFV_ALLQQ.SFI_CUSTOMER_PROFILE.

Oracle Goldengate marked following column as key columns on table LAB_QANFV_ALLQQ.SFI_CUSTOMER_PROFILE: KEY1.

Logging of supplemental redo log data is enabled for table LAB_QANFV_ALLQQ.SFI_CUSTOMER_PROFILE.

All columns supplementally logged for table LAB_QANFV_ALLQQ.SFI_CUSTOMER_PROFILE.

Prepared CSN for table LAB_QANFV_ALLQQ.SFI_CUSTOMER_PROFILE: 60656405752

GGSCI 4> DELETE TRANDATA LAB_QANFV_ALLQQ.SFI_CUSTOMER_PROFILE

2023-07-13 06:55:21  INFO    OGG-15142  Logging of supplemental redo log data disabled for table LAB_QANFV_ALLQQ.SFI_CUSTOMER_PROFILE.

2023-07-13 06:55:21  INFO    OGG-15139  TRANDATA for scheduling columns has been disabled on table LAB_QANFV_ALLQQ.SFI_CUSTOMER_PROFILE.

Note!! - per output looks like supplemental logging was disabled.

BUT - ALLCOLS Supplemental logging still persists, and requires a separate syntax

GGSCI 4> INFO TRANDATA LAB_QANFV_ALLQQ.SFI_CUSTOMER_PROFILE

2023-07-13 07:11:00  INFO    OGG-10471  ***** Oracle Goldengate support information on table LAB_QANFV_ALLQQ.SFI_CUSTOMER_PROFILE *****

Oracle Goldengate support native capture on table LAB_QANFV_ALLQQ.SFI_CUSTOMER_PROFILE.

Oracle Goldengate marked following column as key columns on table LAB_QANFV_ALLQQ.SFI_CUSTOMER_PROFILE: KEY1.

Logging of supplemental redo log data is enabled for table LAB_QANFV_ALLQQ.SFI_CUSTOMER_PROFILE.

All columns supplementally logged for table LAB_QANFV_ALLQQ.SFI_CUSTOMER_PROFILE.

SELECT log_group_name, log_group_type 

 FROM DBA_LOG_GROUPS 

WHERE table_name='SFI_CUSTOMER_PROFILE';

LOG_GROUP_NAME                 LOG_GROUP_TYPE

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

SYS_C00164216                  ALL COLUMN LOGGING

GGSCI 5> DELETE TRANDATA LAB_QANFV_ALLQQ.SFI_CUSTOMER_PROFILE, ALLCOLS

2023-07-13 06:58:35  INFO    OGG-15142  Logging of supplemental redo log data disabled for table LAB_QANFV_ALLQQ.SFI_CUSTOMER_PROFILE.

2023-07-13 06:58:35  INFO    OGG-15137  TRANDATA is already disabled for table LAB_QANFV_ALLQQ.SFI_CUSTOMER_PROFILE.

2023-07-13 06:58:35  INFO    OGG-15137  TRANDATA is already disabled for table LAB_QANFV_ALLQQ.SFI_CUSTOMER_PROFILE.

GGSCI 7> INFO TRANDATA LAB_QANFV_ALLQQ.SFI_CUSTOMER_PROFILE

2023-07-13 06:59:55  INFO    OGG-10471  ***** Oracle Goldengate support information on table LAB_QANFV_ALLQQ.SFI_CUSTOMER_PROFILE *****

Oracle Goldengate support native capture on table LAB_QANFV_ALLQQ.SFI_CUSTOMER_PROFILE.

Oracle Goldengate marked following column as key columns on table LAB_QANFV_ALLQQ.SFI_CUSTOMER_PROFILE: KEY1.

Logging of supplemental redo log data is disabled for table LAB_QANFV_ALLQQ.SFI_CUSTOMER_PROFILE.

SELECT log_group_name, log_group_type 

 FROM DBA_LOG_GROUPS 

WHERE table_name='SFI_CUSTOMER_PROFILE';

no rows selected

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

Example - Duplicat Logging - worked!!

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

COL LOG_GROUP_NAME FOR A30

COL LOG_GROUP_TYPE FOR A30

LOG_GROUP_NAME                 LOG_GROUP_TYPE

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

GGS_24223                      USER LOG GROUP

SYS_C0083671                   PRIMARY KEY LOGGING

SYS_C0083672                   UNIQUE KEY LOGGING

SYS_C0083673                   FOREIGN KEY LOGGING

SYS_C0083674                   ALL COLUMN LOGGING

GGS_1802052                    USER LOG GROUP

GGSCI 2> dblogin USERID ogg, password xxxxxxx

GGSCI 3> INFO TRANDATA LAB_QANFV_ALLQQ.SFI_CUSTOMER_OPTIONS

2023-07-20 05:30:20  INFO    OGG-10471  ***** Oracle Goldengate support information on table LAB_QANFV_ALLQQ.SFI_CUSTOMER_OPTIONS *****

Oracle Goldengate support native capture on table LAB_QANFV_ALLQQ.SFI_CUSTOMER_OPTIONS.

Oracle Goldengate marked following column as key columns on table LAB_QANFV_ALLQQ.SFI_CUSTOMER_OPTIONS: KEY1, CODE_OPTION.

Logging of supplemental redo log data is enabled for table LAB_QANFV_ALLQQ.SFI_CUSTOMER_OPTIONS.

All columns supplementally logged for table LAB_QANFV_ALLQQ.SFI_CUSTOMER_OPTIONS.

Prepared CSN for table LAB_QANFV_ALLQQ.SFI_CUSTOMER_OPTIONS: 27334647698

GGSCI 4> DELETE TRANDATA LAB_QANFV_ALLQQ.SFI_CUSTOMER_OPTIONS

2023-07-20 05:30:25  INFO    OGG-15142  Logging of supplemental redo log data disabled for table LAB_QANFV_ALLQQ.SFI_CUSTOMER_OPTIONS.

2023-07-20 05:30:25  INFO    OGG-15139  TRANDATA for scheduling columns has been disabled on table LAB_QANFV_ALLQQ.SFI_CUSTOMER_OPTIONS.

GGSCI 5> DELETE TRANDATA LAB_QANFV_ALLQQ.SFI_CUSTOMER_OPTIONS, ALLCOLS

2023-07-20 05:30:29  INFO    OGG-15142  Logging of supplemental redo log data disabled for table LAB_QANFV_ALLQQ.SFI_CUSTOMER_OPTIONS.

2023-07-20 05:30:29  INFO    OGG-15137  TRANDATA is already disabled for table LAB_QANFV_ALLQQ.SFI_CUSTOMER_OPTIONS.

2023-07-20 05:30:29  INFO    OGG-15137  TRANDATA is already disabled for table LAB_QANFV_ALLQQ.SFI_CUSTOMER_OPTIONS.

GGSCI 7> INFO TRANDATA LAB_QANFV_ALLQQ.SFI_CUSTOMER_OPTIONS

2023-07-20 05:30:34  INFO    OGG-10471  ***** Oracle Goldengate support information on table LAB_QANFV_ALLQQ.SFI_CUSTOMER_OPTIONS *****

Oracle Goldengate support native capture on table LAB_QANFV_ALLQQ.SFI_CUSTOMER_OPTIONS.

Oracle Goldengate marked following column as key columns on table LAB_QANFV_ALLQQ.SFI_CUSTOMER_OPTIONS: KEY1, CODE_OPTION.

Logging of supplemental redo log data is disabled for table LAB_QANFV_ALLQQ.SFI_CUSTOMER_OPTIONS.

Note!! - per output looks like supplemental logging was disabled.

BUT - Duplicate Supplemental logging still persists, and requires a separate syntax

SELECT log_group_name, log_group_type 

 FROM DBA_LOG_GROUPS 

WHERE table_name='SFI_CUSTOMER_OPTIONS';

LOG_GROUP_NAME                 LOG_GROUP_TYPE

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

GGS_1802052                    USER LOG GROUP

This could not be deleted with ggsci commands, only from sqlplus

SQL> ALTER TABLE LAB_QANFV_ALLQQ.SFI_CUSTOMER_OPTIONS DROP SUPPLEMENTAL LOG GROUP GGS_1802052;

SQL> SELECT log_group_name, log_group_type

       FROM DBA_LOG_GROUPS

      WHERE table_name='SFI_CUSTOMER_OPTIONS';

no rows selected

===========================
Reference
===========================
Introduction to Oracle Supplemental Logging for Logical Replication

Oracle GoldenGate: What is Supplemental Logging View & Its Importance

Oracle GoldenGate - Supplemental Logging