Shulou(Shulou.com)06/01 Report--

Migration of EAS attachment tables from database to FTP

Environment description:

EAS:EAS8.2

DB:Oracle 11.2.0.1.0

FTP: vsftpd-2.2.2

FTP_OS: CentOS release 6.7 (Final)

Description of the problem:

After the expense control reimbursement was enabled in the Kingdee system, the size of the database increased rapidly from 5G to 350g within a year, including the attached table.

The ffile field of t_bas_attachment belongs to BLOB type, with a size of 270g and an average size of 1m per piece of data. Database performance and daily maintenance will be affected, for example:

When backing up to the t_bas_attachment table during daily expdp backups, ORA-01555 errors often occur (http://blog.itpub.net/29785807/viewspace-2640146/

), blindly adjusting the parameters will not fundamentally solve the problem

Solution:

Through EAS, migrate the attachment to the FTP server; that is, migrate the ffile field data of the tasking base _ attachment table to the FTP server

Implementation process:

One: set up FTP server

Second: change the storage mode of attachments

Three: configure FTP and enable

Four: view migration information

Five: start the migration

Six: release segment space

Seven: free up data file space

Eight: query post-migration information

One: set up FTP server

1.1 View the vsftpd that comes with the system

[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 set vsftpd.conf parameters

[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.3Create ftp users

[root@chenjchserver cjcfile] # useradd cjcuser

[root@chenjchserver cjcfile] # passwd cjcuser

[root@chenjchserver cjcfile] # id cjcuser

1.4 create and set ftp directory permissions

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

[root@chenjchserver cjcfile] # chmod 777 / chenjchserver/cjcfile

1.5 restart the ftp service

[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 10 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 service setup self-startup

[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 testing the basic functions of ftp

-Windows connects to FTP

-1 Log in to FTP

C:\ Users\ Administrator > ftp 192.*

Connect to 192.*.

220 (vsFTPd 2.2.2)

User (192. Customers: (none)): cjcuser

331 Please specify the password.

Password:

230 Login successful.

-2 check what files are in the root directory

Ftp > dir

200 PORT command successful. Consider using PASV.

150 Here comes the directory listing.

-rw-r--r-- 100 10 Dec 27 06:26 1.text

226 Directory send OK.

Ftp: 64 bytes received, 0.00 seconds 32.00 kilobytes per second.

-3 create a FTP directory test1227 in the root directory

Ftp > mkdir test1227

257 "/ test1227" created

-4 change to the test1227 directory

Ftp > cd test1227

250 Directory successfully changed.

-5 switch the local directory

Ftp > lcd Desktop

The current local directory is C:\ Users\ Administrator\ Desktop.

-6 upload files

Ftp > put 000111222.txt

200 PORT command successful. Consider using PASV.

150 Ok to send data.

226 Transfer complete.

Ftp: send 61 bytes in 0.02 seconds 3.81 kilobytes per second.

-7 download files

Ftp > cd..

250 Directory successfully changed.

Ftp > dir

200 PORT command successful. Consider using PASV.

150 Here comes the directory listing.

-rw-r--r-- 100 10 Dec 27 06:26 1.text

Drwxr-xr-x 2 501 501 4096 Dec 27 07:01 test1227

226 Directory send OK.

Ftp: received 130Bytes, 0.00s 32.50kbytes / s.

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: received 10 bytes and took 0.02 seconds and 0.45 kilobytes per second.

-8 Delete a single file

Ftp > delete 1.txt

250 Delete operation successful.

-9 Delete the files under the folder

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-- 100 10 Dec 27 06:26 1.txt

Drwxr-xr-x 2 501 501 4096 Dec 27 07:11 test1227

226 Directory send OK.

Ftp: received 129bytes and used 0.01s 25.80kbytes per second.

-10 Delete a directory

Ftp > rmdir test1227

250 Remove directory operation successful.

Second: change the storage mode of attachments

The way of changing attachment is changed from DB to FTP

-Parameter setting-attachment storage method

Three: configure FTP and enable

Four: view migration information

-the foreground and background respectively check whether the number of attachments to be migrated is the same.

Select count (*) from T_BAS_ATTACHMENT

Five: start the migration

-it takes 5 hours.

After the migration is completed, if the foreground interface cannot be closed, the task manager can kill java.exe and force the exit.

During the migration, you can see that a new file has been generated in the FTP directory:

-View FTP logs

-you can check how many attachments have been migrated through the database and how many attachments are left to be migrated

Select count (*), fstoragetype

From t_bas_attachment

Group by fstoragetype

Order by 2 desc

Six: release segment space

6.1 View segment information

Select table_name, column_name, segment_name, INDEX_NAME

From dba_lobs

Where table_name = 'Tunable BASTACHMENTS'

And owner = 'CHENJCH'

6.2 View Segment size

Select bytes / 1024 / 1024 | | 'MB', segment_name, segment_type

From dba_segments

Where owner = 'CHENJCH'

And segment_name in ('Tunable basic equipment'

'SYS_LOB0000xxxxxxxxxxxxx'

'SYS_IL00007xxxxxxxxxxxxx')

-- before migration

-- after migration (the size remains the same, although the data is less, but the segment space is not automatically released)

6.3 segment contraction

-it takes 3.5 hours.

-it will take up some disk space.

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

-time consuming 2min

Alter table t_bas_attachment move

-it takes 1 second

Alter index PK_ATTACHMENT rebuild

-collect statistics

EXEC DBMS_STATS.GATHER_TABLE_STATS ('K2',' Tunable basic ATTACHMENT`, estimate_percent = > 100, CASCADE = > TRUE)

-- before querying segment size (segment space has been freed)

6.4 View attachment size

Select max (dbms_lob.getLength (ffile) / 1024 / 1024) as "maximum (MB)"

Min (dbms_lob.getLength (ffile)) as "minimum (Bytes)"

Avg (dbms_lob.getLength (ffile) / 1024 / 1024) as "average (MB)"

From k2.t_bas_attachment

-before migration:

-after migration:

Seven: free up data file space

-at this time, the available space of the data file has been released, but the space occupied by the data file in the operating system will not be automatically released, so the data under high water level can be shrunk.

7.1 View data file information

Select file_id

Bytes / 1024 / 1024 / 1024 as "current size (GB)"

File_name

Tablespace_name

From dba_data_files a

Where tablespace_name = 'CJC_D_TBS'

Order by 1

7.2 View recyclable segment space

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 | | 'Mashing' 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 data files shrink

The Resize size can be increased by about 10m to avoid ORA-03214 errors.

The shrinkage script is as follows:

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

……

8. View post-migration information

The main t_bas_attachment table fstoragetype, fremotepath field has changed

Select b.fnumber

B.fname_l1

To_char (a.fcreatetime, 'yyyy-mm-dd hh34:mi:ss') creation time

To_char (a.flastupdatetime, 'yyyy-mm-dd hh34:mi:ss') update time

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

Before migration:

After migration:

-Fuzzy processing of actual data

Welcome to follow my Wechat official account "IT Little Chen" and learn and grow together!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.