Oracle Stream Replcation 配置
2017-10-25 本文已影响116人
风亡小窝
环境：
主数据库
操作系统：linux
IP地址：172.168.68.173
数据库：Oracle 11.2.0.3.0
ORACLE_SID：sm1
Global_name：sm1
从数据库
操作系统：linux
IP地址：172.168.68.172
数据库：Oracle 11.2.0.3.0
ORACLE_SID：sm2
Global_name：sm2
- 主数据库 oracle 版本不能高于从数据库 oracle 版本
- Global_name 必须要跟 SID 相同(修改SID请看文章末的参考链接)
- 注意 oracle_home 不能以 / 结尾
--查看 oracle_sid
echo $ORACLE_SID
--查看 oracle_home
echo $ORACLE_HOME
--查看 oracle 版本
select * from v$version;
切换到 oracle 用户
su - oralce
登陆到 oracle
sqlplus / as sysdba
- 源数据库和目标数据库必须是归档的
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;
SQL> archive log list /* 查看修改结果 */
- 源数据库和目的数据库均需要设置的参数：
alter system set global_names=true scope = both;
#默认为 false, Database Link 使用的是数据库的 global_name。
alter system set job_queue_processes = 10 scope=both;
alter system set sga_target = 300m scope=spfile;
alter system set open_links=4 scope=spfile;
alter system set statistics_level='TYPICAL' scope=both;
alter system set "_job_queue_interval"=1 scope=spfile;
alter system set aq_tm_processes=2 scope=both;
alter system set streams_pool_size=200m scope=both;
查看修改结果
show parameter processes;
show parameter session;
show parameter stream
- 在源数据库上启用追加日志
可以基于Database级别或Table级别，启用追加日志（Supplemental Log）。
/* 启用 Database 追加日志 */
alter database add supplemental log data;
/* 启用 Table 追加日志 */
alter table add supplement log group log_group_name(table_column_name) always;
重启数据库，使修改生效
- 源数据库和目的数据库创建相同表空间和用户并赋权
创建表空间：
create tablespace streams_tbs datafile '.\streams_tbs.dbf' size 100M reuse autoextend on maxsize unlimited;
/* 查看所有表空间名和状态 */
select tablespace_name, status from dba_tablespaces;
/* 查询当前表空间属性 */
select * from dba_tablespaces where tablespace_name='STREAMS_TBS';
创建用户：
CREATE USER strmadmin IDENTIFIED BY strmadmin DEFAULT TABLESPACE streams_tbs QUOTA UNLIMITED ON streams_tbs;
赋权：
/* 授予 dba 权限简化配置 */
GRANT DBA to strmadmin;
/* 赋予流管理特权 */
exec DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE('strmadmin');
- 配置 listener.ora 和 tnsnames.ora
源数据库
# sm1/tnsnames.ora
sm1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.168.68.173)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = sm1)
(SERVER = DEDICATED)
)
)
sm2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.168.68.172)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = sm2)
(SERVER = DEDICATED)
)
)
# sm1/lisenter.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = sm1)
(ORACLE_HOME = /opt/oracle/product/11.2.0/dba_home)
(SID_NAME = sm1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.168.68.173)(PORT = 1521))
)
)
目的数据库
# sm2/tnsnames.ora
sm1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.168.68.173)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = sm1)
(SERVER = DEDICATED)
)
)
sm2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.168.68.172)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = sm2)
(SERVER = DEDICATED)
)
)
# sm2/listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = sm2)
(ORACLE_HOME = /opt/oracle/product/11.2.0/dba_home)
(SID_NAME = sm2)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.168.68.172)(PORT = 1521))
)
)
- 创建 database link
先用strmadmin登陆，在创建dblink
conn strmadmin/strmadmin@sm1;
create database link sm2 connect to strmadmin identified by strmadmin using 'sm2';
conn strmadmin/strmadmin@sm2;
create database link sm1 connect to strmadmin identified by strmadmin using 'sm1';
/*查看 db_link */
select * from dba_db_links;
/*移除 db_link */
drop public database link link_name;
当
global_names = true时要注意以下几点：
- 源和目的数据库的global_name不能相同
- 你在本地建立的DBLINK的名称必须和远程数据库的Global_name必须相同
查看global_names：show parameter global_name;
查看global_name ：select * from global_name;
修改global_name ：update global_name set global_name='ORCL';
更多请参考==>这儿- 修改global_name后请重启数据库使其生效
- 源库与目标库必须创建directory
create directory dir_local as './local_dir';
/*查看 directory*/
select * from dba_directories;
/*删除 directory*/
drop directory dir_name;
8.在源库执行MAINTAIN_xxxxx过程
-- 登陆到strmadmin账号
conn strmadmin/strmadmin
/* 全库级复制 */
begin
dbms_streams_adm.maintain_global(
source_directory_object =>'dir_local',
destination_directory_object =>'dir_local',
source_database =>'sm1',
destination_database =>'sm2',
capture_name =>'DBXA_CAP',
capture_queue_name =>'DBXA_CAP_Q',
capture_queue_table =>'DBXA_CAP_Q_T',
propagation_name =>'DBXA_TO_DBXB_PROP',
apply_name =>'DBXA_APP',
apply_queue_name =>'DBXA_CAP_Q',
apply_queue_table =>'DBXA_CAP_Q_T',
bi_directional =>FALSE,
perform_actions =>TRUE,
include_ddl =>TRUE,
instantiation =>DBMS_STREAMS_ADM.INSTANTIATION_FULL_NETWORK
);
end;
/
如果不需要用户为streams组件定义有意义的名称,例程可以得到简化，如下：
/* 全库级复制 */
begin
dbms_streams_adm.maintain_global(
source_database =>'sm1',
destination_database =>'sm2',
source_directory_object =>'dir_local',
destination_directory_object =>'dir_local',
bi_directional => FALSE,
perform_actions =>TRUE,
include_ddl =>TRUE,
instantiation =>DBMS_STREAMS_ADM.INSTANTIATION_FULL_NETWORK
);
end;
/
省略stream组件的名称后，组件的名称由例程自行生成。
/* 表空间级复制 */
declare
ts_names dbms_streams_tablespace_adm.tablespace_set;
begin
ts_names(1) := 'ts_name1';
ts_names(2) := 'ts_name2';
ts_names(3) := 'ts_name3';
dbms_streams_adm.maintain_tts(
tablespace_names => ts_names ,
source_database => 'sm1',
destination_database => 'sm2',
source_directory_object => 'dir_local ',
destination_directory_object => 'dir_local ',
bi_directional => FALSE,
perform_actions => TRUE
);
end;
/
/* scheme 级复制 */
declare
schemas dbms_utility.uncl_array;
begin
schemas(1) := 'chenhao';
dbms_streams_adm.maintain_schemas (
schema_names => schemas,
source_database => 'sm1',
destination_database => 'sm2',
source_directory_object => 'dir_local',
destination_directory_object => 'dir_local',
bi_directional => FALSE,
include_ddl => TRUE,
instantiation => DBMS_STREAMS_ADM.INSTANTIATION_SCHEMA_NETWORK
);
end;
/
/*表级别的复制*/
declare
tables dbms_utility.uncl_array;
begin
tables(1) := 'SCOTT.DEPT';
tables(2) := 'SCOTT.EMP';
tables(3) := 'HR.SALGRADE';
tables(4) := 'HR.BONUS';
dbms_streams_adm.maintain_tables (
table_names => tables,
source_database => 'sm1',
destination_database => 'sm2',
source_directory_object => 'dir_local',
destination_directory_object => 'dir_local',
bi_directional => FALSE,
include_ddl => TRUE,
perform_actions => TRUE,
instantiation => DBMS_STREAMS_ADM.INSTANTIATION_TABLE
);
end;
/
bi_directional默认为false，表示单向复制。如需要双向复制则设置为true。
更多详细的参数解释请参考 => here
/*修改传播时延，使其尽快的复制到目的数据库*/
begin
dbms_aqadm.alter_propagation_schedule(
queue_name => 'SM1$CAPQ',
destination => 'sm2',
destination_queue => 'SM2$APPQ',
latency => 5
);
end;
/
如果执行过程中遭遇错误，则使用以下例程清除错误，然后重新执行
begin
dbms_streams_adm.recover_operation(
script_id=>'5C342452BA72557DE050007F01001BCA',
operation_mode=>'ROLLBACK'
);
end;
/
查看正在执行的 procedure，必要时清空 DBA_RECOVERABLE_SCRIPT
select * from DBA_RECOVERABLE_SCRIPT;
现在应该配置成功了，你可以进行测试了。
-- 查看捕获，传播，应用进程
select capture_name, status, queue_name from dba_capture;
select propagation_name, status, source_queue_name, destination_queue_name from dba_propagation;
select apply_name, status, queue_name from dba_apply;
-- 移除捕获，传播，应用进程
exec dbms_capture_adm.drop_capture('capture_name');
exec dbms_propagation_adm.drop_propagation('propagation_name');
exec dbms_apply_adm.drop_apply('apply_name');
-- 查看队列
select owner, name from dba_queues;
-- 移除对列
exec dbms_streams_adm.remove_queue(
queue_name => 'queue_name',
cascade => true,
drop_unused_queue_table => true);
--带数据完成源端exp和目端的import：
exp USERID=SYSTEM/manager@sm1 OWNER=SYSTEM FILE=./STRM.dmp LOG=./export.log OBJECT_CONSISTENT=Y STATISTICS = NONE
imp USERID=SYSTEM/manager@sm2 FULL=Y CONSTRAINTS=Y FILE=./STRM.dmp IGNORE=Y COMMIT=Y LOG=./import.log STREAMS_INSTANTIATION=Y
--或仅作实例化(不带数据)：
exp USERID=SYSTEM/manager@sm1 OWNER=SYSTEM FILE=./STRM.dmp LOG=./export.log OBJECT_CONSISTENT=Y STATISTICS = NONE ROWS=NO
imp USERID=SYSTEM/manager@sm2 FULL=Y CONSTRAINTS=Y FILE=./STRM.dmp IGNORE=Y COMMIT=Y LOG=./import.log STREAMS_INSTANTIATION=Y
查看scn
set serveroutput on
DECLARE
iscn NUMBER; -- Variable to hold instantiation SCN value
BEGIN
iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_OUTPUT.PUT_LINE ('Instantiation SCN is: ' || iscn);
END;
/
设置为目标库互置用户的SCN
BEGIN
DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN(
source_schema_name => 'SYSTEM',
source_database_name => 'DBA',
instantiation_scn => &iscn);
END;
/
启动apply进程
BEGIN
DBMS_APPLY_ADM.START_APPLY(apply_name=>'APPLY$_SM1_181');
END;
/
启动capture进程
BEGIN
DBMS_CAPTURE_ADM.START_CAPTURE(
capture_name=>'SM1$CAP');
END;
/
查询进程错误
-- capture进程错误
select error_number,error_message from dba_capture;
-- apply进程错误
select error_number,error_message,queue_name,error_creation_time from dba_apply_error;
清理stream配置
这会删除整个数据库中的Streams配置，如果有两个 streams的用户，会把这两个用户的进程删清楚掉
exec dbms_streams_adm.remove_streams_configuration;
参考链接：
Replicating Data Using Oracle Streams
一步一步学Streams
Database Vault 的禁用
修改SID和DB_NAME
各种 name 的关系
重建 redolog
oracle 11g streams搭建
oracle 11g streams各种类型搭建主要步骤
spfile 和 pfile的区别，生成，加载和修复
ORA-01034和ORA-27101错误
ORA-26687
