EBS 清理附件表空间apps_ts_media表空间内附件fnd_lobs

参考文档：

Avoiding abnormal growth of FND_LOBS table in Applications 11i [ID 298698.1]

FND_LOBS is usually one of the top 10. This is because, it stores all the attachments that have been uploaded to Oracle Applications. There is a LOB field within this table called FILE_DATA, the corresponding LOB segment (e.g., APPLSYS.SYS_IL0000680397C00004$$) is where the actual attachment data is stored, and isusually very large

--Concurrent Program "Purge Obsolete Generic File Manager Data  "   To The Sysadmin User.'

--PCTVERSION is the percent of used LOB data blocks that is available for versioning old LOB data

FND_LOBS table's FILE_DATA LOB column usually gets the data uploaded only once, but is read multiple times. Hence it is not necessary to keep older versions of LOB data. It is recommended that this value be changed to "0".

 

By default PCTVERSION is set to 10%. So, most of the instances usually have it set to 10%, it must be set to 0% explicitly. The value can be changed any time in a running system.

Use the following query to find out currently set value for PCTVERSION:

select PCTVERSION from dba_lobs where TABLE_NAME = 'FND_LOBS' and COLUMN_NAME='FILE_DATA'; 

PCTVERSION

----------

        10

 

PCTVERSION can be changed using the following SQL (it can be run anytime in a running system):

 

ALTER TABLE FND_LOBS MODIFY LOB (FILE_DATA) ( PCTVERSION 0 );

 

 

Please be aware :

The solution shows, how to avoid the increase of the table in future - it does not shrink the current tablespace !

The only supported way to shrink the tablespace is to export and re-import the whole Database.

 

oracle创建了两个objects　TEST这个是我们很熟悉的表了，PCT_LOB就是LOB了 创建了三个segment，一个是表，一个是LOBSEGMENT,另外一个是LOBINDEX 这个看上去好像莫名其妙的LOBINDEX就是主要为我们的LOB read consistency服务的 注意我们在创建表的时候指明了disable storage in row,所以lob存储将采取out-line的方式存储到LOBSEGMENT中。 如果是enable storage in row的话，那么长度小于3960bytes的采用in-line的方式存储，那么这时undo, redo的产生和普通的数据一样。 修改数据的时候old version存储在回滚段中，这样query可以利用undo信息重构block生成其所需的前镜像。 当采用out-line的方式存储的时候，这时读一致性可以说完全是由LOBINDEX+LOGSEGMENT来保证的。 LOBINDEX类似于Ｂ-tree的结构，存储各个LOB entry的LOB ID,LOB ID指向LOBSEGMENT中的实际存储区域。 如果要delete一条数据，删除的操作就是更新一下LOBINDEX,　并不会去将LOBSEGMENT中的内容写入回滚段中，这时候有少量的undo信息产生，但是是因为修改LOGINDEX产生的。 如果是update LOB，并不是去update原来的LOB entry，而是插入一条新的LOB entry,并且对LOB自身不产生undo信息,原来旧的数据仍然存放于LOBSEGMENT中（LOBSEGMENT中会存储相关的SCN信息） 这样query需要读取old version的数据就不是从回滚段中读取old value来重构，而是从LOBSEGMENT中读取原先的LOB entry。 如果delete,update操作很多，oracle会不会一直保存这些old version的数据呢？答案是不会。 这时就靠我们上面在创建LOB时的参数PCTVERSION来控制了。PCTVERSION=10的含义就是在HWM下留有10%的空间用于存放Old version的数据. 如果存放old version的空间多于PCTVERSION，那么就可以被重用。这时如果有query需要重构旧的数据，就会产生ORA-01555错误。 想要避免01555的话一个就是尽量缩短query的时间，另外就是增大PCTVERSION，当然这会消耗更多的空间存放旧数据。 那么最后一个问题是如果辨别ORA-01555是不是发生在LOB上的 一般来说，普通的01555错误会指明发生01555的rollback segment,而LOB的则没有，而是伴随着ORA-22924出现 xfan-tiger1$> oerr ora 22924 22924, 00000, “snapshot too old” //  *Cause:  The version of the LOB value needed for the consistent read was //           already overwritten by another writer. //  *Action: Use a larger version pool. xfan-tiger1$> oerr ora 01555 01555, 00000, “snapshot too old: rollback segment number %s with name \”%s\” too small” // *Cause: rollback records needed by a reader for consistent read are //         overwritten by other writers // *Action: If in Automatic Undo Management mode, increase undo_retention //          setting. Otherwise, use larger rollback segments 参考：http://www.dbafan.com/blog/?p=11

 

 

 

