# 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如果未开启CDB模式，查看11g权限命令，Oracle19如果开启CDB模式，其权限用户参考命令如下：
     ```
     sqlplus sys/password@//localhost:1521/ORCLCDB as sysdba
     CREATE USER migrationuser IDENTIFIED BY mgrationuserPWD DEFAULT TABLESPACE logminer_tbs QUOTA UNLIMITED ON logminer_tbs CONTAINER=ALL;
     GRANT CREATE SESSION TO migrationuser CONTAINER=ALL;
     GRANT SET CONTAINER TO migrationuser CONTAINER=ALL;
     GRANT SELECT ON V_$DATABASE to migrationuser CONTAINER=ALL;
     GRANT FLASHBACK ANY TABLE TO migrationuser CONTAINER=ALL;
     GRANT SELECT ANY TABLE TO migrationuser CONTAINER=ALL;
     GRANT SELECT_CATALOG_ROLE TO migrationuser CONTAINER=ALL;
     GRANT EXECUTE_CATALOG_ROLE TO migrationuser CONTAINER=ALL;
     GRANT SELECT ANY TRANSACTION TO migrationuser CONTAINER=ALL;
     GRANT ANALYZE ANY TO migrationuser CONTAINER=ALL;
     GRANT LOGMINING TO migrationuser CONTAINER=ALL;
     GRANT CREATE TABLE TO migrationuser CONTAINER=ALL;
     GRANT CREATE SEQUENCE TO migrationuser CONTAINER=ALL;
     GRANT EXECUTE ON DBMS_LOGMNR TO migrationuser CONTAINER=ALL;
     GRANT EXECUTE ON DBMS_LOGMNR_D TO migrationuser CONTAINER=ALL;
     GRANT SELECT ON V_$LOG TO migrationuser CONTAINER=ALL;
     GRANT SELECT ON V_$LOG_HISTORY TO migrationuser CONTAINER=ALL;
     GRANT SELECT ON V_$LOGMNR_LOGS TO migrationuser CONTAINER=ALL;
     GRANT SELECT ON V_$LOGMNR_CONTENTS TO migrationuser CONTAINER=ALL;
     GRANT SELECT ON V_$LOGMNR_PARAMETERS TO migrationuser CONTAINER=ALL;
     GRANT SELECT ON V_$LOGFILE TO migrationuser CONTAINER=ALL;
     GRANT SELECT ON V_$ARCHIVED_LOG TO migrationuser CONTAINER=ALL;
     GRANT SELECT ON V_$ARCHIVE_DEST_STATUS TO migrationuser CONTAINER=ALL;
     exit;
     ```
     
   
   - Oracle 11g权限用户参考命令：
     ```
     sqlplus sys/password@host:port/SID AS SYSDBA;
       CREATE USER migrationuser IDENTIFIED BY mgrationuserPDW DEFAULT TABLESPACE LOGMINER_TBS QUOTA UNLIMITED ON LOGMINER_TBS;
       GRANT CREATE SESSION TO migrationuser;
       GRANT SELECT ON V_$DATABASE to migrationuser;
       GRANT FLASHBACK ANY TABLE TO migrationuser;
       GRANT SELECT ANY TABLE TO migrationuser;
       GRANT SELECT_CATALOG_ROLE TO migrationuser;
       GRANT EXECUTE_CATALOG_ROLE TO migrationuser;
       GRANT SELECT ANY TRANSACTION TO migrationuser;
       GRANT ANALYZE ANY TO migrationuser;
       GRANT CREATE TABLE TO migrationuser;
       GRANT LOCK ANY TABLE TO migrationuser;
       GRANT ALTER ANY TABLE TO migrationuser;
       GRANT CREATE SEQUENCE TO migrationuser;
       GRANT EXECUTE ON DBMS_LOGMNR TO migrationuser;
       GRANT EXECUTE ON DBMS_LOGMNR_D TO migrationuser;
       GRANT SELECT ON V_$LOG TO migrationuser;
       GRANT SELECT ON V_$LOG_HISTORY TO migrationuser;
       GRANT SELECT ON V_$LOGMNR_LOGS TO migrationuser;
       GRANT SELECT ON V_$LOGMNR_CONTENTS TO migrationuser;
       GRANT SELECT ON V_$LOGMNR_PARAMETERS TO migrationuser;
       GRANT SELECT ON V_$LOGFILE TO migrationuser;
       GRANT SELECT ON V_$ARCHIVED_LOG TO migrationuser;
       GRANT SELECT ON V_$ARCHIVE_DEST_STATUS TO migrationuser;
       exit
     ```
     
   
   - 备库只读权限参考：
     ```
     sqlplus sys/password@host:port/SID AS SYSDBA;
       CREATE USER migrationuser IDENTIFIED BY mgrationuserPwd DEFAULT TABLESPACE LOGMINER_TBS QUOTA UNLIMITED ON LOGMINER_TBS;
       GRANT CREATE SESSION TO migrationuser;
       GRANT SET CONTAINER TO migrationuser;  // 仅在CDB模式下需要该权限
       GRANT SELECT ON V_$DATABASE to migrationuser;
       GRANT SELECT ANY TABLE TO migrationuser;
       GRANT SELECT_CATALOG_ROLE TO migrationuser;
       GRANT EXECUTE_CATALOG_ROLE TO migrationuser;  // 允许连接器将数据字典写入Oracle重做日志，这是跟踪schema变更所必需的。
       GRANT SELECT ANY TRANSACTION TO migrationuser;  
       GRANT LOGMINING TO migrationuser;   // 12c 及以上才需要该权限
       GRANT EXECUTE ON DBMS_LOGMNR TO migrationuser;  // 12c以上无需，10g和11g 需要该权限
       GRANT EXECUTE ON DBMS_LOGMNR_D TO migrationuser; // 12c以上无需，10g和11g 需要该权限
       // 以下为日志视图的查询权限
       GRANT SELECT ON V_$LOG TO migrationuser;
       GRANT SELECT ON V_$LOG_HISTORY TO migrationuser;
       GRANT SELECT ON V_$LOGMNR_LOGS TO migrationuser;
       GRANT SELECT ON V_$LOGMNR_CONTENTS TO migrationuser;
       GRANT SELECT ON V_$LOGMNR_PARAMETERS TO migrationuser;
       GRANT SELECT ON V_$LOGFILE TO migrationuser;
       GRANT SELECT ON V_$ARCHIVED_LOG TO migrationuser;
     ```