xterm title bar

Add the following to your favorite shell login:

Advertisement

My Change Data Capture Adventure Part1

This is my adventure in automating the configuration for Change Data Capture (CDC) using Asynchronous HotLog.

Please read the reference materials since not all steps are listed here.

For example: setting the database initialization parameter and FORCE LOGGING

Simply, CDC have the following components

1. Source
2. Publisher to publish the changed data
3. Subscriber to subscribe to the changed data

In my example:

1. Source = SCOTT
2. Publisher = OPS$ORACLE
3. Subscriber = SCOTT_SUB

As part of the setup, I have created 2 tables – CLIENTS (Source Schema) and XCDC (Source Table)

OPS$ORACLE@dbtest> select * from CLIENTS;

PK_CLIENT_ID
------------------------------
JOE
JOHN
SCOTT

OPS$ORACLE@dbtest> select * from XCDC;

TABLE_NAME
 ------------------------------
 BADTABLE
 DEPT
 EMP

OPS$ORACLE@dbtest>

NOTE:  BADTABLE is not a valid table.

ANOTHER NOTE:  This is a Multi-Tenant Data Architecture; hence, EMP & DEPT tables will exist for ALL clients.

******* GRANT PRIVILEGES TO THE PUBLISHER: *******

SYS@dbtest> -- Execute as SYS
SYS@dbtest> /*
SYS@dbtest> CREATE TABLESPACE cdc_data DATAFILE
SYS@dbtest>   '/oracle/oradata/dbtest/cdc_data01.dbf' SIZE 524352K AUTOEXTEND ON NEXT 512M MAXSIZE 16777280K
SYS@dbtest> EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1m
SYS@dbtest> SEGMENT SPACE MANAGEMENT AUTO;
SYS@dbtest> */
SYS@dbtest> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Database altered.

SYS@dbtest> ALTER USER ops$oracle QUOTA UNLIMITED ON cdc_data;

User altered.

SYS@dbtest> ALTER USER ops$oracle QUOTA UNLIMITED ON sysaux;

User altered.

SYS@dbtest> ALTER USER ops$oracle DEFAULT TABLESPACE cdc_data;

User altered.

SYS@dbtest> -- system privs
SYS@dbtest> GRANT create session TO ops$oracle;

Grant succeeded.

SYS@dbtest> GRANT create table TO ops$oracle;

Grant succeeded.

SYS@dbtest> GRANT create sequence TO ops$oracle;

Grant succeeded.

SYS@dbtest> GRANT create procedure TO ops$oracle;

Grant succeeded.

SYS@dbtest> GRANT create job TO ops$oracle;

Grant succeeded.

SYS@dbtest> -- role privs
SYS@dbtest> GRANT execute_catalog_role TO ops$oracle;

Grant succeeded.

SYS@dbtest> GRANT select_catalog_role TO ops$oracle;

Grant succeeded.

SYS@dbtest> GRANT dba TO ops$oracle;

Grant succeeded.

SYS@dbtest> GRANT execute ON dbms_cdc_publish TO ops$oracle;

Grant succeeded.

SYS@dbtest> -- streams specific priv
SYS@dbtest> execute dbms_streams_auth.grant_admin_privilege(grantee=>'OPS$ORACLE');

PL/SQL procedure successfully completed.

SYS@dbtest> --
SYS@dbtest> connect /
Connected.
OPS$ORACLE@dbtest> CREATE TABLE xcdc (table_name VARCHAR2 (30));

Table created.

OPS$ORACLE@dbtest> ALTER TABLE xcdc ADD CONSTRAINT PK_xcdc PRIMARY KEY(table_name) USING INDEX;

Table altered.

OPS$ORACLE@dbtest> exit

sqlp is just an alias

> alias sqlp
alias sqlp='sqlplus /'

******* ADD SUPPLEMENTAL LOG DATA FOR SOURCE TABLES: *******

> sqlp @add_sup.sql scott

SQL*Plus: Release 11.2.0.2.0 Production on Sat Nov 20 06:02:38 2010

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

OPS$ORACLE@dbtest> define vsource = &1
OPS$ORACLE@dbtest> DECLARE
 2     l_sql   VARCHAR2 (1000);
 3  BEGIN
 4     FOR x IN (
 5            SELECT t.owner, z.table_name
 6              FROM xcdc z, clients c, dba_tables t
 7             WHERE c.pk_client_id = UPPER('&&vsource')
 8               AND c.pk_client_id = t.owner
 9               AND z.table_name   = t.table_name
 10            MINUS
 11            SELECT g.owner, g.table_name
 12              FROM dba_log_groups g, clients c
 13             WHERE     g.log_group_type = 'ALL COLUMN LOGGING'
 14                   AND c.pk_client_id = owner
 15                   AND c.pk_client_id = UPPER('&&vsource')
 16     ) LOOP
 17        l_sql := 'alter table '||x.owner||'.'||x.table_name||' add supplemental log data (all) columns';
 18        EXECUTE IMMEDIATE l_sql;
 19        DBMS_OUTPUT.put_line (l_sql);
 20     END LOOP;
 21  EXCEPTION
 22     WHEN OTHERS
 23     THEN
 24        DBMS_OUTPUT.put_line ('******* ERROR: ' || l_sql);
 25        RAISE;
 26  END;
 27  /
