Oracle环境准备(LogMiner)

1.1 版本

CDC（Change Data Capture），即資料變更抓取，透過為源端資料源開啟 CDC，FineDataLink 資料管道可實現資料源的實時資料同步以及資料表的物理刪除同步。

FineDataLink 資料管道支援 Oracle 的 XStream 和 LogMiner 兩種 CDC 模式，本章節主要介紹如何為 Oracle 資料庫開啟 LogMiner 模式的CDC功能。另外，根據Oracle資料庫類型為CDB資料庫還是非 CDB 資料庫，CDC 的配置有所不同。

執行同步任務前，需要參考本文在資料源中進行一些配置，為後續的資料同步做好準備。

注：歸檔日誌會佔用較多的磁碟空間，若磁碟空間滿了會影響業務，請定期清理過期歸檔日誌，詳情參見：清理Oracle归档日志

3.1 開啟 Archive Log 

1）以具有 DBA 權限的使用者身分登入資料庫

2）使用命令查看資料庫的 logging mode ：

select log_mode from v$database;

如果傳回的結果是 ARCHIVELOG , 可以直接操作 3.3 節進行操作。

如果傳回的結果是 NOARCHIVELOG , 繼續按照以下步驟操作:

• 登入服務端：sqlplus / as sysdba

• 關閉資料庫: 使用命令：shutdown immediate;

• 啟動並掛載資料庫: startup mount;

• 開啟存檔並開啟資料庫：

alter database archivelog;
alter database open;

3.2 開啟 Supplemental Log

必須為擷取的表或資料庫啟動補充日誌記錄，以便資料更改能夠擷取已更改的資料庫行之前的狀態。下面將說明如何在表/資料庫級別上配置它。 一般建議對庫進行啟動。

3.2.1 Oracle 10g、11g版本開啟方式

使用者根據實際情況，選擇其中一種方案：

方案	說明	步驟
開啟單表補全日誌	同步某張表時，就需要開啟該表的補全日志 優勢：操作比較精準 開啟單表的全補充得在運作管道任務前，不然有可能會出現資料丟失 劣勢：需要一張一張開啟	ALTER TABLE <schema name>.<table name> ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
開啟全庫補全日誌	若使用者庫裏的表資料量可控，且希望一勞永逸（不想每同步一張表就開啟一次），可開啟全庫補全日志 劣勢：會佔用較多資料庫空間	alter database add supplemental log data; alter system switch logfile; ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

3.2.2 Oracle 12c、18c、19c、RAC叢集版本的特殊配置方法

使用以下命令，確認 supplemental logging 是否開啟：

SELECT supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_all FROM v$database;

如果傳回的三列都是 Yes 或者 Implicit ，則表示 identification key logging(標識鍵日誌)和 full supplemental logging（全補充日誌）已同時開啟，可以直接查看 3.4 節資料庫帳號相關內容。

如果傳回的前兩列是 Yes 或者 Implicit ，則表示只開啟了 identification key logging（標識鍵日誌），需要參考 3.3 節開啟全補充日誌。

3.2.3 Oracle 12c、18c、19c、RAC叢集的 CDB 開啟方式

為單個表開啟 identification key logging（標識鍵日誌）：

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;    
ALTER TABLE <schema name>.<table name> ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

對所有表開啟 identification key logging（標識鍵日誌）：

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

3.3 開啟 full supplemental logging（全補充日誌）

提示:僅 3.2.2 節中滿足返回的前兩列是 Yes 或者 Implicit ，才需要開啓。

提示: 僅 3.2.2 節中滿足傳回的前兩列是 Yes 或者 Implicit ，才需要開啟。

單表開啟 full supplemental logging（全補充日誌），使用如下命令：

ALTER TABLE <schema name>.<table name> ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

所有表開啟 full supplemental logging（全補充日誌），使用如下命令：

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

提交更改的配置：

ALTER SYSTEM SWITCH LOGFILE;

然後可以使用命令查看單表的補全日誌權限是否開啟：

SELECT OWNER, TABLE_NAME, LOG_GROUP_NAME, ALWAYS, LOGGING_COLUMNS
FROM DBA_LOG_GROUPS
WHERE TABLE_NAME = '你的表名';

3.4 分配資料庫帳號權限

需要規劃一個可存取資料源的帳號，用於後續資料管道程式中存取資料源並進行資料提取、寫入的同步操作。

建立帳號操作詳情請參見: 建立 Oracle 帳號

3.4.1 Oracle 10g、11g版本

使用如下命令建立使用者帳戶並分配權限：

CREATE USER <user name> IDENTIFIED BY <password>;
GRANT create session, alter session, execute_catalog_role, select any dictionary, select any transaction, select any table, unlimited tablespace to <user name>;

此時即可在進行資料連結時使用該使用者帳號，例如上述使用的是Roxy，則資料連結如下圖所示：

3.4.2 Oracle 12c、18c、19c standard databases 版本

CREATE USER <user name> IDENTIFIED BY <password>;
GRANT create session, alter session, select any dictionary, select any transaction,select any table,logmining, execute_catalog_role TO <username>;

