合 OGG从入门到高可用系列
【OGG】OGG的单向复制配置-支持DDL(二)
实验环境介绍
| 项目 | source db | target db |
|---|---|---|
| db 类型 | 单实例 | 单实例 |
| db version | 11.2.0.3 | 11.2.0.3 |
| db 存储 | FS type | FS type |
| ORACLE_SID | ogg1 | ogg2 |
| db_name | ogg1 | ogg2 |
| 主机IP地址： | 192.168.59.129 | 192.168.59.130 |
| OS版本及kernel版本 | RHEL6.5 64位，2.6.32-504.16.2.el6.x86_64 | RHEL6.5 64位，2.6.32-504.16.2.el6.x86_64 |
| OGG版本 | 11.2.1.0.1 64位 | 11.2.1.0.1 64位 |
| OS hostname | orcltest | rhel6_lhr |
实验部分
实验目标
配置2台服务器，搭建OGG,实现hr用户下的数据ddl和DML复制功能。
先验证之前的配置不支持DDL复制
验证之前的配置不支持DDL复制，这里在source端，新建一张表，发现无法复制到target端！target端也新建相同的表后，DML操作可以成功复制。
[oracle@orcltest ~]$ sqlplus hr/hr@ogg1
SQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 10 16:18:03 2015
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> create table t2 (id number primary key,name varchar2(20));
Table created.
SQL> conn hr/hr@ogg2
Connected.
SQL> select tname from tab where tname='T2';
no rows selected
SQL> create table t2 (id number primary key,name varchar2(20));
Table created.
SQL> conn hr/hr@ogg1
Connected.
SQL> insert into t2 values (1,'one');
1 row created.
SQL> commit;
Commit complete.
SQL> conn hr/hr@ogg2
Connected.
SQL> select * from t2;
ID NAME
---------- --------------------
1 one
SQL>
此时source库：
GGSCI (orcltest) 26> dblogin userid ggusr@ogg1,password lhr
Successfully logged into database.
GGSCI (orcltest) 29> info trandata hr.t2
Logging of supplemental redo log data is disabled for table HR.T2.
GGSCI (orcltest) 30>
开始配置OGG支持DDL复制(在source端操作)
赋予ggusr用户相应的权限，修改全局配置文件添加ggschema参数
[oracle@orcltest ~]$ sqlplus sys/lhr@ogg1 as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 10 16:27:05 2015
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> grant execute on utl_file to ggusr;
Grant succeeded.
SQL>
[oracle@orcltest gg11]$ ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (orcltest) 1> edit param ./GLOBALS
GGSCHEMA ggusr
~
~
~
~
。。。。。。。。。。。。。。。
~
~
~
~
"./GLOBALS" 1L, 15C written
GGSCI (orcltest) 2> view param ./GLOBALS
GGSCHEMA ggusr
GGSCI (orcltest) 3>
运行相关的sql脚本
如果想使用DDL功能，需要在之前运行支持DDL的相关脚本。
1.@marker_setup.sql
2.@ddl_setup.sql
3.@role_setup.sql
4.GRANT GGS_GGSUSER_ROLE TO gguser
5.@ddl_enable.sql
6.@?/rdbms/admin/dbmspool.sql
7.@ddl_pin.sql ggusr
[oracle@orcltest gg11]$ sqlplus sys/lhr@ogg1 as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 10 16:30:45 2015
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:ggusr
Marker setup table script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GGUSR
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:ggusr
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 GGUSR 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 GGUSR
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
------------------------------------------------------------------------------------------------------------------------
/u02/app/oracle/diag/rdbms/ogg1/ogg1/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:ggusr
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.
