10g Logical Standby的建立，優化及管理 - 更新

一 . 前期檢查工作  

主庫上操作 ：
SELECT NAME,CREATED,LOG_MODE,OPEN_MODE,DATABASE_ROLE,FORCE_LOGGING FROM V$DATABASE;     確認資料庫處於自動歸檔模式，如果不是，修改為自動歸檔模式 。

執行此句 ALTER DATABASE FORCE LOGGING; 
備註：  ALTER DATABASE NO FORCE LOGGING; 在所有建立Logical Standby工作完成後取消force logging  .

另外，在建立邏輯備庫時確保在主資料庫中做配置使用的賬號有以下資料庫角色許可權：
a、logstdby_administrator 角色， 用來使用邏輯備用功能
b、select_catalog_role 角色， 能夠訪問所有資料字典檢視。
這裡我們選用sys 來進行操作。

我們需要通過查詢主庫中檢視DBA_LOGSTDBY_UNSUPPORTED 來確定主資料庫中是否含有不支援的物件 (如果有，可能需要做一些調整才能繼續) ：
SQL> SELECT * FROM DBA_LOGSTDBY_UNSUPPORTED;
該檢視顯示包含不被邏輯STANDBY支援的資料型別的表的列名及該列的資料型別.

SELECT OWNER,TABLE_NAME FROM DBA_LOGSTDBY_NOT_UNIQUE
WHERE (OWNER,TABLE_NAME) NOT IN (SELECT DISTINCT OWNER,TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED)
AND BAD_COLUMN = 'Y';
該語句在主庫中檢查SQL應用能否唯一識別表列,找出不被支援的表.

確定在主資料庫上，補充日誌是否被啟用，可以查詢v$database，如下：
SQL> select supplemental_log_data_pk,supplemental_log_data_ui from v$database;
SUP SUP
‐‐‐ 
YES YES

如果主庫中存在不符合要求的table, 補充日誌沒有被啟用, 且確實沒有辦法加入PK 或非空唯一索引，那麼在Primary DB上啟用補充日誌：
SQL> alter database add supplemental log data(primary key,unique index)
columns;    

在主庫上建立一個新的表空間，用於LogMiner，否則Logical Standby 需要的物件
將預設建立在SYSTEM 表空間中(這是比較危險的)。( 這一步在ORACLE 文件中似乎沒有提到，這裡是為了不影響system 表空間而引入的 ) 。
SQL> CREATE TABLESPACE logmnrts DATAFILE    
'/data/mxdell/logmnrts01.DBF'  SIZE  2000M  
AUTOEXTEND OFF  
EXTENT MANAGEMENT LOCAL UNIFORM. SIZE 10M ;
SQL> EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE('logmnrts'); 
如果執行報錯，可能需要增大logmnrts 表空間大小 。

SQL>show parameter LOG_ARCHIVE_LOCAL_FIRST;  確認使用預設的本地先歸檔(true)

SQL>alter system set archive_lag_target=600 scope=both;  
設定主庫強制10分鐘自動歸檔一次  (備註：  這裡設定這個引數是因為我們使用歸檔傳輸ARCH方式及最大效能模式，為了使Logical Standby上資料和Primary DB上只相差10分鐘以內； 如果不是此種需要，可以不用設定)  

由於後續的BMS_LOGSTDBY.BUILD這個過程會通過閃回查詢的方式來獲取資料字典的一致性，因此oracle 初始化引數UNDO_RETENTION 值需要設定的足夠大。
Oracle recommends setting the UNDO_RETENTION initialization parameter to 3600 on both the primary and logical standby databases ,Oracle10g 預設undo_retention為900，這裡設定為3600， 同樣Standby上的引數也需要設定為3600。
SQL>alter system set undo_retention=3600 scope=both;  

SELECT *  FROM DBA_LOGSTDBY_SKIP; 
檢視邏輯Standby的過濾操作； 對於一些系統schema，邏輯備庫預設是忽略其實際變更的。邏輯備庫和主庫只是邏輯意義上也就是使用者資料保持一致，後設資料自然是不應該複製的，否則就亂套了，所以不要在系統schema上建立任何使用者自己的資料表等 。 
SQL>  select owner,name from DBA_LOGSTDBY_SKIP; 

