Oracle Golden Gate 双向复制配置

Oracle Golden Gate 双向复制配置

Oracle goldengate bi-directional active-active configure
 
一、golden gate 软件安装，建立安装目录后，在该目录下解压即可。（在源端和目标端都执行）
注意：建议用oracle 用户安装，设置oralce用户的环境变量：
LD_LIBRARY_PATH=/usr/lib:/usr/X11R6/lib:$ORACLE_GOLDENGATE:$ORACLE_HOME/lib
LD_LIBRARY_PATH 中必须有 $ORACLE_HOME/lib 才能执行./ggsci ，否则回报缺少动态库文件的错误：
但是这种情况下oracle 数据库运行没有问题。
[oracle@haozg goldengate]$ ./ggsci
./ggsci: error while loading shared libraries: libclntsh.so.11.1: cannot open shared object file:
No such file or directory
原因是：goldengate 在运行时需要oralce 的动态库文件。所以需要把oracle的动态库文件放到共享库中，
赋值给环境变量LD_LIBRARY_PATH，ogg 的官方文档是这样描述：
Make certain that the database libraries are added to the shared-library environment variables of the system。
--ogg官方文档上的安装步骤
Installing Oracle GoldenGate on Linux and UNIX
Follow these steps to install Oracle GoldenGate for Oracle on a Linux or UNIX system 。
Installing the Oracle GoldenGate files
1. Extract the Oracle GoldenGate mediapack.zip file to the system and directory where you
   want Oracle GoldenGate to be installed.
2. Run the command shell.
   ./ggsci
3、In GGSCI, issue the following command to create the Oracle GoldenGate working directories.
   CREATE SUBDIRS
4、Issue the following command to exit GGSCI.
   EXIT
 
 
二、创建goldengate 用户（在源端和目标端都执行）
说明：为goldengate软件创建数据库用户，为了不影响生产环境，此用户用来安装存放一些复制软件自身用到的procedure、table等，
      也就是搭建ddl复制环境用到的一些表，过程等。该用户需要有一定的权限。---ddl objects 都在这个用户下。
      Oracle GoldenGate schema   --ogg 官网上的称呼
注意：该用户在官网上也称之外复制用户：     replicate user
1、创建 ogg schema
SQL> create user ogg identified by ogg;
User created.
2、对其授权
SQL> grant connect,resource,dba to ogg;
Grant succeeded.
SQL> GRANT EXECUTE ON utl_file TO ogg;
Grant succeeded.
SQL> !pwd  
/oracle/goldengate
===========配置思路是：先配置主端到灾备端的单向复制，然后再配置从灾备端到主端的单向复制，从而实现双向复制============
三  先执行从主端到灾备端的单向复制配置
------------------------------主端的配置---------------------------------------------------
---------------------------数据库层面的配置--------------------------------------------------
1、检查附加日志情况
Select
SUPPLEMENTAL_LOG_DATA_MIN
,SUPPLEMENTAL_LOG_DATA_PK
,SUPPLEMENTAL_LOG_DATA_UI
,SUPPLEMENTAL_LOG_DATA_FK
,SUPPLEMENTAL_LOG_DATA_ALL from v$database;
正确状态如下：
SUPPLEME SUP SUP SUP SUP
-------- --- --- --- ---
YES      NO  NO  NO  NO
2、增加数据库附加日志及回退
alter database add supplemental log data;
alter database add supplemental log data (primary key, unique,foreign key) columns;
---rollback
alter database drop supplemental log data (primary key, unique,foreign key) columns;
alter database drop supplemental log data;
3、开启数据库强制日志模式
alter database force logging;
------------------------------安装ddl 复制支持-----------------------------
4、执行marker_setup.sql 脚本。This script. installs support for the Oracle GoldenGate DDL marker system
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:ogg

Marker setup table script. complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to OGG
MARKER TABLE
-------------------------------
OK
MARKER SEQUENCE
-------------------------------
OK
Script. complete.
SQL>
5、执行@ddl_setup.sql
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:ogg
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 OGG 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 OGG
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
------------------------------------------------------------------------------------------------------------------------
/oracle/diag/rdbms/ora11/ora11/trace/ggs_ddl_trace.log
Analyzing installation status...

STATUS OF DDL REPLICATION
------------------------------------------------------------------------------------------------------------------------
SUCCESSFUL installation of DDL Replication software components
Script. complete.
SQL>
6、执行role_setup.sql。
The script. drops and creates the role that is needed for DDL synchronization, and it grants DML permissions on
the Oracle GoldenGate DDL objects.
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:ogg
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>
7、Grant the role that was created (default name is GGS_GGSUSER_ROLE  to all Oracle GoldenGate Extract users.
SQL> Grant GGS_GGSUSER_ROLE to ogg;
Grant succeeded.
SQL>