oracle ogg考题,Linux下单机OGG同步Oracle 11g DB测试-CSDN博客

一、安装goldengate软件

1.测试环境：

OS: RedHat 6 64bit

DB: Oracle 11.2.0.3 64bit

查看OS和DB版本

[oracle@redhat6 ~]$ uname -a

Linux redhat6 2.6.32-131.0.15.el6.x86_64 #1 SMP Tue May 10 15:42:40 EDT 2011 x86_64 x86_64 x86_64 GNU/Linux

SQL> select * from v$version where rownum=1;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

1.2.安装OGG

[oracle@redhat6 ~]$ cd /u01/ogg

[oracle@redhat6 ogg]$unzip ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip

[oracle@redhat6 ogg]$ tar -xvf fbo_ggs_Linux_x64_ora11g_64bit.tar

1.3.添加环境变量

[oracle@redhat6 ogg]$ vi /home/oracle/.bash_profile

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib

export LD_LIBRARY_PATH

export PATH=/u01/ogg:$PATH

export GGATE=/u01/ogg

1.4.使用ggsci工具创建目录

[oracle@redhat6 ogg]$ ./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

GGSCI (redhat6) 1> create subdirs

以上配置须SOURCE和TARGET端都进行

二、配置源(SOURCE)数据库

Goldengate通过抓取源端数据库重做日志进行分析，将获取的数据应用到目标端，实现数据同步。因此，源数据库需要必须处于归档模式，并启用附加日志和强制日志。

(1)归档模式、

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /u01/app/oracle/archlog

Oldest online log sequence 12

Next log sequence to archive 14

Current log sequence 14

SQL> select log_mode,supplemental_log_data_min,force_logging from v$database;

LOG_MODE SUPPLEME FOR

------------ -------- ---

ARCHIVELOG YES NO

(2)强制日志

SQL> alter database force logging;

Database altered.

(3)附加日志

SQL>alter database add supplemental log data;

SQL> select log_mode,supplemental_log_data_min,force_logging from v$database;

LOG_MODE SUPPLEME FOR

------------ -------- ---

ARCHIVELOG YES YES

2.2禁用RecycleBin(oracle10g ogg需要禁用，oracle11g ogg不要求)

SQL> alter system set recyclebin=off scope=spfile;

System altered.

SQL> shutdown immediate

2.3创建存放DDL信息的用户并授权

SQL> create user ggate identified by ggate default tablespace users temporary tablespace temp;

User created.

SQL> grant connect,resource to ggate;

Grant succeeded.

SQL> grant execute on utl_file to ggate;

Grant succeeded.

[oracle@redhat6 ~]$ cd $GGATE

[oracle@redhat6 ggate]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Dec 9 04:55:44 2013

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; ---输入之前创建的用户ggate

SQL> @ddl_setup.sql; ---输入之前创建的用户ggate

SQL> @role_setup.sql; ---输入之前创建的用户ggate

SQL>grant GGS_GGSUSER_ROLE to ggate;

SQL>@ddl_enable.sql;

三、测试OGG

3.1 分别在SOURCE DB和TARGET DB上创建测试用户

SOURCE DB:

SQL> create user source identified by oracle default tablespace users temporary tablespace temp;

User created.

SQL> grant connect,resource,dba to source;

Grant succeeded.

TARGET DB:

SQL> create user targer identified by oracle default tablespace tbs_hjj temporary tablespace temp;

User created.

SQL> grant connect,resource,dba to targer;

Grant succeeded.

3.2 在SOURCE和TARGET分别配置MANAGER

远端和目标端都做同样的操作

GGSCI (redhat6) 1> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER STOPPED

GGSCI (redhat6) 2> edit params mgr

GGSCI (redhat6) 3> start mgr

Manager started.

GGSCI (redhat6) 4> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

3.3 配置SOURCE DB的复制队列

连接到数据库，测试连接：

GGSCI (redhat6) 5> dblogin userid ggate@hjj,password ggate

Successfully logged into database.

增加一个抽取extract:

GGSCI (redhat6) 6> add extract ext1,tranlog,begin now

EXTRACT added.

GGSCI (redhat6) 7> add exttrail /u01/ogg/dirdat/lt,extract ext1

EXTTRAIL added.

GGSCI (redhat6) 8> edit params ext1

extract ext1

userid ggate@hjj,password ggate

rmthost redhat6,mgrport 7809

rmttrail /u01/ogg/dirdat/lt

ddl include mapped objname source.*;

table source.*;

GGSCI (redhat6) 2> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING EXT1 00:00:00 00:00:10

3.4 配置TARGET DB的同步队列

GGSCI (redhat6) 2> edit params ./GLOBAL

GGSCHEMA ggate

CHECKPOINTTABLE ggate.checkpoint

GGSCI (redhat6) 3> dblogin userid ggate@hjj,password ggate

Successfully logged into database.

GGSCI (redhat6) 4> add checkpointtable ggate.checkpoint

Successfully created checkpoint table ggate.checkpoint.

创建同步队列

GGSCI (redhat6) 1> add replicat rep1,exttrail /u01/ogg/dirdat/lt,checkpointtable ggate.checkpoint

REPLICAT added.

GGSCI (redhat6) 5> edit params rep1

replicat rep1

ASSUMETARGETDEFS

userid ggate@hjj,password ggate

discardfile /u01/ogg/dirdat/rep1_discard.txt,append, megabytes 10

DDL

map source.*, target targer.*;

3.5 开启同步

(1)SOURCE端：

GGSCI (redhat6) 10> start extract ext1

Sending START request to MANAGER ...

EXTRACT EXT1 starting

GGSCI (redhat6) 11> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING EXT1 00:07:38 00:00:00

(2)TARGET端：

GGSCI (redhat6) 3> start replicat rep1

Sending START request to MANAGER ...

REPLICAT REP1 starting

GGSCI (redhat6) 4> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING EXT1 00:00:00 00:00:05

REPLICAT RUNNING REP1 00:00:00 00:00:01

3.6 测试数据复制

(1)SOURCE DB:

[oracle@redhat6 ogg]$ sqlplus source/oracle

SQL*Plus: Release 11.2.0.3.0 Production on Mon Dec 9 20:09:11 2013

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 hjj as select * from sys.all_users;

Table created.

(2)TARGET DB 查看数据同步情况:

[oracle@redhat6 ogg]$ sqlplus targer/oracle

SQL*Plus: Release 11.2.0.3.0 Production on Mon Dec 9 20:09:26 2013

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> select count(*) from hjj;

COUNT(*)

----------

在SOURCE端执行：

SQL> insert into hjj select * from sys.all_users;

12 rows created.

SQL> commit;

Commit complete.

在TARGET查看同步情况

SQL> select count(*) from hjj;

COUNT(*)

----------

Oracle到Oracle的单向复制配置完成。

oracle ogg考题,Linux下单机OGG同步Oracle 11g DB测试

“相关推荐”对你有帮助么？