参考网页：

http://note.youdao.com/share/?id ... fff9f582d&type=note

ebs由于上传附件比较多，而且没有失效日期，附件表空间APPS_TS_MEDIA大小接近900GB

与该表空间内FND_LOBS表相关

参考 SR 3-6862189381

Hi

There is only one standard method to purge the table, that is concurrent program "Purge Obsolete Generic File Manager Data".
This program will not purge FND_HELP and other records without EXPIRATION_DATE. It will purge the data with program_name=export and the data with an expiration date.
You can use following SQL statement to query the data which has no expiration date
select program_name,count(*) 
from FND_LOBS 
where expiration_date is NULL 
group by program_name;

An example output would look like:
PROGRAM_NAME COUNT(*)
-------------------------------- ----------
FND_HELP 46439
export 9
FNDATTCH 6

FND_HELP is the data of iHelp and should not be deleted otherwise you will not use the help function.
export is the data generated by "export" from form, this kind of data can be purged freely.
FNDATTCH is attachment. 
There are also other kinds of "program_name" from the result, even "null" program_name, these files are from different modules and products, you may need to check with end users if they can be purged.
Please keep in mind, the FND_LOBS table is used by different Oracle Applications features (such as Export, Attachments, FND Help, etc) and different application product modules utilize FND_LOBS to store a variety of data, both temporary data and active on-going business data. There is no any program or utility to mass purge business records because we don't know which file is useful and which is not.

In summary, to purge the data of FND_LOBS, you need to set the data to have an EXPIRATION_DATE and then run the concurrent program. We can only suggest to keep "FND_HELP" untouched and for other files, you need to check with application users.
If you are not sure what the file is, you can follow Note 1457782.1 to download the file and then check with end-users.
Other methods to delete/rebuild the table are not supported.

Best Regards
Vincent
Oracle Support

-------------------------------------------------------------------------------------------------------------------------------

Hi

Thanks a lot for your explaination and I got to know it.

I have checked that all the attachments that uploaded by users have its colum in FND_LOBS with the value of NULL for both EXPIRATION_DATE and UPLOAD_DATE.
so to purge these part of attachments(confirmed these attachments to be useless in the test environmnet), what I do is like following:
1. Set a real date before for the colum of EXPIRATION_DATE for all that part of attachments( EXPIRATION_DATE is null and UPLOAD_DATE is null).
2. run the request "Purge Obsolete Generic File Manager Data" to purge the data .
3. I guess the size of SYS_LOB0000048967C00004$$ would be reduced after that.

is that all right?

thanks and regards

-------------------------------------------------------------------------------------------------------------------------------

Hi

Step 1 and 2 are correct method to purge the table FND_LOBS.
Regarding the question 3, the size of the segment does not change.
You need to perform additional steps like re-allocate to reduce the size of a segment.
You can refer to Note 303709.1 for details. If you need more assistance regarding the sizing issue, please open a new SR with product "Oracle Applications Technology Stack"-->"Database".

Best Regards
Vincent
Oracle Support

所有附件描述信息记录在FND_LOBS表内，实际附件以二进制形式存在FND_LOBS内blob字段对应的lobsegment。

项目上传的附件内容都没有expiration_date，都为空

第一步：

设置FND_LOBS附件的expiration_date（ 此步的目的在于运行第二步的请求时，他只会删除FND_LOBS里面expiration_date已经过期了的，未过期或者为空的，他是不会删的，而我们这里大部分附件的expiration_date都为空，所以需要先将他们更新为一个过期的日期，这样请求就能把他们都清理掉。）

1. 筛选需要修改的行： 筛选出所有是中文的；筛选出所有upload_date为空的行。

筛选语句如下2条，

select * from FND_LOBS WHERE FILE_NAME <> CONVERT(FILE_NAME, 'US7ASCII', 'UTF8') AND PROGRAM_NAME IS NULL

select * from FND_LOBS WHERE PROGRAM_NAME IS NULL AND UPLOAD_DATE IS NULL

