How-to re-instantiate tables with LONG column for Oracle logical standby

To re-instantiate tables without LONG column for logical standby, we can use the dbms_logstdby.instantiate_table procedure.

SQL> exec dbms_logstdby.instantiate_table('SIEBEL','S_SRM_REQUEST','MY_DBLINK');

But the procedure didn’t support table with Long column, we can manually re-instantiate table having LONG column for logical standby:

1. Get applied scn on logical standby

SQL> alter database stop logical standby apply;

Database altered.
SQL> SELECT APPLIED_SCN FROM DBA_LOGSTDBY_PROGRESS;

APPLIED_SCN
-----------
5793495324

2. expdp using flashback_scn on primary, and copy dump files to logical standby

expdp TABLES=SIEBEL.S_SRM_REQUEST FLASHBACK_SCN=5793495324

3. impdp to logical standby

SQL> drop table SIEBEL.S_SRM_REQUEST;

Table dropped.

impdp TABLES SIEBEL.S_SRM_REQUEST

--restart logical standby apply
SQL> alter database start logical standby apply immediate;

Database altered.

4.check the data after sometime
–on logical stanby

SQL> alter database stop logical standby apply;

Database altered.

SQL> SELECT APPLIED_SCN FROM DBA_LOGSTDBY_PROGRESS;

APPLIED_SCN
-----------
5909587168

SQL> select count(*) from siebel.S_SRM_REQUEST;

COUNT(*)
----------
12495978

–on primary

SQL> select count(*) from siebel.S_SRM_REQUEST as of scn 5909587168;

COUNT(*)
----------
12495978

These steps are created based on my understanding of Oracle logical standby. I did use it for a few times but I didn’t find an official document to support this. Also I asked Oracle support whether it’s supported. They didn’t say yes, nor no. They said something like “if it works, it works”.

So using it on your own risk.