sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 产品DBA > Oracle GG >

【学习笔记】Oracle goldengate 10gasm to 11gR2 asm 单向复制(DDL支持)

时间:2016-12-11 19:05   来源:Oracle研究中心   作者:网络   点击:

天萃荷净 Oracle研究中心学习笔记:分享一篇关于Oracle数据库goldengate的配置文章,详细介绍goldengate 10gasm to 11gR2 asm 单向复制(DDL支持)的配置方法。

本站文章除注明转载外,均为本站原创: 转载自love wife & love life —Roger 的Oracle技术博客
本文链接地址: goldengate 学习系列1–10gasm to 11gR2 asm 单向复制(DDL支持)

Oracle Goldengate 11.2 Configuretion–10gasm to 11gR2 asm 单向复制(DDL支持)


1. 创建用户

groupadd ggs
useradd -g oinstall -G dba ggs
passwd ggs

2. 配置环境变量(源端、目标端)

---源端
export ORACLE_SID=10gasm
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export ORACLE_HOME=/home/oracle/oracle/product/10.2.0
export LD_LIBRARY_PATH=/home/ggs/ggs_home:$ORACLE_HOME/lib

--目标端
export ORACLE_SID=roger
export NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252
export ORACLE_HOME=/home/ora11g/product/11.2/db
export LD_LIBRARY_PATH=/home/ggs/ggs_home:$ORACLE_HOME/lib

3. 创建ggs相关目录

---源端
[ggs@10gasm ggs_home]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x86, 32bit (optimized), Oracle 10g on Apr 23 2012 07:06:02
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.

GGSCI (10gasm) 1>  create subdirs

Creating subdirectories under current directory /home/ggs/ggs_home

Parameter files                /home/ggs/ggs_home/dirprm: already exists
Report files                   /home/ggs/ggs_home/dirrpt: created
Checkpoint files               /home/ggs/ggs_home/dirchk: created
Process status files           /home/ggs/ggs_home/dirpcs: created
SQL script files               /home/ggs/ggs_home/dirsql: created
Database definitions files     /home/ggs/ggs_home/dirdef: created
Extract data files             /home/ggs/ggs_home/dirdat: created
Temporary files                /home/ggs/ggs_home/dirtmp: created
Stdout files                   /home/ggs/ggs_home/dirout: created

GGSCI (10gasm) 2>

---目标端

-bash-3.2$ cd /home/ggs/ggs_home/
-bash-3.2$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x86, 32bit (optimized), Oracle 11g on Apr 23 2012 08:09:25

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.

GGSCI (11gR2test) 1> create subdirs

Creating subdirectories under current directory /home/ggs/ggs_home

Parameter files                /home/ggs/ggs_home/dirprm: already exists
Report files                   /home/ggs/ggs_home/dirrpt: created
Checkpoint files               /home/ggs/ggs_home/dirchk: created
Process status files           /home/ggs/ggs_home/dirpcs: created
SQL script files               /home/ggs/ggs_home/dirsql: created
Database definitions files     /home/ggs/ggs_home/dirdef: created
Extract data files             /home/ggs/ggs_home/dirdat: created
Temporary files                /home/ggs/ggs_home/dirtmp: created
Stdout files                   /home/ggs/ggs_home/dirout: created

GGSCI (11gR2test) 2>

4. 开启源端数据库日志追加模式

[oracle@10gasm ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Aug 15 00:10:38 2012
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;

SUPPLEME
--------
NO

SQL> alter database add supplemental log data;

Database altered.

SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;

SUPPLEME
--------
YES

5. 修改源端listener.ora/tnsnames.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /home/oracle/oracle/product/10.2.0/)
      (PROGRAM = extproc)
    )
  (SID_DESC =
    (ORACLE_HOME = /home/oracle/oracle/product/10.2.0/)
    (SID_NAME = +ASM)
   )
  )

+ASM =
  (DESCRIPTION =
    (ADDRESS=(PROTOCOL=TCP)(HOST=10gasm)(PORT=1521))
    (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=+ASM)(INSTANCE_NAME=+ASM)(UR=A))
  )