2. 更新：分别用上面的2条筛选语句进行更新。

update fnd_lobs set expiration_date = to_date('2008-10-10 10:10:10','yyyy-MM-dd HH24:mi:ss')  where file_id in 

(select file_id from FND_LOBS WHERE FILE_NAME <> CONVERT(FILE_NAME, 'US7ASCII', 'UTF8') AND PROGRAM_NAME IS NULL)

update fnd_lobs set expiration_date = to_date('2008-10-10 10:10:10','yyyy-MM-dd HH24:mi:ss')  where file_id in 

( select * from FND_LOBS WHERE PROGRAM_NAME IS NULL AND UPLOAD_DATE IS NULL )

第二步

在ebs内运行请求“ Purge Obsolete Generic File Manager Data”

1. 添加请求，参考 216541.1

Assign the "Purge Obsolete Generic File Manager Data" Concurrent 
Program to the Request Group "System Administrator Reports". 

1. Access Oracle Applications as the Sysadmin user. 
2. Select the System Administrator responsibility. 
3. Navigate to the following menus:  
   Security / Responsibility / Request 
4. Place the cursor in the "Group" field of the 
   "Request Groups" form.
5. Select "View" from the menu.
   Select "Find" from the menu.
   Select "System Administrator Reports".
6. Place the cursor in the "Type" column.
   Use "File" from the menu to create a "New" record. 
7. Set the "Type" field to "Program".
8. Select "Purge Obsolete Generic File Manager Data" for the "Name" field.
9. Select "File" from the menu.
   Select "Save and Proceed" from the menu.

Program Parameters:
  
  
Expired: 
Enter "Y" if you want to purge expired data only. Enter "N" if you want the purge to include all data. The default is "Y."
Program Name:
Enter the program name(s) to process. Leave blank to process all programs.
Program Tag:
Enter the program tag(s) to process. Leave blank to process all program tags. Tags are identifiers used by the Generic File Manager for categorization purposes.
  
  

   
   

  
  

2. 运行请求

用管理员sysadmin本地登录ebs

运行后可以查看fnd_lobs表内那些行已经被删除，不过实际的物理大小还是没有变的

(转移表有两个办法，一个是对表，lobsegment，index等进行move，另一个就是使用expdp/impdp。下面讲的是move方法，后来经过测试expdp/impdp要比move方便一点，参考末尾，建议大家使用expdp/impdp)

第三步：将数据文件清理

运行上面的请求后，实际的数据并不会变化，只是FND_LOBS内相关行被清理了，实际的附件还是存在数据库内。

此时可以通过move操作或者export/import操作将那些数据通过转移来清空释放。（export/import方法测试过程中遇到一个问题，有的表的lobsegment类型不是正常的，这里暂时使用move方法。）

参考note： 303709.1

1. 附件所在的表空间内所有类型objects都需要进行move(或者export/import)，包括表，lobsegment，index，分区表等。

他们的move方法不尽相同，move起来不像export/import转移那么方便，需要根据object类型进行操作。

1.1 我们先单独将FND_LOBS转移一下，我们这里它有800G左右，所以先弄他。

support介绍：

alter table <owner>.<table_name> move lob (<lob_column_name>) 

store as <segment_name>;

这里我们需要运行:

alter table applsys.FND_LOBS move lob (FILE_DATA) 

store as SYS_LOB0000048967C00004$$

（通过select * from dba_lobs where table_name =  'FND_LOBS'来确定上面的选项， <lob_column_name>就是COLUMN_NAME列的值， <segment_name>就是SEGMENT_NAME列的值，owner和table_name就是表名和所有者了 ）

上面的命令就是让占用空间最大的那个FND_LOBS表在其表空间内原地move一下，实际的表，lobsegment，index所在的表空间不变。

800G需要半个小时左右。

运行结束后，会发现数据文件的使用量基本只有几十M

1.2 将APPS_TS_MEDIA表空间内所有objects转移到另一个新建表空间内TEST_TS。

下面我们需要将APPS_TS_MEDIA表空间内所有对象都转移到一个新建的表空间TEST_TS内（数据大小需要足够！！），包括FND_LOBS表。

这样 APPS_TS_MEDIA内的数据文件就可以被缩小到几乎为1M，但是 为了BES工作正常， 我们之后必须把那些move出去的全都再move回来，

