Oracle主从配置

Myth丶恋晨 2022-10-29 01:50 39阅读 0赞

Oracle GoldenGate配置文档

一.源端配置  
1.数据库开启归档  
查看数据库是否开启归档  
sqlplus / as sysdba  
SQL> archive log list;

数据库开启归档  
shutdown immediate;  
startup mount;

alter database archivelog;  
执行上边命令如果遇到ORA-00265: instance recovery required, cannot set ARCHIVELOG mode  
将数据库起到open状态，正常关闭shutdown immediate，再起到mount状态  
解决办法：alter database open ; 再从shutdown immediate往下执行

alter database open;  
alter system archive log start;

启用库级别补充日志  
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;  
SQL> ALTER SYSTEM SWITCH LOGFILE;

开启数据库强制日志模式  
alter database force logging;

检查SQL> SELECT SUPPLEMENTAL\_LOG\_DATA\_MIN FROM V$DATABASE;

2.安装goldengate  
创建ogg安装目录（用root用户）  
mkdir /opt/ogg  
chown oracle:oinstall /datafile/ogg -R

将压缩文件复制到ogg安装路径下,并更改属主  
chown oracle:oinstall /datafile/ogg/fbo\_ggs\_Linux\_x64\_ora11g\_64bit.tar

切换到oracle用户并解压  
su - oracle  
cd /opt/ogg  
tar -xvf fbo\_ggs\_Linux\_x64\_ora11g\_64bit.tar

设置PATH及LD\_LIBRARY\_PARH  
vim ~/.bash\_profile

export PATH=/datafile/ogg: O R A C L E H O M E / b i n : ORACLE\_HOME/bin: ORACLEHOME/bin:PATH  
export LD\_LIBRARY\_PATH=/datafile/ogg:$ORACLE\_HOME/lib

在ogg安装路径执行  
./ggsci

3.源端ogg配置  
1)在GGSCI中，创建ogg工作目录  
GGSCI (ogg) 1> CREATE SUBDIRS  
GGSCI (ogg) 1> exit

2)添加支持DDL的用户goldengate  
mkdir /opt/oracle/oggdata  
sqlplus / as sysdba  
create tablespace ogg\_tbs logging datafile ‘/datafile/oracle/oggdata/ogg\_tbs.dbf’ size 50m autoextend on next 50m maxsize 20480m extent management local;

CREATE USER goldengate IDENTIFIED BY goldengate default tablespace ogg\_tbs;  
GRANT CONNECT,RESOURCE,DBA TO goldengate;  
grant unlimited tablespace to goldengate;  
grant execute on utl\_file to goldengate;  
grant create table, create sequence to goldengate;

3)在GGSCI中,执行以下命令  
EDIT PARAMS ./GLOBALS

输入以下参数  
GGSCHEMA goldengate

4)在SQL\*Plus中执行以下命令  
sqlplus / as sysdba  
@sequence.sql  
输入：goldengate

GRANT EXECUTE on goldengate.updateSequence TO goldengate;

对sys.seq 添 加 补 充 日 志 a l t e r t a b l e s y s . s e q 添加补充日志 alter table sys.seq 添加补充日志altertablesys.seq add supplemental log data (primary key) columns;

安装DDL对象  
GRANT EXECUTE ON utl\_file to goldengate;  
@marker\_setup.sql  
@ddl\_setup.sql  
@role\_setup.sql  
GRANT GGS\_GGSUSER\_ROLE TO goldengate;  
@ddl\_enable.sql

5)配置ogg参数  
EDIT PARAMS mgr

文件添加  
PORT 7809  
DYNAMICPORTLIST 7810-7820, 7830  
AUTOSTART EXTRACT \*  
AUTORESTART EXTRACT *, RETRIES 4, WAITMINUTES 4  
PURGEOLDEXTRACTS /opt/ogg/dirdat/tt*, USECHECKPOINTS, MINKEEPHOURS 2

6)配置extract以获取数据变更  
add extract extfull, tranlog,begin now  
EDIT PARAMS extfull  
输入参数  
EXTRACT extfull  
setenv ( NLS\_LANG = “AMERICAN\_AMERICA.ZHS16GBK” )  
ddl include mapped  
USERID goldengate, PASSWORD goldengate  
EXTTRAIL /datafile/ogg/dirdat/sr  
SEQUENCE EPLATFORM.seq\_test;

TABLE EPLATFORM.\*;  
添加trail文件  
add exttrail /datafile/ogg/dirdat/sr, extract extfull

7)配置extract以传输  
添加传输进程  
add extract dpfull exttrailsource /datafile/ogg/dirdat/sr  
EDIT PARAMS dpfull  
输入参数  
EXTRACT dpfull  
passthru  
RMTHOST 10.222.22.41, MGRPORT 7809  
RMTTRAIL /datafile/ogg/dirdat/tg  
SEQUENCE EPLATFORM.seq\_test;  
TABLE EPLATFORM.\*;

8)添加目标端的trail文件  
add rmttrail /datafile/ogg/dirdat/tg, extract dpfull

9)启用传输并启动ogg控制台  
进入sqlplus  
alter system set enable\_goldengate\_replication=true;

回到ggsci  
DBLOGIN USERID goldengate PASSWORD goldengate  
ADD SCHEMATRANDATA goldengate

start mgr  
start dpfull  
info all

10)配置多个目标端  
stop dpfull  
edit params dpfull

