Oracle Study Notes: Create a logical standby

Similar to physical creation, only the special location of logical standby is indicated here.

The biggest difference between logical standby and physical standby is:Is to extract the SQL application from the redo file to the logical database.,Standby databases are different from primary and physica databases in terms of system structure.HeReal-time open status,You can have your own auxiliary database mode objects.! However, due to SQL restrictions, the logical database and primary database are essentially different: We can think that logical database only provides the foundation.Data redundancy protection, rather than full database application Protection! With this understanding, it is not difficult to understand the many restrictions on creating a logical standby database!

 

Restrictions on creating a logical standby database:

1. Many ORACLE data types are not supported: bfile, encryped columns, rowid, urowid, xmltype, object, varrays, nested table, user-defined type
2. Tables with the storage structure of the compression segment are not supported: segemnt Compression
3. The PLS/SQL package and process for modifying metadata are not supported, such as dbms_java, dbms_alert, and dbms_redefinition ......
4. Many SQL statements are not supported: Alter database, CREATE DATABASE LINK, and so on ......
5. Table data needs to be unique: This involves the following specific detection and solution.

• check whether the data type is supported: Select * from dba_logstdby_unsupported
• check whether the table data is unique: Select * from dba_logstdby_not_unique
• check whether the Supplemental log record is Enabled: selelct * from V $ database. Check the sup and sup columns.
• enable supplemental logging: execute dbms_logstdby.build;
• enable or disable supplemental logging for all data: alter database {Add | drop} Supplemental log data
• enable or disable supplemental logging for all data: Alter database {Add | drop} Supplemental log data (all) columns
• enable or disable supplemental logging all unique column data: Alter database {Add | drop} Supplemental log data ({[primary key] [unique] [foreign key]}) columns
• enable or disable supplemental logging for PLSQL replication: Alter database {Add | drop} Supplemental log data for procedural replication
• for a table that can define a unique constraint, the directive primary key: alter table user_table_name add primary key (column,) rely disable

6. Before creating logical standby, you must initialize the logminer dictionary information:Execute dbms_logstdby.build;The supplemental Logging Function of primary database is automatically enabled during this process!

7.Physical standby can only be converted to logical standby in one way, but not vice versa!

 

Create a logical stanby

1. Create physical standby
2. Run exec dbms_logstdby.build on primary;
3. Run the following command on physica Standby:Alter database recover to logical standbyNew_dbname; -- Note that the new database name must be used. Note that this operation may fail. Observe the alter. log of standby. I encountered a problem where the earlier archive logs were missing.
4. Open Logical standby Database: Use resetlogs open operation