【ORACLE 高可用】作业 ：配置ORACLE GoldenGate 2
生活随笔
收集整理的這篇文章主要介紹了
【ORACLE 高可用】作业 ：配置ORACLE GoldenGate 2
小編覺得挺不錯的,現(xiàn)在分享給大家,幫大家做個參考.
OGG作業(yè)：
輸出完整的操作過程：
1.不使用數(shù)據(jù)泵完成Oracle-Oracle的雙向復(fù)制。
2.使用數(shù)據(jù)泵進行Oracle-Oracle的單向復(fù)制。
3.完成Oracle-MySQL的單向復(fù)制。
4.完成Oracle分別向Oracle和mysql雙路的單向復(fù)制
----------------------------------------------------------------
?? ?OGG 初始化設(shè)置不再敘述，直接進入正題
1.不使用數(shù)據(jù)泵完成Oracle-Oracle的雙向復(fù)制。
?? ?1.1.DG1 服務(wù)器：
?? ?1.1.1 做為源機的MANAGER及EXTRACT 進程配置
?? ?GGSCI (dg1) 152> view params mgr
?? ?Port 7809
?? ?DYNAMICPORTLIST 7840-7939
?? ?--AUTOSTART ER *
?? ?AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3
?? ?PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 3
?? ?LAGREPORTHOURS 1
?? ?LAGINFOMINUTES 30
?? ?LAGCRITICALMINUTES 45
?? ?GGSCI (dg1) 153> add extract ext1,tranlog,begin now
?? ?EXTRACT added.
?? ?GGSCI (dg1) 153> add exttrail ./dirdat/gg,extract ext1,megabytes 100
?? ?EXTTRAIL added.
?? ?GGSCI (dg1) 153> edit params ext1
?? ??? ?EXTRACT? ext1
?? ??? ?USERID ggs,? PASSWORD ggs
?? ??? ?TRANLOGOPTIONS? EXCLUDEUSER ggs
?? ??? ?RMTHOST? 192.168.10.88, MGRPORT 7809
?? ??? ?RMTTRAIL? ./dirdat/gg
?? ??? ?TABLE? hr.* ;
?? ?1.1.2 DG1做為復(fù)制的目標機配置
?? ?GGSCI (dg1) 158> dblogin userid ggs,password ggs
?? ?Successfully logged into database.
?? ?GGSCI (dg1) 159> add checkpointtable ggs.checkpointtable
?? ?Successfully created checkpoint table ggs.checkpointtable.
?? ?GGSCI (dg1) 160> add replicat? rep2,exttrail ./dirdat/gs checkpointtable ggs.checkpointtable
?? ?REPLICAT added.
?? ?GGSCI (dg1) 161> info all
?? ?Program???? Status????? Group?????? Lag at Chkpt? Time Since Chkpt
?? ?MANAGER???? RUNNING????????????????????????????????????????? ?
?? ?EXTRACT???? RUNNING???? EXT1??????? 00:00:00????? 00:00:05?? ?
?? ?REPLICAT??? STOPPED???? REP2??????? 00:00:00????? 00:00:02
?? ?GGSCI (dg1) 4> edit params rep2
?? ?GGSCI (dg1) 5> view params rep2
?? ??? ?REPLICAT rep2
?? ??? ?ASSUMETARGETDEFS
?? ??? ?USERID ggs, PASSWORD? ggs
?? ??? ?MAP hr.*, TARGET? hr.*;
?? ?GGSCI (dg1) 20> start replicat rep2
?? ?Sending START request to MANAGER ...
?? ?REPLICAT REP2 starting
?? ?GGSCI (dg1) 21> info all
?? ?Program???? Status????? Group?????? Lag at Chkpt? Time Since Chkpt
?? ?MANAGER???? RUNNING????????????????????????????????????????? ?
?? ?EXTRACT???? RUNNING???? EXT1??????? 00:00:00????? 00:00:04?? ?
?? ?REPLICAT??? RUNNING???? REP2??????? 00:00:00????? 00:00:04 ?
?? ?1.2.DG2 服務(wù)器
?? ?1.2.1 DG2做為復(fù)制的目標機配置
?? ??? ?GGSCI (dg2) 1> dblogin userid ggs,password ggs
?? ??? ?Successfully logged into database.
?? ??? ?GGSCI (dg2) 2> add checkpointtable ggs.checkpointtable
?? ??? ?Successfully created checkpoint table ggs.checkpointtable.
?? ??? ?GGSCI (dg2) 11> add replicat? rep1,exttrail ./dirdat/gg checkpointtable ggs.checkpoint
?? ??? ?REPLICAT added.
?? ??? ?GGSCI (dg2) 12> info all
?? ??? ?Program???? Status????? Group?????? Lag at Chkpt? Time Since Chkpt
?? ??? ?MANAGER???? RUNNING????????????????????????????????????????? ?
?? ??? ?REPLICAT??? STOPPED???? REP1??????? 00:00:00????? 00:00:03?? ?
?? ??? ?GGSCI (dg2) 13> view params rep1
?? ??? ?REPLICAT rep1
?? ??? ?ASSUMETARGETDEFS
?? ??? ?USERID ggs, PASSWORD? ggs
?? ??? ? ?
?? ??? ?MAP hr.*, TARGET? hr.*;
?? ??? ?GGSCI (dg2) 14> start rep1
?? ??? ?Sending START request to MANAGER ...
?? ??? ?REPLICAT REP1 starting
?? ??? ?GGSCI (dg2) 15> info all
?? ??? ?Program???? Status????? Group?????? Lag at Chkpt? Time Since Chkpt
?? ??? ?MANAGER???? RUNNING????????????????????????????????????????? ?
?? ??? ?REPLICAT??? RUNNING???? REP1??????? 00:00:00????? 00:00:01 ?
?? ?1.2.1 DG2做為復(fù)制的源機配置
?? ??? ?
?? ?GGSCI (dg2) 9> add extract ext2,tranlog,begin now
?? ?EXTRACT added.
?? ?GGSCI (dg2) 10> add exttrail ./dirdat/gs,extract ext2,megabytes 100
?? ?EXTTRAIL added.
?? ?GGSCI (dg2) 12> edit params ext2
?? ??? ??? ?EXTRACT? ext2
?? ??? ??? ?USERID ggs,? PASSWORD ggs
?? ??? ??? ?TRANLOGOPTIONS? EXCLUDEUSER ggs
?? ??? ??? ?RMTHOST? 192.168.10.87, MGRPORT 7809
?? ??? ??? ?RMTTRAIL? ./dirdat/gs
?? ??? ??? ?TABLE? hr.* ;?? ?
?? ??? ?GGSCI (dg2) 14> start extract ext2
?? ??? ?Sending START request to MANAGER ...
?? ??? ?EXTRACT EXT2 starting
?? ??? ?GGSCI (dg2) 15> start replicat rep1
?? ??? ?Sending START request to MANAGER ...
?? ??? ?REPLICAT REP1 starting
?? ??? ?GGSCI (dg2) 16> info all
?? ??? ?Program???? Status????? Group?????? Lag at Chkpt? Time Since Chkpt
?? ??? ?MANAGER???? RUNNING????????????????????????????????????????? ?
?? ??? ?EXTRACT???? RUNNING???? EXT2??????? 00:01:47????? 00:00:00?? ?
?? ??? ?REPLICAT??? ABENDED???? REP1??????? 00:00:00????? 00:28:12?? ?
?? ??? ?GGSCI (dg2) 17> info all
?? ??? ?Program???? Status????? Group?????? Lag at Chkpt? Time Since Chkpt
?? ??? ?MANAGER???? RUNNING????????????????????????????????????????? ?
?? ??? ?EXTRACT???? RUNNING???? EXT2??????? 00:00:00????? 00:00:09?? ?
?? ??? ?REPLICAT??? ABENDED???? REP1??????? 00:00:00????? 00:29:05
?? ?測試：
?? ?兩臺服務(wù)順OGG狀態(tài)：
?? ?GGSCI (dg1) 17> info all
?? ?Program???? Status????? Group?????? Lag at Chkpt? Time Since Chkpt
?? ?MANAGER???? RUNNING????????????????????????????????????????? ?
?? ?EXTRACT???? RUNNING???? EXT1??????? 00:00:00????? 00:00:08?? ?
?? ?REPLICAT??? RUNNING???? REP2??????? 00:00:00????? 00:00:09?? ?
?? ?GGSCI (dg2) 51> info all
?? ?Program???? Status????? Group?????? Lag at Chkpt? Time Since Chkpt
?? ?MANAGER???? RUNNING????????????????????????????????????????? ?
?? ?EXTRACT???? RUNNING???? EXT2??????? 00:00:00????? 00:00:07?? ?
?? ?REPLICAT??? RUNNING???? REP1??????? 00:00:00????? 00:00:01?? ?
?? ?在DB1 中建立表，插入數(shù)據(jù)并提交
?? ?SQL> CREATE TABLE TST (ID NUMBER(5), CONSTRAINT PK_TST_ID PRIMARY KEY (ID)? );
?? ?Table created.
?? ?SQL> insert into tst values(1);
?? ?1 row created.
?? ?SQL> commit;
?? ?Commit complete.
?? ?SQL> select * from tst;
?? ?
?? ?在DB2 中建立表，并查詢
?? ?SQL> CREATE TABLE TST (ID NUMBER(5), CONSTRAINT PK_TST_ID PRIMARY KEY (ID)? );
?? ?Table created.
?? ?SQL> select * from tst;
?? ?no rows selected
?? ?SQL> /
?? ?no rows selected
?? ?SQL> /
?? ??? ?ID
?? ?----------
?? ??? ? 1
?? ?SQL> insert into tst values(2);
?? ?1 row created.
?? ?SQL> commit;
?? ?Commit complete.
?? ?SQL> select * from tst;
?? ??? ?ID
?? ?----------
?? ??? ? 1
?? ??? ? 2
?? ?完成后在DB1中查詢：
?? ?SQL> select * from tst;
?? ??? ?ID
?? ?----------
?? ??? ? 1
?? ?SQL> /
?? ??? ?ID
?? ?----------
?? ??? ? 1
?? ??? ? 2
?? ?說明數(shù)據(jù)已過來了。
==================================================================
2.使用數(shù)據(jù)泵進行Oracle-Oracle的單向復(fù)制
?? ?(第十課中，我就是使用了數(shù)據(jù)泵的方式配置了單向復(fù)制，現(xiàn)抄錄如下)
?? ?2.1 配置抽取進程：
?? ?GGSCI (node1) 6> add extract extnd,tranlog,begin now
?? ?EXTRACT added.
?? ?GGSCI (node1) 7> add exttrail ./dirdat/nd,extract extnd,megabytes 100
?? ?EXTTRAIL added.
?? ?GGSCI (node1) 8> edit params extnd
?? ?（下面為配置文件內(nèi)容）
?? ?EXTRACT extnd
?? ?SETENV (NLS_LANG = "AMERICAN_AMERICA.UTF8")
?? ?SETENV (ORACLE_HOME = "/opt/app/oracle/product/11.2.0")
?? ?USERID ggs@db1, PASSWORD ggs
?? ?--GETTRUNCATES
?? ?REPORTCOUNT EVERY 1 MINUTES, RATE
?? ?DISCARDFILE ./dirrpt/extnd.dsc,APPEND,MEGABYTES 1024
?? ?--THREADOPTIONS? MAXCOMMITPROPAGATIONDELAY 60000 IOLATENS 60000
?? ?DBOPTIONS? ALLOWUNUSEDCOLUMN
?? ?WARNLONGTRANS 2h,CHECKINTERVAL 3m
?? ?EXTTRAIL ./dirdat/nd
?? ?--TRANLOGOPTIONS EXCLUDEUSER USERNAME
?? ?FETCHOPTIONS NOUSESNAPSHOT
?? ?TRANLOGOPTIONS? CONVERTUCS2CLOBS
?? ?TABLE hr.employee;
?? ?TABLE hr.department;
?? ?2.2添加傳輸進程，配置參數(shù)
?? ?GGSCI (dg1) 24> add extract dpend,exttrailsource ./dirdat/nd
?? ?EXTRACT added.
?? ?GGSCI (dg1) 1> add rmttrail /opt/app/ggs/11.2/dirdat/nd,EXTRACT DPEND
?? ?RMTTRAIL added.
?? ?GGSCI (dg1) 2>
?? ?GGSCI (dg1) 2> edit params dpend?? ?
?? ?EXTRACT dpend
?? ?SETENV (NLS_LANG = AMERICAN_AMERICA.UTF8)
?? ?USERID ggs@db1, PASSWORD ggs
?? ?PASSTHRU
?? ?RMTHOST 192.168.10.88, MGRPORT 7809, compress
?? ?RMTTRAIL /opt/app/ggs/11.2/dirdat/nd
?? ?TABLE hr.depoarment;
?? ?TABLE hr.employee;
?? ?
?? ?2.3 在目標數(shù)據(jù)庫上配置replicat進程
?? ? 2.3.1 配置replicat
?? ?創(chuàng)建checkpoint表
?? ?ggsci>dblogin userid ggs,password ggs
?? ?ggsci>add checkpointtable ggs.checkpoint
?? ?ggsci>?edit params ./GLOBALS??#GLOBALS必須大寫，編輯GLOBALS需要推出ggsci再進入，輸入：
?? ?CHECKPOINTTABLE ggs.checkpoint?
?? ?2.3.2 創(chuàng)建replicat：
?? ?GGSCI (dg2) 8> dblogin userid ggs,password ggs
?? ?Successfully logged into database.
?? ?GGSCI (dg2) 9> add checkpointtable ggs.checkpoint
?? ?Successfully created checkpoint table ggs.checkpoint.
?? ?GGSCI (dg2) 10> add replicat repnd,exttrail /opt/app/ggs/11.2/dirdat/nd,checkpointtable ggs.checkpoint
?? ?REPLICAT added.
?? ?2.3.3?啟動extract及replicat
?? ?2.3.3.1、啟動extract?保證mgr已啟動。
?? ?GGSCI (dg1) 7> start dpend
?? ?EXTRACT DPEND is already running.
?? ?GGSCI (dg1) 8> start extnd
?? ?EXTRACT EXTND is already running.
?? ?GGSCI (dg1) 9> info all
?? ?Program???? Status????? Group?????? Lag at Chkpt? Time Since Chkpt
?? ?MANAGER???? RUNNING????????????????????????????????????????? ?
?? ?EXTRACT???? RUNNING???? DPEND?????? 00:00:00????? 00:00:07?? ?
?? ?EXTRACT???? RUNNING???? EXTND?????? 00:00:00????? 00:00:10? ?
?? ?GGSCI (dg2) 16> start REPND
?? ?REPLICAT REPND is already running.
?? ?GGSCI (dg2) 17> info all
?? ?Program???? Status????? Group?????? Lag at Chkpt? Time Since Chkpt
?? ?MANAGER???? RUNNING????????????????????????????????????????? ?
?? ?REPLICAT??? RUNNING???? REPND?????? 00:00:00????? 00:00:10?? ?
?
?? ?
?? ?
=======================================================================
3.完成Oracle-MySQL的單向復(fù)制。
3.1. download mysql and goldengate:
https://edelivery.oracle.com/EPD/Download/get_form?egroup_aru_number=14841440
https://edelivery.oracle.com/EPD/Search/handle_go
3.2. MYSQL INSTALL :
?? ?rpm -ivh /download/MySQL-server-advanced-5.6.13-1.rhel5.i386.rpm
?? ?rpm -ivh /download/MySQL-client-advanced-5.6.13-1.rhel5.i386.rpm
3.3. 安裝goldengate
?? ?$?Su?–?oracle?$?mkdir?-p?/opt/app/ggs/11.2
?? ?$?chown?-R?oracle:oinstall?/opt/app/ggs/11.2??$?chmod?-R?775?/opt/app/ggs/11.2
?? ?$?cd?/opt/app/ggs/11.2?
?? ?$?unzip?V32420-01.zip
?? ?$?tar xvf ggs_Linux_x86_MySQL_32bit.tar
?? ?cd?/opt/app/ggs/11.2
?? ?[oracle@dg2 11.2]$./ggsci
?? ?
3.4 配置源端服務(wù)器
?? ?
?? ?在先前已民配置好目標端MANAGE的前提下，進行以下配置
?? ?3.4.1 添加extract
?? ?GGSCI (dg1) 1> add extract ext2my,tranlog,begin now
?? ?EXTRACT added.
?? ?GGSCI (dg1) 2> add exttrail ./dirdat/my,extract ext2my,megabytes 100
?? ?EXTTRAIL added.
?? ?GGSCI (dg1) 3> edit params ext2my
?? ??? ?EXTRACT? ext2my
?? ??? ?USERID ggs,? PASSWORD ggs
?? ??? ?TRANLOGOPTIONS? EXCLUDEUSER ggs
?? ??? ?RMTHOST? 192.168.10.85, MGRPORT 7809
?? ??? ?RMTTRAIL? ./dirdat/my
?? ??? ?TABLE? hr.* ;
?? ?GGSCI (dg1) 5> info all
?? ?Program???? Status????? Group?????? Lag at Chkpt? Time Since Chkpt
?? ?MANAGER???? STOPPED????????????????????????????????????????? ?
?? ?EXTRACT???? ABENDED???? EXT1??????? 00:00:00????? 17:48:59?? ?
?? ?EXTRACT???? STOPPED???? EXT2MY????? 00:00:00????? 00:00:46?? ?
?? ?REPLICAT??? ABENDED???? REP2??????? 00:00:00????? 17:48:45?? ?
?? ?3.4.2 添加對象定義文件
?? ?GGSCI (dg1) 7> edit param defgen
?? ?GGSCI (dg1) 9> view param defgen
?? ?defsfile /opt/app/ggs/11.2/dirdef/ext2my.def
?? ?userid ggs,password ggs
?? ?table hr.*;
?? ?生成定義文件并拷貝到目標服務(wù)器
?? ?./defgen paramfile dirprm/defgen.prm
?? ??? ?[oracle@dg1 11.2]$ cat dirdef/ext2my.def
?? ??? ?*+- Defgen version 2.0, Encoding UTF-8
?? ??? ?*
?? ??? ?* Definitions created/modified? 2013-08-31 16:49
?? ??? ?*
?? ??? ?*? Field descriptions for each column entry:
?? ??? ?*
?? ??? ?*???? 1??? Name
?? ??? ?*???? 2??? Data Type
?? ??? ?*???? 3??? External Length
?? ??? ?*???? 4??? Fetch Offset
?? ??? ?*???? 5??? Scale
?? ??? ?*???? 6??? Level
?? ??? ?*???? 7??? Null
?? ??? ?*???? 8??? Bump if Odd
?? ??? ?*???? 9??? Internal Length
?? ??? ?*??? 10??? Binary Length
?? ??? ?*??? 11??? Table Length
?? ??? ?*??? 12??? Most Significant DT
?? ??? ?*??? 13??? Least Significant DT
?? ??? ?*??? 14??? High Precision
?? ??? ?*??? 15??? Low Precision
?? ??? ?*??? 16??? Elementary Item
?? ??? ?*??? 17??? Occurs
?? ??? ?*??? 18??? Key Column
?? ??? ?*??? 19??? Sub Data Type
?? ??? ?*
?? ??? ?Database type: ORACLE
?? ??? ?Character set ID: windows-936
?? ??? ?National character set ID: UTF-16
?? ??? ?Locale: neutral
?? ??? ?Case sensitivity: 14 14 14 14 14 14 14 14 14 14 14 14 11 14 14 14
?? ??? ?*
?? ??? ?Definition for table HR.DEPARTMENT
?? ??? ?Record length: 104
?? ??? ?Syskey: 0
?? ??? ?Columns: 3
?? ??? ?DEP_ID??? 134???? 11??????? 0? 0? 0 1 0????? 8????? 8????? 8 0 0 0 0 1??? 0 1 3
?? ??? ?DEP_NAME?? 64???? 30?????? 12? 0? 0 1 0???? 30???? 30????? 0 0 0 0 0 1??? 0 0 0
?? ??? ?DEP_DESC?? 64???? 50?????? 48? 0? 0 1 0???? 50???? 50????? 0 0 0 0 0 1??? 0 0 0
?? ??? ?End of definition
?? ??? ?*
?? ??? ?Definition for table HR.EMPLOYEE
?? ??? ?Record length: 48
?? ??? ?Syskey: 0
?? ??? ?Columns: 2
?? ??? ?EMP_ID??? 134???? 11??????? 0? 0? 0 1 0????? 8????? 8????? 8 0 0 0 0 1??? 0 1 3
?? ??? ?EMP_NAME?? 64???? 30?????? 12? 0? 0 1 0???? 30???? 30????? 0 0 0 0 0 1??? 0 0 0
?? ??? ?End of definition
?? ??? ?*
?? ??? ?Definition for table HR.TST
?? ??? ?Record length: 12
?? ??? ?Syskey: 0
?? ??? ?Columns: 1
?? ??? ?ID? 134????? 8??????? 0? 0? 0 1 0????? 8????? 8????? 8 0 0 0 0 1??? 0 1 3
?? ??? ?End of definition
?? ?scp /opt/app/ggs/11.2/dirdef/ext2my.def ractdg3:/opt/app/ggs/11.2/dirdef/ext2my.def
?? ?
3.5 配置目標端服務(wù)器
?? ?3.5.1 配置MYSQL SERVER
?? ?# mysql -u root -p password '123456'
?? ?Enter password:
?? ?mysqladmin: connect to server at 'localhost' failed
?? ?error: 'Access denied for user 'root'@'localhost' (using password: YES)'
?? ?
?? ?修改MYSQL 密碼 操作如下：
?? ?# /etc/init.d/mysql stop
?? ?# mysqld_safe --user=mysql --skip-grant-tables --skip-networking &
?? ?# mysql -u root mysql
?? ?mysql> UPDATE user SET Password=PASSWORD('123456') where USER='root' and host='root' or host='localhost';//把空的用戶密碼都修改成非空的密碼就行了。
?? ?mysql> FLUSH PRIVILEGES;
?? ?mysql> quit # /etc/init.d/mysqld restart
?? ?# mysql -uroot -p
?? ?Enter password: <輸入新設(shè)的密碼123456>
?? ?
?? ?MySql5.6操作時報錯：You must SET PASSWORD before executing this statement解決
?? ?mysql>? SET PASSWORD = PASSWORD('123456');
?? ?Query OK, 0 rows affected (0.03 sec)
?? ? update user set password=password('123456') where user='root' and host='root' or host='localhost';
?? ?mysql> create database hr;
?? ?Query OK, 1 row affected (0.01 sec)
?? ?mysql> show databases;
?? ?+--------------------+
?? ?| Database?????????? |
?? ?+--------------------+
?? ?| information_schema |
?? ?| hr???????????????? |
?? ?| mysql????????????? |
?? ?| performance_schema |
?? ?| test?????????????? |
?? ?+--------------------+
?? ?5 rows in set (0.00 sec)
?? ?mysql>
?? ?3.5.2 配置目標端
?? ?GGSCI (ractdg3) 9> create subdirs
?? ?Creating subdirectories under current directory /opt/app/ggs/11.2
?? ?Parameter files??????????????? /opt/app/ggs/11.2/dirprm: already exists
?? ?Report files?????????????????? /opt/app/ggs/11.2/dirrpt: created
?? ?Checkpoint files?????????????? /opt/app/ggs/11.2/dirchk: created
?? ?Process status files?????????? /opt/app/ggs/11.2/dirpcs: created
?? ?SQL script files?????????????? /opt/app/ggs/11.2/dirsql: created
?? ?Database definitions files???? /opt/app/ggs/11.2/dirdef: created
?? ?Extract data files???????????? /opt/app/ggs/11.2/dirdat: created
?? ?Temporary files??????????????? /opt/app/ggs/11.2/dirtmp: created
?? ?Stdout files?????????????????? /opt/app/ggs/11.2/dirout: created
?? ?GGSCI (ractdg3) 5> edit params mgr
?? ?GGSCI (ractdg3) 10> view params mgr
?? ??? ?Port 7809
?? ??? ?--DYNAMICPORTLIST 7840-7939
?? ??? ?--AUTOSTART ER *
?? ??? ?--AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3
?? ??? ?--PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 3
?? ??? ?--LAGREPORTHOURS 1
?? ??? ?--LAGINFOMINUTES 30
?? ??? ?--LAGCRITICALMINUTES 45
?? ?GGSCI (ractdg3) 10> edit param globals
?? ?GGSCI (ractdg3) 11> view param globals
?? ?checkpointtable hr.checkpoint
?? ?GGSCI (ractdg3) 12>
?? ?3.5.3創(chuàng)建checkpoint表
?? ?ggsci>dblogin sourcedb hr userid root
?? ?(這里糾結(jié)了很久，MYSQL 登錄總是報錯：ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
?? ?vi /usr/my.cnf
?? ?添加以下內(nèi)容后
?? ?socket =/tmp/mysql.sock
?? ?
?? ?在ggsci 登錄 沒有問題了，但在LINUX下，登錄又報ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
?? ?在LINUX下登錄，只好先指定 mysql -u root -p -S? '/tmp/mysql.sock'
?? ?進行登錄，后面再另行解決。
?? ?)
?? ?ggsci>add checkpointtable hr.checkpoint
?? ?ggsci>?edit params ./GLOBALS??#GLOBALS必須大寫，編輯GLOBALS需要推出ggsci再進入，輸入：
?? ?CHECKPOINTTABLE hr.checkpoint?
?? ?查看CHECKPOINT TABLE
?? ?mysql> use hr
?? ?Reading table information for completion of table and column names
?? ?You can turn off this feature to get a quicker startup with -A
?? ?Database changed
?? ?mysql> show tables;
?? ?+--------------+
?? ?| Tables_in_hr |
?? ?+--------------+
?? ?| checkpoint?? |
?? ?+--------------+
?? ?1 row in set (0.00 sec)
?? ?mysql>
?? ?3.5.4 創(chuàng)建replicat：
?? ?GGSCI (dg2) 10> add replicat rep2my,exttrail /opt/app/ggs/11.2/dirdat/my,checkpointtable hr.checkpoint
?? ?REPLICAT added.
?? ?GGSCI (ractdg3) 8> info all
?? ?Program???? Status????? Group?????? Lag at Chkpt? Time Since Chkpt
?? ?MANAGER???? RUNNING????????????????????????????????????????? ?
?? ?REPLICAT??? STOPPED???? REP2MY????? 00:00:00????? 00:00:02?? ?
?? ?GGSCI (ractdg3) 9> edit params rep2my
?? ?GGSCI (ractdg3) 10> view params rep2my
?? ?REPLICAT rep2my
?? ?sourcedefs /opt/app/ggs/11.2/dirdef/ext2my.def
?? ?--APPLYNOOPUPDATES
?? ?--HANDLECOLLISIONS
?? ?SOURCEDB hr,userid root,password 123456
?? ?discardfile /opt/app/ggs/11.2/dirrpt/rep2my_discard.log,megabytes 10
?? ?MAP hr.*, TARGET? hr.*;
?? ??? ?
?? ?GGSCI (ractdg3) 19> start REPLICAT REP2MY
?? ?GGSCI (ractdg3) 20> info all
?? ?Program???? Status????? Group?????? Lag at Chkpt? Time Since Chkpt
?? ?MANAGER???? RUNNING????????????????????????????????????????? ?
?? ?REPLICAT??? RUNNING???? REP2MY????? 00:00:00????? 00:00:05
?? ?3.5.5 添加同步表并測試
?? ?[root@ractdg3 11.2]# mysql -u root -p -S '/tmp/mysql.sock'
?? ?Enter password:
?? ?Welcome to the MySQL monitor.? Commands end with ; or \g.
?? ?Your MySQL connection id is 5
?? ?Server version: 5.6.13-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)
?? ?Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
?? ?Oracle is a registered trademark of Oracle Corporation and/or its
?? ?affiliates. Other names may be trademarks of their respective
?? ?owners.
?? ?Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
?? ?mysql> use hr;
?? ?Reading table information for completion of table and column names
?? ?You can turn off this feature to get a quicker startup with -A
?? ?mysql> create table tst(id integer,constraint pk_tst_id primary key (id));
?? ?Query OK, 0 rows affected (0.02 sec)
?? ?mysql> show tables;
?? ?+--------------+
?? ?| Tables_in_hr |
?? ?+--------------+
?? ?| checkpoint?? |
?? ?| tst????????? |
?? ?+--------------+
?? ?2 rows in set (0.00 sec)
?? ?mysql> create table TST(ID INT,CONSTRAINT PK_TST_ID PRIMARY KEY(ID));
?? ?
?? ?mysql> DESC TST;
?? ?+-------+---------+------+-----+---------+-------+
?? ?| Field | Type??? | Null | Key | Default | Extra |
?? ?+-------+---------+------+-----+---------+-------+
?? ?| ID??? | int(11) | NO?? | PRI | 0?????? |?????? |
?? ?+-------+---------+------+-----+---------+-------+
?? ?1 row in set (0.00 sec)
?? ?mysql> select * from TST;
?? ?+----+
?? ?| ID |
?? ?+----+
?? ?|? 3 |
?? ?+----+
?? ?1 row in set (0.00 sec)
?? ?看到數(shù)據(jù)已同步過來了，MYSQL 中，表名有大小寫區(qū)分，開始報表名TST找不到，是在建立表中使用的是tablename:tst
4.完成Oracle分別向Oracle和mysql雙路的單向復(fù)制
?? ?結(jié)合前面的2，3，相關(guān)配置，不再重復(fù)說明，現(xiàn)在我們來看看測試結(jié)果：
?? ?查看一下源服務(wù)器：
?? ?GGSCI (dg1) 22> info all
?? ?Program???? Status????? Group?????? Lag at Chkpt? Time Since Chkpt
?? ?MANAGER???? RUNNING????????????????????????????????????????? ?
?? ?EXTRACT???? RUNNING???? EXT1??????? 00:00:00????? 26:15:27?? ?
?? ?EXTRACT???? RUNNING???? EXT2MY????? 00:00:00????? 00:00:08?? ?
?? ?REPLICAT??? ABENDED???? REP2??????? 00:00:00????? 26:15:13?? ?
?? ?第一個同步的ORACLE 目標服務(wù)器：
?? ?GGSCI (dg2) 8> info all
?? ?Program???? Status????? Group?????? Lag at Chkpt? Time Since Chkpt
?? ?MANAGER???? RUNNING????????????????????????????????????????? ?
?? ?EXTRACT???? ABENDED???? EXT2??????? 00:00:00????? 26:20:36?? ?
?? ?REPLICAT??? RUNNING???? REP1??????? 00:00:00????? 00:00:01?? ?
?? ?第二個同步的MYSQL 目標服務(wù)器：
?? ?GGSCI (ractdg3) 35> info all
?? ?Program???? Status????? Group?????? Lag at Chkpt? Time Since Chkpt
?? ?MANAGER???? RUNNING????????????????????????????????????????? ?
?? ?REPLICAT??? RUNNING???? REP2MY????? 00:00:00????? 00:00:04?? ?
?? ?測試：
?? ?1.在源端插入一條記錄：
?? ?SQL> select * from hr.tst;
?? ??? ?ID
?? ?----------
?? ??? ? 1
?? ??? ? 2
?? ??? ? 3
?? ?SQL> insert into hr.tst values(4);
?? ?1 row created.
?? ?SQL> commit;
?? ?Commit complete.
?? ?SQL>
?? ?2.在目標端1-ORACLE:
?? ??? ?[oracle@dg2 11.2]$ sqlplus / as sysdba
?? ??? ?SQL*Plus: Release 11.2.0.1.0 Production on Sat Aug 31 18:12:44 2013
?? ??? ?Copyright (c) 1982, 2009, Oracle.? All rights reserved.
?? ??? ?Connected to:
?? ??? ?Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
?? ??? ?With the Partitioning, OLAP, Data Mining and Real Application Testing options
?? ??? ?SQL> select * from hr.tst
?? ??? ?? 2? ;
?? ??? ??? ?ID
?? ??? ?----------
?? ??? ??? ? 1
?? ??? ??? ? 2
?? ??? ??? ? 3
?? ??? ?SQL> /
?? ??? ??? ?ID
?? ??? ?----------
?? ??? ??? ? 1
?? ??? ??? ? 2
?? ??? ??? ? 3
?? ??? ?SQL> /
?? ??? ??? ?ID
?? ??? ?----------
?? ??? ??? ? 1
?? ??? ??? ? 2
?? ??? ??? ? 3
?? ??? ??? ? 4
?? ?3.在目標端2：MYSQL ：
?? ?mysql> select * from TST;
?? ?+----+
?? ?| ID |
?? ?+----+
?? ?|? 3 |
?? ?|? 4 |
?? ?+----+
?? ?2 rows in set (0.00 sec)
?? ?說明已過來了。
?? ?從上面數(shù)據(jù)可看到，源端數(shù)據(jù)和目標端不一致，刪除全部數(shù)據(jù)，看到目標端出錯。
?? ?添加參數(shù)：HANDLECOLLISIONS。問題解決。
?? ?（前幾天沒看視頻前，也遇到這問題，搞了半天呀，說來全是淚，后來是重配解決）
?? ?2013-08-31 18:18:44? WARNING OGG-01003? Oracle GoldenGate Delivery for MySQL, rep2my.prm:? Repositioning to rba 1242 in seqno 0.
?? ?2013-08-31 18:18:44? ERROR?? OGG-01296? Oracle GoldenGate Delivery for MySQL, rep2my.prm:? Error mapping from HR.TST to hr.TST.
?? ?2013-08-31 18:18:44? ERROR?? OGG-01668? Oracle GoldenGate Delivery for MySQL, rep2my.prm:? PROCESS ABENDING.
?? ?2013-08-31 18:19:07? INFO??? OGG-00987? Oracle GoldenGate Command Interpreter for MySQL:? GGSCI command (root): start replicat rep2my.
?? ?2013-08-31 18:19:07? INFO??? OGG-00963? Oracle GoldenGate Manager for MySQL, mgr.prm:? Command received from GGSCI on host ractdg3 (START REPLICAT REP2MY ).
?? ?2013-08-31 18:19:07? INFO??? OGG-00975? Oracle GoldenGate Manager for MySQL, mgr.prm:? REPLICAT REP2MY starting.
?? ?2013-08-31 18:19:07? INFO??? OGG-00995? Oracle GoldenGate Delivery for MySQL, rep2my.prm:? REPLICAT REP2MY starting.
?? ?2013-08-31 18:19:07? INFO??? OGG-03035? Oracle GoldenGate Delivery for MySQL, rep2my.prm:? Operating system character set identified as UTF-8. Locale: zh_CN, LC_ALL:.
?? ?2013-08-31 18:19:07? INFO??? OGG-01815? Oracle GoldenGate Delivery for MySQL, rep2my.prm:? Virtual Memory Facilities for: COM
?? ???? anon alloc: mmap(MAP_ANON)? anon free: munmap
?? ???? file alloc: mmap(MAP_SHARED)? file free: munmap
?? ???? target directories:
?? ???? /opt/app/ggs/11.2/dirtmp.
?? ?2013-08-31 18:19:07? INFO??? OGG-00996? Oracle GoldenGate Delivery for MySQL, rep2my.prm:? REPLICAT REP2MY started.
mysql:
http://blog.sina.com.cn/s/blog_4a424eca0100t0z4.html
Goldengate 配置oracle to mysql
http://wenku.baidu.com/view/8089d24dfe4733687e21aa9d.html
http://www.xifenfei.com/1028.html
http://baiying.blog.51cto.com/1068039/646454
雙向
http://ylw6006.blog.51cto.com/470441/909286
輸出完整的操作過程：
1.不使用數(shù)據(jù)泵完成Oracle-Oracle的雙向復(fù)制。
2.使用數(shù)據(jù)泵進行Oracle-Oracle的單向復(fù)制。
3.完成Oracle-MySQL的單向復(fù)制。
4.完成Oracle分別向Oracle和mysql雙路的單向復(fù)制
----------------------------------------------------------------
?? ?OGG 初始化設(shè)置不再敘述，直接進入正題
1.不使用數(shù)據(jù)泵完成Oracle-Oracle的雙向復(fù)制。
?? ?1.1.DG1 服務(wù)器：
?? ?1.1.1 做為源機的MANAGER及EXTRACT 進程配置
?? ?GGSCI (dg1) 152> view params mgr
?? ?Port 7809
?? ?DYNAMICPORTLIST 7840-7939
?? ?--AUTOSTART ER *
?? ?AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3
?? ?PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 3
?? ?LAGREPORTHOURS 1
?? ?LAGINFOMINUTES 30
?? ?LAGCRITICALMINUTES 45
?? ?GGSCI (dg1) 153> add extract ext1,tranlog,begin now
?? ?EXTRACT added.
?? ?GGSCI (dg1) 153> add exttrail ./dirdat/gg,extract ext1,megabytes 100
?? ?EXTTRAIL added.
?? ?GGSCI (dg1) 153> edit params ext1
?? ??? ?EXTRACT? ext1
?? ??? ?USERID ggs,? PASSWORD ggs
?? ??? ?TRANLOGOPTIONS? EXCLUDEUSER ggs
?? ??? ?RMTHOST? 192.168.10.88, MGRPORT 7809
?? ??? ?RMTTRAIL? ./dirdat/gg
?? ??? ?TABLE? hr.* ;
?? ?1.1.2 DG1做為復(fù)制的目標機配置
?? ?GGSCI (dg1) 158> dblogin userid ggs,password ggs
?? ?Successfully logged into database.
?? ?GGSCI (dg1) 159> add checkpointtable ggs.checkpointtable
?? ?Successfully created checkpoint table ggs.checkpointtable.
?? ?GGSCI (dg1) 160> add replicat? rep2,exttrail ./dirdat/gs checkpointtable ggs.checkpointtable
?? ?REPLICAT added.
?? ?GGSCI (dg1) 161> info all
?? ?Program???? Status????? Group?????? Lag at Chkpt? Time Since Chkpt
?? ?MANAGER???? RUNNING????????????????????????????????????????? ?
?? ?EXTRACT???? RUNNING???? EXT1??????? 00:00:00????? 00:00:05?? ?
?? ?REPLICAT??? STOPPED???? REP2??????? 00:00:00????? 00:00:02
?? ?GGSCI (dg1) 4> edit params rep2
?? ?GGSCI (dg1) 5> view params rep2
?? ??? ?REPLICAT rep2
?? ??? ?ASSUMETARGETDEFS
?? ??? ?USERID ggs, PASSWORD? ggs
?? ??? ?MAP hr.*, TARGET? hr.*;
?? ?GGSCI (dg1) 20> start replicat rep2
?? ?Sending START request to MANAGER ...
?? ?REPLICAT REP2 starting
?? ?GGSCI (dg1) 21> info all
?? ?Program???? Status????? Group?????? Lag at Chkpt? Time Since Chkpt
?? ?MANAGER???? RUNNING????????????????????????????????????????? ?
?? ?EXTRACT???? RUNNING???? EXT1??????? 00:00:00????? 00:00:04?? ?
?? ?REPLICAT??? RUNNING???? REP2??????? 00:00:00????? 00:00:04 ?
?? ?1.2.DG2 服務(wù)器
?? ?1.2.1 DG2做為復(fù)制的目標機配置
?? ??? ?GGSCI (dg2) 1> dblogin userid ggs,password ggs
?? ??? ?Successfully logged into database.
?? ??? ?GGSCI (dg2) 2> add checkpointtable ggs.checkpointtable
?? ??? ?Successfully created checkpoint table ggs.checkpointtable.
?? ??? ?GGSCI (dg2) 11> add replicat? rep1,exttrail ./dirdat/gg checkpointtable ggs.checkpoint
?? ??? ?REPLICAT added.
?? ??? ?GGSCI (dg2) 12> info all
?? ??? ?Program???? Status????? Group?????? Lag at Chkpt? Time Since Chkpt
?? ??? ?MANAGER???? RUNNING????????????????????????????????????????? ?
?? ??? ?REPLICAT??? STOPPED???? REP1??????? 00:00:00????? 00:00:03?? ?
?? ??? ?GGSCI (dg2) 13> view params rep1
?? ??? ?REPLICAT rep1
?? ??? ?ASSUMETARGETDEFS
?? ??? ?USERID ggs, PASSWORD? ggs
?? ??? ? ?
?? ??? ?MAP hr.*, TARGET? hr.*;
?? ??? ?GGSCI (dg2) 14> start rep1
?? ??? ?Sending START request to MANAGER ...
?? ??? ?REPLICAT REP1 starting
?? ??? ?GGSCI (dg2) 15> info all
?? ??? ?Program???? Status????? Group?????? Lag at Chkpt? Time Since Chkpt
?? ??? ?MANAGER???? RUNNING????????????????????????????????????????? ?
?? ??? ?REPLICAT??? RUNNING???? REP1??????? 00:00:00????? 00:00:01 ?
?? ?1.2.1 DG2做為復(fù)制的源機配置
?? ??? ?
?? ?GGSCI (dg2) 9> add extract ext2,tranlog,begin now
?? ?EXTRACT added.
?? ?GGSCI (dg2) 10> add exttrail ./dirdat/gs,extract ext2,megabytes 100
?? ?EXTTRAIL added.
?? ?GGSCI (dg2) 12> edit params ext2
?? ??? ??? ?EXTRACT? ext2
?? ??? ??? ?USERID ggs,? PASSWORD ggs
?? ??? ??? ?TRANLOGOPTIONS? EXCLUDEUSER ggs
?? ??? ??? ?RMTHOST? 192.168.10.87, MGRPORT 7809
?? ??? ??? ?RMTTRAIL? ./dirdat/gs
?? ??? ??? ?TABLE? hr.* ;?? ?
?? ??? ?GGSCI (dg2) 14> start extract ext2
?? ??? ?Sending START request to MANAGER ...
?? ??? ?EXTRACT EXT2 starting
?? ??? ?GGSCI (dg2) 15> start replicat rep1
?? ??? ?Sending START request to MANAGER ...
?? ??? ?REPLICAT REP1 starting
?? ??? ?GGSCI (dg2) 16> info all
?? ??? ?Program???? Status????? Group?????? Lag at Chkpt? Time Since Chkpt
?? ??? ?MANAGER???? RUNNING????????????????????????????????????????? ?
?? ??? ?EXTRACT???? RUNNING???? EXT2??????? 00:01:47????? 00:00:00?? ?
?? ??? ?REPLICAT??? ABENDED???? REP1??????? 00:00:00????? 00:28:12?? ?
?? ??? ?GGSCI (dg2) 17> info all
?? ??? ?Program???? Status????? Group?????? Lag at Chkpt? Time Since Chkpt
?? ??? ?MANAGER???? RUNNING????????????????????????????????????????? ?
?? ??? ?EXTRACT???? RUNNING???? EXT2??????? 00:00:00????? 00:00:09?? ?
?? ??? ?REPLICAT??? ABENDED???? REP1??????? 00:00:00????? 00:29:05
?? ?測試：
?? ?兩臺服務(wù)順OGG狀態(tài)：
?? ?GGSCI (dg1) 17> info all
?? ?Program???? Status????? Group?????? Lag at Chkpt? Time Since Chkpt
?? ?MANAGER???? RUNNING????????????????????????????????????????? ?
?? ?EXTRACT???? RUNNING???? EXT1??????? 00:00:00????? 00:00:08?? ?
?? ?REPLICAT??? RUNNING???? REP2??????? 00:00:00????? 00:00:09?? ?
?? ?GGSCI (dg2) 51> info all
?? ?Program???? Status????? Group?????? Lag at Chkpt? Time Since Chkpt
?? ?MANAGER???? RUNNING????????????????????????????????????????? ?
?? ?EXTRACT???? RUNNING???? EXT2??????? 00:00:00????? 00:00:07?? ?
?? ?REPLICAT??? RUNNING???? REP1??????? 00:00:00????? 00:00:01?? ?
?? ?在DB1 中建立表，插入數(shù)據(jù)并提交
?? ?SQL> CREATE TABLE TST (ID NUMBER(5), CONSTRAINT PK_TST_ID PRIMARY KEY (ID)? );
?? ?Table created.
?? ?SQL> insert into tst values(1);
?? ?1 row created.
?? ?SQL> commit;
?? ?Commit complete.
?? ?SQL> select * from tst;
?? ?
?? ?在DB2 中建立表，并查詢
?? ?SQL> CREATE TABLE TST (ID NUMBER(5), CONSTRAINT PK_TST_ID PRIMARY KEY (ID)? );
?? ?Table created.
?? ?SQL> select * from tst;
?? ?no rows selected
?? ?SQL> /
?? ?no rows selected
?? ?SQL> /
?? ??? ?ID
?? ?----------
?? ??? ? 1
?? ?SQL> insert into tst values(2);
?? ?1 row created.
?? ?SQL> commit;
?? ?Commit complete.
?? ?SQL> select * from tst;
?? ??? ?ID
?? ?----------
?? ??? ? 1
?? ??? ? 2
?? ?完成后在DB1中查詢：
?? ?SQL> select * from tst;
?? ??? ?ID
?? ?----------
?? ??? ? 1
?? ?SQL> /
?? ??? ?ID
?? ?----------
?? ??? ? 1
?? ??? ? 2
?? ?說明數(shù)據(jù)已過來了。
==================================================================
2.使用數(shù)據(jù)泵進行Oracle-Oracle的單向復(fù)制
?? ?(第十課中，我就是使用了數(shù)據(jù)泵的方式配置了單向復(fù)制，現(xiàn)抄錄如下)
?? ?2.1 配置抽取進程：
?? ?GGSCI (node1) 6> add extract extnd,tranlog,begin now
?? ?EXTRACT added.
?? ?GGSCI (node1) 7> add exttrail ./dirdat/nd,extract extnd,megabytes 100
?? ?EXTTRAIL added.
?? ?GGSCI (node1) 8> edit params extnd
?? ?（下面為配置文件內(nèi)容）
?? ?EXTRACT extnd
?? ?SETENV (NLS_LANG = "AMERICAN_AMERICA.UTF8")
?? ?SETENV (ORACLE_HOME = "/opt/app/oracle/product/11.2.0")
?? ?USERID ggs@db1, PASSWORD ggs
?? ?--GETTRUNCATES
?? ?REPORTCOUNT EVERY 1 MINUTES, RATE
?? ?DISCARDFILE ./dirrpt/extnd.dsc,APPEND,MEGABYTES 1024
?? ?--THREADOPTIONS? MAXCOMMITPROPAGATIONDELAY 60000 IOLATENS 60000
?? ?DBOPTIONS? ALLOWUNUSEDCOLUMN
?? ?WARNLONGTRANS 2h,CHECKINTERVAL 3m
?? ?EXTTRAIL ./dirdat/nd
?? ?--TRANLOGOPTIONS EXCLUDEUSER USERNAME
?? ?FETCHOPTIONS NOUSESNAPSHOT
?? ?TRANLOGOPTIONS? CONVERTUCS2CLOBS
?? ?TABLE hr.employee;
?? ?TABLE hr.department;
?? ?2.2添加傳輸進程，配置參數(shù)
?? ?GGSCI (dg1) 24> add extract dpend,exttrailsource ./dirdat/nd
?? ?EXTRACT added.
?? ?GGSCI (dg1) 1> add rmttrail /opt/app/ggs/11.2/dirdat/nd,EXTRACT DPEND
?? ?RMTTRAIL added.
?? ?GGSCI (dg1) 2>
?? ?GGSCI (dg1) 2> edit params dpend?? ?
?? ?EXTRACT dpend
?? ?SETENV (NLS_LANG = AMERICAN_AMERICA.UTF8)
?? ?USERID ggs@db1, PASSWORD ggs
?? ?PASSTHRU
?? ?RMTHOST 192.168.10.88, MGRPORT 7809, compress
?? ?RMTTRAIL /opt/app/ggs/11.2/dirdat/nd
?? ?TABLE hr.depoarment;
?? ?TABLE hr.employee;
?? ?
?? ?2.3 在目標數(shù)據(jù)庫上配置replicat進程
?? ? 2.3.1 配置replicat
?? ?創(chuàng)建checkpoint表
?? ?ggsci>dblogin userid ggs,password ggs
?? ?ggsci>add checkpointtable ggs.checkpoint
?? ?ggsci>?edit params ./GLOBALS??#GLOBALS必須大寫，編輯GLOBALS需要推出ggsci再進入，輸入：
?? ?CHECKPOINTTABLE ggs.checkpoint?
?? ?2.3.2 創(chuàng)建replicat：
?? ?GGSCI (dg2) 8> dblogin userid ggs,password ggs
?? ?Successfully logged into database.
?? ?GGSCI (dg2) 9> add checkpointtable ggs.checkpoint
?? ?Successfully created checkpoint table ggs.checkpoint.
?? ?GGSCI (dg2) 10> add replicat repnd,exttrail /opt/app/ggs/11.2/dirdat/nd,checkpointtable ggs.checkpoint
?? ?REPLICAT added.
?? ?2.3.3?啟動extract及replicat
?? ?2.3.3.1、啟動extract?保證mgr已啟動。
?? ?GGSCI (dg1) 7> start dpend
?? ?EXTRACT DPEND is already running.
?? ?GGSCI (dg1) 8> start extnd
?? ?EXTRACT EXTND is already running.
?? ?GGSCI (dg1) 9> info all
?? ?Program???? Status????? Group?????? Lag at Chkpt? Time Since Chkpt
?? ?MANAGER???? RUNNING????????????????????????????????????????? ?
?? ?EXTRACT???? RUNNING???? DPEND?????? 00:00:00????? 00:00:07?? ?
?? ?EXTRACT???? RUNNING???? EXTND?????? 00:00:00????? 00:00:10? ?
?? ?GGSCI (dg2) 16> start REPND
?? ?REPLICAT REPND is already running.
?? ?GGSCI (dg2) 17> info all
?? ?Program???? Status????? Group?????? Lag at Chkpt? Time Since Chkpt
?? ?MANAGER???? RUNNING????????????????????????????????????????? ?
?? ?REPLICAT??? RUNNING???? REPND?????? 00:00:00????? 00:00:10?? ?
?
?? ?
?? ?
=======================================================================
3.完成Oracle-MySQL的單向復(fù)制。
3.1. download mysql and goldengate:
https://edelivery.oracle.com/EPD/Download/get_form?egroup_aru_number=14841440
https://edelivery.oracle.com/EPD/Search/handle_go
3.2. MYSQL INSTALL :
?? ?rpm -ivh /download/MySQL-server-advanced-5.6.13-1.rhel5.i386.rpm
?? ?rpm -ivh /download/MySQL-client-advanced-5.6.13-1.rhel5.i386.rpm
3.3. 安裝goldengate
?? ?$?Su?–?oracle?$?mkdir?-p?/opt/app/ggs/11.2
?? ?$?chown?-R?oracle:oinstall?/opt/app/ggs/11.2??$?chmod?-R?775?/opt/app/ggs/11.2
?? ?$?cd?/opt/app/ggs/11.2?
?? ?$?unzip?V32420-01.zip
?? ?$?tar xvf ggs_Linux_x86_MySQL_32bit.tar
?? ?cd?/opt/app/ggs/11.2
?? ?[oracle@dg2 11.2]$./ggsci
?? ?
3.4 配置源端服務(wù)器
?? ?
?? ?在先前已民配置好目標端MANAGE的前提下，進行以下配置
?? ?3.4.1 添加extract
?? ?GGSCI (dg1) 1> add extract ext2my,tranlog,begin now
?? ?EXTRACT added.
?? ?GGSCI (dg1) 2> add exttrail ./dirdat/my,extract ext2my,megabytes 100
?? ?EXTTRAIL added.
?? ?GGSCI (dg1) 3> edit params ext2my
?? ??? ?EXTRACT? ext2my
?? ??? ?USERID ggs,? PASSWORD ggs
?? ??? ?TRANLOGOPTIONS? EXCLUDEUSER ggs
?? ??? ?RMTHOST? 192.168.10.85, MGRPORT 7809
?? ??? ?RMTTRAIL? ./dirdat/my
?? ??? ?TABLE? hr.* ;
?? ?GGSCI (dg1) 5> info all
?? ?Program???? Status????? Group?????? Lag at Chkpt? Time Since Chkpt
?? ?MANAGER???? STOPPED????????????????????????????????????????? ?
?? ?EXTRACT???? ABENDED???? EXT1??????? 00:00:00????? 17:48:59?? ?
?? ?EXTRACT???? STOPPED???? EXT2MY????? 00:00:00????? 00:00:46?? ?
?? ?REPLICAT??? ABENDED???? REP2??????? 00:00:00????? 17:48:45?? ?
?? ?3.4.2 添加對象定義文件
?? ?GGSCI (dg1) 7> edit param defgen
?? ?GGSCI (dg1) 9> view param defgen
?? ?defsfile /opt/app/ggs/11.2/dirdef/ext2my.def
?? ?userid ggs,password ggs
?? ?table hr.*;
?? ?生成定義文件并拷貝到目標服務(wù)器
?? ?./defgen paramfile dirprm/defgen.prm
?? ??? ?[oracle@dg1 11.2]$ cat dirdef/ext2my.def
?? ??? ?*+- Defgen version 2.0, Encoding UTF-8
?? ??? ?*
?? ??? ?* Definitions created/modified? 2013-08-31 16:49
?? ??? ?*
?? ??? ?*? Field descriptions for each column entry:
?? ??? ?*
?? ??? ?*???? 1??? Name
?? ??? ?*???? 2??? Data Type
?? ??? ?*???? 3??? External Length
?? ??? ?*???? 4??? Fetch Offset
?? ??? ?*???? 5??? Scale
?? ??? ?*???? 6??? Level
?? ??? ?*???? 7??? Null
?? ??? ?*???? 8??? Bump if Odd
?? ??? ?*???? 9??? Internal Length
?? ??? ?*??? 10??? Binary Length
?? ??? ?*??? 11??? Table Length
?? ??? ?*??? 12??? Most Significant DT
?? ??? ?*??? 13??? Least Significant DT
?? ??? ?*??? 14??? High Precision
?? ??? ?*??? 15??? Low Precision
?? ??? ?*??? 16??? Elementary Item
?? ??? ?*??? 17??? Occurs
?? ??? ?*??? 18??? Key Column
?? ??? ?*??? 19??? Sub Data Type
?? ??? ?*
?? ??? ?Database type: ORACLE
?? ??? ?Character set ID: windows-936
?? ??? ?National character set ID: UTF-16
?? ??? ?Locale: neutral
?? ??? ?Case sensitivity: 14 14 14 14 14 14 14 14 14 14 14 14 11 14 14 14
?? ??? ?*
?? ??? ?Definition for table HR.DEPARTMENT
?? ??? ?Record length: 104
?? ??? ?Syskey: 0
?? ??? ?Columns: 3
?? ??? ?DEP_ID??? 134???? 11??????? 0? 0? 0 1 0????? 8????? 8????? 8 0 0 0 0 1??? 0 1 3
?? ??? ?DEP_NAME?? 64???? 30?????? 12? 0? 0 1 0???? 30???? 30????? 0 0 0 0 0 1??? 0 0 0
?? ??? ?DEP_DESC?? 64???? 50?????? 48? 0? 0 1 0???? 50???? 50????? 0 0 0 0 0 1??? 0 0 0
?? ??? ?End of definition
?? ??? ?*
?? ??? ?Definition for table HR.EMPLOYEE
?? ??? ?Record length: 48
?? ??? ?Syskey: 0
?? ??? ?Columns: 2
?? ??? ?EMP_ID??? 134???? 11??????? 0? 0? 0 1 0????? 8????? 8????? 8 0 0 0 0 1??? 0 1 3
?? ??? ?EMP_NAME?? 64???? 30?????? 12? 0? 0 1 0???? 30???? 30????? 0 0 0 0 0 1??? 0 0 0
?? ??? ?End of definition
?? ??? ?*
?? ??? ?Definition for table HR.TST
?? ??? ?Record length: 12
?? ??? ?Syskey: 0
?? ??? ?Columns: 1
?? ??? ?ID? 134????? 8??????? 0? 0? 0 1 0????? 8????? 8????? 8 0 0 0 0 1??? 0 1 3
?? ??? ?End of definition
?? ?scp /opt/app/ggs/11.2/dirdef/ext2my.def ractdg3:/opt/app/ggs/11.2/dirdef/ext2my.def
?? ?
3.5 配置目標端服務(wù)器
?? ?3.5.1 配置MYSQL SERVER
?? ?# mysql -u root -p password '123456'
?? ?Enter password:
?? ?mysqladmin: connect to server at 'localhost' failed
?? ?error: 'Access denied for user 'root'@'localhost' (using password: YES)'
?? ?
?? ?修改MYSQL 密碼 操作如下：
?? ?# /etc/init.d/mysql stop
?? ?# mysqld_safe --user=mysql --skip-grant-tables --skip-networking &
?? ?# mysql -u root mysql
?? ?mysql> UPDATE user SET Password=PASSWORD('123456') where USER='root' and host='root' or host='localhost';//把空的用戶密碼都修改成非空的密碼就行了。
?? ?mysql> FLUSH PRIVILEGES;
?? ?mysql> quit # /etc/init.d/mysqld restart
?? ?# mysql -uroot -p
?? ?Enter password: <輸入新設(shè)的密碼123456>
?? ?
?? ?MySql5.6操作時報錯：You must SET PASSWORD before executing this statement解決
?? ?mysql>? SET PASSWORD = PASSWORD('123456');
?? ?Query OK, 0 rows affected (0.03 sec)
?? ? update user set password=password('123456') where user='root' and host='root' or host='localhost';
?? ?mysql> create database hr;
?? ?Query OK, 1 row affected (0.01 sec)
?? ?mysql> show databases;
?? ?+--------------------+
?? ?| Database?????????? |
?? ?+--------------------+
?? ?| information_schema |
?? ?| hr???????????????? |
?? ?| mysql????????????? |
?? ?| performance_schema |
?? ?| test?????????????? |
?? ?+--------------------+
?? ?5 rows in set (0.00 sec)
?? ?mysql>
?? ?3.5.2 配置目標端
?? ?GGSCI (ractdg3) 9> create subdirs
?? ?Creating subdirectories under current directory /opt/app/ggs/11.2
?? ?Parameter files??????????????? /opt/app/ggs/11.2/dirprm: already exists
?? ?Report files?????????????????? /opt/app/ggs/11.2/dirrpt: created
?? ?Checkpoint files?????????????? /opt/app/ggs/11.2/dirchk: created
?? ?Process status files?????????? /opt/app/ggs/11.2/dirpcs: created
?? ?SQL script files?????????????? /opt/app/ggs/11.2/dirsql: created
?? ?Database definitions files???? /opt/app/ggs/11.2/dirdef: created
?? ?Extract data files???????????? /opt/app/ggs/11.2/dirdat: created
?? ?Temporary files??????????????? /opt/app/ggs/11.2/dirtmp: created
?? ?Stdout files?????????????????? /opt/app/ggs/11.2/dirout: created
?? ?GGSCI (ractdg3) 5> edit params mgr
?? ?GGSCI (ractdg3) 10> view params mgr
?? ??? ?Port 7809
?? ??? ?--DYNAMICPORTLIST 7840-7939
?? ??? ?--AUTOSTART ER *
?? ??? ?--AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3
?? ??? ?--PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 3
?? ??? ?--LAGREPORTHOURS 1
?? ??? ?--LAGINFOMINUTES 30
?? ??? ?--LAGCRITICALMINUTES 45
?? ?GGSCI (ractdg3) 10> edit param globals
?? ?GGSCI (ractdg3) 11> view param globals
?? ?checkpointtable hr.checkpoint
?? ?GGSCI (ractdg3) 12>
?? ?3.5.3創(chuàng)建checkpoint表
?? ?ggsci>dblogin sourcedb hr userid root
?? ?(這里糾結(jié)了很久，MYSQL 登錄總是報錯：ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
?? ?vi /usr/my.cnf
?? ?添加以下內(nèi)容后
?? ?socket =/tmp/mysql.sock
?? ?
?? ?在ggsci 登錄 沒有問題了，但在LINUX下，登錄又報ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
?? ?在LINUX下登錄，只好先指定 mysql -u root -p -S? '/tmp/mysql.sock'
?? ?進行登錄，后面再另行解決。
?? ?)
?? ?ggsci>add checkpointtable hr.checkpoint
?? ?ggsci>?edit params ./GLOBALS??#GLOBALS必須大寫，編輯GLOBALS需要推出ggsci再進入，輸入：
?? ?CHECKPOINTTABLE hr.checkpoint?
?? ?查看CHECKPOINT TABLE
?? ?mysql> use hr
?? ?Reading table information for completion of table and column names
?? ?You can turn off this feature to get a quicker startup with -A
?? ?Database changed
?? ?mysql> show tables;
?? ?+--------------+
?? ?| Tables_in_hr |
?? ?+--------------+
?? ?| checkpoint?? |
?? ?+--------------+
?? ?1 row in set (0.00 sec)
?? ?mysql>
?? ?3.5.4 創(chuàng)建replicat：
?? ?GGSCI (dg2) 10> add replicat rep2my,exttrail /opt/app/ggs/11.2/dirdat/my,checkpointtable hr.checkpoint
?? ?REPLICAT added.
?? ?GGSCI (ractdg3) 8> info all
?? ?Program???? Status????? Group?????? Lag at Chkpt? Time Since Chkpt
?? ?MANAGER???? RUNNING????????????????????????????????????????? ?
?? ?REPLICAT??? STOPPED???? REP2MY????? 00:00:00????? 00:00:02?? ?
?? ?GGSCI (ractdg3) 9> edit params rep2my
?? ?GGSCI (ractdg3) 10> view params rep2my
?? ?REPLICAT rep2my
?? ?sourcedefs /opt/app/ggs/11.2/dirdef/ext2my.def
?? ?--APPLYNOOPUPDATES
?? ?--HANDLECOLLISIONS
?? ?SOURCEDB hr,userid root,password 123456
?? ?discardfile /opt/app/ggs/11.2/dirrpt/rep2my_discard.log,megabytes 10
?? ?MAP hr.*, TARGET? hr.*;
?? ??? ?
?? ?GGSCI (ractdg3) 19> start REPLICAT REP2MY
?? ?GGSCI (ractdg3) 20> info all
?? ?Program???? Status????? Group?????? Lag at Chkpt? Time Since Chkpt
?? ?MANAGER???? RUNNING????????????????????????????????????????? ?
?? ?REPLICAT??? RUNNING???? REP2MY????? 00:00:00????? 00:00:05
?? ?3.5.5 添加同步表并測試
?? ?[root@ractdg3 11.2]# mysql -u root -p -S '/tmp/mysql.sock'
?? ?Enter password:
?? ?Welcome to the MySQL monitor.? Commands end with ; or \g.
?? ?Your MySQL connection id is 5
?? ?Server version: 5.6.13-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)
?? ?Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
?? ?Oracle is a registered trademark of Oracle Corporation and/or its
?? ?affiliates. Other names may be trademarks of their respective
?? ?owners.
?? ?Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
?? ?mysql> use hr;
?? ?Reading table information for completion of table and column names
?? ?You can turn off this feature to get a quicker startup with -A
?? ?mysql> create table tst(id integer,constraint pk_tst_id primary key (id));
?? ?Query OK, 0 rows affected (0.02 sec)
?? ?mysql> show tables;
?? ?+--------------+
?? ?| Tables_in_hr |
?? ?+--------------+
?? ?| checkpoint?? |
?? ?| tst????????? |
?? ?+--------------+
?? ?2 rows in set (0.00 sec)
?? ?mysql> create table TST(ID INT,CONSTRAINT PK_TST_ID PRIMARY KEY(ID));
?? ?
?? ?mysql> DESC TST;
?? ?+-------+---------+------+-----+---------+-------+
?? ?| Field | Type??? | Null | Key | Default | Extra |
?? ?+-------+---------+------+-----+---------+-------+
?? ?| ID??? | int(11) | NO?? | PRI | 0?????? |?????? |
?? ?+-------+---------+------+-----+---------+-------+
?? ?1 row in set (0.00 sec)
?? ?mysql> select * from TST;
?? ?+----+
?? ?| ID |
?? ?+----+
?? ?|? 3 |
?? ?+----+
?? ?1 row in set (0.00 sec)
?? ?看到數(shù)據(jù)已同步過來了，MYSQL 中，表名有大小寫區(qū)分，開始報表名TST找不到，是在建立表中使用的是tablename:tst
4.完成Oracle分別向Oracle和mysql雙路的單向復(fù)制
?? ?結(jié)合前面的2，3，相關(guān)配置，不再重復(fù)說明，現(xiàn)在我們來看看測試結(jié)果：
?? ?查看一下源服務(wù)器：
?? ?GGSCI (dg1) 22> info all
?? ?Program???? Status????? Group?????? Lag at Chkpt? Time Since Chkpt
?? ?MANAGER???? RUNNING????????????????????????????????????????? ?
?? ?EXTRACT???? RUNNING???? EXT1??????? 00:00:00????? 26:15:27?? ?
?? ?EXTRACT???? RUNNING???? EXT2MY????? 00:00:00????? 00:00:08?? ?
?? ?REPLICAT??? ABENDED???? REP2??????? 00:00:00????? 26:15:13?? ?
?? ?第一個同步的ORACLE 目標服務(wù)器：
?? ?GGSCI (dg2) 8> info all
?? ?Program???? Status????? Group?????? Lag at Chkpt? Time Since Chkpt
?? ?MANAGER???? RUNNING????????????????????????????????????????? ?
?? ?EXTRACT???? ABENDED???? EXT2??????? 00:00:00????? 26:20:36?? ?
?? ?REPLICAT??? RUNNING???? REP1??????? 00:00:00????? 00:00:01?? ?
?? ?第二個同步的MYSQL 目標服務(wù)器：
?? ?GGSCI (ractdg3) 35> info all
?? ?Program???? Status????? Group?????? Lag at Chkpt? Time Since Chkpt
?? ?MANAGER???? RUNNING????????????????????????????????????????? ?
?? ?REPLICAT??? RUNNING???? REP2MY????? 00:00:00????? 00:00:04?? ?
?? ?測試：
?? ?1.在源端插入一條記錄：
?? ?SQL> select * from hr.tst;
?? ??? ?ID
?? ?----------
?? ??? ? 1
?? ??? ? 2
?? ??? ? 3
?? ?SQL> insert into hr.tst values(4);
?? ?1 row created.
?? ?SQL> commit;
?? ?Commit complete.
?? ?SQL>
?? ?2.在目標端1-ORACLE:
?? ??? ?[oracle@dg2 11.2]$ sqlplus / as sysdba
?? ??? ?SQL*Plus: Release 11.2.0.1.0 Production on Sat Aug 31 18:12:44 2013
?? ??? ?Copyright (c) 1982, 2009, Oracle.? All rights reserved.
?? ??? ?Connected to:
?? ??? ?Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
?? ??? ?With the Partitioning, OLAP, Data Mining and Real Application Testing options
?? ??? ?SQL> select * from hr.tst
?? ??? ?? 2? ;
?? ??? ??? ?ID
?? ??? ?----------
?? ??? ??? ? 1
?? ??? ??? ? 2
?? ??? ??? ? 3
?? ??? ?SQL> /
?? ??? ??? ?ID
?? ??? ?----------
?? ??? ??? ? 1
?? ??? ??? ? 2
?? ??? ??? ? 3
?? ??? ?SQL> /
?? ??? ??? ?ID
?? ??? ?----------
?? ??? ??? ? 1
?? ??? ??? ? 2
?? ??? ??? ? 3
?? ??? ??? ? 4
?? ?3.在目標端2：MYSQL ：
?? ?mysql> select * from TST;
?? ?+----+
?? ?| ID |
?? ?+----+
?? ?|? 3 |
?? ?|? 4 |
?? ?+----+
?? ?2 rows in set (0.00 sec)
?? ?說明已過來了。
?? ?從上面數(shù)據(jù)可看到，源端數(shù)據(jù)和目標端不一致，刪除全部數(shù)據(jù)，看到目標端出錯。
?? ?添加參數(shù)：HANDLECOLLISIONS。問題解決。
?? ?（前幾天沒看視頻前，也遇到這問題，搞了半天呀，說來全是淚，后來是重配解決）
?? ?2013-08-31 18:18:44? WARNING OGG-01003? Oracle GoldenGate Delivery for MySQL, rep2my.prm:? Repositioning to rba 1242 in seqno 0.
?? ?2013-08-31 18:18:44? ERROR?? OGG-01296? Oracle GoldenGate Delivery for MySQL, rep2my.prm:? Error mapping from HR.TST to hr.TST.
?? ?2013-08-31 18:18:44? ERROR?? OGG-01668? Oracle GoldenGate Delivery for MySQL, rep2my.prm:? PROCESS ABENDING.
?? ?2013-08-31 18:19:07? INFO??? OGG-00987? Oracle GoldenGate Command Interpreter for MySQL:? GGSCI command (root): start replicat rep2my.
?? ?2013-08-31 18:19:07? INFO??? OGG-00963? Oracle GoldenGate Manager for MySQL, mgr.prm:? Command received from GGSCI on host ractdg3 (START REPLICAT REP2MY ).
?? ?2013-08-31 18:19:07? INFO??? OGG-00975? Oracle GoldenGate Manager for MySQL, mgr.prm:? REPLICAT REP2MY starting.
?? ?2013-08-31 18:19:07? INFO??? OGG-00995? Oracle GoldenGate Delivery for MySQL, rep2my.prm:? REPLICAT REP2MY starting.
?? ?2013-08-31 18:19:07? INFO??? OGG-03035? Oracle GoldenGate Delivery for MySQL, rep2my.prm:? Operating system character set identified as UTF-8. Locale: zh_CN, LC_ALL:.
?? ?2013-08-31 18:19:07? INFO??? OGG-01815? Oracle GoldenGate Delivery for MySQL, rep2my.prm:? Virtual Memory Facilities for: COM
?? ???? anon alloc: mmap(MAP_ANON)? anon free: munmap
?? ???? file alloc: mmap(MAP_SHARED)? file free: munmap
?? ???? target directories:
?? ???? /opt/app/ggs/11.2/dirtmp.
?? ?2013-08-31 18:19:07? INFO??? OGG-00996? Oracle GoldenGate Delivery for MySQL, rep2my.prm:? REPLICAT REP2MY started.
mysql:
http://blog.sina.com.cn/s/blog_4a424eca0100t0z4.html
Goldengate 配置oracle to mysql
http://wenku.baidu.com/view/8089d24dfe4733687e21aa9d.html
http://www.xifenfei.com/1028.html
http://baiying.blog.51cto.com/1068039/646454
雙向
http://ylw6006.blog.51cto.com/470441/909286
總結(jié)
以上是生活随笔為你收集整理的【ORACLE 高可用】作业 ：配置ORACLE GoldenGate 2的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 【ORACLE 高可用】 作业 ：配置O
- 下一篇： 【ORACLE 高可用】 ORACLE