【OGG】RAC環境下配置OGG單向同步 (四)

Appleses發表於2016-01-30

OGGRAC環境下配置OGG單向同步 ()

一.1  BLOG文件結構圖

 

wpsF62E.tmp 

 

一.2  前言部分

 

一.2.1  導讀

各位技術愛好者,看完本文後,你可以掌握如下的技能,也可以學到一些其它你所不知道的知識,~O(∩_∩)O~:

① RAC環境下配置OGG單向同步

 

 

注意:本篇BLOG中程式碼部分需要特別關注的地方我都用黃色背景和紅色字型來表示,比如下邊的例子中,thread 1的最大歸檔日誌號為33thread 2的最大歸檔日誌號為43是需要特別關注的地方。

  List of Archived Logs in backup set 11

  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time

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

  1    32      1621589    2015-05-29 11:09:52 1625242    2015-05-29 11:15:48

  1    33      1625242    2015-05-29 11:15:48 1625293    2015-05-29 11:15:58

  2    42      1613951    2015-05-29 10:41:18 1625245    2015-05-29 11:15:49

  2    43      1625245    2015-05-29 11:15:49 1625253    2015-05-29 11:15:53

 

 

 

 

 

本文如有錯誤或不完善的地方請大家多多指正,ITPUB留言或QQ皆可,您的批評指正是我寫作的最大動力。

一.2.2  實驗環境介紹

 

專案

source db

target  db

db 型別

rac

單例項

db version

11.2.0.1

11.2.0.1

db 儲存

ASM

FS type

ORACLE_SID

jmrac1/jmrac2

orcl

db_name

jmrac

orcl

主機IP地址:

192.168.1.31/192.168.1.32

192.168.1.128

OS版本及kernel版本

RHEL5.7 64位,2.6.18-274.el5

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

node1/node2

orcltest

 

 

 

一.2.3  相關參考文章連結

 

OGGOGG的下載和安裝篇:http://blog.itpub.net/26736162/viewspace-1693241/

OGGOGG的單向DML複製配置():http://blog.itpub.net/26736162/viewspace-1696020/

OGGOGG的單向複製配置-支援DDL()http://blog.itpub.net/26736162/viewspace-1696031/

OGGOGG簡單配置雙向複製()http://blog.itpub.net/26736162/viewspace-1699516/

 

 

一.2.4  本文簡介

 

 

本文基於RAC環境下配置OGG單向同步,主要參考網址為:http://ylw6006.blog.51cto.com/all/470441/16 ,非常感謝斬月大師。

 

 

 

 

 

 

 

一.3  實驗部分

 

一.3.1  實驗目標

本文配置是:racsource)同單例項(target)資料庫之間的ogg單向同步

 

一.4  RAC環境下配置OGG單向同步

 

一.4.1  首先在兩個rac節點上配置ASM動態註冊,11g的監聽器引入了endpoints_listener.ora檔案管理

一.4.1.1  配置listener

配置監聽,加入對ASM的動態註冊:

[oracle@node1 ~]$ srvctl status listener

Listener LISTENER is enabled

Listener LISTENER is running on node(s): node1,node2

[oracle@node1 ~]$ lsnrctl services

 

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 11-JUN-2015 16:39:32

 

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

 

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

Services Summary...

Service "+ASM" has 2 instance(s).

  Instance "+ASM1", status UNKNOWN, has 1 handler(s) for this service...

    Handler(s):

      "DEDICATED" established:0 refused:0

         LOCAL SERVER

  Instance "+ASM1", status READY, has 1 handler(s) for this service...

    Handler(s):

      "DEDICATED" established:3 refused:0 state:ready

         LOCAL SERVER

