Oracle 11g OGG单实例RAC同步
Oracle 11g OGG单实例/RAC同步
2021年8月4日
15:14
测试环境
| ip | 数据库版本 | SID |
源端 | 10.32.50.88 | 11g | orcl |
目标端 | 10.32.68.112 | 11g | orcl |
同步两端的SID可以不一致
RAC中的ASM及TNS配置环境
注意：RAC所有节点都要配置(grid) cat listener.ora LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))# line added by Agent LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1))))# line added by Agent ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON# line added by Agent ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON# line added by Agent
SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = +ASM) (ORACLE_HOME=/u01/gridsoft/11.2.0) (SID_NAME = +ASM1) ) )
--reload监控，使配置生效(grid) lsnrctl reload
--测试连接是否正常(oracle) sqlplus sys/oracle@192.168.1.56:1521/+ASM as sysdba
配置TNS, 使用每个节点本地IP地址(oracle) --节点1 cd $ORACLE_HOME/network/admin cat tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools.
RAC = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.56)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.58)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) )
+ASM = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.56)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = +ASM) ) )
--节点2 注意：要用oracle用户去配置 [oracle@rac2 admin]$ cd /u01/app/oracle/product/11.2.0/db_1/network/admin [oracle@rac2 admin]$ cat tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools.
RAC = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.56)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.58)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) )
+ASM = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.58)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = +ASM) ) ) |
正式部署OGG环境, 如果是RAC环境需要配置上面信息
1, 开启归档（源端, 存在RAC配置信息）
修改归档路径参数 alter system set log_archive_dest_1='location=+DATA/orcl/archivelog' scope=spfile sid='*';
关闭数据库，全部节点都要关闭 srvctl stop database -d orcl
节点2 数据库启动到mount状态 srvctl start instance -d orcl -i orcl2 -o mount
修改归档并启动数据库 alter database archivelog; alter database open;
查看归档信息 archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination +DATA/orcl/archivelog Oldest online log sequence 3 Next log sequence to archive 4 Current log sequence 4
启动节点1 alter database open; |
源端/目标端
2, 安装ogg软件，安装方式如下（两端均执行）
oracle用户下添加OGG环境变量 export OGG_HOME=/oracle/ogg/install export INVENTORY_LOCATION=/oracle/oraInventory export PATH=$OGG_HOME:$PATH export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$OGG_HOME:/lib:/usr/lib
oracle用户下创建ogg目录 mkdir -p /oracle/ogg/install/ mkdir -p /oracle/archivelog/ chown -R oracle:oinstall /oracle |
如果存在RAC环境将使用共享磁盘共享的裸设备有多种方法
使用共享磁盘划分一个10G共享裸设备 fdisk /dev/sde
格式化磁盘区 mkfs -t ext3 /dev/sde1
授权及挂载 chown -R oracle:oinstall /oracle mount /dev/sde1 /oracle vi /etc/fstab /dev/sde1 /goldengate ext3 defaults 1 2 |
采用在现有ASM磁盘上创建一个ASM卷并挂载(grid) sqlplus / as sysasm alter diskgroup DATA add volume acfsload size 1G; ll /dev/asm* brwxrwx--- 1 root dba 252, 107521 May 19 15:44 acfsload-210
创建acfs挂载目录，每个节点都要创建相同目录(root) mkdir -p /u01/app/acfsmounts/acfsload-210
用mkfs创建文件系统(root) mkfs -t acfs -n acfs01 /dev/asm/acfsload-210 shell-init: error retrieving current directory: getcwd: cannot access parent directories: No such file or directory mkfs.acfs: version = 11.2.0.4.0 mkfs.acfs: on-disk version = 39.0 mkfs.acfs: volume = /dev/asm/acfsload-210 mkfs.acfs: volume size = 1073741824 mkfs.acfs: Format complete.
用acfsuit命令注册文件系统(root) acfsutil registry -a -f /dev/asm/acfsload-210 /u01/app/acfsmounts/acfsload-210 acfsutil registry: mount point /u01/app/acfsmounts/acfsload-210 successfully added to Oracle Registry
挂在目录及授权(root) mount.acfs -o all chown oracle:oinstall /u01/app/acfsmounts/acfsload-210 |
解压OGG安装包（两端均执行）
unzip 191004_fbo_ggs_Linux_x64_shiphome.zip cd fbo_ggs_Linux_x64_shiphome/Disk1 ls install response runInstaller stage ./runInstaller |
3, 创建数据库ogg用户（两端均执行）
create tablespace ogg datafile '/u01/app/oracle/oradata/orcl/ogg.dbf' size 1g autoextend on; create tablespace test datafile '/u01/app/oracle/oradata/orcl/data.dbf' size 1g autoextend on; CREATE USER ogg IDENTIFIED BY ogg DEFAULT TABLESPACE ogg; 授权ogg用户，如果对用户权限要求不严格可以直接授予dba权限，否则授予如下权限 grant connect,resource,dba to ogg;
创建数据库测试数据用户 create user test identified by test default tablespace test; grant connect,resource to test; |
4, 数据库环境准备(源端)
打开最小附加日志 alter database add supplemental log data; alter database force logging; select NAME,OPEN_MODE,FORCE_LOGGING,SUPPLEMENTAL_LOG_DATA_MIN from v$database;
如果要让 GoldenGate 支持DDL操作，还需要额外运行几个脚本，这些脚本是OGG带的而不是 Oracle 带的，在 GoldenGate 的安装目录都可以找到，源端与目标端都需要运行，如下： @marker_setup.sql --该脚本用于创建DDL的marker表GGS_MARKER，用于存储DDL信息，该表只进行insert操作 @ddl_setup.sql --该脚本创建了进行DDL复制抽取和复制所需的对象 @role_setup.sql --创建DDL复制所需的对象 @ddl_enable.sql -- 启用DDL复制，实质上是创建触发器，用以想MARKER和HISTORY表插入DDL信息 |
5, 主备库创建mgr管理进程，添加端口7809（两端均执行）
ggsci 创建ogg子目录 create subdirs
默认mgr进程可以添加如下参数： view params mgr PORT 7809 (指定服务监听端口；默认端口为7809) DYNAMICPORTLIST 7820-7830, 7833, 7835 (动态端口：可以制定最大256个可用端口的动态列表，当指定的端口不可用时，管理进程将会从列表中选择一个可用的端口，源端和目标段的Collector、Replicat、GGSCI进程通信也会使用这些端口) autostart er * (本处设置表示每2分钟尝试重新启动所有EXTRACT进程；以后5分钟清零。) autorestart er *, retries 5, waitminutes 3 （自动启动所有参数，会尝试5次，每次等待3分钟） purgeoldextracts ./dirdat/*,usecheckpoints, minkeepdays 7 (自动清理过期的trail文件，会保留7天)
启动mgr进程 start mgr |
6, 添加表级附加日志（两端均执行）
dblogin userid ogg, password ogg add trandata test.* |
7, 添加抽取进程（源端）
exta为进程名，一般为ext开头表示是extract进程，后面可以加1-2位字符标识；begin可以是now也可以是指定的时间 add ext exta, tranlog, begin now
为exta进程配置本地队列，本地的trail文件以la开头 add exttrail ./dirdat/lc, ext exta, MEGABYTES 20
添加投递进程pmpa，将本地的./dirdat/la文件传送到目标端 add extract pmpa, EXTTRAILSOURCE ./dirdat/lc
为pmpa进程配置远程队列，队列的位置是源端的主机目录,传送到目标断后命名以rc开头。 add rmttrail ./dirdat/rc, ext dpea, MEGABYTES 20 add RMTTRAIL ./dirdat/rc, EXTRACT PMPA |
8, 配置进程参数（源端）
view params EXTA EXTRACT exta setenv ( NLS_LANG = "AMERICAN_AMERICA.ZHS16GBK" ) setenv (ORACLE_SID = orcl) --SETENV (ORACLE_HOME = "/u01/app/oracle/product/db11g") USERID ogg, PASSWORD ogg --TRANLOGOPTIONS ASMUSER sys@+ASM,ASMPASSWORD oracle (RAC中ASM配置) THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 20000 EXTTRAIL ./dirdat/lc DYNAMICRESOLUTION DDL INCLUDE ALL (DDL复制参数) DDLOPTIONS ADDTRANDATA,REPORT (DDL复制参数) table test.*;
附加参数, 可忽略 --GETUPDATEBEFORES --DDL INCLUDE ALL (DDL复制参数) --DDLOPTIONS ADDTRANDATA NOCROSSRENAME REPORT (ddl复制参数) --NOCOMPRESSDELETES 启动进程 start EXTA
view params PMPA extract pmpa setenv ( NLS_LANG = AMERICAN_AMERICA.ZHS16GBK ) rmthost 10.32.68.112, mgrport 7809, compress PASSTHRU --REPORT AT 01:59 --reportrollover at 02:00 RMTTRAIL ./dirdat/rc DYNAMICRESOLUTION table test.*;
启动进程 start PMPA |
9, 配置目标端进程和参数（目标端）
add rep rep1a, exttrail ./dirdat/rc, nodbcheckpoint view params REP1A replicat rep1a setenv ( NLS_LANG = AMERICAN_AMERICA.ZHS16GBK ) setenv (ORACLE_SID = orcl) userid ogg, password ogg reperror default,abend discardfile ./dirrpt/rep1a.dsc,append, megabytes 10 DDL INCLUDE MAPPED (DDL复制参数) DDLOPTIONS REPORT (DDL复制参数) map test.*, target test.*;
附加参数, 可忽略 --assumetargetdefs --allownoopupdates --dynamicresolution --DDL include all --ddloptions report --DDLERROR DEFAULT DISCARD --DDLERROR DEFAULT IGNORE RETRYOP MAXRETRIES 3 RETRYDELAY 5 --ASSUMETARGETDEFS --INSERTALLRECORDS
启动进程 start REP1A |
查看ogg环境 show 查看进程信息 info all
查看配置/编辑文件信息 view/edit params filename
启动/停止进程 start/stop name
查看进程报错日志 view report name
查看ogg错误日志 view GGSEVT Contents
强制停止进程 killi extract name
查看历史命令 history
参数文件存放位置 Storing parameter filename
删除进程配置文件 delete name delete name force delete replicat name!
查看trail info exttrail *
查看oracle thread和gg thread的对应关系 select distinct(thread#) from V$log； info extract ext1 ,showch
查看extract的lag (info all 中lag一列是unknow时用) send ext1 getlag
查看延迟 lag name
|
https://www.cnblogs.com/houzhiqing/p/5289383.html --单机->单机
https://www.cnblogs.com/lottu/p/9542681.html --单机->单机
