配置ogg试验成功

点击数：1373发布日期：2020-04-24 19:13:27　来源：老鹰

--建立ogg表空间
create tablespace GOLDENGATE datafile '/u01/oradata/tardb/tbs_goldengate.dbf' size 500m;


--建立ogg用户
create user ogg identified by ogg default tablespace GOLDENGATE;
grant RESOURCE,CONNECT,DBA  to ogg;


--细化权限
GRANT CONNECT TO ogg;
GRANT ALTER ANY  TABLE TO ogg;
GRANT ALTER SESSION TO ogg;
GRANT CREATE SESSION TO ogg;
GRANT FLASHBACK ANY TABLE TO ogg;
GRANT SELECT ANY DICTIONARY TO ogg;
GRANT SELECT ANY TABLE TO ogg;
GRANT "RESOURCE" TO ogg;




--ogg软件的安装（源和目标系统均需要安装）
--使用Oracle用户如非此用户
tar zxvf *.gz
gunzip *.gz 或 gzip –d *.gz
tar xvf *.gz


-- 创建子目录,目标和源两端
GGSCI> create subdirs




--检查附加日志情况
alter database add supplemental log data ;


Select  
SUPPLEMENTAL_LOG_DATA_MIN
,SUPPLEMENTAL_LOG_DATA_PK
,SUPPLEMENTAL_LOG_DATA_UI
,SUPPLEMENTAL_LOG_DATA_FK
,SUPPLEMENTAL_LOG_DATA_ALL from v$database;




--改为归档
alter system set LOG_ARCHIVE_DEST_1='LOCATION=/arch1' scope=both;
mkdir \arch1
shutdown immediate
startup mount
alter database archivelog;
alter database open;
alter system switch logfile;


--查看归档非归档模式
select dbid,name,created,log_mode from v$database;
archive log list;




--变forceloging
alter database force logging;
select force_logging from v$database;




--关闭数据库的recyclebin (仅实施DDL时进行配置)
查询当前recyclebin的参数值：
show parameter recyclebin
recyclebin                           string      OFF




--关闭recyclebin：
alter system set recyclebin=off scope=both;




--添加环境变量(源端目标端)


export GG_HOME=/oracle/ogg
export PATH=$PATH:$GG_HOME
export LIBPATH=$GG_HOME:$ORACLE_HOME/lib


export GG_HOME=/oracle/ogg
export PATH=$PATH:$GG_HOME
export LIBPATH=$GG_HOME:$ORACLE_HOME/lib




--编辑GLOBALS参数文件
EDIT PARAMS ./GLOBALS                      --在该文件中添加以下内容
GGSCHEMA ogg                               --指定的进行DDL复制的数据库用户




--源端建立OGG的DDL对象
sqlplus "/ as sysdba"
SQL> @marker_setup.sql
Enter GoldenGate schema name:ogg


SQL> @ddl_setup.sql
Enter GoldenGate schema name:ogg


SQL> @role_setup.sql


Grant this role to each user assigned to the Extract, Replicat, GGSCI, 
and Manager processes, by using the following SQL command:


GRANT GGS_GGSUSER_ROLE TO ogg


GRANT GGS_GGSUSER_ROLE TO ogg; --这里的goldengate是extract使用的用户LE权限。


SQL> @ ddl_enable.sql


SQL> @ddl_pin


SQL> @ ddl_disable.sql




--打开表的补充日志
dblogin userid ogg,password ogg


add trandata hr.STUDENT


INFO TRANDATA STUDY.ZRP






--查看表命令：


sqlplus / as sysdba
select table_name from all_tables where owner='HR' 
and table_name not in 
(select distinct table_name from dba_log_group_columns where owner='HR');








