ORACLE10G 物理standby轉為邏輯standby

SQL> SELECT * FROM DBA_LOGSTDBY_UNSUPPORTED;

OWNER      TABLE_NAME           COLUMN_NAME          DATA_TYPE            ATTRIBUTES
---------- -------------------- -------------------- -------------------- --------------------
SH         MVIEW$_EXCEPTIONS    BAD_ROWID            ROWID
OE         CUSTOMERS            CUST_ADDRESS         CUST_ADDRESS_TYP
OE         CUSTOMERS            PHONE_NUMBERS        PHONE_LIST_TYP
OE         CUSTOMERS            CUST_GEO_LOCATION    SDO_GEOMETRY
OE         WAREHOUSES           WAREHOUSE_SPEC       XMLTYPE
OE         WAREHOUSES           WH_GEO_LOCATION      SDO_GEOMETRY
PM         ONLINE_MEDIA         PRODUCT_AUDIO        ORDAUDIO
PM         ONLINE_MEDIA         PRODUCT_TESTIMONIALS ORDDOC
PM         ONLINE_MEDIA         PRODUCT_PHOTO        ORDIMAGE
PM         ONLINE_MEDIA         PRODUCT_PHOTO_SIGNATURE ORDIMAGESIGNATURE
PM         ONLINE_MEDIA         PRODUCT_THUMBNAIL    ORDIMAGE
PM         ONLINE_MEDIA         PRODUCT_VIDEO        ORDVIDEO
PM         PRINT_MEDIA          AD_TEXTDOCS_NTAB     TEXTDOC_TAB
PM         PRINT_MEDIA          AD_GRAPHIC           BFILE
PM         PRINT_MEDIA          AD_HEADER            ADHEADER_TYP
OE         CATEGORIES_TAB       CATEGORY_NAME        VARCHAR2             Object Table
OE         CATEGORIES_TAB       CATEGORY_DESCRIPTION VARCHAR2             Object Table
OE         CATEGORIES_TAB       CATEGORY_ID          NUMBER               Object Table
OE         CATEGORIES_TAB       PARENT_CATEGORY_ID   NUMBER               Object Table

19 rows selected.

2)維護邏輯standby與primary的資料庫同步是通過sql應用實現，SQL應用轉換的SQL語句在執行時，對於insert還好說，對於update,delete操作則必須能夠唯一定位到資料庫待更新的那條記錄，問題就在這裡，如果primary庫中表設定不當，可能就無法確認唯一條件，邏輯standby跟物理standby的區別，就是因為它只是邏輯上與primary資料庫相同，物理上可能與primary資料庫存在相當大差異，一定要認識到，邏輯standby的物理結構與primary是不相同的(即使初始邏輯standby是通過primary的備份建立)，因此想通過rowid更新顯然是不好使的，就不能再將其做為唯一條件，可以通過以下的方法來解決：
如何確保primary庫中各表的行可被唯一標識
Oracle 通過主鍵、唯一索引/約束補充日誌(supplemental logging)來確定待更新邏輯standby庫中的行，當資料庫啟用了補充日誌(supplemental logging)每一條update語句寫redo的時候會附加列值唯一資訊，比如：
如果表定義了主鍵，則主鍵值會隨同被更新列一起做為update語句的一部分，以便執行時區別哪些列應該被更新；
如果沒有主鍵，則非空的唯一索引/約束會隨同被更新列做為update語句的一部分，以便執行時區分哪些列應該被更新，如果該表有多個唯一索引/約束，則oracle自動選擇最短的那個；
如果表即無主鍵，也沒有定義唯一索引/約束，所有可定長度的列連同被更新列作為update語句的一部分，更明確的話可定長度的列是指那些除:long,lob,long raw,object type,collection型別外的列；
確定在主資料庫上，補充日誌是否被啟用，可以查詢v$database，如下：SQL> select SUPPLEMENTAL_LOG_DATA_FK,SUPPLEMENTAL_LOG_DATA_ALL,SUPPLEMENTAL_LOG_DATA_MIN from v$database; 
SUP SUP SUPPLEME
--- --- --------
NO  NO  NO

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;       
Database altered.
SQL> select SUPPLEMENTAL_LOG_DATA_FK,SUPPLEMENTAL_LOG_DATA_ALL,SUPPLEMENTAL_LOG_DATA_MIN from v$database;

