Oracle_11G搭建单实例GoldenGate步骤.docx

Oracle 11G 搭建单实例GoldenGate步骤 1GoldenGate安装准备在Oracle官网上下载GoldenGate介质，GoldenGate是包含在Fusion Middleware类目下面，并上传到源数据库和目标数据库主机；2GoldenGate安装过程注意：此步骤需要在两台主机上操作2.1创建GoldenGate操作系统用户这里直接使用Oracle用户安装GoldenGate，而不创建新的用户。2.2创建GoldenGate安装路径rootGMDBA # mkdir -p /u01/ggs/11.2.0rootGMDBA # mv /u01/software/ogg112101_fbo_ggs_Linux_x86_ora11g_32bit.zip /u01/ggs/11.2.0/rootGMDBA # chown -R oracle:oinstall /u01/ggs/rootGMDBA # su - oracle2.3编辑用户环境变量oracleGMDBA 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 sqlplusalias rman=rlwrap rman2.4安装GoldenGate文件oracleGMDBA $ cd /u01/ggs/11.2.0/oracleGMDBA 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.tar inflating: OGG_WinUnix_Rel_Notes_.1.pdf inflating: Oracle GoldenGate .1 README.txt inflating: Oracle GoldenGate .1 README.doc oracleGMDBA 11.2.0$ tar -xvf fbo_ggs_Linux_x86_ora11g_32bit.taroracleGMDBA 11.2.0$ cdoracleGMDBA $ cd /u01/ggs/11.2.0oracleGMDBA 11.2.0$ ggsciOracle GoldenGate Command Interpreter for OracleVersion .1 OGGCORE_.1_PLATFORMS_120423.0230_FBOLinux, 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 existsReport files /u01/ggs/11.2.0/dirrpt: createdCheckpoint files /u01/ggs/11.2.0/dirchk: createdProcess status files /u01/ggs/11.2.0/dirpcs: createdSQL script files /u01/ggs/11.2.0/dirsql: createdDatabase definitions files /u01/ggs/11.2.0/dirdef: createdExtract data files /u01/ggs/11.2.0/dirdat: createdTemporary files /u01/ggs/11.2.0/dirtmp: createdStdout files /u01/ggs/11.2.0/dirout: createdGGSCI (GMDBA) 2注意：需要进入ogg的安装目录在执行ggscioracleGMDBA $ cd /u01/ogg/11.2.0/oracleGMDBA 11.2.0$ ggsciOracle GoldenGate Command Interpreter for OracleVersion .1 OGGCORE_.1_PLATFORMS_120423.0230_FBOLinux, 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 CREATE ER INFO, KILL, LAG, SEND, STATUS, START, STATS, STOP EXTRACT ADD, ALTER, CLEANUP, DELETE, INFO, KILL, LAG, REGISTER, SEND, START, STATS, STATUS, STOP UNREGISTER EXTTRAIL ADD, ALTER, DELETE, INFO GGSEVT VIEW MANAGER INFO, SEND, START, STOP, STATUS MARKER INFO PARAMS EDIT, VIEW REPLICAT ADD, ALTER, CLEANUP, DELETE, INFO, KILL, LAG, SEND, START, STATS, STATUS, STOP REPORT VIEW RMTTRAIL ADD, ALTER, DELETE, INFO TRACETABLE ADD, DELETE, INFO TRANDATA ADD, DELETE, INFO SCHEMATRANDATA ADD, DELETE, INFO CHECKPOINTTABLE ADD, DELETE, CLEANUP, INFO Commands without an object: (Database) DBLOGIN, LIST TABLES, ENCRYPT PASSWORD, FLUSH SEQUENCE MININGDBLOGIN (DDL) DUMPDDL (Miscellaneous) FC, HELP, HISTORY, INFO ALL, OBEY, SET EDITOR, SHELL, SHOW, VERSIONS, ! (note: you must type the word COMMAND 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) 22.5配置源数据库2.5.1开启归档oracleGMDBA $ sqlplus / as sysdbaSQL*Plus: Release .0 Production on Sun Dec 15 20:16:57 2013Copyright (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.SQL2.5.4添加表级trandata对hr用户下的所有表进行同步oracleGMDBA ggs$ cd 11.2.0/oracleGMDBA 11.2.0$ ./ggsci Oracle GoldenGate Command Interpreter for OracleVersion .1 OGGCORE_.1_PLATFORMS_120423.0230_FBOLinux, 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) 32.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 ChkptMANAGER RUNNING 2.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 EXT1EXTTRAIL added.GGSCI (GMDBA) 10 info allProgram Status Group Lag at Chkpt Time Since ChkptMANAGER RUNNING EXTRACT 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 RUNNING EXTRACT RUNNING EXT1 00:12:29 00:00:07 2.5.7配置pump投递进程组GGSCI (GMDBA) 13 edit params pump1extract pump1 dynamicresolutionuserid ggs,password ggsrmthost 0,mgrport 7809,compressrmttrail /u01/ggs/11.2.0/dirdat/pttable hr.*;GGSCI (GMDBA) 14 add extract pump1,exttrailsource /u01/ggs/11.2.0/dirdat/etEXTRACT added.GGSCI (GMDBA) 15ADD RMTTRAIL /u01/ggs/11.2.0/dirdat/pt, EXTRACT PUMP1RMTTRAIL 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 RUNNING EXTRACT RUNNING EXT1 00:00:00 00:00:00 EXTRACT RUNNING PUMP1 00:00:00 00:04:07 2.6目标端操作2.6.1目标端用户创建SQL create user ggs identified by ggs default tablespace users temporary tablespace temp;User created.SQLgrant 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表oracleGMDBAGC ogg$ cd 11.2.0/oracleGMDBAGC 11.2.0$ ./ggsci Oracle GoldenGate Command Interpreter for OracleVersion .1 OGGCORE_.1_PLATFORMS_120423.0230_FBOLinux, 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) 42.6.3配置mgrGGSCI (GMDBAGC) 1 edit params mgrport 7809dynamicp