本文通过一系列步骤展示了从源Oracle数据库到目标Oracle数据库的数据迁移过程，使用了DataX作为数据同步工具。在源端创建了一个包含时间戳的表，并插入了一条记录，然后在目标端创建了相同的表并授权。接着，创建了DataX的JSON配置文件进行数据迁移，并执行了数据同步。然而，同步完成后，目标端的数据丢失了时间戳的微秒精度。这表明在迁移过程中可能存在数据类型或精度转换的问题。
1.在源端Oracle创建表https://www.cndba.cn/hbhe0316/article/22826
SQL> create table t2 (T1 TIMESTAMP(6));
Table created.
SQL> insert into t2 values(to_timestamp('2021-10-22 15:23:23.123456','yyyy-mm-dd hh24:mi:ss.ff'));
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t2;
T1
---------------------------------------------------------------------------
22-OCT-21 03.23.23.123456 PM
SQL> grant all on sys.t2 to system;
Grant succeeded.2.在目标端创建表
SQL> create table t2 (T1 TIMESTAMP(6));
Table created.
SQL> grant all on sys.t2 to system;
Grant succeeded.3.创建datax json文件
https://www.cndba.cn/hbhe0316/article/22826
[root@db01 job]# cat oracletooracle.json
{
"job": {
"setting": {
"speed": {
"channel": 5,
"byte": -1,
"batchSize":2048,
"record":-1
},
"errorLimit": {
"record": 0,
"percentage": 0.02
}
},
"content": [
{
"reader": {
"name": "oraclereader",
"parameter": {
"username": "system",
"password": "wwwwww",
"column": ["*"],
"splitPk": "",
"connection": [
{
"querySql": [
"select * from sys.t2 where 1=1"
],
"jdbcUrl": [
"jdbc:oracle:thin:@192.168.56.101:1521:orcl"
]
}
]
}
},
"writer": {
"name": "oraclewriter",
"parameter": {
"username": "system",
"password": "wwwwww",
"column": ["*"],
"preSql": [
],
"connection": [
{
"table": [
"sys.t2"
],
"jdbcUrl": "jdbc:oracle:thin:@//192.168.56.188:1521/orcl"
}
]
}
}
}
]
}
}4.同步数据
https://www.cndba.cn/hbhe0316/article/22826
https://www.cndba.cn/hbhe0316/article/22826
[root@db01 job]# python /datax/bin/datax.py /datax/job/oracletooracle.json
DataX (DATAX-OPENSOURCE-3.0), From Alibaba !
Copyright (C) 2010-2017, Alibaba Group. All Rights Reserved.
2021-12-14 22:30:03.633 [main] INFO VMInfo - VMInfo# operatingSystem class => sun.management.OperatingSystemImpl
2021-12-14 22:30:03.642 [main] INFO Engine - the machine info =>
osInfo: Oracle Corporation 1.8 25.262-b10
jvmInfo: Linux amd64 3.10.0-1160.el7.x86_64
cpu num: 10
totalPhysicalMemory: -0.00G
freePhysicalMemory: -0.00G
maxFileDescriptorCount: -1
currentOpenFileDescriptorCount: -1
GC Names [PS MarkSweep, PS Scavenge]
MEMORY_NAME | allocation_size | init_size
PS Eden Space | 256.00MB | 256.00MB
Code Cache | 240.00MB | 2.44MB
Compressed Class Space | 1,024.00MB | 0.00MB
PS Survivor Space | 42.50MB | 42.50MB
PS Old Gen | 683.00MB | 683.00MB
Metaspace | -0.00MB | 0.00MB
2021-12-14 22:30:03.664 [main] INFO Engine -
{
"content":[
{
"reader":{
"name":"oraclereader",
"parameter":{
"column":[
"*"
],
"connection":[
{
"jdbcUrl":[
"jdbc:oracle:thin:@192.168.56.101:1521:orcl"
],
"querySql":[
"select * from sys.t2 where 1=1"
]
}
],
"password":"******",
"splitPk":"",
"username":"system"
}
},
"writer":{
"name":"oraclewriter",
"parameter":{
"column":[
"*"
],
"connection":[
{
"jdbcUrl":"jdbc:oracle:thin:@//192.168.56.188:1521/orcl",
"table":[
"sys.t2"
]
}
],
"password":"******",
"preSql":[],
"username":"system"
}
}
}
],
"setting":{
"errorLimit":{
"percentage":0.02,
"record":0
},
"speed":{
"batchSize":2048,
"byte":-1,
"channel":5,
"record":-1
}
}
}
2021-12-14 22:30:03.685 [main] WARN Engine - prioriy set to 0, because NumberFormatException, the value is: null
2021-12-14 22:30:03.688 [main] INFO PerfTrace - PerfTrace traceId=job_-1, isEnable=false, priority=0
2021-12-14 22:30:03.688 [main] INFO JobContainer - DataX jobContainer starts job.
2021-12-14 22:30:03.690 [main] INFO JobContainer - Set jobId = 0
2021-12-14 22:30:04.054 [job-0] INFO OriginalConfPretreatmentUtil - Available jdbcUrl:jdbc:oracle:thin:@192.168.56.101:1521:orcl.
2021-12-14 22:30:04.054 [job-0] WARN OriginalConfPretreatmentUtil - 您的配置有误. 由于您读取数据库表采用了querySql的方式, 所以您不需要再配置 column. 如果您不想看到这条提醒，请移除您源头表中配置中的 column.
2021-12-14 22:30:04.377 [job-0] INFO OriginalConfPretreatmentUtil - table:[sys.t2] all columns:[
T1
].
2021-12-14 22:30:04.377 [job-0] WARN OriginalConfPretreatmentUtil - 您的配置文件中的列配置信息存在风险. 因为您配置的写入数据库表的列为*，当您的表字段个数、类型有变动时，可能影响任务正确性甚至会运行出错。请检查您的配置并作出修改.
2021-12-14 22:30:04.378 [job-0] INFO OriginalConfPretreatmentUtil - Write data [
INSERT INTO %s (T1) VALUES(?)
], which jdbcUrl like:[jdbc:oracle:thin:@//192.168.56.188:1521/orcl]
2021-12-14 22:30:04.379 [job-0] INFO JobContainer - jobContainer starts to do prepare ...
2021-12-14 22:30:04.379 [job-0] INFO JobContainer - DataX Reader.Job [oraclereader] do prepare work .
2021-12-14 22:30:04.380 [job-0] INFO JobContainer - DataX Writer.Job [oraclewriter] do prepare work .
2021-12-14 22:30:04.380 [job-0] INFO JobContainer - jobContainer starts to do split ...
2021-12-14 22:30:04.381 [job-0] INFO JobContainer - Job set Channel-Number to 5 channels.
2021-12-14 22:30:04.383 [job-0] INFO JobContainer - DataX Reader.Job [oraclereader] splits to [1] tasks.
2021-12-14 22:30:04.383 [job-0] INFO JobContainer - DataX Writer.Job [oraclewriter] splits to [1] tasks.
2021-12-14 22:30:04.403 [job-0] INFO JobContainer - jobContainer starts to do schedule ...
2021-12-14 22:30:04.407 [job-0] INFO JobContainer - Scheduler starts [1] taskGroups.
2021-12-14 22:30:04.410 [job-0] INFO JobContainer - Running by standalone Mode.
2021-12-14 22:30:04.418 [taskGroup-0] INFO TaskGroupContainer - taskGroupId=[0] start [1] channels for [1] tasks.
2021-12-14 22:30:04.422 [taskGroup-0] INFO Channel - Channel set byte_speed_limit to -1, No bps activated.
2021-12-14 22:30:04.422 [taskGroup-0] INFO Channel - Channel set record_speed_limit to -1, No tps activated.
2021-12-14 22:30:04.476 [taskGroup-0] INFO TaskGroupContainer - taskGroup[0] taskId[0] attemptCount[1] is started
2021-12-14 22:30:04.483 [0-0-0-reader] INFO CommonRdbmsReader$Task - Begin to read record by Sql: [select * from sys.t2 where 1=1
] jdbcUrl:[jdbc:oracle:thin:@192.168.56.101:1521:orcl].
2021-12-14 22:30:04.640 [0-0-0-reader] INFO CommonRdbmsReader$Task - Finished read record by Sql: [select * from sys.t2 where 1=1
] jdbcUrl:[jdbc:oracle:thin:@192.168.56.101:1521:orcl].
2021-12-14 22:30:04.991 [taskGroup-0] INFO TaskGroupContainer - taskGroup[0] taskId[0] is successed, used[516]ms
2021-12-14 22:30:04.992 [taskGroup-0] INFO TaskGroupContainer - taskGroup[0] completed it's tasks.
2021-12-14 22:30:14.479 [job-0] INFO StandAloneJobContainerCommunicator - Total 1 records, 8 bytes | Speed 0B/s, 0 records/s | Error 0 records, 0 bytes | All Task WaitWriterTime 0.000s | All Task WaitReaderTime 0.000s | Percentage 100.00%
2021-12-14 22:30:14.480 [job-0] INFO AbstractScheduler - Scheduler accomplished all tasks.
2021-12-14 22:30:14.481 [job-0] INFO JobContainer - DataX Writer.Job [oraclewriter] do post work.
2021-12-14 22:30:14.482 [job-0] INFO JobContainer - DataX Reader.Job [oraclereader] do post work.
2021-12-14 22:30:14.483 [job-0] INFO JobContainer - DataX jobId [0] completed successfully.
2021-12-14 22:30:14.485 [job-0] INFO HookInvoker - No hook invoked, because base dir not exists or is a file: /datax/hook
2021-12-14 22:30:14.489 [job-0] INFO JobContainer -
[total cpu info] =>
averageCpu | maxDeltaCpu | minDeltaCpu
-1.00% | -1.00% | -1.00%
[total gc info] =>
NAME | totalGCCount | maxDeltaGCCount | minDeltaGCCount | totalGCTime | maxDeltaGCTime | minDeltaGCTime
PS MarkSweep | 1 | 1 | 1 | 0.023s | 0.023s | 0.023s
PS Scavenge | 1 | 1 | 1 | 0.019s | 0.019s | 0.019s
2021-12-14 22:30:14.490 [job-0] INFO JobContainer - PerfTrace not enable!
2021-12-14 22:30:14.491 [job-0] INFO StandAloneJobContainerCommunicator - Total 1 records, 8 bytes | Speed 0B/s, 0 records/s | Error 0 records, 0 bytes | All Task WaitWriterTime 0.000s | All Task WaitReaderTime 0.000s | Percentage 100.00%
2021-12-14 22:30:14.495 [job-0] INFO JobContainer -
任务启动时刻 : 2021-12-14 22:30:03
任务结束时刻 : 2021-12-14 22:30:14
任务总计耗时 : 10s
任务平均流量 : 0B/s
记录写入速度 : 0rec/s
读出记录总数 : 1
读写失败总数 : 05.目标端查看数据https://www.cndba.cn/hbhe0316/article/22826
SQL> select * from sys.t2;
T1
---------------------------------------------------------------------------
22-OCT-21 03.23.23.123000 PM6.差异点
源端https://www.cndba.cn/hbhe0316/article/22826https://www.cndba.cn/hbhe0316/article/22826
SQL> select * from sys.t2;
T1
---------------------------------------------------------------------------
22-OCT-21 03.23.23.123456 PM目标:https://www.cndba.cn/hbhe0316/article/22826https://www.cndba.cn/hbhe0316/article/22826
SQL> select * from sys.t2;
T1
---------------------------------------------------------------------------
22-OCT-21 03.23.23.123000 PM存在时间精度丢失
https://www.cndba.cn/hbhe0316/article/22826
版权声明：本文为博主原创文章，未经博主允许不得转载。
datax
扫一扫
2096
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
