YashanDB Migration Platform (YMP)

・ https://doc.yashandb.com/ymp/23.4/zh/Product-Introduction/Summarize.html

1. Installing YMP

# 关闭防火墙
systemctl stop firewalld
# 关闭开机自启
systemctl disable firewalld

#安装依赖包
yum install -y lsof
yum install -y libaio

#创建用户
useradd -d /home/ymp -m ymp
echo ymp | passwd --stdin ymp

# 一次性写入 /etc/security/limits.conf
cat >> /etc/security/limits.conf << 'EOF'
# YMP YashanDB limits
ymp soft nofile 1048576
ymp hard nofile 1048576
ymp soft nproc 1048576
ymp hard nproc 1048576
ymp soft rss unlimited
ymp hard rss unlimited
ymp soft stack 8192
ymp hard stack 8192
EOF

su - ymp
cd /home/ymp/

#准备OCI包并解压（仅同步Oracle数据需要）
unzip instantclient-basic-linux.x64-19.19.0.0.0dbru.el9.zip

#安装YMP（YMP内置库版本和目标库版本一定要保持一致！！！）
unzip ~/yashan-migrate-platform-23.4.3.2-linux-x86-64.zip
sh /home/ymp/yashan-migrate-platform/bin/ymp.sh install --db /home/ymp/yashandb-23.4.1.109-linux-x86_64.tar.gz --path /home/ymp/instantclient_19_19

#网页访问YMP
・ *http://ip:8090*
（默认用户密码：admin/admin，首次登录需要修改。）

2.YashanDB 参数配置案例

1.使用高级包调优
exec dbms_param.optimize(true, null, 80);

2.查看调优参数值
SELECT DBMS_PARAM.SHOW_RECOMMEND() FROM dual;

4.使用SQL命令调整
--  调整 DATA_BUFFER_SIZE 至 44 GB
ALTER SYSTEM SET DATA_BUFFER_SIZE = 4G scope=spfile;

--  调整 WORK_AREA_POOL_SIZE 至 1 GB
ALTER SYSTEM SET WORK_AREA_POOL_SIZE= 1G  scope=spfile;

--  调整 SHARE_POOL_SIZE 至 1 GB（1024 MB）
ALTER SYSTEM SET SHARE_POOL_SIZE = 1G  scope=spfile;

--  调整 VM_BUFFER_SIZE 至 1 GB（1024 MB）
ALTER SYSTEM SET VM_BUFFER_SIZE = 1G scope=spfile;

5.重启数据库
yasboot cluster restart -c yashandb

6.查看参数值是否修改成功
select * from v$parameter where name in（'DATA_BUFFER_SIZE'，'WORK_AREA_POOL_SIZE','SHARE_POOL_SIZE','VM_BUFFER_SIZE'）;

3. 评估源端数据（以 Oracle 为例）

① 总数据量（不含索引）

SELECT ROUND(SUM(bytes)/1024/1024/1024, 2) AS total_gb
FROM   dba_segments
WHERE  segment_type IN ('TABLE','TABLE PARTITION','TABLE SUBPARTITION','LOBSEGMENT','LOBINDEX')
AND    owner IN ('SCHEMA1','SCHEMA2');

② 表总数

SELECT COUNT(*) AS table_count
FROM   dba_tables
WHERE  owner IN ('SCHEMA1','SCHEMA2');

③ 单表总大小 Top 100（含分区与 LOB）

SELECT * FROM (
  SELECT ts.owner,
         ts.table_name,
         ts.size_gb,
         NVL(dt.partitioned, 'NO') AS is_partitioned -- YES表示分区表，NO表示非分区表
  FROM (
    SELECT s.owner,
           NVL(l.table_name, s.segment_name)        AS table_name,
           ROUND(SUM(s.bytes)/1024/1024/1024, 2)    AS size_gb
    FROM   dba_segments s
    LEFT JOIN dba_lobs l
           ON s.owner = l.owner
          AND s.segment_name = l.segment_name
    WHERE  s.owner IN ('SCHEMA1','SCHEMA2')
    AND    s.segment_type IN ('TABLE','TABLE PARTITION','TABLE SUBPARTITION','LOBSEGMENT','LOBINDEX')
    GROUP  BY s.owner, NVL(l.table_name, s.segment_name)
  ) ts
  LEFT JOIN dba_tables dt
         ON ts.owner = dt.owner
        AND ts.table_name = dt.table_name
  ORDER  BY ts.size_gb DESC
) WHERE ROWNUM <= 100;

