20200107-EAS附件表由数据库迁移到FTP

环境说明：

EAS:EAS8.2
DB:Oracle 11.2.0.1.0
FTP: vsftpd-2.2.2
FTP_OS: CentOS release 6.7 (Final)

问题说明：

金蝶系统启用费控报销后，数据库大小在一年内由5G迅速上升至350G，其中附件表
t_bas_attachment 的 ffile 字段属于BLOB类型， 大小达270G，平均1条数据1M大小，数据库性能和日常维护都会受到影响，例如：
日常expdp备份时备份到t_bas_attachment表时，经常会出现ORA-01555错误( http://blog.itpub.net/29785807/viewspace-2640146/
) ，一味的调大参数并不能从根本上解决问题；

解决方案：

通过EAS，将附件迁移到FTP服务器上；即将 t_bas_attachment表 ffile 字段数据迁移到FTP服务器上；
实施过程：

一：搭建FTP服务器
二：更改附件存储方式
三：配置FTP并启用
四：查看迁移信息
五：开始迁移
六：释放段空间
七：释放数据文件空间
八：查询迁移后信息

一：搭建FTP服务器

1.1 查看系统自带的vsftpd

[root@chenjchserver ~]# cat /etc/issue
CentOS release 6.7 (Final)

[root@chenjchserver ~]# rpm -qa|grep vsftpd
vsftpd-2.2.2-14.el6.x86_64

1.2 设置vsftpd.conf参数

[root@chenjchserver vsftpd]# ls
ftpusers  user_list  vsftpd.conf  vsftpd_conf_migrate.sh

[root@chenjchserver vsftpd]# cp vsftpd.conf vsftpd.conf.bak

[root@chenjchserver ~]# vi /etc/vsftpd/vsftpd.conf
anonymous_enable=NO
local_enable=YES
write_enable=YES
local_umask=022
dirmessage_enable=YES
xferlog_enable=YES
xferlog_std_format=YES
connect_from_port_20=YES
xferlog_file=/var/log/xferlog
idle_session_timeout=6000
data_connection_timeout=1200
chroot_list_enable=YES
chroot_list_file=/etc/vsftpd/chroot_list
chroot_list_enable=YES
chroot_local_user=YES
userlist_deny=NO
userlist_enable=YES
userlist_file=/etc/vsftpd/user_list
chroot_list_enable=YES
local_root=/chenjchserver/cjcfile
listen=YES
pam_service_name=vsftpd
userlist_enable=YES
tcp_wrappers=YES

1.3 创建ftp用户

[root@chenjchserver cjcfile]# useradd cjcuser
[root@chenjchserver cjcfile]# passwd cjcuser
[root@chenjchserver cjcfile]# id cjcuser

1.4 创建并设置ftp目录权限

[root@chenjchserver cjcfile]# mkdir /chenjchserver/cjcfile -p
[root@chenjchserver cjcfile]# chmod 777 /chenjchserver/cjcfile

1.5 重启ftp服务

[root@chenjchserver ~]# service vsftpd status
vsftpd is stopped

[root@chenjchserver ~]# service vsftpd start
Starting vsftpd for vsftpd:                                [  OK  ]

[root@chenjchserver ~]# ps -ef|grep vsftpd
root      4330     1  0 14:10 ?        00:00:00 /usr/sbin/vsftpd /etc/vsftpd/vsftpd.conf
root      4333  3897  0 14:10 pts/0    00:00:00 grep vsftpd

1.6 ftp 服务设置自启动

[root@chenjchserver ~]# chkconfig --list|grep vsftpd
vsftpd           0:off 1:off 2:off 3:off 4:off 5:off 6:off

[root@chenjchserver ~]# chkconfig vsftpd on

[root@chenjchserver ~]# chkconfig --list|grep vsftpd
vsftpd           0:off 1:off 2:on  3:on  4:on  5:on  6:off

1.7 测试ftp基本功能

