4种复制结构(广播复制,集中复制,多级复制,点对点复制一下一一列举(所列数据都处于归档情况,检验参数如下sql)
SQL> select log_mode,supplemental_log_data_min,force_logging from v$database;
LOG_MODE SUPPLEME FOR
------------ -------- ---
ARCHIVELOG YES YES
I:安装包版本及下载
a.安装包:
Ogg version:
121210_ggs_Windows_x64_shiphome.zip
Oracle version:
win64_11gR2_database
b.下载ogg最新版本:
http://www.oracle.com/index.html --》downloads-->middleware-->goldengate-->
Accept License Agreement 勾上同意
12cwindows下最新版本是:
点击下载,然后跳转到license登陆界面:
我的账号:824661247@qq.com
我的密码:824661247!@#qweZl
下载的文件名称如下:
121210_ggs_Windows_x64_shiphome.zip
II: 广播复制构思(适用场景:数据仓库读写分离前台报表)
a.测试拓扑图(如下实践,一个rep4对应target,一个rep7对应db3)
b.测试环境准备
准备1台windows 7_64bit虚拟机: win7-1
Win7-1 ip地址:10.21.45.85 源数据库:sourcedb
目标数据库:targetdb 和db3(2个目标数据库设置一致即可)
C.测试需要达到的目标
Configuring DML AND DDL Support
第一步:仅安装数据库软件
第二步:配置数据库
a.连接字符串配置
1.使用 net Manger 配置连接字符串
SOURCE =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =10.21.45.85)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = sourcedb)
)
)
TARGET =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.21.45.85)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = targetdb)
)
)
DB3 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)( HOST =10.21.45.85)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = db3)
)
)
b.目标库DB3上设置(在targetdb目标库上也执行同样的操作)
1.创建ogg用户授予dba角色权限(ogg用户用于goldengate进程replicat访问分析redo log)
2.创建receiver用户授予dba角色权限(receiver用户用于目标库测试演示源库dml和ddl操作后效果查看)
Cmdà
Microsoft Windows [版本 6.1.7601]
版权所有 (c) 2009 Microsoft Corporation。保留所有权利。
C:\Users\Administrator>sqlplus sys/oracle@db3 as sysdba
SQL*Plus: Release11.2.0.1.0 Production on 星期日 4月 19 19:58:18 2015
Copyright (c) 1982, 2010, Oracle. All rights reserved.
连接到:
Oracle Database11gEnterprise Edition Release11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> sho parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string db3
SQL> create user ogg identified by oracle default tablespace users;
用户已创建。
SQL> grant dba to ogg;
授权成功。
SQL> create user receiver identified by oracle default tablespace users;
用户已创建。
SQL> conn sys/oracle@db3 as sysdba
已连接。
SQL> grant dba to receiver;
授权成功。
SQL> conn receiver/oracle@db3
已连接。
SQL> create table t(id int);
表已创建。
c.源库sourcedb上设置
1.创建ogg用户授予dba角色权限(ogg用户用于goldengate进程extract访问分析redo log)
2.创建sender用户授予dba角色权限(sender用户用于源库测试演示session的dml和ddl操作)
Cmdà命令类同目标库设置
第三步:数据库归档处理(sourcedb,targetdb,db3均设置归档)
一键归档脚本如下:
conn sys/oracle@db3 as sysdba
alter database add supplemental log data;
alter database force logging;
shutdown immediate
startup mount
alter database archivelog;
alter database open;
select log_mode,supplemental_log_data_min,force_logging from v$database;
第四步:安装ogg软件
1. 双击执行setup.exe
解压ogg安装压缩包:
C:\Users\Administrator\Desktop\121210_ggs_Windows_x64_shiphome\ggs_Windows_x64_shiphome\Disk1\setup.exe 双击运行,
2.
3.
4.
5.
6.
7.下载这个文件安装即可:http://download.microsoft.com/download/3/2/2/3224B87F-CFA0-4E70-BDA3-3DE650EFEBA5/vcredist_x64.exe
第五步:配置ogg
注册服务:GGSCI (win7) 1> install addservice addevents(12c的貌似不用注册)
1. GGSCI (win7) 1>create subdirs
源库上和目标库上都创建,创建目录文件后(比之前多了10个文件夹)
2. 源库上创建并开启管理进程:
GGSCI (win7) 2> edit params mgr
GGSCI (win7) 3>Start mgr
3. 源库上创建 extract
GGSCI (win7) 4> add extract ext4, tranlog, begin now
EXTRACT added.
GGSCI (win7) 5> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXT1 00:00:00 00:00:04
EXTRACT STOPPED EXT4 00:00:00 00:00:04
4添加本地trail文件
GGSCI (win7) 6> add exttrail C:\ogg\dirdat\dl, extract ext4
EXTTRAIL added.
GGSCI (win7) 8> view params ext4
extract ext4
setenv(ORACLE_SID='SOURCE')
DDL INCLUDE ALL
USERID OGG@SOURCE, password oracle
rmthost win7, mgrport 7809
rmttrail c:\ogg\dirdat\dl
table sender.*;
5.目标数据库创建checkpoint表
GGSCI (win7) 9>edit params GLOBALS
GGSCHEMA OGG
CHECKPOINTTABLE OGG.CHECKPOINT
GGSCI (win7) 10>Dblogin userid ogg@target password oracle
GGSCI (win7) 11>Add checkpointtable ogg.CHECKPOINT
GGSCI (win7) 10>Dblogin userid ogg@DB3 password oracle
GGSCI (win7) 11>Add checkpointtable ogg.CHECKPOINT
---注意广播复制时候:2个目标库上要分别都创建检查表对象,如上
6.目标库上创建 replicat
GGSCI>add replicat rep4, exttrail c:\ogg\dirdat\dl, checkpointtable ogg.CHECKPOINT
REPLICAT added.
replicat rep4
userid ogg@target, password oracle—仅仅改变成userid ogg@db3 password oracle 即可
reperror default abend
DDL INCLUDE ALL
assumetargetdefs
map sender.*, target receiver.*;
第六开启ddl操作
以下操作在源端、目标端都要执行
安装DDL相关对象时,建议退出所有应用程序,并防止数据库有新的连接,否则可能导致操作失败。
安装支持DDL同步对象,可通过下述命令实现:
1.创建ogg单独表空间tbs_ogg(用于存放goldengate ddl对象)
C:\Users\Administrator>sqlplus sys/oracle@source as sysdba
SQL*Plus: Release11.2.0.1.0 Production on 星期日 4月 19 21:06:37 2015
Copyright (c) 1982, 2010, Oracle. All rights reserved.
连接到:
Oracle Database11gEnterprise Edition Release11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select default_tablespace from dba_users where username='OGG';
DEFAULT_TABLESPACE
------------------------------
USERS
SQL> create tablespace tbs_ogg datafile 'c:\oracle\sourcedata\tbs_ogg.dbf'size10mautoextend on next10m;
表空间已创建。
SQL> alter user ogg default tablespace tbs_ogg;
用户已更改。
SQL> select default_tablespace from dba_users where username='OGG';
DEFAULT_TABLESPACE
------------------------------
TBS_OGG
SQL>
2. 赋予GoldenGate用户utl_file执行权限(最好断开所有数据库连接、停止监听)
SQL> conn sys/oracle@source as sysdba
已连接。
SQL> grant execute on utl_file to ogg;
授权成功。
SQL> conn sys/oracle@target as sysdba
已连接。
SQL> grant execute on utl_file to ogg;
授权成功。
SQL> conn sys/oracle@db3 as sysdba
已连接。
SQL> grant execute on utl_file to ogg;
授权成功。
3.c:\ogg\params.sql脚本设置
ddl_fire_error_in_trigger=‘TRUE’
allow_invisible_index_keys = 'TRUE'
4.创建GLOBALS参数
GGSCI (win7) 9>edit params GLOBALS
GGSCHEMA OGG
《依次执行以下脚本》
marker_setup.sql
ddl_setup.sql
role_setup.sql
Grant the role to all GoldenGate Extract users
ddl_enable.sql
install and use the optional performance tool
5.OGG之DDL复制配置时执行marker_setup.sql hang的问题
C:\Users\Administrator>cd c:\
c:\>cd c:\ogg ---注意一定要先切换到脚本目录下在进入数据库@调用脚本,否则hang住
c:\ogg>sqlplus sys/oracle@source as sysdba --ogg 安装文件夹下的ddl脚本,在源库上执行即可
SQL*Plus: Release11.2.0.1.0 Production on 星期日 4月 19 21:56:29 2015
Copyright (c) 1982, 2010, Oracle. All rights reserved.
连接到:
Oracle Database11gEnterprise Edition Release11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @marker_setup
Marker setup script
You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.
Enter Oracle GoldenGate schema name:ogg
Marker setup table script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to OGG
MARKER TABLE
-------------------------------
OK
MARKER SEQUENCE
-------------------------------
OK
Script complete.
SQL>
6.SQL> @ddl_setup --以下是执行脚本ddl_setup情况开始
Oracle GoldenGate DDL Replication setup script
Verifying that current user has privileges to install DDL Replication...
You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: For an Oracle10gsource, the system recycle bin must be disabled. For Oracle11gand later, i
t can be enabled.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.
Enter Oracle GoldenGate schema name:ogg
Working, please wait ...
Spooling to file ddl_setup_spool.txt
Checking for sessions that are holding locks on Oracle Golden Gate metadata tables ...
Check complete.
Using OGG as a Oracle GoldenGate schema name.
Working, please wait ...
DDL replication setup script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to OGG
CLEAR_TRACE STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
CREATE_TRACE STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
TRACE_PUT_LINE STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
INITIAL_SETUP STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
DDLVERSIONSPECIFIC PACKAGE STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
DDLREPLICATION PACKAGE STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
DDLREPLICATION PACKAGE BODY STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
DDL IGNORE TABLE
-----------------------------------
OK
DDL IGNORE LOG TABLE
-----------------------------------
OK
DDLAUX PACKAGE STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
DDLAUX PACKAGE BODY STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
SYS.DDLCTXINFO PACKAGE STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
SYS.DDLCTXINFO PACKAGE BODY STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
DDL HISTORY TABLE
-----------------------------------
OK
DDL HISTORY TABLE(1)
-----------------------------------
OK
DDL DUMP TABLES
-----------------------------------
OK
DDL DUMP COLUMNS
-----------------------------------
OK
DDL DUMP LOG GROUPS
-----------------------------------
OK
DDL DUMP PARTITIONS
-----------------------------------
OK
DDL DUMP PRIMARY KEYS
-----------------------------------
OK
DDL SEQUENCE
-----------------------------------
OK
GGS_TEMP_COLS
-----------------------------------
OK
GGS_TEMP_UK
-----------------------------------
OK
DDL TRIGGER CODE STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
DDL TRIGGER INSTALL STATUS
-----------------------------------
OK
DDL TRIGGER RUNNING STATUS
----------------------------------------------------------------------
ENABLED
STAYMETADATA IN TRIGGER
----------------------------------------------------------------------
OFF
DDL TRIGGER SQL TRACING
----------------------------------------------------------------------
0
DDL TRIGGER TRACE LEVEL
----------------------------------------------------------------------
0
LOCATION OF DDL TRACE FILE
----------------------------------------------------------------------------------------------------
--------------------
c:\oracle\diag\rdbms\sourcedb\source\trace/ggs_ddl_trace.log
Analyzing installation status...
VERSION OF DDL REPLICATION
----------------------------------------------------------------------------------------------------
--------------------
OGGCORE_12.1.2.1.0_PLATFORMS_140920.0203
STATUS OF DDL REPLICATION
----------------------------------------------------------------------------------------------------
--------------------
SUCCESSFUL installation of DDL Replication software components
Script complete.
7.SQL> @role_setup --以下是执行脚本role_setup情况开始
GGS Role setup script
This script will drop and recreate the role GGS_GGSUSER_ROLE
To use a different role name, quit this script and then edit the params.sql script to change the gg_
role parameter to the preferred name. (Do not run the script.)
You will be prompted for the name of a schema for the GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.
Enter GoldenGate schema name:ogg
已写入 file role_setup_set.txt
PL/SQL 过程已成功完成。
Role setup script complete
Grant this role to each user assigned to the Extract, GGSCI, and Manager processes, by using the fol
lowing SQL command:
GRANT GGS_GGSUSER_ROLE TO
where is the user assigned to the GoldenGate processes.
--以上是执行脚本role_setup情况结束
8.授予ogg角色ggs_ggsuser_role
SQL> grant ggs_ggsuser_role to ogg;
授权成功。
9.运行脚本 @ddl_enable.sql
SQL> @ddl_enable.sql
触发器已更改
10.将DDL相关对象pin到shared pool
实施此操作需要调用dbms_shared_pool包,需要事先确认此包是否已安装。
确认dbms_shared_pool包是否已安装,通过下述命令实现:
desc dbms_shared_pool
将DDL对象pin到shared pool,通过下述命令实现:
@ddl_pin ogg
SQL> desc dbms_shared_pool
PROCEDURE ABORTED_REQUEST_THRESHOLD
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
THRESHOLD_SIZE NUMBER IN
PROCEDURE KEEP
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
NAME VARCHAR2 IN
FLAG CHAR IN DEFAULT
PROCEDURE PURGE
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
NAME VARCHAR2 IN
FLAG CHAR IN DEFAULT
HEAPS NUMBER IN DEFAULT
PROCEDURE SIZES
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
MINSIZE NUMBER IN
PROCEDURE UNKEEP
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
NAME VARCHAR2 IN
FLAG CHAR IN DEFAULT
SQL> @ddl_pin ogg
PL/SQL 过程已成功完成。
PL/SQL 过程已成功完成。
PL/SQL 过程已成功完成。
cmdà
Microsoft Windows [版本 6.1.7601]
版权所有 (c) 2009 Microsoft Corporation。保留所有权利。
C:\Users\Administrator>cd c:\ogg
c:\ogg>ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140920.0203
Windows x64 (optimized), Oracle11gon Sep 20 2014 07:17:50
Operating system character set identified as GBK.
Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved.
GGSCI (win7) 1> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER STOPPED
EXTRACT STOPPED EXT1 00:00:00 176:44:48
EXTRACT STOPPED EXT4 00:00:00 172:11:22
EXTRACT ABENDED EXT3 00:00:00 171:23:16
REPLICAT STOPPED REP1 00:00:00 176:44:47
GGSCI (win7) 2> add extract ext4, tranlog, begin now
EXTRACT added.
GGSCI (win7) 3> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER STOPPED
EXTRACT STOPPED EXT1 00:00:00 176:45:45
EXTRACT STOPPED EXT4 00:00:00 172:12:19
EXTRACT ABENDED EXT3 00:00:00 171:24:13
EXTRACT STOPPED EXT4 00:00:00 00:00:04
REPLICAT STOPPED REP1 00:00:00 176:45:44
GGSCI (win7) 5> add exttrail c:\ogg\dirdat\dl, extract ext4
EXTTRAIL added.
GGSCI (win7) 6> edit params ext4
GGSCI (win7) 8> view params ext4
extract ext4
setenv(ORACLE_SID='SOURCE')
ddl include all
USERID OGG@SOURCE, password oracle
rmthost10.21.45.85, mgrport 7809
rmttrail c:\ogg\dirdat\dl
table sender.*;
GGSCI (win7) 10> view params mgr
PORT 7809
GGSCI (win7) 11> edit params GLOBALS
GGSCI (win7) 12> add replicat rep4, exttrail c:\ogg\dirdat\dl, checkpointtable ogg.checkpoint
REPLICAT added.
GGSCI (win7) 13> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER STOPPED
EXTRACT STOPPED EXT1 00:00:00 177:11:30
EXTRACT STOPPED EXT4 00:00:00 172:38:04
EXTRACT ABENDED EXT3 00:00:00 171:49:57
EXTRACT STOPPED EXT4 00:00:00 00:25:49
REPLICAT STOPPED REP1 00:00:00 177:11:29
REPLICAT STOPPED REP4 00:00:00 00:00:04
GGSCI (win7) 14> edit params rep4
GGSCI (win7) 15> view params rep4
replicat rep4
userid ogg@target, password oracle
reperror default abend
assumetargetdefs
DDL INCLUDE MAPPED
DDLERROR DEFAULT IGNORE RETRYOP
map sender.*, target receiver.*;
GGSCI (win7) 16> start mgr
Manager started.
GGSCI (win7) 17> start ext4
Sending START request to MANAGER ...
EXTRACT EXT4 starting
GGSCI (win7) 18> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXT1 00:00:00 177:20:08
EXTRACT STOPPED EXT4 00:00:00 172:46:42
EXTRACT ABENDED EXT3 00:00:00 171:58:36
EXTRACT RUNNING EXT4 00:34:27 00:00:00
REPLICAT STOPPED REP1 00:00:00 177:20:07
REPLICAT STOPPED REP4 00:00:00 00:08:42
GGSCI (win7) 19> start rep4
Sending START request to MANAGER ...
REPLICAT REP4 starting
GGSCI (win7) 20> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXT1 00:00:00 177:20:29
EXTRACT STOPPED EXT4 00:00:00 172:47:03
EXTRACT ABENDED EXT3 00:00:00 171:58:56
EXTRACT RUNNING EXT4 00:00:00 00:00:10
REPLICAT STOPPED REP1 00:00:00 177:20:28
REPLICAT RUNNING REP4 00:00:00 00:00:02
广播复制测试结果如下:
广播复制成功!
III:集中复制构思(如下实践,一个ext1对应源库sourcedb,一个ext2对应源库sota,一个rep1应用ext1捕获的ex taril文件,一个rep2应用ext2捕获的zl trail文件都对应同一个目标库targetdb)
注意:本实验一定要清楚了解检查点机制
1. 清理已经创建的extract
GGSCI (win7) 15> delete ext5
2015-04-20 17:10:48 WARNING OGG-01753 Cannot unregister EXTRACT EXT5 from database because no data
base login was provided. You can manually unregister this group later with the UNREGISTER EXTRACT co
mmand with LOGRETENTION. Issue DBLOGIN first.
Deleted EXTRACT EXT5.
GGSCI (win7) 19> delete rep3
ERROR: Could not delete DB checkpoint for REPLICAT REP3 (Database login required to delete database checkpoint)
GGSCI (win7) 23> Dblogin userid ogg@target password oracle --先登录在删除即可。
Successfully logged into database.
GGSCI (win7 as ogg@db3) 24> delete rep1
Deleted REPLICAT REP1.
---要深刻理解checkpoint的意义!
--清理完毕!
GGSCI (win7 as ogg@db3) 35> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
对db3的ddl开启(依照第六开启ddl操作,即可)
--开启ddl操作:sourcedb已经开启了ddl操作(运行了相应脚本,db3也要做相应ddl的设置)
--现在DB3作为源库了,所以还要创建一个sender用户并授予dba角色权限
1. 创建ext1
GGSCI (win7 as ogg@db3) 38> add extract ext1, tranlog, begin now
EXTRACT added.
GGSCI (win7 as ogg@db3) 40> add exttrail c:\ogg\dirdat\ex, extract ext1
EXTTRAIL added.
GGSCI (win7 as ogg@db3) 65> view params ext1
GGSCI (win7) 27> view params ext1
extract ext1
setenv(ORACLE_SID='SOURCE')
SETENV (NLS_LANG='AMERICAN_AMERICA.ZHS16GBK')
DDL INCLUDE ALL
USERID ogg@source,password oracle
RMTHOST win7, MGRPORT 7809
rmttrail C:\ogg\dirdat\ex
table sender.*;ggserr.log:诡异的报错如下
2015-04-20 21:44:10 ERROR OGG-00717 Oracle GoldenGate Capture for Oracle, EXT1.prm: Found unsupported in-memory undo record in sequence 15, at RBA 16349440, with SCN 0.228524 (228524) ... Minimum supplemental logging must be enabled to prevent data loss.
2015-04-20 21:44:10 ERROR OGG-01668 Oracle GoldenGate Capture for Oracle, EXT1.prm: PROCESS ABENDING.
解决:alter extract extb1, tranlog, begin 2011-04-21 22:16:52.000000, thread 1
alter extract extb1, tranlog, begin now, thread 1
先用固定时间,不行,后又改回同样的begin now,就行了,为什么啊------不知道
2. 创建ext2
GGSCI (win7 as ogg@db3) 70> add extract ext2, tranlog, begin now
EXTRACT added.
GGSCI (win7 as ogg@db3) 72> add exttrail c:\ogg\dirdat\zl, extract ext2
EXTTRAIL added.
GGSCI (win7 as ogg@db3) 75> view params ext2
GGSCI (win7) 28> view params ext2
extract ext2
setenv(ORACLE_SID='ST')
SETENV (NLS_LANG='AMERICAN_AMERICA.ZHS16GBK')
DDL INCLUDE ALL
USERID OGG@SOTA, password oracle
rmthost win7, mgrport 7809
rmttrail c:\ogg\dirdat\zl
table sender.*;
GGSCI (win7) 9> Dblogin userid ogg@target password oracle
Successfully logged into database.
GGSCI (win7 as ogg@target) 10> Add checkpointtable ogg.CHECKPOINT
GGSCI (win7 as ogg@target) 11> add replicat rep1, exttrail c:\ogg\dirdat\ex, checkpointtable ogg.checkpoint
REPLICAT added.
3. 创建 rep1
GGSCI (win7) 15> add replicat rep2, exttrail c:\ogg\dirdat\ex, checkpointtable ogg.checkpoint
REPLICAT added.
GGSCI (win7) 30> view params rep1
replicat rep1
SETENV (NLS_LANG='AMERICAN_AMERICA.ZHS16GBK')
userid ogg@target, password oracle
rmthost win7
reperror default abend
assumetargetdefs
DDL INCLUDE MAPPED
DDLERROR DEFAULT IGNORE RETRYOP
map sender.*, target receiver.*;
4. 创建 rep2
GGSCI (win7) 15> add replicat rep2, exttrail c:\ogg\dirdat\zl, checkpointtable ogg.checkpoint
REPLICAT added.
GGSCI (win7) 30> view params rep2
replicat rep1
SETENV (NLS_LANG='AMERICAN_AMERICA.ZHS16GBK')
userid ogg@target, password oracle
rmthost win7
reperror default abend
assumetargetdefs
DDL INCLUDE MAPPED
DDLERROR DEFAULT IGNORE RETRYOP
map sender.*, target receiver.*;
5. 检查点
GGSCI (win7) 32> view params GLOBALS
GGSCHEMA OGG
CHECKPOINTTABLE OGG.CHECKPOINT
GGSCI (win7) 26> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:00 00:00:04
EXTRACT RUNNING EXT2 00:00:00 00:00:08
REPLICAT RUNNING REP1 00:00:00 00:00:06
REPLICAT RUNNING REP2 00:00:00 00:00:02
补充部分:
--重置抽取进程,本地文件序列号从0开始生成。
alter extract ext1,extseqno 0,extrba 0
--重置读取进程,重新从0号trial文件开始读取。
alter replicat rep1,extseqno 0,extrba 0
检查点详情查看:
GGSCI (win7) 12> info ext1, showch
集中复制dml演示截图如下:Ok
集中复制ddl演示截图如下:Ok
广播复制成功!
IV:多级复制构思(如下实践,一个ext1对应源库sourcedb,一个ext2对应源库sota,一个rep1应用ext1捕获的ex taril文件,一个rep2应用ext2捕获的zl trail文件都对应同一个目标库targetdb)
1. 清理已经创建的extract
针对本实验,我们可以借用III集中复制的环境,保留sourcedb到targetdb的设置,
所以保留ext1和rep1清理掉 ext2和rep2,在重新设置即可,操作如下:
GGSCI (win7) 35> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:00 00:00:10
EXTRACT RUNNING EXT2 00:00:00 00:00:00
REPLICAT RUNNING REP1 00:00:00 00:00:07
REPLICAT RUNNING REP2 00:00:00 00:00:07
GGSCI (win7) 36> stop ext2
Sending STOP request to EXTRACT EXT2 ...
Request processed.
GGSCI (win7) 37> delete ext2
2015-04-21 16:29:37 WARNING OGG-01753 Cannot unregister EXTRACT EXT2 from database because no data
base login was provided. You can manually unregister this group later with the UNREGISTER EXTRACT co
mmand with LOGRETENTION. Issue DBLOGIN first.
Deleted EXTRACT EXT2.
GGSCI (win7) 38> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:00 00:00:08
REPLICAT RUNNING REP1 00:00:00 00:00:05
REPLICAT RUNNING REP2 00:00:00 00:00:06
GGSCI (win7) 39> stop rep2
Sending STOP request to REPLICAT REP2 ...
Request processed.
GGSCI (win7) 40> delete rep2
ERROR: Could not delete DB checkpoint for REPLICAT REP2 (Database login required to delete database
checkpoint).
GGSCI (win7) 49> Dblogin userid ogg@target password oracle
Successfully logged into database.
GGSCI (win7 as ogg@target) 50> delete rep2
Deleted REPLICAT REP2.
GGSCI (win7 as ogg@target) 51> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:00 00:00:08
REPLICAT RUNNING REP1 00:00:00 00:00:02
3. Targetdb作为第二级复制源,所以也要做ddl操作开启:
c:\ogg>sqlplus sys/oracle@target as sysdba
SQL*Plus: Release11.2.0.1.0 Production on 星期二 4月 21 16:51:19 2015
Copyright (c) 1982, 2010, Oracle. All rights reserved.
连接到:
Oracle Database11gEnterpriseEdition Release11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> sho parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string targetdb
SQL> sho user
USER 为 "SYS"
SQL> create tablespace tbs_ogg datafile 'c:\oracle\targetdata\tbs_ogg.dbf'size10mautoextend on nex
t10m;
表空间已创建。
SQL> alter user ogg default tablespace tbs_ogg;
用户已更改。
SQL> grant execute on utl_file to ogg;
授权成功。
SQL> @marker_setup
Marker setup script
You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.
Enter Oracle GoldenGate schema name:ogg
Marker setup table script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to OGG
MARKER TABLE
-------------------------------
OK
MARKER SEQUENCE
-------------------------------
OK
Script complete.
SQL> @ddl_setup
Oracle GoldenGate DDL Replication setup script
Verifying that current user has privileges to install DDL Replication...
You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: For an Oracle10gsource, the system recycle bin must be disabled. For Oracle11gand later, i
t can be enabled.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.
Enter Oracle GoldenGate schema name:ogg
Working, please wait ...
Spooling to file ddl_setup_spool.txt
Checking for sessions that are holding locks on OracleGolden Gatemetadata tables ...
Check complete.
Using OGG as a Oracle GoldenGate schema name.
Working, please wait ...
DDL replication setup script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to OGG
CLEAR_TRACE STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
CREATE_TRACE STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
TRACE_PUT_LINE STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
INITIAL_SETUP STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
DDLVERSIONSPECIFIC PACKAGE STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
DDLREPLICATION PACKAGE STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
DDLREPLICATION PACKAGE BODY STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
DDL IGNORE TABLE
-----------------------------------
OK
DDL IGNORE LOG TABLE
-----------------------------------
OK
DDLAUX PACKAGE STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
DDLAUX PACKAGE BODY STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
SYS.DDLCTXINFO PACKAGE STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
SYS.DDLCTXINFO PACKAGE BODY STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
DDL HISTORY TABLE
-----------------------------------
OK
DDL HISTORY TABLE(1)
-----------------------------------
OK
DDL DUMP TABLES
-----------------------------------
OK
DDL DUMP COLUMNS
-----------------------------------
OK
DDL DUMP LOG GROUPS
-----------------------------------
OK
DDL DUMP PARTITIONS
-----------------------------------
OK
DDL DUMP PRIMARY KEYS
-----------------------------------
OK
DDL SEQUENCE
-----------------------------------
OK
GGS_TEMP_COLS
-----------------------------------
OK
GGS_TEMP_UK
-----------------------------------
OK
DDL TRIGGER CODE STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
DDL TRIGGER INSTALL STATUS
-----------------------------------
OK
DDL TRIGGER RUNNING STATUS
----------------------------------------------------------------------
ENABLED
STAYMETADATA IN TRIGGER
----------------------------------------------------------------------
OFF
DDL TRIGGER SQL TRACING
----------------------------------------------------------------------
0
DDL TRIGGER TRACE LEVEL
----------------------------------------------------------------------
0
LOCATION OF DDL TRACE FILE
----------------------------------------------------------------------------------------------------
--------------------
c:\oracle\diag\rdbms\targetdb\target\trace/ggs_ddl_trace.log
Analyzing installation status...
VERSION OF DDL REPLICATION
----------------------------------------------------------------------------------------------------
--------------------
OGGCORE_12.1.2.1.0_PLATFORMS_140920.0203
STATUS OF DDL REPLICATION
----------------------------------------------------------------------------------------------------
--------------------
SUCCESSFUL installation of DDL Replication software components
Script complete.
SQL> @role_setup
GGS Role setup script
This script will drop and recreate the role GGS_GGSUSER_ROLE
To use a different role name, quit this script and then edit the params.sql script to change the gg_
role parameter to the preferred name. (Do not run the script.)
You will be prompted for the name of a schema for the GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.
Enter GoldenGate schema name:ogg
已写入 file role_setup_set.txt
PL/SQL 过程已成功完成。
Role setup script complete
Grant this role to each user assigned to the Extract, GGSCI, and Manager processes, by using the fol
lowing SQL command:
GRANT GGS_GGSUSER_ROLE TO
where is the user assigned to the GoldenGate processes.
SQL> grant ggs_ggsuser_role to ogg;
授权成功。
SQL> @ddl_enable.sql
触发器已更改
SQL> @ddl_pin ogg
PL/SQL 过程已成功完成。
PL/SQL 过程已成功完成。
PL/SQL 过程已成功完成。
4. 测试sourcedb到targetdb之间复制依然正常,ok..接下来创建targetdb到sota的进程(ext2,rep2)
创建ext2:
GGSCI (win7 as ogg@target) 54> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:00 00:00:04
REPLICAT RUNNING REP1 00:00:05 00:00:00
GGSCI (win7 as ogg@target) 55> add extract ext2, tranlog, begin now
EXTRACT added.
GGSCI (win7 as ogg@target) 56> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:00 00:00:10
EXTRACT STOPPED EXT2 00:00:00 00:00:05
REPLICAT RUNNING REP1 00:00:00 00:00:03
GGSCI (win7 as ogg@target) 57> view params ext1
extract ext1
setenv(ORACLE_SID='SOURCE')
SETENV (NLS_LANG='AMERICAN_AMERICA.ZHS16GBK')
DDL INCLUDE ALL
USERID ogg@source,password oracle
RMTHOST win7, MGRPORT 7809
rmttrail C:\ogg\dirdat\lt
table sender.*;
GGSCI (win7 as ogg@target) 58> add exttrail c:\ogg\dirdat\tt, extract ext2
EXTTRAIL added.
GGSCI (win7 as ogg@target) 59> edit params ext2
GGSCI (win7 as ogg@target) 60> start ext2
Sending START request to MANAGER ...
EXTRACT EXT2 starting
GGSCI (win7 as ogg@target) 61> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:00 00:00:05
EXTRACT ABENDED EXT2 00:00:00 00:36:22
REPLICAT RUNNING REP1 00:00:00 00:00:08
GGSCI (win7 as ogg@target) 62> edit params ext2
GGSCI (win7 as ogg@target) 63> start ext2
Sending START request to MANAGER ...
EXTRACT EXT2 starting
GGSCI (win7 as ogg@target) 64> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:00 00:00:02
EXTRACT RUNNING EXT2 00:00:00 00:37:43
REPLICAT RUNNING REP1 00:00:00 00:00:08
GGSCI (win7 as ogg@target) 66> Dblogin userid ogg@sota password oracle
Successfully logged into database.
GGSCI (win7 as ogg@st) 67> add checkpointtable ogg.checkpoint
Successfully created checkpoint table ogg.checkpoint.
GGSCI (win7 as ogg@st) 68> add replicat rep2, exttrail c:\ogg\dirdat\tt, checkpointtable ogg.
INT
REPLICAT added.
GGSCI (win7 as ogg@st) 69> edit params rep2
GGSCI (win7 as ogg@st) 70> start rep2
Sending START request to MANAGER ...
REPLICAT REP2 starting
GGSCI (win7 as ogg@st) 71> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT ABENDED EXT1 00:00:00 02:56:35
EXTRACT RUNNING EXT2 00:00:00 00:00:08
REPLICAT RUNNING REP1 00:00:00 00:00:05
REPLICAT STARTING REP2 00:00:00 00:01:22
GGSCI (win7 as ogg@st) 72> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT ABENDED EXT1 00:00:00 02:57:34
EXTRACT RUNNING EXT2 00:00:00 00:00:04
REPLICAT RUNNING REP1 00:00:00 00:00:04
REPLICAT RUNNING REP2 00:00:00 00:00:06
GGSCI (win7 as ogg@st) 80> view params ext2
extract ext2
setenv(ORACLE_SID='TARGET')
SETENV (NLS_LANG='AMERICAN_AMERICA.ZHS16GBK')
DDL INCLUDE ALL
USERID OGG@TARGET, password oracle
rmthost win7, mgrport 7809
rmttrail c:\ogg\dirdat\tt
table sender.*;
GGSCI (win7 as ogg@st) 87> start ext1
Sending START request to MANAGER ...
EXTRACT EXT1 starting
GGSCI (win7 as ogg@st) 88> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 03:13:00 00:00:01
EXTRACT RUNNING EXT2 00:00:00 00:00:08
REPLICAT RUNNING REP1 00:00:00 00:00:10
REPLICAT RUNNING REP2 00:00:00 00:00:00
GGSCI (win7 as ogg@st) 106> Dblogin userid ogg@sotb password oracle
Successfully logged into database.
GGSCI (win7 as ogg@sotb) 107> add replicat rep3, exttrail c:\ogg\dirdat\tt, checkpointtable ogg.CHEC
KPOINT
REPLICAT added.
GGSCI (win7 as ogg@sotb) 108> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXT1 00:00:00 00:09:00
EXTRACT RUNNING EXT2 00:00:00 00:00:09
REPLICAT STOPPED REP1 00:00:00 00:08:50
REPLICAT RUNNING REP2 00:00:00 00:00:09
REPLICAT STOPPED REP3 00:00:00 00:00:03
GGSCI (win7 as ogg@sotb) 109> edit params rep3
GGSCI (win7 as ogg@sotb) 110> view params rep2
replicat rep2
SETENV (NLS_LANG='AMERICAN_AMERICA.ZHS16GBK')
userid ogg@sota, password oracle
rmthost win7
reperror default abend
assumetargetdefs
DDL INCLUDE MAPPED
DDLERROR DEFAULT IGNORE RETRYOP
map sender.*, target receiver.*;
GGSCI (win7 as ogg@sotb) 111> start rep3
Sending START request to MANAGER ...
REPLICAT REP3 starting
GGSCI (win7 as ogg@sotb) 112> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXT1 00:00:00 00:10:08
EXTRACT RUNNING EXT2 00:00:00 00:00:02
REPLICAT STOPPED REP1 00:00:00 00:09:58
REPLICAT RUNNING REP2 00:00:00 00:00:05
REPLICAT STOPPED REP3 00:00:00 00:01:11
GGSCI (win7 as ogg@sotb) 113> Dblogin userid ogg@sotb password oracle
Successfully logged into database.
GGSCI (win7 as ogg@sotb) 114> Add checkpointtable ogg.CHECKPOINT
Successfully created checkpoint table ogg.CHECKPOINT.
GGSCI (win7 as ogg@sotb) 115> start rep3
Sending START request to MANAGER ...
REPLICAT REP3 starting
GGSCI (win7 as ogg@sotb) 116> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXT1 00:00:00 00:12:16
EXTRACT RUNNING EXT2 00:00:00 00:00:02
REPLICAT STOPPED REP1 00:00:00 00:12:06
REPLICAT RUNNING REP2 00:00:00 00:00:02
REPLICAT RUNNING REP3 00:08:52 00:00:01
GGSCI (win7 as ogg@sotb) 117> start ext1
Sending START request to MANAGER ...
EXTRACT EXT1 starting
GGSCI (win7 as ogg@sotb) 118> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:00 00:18:23
EXTRACT RUNNING EXT2 00:00:00 00:00:08
REPLICAT STOPPED REP1 00:00:00 00:18:14
REPLICAT RUNNING REP2 00:00:00 00:00:03
REPLICAT RUNNING REP3 00:00:00 00:00:02
GGSCI (win7 as ogg@sotb) 119> start rep1
Sending START request to MANAGER ...
REPLICAT REP1 starting
GGSCI (win7 as ogg@sotb) 148> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:00 00:00:09
EXTRACT RUNNING EXT2 00:00:00 00:00:09
REPLICAT RUNNING REP1 00:00:00 00:00:00
REPLICAT RUNNING REP2 00:00:00 00:00:00
REPLICAT RUNNING REP3 00:00:00 00:00:09
一键禁闭回收站
Cmdà
set oracle_sid=sotb
sqlplus / as sysdba
alter system set recyclebin=off scope=spfile;
shutdown immediate
startup
sho parameter recyclebin
dml多级复制成功!
Ddl多级复制成功!
V:点对点复制构思
基于双活复制基础上再做双活,相当于三对双活。
双活sourcedb和targetdb
双活sourcedb和sota
双活targetdb和sota
tranlogoptions excludeuser ogg //避免出现死循环复制,db1上的extract进程也需要进行此项设置
第一步
双活sourcedb和targetdb
1. 创建ext1/ext2/rep1/rep2
从sourcedb到targetdb
add extract ext1, tranlog, begin now
add exttrail c:\ogg\dirdat\wy, extract ext1
add replicat rep1, exttrail c:\ogg\dirdat\wy checkpointtable ogg.checkpoint
从targetdb到sourcedb
add extract ext2, tranlog, begin now
add exttrail c:\ogg\dirdat\yw, extract ext2
add replicat rep2, exttrail c:\ogg\dirdat\yw checkpointtable ogg.checkpoint
具体实现代码如下:
GGSCI (win7 as receiver@target) 31> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
GGSCI (win7 as receiver@target) 32> add extract ext1, tranlog, begin now
EXTRACT added.
GGSCI (win7 as receiver@target) 33> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXT1 00:00:00 00:00:04
GGSCI (win7 as receiver@target) 34> add exttrail c:\ogg\dirdat\wy, extract ext1
EXTTRAIL added.
GGSCI (win7 as receiver@target) 35> edit params ext1
GGSCI (win7 as receiver@target) 36> start ext1
Sending START request to MANAGER ...
EXTRACT EXT1 starting
GGSCI (win7 as receiver@target) 37> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXT1 00:00:00 00:02:47
GGSCI (win7 as receiver@target) 38> view params ext1
extract ext1
tranlogoptions excludeuser ogg
setenv(ORACLE_SID='SOURCE')
SETENV (NLS_LANG='AMERICAN_AMERICA.ZHS16GBK')
DDL INCLUDE ALL
USERID sender@source,password oracle
RMTHOST win7, MGRPORT 7809
rmttrail C:\ogg\dirdat\wy
table sender.*;
GGSCI (win7 as receiver@target) 39> edit params ext1
GGSCI (win7 as receiver@target) 40> start ext1
Sending START request to MANAGER ...
EXTRACT EXT1 starting
GGSCI (win7 as receiver@target) 41> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:00 00:06:01
GGSCI (win7 as receiver@target) 42> edit params globals
GGSCHEMA ogg
CHECKPOINTTABLE ogg.CHECKPOINT
GGSCI (win7 as receiver@target) 43> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:00 00:00:06
GGSCI (win7 as receiver@target) 44> add replicat rep1, exttrail c:\ogg\dirdat\wy checkpointtable ogg.checkpoint
REPLICAT added.
GGSCI (win7 as receiver@target) 46> edit params rep1
GGSCI (win7 as receiver@target) 47> start rep1
Sending START request to MANAGER ...
REPLICAT REP1 starting
GGSCI (win7 as receiver@target) 74> view params rep1
replicat rep1
SETENV (NLS_LANG='AMERICAN_AMERICA.ZHS16GBK')
userid ogg@target, password oracle
rmthost win7
reperror default abend
assumetargetdefs
DDL INCLUDE MAPPED
DDLERROR DEFAULT IGNORE RETRYOP
map sender.*, target sender.*;
GGSCI (win7 as receiver@target) 48> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:00 00:00:08
REPLICAT RUNNING REP1 00:00:00 00:00:04
GGSCI (win7 as receiver@target) 53> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:01:51 00:00:04
REPLICAT RUNNING REP1 00:00:00 00:00:09
GGSCI (win7 as receiver@target) 54> add extract ext2, tranlog, begin now
EXTRACT added.
GGSCI (win7 as receiver@target) 55> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:00 00:00:10
EXTRACT STOPPED EXT2 00:00:00 00:00:03
REPLICAT RUNNING REP1 00:00:00 00:00:06
GGSCI (win7 as receiver@target) 56> add exttrail c:\ogg\dirdat\yw, extract ext2
EXTTRAIL added.
GGSCI (win7 as receiver@target) 58> edit params ext2
GGSCI (win7 as receiver@target) 75> view params ext2
extract ext2
tranlogoptions excludeuser ogg
setenv(ORACLE_SID='TARGET')
SETENV (NLS_LANG='AMERICAN_AMERICA.ZHS16GBK')
DDL INCLUDE ALL
USERID ogg@target,password oracle
RMTHOST win7, MGRPORT 7809
rmttrail C:\ogg\dirdat\yw
table sender.*;
GGSCI (win7 as receiver@target) 63> start ext2
Sending START request to MANAGER ...
EXTRACT EXT2 starting
GGSCI (win7 as receiver@target) 64> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:00 00:00:10
EXTRACT RUNNING EXT2 00:00:00 00:05:10
REPLICAT RUNNING REP1 00:00:00 00:00:09
GGSCI (win7 as receiver@target) 67> add replicat rep2, exttrail c:\ogg\dirdat\yw checkpointtable ogg.checkpoint
REPLICAT added.
GGSCI (win7 as receiver@target) 68> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:00 00:00:00
EXTRACT RUNNING EXT2 00:00:00 00:00:09
REPLICAT RUNNING REP1 00:00:00 00:00:05
REPLICAT STOPPED REP2 00:00:00 00:00:03
GGSCI (win7 as receiver@target) 69> edit params rep2
GGSCI (win7 as receiver@target) 76> view params rep2
replicat rep2
SETENV (NLS_LANG='AMERICAN_AMERICA.ZHS16GBK')
userid ogg@source, password oracle
rmthost win7
reperror default abend
assumetargetdefs
DDL INCLUDE MAPPED
DDLERROR DEFAULT IGNORE RETRYOP
map sender.*, target sender.*;
GGSCI (win7 as receiver@target) 70> start rep2
Sending START request to MANAGER ...
REPLICAT REP2 starting
GGSCI (win7 as receiver@target) 71> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:00 00:00:07
EXTRACT RUNNING EXT2 00:00:00 00:00:04
REPLICAT RUNNING REP1 00:00:00 00:00:06
REPLICAT RUNNING REP2 00:00:00 00:00:01
2. 测试双活sourcedb和targetdb dml操作截图:
测试双活sourcedb和targetdb ddl操作截图:
第二步
双活sourc和sota
1. 创建ext3/ext4/rep3/rep4
从sourcedb到sota
add extract ext3, tranlog, begin now
add exttrail c:\ogg\dirdata\zl, extract ext3
add replicat rep3, exttrail c:\ogg\dirdata\zl checkpointtable ogg.checkpoint
从sota到sourcedb
add extract ext4, tranlog, begin now
add exttrail c:\ogg\dirdata\lz, extract ext4
add replicat rep4, exttrail c:\ogg\dirdata\lz checkpointtable ogg.checkpoint
2. 测试双活sourcedb和sota dml操作截图:
3. 测试双活sourcedb和sota ddl操作截图:
第三步
双活targetdb和sota
1.创建ext5/ext6/rep5/rep6
从targetdb到sota
add extract ext5, tranlog, begin now
add exttrail c:\ogg\dirdaa\zl, extract ext5
add replicat rep5, exttrail c:\ogg\dirdaa\zl checkpointtable ogg.checkpoint
从sota到targetdb
add extract ext6, tranlog, begin now
add exttrail c:\ogg\dirdaa\lz, extract ext6
add replicat rep6, exttrail c:\ogg\dirdaa\lz checkpointtable ogg.checkpoint
2.测试双活sourcedb和targetdb dml操作截图:
测试双活sourcedb和targetdb ddl操作截图:
最终截图进程截图如下:
演示ddl 图1
演示ddl 图2
演示ddl 图3
演示dml 图1
演示dml 图2
演示dml 图3
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29267792/viewspace-1593719/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29267792/viewspace-1593719/