SUP SUP SUPPLEME
--- --- --------
NO  NO  YES

批註：SUPPLEMENTAL_LOG_DATA_MIN=YES和IMPLICIT都表示資料庫啟動了最小化的補充日誌。YES表示通過ALTER DATABASE ADD SUPPLEMENTAL LOG DATA語句啟動的最小化補充日誌;如果啟動了對主鍵、唯一鍵、外來鍵、ALL的補充日誌，預設情況下都會開啟最小化的補充日誌，IMPLICIT表示的是通過啟動對主鍵、唯一鍵、外來鍵或者ALL的支援而開啟的最小化補充日誌。
因此，Oracle 建議你為表建立一個主鍵或非空的唯一索引/約束，以儘可能確保sql應用能夠有效應用redo資料更新邏輯standby資料庫。
3)執行下列語句檢查sql應用能否唯一識別表列，找出不被支援的表：
SQL> SELECT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_NOT_UNIQUE
  2  WHERE (OWNER, TABLE_NAME) NOT IN
  3  (SELECT DISTINCT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED)
  4  AND BAD_COLUMN = 'Y';

OWNER                          TABLE_NAME
------------------------------ ------------------------------
TSMSYS                         SRS$      --這是一張系統表，首先檢視該使用者是否可以登入，如果是鎖定狀態，我們就無需設定它

SQL> select username,account_status from dba_users where username like '%TSMSYS%';

USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
TSMSYS                         EXPIRED & LOCKED
批註：DBA_LOGSTDBY_NOT_UNIQUE 該檢視顯示包含不被支援的資料型別，即既沒有主鍵，也沒有唯一索引的表，如果表中的列包括足夠多的資訊通常也可支援在邏輯standby的更新，不被支援的表通常是由於列的定義包含了不支援的資料型別。
對於BAD_COLUMN 列值有兩個：
Y：表示該表中有采用大資料型別的欄位，比如LONG，CLOB，如果表中除log列某些行記錄完全匹配，則該表無法成功應用於邏輯standby，standby會嘗試維護這些表，不過你必須保證應用不允許；
Ｎ：表示該表擁有足夠的資訊，能夠支援在邏輯standby的更新，不過仍然建議你為該表建立一個主鍵或者唯一索引/約束以提高log應用效率；

4)假設某張表你可以確認資料是唯一的，但是因為效率方面的考慮，不想為其建立主鍵或唯一約束，怎麼辦呢，沒關係，oracle想到了這一點，你可以建立一個disable的primary-key rely約束：
關於primary-key RELY約束：
如果你能夠確認表中的行是唯一的，那麼可以為該表建立rely的主鍵，RELY約束並不會造成系統維護主鍵的開銷，如果你對一個表建立了rely約束，系統則會假定該表中的行是唯一，這樣能夠提供sql應用時的效能，但是需要注意，由於rely的主鍵約束只是假定唯一，如果實際並不唯一的話，有可能會造成錯誤的更新。
建立rely的主鍵約束非常簡單，只要在標準的建立語句後加上RELY DISABLE即可，示例如下：
SQL> ALTER TABLE mytab ADD PRIMARY KEY (id, name) RELY DISABLE;    --表mytab為我要建立rely主鍵約束的表名

2.建立邏輯standby
為了方便區分當前操作的資料庫，設定一下操作符，如下：
SQL> set sqlprompt primary>           --表示primary資料庫
SQL> set sqlprompt lgstandby>         --表示standby資料庫
1)在物理standby上取消redo apply ：
lgstandby>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
批註：如果決定將其轉換為邏輯standby,就必須停止該物理standby的redo應用，以避擴音前應用含LogMiner 字典的redo 資料，造成轉換為邏輯standby 後，sql 應用時logMiner 字典資料不足而影響到邏輯standby 與primary 的正常同步。
2)設定primary資料庫
[oracle@xiaoru app]$ mkdir my_arch2
[oracle@xiaoru app]$ ls
admin  flash_recovery_area  my_arch  my_arch2  oracle  oradata  oraInventory
primary>alter system set LOG_ARCHIVE_DEST_3='LOCATION=/u01/app/my_arch2/ VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE)
  2  DB_UNIQUE_NAME=orcl' LOG_ARCHIVE_DEST_STATE_3=ENABLE SCOPE=BOTH ;

System altered.

primary>show parameter LOG_ARCHIVE_DEST_3;