④ 单表 LOB 大小 Top 100

SELECT * FROM (
  SELECT l.owner, l.table_name,
         ROUND(SUM(s.bytes)/1024/1024/1024, 2) AS lob_gb
  FROM   dba_lobs l
  JOIN   dba_segments s
         ON l.owner = s.owner
        AND l.segment_name = s.segment_name
  WHERE  l.owner IN ('SCHEMA1','SCHEMA2')
  GROUP  BY l.owner, l.table_name
  ORDER  BY lob_gb DESC
) WHERE ROWNUM <= 100;

⑤ 各分级表数量占比

WITH TableSizes AS (
  SELECT s.owner,
         NVL(l.table_name, s.segment_name)        AS table_name,
         SUM(s.bytes)/1024/1024/1024              AS size_gb
  FROM   dba_segments s
  LEFT JOIN dba_lobs l
         ON s.owner = l.owner
        AND s.segment_name = l.segment_name
  WHERE  s.owner IN ('SCHEMA1','SCHEMA2')
  AND    s.segment_type IN ('TABLE','TABLE PARTITION','TABLE SUBPARTITION','LOBSEGMENT','LOBINDEX')
  GROUP  BY s.owner, NVL(l.table_name, s.segment_name)
)
SELECT
  CASE
    WHEN size_gb < 5 THEN '1. 小表 (0~5G)'
    WHEN size_gb >= 5 AND size_gb < 50 THEN '2. 中表 (5~50G)'
    WHEN size_gb >= 50 AND size_gb < 100 THEN '3. 大表 (50~100G)'
    ELSE '4. 超大表 (>100G)'
  END AS size_level,
  COUNT(*) AS table_count,
  ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 2) || '%' AS ratio
FROM TableSizes
GROUP BY
  CASE
    WHEN size_gb < 5 THEN '1. 小表 (0~5G)'
    WHEN size_gb >= 5 AND size_gb < 50 THEN '2. 中表 (5~50G)'
    WHEN size_gb >= 50 AND size_gb < 100 THEN '3. 大表 (50~100G)'
    ELSE '4. 超大表 (>100G)'
  END
ORDER BY size_level;

⑥ 大表占比（> 5 GB）

WITH tab_size AS (
  SELECT owner, segment_name,
         SUM(bytes)/1024/1024/1024 AS gb
  FROM   dba_segments
  WHERE  owner IN ('SCHEMA1','SCHEMA2')
  AND    segment_type IN ('TABLE','TABLE PARTITION','TABLE SUBPARTITION')
  GROUP  BY owner, segment_name
)
SELECT COUNT(*)                                   AS total_tab,
       SUM(CASE WHEN gb > 5 THEN 1 ELSE 0 END)    AS big_tab,
       ROUND(SUM(CASE WHEN gb > 5 THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) AS big_pct
FROM   tab_size;

YMP install failed with insufficient memory

~]$ /home/ymp/yashan-migrate-platform/bin/ymp.sh install --db /soft/yashandb-23.4.7.103-linux-x86_64.tar.gz --path /home/ymp/instantclient_19_19

09:29:12.202 [main] INFO com.yashandb.tool.ymp.command.util.CommandUtil - task completed, status: SUCCESS
OpenJDK 64-Bit Server VM warning: INFO: os::commit_memory(0x00007f095c9e0000, 65536, 0) failed; error='Not enough space' (errno=12)
#
# There is insufficient memory for the Java Runtime Environment to continue.
# Native memory allocation (mmap) failed to map 65536 bytes for committing reserved memory.
# An error report file with more information is saved as:
# /home/ymp/hs_err_pid8610.log
YMP install failed!

Solution:

Decrease Java heap size (-Xmx/-Xms) and thread stack sizes (-Xss).

~]$ ps aux | grep [y]as | awk '{print $2}' | xargs kill -15
~]$ rm -f /home/ymp/.yasboot/ymp.env
~]$ rm -rf /home/ymp/yashan-migrate-platform/db

~]$ grep -Ev '^$|^#' yashan-migrate-platform/conf/application.properties
~]$ sed -i '/^ymp_memory/s/4/2/' /home/ymp/yashan-migrate-platform/conf/application.properties
~]$ sed -i '/^ymp_direct_memory/s/2/1/' /home/ymp/yashan-migrate-platform/conf/application.properties