完整oracle 流複製配置

 
基本環境搭配:

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');