Example: On Resuming Replication on Skipped Table in logical standby DB

Example : On Resuming Replication on Skipped Table in logical standby DB
Check if table is listed in unsupported list.
SYS (ARS1T3) SQL>select owner,table_name from DBA_LOGSTDBY_UNSUPPORTED where table_name like 'FIELD_ENUM_VALUES';

no rows selected

-- Connect to Primary database and reset and unlock system user if required,If you already knew the password Ignore it as we are going to use DB Link to recreate and populate rows.


All the below steps are in In Logical standby DB
create public database link PROD_DB_SYSTEM connect to SYSTEM identified by <Password> using 'PROD_DB';


-- Make sure its applying
SELECT STATE  FROM  V$LOGSTDBY_STATE;


--Check the LAG time, if any first resolve the LAG.

col Primary format a30
col Standby format a30
SELECT TO_CHAR(latest_time,'yyyy/mm/dd hh24:mi:ss') "Primary",
TO_CHAR(applied_time,'yyyy/mm/dd hh24:mi:ss') "Standby",
round ((latest_time-applied_time)*24*60) "Lag in Minutes"
FROM V$LOGSTDBY_PROGRESS;


-- Check if this table is included in skip list., Here both , DML & DDL is skipped.

SYS (PROD_DB3) SQL>SET LINES 200
select * from DBA_LOGSTDBY_SKIP WHERE NAME LIKE 'FIELD_ENUM_VALUES';SYS (PROD_DB3) SQL>

ERROR                                                             STATEMENT_OPT                  OWNER                          NAME                           U E
----------------------------------------------------------------- ------------------------------ ------------------------------ ------------------------------ - -
PROC
--------------------------------------------------------------------------------------------------
N                                                                 DML                            LOGICAL_USER                        FIELD_ENUM_VALUES              Y


N                                                                 SCHEMA_DDL                     LOGICAL_USER                        FIELD_ENUM_VALUES              Y





-- If their is no LAG then, Stop the logical standby Process

SYS (PROD_DB3) SQL>ALTER DATABASE STOP LOGICAL STANDBY APPLY;

Database altered.

-- Unskip the DDL for this table
SYS (PROD_DB3) SQL>EXECUTE DBMS_LOGSTDBY.UNSKIP('SCHEMA_DDL','LOGICAL_USER','FIELD_ENUM_VALUES');

PL/SQL procedure successfully completed.

SYS (PROD_DB3) SQL>select * from DBA_LOGSTDBY_SKIP WHERE NAME LIKE 'FIELD_ENUM_VALUES';

ERROR                                                             STATEMENT_OPT                  OWNER                          NAME                           U E
----------------------------------------------------------------- ------------------------------ ------------------------------ ------------------------------ - -
PROC
--------------------------------------------------------------------------------------------------
N                                                                 DML                            LOGICAL_USER                        FIELD_ENUM_VALUES              Y

-- Below procedure will , drop recreate the table and import data

SYS (PROD_DB3) SQL> EXECUTE DBMS_LOGSTDBY.INSTANTIATE_TABLE('LOGICAL_USER','FIELD_ENUM_VALUES','PROD_DB_SYSTEM');

PL/SQL procedure successfully completed.

SYS (PROD_DB3) SQL>EXECUTE DBMS_LOGSTDBY.UNSKIP('DML','LOGICAL_USER','FIELD_ENUM_VALUES');

PL/SQL procedure successfully completed.

SYS (PROD_DB3) SQL> ALTER DATABASE START LOGICAL STANDBY APPLY;

Database altered.

SYS (PROD_DB3) SQL>select * from DBA_LOGSTDBY_SKIP WHERE NAME LIKE 'FIELD_ENUM_VALUES';

no rows selected

SELECT STATE  FROM  V$LOGSTDBY_STATE;

STATE
----------------------------------------------------------------
IDLE

press enter to continue


Primary                        Standby                        Lag in Minutes
------------------------------ ------------------------------ --------------
2014/12/10 18:52:10            2014/12/10 18:5:23                         2


After 2 mins

SYS (PROD_DB3) SQL>
SELECT STATE  FROM  V$LOGSTDBY_STATE;SYS (PROD_DB3) SQL>

STATE
----------------------------------------------------------------
APPLYING

SYS (PROD_DB3) SQL>col Primary format a30
col Standby format a30
SELECT TO_CHAR(latest_time,'yyyy/mm/dd hh24:mi:ss') "Primary",
TO_CHAR(applied_time,'yyyy/mm/dd hh24:mi:ss') "Standby",
round ((latest_time-applied_time)*24*60) "Lag in Minutes"
FROM V$LOGSTDBY_PROGRESS;
SYS (PROD_DB3) SQL>SYS (PROD_DB3) SQL>  2    3    4
Primary                        Standby
------------------------------ ------------------------------
                                    Lag in Minutes
--------------------------------------------------
2014/12/10 18:52:10            2014/12/10 18:52:10
                                                 0


SYS (PROD_DB3) SQL>
SYS (PROD_DB3) SQL>set lines 200
SYS (PROD_DB3) SQL>/

Primary                        Standby                                                            Lag in Minutes
------------------------------ ------------------------------ --------------------------------------------------
2014/12/10 18:52:10            2014/12/10 18:52:10                                                             0



Check the number of rows on the source and dest table, It must be matching,..

Note :
====
please use the commands carefully before executing in your server 
its advised to test in test server.

Note : 
====
Implementing or trying any of the advice from this blog is at your own risk.