物化檢視實現資料庫倉庫主從複製(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. 更新統計資訊並建立統計分析任務

略。

;