alter table SCOTT.DEPT add supplemental log data (all) columns
alter table SCOTT.EMP add supplemental log data (all) columns

PL/SQL procedure successfully completed.

OPS$ORACLE@dbtest> select owner, table_name from DBA_LOG_GROUPS where owner=UPPER('&vsource');

OWNER                          TABLE_NAME
------------------------------ ------------------------------
SCOTT                          DEPT
SCOTT                          EMP

OPS$ORACLE@dbtest> exit
 

NOTE that BADTABLE was ignored because it is not in DBA_TABLES.

******* PREPARE THE SOURCE TABLES: *******

> sqlp @prepare_tab.sql scott

SQL*Plus: Release 11.2.0.2.0 Production on Sat Nov 20 06:16:27 2010

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

OPS$ORACLE@dbtest> define vsource = &1
OPS$ORACLE@dbtest> DECLARE
 2     l_sql     VARCHAR2 (1000);
 3     l_table   VARCHAR2 (100);
 4  BEGIN
 5     FOR x IN (
 6            SELECT t.owner, z.table_name
 7              FROM xcdc z, clients c, dba_tables t
 8             WHERE c.pk_client_id = UPPER('&&vsource')
 9               AND c.pk_client_id = t.owner
 10               AND z.table_name   = t.table_name
 11            MINUS
 12            SELECT p.table_owner, p.table_name
 13              FROM dba_capture_prepared_tables p
 14              WHERE p.table_owner=UPPER('&&vsource')
 15     ) LOOP
 16        l_table := x.owner||'.'||x.table_name;
 17        DBMS_CAPTURE_ADM.prepare_table_instantiation (l_table);
 18     END LOOP;
 19  EXCEPTION
 20     WHEN OTHERS
 21     THEN
 22        DBMS_OUTPUT.put_line ('******* ERROR: ' || l_sql);
 23        RAISE;
 24  END;
 25  /

PL/SQL procedure successfully completed.

OPS$ORACLE@dbtest> select table_owner, table_name from DBA_CAPTURE_PREPARED_TABLES where table_owner=UPPER('&&vsource');

TABLE_OWNER                    TABLE_NAME
------------------------------ ------------------------------
SCOTT                          EMP
SCOTT                          DEPT

OPS$ORACLE@dbtest> exit
 

******* CREATE CHANGE SETS: *******

> sqlp @changeset.sql

SQL*Plus: Release 11.2.0.2.0 Production on Sat Nov 20 06:32:17 2010

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

OPS$ORACLE@dbtest> DECLARE
 2  l_cs VARCHAR2(100);
 3  l_de VARCHAR2(100);
 4  BEGIN
 5    FOR x IN (SELECT pk_client_id FROM clients)
 6    LOOP
 7       l_cs := x.pk_client_id||'_CS';
 8       l_de := x.pk_client_id||'_CHANGE_SET';
 9       dbms_cdc_publish.create_change_set(change_set_name=>l_cs,description=>l_de,change_source_name=>'HOTLOG_SOURCE',stop_on_ddl=>'Y') ;
 10    END LOOP;
 11  END;
 12  /

PL/SQL procedure successfully completed.

OPS$ORACLE@dbtest> set echo off
==============================
SET_NAME                      : SCOTT_CS
CHANGE_SOURCE_NAME            : HOTLOG_SOURCE
BEGIN_DATE                    :
END_DATE                      :
BEGIN_SCN                     :
END_SCN                       :
FRESHNESS_DATE                :
FRESHNESS_SCN                 :
ADVANCE_ENABLED               : N
IGNORE_DDL                    :
CREATED                       : 20-NOV-2010 06:32:41
ROLLBACK_SEGMENT_NAME         :
ADVANCING                     : N
PURGING                       : N
LOWEST_SCN                    : 0
TABLESPACE                    :
CAPTURE_ENABLED               : N
STOP_ON_DDL                   : Y
CAPTURE_ERROR                 : N
CAPTURE_NAME                  : CDC$C_SCOTT_CS
QUEUE_NAME                    : CDC$Q_SCOTT_CS
QUEUE_TABLE_NAME              : CDC$T_SCOTT_CS
APPLY_NAME                    : CDC$A_SCOTT_CS
SET_DESCRIPTION               : SCOTT_CHANGE_SET
PUBLISHER                     : OPS$ORACLE
LOWEST_TIMESTAMP              :
MAP_NAME                      : CDC$M_SCOTT_CS
==============================

