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 goldengate
NAME 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_profile
ORACLE_BASE=/home/oracle/app/oracle
ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_1
ORACLE_SID=test
PATH=$ORACLE_HOME/OPatch:$ORACLE_HOME/bin:$PATH:$HOME/bin
NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export PATH ORACLE_BASE ORACLE_HOME ORACLE_SID NLS_LANG
LD_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]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.3 14400833 OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Aug 23 2012 20:20:21
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (prod) 1> create subdirs
Creating subdirectories under current directory /goldengate
Parameter files /goldengate/dirprm: already exists
Report files /goldengate/dirrpt: created
Checkpoint files /goldengate/dirchk: created
Process status files /goldengate/dirpcs: created
SQL script files /goldengate/dirsql: created
Database definitions files /goldengate/dirdef: created
Extract data files /goldengate/dirdat: created
Temporary files /goldengate/dirtmp: created
Stdout files /goldengate/dirout: created
2、 主库配置
2.1 设置数据库归档模式
SQL> Select log_mode from v$database;
LOG_MODE
------------
NOARCHIVELOG
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 488534016 bytes
Fixed Size 2229624 bytes
Variable Size 301992584 bytes
Database Buffers 180355072 bytes
Redo Buffers 3956736 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
2.2 开启数据库附加日志
使用以下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;
2.3 开启数据库强制日志模式
SQL> Alter database force logging;
Database altered.
注：该模式的打开需要和业务部门进行相关确认和讨论；如果数据库不能打开到force logging的模式下，则no logging的表无法进行同步；
如果需要进行回退，关闭强制日志模式，请使用以下sql：
Alter database no force logging
2.4 创建运行GoldenGate的用户
SQL> 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.
2.5 关闭数据库的recyclebin (仅实施DDL时进行配置)
查询当前recyclebin的参数值（10g需要关闭，11g不需要）：
SQL> show parameter recyclebin
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recyclebin string on
SQL> 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 bytes
Fixed Size 2229624 bytes
Variable Size 306186888 bytes
Database Buffers 176160768 bytes
Redo Buffers 3956736 bytes
Database mounted.
Database opened.
2.6 添加变量（以linux操作系统为例）
export GG_HOME=/goldengate
export LD_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
2.7 编辑GLOBALS参数文件
[oracle@prod goldengate]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.3 14400833 OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Aug 23 2012 20:20:21
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (prod) 1> edit params ./GLOBALS
GGSCHEMA goldengate
CheckpointTable goldengate.ckpt
UnlockedTrailFiles
2.8 停止数据库的所有Session
OGG的DDL对象安装时不能有运行的sessoin存在，请DBA执行停机清理或者杀死所有数据库中的Session。
建议有条件先执行停止业务，并关闭Oracle的Listener。
注：时间大约需要30分钟左右，在这段时间内，需要完成2.9小节的所有操作步骤。此后的所有步骤，都可以在业务正常运行时进行操作。
2.9 建立OGG的DDL对象、查询不支持数据类型及ASM的tns配置
[oracle@prod goldengate]$ sqlplus '/as sysdba'
SQL*Plus: Release 11.2.0.3.0 Production on Mon Jan 6 14:29:24 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing 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:goldengate
Marker setup table script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GOLDENGATE
MARKER TABLE
-------------------------------
OK
MARKER SEQUENCE
-------------------------------
OK
Script complete.
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:goldengate
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 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 GOLDENGATE
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
------------------------------------------------------------------------------------------------------------------------
/u01/oracle/app/oracle/diag/rdbms/ogg/ogg/trace/ggs_ddl_trace.log
Analyzing installation status...
STATUS OF DDL REPLICATION
------------------------------------------------------------------------------------------------------------------------
SUCCESSFUL installation of DDL Replication software components
Script complete.
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:goldengate
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> GRANT GGS_GGSUSER_ROLE TO goldengate;
Grant succeeded.
SQL> @ ddl_enable.sql
Trigger altered.
10G中ddl_pin.sql脚本可能报错，则运行
SQL> @?/rdbms/admin/dbmspool.sql
SQL> @ddl_pin.sql goldengate
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
GGSCI (prod) 2> dblogin userid goldengate, password goldengate
Successfully logged into database.
GGSCI (prod) 3> add checkpointtable ckpt
Successfully created checkpoint table ckpt.
查询不支持的列数据类型：
select *
from dba_tab_columns
where 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_columns
where 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
此时可开启业务
设置TNS
ASM =
(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)
)
)
2.10 编辑MGR
GGSCI (prod) 1> edit params mgr
Port 7809
userid goldengate , password goldengate
DYNAMICPORTLIST 9101-9356
CheckMinutes 10
PurgeOldExtracts ./dirdat/*, UseCheckpoints, MinKeepHours 96
PurgeMarkerHistory MinKeepDays 3, MaxKeepDays 7, FrequencyMinutes 120
AutoRestart extract *, WaitMinutes 5, Retries 3
LagInfoMinutes 0
LagReportMinutes 10
2.11 添加抽取/传输进程与队列文件
GGSCI (prod) 5> add extract ext01, tranlog , begin now （, threads 2）--rac环境需要括号内容
EXTRACT added.
GGSCI (prod) 6> add exttrail ./dirdat/me , extract ext01 , megabytes 200
EXTTRAIL added.
GGSCI (prod) 7> edit params ext01
extract ext01
SETENV (ORACLE_HOME="/u01/oracle/app/oracle/product/11.2.0/dbhome_1" )
SETENV (ORACLE_SID="ogg")
SETENV (NLS_LANG ="AMERICAN_AMERICA.ZHS16GBK")
--TRANLOGOPTIONS EXCLUDEUSER <Replicat username>
--CacheMgr CacheDirectory ./dirtmp 51980MB, CacheDirectory ./dirtmp/goldengate_tmp
userid 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 3000
exttrail ./dirdat/me
DDL Include ALL
--DDL INCLUDE ALL, EXCLUDE OBJNAME "<schema name>.<object name> "
DDLOptions AddTranData, Report
DDLOptions NoCrossRename, Report
Table scott.*;
--TABLEEXCLUDE yszx_zz_jinshuiqu.TMP* (强烈建议添加排除临时表)
-- Prevent data looping. This is generally used in bi-directional
-- configuration
-- TRANLOGOPTIONS EXCLUDEUSER <Replicat username>
GGSCI (prod) 8> add extract pump01, EXTTRAILSOURCE ./dirdat/me
EXTRACT added.
GGSCI (prod) 9> add rmttrail ./dirdat/mr , extract pump01, megabytes 200
RMTTRAIL added.
GGSCI (prod) 10> edit params pump01
extract pump01
SETENV (ORACLE_HOME = "/u01/oracle/app/oracle/product/11.2.0/dbhome_1" )
SETENV (ORACLE_SID="ogg")
passthru
rmthost 10.65.9.132 , mgrport 7809
rmttrail ./dirdat/mr
--DISCARDFILE <diacard file path/<discard file name.dsc>, APPEND Megabytes <n>
table scott.*;
3、 Oracle EXPDP/IMPDP初始化方案
在数据初始化的过程中，不需要源数据库停机。
1) 源端和目标端安装OGG软件，并启动manager
2) 源端配置OGG的Extract及DataPump
3) 源端启动Extract进程，并且人工记录抽取进程启动的时间点，例如：“2011-05-03
11:20:55” ，将此时间点作为在下一步中查找长事务的一个判断条件；
4) 长事务处理：在V$TRANSACTION中查找当前正在运行的事务（RAC环境下，查看gv$transaction），并找出相应的长事务。在执行rman备份之前，确保这些长事务已经完成，或者被kill掉（需要得到dba或者相关管理人员的确认）。
Select start_time from gv$transaction where to_date(start_time,
'yyyy-mm-dd hh34:mi:ss')<to_date('2011-05-03 11:20:55', 'yyyy-mm-dd
hh34:mi:ss');
注:通过上面的SQL语句查找到比第3歩中记录下的时间点早的事务，需要等到该事务结束，然后执行rman的备份；
3.1 启动主库mgr和extract进程
GGSCI (prod) 13> start mgr
Manager started.
GGSCI (prod) 14> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXT01 00:00:00 00:42:40
EXTRACT STOPPED PUMP01 00:00:00 00:38:21
GGSCI (prod) 15> start extract ext01
Sending START request to MANAGER ...
EXTRACT EXT01 starting
GGSCI (prod) 16> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT01 00:42:50 00:00:00
EXTRACT STOPPED PUMP01 00:00:00 00:38:31
3.2 创建directory用于执行数据泵操作
SQL> create directory expdir as '/u01';
Directory created.
SQL> grant read, write on directory expdir to public;
Grant succeeded.
3.3 主库获取当前scn
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
1085365
3.4 主库数据导出
在源端OS系统中执行数据导出，导出用户名、dmp文件名自行修改，导出完成后ftp至目标端datapump目录
[oracle@prod goldengate]$ expdp system/oracle directory=expdir dumpfile=scott.dmp schemas=scott flashback_scn=1085365
4、 备库配置
4.1 ogg安装
[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]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.3 14400833 OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Aug 23 2012 20:20:21
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (proddg) 1> create subdirs
Creating subdirectories under current directory /goldengate
Parameter files /goldengate/dirprm: already exists
Report files /goldengate/dirrpt: created
Checkpoint files /goldengate/dirchk: created
Process status files /goldengate/dirpcs: created
SQL script files /goldengate/dirsql: created
Database definitions files /goldengate/dirdef: created
Extract data files /goldengate/dirdat: created
Temporary files /goldengate/dirtmp: created
Stdout files /goldengate/dirout: created
4.2 创建运行GoldenGate的用户
SQL> 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.
4.3 编辑GLOBALS参数文件和checkpointtable、ddl
[oracle@prod goldengate]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.3 14400833 OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Aug 23 2012 20:20:21
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (prod) 1> edit params ./GLOBALS
GGSCHEMA goldengate
CheckpointTable goldengate.ckpt
UnlockedTrailFiles
SQL> @marker_setup.sql
SQL> @ddl_setup
GGSCI (proddg) 4> dblogin userid goldengate, password goldengate
Successfully logged into database.
GGSCI (proddg) 5> add checkpointtable ckpt
Successfully created checkpoint table ckpt.
4.4 编辑MGR
GGSCI (prod) 1> edit params mgr
Port 7809
userid goldengate , password goldengate
DYNAMICPORTLIST 9101-9356
CheckMinutes 10
PurgeOldExtracts ./dirdat/*, UseCheckpoints, MinKeepHours 96
PurgeMarkerHistory MinKeepDays 3, MaxKeepDays 7, FrequencyMinutes 120
--AutoRestart replicat *, WaitMinutes 5, Retries 3
LagInfoMinutes 0
LagReportMinutes 10
4.5 添加接收进程
GGSCI (proddg) 1> add replicat rep01, exttrail ./dirdat/mr
REPLICAT added.
GGSCI (proddg) 2> edit params rep01
replicat rep01
SETENV (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
--HandleCollisions
AssumeTargetDefs
DiscardFile ./dirrpt/rep1.dsc, APPEND Megabytes 800 , Purge
DBOptions DeferrefConst
DBOptions SuppressTriggers（11.2.0.4不支持）
MaxTransOps 10000
GroupTransOps 1000
SQLEXEC "ALTER SESSION SET COMMIT_WRITE = BATCH,NOWAIT"
BatchSQL
--DDL INCLUDE ALL, EXCLUDE OBJNAME "<schema name>.<object name> "
DDLOptions Report
DDLError 24344 Ignore
DDLError 4052 Ignore
DDLError 955 Ignore
DDLError 1408 Ignore
DDLError 911 Ignore
--DDLError 907 Ignore
--DDLError 942 Ignore
AllowNoOpUpdates
CheckSequenceValue
--IGNORETRUNCATES
--DEFERAPPLYINTERVAL 1 MINUTES
-- Sequence testgg.*, Target testgg.*
MapExclude scott.SYS_EXPORT_SCHEMA* ;
map scott.* , target scott.* ;
4.6 创建目录并导入数据
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.dmp
4.7 修改数据库
4.7.1 禁用触发器
declare
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;
/
4.7.2 禁用外键
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；
5、 启动主库传输进程和备库mgr、接收进程
主库：
GGSCI (prod) 6> start extract pump01
Sending START request to MANAGER ...
EXTRACT PUMP01 starting
GGSCI (prod) 7> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT01 00:00:00 00:00:04
EXTRACT RUNNING PUMP01 00:00:00 01:14:59
备库：
GGSCI (proddg) 10> start mgr
Manager started.
GGSCI (proddg) 3> start replicat rep01,aftercsn 1085365
此处的SCN为前面expdp导出时的SCN
Sending START request to MANAGER ...
REPLICAT REP01 starting
GGSCI (proddg) 2> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP01 00:00:00 00:00:06
目 录
2、 主库配置... PAGEREF _Toc377733475 \h 2
2.1 设置数据库归档模式... PAGEREF _Toc377733476 \h 2
2.2 开启数据库附加日志... PAGEREF _Toc377733477 \h 3
2.3 开启数据库强制日志模式... PAGEREF _Toc377733478 \h 4
2.4 创建运行GoldenGate的用户... PAGEREF _Toc377733479 \h 4
2.5 关闭数据库的recyclebin (仅实施DDL时进行配置). PAGEREF _Toc377733480 \h 5
2.6 添加变量（以linux操作系统为例）... PAGEREF _Toc377733481 \h 5
2.7 编辑GLOBALS参数文件... PAGEREF _Toc377733482 \h 6
2.8 停止数据库的所有Session. PAGEREF _Toc377733483 \h 6
2.9 建立OGG的DDL对象... PAGEREF _Toc377733484 \h 6
2.10 编辑MGR. PAGEREF _Toc377733485 \h 14
2.11 添加抽取/传输进程与队列文件... PAGEREF _Toc377733486 \h 14
3、 Oracle EXPDP/IMPDP初始化方案... PAGEREF _Toc377733487 \h 16
3.1 启动主库mgr和extract进程... PAGEREF _Toc377733488 \h 16
3.2 创建directory用于执行数据泵操作... PAGEREF _Toc377733489 \h 17
3.3 主库获取当前scn. PAGEREF _Toc377733490 \h 17
3.4 主库数据导出... PAGEREF _Toc377733491 \h 17
4、 备库配置... PAGEREF _Toc377733492 \h 17
4.1 ogg安装... PAGEREF _Toc377733493 \h 17
4.2 创建运行GoldenGate的用户... PAGEREF _Toc377733494 \h 18
4.3 编辑GLOBALS参数文件和checkpointtable、ddl. PAGEREF _Toc377733495 \h 19
4.4 编辑MGR. PAGEREF _Toc377733496 \h 19
4.5 添加接收进程... PAGEREF _Toc377733497 \h 20
4.6 创建目录并导入数据... PAGEREF _Toc377733498 \h 21
4.7 修改数据库... PAGEREF _Toc377733499 \h 21
4.7.1 禁用触发器... PAGEREF _Toc377733500 \h 21
4.7.2 禁用外键... PAGEREF _Toc377733501 \h 21
5、 启动主库传输进程和备库mgr、接收进程... PAGEREF _Toc377733502 \h 22
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 goldengate
NAME 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_profile
ORACLE_BASE=/home/oracle/app/oracle
ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_1
ORACLE_SID=test
PATH=$ORACLE_HOME/OPatch:$ORACLE_HOME/bin:$PATH:$HOME/bin
NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export PATH ORACLE_BASE ORACLE_HOME ORACLE_SID NLS_LANG
LD_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]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.3 14400833 OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Aug 23 2012 20:20:21
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (prod) 1> create subdirs
Creating subdirectories under current directory /goldengate
Parameter files /goldengate/dirprm: already exists
Report files /goldengate/dirrpt: created
Checkpoint files /goldengate/dirchk: created
Process status files /goldengate/dirpcs: created
SQL script files /goldengate/dirsql: created
Database definitions files /goldengate/dirdef: created
Extract data files /goldengate/dirdat: created
Temporary files /goldengate/dirtmp: created
Stdout files /goldengate/dirout: created
2、 主库配置
2.1 设置数据库归档模式
SQL> Select log_mode from v$database;
LOG_MODE
------------
NOARCHIVELOG
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 488534016 bytes
Fixed Size 2229624 bytes
Variable Size 301992584 bytes
Database Buffers 180355072 bytes
Redo Buffers 3956736 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
2.2 开启数据库附加日志
使用以下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;
2.3 开启数据库强制日志模式
SQL> Alter database force logging;
Database altered.
注：该模式的打开需要和业务部门进行相关确认和讨论；如果数据库不能打开到force logging的模式下，则no logging的表无法进行同步；
如果需要进行回退，关闭强制日志模式，请使用以下sql：
Alter database no force logging
2.4 创建运行GoldenGate的用户
SQL> 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.
2.5 关闭数据库的recyclebin (仅实施DDL时进行配置)
查询当前recyclebin的参数值（10g需要关闭，11g不需要）：
SQL> show parameter recyclebin
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recyclebin string on
SQL> 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 bytes
Fixed Size 2229624 bytes
Variable Size 306186888 bytes
Database Buffers 176160768 bytes
Redo Buffers 3956736 bytes
Database mounted.
Database opened.
2.6 添加变量（以linux操作系统为例）
export GG_HOME=/goldengate
export LD_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
2.7 编辑GLOBALS参数文件
[oracle@prod goldengate]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.3 14400833 OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Aug 23 2012 20:20:21
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (prod) 1> edit params ./GLOBALS
GGSCHEMA goldengate
CheckpointTable goldengate.ckpt
UnlockedTrailFiles
2.8 停止数据库的所有Session
OGG的DDL对象安装时不能有运行的sessoin存在，请DBA执行停机清理或者杀死所有数据库中的Session。
建议有条件先执行停止业务，并关闭Oracle的Listener。
注：时间大约需要30分钟左右，在这段时间内，需要完成2.9小节的所有操作步骤。此后的所有步骤，都可以在业务正常运行时进行操作。
2.9 建立OGG的DDL对象、查询不支持数据类型及ASM的tns配置
[oracle@prod goldengate]$ sqlplus '/as sysdba'
SQL*Plus: Release 11.2.0.3.0 Production on Mon Jan 6 14:29:24 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing 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:goldengate
Marker setup table script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GOLDENGATE
MARKER TABLE
-------------------------------
OK
MARKER SEQUENCE
-------------------------------
OK
Script complete.
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:goldengate
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 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 GOLDENGATE
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
------------------------------------------------------------------------------------------------------------------------
/u01/oracle/app/oracle/diag/rdbms/ogg/ogg/trace/ggs_ddl_trace.log
Analyzing installation status...
STATUS OF DDL REPLICATION
------------------------------------------------------------------------------------------------------------------------
SUCCESSFUL installation of DDL Replication software components
Script complete.
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:goldengate
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> GRANT GGS_GGSUSER_ROLE TO goldengate;
Grant succeeded.
SQL> @ ddl_enable.sql
Trigger altered.
10G中ddl_pin.sql脚本可能报错，则运行
SQL> @?/rdbms/admin/dbmspool.sql
SQL> @ddl_pin.sql goldengate
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
GGSCI (prod) 2> dblogin userid goldengate, password goldengate
Successfully logged into database.
GGSCI (prod) 3> add checkpointtable ckpt
Successfully created checkpoint table ckpt.
查询不支持的列数据类型：
select *
from dba_tab_columns
where 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_columns
where 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
此时可开启业务
设置TNS
ASM =
(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)
)
)
2.10 编辑MGR
GGSCI (prod) 1> edit params mgr
Port 7809
userid goldengate , password goldengate
DYNAMICPORTLIST 9101-9356
CheckMinutes 10
PurgeOldExtracts ./dirdat/*, UseCheckpoints, MinKeepHours 96
PurgeMarkerHistory MinKeepDays 3, MaxKeepDays 7, FrequencyMinutes 120
AutoRestart extract *, WaitMinutes 5, Retries 3
LagInfoMinutes 0
LagReportMinutes 10
2.11 添加抽取/传输进程与队列文件
GGSCI (prod) 5> add extract ext01, tranlog , begin now （, threads 2）--rac环境需要括号内容
EXTRACT added.
GGSCI (prod) 6> add exttrail ./dirdat/me , extract ext01 , megabytes 200
EXTTRAIL added.
GGSCI (prod) 7> edit params ext01
extract ext01
SETENV (ORACLE_HOME="/u01/oracle/app/oracle/product/11.2.0/dbhome_1" )
SETENV (ORACLE_SID="ogg")
SETENV (NLS_LANG ="AMERICAN_AMERICA.ZHS16GBK")
--TRANLOGOPTIONS EXCLUDEUSER <Replicat username>
--CacheMgr CacheDirectory ./dirtmp 51980MB, CacheDirectory ./dirtmp/goldengate_tmp
userid 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 3000
exttrail ./dirdat/me
DDL Include ALL
--DDL INCLUDE ALL, EXCLUDE OBJNAME "<schema name>.<object name> "
DDLOptions AddTranData, Report
DDLOptions NoCrossRename, Report
Table scott.*;
--TABLEEXCLUDE yszx_zz_jinshuiqu.TMP* (强烈建议添加排除临时表)
-- Prevent data looping. This is generally used in bi-directional
-- configuration
-- TRANLOGOPTIONS EXCLUDEUSER <Replicat username>
GGSCI (prod) 8> add extract pump01, EXTTRAILSOURCE ./dirdat/me
EXTRACT added.
GGSCI (prod) 9> add rmttrail ./dirdat/mr , extract pump01, megabytes 200
RMTTRAIL added.
GGSCI (prod) 10> edit params pump01
extract pump01
SETENV (ORACLE_HOME = "/u01/oracle/app/oracle/product/11.2.0/dbhome_1" )
SETENV (ORACLE_SID="ogg")
passthru
rmthost 10.65.9.132 , mgrport 7809
rmttrail ./dirdat/mr
--DISCARDFILE <diacard file path/<discard file name.dsc>, APPEND Megabytes <n>
table scott.*;
3、 Oracle EXPDP/IMPDP初始化方案
在数据初始化的过程中，不需要源数据库停机。
1) 源端和目标端安装OGG软件，并启动manager
2) 源端配置OGG的Extract及DataPump
3) 源端启动Extract进程，并且人工记录抽取进程启动的时间点，例如：“2011-05-03
11:20:55” ，将此时间点作为在下一步中查找长事务的一个判断条件；
4) 长事务处理：在V$TRANSACTION中查找当前正在运行的事务（RAC环境下，查看gv$transaction），并找出相应的长事务。在执行rman备份之前，确保这些长事务已经完成，或者被kill掉（需要得到dba或者相关管理人员的确认）。
Select start_time from gv$transaction where to_date(start_time,
'yyyy-mm-dd hh34:mi:ss')<to_date('2011-05-03 11:20:55', 'yyyy-mm-dd
hh34:mi:ss');
注:通过上面的SQL语句查找到比第3歩中记录下的时间点早的事务，需要等到该事务结束，然后执行rman的备份；
3.1 启动主库mgr和extract进程
GGSCI (prod) 13> start mgr
Manager started.
GGSCI (prod) 14> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXT01 00:00:00 00:42:40
EXTRACT STOPPED PUMP01 00:00:00 00:38:21
GGSCI (prod) 15> start extract ext01
Sending START request to MANAGER ...
EXTRACT EXT01 starting
GGSCI (prod) 16> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT01 00:42:50 00:00:00
EXTRACT STOPPED PUMP01 00:00:00 00:38:31
3.2 创建directory用于执行数据泵操作
SQL> create directory expdir as '/u01';
Directory created.
SQL> grant read, write on directory expdir to public;
Grant succeeded.
3.3 主库获取当前scn
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
1085365
3.4 主库数据导出
在源端OS系统中执行数据导出，导出用户名、dmp文件名自行修改，导出完成后ftp至目标端datapump目录
[oracle@prod goldengate]$ expdp system/oracle directory=expdir dumpfile=scott.dmp schemas=scott flashback_scn=1085365
4、 备库配置
4.1 ogg安装
[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]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.3 14400833 OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Aug 23 2012 20:20:21
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (proddg) 1> create subdirs
Creating subdirectories under current directory /goldengate
Parameter files /goldengate/dirprm: already exists
Report files /goldengate/dirrpt: created
Checkpoint files /goldengate/dirchk: created
Process status files /goldengate/dirpcs: created
SQL script files /goldengate/dirsql: created
Database definitions files /goldengate/dirdef: created
Extract data files /goldengate/dirdat: created
Temporary files /goldengate/dirtmp: created
Stdout files /goldengate/dirout: created
4.2 创建运行GoldenGate的用户
SQL> 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.
4.3 编辑GLOBALS参数文件和checkpointtable、ddl
[oracle@prod goldengate]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.3 14400833 OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Aug 23 2012 20:20:21
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (prod) 1> edit params ./GLOBALS
GGSCHEMA goldengate
CheckpointTable goldengate.ckpt
UnlockedTrailFiles
SQL> @marker_setup.sql
SQL> @ddl_setup
GGSCI (proddg) 4> dblogin userid goldengate, password goldengate
Successfully logged into database.
GGSCI (proddg) 5> add checkpointtable ckpt
Successfully created checkpoint table ckpt.
4.4 编辑MGR
GGSCI (prod) 1> edit params mgr
Port 7809
userid goldengate , password goldengate
DYNAMICPORTLIST 9101-9356
CheckMinutes 10
PurgeOldExtracts ./dirdat/*, UseCheckpoints, MinKeepHours 96
PurgeMarkerHistory MinKeepDays 3, MaxKeepDays 7, FrequencyMinutes 120
--AutoRestart replicat *, WaitMinutes 5, Retries 3
LagInfoMinutes 0
LagReportMinutes 10
4.5 添加接收进程
GGSCI (proddg) 1> add replicat rep01, exttrail ./dirdat/mr
REPLICAT added.
GGSCI (proddg) 2> edit params rep01
replicat rep01
SETENV (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
--HandleCollisions
AssumeTargetDefs
DiscardFile ./dirrpt/rep1.dsc, APPEND Megabytes 800 , Purge
DBOptions DeferrefConst
DBOptions SuppressTriggers（11.2.0.4不支持）
MaxTransOps 10000
GroupTransOps 1000
SQLEXEC "ALTER SESSION SET COMMIT_WRITE = BATCH,NOWAIT"
BatchSQL
--DDL INCLUDE ALL, EXCLUDE OBJNAME "<schema name>.<object name> "
DDLOptions Report
DDLError 24344 Ignore
DDLError 4052 Ignore
DDLError 955 Ignore
DDLError 1408 Ignore
DDLError 911 Ignore
--DDLError 907 Ignore
--DDLError 942 Ignore
AllowNoOpUpdates
CheckSequenceValue
--IGNORETRUNCATES
--DEFERAPPLYINTERVAL 1 MINUTES
-- Sequence testgg.*, Target testgg.*
MapExclude scott.SYS_EXPORT_SCHEMA* ;
map scott.* , target scott.* ;
4.6 创建目录并导入数据
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.dmp
4.7 修改数据库
4.7.1 禁用触发器
declare
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;
/
4.7.2 禁用外键
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；
5、 启动主库传输进程和备库mgr、接收进程
主库：
GGSCI (prod) 6> start extract pump01
Sending START request to MANAGER ...
EXTRACT PUMP01 starting
GGSCI (prod) 7> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT01 00:00:00 00:00:04
EXTRACT RUNNING PUMP01 00:00:00 01:14:59
备库：
GGSCI (proddg) 10> start mgr
Manager started.
GGSCI (proddg) 3> start replicat rep01,aftercsn 1085365
此处的SCN为前面expdp导出时的SCN
Sending START request to MANAGER ...
REPLICAT REP01 starting
GGSCI (proddg) 2> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP01 00:00:00 00:00:06
以上是“OGG如何安装”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注亿速云行业资讯频道！
免责声明：
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
![[mysql]mysql8修改root密码](https://static.528045.com/imgs/18.jpg?imageMogr2/format/webp/blur/1x0/quality/35)
