ORACLE单向全库streams上端捕获 配置
                   （dbms_streams_adm.maintain_globa）

开始时ORACLE 版本为10.2.0.1 后期配置是发现存在BUG ，建议实施之前先安装数据库软件，然后升级数据库软件为10.2.0.5，然后建库配置Streams;


网络环境：
hostname           ipaddresss       instance_name      archive mode  sample schema
host1.abc.com       192.168.1.64     orcl                 archivelog    install
host2.abc.com       192.168.1.64     orcl2                archivelog    no install
（本测试建库时未启用em)


确保两节点的$ORACLE_HOME/network/admin/tnsnames.ora文间中有下列内容：
[oracle@host1 admin]$ more tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = host1.abc.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

ORCL2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = host2.abc.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl2)
    )
  )

通过netmgr 编辑两实例的$ORACLE_HOME/network/admin/listener.ora 如下：
host1.abc.com      orcl
[oracle@host1 admin]$ pwd
/u01/app/oracle/product/10.2.0/db_1/network/admin
[oracle@host1 admin]$ more listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
      (SID_NAME = orcl)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = host1.abc.com)(PORT = 1521))
    )
  )






EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )


host2.abc.com  orcl2

[oracle@host2 admin]$ pwd
/u01/app/oracle/product/10.2.0/db_1/network/admin
[oracle@host2 admin]$ more listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = orcl2)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
      (SID_NAME = orcl2)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = host2.abc.com)(PORT = 1521))
    )
  )
开始配置单向全库源端streams：
调整源库及目标库的部分参数设置（两节点均操作）：
alter system set global_names=true scope=spfile;

alter database add supplemental log data(primary key,unique) columns;

alter system set job_queue_processes=5 scope=both;

alter system set aq_tm_processes=4 scope=both;

alter system set sga_max_size=1500m scope=spfile;

alter system set sga_target=1024m scope=spfile;

alter system set undo_retention=2700 scope=spfile;

alter system set streams_pool_size=300m scope=both;

alter system set logmnr_max_persistent_sessions=1 scope=spfile;

注意：与复制有关的2个参数：

如果等了足够长的时间发现数据没有复制过来，仔细检查了capture/propagation/apply各进程的状态都是正常的， 并检查参数.

alter system set "_job_queue_interval"=1 scope=spfile;

并且将aq_tm_processes参数改为1（我原来这是为10）

alter system set aq_tm_processes=1;

改完后重启，发现数据就可以去了。这个隐含参数只是控制对job队列的检查频率，默认5秒。注意 undo_retention 参数要有足够的时间，undo 表空间要大,sga ,pga 如果用db_recovery_file_dest_size，那么 要设置的足够大。
归档目的地可用默认的DB_RECOVERY_FILE_DEST 或创建归档目录然后指定为LOG_ARCHIVE_DEST_1，在使用db_recovery_file_dest时，要调整db_recovery_file_dest_size 参数足够大，否则默认当db_recovery_file_dest使用率达到一定百分比后数据库会自动删除归档日志文件。
调整UNDO可用如下命令：
show parameter undo;
SELECT FILE_NAME FROM DBA_DATA_FILES WHERE TABLESPACE='UNDOTBS1';

SELECT SUM(BYTES)/1024/1024
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME='UNDOTBS1';

alter database databasename datafile 'datafilename' resize 2048M;
源库
connect /as sysdba



create tablespace streams_tbs
   datafile '/u01/app/oracle/oradata/orcl/streams01.dbf'
   size 400m;

create user strmadmin identified by strmadmin
   default tablespace streams_tbs
   quota unlimited on streams_tbs;

grant connect,resource,aq_administrator_role,dba to strmadmin;
grant execute on dbms_lock to strmadmin;
execute dbms_streams_auth.grant_admin_privilege('strmadmin');

create directory dir_sou as '/u01/app/oracle/db0'; -----此目录db0要存在
grant read,write on directory dir_sou to strmadmin;

connect strmadmin/strmadmin
create database link orcl2
connect to strmadmin identified by strmadmin using 'orcl2';




备库
connect /as sysdba



create tablespace streams_tbs
   datafile '/u01/app/oracle/oradata/orcl2/streams01.dbf'
   size 400m;

create user strmadmin identified by strmadmin
   default tablespace streams_tbs
   quota unlimited on streams_tbs;

grant connect,resource,aq_administrator_role,dba to strmadmin;
grant execute on dbms_lock to strmadmin;
execute dbms_streams_auth.grant_admin_privilege('strmadmin');

create directory dir_des as '/u01/app/oracle/db1'; -----此目录db1要存在
grant read,write on directory dir_des to strmadmin;

connect strmadmin/strmadmin;
create database link orcl
connect to strmadmin identified by strmadmin using 'orcl';

源库：
connect srmadmin/strmadmin