move到新建的表空间命令如下（只列举一个例子，其他全部相同）：

查询APPS_TS_MEDIA内类型为TABLE的objects：

select * from dba_tables where tablespace_name='APPS_TS_MEDIA';

然后将上面的表都move了

ALTER TABLE user.table_name MOVE TABLESPACETEST_TS

（这条语句执行后，lobsegment以及lobindex会随所属表一同move走。

但是有少数表，他们的table是move了，但他们的 lobsegment和lobindex以及index都没动地方，原因是表用到lobsegment类型不同，我就不多说了，后面我将在针对这些objects再move一遍 ）

上面命令运行后，查看 APPS_TS_MEDIA内剩下的所有objects，应该是lobsegment和lobindex以及index类型的objects没被移走，需要针对这些 lobsegment和lobindex以及index  重新move一次。

先查看这些lobsegment以及index属于哪个表（lobindex会随lobsegment自动变动，不用管），在进行move操作

先move lobsegment：

查询

select * from dba_lobs where segment_name  in (select segment_name from dba_segments where tablespace_name ='APPS_TS_MEDIA' )

运行move

ALTER TABLE applsys.FND_LOBS MOVE TABLESPACE TEST_TS LOB (FILE_DATA) store as  (tablespace TEST_TS );

这条命令会转移表以及他的lobsegment和lobindex，如果一个表对应多个 lobsegment 。LOB后面括号内用逗号隔开。

再move index：

针对剩下的index做move，如下：

查询  select * from dba_segments where segment_type='INDEX'

alter index index_name rebuild tablespace  TEST_TS;

到这里 APPS_TS_MEDIA所有的 objects已经全部转移到 TEST_TS里面了。

2 更改APPS_TS_MEDIA表空间内所有数据文件大小。

select * from DBA_data_files where tablespace_name='APPS_TS_MEDIA'

alter database datafile '/db/oracle/data/a_media01.dbf' resize 5M;

(注意，保留足够的空间)

到这里你基本已经成功了。剩下的只是再按原来的方法move回到 APPS_TS_MEDIA  

第四步：将TEST_TS内所有objects都move回到APPS_TS_MEDIA  

1 move表空间TEST_TS内的表回去

ALTER TABLE  user.table_name  MOVE TABLESPACE  APPS_TS_MEDIA

2 move表空间TEST_TS内剩下的lobsegment回去

ALTER TABLE applsys.FND_LOBS MOVE TABLESPACE  APPS_TS_MEDIA   LOB (FILE_DATA) store as  (tablespace  APPS_TS_MEDIA );

3 move表空间TEST_TS内剩下的index回去

alter index  index_name rebuild tablespace  APPS_TS_MEDIA;

 

-----------------------------------------------华丽丽的分割线----------------------------------------------------

这里介绍下expdp/impdp简单使用，只适用于 转移。

create directory dpdata as '/U08/DEVP/db/expdp';

SELECT * FROM dba_directories

grant read,write on directory dpdata to apps

grant read,write on directory dpdata to sys

grant read,write on directory dpdata to system

expdp system/******@VIS TABLES=USER.TABLE_NAME dumpfile=expdp01.dmp logfile=expdp01.log DIRECTORY=dpdata;

将一个表导出， dpdata命令需要提前在数据库建好

impdp system/******@VIS dumpfile = expdp01 .dmp logfile=impdp01.log directory=dpdata tables= USER.TABLE_NAME  job_name=my_job table_exists_action=REPLACE remap_tablespace= APPS_TS_MEDIA: TEST_TS

将一个表导入， APPS_TS_MEDIA是原来表空间，TEST_TS是要转移过去的表空间， table_exists_action=REPLACE这是重点，表明是替换

impdp system/******@VIS  dumpfile = expdp01 .dmp logfile=impdp01_back.log directory=dpdata tables=  USER.TABLE_NAME  job_name=my_job table_exists_action=REPLACE remap_tablespace= TEST_TS: APPS_TS_MEDIA

从 TEST_TS内导回到 APPS_TS_MEDIA

其实可以不用转移，没有测试

导入只做一步：

impdp system/******@VIS dumpfile = expdp01 .dmp logfile=impdp01.log directory=dpdata tables=  USER.TABLE_NAME  job_name=my_job table_exists_action=REPLACE