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 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"，共同学习，共同成长！！！