Service "HAHA" has 2 instance(s).

  Instance "jmrac1", status READY, has 2 handler(s) for this service...

    Handler(s):

      "DEDICATED" established:0 refused:0 state:ready

         REMOTE SERVER

         (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=node1-vip)(PORT=1521)))

      "DEDICATED" established:5 refused:0 state:ready

         LOCAL SERVER

  Instance "jmrac2", status READY, has 1 handler(s) for this service...

    Handler(s):

      "DEDICATED" established:0 refused:0 state:ready

         REMOTE SERVER

         (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=node2-vip)(PORT=1521)))

The command completed successfully

[oracle@node1 ~]$

 

[grid@node1 ~]$ cat $TNS_ADMIN/listener.ora

LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))   # line added by Agent

LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1))))                # line added by Agent

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON                # line added by Agent

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent

 

SID_LIST_LISTENER =

  (SID_LIST =

   (SID_DESC =

     (GLOBAL_DBNAME = +ASM)

     (ORACLE_HOME=/u01/grid)

     (SID_NAME = +ASM1)

   )

  )

[grid@node1 ~]$ cat $TNS_ADMIN/endpoints_listener.ora

LISTENER_NODE1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=node1-vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.31)(PORT=1521)(IP=FIRST))))         # line added by Agent

[grid@node1 ~]$

 

節點二監聽:

[grid@node2 ~]$ cat $TNS_ADMIN/listener.ora

LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1))))                # line added by Agent

LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))            # line added by Agent

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON                # line added by Agent

 

SID_LIST_LISTENER =

  (SID_LIST =

   (SID_DESC =

     (GLOBAL_DBNAME = +ASM)

     (ORACLE_HOME=/u01/grid)

     (SID_NAME = +ASM2)

   )

  )

[grid@node2 ~]$ cat $TNS_ADMIN/endpoints_listener.ora

LISTENER_NODE2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=node2-vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.32)(PORT=1521)(IP=FIRST))))         # line added by Agent

[grid@node2 ~]$

 

 

檢查配置情況:

C:\Users\Administrator> sqlplus sys/lhr@192.168.1.31:1521/+ASM as sysdba

 

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jun 11 11:13:37 2015

 

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

 

 

Connected to:

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

With the Real Application Clusters and Automatic Storage Management options

 

SQL> set line 9999

SQL> show parameter name

 

NAME                                 TYPE                   VALUE

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

db_unique_name                       string                 +ASM

instance_name                        string                 +ASM1

lock_name_space                      string

service_names                        string                 +ASM

SQL> conn  sys/lhr@192.168.1.32:1521/+ASM as sysdba

Connected.

SQL> show parameter name

 

NAME                                 TYPE                   VALUE

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

db_unique_name                       string                 +ASM

instance_name                        string                 +ASM2

lock_name_space                      string

service_names                        string                 +ASM

SQL>

 

 

一.4.1.2  配置tnsnames.ora

注意切換到oracle使用者下,2個節點均配置:

節點一:

[oracle@node1 ~]$ su - oracle

Password:

[oracle@node1 ~]$ cd $TNS_ADMIN

[oracle@node1 admin]$ ll

total 16

drwxr-xr-x 2 oracle oinstall 4096 Feb 27  2012 samples

-rw-r--r-- 1 oracle oinstall  187 May  7  2007 shrept.lst

-rw-r--r-- 1 oracle oinstall 1137 Apr 28 14:41 tnsnames1504282PM4155.bak

-rw-r----- 1 oracle oinstall 1752 May 12 16:17 tnsnames.ora

[oracle@node1 admin]$ more tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

 

RAC =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.32)(PORT = 1521))

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.31)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = HAHA)

    )

  )

 

ASM = 

(DESCRIPTION = 

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.31)(PORT = 1521)) 

    (CONNECT_DATA = 

      (SERVER = DEDICATED) 

      (SERVICE_NAME = +ASM) 

      (SID_NAME = +ASM1) 

    ) 

  )

 

 

[oracle@node1 admin]$

 

 

節點二:

[oracle@node2 admin]$ more tnsnames.ora

# tnsnames.ora.node2 Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora.node2

# Generated by Oracle configuration tools.

 

