Migrating LOBs on Standard Edition with Materialized View Logs

LOBs can be stored as either BasicFiles or as SecureFiles. While both storage types are still supported in 19c, let me start by a quote from 12.1 documentation:

SecureFiles is the default storage mechanism for LOBs starting with Oracle Database 12c, and Oracle strongly recommends SecureFiles for storing and managing LOBs, rather than BasicFiles. BasicFiles will be deprecated in a future release.

Besides the mentioned warning, there are also performance considerations that drive us toward moving to SecureFiles. We won't delve into those - this post will focus on how to migrate from BasicFile to SecureFile. Which is simple with relatively small amount of rows:

ALTER TABLE my_table MOVE LOB (lob_column) STORE AS SECUREFILE;

But.. this can take significant amount of time. In my case, it took 15 hours for 6TB of LOBs. And while this is running, the table is locked - users can't issue DMLs on it.

If we were on Enterprise Edition, we could think of ONLINE move option or even better, dbms_redefinition; but those are specifically Enterprise Edition features according to Licensing Guide.

So, here's an idea on approach somewhat similar to the use of dbms_redefinition which works on Standard Edition. Be warned though, this idea focuses solely on cloning the table data - everything else (explained at the end of this post) is up to the developers/DBAs and not covered by this post.

Preparation

First, we create a new, empty table, with the same columns as our BasicFile source table. We can use dbms_metadata.get_ddl to obtain original DDL and modify it to specify SecureFile instead of BasicFile.

So, thus, suppose that now we have two tables:

• my_basicfile with millions of rows
• my_securefile which is empty for now

You could also specify NOLOGGING on the new table - just don't forget that in most cases you'll also want to change this back to LOGGING and take appropriate backup before letting users use the new table. It should also go without saying that NOLOGGING operations won't propagate to your physical standby.

Anyway, for most of actions described after this point, it's best to execute them while there are no users online (perhaps using restricted session?).

This example requires that the BasicFiles table have a primary key. The same concept might work WITH ROWID clause for CREATE MATERIALIZED VIEW LOG, but this isn't the case in this example.

Initial Clone

Let's start by creating Materialized View and a MV Log:

CREATE MATERIALIZED VIEW LOG ON my_basicfile
    WITH PRIMARY KEY
    INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW my_securefile
	ON PREBUILT TABLE
	REFRESH FAST
	AS SELECT * FROM my_basicfile;

The log is used to track all changed rows (ins/upd/del) on the source table since its creation. We'll need it in the next step for fast refresh. This log must be created before dbms_mview.refresh() is called and before users are allowed to make changes to the my_basicfile.

Note that the MV log only stores the primary keys of changed rows - the actual LOB data is read from the source table during refresh.

The MATERIALIZED VIEW is created on prebuilt table, which is actually empty. So, we need to populate it:

begin
    dbms_mview.refresh(
        list => 'my_securefile',
        method => 'C', 
        atomic_refresh => FALSE);
end;

The method => 'C' indicates complete refresh (copying all rows from the my_basicfile source).

atomic_refresh=>FALSE doesn't seem needed at first glance, but it's there so that the underlying method uses /*+ APPEND */ hint when inserting the data - which makes this complete refresh a bit faster. It also uses TRUNCATE before insert this way, but that doesn't matter since my_securefile is empty at this stage.

This dbms_mview.refresh is still likely to take significant amount of time - but the users can be online while it is running; remember, any changes they make to the underlying my_basicfile is recorded in a materialized view log.

Under the hood, according to my tracing on 19c, following is executed:

LOCK TABLE "LOB_TEST"."MY_SECUREFILE" IN EXCLUSIVE MODE  NOWAIT;
/* MV_REFRESH (DEL) */ truncate table "LOB_TEST"."MY_SECUREFILE" purge snapshot log;
/* MV_REFRESH (INS) */INSERT /*+ BYPASS_RECURSIVE_CHECK APPEND SKIP_UNQ_UNUSABLE_IDX */ INTO "LOB_TEST"."MY_SECUREFILE"("COL_PK","COL_CLOB") SELECT "MY_BASICFILE"."COL_PK","MY_BASICFILE"."COL_CLOB" FROM "LOB_TEST"."MY_BASICFILE" "MY_BASICFILE";