begin
   dbms_streams_adm.maintain_global(
   source_directory_object=>'dir_sou',
   destination_directory_object=>'dir_des',
   source_database=>'orcl',
   destination_database=>'orcl2',
   perform_actions=>true,
   dump_file_name=>'stream_replicate.dmp',
   bi_directional=>false,
   include_ddl=>true,
   instantiation=>dbms_streams_adm.instantiation_full);
   end;
   /

关闭操作：
1）源库  
select capture_name,status from dba_capture;
exec dbms_capture_adm.stop_capture(capture_name=>'capturename');
select propagation_name,status from dba_propagation;
exec dbms_propagation_adm.stop_propagation(propagation_name=>'propagationname');
2)目标库
select apply_name,status from dba_apply;
exec dbms_apply_adm.stop_apply(apply_name=>'applyname');

开启操作：
1)目标库
select apply_name,status from dba_apply;
exec dbms_apply_adm.start_apply(apply_name=>'applyname');
2)源库
select propagation_name,status from dba_propagation;
exec  dbms_propagation_adm.start_propagation(propagation_name=>'propagationname');
select capture_name,status from dba_capture;
exec  dbms_capture_adm.start_capture(capture_name=>'capturename');




遇到的BUG 及处理方法；

Oracle 10.2.0.4 数据库级Stream 复制遭遇ORA-26744 ORA-26773
刚刚创建的流复制的alert里发现如下错误：

Errors in file /u01/app/admin/menhu/bdump/menhu_c001_20705.trc:
ORA-26744: STREAMS capture process "RIMARY$CAP" does not support "SYSMAN"."MGMT_METRICS_RAW" because of the following reason:
ORA-26773: Invalid data type for column "malformed redo"


流复制的bug实在是多- -，这次又是一个，发现DBA_APPLY里status变成Aborted了，然后一看alert，mentalink了下这个 MGMT_METRICS_RAW，发现
是和db control有关的东西，只能重新创建数据库，禁用了control，果然就好了。。。。。




下面的两个问题都是BUG，第二个问题不影响使用;

遇到的问题一：
                 Streams Capture Process aborts with ORA-26773: Invalid Data Type "Malformed Redo" [ID 601557.1]               
________________________________________

In this Document

Applies to:
Oracle Server - Enterprise Edition - Version 10.2.0.1 to 11.1.0.6 [Release 10.2 to 11.1]
Information in this document applies to any platform.
Symptoms
Streams Capture Process aborts with the following errors

ORA-26744: STREAMS capture process "CAPTURE_SITE1" does not support "EMS"."ISSUE" because of the following reason: ORA-26773: Invalid data type for column "malformed redo"

Changes
Cause
The cause of this problem has been identified in Bug 6355481. The malformed redo is attributed to the following conditions:

1. A before update trigger exists on the problem table;
2. In the trigger, a column of the table being updated is updated conditionally;
3. The values of the update dml do not satisfy the condition in the before-update trigger, so no change is actually made in the trigger;
4. The row being updated needs to be chained and the change is done to the head row piece.

Solution
1. Apply 10.2.0.5 when it becomes available or upgrade to 11.1.0.6 onwards.
10.2.0.4 already has the 'fix' for this problem
References
BUG:6355481 - ORA-26744 / ORA-26773: INVALID DATA TYPE FOR COLUMN "MALFORMED REDO"

•        Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition > Streams (Replication and Messaging)
                错误       

       
ORA-26744; ORA-26773




遇到的问题二：


                ID        

       

                 Using DBMS_REDEFINITION on Streams table on source site causes Missing Streams Multi-Version Data Dictionary MVDD [ID 758285.1]         转到底部

________________________________________
修改时间:2012-2-21 类型ROBLEM 状态UBLISHED 优先级:3         注释 (0)
         
                              



In this Document
Symptoms
Cause
Solution
References
________________________________________
Applies to:
Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 11.1.0.7 - Release: 10.2 to 11.1
Information in this document applies to any platform.
Oracle Server Enterprise Edition - Version: 10.2.0.1 to 11.1.0.7
***Checked for relevance on 21-FEB-2012***
Symptoms
When using the DBMS_REDEFINITION to reorganize tables in the streams system you get errors in the alert log file as following:
knlc_ProcessMVDD-1: MISSING Streams multi-version data dictionary!!!
knlldmm: gdbnm=TEST.WORLD
knlldmm: objn=1167980
knlldmm: objv=5
knlldmm: scn=8311667957867
knlldmm: opnum=6
knlc_ProcessMVDD-1: MISSING Streams multi-version data dictionary!!!
knlldmm: gdbnm=TEST.WORLD
knlldmm: objn=1167980
knlldmm: objv=5
knlldmm: scn=8311667957867
knlldmm: opnum=6

Cause
The cause of this issue has been identified and verified in Bug 7257038.

Solution
To implement the solution, please execute the following steps:
1) Apply Patch 7257038.

Note: If the patch is not available on the My Oracle Support apply the latest patch set and open Service Request with Oracle support asking for one off Back port for your platform.