RAC =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.32)(PORT = 1521))

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.31)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = HAHA)

    )

  )

 

ASM = 

(DESCRIPTION = 

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.32)(PORT = 1521)) 

    (CONNECT_DATA = 

      (SERVER = DEDICATED) 

      (SERVICE_NAME = +ASM) 

      (SID_NAME = +ASM2) 

    ) 

  )

[oracle@node2 admin]$

 

 

 

 

 

檢查配置情況:

 

 

[oracle@node1 admin]$ sqlplus lhr/lhr@rac

 

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jun 11 16:47:45 2015

 

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

 

 

Connected to:

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

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

 

SQL> show parameter name

 

NAME                                 TYPE        VALUE

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

db_file_name_convert                 string

db_name                              string      jmrac

db_unique_name                       string      jmrac

global_names                         boolean     FALSE

instance_name                        string      jmrac2

lock_name_space                      string

log_file_name_convert                string

service_names                        string      HAHA, jmrac

SQL> conn sys/lhr@ASM  as sysasm

Connected.

SQL> show parameter name

 

NAME                                 TYPE        VALUE

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

db_unique_name                       string      +ASM

instance_name                        string      +ASM1

lock_name_space                      string

service_names                        string      +ASM

SQL>

 

 

一.4.2  RAC上安裝OGG軟體

一.4.2.1  安裝ACFS

我們首先來安裝ACFS,ASM Cluster File System,相關知識不多解釋,而安裝ACFS也有很多種辦法,這裡我們採用命令列的方式來安裝ACFS,我們在節點一上操作:

   aroot使用者手工載入驅動:ASM volume driveracfsload為grid使用者下的命令

[root@node1 ~]# acfsload -s

acfsload: ACFS-9228: usage: acfsload {start|stop} [-s]

[root@node1 ~]# acfsload start

ACFS-9327: Verifying ADVM/ACFS devices.

ACFS-9322: done.

[root@node1 ~]#

 

b、建立磁碟組也可以不用建立磁碟組,後邊採用已經存在的磁碟組來建立卷組,我們這裡就不再重新建立磁碟組了

c、建立asm      

[root@node1 ~]# su - grid

[grid@node1 ~]$ sqlplus / as sysasm

 

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jun 11 14:16:13 2015

 

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

 

 

Connected to:

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

With the Real Application Clusters and Automatic Storage Management options

 

SQL> alter diskgroup DATA add volume acfsvol1 size 1G;

 

Diskgroup altered.

 

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Real Application Clusters and Automatic Storage Management options

[grid@node1 ~]$ ll /dev/asm

total 0

brwxrwx--- 1 root asmadmin 252, 118786 Jun 11 14:16 acfsvol1-232

brwxrwx--- 1 root asmadmin 252, 118785 Jun 11 13:45 vol01-232

[grid@node1 ~]$

 d、建立acfs掛載目錄rac1rac2節點都執行

 

[root@node1 ~]# mkdir -p /u01/app/acfsmounts/acfsvol1-232

 

[root@node2 ~]# mkdir -p /u01/app/acfsmounts/acfsvol1-232

 

e、用mkfs建立檔案系統

[root@node1 ~]# /sbin/mkfs -t acfs -n acfs01 /dev/asm/acfsvol1-232

mkfs.acfs: version                   = 11.2.0.1.0.0

mkfs.acfs: on-disk version           = 39.0

mkfs.acfs: volume                    = /dev/asm/acfsvol1-232

mkfs.acfs: volume size               = 1073741824

mkfs.acfs: Format complete.

[root@node1 ~]#

f、用acfsuit命令註冊檔案系統

[root@node1 ~]# /sbin/acfsutil registry -a -f /dev/asm/acfsvol1-232   /u01/app/acfsmounts/acfsvol1-232

acfsutil registry: mount point /u01/app/acfsmounts/acfsvol1-232 successfully added to Oracle Registry

 g、用mount.acfs命令掛載檔案系統

