Kevin Kempf's Blog

December 9, 2015

FND_LOBS and Securefiles (Part II)

Filed under: 11g, 12c, R12, R12.2 — kkempf @ 3:03 pm

An Actionable Plan

Last post, I talked about how I was playing with securefiles, fnd_lobs, deduplication and advanced compression.  It took a bit of tweaking, but I think I can safely share my success at reigning in FND_LOBS.  I’m going to outline the process of an online conversion from FND_LOBS defined as a basicfile (the default) to securefiles.

Jumping off a Bridge

Ever see Oracle do a presentation and the first slide is legalese called a Safe Harbor which basically says everything you are about to see and hear is subject to change and no business plans should be made on assumptions or potential future releases?  I feel like I should make a similar warning, let’s call it a bridge jumper.  Remember the old saying, “If all your friends jumped off a bridge, would you do it too?”.  Here’s what I’ll say: This works in DEV on EBS version R12.2, and I’ve done some minimal functional testing.  Don’t do it in PROD until you prove that it doesn’t impact something I forgot about or can’t foresee.  I recommend a regression pass.  Also don’t use products you’re not licensed for, such as advanced compression, even in non-production environments.

References

http://docs.oracle.com/cd/B28359_01/appdev.111/b28393/adlob_smart.htm

http://www.oracle.com/technetwork/articles/sql/11g-securefiles-084075.html

Just the Highlights

You can convert your FND_LOBS table via online redefintion from basicfiles to securefiles.  In doing so, you will define the new lob table as securefile storage, and if you use deduplication and compression clauses, you will probably save a lot of space.  I did, dropping from 150GB to about 50GB.

select a.owner, a.table_name, a.column_name, a.segment_name , b.bytes/1024/1024
    from dba_lobs a, dba_segments b
    where a.segment_name = b.segment_name
    and a.owner = b.owner
    and a.table_name like 'FND_LOBS%'
   order by b.bytes/1024/1024 desc
SQL> /
OWNER                   TABLE_NAME              COLUMN_NAME                     SEGMENT_NAME            B.BYTES/1024/1024
------------------------------ ------------------------------ --------------------------------------------------
APPLSYS                FND_LOBS               FILE_DATA                      SYS_LOB0000034032C00004$$        149115.25

By the Numbers

I really wish WordPress would let me paste code in better, as I could make this a lot easier on the eyes.  Well you get what you get.

1. Increase the size of your APPS_TS_MEDIA tablespace until you can hold at least another table some percentage of the size of your FND_LOBs lob segment.  See my prior post to figure out how to determine this.

2. Create FND_LOBS_NEW (yeah, creative name right?) using the same syntax as your old table.  Note deduplicate, compress high clauses.

create
  table applsys.fnd_lobs_new
  (
    file_id          number
  ,file_name         varchar2( 256)
  ,file_content_type varchar2( 256) not null
  ,file_data blob
  ,upload_date     date
  ,expiration_date date
  ,program_name    varchar2( 32)
  ,program_tag     varchar2( 32)
  ,language        varchar2( 4) default( userenv( 'LANG') )
  ,oracle_charset  varchar2( 30) default( substr( userenv( 'LANGUAGE'),instr( userenv( 'LANGUAGE'),'.') + 1) )
  ,file_format     varchar2( 10) not null
  ,primary key( file_id ) validate
  )
  tablespace apps_ts_media pctfree 10initrans 10maxtrans 255storage
  (
    initial 128k buffer_pool default
  )
  logging nocompress lob
  (
    file_data
  )
  store as securefile
  (
    deduplicate compress high tablespace apps_ts_media chunk 8192storage( initial 128k buffer_pool default) pctversion 0nocache logging
  ) ;

3. Take your new table out of logging and enable parallel dml in an effort to save time:

  • alter table applsys.fnd_lobs_new nologging;
  • alter session force parallel dml parallel 4;
  • set timing on

