Oracle Learning Dataguard (v) Creating logic Standby

Unlike a physical repository, a logical standby is not an exact copy of the primary database. The same rowid, the values returned on the logical repository are not the same. Some data types or objects, and the logical repository does not support synchronization. Before you create a logical repository, you first need to confirm which schemas and objects are not supported by sql-apply.

1. Query schema that is not synchronized.

Sql> set pagesize 200sql> SELECT owner from dba_logstdby_skip WHERE statement_opt = ' INTERNAL SCHEMA '; OWNER--------- ----------------------Syssystemoutlnmgmt_viewmdsysordsysexfsysdbsnmpwmsysappqossysapex_ 030200orddatactxsysanonymoussysmanxdbordpluginsowbsyssi_informtn_schemaolapsysoracle_ocmxs$nulldip23 rows Selected.

2. Query which tables are not executed.

Sql> SELECT DISTINCT owner,table_name from dba_logstdby_unsupported ORDER by owner,table_name;no rows selected

3. If the previous step returns data, you can find out which types are not supported by the following query

Sql> SELECT column_name,data_type from dba_logstdby_unsupported WHERE owner= ' OE ' and table_name = ' CUSTOMERS '; no rows S Elected

4. The sql-apply of a logical repository is performed by a unique identifier of the table, which means that the table must have a primary key or a unique index.

Sql> Col owner for A10sql> Col table_name for a30sql> SELECT owner, table_name from Dba_logstdby_not_unique;owner TABLE_NAME----------------------------------------SCOTT Bonusscott Salgradescott T1

What if the table does not have a primary KEY or a unique constraint? Oracle writes supplemental logging in the log file. That is, it writes a large amount of additional information, using all the column values to build the uniqueness of the update.

Of course there are some tables that cannot be constructed unique, and can be queried by the following statement

Select owner, table_name from Dba_logstdby_not_uniquewhere (owner, TABLE_NAME) not in (SELECT DISTINCT OWNER, table_name FR OM dba_logstdby_unsupported) and bad_column = ' Y ';

5. Turn off the log app for the physical standby library

sql> ALTER database RECOVER MANAGED STANDBY database cancel;database altered.

6. Build the necessary data dictionary to create the logical repository.

Go to the main library and execute the following command

Sql> EXECUTE Dbms_logstdby. Build;pl/sql procedure successfully completed.

This command does the following things

• The Supplemental logging feature is turned on on the main library.

• Build the Logminer data dictionary on the main library so that the logical repository knows how to handle the redo data sent from the main library.

• Record an SCN number, which is applied from the Sql-apply log on the library to start with this SCN number.

7. If there is a need for a primary and standby switch, you must first manually open the supplemental logging on the standby database.

sql> ALTER DATABASE ADD supplemental LOG DATA (PRIMARY KEY, UNIQUE INDEX) columns;database altered.

8. Redo before switching from library to logical standby

 sql> alter database recover to logical  STANDBY  "STANDBY"; alter database recover to logical standby  "STANDBY" *error at line  1:ORA-19953: database should not be openSQL> shutdown  Immediatedatabase closed. Database dismounted. Oracle instance shut down. Sql> startup mountoracle instance started. total system global area  839282688 bytesfixed size       2233000 bytesVariable Size    494931288 bytesDatabase  buffers   339738624 bytesredo buffers      2379776  bytesdatabase mounted. sql> alter database recover to logical standby  "STANDBY";D atabase  Altered. 

9. Open the Database

sql> shutdown Immediateora-01507:database not mountedoracle instance shut down. Sql> Startup Mountoracle instance started. Total System Global area 839282688 bytesfixed size 2233000 bytesvariable size 494931288 bytesdatabase buffers 3 39738624 Bytesredo buffers 2379776 bytesdatabase mounted. sql> ALTER DATABASE open Resetlogs;database altered. Sql>

10. The Boot log should

sql> ALTER DATABASE START LOGICAL STANDBY APPLY immediate;database altered.

11. Verifying Data Synchronization

Main Library

Sql> Conn Scott/tigerconnected.sql> Select COUNT (*) from T1; COUNT (*)----------14sql> INSERT INTO T1 SELECT * from t1;14 rows created. Sql> commit; Commit complete.

Querying data from the library

Sql> Conn Scott/tigerconnected.sql> Select COUNT (*) from T1; COUNT (*)----------28

You can create tables, modify data, and so on from a library.

sql> conn / as sysdbaconnected.sql> alter  database stop logical standby apply;database altered. sql> alter session disable guard; Session altered. sql> create table scott.t2 as select * from scott.t1; Table created. sql> alter session enable guard; Session altered. sql> alter database start logical standby apply immediate;database  Altered. 

sql> conn/as sysdbaconnected.sql> ALTER SESSION DISABLE GUARD; Session altered. sql> INSERT INTO SCOTT.T1 select * from scott.t1;28 rows created. Sql> commit; Commit complete. Sql> alter session enable guard; Session altered.

The Dataguard transmission of the table, can also be modified data, this is quite dangerous, the data on both sides is inconsistent.

This article is from the "Ding Dong" blog, please be sure to keep this source http://lqding.blog.51cto.com/9123978/1682704

Oracle Learning Dataguard (v) Creating logic Standby