| need help with oracle stream [message #377941] |
Fri, 26 December 2008 12:19  |
amitchhabra1981
Messages: 12
Registered: December 2008
Location: NOIDA
|
Junior Member |
|
|
hi i am amit i am working on a project in which i have to transfer data from one schema to another schema in a same database with help of oracle stream but i dont have clue how i will work on oracle stream.
so, please help me.
thank u
amit 
|
|
|
|
|
|
|
|
|
|
| Re: need help with oracle stream [message #378246 is a reply to message #378058] |
Mon, 29 December 2008 08:03  |
amitchhabra1981
Messages: 12
Registered: December 2008
Location: NOIDA
|
Junior Member |
|
|
hi michel,
I Followed these steps:
1. Enable ARCHIVELOG MODE on both database
2. Create Stream administrator User
Source Database: DB1
SQL> conn sys@db1 as sysdba
Enter password:
Connected.
SQL> create user strmadmin identified by strmadmin;
User created.
SQL> grant connect, resource, dba to strmadmin;
Grant succeeded.
SQL> grant select_catalog_role, select any dictionary to strmadmin;
Grant succeeded.
Target Database: DB2
SQL> conn sys@db2 as sysdba
Enter password:
Connected.
SQL> create user strmadmin identified by strmadmin;
User created.
SQL> grant connect, resource, dba to strmadmin;
Grant succeeded.
SQL> grant select_catalog_role, select any dictionary to strmadmin;
Grant succeeded.
3. Setup INIT parameters
Source Database: DB1
SQL> conn sys@db1 as sysdba
Enter password:
Connected.
SQL> alter system set global_names=true;
System altered.
SQL> alter system set streams_pool_size = 100 m;
System altered.
Target Database: DB2
SQL> conn sys@db2 as sysdba
Enter password:
Connected.
SQL> alter system set global_names=true;
System altered.
SQL> alter system set streams_pool_size = 100 m;
System altered.
4. Create Database Link
Target Database: DB1
SQL> conn strmadmin/strmadmin@db1
Connected.
SQL> create database link db2
2 connect to strmadmin
3 identified by strmadmin
4 using 'DB2';
Database link created.
Source Database: DB2
SQL> conn strmadmin/strmadmin@db2
Connected.
SQL> create database link db1
2 connect to strmadmin
3 identified by strmadmin
4 using 'DB1';
Database link created.
5. Setup Source and Destination queues
Source Database: DB1
SQL> conn strmadmin/strmadmin@db1
Connected.
SQL> EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();
PL/SQL procedure successfully completed.
Target Database: DB2
SQL> conn strmadmin/strmadmin@db2
Connected.
SQL> EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();
PL/SQL procedure successfully completed.
6. Setup Schema for streams
SQL> conn scott/tiger@db1
Connected.
SQL> create table TAJ ( no number primary key,name varchar2(20),ddate date);
Table created.
Target Database: DB2
SQL> conn sys@db2 as sysdba
Enter password:
Connected.
SQL> conn scott/tiger@db2
Connected.
SQL> create table TAJ ( no number primary key,name varchar2(20),ddate date);
Table created.
7. Setup Supplemental logging at the source database
Source Database: DB1
SQL> conn scott/tiger@db1
Connected.
SQL> alter table taj
2 add supplemental log data (primary key,unique) columns;
Table altered.
8. Configure capture process at the source database
Source Database: DB1
SQL> conn strmadmin/strmadmin@db1
Connected.
SQL> begin dbms_streams_adm.add_table_rules
2 ( table_name => 'scott.taj',
3 streams_type => 'capture',
4 streams_name => 'capture_stream',
5 queue_name=> 'strmadmin.streams_queue',
6 include_dml => true,
7 include_ddl => true,
8 inclusion_rule => true);
9 end;
10 /
PL/SQL procedure successfully completed.
9. Configure the propagation process
Source Database: DB1
SQL> conn strmadmin/strmadmin@db1
Connected.
SQL> begin dbms_streams_adm.add_table_propagation_rules
2 ( table_name => 'scott.taj',
3 streams_name => 'DB1_TO_DB2',
4 source_queue_name => 'strmadmin.streams_queue',
5 destination_queue_name => 'strmadmin.streams_queue@DB2',
6 include_dml => true,
7 include_ddl => true,
8 source_database => 'DB1',
9 inclusion_rule => true);
10 end;
11 /
PL/SQL procedure successfully completed.
10. Set the instantiation system change number (SCN)
Source Database: DB1
SQL> CONN STRMADMIN/STRMADMIN@DB1
Connected.
SQL> declare
2 source_scn number;
3 begin
4 source_scn := dbms_flashback.get_system_change_number();
5 dbms_apply_adm.set_table_instantiation_scn@DB2
6 ( source_object_name => 'scott.taj',
7 source_database_name => 'DB1',
8 instantiation_scn => source_scn);
9 end;
10 /
PL/SQL procedure successfully completed.
11. Configure the apply process at the destination database
Target Database: DB2
SQL> conn strmadmin/strmadmin@db2
Connected.
SQL> begin dbms_streams_adm.add_table_rules
2 ( table_name => 'scott.taj',
3 streams_type => 'apply',
4 streams_name => 'apply_stream',
5 queue_name => 'strmadmin.streams_queue',
6 include_dml => true,
7 include_ddl => true,
8 source_database => 'DB1',
9 inclusion_rule => true);
10 end;
11 /
PL/SQL procedure successfully completed.
12. Start the capture and apply processes
Source Database: DB1
SQL> conn strmadmin/strmadmin@db1
Connected.
SQL> begin dbms_capture_adm.start_capture
2 ( capture_name => 'capture_stream');
3 end;
4 /
PL/SQL procedure successfully completed.
Target Database: DB2
SQL> conn strmadmin/strmadmin@db2
Connected.
SQL> begin dbms_apply_adm.set_parameter
2 ( apply_name => 'apply_stream',
3 parameter => 'disable_on_error',
4 value => 'n');
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> begin
2 dbms_apply_adm.start_apply
3 ( apply_name => 'apply_stream');
4 end;
5 /
PL/SQL procedure successfully completed.
NOTE: Stream replication environment is ready, just needed to test it.
SQL> conn scott/tiger@db1
Connected.
SQL> --DDL operation
SQL> alter table taj add (flag char(1));
Table altered.
SQL> --DML operation
SQL> begin
2 insert into taj values (1,'first_entry',sysdate,1);
3 commit;
4 end;
5 /
Now after changes on DB1 in table TAJ it should replicate the changes on TAJ@DB2 but its doing nothing. Please let me know if we are doing something wrong. Also please let me know if we need to make some changes for schema to schema transfer in single database.
|
|
|
|
| Re: need help with oracle stream [message #378276 is a reply to message #378246] |
Mon, 29 December 2008 13:18 |
varunvir
Messages: 389
Registered: November 2007
|
Senior Member |
|
|
create separate permanent and temporary tablespaces and assign to strmadmin.
Then create logminer dict by following command:
Begin
Dbms_logmnr_d.set_tablespace('logmnr_tbs');
End;
/
Grnat following privileges to strmadmin
grant dba,select_catalog_role to strmadmin;
exec dbms_streams_auth.grant_admin_privilege('strmadmin',true);
Moreover you did set any parameters for capture and apply process.Set all the parameters for capture and apply and then restart.
Check the value of job_queue_process--This value should be
higher and 2 in stream environment.
Regards,
Varun Punj
|
|
|
|
| Re: need help with oracle stream [message #378334 is a reply to message #378276] |
Tue, 30 December 2008 01:13 |
amitchhabra1981
Messages: 12
Registered: December 2008
Location: NOIDA
|
Junior Member |
|
|
Hi Varun,
Thanks for your reply.
exec dbms_streams_auth.grant_admin_privilege('strmadmin',true);
is this command mandatory? i am not getting privilage related issue without executing this command.
if its mandatory then please let me know the alternate of this command becouse when i am executing this on my database(Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Prod) it showing incompatibility error.
Thanks
Amit
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
