GoldenGate 对asm磁盘的访问的两种方式:asm实例连接和API(dblogreader)两种方式的对比

背景说明

GoldenGate在抽取数据的时候要读取online redo里的数据,online redo很多时候是储存在asm磁盘里,对asm磁盘的访问有两种方式:

  1. 是在tnsnames.ora文件中增加访问asm实例的链接描述,并在Extract进程参数文件中添加访问asm实例的链接信息
  2. 是使用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磁盘和本地文件系统,都测试正常,数据能正常同步!

姚远ACE CSDN认证博客专家 ACE 华为云 MVP
Oracle ACE,华为云 MVP,Oracle10g,12c OCM; MySQL 5.6,5.7,8.0 OCP;CCNA; EMC Certified; IBM P Certified; RHCE; SQLServer 764; DB2 Certified; TOEIC 890;获得过两次国家部级科技进步奖;发明过两项计算机专利。微信:yaoyuanace 邮箱:yaoyuanace(at)qq.com
已标记关键词 清除标记
©️2020 CSDN 皮肤主题: Age of Ai 设计师:meimeiellie 返回首页