达梦文件迁移 | 一名数据库爱好者

亲身撸了一遍达梦文件的迁移，全在本文，亲测有效

数据文件迁移

alter tablespace xy1 rename datafile '/dm8/data/DAMENG/xy1.DBF' to '/dm8/data/DAMENG/remove.DBF';

达梦文件迁移-LMLPHP

出现错误：数据库当前处于 online 状态，数据文件不能迁移

alter tablespace xy1 offline;

达梦文件迁移-LMLPHP

再次执行迁移

alter tablespace xy1 rename datafile '/dm8/data/DAMENG/xy1.DBF' to '/dm8/data/DAMENG/remove.DBF';

迁移成功

注意

迁移的表空间文件一定要处于 关机状态

重做日志文件转移

查看当前重做日志

SQL> select group_id,file_id,path,rlog_size from v$rlogfile;


LINEID     group_id    file_id     PATH                          rlog_size
---------- ----------- ----------- ----------------------------- --------------------
1          2           0           /dm8/data/DAMENG/DAMENG01.log 268435456
2          2           1           /dm8/data/DAMENG/DAMENG02.log 268435456
3          2           2           /dm8/data/DAMENG/DAMENG03.log 314572800


used time: 0.854(ms). Execute id is 701.

移动重做日志

SQL> alter database mount;   ---切换到 mount 状态
executed successfully
used time: 18.519(ms). Execute id is 0.
SQL>
SQL>
SQL>
SQL> alter database rename logfile
 '/dm8/data/DAMENG/DAMENG01.log' to '/dm8/data/DAMENG/disk/DAMENG01.log';2
executed successfully        ---转移成功
used time: 276.915(ms). Execute id is 702.
SQL>
SQL>
SQL> alter database open;    ---切换到open状态
executed successfully
used time: 27.070(ms). Execute id is 0.
SQL>
SQL> select group_id,file_id,path,rlog_size from v$rlogfile;    ---再次查看


LINEID     group_id    file_id     PATH                               rlog_size
---------- ----------- ----------- ---------------------------------- --------------------
1          2           0           /dm8/data/DAMENG/disk/DAMENG01.log 268435456
2          2           1           /dm8/data/DAMENG/DAMENG02.log      268435456
3          2           2           /dm8/data/DAMENG/DAMENG03.log      314572800


used time: 2.750(ms). Execute id is 703.
SQL>

转移成功

移动系统文件与roll文件

注：系统文件system与roll文件不允许offline。所以在移动时，只能修改控制文件中的路径

控制文件 转换成 文本文件

dmdba@LAPTOP-FPIQJ438:/dm8/data/DAMENG$ dmctlcvt type=1 src=dm.ctl dest=/dm8/data/DAMENG/dm.txt
DMCTLCVT V8
convert ctl to txt success!

修改 system 和 roll 文件路径

dmdba@LAPTOP-FPIQJ438:/dm8/data/DAMENG$ vim dm.txt
dmdba@LAPTOP-FPIQJ438:/dm8/data/DAMENG$
dmdba@LAPTOP-FPIQJ438:/dm8/data/DAMENG$ cat dm.txt | grep -i system
ts_name=SYSTEM
fil_path=/dm8/data/DAMENG/disk/SYSTEM.DBF
dmdba@LAPTOP-FPIQJ438:/dm8/data/DAMENG$ cat dm.txt | grep -i roll
ts_name=ROLL
fil_path=/dm8/data/DAMENG/disk/ROLL.DBF
dmdba@LAPTOP-FPIQJ438:/dm8/data/DAMENG$

两个文件路径修改到 /dm8/data/DAMENG/disk/

文本文件 还原到 控制文件

dmdba@LAPTOP-FPIQJ438:/dm8/data/DAMENG$ dmctlcvt type=2 src=dm.txt dest=/dm8/data/DAMENG/dm.ctl
DMCTLCVT V8
convert txt to ctl success!

关闭数据库服务

dmdba@LAPTOP-FPIQJ438:/dm8/bin$ ./DmServiceDMSERVER stop
Stopping DmServiceDMSERVER:
                                                           [ OK ]