NAME                  TYPE        VALUE
--------------------- ----------- ------------------------------
log_archive_dest_3     string      LOCATION=/u01/app/my_arch2/ VALID_FOR=(STANDBY_LOGFILES,STANLID_FOR=(
                                   STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=orcl

執行如下語句，生成LogMiner字典資訊：
primary>EXECUTE DBMS_LOGSTDBY.BUILD;

PL/SQL procedure successfully completed.

批註：該過程專門用於生成記錄的後設資料資訊到redo log，這樣改動才會被傳輸到邏輯standby，然後才會被邏輯standby 進行SQL 應用。該過程通過閃回查詢的方式來獲取資料字典的一致性，因此oracle初始化引數UNDO_RETENTION值需要設定的足夠大。
3)轉換物理standby為邏輯standby
lgstandby>show parameter db_name;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      myorcl

lgstandby>ALTER DATABASE RECOVER TO LOGICAL STANDBY lg_myorcl;
ALTER DATABASE RECOVER TO LOGICAL STANDBY lg_myorcl
*
ERROR at line 1:
ORA-02084: database name is missing a component
ORACLE10G官網檢視錯誤分析，截圖如下：

Database altered.

批註：邏輯standby是一個全新的資料庫，如果當前使用spfile,則資料庫會自動修改其中的相關資訊，如果使用的是pfile,在下次執行shutdown的時候oracle會提示你去修改db_name初始化引數的值。
4)重啟standby資料庫
lgstandby>shutdown immediate
lgstandby>startup mount;
lgstandby>show parameter db_name;        --檢視db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      LGMYORCL

lgstandby>select name,database_role,open_mode from v$database;

NAME      DATABASE_ROLE    OPEN_MODE
--------- ---------------- ----------
LGMYORCL  LOGICAL STANDBY  MOUNTED
5)開啟邏輯standby
由於邏輯standby與primary資料庫事務並不一致，因此第一次開啟時必須指定resetlogs選擇，如下：
lgstandby>alter database open resetlogs;

Database altered.

然後執行如下SQL語句開啟redo應用：
lgstandby>alter database start logical standby apply immediate;

Database altered.

3.驗證
1)首先查詢主、備庫中scott使用者下的表，如下：
primary>conn scott/tiger;
Connected.
primary>select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
DEPT                           TABLE
EMP                            TABLE
BONUS                          TABLE
SALGRADE                       TABLE

lgstandby>conn scott/tiger;
Connected.
lgstandby>select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
DEPT                           TABLE
EMP                            TABLE
BONUS                          TABLE
SALGRADE                       TABLE

2)主庫中建立表mydept，併為其新增主鍵約束，如下
primary>create table mydept as select * from dept;

Table created.

primary>alter table mydept add constraints pk_d primary key (deptno);

Table altered.

primary>select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
DEPT                           TABLE
EMP                            TABLE
BONUS                          TABLE
SALGRADE                       TABLE
MYDEPT                         TABLE

primary>select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE from user_constraints where table_name='MYDEPT';

TABLE_NAME                     CONSTRAINT_NAME                C
------------------------------ ------------------------------ -
MYDEPT                         PK_D                           P
primary>conn / as sysdba
Connected.
primary>alter system switch logfile;   --切換日誌

System altered.

3)備庫檢視主庫在scott使用者下建立的mydept表是否同步
lgstandby>select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
DEPT                           TABLE
EMP                            TABLE
BONUS                          TABLE
SALGRADE                       TABLE
MYDEPT                         TABLE

lgstandby>select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE from user_constraints where table_name='MYDEPT';

TABLE_NAME                     CONSTRAINT_NAME                C
------------------------------ ------------------------------ -
MYDEPT                         PK_D                           P

4)主庫上對錶mydept執行DML語句，示例如下：
primary>conn scott/tiger
Connected.
primary>delete mydept where deptno=10;

1 row deleted.

primary>select * from mydept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        50 ORACLE         JAVA
        60 dayong         xiaoru
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

primary>commit;

Commit complete.

primary>conn / as sysdba;
Connected.
primary>alter system switch logfile;

System altered.

5)備庫查詢資料是否同步
lgstandby>show user;
USER is "SCOTT"
lgstandby>select * from mydept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        50 ORACLE         JAVA
        60 dayong         xiaoru
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

批註：由查詢結果可知，備庫已經成功同步，至此，物理standby轉為邏輯standby已經完成