Instantiate a Data Guard table Oracle Database Tips by Donald BurlesonDecember 9, 2015

Instantiating a Table

Using the instantiate_table  procedure of the dbms_logstdby package, the DBA can re-build or add a new table in the logical standby database. The DBA will need to re-build a table or refresh its data in the logical standby database, if any unrecoverable statement is issued on the primary database against that table. The unrecoverable transaction will not be recorded in the archive log file, so it will be missing from the standby database.

However, setting the FORCE LOGGING option on the primary database will avoid any unrecoverable operation. Also, the DBA may need to re-build a table when it is desirable for this table to be managed by the SQL Apply operation.  Either add a new table from the primary database or un-skip a previously skipped table.

Prior to executing the instantiate_table  procedure, a dblink between the primary and the standby site must be created. The user specified in the database link should exist on the primary database. Moreover, the role LOGSTDBY_ADMINISTRATOR must be granted to the user used in the database link. The dba_logstdby_skip view contains operations being skipped on a table.

If a previously skipped table is being added to the set of objects managed by the SQL Apply operation, query this view to find the list of operations that are being skipped. All of these operations will have to be set as valid in order to be applied on the table after re-building it. The following steps show how to re-build a table on the standby site:

• Stop the log apply service using:

ALTER DATABASE STOP LOGICAL STANDBY APPLY;

• Recreate the table using:
  
  EXEC DBMS_LOGSTDBY.INSTANTIATE_TABLE('Schema_Name','TableName','DblinkName');

• Un-skip all the operations determined from the dba_logstdby_skip view for this table as explained above. The dbms_logstdby.unskip procedure can be used to UNSKIP a previously set filter.