The patches available on the My Oracle Support are:

On 10.2.0.3.0:
Linux x86.

On 10.2.0.4.0:
Linux x86.
Linux x86-64.
HP-UX Itanium.
Oracle Solaris on SPARC (64-bit).
IBM AIX on POWER Systems (64-bit).

On 10.2.0.4.3:
Linux x86.
IBM AIX on POWER Systems (64-bit).

On 10.2.0.4.4:
Linux x86.
IBM AIX on POWER Systems (64-bit).

On 11.1.0.7.0:
Linux x86.
Linux x86-64.
Oracle Solaris on SPARC (64-bit).

2) Bug 7257038 is fixed on the following:

12.1 (Future Release)
11.2 (Server Patch Set).
11.1.0.7.2 (Patch Set Update)
10.2.0.5.0 (Server Patch Set)
11.1.0.7.0 Patch 22 on Windows Platforms


Note that if you hit Bug 7257038 and you are using Conflict resolution using Apply handler you need to review and follow Note 781081.1 Conflict resolution using Apply handler is no longer available after using DBMS_REDEFINITION on Streams table at source site.
References
BUG:7257038 - USING DBMS_REDEFINITION ON STREAMS TABLE CAUSES "MISSING STREAMS MVDD"
NOTE:781081.1 - Conflict resolution using Apply handler is no longer available after using DBMS_REDEFINITION on Streams table at source site.


                         相关内容       

       
                产品       

       
•        Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition > Streams (Replication and Messaging)
                关键字       

       
64-BIT; CONFLICT RESOLUTION; DATA DICTIONARY; DBMS_REDEFINITION; SCN; SOLARIS; STREAMS; TABLE.RTF; VERSION

下端实时捕获设置：




一、在捕获进程所在库创建standby redo logfile(目标库）
alter database add standby logfile group 4 ('/u01/app/oracle/oradata/stdby04.dbf') size 50m;

alter database add standby logfile group 5 ('/u01/app/oracle/oradata/stdby05.dbf') size 50m;

alter database add standby logfile group 6 ('/u01/app/oracle/oradata/stdby06.dbf') size 50m;

alter database add standby logfile group 7 ('/u01/app/oracle/oradata/stdby07.dbf') size 50m;


二、在源库修改日志传送方式
alter system set log_archive_dest_2='service=orcl2 lgwr async noregister';

三、修改capture进程属性，启用实时捕获 （目标库）
BEGIN
DBMS_CAPTURE_ADM.SET_PARAMETER(
capture_name => 'DB0$CAP',
parameter => 'downstream_real_time_mine',
value => 'y');
END;
/

四、在源库归档当前日志，使得capture进程切换到standby redo logfile中捕获数据

STRMADM@ning>alter system archive log current;


备注：为了不是用默认的db_recovery_file_dest,避免自动删除归档日志，可做如下设置，或者使用默认的db_recovery_file_dest 但db_recovery_file_dest_size要有足够的空间大小以避免上述现象发生

源库：
SQL> alter system set log_archive_dest_1='location=/u01/app/oracle/arch' scope=both;

System altered.

SQL> alter system set log_archive_dest_2='service=orcl2 arch noregister' scope=both;

System altered.


目标库：

SQL> alter system set log_archive_dest_1='location=/u01/app/oracle/arch' scope=both;

下端实时捕获设置：




一、在捕获进程所在库创建standby redo logfile(目标库）
alter database add standby logfile group 4 ('/u01/app/oracle/oradata/stdby04.dbf') size 50m;

alter database add standby logfile group 5 ('/u01/app/oracle/oradata/stdby05.dbf') size 50m;

alter database add standby logfile group 6 ('/u01/app/oracle/oradata/stdby06.dbf') size 50m;

alter database add standby logfile group 7 ('/u01/app/oracle/oradata/stdby07.dbf') size 50m;


二、在源库修改日志传送方式
alter system set log_archive_dest_2='service=orcl2 lgwr async noregister';

三、修改capture进程属性，启用实时捕获 （目标库）
BEGIN
DBMS_CAPTURE_ADM.SET_PARAMETER(
capture_name => 'DB0$CAP',
parameter => 'downstream_real_time_mine',
value => 'y');
END;
/

四、在源库归档当前日志，使得capture进程切换到standby redo logfile中捕获数据

STRMADM@ning>alter system archive log current;


备注：为了不是用默认的db_recovery_file_dest,避免自动删除归档日志，可做如下设置，或者使用默认的db_recovery_file_dest 但db_recovery_file_dest_size要有足够的空间大小以避免上述现象发生

源库：
SQL> alter system set log_archive_dest_1='location=/u01/app/oracle/arch' scope=both;

System altered.

SQL> alter system set log_archive_dest_2='service=orcl2 arch noregister' scope=both;

System altered.


目标库：

SQL> alter system set log_archive_dest_1='location=/u01/app/oracle/arch' scope=both;