goldengate相同平台在线数据初始化.docx

1、goldengate相同平台在线数据初始化goldengate相同平台在线数据初始化（1）(2011-03-17 15:34:33) 分类： goldengate 概述：goldengate数据库初始化有很多方法，用的最多的就是通过它自己的initial data load和oracle数据库rman工具进行，但是initial data load必须先将数据结构导入到目标库中，对于大型的生产库来说不是很合适，本文将介绍通过rman进行数据库初始化测试步骤准备环境源环境：linux 4.8 node1 10.10.10.101 oracle 10g目标环境: linux 5.2 node5 1

2、0.10.10.11 oracle 10g1 源环境抽取进程配置添加数据库级别最小补全日志alter database add supplemental log data;Database altered.添加表级别的最小补全日志GGSCI (node1) 2 dblogin userid ggate password oracleSuccessfully logged into database.GGSCI (node1) 3 add trandata test.*Logging of supplemental redo data enabled for table TEST.T1.GGSC

3、I (node1) 4 add trandata scott.*2011-03-15 01:34:01 WARNING OGG-00869 No unique key is defined for table BONUS. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.Logging of supplemental redo data enabled for table SCOTT.BONU

4、S.Logging of supplemental redo data enabled for table SCOTT.DEPT.Logging of supplemental redo data enabled for table SCOTT.EMP.表级别添加可以支持通配符，表级补全日志需要在最小补全日志打开的情况下才起作用，只开启最小补全日志（alter database add supplemental log data;），redolog记录的信息还不够全面，必须再使用add trandata开启表级的补全日志以获得必要的信息。添加无任何主键的表会报错，因为原表没有主键或Unique

5、 Index，所以GG自动把原表所有的Column都拿来当主键用，但这样会造成传输上的问题，因此GG在这种模式下，只能支持有限数量的Column表，若有巨多column的表，需要添加unique index。创建抽取进程GGSCI (node1) 16 add extract ext1,tranlog,begin nowEXTRACT added.GGSCI (node1) 18 add exttrail /vistor/media/GG/dirdat/lt,extract ext1EXTTRAIL added.GGSCI (node1) 19 add extract dpump,exttra

6、ilsource /vistor/media/GG/dirdata/ltEXTRACT added.创建抽取进程属性文件GGSCI (node1) 20 edit params ext1-ext1内容xtract ext1userid GGATE,password GGATEEXTTRAIL /vistor/media/GG/dirdat/lttable test.*table scott.*-ext1内容添加目标传输文件目录GGSCI (node1) 24 add rmttrail /u01/app/GG/dirdat/rt,extract dpumpRMTTRAIL added.编辑dpu

7、mp属性-dpump内容extract dpumpuserid GGATE,password GGATErmhost 10.10.10.11,mgrport 7809rmttrail /u01/app/GG/dirdat/rtPASSTHRUtable test.*table scott.*;-dpump内容启动extract、pump进程GGSCI (node1) 34 start managerManager started.GGSCI (node1) 35 start ext1Sending START request to MANAGER .EXTRACT EXT1 startingG

8、GSCI (node1) 36 start dpumpSending START request to MANAGER .EXTRACT DPUMP startingGGSCI (node1) 37 info allProgram Status Group Lag Time Since ChkptMANAGER RUNNINGEXTRACT RUNNING DPUMP 00:00:00 00:02:09EXTRACT RUNNING EXT1 00:00:40 00:00:04此时抽取进程已经开始采集数据库变化，下一步我们将把源库在线通过rman生成目标库。goldengate相同平台在线数据

9、初始化（2）(2011-03-28 13:56:02) 转载标签： 杂谈分类： goldengate 源库抽取进程一直在捕获变化，下面我们将通过rman 生成目标库创建目标库pfile文件sysORCLcreate pfile=/tmp/pfile.ora from spfile;File created.rman备份源库文件和归档，此时数据库若有变化保存于归档RMAN backup database plus archivelog;Starting backup at 18-MAR-11current log archivedallocated channel: ORA_DISK_1chan

10、nel ORA_DISK_1: sid=143 devtype=DISKchannel ORA_DISK_1: starting archive log backupset。Starting Control File and SPFILE Autobackup at 18-MAR-11piece handle=/u01/app/oracle/product/10.2.0/dbs/c-1244527013-20110318-00 comment=NONEFinished Control File and SPFILE Autobackup at 18-MAR-11一旦备份完成，捕获SCN号Sta

