oracle資料庫主主複製

標籤：開啟   creat   規劃   epc   cond   ted   名稱   ica   oracl   

oracle 複製基於表複製配置過程；

規劃：

檢查oarcle資料庫是否支援進階複製功能，true

>Select value from v$OPTION where parameter=‘advanced replicaiont‘

2,是否支援global_names

(當GLOBAL_NAMES參數設定為TRUE時，使用DATABASE LINK時，DATABASE LINK的名稱必須與被串連庫的GLOBAL_NAME一致)

 

>show parameter global_names ;結果為true

查看global_name配置：

>select * from global_name;

3，查看job_queue_processes; 在0-1000之間

>show parameter job_queue_processes;

 

4，查看open_links

>show parameter open_links; default：4 不可更改

5,查看全域資料庫名稱：

>select * from global_name;

6，設定全域資料庫名稱（安裝過程中由於字串長度等原因，實際global_name配置不正確，安裝完成更改即可。否則dblink串連不上）：

>alter database rename global_name to masterone.test.com;

>alter database rename global_name to mastertwo.test.com;

1，建立資料庫，    全域資料庫名稱分別為：masterone.test.com/mastertwo.test.com    Serivce name 分別為：masterone.test.com/mastertwo.test.com2，在兩個資料庫中tnsnames.ora配置客戶訪問：配置內容如下：    MASTERTWO =      (DESCRIPTION =        (ADDRESS = (PROTOCOL = TCP)(HOST = mastertwo.test.com)(PORT = 1521))        (CONNECT_DATA =          (SERVER = DEDICATED)          (SERVICE_NAME = mastertwo.test.com)        )      )    MASTERONE =      (DESCRIPTION =        (ADDRESS = (PROTOCOL = TCP)(HOST = masterone.test.com)(PORT = 1521))        (CONNECT_DATA =          (SERVER = DEDICATED)          (SERVICE_NAME = masterone.test.com)        )      )    3，開啟SQL Plus測試連結（保證資料庫可聯通）    >connect [email protected];    >connect [email protected];4，建立replication admin ，使用system串連masterone.test.com資料庫；5，建立repadmin 執行命令     Create user repadmin identified by repadmin;6，賦予repadmin使用者可以建立和管理複製環境的許可權    Exec dbms_repcat_admin.grant_admin_any_schema(username=>‘repadmin‘);    Grant comment any table to repadmin;    Grant lock any table to repadmin;    如果使用OEM管理工具，還需執行如下命令：    GRANT SELECT ANY DICTIONARY TO repadmin;7，註冊repadmin為傳播函數    Exec dbms_defer_sys.register_propagator(username=>‘repadmin‘);8，註冊repadmin為接受函數        BEGIN           DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (              username => ‘repadmin‘,              privilege_type => ‘receiver‘,              list_of_gnames => NULL);        END;9，為了在檢查時保持延遲事務隊列的大小, 您應該清除已成功完成的延遲事務。SCHEDULE_PURGE 過程為您自動清除過程。使用repadmin登陸資料庫    Connect repadmin;        BEGIN       DBMS_DEFER_SYS.SCHEDULE_PURGE (          next_date => SYSDATE,          interval => ‘SYSDATE + 1/24‘,          delay_seconds => 0);    END;10，在mastertwo.mor.cr上執行相同操作4-9；11，使用system在masterone.test.com上建立共有dblink    Connect [email protected]    Create public datebase link mastertwo.test.com using ‘mastertwo‘;        Connect [email protected]    Create database link mastertwo.test.com connect to repadmin identified by repadmin;        使用system在mastertwo.test.com上建立共有dblink    Connect [email protected];    Create public database link masterone.test.com using ‘masterone‘;        Connect [email protected]    Create database link masterone.mor.cr connect to repadmin identified by repadmin;    12，使用repadmin登陸masterone.test.com/mastertwo.test.com    Connect [email protected]    BEGIN       DBMS_DEFER_SYS.SCHEDULE_PUSH (          destination => ‘mastertwo.test.com‘,          interval => ‘SYSDATE + (1/144)‘,          next_date => SYSDATE,          parallelism => 1,          execution_seconds => 1500,          delay_seconds => 1200); /*延遲時間可以設定小一點*/    END;        Connect [email protected]    BEGIN       DBMS_DEFER_SYS.SCHEDULE_PUSH (          destination => ‘masterone.test.com‘,          interval => ‘SYSDATE + (1/144)‘,          next_date => SYSDATE,          parallelism => 1,          execution_seconds => 1500,          delay_seconds => 1200);    END;    13，建立master group        在建立同步群組之前應該確保複製環境的中的每個資料庫中有相同的schema和表結構。例如在測試環境中定義的schema為hr，表結構為test        只在master site上執行此操作：masterone.test.com作為master site    Connect [email protected]        Exec dbms_repcat.create_master_repgroup(‘hrgroup‘)14，向master group中添加object        BEGIN       DBMS_REPCAT.CREATE_MASTER_REPOBJECT (          gname => ‘hrgroup‘,          type => ‘TABLE‘,          oname => ‘test‘,          sname => ‘hr‘,          use_existing_object => TRUE,          copy_rows => FALSE);    END;    15，添加額外的master site    BEGIN       DBMS_REPCAT.ADD_MASTER_DATABASE (          gname => ‘hrgroup‘,          master => ‘mastertwo.test.com‘,          use_existing_objects => TRUE,          copy_rows => FALSE,          propagation_mode => ‘ASYNCHRONOUS‘);    END;        使用如下命令檢查，如果出現兩個資料庫的串連則操作成功    SELECT DBLINK FROM DBA_REPSITES WHERE GNAME = ‘HRGROUP‘;    mastertwo.test.com    masterone.test.com    16，添加複製支援        BEGIN         DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (          sname => ‘hr‘,          oname => ‘test‘,           type => ‘TABLE‘,          min_communication => TRUE);     END;        完成檢查通過命令檢查，返回0可繼續執行    SELECT COUNT(*) FROM DBA_REPCATLOG WHERE GNAME = ‘HRGROUP‘;17，啟用複製    BEGIN        DBMS_REPCAT.RESUME_MASTER_ACTIVITY (          gname => ‘hrgroup‘);     END;18，測試    在masterone.test.com上hr.test表中分別通過進行增刪改操作        Insert into hr.test values (1,‘testuser1‘);        Commit;(在SQL Plus上操作別忘提交)    由於複寫延遲為1200，毫秒所以不會很快同步到mastertwo.test.com上

參考地址：https://docs.oracle.com/cd/B28359_01/server.111/b28327/rarmastergroup.htm#i1004613

oracle資料庫主主複製