之前写过的两篇goldengate的操作实验：
1: http://blog.csdn.net/bamuta/article/details/25703847
2: http://blog.csdn.net/bamuta/article/details/25704037
一．说明：
1. 本次要完成的OGG复制的特点
1) 异构平台：linux到windows
2) 跨版本：11.2.0.1到10.2.01
3) 支持DDL
4) 同数据：oracle到oracle
5) 不同字符集
6) 不同的schema
2. 两端环境对比
|
源端(单实例) |
目标端(单实例) |
hostname |
pera205 |
Dev_88 |
OS |
RHEL5.7_64位 |
Win2003server_64位 |
ip |
192.168.20.205 |
192.168.20.88 |
database |
11．2.0.1 |
10.2.0.1 |
ogg |
fbo_ggs_Linux_x64_ora11g_64bit.tar |
ggs_Windows_x86_ora10g_32bit.zip |
字符集 |
AL32UTF8 |
ZHS16GBK |
Schema |
Pera |
Peragg |
|
|
|
3. 关于DDL
Oracle的DDL复制本是基于数据库全局trigger来实现，而dml是基于redo,DDL和DML的复制是相互独立的，DDL复制和DML复制相互不影响。
配置ddl支持
grant execute on utl_file to ggs;
禁用recyclebin
运行必要的脚本：
sqlplus "/as sysdba"
@ marker_setup.SQL
@ddl_setup.SQL
@role_setup.SQL
@ ?/rdbms/admin/dbmspool
@ddl_pin
还需要在各个参数文件中加以下内容
DDL INCLUDE ALL
4. 清理过期的trail文件：
经过了半天时间在源端（./dirdat）和目标端（D:\oracle\product\ggs10\dirdat）分别都产生了2.5个G的trail文件。如果时间过得更久，产生的会更多，无疑对空间是个挑战。
目标端修改mgr
port 7809
PURGEOLDEXTRACTSD:\oracle\product\ggs10\dirdat\rt*, USECHECKPOINTS, MINKEEPHOURS 2
源端
PURGEOLDEXTRACTS /home/oracle/app/gg11/dirdat/ex*,USECHECKPOINTS, MINKEEPHOURS 2
二．实施过程
1---3为安装部分，源端与目标端机器均需要相同操作。
1. 操作步骤简介：
1) 源端：正常运行的生产库，生产数据均在1个用户pera下。
2) 目标端：安装数据库软件，并创建相应表空间（略）
3) 两端GoldenGate安装配置
4) 启动验证程序
5) Init load:源端以flashback_scn的方法导出pera下的所有表。并在目标端导入。
6) 目标端使用aftercsn启动replicat进程。
7) 完成ddl支持
2. 源端linux上安装OGG
1) 配置数据库
源端启用归档：
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 295
Next log sequence to archive 298
Current log sequence 298
源端启用supplementlog:
目标端可以不用打开
SQL> Select supplemental_log_data_minfrom V$database;
SQL> Alterdatabase add supplemental log data;
SQL> alter system switch logfile;
SQL> Select supplemental_log_data_minfrom V$database;
SUPPLEME
--------
YES
由于Oracle中每行记录是由rowid来唯一标识的，但是逻辑复制（如goldengate,stream等）源端和目标端数据库的数据块的结构可能完全不一样，Rowid无法定位。所以使用了supplement log后，就可以精准定位每一条记录，解决了该问题。
源端启用force log:
SQL> Select name,open_mode,force_logging,supplemental_log_data_minfrom V$database;
SQL> alter database force logging;
SQL> Select name,open_mode,force_logging,supplemental_log_data_min fromV$database;
创建管理用户:
SQL> create tablespace ggs datafile'/home/oracle/app/oradata/pera205/ggs01.dbf' size 100m;
SQL> create user ggs identified by ggsdefault tablespace ggs;
SQL> grant dba to ggs;
grant execute on utl_file to ggs;
可以不用直接给dba仅限，给仅仅需要的权限，dba用户会带来安装问题。具体参考官方文档。
禁用recyclebin
如果启用DDL 支持，必须关闭recyclebin。
Ifthe recyclebin is enabled, the Oracle GoldenGate DDL trigger session receivesimplicitrecycle bin DDL operations that cause the trigger to fail.
SQL> alter system set recyclebin=off scope=spfile;
2) 软件解压
[oracle@pera205 gg11]$ pwd
/home/oracle/app/gg11
[oracle@pera205 gg11]$ tar -xvffbo_ggs_Linux_x64_ora11g_64bit.tar
3) 环境变量：
新增：
export GG_HOME=/home/oracle/app/gg11
PATH=$GG_HOME
LD_LIBRARY_PATH=$GG_HOME:$ORACLE_HOME/lib
4) 安装GoldenGate
[oracle@pera205 gg11]$ pwd
/home/oracle/app/gg11
[oracle@pera205 gg11]$ ggsci
GGSCI (pera205) 1>create subdirs
3. 目标端windows上安装OGG
1) 配置数据库
SQL> archive log list
数据库日志模式 非存档模式
自动存档 禁用
存档终点 USE_DB_RECOVERY_FILE_DEST
最早的联机日志序列 2137
当前日志序列 2139
SQL> Select name,open_mode,force_logging,supplemental_log_data_minfrom V$database;
NAME OPEN_MODE FOR SUPPLEME
--------- ---------- --- --------
PERA READ WRITE NO NO
创建管理用户:
SQL> create tablespace ggs datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\PERA\ggs01.dbf'size 100m;
SQL> create user ggs identified by ggsdefault tablespace ggs;
SQL> grant dba to ggs;
--给golden gate数据库用户授予能执行ddl安装脚本的权限。
grant execute on utl_file to ggs;
禁用recyclebin
如果启用DDL 支持，必须关闭recyclebin。但是在目标端可以不用关闭。
SQL> alter system set recyclebin=off scope=spfile;
创建新的目标用户
SQL> create tablespace peragg datafile
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\PERA\peragg01.dbf'size 100m;
SQL> create user peragg identified by peraggdefault tablespace peragg;
SQL> grant dba to peragg;
2) 软件解压
解压到了D:\oracle\product\ggs10
3) 环境变量：
新增：
4) 安装GoldenGate
D:\oracle\product\ggs10>ggsci.exe
系统无法执行指定的程序。
--需要安装Visual C++ 2005 SP1 Redistributable Package(x86)
D:\oracle\product\ggs10>ggsci.exe
Oracle GoldenGate Command Interpreter forOracle
Version 11.1.1.1.2OGGCORE_11.1.1.1.2_PLATFORMS_111004.2100
Windows (optimized), Oracle 10g on Oct 5 2011 00:50:35
Copyright (C) 1995, 2011, Oracle and/or itsaffiliates. All rights reserved.
GGSCI (dev_88) 1> create subdirs
4. 配置GG
------下面两步两端都需要配置。
1) GLOBALS参数配置
> edit params ./globals
GGSCHEMA ggs
2) 配置mgr进程
> edit paramsmgr
port 7809
加外，为了清理过期trail文件，源端加入：
PURGEOLDEXTRACTS /home/oracle/app/gg11/dirdat/ex*,USECHECKPOINTS, MINKEEPHOURS 2
目标端加入：
PURGEOLDEXTRACTSD:\oracle\product\ggs10\dirdat\rt*, USECHECKPOINTS, MINKEEPHOURS 2
> start mgr
> info all
3) 源端配置DDL
SQL> @marker_setup.sql;
Enter GoldenGate schema name:ggs
SQL> @ddl_setup
Enter GoldenGate schema name:ggs
Enter mode of installation:INITIALSETUP
SQL> @role_setup
Enter GoldenGate schema name:ggs
SQL> @ddl_enable
Trigger altered.
SQL> @marker_status.sql
Please enter the name of a schema for theGoldenGate database objects:ggs
SQL> @?/rdbms/admin/dbmspool.sql;
SQL> @ddl_pin.sql
Enter value for 1: ggs
4) 源端 add trandata
源端创建验证表。
SQL> conn pera/pera
SQL> create table check_test
(id number,
name varchar2(30));
GGSCI (pera205) 13> dblogin userid ggs,password ggs
Successfully logged into database.
GGSCI (pera205) 14> add trandata pera.*
这一步时，部分表遇到以下问题：这个问题是缺少主键，可以忽略。
2014-05-09 17:07:19 WARNING OGG-00869 No unique key is defined for tableERM_USER_MAJOR. All viable columns will be used to represent the key, but maynot guarantee uniqueness. KEYCOLS may beused to define the key.
？？？
--下面报错针对一张临时表，忽略即可。
2014-07-2316:32:28 WARNING OGG-00706 Failed to add supplemental log group on tablePERA.RDM_MONITOR_STATISTICS_TEMP due to ORA-14450: attempt to access atransactional temp table already in use, SQL ALTER TABLE"PERA"."RDM_MONITOR_STATISTICS_TEMP" ADD SUPPLEMENTAL LOGGROUP "GGS_RDM_MONITOR_STA_144259"("TASK_ID","GROUP_ID","GROUP_NAME","GROUP_PATH","MODEL_ID","FINISHED_WB_COUNT","ALL_WB_COUNT",".
----如果一张表，其列的数量超过33列（含），且没有primary key的话，则在执行add trandata命令时，会报OGG-01387错误，这个错误需要手工修复：
2014-07-2316:32:35 WARNING OGG-01387 Table PERA.RDM_PERMISSION_DATA has no validkey columns, added unconditional supplemental log group for all table columns.
以下SQL可以检查哪些表的supplement log失败。
select owner,table_name
from all_tables
where owner in ('PERA')
and table_name not in (select distinct table_name
from dba_log_group_columns
where owner in ('PERA'));
需要手动如下操作
alter table RDM_PERMISSION_DATA add supplemental log group ggs_pera_1(col1,col2,...,col33) always;
alter table RDM_PERMISSION_DATA add supplemental log group ggs_pera_2(col34,col35...,col40) always;
5) 源端 extract 进程文件
GGSCI (pera205) 6> edit params ex1
EXTRACT ex1
SETENV (NLS_LANG = AMERICAN_AMERICA.AL32UTF8)
USERID ggs, password ggs
EXTTRAIL ./dirdat/ex
DDL INCLUDE ALL
TABLE pera.*;
GGSCI (pera205) 3> view params ex1
GGSCI (pera205) 4> add extract ex1,tranlog, begin now
EXTRACT added.
GGSCI (pera205) 5> add exttrail./dirdat/ex, extract ex1
EXTTRAIL added.
6) 源端pump 进程
GGSCI (pera205) 13> edit params dp1
EXTRACT dp1
USERID ggs,PASSWORD ggs
RMTHOST 192.168.20.88, MGRPORT 7809
RMTTRAIL D:\oracle\product\ggs10\dirdat\rt
DDL INCLUDE ALL
TABLE pera.*;
--DDL的其他写法
ddl include mapped objname pera.*;
DDL INCLUDE MAPPED
--最好别用ALL，ALL会传递过多的DDL信息，导致replication的时候容易失败。
增加源端extract目录
GGSCI (pera205) 7> add extract dp1exttrailsource ./dirdat/ex
EXTRACT added.
增加目录端trail文件位置
GGSCI (pera205) 8> add rmttrail D:\oracle\product\ggs10\dirdat\rt,extract dp1
RMTTRAIL added.
7) 目标端 CHECKPOINT TABLE
GGSCI (DEV_88) 9> edit params ./GLOBALS
GGSCHEMA ggs
CHECKPOINTTABLE ggs.ggs_checkpoint
[oracle@DEV_88 gg11]$ ggsci
GGSCI (DEV_88) 1> dblogin userid ggs,password ggs
GGSCI (DEV_88) 4> add checkpointtable
SQL> conn ggs/ggs
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ -----------------
GGS_CHECKPOINT TABLE
8) 目标端 REPLICAT进程
GGSCI (DEV_88) 7> edit params rt1
REPLICAT RT1
SETENV (NLS_LANG = AMERICAN_AMERICA.AL32UTF8)
ALLOWNOOPUPDATES
USERID ggs,PASSWORD ggs
ASSUMETARGETDEFS
DISCARDFILE ./discards.dsc, PURGE
DDL INCLUDE MAPPED
MAP pera.*, TARGET peragg.*;
GGSCI (dev_88) 4> add replicat rt1,exttrail D:\oracle\product\ggs10\dirdat\rt, checkpointtable ggs.ggs_checkpoint
REPLICAT added.
9) 源端创建验证程序：
启动验证程序
declare
begin
for i in 1..100000 loop
insert into check_test values(i,'tan');
commit;
dbms_lock.sleep(1);
end loop;
end;
/
SQL> select * from check_test;
GGSCI (pera205) 7> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED DP1 00:00:00 02:00:09
EXTRACT STOPPED EX1 00:00:00 02:02:02
GGSCI (DEV_88) 11> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
REPLICAT STOPPED RT1 00:00:00 00:20:40
1. 同步数据
1) 源端启动extract,pump进程
GGSCI (pera205) 8> start ex1
GGSCI (pera205) 14> start dp1
GGSCI (pera205) 15> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DP1 00:00:00 02:03:02
EXTRACT RUNNING EX1 00:00:00 00:00:03
此时先不用启动目标端的replicat进程。
2) 使用exp/imp完成init load
用带FLASHBACK_SCN 的expdp/impdp初始化
SQL> select current_scn from V$database;
CURRENT_SCN
-----------
588375128
exp system/oracle@pera205file=pera.20140724.dmp log=gg.log owner=pera flashback_scn= 588375128 compress=n buffer=40960000
imp system/oracle file=pera.20140724.dmplog=imp.log fromuser=pera touser=peragg
3) 用SCN 启动Replicat
GGSCI (DEV_88) 28> start rt1, aftercsn 588375128
Sending START request to MANAGER ...
REPLICAT RT1 starting
GGSCI (DEV_88) 29> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING RT1 00:00:00 00:00:04
2. 验证：
DML验证 通过
测试了数据传递、都能通过。（是通过前面创建的表check_test验证的，验证完后删除）
DDL验证 通过
对表测试了create 、alter 、truncate、drop，都能正确传递到目标端。
字符集验证通过
操作过程：源端插入汉字，查看字符编码，在同步到目标端后，在目标端查看字符编码再查看字符编码。
源端验证：
SQL> select dump('谭',1016) fromdual;
Typ=96 Len=3 CharacterSet=AL32UTF8:e8,b0,ad
SQL> select xid, dump(xname,1016) dumpfrom checks;
Typ=1 Len=3 CharacterSet=AL32UTF8: e8,b0,ad
目标库字符正确的编码
SQL> select dump('谭',1016) from dual;
DUMP('谭',1016)
-----------------------------------------
Typ=96 Len=2 CharacterSet=ZHS16GBK: cc,b7
目标库验证传输过来的汉字：
select xid, dump(xname,1016) dump fromchecks;
Typ=1 Len=2 CharacterSet=ZHS16GBK: cc,b7