【OGG搭建】单项ogg（还未完成）

1.环境描述

        

OS	DATABASE	IP	OGG
源  端	OEL7.3	Oracle11.2.0.4	192.168.240.2	11.2.1.0.1
目标端	OEL7.3	Oracle11.2.0.4	192.168.240.3	11.2.1.0.1

2.创建安装目录并解压安装介质：

源端：

[root@host2 ~]# mkdir -p /u01/app/ogg
[root@host2 ~]# cd /u01/app/ogg/
[root@host2 ogg]# ls
ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip
[root@host2 ogg]# unzip ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip 
Archive:  ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip
  inflating: fbo_ggs_Linux_x64_ora11g_64bit.tar  
  inflating: OGG_WinUnix_Rel_Notes_11.2.1.0.1.pdf  
  inflating: Oracle GoldenGate 11.2.1.0.1 README.txt  
  inflating: Oracle GoldenGate 11.2.1.0.1 README.doc  
[root@host2 ogg]# tar -xvf fbo_ggs_Linux_x64_ora11g_64bit.tar 

目标端：

[root@host3 ~]# mkdir -p /u01/app/ogg
[root@host3 ~]# cd /u01/app/ogg/
[root@host3 ogg]# ls
ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip
[root@host3 ogg]# unzip ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip 
Archive:  ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip
  inflating: fbo_ggs_Linux_x64_ora11g_64bit.tar  
  inflating: OGG_WinUnix_Rel_Notes_11.2.1.0.1.pdf  
  inflating: Oracle GoldenGate 11.2.1.0.1 README.txt  
  inflating: Oracle GoldenGate 11.2.1.0.1 README.doc  
[root@host3 ogg]# tar -xvf fbo_ggs_Linux_x64_ora11g_64bit.tar 

3.配置环境变量：

源端和目标端相同

[oracle@host2 ~]$ vi .bash_profile
【增加】
export PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin
export OGG_HOME=/u01/app/ogg
export PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin:$OGG_HOME
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
alias  ggsci='cd $OGG_HOME;ggsci'
[oracle@host2 ~]$ . .bash_profile

4.源端数据库配置：

①开启归档模式

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     10
Next log sequence to archive   12
Current log sequence           12

②开启强制日志

SQL> select force_logging from v$database ;

FOR
--------
NO

SQL> alter database force logging;

Database altered.

SQL> select force_logging from v$database;

FOR
--------
YES

③开启补充日志

SQL> select supplemental_log_data_min from v$database ;

SUPPLEME
--------
NO

SQL> alter database add supplemental log data;

Database altered.

SQL> select supplemental_log_data_min from v$database;

SUPPLEME
--------
YES

 ④oracle数据库版本在11.2.0.4以后需修改参数enable_goldengate为TRUE，这个参数在11.2.0.4和12.1.0.2以后才出现,而且这个参数是在源端和目标端同时设定该参数，只有设置了改参数为true,才能使用OGG的一些功能。

SQL> ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION = TRUE SCOPE=BOTH;

System altered.

 5.创建goldengate数据库用户、表空间并赋予相关权限：

①创建表空间，用户
SQL> create tablespace ogg_ts datafile'/u01/app/oracle/oradata/ogg_ts.dbf' size 100m;

Tablespace created.

SQL> create user ogg identified by ogg default tablespace ogg_ts;

User created.
②赋予用户权限

SQL> grant resource to ogg;

Grant succeeded.

SQL> grant create session,alter session to ogg;

Grant succeeded.

SQL> grant select any dictionary to ogg;

Grant succeeded.

SQL> grant flashback any table to ogg;

Grant succeeded.

SQL> grant alter any table to ogg;

Grant succeeded.

SQL> grant select any table to ogg;

Grant succeeded.

SQL> grant execute on dbms_flashback to ogg;

Grant succeeded.

 6.创建测试数据：

①创建测试用户jing，赋予相关权限

SQL> create user jing identified by jing;                                                    

User created.

SQL> grant select on scott.dept to jing;

Grant succeeded.

SQL> grant select on scott.emp to jing;

Grant succeeded.

SQL> grant connect,resource to jing;

Grant succeeded.

②创建测试表

SQL> create table mydept as select * from scott.dept;

Table created.

SQL> create table myemp as select * from scott.emp;

Table created.

SQL> alter table mydept add primary key(deptno);

Table altered.

SQL> alter table myemp add primary key(empno);

Table altered.

7.导出数据用于目标端：

[oracle@host2 ~]$ exp jing/jing file=/home/oracle/jing.dmp tables=mydept,myemp rows=y
[oracle@host2 ~]$ scp jing.dmp 192.168.240.3:/home/oracle/.
oracle@192.168.240.3's password: 
jing.dmp                                                                                                                              100%   16KB  16.0KB/s   00:00  

8.目标端创建相应用户并授权：

SQL> create user jing identified by jing;

User created.

SQL> grant connect,resource to jing;

Grant succeeded.

9.导入铺底数据：

[oracle@host3 ~]$ imp jing/jing file=/home/oracle/jing.dmp full=y

Import: Release 11.2.0.4.0 - Production on Thu Jan 25 22:06:24 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing JING's objects into JING
. importing JING's objects into JING
. . importing table                       "MYDEPT"          4 rows imported
. . importing table                        "MYEMP"         14 rows imported
Import terminated successfully without warnings.

验证：

SQL> conn jing/jing
Connected.
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
MYDEPT                         TABLE
MYEMP                          TABLE

10.目标端创建用户、表空间并赋予相关权限：

① 创建用户和表空间

SQL> create tablespace ogg_ts datafile'/u01/app/oracle/oradata/ogg_ts.dbf' size 100m;

Tablespace created.

SQL> create user ogg identified by ogg default tablespace ogg_ts;

User created.

②赋予相关权限

SQL> grant resource to ogg;

Grant succeeded.

SQL> grant create session,alter session to ogg;

Grant succeeded.

SQL> grant select any dictionary to ogg;

Grant succeeded.

SQL> grant flashback any table to ogg;

Grant succeeded.

SQL> grant alter any table to ogg;

Grant succeeded.

SQL> grant select any table to ogg;

Grant succeeded.

SQL> grant execute on dbms_flashback to ogg;

Grant succeeded.

SQL> grant insert any table to ogg;

Grant succeeded.

SQL> grant update any table to ogg;

Grant succeeded.

SQL> grant delete any table to ogg;

Grant succeeded.

11.OGG的配置：