OWNER                          NAME
------------------------------ ------------------------------
SYSTEM                         %
SYS                            %
DIP                            %
OUTLN                          %

 

二.   在Standby Server上建立物理STANDBY  

這裡Physical Standby 中DB_NAME及DB_UNIQUE_NAME均為MXDELL 。spfile 是直接
拷貝主庫上的引數檔案，並根據具體記憶體修改 。這裡假設兩臺Server硬體配置一樣 。

熱備份或RMAN建立物理Standby  
Primary DB上引數修改：
*.db_name='mxdell'
*.db_unique_name='mxdell'
*.log_archive_dest_1='location=/data/mxdell/arch valid_for=(all_logfiles,all_roles) db_unique_name=mxdell'
*.log_archive_dest_2='service=standby valid_for=(online_logfiles,primary_role) db_unique_name=mxstandby'
*.log_archive_dest_state_1='enable' 
*.log_archive_dest_state_2='enable' 

備註： 按照Oracle10g 物理Standby標準文件，建立物理Standby過程中主庫中的引數還有一些用於主庫備庫互相切換的引數比如fal_server,fal_client等 ，這裡不做切換打算，暫時不設定。 詳情參考：
http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/create_ps.htm 

Physical Standby上引數修改：  
*.db_name='mxdell'
*.db_unique_name='mxstandby' 
*.log_archive_dest_1='location=/data/mxdell/arch valid_for=(all_logfiles,all_roles) db_unique_name=mxstandby'
*.log_archive_dest_2='service=mxdell valid_for=(online_logfiles,primary_role) db_unique_name=mxdell'    -- 可以不用設定，主要用於主庫備庫切換 
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.standby_archive_dest='/data/mxdell/arch'   -- 接收來自主庫的Log用於應用SQL，最好設定與standby上自己的歸檔路徑log_archive_dest_1不一致 。 
*.log_archive_min_succeed_dest=1 

*.db_file_name_convert='/data/mxdell','/data/mxdell'
*.log_file_name_convert='/u01/product/oradata/mxdell','/u01/product/oradata/mxdell'
*.standby_file_management='AUTO'

*.fal_server='mxdell'
*.fal_client='standby'

 

Primary DB 和Physical Standby 的tnsnames.ora都加入如下兩設定：
其中8.114是主庫IP,130.189是物理備庫IP 
MXDELL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.161.8.114)(PORT = 1526))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mxdell)
    )
  )

STANDBY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.134.130.189)(PORT = 1526))
    (CONNECT_DATA =
      (SERVICE_NAME = mxdell)
      (INSTANCE_NAME = mxdell)       # 可以不需要 
    )
  )

 

Primary DB 和Physical Standby 上listener.ora如下(都一樣)，並開啟兩臺機器的監聽：

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/product/oracle)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = mxdell)
      (ORACLE_HOME = /u01/product/oracle)
      (GLOABAL_DBNAME = mxdell)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = MXPSVDFMSNDB)(PORT = 1526))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )

開啟主庫。
物理備庫上操作：
SQL> startup nomount
SQL> alter database mount standby database ;
SQL> alter database recover managed standby database disconnect from session ;
測試物理Standby的恢復同步情況 (略) 。

 

四 . 準備將物理Standby切換為邏輯STANDBY

在主庫上執行： SQL>EXECUTE DBMS_LOGSTDBY.BUILD;

提示： 
Ø 該過程會自動啟用primary 資料庫的補充日誌(supplemental logging)功能(如果未啟用的話)。
Ø 該過程執行需要等待當前所有事務完成，因此如果當前有較長的事務執行，可能該過程
執行也需要多花一些等待時間。
Ø 該過程是通過閃回查詢的方式來獲取資料字典的一致性，因此oracle 初始化引數
UNDO_RETENTION 值需要設定的足夠大。

切換物理Standby為邏輯Standby :
SQL> alter database recover managed standby database cancel ;
（如果是mananged恢復模式, 需要先cancel）

SQL> alter database recover to logical standby mxweb01;

可能會遇到兩種錯誤：
1. sys密碼不一致導致一直hand住；         ---建立與主庫一致的密碼檔案
2. 備庫監聽沒有包含standby的instance資訊。  ---監聽檔案中新增instance資訊