4. Map the columns to the new table (this ran about 12 hours for me)

declare
  l_col_mapping varchar2(1000);
begin
  l_col_mapping :=
     'file_id file_id, '
   ||'file_name file_name, '
   ||'file_content_type file_content_type, '
   ||'file_data file_data, '
   ||'upload_date upload_date, '
   ||'expiration_date expiration_date, '
   ||'program_name program_name, '
   ||'program_tag program_tag, '
   ||'language language, '
   ||'oracle_charset oracle_charset, '
   ||'file_format file_format'
   ;
   dbms_redefinition.start_redef_table ('APPLSYS', 'FND_LOBS', 'FND_LOBS_NEW', l_col_mapping)
   ;
end;
/

5. Drop the existing index on APPLSYS.FND_LOBS_CTX (intermedia index). At this point, you may experience adverse performance on FND_LOBS for some users, but this index is of the insides of the LOBs as I understand it, so I doubt anyone will notice (your primary key remains available).  If you don’t do this, the next step fails spectacularly

drop index applsys.fnd_lobs_ctx;

6. Begin the redefinition process.  This ran really fast for me.

declare
   l_error_count pls_integer := 0;
begin
   dbms_redefinition.copy_table_dependents('APPLSYS', 'FND_LOBS', 'FND_LOBS_NEW', 1, TRUE, TRUE, TRUE, FALSE, l_error_count)
   ;
   dbms_output.put_line('Errors Occurred := '|| to_char(l_error_count))
   ;
end;
/

7. Finish the redefinition process.  Also very fast.

begin 
    dbms_redefinition.finish_redef_table
       ('ARUP', 'FND_LOBS', 'FND_LOBS_NEW');
end;
/

8. Confirm it worked

select securefile
from dba_lobs
where table_name = 'FND_LOBS'
/
SEC
---
YES 

9. Space check

select a.owner, a.table_name, a.column_name, a.segment_name , b.bytes/1024/1024
    from dba_lobs a, dba_segments b
    where a.segment_name = b.segment_name
    and a.owner = b.owner
    and a.table_name like 'FND_LOBS%'
   order by b.bytes/1024/1024 desc
OWNER                   TABLE_NAME              COLUMN_NAME             SEGMENT_NAME            B.BYTES/1024/1024
------------------------------ ------------------------------ ------------------------------ ------------------------------
APPLSYS                FND_LOBS               FILE_DATA              SYS_LOB0008283556C00004$$            46987

Elapsed: 00:00:11.64

10. Drop the temp table

drop table fnd_lobs_new;

11. Recreate the index APPLSYS.FND_LOBS_CTX from the EBS script.  This ran about 5 hours for me.

cd $FND_TOP/sql
sqlplus apps
SQL> @aflobbld.sql APPLSYS APPS

12. Gather statistics

EXEC FND_STATS.GATHER_TABLE_STATS(OWNNAME => ‘APPLSYS’,TABNAME => ‘FND_LOBS’);

Parting Shot

It occurs to me during cleanup, that if you create your “temporary” FND_LOBS table (in my case, FND_LOBS_NEW) in a new tablespace instead of apps_ts_media, once you’re done you could probably reclaim all the disk allocated for this one-time project. The alternative is that your apps_ts_media is now 3x (or more) what you need, as you eliminated your large, basicfiles FND_LOBS table, and now have a compressed and deduplicated version on securefiles which will presumably take a long, long time to fill up your apps_ts_media tablespace now that you’ve expanded it for the project. Because of high water marks, I was unable to shrink my tablespace in any significant way after I was done.

That’s It Folks

I tested this a few ways from the functional standpoint.  I ran a report which printed a LOB (Invoice) which was part of a requisition.  I opened an attachment in Quality.  I saved an attachment in Quality.  Everything checked out.  On to a full regression cycle!  Thanks for tuning in, let me know if you find this useful.

Advertisements

Create a free website or blog at WordPress.com.