goldengate 学习系列1–10gasm to 11gR2 asm 单向复制(DDL支持)
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
####### Oracle Goldengate 11.2 Configuretion–10gasm to 11gR2 asm 单向复制(DDL支持)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 |
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> |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
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支持
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 |
[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> 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 <loggedUser> where <loggedUser> 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. PL/SQL procedure successfully completed. SQL> |
7. 配置源端
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 |
--配置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端
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 |
将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，测试是否成功
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 |
---源端 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操作。误导大家了。
6 Responses to “goldengate 学习系列1–10gasm to 11gR2 asm 单向复制(DDL支持)”
这仅仅是一个开始，打算写一个系列。
roger ,不错的文章
在edit params 进程 中说明参数的作用就更好了
GGSCI (wangyu-d0cf7d41) 1> dblogin userid ggs,password ggs
ERROR: Unable to connect to database using user ggs. Please check privileges.
allocating error handle.
很受用。会经常来逛的。www.24qing.com
Leave a Reply
You must be logged in to post a comment.