6. 登录ggsci,进入db

---首先创建数据库用户

create user ggs identified by ggs;
grant connect,resource to ggs;
grant sysdba,dba to ggs;
alter user ggs TEMPORARY TABLESPACE  temp;

GGSCI (10gasm) 4> dblogin userid ggs@10gasm,password ggs
Successfully logged into database.

—-配置ddl支持

[oracle@10gasm ggs_home]$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production ON Wed Aug 15 04:09:35 2012
Copyright (c) 1982, 2005, Oracle.  ALL rights reserved.
Connected TO:
Oracle DATABASE 10g Enterprise Edition Release 10.2.0.1.0 - Production
WITH the Partitioning, OLAP AND DATA Mining options

SQL> @ marker_setup.SQL

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:ggs
Marker setup TABLE script complete, running verification script...
Please enter the name OF a schema FOR the GoldenGate DATABASE objects:
Setting schema name TO GGS

MARKER TABLE
-------------------------------
OK

MARKER SEQUENCE
-------------------------------
OK

Script complete.

SQL> @ddl_setup.SQL

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 Oracle 10g SOURCE, the system recycle bin must be disabled. FOR Oracle 11g AND later, it 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:ggs
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 GGS AS a Oracle GoldenGate schema name.
Working, please wait ...
RECYCLEBIN must be empty.
This installation will purge RECYCLEBIN FOR ALL users.
TO proceed, enter yes. TO stop installation, enter no.
Enter yes OR no:yes
DDL replication setup script complete, running verification script...
Please enter the name OF a schema FOR the GoldenGate DATABASE objects:
Setting schema name TO GGS

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 PACKAGOracleoracleplus.netE 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
------------------------------------------------------------------------------------------------------------------------
/home/oracle/admin/10gasm/udump/ggs_ddl_trace.log

Analyzing installation STATUS...


STATUS OF DDL REPLICATION
------------------------------------------------------------------------------------------------------------------------
SUCCESSFUL installation OF DDL Replication software components

Script complete.

SQL> @role_setup.SQL 

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:ggs
Wrote file role_setup_set.txt
PL/SQL PROCEDURE successfully completed.
ROLE setup script complete
GRANT this ROLE TO each USER assigned TO the EXTRACT, GGSCI, AND Manager processes, BY USING the following SQL command:
GRANT GGS_GGSUSER_ROLE TO <loggedUser>
WHERE <loggedUser> IS the USER assigned TO the GoldenGate processes.

SQL> @  /rdbms/admin/dbmspool

Package created.
GRANT succeeded.
VIEW created.
Package body created.

SQL> @ddl_pin
Enter VALUE FOR 1: ggs
PL/SQL PROCEDURE successfully completed.
Enter VALUE FOR 1: ggs
PL/SQL PROCEDRE successfully completed.
PL/SQL PROCEDURE successfully completed.

7. 配置源端

--配置mgr
edit param mgr,输入如下信息:

port 7809
DYNAMICPORTLIST 7840-7849
AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 7
PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 14

LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45

--配置抽取进程

GGSCI (10gasm) 8> edit param ext1

extract ext1
userid ggs@10gasm,password ggs
TRANLOGOPTIONS ASMUSER sys@+ASM, ASMPASSWORD oracle
discardfile  ./dirrpt/ext1.dsc, append, megabytes 50
warnlongtrans 2h, checkinterval 3m
EXTTRAIL ./dirdat/ex  
NUMFILES 3000 
ALLOCFILES 200
GETTRUNCATES            
SEQUENCE roger.*;
table roger.*;

GGSCI (10gasm) 68> add ext ext1,begin now
EXTRACT altered.

GGSCI (10gasm) 15> add exttrail  ./dirdat/ex,  extract ext1,  megabytes 100
EXTTRAIL added.

---创建对应的Data Pump进程dp1
GSCI (10gasm) 16> edit param dp1

EXTRACT dp1
RMTHOST 192.168.0.116, MGRPORT 7809 TCPBUFSIZE 5000000
PASSTHRU
RMTTRAIL  ./dirdat/r1
NUMFILES 3000

