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
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.