-
Notifications
You must be signed in to change notification settings - Fork 9
Expand file tree
/
Copy path4.5.html
More file actions
1212 lines (1086 loc) · 44 KB
/
4.5.html
File metadata and controls
1212 lines (1086 loc) · 44 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width,initial-scale=1">
<title>迁移指南 :: IvorySQL文档中心</title>
<link rel="canonical" href="https://docs.ivorysql.org/ivorysql-doc/master/4.5.html">
<link rel="prev" href="4.4.html">
<link rel="next" href="5.1.html">
<meta name="generator" content="Antora 3.1.7">
<link rel="stylesheet" href="../../_/css/site.css">
<script>var uiRootPath = '../../_'</script>
</head>
<body class="article">
<header class="header">
<nav class="navbar">
<div class="navbar-brand">
<a class="navbar-item" href="https://docs.ivorysql.org">IvorySQL文档中心</a>
<div class="navbar-item search hide-for-print">
<div id="search-field" class="field">
<input id="search-input" type="text" placeholder="Search the docs">
</div>
</div>
<button class="navbar-burger" data-target="topbar-nav">
<span></span>
<span></span>
<span></span>
</button>
</div>
<div id="topbar-nav" class="navbar-menu">
<div class="navbar-end">
<a class="navbar-item" href="https://www.ivorysql.org/">官方网站</a>
</div>
</div>
</nav>
</header>
<div class="body">
<div class="nav-container" data-component="ivorysql-doc" data-version="master">
<aside class="nav">
<div class="panels">
<div class="nav-panel-menu is-active" data-panel="menu">
<nav class="nav-menu">
<h3 class="title"><a href="welcome.html">文档中心</a></h3>
<ul class="nav-list">
<li class="nav-item" data-depth="0">
<ul class="nav-list">
<li class="nav-item" data-depth="1">
<button class="nav-item-toggle"></button>
<span class="nav-text">IvorySQL</span>
<ul class="nav-list">
<li class="nav-item" data-depth="2">
<a class="nav-link" href="welcome.html">欢迎</a>
</li>
<li class="nav-item" data-depth="2">
<a class="nav-link" href="1.html">发行说明</a>
</li>
<li class="nav-item" data-depth="2">
<a class="nav-link" href="2.html">关于IvorySQL</a>
</li>
<li class="nav-item" data-depth="2">
<button class="nav-item-toggle"></button>
<span class="nav-text">IvorySQL入门</span>
<ul class="nav-list">
<li class="nav-item" data-depth="3">
<a class="nav-link" href="3.1.html">快速开始</a>
</li>
<li class="nav-item" data-depth="3">
<a class="nav-link" href="3.2.html">日常监控</a>
</li>
<li class="nav-item" data-depth="3">
<a class="nav-link" href="3.3.html">日常维护</a>
</li>
</ul>
</li>
<li class="nav-item" data-depth="2">
<button class="nav-item-toggle"></button>
<span class="nav-text">IvorySQL高级</span>
<ul class="nav-list">
<li class="nav-item" data-depth="3">
<a class="nav-link" href="4.1.html">安装指南</a>
</li>
<li class="nav-item" data-depth="3">
<a class="nav-link" href="4.2.html">集群搭建</a>
</li>
<li class="nav-item" data-depth="3">
<a class="nav-link" href="4.3.html">开发者指南</a>
</li>
<li class="nav-item" data-depth="3">
<a class="nav-link" href="4.4.html">运维管理指南</a>
</li>
<li class="nav-item is-current-page" data-depth="3">
<a class="nav-link" href="4.5.html">迁移指南</a>
</li>
</ul>
</li>
<li class="nav-item" data-depth="2">
<button class="nav-item-toggle"></button>
<span class="nav-text">IvorySQL生态</span>
<ul class="nav-list">
<li class="nav-item" data-depth="3">
<a class="nav-link" href="5.1.html">PostGIS</a>
</li>
<li class="nav-item" data-depth="3">
<a class="nav-link" href="5.2.html">pgvector</a>
</li>
</ul>
</li>
<li class="nav-item" data-depth="2">
<button class="nav-item-toggle"></button>
<span class="nav-text">IvorySQL架构设计</span>
<ul class="nav-list">
<li class="nav-item" data-depth="3">
<button class="nav-item-toggle"></button>
<span class="nav-text">查询处理</span>
<ul class="nav-list">
<li class="nav-item" data-depth="4">
<a class="nav-link" href="6.1.1.html">双parser</a>
</li>
</ul>
</li>
<li class="nav-item" data-depth="3">
<button class="nav-item-toggle"></button>
<span class="nav-text">兼容框架</span>
<ul class="nav-list">
<li class="nav-item" data-depth="4">
<a class="nav-link" href="6.2.1.html">initdb过程</a>
</li>
</ul>
</li>
<li class="nav-item" data-depth="3">
<button class="nav-item-toggle"></button>
<span class="nav-text">兼容特性</span>
<ul class="nav-list">
<li class="nav-item" data-depth="4">
<a class="nav-link" href="6.3.1.html">like</a>
</li>
<li class="nav-item" data-depth="4">
<a class="nav-link" href="6.3.3.html">RowID</a>
</li>
<li class="nav-item" data-depth="4">
<a class="nav-link" href="6.3.2.html">OUT 参数</a>
</li>
<li class="nav-item" data-depth="4">
<a class="nav-link" href="6.3.4.html">%TYPE、%ROWTYPE</a>
</li>
</ul>
</li>
<li class="nav-item" data-depth="3">
<a class="nav-link" href="6.4.html">国标GB18030</a>
</li>
</ul>
</li>
<li class="nav-item" data-depth="2">
<button class="nav-item-toggle"></button>
<span class="nav-text">Oracle兼容功能列表</span>
<ul class="nav-list">
<li class="nav-item" data-depth="3">
<a class="nav-link" href="7.1.html">1、框架设计</a>
</li>
<li class="nav-item" data-depth="3">
<a class="nav-link" href="7.2.html">2、GUC框架</a>
</li>
<li class="nav-item" data-depth="3">
<a class="nav-link" href="7.3.html">3、大小写转换</a>
</li>
<li class="nav-item" data-depth="3">
<a class="nav-link" href="7.4.html">4、双模式设计</a>
</li>
<li class="nav-item" data-depth="3">
<a class="nav-link" href="7.5.html">5、兼容Oracle like</a>
</li>
<li class="nav-item" data-depth="3">
<a class="nav-link" href="7.6.html">6、兼容Oracle匿名块</a>
</li>
<li class="nav-item" data-depth="3">
<a class="nav-link" href="7.7.html">7、兼容Oracle函数与存储过程</a>
</li>
<li class="nav-item" data-depth="3">
<a class="nav-link" href="7.8.html">8、内置数据类型与内置函数</a>
</li>
<li class="nav-item" data-depth="3">
<a class="nav-link" href="7.9.html">9、新增Oracle兼容模式的端口与IP</a>
</li>
<li class="nav-item" data-depth="3">
<a class="nav-link" href="7.10.html">10、XML函数</a>
</li>
<li class="nav-item" data-depth="3">
<a class="nav-link" href="7.11.html">11、兼容Oracle sequence</a>
</li>
<li class="nav-item" data-depth="3">
<a class="nav-link" href="7.12.html">12、包</a>
</li>
<li class="nav-item" data-depth="3">
<a class="nav-link" href="7.13.html">13、不可见列</a>
</li>
<li class="nav-item" data-depth="3">
<a class="nav-link" href="7.14.html">14、RowID</a>
</li>
<li class="nav-item" data-depth="3">
<a class="nav-link" href="7.15.html">15、OUT 参数</a>
</li>
<li class="nav-item" data-depth="3">
<a class="nav-link" href="7.16.html">16、%TYPE、%ROWTYPE</a>
</li>
</ul>
</li>
<li class="nav-item" data-depth="2">
<button class="nav-item-toggle"></button>
<span class="nav-text">IvorySQL贡献指南</span>
<ul class="nav-list">
<li class="nav-item" data-depth="3">
<a class="nav-link" href="8.1.html">社区贡献指南</a>
</li>
<li class="nav-item" data-depth="3">
<a class="nav-link" href="8.2.html">asciidoc语法快速参考</a>
</li>
</ul>
</li>
<li class="nav-item" data-depth="2">
<a class="nav-link" href="9.html">工具参考</a>
</li>
<li class="nav-item" data-depth="2">
<a class="nav-link" href="10.html">FAQ</a>
</li>
</ul>
</li>
<li class="nav-item" data-depth="1">
<button class="nav-item-toggle"></button>
<span class="nav-text">PostgreSQL</span>
<ul class="nav-list">
<li class="nav-item" data-depth="2">
<a class="nav-link" href="100.html">PG参数参考手册</a>
</li>
<li class="nav-item" data-depth="2">
<a class="nav-link" href="110.html">PG函数参考手册</a>
</li>
</ul>
</li>
</ul>
</li>
</ul>
</nav>
</div>
<div class="nav-panel-explore" data-panel="explore">
<div class="context">
<span class="title">文档中心</span>
<span class="version">master</span>
</div>
<ul class="components">
<li class="component is-current">
<a class="title" href="welcome.html">文档中心</a>
<ul class="versions">
<li class="version is-current is-latest">
<a href="welcome.html">master</a>
</li>
<li class="version">
<a href="../v4.6/v4.6/welcome.html">v4.6</a>
</li>
<li class="version">
<a href="../v4.5/v4.5/welcome.html">v4.5</a>
</li>
<li class="version">
<a href="../v4.4/v4.4/welcome.html">v4.4</a>
</li>
<li class="version">
<a href="../v4.2/v4.2/welcome.html">v4.2</a>
</li>
<li class="version">
<a href="../v4.0/v4.0/welcome.html">v4.0</a>
</li>
<li class="version">
<a href="../v3.4/v3.4/welcome.html">v3.4</a>
</li>
<li class="version">
<a href="../v3.3/v3.3/welcome.html">v3.3</a>
</li>
<li class="version">
<a href="../v3.2/v3.2/welcome.html">v3.2</a>
</li>
<li class="version">
<a href="../v3.1/v3.1/welcome.html">v3.1</a>
</li>
<li class="version">
<a href="../v3.0/v3.0/welcome.html">v3.0</a>
</li>
<li class="version">
<a href="../v2.3/v2.3/welcome.html">v2.3</a>
</li>
<li class="version">
<a href="../v2.2/v2.2/welcome.html">v2.2</a>
</li>
<li class="version">
<a href="../v2.1/v2.1/welcome.html">v2.1</a>
</li>
<li class="version">
<a href="../v1.17/v1.17/welcome.html">v1.17</a>
</li>
<li class="version">
<a href="../v1.8/v1.8/welcome.html">v1.8</a>
</li>
<li class="version">
<a href="../v1.5/v1.5/welcome.html">v1.5</a>
</li>
<li class="version">
<a href="../v1.4/v1.4/welcome.html">v1.4</a>
</li>
<li class="version">
<a href="../v1.3/v1.3/welcome.html">v1.3</a>
</li>
<li class="version">
<a href="../v1.2/v1.2/welcome.html">v1.2</a>
</li>
<li class="version">
<a href="../v1.1/v1.1/welcome.html">v1.1</a>
</li>
<li class="version">
<a href="../v1.0/v1.0/welcome.html">v1.0</a>
</li>
</ul>
</li>
</ul>
</div>
</div>
</aside>
</div>
<main class="article">
<div class="toolbar" role="navigation">
<button class="nav-toggle"></button>
<nav class="breadcrumbs" aria-label="breadcrumbs">
<ul>
<li><a href="welcome.html">文档中心</a></li>
<li>IvorySQL</li>
<li>IvorySQL高级</li>
<li><a href="4.5.html">迁移指南</a></li>
</ul>
</nav>
<div class="page-versions">
<button class="version-menu-toggle" title="switch to English">CN</button>
<div class="version-menu">
<a class="version is-current" href="">CN</a>
<a class="version" href="../../../../en/ivorysql-doc/master/4.5.html">EN</a>
</div>
</div>
<div class="edit-this-page"><a href="https://github.com/IvorySQL/ivorysql_docs/edit/master/CN/modules/ROOT/pages/master/4.5.adoc">编辑此页面</a></div>
</div>
<div class="content">
<aside class="toc sidebar" data-title="目录" data-levels="2">
<div class="toc-menu"></div>
</aside>
<article class="doc">
<h1 class="page">迁移指南</h1>
<div class="sect1">
<h2 id="迁移概述"><a class="anchor" href="#迁移概述"></a>1. 迁移概述</h2>
<div class="sectionbody">
<div class="paragraph">
<p>数据库迁移是指将数据从一个数据库转移到另一个数据库的过程，两端的数据库可能是PostgreSQL, IvorySQL, MySQL, Oracle, SQL Server等。迁移过程是一个具有挑战性的复杂过程，需要对数据库的原理以及各自的特性了如指掌。如果应用已经部署到生产环境并处于正常运行状态，为了保持业务不中断运行，并且不发生数据丢失，数据库迁移后需进行平滑的应用迁移。</p>
</div>
<div class="paragraph">
<p>迁移后数据库和系统应符合以下要求：</p>
</div>
<div class="ulist">
<ul>
<li>
<p>迁移后的数据库系统应完全承载原数据库系统的数据。避免迁移过程中数据丢失导致新的数据库系统数据不完整。</p>
</li>
<li>
<p>迁移后的数据库系统应完全适配原有数据库的功能。避免迁移后因数据类型或语法、函数的不支持，且无替代方案，导致整个业务系统的无法运行或抛错。</p>
</li>
<li>
<p>迁移后的数据库应适配整个业务系统的上下游，稳定可靠的保障整个业务系统的运行。</p>
</li>
<li>
<p>迁移后的数据库各方面综合性能不能弱于原数据库，为整个业务系统提供性能保证。</p>
</li>
</ul>
</div>
</div>
</div>
<div class="sect1">
<h2 id="迁移工具ora2pg"><a class="anchor" href="#迁移工具ora2pg"></a>2. 迁移工具Ora2Pg</h2>
<div class="sectionbody">
<div class="paragraph">
<p>Ora2Pg 是一个免费的工具，用于将 Oracle 数据库迁移到 IvorySQL 兼容的模式。它连接您的 Oracle 数据库，自动扫描并提取它的结构或数据，然后生成可以装载到 IvorySQL 数据库的 SQL 脚本。Ora2Pg 可以从逆向工程 Oracle 数据库到大型企业数据库迁移，或者简单地将一些 Oracle 数据复制到 IvorySQL 数据库中。它非常容易使用，并且不需要任何 Oracle 数据库知识，而不需要提供连接到Oracle数据库所需的参数。</p>
</div>
<div class="paragraph">
<p>Ora2Pg 由一个 Perl 脚本（ora2pg）以及一个 Perl 模块（ <a href="https://github.com/darold/ora2pg/blob/master/lib/Ora2Pg.pm">Ora2Pg.pm</a>）组成，唯一需要做的事情就是修改它的配置文件 ora2pg.conf，设置连接 Oracle 数据库的 DSN 和一个可选的 SCHEMA 名称。完成之后，只需要设置导出的类型：TABLE（包括约束和索引）、VIEW、MVIEW、TABLESPACE、SEQUENCE、INDEXES、TRIGGER、GRANT、FUNCTION、PROCEDURE、PACKAGE、PARTITION、TYPE、INSERT 或 COPY、FDW、QUERY、KETTLE 以及 SYNONYM。</p>
</div>
<div class="paragraph">
<p>默认情况下，Ora2Pg 导出一个 SQL 文件，可以通过 IvorySQL 客户端工具 psql 执行导出的SQL文件。当进行数据迁移时，可以在配置文件中设置一个目标数据库的 DSN，直接将数据从Oracle导入到 IvorySQL 数据库中。</p>
</div>
<table class="tableblock frame-all grid-all fit-content">
<colgroup>
<col>
<col>
</colgroup>
<tbody>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock"><strong>对象</strong></p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">ora2pg是否支持</p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock">view</p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">是</p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock">trigger</p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">是，某些情况下需要手工修改脚本</p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock">sequence</p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">是</p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock">function</p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">是</p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock">procedure</p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">是，某些情况下需要手工修改脚本</p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock">type</p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">是，某些情况下需要手工修改脚本</p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock">materialized view</p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">是，某些情况下需要手工修改脚本</p></td>
</tr>
</tbody>
</table>
</div>
</div>
<div class="sect1">
<h2 id="迁移oracle数据库至ivorysql"><a class="anchor" href="#迁移oracle数据库至ivorysql"></a>3. 迁移Oracle数据库至IvorySQL</h2>
<div class="sectionbody">
<div class="sect2">
<h3 id="环境准备"><a class="anchor" href="#环境准备"></a>3.1. 环境准备</h3>
<table class="tableblock frame-all grid-all stretch">
<colgroup>
<col style="width: 33.3333%;">
<col style="width: 33.3333%;">
<col style="width: 33.3334%;">
</colgroup>
<tbody>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock">linux环境</p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">Oracle版本</p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">IvorySQL版本</p></td>
</tr>
<tr>
<td class="tableblock halign-left valign-top"><p class="tableblock">Centos Stream 9</p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">19.0.0.0</p></td>
<td class="tableblock halign-left valign-top"><p class="tableblock">4.2</p></td>
</tr>
</tbody>
</table>
</div>
<div class="sect2">
<h3 id="依赖环境安装"><a class="anchor" href="#依赖环境安装"></a>3.2. 依赖环境安装</h3>
<div class="sect3">
<h4 id="安装perl"><a class="anchor" href="#安装perl"></a>3.2.1. 安装Perl</h4>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-bash hljs" data-lang="bash">[root@localhost /]# dnf install -y perl perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker
[root@localhost /]# perl -v
This is perl 5, version 16, subversion 3 (v5.16.3) built for x86_64-linux-thread-multi
(with 44 registered patches, see perl -V for more detail)
Copyright 1987-2012, Larry Wall
Perl may be copied only under the terms of either the Artistic License or the
GNU General Public License, which may be found in the Perl 5 source kit.
Complete documentation for Perl, including FAQ lists, should be found on
this system using "man perl" or "perldoc perl". If you have access to the
Internet, point your browser at http://www.perl.org/, the Perl Home Page.</code></pre>
</div>
</div>
</div>
<div class="sect3">
<h4 id="安装dbi模块"><a class="anchor" href="#安装dbi模块"></a>3.2.2. 安装DBI模块</h4>
<div class="paragraph">
<p>DBI，Database Independent Interface，是 Perl 语言连接数据库的接口</p>
</div>
<div class="paragraph">
<p>下载地址 <a href="https://mirrors.sjtug.sjtu.edu.cn/cpan/modules/by-module/Plack/TIMB/DBI-1.643.tar.gz" class="bare">https://mirrors.sjtug.sjtu.edu.cn/cpan/modules/by-module/Plack/TIMB/DBI-1.643.tar.gz</a></p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-asciidoc hljs" data-lang="asciidoc"># tar zxvf DBI-1.643.tar.gz
# cd DBI-1.643/
# perl Makefile.PL
# make && make install</code></pre>
</div>
</div>
</div>
<div class="sect3">
<h4 id="安装dbd-oracle"><a class="anchor" href="#安装dbd-oracle"></a>3.2.3. 安装DBD-Oracle</h4>
<div class="paragraph">
<p>下载地址：https://sourceforge.net/projects/ora2pg/</p>
</div>
<div class="paragraph">
<p>设置环境变量; 加载环境变量;因为必须定义ORACLE_HOME环境变量;本例在ivorysql用户下配置环境变量</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-asciidoc hljs" data-lang="asciidoc">export ORACLE_HOME=/opt/oracle/product/19c/dbhome_1
# tar -zxvf DBD-Oracle-1.76.tar.gz # source /home/postgres/.bashrc
# cd DBD-Oracle-1.76
# perl Makefile.PL
# make && make install</code></pre>
</div>
</div>
</div>
</div>
<div class="sect2">
<h3 id="安装ora2pg"><a class="anchor" href="#安装ora2pg"></a>3.3. 安装Ora2pg</h3>
<div class="paragraph">
<p>下载地址：https://sourceforge.net/projects/ora2pg/</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-asciidoc hljs" data-lang="asciidoc"># tar -xjf ora2pg-24.0.tar.bz2
# cd ora2pg-24.0
# perl Makefile.PL
# make && make install</code></pre>
</div>
</div>
<div class="paragraph">
<p>默认安装在/usr/local/bin/目录下</p>
</div>
<div class="paragraph">
<p>检查软件环境：</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-asciidoc hljs" data-lang="asciidoc"># vi check.pl
#!/usr/bin/perl
use strict;
use ExtUtils::Installed;
my $inst= ExtUtils::Installed->new();
my @modules = $inst->modules();
foreach(@modules)
{
my $ver = $inst->version($_) || "???";
printf("%-12s -- %s\n", $_, $ver);
}
exit;
# perl check.pl
DBD::Oracle -- 1.76
DBD::Pg -- 3.8.0
DBI -- 1.643
Ora2Pg -- 24.0
Perl -- 5.16.3
设置环境变量</code></pre>
</div>
</div>
<div class="paragraph">
<p>export PERL5LIB=<your_install_dir>
#export PERL5LIB=/usr/local/bin/</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-asciidoc hljs" data-lang="asciidoc">=== 源端准备工作
更新oracle统计信息 提高性能</code></pre>
</div>
</div>
<div class="paragraph">
<p>BEGIN
DBMS_STATS.GATHER_SCHEMA_STATS('SH');
DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT');
DBMS_STATS.GATHER_SCHEMA_STATS('HR');
DBMS_STATS.GATHER_DATABASE_STATS;
DBMS_STATS.GATHER_DICTIONARY_STATS;
END;/</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-asciidoc hljs" data-lang="asciidoc">查询源端对象的类型
```bash
SYS@PROD1>set pagesize 200
SYS@PROD1>select distinct OBJECT_TYPE from dba_objects where OWNER in ('SH','SCOTT','HR') ;
OBJECT_TYPE
-------------------
INDEX PARTITION
TABLE PARTITION
SEQUENCE
PROCEDURE
LOB X
TRIGGER
DIMENSION X
MATERIALIZED VIEW
TABLE
INDEX
VIEW
11 rows selected.</code></pre>
</div>
</div>
</div>
<div class="sect2">
<h3 id="ora2pg导出表结构"><a class="anchor" href="#ora2pg导出表结构"></a>3.4. ora2pg导出表结构</h3>
<div class="paragraph">
<p><strong>配置ora2pg.conf:</strong></p>
</div>
<div class="paragraph">
<p>默认情况下，Ora2Pg会查找/etc/ora2pg/ora2pg.conf配置文件，如果文件存在，您只需执行：/usr/local/bin/ora2pg</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-asciidoc hljs" data-lang="asciidoc">cat /etc/ora2pg/ora2pg.conf.dist | grep -v ^# |grep -v ^$ >ora2pg.conf
vi ora2pg.conf
# cat ora2pg.conf
ORACLE_HOME /opt/oracle/product/19c/dbhome_1
ORACLE_DSN dbi:Oracle:host=localhost;sid=ORCLCDB;port=1521
ORACLE_USER system
ORACLE_PWD oracle
SCHEMA SH
EXPORT_SCHEMA 1 # 将用户导入到PostgreSQL数据库中
DISABLE_UNLOGGED 1 #避免将NOLOGGING属性设为UNLOGGED
SKIP fkeys ukeys checks #跳过外键 唯一 和检查约束
TYPE TABLE,VIEW,GRANT,SEQUENCE,TABLESPACE,PROCEDURE,TRIGGER,FUNCTION,PACKAGE,PARTITION,TYPE,MVIEW,QUERY,DBLINK,SYNONYM,DIRECTORY,TEST,TEST_VIEW
NLS_LANG AMERICAN_AMERICA.UTF8
OUTPUT sh.sql</code></pre>
</div>
</div>
<div class="quoteblock">
<blockquote>
<div class="olist arabic">
<ol class="arabic">
<li>
<p>只能同时执行一种类型的导出，因此TYPE指令必须是唯一的。如果您有多个，则只会在文件中找到最后一个。但我测试就可以同时导出多个类型的。</p>
</li>
<li>
<p>请注意，您可以通过向TYPE指令提供以逗号分隔的导出类型列表来链接多个导出，但在这种情况下，您不能将COPY或INSERT与其他导出类型一起使用。</p>
</li>
<li>
<p>某些导出类型不能或不应该直接加载到 IvorySQL 数据库中，仍然需要很少的手动编辑。GRANT，TABLESPACE，TRIGGER，FUNCTION，PROCEDURE，TYPE，QUERY和PACKAGE导出类型就是这种情况，特别是如果您有PLSQL代码或Oracle特定SQL。</p>
</li>
<li>
<p>对于TABLESPACE，您必须确保系统上存在文件路径，对于SYNONYM，您可以确保对象的所有者和模式对应于新的PostgreSQL数据库设计。</p>
</li>
<li>
<p>建议导出表结构时，一个类型一个类型的操作，避免其它错误相互影响。</p>
</li>
</ol>
</div>
</blockquote>
</div>
<div class="sect3">
<h4 id="测试连接"><a class="anchor" href="#测试连接"></a>3.4.1. <strong>测试连接</strong></h4>
<div class="paragraph">
<p>设置Oracle数据库DSN后，您可以执行ora2pg以查看它是否有效：</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-asciidoc hljs" data-lang="asciidoc"># ora2pg -t SHOW_VERSION -c ora2pg.conf
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0</code></pre>
</div>
</div>
</div>
<div class="sect3">
<h4 id="迁移成本评估"><a class="anchor" href="#迁移成本评估"></a>3.4.2. 迁移成本评估</h4>
<div class="paragraph">
<p>估算从Oracle到PostgreSQL的迁移过程的成本并不容易。为了获得对此迁移成本的良好评估，Ora2Pg将检查所有数据库对象，所有函数和存储过程，以检测是否仍有一些对象和PL / SQL代码无法由Ora2Pg自动转换。</p>
</div>
<div class="paragraph">
<p>Ora2Pg具有内容分析模式，该模式检查Oracle数据库以生成有关Oracle数据库包含的内容和无法导出的内容的文本报告。</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-asciidoc hljs" data-lang="asciidoc"># ora2pg -t SHOW_REPORT --estimate_cost -c ora2pg.conf
[========================>] 11/11 tables (100.0%) end of scanning.
[========================>] 11/11 objects types (100.0%) end of objects auditing.
-------------------------------------------------------------------------------
Ora2Pg v24.0 - Database Migration Report
-------------------------------------------------------------------------------
Version Oracle Database 19c Enterprise Edition Release 19.0.0.0.0
Schema SH
Size 287.25 MB
-------------------------------------------------------------------------------
Object Number Invalid Estimated cost Comments Details
-------------------------------------------------------------------------------
DATABASE LINK 0 0 0 Database links will be exported as SQL/MED IvorySQL's Foreign Data Wrapper (FDW) extensions using oracle_fdw.
DIMENSION 5 0 0
GLOBAL TEMPORARY TABLE 0 0 0 Global temporary table are not supported by PostgreSQL and will not be exported. You will have to rewrite some application code to match the PostgreSQL temporary table behavior.
INDEX 20 0 3.4 14 index(es) are concerned by the export, others are automatically generated and will do so on PostgreSQL. Bitmap will be exported as btree_gin index(es) and hash index(es) will be exported as b-tree index(es) if any. Domain index are exported as b-tree but commented to be edited to mainly use FTS. Cluster, bitmap join and IOT indexes will not be exported at all. Reverse indexes are not exported too, you may use a trigram-based index (see pg_trgm) or a reverse() function based index and search. Use 'varchar_pattern_ops', 'text_pattern_ops' or 'bpchar_pattern_ops' operators in your indexes to improve search with the LIKE operator respectively into varchar, text or char columns. 11 bitmap index(es). 1 domain index(es). 2 b-tree index(es).
INDEX PARTITION 196 0 0 Only local indexes partition are exported, they are build on the column used for the partitioning.
JOB 0 0 0 Job are not exported. You may set external cron job with them.
MATERIALIZED VIEW 2 0 6 All materialized view will be exported as snapshot materialized views, they are only updated when fully refreshed.
SYNONYM 0 0 0 SYNONYMs will be exported as views. SYNONYMs do not exists with PostgreSQL but a common workaround is to use views or set the PostgreSQL search_path in your session to access object outside the current schema.
TABLE 11 0 1.1 1 external table(s) will be exported as standard table. See EXTERNAL_TO_FDW configuration directive to export as file_fdw foreign tables or use COPY in your code if you just want to load data from external files. Total number of rows: 1063384. Top 10 of tables sorted by number of rows:. sales has 918843 rows. costs has 82112 rows. customers has 55500 rows. supplementary_demographics has 4500 rows. times has 1826 rows. promotions has 503 rows. products has 72 rows. countries has 23 rows. channels has 5 rows. sales_transactions_ext has 0 rows. Top 10 of largest tables:.
TABLE PARTITION 56 0 5.6 Partitions are exported using table inheritance and check constraint. Hash and Key partitions are not supported by PostgreSQL and will not be exported. 56 RANGE partitions..
VIEW 1 0 1 Views are fully supported but can use specific functions.
-------------------------------------------------------------------------------
Total 291 0 17.10 17.10 cost migration units means approximatively 1 man-day(s). The migration unit was set to 5 minute(s)
------------------------------------------------------------------------------
Migration level : A-1
-------------------------------------------------------------------------------
Migration levels:
A - Migration that might be run automatically
B - Migration with code rewrite and a human-days cost up to 5 days
C - Migration with code rewrite and a human-days cost above 5 days
Technical levels:
1 = trivial: no stored functions and no triggers
2 = easy: no stored functions but with triggers, no manual rewriting
3 = simple: stored functions and/or triggers, no manual rewriting
4 = manual: no stored functions but with triggers or views with code rewriting
5 = difficult: stored functions and/or triggers with code rewriting
-------------------------------------------------------------------------------</code></pre>
</div>
</div>
</div>
<div class="sect3">
<h4 id="导出sh表构"><a class="anchor" href="#导出sh表构"></a>3.4.3. <strong>导出SH表构</strong></h4>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-asciidoc hljs" data-lang="asciidoc"># ora2pg -c ora2pg.conf
[========================>] 11/11 tables (100.0%) end of scanning.
[========================>] 12/12 tables (100.0%) end of table export.
[========================>] 1/1 views (100.0%) end of output.
[========================>] 0/0 sequences (100.0%) end of output.
[========================>] 0/0 procedures (100.0%) end of procedures export.
[========================>] 0/0 triggers (100.0%) end of output.
[========================>] 0/0 functions (100.0%) end of functions export.
[========================>] 0/0 packages (100.0%) end of output.
[========================>] 56/56 partitions (100.0%) end of output.
[========================>] 0/0 types (100.0%) end of output.
[========================>] 2/2 materialized views (100.0%) end of output.
[========================>] 0/0 dblink (100.0%) end of output.
[========================>] 0/0 synonyms (100.0%) end of output.
[========================>] 2/2 directory (100.0%) end of output.
Fixing function calls in output files....</code></pre>
</div>
</div>
</div>
<div class="sect3">
<h4 id="导出sh用户数据"><a class="anchor" href="#导出sh用户数据"></a>3.4.4. <strong>导出SH用户数据</strong></h4>
<div class="paragraph">
<p>配置ora2pg.conf 的TYPE 为COPY 或 INSERT</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-asciidoc hljs" data-lang="asciidoc"># cp ora2pg.conf sh_data.conf
# vi sh_data.conf
ORACLE_HOME /opt/oracle/product/19c/dbhome_1
ORACLE_DSN dbi:Oracle:host=localhost;sid=ORCLCDB;port=1521
ORACLE_USER system
ORACLE_PWD oracle
SCHEMA SH
EXPORT_SCHEMA 1
DISABLE_UNLOGGED 1
SKIP fkeys ukeys checks
TYPE COPY
NLS_LANG AMERICAN_AMERICA.UTF8
OUTPUT sh_data.sql</code></pre>
</div>
</div>
<div class="paragraph">
<p>导出数据</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-asciidoc hljs" data-lang="asciidoc"># ora2pg -c sh_data.conf
[========================>] 11/11 tables (100.0%) end of scanning.
[========================>] 5/5 rows (100.0%) Table CHANNELS (5 recs/sec)
[> ] 5/1063384 total rows (0.0%) - (0 sec., avg: 5 recs/sec).
[> ] 0/82112 rows (0.0%) Table COSTS_1995 (0 recs/sec)
[> ] 5/1063384 total rows (0.0%) - (0 sec., avg: 5 recs/sec).
[> ] 0/82112 rows (0.0%) Table COSTS_H1_1997 (0 recs/sec)
[> ] 5/1063384 total rows (0.0%) - (0 sec., avg: 5 recs/sec).
[> ] 0/82112 rows (0.0%) Table COSTS_1996 (0 recs/sec)
[> ] 5/1063384 total rows (0.0%) - (0 sec., avg: 5 recs/sec).
……………………………………………………………
[========================>] 4500/4500 rows (100.0%) Table SUPPLEMENTARY_DEMOGRAPHICS (4500 recs/sec)
[=======================> ] 1061558/1063384 total rows (99.8%) - (45 sec., avg: 23590 recs/sec).
[========================>] 1826/1826 rows (100.0%) Table TIMES (1826 recs/sec)
[========================>] 1063384/1063384 total rows (100.0%) - (45 sec., avg: 23630 recs/sec).
[========================>] 1063384/1063384 rows (100.0%) on total estimated data (45 sec., avg: 23630 recs/sec)
Fixing function calls in output files...</code></pre>
</div>
</div>
<div class="paragraph">
<p>查看导出的文件：</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-asciidoc hljs" data-lang="asciidoc">[root@test01 ora2pg]# ls -lrt *.sql
-rw-r--r-- 1 root root 15716 Jul 2 21:21 TABLE_sh.sql
-rw-r--r-- 1 root root 858 Jul 2 21:21 VIEW_sh.sql
-rw-r--r-- 1 root root 2026 Jul 2 21:21 TABLESPACE_sh.sql
-rw-r--r-- 1 root root 345 Jul 2 21:21 SEQUENCE_sh.sql
-rw-r--r-- 1 root root 2382 Jul 2 21:21 GRANT_sh.sql
-rw-r--r-- 1 root root 344 Jul 2 21:21 TRIGGER_sh.sql
-rw-r--r-- 1 root root 346 Jul 2 21:21 PROCEDURE_sh.sql
-rw-r--r-- 1 root root 344 Jul 2 21:21 PACKAGE_sh.sql
-rw-r--r-- 1 root root 345 Jul 2 21:21 FUNCTION_sh.sql
-rw-r--r-- 1 root root 6771 Jul 2 21:21 PARTITION_sh.sql
-rw-r--r-- 1 root root 341 Jul 2 21:21 TYPE_sh.sql
-rw-r--r-- 1 root root 342 Jul 2 21:21 QUERY_sh.sql
-rw-r--r-- 1 root root 950 Jul 2 21:21 MVIEW_sh.sql
-rw-r--r-- 1 root root 344 Jul 2 21:21 SYNONYM_sh.sql
-rw-r--r-- 1 root root 926 Jul 2 21:21 DIRECTORY_sh.sql
-rw-r--r-- 1 root root 343 Jul 2 21:21 DBLINK_sh.sql
-rw-r--r-- 1 root root 55281235 Jul 2 17:11 sh_data.sql</code></pre>
</div>
</div>
<div class="paragraph">
<p>以同样的方法分别导出HR，SCOTT用户数据。</p>
</div>
</div>
</div>
<div class="sect2">
<h3 id="在ivorysql环境中创建orcl库"><a class="anchor" href="#在ivorysql环境中创建orcl库"></a>3.5. 在IvorySQL环境中创建orcl库</h3>
<div class="paragraph">
<p>创建ORCL 数据库</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-asciidoc hljs" data-lang="asciidoc"># su - ivorysql
Last login: Tue Jul 2 20:04:30 CST 2019 on pts/3
$ createdb orcl
$ psql
psql (17.5)
Type "help" for help.
ivorysql=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
-----------+----------+-----------+---------+-------+------------+-----------------+-----------------------
ivorysql | ivorysql | SQL_ASCII | C | C | | libc |
orcl | ivorysql | SQL_ASCII | C | C | | libc |
postgres | ivorysql | SQL_ASCII | C | C | | libc |
template0 | ivorysql | SQL_ASCII | C | C | | libc | =c/ivorysql +
| | | | | | | ivorysql=CTc/ivorysql
template1 | ivorysql | SQL_ASCII | C | C | | libc | =c/ivorysql +
| | | | | | | ivorysql=CTc/ivorysql
(5 rows)
ivorysql=#</code></pre>
</div>
</div>
<div class="literalblock">
<div class="content">
<pre>创建SH,HR,SCOTT 用户：</pre>
</div>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-asciidoc hljs" data-lang="asciidoc">$ psql orcl
psql (17.5)
Type "help" for help.
orcl=#
orcl=# create user sh with password 'sh';
CREATE ROLE</code></pre>
</div>
</div>
</div>
</div>
</div>
<div class="sect1">
<h2 id="迁移门户"><a class="anchor" href="#迁移门户"></a>4. 迁移门户</h2>
<div class="sectionbody">
<div class="sect2">
<h3 id="导入表结构"><a class="anchor" href="#导入表结构"></a>4.1. 导入表结构</h3>
<div class="paragraph">
<p>由于有物化视图，在TABLE_sh.sql 里包含了物化视图的索引，会创建失败。需先创建表，在创建物化视图，最后创建索引。</p>
</div>
<div class="paragraph">
<p>取消物化视图索引，后面单独创建：</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-asciidoc hljs" data-lang="asciidoc">CREATE INDEX fw_psc_s_mv_chan_bix ON fweek_pscat_sales_mv (channel_id);
CREATE INDEX fw_psc_s_mv_promo_bix ON fweek_pscat_sales_mv (promo_id);
CREATE INDEX fw_psc_s_mv_subcat_bix ON fweek_pscat_sales_mv (prod_subcategory);
CREATE INDEX fw_psc_s_mv_wd_bix ON fweek_pscat_sales_mv (week_ending_day);
CREATE TEXT SEARCH CONFIGURATION en (COPY = pg_catalog.english);
ALTER TEXT SEARCH CONFIGURATION en ALTER MAPPING FOR hword, hword_part, word WITH unaccent, english_stem;</code></pre>
</div>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-asciidoc hljs" data-lang="asciidoc">psql orcl -f tab.sql
ALTER TABLE PARTITION sh.sales OWNER TO sh;
COMMENT
COMMENT
COMMENT
COMMENT
COMMENT
COMMENT
COMMENT
ALTER TABLE
ALTER TABLE