重庆市中考满分作文-满分作文.docx

Stream的主要环节分为捕获（capture），分发（propagation）和应用（apply）。如果从应用这些环节所处的地方，可以分为：UpStreams（capture和propagation在source数据库，apply在destination数据库）。DownStream（capture和propagation，以及apply在destination数据库）如果成对象来分，可以分成：database级的streamowner级的streamtable级的stream下面我们来建立几个stream：（一）owner级UpStream：1. 在source和destination机（注以下操作需要在2个机器上进行）：1.1 创建stream用户的表空间：create tablespace tbs_stream datafile /oracle/app/oracle/oradata/stream02/tbs_stream02.dbf size 200M; 1.2 将logmnr的默认表空间转到刚刚建立的表空间：SQL execute dbms_logmnr_d.set_tablespace(tbs_stream);PL/SQL procedure successfully completed.SQL1.3 创建stream user和授权：SQL CREATE USER strmadmin IDENTIFIED BY strmadmin 2 DEFAULT TABLESPACE tbs_stream 3 QUOTA UNLIMITED ON tbs_stream; SQL grant dba to strmadmin;Grant succeeded.SQL SQL SQL BEGIN 2 DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE( 3 grantee = strmadmin, 4 grant_privileges = true);END;/ 5 6 PL/SQL procedure successfully completed.SQL 1.4 check stream admin用户的情况：SQL SELECT * FROM dba_streams_administrator;USERNAME LOCAL_ ACCESS- - -STRMADMIN YES YESSQL 1.6 查一下arch目录是否设置正确，是否启动归档模式。1.7 设置tnsname，能tnsping到对方的机器。1.8 设置global_namesSQL Alter system set global_names=TRUE scope=BOTH; System altered.SQL 1.9 设置stream需要的初始化参数：alter 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; 1.10 建立dblink。在source机：create database link stream02 connect to strmadmin identified by strmadmin using stream02;在destination机：create database link stream01 connect to strmadmin identified by strmadmin using stream01;2. 在source数据库启用Database 追加日志alter database add supplemental log data;3.在source数据库创建stream队列：connect strmadmin/strmadminSQL exec dbms_streams_adm.set_up_queue; PL/SQL procedure successfully completed.注意如果没有指定queue_table和queue_name，数据库是默认给queue_name为STREAMS_QUEUE_TABLE ，queue_name为STREAMS_QUEUE： SQL select owner,queue_table,name from dba_queues where owner=STRMADMIN;OWNER QUEUE_TABLE NAME- - -STRMADMIN STREAMS_QUEUE_TABLE STREAMS_QUEUESTRMADMIN STREAMS_QUEUE_TABLE AQ$_STREAMS_QUEUE_TABLE_ESQL 4. 在destination数据库创建stream队列：connect strmadmin/strmadminbegindbms_streams_adm.set_up_queue(queue_table = dest_queue_table,queue_name = dest_queue);end;/SQL select owner,queue_table,name from dba_queues where owner=STRMADMIN;OWNER QUEUE_TABLE NAME- - -STRMADMIN DEST_QUEUE_TABLE DEST_QUEUESTRMADMIN DEST_QUEUE_TABLE AQ$_DEST_QUEUE_TABLE_ESQL5. 在source数据库创建capture进程：connect strmadmin/strmadminbegindbms_streams_adm.add_schema_rules(schema_name = hr,streams_type = capture,streams_name = capture_stream01_to_stream02,queue_name = strmadmin.STREAMS_QUEUE,include_dml = true,include_ddl = true,include_tagged_lcr = false,source_database = null,inclusion_rule = true);end;/此时可以在source数据库看到有c001这样的进程.6.初始化需要做stream的schema：6.1 在source数据库导出：exp userid=hr/hr file=/oracle/hr.dmp object_consistent=y rows=y6.2 在destination数据库导入imp userid=system/oraclestream02 file=/oracle/hr.dmp ignore=y commit=y log=/oracle/hr.log streams_instantiation=y fromuser=hr touser=hr7.在source数据库创建schema级分发规则：begindbms_streams_adm.add_schema_propagation_rules(schema_name = hr,streams_name = propag_strm01_to_strm02,source_queue_name = strmadmin.STREAMS_QUEUE,destination_queue_name = strmadmin.dest_queuestream02,include_dml = true,include_ddl = true,include_tagged_lcr = false,source_database = stream01,inclusion_rule = true);end;/注意此时job queue进程开始工作（j00x）8. 在source数据库设置分发作业计划，latency = 0表示实时分发。begindbms_aqadm.alter_propagation_schedule(queue_name = STREAMS_QUEUE,destination = stream02,latency = 0);end;/9. 在destination数据库创建apply进程：begindbms_streams_adm.add_schema_rules(schema_name = hr,streams_type = apply,streams_name = apply_strm01_to_strm02,queue_name = strmadmin.dest_queue,include_dml = true,include_ddl = true,include_tagged_lcr = false,source_database = stream01,inclusion_rule = true);end;/注意此时有a001进程出现。10. 最后启动stream：10.1 在destination数据库启动apply：begindbms_apply_adm.start_apply(apply_name = apply_strm01_to_strm02);end;/10.2 在source数据库启动capture：begindbms_capture_adm.start_capture(capture_name = capture_stream01_to_stream02);end;/11. 停止stream：11.1 在source数据库停止capture：begindbms_capture_adm.stop_capture(capture_name = capture_stream01_to_stream02);end;/11.2 在destination数据库停止apply：begindbms_apply_adm.stop_apply(apply_name = apply_strm01_to_strm02);end;/（二） owner级downstream：1.11.10 同upstream。2. 在downstream site设置：ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=SPFILE;ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=LOCATION=/oracle/app/oracle/arch/standby_arch VALID_FOR=(STANDBY_LOGFILE,PRIMARY_ROLE) SCOPE=SPFILE;3. 在source site和downstream site同时设置：ALTER SYSTEM SET LOG_ARCHIVE_CONFIG=DG_CONFIG=(stream01,stream02) SCOPE=SPFILE;4. 从soruce site创建standby redo：4.1 在source库查询redo情况：SQL select THREAD#, GROUP#, BYTES/1024/1024 from V$LOG; THREAD# GROUP# BYTES/1024/1024- - - 1 1 50 1 2 50 1 3 50SQL注意创建standby redo时，大小要和source site的redo大小一致，数量上要比source site的redo多一个。4.2 在downstream site：ALTER DATABASE ADD STANDBY LOGFILE GROUP 4(/oracle/app/oracle/oradata/stream02/stdy_redo04.log) SIZE 50M;ALTER DATABASE ADD STANDBY LOGFILE GROUP 5(/oracle/app/oracle/oradata/stream02/stdy_redo05.log) SIZE 50M;ALTER DATABASE ADD STANDBY LOGFILE GROUP 6(/oracle/app/oracle/oradata/stream02/stdy_redo06.log) SIZE 50M;ALTER DATABASE ADD STANDBY LOGFILE GROUP 7(/oracle/app/oracle/oradata/stream02/stdy_redo04.log) SIZE 50M;4.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 UNASSIGNEDSQL5. 准备source site的参数：ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=SPFILE;ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=SERVICE=stream02 LGWR SYNC NOREGISTERVALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)DB_UNIQUE_NAME=stream02SCOPE=SPFILE;6. 在source site和downstream site同时设置参数：ALTER SYSTEM SET LOG_ARCHIVE_CONFIG=DG_CONFIG=(stream01,stream02) SCOPE=SPFILE;7. 开始stream配置：7.1 在source site创建需要复制的schema：SQL create user hjm identified by hjm default tablespace users;User created.SQL grant connect, resource, create table to hjm;Grant succeeded.SQL7.2 在downstream site设置stream queue：conn strmadmin/strmadminBEGINDBMS_STREAMS_ADM.SET_UP_QUEUE(queue_table = strmadmin.DOWNSTREAM_Q_TABLE,queue_name = strmadmin.DOWNSTREAM_Q,queue_user = STRMADMIN);END;/ 1* select name, queue_table,QUEUE_TYPE,USER_COMMENT from user_queuesSQL /NAME 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.3 在downstream site创建apply进程：conn strmadmin/strmadminBEGINDBMS_APPLY_ADM.CREATE_APPLY(queue_name = strmadmin.DOWNSTREAM_Q,apply_name = DOWNSTREAM_APPLY,apply_captured = TRUE);END;/7.4 检查 apply的信息：SQL SELECT apply_name, status, queue_name FROM DBA_APPLY where APPLY_NAME=DOWNSTREAM_APPLY;APPLY_NAME STATUS QUEUE_NAME- - -DOWNSTREAM_APPLY DISABLED DOWNSTREAM_QSQL SQL l 1 SELECT parameter, value, set_by_user 2 FROM DBA_APPLY_PARAMETERS 3* WHERE apply_name = DOWNSTREAM_APPLYSQL /PARAMETER VALUE SET_BY- - -ALLOW_DUPLICATE_ROWS N NOCOMMIT_SERIALIZATION FULL NODISABLE_ON_ERROR Y NODISABLE_ON_LIMIT N NOMAXIMUM_SCN INFINITE NOPARALLELISM 1 NOSTARTUP_SECONDS 0 NOTIME_LIMIT INFINITE NOTRACE_LEVEL 0 NOTRANSACTION_LIMIT INFINITE NOTXN_LCR_SPILL_THRESHOLD 10000 NOWRITE_ALERT_LOG Y NO12 rows selected.SQL7.5 在downstream site创建capture进程：conn strmadmin/strmadminBEGINDBMS_CAPTURE_ADM.CREATE_CAPTURE(queue_name = strmadmin.DOWNSTREAM_Q,capture_name = DOWNSTREAM_CAPTURE,rule_set_name = NULL,start_scn = NULL,source_database = stream01,use_database_link = true, first_scn = NULL,logfile_assignment = implicit); END;/7.6 检查capture的信息：SQL SELECT capture_name, status from dba_capture;CAPTURE_NAME STATUS- -DOWNSTREAM_CAPTURE DISABLEDSQL SQL SELECT parameter, value, set_by_user FROM DBA_CAPTURE_PARAMETERS;PARAMETER VALUE SET_BY- - -DISABLE_ON_LIMIT N NODOWNSTREAM_REAL_TIME_MINE N NOMAXIMUM_SCN INFINITE NOMESSAGE_LIMIT INFINITE NOPARALLELISM 1 NOSTARTUP_SECONDS 0 NOTIME_LIMIT INFINITE NOTRACE_LEVEL 0 NOWRITE_ALERT_LOG Y NO9 rows selected.SQL8. 为实时变动设置capture：8.1 在downstream site：conn strmadmin/strmadminSQL BEGIN 2 DBMS_CAPTURE_ADM.SET_PARAMETER( 3 capture_name = DOWNSTREAM_CAPTURE, 4 parameter = downstream_real_time_mine, 5 value = y); 6 END; 7 / PL/SQL procedure successfully completed.SQL8.2 在downstream site设置rule规则：SQL BEGIN 2 DBMS_STREAMS_ADM.ADD_SCHEMA_RULES( 3 schema_name = hjm, 4 streams_type = capture, 5 streams_name = downstream_capture, 6 queue_name = strmadmin.downstream_q, 7 include_dml = true, 8 include_ddl = true, 9 include_tagged_lcr = false,10 source_database = stream01,11 inclusion_rule = TRUE);12 END;13 /PL/SQL procedure successfully completed.SQL8.3 在downstream site检查rule设置情况：SQL l 1 SELECT rule_name, rule_condition 2 FROM DBA_STREAMS_SCHEMA_RULES 3 WHERE streams_name = DOWNSTREAM_CAPTURE 4* AND streams_type = CAPTURESQL /RULE_NAME RULE_CONDITION- -HJM15 (:dml.get_object_owner() = HJM) and :dml.is_null_tag() = Y and :dml.get_source_database_name() = STREAM01.REGRESS. RDBMS.DEV.US.ORACLE.COM )HJM16 (:ddl.get_object_owner() = HJM or :ddl.get_base_table_own er() = HJM) and :ddl.is_null_tag() = Y and :ddl.get_sour ce_database_name() = STREAM01.REGRESS.RDBMS.DEV.US.ORACLE.C OM )SQL9. 实例化需要同步的owner：9.1 在source site：exp system/oracle owner=hjm file=hjm.dump log=hjm.log object_consistent=Y9.2 在downstream site：SQL create user hjm identified by hjm default tablespace users;User created.SQL grant connect, resource, create table to hjm;Grant succeeded.SQL9.3 在source site：imp system/oraclestream02 file=hjm.dump full=y ignore=y STREAMS_INSTANTIATION=Y10. 在downstream site开始启动apply进程：conn strmadmin/strmadminSQL exec DBMS_APPLY_ADM.START_APPLY(apply_name = DOWNSTREAM_APPLY);PL/SQL procedure successfully completed.10.1 检查 apply情况：SQL select apply_name, status from dba_apply where APPLY_NAME=DOWNSTREAM_APPLY;APPLY_NAME STATUS- -DOWNSTREAM_APPLY ENABLEDSQL可以看到已经从disbale变成enabled。11. 在downstream site启动capture进程：conn strmadmin/strmadminSQL exec DBMS_CAPTURE_ADM.START_CAPTURE(capture_name = DOWNSTREAM_CAPTURE);PL/SQL procedure successfully completed.SQL11.1 检查 capture情况：SQL select capture_name, status from dba_capture;CAPTURE_NAME STATUS- -DOWNSTREAM_CAPTURE ENABLEDSQL看到capture的status也从disable变成enabled了。至此，downstream建立完毕。【trouble shooting】：1.对于upstream，需要检查dba_capture, dba_propagation, dba_apply:-在source数据库：SQL l 1 select propagation_name as name,status,ERROR_MESSAGE from dba_propagation 2 union all 3* select CAPTURE_NAME,status,ERROR_MESSAGE from dba_captureSQL /NAME STATUS ERROR_MESSAGE- - -PROPAG_STRM01_TO_STRM02 ENABLEDCAPTURE_STREAM01_TO_STREAM02 ENABLEDSQL -在destination数据库： 1* SELECT apply_name,status,error_message from dba_applySQL /APPLY_NAME STATUS ERROR_MESSAGE- - -APPLY_STRM01_TO_STRM02 ENABLED以上均为enable说明各个进程正常；如果为disable，需要用dbms_capture_adm.start_capture/DBMS_PROPAGATION_ADM.start_propagation/dbms_apply_adm.start_apply包来进行启动；如果是abort，说明是异常终止，需要查对应的error message，排除错误后启动。案例一：upstream的apply进程和capture进程都正常，但是无法同步，查dba_propagation，发现异常：SQL l 1* select propagation_name,status,ERROR_MESSAGE from dba_propagationSQLPROPAGATION_NAME STATUS ERROR_MESSAGE- - -PROPAG_STRM01_TO_STRM02 DISABLED ORA-12541: TNS:no listener ORA-12541: TNS:no listener进一步查alertlog中发现：Sun Jul 17 06:01:02 2011Propagation Schedule for (STRMA