Link to home
Create AccountLog in
Avatar of thebabinator

asked on

Oracle 11g streams how to configure

I am new to oracle and trying to setup streams.
we have Oracle 11g enterprise running on windows 2008.

The setup process seems to have worked but i continue to get the error:
ORA-02019: connection description for remote database not found

The name of the database is the same on both servers.  i havent found if that will be a problem.
I dont know how to check for or create a database link which might be what i need.

i have searched but not sure if i am searching for the right solution. searching on the error i find mostly that i need to create a dblink but no so much how. and also changing .ora files which i cannot find.

i have tried: CREATE PUBLIC DATABASE LINK server2.domain CONNECT TO user AS pswd
but that just gives me 0933 errors.

Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

>>I am new to oracle and trying to setup streams

Good luck with this.  Streams setup can be pretty confusing.  This is even more difficult if you are new to Oracle.

>> create a database link

Yes, you need a database link for Streams to work.  The view to look for links is DBA_DB_LINKS.

To create one, you need to set up an entry in the tnanames.ora file for the remote database and use that in the create database link statement.
Avatar of thebabinator


So how do i add the link when the second database has the same name?

goal is to have the database replicated between two sites and the users use the closet db or if one goes down.
Never tried this but can't think of a reason it wouldn't work.

Just create a tnsnames.ora entry named something like REMOTE.  The tnsnames entry doesn't have to match the service_name.
OK i have made an entry in tnsnames.ora with the correct pointer.

returns nothing.  Do i have restart something?
After you added the entry in tnsnames.ora did you reissue the 'create database link' command?

create database link REMOTE connect to user identified by password using 'REMOTE';

where REMOTE is the alias you created in the tnsnames file.
OK well i think i made some progress. but i have errors when i tried to create a test user.
1st question is do i have set up the streams on both servers so they update each other or by setting it up on server1 it will automatically get updates from server2?

after setting up streams all looked good with no known errors.
I attempted to create a user on server1 and got the errors in the Apply process.
Errors on server2:
TNS:packet writer failure ORA-02063
ORA-26714: User error encountered while applying

I have looked at the dba_apply_error view but that doesnt indicate what the issue is or how to fix:
"ORA-26786: A row with key ("INST_ID") = (22) exists but has conflicting column(s) "INST_LASTMOD" in table WKSYS.WK$INSTANCE
ORA-01403: no data found"

also to get the streams to setup with out errors i had to have different names for the databases..
but obviously i still have something missing.
>>do i have set up the streams on both servers

Depends on how you set it up in the first place.  You are supposed to be able to do this with OEM Grid control but that failed for me.

There's also 'magic' package/procedure that is supposed to do everything for you in one step but I never did get that working.

>>I attempted to create a user...

I don't think that error is from the create user statement.  WKSYS is for ultrasearch.  Not sure why a create user would cause an error in one of those tabels.

I assume you set up GLOBAL rules?  I didn't set that up so I might not be able to help much.  I'm running table-level replication.
>> had to have different names for the databases

I'll take your word for that.  I never tried it.  I couldn't think of a reason off the top of my head why it wouldn't work.  Guess it doesn't.  Good to know.
i did set it up with Global so it would replicate everything.  this is needed from a disaster failover perspective.

everything i have found on the error says to deal with it appropriately. what kind of resolution is that?

Anyway now that i am finished with my current screaming session...  Where to go from here?

>>what kind of resolution is that?

It's tons better than the way Multi-Master Advanced Replication worked.  I had to wait for 11gR2 before I could make the leap but have never regretted moving to Streams.

>>Where to go from here?

I hear Tahiti is nice this time of year?  OK, I'm done..  

Did you use OEM or the magic one-step-package to do everything for you or some other way?

I hope you are intimately familiar with:

i just used the wizard in the EM.  They don't have R2 out for windows yet.

And no i am not even close to fully understanding the doc.

The theory was that the wizard would indicate if there was something else to be completed.
I tried creating users on both sides and they give the same errors.
Maybe restart the services?  I so prefer MS SQL!
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
OK this has been a nightmare.  I finally received a response from Oracle and have been working with them.

still not perfect yet but its one way.  the wizard in the EM completes but leave many details to configure manually. besides a massive learning curve i have had the fun of getting errors with no reason found yet.

learning to run the health script has been a big help too.
i have a massive learning curve to catch up with.