[root@node1 ~]# mount.acfs -o all

[root@node1 ~]# df -h

Filesystem            Size  Used Avail Use% Mounted on

/dev/sda3              75G   57G   15G  81% /

/dev/sda1              99M   27M   67M  29% /boot

tmpfs                1005M  463M  542M  47% /dev/shm

/dev/asm/vol01-232    1.0G   73M  952M   8% /u01/app/acfsmounts/data_vol01

/dev/asm/acfsvol1-232

                      1.0G   73M  952M   8% /u01/app/acfsmounts/acfsvol1-232

[root@node1 ~]#

[root@node1 ~]# ssh node2 "df -h"

root@node2's password:

Filesystem            Size  Used Avail Use% Mounted on

/dev/sda3              75G   16G   55G  23% /

/dev/sda1              99M   26M   69M  28% /boot

tmpfs                1005M  463M  542M  47% /dev/shm

/dev/asm/vol01-232    1.0G   73M  952M   8% /u01/app/acfsmounts/data_vol01

/dev/asm/acfsvol1-232

                      1.0G   73M  952M   8% /u01/app/acfsmounts/acfsvol1-232

[root@node1 ~]#

h、改變檔案系統屬性供oracle使用

 

[root@node1 ~]# chown oracle.asmadmin /u01/app/acfsmounts/acfsvol1-232

[root@node1 ~]# ll -d /u01/app/acfsmounts/acfsvol1-232

drwxrwx--- 4 oracle asmadmin 4096 Jun 11 14:24 /u01/app/acfsmounts/acfsvol1-232

[root@node1 ~]# ssh node2 "ls -ld /u01/app/acfsmounts/acfsvol1-232"

root@node2's password:

drwxrwx--- 4 oracle asmadmin 4096 Jun 11 14:24 /u01/app/acfsmounts/acfsvol1-232

[root@node1 ~]#

 

 

一.4.2.2  acfs上安裝oggacfs用來存放ogg的安裝目錄,便於叢集件的共享,在10g上部署可以選擇ocfs2

首先上傳ogg軟體到/TMP目錄下:

wpsF62F.tmp 

2個節點均配置環境變數,加入如下引數:

export OGG_HOME=/u01/app/acfsmounts/acfsvol1-232/gg11

export PATH=$OGG_HOME:$ORACLE_HOME/bin:$PATH

export LD_LIBRARY_PATH=$OGG_HOME:$ORACLE_HOME/lib:$LD_LIBRARYPATH

 

alias ggsci='rlwrap ggsci'

 

節點一:

[oracle@node1 gg11]$ source ~/.bash_profile

[oracle@node1 gg11]$ echo $OGG_HOME

/u01/app/acfsmounts/acfsvol1-232/gg11

[oracle@node1 gg11]$

節點二:

[oracle@node2 ~]$  source ~/.bash_profile

[oracle@node2 ~]$ echo $OGG_HOME

/u01/app/acfsmounts/acfsvol1-232/gg11

[oracle@node2 ~]$

 

開始在節點一安裝OGG軟體:

[root@node1 tmp]# chown oracle.oinstall ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip

[root@node1 tmp]# su - oracle

[oracle@node1 tmp]$ 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 

[oracle@node1 tmp]$

[oracle@node1 tmp]$  tar xvf fbo_ggs_Linux_x64_ora11g_64bit.tar -C $OGG_HOME

。。。。。。。。。。。。。。。。

[oracle@node1 ~]$ cd $OGG_HOME

[oracle@node1 gg11]$ pwd

/u01/app/acfsmounts/acfsvol1-232/gg11

