Skip to Main Content
  • Questions
  • Migration of Basicfile to Securefile

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Cheuk.

Asked: January 07, 2019 - 9:49 pm UTC

Last updated: January 11, 2019 - 6:05 am UTC

Version: 12.2.0.1 SE

Viewed 1000+ times

You Asked

We've a database that was upgraded from 11g standard edition to 12c standard edition. According to Oracle documentation, we should migrate from basicfile storage to securefile. All the recommendations we found so far are for enterprise edition and suggest Online Redefinition for the migration. However, Online Redefinition is disabled in 12c SE. Is there an alternative to perform the migration while the database is online? Also, going forward what's the recommended way to reclaim the lob tablespace in 12c SE? Previously we were using Online Redefinition in 11g for the reclamation.

thanks
-cheuk

and Connor said...

Under the covers, dbms_redefinition is doing something like:

- copy the data over at a point in time
- capture any deltas in the source table whilst this occurs
- as a final step, lock everything, apply the deltas and flick the data dictionary to point at the new table

So you can get reasonably close to this with your own custom solution, eg

SQL> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production                0
PL/SQL Release 12.2.0.1.0 - Production                                                    0
CORE    12.2.0.1.0      Production                                                                0
TNS for 64-bit Windows: Version 12.2.0.1.0 - Production                                   0
NLSRTL Version 12.2.0.1.0 - Production                                                    0

5 rows selected.

SQL>
SQL> create table T1 ( x int, y clob, constraint T1_PK primary key (x) ) lob (y) store as basicfile ;

Table created.

SQL> create sequence T1_SEQ;

Sequence created.

SQL> declare
  2     l_long varchar2(32767) := rpad('x',32000,'x');
  3    begin
  4        insert into t1 select rownum, l_long from dual connect by level <= 100;
  5   end;
  6  /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL>
SQL>
SQL> create table T2 ( x int , y clob ) lob ( y) store as securefile;

Table created.


T1 is my source table, and T2 is how I want the table to end up.

SQL> create sequence delta_seq;

Sequence created.

SQL>
SQL> create table deltas ( d int default delta_seq.nextval, x int, op varchar2(1));

Table created.

SQL>
SQL> create or replace
  2  trigger GRAB_EVERYTHING
  3  after insert or update or delete
  4  on t1
  5  for each row
  6  disable
  7  begin
  8    if inserting then
  9      insert into deltas (x,op) values (:new.x,'I');
 10    end if;
 11
 12    if updating then
 13      if :new.x = :old.x then
 14        insert into deltas (x,op) values (:new.x,'I');
 15      else
 16        insert into deltas (x,op) values (:old.x,'D');
 17        insert into deltas (x,op) values (:new.x,'I');
 18      end if;
 19    end if;
 20
 21    if deleting then
 22      insert into deltas (x,op) values (:old.x,'D');
 23    end if;
 24  end;
 25  /

Trigger created.


This will grab all changes to T1 whilst I'm copying data over - notice it is currently disabled. Now I'll enable the trigger and grab a consistent set of rows to copy as close as possible.

SQL> declare
  2    cursor c is select * from t1;
  3    type row_list is table of c%rowtype index by pls_integer;
  4    r row_list;
  5  begin
  6    lock table t1 in exclusive mode;
  7    open c;
  8    execute immediate 'alter trigger GRAB_EVERYTHING enable';
  9    fetch c bulk collect into r;
 10    close c;
 11
 12    forall i in 1 .. r.count
 13      insert into t2 values (r(i).x, r(i).y );
 14  end;
 15  /

PL/SQL procedure successfully completed.



I locked the table, opened my cursor and the did DDL which released the lock. Now I can fetch from my cursor to get that consistent set of rows (whilst my trigger is capturing changes). I did a single bulk collect but for a real table you would get batches at a time etc.

Now I'll simulate some activity whilst the copy is taking place.

SQL> insert into t1 values (200,'blah');

1 row created.

SQL> delete from t1 where x = 35;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from deltas order by 1;

         D          X O
---------- ---------- -
         1        200 I
         2         35 D

2 rows selected.



Now its time to lock everything, apply the updates, and switch over.

SQL> lock table t1 in exclusive mode;

Table(s) Locked.

SQL> lock table t2 in exclusive mode;

Table(s) Locked.

SQL> begin
  2  for i in ( select * from deltas order by 1 )
  3  loop
  4    if i.op = 'I' then
  5      insert into t2 values (i.x, ( select y from t1 where x = i.x ));
  6    elsif i.op = 'D' then
  7      delete from t2 where x = i.x;
  8    elsif i.op = 'U' then
  9      update t2 set y = ( select y from t1 where x = i.x )
 10      where x = i.x;
 11    end if;
 12  end loop;
 13  end;
 14  /

PL/SQL procedure successfully completed.

SQL>
SQL> rename t1 to t1_gone;

Table renamed.

SQL> rename t2 to t1;

Table renamed.


Obviously there's more to do here in terms of constraints, grants etc etc but you get the idea. There's a small service disruption at the start and end of the process.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Online Redefinition

Cheuk Cheng, January 10, 2019 - 4:42 am UTC

Thank you Connor for the quick reply.

As a followup question, what do you recommend for reclaiming tablespace once we migrate to securefile? Do you have any insights if online redefinition will be re-added to SE in the future? We've been using it since 11g.

Regards,
-c
Connor McDonald
January 11, 2019 - 6:05 am UTC

Not sure what you mean by reclaim space. With appropriate retention settings, lob space will be reclaimed as required.

I don't know if online redef is scheduled for standard edition sorry.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database