重啟邏輯備庫
第一次啟動 
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database open resetlogs; （這一步應該可以看到初始歸檔生成）
然後 
SQL> shutdown immediate;
SQL> startup;

Logical  Stadnby上啟動sql apply
alter database start logical standby apply; 

如果要啟動實時應用特性，需要先在備庫新增standby redo logfile，這裡我們不使用 。
alter database add standby logfile '/data/mxdell/redo01s.log' size 50m;
alter database add standby logfile '/data/mxdell/redo02s.log' size 50m;
alter database add standby logfile '/data/mxdell/redo03s.log' size 50m;
alter database add standby logfile '/data/mxdell/redo04s.log' size 50m;
alter database add standby logfile '/data/mxdell/redo05s.log' size 50m;
啟動real time sql apply 
alter database stop logical standby apply;
alter database start logical standby apply immediate;

 

五， 觀察狀態及檢視，調整Logical Standby的Apply效能引數（重要的一步）
參考：
http://www.ningoo.net/html/2008/oracle10gr2_logical_standby_managing_and_tuning.html  

1. 可以先花一點點時間認識logical Standby原理：  
SQL Apply實際上是一組後臺並行程式 ： 
• reader：讀取redo記錄傳遞給preparer程式
• preparer: 根據redo記錄和資料字典資訊生成LCR
• builder: 將同一個事務的LCR打包。對於大事務(eager transaction)，可能被打成多個事務包(transaction chunk)，那麼可能有些包裡是不包含commit的，每一個事務包都可能交給不同的applier程式。
• analyzer:分析事務包之間的依賴關係
• coordinator:將分析好的事務包交給applier程式
• applier:將事務包應用，如果事務包依賴其他事務包，則需要等待相應的事務包完成。事務能否commit，可能還需要根據不同的情況從coordinator獲得相應的資訊。
其中，reader,builder,analyzer和coordinator只能有一個程式，而preparer和applier則可以根據需要開啟多個並行程式，但是系統中所有的並行程式不能超過初始化引數parallel_max_servers的限制：

一般來說，一個preparer程式可以滿足20個applier程式的需要。applier的個數，需要根據系統的情況來看，如果所有的applier程式都比較忙碌，可能要考慮增加。簡單的可以通過transactions ready和transactions applied兩個狀態統計來判斷，如果兩者差值很大，applier程式數可能太少。還可以觀察V$LOGSTDBY_PROCESS檢視，來檢視apply程式的狀態，如果存在status_code的值為16116(表示無事務處理即idle狀態)，則說明apply程式足夠，存在idle程式，不需要增加apply程式數量 。

通過檢視V$LOGSTDBY_PROCESS檢視來檢查PREPARE程式數是否足夠，如果status_code狀態不為16116 （注意： status_code欄位為16116表示無事務處理即idle狀態），而apply有程式存在idle狀態，可能考慮增加prepare_servers數量， 否則暫時不需要增加（預設preparer及appliers 為1和5 , 一般來說preparer預設足夠，大多數時候appliers 需要調整）。

alter database stop logical standby apply; 
execute dbms_logstdby.apply_set('PREPARE_SERVERS', 2);
execute dbms_logstdby.apply_set('APPLY_SERVERS', 8);
alter database start logical standby apply; 

SQL> select name,value from v$logstdby_stats where name like 'transactions%';
NAME                           VALUE
------------------------------ -------------
transactions ready             154100
transactions applied           154058

系統中當前已有的preparer和applier程式也可以通過v$logstdby_stats來查詢：
SQL> select name,value from v$logstdby_stats where name like 'number%';
NAME                           VALUE
------------------------------ --------------
number of preparers            2
number of appliers              8

各個SQL Apply程式的狀態可以通過v$logstdby_process檢視獲得：
SQL> select sid,type,status from v$logstdby_process;

  SID TYPE         STATUS
