溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
小編給大家分享一下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
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.
使用以下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 logging
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.
查詢當前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.
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
[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
OGG的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 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)
)
)
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
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.*;
在數據初始化的過程中，不需要源數據庫停機。
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的備份；
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
SQL> 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]$ ./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
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.
[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.
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
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.* ;
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
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;
/
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 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
說明：數據庫版本為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
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.
使用以下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 logging
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.
查詢當前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.
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
[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
OGG的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 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)
)
)
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
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.*;
在數據初始化的過程中，不需要源數據庫停機。
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的備份；
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
SQL> 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]$ ./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
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.
[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.
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
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.* ;
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
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;
/
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 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如何安裝”這篇文章的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注億速云行業資訊頻道！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
