实验：单实例上不同schema之间的表数据同步

环境：linux上11gR2，装好gg软件，hr和du分别是两个用户，分别在这两个用户里创建一个简单的表ggtest1；host=192.168.0.99

目的：简单尝试一下gg的同步数据功能,在HR中修改表ggtest1，在DU用户下进行验证

1.打开SUPPLEMENTAL LOG

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; 

SQL> ALTER SYSTEM SWITCH LOGFILE; 

查看下是否打开：

SQL> select  SUPPLEMENTAL_LOG_DATA_MIN  from v$database;

 

SUPPLEME

--------

YES

SQL> EXIT 

2. Configure the Manager process on the source                                                     （配置mgr进程和参数文件）

Create the Manager parameter file

[oracle@ora11g u01]$ cd gg11/

[oracle@ora11g gg11]$ ggsci

GGSCI (ora11g) >  EDIT PARAMS MGR

-- GoldenGate Manager Parameter file

PORT 7809

Start the Manager

GGSCI (ora11g) > START MGR 

  Manager started.

Verify that the Manager has started.

GGSCI (ora11g) > info mgr

Manager is running (IP port ora11g.7809).

3. Add the Extract group                                                                                              （配置extract进程组和参数文件）

GGSCI (ora11g) > ADD EXTRACT EORAHR, TRANLOG, BEGIN NOW, THREADS 1            

EXTRACT added.

Verify the results:

GGSCI (ora11g) > INFO EXTRACT EORAHR

EXTRACT    EORAHR    Initialized   2014-04-27 23:19   Status STOPPED

Checkpoint Lag       00:00:00 (updated 00:00:33 ago)

Log Read Checkpoint  Oracle Redo Logs

                     2014-04-27 23:19:18  Thread 1, Seqno 0, RBA 0

                     SCN 0.0 (0)

4. Create the Extract parameter file

Execute the following commands on the system.

GGSCI (ora11g) > EDIT PARAM EORAHR

-- Change Capture parameter file to capture

-- ggtest1 Changes

EXTRACT EORA HR

USERID  hr, PASSWORD   hr

RMTHOST 192.168.0.99, MGRPORT 7809

RMTTRAIL ./dirdat/hr

TABLE  hr.ggtest1;

5. Define the GoldenGate trail                                                                                       （添加源数据库trail文件，启动extract）                            

GGSCI (ora11g) > ADD RMTTRAIL ./dirdat/hr, EXTRACT EORAHR,MEGABYTES 5 

RMTTRAIL added.

Verify the results:

GGSCI (ora11g) > INFO RMTTRAIL *

 Extract Trail: ./dirdat/hr

             Extract: EORAHR

               Seqno: 0

                 RBA: 0

           File Size: 5M

6. Start the capture process

GGSCI (ora11g) > START EXTRACT EORAHR  

Sending START request to MANAGER ...

EXTRACT EORAHR starting

Verify the results: 

GGSCI (ora11g) > INFO EXTRACT EORAHR, DETAIL 

 

EXTRACT    EORAHR    Last Started 2014-04-27 23:24   Status RUNNING

Checkpoint Lag       00:00:00 (updated 00:00:08 ago)

Log Read Checkpoint  Oracle Redo Logs

                     2014-04-27 23:24:03  Thread 1, Seqno 44, RBA 2334720

                     SCN 0.1689008 (1689008)

GGSCI (ora11g) > VIEW REPORT EORAHR

7.Create a GLOBALS file on the target system（这里还是在192.168.0.99上操作，只不过将 checkpoint table加到du用户下就行）

Create and edit the GLOBALSparameter file to add the checkpoint table.

GGSCI (ora11g) > EDIT PARAMS ./GLOBALS

参数内容:

CHECKPOINTTABLE du.ggschkpt

8. Activate the GLOBALS parameters

  For the GLOBALSconfiguration to take effect, you must exit the session in which  the changes were made. Execute the following command to exit GGSCI.

GGSCI (ora11g) > EXIT  

 

10. Add a Replicat checkpoint table（用du用户登录，如果提示权限不够，改改就好）

On the system, execute the following commands in GGSCI:

[oracle@ora11g u01]$ cd gg11/

[oracle@ora11g u01]$   ggsci

GGSCI (ora11g) > DBLOGIN USERID du, PASSWORD du

Successfully logged into database.

GGSCI (ora11g) > ADD CHECKPOINTTABLE 

No checkpoint table specified, using GLOBALS specification (du.ggschkpt)...

Successfully created checkpoint table du.ggschkpt.

11. Add the Replicat group

Execute the following command on the system to add a delivery group

named RORA.

GGSCI (ora11g) > ADD REPLICAT RORADU, EXTTRAIL ./dirdat/hr

12. Create Replicat parameter file

GGSCI (ora11g) > EDIT PARAM RORADU

-- Change Delivery parameter file to apply

-- ggtest1 changes

REPLICAT RORADU

USERID  du, PASSWORD   du

HANDLECOLLISIONS

ASSUMETARGETDEFS

DISCARDFILE ./dirrpt/RORA DU.DSC, PURGE

MAP  hr.ggtest1, TARGET  du.ggtest1;                                                                       （分号结尾，不能丢了）

13. Start the Replicat process 

GGSCI (ora11g) > START REPLICAT RORADU

Verify the results:

GGSCI (ora11g) > INFO ALL

Program         Status            Group       Lag at Chkpt   Time Since Chkpt

 

MANAGER     RUNNING                                          

EXTRACT        RUNNING     EORAHR      00:00:00         00:00:08   

REPLICAT       RUNNING     RORADU      00:00:00         00:02:22  

 

14.验证

创建Replicat group的时候，出了点问题，研究了半天才解决，下面来验证一下之前的配置！

HR用户下：

SQL> insert into ggtest1 values (0,'marry');

1 row created.

SQL> insert into ggtest1 values (1,'frank');

1 row created.

如果此时不commit，那么在DU用户下应该是查不到的，DU用户登录， 验证一下：

SQL> show user

USER is "DU"

SQL> select * from ggtest1;

no rows selected

回到HR用户下，commit之后，再到DU用户下查询：

SQL> /

        ID NAME

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

         0 marry

         1 frank

总结：这期间出了很多错，有空整理出来。。gg初学，如有错误，还望指出。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29602308/viewspace-1150764/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29602308/viewspace-1150764/