# 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](https://docs.oracle.com/database/121/ADLOB/adlob_intro.htm#ADLOB45131): > 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: ```sql 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](https://docs.oracle.com/en/database/oracle/oracle-database/19/dblic/Licensing-Information.html#GUID-B6113390-9586-46D7-9008-DCC9EDA45AB4). 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**: ```plaintext 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: ```sql 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: ```sql 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: ```sql 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: ```plaintext 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: ```plaintext 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.: ```sql 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.