NC数据双引方案.docVIP

NC数据双引擎方案 (一)确认复制库安装在存储上 复制库192.168.100.115上ORACLE数据库已经创建在存储上. (二)确定需要复制的表 自定义查询需要的数据表清单(即自定义查询用到的表). 除了业务需要的表,pub_datadict,pub_datadictdetail也需要同步, 自定义查询设计时需要,否则自定义查询设计打不开. (三)建立datalink 复制库(192.168.100.115)上 E:\oracle\product\10.2.0\db_1\network\admin\tnsnames.ora增加 ERP = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.112)(PORT = 1521)) ) (CONNECT_DATA = (SID = ERP) (SERVER = DEDICATED) ) ) 复制库(192.168.100.115)上建立datalink: 以NC用户登录： CREATE public DATABASE LINK ERPLINK CONNECT TO NC IDENTIFIED BY NC USING ERP’; (四)生产库(192.168.100.112)上建立MV LOG 物化试图日志 以NC用户登录创建. 见jblmvlog.sql 注意的是有些表没有主键，建mvlog的方式如下例 CREATE MATERIALIZED VIEW LOG ON cm_cost TABLESPACE MATERIALIZED WITH ROWID, SEQUENCE INCLUDING NEW VALUES; 有主键的方式如下例： CREATE MATERIALIZED VIEW LOG ON arap_djfb TABLESPACE MATERIALIZED WITH ROWID, PRIMARY KEY, SEQUENCE INCLUDING NEW VALUES; MATERIALIZED为表空间 (五)复制库(192.168.100.115)上建立MV物化试图 以NC用户登录创建. 见jblmv.sql 注意的是有些表没有主键，建mvlog的方式如下例 CREATE MATERIALIZED VIEW cm_cost REFRESH FAST WITH ROWID ON DEMAND AS SELECT * FROM cm_cost@ERPLINK; 有主键的方式如下例： CREATE MATERIALIZED VIEW arap_djfb REFRESH FAST WITH PRIMARY KEY ON DEMAND AS SELECT * FROM arap_djfb@ERPLINK; (六)复制库(192.168.100.115)上建立刷新组和任务 以NC用户登录创建. 见jvmjob.sql 创建刷新组： BEGIN DBMS_REFRESH.MAKE( name = JBLJOB, list = , next_date = SYSDATE, interval = /*10:Mins*/ sysdate + 10/(60*24), implicit_destroy = FALSE, lax = FALSE, job = 0, rollback_seg = NULL, push_deferred_rpc = FALSE, refresh_after_errors = TRUE, purge_option = NULL, parallelism = NULL, heap_size = NULL); END; / commit; 添加任务： BEGIN DBMS_REFRESH.ADD( name = JBLJOB, list = arap_djfb, lax = TRUE); E