Database Software

hi, all
I am new in the streaming part of oracle, I have two database in vmware , I want to do online replication of the database. one is the main database and the other acts as backup
I am using oracle tried for schema level streaming ,I have used orcl1=> orcl2, All of the databases were on Oracle 10.2.0.4,But I am facing any dml/ddl changes to orcl1 is not going to orcl2
i follwed the following procedure:
main database:
Operating System:ORACLE ENTERPRISE LINUX 5.3
IP addr:10.3.1.31
sid:orcl1
global_name=orcl1.oracle.com

bak database:
Operating System:ORACLE ENTERPRISE LINUX 5.3
IP addr:10.3.1.32
sid:orcl2
global_name=orcl2.oracle.com

1>Set initialization parameters,the main and bak database execute the following statements
Sqlplus ‘/ as sysdba’
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;

2>Set database to archive mode(main and bak)
sqlplus ‘/ as sysdba’
alter system set log_archive_dest_1=’location=/opt/stream/arch‘ 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;

3>Create Environmental stream manage user (main and bak)
connect / as sysdba
#Stream the main environment to create specific table space
create tablespace tbs_stream datafile '/yang/oradata/prod/tbs_stream01.dbf' size 100m autoextend on maxsize unlimited segment space management auto;

#Be logminer the data dictionary tablespace from system transferred to the new tablespace,tablespace to prevent the system stays full
execute dbms_logmnr_d.set_tablespace('tbs_stream');

#Create Stream manage user
create user strmadmin identified by strmadmin default tablespace tbs_stream temporary tablespace temp;

#authorized user
grant connect,resource,dba,aq_administrator_role to strmadmin;

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

4>Configure network connections
#Configure the main environmental tnsnames.ora
orcl2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.3.1.32 )(PORT = 1521))
)
(CONNECT_DATA =
(SID = orcl2)
(SERVER = DEDICATED)
)
)
#Configure the bak environmental tnsnames.ora
orcl1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.3.1.31 )(PORT = 1521))
)
(CONNECT_DATA =
(SID = orcl1)
(SERVER = DEDICATED)
)
)

5>Create db_link
#Create main database link
connect strmadmin/strmadmin
create database link orcl2 connect to strmadmin identified by strmadmin using 'orcl2';

#Create bak database link
connect strmadmin/strmadmin
create database link orcl1 connect to strmadmin identified by strmadmin using 'orcl1'

6>Create master stream queue
#To strmadmin status ,log on the main database

conn strmadmin/strmadmin
begin
dbms_streams_adm.set_up_queue(
queue_table => 'orcl_queue_table',
queue_name => 'orcl_queue');
end;
/

7>Create bak stream queue
#To strmadmin status ,log on the bak database

connect strmadmin/strmadmin
begin
dbms_streams_adm.set_up_queue(
queue_table => 'orcl2_queue_table',
queue_name => 'orcl2_queue');
end;
/

8>Create capture process
#To strmadmin status ,log on the main database **A reminder,this document in order to test the user do an example

connect strmadmin/strmadmin
begin
dbms_streams_adm.add_schema_rules(
schema_name => 'test',
streams_type => 'capture',
streams_name => 'capture_orcl1',
queue_name => 'strmadmin.orcl_queue',
include_dml => true,
include_ddl => true,
include_tagged_lcr => false,
source_database => null,
inclusion_rule => true);
end;
/

9>Instantiated database
exp test/test@orcl1 file='/tmp/test.dmp' object_consistent=y rows=y
imp system/oracle@orcl2 file='/tmp/test.dmp' ignore=y commit=y log='/tmp/test.log' streams_instantiation=y fromuser=test touser=test;

10>Create propagation process
#To strmadmin status ,log on the main database

connect strmadmin/strmadmin
begin
dbms_streams_adm.add_schema_propagation_rules(
schema_name => 'test',
streams_name => 'orcl1_to_orcl2',
source_queue_name => 'strmadmin.orcl1_queue',
destination_queue_name => 'strmadmin.orcl2_queue@orcl2',
include_dml => true,
include_ddl => true,
include_tagged_lcr => false,
source_database => 'orcl1',
inclusion_rule => true);
end;
/

# alter propagation sleep time=0
begin
dbms_aqadm.alter_propagation_schedule(
queue_name => 'orcl1_queue',
destination => 'orcl2',
latency => 0);
end;
/

11>Create apply procee
#To strmadmin status ,log on the bak database
connect strmadmin/strmadmin
begin
dbms_streams_adm.add_schema_rules(
schema_name => 'test',
streams_type => 'apply',
streams_name => 'apply_orcl2',
queue_name => 'strmadmin.orcl2_queue',
include_dml => true,
include_ddl => true,
include_tagged_lcr => false,
source_database => 'orcl',
inclusion_rule => true);
end;
/

12>startup stream

#To strmadmin status ,log on the bak database
connect strmadmin/strmadmin
begin
dbms_apply_adm.start_apply(
apply_name => 'apply_orcl2');
end;
/

#To strmadmin status ,log on the main database
connect strmadmin/strmadmin
begin
dbms_capture_adm.start_capture(
capture_name => 'capture_orcl');
end;
/

****TEST***
#To test status,log on the main database
1.create table t1(id number primary key,name varchar2(40));
2.insert into t1 values(1,'a');
3.insert into t1 values(2,'b');
4.insert into t1 values(3,'c');
5commit;

#To test status,log on the bak database
SQL> select * from tab;
no rows selected

#To strmadmin status,log on the main database
SQL> SELECT CAPTURE_NAME,
2 QUEUE_NAME,
3 RULE_SET_NAME,
4 NEGATIVE_RULE_SET_NAME,
5 STATUS
6 FROM DBA_CAPTURE;

CAPTURE_NAME QUEUE_NAME RULE_SET_NAME NEGATIVE_RULE_SET_NAME STATUS
------------------------------ ------------------------- ----------------------------------- ------------------------------ --------

CAPTURE_STREAM STREAMS_QUEUE RULESET$_3 ENABLED


SQL> SELECT CAPTURE_NAME, QUEUE_NAME, STATUS, CAPTURED_SCN, APPLIED_SCN
2 FROM DBA_CAPTURE;
CAPTURE_NAME QUEUE_NAME STATUS CAPTURED_SCN APPLIED_SCN
------------------------------ ------------------------------ -------- ------------ -----------
CAPTURE_STREAM STREAMS_QUEUE ENABLED 554206 554206


#To strmadmin status,log on the bak database
SELECT apply_name, apply_captured, status FROM dba_apply;
APPLY_NAME APP STATUS
------------------------------ --- --------
APPLY_STREAM YES ENABLED

SQL> select apply_name,message_number,error_message from dba_apply_error;
no rows selected

Thanks and looking forward for valuable suggestions from Gurus

Edited by: user11267696 on 2009-9-11 下午4:02