Oracle数据源如何开通归档日志、查询权限和日志解析权限？

问题描述

Oracle数据源默认权限无法正常执行实时处理集成作业。

原因分析

• Oracle库需要开启归档日志，归档日志建议至少保留三天。
• Oracle表没有表查询权限和日志解析权限。

解决方案

1. 开启归档日志。
   1. 以sysdba身份登录Oracle数据库。
   2. 执行SQL命令ARCHIVE LOG LIST查询当前数据库的归档状态，如下结果为未开启日志归档。

      Database log mode No Archive Mode #非存档模式
      Automatic archival Disabled
      Archive destination USE_DB_RECOVERY_FILE_DEST 
      Oldest online log sequence 1 
      Current log sequence 2

   3. 执行SQL命令SHUTDOWN IMMEDIATE关闭数据库。
   4. 执行SQL命令STARTUP MOUNT启动数据库至MOUNT状态。
   5. 执行SQL命令ALTER DATABASE ARCHIVELOG启动归档模式。
   6. 执行SQL命令ARCHIVE LOG LIST查询归档状态，如下结果为已经开启归档日志。

      Databaselogmode Archive Mode #存档模式
      Automatic archival Enabled 
      Archive destination USE_DB_RECOVERY_FILE_DEST 
      Oldest online log sequence 1
      Next log sequence to archive 2
      Currentlogsequence 2

   7. 执行SQL命令ALTER DATABASE OPEN启动数据库。
2. Oracle开启数据库和需要迁移的表的补充日志。
   1. 执行以下SQL开启数据库补充日志。

      ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; 

   2. 开启需要实时同步的表的补充日志。

      ALTER TABLE "schema_name"."table_name" ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

      设置成功后通过以下SQL可以查询到ALL_COLUMN_LOGGING说明该表已经开通补充日志。

      SELECT 'KEY', LOG_GROUP_TYPE FROM ALL_LOG_GROUPS WHERE OWNER = 'schema_name' AND TABLE_NAME = 'table_name';
      "KEY" LOG_GROUP_TYPE
      KEY ALL_COLUMN_LOGGING

3. 开通Oracle用户所需权限。
   • Oracle 19权限用户参考命令：

     sqlplus sys/password@//localhost:1521/ORCLCDB as sysdba
     CREATE USER mgrationuser IDENTIFIED BY mgrationuserPWD DEFAULT TABLESPACE logminer_tbs QUOTA UNLIMITED ON logminer_tbs CONTAINER=ALL;
     GRANT CREATE SESSION TO mgrationuser CONTAINER=ALL;
     GRANT SET CONTAINER TO mgrationuser CONTAINER=ALL;
     GRANT SELECT ON V_$DATABASE to mgrationuser CONTAINER=ALL;
     GRANT FLASHBACK ANY TABLE TO mrationuser CONTAINER=ALL;
     GRANT SELECT ANY TABLE TO mgrationuser CONTAINER=ALL;
     GRANT SELECT_CATALOG_ROLE TO mgrationuser CONTAINER=ALL;
     GRANT EXECUTE_CATALOG_ROLE TO mgrationuser CONTAINER=ALL;
     GRANT SELECT ANY TRANSACTION TO mgrationuser CONTAINER=ALL;
     GRANT LOGMINING TO mgrationuser CONTAINER=ALL;
     GRANT CREATE TABLE TO mgrationuser CONTAINER=ALL;
     -- Don’t need to execute this statement, If you set 'scan.incremental.snapshot.enabled=true' (default).
     GRANT LOCK ANY TABLE TO mgrationuser CONTAINER=ALL;
     GRANT CREATE SEQUENCE TO mgrationuser CONTAINER=ALL;
     GRANT EXECUTE ON DBMS_LOGMNR TO mgrationuser CONTAINER=ALL;
     GRANT EXECUTE ON DBMS_LOGMNR_D TO mgrationuser CONTAINER=ALL;
     GRANT SELECT ON V_$LOG TO mgrationuser CONTAINER=ALL;
     GRANT SELECT ON V_$LOG_HISTORY TO mgrationuser CONTAINER=ALL;
     GRANT SELECT ON V_$LOGMNR_LOGS TO mgrationuser CONTAINER=ALL;
     GRANT SELECT ON V_$LOGMNR_CONTENTS TO mgrationuser CONTAINER=ALL;
     GRANT SELECT ON V_$LOGMNR_PARAMETERS TO mgrationuser CONTAINER=ALL;
     GRANT SELECT ON V_$LOGFILE TO mgrationuser CONTAINER=ALL;
     GRANT SELECT ON V_$ARCHIVED_LOG TO mgrationuser CONTAINER=ALL;
     GRANT SELECT ON V_$ARCHIVE_DEST_STATUS TO mgrationuser CONTAINER=ALL;
     exit;

   • Oracle 11g权限用户参考命令：

     sqlplus sys/password@host:port/SID AS SYSDBA;
       CREATE USER mgrationuser IDENTIFIED BY mgrationuserPDW DEFAULT TABLESPACE LOGMINER_TBS QUOTA UNLIMITED ON LOGMINER_TBS;
       GRANT CREATE SESSION TO mgrationuser;
       GRANT SELECT ON V_$DATABASE to mgrationuser;
       GRANT FLASHBACK ANY TABLE TO mgrationuser;
       GRANT SELECT ANY TABLE TO mgrationuser;
       GRANT SELECT_CATALOG_ROLE TO mgrationuser;
       GRANT EXECUTE_CATALOG_ROLE TO mgrationuser;
       GRANT SELECT ANY TRANSACTION TO mgrationuser;
       GRANT CREATE TABLE TO mgrationuser;
       GRANT LOCK ANY TABLE TO mgrationuser;
       GRANT ALTER ANY TABLE TO mgrationuser;
       GRANT CREATE SEQUENCE TO mgrationuser;
       GRANT EXECUTE ON DBMS_LOGMNR TO mgrationuser;
       GRANT EXECUTE ON DBMS_LOGMNR_D TO mgrationuser;
       GRANT SELECT ON V_$LOG TO mgrationuser;
       GRANT SELECT ON V_$LOG_HISTORY TO mgrationuser;
       GRANT SELECT ON V_$LOGMNR_LOGS TO mgrationuser;
       GRANT SELECT ON V_$LOGMNR_CONTENTS TO mgrationuser;
       GRANT SELECT ON V_$LOGMNR_PARAMETERS TO mgrationuser;
       GRANT SELECT ON V_$LOGFILE TO mgrationuser;
       GRANT SELECT ON V_$ARCHIVED_LOG TO mgrationuser;
       GRANT SELECT ON V_$ARCHIVE_DEST_STATUS TO mgrationuser;
       exit