基本环境搭配:
Sqlplus ‘/ as sysdba’
alter system set aq_tm_processes=2 scope=both; //不要设置成0或者10, 这样做可能禁用队列监测处理和影响流池内存利用率.
alter system set global_names=true scope=both;
alter system set job_queue_processes=10 scope=both; //建议的最低值为4,定义传播进程数.
alter system set parallel_max_servers=20 scope=both;//并行执行可用的最大进程数量
alter system set undo_retention=3600 scope=both;//数据提交后至少保留的秒数.
alter system set nls_date_format='YYYY-MM-DD HH24:MI:SS' scope=spfile;//时间格式
alter system set streams_pool_size=256M scope=spfile;//没有捕获进程无须配置此处
alter system set utl_file_dir='*' scope=spfile; //*代表可以操作任何目录
alter system set open_links=4 scope=spfile; //数据库连接数.DB_LINK
重启数据库
shutdown;
startup;
*将数据库置为归档模式:
sqlplus ‘/ as sysdba’
alter system set log_archive_dest_1='location=E:\oracle\product\10.2.0\archive\arch' scope=spfile; //归档日志路径
alter system set log_archive_start=TRUE scope=spfile;
alter system set log_archive_format='arch%t_%s_%r.arc’ scope=spfile;//设置归档模式固定参数
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;
//alter system set log_archive_dest_state_2='enable' scope=both;
SQL> archive log list
创建主从环境stream管理用户:
创建主环境的Stream专用表空间
Connect / as sysdba
Create tablespace tbs_stream datafile 'E:\oracle\product\10.2.0\oradata\michael\tbs_stream01.dbf' size 100m autoextend on maxsize unlimited segment space management auto; //表空间大小视情况
Execute dbms_logmnr_d.set_tablespace ('tbs_stream');
创建Stream管理用户并授权
create user strmadmin identified by strmadmin default tablespace tbs_stream temporary tablespace temp;
grant connect,resource,dba,aq_administrator_role to strmadmin;
begin
dbms_streams_auth.grant_admin_privilege(
grantee => 'strmadmin',
grant_privileges => true);
end;
创建主数据库数据库链:
connect strmadmin/strmadmin
create public database link peter.landun connect to strmadmin identified by strmadmin using'peter';
创建从数据库数据库链
connect strmadmin/strmadmin
create public database link michael connect to strmadmin identified by strmadmin using 'michael.landun';//创建公用的DB_LINK,名称为”michael.landun”.
downstream_capture配置2008-11-15 15:18Initialisation parameters -- minimum
alter system set aq_tm_processes=3 scope=BOTH;
alter system set global_names=TRUE scope=BOTH;
alter system set job_queue_processes=10 scope=BOTH;
alter system set undo_retention=3600 scope=BOTH;
alter system set streams_pool_size=180M scope=BOTH;
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;
At source database
alter system set LOG_ARCHIVE_DEST_2 = 'SERVICE=DESTTRY LGWR ASYNC OPTIONAL
NOREGISTER VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE)';
alter system set LOG_ARCHIVE_DEST_STATE_2 = 'ENABLE';
alter system set LOG_ARCHIVE_CONFIG='SEND' scope=both;
At the downstream database:
alter system set LOG_ARCHIVE_DEST_2 = 'location=E:\Oracle\product\10.2.0\db_1\admin\sample\arch\archive2 MANDATORY VALID_FOR=(STANDBY_LOGFILE,PRIMARY_ROLE)'
alter system set LOG_ARCHIVE_DEST_STATE_2 = 'ENABLE';
alter system set LOG_ARCHIVE_CONFIG= 'RECEIVE' scope=both;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('E:\oracle\product\10.2.0\REDODATE\REDO04a.LOG') size 10m;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('E:\oracle\product\10.2.0\REDODATE\REDO05a.LOG') size 10m;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('E:\oracle\product\10.2.0\REDODATE\REDO06a.LOG') size 10m;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('E:\oracle\product\10.2.0\REDODATE\REDO07a.LOG') size 10m;
SET VERIFY OFF
SET ECHO OFF
SPOOL streams_setup.log
conn sys/sys@desttry as sysdba
create tablespace downtargetstream6 datafile 'D:\downtargetstream6.dbf' size 25M reuse autoextend on maxsize unlimited;
create user streamadmin identified by streamadmin default tablespace downtargetstream6 quota unlimited on downtargetstream6;
grant dba to streamadmin;
GRANT EXECUTE ON DBMS_LOCK TO streamadmin;
GRANT EXECUTE ON DBMS_PIPE to streamadmin;
create user streamuser identified by streamuser;
GRANT CONNECT, RESOURCE,DBA TO streamuser IDENTIFIED BY streamuser;
exec dbms_streams_auth.grant_remote_admin_access('streamadmin');
本文转自海天一鸥博客园博客，原文链接：http://www.cnblogs.com/sgsoft/archive/2011/04/03/2004878.html，如需转载请自行联系原作者
