AttunityReplicate连接Oracle数据库配置

By on April 1, 2020

AttunityReplicate连接Oracle数据库配置

更新日期：2019-11-25

1. 数据库准备

1.1 确保ORACLE归档日志打开

ALTER database ARCHIVELOG

1.2 确保打开Supplemental Log

a. 检查,执行如下sql,返回结果是YES或者IMPLICIT，即意味着已经打开

SELECT supplemental_log_data_min FROM v$database;

b. 如果a没有返回预期结果，可以通过如下命令打开supplemental log

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA

2. 用户权限

2.1 创建用户

drop user aruser;
create user aruser identified by aruser ;
grant create session to aruser;
grant connect to aruser;

2.2. 基本授权

grant select any directory  to ARUSER; 
grant SELECT ANY TRANSACTION to aruser;
grant SELECT on V_$ARCHIVED_LOG to aruser;
grant SELECT on V_$LOG to aruser;
grant SELECT on V_$LOGFILE to aruser;
grant SELECT on V_$DATABASE to aruser;
grant SELECT on V_$THREAD to aruser;
grant SELECT on V_$PARAMETER to aruser;
grant SELECT on V_$NLS_PARAMETERS to aruser;
grant SELECT on V_$TIMEZONE_NAMES to aruser;
grant SELECT on V_$TRANSACTION to aruser;
grant SELECT on ALL_INDEXES to aruser;
grant SELECT on ALL_OBJECTS to aruser;
grant SELECT on DBA_OBJECTS to aruser;
grant SELECT on ALL_TABLES to aruser;
grant SELECT on ALL_USERS to aruser;
grant SELECT on ALL_CATALOG to aruser;
grant SELECT on ALL_CONSTRAINTS to aruser;
grant SELECT on ALL_CONS_COLUMNS to aruser;
grant SELECT on ALL_TAB_COLS to aruser;
grant SELECT on ALL_IND_COLUMNS to aruser;
grant SELECT on ALL_LOG_GROUPS to aruser;
grant SELECT on SYS.DBA_REGISTRY to aruser;
grant SELECT on SYS.OBJ$ to aruser;
grant SELECT on SYS.ENC$ to aruser;
grant SELECT on DBA_TABLESPACES to aruser;
grant SELECT on ALL_TAB_PARTITIONS to aruser;
grant SELECT on ALL_ENCRYPTED_COLUMNS to aruser;
--If views are expose
grant SELECT on ALL_VIEWS to aruser;
-- This is for pattern selection
grant SELECT ANY TABLE to aruser;
-- This is used when automaticall add supplemental log.
grant ALTER ANY TABLE to aruser;
--or grant will use replicate tables 
--grant ALTER on <any-replicated-table> to aruser;

2.3 使用logminer读取日志需要的权限

-- For Logminer
grant EXECUTE on DBMS_LOGMNR to aruser;
grant SELECT on V_$LOGMNR_LOGS to aruser;
grant SELECT on V_$LOGMNR_CONTENTS to aruser;
-- For 12 c
grant logmining to aruser;
-- For Oracle 12c PDB
grant SELECT on v$containers to aruser;

2.4 使用bfile读取日志需要的权限

如果是oracle standby数据库，必须使用bfile模式

--For Binary Reader
grant SELECT on v_$transportable_platform to aruser;
---Customer can predefine directory, then no need to grant this privilege
grant CREATE ANY DIRECTORY to aruser;
GRANT SELECT ON dba_directories TO ARUSER;
GRANT SELECT ON all_directories TO ARUSER;
--without this privilege v$standby_log ,AR reports V$thread can't be accesed
grant select on v$standby_log to ARUSER ;
--For ASM REDO log copy
GRANT EXECUTE ON DBMS_FILE_TRANSFER TO aruser;
grant EXECUTE on DBMS_FILE_GROUP to aruser;

3. 特殊情况

3.1 bfile模式读取Standby数据库特殊操作

因为Standby数据库是只读的，AR无法在standby数据库创建directory用于指向备机的archive和redo日志，所以需要在主库上创建两个directory,指向standby数据库的归档和redo日志的路径，再由dg将directory定义复制到standby库。

  1. 需要在主库的orcle上创建两个directory,分别指向备机的redolog和archivelog的路径，注意路径最后不要带”/”
  2. 例： 
    create or replace directory "OTC_ARCHIVE" as '/data/oracle/app/oracle/flash_recovery_area/OTC/archivelog
  3. directory的名字，XX_ARCHIVE、XX_AONLINE的名字，不能以ATTUREP_开头
  4. directory对于上面创建的ARUSER用户，必须有读取权限
  5. 关于XX_ARCHIVE、XX_AONLINE指向的路径，可以参考 
    select * from v$archive_log;  --XX_ARCHIVE
select * from v$logfile;  --XX_AONLINE

3.2 archivelog存在在ASM中

From Oracle 11g Release 2 (11.2.0.2), Attunity Replicate must be granted the SYSASM privilege in order to access the ASM account. For older supported versions, granting Attunity Replicate the SYSDBA privilege should be sufficient.

When connecting to ASM, Attunity Replicate will first try to log in as SYSDBA and, if unsuccessful, will try to log in as SYSASM.

You can validate ASM account access by opening a command prompt and issuing the following statements:

sqlplus asmuser/asmpassword@+asmserver as sysdba
-OR-
sqlplus asmuser/asmpassword@+asmserver as sysasm

为什么需要sysasm或syadba 访问asm的权限只有三种 https://docs.oracle.com/en/database/oracle/oracle-database/18/ostmg/authenticate-access-asm-instance.html#GUID-1A4E79BE-7D26-4FAC-BC11-B6C2F677A41F

An Oracle ASM instance does not have a data dictionary, so the only way to connect to an Oracle ASM instance is by using one of three system privileges, SYSASM, SYSDBA, or SYSOPER. There are three modes of connecting to Oracle ASM instances:

https://docs.oracle.com/en/database/oracle/oracle-database/18/ostmg/asm-access-control-diskgroups.html#GUID-59D1AD06-DDE9-4037-B293-CCD35BB300CB 中章节Using SQL Statements to Manage Oracle ASM File Access Control ar需要从asm中进行文件访问，就必须sysasm or sysdba ar使用dbms_diskgroup访问asm

Using SQL Statements to Manage Oracle ASM File Access Control

3.3 复制lob字段

table必须有pk或唯一索引

3.4 强制设置字符集

  1. AR会自动探测源和目标的字符集，并在数据抽取到AR时进行转换，一般情况下无需特殊设置
  2. 如果特殊情况下，需要手工指定字符集（例如再一个西文的数据库中，插入了中文字符）
  3. 步骤如下：
  4. 首先使用plsql查询数据表，并设置plsql所在windows环境的环境变量，NLS_LANG=某某字符集， 例如：AMERICAN_AMERICA.ZHS16GBK
  5. 确保使用1中对应的设置可以在PLSQL中看到中文
  6. 在AR server上设置同样的NLS_LANG
  7. 在AR 数据源或者目标中，设置内部参数CHARACTERSET，值和步骤1中的值一致

3.5 logminer读取时，oracle trace log问题

当使用logminer接口读取日志时，oracle自己会在trace日志中记录每次访问情况，造成日志变大，建议定期清理

Back to blog