Shulou(Shulou.com)06/01 Report--

A logical backup, unlike a physical backup, is not an exact copy of the Primary database. For the same rowid, the values returned on the logical repository are not the same. For some data types or objects, the logical repository does not support synchronization. Before creating a logical backup, you first need to identify which schemas and objects are not supported by SQL-APPLY.

1. Query unsynchronized schemas.

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 selected

4. SQL-APPLY of a logical repository is performed by the unique identity of the table, which means that the table must have a primary key or 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 has no primary key or unique constraint? Oracle writes supplemental logging to the log file. That is, a lot of additional information will be written, using all column values to construct uniqueness at update time.

Of course, there are some tables that cannot be constructed uniquely, which 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 FROM DBA_LOGSTDBY_UNSUPPORTED)AND BAD_COLUMN = 'Y';

5. Close the log application of the physical backup

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;Database altered.

6. Construct the data dictionary necessary to create a 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

Enable supplementary logging on the main library.

Build LogMiner's data dictionary on the master database, so that the logical backup database knows how to handle redo data sent by the master database.

Record a scn number and apply SQL-APPLY mode logs from the repository starting with this scn number.

7. If there is a need to switch between active and standby, you must first manually open supplementary logging on the standby database.

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX) COLUMNS;Database altered.

8. redo before switching from application to logical backup

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";Database 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 339738624 bytesRedo Buffers 2379776 bytesDatabase mounted.SQL> alter database open resetlogs;Database altered.SQL>

10. Startup logs should

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;Database altered.

11. Verify data synchronization

master 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.

Query data from the library

SQL> conn scott/tigerConnected.SQL> select count(*) from t1; COUNT(*)---------- 28

From the library can also create tables, modify data and so on.

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 table transmitted by DataGuard can also be modified, which is quite dangerous, and the data on both sides is inconsistent.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.