调整参数内容：  
EXTRACT dpfull

passthru

RMTHOST 10.222.22.45, MGRPORT 7809  
RMTTRAIL /datafile/ogg/dirdat/tg  
SEQUENCE EPLATFORM.seq\_test;  
TABLE EPLATFORM.\*;

RMTHOST 10.232.48.142, MGRPORT 7809  
RMTTRAIL /opt/ogg/dirdat/tg  
SEQUENCE EPLATFORM.seq\_test;  
TABLE EPLATFORM.\*;

RMTHOST 10.232.48.143, MGRPORT 7809  
RMTTRAIL /opt/ogg/dirdat/tg  
SEQUENCE EPLATFORM.seq\_test;  
TABLE EPLATFORM.\*;

RMTHOST 10.232.48.144, MGRPORT 7809  
RMTTRAIL /opt/ogg/dirdat/tg  
SEQUENCE EPLATFORM.seq\_test;  
TABLE EPLATFORM.\*;

RMTHOST 10.232.48.145, MGRPORT 7809  
RMTTRAIL /opt/ogg/dirdat/tg  
SEQUENCE EPLATFORM.seq\_test;  
TABLE EPLATFORM.\*;

RMTHOST 10.232.48.146, MGRPORT 7809  
RMTTRAIL /opt/ogg/dirdat/tg  
SEQUENCE EPLATFORM.seq\_test;  
TABLE EPLATFORM.\*;

RMTHOST 10.232.48.147, MGRPORT 7809  
RMTTRAIL /opt/ogg/dirdat/tg  
SEQUENCE EPLATFORM.seq\_test;  
TABLE EPLATFORM.\*;

RMTHOST 10.232.48.148, MGRPORT 7809  
RMTTRAIL /opt/ogg/dirdat/tg  
SEQUENCE EPLATFORM.seq\_test;  
TABLE EPLATFORM.\*;

RMTHOST 10.232.48.149, MGRPORT 7809  
RMTTRAIL /opt/ogg/dirdat/tg  
SEQUENCE EPLATFORM.seq\_test;  
TABLE EPLATFORM.\*;

RMTHOST 10.232.48.150, MGRPORT 7809  
RMTTRAIL /opt/ogg/dirdat/tg  
SEQUENCE EPLATFORM.seq\_test;  
TABLE EPLATFORM.\*;

执行删除原有rmttrail  
delete rmttrail /datafile/ogg/dirdat/tg

重新添加rmttrail  
add rmttrail /datafile/ogg/dirdat/tg, extract dpfull

启动dpfull  
start dpfull

二.目标端配置  
1.安装goldengate  
创建ogg安装目录（用root用户）  
mkdir /opt/ogg  
chown oracle:oinstall /datafile/ogg -R

将压缩文件复制到ogg安装路径下,并更改属主  
chown oracle:oinstall /datafile/ogg/fbo\_ggs\_Linux\_x64\_ora11g\_64bit.tar

切换到oracle用户并解压  
su - oracle  
cd /opt/ogg  
tar -xvf fbo\_ggs\_Linux\_x64\_ora11g\_64bit.tar

设置PATH及LD\_LIBRARY\_PARH  
vim ~/.bash\_profile

# Path Oracle GoldenGate #

export PATH=/opt/ogg: O R A C L E H O M E / b i n : ORACLE\_HOME/bin: ORACLEHOME/bin:PATH  
export LD\_LIBRARY\_PATH=/opt/ogg:$ORACLE\_HOME/lib

在ogg安装路径执行  
./ggsci

2.源端ogg配置  
1)在GGSCI中，创建ogg工作目录  
GGSCI (ogg) 1> CREATE SUBDIRS  
GGSCI (ogg) 1> exit

3)在GGSCI中,执行以下命令  
EDIT PARAMS ./GLOBALS

输入以下参数  
GGSCHEMA goldengate  
CHECKPOINTTABLE goldengate.ckpt

4)在SQL\*Plus中执行以下命令  
sqlplus / as sysdba  
@sequence.sql  
输入：goldengate

GRANT EXECUTE on goldengate.replicateSequence TO goldengate;

5)配置ogg参数  
EDIT PARAMS mgr

6)配置replicat  
创建checkpoint table  
dblogin userid goldengate password goldengate  
ADD CHECKPOINTTABLE goldengate.ckpt

add replicat repfull, exttrail /datafile/ogg/dirdat/tg, CHECKPOINTTABLE goldengate.ckpt

EDIT PARAMS repfull  
输入参数  
REPLICAT repfull  
setenv ( NLS\_LANG = “AMERICAN\_AMERICA.ZHS16GBK” )  
ddl include mapped  
USERID goldengate, PASSWORD goldengate  
DBOPTIONS SUPPRESSTRIGGERS,DEFERREFCONST  
HANDLECOLLISIONS  
ASSUMETARGETDEFS  
DISCARDFILE /datafile/ogg/dirrpt/repfull.dsc  
DDLERROR DEFAULT IGNORE RETRYOP  
REPERROR(DEFAULT,IGNORE)  
MAP EPLATFORM.*, TARGET EPLATFORM.*;  
7)启用传输并启动ogg控制台  
进入sqlplus  
alter system set enable\_goldengate\_replication=true;

回到ggsci  
DBLOGIN USERID goldengate PASSWORD goldengate  
ADD SCHEMATRANDATA goldengate

start mgr  
start repfull  
info all