Supplemental logging when using Oracle Golden Gate with Oracle EBS 12.2.3

          Supplemental logging when using Oracle Golden Gate with Oracle EBS 12.2.3

Source Oracle EBS : 11.5.10.2

Target Oracle EBS : 12.2.3

Database version: 11.2.0.4

Tables: Custom Stage Tables

Oracle Golden gate : 12.1.2.0.1

Problem Statement:

We need to set up Oracle EBS database as a Golden Gate publisher. In order to do so, Supplemental Logging enablement at the DB level is required.

Evaluating concerns around performance impacts that we might see if we enable supplemental logging.

Golden Gate enablement was required in order to support data migration from Oracle EBS 11.5.10.2 on one data center to Oracle EBS 12.2.3 applications on a different secure data center.

We had to enable supplemental logging only on 12 Custom Staging tables, from where the Oracle Golden gate will perform the data migration. This data is migrated to Oracle EBS 12.2.3 database having 11.2.0.4 database version.

Evaluation details:

Few things to be considered in terms of enabling supplemental logging when using Oracle Golden Gate:

1. Enabling supplemental logging to source database will increase the size of the redo logs. This might increase more, depending on the amount of updates done on source database. Tuning the source database by configuring appropriate number of redo groups and redo size can help.

2. There are options to enable only required level of supplemental logging on the database while using golden gate. Enabling only primary key/Unique key might help and if it’s not present on tables where supplemental logging is required, then we can follow Oracle document for alternative:

How to Handle Tables Without Primary Keys or Unique Indexes With Oracle GoldenGate (Doc ID 1271578.1)

However without a key on a table with multiple columns can definitely increase the redo log generation. 

3. We also might need to understand how the EBS application connections will be handled while the supplemental logging will be enabled. If any of the application process is doing any updates to the database table, then there are good chance that it might create locks and logging can be enabled only after the transaction is completed.

TESTING and Results:

I did some tests for 1 EBS process that uses a lot of updates to see the impact of enabling the minimum supplemental logging at database level.

I executed the job Receipt Accruals - Period-End. The job has a loop of 2M records and it updates a lot of other records inside the loop.

The job normally takes more than 10h to complete and below are the statistics for the execution with Supplemental Logging and without it.

Mode: Without supplemental Logging

Time to complete: 626 minutes

Size of archive logs: 24.48GB

Number of archive logs: 36

Mode: With supplemental Logging (ALTER DATABASE ADD SUPPLEMENTAL LOG DATA)

Time to complete: 643 minutes

Size of archive logs: 25.62

Number of archive logs: 37

So as we can see the minimum supplemental logging has a very little impact in the performance and in the log generation.

In this test, the job took 2.7% more to complete and the volume of archive increase 4.65% and this is one of the worst case scenarios (the job does a lot of updates and supplemental logging does not affect insert and delete statements).

Error while grant execution can break replication in mysql 5.6

This looks like a bug in MySQL 5.6 : user  with GRANT privileges can  cause all replicas to break,  if the grant executed errors.

How?

 Let me explain . Lets assume iam trying to issue a grant replication client privilege to an user as seen below:

mysql> grant replication client on mine.*  to user@'10.29.1%';
ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

 The above grant failed coz the global privilege was supposed to be granted on *.*.
 Hence I re-execute by making corrections :

mysql> grant replication client on *.* to user@'10.29.1%';
Query OK, 0 rows affected (0.10 sec)
mysql> flush privileges;

All i did was corrected a grant statement. However this broke the replication of mysql database.

and Its a bug as per mysql : 68892.

 

Explaination:

 

Due to this replication bug, any GRANT command that errors out will break the mysql replication.

Then hence  skip the offending event on ALL replicas (or inject an empty transaction if GTID in use). The reason  is that MySQL 5.6 writes the following in the binlog when your grant fails:

#120009 01:02:10 server id 1 end_log_pos 422 CRC32 0x4d815dbc
# Incident: LOST_EVENTS
RELOAD DATABASE; # Shall generate syntax error
# at 422

This is intended to avoid any grant that fails on the master due to insufficient privileges, to succeed on the replicas instead (due to the fact that replication thread runs with SUPER privileges). To avoid that, replication is stopped with an error.
Concluding that any user on 5.6 which has GRANT privileges can break the replication if while executing any GRANT it error.


Cheers.

Performance issue during responsibility change in 11i

If you see performance issue during responsibility changing after launching form then perform following .

1.     Check the explain plan which  query is picking using (connect as apps ) :

Set pages 200

Set lines 200

Explain plan for

