We are now going to create some skip rules on the logical standby database in order to skip replication of DDL or DML operations on some tables. Then we'll see how to query the existing skip rules and finally the method for disabling the rules.
SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
skip
rule to skip changes caused by DML statements on the EMP
table of the SCOTT
schema. Execute the following statement on the logical standby database:SQL> EXECUTE DBMS_LOGSTDBY.SKIP(STMT => 'DML', SCHEMA_NAME => 'SCOTT', OBJECT_NAME => 'EMP'), PL/SQL procedure successfully completed.
skip
rule:SQL> EXECUTE DBMS_LOGSTDBY.SKIP(STMT => 'SCHEMA_DDL', SCHEMA_NAME => 'SCOTT', OBJECT_NAME => 'EMP'),
SQL> EXECUTE DBMS_LOGSTDBY.SKIP(STMT => 'DML', SCHEMA_NAME => 'HR', OBJECT_NAME => '%'),
CREATE/DROP DIRECTORY
commands will not be executed by SQL Apply:SQL> EXECUTE DBMS_LOGSTDBY.SKIP(STMT => 'DIRECTORY'),
/u01/app/oracle/datafile/ORCL
, we want the logical standby database to create the datafile under /datafile/ORCL
. We can use the DBMS_LOGSTDBY.SKIP
procedure with the PROC_NAME
parameter for this goal. Let's create a rule for this purpose. First we'll create a procedure to replace datafile names. Run the following create procedure statement on the logical standby with sys
user:SQL> create or replace procedure sys.change_ts_ddl ( 2 old_stmt in varchar2 3 , stmt_typ in varchar2 4 , schema in varchar2 5 , name in varchar2 6 , xidusn in number 7 , xidslt in number 8 , xidsqn in number 9 , action out number 10 , new_stmt out varchar2 11 ) as 12 begin 13 new_stmt := replace(old_stmt, '/u01/app/oracle2/datafile/ORCL','/datafile/ORCL'), 14 action := dbms_logstdby.skip_action_replace; 15 16 exception 17 when others then 18 action := dbms_logstdby.skip_action_error; 19 new_stmt := null; 20 21 end change_ts_ddl; 22 /
SQL> EXECUTE DBMS_LOGSTDBY.SKIP(STMT => 'TABLESPACE', PROC_NAME => 'SYS.CHANGE_TS_DDL'), PL/SQL procedure successfully completed.
/u01/app/oracle2/datafile/ORCL
value to /datafile/ORCL
. Now let's add a datafile on the primary database as follows:SQL> ALTER TABLESPACE SYSTEM ADD DATAFILE '/U01/APP/ORACLE/DATAFILE/ORCL/SYSTEM02.DBF' SIZE 1G; Tablespace altered.
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE; Database altered.
Completed: alter tablespace system add datafile '/datafile/ORCL/system02.dbf' size 1G
krvxerpt: Errors detected in process 42, role Apply Slave. dglspc: unhandled failure calling user procedure 604 ... PLS-00306: wrong number or types of arguments in call to 'CHANGE_TS_DDL' ORA-06550: line 1, column 443: PL/SQL: Statement ignored ORA-06550: line , column : LOGSTDBY Analyzer process AS00 server id=0 pid=41 OS id=13178 stopped LOGSTDBY Apply process AS03 server id=3 pid=44 OS id=13184 stopped LOGSTDBY Apply process AS04 server id=4 pid=45 OS id=13186 stopped LOGSTDBY Apply process AS02 server id=2 pid=43 OS id=13182 stopped LOGSTDBY Apply process AS05 server id=5 pid=46 OS id=13188 stopped LOGMINER: session#=1, reader MS00 pid=37 OS id=13172 sid=145 stopped LOGMINER: session#=1, preparer MS02 pid=40 OS id=13176 sid=178 stopped LOGMINER: session#=1, builder MS01 pid=38 OS id=13174 sid=156 stopped
DBA_LOGSTDBY_SKIP
view to gather this information. Run the following query on the logical standby database:SQL> SELECT OWNER, NAME,STATEMENT_OPT, PROC FROM DBA_LOGSTDBY_SKIP WHERE STATEMENT_OPT <> 'INTERNAL SCHEMA'; OWNER NAME STATEMENT_OPT PROC -------- ------------------ --------------- ------------------ DIRECTORY SCOTT EMP DML SCOTT EMP SCHEMA_DDL HR % DML TABLESPACE SYS.CHANGE_TS_DDL
We can see all the rules we created in this output. The first rule disables running the directory DDL commands on the logical standby database. The DML and DDL statements on the EMP
table of the SCOTT
schema will be skipped by SQL Apply. Also all the tables of the HR
schema are out of replication scope in terms of DML operations. At the last line of the output, we can see the rule we created, which defines a procedure for the DDL operations on the logical standby database. The SYS.CHANGE_TS_DDL
procedure will be executed prior to the replicated tablespace DDL commands on the logical standby databse. This procedure will change the directory of the datafiles.
DBMS_LOGSTDBY.UNSKIP
procedure to remove the skip rule for that table or schema. However, prior to this we need the current state of the table and its data on the logical standby database to start the replication again. For this purpose we will use the DBMS_LOGSTDBY.INSTANTIATE_TABLE
procedure. This procedure will drop and recreate the table if it still exists on the logical standby database. The current data will be imported but associated indexes and constraints will not be replicated. First, we stop SQL Apply as follows:SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
SELECT_CATALOG_ROLE
procedure. Let's create this database link on the logical standby database as follows:SQL> CREATE PUBLIC DATABASE LINK INSTANTIATE_TABLE_LINK CONNECT TO SYSTEM IDENTIFIED BY ORACLE USING 'TURKEY'; Database link created.
INSTANTIATE_TABLE
procedure as follows:SQL> EXECUTE DBMS_LOGSTDBY.INSTANTIATE_TABLE (SCHEMA_NAME => 'SCOTT', TABLE_NAME => 'EMP', DBLINK => 'INSTANTIATE_TABLE_LINK'), PL/SQL procedure successfully completed.
This procedure uses Data Pump on the background. It locks the table on the primary for a moment and records that SCN. Then the drop
table, create
table and export
/import
operations are performed. After the procedure is completed, logical standby uses the SCN value for consistent replication of the table. You'll see the following lines in the alert log of the logical standby database, which indicates the use of Data Pump import:
DM00 started with pid=36, OS id=12415, job SYS.SYS_IMPORT_TABLE_01 DW00 started with pid=37, OS id=12426, wid=1, job SYS.SYS_IMPORT_TABLE_01
skip
rules of SCOTT.EMP
table from the logical standby database using DBMS_LOGSTDBY.UNSKIP
as follows:SQL> EXECUTE DBMS_LOGSTDBY.UNSKIP(STMT => 'DML', SCHEMA_NAME => 'SCOTT', OBJECT_NAME => 'EMP'), PL/SQL procedure successfully completed. SQL> EXECUTE DBMS_LOGSTDBY.UNSKIP(STMT => 'SCHEMA_DDL', SCHEMA_NAME => 'SCOTT', OBJECT_NAME => 'EMP'), PL/SQL procedure successfully completed.
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
Now you know how to disable replication for a table or schema in a logical standby database configuration. You have learned how to use the DBMS_LOGSTDBY.SKIP
procedure for this purpose. We also mentioned how to specify a procedure to run before DDL statements with an example of automatically changing the datafile directory structures for the tablespace DDL commands on the logical standby database. Then we saw how to query and disable the skip rules. The DBMS_LOGSTDBY.INSTANTIATE_TABLE
procedure is used to re-build the table on the standby and the DBMS_LOGSTDBY.UNSKIP
procedure removes the skip rule for the specified table or schema.
In order to control user modification to tables on the logical standby database we will use the Database Guard setting. Database Guard offers the following three options:
SYS
from modifying any table in the logical standby database. This is the default mode of a logical standby database.SYS
.