[oracle@node1 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 (node1) 1>

GGSCI (node1) 1> create subdirs

 

Creating subdirectories under current directory /u01/app/acfsmounts/acfsvol1-232/gg11

 

Parameter files                /u01/app/acfsmounts/acfsvol1-232/gg11/dirprm: already exists

Report files                   /u01/app/acfsmounts/acfsvol1-232/gg11/dirrpt: created

Checkpoint files               /u01/app/acfsmounts/acfsvol1-232/gg11/dirchk: created

Process status files           /u01/app/acfsmounts/acfsvol1-232/gg11/dirpcs: created

SQL script files               /u01/app/acfsmounts/acfsvol1-232/gg11/dirsql: created

Database definitions files     /u01/app/acfsmounts/acfsvol1-232/gg11/dirdef: created

Extract data files             /u01/app/acfsmounts/acfsvol1-232/gg11/dirdat: created

Temporary files                /u01/app/acfsmounts/acfsvol1-232/gg11/dirtmp: created

Stdout files                   /u01/app/acfsmounts/acfsvol1-232/gg11/dirout: created

 

 

GGSCI (node1) 2>

 

 

 

節點二測試:

[root@node2 ~]# su - oracle

[oracle@node2 ~]$ cd $OGG_HOME

[oracle@node2 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 (node2) 1>

 

 

一.4.2.3  配置mgr程式

 

節點一上配置mgr程式:

 

GGSCI (node1) 2> edit params mgr

 

 

GGSCI (node1) 3> view params mgr

 

port 7809 

autostart er * 

autorestart er *

 

 

GGSCI (node1) 4> start mgr

 

Manager started.

 

 

GGSCI (node1) 5> info mgr

 

Manager is running (IP port node1.7809).

 

 

GGSCI (node1) 6> sh netstat -ntpl |grep 7809 

 

(Not all processes could be identified, non-owned process info

will not be shown, you would have to be root to see it all.)

tcp        0      0 0.0.0.0:7809                0.0.0.0:*                   LISTEN      7561/mgr           

 

 

GGSCI (node1) 7> sh ps -ef|grep mgr

 

gdm       5077  5060  0 14:52 ?        00:00:00 /usr/libexec/gdmgreeter

oracle    7561  7424  0 15:11 ?        00:00:00 ./mgr PARAMFILE /u01/app/acfsmounts/acfsvol1-232/gg11/dirprm/mgr.prm REPORTFILE /u01/app/acfsmounts/acfsvol1-232/gg11/dirrpt/MGR.rpt PROCESSID MGR PORT 7809

oracle    7595  7424  0 15:11 pts/2    00:00:00 sh -c ps -ef|grep mgr

 

 

GGSCI (node1) 8>

 

一.4.3  target庫安裝OGG軟體

省略安裝過程。。。。

 

一.4.4  rac資料庫上進行配置,建立使用者,授權,執行執行序列號和ddl複製的相關指令碼等

指令碼:

create user ogg identified by ogg; 

grant connect,resource,dba to ogg; 

@sequence.sql

alter database  add supplemental log data; 

alter database  add supplemental log data (primary key) columns; 

alter database  add supplemental log data (foreign key) columns; 

alter database  add supplemental log data (unique) columns; 

alter system archive log current; 

grant execute on utl_file to ogg; 

@marker_setup.sql 

@ddl_setup.sql 

@role_setup.sql 

grant ggs_ggsuser_role to ogg; 

@ddl_enable.sql 

@ddl_pin ogg

 

 

[oracle@node1 gg11]$ ORACLE_SID=jmrac1

[oracle@node1 gg11]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jun 11 15:17:43 2015

 

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

 

 

Connected to:

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

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

 

SQL> select instance_name from v$instance;

 

INSTANCE_NAME

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

jmrac1

 

SQL> create user ogg identified by ogg;

 

User created.

 

SQL> grant connect,resource,dba to ogg;

 

Grant succeeded.

 

SQL> @sequence.sql

Please enter the name of a schema for the GoldenGate database objects:

ogg

Setting schema name to OGG

 

UPDATE_SEQUENCE STATUS:

 

Line/pos

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

Error

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

No errors

No errors

 

 

GETSEQFLUSH

 

Line/pos

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

Error

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

No errors

No errors

 

 

SEQTRACE

 

Line/pos

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

Error

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

No errors

No errors

 

 

REPLICATE_SEQUENCE STATUS:

 

Line/pos

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

Error

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

No errors

No errors

 

 

STATUS OF SEQUENCE SUPPORT

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

SUCCESSFUL installation of Oracle Sequence Replication support

SQL> alter database  add supplemental log data; 

 

Database altered.

 

SQL> alter database  add supplemental log data (primary key) columns;  

 

Database altered.

 

SQL> alter database  add supplemental log data (foreign key) columns; 

 

Database altered.

 

SQL> alter database  add supplemental log data (unique) columns;

 

Database altered.

 

SQL> alter system archive log current;  

 

System altered.

 

SQL> grant execute on utl_file to ogg; 

 

Grant succeeded.

 

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:ogg

 

 

Marker setup table script complete, running verification script...

Please enter the name of a schema for the GoldenGate database objects:

Setting schema name to OGG

 

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:ogg

 

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 OGG 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 OGG

 

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

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

/u01/app/oracle/diag/rdbms/jmrac/jmrac1/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:ogg

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.

SQL>  grant ggs_ggsuser_role to ogg;

 

Grant succeeded.

 

SQL> @ddl_enable.sql 

 

Trigger altered.

 

SQL> @ddl_pin ogg

 

PL/SQL procedure successfully completed.

 

 

PL/SQL procedure successfully completed.

 

 

PL/SQL procedure successfully completed.

 

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     49

Next log sequence to archive   50

Current log sequence           50

SQL>

SQL> create user test identified by test; 

 

User created.

 

SQL> grant connect,resource to test;

 

Grant succeeded.

 

SQL>

 

 

一.4.5  source端配置extract group

[oracle@node1 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 (node1) 1> dblogin userid ogg,password ogg 

Successfully logged into database.

 

GGSCI (node1) 2> add extract testext,tranlog,begin now,threads 2

EXTRACT added.

 

 

GGSCI (node1) 3> add exttrail ./dirdat/et, extract testext 

EXTTRAIL added.

 

 

GGSCI (node1) 4> edit params testext 

EXTRACT testext

SETENV (ORACLE_HOME = "/u01/app/oracle/product/11.2.0/dbhome_1")

USERID ogg@rac, PASSWORD ogg

TRANLOGOPTIONS ASMUSER sys@ASM,ASMPASSWORD lhr

THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 20000

EXTTRAIL ./dirdat/et

DYNAMICRESOLUTION

DDL INCLUDE ALL

TABLE test.*;

~

~

~

。。。。。。。。。。。。。。。。。。

~

~

"dirprm/testext.prm" [New] 9L, 289C written

 

 

GGSCI (node1) 5> view params testext 

 

EXTRACT testext 

SETENV (ORACLE_HOME = "/u01/app/oracle/product/11.2.0/dbhome_1") 

USERID ogg@rac, PASSWORD ogg 

TRANLOGOPTIONS ASMUSER sys@ASM,ASMPASSWORD lhr 

THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 20000 

EXTTRAIL ./dirdat/et 

DYNAMICRESOLUTION 

DDL INCLUDE ALL 

TABLE test.*;

 

 

GGSCI (node1) 6>

 

[oracle@node1 gg11]$ sqlplus ogg/ogg@rac

 

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jun 11 15:31:41 2015

 

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

 

 

Connected to:

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

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

 

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

[oracle@node1 gg11]$ sqlplus sys/lhr@ASM as sysasm

 

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jun 11 15:32:10 2015

 

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

 

 

Connected to:

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

With the Real Application Clusters and Automatic Storage Management options

 

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Real Application Clusters and Automatic Storage Management options

[oracle@node1 gg11]$

 

 

一.4.6  source端配置data pump extract group

 

GGSCI (node1) 1> add extract testpump,exttrailsource ./dirdat/et,begin now 

EXTRACT added.

 

 

GGSCI (node1) 2> add rmttrail ./dirdat/rt,extract testpump 

RMTTRAIL added.

 

 

GGSCI (node1) 3> edit params  testpump 

EXTRACT testpump

RMTHOST 192.168.1.128, MGRPORT 7809

RMTTRAIL ./dirdat/rt

PASSTHRU

TABLE test.*; 

~

~

~

。。。。。。。。。。。。。。。。。。

~

~

~

~

"dirprm/testpump.prm" [New] 5L, 105C written

 

 

GGSCI (node1) 4> view params  testpump

 

EXTRACT testpump 

RMTHOST 192.168.1.128, MGRPORT 7809 

RMTTRAIL ./dirdat/rt 

PASSTHRU 

TABLE test.*; 

 

 

GGSCI (node1) 5>

GGSCI (node1) 5> info all

 

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

 

MANAGER     RUNNING                                          

EXTRACT     STOPPED     TESTEXT     00:00:00      00:10:18   

EXTRACT     STOPPED     TESTPUMP    00:00:00      00:02:19   

 

 

GGSCI (node1) 6>

 

 

 

 

一.4.7  target端配置

注意:這裡要保證在tnsnames.ora檔案中配置了orcl連線串

[oracle@orcltest ~]$ ORACLE_SID=orcl

[oracle@orcltest ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jun 11 15:38:14 2015

 

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

 

 

Connected to:

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

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> create user ogg identified by ogg; 

 

User created.

 

SQL> grant connect,resource,dba to ogg; 

 

Grant succeeded.

 

SQL> create user test identified by test; 

 

User created.

 

SQL> grant connect,resource to test;

 

Grant succeeded.

 

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@orcltest ~]$

 

[oracle@orcltest ~]$ cd $OGG_HOME

[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> info all

 

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

 

MANAGER     STOPPED                                          

 

 

 

GGSCI (orcltest) 2> add replicat testrpt,exttrail ./dirdat/rt,nodbcheckpoint 

REPLICAT added.

 

 

GGSCI (orcltest) 3> edit params testrpt 

REPLICAT testrpt

setenv (ORACLE_SID=orcl)

SETENV (ORACLE_HOME = "/u01/app/oracle/product/11.2.0/dbhome_1")

USERID ogg@orcl,PASSWORD ogg

ASSUMETARGETDEFS

HANDLECOLLISIONS

REPERROR (DEFAULT, DISCARD)

DDLERROR DEFAULT DISCARD

DDLOPTIONS REPORT

DISCARDFILE ./repsz.dsc,append,megabytes 100

MAP test.*, TARGET test.*;

~

~

~

~

~

。。。。。。。。。。。。。。。

~

~

~

~

"dirprm/testrpt.prm" [New] 11L, 328C written

 

 

GGSCI (orcltest) 4> view params testrpt

 

REPLICAT testrpt

setenv (ORACLE_SID=orcl)

SETENV (ORACLE_HOME = "/u01/app/oracle/product/11.2.0/dbhome_1") 

USERID ogg@orcl,PASSWORD ogg

ASSUMETARGETDEFS 

HANDLECOLLISIONS 

REPERROR (DEFAULT, DISCARD) 

DDLERROR DEFAULT DISCARD 

DDLOPTIONS REPORT 

DISCARDFILE ./repsz.dsc,append,megabytes 100 

MAP test.*, TARGET test.*;

 

 

GGSCI (orcltest) 5>

GGSCI (orcltest) 5> info all

 

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

 

MANAGER     STOPPED                                          

REPLICAT    STOPPED     TESTRPT     00:00:00      00:25:50   

 

 

GGSCI (orcltest) 6>

 

 

[oracle@orcltest ~]$ sqlplus ogg/ogg@orcl

 

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jun 11 16:04:06 2015

 

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

 

 

Connected to:

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

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL>

 

 

 

一.4.8  啟動各程式

 

注意程式的啟動順序,先是sourcetarget端的mgr程式,其次是source端的extract程式,target端的replicat程式,最後是source端的extract pump程式

 

程式安排在最後啟動,在啟動source端的pump程式之前,如果target端的replicat程式未啟動,則會報如下錯誤:

2012-07-15 13:56:14  ERROR   OGG-01033  There is a problem in network communication, a remote file problem, encryption keys for target and source do not match (if using ENCRYPT) or an unknown error. (Remote file used is /vol2/ogg/dirdat/rt000000, reply received is Could not create /vol2/ogg/dirdat/rt000000).

 

利用start group名稱啟動各個程式,啟動後的情況:

source端:

GGSCI (node1) 1> info all

 

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

 

MANAGER     RUNNING                                          

EXTRACT     RUNNING     TESTEXT     00:00:00      00:00:03   

EXTRACT     RUNNING     TESTPUMP    00:00:00      00:00:02   

 

 

GGSCI (node1) 2>

 

target端:

GGSCI (orcltest) 7> info all

 

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

 

MANAGER     RUNNING                                          

REPLICAT    RUNNING     TESTRPT     00:00:00      00:00:04

 

 

確保每個程式都是running狀態,如果不是的話就view report 程式名,檢視日誌解決錯誤後重新啟動程式。

 

一.4.9  測試同步情況

 

C:\Users\Administrator>sqlplus test/test@rac

 

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jun 11 16:15:59 2015

 

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

 

 

Connected to:

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

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

 

SQL> create table t1 (id number,name char(10));

 

Table created.

 

SQL> desc t1;

Name                                      Null?    Type

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

ID                                                 NUMBER

NAME                                               CHAR(10)

 

SQL> insert into t1 values (1,'one');

 

1 row created.

 

SQL> select * from t1;

 

        ID NAME

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

         1 one

 

SQL> commit;

 

Commit complete.

 

SQL> conn test/test@orcl

Connected.

SQL> select * from tab;

 

TNAME                          TABTYPE  CLUSTERID

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

T1                             TABLE

 

SQL> select * from t1;

 

        ID NAME

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

         1 one

 

SQL>

SQL> conn test/test@rac

Connected.

SQL> create table rac_test as select * from all_objects;

 

Table created.

 

SQL>  conn test/test@orcl

Connected.

SQL> select count(1) from rac_test;

 

  COUNT(1)

----------

     68092

 

SQL> conn test/test@rac

Connected.

SQL> select count(1) from rac_test;

 

  COUNT(1)

----------

     55664

 

SQL>

 

 

 

 

一.4.10  總結

 

總結:rac環境下配置ogg,基本同單例項無異!重點需要注意的地方有以下幾點!
1:使用ASM儲存,需要先配置ASM例項的靜態註冊,同時在tnsnames.ora檔案中配置連線串
2:在配置exttract程式中,注意需要配置TRANLOGOPTIONS 引數,輸入連線asm例項的憑證
3:在配置exttract程式中,新增tranlog的時候,需要寫thread 2
4: 注意程式的啟動順序,先是sourcetarget端的mgr程式,其次是source端的extract程式,target端的replicat程式,最後是source端的extract pump程式

 

 

一.5  About Me

 

...........................................................................................................................................................................................

本文作者:小麥苗,只專注於資料庫的技術,更注重技術的運用

ITPUB BLOG:http://blog.itpub.net/26736162

本文地址:http://blog.itpub.net/26736162/viewspace-1699522/

本文pdf版:  提取碼:af2d

QQ:642808185 若加QQ請註明你所正在讀的文章標題

創作時間地點:2015-06-11 09:00~ 2015-06-11 19:00 於外匯交易中心

<版權所有,文章允許轉載,但須以連結方式註明源地址,否則追究法律責任!>

...........................................................................................................................................................................................

 

 

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/28628435/viewspace-1984376/,如需轉載,請註明出處,否則將追究法律責任。

相關文章