WITH required_sys_privileges AS (
    SELECT column_value AS PRIVILEGE
    FROM TABLE (
        sys.odcivarchar2list(
                'SELECT ANY TRANSACTION',
                'CREATE SESSION',
                'SELECT ANY DICTIONARY',
                'SELECT ANY TABLE',
                'FLASHBACK ANY TABLE',
                'LOGMINING'
            )
        )
),
     required_role_privileges AS (
         SELECT column_value AS ROLE_NAME
         FROM TABLE (
             sys.odcivarchar2list(
                     'EXECUTE_CATALOG_ROLE',
                     'RESOURCE'
                 )
             )
     ),
     required_table_privileges AS (
         SELECT column_value AS PRIVILEGE
         FROM TABLE (
             sys.odcivarchar2list(
                     'DBMS_FLASHBACK',
                     'DBMS_LOGMNR',
                     'DBMS_LOGMNR_D'
                 )
             )
     )
SELECT r.PRIVILEGE,
       NVL2(p.USERNAME, 'OK', 'REQUIRED') AS PRIVILEGE_STATUS
FROM required_sys_privileges r
         LEFT JOIN user_sys_privs p
                   ON r.PRIVILEGE = p.PRIVILEGE
UNION
SELECT r.ROLE_NAME,
       NVL2(p.USERNAME, 'OK', 'REQUIRED') AS PRIVILEGE_STATUS
FROM required_role_privileges r
         LEFT JOIN user_role_privs p
                   ON r.ROLE_NAME = p.GRANTED_ROLE
UNION
SELECT r.PRIVILEGE,
       NVL2(p.GRANTEE, 'OK', 'REQUIRED') AS PRIVILEGE_STATUS
FROM required_table_privileges r
         LEFT JOIN user_tab_privs p
                   ON r.PRIVILEGE = p.TABLE_NAME;
显示代码

如果資料來源為 PDB ，則無法直接讀取資料庫的 redolog ，需要藉助 CDB 來讀取

提示:只有資料庫支援資料庫容器, 並且 FineDataLink 要同步的是 PDB 庫,  才需要本節的步驟。

注：4.1、4.2節是為了判斷是否支援資料庫容器，若不支援資料庫容器，參考本文第三章內容。

4.1 查詢該資料庫是否開啟多租戶

1）以具有 DBA 權限的使用者身分登入資料庫，使用如下命令查看資料庫是否開啟多租戶：

SELECT CDB FROM V$DATABASE;

4.2 判斷當前是否為根容器下

SELECT SYS_CONTEXT('USERENV', 'CON_NAME') FROM DUAL;

4.3 開啟資料庫權限

參考本文 3.1、3.2、3.3 節開啟資料庫日誌權限。

4.4 判斷連結使用者是否為 Common User 以及是否擁有 Set Container 權限

4.4.1 啟動所有的 PDB

在 sqlplus 下使用命令查看當前 PDB 狀態，如下圖所示：

show pdbs;

或者也可以在資料庫查詢介面使用命令

SELECT name, open_mode
FROM   v$pdbs
ORDER BY name;

2）此時可以看出 PDBORCL 和 ORCLPDB 均未啟動，此時需要將其開啟。

使用如下命令：

注： PDB 名稱需要根據資料庫實際情況修改。

ALTER PLUGGABLE DATABASE ORCLPDB1 OPEN;
ALTER PLUGGABLE DATABASE PDBORCL OPEN;

然後再次查看PDB 狀態，PDB 均開啟：

4.4.2 建立帳號

注：在 CDB 中建立公共使用者， PDBS 中也會建立相同使用者。若 CDB 下 GRANT 命令賦權時未指定container=all，則賦權只在 CDB 中生效，並不會在 PDB 中生效，這個使用者要能夠存取PDB，需要切換到 PDB 再賦權。若賦權時指定 container=all，則賦權在 CDB 中生效，也在 PDB 中生效。

1）建立使用者帳號

CREATE USER "C##ROXY122" IDENTIFIED BY 123 CONTAINER=all; 

注1：帳號和密碼此處分別為 C##ROXY122 和 123，使用者可根據實際情況修改。

注2：當 Oracle 處於多租戶模式下時，帳號需增加 C## 前綴。

2）授予權限

GRANT create session, alter session, select any dictionary, select any transaction,select any table,logmining, execute_catalog_role,set container TO "C##ROXY122" container=all;

此處僅為新增帳號的授權操作，資料庫權限需要參考本文 4.3 節開啟：

ALTER USER "C##ROXY122" QUOTA UNLIMITED ON "USERS";
SELECT supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_all FROM v$database;
GRANT create session, alter session, select any dictionary, select any transaction,select any table,logmining, execute_catalog_role TO "C##ROXY122";

最後授予使用者 V_$PDBS 的權限，如下圖所示：

alter user "C##ROXY122" set container_data=all for sys.v_$pdbs container = current; 

此時再查詢這個新增帳號的權限：

SELECT USERNAME, OBJECT_NAME FROM CDB_CONTAINER_DATA WHERE USERNAME = 'C##ROXY122' AND OBJECT_NAME = 'V_$PDBS';

可以查到對應的 v$pdbs 權限，如下圖所示：

遠端 Oracle 所在伺服器並切換到 sqlplus 模式下，執行賦予權限命令：

grant restricted session TO "C##ROXY122" container=all;

然後執行命令，賦予建表權限：

GRANT CREATE TABLE TO <username> CONTAINER=ALL;

執行後查詢是否還有建表權限：

SELECT PRIVILEGE FROM USER_SYS_PRIVS WHERE PRIVILEGE = 'CREATE TABLE';

此時可以正常建表。

5. 後續步驟