SEQUENCE roger.*;
EXTRACT dp1
RMTHOST 192.168.0.116, MGRPORT 7809 TCPBUFSIZE 5000000
PASSTHRU
RMTTRAIL  ./dirdat/rm
NUMFILES 3000

SEQUENCE roger.*;
TABLE roger.*;
~
"dirprm/dp1.prm" 8L, 152C written

GGSCI (10gasm) 17> add extract  dp1, exttrailsource ./dirdat/ex
EXTRACT added.

GGSCI (10gasm) 18>
GGSCI (10gasm) 18>  add rmttrail ./dirdat/rm, extract dp1, megabytes 10
RMTTRAIL added.

GGSCI (10gasm) 19>

8. 配置target端

将asm实例的remote_login_passwordfile参数属性修改为SHARED模式,然后重启实例。

---配置mgr

GGSCI (11gR2test) 2> dblogin userid ggs@11GR2TEST,password ggs
Successfully logged into database.
GGSCI (11gR2test) 3> edit param mgr
port 7809
dynamicportlist 7840-7850
purgeoldextracts ./dirdat/*, usecheckpoints, minkeepdays 3

----创建checkpoint table

GGSCI (11gR2test) 2> edit param ./GLOBALS

GGSCHEMA  ggs
CHECKPOINTTABLE  ggs.GGSCHKPT
~
~
"./GLOBALS" 3L, 45C written

GGSCI (11gR2test) 3> exit
-bash-3.2$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x86, 32bit (optimized), Oracle 11g on Apr 23 2012 08:09:25

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.

GGSCI (11gR2test) 1> dblogin userid ggs@11GR2TEST,password ggs
Successfully logged into database.

GGSCI (11gR2test) 2> add checkpointtable

No checkpoint table specified, using GLOBALS specification (ggs.GGSCHKPT)...
Successfully created checkpoint table ggs.GGSCHKPT.

---配置复制进程rep1

GGSCI (11gR2test) 23> view param rep1

replicat rep1
userid  ggs@11GR2TEST,password ggs
reperror default, discard
DISCARDROLLOVER AT 20:30
discardfile ./dirrpt/rep1.dsc, append, megabytes 50
assumetargetdefs    
allownoopupdates   
numfiles 3000
gettruncates
map roger.*, target roger.*;

GGSCI (11gR2test) 44> add replicat rep1,  exttrail ./dirdat/rm
REPLICAT added.

9. 启动源端和目标端的ggs,测试是否成功

---源端
GGSCI (10gasm) 50> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          
EXTRACT     RUNNING     DP1         00:00:00      00:00:01   
EXTRACT     RUNNING     EXT1        00:00:00      00:00:03   

---目标端
GGSCI (11gR2test) 24> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          
REPLICAT    RUNNING     REP1        00:00:00      00:00:05   

10. test

---源端
SQL> show user   
USER is "ROGER"
SQL> select count(*) from t;

  COUNT(*)
----------
     49745

SQL> delete from t where rownum < 101;
100 rows deleted.

SQL> commit;
Commit complete.

SQL> create table test_ogg as select * from t where rownum < 101;
Table created.

SQL> truncate table t;
Table truncated.

SQL> select count(*) from t;

  COUNT(*)
----------
         0

---目标端

SQL> conn /as sysdba
Connected.

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
roger

SQL> conn roger/roger
Connected.

SQL> select count(*) from t;

  COUNT(*)
----------
     49745

SQL> /

  COUNT(*)
----------
     49645

SQL> select count(*) from t;

  COUNT(*)
----------
         0
可以看到无法同步cats操作,truncate是可以的。还需要慢慢熟悉,找回以前的记忆。更正:经过测试,11.2是支持cats操作。误导大家了。

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle goldengate 10gasm to 11gR2 asm 单向复制(DDL支持)

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1391.html

Oracle研究中心

关键词:

Oracle Goldengate

Oracle GG

Oracle 10gasm to 11gR2 asm 单向复制(DDL支持)配置方法