Oracle 10g 邏輯Standby 建立及注意點

A.下面的所有操作均在Primary DB:

SELECT NAME,CREATED,LOG_MODE,OPEN_MODE,DATABASE_ROLE,FORCE_LOGGING FROM V$DATABASE;
查詢資料庫的名稱,建立時間,歸檔模式,狀態及角色等

SELECT FORCE_LOGGING FROM V$DATABASE; 查詢資料庫的狀態及角色及建立時間

# ALTER DATABASE NO FORCE LOGGING; 取消
ALTER DATABASE FORCE LOGGING; 執行此句

SELECT DATABASE_ROLE FROM V$DATABASE; 查詢資料庫的角色

SELECT * FROM DBA_LOGSTDBY_UNSUPPORTED;
該檢視顯示包含不被邏輯STANDBY支援的資料型別的表的列名及該列的資料型別.

SELECT * FROM DBA_LOGSTDBY_NOT_UNIQUE;
該檢視顯示所有即沒主鍵也沒唯一索引的表.

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應用能否唯一識別表列,找出不被支援的表.

SELECT SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;
該檢視顯示主資料庫上,補充日誌是否被啟用.

SELECT * FROM DBA_LOGSTDBY_SKIP; 檢視邏輯過濾操作

SQL>show parameter LOG_ARCHIVE_LOCAL_FIRST; 設定進行強制本地先歸檔

SQL>alter system set archive_lag_target=600 scope=both; 設定主庫強制10分鐘自動歸檔一次

SQL>alter system set undo_retention=3600 scope=both; 設定大一點預防ORA-01555錯誤

CREATE TABLESPACE LOGMNRTS DATAFILE  
   '/data/mxdell/logmnrts01.dbf' SIZE 2025M AUTOEXTEND OFF 
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM. SIZE 10M
BLOCKSIZE 16K
SEGMENT SPACE MANAGEMENT MANUAL;

SQL>EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE('LOGMNRTS');

 

B.方法一:異機建立物理STANDY(這裡DB_NAME及DB_UNIQUE_NAME均為MXDELL)

Primary:

SQL>alter system set log_archive_dest_2='service=standby optional reopen=60' scope=both;

SQL>alter system switch logfile;

 

C.設定簡單引數使物理STANDBY能夠自動應用歸檔。
首先兩臺機器tnsnames.ora需要新增
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) #可以不需要
    )
  )

其次:
Standby:
關閉資料庫
設定下列引數(10g)
*.standby_archive_dest='/data/mxdell/arch'
*.db_file_name_convert='/data/mxdell','/data/mxdell'
*.log_file_name_convert='/u01/product/oradata/mxdell','/u01/product/oradata/mxdell'
*.log_archive_trace=0
*.log_archive_min_succeed_dest=1
*.standby_file_management='AUTO'
*.fal_server='mxdell'
*.fal_client='standby'

 

 

=============================== 

D.準備切換邏輯STANDBY

下面的所有操作均在Primary DB:

SQL>alter system set log_archive_dest_1='location=/data/mxdell/arch valid_for=(all_logfiles,all_roles) db_unique_name=mxdell' scope=both;

SQL>alter system set log_archive_dest_2='service=standby optional reopen=60 valid_for=(online_logfiles,primary_role) db_unique_name=mxdell'

scope=both;

 

下面的所有操作均在Standby DB:
*.db_unique_name='standby'
*.log_archive_dest_1='location=/data/mxdell/arch valid_for=(all_logfiles,all_roles) db_unique_name=mxdell'
*.log_archive_dest_2='service=mxdell valid_for=(online_logfiles,primary_role) db_unique_name=mxdell'

在primry db上執行(等所有transaction執行完畢)
SQL>EXECUTE DBMS_LOGSTDBY.BUILD;
alter system switch logfile；

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

重啟邏輯備庫

第一次啟動
shutdown immediate;
startup mount;
alter database open resetlogs;
然後
shutdown immediate;
startup;

啟動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;

 

自動刪除已經應用過的日誌:
邏輯備庫一方面需要接收主庫傳過來的日誌，一方面自己也會產生日誌，不及時刪除，可能很快歸檔空間就要爆掉(如無特殊要求可開啟)

alter database stop logical standby apply;
execute dbms_logstdby.apply_set('LOG_AUTO_DELETE','false');
alter database start logical standby apply; 

 

logical standby 以後開啟的步驟：

SQL>startup 
SQL>alter database start logical standby apply;   

 

 

=================

 

Logical Standby管理 ：

  

 

注意事項：

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