oracle数据库基于goldengate的双机热备配置手册

http://blog.csdn.net/linucle/article/details/13505939
http://blog.itpub.net/10248702/viewspace-624577/
http://blog.itpub.net/29107230/viewspace-1063094/
http://ylw6006.blog.51cto.com/470441/904373/
http://www.ludatou.com/?p=905
http://blog.csdn.net/tianlesoftware/article/details/6950018
---------------------------------------------------------源数据库----------------------------------------------------------------




cd D:\app\Administrator\product\12.1.2\oggcore_1\
ggsci.exe


注册OGG服务
install addservice addevents 服务中会多出 GGSMGR




创建OGG工作目录
create subdirs 
----------------------------------------------------------
Parameter files                D:\app\Administrator\product\12.1.2\oggcore_1\dirprm  OGG进程参数文件
Report files                   D:\app\Administrator\product\12.1.2\oggcore_1\dirrpt  OGG报告
Checkpoint files               D:\app\Administrator\product\12.1.2\oggcore_1\dirchk  检查点文件方式记录
Process status files           D:\app\Administrator\product\12.1.2\oggcore_1\dirpcs
SQL script files               D:\app\Administrator\product\12.1.2\oggcore_1\dirsql  sql脚本
Database definitions files     D:\app\Administrator\product\12.1.2\oggcore_1\dirdef  数据库定义信息
Extract data files             D:\app\Administrator\product\12.1.2\oggcore_1\dirdat
Temporary files                D:\app\Administrator\product\12.1.2\oggcore_1\dirtmp
Credential store files         D:\app\Administrator\product\12.1.2\oggcore_1\dircrd
Masterkey wallet files         D:\app\Administrator\product\12.1.2\oggcore_1\dirwlt
Dump files                     D:\app\Administrator\product\12.1.2\oggcore_1\dirdmp
-------------------------------------------------------------
D:\app\Administrator\product\12.1.2\oggcore_1\dirprm
mgr.prm


archive log list
shutdown immediate
startup mount
alter database archivelog;
alter database add supplemental log data;
select supplemental_log_data_min from v$database;


info all
info manager


add extract ext1,tranlog,begin now
新增日志抓取进程 名字ext1  抓取事务日志  从现在开始


日志抓取进程 抓取出来的信息 需要设置存储路径 队列文件 lt是队列文件的开头名称
add exttrail D:\app\Administrator\product\12.1.2\oggcore_1\dirdat\lt,extract ext1


给ext1进程添加参数


edit params ext1


