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 1987M;

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

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

……

八 查看迁移后信息

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

select b.fnumber,

b.fname_l1,

to_char(a.fcreatetime, 'yyyy-mm-dd hh34:mi:ss') 创建时间,

to_char(a.flastupdatetime, 'yyyy-mm-dd hh34: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"，共同学习，共同成长！！！