On your logical standby database, you specified these rules:
SQL> EXECUTE DBMS_LOGSTBY.SKIP (STMT=> ‘DML’, –
SCHEMA_NAME => ‘HR’, –
OBJECT_NAME=> ‘EMP_NEW’);
SQL> EXECUTE DBMS_LOGSTBY.SKIP (STMT=> ‘DML’, –
SCHEMA_NAME => ‘HR’, –
OBJECT_NAME=> ‘EMP_OLD’);
After completion of the weekend batch cycle you attempt to delete the SQL Apply filters:
SQL> EXECUTE DBMS_LOGSTBY.UNSKIP (STMT=> ‘DML’, –
SCHEMA_NAME => ‘HR’, –
OBJECT_NAME=> ‘EMP%’);
Which is true regarding the execution of the UNSKIP procedure?
A.
It succeeds only if SQL apply is stopped before deleting the SQL Apply filter.
B.
It succeeds but the SQL Apply filters are not deleted.
C.
It deletes both the SQL Apply filters.
D.
It returns an error because the syntax to delete a SQL Apply filter must specify the same object names as specified when the filter was added.
E.
It succeeds only if all DML statements executed on the primary have been applied on the logical standby deleting the SQL Apply filter.
Explanation:
The UNSKIP procedure reverses the actions of the SKIP procedure by finding the record, matching all the parameters, and removing the record from the system
table. The match must be exact, and multiple skip actions can be undone only by a matching number of unskip actions. You cannot undo multiple skip actions using
wildcard characters.https://docs.oracle.com/cd/B12037_01/appdev.101/b10802/d_lsbydb.htm#997823
just one correct answer!
D
tested it…
SQL> alter database stop logical standby apply;
Database altered.
SQL> EXECUTE DBMS_LOGSTDBY.SKIP (STMT=> ‘DML’, SCHEMA_NAME => ‘HR’, OBJECT_NAME=> ‘EMP_NEW’);
EXECUTE DBMS_LOGSTDBY.SKIP (STMT=> ‘DML’, SCHEMA_NAME => ‘HR’, OBJECT_NAME=> ‘EMP_OLD’);
PL/SQL procedure successfully completed.
SQL> alter database start logical standby apply;
then I did the unskip
SQL> EXECUTE DBMS_LOGSTDBY.UNSKIP (STMT=> ‘DML’, SCHEMA_NAME => ‘HR’, OBJECT_NAME=> ‘EMP%’);
BEGIN DBMS_LOGSTDBY.UNSKIP (STMT=> ‘DML’, SCHEMA_NAME => ‘HR’, OBJECT_NAME=> ‘EMP%’); END;
*
ERROR at line 1:
ORA-16103: Logical Standby apply must be stopped to allow this operation
ORA-06512: at “SYS.DBMS_LOGSTDBY”, line 418
ORA-06512: at line 1
A is correct!
SQL> alter database stop logical standby apply;
Database altered.
SQL> EXECUTE DBMS_LOGSTDBY.UNSKIP (STMT=> ‘DML’, SCHEMA_NAME => ‘HR’, OBJECT_NAME=> ‘EMP%’);
PL/SQL procedure successfully completed.
B
A, B
And afterwards, checked wether the skips still exists, which is confirmed
SQL> select name, statement_opt from DBA_LOGSTDBY_SKIP where name like ‘EMP%’;
NAME
—————————————————————–
STATEMENT_OPT
——————————
EMP_OLD
DML
EMP_NEW
DML
Thanks for your testing! 🙂
So if I have to choose a single answer, I choose the answer “B”, because the truth, they do not eliminate anything. And the question does not explain how “logical standby applies”. Right?
B
Correct Ans: B
UNSKIP will take the wildcard character % as another character and there is no matching SKIP procedure matches ‘EMP%’ so when you execute the statement to delete SQL Apply filters, statement will succeed but since there is no matching ‘EMP%’ found so it will not delete the two filters defined using SKIP procedure.
I have tested this as well.