PL/SQL procedure successfully completed.
 

So far, this has been the easy part. The harder part is to create_change_table and provide column_type_list.

I will post as soon as I get a working model.

References:
>>Database Data Warehousing Guide: Change Data Capture
Comparison Between Oracle Streams and Change Data Capture [ID 727445.1]

How to modify AWR snapshot

Since I am using a third party application to monitor and to gather metrics for the database, it does not make sense to have AWR running as well.

It’s possible to modify the interval and retention for AWR.

AWR defaults:

SQL> @pr ‘select snap_interval,retention from dba_hist_wr_control’;
==============================
SNAP_INTERVAL : +00000 01:00:00.0
RETENTION : +00008 00:00:00.0
==============================

PL/SQL procedure successfully completed.

Modify AWR interval:

SQL> exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(interval => 0);

PL/SQL procedure successfully completed.

SQL> @pr ‘select snap_interval,retention from dba_hist_wr_control’;
==============================
SNAP_INTERVAL : +40150 00:00:00.0
RETENTION : +00008 00:00:00.0
==============================

PL/SQL procedure successfully completed.

Modify AWR retention:

SQL> exec dbms_workload_repository.modify_baseline_window_size(1);

PL/SQL procedure successfully completed.

SQL> exec dbms_workload_repository.modify_snapshot_settings(retention=>1440);

PL/SQL procedure successfully completed.

SQL> @pr ‘select snap_interval,retention from dba_hist_wr_control’;
==============================
SNAP_INTERVAL : +40150 00:00:00.0
RETENTION : +00001 00:00:00.0
==============================

PL/SQL procedure successfully completed.

The above information shows the retention period of 1 day and interval between snaphots is 40,150 days.

Reference:
>>Managing the Automatic Workload Repository

>> opens in new tab.

Data Guard Monitor Redo Apply 11.2.0.2

Oracle just made monitoring standby databases easier with the introduction of a new column GAP_STATUS to V$ARCHIVE_DEST_STATUS.

Here is an example from 10.2.0.4 database

> select * from v$version;

BANNER
—————————————————————-
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bi
PL/SQL Release 10.2.0.4.0 – Production
CORE 10.2.0.4.0 Production
TNS for Solaris: Version 10.2.0.4.0 – Production
NLSRTL Version 10.2.0.4.0 – Production

> @t
old 2: c clob := q’\&1\’;
new 2: c clob := q’\select * from v$archive_dest_status where dest_id<=2\';
==============================
DEST_ID : 1
DEST_NAME : LOG_ARCHIVE_DEST_1
STATUS : VALID
TYPE : LOCAL
DATABASE_MODE : OPEN
RECOVERY_MODE : IDLE
PROTECTION_MODE : MAXIMUM PERFORMANCE
DESTINATION :
STANDBY_LOGFILE_COUNT : 0
STANDBY_LOGFILE_ACTIVE : 0
ARCHIVED_THREAD# : 1
ARCHIVED_SEQ# : 70097
APPLIED_THREAD# : 0
APPLIED_SEQ# : 0
ERROR :
SRL : NO
DB_UNIQUE_NAME :
SYNCHRONIZATION_STATUS : CHECK CONFIGURATION
SYNCHRONIZED : NO
==============================
DEST_ID : 2
DEST_NAME : LOG_ARCHIVE_DEST_2
STATUS : VALID
TYPE : PHYSICAL
DATABASE_MODE : MOUNTED-STANDBY
RECOVERY_MODE : MANAGED REAL TIME APPLY
PROTECTION_MODE : MAXIMUM PERFORMANCE
DESTINATION :
STANDBY_LOGFILE_COUNT : 5
STANDBY_LOGFILE_ACTIVE : 4
ARCHIVED_THREAD# : 1
ARCHIVED_SEQ# : 70082
APPLIED_THREAD# : 1
APPLIED_SEQ# : 70082
ERROR :
SRL : NO
DB_UNIQUE_NAME :
SYNCHRONIZATION_STATUS : CHECK CONFIGURATION
SYNCHRONIZED : NO
==============================

PL/SQL procedure successfully completed.

Here is an example from 11.2.0.2 database

> select * from v$version;

BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
PL/SQL Release 11.2.0.2.0 – Production
CORE 11.2.0.2.0 Production
TNS for Solaris: Version 11.2.0.2.0 – Production
NLSRTL Version 11.2.0.2.0 – Production

