1 背景
生产环境和灾备放在不同的机房，中间使用20M的专线相连，为了实现两个机房之间数据库的灾难备份，两个机房的操作系统不一样,没有办法使用dataguard，于是免费的Stream成为我们的首选，复制时只复制其中两个用户，数据库总大小为1.5T左右，每天生成归档量为20-40G左右，运行stream期间除遇到大的事务延迟外，没有发现有大的性能问题。
除灾备外还有一个最重要的目的，这种架构可以实现数据库大版本升级（跨平台）0风险，只需停机半小时就能完成升级切换, 切换时注意同步sequence
2 环境信息
2.1 主库 ora71
| Topic | Description |
| OS Version | Linux |
| IP | 192.168.0.71 |
| Oracle version | 10.2.0.2 |
| SID | ora71 |
| Global name | ora71 |
2.2 灾备库ora72
| Topic | Description |
| OS Version | AIX 6.1 |
| IP | 192.168.1.72 |
| Oracle version | 11.2.0.2 |
| SID | ora72 |
| Global name | ora72 |
3 搭建Stream
3.1 清除以前配置
如果源库已经存在,请先在源库和目标库上面清除原来stream配置，一般有两种方式,更推荐使用方法2
方法1
下面操作在stream backup库上面一定是在impdp之前，因为这个操作会把表的SCN号清掉
connect SYS/password as SYSDBA
EXEC DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION();
如果.REMOVE_STREAMS_CONFIGURATION报错也可以先执行删除用户操作，再执行清除操作
drop user stream_admin cascade;
下面操作可以在impdp之前操作，也可以在之后操作，不涉及到清SCN号
也可以使用下面方式
1.删除captrue
SQL> exec dbms_capture_adm.drop_capture(CAPTURE_NAME=>'CAPTURE_ORA71',DROP_UNUSED_RULE_SETS=>TRUE);
PL/SQL procedure successfully completed
2.删除传播进程
exec DBMS_PROPAGATION_ADM.drop_propagation(propagation_name =>'PROP_ORA71_TO_ORA72' ,drop_unused_rule_sets => true);
SQL>
select RULE_NAME,STREAMS_TYPE,STREAMS_NAME from DBA_STREAMS_RULES where rule_owner='STREAM_ADMIN';
exec dbms_streams_adm.remove_rule(RULE_NAME=> 'EYMIT116',STREAMS_TYPE=>'CAPTURE',STREAMS_NAME=>'PROP_ORA71_TO_ORA72',DROP_UNUSED_RULE=>TRUE);
--删除消息队列
select owner,name,QUEUE_TABLE,QUEUE_TYPE from dba_queues where wner='STREAM_ADMIN';
OWNER NAME QUEUE_TABLE QUEUE_TYPE
------------------------------ ------------------------------ ------------------------------ --------------------
STREAM_ADMIN ORA71_QUEUE ORA71_QUEUE_TABLE NORMAL_QUEUE
STREAM_ADMIN AQ$_ORA71_QUEUE_TABLE_E ORA71_QUEUE_TABLE EXCEPTION_QUEUE
exec dbms_streams_adm.REMOVE_QUEUE(QUEUE_NAME=>'ORA71_QUEUE', CASCADE=>TRUE, DROP_UNUSED_QUEUE_TABLE=>true);
3.删除Apply配置
删除apply之前需要把所有报错信息删除
exec dbms_apply_adm.delete_all_errors;
select APPLY_NAME,status from dba_apply;
exec dbms_apply_adm.DROP_APPLY(APPLY_NAME=>'APPLY_ORA71_TO_ORA72',DROP_UNUSED_RULE_SETS=>TRUE);
--删除消息队列
SQL> select owner,name,QUEUE_TABLE,QUEUE_TYPE from dba_queues where wner='STREAM_ADMIN';
OWNER NAME QUEUE_TABLE QUEUE_TYPE
------------------------------ ------------------------------ ------------------------------ --------------------
STREAM_ADMIN Q_ORA71_TO_ORA72 Q_ORA71_TO_ORA72_TABLE NORMAL_QUEUE
STREAM_ADMIN AQ$_Q_ORA71_TO_ORA72_TABLE_E Q_ORA71_TO_ORA72_TABLE EXCEPTION_QUEUE
SQL>
SQL> exec dbms_streams_adm.REMOVE_QUEUE(QUEUE_NAME=>'Q_ORA71_TO_ORA72', CASCADE=>TRUE, DROP_UNUSED_QUEUE_TABLE=>true);
PL/SQL procedure successfully completed
3.2 修改oracle 参数
| COMPATIBLE | 默认值: 10.0.0 是否必须修改: 不 | Oracle 10g R2必须设置为10.2.0或更高. |
| GLOBAL_NAMES | 默认: false 是否必须修改: 是 | 两边都必须设置为true |
| JOB_QUEUE_PROCESSES | 默认: 10 是否必须修改: 否 | 最低为2 |
| LOG_ARCHIVE_CONFIG | 默认: 'SEND, RECEIVE, NODG_CONFIG' 是否必须修改: 是 | 如果是下游stream必须要设置 |
| LOG_ARCHIVE_CONFIG |
|
|
| LOG_ARCHIVE_DEST_n | 默认无 是否必须修改: 是 | 需要指定归档日志路径(开启归档) |
| LOG_ARCHIVE_DEST_STATE_n | 默认: enable |
|
| OPEN_LINKS | 默认: 4 是否必须修改: 否 | Stream环境确保最小为4 |
| PARALLEL_MAX_SERVERS | 默认：自动分配 是否必须修改: 是 | 设置一个合适的值 |
| PROCESSES | 默认：40 是否必须修改: 是 | 设置合适的值，我们设置为1000 |
| SGA_MAX_SIZE | 默认：SGA |
|
| SGA_TARGET | 默认：0 是否必须修改: 是 | 设置为动态分配 |
| SHARED_POOL_SIZE | 默认：0 是否必须修改: 是 | 大于84M，如果SGA_TARGET和STREAMS_ POOL_SIZE没有设置，stream将从shared pool中分配10% |
| STREAMS_POOL_SIZE | 默认：0 是否必须修改: 是 | 如果设置为0 stream将不会跑，每个captrue进程最少10 MB ，每个队列最少10M，stream库上面每个apply进程最少1M
|
| TIMED_STATISTICS | 默认: TYPICAL. 是否必须修改: 是 | 必须为TYPICAL或者 ALL |
| UNDO_RETENTION | 默认: 900 是否必须修改: 是 | Stream要求最少3600 |
Login as SYSDBA
Connect "/ as sysdba"
3.2.1 Global_Names
SQL> alter system set global_names=true scope=both sid='*';
System altered.
如果某个数据库的GLOBAL_NAMES参数设置成了TRUE，那么要求该数据库上DB Link名字与连接的对方的数据库的Golbal Name相同。
alter system set streams_pool_size=100M scope=both sid='*';
alter system set undo_retention=9000 scope=both sid='*';
alter system set nls_date_format='YYYY-MM-DD HH24:MI:SS' scope=spfile sid='*';
alter system set aq_tm_processes=2 scope=both sid='*';
alter system set parallel_max_servers=20 scope=both sid='*';
查看哪些对象不支持
select * from DBA_STREAMS_UNSUPPORTED where wner='EYMIT'
在传输过程中确保这个不支持的对象是只读的
3.3 在主库上面创建管理stream的用户
3.3.1 Create tablespace for stream
create tablespace stream_tbs
datafile '+DATAVG' size 100m autoextend on next 500m maxsize 20g segment space management auto;
3.3.2 Create stream_admin user
create user stream_admin identified by stream_xxxxx default tablespace stream_tbs temporary tablespace temp;
3.3.3 Grant privilege to stream_admin
grant connect,resource,dba,aq_administrator_role to stream_admin;
begin
dbms_streams_auth.grant_admin_privilege(
grantee => 'stream_admin',
grant_privileges => true);
end;
/
3.3.4 Change the default tablespace of Logminer
#将logminer的数据字典从system表空间转移到新建的表空间，防止撑满system表空间
SQL> execute dbms_logmnr_d.set_tablespace('stream_tbs');
3.4 在灾备库上面创建管理stream的用户
#以sysdba身份登录
connect / as sysdba
3.4.1 Create tablespace for stream
create tablespace stream_tbs
datafile '/oradata/ora72/stream_tbs01.dbf' size 100m autoextend on next 500m maxsize 20g segment space management auto;
3.4.2 Create stream_admin user
create user stream_admin identified by stream_xxxxx
default tablespace stream_tbs temporary tablespace temp;
3.4.3 Grant privilege to stream_admin
grant connect,resource,dba,aq_administrator_role to stream_admin ;
begin
dbms_streams_auth.grant_admin_privilege(
grantee => 'stream_admin ',
grant_privileges => true);
end;
/
3.4.4 Change the default tablespace of Logminer
execute dbms_logmnr_d.set_tablespace('stream_tbs');
3.5 配置tnsnames.ora
3.5.1 Config tnsnames.ora of Master DB
主库（tnsnames.ora）中添加Backup Database配置。
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.72)(PORT = 1522))
)
(CONNECT_DATA =
(SID = ora72)
(SERVER = DEDICATED)
)
)
3.5.2 Config tnsnames.ora of Backup DB
Backup Database（tnsnames.ora）中添加主库配置。
ora71 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.71)(PORT = 1521))
)
(CONNECT_DATA =
(SID = ora71)
(SERVER = DEDICATED)
)
)
3.6 在主库上面启用 supplemental log
使所有操作都记录在redo log中，防止部分手动设置nolog的操作不能记录到redo log中，从而导致数据不同步
ALTER DATABASE FORCE LOGGING;
启用追加日志（Supplemental Log），可以基于Database级别或Table级别
sysdba登录master数据库
#检查是否启用了追加日志：
SELECT SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI,SUPPLEMENTAL_LOG_DATA_ALL FROM V$DATABASE;
#启用Database Supplemental Log
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA
(PRIMARY KEY, UNIQUE, FOREIGN KEY,ALL) COLUMNS;
如果需要删除SUPPLEMENTAL
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA
(PRIMARY KEY, UNIQUE, FOREIGN KEY,ALL) COLUMNS;
3.7 创建 Database Link
3.7.1 Create 主库 Link
#以stream_admin 身份，登录主库。
connect stream_admin /stream_xxxxx@ora71
create database link ora72 connect to stream_admin identified by stream_xxxxx using 'ora72';
3.7.2 Create 灾备库Link
#以stream_admin 身份，登录Backup Database。
connect stream_admin/stream_xxxxx
create database link ora71 connect to stream_admin identified by stream_xxxxx using 'ora71';
3.7.3 Test Database Link
测试两个DBLINK的可用性
3.8 同步用户的导入导出
3.8.1 create Tablespace and User on Backup DB
# Tablespace
CREATE SMALLFILE TABLESPACE "EYMIT"
DATAFILE '/oradata/ora72/EYMIT_01.dbf' SIZE 2G AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED
LOGGING
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;
# Users
create user EYMIT
identified by EYMITpwd
default tablespace EYMIT_TBS
temporary tablespace TEMP
profile DEFAULT;
-- Grant/Revoke object privileges
-- Grant/Revoke role privileges
grant connect to EYMIT;
grant resource to EYMIT;
-- Grant/Revoke system privileges
grant create any synonym to EYMIT;
grant create any table to EYMIT;
grant unlimited tablespace to EYMIT;
-- Create the user
create user EYMIT2
identified by EYMIT2pwd
default tablespace EYMIT2_TBS
temporary tablespace TEMP
profile DEFAULT;
-- Grant/Revoke role privileges
grant connect to EYMIT2;
grant resource to EYMIT2;
-- Grant/Revoke system privileges
grant create any synonym to EYMIT2;
grant create session to EYMIT2;
grant create synonym to EYMIT2;
grant debug connect session to EYMIT2;
grant select any dictionary to EYMIT2;
grant unlimited tablespace to EYMIT2;
3.8.2 Export shema
在导入前确保STREAM目标数据库中没有JOB在更改目标库中的数据
使用expdp的方式
在源用户中初始化SCN号
sqlplus stream_admin/stream_xxxxx
BEGIN
DBMS_CAPTURE_ADM.PREPARE_SCHEMA_INSTANTIATION(
schema_name => 'EYMIT');
END;
/
BEGIN
DBMS_CAPTURE_ADM.PREPARE_SCHEMA_INSTANTIATION(
schema_name => 'EYMIT2');
END;
/
导出前一定要检查undo_retention的时间比导出时间长.
SQL> show parameter undo_retention
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
undo_retention integer 56000
SQL>
把SCN号信息写到redo log中,不执行下面语句取不到符合条件的SCN号.
exec DBMS_CAPTURE_ADM.BUILD();
取得当前SCN号,确保这个arch logfile是有效且存在在磁盘中的.
Select FIRST_CHANGE#,name from v$archived_log where dictionary_begin='YES';
从源库导出,注意需要加入flashback_scn,加入scn后导出的表不会都是这个SCN号，但是都会比这个SCN号大，一般相关联的表会是同一个SCN号
expdp userid/password dumpfile= directory= flashback_scn=
如：
expdp system/xxxxxx directory=expdp_dir dumpfile=expdp_ora71_20120824_%U.dmp schemas=EYMIT2,EYMIT exclude=TABLE:\"IN \(\'T_A
TTACHMENT\'\)\",STATISTICS flashback_scn=1110227790 PARALLEL=5
导入到目标库
impdp userid/password dumpfile= directory=
如：
impdp system/xxxx directory=expdp_dir dumpfile= expdp_ora71_20120824_%U.dmp logfile=impdp_ora71_20110916.log EXCLUDE=grant
PARALLEL=3 REMAP_TABLESPACE=EYMIT_TBS:EYMIT_tbs,CIRCBJ_ACC_TBS:EYMIT2_tbs
3.9 创建stream消息队列
3.8.1 在主库上面创建队列
#以stream_admin 身份，登录主库。
connect stream_admin /stream_admin@ora71
begin
dbms_streams_adm.set_up_queue(
queue_table => 'ora71_queue_table',
queue_name => 'ora71_queue');
end;
/
#同时，也创建了queue table。一个queue table可以有多个queue。
3.8.2 在灾备库上面创建队列
#以stream_admin 身份，登录Backup Database。
connect stream_admin /stream_admin@ora72
dbms_streams_adm.set_up_queue(
queue_table => 'q_table_ora71_to_ora72',
queue_name => 'q_ora71_to_ora72');
end;
/
3.10 在主库上面创建Capture捕获进程
#以stream_admin 身份，登录主库。
connect stream_admin /stream_admin@ora71
execute DBMS_CAPTURE_ADM.CREATE_CAPTURE(queue_name => 'ora71_queue', capture_name => 'capture_ora71', use_database_link=>TRUE,start_scn=>1741155485, first_scn=>1741155485);
注意这里的start_scn and first_scn是从上面视图v$archived_log中取得.
注意如果同步两个用户，需要更改变shema_name后再执行一次
begin
dbms_streams_adm.add_schema_rules(
schema_name => 'EYMIT',
streams_type => 'capture',
streams_name => 'capture_ora71',
queue_name => 'ora71_queue',
include_dml => true,
include_ddl => true,
include_tagged_lcr => false,
source_database => null,
inclusion_rule => true);
end;
/
begin
dbms_streams_adm.add_schema_rules(
schema_name => 'EYMIT2',
streams_type => 'capture',
streams_name => 'capture_ora71',
queue_name => 'ora71_queue',
include_dml => true,
include_ddl => true,
include_tagged_lcr => false,
source_database => null,
inclusion_rule => true);
end;
/
3.11 在主库上面创建propagation传播进程
#以stream_admin 身份，登录主库。
注意如果同步两个用户，需要更改变shema_name后再执行一次
connect stream_admin/stream_admin
begin
dbms_streams_adm.add_schema_propagation_rules(
schema_name => 'EYMIT',
streams_name => 'prop_ora71_to_ora72',
source_queue_name => 'stream_admin.ora71_queue',
destination_queue_name => 'stream_admin.q_ora71_to_ora72@ora72',
include_dml => true,
include_ddl => true,
include_tagged_lcr => false,
source_database => 'ora71',
inclusion_rule => true);
end;
/
begin
dbms_streams_adm.add_schema_propagation_rules(
schema_name => 'EYMIT2',
streams_name => 'prop_ora71_to_ora72',
source_queue_name => 'stream_admin.ora71_queue',
destination_queue_name => 'stream_admin.q_ora71_to_ora72@ora72',
include_dml => true,
include_ddl => true,
include_tagged_lcr => false,
queue_to_queue =>true,
source_database => 'ora71',
inclusion_rule => true);
end;
/
#修改propagation休眠时间为0，实时传播LCR。
begin
dbms_aqadm.alter_propagation_schedule(
queue_name => 'ORA71_QUEUE',
destination => '"STREAM_ADMIN"."Q_ORA71_TO_ORA72"@ORA72',
latency => 0);
end;
/
在Primary库上面设置capture的SGA为100M,避免出现性能问题
-- set the SGA size of capture.
EXEC dbms_capture_adm.set_parameter('CAPTURE_ORA71','_SGA_SIZE','100');
3.12 在灾备库上面创建Apply应用进程
#以stream_admin 身份，登录Backup Database。
connect stream_admin /stream_admin
如果同步2个用户需要建两个rules
dbms_streams_adm.add_schema_rules(
schema_name => 'EYMIT',
streams_type => 'apply',
streams_name => 'apply_ora71_to_ora72',
queue_name => 'stream_admin.q_ora71_to_ora72',
include_dml => true,
include_ddl => true,
include_tagged_lcr => false,
source_database => 'ora71',
inclusion_rule => true);
end;
/
begin
dbms_streams_adm.add_schema_rules(
schema_name => 'EYMIT2',
streams_type => 'apply',
streams_name => 'apply_ora71_to_ora72',
queue_name => 'stream_admin.q_ora71_to_ora72',
include_dml => true,
include_ddl => true,
include_tagged_lcr => false,
source_database => 'ora71',
inclusion_rule => true);
end;
/
如果要应用的用户和源用户不一样，需要进行改用户操作，注意只能改DML，不能改DDL操作，也就是说当执行DDL操作时会报错
exec dbms_streams_adm.rename_schema(rule_name => agent_rule_name_dml,
from_schema_name => 'EYMIT',
to_schema_name => 'PUBEYMIT',
operation => 'ADD');
http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_streams_adm.htm#sthref11008
在Backup DB库中设置apply的进程为1，11g默认为4,貌似是Bug(ID 345119.1),不修改没有主键的表同步会报错
-- 设置apply进程的并行数量
DBMS_APPLY_ADM.SET_PARAMETER(
apply_name => 'APPLY_ORA71_TO_ORA72',
parameter => 'PARALLELISM',
value => 1 );
END;
/
查询初始化的SCN是否存在或正确
select * from DBA_APPLY_INSTANTIATED_OBJECTS;
已执行到这一步
3.13 在主库上面过滤不需要同步的表
#以stream_admin 身份，登录主库。
创建排除RULE , T_ATTACHMENT为附件表，里面有不支持的列，MV_FC_VEHICLE为物化视图，STREAM不支持
connect stream_admin/stream_admin
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES
(
table_name => 'EYMIT2.EXCLUE_TABLE1',
streams_type => 'capture',
streams_name => 'capture_ORA71',
queue_name => 'ORA71_queue',
include_dml => true,
include_ddl => true,
source_database => null,
inclusion_rule => false
);
END;
/
在backup库禁用不同步表的外键约束
FROM DBA_CONSTRAINTS a
WHERE A.owner IN ('EYMIT2','EYMIT')
AND R_CONSTRAINT_NAME IN
(SELECT CONSTRAINT_NAME
FROM DBA_CONSTRAINTS
WHERE TABLE_NAME = 'T_ATTACHMENT'
AND CONSTRAINT_TYPE IN ('P','U'));
3.14 启动STREAM
#以stream_admin 身份，登录Backup Database。
connect stream_admin /stream_admin
#启动Apply进程
dbms_apply_adm.start_apply(
apply_name => 'apply_ora71_to_ora72');
end;
/
#以stream_admin 身份，登录主库。
connect stream_admin /stream_admin
#启动Capture进程
dbms_capture_adm.start_capture(
capture_name =>'capture_ora71');
end;
/
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21605631/viewspace-759699/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/21605631/viewspace-759699/
1223
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
