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如何安装”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注亿速云行业资讯频道！