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.方法一: 另外一台Server 建立物理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;
在物理Standby上执行 : 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
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/35489/viewspace-610656/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/35489/viewspace-610656/