Windows 连接FTP
1 登陆FTP

C:\Users\Administrator>ftp 192.*.*.*
连接到 192.*.*.*。
220 (vsFTPd 2.2.2)
用户(192.*.*.*:(none)): cjcuser
331 Please specify the password.
密码:
230 Login successful.

2 查看根目录下有哪些文件

ftp> dir
200 PORT command successful. Consider using PASV.
150 Here comes the directory listing.
-rw-r--r--    1 0        0              10 Dec 27 06:26 1.text
226 Directory send OK.
ftp: 收到 64 字节，用时 0.00秒 32.00千字节/秒。

3 在根目录创建FTP目录test1227

ftp> mkdir test1227
257 "/test1227" created

4 切换到test1227目录

ftp> cd test1227
250 Directory successfully changed.

5 切换本地目录

ftp> lcd Desktop
目前的本地目录 C:\Users\Administrator\Desktop。

6 上传文件

ftp> put 000111222.txt
200 PORT command successful. Consider using PASV.
150 Ok to send data.
226 Transfer complete.
ftp: 发送 61 字节，用时 0.02秒 3.81千字节/秒。

7 下载文件

ftp> cd ..
250 Directory successfully changed.

ftp> dir
200 PORT command successful. Consider using PASV.
150 Here comes the directory listing.
-rw-r--r--    1 0        0              10 Dec 27 06:26 1.text
drwxr-xr-x    2 501      501          4096 Dec 27 07:01 test1227

226 Directory send OK.
ftp: 收到 130 字节，用时 0.00秒 32.50千字节/秒。

ftp> get 1.txt
200 PORT command successful. Consider using PASV.
550 Failed to open file.

ftp> get 1.text
200 PORT command successful. Consider using PASV.
150 Opening BINARY mode data connection for 1.text (10 bytes).
226 Transfer complete.
ftp: 收到 10 字节，用时 0.02秒 0.45千字节/秒。

8 删除单个文件

ftp> delete 1.txt
250 Delete operation successful.

9 删除文件夹下文件

ftp> mdelete test1227
200 Switching to ASCII mode.
mdelete test1227/000111222.txt? yes
250 Delete operation successful.

ftp> dir
200 PORT command successful. Consider using PASV.
150 Here comes the directory listing.
-rw-r--r--    1 0        0              10 Dec 27 06:26 1.txt
drwxr-xr-x    2 501      501          4096 Dec 27 07:11 test1227
226 Directory send OK.
ftp: 收到 129 字节，用时 0.01秒 25.80千字节/秒。

10 删除目录

ftp> rmdir test1227
250 Remove directory operation successful.

二：更改附件存储方式

附件更改方式由DB改成FTP
参数设置—附件存储方式

三：配置FTP并启用

四：查看迁移信息

前后台分别查看待迁移附件数量是否一致

select count (*) from T_BAS_ATTACHMENT ;

五：开始迁移

耗时5小时


迁移完成后，前台界面如果关不掉，可以 任务管理器杀掉 java.exe，强制退出；
迁移过程中可用看到FTP目录下已有新文件产生：

查看FTP日志

可用通过数据库查看迁移完成多少附件，剩余多少附件待迁移

select count (*), fstoragetype
  from t_bas_attachment
  group by fstoragetype
  order by 2 desc ;

六：释放段空间

6.1 查看段信息

select table_name , column_name , segment_name , INDEX_NAME
  from dba_lobs
  where table_name = 'T_BAS_ATTACHMENT'
   and owner = 'CHENJCH' ;

6.2 查看段大小

select bytes / 1024 / 1024 || ' MB' , segment_name , segment_type
  from dba_segments
  where owner = 'CHENJCH'
   and segment_name in ( 'T_BAS_ATTACHMENT' ,
                        'SYS_LOB0000xxxxxxxxxxxxx' ,
                        'SYS_IL00007xxxxxxxxxxxxx' );

迁移前