extract ext1
SETENV(NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")
userid ogg@orcl,password ogg
rmthost WIN-NODE1,mgrport 7809
rmttrail D:\app\Administrator\product\12.1.2\oggcore_1\dirdat\lt
table sender.t;


ogg为复制用户 必须和业务用户分开 登录哪个数据库 用哪个用户去复制
rmthost WIN-NODE1,mgrport 7809 本OGG主机的位置和端口号 extract连接哪个OGG的核心进程


创建OGG复制用户
create user ogg identified by ogg account unlock;
grant connect to ogg;
grant dba to ogg;




启动日志抓取进程
start extract ext1


新建测试业务用户
create user sender identified by sender account unlock;
grant connect to sender;
grant dba to sender;








创建投递进程
add extract datapump, exttrailsource D:\app\Administrator\product\12.1.2\oggcore_1\dirdat\lt
add rmttrail D:\app\Administrator\product\12.1.2\oggcore_1\dirdat\tl,EXTRACT datapump,MEGABYTES 100


edit param datapump


extract datapump
dynamicresolution
passthru
rmthost 192.168.160.121, mgrport 7809, compress
rmttrail D:\app\Administrator\product\12.1.2\oggcore_1\dirdat\tl
numfiles 5000
TABLE sender.t;






---------------------------------------------------------目标数据库----------------------------------------------------------------


archive log list
shutdown immediate
startup mount
alter database archivelog;
alter database add supplemental log data;
select supplemental_log_data_min from v$database;


创建OGG复制用户
create user ogg identified by ogg account unlock;
grant connect to ogg;
grant dba to ogg;




创建checkpoint表 


ogg下配置
edit params GLOBALS
GGSCHEMA ogg
CHECKPOINTTABLE ogg.checkpoint
ogg用户下的checkpoint表


创建ogg用户下的checkpoint表
dblogin userid ogg@orcl password ogg
add checkpointtable ogg.checkpoint


创建复制进程
add replicat rep1,exttrail D:\app\Administrator\product\12.1.2\oggcore_1\dirdat\tl,checkpointtable ogg.checkpoint


配置rep1复制进程
edit params rep1


rep1进程参数
REPLICAT rep1
SETENV (ORACLE_SID=orcl)
SETENV (NLS_LANG = "SIMPLIFIED CHINESE_CHINA.ZHS16GBK")
userid ogg@orcl,password ogg
REPORTCOUNT EVERY 30 MINUTES, RATE
REPERROR DEFAULT, ABEND
numfiles 5000
--HANDLECOLLISIONS
assumetargetdefs
DISCARDFILE D:\app\Administrator\product\12.1.2\oggcore_1\dirrpt\rep1.dsc, APPEND, MEGABYTES 1000
ALLOWNOOPUPDATES
map sender.t,target receiver.t;


创建源库用户
create user receiver identified by receiver account unlock;
grant connect to receiver;
grant dba to receiver;
conn receiver/receiver;
create table t(id int);






--------------------------------------------配置OGG支持DML单向复制-----------------------------------------------------




开始配置OGG支持DDL复制(在source端操作)


edit param ./GLOBALS  
 
ggschema ogg 


view param ./GLOBALS  


运行相关的sql脚本
cd D:\app\Administrator\product\12.1.2\oggcore_1
sqlplus /nolog
conn /as sysdba
@marker_setup.sql
ogg
show parameter recyclebin;
alter system set recyclebin=off scope=spfile;
shutdown immediate
startup
show parameter recyclebin;




需要给OGG用户建立到自己所属表空间，就可以解决。这也是一种安全机制，避免建立到默认的数据字典表空间下面。
create tablespace OGG datafile 'D:\app\Administrator\oradata\orcl\OGG.dbf' size 100M autoextend on  maxsize unlimited; 
alter user ogg default tablespace ogg;
@ddl_setup.sql
ogg


@role_setup.sql
ogg


grant ggs_ggsuser_role to ogg;


@ddl_enable.sql


@?/rdbms/admin/dbmspool.sql


@ddl_pin.sql ogg


edit params ext1


写在语言下面
ddl include all


----------------------------------目标端配置---------------------------------------
edit params REP1
写在语言下面
ddl include all  
ddlerror default ignore retryop maxretries 3 retrydelay 5


 start replicat rep1  


sender 


receiver




----------------------------------配置双活数据节点-----------------------------------


目标端配置
add extract ext1,tranlog,begin now
add exttrail D:\app\Administrator\product\12.1.2\oggcore_1\dirdat\st,extract ext1


edit params ext1


extract ext1
TRANLOGOPTIONS EXCLUDEUSER ogg
SETENV(NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")
userid ogg@orcl,password ogg
rmthost WIN-NODE2,mgrport 7809
rmttrail D:\app\Administrator\product\12.1.2\oggcore_1\dirdat\st
table receiver.*;


源端配置


ogg下配置
edit params GLOBALS
GGSCHEMA ogg
CHECKPOINTTABLE ogg.checkpoint
ogg用户下的checkpoint表


创建ogg用户下的checkpoint表
dblogin userid ogg@orcl password ogg
add checkpointtable ogg.checkpoint


目标端配置
add extract datapump, exttrailsource D:\app\Administrator\product\12.1.2\oggcore_1\dirdat\st
add rmttrail D:\app\Administrator\product\12.1.2\oggcore_1\dirdat\st,EXTRACT datapump,MEGABYTES 100


edit param datapump


extract datapump
dynamicresolution
passthru
rmthost 192.168.160.120, mgrport 7809, compress
rmttrail D:\app\Administrator\product\12.1.2\oggcore_1\dirdat\st
numfiles 5000
TABLE receiver.*;








源端配置


创建复制进程
add replicat rep1,exttrail D:\app\Administrator\product\12.1.2\oggcore_1\dirdat\st,checkpointtable ogg.checkpoint


配置rep1复制进程
edit params rep1




REPLICAT rep1
HANDLECOLLISIONS
ASSUMETARGETDEFS
SETENV (ORACLE_SID=orcl)
SETENV (NLS_LANG = "SIMPLIFIED CHINESE_CHINA.ZHS16GBK")
userid ogg@orcl,password ogg
REPORTCOUNT EVERY 30 MINUTES, RATE
REPERROR DEFAULT, ABEND
numfiles 5000
DISCARDFILE D:\app\Administrator\product\12.1.2\oggcore_1\dirrpt\rep1.dsc, APPEND, MEGABYTES 1000
ALLOWNOOPUPDATES
map receiver.*,target sender.*;






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






create user hytera identified by hytera account unlock;
grant connect to hytera;
grant dba to hytera;