Hi all,
As we all know Logical standby is absed on sql apply concept and use log mining to get the sql statement from archive logs .
Generally for an standby environment it is required that both the primary and standby has same file structure so that if datafiles are located in some directory or mount point , the datafiles on the standby should also be placed in same directory or mount.
But many times in real life scenarios such condition is not possible to exist. So different directory structures can exists in primary as well as standby.
To accomodate such situation in PHYSICAL STANDBY DATABASE two new parameters were introduced in oracle 9i db_file_name_convert and log_file_name_convert.
But since Logical standby uses sql apply it does not consider above parameters and it simply fires the sql command to add datafile or tablespace and if it does not find the correct location it gets the stuck and throws the above errors and stops the logical.
Thus db_file_name_convert and log_file_name_convert parameters do not function
on a logical standby and you must perform the following steps to fix the problem:
1. Temporarily bypass the database guard so you can make modifications to
the logical standby database.
SQL> EXECUTE DBMS_LOGSTDBY.GUARD_BYPASS_ON;
2. Execute the create tablespace command using the correct file specification:
3. Renable the database guard:
SQL> EXECUTE DBMS_LOGSTDBY.GUARD_BYPASS_OFF;
4. Prepare to skip the failed DDL statement by finding the XIDUSN, XIDSLT, and
XIDSQN values for the failed DDL statement using the DBA_LOGSTDBY_EVENTS view:
SQL> SELECT XIDUSN, XIDSLT, XIDSQN FROM DBA_LOGSTDBY_EVENTS
2> WHERE EVENT_TIME = (SELECT MAX(EVENT_TIME) FROM DBA_LOGSTDBY_EVENTS);
5. Skip the failed DDL statement by using the DBMS_LOGSTDBY.SKIP_TRANSACTION procedure
with the values found in step number 4:
SQL> EXECUTE DBMS_LOGSTDBY.SKIP_TRANSACTION( /*xidusn*/, /*xidslt*/,
/*xidsqn*/);
6. Start log apply services on the logical standby database:
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY;
Some of the important views which one needs to consider for logical standby is
DBA_LOGSTDBY_EVENTS,DBA_LOGSTDBY_PROGRESS,v$LOGSTDBY
