Oracle DG modify logical Standby data

Compared with physical Standby, logical Standby management is a little more complicated. This is the difference between one half database management and two databases (assuming that the Data Guard environment is one master and one slave). After all, logical Standby is only logical, as if it is consistent with the Primary database, in fact, it is a database system that runs independently and may even be completely different from the Primary database. It is also appropriate to spend more time managing this configuration environment.

Recommended reading:

Configure Standby Redo Log for Data Guard

RMAN duplicate for Standby failure Solution Process

Enable physical Standby in read only/WRITE mode in Data Gurad physical backup mode

RAC primary + Single Standby DG configuration practices

Oracle Standby policy PPT

1. The specified object skips the application.

By default, all operations that can be supported by the logical Standby database are executed on the logical Standby side in the REDO data received from the Primary. If you want to skip some operations on some objects, DBMS_LOGSTDBY.SKIP can be used.

Let's take a look at the syntax of DBMS_LOGSTDBY.SKIP:

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 );

Except stmt, all other parameters are optional and can be understood in the literal sense. For example, if you want to skip the DML operation on the dept table under SCOTT, you can execute the following statements (stop the REDO application before executing the process ):

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 tables in the logical Standby cancel the synchronization and maintenance with Primary and want to resume the synchronization again, no problem. DBMS_LOGSTDBY is a big job, and it also has an UNSKIP expert dedicated to this.

Let's take a look at the syntax of DBMS_LOGSTDBY.UNSKIP:

DBMS_LOGSTDBY.UNSKIP (

Stmt IN VARCHAR2,

Schema_name IN VARCHAR2,

Object_name IN VARCHAR2 );

All three parameters are required. The parameters are defined in the same way as the SKIP process.

The following shows how to restore the synchronization of the tmp1 table.

First, check which objects in the Current Logical Standby status are not synchronized. You can view them in 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

Note that the current SQL application status should be stopped before DBMS_LOGSTDBY.UNSKIP is executed:

SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;

Database altered.

Execute the DBMS_LOGSTDBY.UNSKIP process to restore the previously stopped scott. tmp1 table application:

SQL> execute dbms_logstdby.unskip ('dml', 'Scott ', 'dept ');

PL/SQL procedure successfully completed.

 

3. Add or recreate an object

Although the specified object skipping application is canceled, it is possible that the data of the Primary database has been modified during this period, and the two ends are not synchronized at this time. If the Standby end continues to apply the data, it is very likely that the application will be wrong.

Oracle has foreseen this kind of situation. Another process in the DBMS_LOGSTDBY package is INSTANTIATE_TABLE, which is used to synchronize skipped objects to ensure consistency with the Primary database.

The call Syntax of DBMS_LOGSTDBY.INSTANTIATE_TABLE 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 also need to provide a database chain. Therefore, we first create a database chain to connect 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.

Execute the process of using DBMS_LOGSTDBY.INSTANTIATE_TABLE and re-Synchronize the SCOTT. TMP1 table (do not forget to pause the current SQL application before executing this process ):

SQL> EXEC DBMS_LOGSTDBY.INSTANTIATE_TABLE ('Scott ', 'dept', 'Pre _ TBL_DATA ');

PL/SQL procedure successfully completed.

SQL> SELECT * FROM SCOTT. DEPT;

The object has been rebuilt, and then restart the SQL application:

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

Database altered.

 

1 2 Next Page