一、部署规划 目前生产为一套双节点RAC，且配备一个双节点DG。现想通过ogg的downstream模式，将数据从生产数据库同步至目标数据库，并且测试，当生产做dg切换时对数据同步是否有影响。具体各库信息如下： （downstream数据库以及目标数据库安装这里不做赘述，主要讲解如何配置downstream数据库并同步数据） 生产信息
|
节点 |
主机名 |
Ip地址 |
数据库版本 |
数据库名（实例名） |
|
一 节点 |
rac 1 |
192.168. 243.14 |
11.2.0.4 |
test（test1） |
|
二 节点 |
rac 1 |
192.168. 243.15 |
11.2.0.4 |
test（test2） |
备库信息
|
节点 |
主机名 |
Ip地址 |
数据库版本 |
数据库名（实例名） |
|
一 节点 |
rac 1 |
192.168. 243.16 |
11.2.0.4 |
test（test1） |
|
二 节点 |
rac 1 |
192.168. 243.17 |
11.2.0.4 |
test（test2） |
downstream库及ogg安装信息
|
节点 |
主机名 |
Ip地址 |
数据库版本 |
数据库名（实例名） |
|
单 节点 |
node 1 |
192.168. 238.57 |
19.3.0.0 |
test1（test1） |
ogg安装在downstream库服务器上，统一这里做说明 ogg安装目录：/home/ogg/ogg19c/app ogg安装版本：19.1.0.0.4 目标库信息
|
节点 |
主机名 |
Ip地址 |
数据库版本 |
数据库名（ pdb名） |
|
单 节点 |
node 1 |
192.168. 238.57 |
19.3.0.0 |
ora19c（pdb） |
二、生产端准备配置 2.1 开启最小附加日志
SQL> select supplemental_log_data_min from v$database; SUPPLEME -------- YES -------------若不是YES，需要打开 打开语句： SQL> alter database add supplemental log data; SQL> alter system switch logfile;
注： 1.业务繁忙时可能无法创建附加日志，建议在空闲时创建附加日志。 2. supplemental log可以指示数据库在日志中添加额外信息到日志流中,开启后对于修改操作，oracle就会同时附加一些能够唯一标识修改记录的列到redo log中。 2.2开启归档 确认归档是否开启：
SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination +DATA Oldest online log sequence 829 Next log sequence to archive 831 Current log sequence 831 SQL>
以上说明归档已经开启，如果没有开启，请参照以下方法开启归档：
[oracle@localhost ~]$ sqlplus / as sysdba SQL> alter system set log_archive_dest_1='location=+DATA' scope =both sid='*'; 关闭双节点数据库： SQL>shutdown immediate; 在一节点将一号实例启动： SQL>startup mount; SQL> alter database archivelog ; Database altered. SQL> alter database open; Database altered 将二号实例也启动： SQL>startup; 检查归档是否开启: SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination +DATA Oldest online log sequence 829 Next log sequence to archive 831 Current log sequence 831 SQL>
2.3打开force log
SQL> select force_logging from v$database; FOR --- YES 如果状态为YES，则已经打开，如果状态为NO，则参考以下步骤 SQL> alter database force logging;
注： 1.指定数据库为force logging模式后，数据库将会记录除临时表空间或临时回滚段外所有的操作，而忽略类似NOLOGGING之类的指定参数,对数据库中的所有操作都产生日志信息写入到redo log中。 2.4创建ogg管理用户 源数据库上必须有Extract用户。Extract使用该用户的凭据执行元数据查询，并根据需要从源数据库获取列值。 --创建管理用户
SQL> create tablespace odc_tps datafile '+DATA' size 100M autoextend on; SQL> create user odc identified by odc default tablespace odc_tps;
--授权，DBA权限在安装成功后可以收回
exec dbms_goldengate_auth.grant_admin_privilege('ODC');
GRANT CONNECT TO odc;
GRANT ALTER ANY TABLE TO odc;
GRANT ALTER SESSION TO odc;
GRANT CREATE SESSION TO odc;
GRANT FLASHBACK ANY TABLE TO odc;
GRANT SELECT ANY DICTIONARY TO odc;
GRANT SELECT ANY TABLE TO odc;
GRANT RESOURCE TO odc;
GRANT DBA TO odc;
--安装成功后可以收回dba权限，但是要授予UNLIMITED TABLESPACE权限。
GRANT UNLIMITED TABLESPACE TO odc;
或者alter user odc quota unlimited on users;
2.5日志传输配置 生产端配置tnsnames和archive参数，以传日志到downstream server （如果后续要做dg切换，且不影响ogg的挖掘，下面1，2两步生产和dg端都要操作） 1.tnsname.ora配置
vi $ORACLE_HOME/network/admin/tnsnames.ora DSTEST1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.238.57)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = test1) ) )
2.archive参数配置
SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(test,testdg,test1)'; SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_3='SERVICE=dstest1 ASYNC NOREGISTER VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=test1' scope=both; SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=ENABLE;
3.将source库的口令文件传到downstream server
scp /oracle/app/oracle/product/11.2.0/db_1/dbs/orapwtest1 192.168.238.57:/home/ora19c/oracle/app/product/19.3.0/db_1/dbs/orapwtest1
2.6打开数据库参数
SQL> ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION = true scope=both sid='*';
三、downstream 准备配置 3.1开启归档 步骤同生产不做赘述 3.2 archive参数配置
SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(test,testdg,test1)';
如果要在Real-time Mode使用OGG，要在downstream库中添加standby redo log standby redo log size >= source log file size The number of standby log file groups >= The number of source online log file groups+1 --生产查询
SQL> SELECT BYTES,BYTES/1024/1024 MB FROM GV$LOG; BYTES MB ---------- ---------- 1073741824 1024 1073741824 1024 1073741824 1024 1073741824 1024 1073741824 1024 1073741824 1024 1073741824 1024 1073741824 1024 1073741824 1024 1073741824 1024 1073741824 1024 BYTES MB ---------- ---------- 1073741824 1024 12 rows selected. SQL> SELECT COUNT(GROUP#) FROM GV$LOG; COUNT(GROUP#) ------------- 12
--downstream库添加standby redo log
ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/home/ora19c/oracle/app/oradata/TEST1/standby_redo04') SIZE 1024M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/home/ora19c/oracle/app/oradata/TEST1/standby_redo05') SIZE 1024M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/home/ora19c/oracle/app/oradata/TEST1/standby_redo06') SIZE 1024M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/home/ora19c/oracle/app/oradata/TEST1/standby_redo07') SIZE 1024M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 8 ('/home/ora19c/oracle/app/oradata/TEST1/standby_redo08') SIZE 1024M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 9 ('/home/ora19c/oracle/app/oradata/TEST1/standby_redo09') SIZE 1024M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 10 ('/home/ora19c/oracle/app/oradata/TEST1/standby_redo10') SIZE 1024M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 11 ('/home/ora19c/oracle/app/oradata/TEST1/standby_redo11') SIZE 1024M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 12 ('/home/ora19c/oracle/app/oradata/TEST1/standby_redo12') SIZE 1024M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 13 ('/home/ora19c/oracle/app/oradata/TEST1/standby_redo13') SIZE 1024M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 14 ('/home/ora19c/oracle/app/oradata/TEST1/standby_redo14') SIZE 1024M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 15 ('/home/ora19c/oracle/app/oradata/TEST1/standby_redo15') SIZE 1024M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 16 ('/home/ora19c/oracle/app/oradata/TEST1/standby_redo16') SIZE 1024M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 17 ('/home/ora19c/oracle/app/oradata/TEST1/standby_redo17') SIZE 1024M;
SQL> SELECT GROUP#, THREAD#, SEQUENCE#, ARCHIVED, STATUS FROM V$STANDBY_LOG;
GROUP# THREAD# SEQUENCE# ARC STATUS
---------- ---------- ---------- --- ----------
4 0 0 YES UNASSIGNED
5 0 0 YES UNASSIGNED
6 0 0 YES UNASSIGNED
7 0 0 YES UNASSIGNED
8 0 0 YES UNASSIGNED
9 0 0 YES UNASSIGNED
10 0 0 YES UNASSIGNED
11 0 0 YES UNASSIGNED
12 0 0 YES UNASSIGNED
13 0 0 YES UNASSIGNED
14 0 0 YES UNASSIGNED
GROUP# THREAD# SEQUENCE# ARC STATUS
---------- ---------- ---------- --- ----------
15 0 0 YES UNASSIGNED
16 0 0 YES UNASSIGNED
17 0 0 YES UNASSIGNED
--downstream库中配置standby redo log自动归档
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='LOCATION=/home/ora19c/oracle/app/stdarch_test1 VALID_FOR=(STANDBY_LOGFILE,PRIMARY_ROLE)'; ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
3.3创建ogg管理用户 当使用下行挖掘配置时，下行数据库上必须有一个Extract挖掘用户。挖掘Extract进程使用该用户的凭据与下游日志挖掘服务器进行交互。
create user odc identified by odc;
exec dbms_goldengate_auth.grant_admin_privilege('ODC');
grant CREATE SESSION,CONNECT,RESOURCE,ALTER ANY TABLE,ALTER SYSTEM,dba to odc;
四、目标库准备 4.1创建ogg管理用户
create user c##odc identified by odc ;
grant connect,resource,alter system,select any dictionary,dba to c##odc container=all;
ALTER USER c##odc QUOTA UNLIMITED ON users;
exec dbms_goldengate_auth.grant_admin_privilege('C##ODC',container=>'all');
4.2打开数据库参数
SQL> ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION = true scope=both sid='*';
五、OGG软件安装 ogg软件安装在downstream库所在服务器上，因为本案例目标库也在相同服务器上，所以安装一个ogg软件即可。若目标库在其他服务器上，可以在目标端也安装一个ogg软件，或者通过downstream服务上的ogg软件远程应用。 5.1上传软件并解压
[ora19c@node1 ogg19c]$ unzip V1009352-01.zip [ora19c@node1 ogg19c]$ ls -rtl total 543540 -rw-r--r-- 1 ora19c oinstall 1413 May 29 2019 OGG-19.1.0.0-README.txt drwxr-xr-x 3 ora19c oinstall 19 Oct 18 2019 fbo_ggs_Linux_x64_shiphome -rw-r--r-- 1 ora19c oinstall 332523 Oct 21 2019 OGG_WinUnix_Rel_Notes_19.1.0.0.4.pdf -rw------- 1 ora19c oinstall 556240981 Jul 25 11:45 V1009352-01.zip
5.2静默安装 --修改相应文件，主要为一下几个参数，也可直接vi编辑文件
cd /home/ogg/ogg19c/fbo_ggs_Linux_x64_shiphome/Disk1/response cp oggcore.rsp oggcore19.rsp sed -i "s#^INSTALL_OPTION=#INSTALL_OPTION=ORA19c#g" oggcore19.rsp sed -i "s#^SOFTWARE_LOCATION=#SOFTWARE_LOCATION=/home/ogg/ogg19c/app#g" oggcore19.rsp sed -i "s#^INVENTORY_LOCATION=#INVENTORY_LOCATION=/home/ogg/ogg19c/app/oraInventory#g" oggcore19.rsp sed -i "s#^UNIX_GROUP_NAME=#UNIX_GROUP_NAME=oinstall#g" oggcore19.rsp [ora19c@node1 response]$ cat oggcore19.rsp |grep -v '^#'|grep -v "^$" oracle.install.responseFileVersion=/oracle/install/rspfmt_ogginstall_response_schema_v19_1_0 INSTALL_OPTION=ORA19c SOFTWARE_LOCATION=/home/ogg/ogg19c/app START_MANAGER= MANAGER_PORT= DATABASE_LOCATION= INVENTORY_LOCATION=/home/ogg/ogg19c/app/oraInventory UNIX_GROUP_NAME=oinstall
--执行相应文件安装ogg
[ora19c@node1 Disk1]$ ./runInstaller -silent -responseFile /home/ogg/ogg19c/fbo_ggs_Linux_x64_shiphome/Disk1/response/oggcore19.rsp 出现提示“Successfully Setup Software.”时，按一下回车完成OGG的安装
5.3配置library环境变量并初始化目录
vi .bash_profile export LD_LIBRARY_PATH=/home/ogg/ogg19c/app:$ORACLE_HOME/lib:$LD_LIBRARY_PATH cd /home/ogg/ogg19c/app [ora19c@node1 app]$ ./ggsci GGSCI (node1) 2> create subdirs
六、OGG同步配置 6.1创建凭据 downstream端配置tnsnames连接串到生产库，downstream库，以及目标库
cd $ORACLE_HOME/network/admin vi tnsnames.ora source = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.243.14)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = test) ) ) DSTEST1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.238.57)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = test1) ) ) target = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.238.57)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pdb) ) )
--创建钱包
GGSCI (node1) 3> create wallet
添加凭证
GGSCI (node1) 4> add credentialstore GGSCI (node1) 5>alter credentialstore ADD USER odc@source PASSWORD odc alias pri GGSCI (node1) 6>alter credentialstore ADD USER odc@test1 PASSWORD odc alias ds GGSCI (node1) 7>alter credentialstore ADD USER c##odc@target PASSWORD odc alias tar GGSCI (node1) 6> info credentialstore Reading from credential store: Default domain: OracleGoldenGate Alias: ds Userid: odc@test1 Alias: tar Userid: c##odc@target Alias: pri Userid: odc@source
6.2表级别附加日志添加
GGSCI (node1) 7> DBLOGIN USERIDALIAS pri Successfully logged into database. GGSCI (node1 as odc@test1) 8> add trandata zmy.* 2024-09-14 17:58:20 INFO OGG-15132 Logging of supplemental redo data enabled for table ZMY.T_OWNERS. 2024-09-14 17:58:20 INFO OGG-15133 TRANDATA for scheduling columns has been added on table ZMY.T_OWNERS. 2024-09-14 17:58:20 INFO OGG-15135 TRANDATA for instantiation CSN has been added on table ZMY.T_OWNERS.
6.3注册抽取进程 可查找有效scn，并按照scn号注册，否则就是从register那一刻算
SELECT first_change# FROM v$archived_log WHERE dictionary_begin = 'YES' AND standby_dest = 'NO' AND name IS NOT NULL AND status = 'A'; REGISTER EXTRACT group DATABASE [CONTAINER (container[, ...])] [SCN system_change_number] SHARE
如果希望每个源数据库有多个提取，可以使用SHARE和REGISTER extract来实现，以获得更好的性能和元数据 本例如下：
GGSCI (node1) 7> DBLOGIN USERIDALIAS pri Successfully logged into database. GGSCI (node1 as odc@test1) 9> MININGDBLOGIN USERIDALIAS ds Successfully logged into mining database. GGSCI (node1 as odc@test1) 16> REGISTER EXTRACT ext1 DATABASE 2024-09-14 18:02:08 INFO OGG-02003 Extract EXT1 successfully registered with database at SCN 241060.
6.4创建管理进程
GGSCI (node1 as odc@test1) 17> edit param mgr port 7809 LAGREPORTHOURS 5 LAGINFOMINUTES 5 LAGCRITICALMINUTES 15 --AUTOSTART extract e* --AUTIRESTART extract p* PURGEOLDEXTRACTS ./dirdat/*, usecheckpoints, minkeepdays 14 accessrule,prog *,ipaddr *,allow
--如果使用下游捕获和提取集成模式，则设置RMAN归档日志删除策略为: CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY 6.5创建抽取进程
GGSCI (node1) 10> DBLOGIN USERIDALIAS pri Successfully logged into database. GGSCI (node1 as odc@rac1) 13> MININGDBLOGIN USERIDALIAS ds Successfully logged into mining database. GGSCI (ds as c##ogg@orcl/CDB$ROOT) 12> edit param ext1 GGSCI (ds as c##ogg@orcl/CDB$ROOT) 13> view param ext1 EXTRACT ext1 USERIDALIAS pri TRANLOGOPTIONS MININGUSERALIAS ds TRANLOGOPTIONS INTEGRATEDPARAMS (downstream_real_time_mine, Y) TRANLOGOPTIONS INTEGRATEDPARAMS (max_sga_size 700) DISCARDFILE ./dirrpt/ext1.dsc, APPEND, MEGABYTES 1024 LOGALLSUPCOLS UPDATERECORDFORMAT FULL DBOPTIONS ALLOWUNUSEDCOLUMN REPORTCOUNT EVERY 1 MINUTES, RATE WARNLONGTRANS 2h,CHECKINTERVAL 300 DDL INCLUDE MAPPED DDLOPTIONS ADDTRANDATA RETRYOP RETRYDELAY 2 MAXRETRIES 3 EXTTRAIL ./dirdat/aa table zmy.*; GGSCI (node1 as odc@rac1) 8> ADD EXTRACT ext1, INTEGRATED TRANLOG, BEGIN NOW EXTRACT (Integrated) added. GGSCI (node1 as odc@rac1) 9> ADD EXTTRAIL ./dirdat/aa, extract ext1, megabytes 500 EXTTRAIL added.
6.6创建传输进程
本次环境目标库和 downstream 库在同一台服务器上，所以没有配置传输进程。 6.7创建应用进程
GGSCI (test12c.localdomain) 1> DBLOGIN USERIDALIAS tar GGSCI (test12c.localdomain) 2>ADD CHECKPOINTTABLE pdb.c##odc.ckpttable; GGSCI (test12c.localdomain) 3> add replicat rep1 integrated exttrail ./dirdat/aa GGSCI (test12c.localdomain) 5> view params rep1 REPLICAT rep1 --SETENV (ORACLE_SID='zhongwc') DBOPTIONS INTEGRATEDPARAMS(parallelism 6) USERIDALIAS tar ASSUMETARGETDEFS DDL INCLUDE MAPPED --SOURCECATALOG pdb MAP zmy.*, TARGET pdb.zmy.*;
6.8启动进程并确认同步
GGSCI (node1 as odc@test1) 42>start ext1 GGSCI (node1 as odc@test1) 42> start rep GGSCI (node1 as odc@test1) 42> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING EXT1 unknown 00:00:10 REPLICAT RUNNING REP1 00:00:00 00:00:00
