EAS附件表由資料庫遷移到FTP

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"，共同學習，共同成長！！！