--MGR参数和进程（源和目标均需要配置）
cd $GG_HOME
./ggsci
ggsci>edit param mgr
PORT 7809
DYNAMICPORTLIST 7840-7914
PURGEOLDEXTRACTS ./dirdat/*, usecheckpoints, minkeepdays 1
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45
PURGEDDLHISTORY MINKEEPDAYS 7, MAXKEEPDAYS 10
PURGEMARKERHISTORY MINKEEPDAYS 7, MAXKEEPDAYS 10




AUTORESTART ER *, RETRIES 3, WAITMINUTES 5, RESETMINUTES 60
以上参数表示每5分钟尝试重新启动所有进程，共尝试三次。以后每60分钟清
零，再按照每5分钟尝试一次共试3次。




start mgr




--笔记本上的目标端管理进程参数
[oracle@tar dirprm]$ cat mgr.prm 
port 7809
PURGEOLDEXTRACTS /oracle/ogg/dirdat, USECHECKPOINTS




--源端配置extract进程
add extract ggext, tranlog, begin now --rac加 threads 1
add exttrail ./dirdat/aa,extract ggext,megabytes 50




add extract ggext, SOURCEISTABLE--初始化时用


UNREGISTER EXTRACT ggext LOGRETENTION
delete extract ggext !


help all
help add extract
help add exttral
history
INFO extract *,tasks
INFO extract ggext




--vi  ggext.prm
--edit params ggext
EXTRACT ggext




--extract 笔记本
EXTRACT ggext
SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
USERID ogg, PASSWORD ogg
EXTTRAIL ./dirdat/aa
TRANLOGOPTIONS ALTARCHIVELOGDEST PRIMARY INSTANCE fupeili /arch1
table HR.*;




SETENV (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")
USERID ogg, PASSWORD ogg
DISCARDFILE ./dirrpt/ggsext.dsc, APPEND, MEGABYTES 100
DDL INCLUDE MAPPED 
DDLOPTIONS ADDTRANDATA NOCROSSRENAME REPORT
EXTTRAIL ./dirdat/aa
TRANLOGOPTIONS ALTARCHIVELOGDEST PRIMARY INSTANCE fupeili /arch1
WILDCARDRESOLVE DYNAMIC
dynamicresolution
THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 90000
GETTRUNCATES
table HR.*;


--源端配pump进程
edit params ggpup
vi ggpup.prm


EXTRACT ggpup
SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
PASSTHRU
RMTHOST 192.168.1.20, MGRPORT 7809, compress
RMTTRAIL ./dirdat/aa
DYNAMICRESOLUTION
--sequence PPM.*;
table HR.*;




add extract ggpup, EXTTRAILSOURCE ./dirdat/aa  --, begin now
add rmttrail ./dirdat/aa, EXTRACT ggpup, MEGABYTES 5
delete extract ggpup




--创建检查点目标端
edit params  ./GLOBALS
checkpointtable ggxip.checktab


dblogin userid ogg,password ogg
add checkpointtable ogg.checktab






dblogin userid goldengate,password goldengate
add checkpointtable goldengate.checkpoint


goldengate.checkpoint




GGSchema goldengate
CheckpointTable goldengate.checkpoint
UnlockedTrailFiles


--目标端配置replicate进程
add replicat ggrep, exttrail ./dirdat/aa
--add replicat om, exttrail ./dirdat/tt, nodbcheckpoint


delete replicat ggrep
INFO replicat *,tasks


edit param ggrep
vi ggrep.prm


replicat  ggrep
setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
userid ogg, password ogg
handlecollisions
assumetargetdefs
DISCARDFILE ./dirrpt/ggrep.dsc, APPEND, MEGABYTES 1000
map hr.*  target hr.*;


start replicat ggrep




--建测试表
CREATE TABLE "HR"."STUDENT"
   (    "SNO" VARCHAR2(15),
        "SNAME" VARCHAR2(20));


begin
for i in 1 .. 99999 loop
insert into student values(i,'1');
commit;
end loop;
commit;
end;
/


truncate table student;




--expdp同步
select dbms_flashback.get_system_change_number from dual;




CREATE OR REPLACE DIRECTORY data_pump_dir AS '/home/oracle';


expdp hr/hr dumpfile=hr.dmp logfile=hrexp.log DIRECTORY=data_pump_dir PARALLEL=2 flashback_scn=235252


compression=all


Select  start_time  from gv$transaction；


启动复制进程
start replicat ggrep, aftercsn 235252




expdp hr/hr dumpfile=hr.dmp logfile=hrexp.log DIRECTORY=data_pump_dir PARALLEL=2 compression=METADATA_ONLY flashback_scn=235252




--长交易管理
send extract ggext , showtrans thread 1  count 10
send extract ggext , showtrans count 10


info extXX, showch




info extract group, detail
info replicat group, detail
lag replicat group,lag extract group
stats replicat group
info extract group ,showch
info replicat group, showch
send extract group, showtrans
view ggsevt
view report group
view params group
view report ./../..




alter replicat REPINTER, extseqno 3759, extrba 0