At time of this writing, neither SharePlex for Oracle nor Oracle GoldenGate support replicating an Oracle
table that has ANYDATA datatype.
I have developed a workaround for this. I first created a
shadow table of the table with ANYDATA datatype in both source and target
database. The shadow table does not have a column with ANYDATA datatype. I then
created a trigger on the table with ANYDATA datatype in source database, and a
trigger on shadow table in target database. After populating the shadow table
with all the data from the source table, I setup to replicate the shadow
table. The triggers will guarantee that any changes at source will be replicated to
the target. Here is an example.
1. In the source database, create a table with ANYDATA datatype
and populate the table:
create table t_my_anydata
(id1 number not null,
id2 number not null,
colA varchar2(100
CHAR),
data anydata,
constraint
"PK_ID" primary key (id1, id2) using index
);
insert into t_my_anydata values
(10,11,'A',anydata.convertnumber(1));
insert into t_my_anydata values
(20,21,'B',anydata.ConvertDate(sysdate));
insert into t_my_anydata values (30,31,'C',anydata.convertvarchar2('test1'));
insert into t_my_anydata(id1,id2,colA) values (40,41,'D');
commit;
2. Use following query to check what is in the table:
column DATA_NUM format 99
column DATA_VARCHAR format a14
column colA format a20
set linesize 300
select id1,
id2,
colA,
CASE WHEN
anydata.gettypename(data) = 'SYS.NUMBER'
THEN
anydata.AccessNumber(data)
END data_num,
CASE WHEN
anydata.gettypename(data) = 'SYS.DATE'
THEN
anydata.AccessDate(data)
END data_date,
CASE WHEN
anydata.gettypename(data) = 'SYS.VARCHAR2'
THEN
anydata.AccessVarchar2(data)
END data_varchar
from t_my_anydata;
3. In the source database, create a shadow table:
create table t_my_anydata_s
(id1 number not null,
id2 number not null,
colA varchar2(100
CHAR),
data_num number,
data_date date,
data_varchar
varchar2(400 CHAR),
constraint "PK_ID_S" primary key (id1, id2) using
index
);
4. Populate shadow table with data from t_my_anydata table:
insert into t_my_anydata_s
select id1,
id2,
colA,
CASE WHEN
anydata.gettypename(data) = 'SYS.NUMBER'
THEN
anydata.AccessNumber(data)
END data_num,
CASE WHEN
anydata.gettypename(data) = 'SYS.DATE'
THEN
anydata.AccessDate(data)
END data_date,
CASE WHEN
anydata.gettypename(data) = 'SYS.VARCHAR2'
THEN
anydata.AccessVarchar2(data)
END data_varchar
from t_my_anydata;
COMMIT;
5. Repeat step 1 to 4 above in the target database, create and
populate both table t_my_anydata and t_my_anydata_s.
6. Create a trigger on table t_my_anydata in source database
to keep t_my_anydata and t_my_anydata_s tables in sync:
CREATE OR REPLACE TRIGGER t_my_anydata_trig
AFTER DELETE OR INSERT OR UPDATE ON t_my_anydata
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
IF deleting THEN
DELETE FROM
t_my_anydata_s
WHERE id1= :OLD.id1
AND id2= :OLD.id2;
ELSIF inserting THEN
INSERT INTO
t_my_anydata_s values
(:NEW.id1,
:NEW.id2,
:NEW.colA,
(CASE WHEN
anydata.gettypename(:NEW.data) = 'SYS.NUMBER'
THEN anydata.AccessNumber(:NEW.data)
END),
(CASE WHEN
anydata.gettypename(:NEW.data) = 'SYS.DATE'
THEN anydata.AccessDate(:NEW.data)
END),
(CASE WHEN
anydata.gettypename(:NEW.data) = 'SYS.VARCHAR2'
THEN anydata.AccessVarchar2(:NEW.data)
END)
);
ELSIF updating THEN
UPDATE
t_my_anydata_s
SET id1 = :NEW.id1,
id2 = :NEW.id2,
colA =
:NEW.colA,
data_num =
(CASE WHEN anydata.gettypename(:NEW.data) = 'SYS.NUMBER'
THEN anydata.AccessNumber(:NEW.data)
END),
data_date =
(CASE WHEN anydata.gettypename(:NEW.data) = 'SYS.DATE'
THEN anydata.AccessDate(:NEW.data)
END),
data_varchar =
(CASE WHEN anydata.gettypename(:NEW.data) = 'SYS.VARCHAR2'
THEN anydata.AccessVarchar2(:NEW.data)
END)
WHERE id1 =
:OLD.id1
AND id2 = :OLD.id2;
END IF;
END;
/
7. Create a trigger on t_my_anydata_s on target database to
keep t_my_anydata_s and t_my_anydata tables in scync:
CREATE OR REPLACE TRIGGER t_my_anydata_s_trig
AFTER DELETE OR INSERT OR UPDATE ON t_my_anydata_s
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
mydata1 ANYDATA;
BEGIN
IF :NEW.data_num is not null THEN
mydata1
:= anydata.convertnumber(:NEW.data_num);
ELSIF :NEW.data_date is not null THEN
mydata1
:= anydata.ConvertDate(:NEW.data_date);
ELSE
mydata1
:= anydata.convertvarchar2(:NEW.data_varchar);
END IF;
IF deleting THEN
DELETE FROM
t_my_anydata
WHERE id1 = :OLD.id1
AND id2 = :OLD.id2;
ELSIF inserting THEN
INSERT INTO
t_my_anydata values
(:NEW.id1,
:NEW.id2,
:NEW.colA,
mydata1
);
ELSIF updating THEN
UPDATE t_my_anydata
SET id1 = :NEW.id1,
id2 = :NEW.id2,
colA =
:NEW.colA,
data = mydata1
WHERE id1 =
:OLD.id1
AND id2 = :OLD.id2;
END IF;
END;
/
You can modify above scripts to support additional datatypes
that is wrapped in ANYDATA datatype. Please note, as of Oracle version 11gR2,
the only supported LOB datatype embedded in ANYDATA is BFILE.
8. Setup replication for table t_my_anydata_s. I will use
SharePlex for Oracle in this example:
a. Enable supplemental logging:
sqlplus / as sysdba
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY)
COLUMNS;
ALTER SYSTEM SWITCH LOGFILE;
b. Make sure SharePlex is running. If not, starts it:
su - splex
ps -ef|grep sp_
/opt/SharePlex/productdir/bin
-sh-3.2$ ./sp_cop &
c. Create a new config file for replicating table
t_my_anydata_s
cd /opt/SharePlex/vardir/config
>more anydata_config
datasource:o.SOURCEDB
zhengd.t_my_anydata_s zhengd.t_my_anydata_s targetsys@o.TARGETDB
d. Activate the cofiguration for table t_my_anydata_s
./sp_ctrl
sp_ctrl > list config
This will list all the configuration files and activation
status. Make sure no configuration is activated, since SharePlex can only have
one active configuration.
sp_ctrl > activate config anydata_config
9. Run tests to check if changes to the table t_my_anydata
are replicated:
a. Insert:
SQL> insert into t_my_anydata values
(50,51,'E',anydata.convertvarchar2('test2'));
1 row created.
SQL>commit;
Commit complete.
Check at source to see if insert is done on the table:
SQL> column DATA_NUM format 99
column DATA_VARCHAR format a14
column colA format a20
set linesize 300
SQL> select id1,
2 id2,
3 colA,
4 CASE WHEN sys.anydata.gettypename(data)
= 'SYS.NUMBER'
5 THEN
sys.anydata.AccessNumber(data)
6 END
data_num,
7 CASE WHEN
sys.anydata.gettypename(data) = 'SYS.DATE'
8 THEN
sys.anydata.AccessDate(data)
9 END
data_date,
10 CASE WHEN sys.anydata.gettypename(data) =
'SYS.VARCHAR2'
11 THEN
sys.anydata.AccessVarchar2(data)
12 END
data_varchar
13 from t_my_anydata;
ID1 ID2 COLA DATA_NUM DATA_DATE
DATA_VARCHAR
---------- ---------- -------------------- --------
--------- --------------
50 51 E test2
10 11 A 1
20 21 B 11-JUL-12
30 31 C test1
40 41 D
Check the same table on the target:
SQL> select id1,
2 id2,
3 colA,
4 CASE WHEN
sys.anydata.gettypename(data) = 'SYS.NUMBER'
5 THEN
sys.anydata.AccessNumber(data)
6 END
data_num,
7 CASE WHEN
sys.anydata.gettypename(data) = 'SYS.DATE'
8 THEN
sys.anydata.AccessDate(data)
9 END
data_date,
10 CASE WHEN sys.anydata.gettypename(data)
= 'SYS.VARCHAR2'
11 THEN
sys.anydata.AccessVarchar2(data)
12 END
data_varchar
13 from t_my_anydata;
ID1 ID2 COLA DATA_NUM DATA_DATE DATA_VARCHAR
---------- ---------- -------------------- --------
------------------ --------------
50 51 E
test2
10 11 A 1
20 21 B 11-JUL-12
30 31 C
test1
40 41 D
b. Update:
Now do a update at source:
SQL> update t_my_anydata set
data=anydata.convertnumber(3)
where id1=10;
1 row updated.
SQL> commit;
Commit complete.
Check both source and target again:
At source:
SQL> select id1,
2 id2,
3 colA,
4 CASE WHEN
sys.anydata.gettypename(data) = 'SYS.NUMBER'
5 THEN
sys.anydata.AccessNumber(data)
6 END
data_num,
7 CASE WHEN
sys.anydata.gettypename(data) = 'SYS.DATE'
8 THEN
sys.anydata.AccessDate(data)
9 END
data_date,
10 CASE WHEN
sys.anydata.gettypename(data) = 'SYS.VARCHAR2'
11 THEN
sys.anydata.AccessVarchar2(data)
12 END
data_varchar
13 from t_my_anydata;
ID1 ID2 COLA DATA_NUM DATA_DATE
DATA_VARCHAR
---------- ---------- -------------------- --------
--------- --------------
50 51 E test2
10 11 A 3
20 21 B 11-JUL-12
30 31 C test1
40 41 D
At the target:
SQL> select id1,
2 id2,
3 colA,
4 CASE WHEN
sys.anydata.gettypename(data) = 'SYS.NUMBER'
5 THEN
sys.anydata.AccessNumber(data)
6 END
data_num,
7 CASE WHEN
sys.anydata.gettypename(data) = 'SYS.DATE'
8 THEN
sys.anydata.AccessDate(data)
9 END
data_date,
10 CASE WHEN
sys.anydata.gettypename(data) = 'SYS.VARCHAR2'
11 THEN
sys.anydata.AccessVarchar2(data)
12 END
data_varchar
13 from t_my_anydata;
ID1 ID2 COLA DATA_NUM DATA_DATE DATA_VARCHAR
---------- ---------- -------------------- --------
------------------ --------------
50 51 E
test2
10 11 A 3
20 21 B 11-JUL-12
30 31 C
test1
40 41 D
c. Delete:
New do a delete at the source:
SQL> delete from t_my_anydata
where id2=51;
1 row deleted.
SQL> commit;
Commit complete.
Now Check both source and target:
At the source:
SQL> select id1,
2 id2,
2 colA,
4 CASE WHEN
sys.anydata.gettypename(data) = 'SYS.NUMBER'
5 THEN
sys.anydata.AccessNumber(data)
6 END
data_num,
7 CASE WHEN
sys.anydata.gettypename(data) = 'SYS.DATE'
8 THEN
sys.anydata.AccessDate(data)
9 END
data_date,
10 CASE WHEN
sys.anydata.gettypename(data) = 'SYS.VARCHAR2'
11 THEN
sys.anydata.AccessVarchar2(data)
12 END
data_varchar
13 from t_my_anydata;
ID1 ID2 COLA DATA_NUM DATA_DATE
DATA_VARCHAR
---------- ---------- -------------------- --------
--------- --------------
10 11 A 3
20 21 B 11-JUL-12
30 31 C test1
40 41 D
At the target:
SQL> select id1,
2 id2,
3 colA,
4 CASE WHEN
sys.anydata.gettypename(data) = 'SYS.NUMBER'
5 THEN
sys.anydata.AccessNumber(data)
6 END
data_num,
7 CASE WHEN
sys.anydata.gettypename(data) = 'SYS.DATE'
8 THEN
sys.anydata.AccessDate(data)
9 END
data_date,
10 CASE WHEN
sys.anydata.gettypename(data) = 'SYS.VARCHAR2'
11 THEN
sys.anydata.AccessVarchar2(data)
12 END
data_varchar
13 from t_my_anydata;
ID1 ID2 COLA DATA_NUM DATA_DATE DATA_VARCHAR
---------- ---------- -------------------- --------
------------------ --------------
10 11 A 3
20 21 B 11-JUL-12
30 31 C
test1
40 41 D
Reference: metalink note:
Oracle GoldenGate database Complete Database Profile check script for
Oracle DB (All Schemas) [ID 1298562.1]
Update:
As of version 7.6.3., SharePlex for Oracle supports replicating ANYDATA datatype if you set this in your environment before starting the daemon for replication:
SP_OCF_ANYDATA_TYPE_INVALID=0