select distinct furg.responsibility_id, furg.responsibility_application_id, furg.security_group_id, furg.start_date, furg.end_date from fnd_user_resp_groups furg, fnd_responsibility fr where furg.user_id = :1 and furg.responsibility_id = fr.responsibility_id and furg.responsibility_application_id = fr.application_id and furg.start_date <= sysdate and (furg.end_date is null or furg.end_date >= sysdate) and fr.start_date <= sysdate and  (fr.end_date is null or fr.end_date >= sysdate) order by furg.responsibility_id, furg.responsibility_application_id, furg.security_group_id;

@?/rdbms/admin/utlxpls

/

2.     For the bad performance it will be using WF_LOCAL_USER_ROLES_N7  . for the query to perform good it should pick WF_LOCAL_USER_ROLES_U1

3.     We can either fix it by rebuilding the index or by just doing gather stats on index WF_LOCAL_USER_ROLES_N7
Option a : For rebuilding index use : connect as apps and run create_WF_LOCAL_USER_ROLES_N7.sql

drop index applsys.WF_LOCAL_USER_ROLES_N7;

CREATE INDEX APPLSYS.WF_LOCAL_USER_ROLES_N7 ON APPLSYS.WF_LOCAL_USER_ROLES

(ROLE_ORIG_SYSTEM_ID, ROLE_ORIG_SYSTEM)

  INITRANS   11

  MAXTRANS   255

LOGGING

LOCAL (

  PARTITION FND_USR

    LOGGING

    NOCOMPRESS

    TABLESPACE APPLSYSX

    PCTFREE    10

    INITRANS   11

    MAXTRANS   255

    STORAGE    (

                INITIAL          40K

                NEXT             40K

                MINEXTENTS       1

                MAXEXTENTS       2147483645

                PCTINCREASE      0

                FREELISTS        1

                FREELIST GROUPS  1

                BUFFER_POOL      DEFAULT

               ),

  PARTITION FND_RESP

    LOGGING

    NOCOMPRESS

    TABLESPACE APPS_TS_TX_DATA

    PCTFREE    10

    INITRANS   11

    MAXTRANS   255

    STORAGE    (

                INITIAL          40K

                NEXT             8K

                MINEXTENTS       1

                MAXEXTENTS       2147483645

                PCTINCREASE      0

                BUFFER_POOL      DEFAULT

               ),

  PARTITION POS

    LOGGING

    NOCOMPRESS

    TABLESPACE APPLSYSD

    PCTFREE    10

    INITRANS   11

    MAXTRANS   255

    STORAGE    (

                INITIAL          40K

                NEXT             40K

                MINEXTENTS       1

                MAXEXTENTS       2147483645

                PCTINCREASE      0

                FREELISTS        1

                FREELIST GROUPS  1

                BUFFER_POOL      DEFAULT

               ),

  PARTITION AMV_APPR

    LOGGING

    NOCOMPRESS

    TABLESPACE APPLSYSX

    PCTFREE    10

    INITRANS   11

    MAXTRANS   255

    STORAGE    (

                INITIAL          40K

                NEXT             8K

                MINEXTENTS       1

                MAXEXTENTS       2147483645

                PCTINCREASE      0

                FREELISTS        1

                FREELIST GROUPS  1

                BUFFER_POOL      DEFAULT

               ),

  PARTITION ENG_LIST

    LOGGING

    NOCOMPRESS

    TABLESPACE APPS_TS_TX_DATA

    PCTFREE    10

    INITRANS   11

    MAXTRANS   255

    STORAGE    (

                INITIAL          40K

                NEXT             8K

                MINEXTENTS       1

                MAXEXTENTS       2147483645

                PCTINCREASE      0

                BUFFER_POOL      DEFAULT

               ),

  PARTITION HZ_PARTY

    LOGGING

    NOCOMPRESS

    TABLESPACE APPS_TS_TX_DATA

    PCTFREE    10

    INITRANS   11

    MAXTRANS   255

    STORAGE    (

                INITIAL          40K

                NEXT             8K

                MINEXTENTS       1

                MAXEXTENTS       2147483645

                PCTINCREASE      0

                BUFFER_POOL      DEFAULT

               ),

  PARTITION GBX

    LOGGING

    NOCOMPRESS

    TABLESPACE APPS_TS_TX_DATA

    PCTFREE    10

    INITRANS   11

    MAXTRANS   255

    STORAGE    (

                INITIAL          40K

                NEXT             8K

                MINEXTENTS       1

                MAXEXTENTS       2147483645

                PCTINCREASE      0

                BUFFER_POOL      DEFAULT

               ),

  PARTITION PQH_ROLE

    LOGGING

    NOCOMPRESS

    TABLESPACE APPS_TS_TX_DATA

    PCTFREE    10

    INITRANS   11

    MAXTRANS   255

    STORAGE    (

                INITIAL          40K

                NEXT             8K

                MINEXTENTS       1

                MAXEXTENTS       2147483645

                PCTINCREASE      0

                BUFFER_POOL      DEFAULT

               ),

  PARTITION WF_LOCAL_ROLES

    LOGGING

    NOCOMPRESS

    TABLESPACE APPS_TS_TX_DATA

    PCTFREE    10

    INITRANS   11

    MAXTRANS   255

    STORAGE    (

                INITIAL          40K

                NEXT             8K

                MINEXTENTS       1

                MAXEXTENTS       2147483645

                PCTINCREASE      0

                BUFFER_POOL      DEFAULT

               ),

  PARTITION PER_ROLE

    LOGGING

    NOCOMPRESS

    TABLESPACE APPS_TS_TX_DATA

    PCTFREE    10

    INITRANS   11

    MAXTRANS   255

    STORAGE    (

                INITIAL          40K

                NEXT             8K

                MINEXTENTS       1

                MAXEXTENTS       2147483645

                PCTINCREASE      0

                BUFFER_POOL      DEFAULT

               ),

  PARTITION AMV_CHN

    LOGGING

    NOCOMPRESS

    TABLESPACE APPS_TS_TX_DATA

    PCTFREE    10

    INITRANS   11

    MAXTRANS   255

    STORAGE    (

                INITIAL          40K

                NEXT             8K

                MINEXTENTS       1

                MAXEXTENTS       2147483645

                PCTINCREASE      0

                BUFFER_POOL      DEFAULT

               ),

  PARTITION HZ_GROUP

    LOGGING

    NOCOMPRESS

    TABLESPACE APPLSYSX

    PCTFREE    10

    INITRANS   11

    MAXTRANS   255

    STORAGE    (

                INITIAL          40K

                NEXT             8K

                MINEXTENTS       1

                MAXEXTENTS       2147483645

                PCTINCREASE      0

                FREELISTS        1

                FREELIST GROUPS  1

                BUFFER_POOL      DEFAULT

               ),

  PARTITION HTB_SEC

    LOGGING

    NOCOMPRESS

    TABLESPACE APPS_TS_TX_DATA

    PCTFREE    10

    INITRANS   11

    MAXTRANS   255

    STORAGE    (

                INITIAL          40K

                NEXT             8K

                MINEXTENTS       1

                MAXEXTENTS       2147483645

                PCTINCREASE      0

                BUFFER_POOL      DEFAULT

               ),

  PARTITION UMX

    LOGGING

    NOCOMPRESS

    TABLESPACE APPLSYSX

    PCTFREE    10

    INITRANS   11

    MAXTRANS   255

    STORAGE    (

                INITIAL          40K

                NEXT             8K

                MINEXTENTS       1

                MAXEXTENTS       2147483645

                PCTINCREASE      0

                FREELISTS        1

                FREELIST GROUPS  1

                BUFFER_POOL      DEFAULT

               )

)

