OGG同构(ORACLE-ORACLE)、异构(ORACLE-MYSQL)同步配置及错误解析
环境：11.2.0.3(已安装数据库实例)+OEL5.7
192.168.1.55 zlm sid:zlm11g
192.168.1.60 zlm2 sid:zlm11g
一、安装软件，配置环境，创建相关用户
1.1 下载介质并安装OGG软件
从官方网址下载最新版OGG FOR ORACLE 11g ON LINUX X86-64软件：
acle/technetwork/middleware/goldengate/downloads/index.html?ssSourceSiteId=ocomen
Oracle GoldenGate V11.2.1.0.1 for Oracle 11g on Linux x86-64 (86 MB)
介质名称：ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip
1.2 把OGG软件包复制到源端oracle主目录，创建安装目录gg11后2次解压到gg11
[oracle@zlm ~]$ ls
ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip
[oracle@zlm ~]$ cd $OACLE_BASE
[oracle@zlm oracle]$ mkdir gg11
[oracle@zlm oracle]$ cd gg11
[oracle@zlm gg11]$ unzip /home/oracle/ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip
[oracle@zlm gg11]$ ll
total 223764
-rw-rw-r-- 1 oracle oinstall 228556800 Apr 23 2012 fbo_ggs_Linux_x64_ora11g_64bit.tar
-
rwxrwxrwx 1 oracle oinstall 220546 May 2 2012 OGG_WinUnix_Rel_Notes_11.2.1.0.1.pdf
-rwxrwxrwx 1 oracle oinstall 93696 May 2 2012 Oracle GoldenGate 11.2.1.0.1 README.doc
-rwxrwxrwx 1 oracle oinstall 24390 May 2 2012 Oracle GoldenGate 11.2.1.0.1&
[oracle@zlm gg11]$ tar xvof fbo_ggs_Linux_x64_ora11g_64bit.tar
1.3 修改环境变量文件.bash_profile，加入OGG的环境变量
export GGHOME=$ORACLE_BASE/gg11
export PATH=$ORACLE_BASE/gg11:$ORACLE_HOME/bin:/usr/bin/:$PATH
[oracle@zlm gg11]$ . ~/.bash_profile
[oracle@zlm gg11]$ ggsci
ggsci: error while loading shared libraries: libnnz11.so: cannot open shared object file: No such file or directory
由于没有设置LD_LIBRARY_PATH环境变量，所以无法执行ggsci，添加export LD_LIBLARY_PATH=$ORACLE_BASE/gg11:$ORACLE_HOME/lib:$ORACLE_HOME/bin
到环境变量.bash_profile并source，或者创建一个link文件：
[oracle@zlm gg11]$ ln -s /u01/app/oracle/product/11.2.0/db_1/lib/libnnz11.so -
/u01/app/oracle/product/11.2.0/db_1/lib/libnnz10.so
/u01/app/oracle/product/11.2.0/db_1/lib/libnnz10.so
1.4 创建OGG专用目录subdirs
GGSCI (zlm) 1> create subdirs
Creating subdirectories under current directory /u01/app/oracle/gg11
Parameter files /u01/app/oracle/gg11/dirprm: already exists
Report files /u01/app/oracle/gg11/dirrpt: created
Checkpoint files /u01/app/oracle/gg11/dirchk: created
Process status files /u01/app/oracle/gg11/dirpcs: created
SQL script files /u01/app/oracle/gg11/dirsql: created
Database definitions files /u01/app/oracle/gg11/dirdef: created
Extract data files /u01/app/oracle/gg11/dirdat: created
Temporary files /u01/app/oracle/gg11/dirtmp: created
Stdout files /u01/app/oracle/gg11/dirout: created
1.5 开启归档模式、强制日志、附加日志
查看v$database看这3个参数是否已开启，使用OGG必须是开启状态
SQL> select LOG_MODE, SUPPLEMENTAL_LOG_DATA_MIN,FORCE_LOGGING from v$database;
LOG_MODE SUPPLEME FOR
------------ -------- ---
ARCHIVELOG YES YES
各参数开启方法：
--archivelog
SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open;
--force logging
SQL> alter database force logging;
--supplemental log data
SQL> alter database add supplemental log data;
1.6 关闭数据库的recyclebin(10gDDL必须，11gDDL可选)
SQL> alter system set recyclebin=off scope=spfile; --同步DDL要求关闭linux安装oracle数据库(rpm方式安装)10g中的回收站特性
1.7 创建复制用户ogg并授予权限
SQL> create user ogg identified by ogg default tablespace users temporary tablespace temp;
SQL> grant connect,resource,unlimited tablespace to ogg;
1.8 创建测试用户sender并授予权限
SQL> create user sender identified by sender default tablespace users temporary tablespace temp;
SQL> grant connect,resource,unlimited tablespace to sender;
1.9 配置复制的DDL支持(必须SYSDBA登录执行)
SQL> grant execute on utl_file to ogg;
SQL> @$GGHOME/marker_setup.sql; --建立一个DDL标记表
SQL> @$GGHOME/ddl_setup.sql; --INITIALSETUP选项运行ddl_setup.sql 将在数据库中创建捕获DDL语句的Trigger等必要组件(注意，执行时必须断开GGSCI连接，否则报错)
SQL> @$GGHOME/role_setup.sql; --建立GGS_GGSUSER_ROLE角
SQL> grant GGS_GGSUSER_ROLE to ogg; --授予给extract group参数中定义的userid用户
SQL> @$GGHOME/ddl_enable.sql; --enable ddl捕获触发器
注意：下面2个SQL脚本只是为了提高DDL复制性能，不是必须的
SQL> @?/rdbms/admin/dbmspool --创建DBMS_SHARED_POOL包
SQL> @ddl_pin --通过dbms_shared_pool.keep存储过程将DDLReplication相关的对象keep在共享池中，以保证这些对象不要reload，提升性能
1.10 目标端重复配置以上1.1-1.9全部内容，至此，ORACLE-ORACLE环境搭建完毕
二、用EXPDP/IMPDP初始化测试数据(仅限ORACLE-ORACLE)
2.1 创建EXPDP/IMPDP使用的directory及其对应的本地目录
SQL> set lin 200 pages 999
SQL> col owner for a5
SQL> col directory_name for a25
SQL> col directory_path for a75
SQL> select * from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论