Oracle Data Guard 11gR2 Administration Beginner's Guide

上QQ阅读APP看书，第一时间看更新

Time for action – working with skip rules on a logical standby database

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.

We need to create skip rules for tables and schemas, but first we need to stop SQL Apply using the following query:
```
SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
```
Then, the following statement will create a 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.
```
If we also want skip DDL statements encountered for this table, the following statement will create another skip rule:
```
SQL> EXECUTE DBMS_LOGSTDBY.SKIP(STMT => 'SCHEMA_DDL', SCHEMA_NAME => 'SCOTT', OBJECT_NAME => 'EMP');
```
The next rule will disable DML replication for a complete schema. Execute the following statement to skip all DML changes to the HR schema:
```
SQL> EXECUTE DBMS_LOGSTDBY.SKIP(STMT => 'DML', SCHEMA_NAME => 'HR', OBJECT_NAME => '%');
```
Tip

The wildcard character in the previous code can also be used in different ways such as TMP_%, which refers to the tables with the prefix TMP_.
The following example is disabling some statements to run on the logical standby database. The CREATE/DROP DIRECTORY commands will not be executed by SQL Apply:
```
SQL> EXECUTE DBMS_LOGSTDBY.SKIP(STMT => 'DIRECTORY');
```

Specify a procedure for DDL statements. Suppose we have different directory structures on primary and logical standby database servers. When we add a new datafile on primary under /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  /

Now create a rule to invoke this procedure before running the replicated tablespace DDL commands on the logical standby database using the following query:
```
SQL> EXECUTE DBMS_LOGSTDBY.SKIP(STMT => 'TABLESPACE', PROC_NAME => 'SYS.CHANGE_TS_DDL');

PL/SQL procedure successfully completed.
```
Create and alter the tablespace commands executed on the primary database. They will now be modified on the logical standby database before being executed. The path of the datafiles in the statements will change from /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.
```

Start SQL Apply on the logical standby as follows:

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

Database altered.

On the alert logfile of the logical standby database, we'll see the following line, which states that the procedure worked as planned:
```
Completed: alter tablespace system add datafile '/datafile/ORCL/system02.dbf' size 1G
```

If something goes wrong and the database cannot execute the procedure, SQL Apply will stop and you'll see the related error outputs on the alert log. For example, if there are missing arguments in the procedure, the following errors will be written into the alert logfile:

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

Now, we query the rules. Let's check what rules we have created, which data will not be replicated, and what procedures were defined for what kind of SQL statements on the logical standby database. We'll use the 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.
Disable a skip rule. We may want to re-enable replication for a table or schema in the logical standby database. In this case we will use 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;
```
We need a database link to connect to the primary database to read and lock the table in the primary database. The link must connect to the primary database with a user who has privileges to read and lock the table, as well as the 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.
```
Then execute the 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
```

Now we must delete the DML and DDL 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.

We're ready to start the SQL Apply again as follows:

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

What just happened?

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.

Database Guard settings for the logical standby database

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:

ALL: This setting will prevent all database users except SYS from modifying any table in the logical standby database. This is the default mode of a logical standby database.
STANDBY: In standby mode, users may modify the database tables, which are out of the replication scope. The tables maintained by SQL Apply are still not modifiable by users except SYS.
NONE: Users are free to modify any tables that they have necessary privileges for. This is the mode of a primary database.

Tip

Note that we can set the Database Guard to ALL in a primary database to keep it read-only for a while without a shutdown.