Oracle DG modifies logical standby-side data

Relative to physical standby, the management of logical standby is a little more complicated. This is the difference between managing a half database and managing two of databases (assuming that the data guard environment is a master standby scenario), after all, logical standby just logically, as if it were consistent with the primary database, which is actually a stand-alone operation, It may even be a completely different database system from the primary database, and it should be possible to manage the configuration environment with a little more effort.

1, the specified object skipped application

By default, all operations that can be supported by the logical standby database will be executed on the logical standby side of the redo data received from primary. If you want to skip certain actions on certain objects, Dbms_logstdby. Skip will be useful.

Take a look at Dbms_logstdby first. Skip's syntax:

Dbms_logstdby. SKIP (

stmt in VARCHAR2,

Schema_name in VARCHAR2 DEFAULT NULL,

object_name in VARCHAR2 DEFAULT NULL,

Proc_name in VARCHAR2 DEFAULT NULL,

Use_like in BOOLEAN DEFAULT TRUE,

ESC in CHAR1 DEFAULT NULL);

In addition to stmt, the other is optional parameters, and see the literal meaning of what it refers to. For example, if you want to skip the DML operation of the Dept table under Scott User, you can do this by executing the following statement (you need to stop the redo application before you perform the procedure):

sql> ALTER DATABASE STOP LOGICAL STANDBY APPLY;

Database altered.

Sql> EXEC Dbms_logstdby. SKIP (' DML ', ' SCOTT ', ' DEPT ');

Pl/sql procedure successfully completed.

Sql>alter DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

Database altered.

2, Restore object synchronization

If some of the tables in the logical standby cancel the sync maintenance with the primary, now hope to resume synchronization, no problem, Dbms_logstdby great big, it also has a call Unskip students do this.

Let's take a look at Dbms_logstdby. Syntax for Unskip:

Dbms_logstdby. Unskip (

stmt in VARCHAR2,

Schema_name in VARCHAR2,

object_name in VARCHAR2);

All three items are required, and the definitions of each parameter are the same as the skip process.

The following shows the synchronization of the recovery Tmp1 table.

First, see which objects in the current logical standby are in a different state, and you can view them through the Dba_logstdby_skip view, for example:

Sql> select * from Dba_logstdby_skip;

ERROR statement_opt OWNER NAME U E PROC

----- ------------------------------ ---------- ----- - - ----------

N DML SCOTT DEPT Y

N INTERNAL SCHEMA SYSTEM% Y

N INTERNAL SCHEMA SYS% Y

N INTERNAL SCHEMA Olapsys% Y

N INTERNAL SCHEMA si_informt% Y

N INTERNAL SCHEMA Mgmt_view% Y

N INTERNAL SCHEMA ordplugins% Y

N INTERNAL SCHEMA XDB% Y

N INTERNAL SCHEMA Sysman% Y

N INTERNAL SCHEMA Wmsys% Y

N INTERNAL SCHEMA dbsnmp% Y

Notice the execution of the Dbms_logstdby. To stop the current SQL application state before unskip the procedure:

sql> ALTER DATABASE STOP LOGICAL STANDBY APPLY;

Database altered.

Perform dbms_logstdby. Unskip process to restore the application of the previously stopped SCOTT.TMP1 table:

Sql> Execute Dbms_logstdby.unskip (' DML ', ' SCOTT ', ' dept ');

Pl/sql procedure successfully completed.

3. Adding or rebuilding objects

The specified object skipped application is canceled, but it is possible that during this time due to primary database data modification, both ends are not synchronized at this time, if the standby end of the application is very likely to cause the application of the wrong data.

Oracle also foresaw in this case that there is a process in the Dbms_logstdby package called Instantiate_table, designed to synchronize the skipped objects to maintain consistency with the primary database.

Dbms_logstdby. The instantiate_table invocation syntax is as follows:

Dbms_logstdby. Instantiate_table (

Schema_name in VARCHAR2,

table_name in VARCHAR2,

Dblink in VARCHAR2);

In addition to the schema name and table name, you need to provide a database chain, so here we first create a chain of database connections to the primary database on the logical standby side:

sql> CREATE DATABASE LINK pre_tbl_data CONNECT to SYSTEM identified by ADMIN

USING ' ORCL_PD ';

Database Link created.

Perform the use of Dbms_logstdby. Instantiate_table the procedure, resynchronize the SCOTT.TMP1 table (Note that the current SQL application is paused before executing this procedure):