Adding or Re-Creating Tables on a Logical Standby Database

Typically, you use table instantiation to re-create a table after an unrecoverable operation. You can also use the procedure to enable SQL apply operations on a table that was formerly skipped.

Before you can create a table, it must meet the requirements described in Section 4.1.4 and Section 4.1.5 that explain:

• How to determine if the primary database contains datatypes or tables that are not supported by a logical standby database
• How to ensure that table rows in the primary database can be uniquely identified

  Note: The DBMS_LOGSTDBY PL/SQL package does not support the BLOB datatype, even though BLOB datatypes are supported by logical standby databases.

The following list and Example 9-3 show how to re-create a table and resume SQL apply operations on that table:

1. Stop log apply services to stop applying SQL statements to the database.
2. Ensure no operations are being skipped by querying the DBA_LOGSTDBY_SKIP view.

   If any operations are being skipped, resume application of each operation that is currently being skipped by using the DBMS_LOGSTDBY.UNSKIP procedure. If multiple filters were created on the table, you will need to execute the procedure multiple times.

3. Re-create the table in the logical standby database using the DBMS_LOGSTDBY.INSTANTIATE_TABLE procedure. In addition to creating a table, this procedure also imports the data from the primary table using a database link. The link supplied to this procedure must have LOGSTDBY_ADMINISTRATOR role granted on the primary database.
4. Resume log apply services.

Before accessing data in the newly added table, you should archive the current redo log on the primary database, and ensure that it is applied to the logical standby database.

Example 9-3 demonstrates how to add the EMP table to a logical standby database.

Example 9-3 Adding a Table to a Logical Standby Database

SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
SQL> SELECT * FROM DBA_LOGSTDBY_SKIP;

ERROR  STATEMENT_OPT         OWNER          NAME            PROC
---------------------------------------------------------------------
N      SCHEMA_DDL            SCOTT          EMP
N      DML                   SCOTT          EMP

SQL> EXECUTE DBMS_LOGSTDBY.UNSKIP('SCHEMA_DDL','SCOTT','EMP');
SQL> EXECUTE DBMS_LOGSTDBY.INSTANTIATE_TABLE('SCOTT','EMP','DBLINK');
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY;
SQL> EXECUTE DBMS_LOGSTDBY.UNSKIP('DML','SCOTT','EMP');

Log on to the primary database and issue the following statements:

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
SQL> SELECT FIRST_CHANGE# FROM V$LOG WHERE STATUS = 'CURRENT';
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

When the value returned by the DBA_LOGSTDBY_PROGRESS.APPLIED_SCN 
procedure is greater than the value selected from the query of the V$LOG view, the 
database is consistent and you can safely run reports again.