Home » Server Options » Streams & AQ » need help with oracle stream
need help with oracle stream [message #377941] Fri, 26 December 2008 12:19 Go to next message
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 Sad
Re: need help with oracle stream [message #377942 is a reply to message #377941] Fri, 26 December 2008 12:29 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
http://www.google.com/search?q=oracle+streams&ie=utf-8&oe=utf-8&aq=t&rls=org.mozilla:en-US:official&client=firefo x-a


start with reading documentation.
http://download.oracle.com/docs/cd/B19306_01/server.102/b14229/toc.htm
http://download.oracle.com/docs/cd/B19306_01/server.102/b14257/aq_intro.htm

[Updated on: Fri, 26 December 2008 12:36]

Report message to a moderator

Re: need help with oracle stream [message #378053 is a reply to message #377942] Sun, 28 December 2008 04:08 Go to previous messageGo to next message
amitchhabra1981
Messages: 12
Registered: December 2008
Location: NOIDA
Junior Member
Sir,

if u will through more light on technical aspect of stream that will be really appreciated.


thank you
Amit
Re: need help with oracle stream [message #378058 is a reply to message #378053] Sun, 28 December 2008 04:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is more technical than the documentation?

Regards
Michel
Re: need help with oracle stream [message #378246 is a reply to message #378058] Mon, 29 December 2008 08:03 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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
Previous Topic: ORA-25292: Buffer operations are not supported on the queue
Next Topic: 10g Release 2 Streams Replication
Goto Forum:
  


Current Time: Fri Mar 29 02:26:12 CDT 2024