goldengate相同平台在线数据初始化.docx
《goldengate相同平台在线数据初始化.docx》由会员分享,可在线阅读,更多相关《goldengate相同平台在线数据初始化.docx(8页珍藏版)》请在冰豆网上搜索。
![goldengate相同平台在线数据初始化.docx](https://file1.bdocx.com/fileroot1/2023-2/26/e3595279-1c57-4131-bf12-7b5d11033574/e3595279-1c57-4131-bf12-7b5d110335741.gif)
goldengate相同平台在线数据初始化
goldengate相同平台在线数据初始化
(1)
(2011-03-1715:
34:
33)
分类:
goldengate
概述:
goldengate数据库初始化有很多方法,用的最多的就是通过它自己的initialdataload和oracle数据库rman工具进行,但是initialdataload必须先将数据结构导入到目标库中,对于大型的生产库来说不是很合适,本文将介绍通过rman进行数据库初始化测试步骤
准备环境
源环境:
linux4.8node110.10.10.101oracle10g
目标环境:
linux5.2node510.10.10.11oracle10g
1源环境抽取进程配置
添加数据库级别最小补全日志
@>alterdatabaseaddsupplementallogdata;
Databasealtered.
添加表级别的最小补全日志
GGSCI(node1)2>dbloginuseridggatepasswordoracle
Successfullyloggedintodatabase.
GGSCI(node1)3>addtrandatatest.*
LoggingofsupplementalredodataenabledfortableTEST.T1.
GGSCI(node1)4>addtrandatascott.*
2011-03-1501:
34:
01 WARNINGOGG-00869 NouniquekeyisdefinedfortableBONUS.Allviablecolumnswillbeusedtorepresentthekey,butmaynotguaranteeuniqueness. KEYCOLSmaybeusedtodefinethekey.
LoggingofsupplementalredodataenabledfortableSCOTT.BONUS.
LoggingofsupplementalredodataenabledfortableSCOTT.DEPT.
LoggingofsupplementalredodataenabledfortableSCOTT.EMP.
表级别添加可以支持通配符,表级补全日志需要在最小补全日志打开的情况下才起作用,只开启最小补全日志(alterdatabaseaddsupplementallogdata;),redolog记录的信息还不够全面,必须再使用addtrandata开启表级的补全日志以获得必要的信息。
添加无任何主键的表会报错,因为原表没有主键或UniqueIndex,所以GG自动把原表所有的Column都拿来当主键用,但这样会造成传输上的问题,因此GG在这种模式下,只能支持有限数量的Column表,若有巨多column的表,需要添加uniqueindex。
创建抽取进程
GGSCI(node1)16>addextractext1,tranlog,beginnow
EXTRACTadded.
GGSCI(node1)18>addexttrail/vistor/media/GG/dirdat/lt,extractext1
EXTTRAILadded.
GGSCI(node1)19>addextractdpump,exttrailsource/vistor/media/GG/dirdata/lt
EXTRACTadded.
创建抽取进程属性文件
GGSCI(node1)20>editparamsext1
---ext1内容
xtractext1
useridGGATE,passwordGGATE
EXTTRAIL/vistor/media/GG/dirdat/lt
tabletest.*
tablescott.*
---ext1内容
添加目标传输文件目录
GGSCI(node1)24>addrmttrail/u01/app/GG/dirdat/rt,extractdpump
RMTTRAILadded.
编辑dpump属性
---dpump内容
extractdpump
useridGGATE,passwordGGATE
rmhost10.10.10.11,mgrport7809
rmttrail/u01/app/GG/dirdat/rt
PASSTHRU
tabletest.*
tablescott.*;
---dpump内容
启动extract、pump进程
GGSCI(node1)34>startmanager
Managerstarted.
GGSCI(node1)35>startext1
SendingSTARTrequesttoMANAGER...
EXTRACTEXT1starting
GGSCI(node1)36>startdpump
SendingSTARTrequesttoMANAGER...
EXTRACTDPUMPstarting
GGSCI(node1)37>infoall
Program Status Group Lag TimeSinceChkpt
MANAGER RUNNING
EXTRACT RUNNING DPUMP 00:
00:
00 00:
02:
09
EXTRACT RUNNING EXT1 00:
00:
40 00:
00:
04
此时抽取进程已经开始采集数据库变化,下一步我们将把源库在线通过rman生成目标库。
goldengate相同平台在线数据初始化
(2)
(2011-03-2813:
56:
02)
转载
标签:
杂谈
分类:
goldengate
源库抽取进程一直在捕获变化,下面我们将通过rman生成目标库
创建目标库pfile文件
sys@ORCL>createpfile='/tmp/pfile.ora'fromspfile;
Filecreated.
rman备份源库文件和归档,此时数据库若有变化保存于归档
RMAN>backupdatabaseplusarchivelog;
Startingbackupat18-MAR-11
currentlogarchived
allocatedchannel:
ORA_DISK_1
channelORA_DISK_1:
sid=143devtype=DISK
channelORA_DISK_1:
startingarchivelogbackupset
。
。
。
。
。
。
StartingControlFileandSPFILEAutobackupat18-MAR-11
piecehandle=/u01/app/oracle/product/10.2.0/dbs/c-1244527013-20110318-00comment=NONE
FinishedControlFileandSPFILEAutobackupat18-MAR-11
一旦备份完成,捕获SCN号
Startingrestoreat28-MAR-11
allocatedchannel:
ORA_DISK_1
channelORA_DISK_1:
sid=143devtype=DISK
ListofBackups
===============
Key TYLVSDeviceTypeCompletionTime#Pieces#CopiesCompressedTag
-----------------------------------------------------------------
30 B F ADISK 24-MAR-11 1 1 NO TAG20110324T134556
31 B F ADISK 24-MAR-11 1 1 NO TAG20110324T134556
32 B F ADISK 24-MAR-11 1 1 NO TAG20110324T134556
33 B F ADISK 24-MAR-11 1 1 NO TAG20110324T134556
usingchannelORA_DISK_1
ListofArchivedLogCopies
Key ThrdSeq SLowTime Name
--------------------------------
18 1 20 A24-MAR-11/u01/app/oracle/arch_orcl/1_20_716746278.dbf
MediarecoverystartSCNis671742
RecoverymustbedonebeyondSCN671783tocleardatafilesfuzziness
Finishedrestoreat28-MAR-11
将备份文件级拷贝到目标库中,进行恢复
run
{
setnewnamefordatafile1to '/u01/app//oradata/system01.dbf';
setnewnamefordatafile2to '/u01/app/oradata/undotbs01.dbf';
setnewnamefordatafile3to '/u01/app/oradata/sysaux01.dbf';
setnewnamefordatafile4to '/u01/app/oradata/users01.dbf';
setnewnamefordatafile5to '/u01/app/oradata/test01.dbf';
setnewnamefordatafile6to '/u01/app/oradata/test02.dbf';
setnewnamefordatafile7to '/u01/app/oradata/jy.dbf';
setnewnamefordatafile8to '/u01/app/oradata/broadway01.dbf';
setnewnamefordatafile9to '/u01/app/oradata/t1.dbf';
setnewnamefordatafile10to'/u01/app/oradata/t2.dbf';
setnewnamefordatafile11to'/u01/app/oradata/ggate.dbf';
restoredatabase;
switchdatafileall;
}
在源环境下创建测试对象
test@ORCL>desct1;
Name Null?
Type
---------------------------------------------------------------------------------------------------------------------------------------------------
X NOTNULLNUMBER(38)
test@ORCL>select*fromt1;
X
----------
1
Elapsed:
00:
00:
00.05
test@ORCL>updatet1setx=2;
1rowupdated.
Elapsed:
00:
00:
00.10
test@ORCL>commit;
Commitcomplete.
Elapsed:
00:
00:
00.01
test@ORCL>createtablet2asselect*fromall_tables;
Tablecreated.
Elapsed:
00:
00:
01.81
test@ORCL>commit;
Commitcomplete.
test@ORCL>createindexinx_t2ont2(table_name);
Indexcreated.
test@ORCL>createorreplaceviewview_t1asselect*fromt1;
Viewcreated.
createorreplaceproceduresp_get_users_byId(param1invarchar2)
is
svarchar2(2000);
begin
s:
='droptablels_table';
executeimmediates;
s:
='createtablels_tableas(
select*fromscott.tbUserswhereuserId=param1)';
executeimmediates;
endsp_get_users_byId;
Procedurecreated.
scott@ORCL>droptablet2;
Tabledropped.
Elapsed:
00:
00:
01.32
goldengate相同平台在线数据初始化 (3)
(2011-03-2914:
26:
37)
转载
标签:
杂谈
分类:
goldengate
恢复至打开目标库
SQL> recoverdatabaseusingbackupcontrolfileuntilchange684503
ORA-00279:
change684449generatedat03/28/201118:
59:
04neededforthread1
ORA-00289:
suggestion:
/u01/app/oracle/arch_orcl/1_30_716746278.dbf
ORA-00280:
change684449forthread1isinsequence#30
Specifylog:
{=suggested|filename|AUTO|CANCEL}
/u01/app/oracle/arch_orcl/1_30_716746278.dbf
Logapplied.
Mediarecoverycomplete.
startupmount
alterdatabaseopenresetlogs
在目标库,添加复制进程
GGSCI(node5)8>addcheckpointtableggate.chkptab
SuccessfullycreatedcheckpointtableGGATE.CHKPTAB;.
GGSCI(node5)9>addreplicatrep1,exttrail/vistor/media/GG/dirdat/rt,checkpointtablecheckpointtableggate.chkptab
ERROR:
InvalidparameterspecifiedforADDREPLICAT.
GGSCI(node5)10>addreplicatrep1,exttrail/vistor/media/GG/dirdat/rt,checkpointtableggate.chkptab
REPLICATadded.
启动复制进程
GGSCI(node5)11> startreplicatrep1,aftercsn697257
SendingSTARTrequesttoMANAGER...
REPLICATREP1starting
查看在csn号697257以后的操作和对象:
update、table、index、procedures都已存在