goldengate 学习系列1--10gasm to 11gR2 asm 单向复制(DDL支持)

原创 Roger 2012-08-15

582

####### 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

SQL>


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.

GGSCI (10gasm) 5> exit

----配置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

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>
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 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
------------------------------------------------------------------------------------------------------------------------
/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>
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

where is the user assigned to the GoldenGate processes.
SQL>
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 procedure successfully completed.

SQL>

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.

GGSCI (11gR2test) 3> 

---配置复制进程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.


GGSCI (11gR2test) 5>

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> 
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> 
SQL> /

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

SQL> select count(*) from t;

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

~~可以看到无法同步cats操作，truncate是可以的。还需要慢慢熟悉，找回以前的记忆。~~

更正：经过测试，11.2是支持cats操作。误导大家了。

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

goldengate 学习系列1--10gasm to 11gR2 asm 单向复制(DDL支持)

评论