> @t
old 2: c clob := q’\&1\’;
new 2: c clob := q’\select * from v$archive_dest_status where dest_id<=2\';
==============================
DEST_ID : 1
DEST_NAME : LOG_ARCHIVE_DEST_1
STATUS : VALID
TYPE : LOCAL
DATABASE_MODE : OPEN
RECOVERY_MODE : IDLE
PROTECTION_MODE : MAXIMUM PERFORMANCE
DESTINATION :
STANDBY_LOGFILE_COUNT : 0
STANDBY_LOGFILE_ACTIVE : 0
ARCHIVED_THREAD# : 1
ARCHIVED_SEQ# : 71345
APPLIED_THREAD# : 0
APPLIED_SEQ# : 0
ERROR :
SRL : NO
DB_UNIQUE_NAME :
SYNCHRONIZATION_STATUS : CHECK CONFIGURATION
SYNCHRONIZED : NO
GAP_STATUS :
==============================
DEST_ID : 2
DEST_NAME : LOG_ARCHIVE_DEST_2
STATUS : VALID
TYPE : PHYSICAL
DATABASE_MODE : MOUNTED-STANDBY
RECOVERY_MODE : MANAGED REAL TIME APPLY
PROTECTION_MODE : MAXIMUM PERFORMANCE
DESTINATION :
STANDBY_LOGFILE_COUNT : 4
STANDBY_LOGFILE_ACTIVE : 1
ARCHIVED_THREAD# : 1
ARCHIVED_SEQ# : 71345
APPLIED_THREAD# : 1
APPLIED_SEQ# : 71344
ERROR :
SRL : YES
DB_UNIQUE_NAME :
SYNCHRONIZATION_STATUS : CHECK CONFIGURATION
SYNCHRONIZED : NO
GAP_STATUS : NO GAP
==============================

PL/SQL procedure successfully completed.

See the GAP_STATUS: NO GAP? Another incentive to upgrade.

Reference:
Monitoring Primary, Physical Standby, and Snapshot Standby Databases
V$ARCHIVE_DEST_STATUS

Upgrade Pain01

Completed upgrade of OLTP database from 10.2.0.4 to 11.2.0.2

To my surprise, I found 199 dead locks and increasing fast in the alert log as shown:

> grep -ic ‘ORA-00060: Deadlock detected’ alert_db06.log
199

The trace file shows:

*** 2010-11-07 03:53:20.238
DEADLOCK DETECTED ( ORA-00060 )

[Transaction Deadlock]

The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:

Deadlock graph:
———Blocker(s)——– ———Waiter(s)———
Resource Name process session holds waits process session holds waits
TM-0003b69d-00000000 113 38 SX SSX 119 221 SX SSX
TM-0003b69d-00000000 119 221 SX SSX 113 38 SX SSX

session 38: DID 0001-0071-00000004 session 221: DID 0001-0077-00000004
session 221: DID 0001-0077-00000004 session 38: DID 0001-0071-00000004

Rows waited on:
Session 38: no row
Session 221: no row

—– Information for the OTHER waiting sessions —–
Session 221:
sid: 221 ser: 4 audsid: 795496700 user: 211/JOHN
flags: (0x45) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40008) -/-
pid: 119 O/S info: user: oracle, term: UNKNOWN, ospid: 25907
image: oracle@proddb06
client details:
O/S info: user: john, term: unknown, ospid: 1234
machine: terminator program: JDBC Thin Client
application name: JDBC Thin Client, hash value=2546894660
current SQL:
DELETE FROM parent WHERE pk_id = :”SYS_B_0″

The following deadlock is not an ORACLE error. It is a deadlock due to the design of an application

Try telling this to the duhvelopers.

Before going further, I would like to thank Google and everyone who shares his/her knowledge and pain.

google: deadlock 11g and found Deadlock on Oracle 11g but Not on 10g

Root cause analysis:

Bad Design

Anecdotal Upgrade Testing

Upgrade

Not spending enough time on the internet

Auditing

I just completed AUDITING for 11.2.0.2 database with the following options:

NOAUDIT ALL;
AUDIT ALTER SYSTEM;
AUDIT DATABASE LINK;
AUDIT INDEX;
AUDIT MATERIALIZED VIEW;
AUDIT PROCEDURE;
AUDIT PROFILE;
AUDIT PUBLIC DATABASE LINK;
AUDIT PUBLIC SYNONYM;
AUDIT ROLE;
AUDIT SEQUENCE;
AUDIT SESSION;
AUDIT SYNONYM;
AUDIT SYSTEM AUDIT;
AUDIT SYSTEM GRANT;
AUDIT TABLE;
AUDIT TRIGGER;
AUDIT TYPE;
AUDIT USER;
AUDIT VIEW;
AUDIT ALTER, GRANT ON DEFAULT;

BY SESSION is the default.
BY ACCESS creates more detailed records.
ON DEFAULT options for newly create objects.

Reference: Oracle® Database SQL Language Reference 11g Release 2 (11.2)