----- ------------ --------------------------------------------------------------------------------
 1080 COORDINATOR  ORA-16116: no work available
 1085 READER       ORA-16127: stalled waiting for additional transactions to be applied
 1023 BUILDER      ORA-16127: stalled waiting for additional transactions to be applied
 1025 PREPARER     ORA-16127: stalled waiting for additional transactions to be applied
 1083 PREPARER     ORA-16127: stalled waiting for additional transactions to be applied
 1084 ANALYZER     ORA-16120: dependencies being computed for transaction at SCN 0x0582.1294c01c
 1073 APPLIER      ORA-16124: transaction 57 17 2322872 is waiting on another transaction
 1049 APPLIER      ORA-16124: transaction 44 7 4445763 is waiting on another transaction
 1059 APPLIER      ORA-16124: transaction 59 5 2277932 is waiting on another transaction
 1092 APPLIER      ORA-16124: transaction 13 8 12274153 is waiting on another transaction
 1036 APPLIER      ORA-16124: transaction 2 18 24555358 is waiting on another transaction
 1051 APPLIER      ORA-16124: transaction 45 0 4338376 is waiting on another transaction
 1064 APPLIER      ORA-16124: transaction 56 11 2362783 is waiting on another transaction
 1071 APPLIER      ORA-16113: applying change to table or sequence "NINGOO"."TEST"
 
從這裡可以看到，由於存在一個大事務，大部分applier程式都在等待1071事務的完成，這樣應用日誌的效率就非常低下。從實際情況來看，大事務對於邏輯備庫的影響是非常大的，主庫一條語句更新1000行，到邏輯備庫就需要解析出1000條語句逐條執行，如果這個表沒有主鍵，那麼這些語句甚至需要走全表掃描，那代價就更高了，你會發現一個事務，也許一個小時都未必能應用完成。要在產品環境中使用邏輯備庫，一定要想辦法打散大事務。

邏輯備庫上當前執行的事務可以從v$transaction中獲得，然後通過關聯v$session和v$sqlarea可以獲得事務的SQL語句，進來獲得執行計劃。

二，  Logical Standby Redo Log
觀察Logical standby的redo日誌狀態，確保因不會由於切換時間等待而導致sql apply
延遲：可通過查詢Oracle日誌來得出結論，如出現：
Thread 1 cannot allocate new log, sequence 3834，則表示日誌組不夠或者日誌檔案過小。也可通過  Select  *  from  v$log  檢視redo的狀態，如果status都處於active狀態，則說明redo log大小要增加，組數也需要適當增加。

三、LCR Cache 
邏輯備庫需要將redo記錄解析成LCR，會在shared pool裡分配一部分空間來作為LCR Cache，如果cache太小，就會像OS的虛擬記憶體管理一樣，需要做page out，這會嚴重影響應用日誌的效能。曾經碰到過這麼一個案例，由於存在一個比較大的事務，跨越了5個logfile，邏輯備庫重啟後，需要從最早一個沒有commit的事務開始重新讀取解析和應用日誌，因為LCR Cache太小，重新分析這5個logfile的時候產生了大量的page out，即使只需要應用這一個事務，每解析一個日誌都花了將近半個小時。後來將LCR Cache增加，一個日誌只花了一分鐘不到。

預設情況下，LCR Cache為Shared pool的四分之一，最少不少於30M，否則SQL Apply不能啟動。如果機器的記憶體足夠，建議將LCR Cache儘量設大一點，當然，同時share pool也要足夠大。如果機器記憶體有限，那可以考慮將buffer cache減少一點來給LCR Cache騰出空間。

可以根據系統中page out的情況來調整LCR Cache的大小：
SQL> select name,value from v$logstdby_stats where name like '%page%';
NAME                           VALUE
------------------------------ --------------
bytes paged out                0
seconds spent in pageout       0
如果Paged Out 大於0，可能需要考慮調整Logical Standby中的MAX_SGA（見下面）。

下面的語句將LCR cache設定為1000M：
alter database stop logical standby apply;
EXECUTE DBMS_LOGSTDBY.APPLY_SET('MAX_SGA', 1000);
alter database start logical standby apply;

如果LCR Cache設定太大也是浪費，下面的SQL可以查詢其使用率：
select name,(least(max_sga,bytes)/max_sga) * 100 pct_utilization
from ( select * from v$sgastat where name = 'Logminer LCR c'
),(select value*(1024*1024) max_sga from dba_logstdby_parameters
where name = 'MAX_SGA');

NAME                           PCT_UTILIZATION
------------------------------ ---------------
Logminer LCR c                             100

四、忽略不需要應用日誌的物件

