real_timedownstream配置.doc

1. 在source和destination机（注以下操作需要在2个机器上进行）1.1 创建stream用户的表空间SQLcreate tablespace tbs_stream datafile C:tbs_stream.dbf size 200M; 1.2 将logmnr的默认表空间转到刚刚建立的表空间SQL execute dbms_logmnr_d.set_tablespace(tbs_stream);1.3 创建stream user和授权SQL CREATE USER strmadmin IDENTIFIED BY strmadmin DEFAULT TABLESPACE tbs_stream ; SQL grant dba to strmadmin;1.4 赋予流管理特权SQL BEGIN DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE( grantee = strmadmin, grant_privileges = true); END; / 1.5 check stream admin用户的情况：SQL SELECT * FROM dba_streams_administrator;1.6 查一下arch目录是否设置正确，是否启动归档模式。主库SQLalter system set log_archive_dest_1=location=C:oracleproduct10.2.0flash_recovery_areaprodarchivelog;备库SQLalter system set log_archive_dest_1=location=C:oracleproduct10.2.0flash_recovery_areah10garchivelog; SQL shutdown immediate SQL startup mount; SQL alter database archivelog; SQL alter database open; SQL archive log list;1.7 设置tnsname，能tnsping到对方的机器。 C:oracleproduct10.2.0db_1NETWORKADMIN tnsnames.ora 文件中添加对方主机信息：H10G = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.102)(PORT = 1521) ) (CONNECT_DATA = (SERVICE_NAME = h10g) ) )*添加到PROD主机中*-PROD = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.101)(PORT = 1521) ) (CONNECT_DATA = (SERVICE_NAME = prod) ) )*添加到H10G主机中*1.8 设置stream需要的初始化参数：SQLalter system set aq_tm_processes=2 scope=both; alter system set global_names=true scope=both; alter system set job_queue_processes=10 scope=both; 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=25M scope=spfile; alter system set utl_file_dir=* scope=spfile; alter system set open_links=4 scope=spfile; * streams_pool_size 参数在生产环境中建议设成200M以上*需重启数据库*1.9 建立dblink在source机：SQLconn strmadmin/strmadminSQLcreate database link h10g connect to strmadmin identified by strmadmin using h10g;在destination机：SQL conn strmadmin/strmadminSQL create database link prod connect to strmadmin identified by strmadmin using prod;2 在source数据库启用Database,table 追加日志 SQLalter database add supplemental log data; SQLALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE, FOREIGN KEY) COLUMNS;3 在downstream site设置：SQLALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=SPFILE;SQLALTER SYSTEM SET LOG_ARCHIVE_DEST_2=LOCATION=C:oracleproduct10.2.0flash_recovery_areah10gstandby_arch VALID_FOR=(STANDBY_LOGFILE,PRIMARY_ROLE) SCOPE=SPFILE;4 在source site和downstream site同时设置：SQLALTER SYSTEM SET LOG_ARCHIVE_CONFIG=DG_CONFIG=(prod,h10g) SCOPE=SPFILE;5创建standby redo：5.1 在source库查询redo情况SQL select THREAD#, GROUP#, BYTES/1024/1024 from V$LOG; 注意创建standby redo时，大小要和source site的redo大小一致，数量上要比source site的redo多一个。5.2 在downstream site：SQLALTER DATABASE ADD STANDBY LOGFILE GROUP 4(C:oracleproduct10.2.0oradatah10gstdy_redo04.log) SIZE 50M; SQLALTER DATABASE ADD STANDBY LOGFILE GROUP 5(C:oracleproduct10.2.0oradatah10gstdy_redo05.log) SIZE 50M; SQLALTER DATABASE ADD STANDBY LOGFILE GROUP 6(C:oracleproduct10.2.0oradatah10gstdy_redo06.log) SIZE 50M; SQLALTER DATABASE ADD STANDBY LOGFILE GROUP 7(C:oracleproduct10.2.0oradatah10gstdy_redo07.log) SIZE 50M;5.3 在downstream site上检查一下是否建立成功：SQL SELECT GROUP#, THREAD#, SEQUENCE#, ARCHIVED, STATUS FROM V$STANDBY_LOG; GROUP# THREAD# SEQUENCE# ARCHIV STATUS- - - - - 4 0 0 YES UNASSIGNED 5 0 0 YES UNASSIGNED 6 0 0 YES UNASSIGNED 7 0 0 YES UNASSIGNED6. 准备source site的参数：SQLALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=SPFILE;SQLALTER SYSTEM SET LOG_ARCHIVE_DEST_2=SERVICE=h10g LGWR SYNC NOREGISTER VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=h10g SCOPE=SPFILE;7. 开始stream配置：7.1在downstream site设置stream queue：SQLconn strmadmin/strmadminSQLBEGIN DBMS_STREAMS_ADM.SET_UP_QUEUE( queue_table = strmadmin.DOWNSTREAM_Q_TABLE, queue_name = strmadmin.DOWNSTREAM_Q, queue_user = STRMADMIN); END; / SQL select name,queue_table,QUEUE_TYPE,USER_COMMENT from user_queuesNAME QUEUE_TABLE QUEUE_TYPE USER_COMMENT- - - -AQ$_DOWNSTREAM_Q_TABLE_E DOWNSTREAM_Q_TABLE EXCEPTION_QUEUE exception queueDOWNSTREAM_Q DOWNSTREAM_Q_TABLE NORMAL_QUEUE注意，从最佳实践出发，建议downstream设置一个queue，而不是多个queue。这一个queue将用于catpure和apply的queue，能避免分发和queue to queue传输的冗余。7.2 在downstream site创建apply进程：SQLconn strmadmin/strmadminSQLBEGIN DBMS_APPLY_ADM.CREATE_APPLY( queue_name = strmadmin.DOWNSTREAM_Q, apply_name = DOWNSTREAM_APPLY, apply_captured = TRUE);END;/7.3 检查 apply的信息：SQL SELECT apply_name, status, queue_name FROM DBA_APPLY where APPLY_NAME=DOWNSTREAM_APPLY;SQL SELECT parameter, value, set_by_user FROM DBA_APPLY_PARAMETERS WHERE apply_name = DOWNSTREAM_APPLY;7.4 在downstream site创建capture进程：SQLconn strmadmin/strmadminSQLBEGIN DBMS_CAPTURE_ADM.CREATE_CAPTURE( queue_name = strmadmin.DOWNSTREAM_Q, capture_name = DOWNSTREAM_CAPTURE, rule_set_name = NULL, start_scn = NULL, source_database = prod, use_database_link = true, first_scn = NULL, logfile_assignment = implicit); END; /7.5 检查capture的信息：SQL SELECT capture_name, status from dba_capture;SQL SELECT parameter, value, set_by_user FROM DBA_CAPTURE_PARAMETERS;8. 为实时变动设置capture：8.1 在downstream site：SQL conn strmadmin/strmadminSQL BEGIN DBMS_CAPTURE_ADM.SET_PARAMETER( capture_name = DOWNSTREAM_CAPTURE, parameter = downstream_real_time_mine, value = y); END; / 8.2 在downstream site设置rule规则：SQL BEGIN DBMS_STREAMS_ADM.ADD_SCHEMA_RULES( schema_name = test, streams_type = capture, streams_name = downstream_capture, queue_name = strmadmin.downstream_q, include_dml = true, include_ddl = true, include_tagged_lcr = false, source_database = prod, inclusion_rule = TRUE); END; /8.3 在downstream site检查rule设置情况：SQL SELECT rule_name, rule_condition FROM DBA_STREAMS_SCHEMA_RULES WHERE streams_name = DOWNSTREAM_CAPTURE AND streams_type = CAPTURE;9. 实例化需要同步的owner：9.1 在source site：SQL create user test identified by test default tablespace users;SQL grant connect, resource, create table to test;9.2 在downstream site：SQL create user test identified by test default tablespace users;SQL grant