11、rting restore at 28-MAR-11allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=143 devtype=DISKList of Backups=Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag- - - - - - - - - -30 B F A DISK 24-MAR-11 1 1 NO TAG20110324T13455631 B F A DISK 24-MAR-11 1 1 NO TAG20110324T13455632

12、 B F A DISK 24-MAR-11 1 1 NO TAG20110324T13455633 B F A DISK 24-MAR-11 1 1 NO TAG20110324T134556using channel ORA_DISK_1List of Archived Log CopiesKey Thrd Seq S Low Time Name- - - - - -18 1 20 A 24-MAR-11 /u01/app/oracle/arch_orcl/1_20_716746278.dbfMedia recovery start SCN is 671742Recovery must be

13、 done beyond SCN 671783 to clear data files fuzzinessFinished restore at 28-MAR-11将备份文件级拷贝到目标库中，进行恢复runset newname for datafile 1 to /u01/app/oradata/system01.dbf;set newname for datafile 2 to /u01/app/oradata/undotbs01.dbf;set newname for datafile 3 to /u01/app/oradata/sysaux01.dbf;set newname for

14、datafile 4 to /u01/app/oradata/users01.dbf;set newname for datafile 5 to /u01/app/oradata/test01.dbf;set newname for datafile 6 to /u01/app/oradata/test02.dbf;set newname for datafile 7 to /u01/app/oradata/jy.dbf;set newname for datafile 8 to /u01/app/oradata/broadway01.dbf;set newname for datafile

15、9 to /u01/app/oradata/t1.dbf;set newname for datafile 10 to /u01/app/oradata/t2.dbf;set newname for datafile 11 to /u01/app/oradata/ggate.dbf;restore database;switch datafile all;在源环境下创建测试对象testORCLdesc t1;Name Null? Type- - -X NOT NULL NUMBER(38)testORCLselect * from t1; X- 1Elapsed: 00:00:00.05tes

16、tORCLupdate t1 set x=2;1 row updated.Elapsed: 00:00:00.10testORCLcommit;Commit complete.Elapsed: 00:00:00.01testORCLcreate table t2 as select * from all_tables;Table created.Elapsed: 00:00:01.81testORCLcommit;Commit complete.testORCLcreate index inx_t2 on t2 (table_name);Index created.testORCLcreate

17、 or replace view view_t1 as select * from t1;View created.create or replace procedure sp_get_users_byId(param1 in varchar2)iss varchar2(2000);begins:=drop table ls_table;execute immediate s;s:=create table ls_table as ( select * from scott.tbUsers where userId=param1);execute immediate s;end sp_get_

18、users_byId;Procedure created.scottORCLdrop table t2;Table dropped.Elapsed: 00:00:01.32 goldengate相同平台在线数据初始化（3）(2011-03-29 14:26:37) 转载标签： 杂谈分类： goldengate 恢复至打开目标库SQL recover database using backup controlfile until change 684503ORA-00279: change 684449 generated at 03/28/2011 18:59:04 needed for th

19、read 1ORA-00289: suggestion : /u01/app/oracle/arch_orcl/1_30_716746278.dbfORA-00280: change 684449 for thread 1 is in sequence #30Specify log: =suggested | filename | AUTO | CANCEL/u01/app/oracle/arch_orcl/1_30_716746278.dbfLog applied.Media recovery complete.startup mountalter database open resetlo

20、gs在目标库，添加复制进程GGSCI (node5) 8 add checkpointtable ggate.chkptabSuccessfully created checkpoint table GGATE.CHKPTAB;.GGSCI (node5) 9 add replicat rep1,exttrail /vistor/media/GG/dirdat/rt,checkpointtable checkpointtable ggate.chkptabERROR: Invalid parameter specified for ADD REPLICAT.GGSCI (node5) 10 add replicat rep1,exttrail /vistor/media/GG/dirdat/rt,checkpointtable ggate.chkptabREPLICAT added.启动复制进程GGSCI (node5) 11 start replicat rep1, aftercsn 697257Sending START request to MANAGER .REPLICAT REP1 starting查看在csn号697257以后的操作和对象：update、table、index、procedures都已存在