Link to home
Start Free TrialLog in
Avatar of Julie Kurpa
Julie KurpaFlag for United States of America

asked on

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?
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

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
Avatar of Julie Kurpa

ASKER

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?
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.
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
As you pointed out and I confirmed in the docs:  You do need global_names set to true for Streams to work.
ASKER CERTIFIED SOLUTION
Avatar of Julie Kurpa
Julie Kurpa
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Glad you were able to get it working.
I'm good.  Just glad you were able to figure it out.
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.