Fast Refresh

After previously mentioned complete refresh finishes (this could be, say, the next evening), we only need to add changes that gathered in materialized view log since we ran the full refresh. Like this:

exec dbms_mview.refresh('my_securefile', method => 'F');

F stands for Fast refresh. This means, that Oracle will use the materialized view log, which is just a table (automatically populated by Oracle as changes occur) named mlog$_my_basicfile - you'll find list of all primary keys that were touched in there.

Under the hood, according to my sql tracing on 19c, it runs:

DELETE FROM "LOB_TEST"."MY_SECUREFILE" SNAP\( WHERE "COL_PK" IN (SELECT * FROM (SELECT MLOG\)."COL_PK" FROM "LOB_TEST"."MLOG\(_MY_BASICFILE" MLOG\) WHERE "SNAPTIME$$" > :1 AND ("DMLTYPE$$" != 'I')) AS OF SNAPSHOT(:B_SCN) );

/* MV_REFRESH (MRG) */ MERGE INTO "LOB_TEST"."MY_SECUREFILE" "SNA\(" USING (SELECT * FROM (SELECT CURRENT\)."COL_PK",CURRENT\(."COL_CLOB" FROM (SELECT "MY_BASICFILE"."COL_PK" "COL_PK","MY_BASICFILE"."COL_CLOB" "COL_CLOB" FROM "LOB_TEST"."MY_BASICFILE" "MY_BASICFILE") CURRENT\), (SELECT MLOG\(."COL_PK" FROM "LOB_TEST"."MLOG\)_MY_BASICFILE" MLOG$ WHERE "SNAPTIME$$" > :1 AND ("DMLTYPE$$" != 'D') GROUP BY MLOG\(."COL_PK") LOG\) WHERE CURRENT\(."COL_PK" = LOG\)."COL_PK") AS OF SNAPSHOT(:B_SCN) )"AV\(" ON ("SNA\)"."COL_PK" = "AV\("."COL_PK") WHEN MATCHED THEN UPDATE  SET "SNA\)"."COL_CLOB" = "AV\("."COL_CLOB" WHEN NOT MATCHED THEN INSERT  (SNA\)."COL_PK",SNA\(."COL_CLOB") VALUES (AV\)."COL_PK",AV$."COL_CLOB");

Cutover & Cleanup

For the final cutover, users should be offline again (restricted session?). Fast Refresh, as explained previously, should finish once again, just to make sure the mview log is all applied to the my_securefile.

So, once we're sure both our tables, my_securefile and my_basicfile have the same contents, we can drop the materialized views, the MV log and finally, also the my_basicfile table. Like so:

DROP MATERIALIZED VIEW my_securefile PRESERVE TABLE;
DROP MATERIALIZED VIEW LOG ON my_basicfile;
DROP TABLE my_basicfile PURGE;

but.. herein lies the problem, what if there were foreign keys referencing my_basicfile? What about permissions? Triggers? All those and more must be manually changed (this could mean altering a huge amount of database objects/tables), so that it points to our new table before we're ready to make the final rename:

ALTER TABLE my_securefile RENAME TO my_basicfile;

Possible Alternative (adding a column)

There is an alternative idea - what if, instead of creating a new table and copying everything over, we'd simply add another column? e.g.:

alter table my_basicfile
    add (col_securelob clob)
    lob (col_securelob) store as securefile;

then, simply update new column to the value of the old. There is no free lunch though - this approach might be much lighter regarding metadata (permissions, foreign keys, etc), but...

You'd need to consider the following challenges:

• Row Lock Contention
• Undo & Redo Generation

Those challenges can be addressed to certain extent by:

• updating in chunks
• setting nologging (temporarily) to the new lob column (e.g. store as securefile (nocache nologging))
• creating a trigger to update the new column when the old one is updated/inserted

A quick and naive comparison of this alternative on a slow demo server with generated demo data proved to be about 7x slower than a MV approach. YMMV, of course.

Conclusion

There are many (other) methods on how to migrate LOBs from basicfile to securefile - a simple blog post such as this one cannot possibly address all of them; This is why I focused on one specific approach which I found specifically interesting. It is by no means "the best" approach - it's simply one of the options.

I've tested this approach on 19c SE and 26ai EE.