dmdba@LAPTOP-FPIQJ438:/dm8/bin$

将 system 和 roll 文件复制到目标目录

dmdba@LAPTOP-FPIQJ438:/dm8/bin$
dmdba@LAPTOP-FPIQJ438:/dm8/bin$ cp /dm8/data/DAMENG/SYSTEM.DBF  /dm8/data/DAMENG/disk/SYSTEM.DBF
dmdba@LAPTOP-FPIQJ438:/dm8/bin$
dmdba@LAPTOP-FPIQJ438:/dm8/bin$
dmdba@LAPTOP-FPIQJ438:/dm8/bin$
dmdba@LAPTOP-FPIQJ438:/dm8/bin$ cp /dm8/data/DAMENG/ROLL.DBF  /dm8/data/DAMENG/disk/ROLL.DBF
dmdba@LAPTOP-FPIQJ438:/dm8/bin$

启动数据库

dmdba@LAPTOP-FPIQJ438:/dm8/bin$ ./DmServiceDMSERVER start
Starting DmServiceDMSERVER:
                                                           [ OK ]
dmdba@LAPTOP-FPIQJ438:/dm8/bin$

查看验证

dmdba@LAPTOP-FPIQJ438:/dm8/bin$ disql SYSDBA/SYSDBA


Server[LOCALHOST:5236]:mode is normal, state is open
login used time : 3.206(ms)
disql V8
SQL>
SQL>
SQL>
SQL> select file_name from dba_data_files;


LINEID     file_name
---------- --------------------------------
1          /dm8/data/DAMENG/xy.DBF
2          /dm8/data/DAMENG/disk/ROLL.DBF
3          /dm8/data/DAMENG/TEMP.DBF
4          /dm8/data/DAMENG/MAIN.DBF
5          /dm8/data/DAMENG/disk/SYSTEM.DBF


used time: 13.853(ms). Execute id is 501.
SQL>
SQL>
SQL> select type,name,value from v$parameter  where name like '%TEMP%';


LINEID     TYPE      name             VALUE
---------- --------- ---------------- ----------------
1          READ ONLY TEMP_PATH        /dm8/data/DAMENG
2          IN FILE   TEMP_SIZE        10
3          SYS       TEMP_SPACE_LIMIT 0


used time: 6.779(ms). Execute id is 502.
SQL>
SQL>

可以看到 system 和 roll 文件更改成功

临时文件移动位置

查看相关参数

SQL> select type,name,value from v$parameter  where name like '%TEMP%';


LINEID     TYPE      name             VALUE
---------- --------- ---------------- ----------------
1          READ ONLY TEMP_PATH        /dm8/data/DAMENG
2          IN FILE   TEMP_SIZE        10
3          SYS       TEMP_SPACE_LIMIT 0


used time: 5.754(ms). Execute id is 601.

发现路径值 TEMP_PATH 的值为 /dm8/data/DAMENG

修改配置文件

dmdba@LAPTOP-FPIQJ438:/dm8/data/DAMENG$ vim dm.ini
dmdba@LAPTOP-FPIQJ438:/dm8/data/DAMENG$ cat dm.ini | grep TEMP_PATH
                TEMP_PATH                       = /dm8/data/DAMENG/disk                            #temporary file path

将路径值 TEMP_PATH 的值修改为 /dm8/data/DAMENG/disk

重启服务生效

dmdba@LAPTOP-FPIQJ438:/dm8/bin$ ./DmServiceDMSERVER restart
Stopping DmServiceDMSERVER:
                                                           [ OK ]
Starting DmServiceDMSERVER:
                                                           [ OK ]
dmdba@LAPTOP-FPIQJ438:/dm8/bin$

验证

SQL>
SQL> select type,name,value from v$parameter  where name like '%TEMP%';


LINEID     TYPE      name             VALUE
---------- --------- ---------------- ---------------------
1          READ ONLY TEMP_PATH        /dm8/data/DAMENG/disk
2          IN FILE   TEMP_SIZE        10
3          SYS       TEMP_SPACE_LIMIT 0


used time: 7.979(ms). Execute id is 501.
SQL>
SQL>

位置改变成功