oracle实现主从复制,物化视图实现数据库仓库主从复制(2)

文章版权所有 Jusin Hao(luckyfriends) ，支持原创，转载请注明。

1. 创建物化视图

1.1. 建立DBLink

复制库(10.168.2.12)上配置tns

/u01/app/oracle/product/11.2.0/db_1/network/admin\tnsnames.ora增加

bidw55 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.168.2.11)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = bidw)

)

)

复制库(10.168.2.12)上建立datalink:

以BI_USE55R用户登录：

CREATE public DATABASE LINK DW11LINK CONNECT TO BI_USER IDENTIFIED BY "oracle" using 'bidw55';

1.2. 生产库(10.168.2.11)上建立MV LOG 物化试图日志

以BI_USER用户登录创建物化试图日志。

1.2.1. 没有主键的，建mvlog的方式：

CREATE MATERIALIZED VIEW LOG ON CHECK_DM TABLESPACE "BI_USER_DATA"

WITH ROWID, SEQUENCE INCLUDING NEW VALUES;

脚本生成方法：

select 'CREATE MATERIALIZED VIEW LOG ON ' || a.table_name ||

' TABLESPACE BI_MATERIALIZED WITH ROWID, SEQUENCE INCLUDING NEW VALUES;'

from user_tables a

where a.table_name in ('CHECK_DM',…..);

1.2.2. 有主键的表，建mvlog方式：

CREATE MATERIALIZED VIEW LOG ON CHECK_DM TABLESPACE "MATERIALIZED" WITH ROWID, PRIMARY KEY, SEQUENCE INCLUDING NEW VALUES;

脚本生成方法：

select 'CREATE MATERIALIZED VIEW LOG ON ' || a.table_name ||

' TABLESPACE BI_MATERIALIZED WITH ROWID, PRIMARY KEY, SEQUENCE INCLUDING NEW VALUES;'

from user_tables a

where a.table_name in ('CHECK_DM',…..);

1.3. 复制库(10.168.2.12)上建立MV物化试图

以BI_USER12用户登录从库，创建物化视图

1.3.1. 没有主键的表，建mv的方式

CREATE MATERIALIZED VIEW CHECK_DM REFRESH FAST WITH ROWID ON DEMAND

AS SELECT * FROM CHECK_DM@DW11LINK;

脚本生成方法：

select 'CREATE MATERIALIZED VIEW ' || a.table_name ||

' parallel 4 REFRESH FAST WITH ROWID ON DEMAND AS SELECT /*+ parallel(4) */ * FROM ' ||

a.table_name || '@DW11LINK;'

from user_tables a

where a.table_name in ('CHECK_DM',…..);

1.3.2. 有主键的表，建mv的方式：

命令参考：

CREATE MATERIALIZED VIEW CHECK_DM REFRESH FAST WITH PRIMARY KEY ON DEMAND AS SELECT * FROM CHECK_DM@ERPLINK;

----指定表空间

CREATE MATERIALIZED VIEW CHECK_DM tablespace bi_user_data REFRESH FAST WITH PRIMARY KEY ON DEMAND AS SELECT * FROM CHECK_DM@ERPLINK;

---加入并行和nologging

CREATE MATERIALIZED VIEW ZONE_INFO parallel 4 nologging REFRESH FAST WITH PRIMARY KEY ON DEMAND AS SELECT /*+ parallel(4) */ * FROM ZONE_INFO@DW11LINK;

脚本生成方法：

select 'CREATE MATERIALIZED VIEW ' || a.table_name ||

' parallel 4 nologging REFRESH FAST WITH PRIMARY KEY ON DEMAND AS SELECT /*+ parallel(4) */ * FROM ' ||

a.table_name || '@DW11LINK;'

from user_tables a

where a.table_name in ('CHECK_DM',…..);

1.4. 复制库(10.168.2.12)上建立刷新组和任务

以BI_USER12用户登录创建.

1.4.1. 创建刷新组：

BEGIN

DBMS_REFRESH.MAKE(

name => 'JBLJOB',

list => '',

next_date => SYSDATE,

interval => '/*60:Mins*/ sysdate + 60/(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;

1.4.2. 添加任务：

BEGIN

DBMS_REFRESH.ADD(

name => 'JBLJOB',

list => 'CHECK_DM',

lax => TRUE);

END;

/

创建刷新任务的脚本生成方法：

SELECT 'BEGIN DBMS_REFRESH.ADD(name =>''' || 'JBLJOB''' || ',' ||

'list =>''' || a.table_name || ''',lax => TRUE);END;/commit;'

from user_tables a

where a.table_name in ('CHECK_DM',…..);

2. 创建物化视图的索引

用以下语句从生产库上导出建索引的DDL语句,然后在复制库上执行

2.1. 在生产服务器上导出索引：

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',FALSE);

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'CONSTRAINTS',FALSE);

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',true);

set pagesize 0

set long 100000

set feedback off

set echo off

set linesize 2000

spool d:\create_index_result.sql

SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name)

FROM USER_INDEXES u

where u.table_name in('CHECK_DM',…..);

spool off;

2.2. 格式化索引脚本

在复制库上创建索引前，需要注意生成的索引可能格式需要调整，

其中唯一索引改为非唯一索引；

修改索引所在表空间；

修改索引所属用户名；

索引较多加入nologging、并行；

有主键的表在创建视图过程中会创建对应的主键唯一索引；

大体格式如下：

CREATE INDEX "BI_USER12"."PK_CHECK_DM" ON "BI_USER12"."CHECK_DM " ("DC_ID") tablespace bi_user_index parallel 4 nologgingparallel 4 nologging;

3. 更新统计信息并建立统计分析任务

略。

;