NOPARALLEL;

4.      >takes 10 mins
Option b: for gather stats for index : Connect as apps and run EXEC DBMS_STATS.gather_index_stats('APPLSYS', 'WF_LOCAL_USER_ROLES_N7', estimate_percent => 15); >less than 1 min

5.     Please run step1 to verify the plan

Note : Try performing option b first,since  it will take only 1 min to complete, if te issue still persists, then use option a as well.

Performance tuning tip for slow running sql

The below script will help in finding out if any index creation/tuning is required on a oracle table for a long running sql script.

Using the sid, fetch the sql_id from gv$sql , before running this script ( sql_id is the input for the script):



set echo on
set verify on
set serveroutput on
set linesize 120
undefine sql_id
spool sta.out
execute dbms_workload_repository.create_snapshot(flush_level=>'ALL');
select min(snap_id) from dba_hist_snapshot;
select max(snap_id) from dba_hist_snapshot;
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.CREATE_TUNING_TASK(
begin_snap=>&beginsnap,
end_snap=>&endsnap,
sql_id=>'&&sql_id',
scope=>'COMPREHENSIVE',
time_limit=>3600,
task_name=>'&&sql_id._tuning_task',
description=>'Tuning task for statement ' || '&&sql_id');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => '&&sql_id._tuning_task');
END;
/

SET LONG 10000
SET LONGCHUNKSIZE 10000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( '&&sql_id._tuning_task') from DUAL;
--
--
-- exec dbms_sqltune.drop_tuning_task(task_name=>'&&sql_id._tuning_task');
--
--
spool off