Oracle 實戰：DB_LINK 與物化視圖/快照同步完整實作

場景概覽

本文記錄一次真實環境操作：修改資料庫名稱 (SID)、配置靜態監聽、建立 DB_LINK、使用 Materialized View (物化視圖) 與 Snapshot (快照) 實現跨資料庫資料同步。

一、修改資料庫名稱 (SID: orcl → bitc)

Linux 環境完整步驟

1. 查看當前實例名

   SQL> SELECT instance_name FROM v$instance;
   INSTANCE_NAME
   ----------------
   orcl

2. 關閉資料庫與監聽

   SQL> SHUTDOWN IMMEDIATE;
   $ lsnrctl stop orcl

3. 修改系統配置檔
   • /etc/oratab：將 orcl: 改為 bitc:
   • ~/.bash_profile：export ORACLE_SID=bitc 並 source ~/.bash_profile 生效
4. 重命名 $ORACLE_HOME/dbs 相關檔案

   cd $ORACLE_HOME/dbs
   mv hc_orcl.dat     hc_bitc.dat
   mv lkORCL          lkBITC
   mv orapworcl       orapwbitc
   mv spfileorcl.ora  spfilebitc.ora

5. 重建密碼檔並啟動

   orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=oracle entries=5 force=y
   SQL> STARTUP

6. 驗證

   SQL> SELECT instance_name FROM v$instance;
   INSTANCE_NAME
   ----------------
   bitc

Windows 環境差異

• 無 /etc/oratab、.bash_profile。
• 透過服務管理器停止/啟動 Oracle 服務。
• 參數檔位於 $ORACLE_HOME/database。
• 密碼檔重建需在兩個 CMD 視窗配合環境變數執行。

二、配置靜態監聽 (Static Listener)

1. 使用 netmgr 圖形工具配置監聽器參數檔 (listener.ora)。
2. 設定 SID_LIST_LISTENER 區段，註冊 SID_NAME = bitc。
3. 動態註冊驗證：

   SQL> SHOW PARAMETER listener;
   SQL> ALTER SYSTEM SET local_listener = 'BITC';
   SQL> ALTER SYSTEM REGISTER;

4. 啟動監聽：

   $ lsnrctl start BITC
   $ lsnrctl status BITC

   輸出顯示 Service "bitc" 與 "orcl" 均指向 Instance "bitc"，確認靜態註冊成功。

注意：動態監聽預設埠 1521，多實例共用；配置 namespace 時所有實例主機名與埠必須完全一致，否則可能阻塞。64 位元 Oracle 配置 32 位元遠端 listener 可能因版本不相容失敗。

三、Materialized View 同步 (主鍵/ROWID 兩種模式)

環境

• 來源端：BITC (u01/abc)
• 目標端：TEST (原連 ORCL，現需重建 DB_LINK 指向 BITC)

1. 重建 DB_LINK

-- TEST 端
DROP PUBLIC DATABASE LINK conn_orcl;
CREATE PUBLIC DATABASE LINK CONN_BITC CONNECT TO u01 IDENTIFIED BY abc USING 'BITC';

2. 來源端建立主鍵表與 Materialized View Log

-- BITC 端 (u01)
CREATE TABLE t1 (id INT PRIMARY KEY, name VARCHAR(30));
CREATE MATERIALIZED VIEW LOG ON t1;  -- 必須有主鍵

3. 目標端建立 FAST REFRESH 物化視圖 (Primary Key)

-- TEST 端
CREATE MATERIALIZED VIEW t1_mv
REFRESH FAST
START WITH SYSDATE NEXT SYSDATE + 1/1440  -- 每分鐘
WITH PRIMARY KEY AS SELECT * FROM u01.t1@CONN_BITC;

• REFRESH FAST：增量刷新，需 MV Log 支援。
• START WITH ... NEXT ...：自動排程刷新間隔 (1/1440 天 = 1 分鐘)。

4. 驗證同步

-- BITC 端插入資料
INSERT INTO t1 VALUES (1, 'chal');
COMMIT;

-- TEST 端：刷新前無資料，1 分鐘後自動同步出現
SELECT * FROM t1_mv;  -- 顯示 1 | chal

5. 無主鍵表使用 ROWID 模式

-- BITC 端
CREATE TABLE stu (id INT, name VARCHAR(30));  -- 無主鍵
-- CREATE MATERIALIZED VIEW LOG ON stu;  -- 報錯 ORA-12014

-- TEST 端：FORCE 模式 (嘗試 FAST，失敗則 COMPLETE) + ROWID
CREATE MATERIALIZED VIEW stu_mv
REFRESH FORCE
START WITH SYSDATE NEXT SYSDATE + 1/1440
WITH ROWID AS SELECT * FROM u01.stu@CONN_BITC;

• 無主鍵只能用 ROWID，且只能 COMPLETE 或 FORCE 刷新。

四、Snapshot 同步 (舊版關鍵字，實為 Materialized View)

CREATE SNAPSHOT 等同於 CREATE MATERIALIZED VIEW，語法保留相容性。

有主鍵：增量刷新

-- BITC 端
CREATE TABLE test1 (id INT PRIMARY KEY, name VARCHAR(30));
CREATE SNAPSHOT LOG ON test1;  -- 實為 MV LOG
INSERT ALL INTO test1 VALUES(1,'egg') INTO test1 VALUES(2,'apple') SELECT * FROM dual;
COMMIT;

-- TEST 端
CREATE SNAPSHOT sn_test1 AS SELECT * FROM u01.test1@CONN_BITC;
ALTER SNAPSHOT sn_test1 REFRESH FAST START WITH SYSDATE NEXT SYSDATE + 1/1440 WITH PRIMARY KEY;

-- 驗證增量同步
INSERT INTO test1 VALUES (3, 'fish'); COMMIT;
-- 1 分鐘後 sn_test1 自動出現第 3 筆

無主鍵：完整刷新

-- BITC 端
CREATE TABLE test2 (id INT, name VARCHAR(30));
INSERT INTO test2 VALUES (1, 'beef'); COMMIT;

-- TEST 端：COMPLETE 刷新
CREATE SNAPSHOT sn_test2 REFRESH COMPLETE
START WITH SYSDATE NEXT SYSDATE + 1/1440
WITH ROWID AS SELECT * FROM u01.test2@CONN_BITC;

五、補充：熱備份 vs Snapshot/物化視圖

六、全域資料庫名、服務名、實例名、SID 關係圖解

• 一般單機：DB_NAME = INSTANCE_NAME = ORACLE_SID = SERVICE_NAME (無域名時)。
• RAC 環境：一個 DB_NAME 對應多個 INSTANCE_NAME (多節點)。
• 網路連線字串 9i+ 建議使用 SERVICE_NAME 而非 SID。