【OGG】RAC環境下配置OGG單向同步 (四)
【OGG】RAC環境下配置OGG單向同步 (四)
一.1 BLOG文件結構圖
一.2 前言部分
一.2.1 導讀
各位技術愛好者,看完本文後,你可以掌握如下的技能,也可以學到一些其它你所不知道的知識,~O(∩_∩)O~:
① RAC環境下配置OGG單向同步
注意:本篇BLOG中程式碼部分需要特別關注的地方我都用黃色背景和紅色字型來表示,比如下邊的例子中,thread 1的最大歸檔日誌號為33,thread 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 相關參考文章連結
【OGG】OGG的下載和安裝篇:http://blog.itpub.net/26736162/viewspace-1693241/
【OGG】OGG的單向DML複製配置(一):http://blog.itpub.net/26736162/viewspace-1696020/
【OGG】OGG的單向複製配置-支援DDL(二):http://blog.itpub.net/26736162/viewspace-1696031/
【OGG】OGG簡單配置雙向複製(三):http://blog.itpub.net/26736162/viewspace-1699516/
一.2.4 本文簡介
本文基於RAC環境下配置OGG單向同步,主要參考網址為:http://ylw6006.blog.51cto.com/all/470441/16 ,非常感謝斬月大師。
一.3 實驗部分
一.3.1 實驗目標
本文配置是:rac(source)同單例項(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,我們在節點一上操作:
a、root使用者手工載入驅動:ASM volume driver,acfsload為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掛載目錄,在rac1和rac2節點都執行
[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上安裝ogg,acfs用來存放ogg的安裝目錄,便於叢集件的共享,在10g上部署可以選擇ocfs2
首先上傳ogg軟體到/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 啟動各程式
注意程式的啟動順序,先是source和target端的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: 注意程式的啟動順序,先是source和target端的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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- RAC環境下配置OGG同步
- ogg12 mysql to oracle 單向同步MySqlOracle
- OGG安裝及單向配置
- 在RAC下安裝配置OGG
- 【OGG】Oracle GoldenGate 11g (二) GoldenGate 11g 單向同步配置 下OracleGo
- OGG 簡單DML同步
- Oracle 11g RAC到單例項OGG同步Oracle單例
- 關於OGG單表同步
- LINUX 環境 mysql to mysql OGG安裝配置(二)LinuxMySql
- OGG搭建(rac到-->單例項)單例
- OGG單向DDL複製操作
- 【OGG】Oracle GoldenGate 11g (二) GoldenGate 11g 單向同步配置 上OracleGo
- OGG表級同步
- ogg 同步問題
- OGG Integrated Mode(downstream方式)環境搭建
- ogg 同步kafka OGG-15051 Java or JNI exception:KafkaJavaException
- sqlserver2008_ogg單向複製配置文件SQLServer
- ogg 11.1.1.1 同步 sequence
- 兩臺ORACLE之間配置OGG-未配置同步DDLOracle
- 【OGG】OGG的下載和安裝篇
- (一)OGG的安裝與配置,並實現單向DML複製操作
- 【OGG】關於在一套複製環境中使用不同版本OGG的問題
- OGG雙向DML複製操作
- pg 用ogg 同步大概步驟
- oracle---oracle的單向ogg搭建流程(DML+DDL)Oracle
- OGG在RAC上的初始化(上)-- 安裝配置篇
- ogg 軟體下載
- (轉)windows環境下rac節點時間同步方法Windows
- Oracle+Ogg 歸檔丟失 重新導資料建立ogg同步步驟Oracle
- nagios監控 ogg同步狀態iOS
- ogg單機安裝概要
- ogg簡單維護命令
- 手把手教你安裝和配置OGG,並實現單向DML複製技術
- Oracle 19C OGG基礎運維-01環境準備Oracle運維
- ogg單向x86ora10g到x64ora11g支援ddl同步實驗
- 配置ogg異構oracle到mysqlOracleMySql
- OGG資料庫遷移方案(四)資料庫
- ogg 同步pg資料到oracle--步驟Oracle