GoldenGate12.2在DataGuard备库同步数据的配置

OGG 12.2.0.1在standby数据库上安装 ###########################软件安装配置############################ 1.设置ORACLE_HOME和ORACLE_SID(主库、备库、目标库) 如果只有一个实例，只需要设置环境变量ORACLE_HOME和ORACLE_SID即可，如果有多个实例，需要在每个进程组(process group)的配置文件中设置： 2.设置library的path,源端(主库、备库、目标库) export GG_HOME=/home/oracle/ogg export PATH=$PATH:$GG_HOME export LIBPATH=$GG_HOME:$ORACLE_HOME/lib 3.执行runinstaller(主库、备库、目标库) ./runinstaller 4.修改日志模式(主库) SELECT supplemental_log_data_min, force_logging FROM v$database; ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; ALTER DATABASE FORCE LOGGING; SELECT supplemental_log_data_min, force_logging FROM v$database; 5.在数据库中启用ogg(主库、目标库) alter system set ENABLE_GOLDENGATE_REPLICATION=true scope=both; 6.设置Flashback Query(主库和备库) UNDO_MANAGEMENT=AUTO UNDO_RETENTION=86400 #需要计算得出 8.建立GoldenGate Credentials(主库、目标库) create tablespace ogg_tbs datafile '/home/db/oracle/oradata/bidb/ogg_tbs01.dbf' size 1m autoextend on next 1m maxsize unlimited; create user ogg identified by ogg123 default tablespace ogg_tbs quota unlimited on ogg_tbs; 9.执行setup脚本(主库，目标库) @marker_setup.sql @ddl_setup.sql @role_setup.sql @ddl_enable.sql @ddl_pin ogg 10.安全相关认证(主库、备库、目标库)  $ ./ggsci 注意下面两个语句末尾不能有分号，否则报ERROR: Invalid command. GGSCI> add credentialstore GGSCI> alter credentialstore add user ogg,password ogg123 GGSCI> dblogin useridalias ogg ########################主库上添加trandata############################### 11.在DG主库上添加trandata(主库)  $ ./ggsci GGSCI> dblogin useridalias ogg GGSCI> add trandata scott.T GGSCI> info trandata scott.* #######################源端配置################################ 12 配置manager进程(备库)  $ ./ggsci GGSCI> edit params mgr 内容如下： PORT 7809 DYNAMICPORTLIST 7801-7809 --AUTORESTART ER *,RETRIES 5,WAITMINUTES 7 PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 7 LAGREPORTHOURS 1 LAGINFOMINUTES 30 LAGCRITICALMINUTES 45 13.配置抽取进程： GGSCI> add extract extncf, tranlog, begin now GGSCI> add exttrail ./dirdat/et extract extncf, megabytes 100 GGSCI> edit params extncf 内容如下： EXTRACT extncf --SETENV (ORACLE_HOME="/home/db/oracle/product/11.2.0") --setenv (NLS_LANG="AMERICAN_AMERICA.AL32UTF8") --setenv (ORACLE_SID="stby") useridalias ogg GETTRUNCATES REPORTCOUNT EVERY 1 MINUTES, RATE DISCARDFILE ./dirrpt/extncf.dsc,APPEND,MEGABYTES 1000 WARNLONGTRANS 2h,CHECKINTERVAL 10m EXTTRAIL ./dirdat/et --TRANLOGOPTIONS  CONVERTUCS2CLOBS TRANLOGOPTIONS EXCLUDEUSER ogg TRANLOGOPTIONS MINEFROMACTIVEDG DBOPTIONS ALLOWUNUSEDCOLUMN --DYNAMICRESOLUTION FETCHOPTIONS FETCHPKUPDATECOLS --table table SCOTT.T; 14.配置datapump进程 GGSCI> add extract dpencf, exttrailsource ./dirdat/et GGSCI> add rmttrail ./dirdat/rt, extract dpencf, megabytes 100 GGSCI> edit params dpencf 内容如下： EXTRACT dpencf RMTHOST 192.168.137.7, MGRPORT 7800, compress PASSTHRU RMTTRAIL ./dirdat/rt DYNAMICRESOLUTION --table table SCOTT.T; 15.启动进程  $ ./ggsci GGSCI> start mgr GGSCI> start extncf #######################目标配置################################ 16.目标端GLOBALS配置： GGSCI> edit params ./GLOBALS 内容如下： CHECKPOINTTABLE ogg.oggchkpt GGSCI> exit Shell> ggsci （重新登陆以激活GLOBALS参数） GGSCI> dblogin useridalias ogg GGSCI> add checkpointtable 17 配置目标端manager进程： GGSCI> edit params mgr 内容如下： port 7800 DYNAMICPORTLIST 7801-7809 --AUTORESTART ER *,RETRIES 5,WAITMINUTES 7 PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 7 LAGREPORTHOURS 1 LAGINFOMINUTES 30 LAGCRITICALMINUTES 45 18 配置目标端复制进程： GGSCI> add replicat repncf, exttrail ./dirdat/rt GGSCI> edit params repncf 内容如下： REPLICAT repncf SETENV (ORACLE_HOME="/home/db/oracle/product/11.2.0") setenv (NLS_LANG="AMERICAN_AMERICA.AL32UTF8") setenv (ORACLE_SID="bidb") useridalias ogg --SQLEXEC "ALTER SESSION SET CONSTRAINTS=DEFERRED" REPORT AT 01:59 REPORTCOUNT EVERY 30 MINUTES, RATE REPERROR DEFAULT, ABEND --numfiles 5000 --HANDLECOLLISIONS assumetargetdefs DISCARDFILE ./dirrpt/repncf.dsc, APPEND, MEGABYTES 1000 GETTRUNCATES ALLOWNOOPUPDATES --table map SCOTT.T, target SCOTT.T; #######################数据初始化################################ 19.源端导出数据(主库操作) SQL> select dbms_flashback.get_system_change_number from dual; GET_SYSTEM_CHANGE_NUMBER ------------------------         347494 expdp \'/ as sysdba\' directory=dump_dir dumpfile=scott.dmp logfile=scott.log tables=scott.t  flashback_scn=347494 scp scott.dmp m3:/home/oracle/dump_dir 20.目标端导入 cd /home/oracle/dump_dir impdp \'/ as sysdba\' directory=dump_dir dumpfile=scott.dmp logfile=scott.log #######################启动进程################################ 20.启动进程 源端： GGSCI> start extncf 目标端： GGSCI> alter replicat repncf extseqno 0, extrba 0 此处的extseqno后面的0代表trail文件的序号，需要到ogg根目录下的dirdat中检查，如果初始配置的话，就是0 GGSCI> start repncf aftercsn 347494