Hi Friends
Pl. give me syntax to create multi master snapshot.
suppose I have same table name on different location and I want to
create
single snapshot at my location
based on different location tables
Printable View
Hi Friends
Pl. give me syntax to create multi master snapshot.
suppose I have same table name on different location and I want to
create
single snapshot at my location
based on different location tables
tahiti.oracle.com
Basic script for creating snapshot logs on masterrep site and
snapshots on snapshot site - This is assuming that you have the
site work done (setup master and snapshot sites)
----------------------- MASTER SITE ----------------------------
CONNECT REPADMIN/xxxx@MASTERREPSITE
-- SAI_PARAMETERS ---
execute DBMS_REPCAT.CREATE_MASTER_REPOBJECT(-
gname => '"WORKORDER_DETAIL"',-
type => 'TABLE',-
oname => '"SAI_PARAMETERS"',-
sname => '"OWNER"',-
copy_rows => TRUE,-
comment => 'SAI_PARAMETERS table in WORKORDER DETAIL',-
use_existing_object => TRUE);
-- SAI_PARAMETERS ---
execute dbms_repcat.generate_replication_support (-
sname => 'OWNER',-
oname => 'SAI_PARAMETERS',-
type => 'table',-
distributed => true,-
min_communication => true);
-- SAI_PARAMETERS ---
CREATE SNAPSHOT LOG ON "OWNER"."SAI_PARAMETERS"
TABLESPACE "WORK_ORDER"
PARALLEL ( DEGREE 2)
WITH PRIMARY KEY
INCLUDING NEW VALUES;
COMMIT;
--------- SAI_PARAMETERS (CONFLICT RESOLUTION) ------------
--
BEGIN
DBMS_REPCAT.DEFINE_COLUMN_GROUP(
'SYSADM',
'SAI_PARAMETERS',
'SAI_PARAMETERS_GRP',
'Column Group for update resolution - Snapshot wins');
END;
/
BEGIN
DBMS_REPCAT.ADD_GROUPED_COLUMN(
'SYSADM',
'SAI_PARAMETERS',
'SAI_PARAMETERS_GRP',
'*');
END;
/
BEGIN
DBMS_REPCAT.ADD_UPDATE_RESOLUTION(
'SYSADM',
'SAI_PARAMETERS',
'SAI_PARAMETERS_GRP',
5,
'OVERWRITE',
'*',
NULL,
NULL,
'Update Conflict resolution - Snapshot wins');
END;
/
BEGIN
DBMS_REPCAT.ADD_UNIQUE_RESOLUTION(
'SYSADM',
'SAI_PARAMETERS',
'PK_SAI_PARAMETERS',
10,
'DISCARD',
'*',
NULL,
'Insert Conflict resolution - Master wins');
END;
/
COMMIT;
EXECUTE DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT(-
sname => 'OWNER',-
oname => 'SAI_PARAMETERS',-
type => 'table',-
distributed => true,-
min_communication => true)
COMMIT;
----------------------- SNAPSHOT SITE ----------------------------
CONNECT SNAPADMIN/XXX@SNAPSHOTSITE
-- SAI_PARAMETERS ---
CREATE SNAPSHOT "OWNER"."SAI_PARAMETERS"
STORAGE ( INITIAL 1M NEXT 16384 MAXEXTENTS 249)
PARALLEL ( DEGREE 2)
LOGGING
TABLESPACE "WORK_ORDER"
BUILD IMMEDIATE
USING INDEX TABLESPACE "USER_INDEX"
STORAGE( INITIAL 16384 nEXT 16384 MAXEXTENTS 249 )
REFRESH FAST
ON DEMAND
FOR UPDATE
AS
SELECT * FROM [email protected]D;
-- SAI_PARAMETERS ---
execute dbms_repcat.create_snapshot_repobject(-
sname => 'OWNER',-
oname => 'SAI_PARAMETERS',-
type => 'SNAPSHOT',-
ddl_text => 'select * from [email protected]D',-
comment => 'Created by '||user||' on '||sysdate,-
gname => 'WORKORDER_DETAIL',-
gen_objs_owner => 'SYSADM',-
MIN_COMMUNICATION => TRUE)
BEGIN
DBMS_REFRESH.ADD(
name => 'SNAPADMIN.WORKORDER_DETAIL_ref',
list => 'sysadm.SAI_PARAMETERS',
lax => TRUE);
END;
/
CONNECT OWNER/XXXX@SNAPSHOTSITE
GRANT SELECT,INSERT,UPDATE,DELETE ON SAI_PARAMETERS TO PUBLIC
;
drop public synonym SAI_parameters;
CREATE PUBLIC SYNONYM SAI_PARAMETERS FOR OWNER.SAI_PARAMETERS
;