Stream Replication

Stream Troubleshooting
Summary

SELECT CAPTURE_NAME, STATUS_CHANGE_TIME, ERROR_NUMBER, ERROR_MESSAGE
  FROM DBA_CAPTURE WHERE STATUS='ABORTED';

@?/demo/schema/mkplug.sql
select * from global_name;
http://www.eygle.com/archives/2007/11/howto_change_global_name.html
create database link db10g.ocm01 connect to strmadmin identified by strmadmin using 'ocm01';
alter database rename global_name to db10g.ocm01;
alter database rename global_name to db10g.ocm;
Senario Description:
Host: OCM
DB Instance:strm1(Oracle 10.2.0.1.0)

Step 1(required,both DB): set initial parameters

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;

shutdown immediate
startup open

Step 2(optional,both DB):set archive log mode
alter system set log_archive_dest_1='location=/archive/db_name' scope=spfile;
alter system set log_archive_start=true scope=spfile;
alter system set log_archive_format='arch%t_%s_%r.arc' scope=spfile;

shut down immediate;
startup mount;
alter database archivelog;
alter database open;
archive log list;

Step 3(both DB):prepare strmadmin schema
create tablespace tbs_stream datafile '/u01/app/oracle/oradata/db10g/tbs_stream01.dbf'
size 5m autoextend on maxsize unlimited segment space management auto;

execute dbms_logmnr_d.set_tablespace('tbs_stream');

create user strmadmin identified by strmadmin;

alter user strmadmin default tablespace tbs_stream
quota unlimited on tbs_stream;

grant connect,resource,dba,aq_administrator_role to strmadmin;

begin
dbms_streams_auth.grant_admin_privilege(
grantee => 'strmadmin',
grant_privileges => true);
end;

Step 4:set tnsname for both host
Host:OCM
strm1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = strm1)
    )
  )
strm2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.102)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = strm2)
    )
  )
Host:OCM01
strm1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.101)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = strm1)
    )
  )
strm2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = strm2)
    )
  )
EXEC DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION();
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License