Oracle_11G搭建单实例GoldenGate步骤

Oracle 11G 搭建单实例GoldenGate步骤1 GoldenGate安装准备在Oracle官网上下载GoldenGate介质，GoldenGate是包含在Fusion Middleware 类目下面，并上传到源数据库和目标数据库主机；2 GoldenGate安装过程注意：此步骤需要在两台主机上操作2.1 创建GoldenGate操作系统用户这里直接使用Oracle用户安装GoldenGate，而不创建新的用户。

2.2 创建GoldenGate安装路径[root@GMDBA ~]# mkdir -p /u01/ggs/11.2.0[root@GMDBA ~]# mv /u01/software/ogg112101_fbo_ggs_Linux_x86_ora11g_32bit.zip /u01/ggs/11.2.0/ [root@GMDBA ~]# chown -R oracle:oinstall /u01/ggs/[root@GMDBA ~]# su - oracle2.3 编辑用户环境变量[oracle@GMDBA ogg]$ vi ~/.bash_profile# .bash_profile# Get the aliases and functionsif [ -f ~/.bashrc ]; then. ~/.bashrcFi# User specific environment and startup programsPATH=$PATH:$HOME/binexport PATHexport ORACLE_SID=GMDBAexport ORACLE_BASE=/u01/app/oracleexport ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1export PATH=$ORACLE_HOME/bin:/u01/ggs/11.2.0:$PATHexport LD_LIBRARY_PATH=$ORACLE_HOME/lib:/u01/ggs/11.2.0:/libalias sqlplus='rlwrap sqlplus'alias rman='rlwrap rman'2.4 安装GoldenGate文件[oracle@GMDBA ~]$ cd /u01/ggs/11.2.0/[oracle@GMDBA 11.2.0]$ unzip ogg112101_fbo_ggs_Linux_x86_ora11g_32bit.zip Archive: ogg112101_fbo_ggs_Linux_x86_ora11g_32bit.zipinflating: fbo_ggs_Linux_x86_ora11g_32bit.tarinflating: OGG_WinUnix_Rel_Notes_11.2.1.0.1.pdfinflating: Oracle GoldenGate 11.2.1.0.1 README.txtinflating: Oracle GoldenGate 11.2.1.0.1 README.doc[oracle@GMDBA 11.2.0]$ tar -xvf fbo_ggs_Linux_x86_ora11g_32bit.tar [oracle@GMDBA 11.2.0]$ cd[oracle@GMDBA ~]$ cd /u01/ggs/11.2.0[oracle@GMDBA 11.2.0]$ ggsciOracle GoldenGate Command Interpreter for OracleVersion 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:25Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.GGSCI (GMDBA) 1> create subdirsCreating subdirectories under current directory /u01/ggs/11.2.0Parameter files /u01/ggs/11.2.0/dirprm: already exists Report files /u01/ggs/11.2.0/dirrpt: created Checkpoint files /u01/ggs/11.2.0/dirchk: created Process status files /u01/ggs/11.2.0/dirpcs: createdSQL script files /u01/ggs/11.2.0/dirsql: created Database definitions files /u01/ggs/11.2.0/dirdef: createdExtract data files /u01/ggs/11.2.0/dirdat: created Temporary files /u01/ggs/11.2.0/dirtmp: created Stdout files /u01/ggs/11.2.0/dirout: createdGGSCI (GMDBA) 2>注意：需要进入ogg的安装目录在执行ggsci[oracle@GMDBA ~]$ cd /u01/ogg/11.2.0/[oracle@GMDBA 11.2.0]$ ggsciOracle GoldenGate Command Interpreter for OracleVersion 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:25Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved. GGSCI (GMDBA) 1> helpGGSCI Command Summary:Object: Command:SUBDIRS CREATEER INFO, KILL, LAG, SEND, STATUS, START, STATS, STOP EXTRACT ADD, ALTER, CLEANUP, DELETE, INFO, KILL,LAG, REGISTER, SEND, START, STATS, STATUS, STOPUNREGISTEREXTTRAIL ADD, ALTER, DELETE, INFOGGSEVT VIEWMANAGER INFO, SEND, START, STOP, STATUSMARKER INFOPARAMS EDIT, VIEWREPLICAT ADD, ALTER, CLEANUP, DELETE, INFO, KILL, LAG, SEND,START, STATS, STATUS, STOPREPORT VIEWRMTTRAIL ADD, ALTER, DELETE, INFOTRACETABLE ADD, DELETE, INFOTRANDATA ADD, DELETE, INFOSCHEMATRANDATA ADD, DELETE, INFOCHECKPOINTTABLE ADD, DELETE, CLEANUP, INFOCommands without an object:(Database) DBLOGIN, LIST TABLES, ENCRYPT PASSWORD, FLUSH SEQUENCEMININGDBLOGIN(DDL) DUMPDDL(Miscellaneous) FC, HELP, HISTORY, INFO ALL, OBEY, SET EDITOR, SHELL,SHOW, VERSIONS, ! (note: you must type the wordCOMMAND after the !to display the ! help topic.)i.e.: GGSCI (sys1)>help !commandFor help on a specific command, type HELP .Example: HELP ADD REPLICATGGSCI (GMDBA) 2>2.5 配置源数据库2.5.1 开启归档[oracle@GMDBA ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.3.0 Production on Sun Dec 15 20:16:57 2013 Copyright (c) 1982, 2011, Oracle. All rights reserved.Connected to an idle instance.SQL> startupORACLE instance started.Total System Global Area 422670336 bytesFixed Size 1345380 bytesVariable Size 327157916 bytesDatabase Buffers 88080384 bytesRedo Buffers 6086656 bytesDatabase mounted.Database opened.SQL> show parameter log_archive_destNAME TYPE VALUE ------------------------------------ ----------- ------------------------------log_archive_dest stringlog_archive_dest_1 stringlog_archive_dest_10 stringlog_archive_dest_11 stringlog_archive_dest_12 stringlog_archive_dest_13 stringlog_archive_dest_14 stringlog_archive_dest_15 stringlog_archive_dest_16 stringlog_archive_dest_17 stringlog_archive_dest_18 stringlog_archive_dest_19 stringlog_archive_dest_2 stringlog_archive_dest_20 stringlog_archive_dest_21 stringlog_archive_dest_22 stringlog_archive_dest_23 stringlog_archive_dest_24 stringlog_archive_dest_25 stringlog_archive_dest_26 stringlog_archive_dest_27 stringlog_archive_dest_28 stringlog_archive_dest_29 stringlog_archive_dest_3 stringlog_archive_dest_30 stringlog_archive_dest_31 stringlog_archive_dest_4 stringlog_archive_dest_5 stringlog_archive_dest_6 stringlog_archive_dest_7 stringlog_archive_dest_8 stringlog_archive_dest_9 stringSQL> show parameter db_recoveryNAME TYPE VALUE------------------------------------ ----------- ------------------------------db_recovery_file_dest string /u01/app/oracle/fast_recovery_areadb_recovery_file_dest_size big integer 4977MSQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL> startup mountORACLE instance started.Total System Global Area 422670336 bytesFixed Size 1345380 bytesVariable Size 327157916 bytesDatabase Buffers 88080384 bytesRedo Buffers 6086656 bytesDatabase mounted.SQL> alter database archivelog;Database altered.SQL> alter database open;Database altered.2.5.2 打开补充日志SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;SUPPLEME--------NOSQL> alter database add supplemental log data;Database altered.SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;SUPPLEME--------YES2.5.3 创建GoldenGate管理用户SQL> create user ggs identified by ggs default tablespace users temporary tablespace temp;User created.SQL> grant connect,resource to ggs;Grant succeeded.SQL> grant execute on utl_file to ggs;Grant succeeded.SQL> grant select any dictionary,select any table to ggs;Grant succeeded.SQL> grant alter any table to ggs;Grant succeeded.SQL> grant flashback any table to ggs;Grant succeeded.SQL> grant execute on DBMS_FLASHBACK to ggs;Grant succeeded.SQL>2.5.4 添加表级trandata对hr用户下的所有表进行同步[oracle@GMDBA ggs]$ cd 11.2.0/[oracle@GMDBA 11.2.0]$ ./ggsciOracle GoldenGate Command Interpreter for OracleVersion 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:25Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.GGSCI (GMDBA) 1> dblogin userid ggs password ggsSuccessfully logged into database.GGSCI (GMDBA) 2> add trandata hr.*Logging of supplemental redo data enabled for table HR.COUNTRIES. Logging of supplemental redo data enabled for table HR.DEPARTMENTS. Logging of supplemental redo data enabled for table HR.EMPLOYEES. Logging of supplemental redo data enabled for table HR.JOBS. Logging of supplemental redo data enabled for table HR.JOB_HISTORY. Logging of supplemental redo data enabled for table HR.LOCATIONS. Logging of supplemental redo data enabled for table HR.REGIONS.GGSCI (GMDBA) 3>2.5.5 配置源端mgr管理进程组GGSCI (GMDBA) 1> edit params mgrport 7500dynamicportlist 7501-7505autorestart extract *,waitminutes 2,retries 5GGSCI (GMDBA) 2> view params mgrport 7500dynamicportlist 7501-7505autorestart extract *,waitminutes 2,retries 5GGSCI (GMDBA) 3> start mgrManager started.GGSCI (GMDBA) 4> info allProgram Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING2.5.6 配置Extract抽取进程组GGSCI (GMDBA) 6> edit params ext1extract ext1dynamicresolutionuserid ggs,password ggssetenv(ORACLE_SID=GMDBA)exttrail /u01/ggs/11.2.0/dirdat/ettable hr.*;GGSCI (GMDBA) 7> view params ext1extract ext1dynamicresolutionuserid ggs,password ggssetenv(ORACLE_SID=GMDBA)exttrail /u01/ggs/11.2.0/dirdat/ettable hr.*;创建extract进程GGSCI (GMDBA) 8> add extract ext1,tranlog,begin nowEXTRACT added.GGSCI (GMDBA) 9> add exttrail /u01/ggs/11.2.0/dirdat/et,extract EXT1 EXTTRAIL added.GGSCI (GMDBA) 10> info allProgram Status Group Lag at Chkpt Time Since ChkptMANAGER RUNNINGEXTRACT STOPPED EXT1 00:00:00 00:00:41 GGSCI (GMDBA) 11> start ext1Sending START request to MANAGER ...EXTRACT EXT1 startingGGSCI (GMDBA) 12> info allProgram Status Group Lag at Chkpt Time Since ChkptMANAGER RUNNINGEXTRACT RUNNING EXT1 00:12:29 00:00:07 2.5.7 配置pump投递进程组GGSCI (GMDBA) 13> edit params pump1extract pump1dynamicresolutionuserid ggs,password ggsrmthost 192.168.80.30,mgrport 7809,compressrmttrail /u01/ggs/11.2.0/dirdat/pttable hr.*;GGSCI (GMDBA) 14> add extract pump1,exttrailsource /u01/ggs/11.2.0/dirdat/et EXTRACT added.GGSCI (GMDBA) 15>ADD RMTTRAIL /u01/ggs/11.2.0/dirdat/pt, EXTRACT PUMP1 RMTTRAIL added.GGSCI (GMDBA) 16> start pump1Sending START request to MANAGER ...EXTRACT PUMP1 startingGGSCI (GMDBA) 17> info allProgram Status Group Lag at Chkpt Time Since ChkptMANAGER RUNNINGEXTRACT RUNNING EXT1 00:00:00 00:00:00 EXTRACT RUNNING PUMP1 00:00:00 00:04:072.6 目标端操作2.6.1 目标端用户创建SQL> create user ggs identified by ggs default tablespace users temporary tablespace temp;User created.SQL>grant connect,resource to ggs;Grant succeeded.SQL> grant execute on utl_file to ggs;Grant succeeded.SQL> grant select any table to ggs;Grant succeeded.SQL> grant insert any table to ggs;Grant succeeded.SQL> grant delete any table to ggs;Grant succeeded.SQL> grant update any table to ggs;Grant succeeded.SQL> GRANT ALTER SESSION TO ggs;Grant succeeded.SQL> GRANT SELECT ANY DICTIONARY to ggs;Grant succeeded.2.6.2 添加checkpoint表[oracle@GMDBAGC ogg]$ cd 11.2.0/[oracle@GMDBAGC 11.2.0]$ ./ggsciOracle GoldenGate Command Interpreter for OracleVersion 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:25Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.GGSCI (GMDBAGC) 1> edit params ./GLOBALScheckpointtable ggs.checkpointGGSCI (GMDBAGC) 1> dblogin userid ggs password ggsERROR: Unable to connect to database using user ggs. Please check privileges. ORA-00942: table or view does not exist.GGSCI (GMDBAGC) 2> dblogin userid ggs password ggsSuccessfully logged into database.GGSCI (GMDBAGC) 3> add checkpointtable ggs.checkpointSuccessfully created checkpoint table ggs.checkpoint.GGSCI (GMDBAGC) 4>2.6.3 配置mgrGGSCI (GMDBAGC) 1> edit params mgrport 7809dynamicportlist 7800-8000autostart er *autorestart extract *,waitminutes 2,retries 5lagreporthours 1laginfominutes 3lagcriticalminutes 5purgeoldextracts /u01/ggs/11.2.0/dirdat/rt*,usecheckpoints,minkeepdays 3 GGSCI (GMDBAGC) 2> start mgrManager started.GGSCI (GMDBAGC) 3> info allProgram Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING2.6.4 配置replicatGGSCI (GMDBAGC) 4> edit params replreplicat repluserid ggs,password ggsassumetargetdefsreperror default,discarddiscardfile /u01/ggs/11.2.0/dirrpt/repl.dsc,append,megabytes 50 dynamicresolutionmap hr.*,target hr.*;GGSCI (GMDBAGC) 5> add replicat repl,exttrail /u01/ggs/11.2.0/dirdat/pt REPLICAT added.GGSCI (GMDBAGC) 4> start replSending START request to MANAGER ...REPLICAT REPL startingGGSCI (GMDBAGC) 5> info allProgram Status Group Lag at Chkpt Time Since ChkptMANAGER RUNNINGREPLICAT RUNNING REPL 00:00:00 00:00:013 测试源库SQL> create table test (a int,b int);Table created.SQL> insert into test values(1,1);1 row created.SQL> commit;Commit complete.SQL>目标库SQL> create table test (a int,b int); Table created.SQL>SQL> select * from test;no rows selectedSQL> select * from test;A B---------- ----------1 1 SQL>。