背景说明
GoldenGate在抽取数据的时候要读取online redo里的数据,online redo很多时候是储存在asm磁盘里,对asm磁盘的访问有两种方式:
是在tnsnames.ora文件中增加访问asm实例的链接描述,并在Extract进程参数文件中添加访问asm实例的链接信息
是使用asm的api接口(DBLOGREADER ),只能支持oracle 11r2以上的版本
测试结果
online redo只在asm磁盘 online redo同时在asm磁盘和本地文件系统
asm实例的链接 支持 支持
asm的api接口(DBLOGREADER) 支持 支持
配置asm磁盘和本地文件系统混合的online redo
online redo只在asm磁盘
set linesize 200 select * from v$log; col member form a50 select * from v$logfile; SQL> set linesize 200 select * from v$log; col member form a50 select * from v$logfile;SQL> GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIV STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME CON_ID ---------- ---------- ---------- ---------- ---------- ---------- ------ -------------------------------- ------------- ------------------- ------------ ------------------- ---------- 1 1 7 209715200 512 2 YES INACTIVE 1970481 2020-10-14 19:14:07 2059245 2020-10-14 23:00:20 0 2 1 8 209715200 512 2 YES INACTIVE 2059245 2020-10-14 23:00:20 2313931 2020-10-15 16:46:42 0 3 1 9 209715200 512 2 NO CURRENT 2313931 2020-10-15 16:46:42 9.2954E+18 0 SQL> SQL> GROUP# STATUS TYPE MEMBER IS_REC CON_ID ---------- -------------- -------------- -------------------------------------------------- ------ ---------- 3 ONLINE +DATA/DBOGG/ONLINELOG/group_3.292.1053792505 NO 0 3 ONLINE +DATA/DBOGG/ONLINELOG/group_3.294.1053792507 YES 0 2 ONLINE +DATA/DBOGG/ONLINELOG/group_2.291.1053792505 NO 0 2 ONLINE +DATA/DBOGG/ONLINELOG/group_2.295.1053792507 YES 0 1 ONLINE +DATA/DBOGG/ONLINELOG/group_1.290.1053792505 NO 0 1 ONLINE +DATA/DBOGG/ONLINELOG/group_1.293.1053792507 YES 0 6 rows selected.
online redo同时在asm磁盘和本地文件系统
alter database add logfile member '/u01/app/oracle/redo12.log' to group 1; alter database add logfile member '/u01/app/oracle/redo22.log' to group 2; alter database add logfile member '/u01/app/oracle/redo32.log' to group 3; GROUP# STATUS TYPE MEMBER IS_REC CON_ID ---------- -------------- -------------- -------------------------------------------------- ------ ---------- 3 ONLINE +DATA/DBOGG/ONLINELOG/group_3.292.1053792505 NO 0 3 ONLINE +DATA/DBOGG/ONLINELOG/group_3.294.1053792507 YES 0 2 ONLINE +DATA/DBOGG/ONLINELOG/group_2.291.1053792505 NO 0 2 ONLINE +DATA/DBOGG/ONLINELOG/group_2.295.1053792507 YES 0 1 ONLINE +DATA/DBOGG/ONLINELOG/group_1.290.1053792505 NO 0 1 ONLINE +DATA/DBOGG/ONLINELOG/group_1.293.1053792507 YES 0 1 ONLINE /u01/app/oracle/redo12.log NO 0 2 ONLINE /u01/app/oracle/redo22.log NO 0 3 ONLINE /u01/app/oracle/redo32.log NO 0 alter database drop logfile member '/u01/app/oracle/redo12.log' ; alter database drop logfile member '/u01/app/oracle/redo22.log' ; alter database drop logfile member '/u01/app/oracle/redo32.log';
抽取进程没有配置到asm磁盘的链接和api接口时
日志报错
2020-10-15T17:45:24.060+0800 ERROR OGG-02828 Oracle GoldenGate Capture for Oracle, ext1.prm: Not able to establish initial position for sequence 15, rba 294,928, No valid log files for current redo sequence 15, thread 1, error retrieving redo file name for sequence 15, archived = 0, use_alternate = 0. 2020-10-15T17:45:24.070+0800 ERROR OGG-01668 Oracle GoldenGate Capture for Oracle, ext1.prm: PROCESS ABENDING. 2020-10-15T17:46:43.081+0800 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): info all.
抽取进程终止!
GGSCI (oledb.scutech) 6> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT STOPPED EXT1 00:00:00 00:01:43 EXTRACT RUNNING PUMP1 00:00:00 00:00:08
抽取进程配置 asm实例的链接测试
修改文件/u01/app/oracle/product/19.0.0/db_1/network/admin/tnsnames.ora
增加:
ASM = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oledb.scutech)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = +asm) ) )
测试 sqlplus sys/dingjia@asm as sysasm
抽取进程配置如下参数:
GGSCI (oledb.scutech) 10> view param ext1 extract ext1 SETENV(ORACLE_SID='dbogg') userid ogg,password dingjia exttrail /u01/app/oracle/ogg19/dirdat/et table scutech.*; TranlogOptions ASMUser sys@ASM, ASMPassword dingjia
online redo只在asm磁盘时测试正常
GSCI (oledb.scutech) 8> start ext1 Sending START request to MANAGER ... EXTRACT EXT1 starting GGSCI (oledb.scutech) 9> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING EXT1 00:00:00 00:00:07 EXTRACT RUNNING PUMP1 00:00:00 00:00:05
online redo同时在asm磁盘和本地文件系统,测试正常,数据能正常同步!
抽取进程配置asm的api接口(DBLOGREADER)
抽取进程配置如下参数:
GGSCI (oledb.scutech) 6> view param ext1 extract ext1 SETENV(ORACLE_SID='dbogg') userid ogg,password dingjia exttrail /u01/app/oracle/ogg19/dirdat/et table scutech.*; TranlogOptions DBLOGREADER
online redo只在asm磁盘时和同时在asm磁盘和本地文件系统,都测试正常,数据能正常同步!