迁移后(大小没变，数据虽然少了，但是段空间没有自动释放)

6.3 段收缩

耗时3.5h
会占用一部分磁盘空间

ALTER TABLE T_BAS_ATTACHMENT  MODIFY LOB ( FFILE ) ( SHRINK SPACE );

耗时2min

alter table t_bas_attachment move ;

耗时1秒

alter index PK_ATTACHMENT rebuild ;

收集统计信息

EXEC DBMS_STATS.GATHER_TABLE_STATS ( 'K2' , 'T_BAS_ATTACHMENT' , estimate_percent => 100 , CASCADE => TRUE );

在查询段大小（段空间已释放）

6.4 查看附件大小

select max ( dbms_lob.getLength ( ffile ) / 1024 / 1024 ) as " 最大(MB)" ,
       min ( dbms_lob.getLength ( ffile )) as " 最小(Bytes)" ,
       avg ( dbms_lob.getLength ( ffile ) / 1024 / 1024 ) as " 平均(MB)"
  from k2.t_bas_attachment ;

迁移前：

迁移后：

七：释放数据文件空间

此时数据文件可用空间已经释放了，但是数据文件占用操作系统的空间不会自动释放，可以对高水位下的数据进行收缩；

7.1 查看数据文件信息

select file_id ,
       bytes / 1024 / 1024 / 1024 as " 当前大小(GB)" ,
       file_name ,
       tablespace_name
  from dba_data_files a
  where tablespace_name = 'CJC_D_TBS'
  order by 1 ;

7.2 查看可回收的段空间

select a.file_id ,
       a.file_name ,
       a.filesize ,
       b.freesize ,
       ( a.filesize - b.freesize ) usedsize ,
       c.hwmsize ,
       c.hwmsize - ( a.filesize - b.freesize ) unsedsize_belowhwm ,
       a.filesize - c.hwmsize canshrinksize ,
       'alter database datafile ' || a.file_name || ' resize ' || c.hwmsize || 'M;' cmd
  from ( select file_id , file_name , round ( bytes / 1024 / 1024 ) filesize
          from dba_data_files
         where tablespace_name = 'CJC_D_TBS' ) a ,
       ( select file_id , round ( sum ( dfs.bytes ) / 1024 / 1024 ) freesize
          from dba_free_space dfs
         where tablespace_name = 'CJC_D_TBS'
         group by file_id ) b ,
       ( select file_id , round ( max ( block_id ) * 8 / 1024 ) HWMsize
          from dba_extents
         where tablespace_name = 'CJC_D_TBS'
         group by file_id ) c
  where a.file_id = b.file_id
   and a.file_id = c.file_id
  order by unsedsize_belowhwm desc ;

7.3 数据文件收缩

Resize 大小可用适量增大10M左右，避免ORA-03214错误；

收缩脚本如下：

alter database datafile 'D:\ORADATA\CJC_D_TBS35A.DBF' resize 1987 M ;
alter database datafile 'D:\ORADATA\CJC_D_TBS36A.DBF' resize 1987 M ;
alter database datafile 'D:\ORADATA\CJC_D_TBS33A.DBF' resize 1988 M ;

八 查看迁移后信息

主要 t_bas_attachment 表fstoragetype , fremotepath 字段有变化

select b.fnumber ,
       b.fname_l1 ,
       to_char ( a.fcreatetime , 'yyyy-mm-dd hh24:mi:ss' ) 创建时间 ,
       to_char ( a.flastupdatetime , 'yyyy-mm-dd hh24:mi:ss' ) 更新时间 ,
       a.fname_l1 ,
       a.fsimplename ,
       a.ftype ,
       a.ffile ,
       a.fsize ,
       fattachid ,
       fstoragetype ,
       fremotepath
  from t_bas_attachment a
  left join t_pm_user b
on a.fcreatorid = b.fid ;

迁移前：

迁移后：

欢迎关注我的微信公众号"IT小Chen"