How can we stream from one database to two targets when the two targets are on different servers but share the instance name?

We stream from a Windows Oracle RAC to AIX Oracle Reporting Server.  Our set up is as follows:

Source Database:
Oracle two node RAC on Windows
Servers:  RACSRVPROD1 & RACSRVPROD2
Instances:  RACPROD1 & RACPROD2


Target Database:
Oracle single instance on AIX
Server Name:  RPTPRODSRV
Instance:   RPTPROD


This has been working fine.

We created a 3rd server (aix) which will become our new production server called RPTNEWSRV.
We want to keep the same Instance name of RPTPROD on the new server.  We used RMAN to duplicate the RPTPROD instance to the new server so all target stuff is set up the same.

Now we want to add this new instance to the streaming environment.  

Obviously we want to make sure the Source database can distinguish between the RPTPROD instances on each server but aren't entirely sure how to do this.

Here are the tnsnames.ora entries in the source server to the two reporting instances:


# Current production
RPTPROD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = RPTPRODSRV)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = RPTPROD)
    )
  )

# future production
NEW_RPTPROD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = RPTNEWSRV)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = RPTPROD)
    )
  )


Then we create a new dblink to point to the new server:


CREATE DATABASE LINK NEW_RPTPROD.domain.name CONNECT TO STREAMSADMIN IDENTIFIED by xxxxx USING 'NEW_RPTPROD';

But when we select from the dblink, we get the following message:


SQL> SELECT * from dual@NEW_RPTPROD.domain.name;
SELECT * from dual@NEW_RPTPROD.domain.name
                   *
ERROR at line 1:
ORA-02085: database link NEW_RPTPROD.domain.name connects to
RPTPROD.domain.name


Obviously we aren't understanding something here.  

How can we stream from one database to two targets when the two targets are on different servers but share the instance name?
LVL 1
vocogovAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Make sure global_names is set to false in NEW_RPTPROD:
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:563222962265
0
vocogovAuthor Commented:
I checked and it was set to false in NEW_RPTPROD.  

It is set to TRUE, however, in the RPTPROD database.   Doesn't global_names need to be "true" for streams?
0
slightwv (䄆 Netminder) Commented:
It has been a while since I set up a Streams Replication configuration.  Yes, global_names must be true so it appears you cannot have two databases using the same global names in the same streams setup.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

vocogovAuthor Commented:
Here's what I did:  
I set global_names to FALSE in the source database.  Now it is allowing me to select from each dblink and it's giving me the correct hostname when I query from v$instance.  

In following my process to instantiate streams to the new server, I'm keep getting the following error:

    SELECT COUNT(1) INTO cnt FROM all_tables@"NEW_RPTPROD.DOMAIN.NAME"
    *
ERROR at line 26:
ORA-06550: line 26, column 5:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 26, column 5:
PL/SQL: SQL Statement ignored
ORA-06550: line 47, column 3:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 47, column 3:
PL/SQL: SQL Statement ignored

I can actually issue that query on the command line with no problems (with or without the quotes).   I'm connected as streams admin for everything.   Any ideas?

SQL> SELECT COUNT(1) FROM all_tables@"NEW_RPTPROD.DOMAIN.NAME";

  COUNT(1)
----------
      3390
0
slightwv (䄆 Netminder) Commented:
As you pointed out and I confirmed in the docs:  You do need global_names set to true for Streams to work.
0
vocogovAuthor Commented:
I have gotten it to work.  The reason it was failing at that spot in the script was because it was connected to the new target database, NEW_RPTPROD, and trying to do a select from SELECT COUNT(1) FROM all_tables@"NEW_RPTPROD.DOMAIN.NAME"; for which there was no dblink by that name in the target database.

I added an entry in the apply side TNSNAMES.ORA to have the "NEW_RPTPROD" point to the new database (just like on the capture side):

NEW_RPTPROD=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = RPTPRODSRV)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = rptprod)
    )
  )

Then created dblink in the apply side to use that tnsnames.ora entry:

CREATE DATABASE LINK NEW_RPTPROD.DOMAIN.NAME CONNECT TO STREAMSADMIN IDENTIFIED by xxxxx USING 'NEW_RPTPROD';

I did need to disable GLOBAL_NAMES in both the capture and new apply databases.  I didn't touch the already existing apply side.

After doing this, the instantiation script ran fine.   I've been watching my heartbeat tables on both apply sides and they are staying in sync.   Row counts for the replicated tables are staying in sync as well.  

I don't plan to leave things like this as a I know it's not a recommended set up.  Soon they'll cut over to this new environment. We'll drop the "old" production streams and I'll instantiate the the new streams configuration properly to use global_names and get rid of the alternative dblinks & tnsnames.ora entries.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
slightwv (䄆 Netminder) Commented:
Glad you were able to get it working.
0
slightwv (䄆 Netminder) Commented:
I'm good.  Just glad you were able to figure it out.
0
vocogovAuthor Commented:
I'm not sure how to select my summary as the solution and yet select one of SlightWV's comments as an assisted solution (the one about changing Global_Names to FALSE).   Been clicking the Assisted Solution button but no success.  

Thank you SlightWV for pointing me in a direction that helped me find the final solution.   If my choice in answers is unfair, please tell me and I'll try adjust it.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.