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管理 ：

http://www.ningoo.net/html/2008/oracle10gr2_logical_standby_managing_and_tuning.html  

 

注意事项：

1.  赋予sysdba给任何用户会导致logical standby 应用停止，但是归档可以传输过去(如果使用的是arch传输的话) 。

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/35489/viewspace-610697/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/35489/viewspace-610697/