Logical Standby Out of SYNC

Logical standby going out of sync is a common scenario.But the time of  “how much out of sync can it be ” depends on the SLA you have.I had a scenario of  logical standby going out of the sync and transactions on a particular table consuming is taking time to get applied.The logical standby was almost 1hrs out of sync, which was too high as per the SLAs. The below are the steps performed to correct it:–

sql> alter database stop logical standby apply;

sql> exec dbms_logstdby.skip(stmnt =>'DML',schema_name =>'SCOTT',table_name =>'EMP');

sql> alter database start logical standby apply immediate;

The logical standby was back in sync and as the table was not used for the reports, it was kept as “skipped” almost for a day.After taking a proper downtime, the below steps were taken:-

On logical standby:-

sql> alter database stop logical standby apply;
sql> exec dbms_logstdby.skip(stmnt =>'DML',schema_name =>'SCOTT',table_name =>'EMP');

Though already skipped before,the above command didn’t give error.Took the export using expdp from the primary database

exdpd directory=dump_pump_dir dumpfile=emp.dmp logfile=expdp_emp.log tables=scott.emp status=5

Imported the dump in logical standby

impdp directory=dump_pump_dir dumpfile=emp.dmp logfile=impdp_emp.log table_exists_action=truncate

Unskipped the skip rule on the table :-

sql> exec dbms_logstdby.unskip(stmnt =>'DML',schema_name =>'SCOTT',table_name =>'EMP');

Started the SQL Apply Process:-

sql> alter database start logical standby apply immediate;

The moment the apply process was started, it returned ORA-1403: No Data Found"(as far as i remember) error for EMP table.

The reason could be  “something related to instantiation of the table” .As we used “table_exists_action=truncate” , the data is truncated, but the metadata remains the same/old.

So immediately stopped the SQL Apply process, created a database link from the logical standby db to the primary and executed the below :-

sql> exec dbms_logstdby.instantiate_table(‘SCOTT’,’EMP’,’DBLINK’);

The above command also does expdp/impdp using the network_link which is the db link.The one major difference (which i suppose) was, value for “table_exists_action” parameter.This time is was “REPLACE”.

The Replace parameter drops the existing table, and then creates and loads it with the data the source using the db link.Now, as i suppose, this time the metadata of the table would also have definitely changed.

Once the table was instantiated, restarted the SQL Apply process :-

sql> alter database start logical standby apply immediate;

Thereafter the logical standby came in SYNC with the primary and till now its running good. 🙂

Though i could have used “dbms_logstdby.instantiate_table” itself in the start and things would have most probably went well, but then i wouldn’t have known the difference and my mistake.Mistakes do make you learn something new.

If using expdp/impdp the below are the correct steps to be followed:-

1. Stop the SQL Apply process

sql> alter database stop logical standby apply;

2.  Put the skip rule for the table

sql> exec dbms_logstdby.skip (stmnt =>'DML',schema_name =>'SCOTT',table_name =>'EMP');

3. Drop and recreate the table on logical standby

sql> drop table scott.emp

sql> create table scott.emp (ename varchar(2),........);

4. On the logical standby database


Note the RESTART_SCN from the above output .

RESTART_SCN:- If SQL Apply stops and restarts for any reason, it will start mining redo records generated on or after that SCN value.

5. On the primary database

expdp system/xxxx tables=scott.emp dumpfile=expemp.dmp flashback_scn=<RESTART_SCN>

6. Import the data dump into the logical standby

impdp system/xxxx dumpfile=expemp.dmp tables=scott.emp table_exists_actions=append

7. Unskip the skip rule

sql> exec dbms_logstdby.unskip(stmnt =>'DML',schema_name =>'SCOTT',table_name =>'EMP');

8. Start the SQL Apply process

sql> alter database start logical standby apply;

It might be that the expdp couldn’t  flashback to the given flashback_scn.Then better create a database link and instantiate the table using “dbms_logstdby.instantiate_table”.

For Reference :- Synchronizing tables in a Logical Standby Database [ID 271455.1]

3 thoughts on “Logical Standby Out of SYNC

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s