OGG如何安装

小编给大家分享一下OGG如何安装，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！1、 主库OGG安装说明：数据库版本为10.2和11.2.0.3可以用goldengate11.2版本，数据库版本为11.2.0.4和12C，需要使用goldengate12.1版本，否则ddl_setup.sql脚本报错,修改数据库goldengate相关参数，show parameter goldengate查看，改为true（主备库都要修改）。SQL> ALTER SYSTEM SET
ENABLE_GOLDENGATE_REPLICATION = TRUE SCOPE=BOTH;System altered.SQL> show parameter goldengateNAME TYPE VALUE————————————
———– —————————–enable_goldengate_replication boolean TRUE[root@prod ~]# mkdir /goldengate[root@prod ~]# chown oracle:dba
/goldengate/[root@prod ~]# chmod 775
/goldengate/[oracle@node1 ~]$ cat .bash_profileORACLE_BASE=/home/oracle/app/oracleORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_1ORACLE_SID=testPATH=$ORACLE_HOME/OPatch:$ORACLE_HOME/bin:$PATH:$HOME/binNLS_LANG=AMERICAN_AMERICA.ZHS16GBKexport PATH ORACLE_BASE ORACLE_HOME
ORACLE_SID NLS_LANGLD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib;export
LD_LIBRARY_PATH[oracle@prod goldengate]$ tar
-xvf fbo_ggs_Linux_x64_ora11g_64bit.tar[oracle@prod goldengate]$ ./ggsciOracle GoldenGate Command Interpreter for
OracleVersion 11.2.1.0.3 14400833 OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258_FBOLinux, x64, 64bit (optimized), Oracle 11g
on Aug 23 2012 20:20:21Copyright (C) 1995, 2012, Oracle and/or its
affiliates. All rights reserved.GGSCI (prod) 1> create subdirsCreating subdirectories under current
directory /goldengateParameter files /goldengate/dirprm: already
existsReport files /goldengate/dirrpt: createdCheckpoint files /goldengate/dirchk: createdProcess status files /goldengate/dirpcs: createdSQL script files /goldengate/dirsql: createdDatabase definitions files /goldengate/dirdef: createdExtract data files /goldengate/dirdat: createdTemporary files /goldengate/dirtmp: createdStdout files /goldengate/dirout: createdSQL> Select log_mode from
v$database;LOG_MODE————NOARCHIVELOGSQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL> startup mount;ORACLE instance started.Total System Global Area 488534016 bytesFixed Size 2229624 bytesVariable Size 301992584 bytesDatabase Buffers 180355072 bytesRedo Buffers 3956736 bytesDatabase mounted.SQL> alter database
archivelog;Database altered.SQL> alter database open;Database altered.使用以下sql语句检查数据库附加日志的打开状况:SQL> Select
2 SUPPLEMENTAL_LOG_DATA_MIN
3 ,SUPPLEMENTAL_LOG_DATA_PK
4 ,SUPPLEMENTAL_LOG_DATA_UI
5 ,SUPPLEMENTAL_LOG_DATA_FK
6 ,SUPPLEMENTAL_LOG_DATA_ALL from
v$database;SUPPLEME SUP SUP SUP SUP——– — — — —NO
NO NO NO NO打开附加日志并切换日志（保证Online redo
log和Archive log一致）SQL> alter database add
supplemental log data ;Database altered.SQL> alter database add
supplemental log data (primary key, unique,foreign key) columns;Database altered.SQL> alter system switch
logfile;System altered.回退操作：如果出现问题，可以通过以下语句进行回退：
alter database drop supplemental log data ;
alter database drop supplemental log data
(primary key, unique,foreign key) columns;使用以下sql语句检查数据库附加日志的打开状况:
SQL> Select 2
SUPPLEMENTAL_LOG_DATA_MIN 3
,SUPPLEMENTAL_LOG_DATA_PK 4
,SUPPLEMENTAL_LOG_DATA_UI 5
,SUPPLEMENTAL_LOG_DATA_FK 6
,SUPPLEMENTAL_LOG_DATA_ALL from v$database;SUPPLEME SUP SUP
SUP SUP——– — —
— —YES YES YES YES NO注：确保最小附加日志,pk,uk,fk附加日志打开。而all columns的附加日志关闭；
如果all columns的附加日志打开的话，则需要使用以下语句予以关闭：
alter database drop supplemental log data
(ALL) columns;SQL> Alter
database force logging;Database
altered.注：该模式的打开需要和业务部门进行相关确认和讨论；如果数据库不能打开到force logging的模式下，则no logging的表无法进行同步；
如果需要进行回退，关闭强制日志模式，请使用以下sql：
Alter database no force loggingSQL> create tablespace goldengate
datafile ‘/u01/oracle/app/oracle/oradata/ogg/goldengate01.dbf’ size 500m
autoextend on maxsize 10240m;Tablespace created.SQL> create user goldengate
identified by goldengate default tablespace goldengate;User created.SQL> grant resource, connect, dba to goldengate;Grant succeeded.查询当前recyclebin的参数值（10g需要关闭，11g不需要）：SQL> show parameter recyclebinNAME TYPE VALUE————————————
———– ——————————recyclebin string onSQL> alter system set
recyclebin=off scope=spfile;System altered.SQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL> startup;ORACLE instance started.Total System Global Area 488534016 bytesFixed Size 2229624 bytesVariable Size 306186888 bytesDatabase Buffers 176160768 bytesRedo Buffers 3956736 bytesDatabase mounted.Database opened.exportGG_HOME=/goldengate
exportLD_LIBRARY_PATH=$ORACLE_HOME/lib
以下为一些不同操作系统，对LIBRARY path 环境变量的不同名称：
IBM AIX LIBPATH
IBM z/OS LIBPATH
HP-UX SHLIB_PATH
Sun Solaris LD_LIBRARY_PATH
HP Tru64 (OSF/1) LD_LIBRARY_PATH
LINUX LD_LIBRARY_PATH[oracle@prod goldengate]$ ./ggsciOracle GoldenGate Command Interpreter for
OracleVersion 11.2.1.0.3 14400833
OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258_FBOLinux, x64, 64bit (optimized), Oracle 11g
on Aug 23 2012 20:20:21Copyright (C) 1995, 2012, Oracle and/or its
affiliates. All rights reserved.GGSCI (prod) 1> edit params
./GLOBALSGGSCHEMA goldengateCheckpointTable goldengate.ckptUnlockedTrailFilesOGG的DDL对象安装时不能有运行的sessoin存在，请DBA执行停机清理或者杀死所有数据库中的Session。
建议有条件先执行停止业务，并关闭Oracle的Listener。
注：时间大约需要30分钟左右，在这段时间内，需要完成2.9小节的所有操作步骤。此后的所有步骤，都可以在业务正常运行时进行操作。[oracle@prod goldengate]$ sqlplus
‘/as sysdba’SQL*Plus: Release 11.2.0.3.0 Production on
Mon Jan 6 14:29:24 2014Copyright (c) 1982, 2011, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition
Release 11.2.0.3.0 – 64bit ProductionWith the Partitioning, OLAP, Data Mining
and Real Application Testing optionsSQL> @marker_setup.sqlMarker setup scriptYou 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:goldengateMarker setup table script complete, running
verification script…Please enter the name of a schema for the
GoldenGate database objects:Setting schema name to GOLDENGATEMARKER TABLE——————————-OKMARKER SEQUENCE——————————-OKScript complete.SQL> @ddl_setup.sqlOracle GoldenGate DDL Replication setup
scriptVerifying 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:goldengateWorking, please wait …Spooling to file ddl_setup_spool.txtChecking for sessions that are holding
locks on Oracle Golden Gate metadata tables …Check complete.Using GOLDENGATE as a Oracle GoldenGate
schema name.Working, please wait …DDL replication setup script complete,
running verification script…Please enter the name of a schema for the
GoldenGate database objects:Setting schema name to GOLDENGATECLEAR_TRACE STATUS:Line/pos Error——————–
—————————————————————–No errors No errorsCREATE_TRACE STATUS:Line/pos Error——————–
—————————————————————–No errors No errorsTRACE_PUT_LINE STATUS:Line/pos Error——————–
—————————————————————–No errors No errorsINITIAL_SETUP STATUS:Line/pos Error——————–
—————————————————————–No errors No errorsDDLVERSIONSPECIFIC PACKAGE STATUS:Line/pos Error——————–
—————————————————————–No errors No errorsDDLREPLICATION PACKAGE STATUS:Line/pos Error——————– —————————————————————–No errors No errorsDDLREPLICATION PACKAGE BODY STATUS:Line/pos Error——————–
—————————————————————–No errors No errorsDDL IGNORE TABLE———————————–OKDDL IGNORE LOG TABLE———————————–OKDDLAUX
PACKAGE STATUS:Line/pos Error——————–
—————————————————————–No errors No errorsDDLAUX PACKAGE BODY STATUS:Line/pos Error——————–
—————————————————————–No errors No errorsSYS.DDLCTXINFO PACKAGE STATUS:Line/pos Error——————–
—————————————————————–No errors No errorsSYS.DDLCTXINFO PACKAGE BODY STATUS:Line/pos Error——————– —————————————————————–No errors No errorsDDL HISTORY TABLE———————————–OKDDL HISTORY TABLE(1)———————————–OKDDL DUMP TABLES———————————–OKDDL DUMP COLUMNS———————————–OKDDL DUMP LOG GROUPS———————————–OKDDL DUMP PARTITIONS———————-免费主机域名————-OKDDL DUMP PRIMARY KEYS———————————–OKDDL SEQUENCE———————————–OKGGS_TEMP_COLS———————————–OKGGS_TEMP_UK———————————–OKDDL TRIGGER CODE STATUS:Line/pos Error——————– —————————————————————–No errors No errorsDDL TRIGGER INSTALL STATUS———————————–OKDDL TRIGGER RUNNING STATUS———————————————————————-ENABLEDSTAYMETADATA IN TRIGGER———————————————————————-OFFDDL TRIGGER SQL TRACING———————————————————————-0DDL TRIGGER TRACE LEVEL———————————————————————-0LOCATION OF DDL TRACE FILE————————————————————————————————————————/u01/oracle/app/oracle/diag/rdbms/ogg/ogg/trace/ggs_ddl_trace.logAnalyzing installation status…STATUS OF DDL REPLICATION————————————————————————————————————————SUCCESSFUL installation of DDL Replication
software componentsScript complete.SQL> @role_setup.sqlGGS Role setup scriptThis script will drop and recreate the role
GGS_GGSUSER_ROLETo 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:goldengateWrote file role_setup_set.txtPL/SQL procedure successfully completed.Role setup script completeGrant 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> GRANT GGS_GGSUSER_ROLE TO
goldengate;Grant succeeded.SQL> @ ddl_enable.sqlTrigger altered.10G中ddl_pin.sql脚本可能报错，则运行SQL>
@?/rdbms/admin/dbmspool.sqlSQL> @ddl_pin.sql goldengatePL/SQL procedure successfully completed.PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.GGSCI (prod) 2> dblogin userid
goldengate, password goldengateSuccessfully logged into database.GGSCI (prod) 3> add
checkpointtable ckptSuccessfully created checkpoint table ckpt.查询不支持的列数据类型：select *
from dba_tab_columnswhere data_type in (‘ANYDATA’, ‘ANYDATASET’,
‘ANYTYPE’, ‘BFILE’,
‘BINARY_INTEGER’, ‘MLSLABEL’, ‘ORDDICOM’, ‘PLS_INTEGER’,
‘TIMEZONE_ABBR’, ‘URITYPE’, ‘UROWID’)
and owner in
(‘YS’,’JTZC1′,’IUFO57′,’JTIUFO’,’JTCWBB’,’ZJCS’,’CESHI’,’JTJT’,’IUFO’);查询单列表不支持的列数据类型：select *
from dba_tab_columnswhere table_name in
(select table_name
from (select owner, table_name, count(column_name) from dba_tab_columns group by owner, table_name having count(column_name) = 1
and owner in(‘YS’,’JTZC1′,’IUFO57′,’JTIUFO’,’JTCWBB’,’ZJCS’,’CESHI’,’JTJT’,’IUFO’)))
and data_type in (‘CLOB’,’BLOB’,’NCLOB’, ‘LONG’,’BFILE’, ‘Nested table’,
‘User defineddata type’,
‘VARRAY’, ‘XML’)GGSCI (prod) 4> ADD TRANDATA
scott.*注：如果因为业务问题，开启DDL以后，对性能的影响比较大的话，需要临时禁用DDL触发器的话，可以运行以下语句：
SQL> @ ddl_disable.sql此时可开启业务设置TNSASM = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =
node1-vip)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST =
node2-vip)(PORT = 1521)) (LOAD_BALANCE = yes) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = +ASM) (UR=A) ) )GGSCI (prod) 1> edit params
mgrPort 7809userid goldengate , password goldengateDYNAMICPORTLIST 9101-9356CheckMinutes 10PurgeOldExtracts ./dirdat/*,
UseCheckpoints, MinKeepHours 96PurgeMarkerHistory MinKeepDays 3,
MaxKeepDays 7, FrequencyMinutes 120AutoRestart extract *, WaitMinutes 5,
Retries 3LagInfoMinutes 0LagReportMinutes 10GGSCI (prod) 5> add extract
ext01, tranlog , begin now （, threads 2）–rac环境需要括号内容EXTRACT added.GGSCI (prod) 6> add exttrail
./dirdat/me , extract ext01 , megabytes 200EXTTRAIL added.GGSCI (prod) 7> edit params
ext01extract ext01SETENV (ORACLE_HOME=”/u01/oracle/app/oracle/product/11.2.0/dbhome_1″
)SETENV (ORACLE_SID=”ogg”)SETENV (NLS_LANG
=”AMERICAN_AMERICA.ZHS16GBK”)–TRANLOGOPTIONS EXCLUDEUSER –CacheMgr CacheDirectory ./dirtmp 51980MB,
CacheDirectory ./dirtmp/goldengate_tmpuserid goldengate , password goldengate–TranLogOptions ExcludeUser goldengate (双向复制使用)–TranLogOptions AltArchivedLogFormat
Instance NETDB1 %t_%s_%r.dbf–TranLogOptions AltArchivedLogFormat
Instance NETDB2 %t_%s_%r.dbf–TranLogOptions AltArchiveLogDest Primary
Instance NETDB1 /arch2, AltArchiveLogDest Instance NETDB2 /arch3–#tranlogoptions asmuser sys@ASM
, ASMPASSWORD oracle（rac 使用）–TRANLOGOPTIONS DBLOGREADER, DBLOGREADER
BUFSIZE [x], BUFSIZE [y]ThreadOptions MaxCommitPropagationDelay
10000 IOLatency 3000exttrail ./dirdat/meDDL Include ALL–DDL INCLUDE ALL, EXCLUDE OBJNAME
“.DDLOptions AddTranData, ReportDDLOptions NoCrossRename, ReportTable scott.*;–TABLEEXCLUDE yszx_zz_jinshuiqu.TMP* (强烈建议添加排除临时表)– Prevent data looping. This is generally
used in bi-directional– configuration– TRANLOGOPTIONS EXCLUDEUSER GGSCI (prod) 8> add extract
pump01, EXTTRAILSOURCE ./dirdat/meEXTRACT added.GGSCI (prod) 9> add rmttrail
./dirdat/mr , extract pump01, megabytes 200RMTTRAIL added.GGSCI (prod)
10> edit params pump01extract pump01SETENV
(ORACLE_HOME = “/u01/oracle/app/oracle/product/11.2.0/dbhome_1″ )SETENV
(ORACLE_SID=”ogg”)passthrurmthost
10.65.9.132 , mgrport 7809rmttrail
./dirdat/mr–DISCARDFILE
, APPEND Megabytes table scott.*;在数据初始化的过程中，不需要源数据库停机。
1)源端和目标端安装OGG软件，并启动manager
2)源端配置OGG的Extract及DataPump
3)源端启动Extract进程，并且人工记录抽取进程启动的时间点，例如：“2011-05-03
11:20:55” ，将此时间点作为在下一步中查找长事务的一个判断条件；
4)长事务处理：在V$TRANSACTION中查找当前正在运行的事务（RAC环境下，查看gv$transaction），并找出相应的长事务。在执行rman备份之前，确保这些长事务已经完成，或者被kill掉（需要得到dba或者相关管理人员的确认）。
Selectstart_timefrom gv$transaction where to_date(start_time,
‘yyyy-mm-dd hh34:mi:ss’)注:通过上面的SQL语句查找到比第3歩中记录下的时间点早的事务，需要等到该事务结束，然后执行rman的备份；

GGSCI (prod) 13> start mgrManager started.GGSCI (prod) 14> info allProgram
Status Group Lag at Chkpt Time Since ChkptMANAGER
RUNNINGEXTRACT
STOPPED EXT01 00:00:00 00:42:40EXTRACT
STOPPED PUMP01 00:00:00 00:38:21GGSCI (prod) 15> start extract
ext01Sending START request to MANAGER …EXTRACT EXT01 startingGGSCI (prod) 16> info allProgram
Status Group Lag at Chkpt Time Since ChkptMANAGER
RUNNING EXTRACT
RUNNING EXT01 00:42:50 00:00:00EXTRACT
STOPPED PUMP01 00:00:00 00:38:31SQL> create
directory expdir as ‘/u01’;Directory
created.SQL> grant
read, write on directory expdir to public;Grant succeeded.SQL> select
dbms_flashback.get_system_change_number from dual;GET_SYSTEM_CHANGE_NUMBER———————— 1085365在源端OS系统中执行数据导出，导出用户名、dmp文件名自行修改，导出完成后ftp至目标端datapump目录[oracle@prod
goldengate]$ expdp system/oracle directory=expdir dumpfile=scott.dmp
schemas=scott flashback_scn=1085365[root@proddg ~]#
mkdir /goldengate[root@proddg ~]#
chown oracle:dba /goldengate/[root@proddg ~]#
chmod 775 /goldengate/[oracle@proddg
goldengate]$ tar -xvf fbo_ggs_Linux_x64_ora11g_64bit.tar[oracle@proddg goldengate]$
./ggsciOracle GoldenGate Command Interpreter for
OracleVersion 11.2.1.0.3 14400833
OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258_FBOLinux, x64, 64bit (optimized), Oracle 11g
on Aug 23 2012 20:20:21Copyright (C) 1995, 2012, Oracle and/or its
affiliates. All rights reserved.GGSCI (proddg) 1> create
subdirsCreating subdirectories under current
directory /goldengateParameter files /goldengate/dirprm: already
existsReport files /goldengate/dirrpt: createdCheckpoint files /goldengate/dirchk: createdProcess status files /goldengate/dirpcs: createdSQL script files /goldengate/dirsql: createdDatabase definitions files /goldengate/dirdef: createdExtract data files /goldengate/dirdat: createdTemporary files /goldengate/dirtmp: createdStdout files /goldengate/dirout: createdSQL> create tablespace
goldengate datafile ‘/u01/oracle/app/oracle/oradata/ogg/goldengate01.dbf’ size
500m autoextend on maxsize 10240m;Tablespace created.SQL> create user goldengate
identified by goldengate default tablespace goldengate;User created.SQL> grant resource, connect, dba to goldengate;Grant succeeded.[oracle@prod goldengate]$ ./ggsciOracle GoldenGate Command Interpreter for
OracleVersion 11.2.1.0.3 14400833 OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258_FBOLinux, x64, 64bit (optimized), Oracle 11g
on Aug 23 2012 20:20:21Copyright (C) 1995, 2012, Oracle and/or its
affiliates. All rights reserved.GGSCI (prod) 1> edit params
./GLOBALSGGSCHEMA goldengateCheckpointTable goldengate.ckptUnlockedTrailFilesSQL> @marker_setup.sqlSQL> @ddl_setupGGSCI (proddg) 4> dblogin
userid goldengate, password goldengateSuccessfully logged into database.GGSCI (proddg) 5> add
checkpointtable ckptSuccessfully created checkpoint table ckpt.GGSCI (prod) 1> edit params
mgrPort 7809userid goldengate , password goldengateDYNAMICPORTLIST 9101-9356CheckMinutes 10PurgeOldExtracts ./dirdat/*,
UseCheckpoints, MinKeepHours 96PurgeMarkerHistory MinKeepDays 3,
MaxKeepDays 7, FrequencyMinutes 120–AutoRestart replicat *, WaitMinutes 5,
Retries 3LagInfoMinutes 0LagReportMinutes 10GGSCI (proddg) 1> add replicat
rep01, exttrail ./dirdat/mrREPLICAT added.GGSCI (proddg) 2> edit params
rep01replicat rep01SETENV (ORACLE_HOME =
“/u01/oracle/app/oracle/product/11.2.0/dbhome_1″ )SETENV (ORACLE_SID=”ogg”)SETENV (NLS_LANG
=”AMERICAN_AMERICA.ZHS16GBK”)userid goldengate , password goldengate–HandleCollisionsAssumeTargetDefsDiscardFile ./dirrpt/rep1.dsc, APPEND
Megabytes 800 , PurgeDBOptions DeferrefConstDBOptions SuppressTriggers（11.2.0.4不支持）MaxTransOps 10000GroupTransOps 1000SQLEXEC “ALTER SESSION SET
COMMIT_WRITE = BATCH,NOWAIT”BatchSQL–DDL INCLUDE ALL, EXCLUDE OBJNAME
“.DDLOptions ReportDDLError 24344 IgnoreDDLError 4052 IgnoreDDLError 955 IgnoreDDLError 1408 IgnoreDDLError 911 Ignore–DDLError 907 Ignore–DDLError 942 IgnoreAllowNoOpUpdatesCheckSequenceValue–IGNORETRUNCATES–DEFERAPPLYINTERVAL 1 MINUTES– Sequence testgg.*, Target testgg.*MapExclude scott.SYS_EXPORT_SCHEMA* ;map scott.* , target scott.* ;SQL> create directory expdir
as ‘/u01’;Directory created.SQL> grant read, write on
directory expdir to public;Grant succeeded.[oracle@proddg u01]$ impdp
system/oracle directory=expdir dumpfile=scott.dmpdeclare
v_sql varchar2(2000);
CURSOR c_trigger IS SELECT ‘alter trigger
‘||owner||’.’||trigger_name||’ disable’ from dba_triggers where owner in
(‘EMP_HLJ’,’EPC_HLJ’,’EPSA_HLJ’,’FLOW_HLJ’,’EPM_DE_HLJ’,’XEPMA_HLJ’,’EPM_LOADC_HLJ’,’EPM_HIS_HL’,’EPM_RPT_HLJ’,’SYSODM’,’EPM_CP’,’EPM_BANK_HLJ’,’EPM_IC_HLJ’,’EPM_SEC’);
BEGIN
OPEN c_trigger;
LOOP
FETCH c_trigger INTO v_sql;
EXIT WHEN c_trigger%NOTFOUND;
execute immediate v_sql;
end loop;
close c_trigger;
end;
/declare
v_sql varchar2(2000);
CURSOR c_trigger IS SELECT ‘alter table
‘||owner||’.’||table_name||’ disable constraint ‘||constraint_name from
dba_constraints where constraint_type=’R’ and owner in
(‘EMP_HLJ’,’EPC_HLJ’,’EPSA_HLJ’,’FLOW_HLJ’,’EPM_DE_HLJ’,’XEPMA_HLJ’,’EPM_LOADC_HLJ’,’EPM_HIS_HL’,’EPM_RPT_HLJ’,’SYSODM’,’EPM_CP’,’EPM_BANK_HLJ’,’EPM_IC_HLJ’,’EPM_SEC’);
BEGIN
OPEN c_trigger;
LOOP
FETCH c_trigger INTO v_sql;
EXIT WHEN c_trigger%NOTFOUND;
execute immediate v_sql;
end loop;
close c_trigger;
end;
/其他：禁用带有DML操作的JOB
1) 在dba_jobs中，有些job/schedule，会在复制对象中产生DML的操作，必须禁用掉，否则会造成DML语句在目标端的重复执行；
2) 在dba_schedule_jobs中的有些job，会在复制对象中产生DML操作，必须禁用掉，否则会造成DML语句在目标端的重复执行；
注：在目标端恢复的时候，可以在pfile文件中将job_queue_processes设置为0，然后将带有dml的job停掉以后，再修改job_queue_processes参数至正常值，如job_queue_processes=10；主库：GGSCI (prod) 6> start extract
pump01Sending START request to MANAGER …EXTRACT PUMP01 startingGGSCI (prod) 7> info allProgram
Status Group Lag at Chkpt Time Since ChkptMANAGER
RUNNINGEXTRACT
RUNNING EXT01 00:00:00 00:00:04EXTRACT
RUNNING PUMP01 00:00:00 01:14:59备库：GGSCI (proddg) 10> start mgrManager started.GGSCI (proddg) 3> start
replicat rep01,aftercsn 1085365此处的SCN为前面expdp导出时的SCNSending START request to MANAGER …REPLICAT REP01 startingGGSCI (proddg) 2> info allProgram
Status Group Lag at Chkpt Time Since ChkptMANAGER
RUNNINGREPLICAT
RUNNING REP01 00:00:00 00:00:06目 录2、 主库配置… PAGEREF _Toc377733475 h 22.1 设置数据库归档模式… PAGEREF _Toc377733476 h 22.2 开启数据库附加日志… PAGEREF _Toc377733477 h 32.3 开启数据库强制日志模式… PAGEREF _Toc377733478 h 42.4 创建运行GoldenGate的用户… PAGEREF _Toc377733479 h 42.5 关闭数据库的recyclebin (仅实施DDL时进行配置). PAGEREF _Toc377733480 h 52.6 添加变量（以linux操作系统为例）… PAGEREF _Toc377733481 h 52.7 编辑GLOBALS参数文件… PAGEREF _Toc377733482 h 62.8 停止数据库的所有Session. PAGEREF _Toc377733483 h 62.9 建立OGG的DDL对象… PAGEREF _Toc377733484 h 62.10 编辑MGR. PAGEREF _Toc377733485 h 142.11 添加抽取/传输进程与队列文件… PAGEREF _Toc377733486 h 143、 Oracle EXPDP/IMPDP初始化方案… PAGEREF _Toc377733487 h 163.1 启动主库mgr和extract进程… PAGEREF _Toc377733488 h 163.2 创建directory用于执行数据泵操作… PAGEREF _Toc377733489 h 173.3 主库获取当前scn. PAGEREF _Toc377733490 h 173.4 主库数据导出… PAGEREF _Toc377733491 h 174、 备库配置… PAGEREF _Toc377733492 h 174.1 ogg安装… PAGEREF _Toc377733493 h 174.2 创建运行GoldenGate的用户… PAGEREF _Toc377733494 h 184.3 编辑GLOBALS参数文件和checkpointtable、ddl. PAGEREF _Toc377733495 h 194.4 编辑MGR. PAGEREF _Toc377733496 h 194.5 添加接收进程… PAGEREF _Toc377733497 h 204.6 创建目录并导入数据… PAGEREF _Toc377733498 h 214.7 修改数据库… PAGEREF _Toc377733499 h 214.7.1 禁用触发器… PAGEREF _Toc377733500 h 214.7.2 禁用外键… PAGEREF _Toc377733501 h 215、 启动主库传输进程和备库mgr、接收进程… PAGEREF _Toc377733502 h 22
说明：数据库版本为10.2和11.2.0.3可以用goldengate11.2版本，数据库版本为11.2.0.4和12C，需要使用goldengate12.1版本，否则ddl_setup.sql脚本报错,修改数据库goldengate相关参数，show parameter goldengate查看，改为true（主备库都要修改）。SQL> ALTER SYSTEM SET
ENABLE_GOLDENGATE_REPLICATION = TRUE SCOPE=BOTH;System altered.SQL> show parameter goldengateNAME TYPE VALUE————————————
———– —————————–enable_goldengate_replication boolean TRUE[root@prod ~]# mkdir /goldengate[root@prod ~]# chown oracle:dba
/goldengate/[root@prod ~]# chmod 775
/goldengate/[oracle@node1 ~]$ cat .bash_profileORACLE_BASE=/home/oracle/app/oracleORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_1ORACLE_SID=testPATH=$ORACLE_HOME/OPatch:$ORACLE_HOME/bin:$PATH:$HOME/binNLS_LANG=AMERICAN_AMERICA.ZHS16GBKexport PATH ORACLE_BASE ORACLE_HOME
ORACLE_SID NLS_LANGLD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib;export
LD_LIBRARY_PATH[oracle@prod goldengate]$ tar
-xvf fbo_ggs_Linux_x64_ora11g_64bit.tar[oracle@prod goldengate]$ ./ggsciOracle GoldenGate Command Interpreter for
OracleVersion 11.2.1.0.3 14400833 OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258_FBOLinux, x64, 64bit (optimized), Oracle 11g
on Aug 23 2012 20:20:21Copyright (C) 1995, 2012, Oracle and/or its
affiliates. All rights reserved.GGSCI (prod) 1> create subdirsCreating subdirectories under current
directory /goldengateParameter files /goldengate/dirprm: already
existsReport files /goldengate/dirrpt: createdCheckpoint files /goldengate/dirchk: createdProcess status files /goldengate/dirpcs: createdSQL script files /goldengate/dirsql: createdDatabase definitions files /goldengate/dirdef: createdExtract data files /goldengate/dirdat: createdTemporary files /goldengate/dirtmp: createdStdout files /goldengate/dirout: createdSQL> Select log_mode from
v$database;LOG_MODE————NOARCHIVELOGSQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL> startup mount;ORACLE instance started.Total System Global Area 488534016 bytesFixed Size 2229624 bytesVariable Size 301992584 bytesDatabase Buffers 180355072 bytesRedo Buffers 3956736 bytesDatabase mounted.SQL> alter database
archivelog;Database altered.SQL> alter database open;Database altered.使用以下sql语句检查数据库附加日志的打开状况:SQL> Select
2 SUPPLEMENTAL_LOG_DATA_MIN
3 ,SUPPLEMENTAL_LOG_DATA_PK
4 ,SUPPLEMENTAL_LOG_DATA_UI
5 ,SUPPLEMENTAL_LOG_DATA_FK
6 ,SUPPLEMENTAL_LOG_DATA_ALL from
v$database;SUPPLEME SUP SUP SUP SUP——– — — — —NO
NO NO NO NO打开附加日志并切换日志（保证Online redo
log和Archive log一致）SQL> alter database add
supplemental log data ;Database altered.SQL> alter database add
supplemental log data (primary key, unique,foreign key) columns;Database altered.SQL> alter system switch
logfile;System altered.回退操作：如果出现问题，可以通过以下语句进行回退：
alter database drop supplemental log data ;
alter database drop supplemental log data
(primary key, unique,foreign key) columns;使用以下sql语句检查数据库附加日志的打开状况:
SQL> Select 2
SUPPLEMENTAL_LOG_DATA_MIN 3
,SUPPLEMENTAL_LOG_DATA_PK 4
,SUPPLEMENTAL_LOG_DATA_UI 5
,SUPPLEMENTAL_LOG_DATA_FK 6
,SUPPLEMENTAL_LOG_DATA_ALL from v$database;SUPPLEME SUP SUP
SUP SUP——– — —
— —YES YES YES YES NO注：确保最小附加日志,pk,uk,fk附加日志打开。而all columns的附加日志关闭；
如果all columns的附加日志打开的话，则需要使用以下语句予以关闭：
alter database drop supplemental log data
(ALL) columns;SQL> Alter
database force logging;Database
altered.注：该模式的打开需要和业务部门进行相关确认和讨论；如果数据库不能打开到force logging的模式下，则no logging的表无法进行同步；
如果需要进行回退，关闭强制日志模式，请使用以下sql：
Alter database no force loggingSQL> create tablespace goldengate
datafile ‘/u01/oracle/app/oracle/oradata/ogg/goldengate01.dbf’ size 500m
autoextend on maxsize 10240m;Tablespace created.SQL> create user goldengate
identified by goldengate default tablespace goldengate;User created.SQL> grant resource, connect, dba to goldengate;Grant succeeded.查询当前recyclebin的参数值（10g需要关闭，11g不需要）：SQL> show parameter recyclebinNAME TYPE VALUE————————————
———– ——————————recyclebin string onSQL> alter system set
recyclebin=off scope=spfile;System altered.SQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL> startup;ORACLE instance started.Total System Global Area 488534016 bytesFixed Size 2229624 bytesVariable Size 306186888 bytesDatabase Buffers 176160768 bytesRedo Buffers 3956736 bytesDatabase mounted.Database opened.exportGG_HOME=/goldengate
exportLD_LIBRARY_PATH=$ORACLE_HOME/lib
以下为一些不同操作系统，对LIBRARY path 环境变量的不同名称：
IBM AIX LIBPATH
IBM z/OS LIBPATH
HP-UX SHLIB_PATH
Sun Solaris LD_LIBRARY_PATH
HP Tru64 (OSF/1) LD_LIBRARY_PATH
LINUX LD_LIBRARY_PATH[oracle@prod goldengate]$ ./ggsciOracle GoldenGate Command Interpreter for
OracleVersion 11.2.1.0.3 14400833
OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258_FBOLinux, x64, 64bit (optimized), Oracle 11g
on Aug 23 2012 20:20:21Copyright (C) 1995, 2012, Oracle and/or its
affiliates. All rights reserved.GGSCI (prod) 1> edit params
./GLOBALSGGSCHEMA goldengateCheckpointTable goldengate.ckptUnlockedTrailFilesOGG的DDL对象安装时不能有运行的sessoin存在，请DBA执行停机清理或者杀死所有数据库中的Session。
建议有条件先执行停止业务，并关闭Oracle的Listener。
注：时间大约需要30分钟左右，在这段时间内，需要完成2.9小节的所有操作步骤。此后的所有步骤，都可以在业务正常运行时进行操作。[oracle@prod goldengate]$ sqlplus
‘/as sysdba’SQL*Plus: Release 11.2.0.3.0 Production on
Mon Jan 6 14:29:24 2014Copyright (c) 1982, 2011, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition
Release 11.2.0.3.0 – 64bit ProductionWith the Partitioning, OLAP, Data Mining
and Real Application Testing optionsSQL> @marker_setup.sqlMarker setup scriptYou 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:goldengateMarker setup table script complete, running
verification script…Please enter the name of a schema for the
GoldenGate database objects:Setting schema name to GOLDENGATEMARKER TABLE——————————-OKMARKER SEQUENCE——————————-OKScript complete.SQL> @ddl_setup.sqlOracle GoldenGate DDL Replication setup
scriptVerifying 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:goldengateWorking, please wait …Spooling to file ddl_setup_spool.txtChecking for sessions that are holding
locks on Oracle Golden Gate metadata tables …Check complete.Using GOLDENGATE as a Oracle GoldenGate
schema name.Working, please wait …DDL replication setup script complete,
running verification script…Please enter the name of a schema for the
GoldenGate database objects:Setting schema name to GOLDENGATECLEAR_TRACE STATUS:Line/pos Error——————–
—————————————————————–No errors No errorsCREATE_TRACE STATUS:Line/pos Error——————–
—————————————————————–No errors No errorsTRACE_PUT_LINE STATUS:Line/pos Error——————–
—————————————————————–No errors No errorsINITIAL_SETUP STATUS:Line/pos Error——————–
—————————————————————–No errors No errorsDDLVERSIONSPECIFIC PACKAGE STATUS:Line/pos Error——————–
—————————————————————–No errors No errorsDDLREPLICATION PACKAGE STATUS:Line/pos Error——————– —————————————————————–No errors No errorsDDLREPLICATION PACKAGE BODY STATUS:Line/pos Error——————–
—————————————————————–No errors No errorsDDL IGNORE TABLE———————————–OKDDL IGNORE LOG TABLE———————————–OKDDLAUX
PACKAGE STATUS:Line/pos Error——————–
—————————————————————–No errors No errorsDDLAUX PACKAGE BODY STATUS:Line/pos Error——————–
—————————————————————–No errors No errorsSYS.DDLCTXINFO PACKAGE STATUS:Line/pos Error——————–
—————————————————————–No errors No errorsSYS.DDLCTXINFO PACKAGE BODY STATUS:Line/pos Error——————– —————————————————————–No errors No errorsDDL HISTORY TABLE———————————–OKDDL HISTORY TABLE(1)———————————–OKDDL DUMP TABLES———————————–OKDDL DUMP COLUMNS———————————–OKDDL DUMP LOG GROUPS———————————–OKDDL DUMP PARTITIONS———————————–OKDDL DUMP PRIMARY KEYS———————————–OKDDL SEQUENCE———————————–OKGGS_TEMP_COLS———————————–OKGGS_TEMP_UK———————————–OKDDL TRIGGER CODE STATUS:Line/pos Error——————– —————————————————————–No errors No errorsDDL TRIGGER INSTALL STATUS———————————–OKDDL TRIGGER RUNNING STATUS———————————————————————-ENABLEDSTAYMETADATA IN TRIGGER———————————————————————-OFFDDL TRIGGER SQL TRACING———————————————————————-0DDL TRIGGER TRACE LEVEL———————————————————————-0LOCATION OF DDL TRACE FILE————————————————————————————————————————/u01/oracle/app/oracle/diag/rdbms/ogg/ogg/trace/ggs_ddl_trace.logAnalyzing installation status…STATUS OF DDL REPLICATION————————————————————————————————————————SUCCESSFUL installation of DDL Replication
software componentsScript complete.SQL> @role_setup.sqlGGS Role setup scriptThis script will drop and recreate the role
GGS_GGSUSER_ROLETo 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:goldengateWrote file role_setup_set.txtPL/SQL procedure successfully completed.Role setup script completeGrant 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> GRANT GGS_GGSUSER_ROLE TO
goldengate;Grant succeeded.SQL> @ ddl_enable.sqlTrigger altered.10G中ddl_pin.sql脚本可能报错，则运行SQL>
@?/rdbms/admin/dbmspool.sqlSQL> @ddl_pin.sql goldengatePL/SQL procedure successfully completed.PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.GGSCI (prod) 2> dblogin userid
goldengate, password goldengateSuccessfully logged into database.GGSCI (prod) 3> add
checkpointtable ckptSuccessfully created checkpoint table ckpt.查询不支持的列数据类型：select *
from dba_tab_columnswhere data_type in (‘ANYDATA’, ‘ANYDATASET’,
‘ANYTYPE’, ‘BFILE’,
‘BINARY_INTEGER’, ‘MLSLABEL’, ‘ORDDICOM’, ‘PLS_INTEGER’,
‘TIMEZONE_ABBR’, ‘URITYPE’, ‘UROWID’)
and owner in
(‘YS’,’JTZC1′,’IUFO57′,’JTIUFO’,’JTCWBB’,’ZJCS’,’CESHI’,’JTJT’,’IUFO’);查询单列表不支持的列数据类型：select *
from dba_tab_columnswhere table_name in
(select table_name
from (select owner, table_name, count(column_name) from dba_tab_columns group by owner, table_name having count(column_name) = 1
and owner in(‘YS’,’JTZC1′,’IUFO57′,’JTIUFO’,’JTCWBB’,’ZJCS’,’CESHI’,’JTJT’,’IUFO’)))
and data_type in (‘CLOB’,’BLOB’,’NCLOB’, ‘LONG’,’BFILE’, ‘Nested table’,
‘User defineddata type’,
‘VARRAY’, ‘XML’)GGSCI (prod) 4> ADD TRANDATA
scott.*注：如果因为业务问题，开启DDL以后，对性能的影响比较大的话，需要临时禁用DDL触发器的话，可以运行以下语句：
SQL> @ ddl_disable.sql此时可开启业务设置TNSASM = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =
node1-vip)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST =
node2-vip)(PORT = 1521)) (LOAD_BALANCE = yes) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = +ASM) (UR=A) ) )GGSCI (prod) 1> edit params
mgrPort 7809userid goldengate , password goldengateDYNAMICPORTLIST 9101-9356CheckMinutes 10PurgeOldExtracts ./dirdat/*,
UseCheckpoints, MinKeepHours 96PurgeMarkerHistory MinKeepDays 3,
MaxKeepDays 7, FrequencyMinutes 120AutoRestart extract *, WaitMinutes 5,
Retries 3LagInfoMinutes 0LagReportMinutes 10GGSCI (prod) 5> add extract
ext01, tranlog , begin now （, threads 2）–rac环境需要括号内容EXTRACT added.GGSCI (prod) 6> add exttrail
./dirdat/me , extract ext01 , megabytes 200EXTTRAIL added.GGSCI (prod) 7> edit params
ext01extract ext01SETENV (ORACLE_HOME=”/u01/oracle/app/oracle/product/11.2.0/dbhome_1″
)SETENV (ORACLE_SID=”ogg”)SETENV (NLS_LANG
=”AMERICAN_AMERICA.ZHS16GBK”)–TRANLOGOPTIONS EXCLUDEUSER –CacheMgr CacheDirectory ./dirtmp 51980MB,
CacheDirectory免费主机域名 ./dirtmp/goldengate_tmpuserid goldengate , password goldengate–TranLogOptions ExcludeUser goldengate (双向复制使用)–TranLogOptions AltArchivedLogFormat
Instance NETDB1 %t_%s_%r.dbf–TranLogOptions AltArchivedLogFormat
Instance NETDB2 %t_%s_%r.dbf–TranLogOptions AltArchiveLogDest Primary
Instance NETDB1 /arch2, AltArchiveLogDest Instance NETDB2 /arch3–#tranlogoptions asmuser sys@ASM
, ASMPASSWORD oracle（rac 使用）–TRANLOGOPTIONS DBLOGREADER, DBLOGREADER
BUFSIZE [x], BUFSIZE [y]ThreadOptions MaxCommitPropagationDelay
10000 IOLatency 3000exttrail ./dirdat/meDDL Include ALL–DDL INCLUDE ALL, EXCLUDE OBJNAME
“.DDLOptions AddTranData, ReportDDLOptions NoCrossRename, ReportTable scott.*;–TABLEEXCLUDE yszx_zz_jinshuiqu.TMP* (强烈建议添加排除临时表)– Prevent data looping. This is generally
used in bi-directional– configuration– TRANLOGOPTIONS EXCLUDEUSER GGSCI (prod) 8> add extract
pump01, EXTTRAILSOURCE ./dirdat/meEXTRACT added.GGSCI (prod) 9> add rmttrail
./dirdat/mr , extract pump01, megabytes 200RMTTRAIL added.GGSCI (prod)
10> edit params pump01extract pump01SETENV
(ORACLE_HOME = “/u01/oracle/app/oracle/product/11.2.0/dbhome_1″ )SETENV
(ORACLE_SID=”ogg”)passthrurmthost
10.65.9.132 , mgrport 7809rmttrail
./dirdat/mr–DISCARDFILE
, APPEND Megabytes table scott.*;在数据初始化的过程中，不需要源数据库停机。
1)源端和目标端安装OGG软件，并启动manager
2)源端配置OGG的Extract及DataPump
3)源端启动Extract进程，并且人工记录抽取进程启动的时间点，例如：“2011-05-03
11:20:55” ，将此时间点作为在下一步中查找长事务的一个判断条件；
4)长事务处理：在V$TRANSACTION中查找当前正在运行的事务（RAC环境下，查看gv$transaction），并找出相应的长事务。在执行rman备份之前，确保这些长事务已经完成，或者被kill掉（需要得到dba或者相关管理人员的确认）。
Selectstart_timefrom gv$transaction where to_date(start_time,
‘yyyy-mm-dd hh34:mi:ss’)注:通过上面的SQL语句查找到比第3歩中记录下的时间点早的事务，需要等到该事务结束，然后执行rman的备份；

GGSCI (prod) 13> start mgrManager started.GGSCI (prod) 14> info allProgram
Status Group Lag at Chkpt Time Since ChkptMANAGER
RUNNINGEXTRACT
STOPPED EXT01 00:00:00 00:42:40EXTRACT
STOPPED PUMP01 00:00:00 00:38:21GGSCI (prod) 15> start extract
ext01Sending START request to MANAGER …EXTRACT EXT01 startingGGSCI (prod) 16> info allProgram
Status Group Lag at Chkpt Time Since ChkptMANAGER
RUNNING EXTRACT
RUNNING EXT01 00:42:50 00:00:00EXTRACT
STOPPED PUMP01 00:00:00 00:38:31SQL> create
directory expdir as ‘/u01’;Directory
created.SQL> grant
read, write on directory expdir to public;Grant succeeded.SQL> select
dbms_flashback.get_system_change_number from dual;GET_SYSTEM_CHANGE_NUMBER———————— 1085365在源端OS系统中执行数据导出，导出用户名、dmp文件名自行修改，导出完成后ftp至目标端datapump目录[oracle@prod
goldengate]$ expdp system/oracle directory=expdir dumpfile=scott.dmp
schemas=scott flashback_scn=1085365[root@proddg ~]#
mkdir /goldengate[root@proddg ~]#
chown oracle:dba /goldengate/[root@proddg ~]#
chmod 775 /goldengate/[oracle@proddg
goldengate]$ tar -xvf fbo_ggs_Linux_x64_ora11g_64bit.tar[oracle@proddg goldengate]$
./ggsciOracle GoldenGate Command Interpreter for
OracleVersion 11.2.1.0.3 14400833
OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258_FBOLinux, x64, 64bit (optimized), Oracle 11g
on Aug 23 2012 20:20:21Copyright (C) 1995, 2012, Oracle and/or its
affiliates. All rights reserved.GGSCI (proddg) 1> create
subdirsCreating subdirectories under current
directory /goldengateParameter files /goldengate/dirprm: already
existsReport files /goldengate/dirrpt: createdCheckpoint files /goldengate/dirchk: createdProcess status files /goldengate/dirpcs: createdSQL script files /goldengate/dirsql: createdDatabase definitions files /goldengate/dirdef: createdExtract data files /goldengate/dirdat: createdTemporary files /goldengate/dirtmp: createdStdout files /goldengate/dirout: createdSQL> create tablespace
goldengate datafile ‘/u01/oracle/app/oracle/oradata/ogg/goldengate01.dbf’ size
500m autoextend on maxsize 10240m;Tablespace created.SQL> create user goldengate
identified by goldengate default tablespace goldengate;User created.SQL> grant resource, connect, dba to goldengate;Grant succeeded.[oracle@prod goldengate]$ ./ggsciOracle GoldenGate Command Interpreter for
OracleVersion 11.2.1.0.3 14400833 OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258_FBOLinux, x64, 64bit (optimized), Oracle 11g
on Aug 23 2012 20:20:21Copyright (C) 1995, 2012, Oracle and/or its
affiliates. All rights reserved.GGSCI (prod) 1> edit params
./GLOBALSGGSCHEMA goldengateCheckpointTable goldengate.ckptUnlockedTrailFilesSQL> @marker_setup.sqlSQL> @ddl_setupGGSCI (proddg) 4> dblogin
userid goldengate, password goldengateSuccessfully logged into database.GGSCI (proddg) 5> add
checkpointtable ckptSuccessfully created checkpoint table ckpt.GGSCI (prod) 1> edit params
mgrPort 7809userid goldengate , password goldengateDYNAMICPORTLIST 9101-9356CheckMinutes 10PurgeOldExtracts ./dirdat/*,
UseCheckpoints, MinKeepHours 96PurgeMarkerHistory MinKeepDays 3,
MaxKeepDays 7, FrequencyMinutes 120–AutoRestart replicat *, WaitMinutes 5,
Retries 3LagInfoMinutes 0LagReportMinutes 10GGSCI (proddg) 1> add replicat
rep01, exttrail ./dirdat/mrREPLICAT added.GGSCI (proddg) 2> edit params
rep01replicat rep01SETENV (ORACLE_HOME =
“/u01/oracle/app/oracle/product/11.2.0/dbhome_1″ )SETENV (ORACLE_SID=”ogg”)SETENV (NLS_LANG
=”AMERICAN_AMERICA.ZHS16GBK”)userid goldengate , password goldengate–HandleCollisionsAssumeTargetDefsDiscardFile ./dirrpt/rep1.dsc, APPEND
Megabytes 800 , PurgeDBOptions DeferrefConstDBOptions SuppressTriggers（11.2.0.4不支持）MaxTransOps 10000GroupTransOps 1000SQLEXEC “ALTER SESSION SET
COMMIT_WRITE = BATCH,NOWAIT”BatchSQL–DDL INCLUDE ALL, EXCLUDE OBJNAME
“.DDLOptions ReportDDLError 24344 IgnoreDDLError 4052 IgnoreDDLError 955 IgnoreDDLError 1408 IgnoreDDLError 911 Ignore–DDLError 907 Ignore–DDLError 942 IgnoreAllowNoOpUpdatesCheckSequenceValue–IGNORETRUNCATES–DEFERAPPLYINTERVAL 1 MINUTES– Sequence testgg.*, Target testgg.*MapExclude scott.SYS_EXPORT_SCHEMA* ;map scott.* , target scott.* ;SQL> create directory expdir
as ‘/u01’;Directory created.SQL> grant read, write on
directory expdir to public;Grant succeeded.[oracle@proddg u01]$ impdp
system/oracle directory=expdir dumpfile=scott.dmpdeclare
v_sql varchar2(2000);
CURSOR c_trigger IS SELECT ‘alter trigger
‘||owner||’.’||trigger_name||’ disable’ from dba_triggers where owner in
(‘EMP_HLJ’,’EPC_HLJ’,’EPSA_HLJ’,’FLOW_HLJ’,’EPM_DE_HLJ’,’XEPMA_HLJ’,’EPM_LOADC_HLJ’,’EPM_HIS_HL’,’EPM_RPT_HLJ’,’SYSODM’,’EPM_CP’,’EPM_BANK_HLJ’,’EPM_IC_HLJ’,’EPM_SEC’);
BEGIN
OPEN c_trigger;
LOOP
FETCH c_trigger INTO v_sql;
EXIT WHEN c_trigger%NOTFOUND;
execute immediate v_sql;
end loop;
close c_trigger;
end;
/declare
v_sql varchar2(2000);
CURSOR c_trigger IS SELECT ‘alter table
‘||owner||’.’||table_name||’ disable constraint ‘||constraint_name from
dba_constraints where constraint_type=’R’ and owner in
(‘EMP_HLJ’,’EPC_HLJ’,’EPSA_HLJ’,’FLOW_HLJ’,’EPM_DE_HLJ’,’XEPMA_HLJ’,’EPM_LOADC_HLJ’,’EPM_HIS_HL’,’EPM_RPT_HLJ’,’SYSODM’,’EPM_CP’,’EPM_BANK_HLJ’,’EPM_IC_HLJ’,’EPM_SEC’);
BEGIN
OPEN c_trigger;
LOOP
FETCH c_trigger INTO v_sql;
EXIT WHEN c_trigger%NOTFOUND;
execute immediate v_sql;
end loop;
close c_trigger;
end;
/其他：禁用带有DML操作的JOB
1) 在dba_jobs中，有些job/schedule，会在复制对象中产生DML的操作，必须禁用掉，否则会造成DML语句在目标端的重复执行；
2) 在dba_schedule_jobs中的有些job，会在复制对象中产生DML操作，必须禁用掉，否则会造成DML语句在目标端的重复执行；
注：在目标端恢复的时候，可以在pfile文件中将job_queue_processes设置为0，然后将带有dml的job停掉以后，再修改job_queue_processes参数至正常值，如job_queue_processes=10；主库：GGSCI (prod) 6> start extract
pump01Sending START request to MANAGER …EXTRACT PUMP01 startingGGSCI (prod) 7> info allProgram
Status Group Lag at Chkpt Time Since ChkptMANAGER
RUNNINGEXTRACT
RUNNING EXT01 00:00:00 00:00:04EXTRACT
RUNNING PUMP01 00:00:00 01:14:59备库：GGSCI (proddg) 10> start mgrManager started.GGSCI (proddg) 3> start
replicat rep01,aftercsn 1085365此处的SCN为前面expdp导出时的SCNSending START request to MANAGER …REPLICAT REP01 startingGGSCI (proddg) 2> info allProgram
Status Group Lag at Chkpt Time Since ChkptMANAGER
RUNNINGREPLICAT
RUNNING REP01 00:00:00 00:00:06以上是“OGG如何安装”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注云技术行业资讯频道！

相关推荐: PostgreSQL执行查询时获取元组属性值实现方法是什么

本篇内容主要讲解“PostgreSQL执行查询时获取元组属性值实现方法是什么”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“PostgreSQL执行查询时获取元组属性值实现方法是什么”吧!测试数据如下：TupleTa…