邏輯備庫一般是用來做報表，或者做讀寫分離來分擔主庫的讀壓力。對於很多系統，實際上不需要將主庫上所有物件的變更都應用到邏輯備庫上。比如OLTP上可能有些表是定期從資料倉儲裡迴流過來的，這些表在資料倉儲中都已經存在，沒有必要到邏輯備庫上查詢。而且這些迴流的表一般都是通過批量的方式拉過來的，都是大事務，對於邏輯備庫的效能有很大的影響，那麼能在邏輯備庫上忽略掉是最好的了。注意引數需要大寫。

alter database stop logical standby apply;
--忽略某個表上的DML
execute dbms_logstdby.skip (stmt => 'DML', schema_name => 'HR',
 object_name => 'EMPLOYEES', proc_name => null);
--忽略某個表上的DDL
execute dbms_logstdby.skip (stmt => 'SCHEMA_DDL', schema_name => 'HR',
 object_name => 'EMPLOYEES', proc_name => null);
--忽略某個Schema的DML
execute dbms_logstdby.skip (stmt => 'DML',
 schema_name => 'NINGOO', object_name => '%', proc_name => null);
--忽略某個Schema的DDL
execute dbms_logstdby.skip (stmt => 'SCHEMA_DDL',
 schema_name => 'NINGOO', object_name => '%', proc_name => null);

alter database start logical standby apply;

五、事務一致性
邏輯備庫在恢復的過程中，可以設定不同的事務一致性級別，一共有三種（9i）：

Full:這是預設的級別，事務應用的順序和主庫完全一致
Read Only：這個級別的效能要比full好一些，並且對於select等到的資料還是一致的。但是事務在應用過程中可能和主庫中commit的順序是不一致的。
NONE：完全不管主庫的事務順序，這個級別效能最好，但是可能讀取到不一致的資料。
alter database stop logical standby apply;
exec dbms_logstdby.apply_set('TRANSACTION_CONSISTENCY','READ_ONLY');
alter database start logical standby apply;

Oracle10gR2已經不建議使用TRANSACTION_CONSISTENCY，而是使用另外一個替代引數PRESERVE_COMMIT_ORDER，設定為TRUE相當於TRANSACTION_CONSISTENCY=FULL ，而設定設定為FALSE相當於TRANSACTION_CONSISTENCY=NONE，而READ_ONLY模式在10g實際上已經被取消了（Metalink：387450.1）。據文件說PRESERVE_COMMIT_ORDER=FALSE可以提升應用日誌50%的效能，但不保證此時讀取的資料是一致的。

六、自動刪除已經應用過的日誌
預設情況下，SQL Apply會在日誌應用完成，並且日誌涉及到的事務都已經全部commit的情況下，自動刪除日誌。因為邏輯備庫一方面需要接收主庫傳過來的日誌，一方面自己也會產生日誌，不及時刪除，可能很快歸檔空間就要爆掉了。這個行為也可以通過修改apply的引數來改變：
alter database stop logical standby apply;
execute dbms_logstdby.apply_set('LOG_AUTO_DELETE', FALSE);
alter database start logical standby apply;

七、更改logminer的預設表空間
邏輯備庫使用logminer技術來獲取logfile中的redo記錄，logminer需要儲存很多的後設資料，在10gR2中，logminer預設使用sysaux表空間。一般的系統中，sysaux都不會給太大，可能很快就被logminer撐爆了，可以考慮修改logminer的預設表空間：
exec DBMS_LOGMNR_D.set_tablespace('TBS_NINGOO_DAT');

 

其他：
Logical standby 開啟關閉的步驟：
SQL> startup 
SQL> alter database start logical standby apply;   

SQL> alter database stop logical standby apply;  
SQL> shutdown immediate  

Logical Standby管理 ：
www.ningoo.nethtml2008oracle10gr2_logical_standby_managing_and_tuning.html 

常見Logical Standby 故障的處理
http://blog.oracle.com.cn/html/63/t-51963.html 

注意事項：
1.  賦予sysdba給任何使用者會導致logical standby 應用停止，但是歸檔可以傳輸過去(如果使用的是arch傳輸的話) 。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/35489/viewspace-622532/，如需轉載，請註明出處，否則將追究法律責任。