This discussion is archived
2 Replies Latest reply: Dec 5, 2012 3:29 AM by Mdht RSS

Same database name at primary and replica site

647939 Pro
Currently Being Moderated
Hi,

I have two databases at two different server(eg. s1 and s2), both databases have the same name say "ORCL"

I want to configure schema level stream replication between these two databases of s1 and s2.

But, while creating the db like for configuring stream replication, we have to give the link name same as the db name. In my case both the db name are same. So i am confused, how should i proceed..

I have tried following:

Try 1)

I created service name orcl_s1 in tnsnames.ora of s2 for orcl of s1 and vice versa.

Then on s1 while creating db link, i gave following command :
create database link ORCL_S1 connect to strmadmin identified by strmadmin using 'ORCL_S1';

But when i tried to test that link, i got an error as following:

SQL> SELECT '1' FROM [DUAL@ORCL_S1|mailto:DUAL@ORCL_S1];
SELECT '1' FROM [DUAL@ORCL_S1|mailto:DUAL@ORCL_S1]
*
ERROR at line 1:
ORA-02085: database link ORCL_S1.REGRESS.RDBMS.DEV.US.ORACLE.COM connects to
ORCL.REGRESS.RDBMS.DEV.US.ORACLE.COM

Try 2)

Then i recreated the database link as following :

create database link ORCL connect to strmadmin identified by strmadmin using 'ORCL_S1';

Error:
ERROR at line 1:
ORA-02082: a loopback database link must have a connection qualifier

Try 3) create database link ORCL_S1 connect to strmadmin identified by strmadmin using 'ORCL';

Error:SQL> select '1' from [dual@ORCL_S1|mailto:dual@ORCL_S1];
select '1' from [dual@ORCL_S1|mailto:dual@ORCL_S1]
*
ERROR at line 1:
ORA-12154: TNS:could not resolve the connect identifier specified

--------------------------------------------------------------------------

Now i am confused, how should i create database link between 2 database having same name for stream replication..

Please guide if any one has any idea...

Regards,
Dipali..
  • 1. Re: Same database name at primary and replica site
    rijesh  chandran - oracle Journeyer
    Currently Being Moderated
    Dipali,

    Usually Oracle Streams uses GLOBAL_NAME not DB_NAME or INSTANCE_NAME or SERVICE_NAME.

    GLOBAL_NAME is the name used to identify a database in a distributed databsaes environment. This is not same as db_name or instance_name or even service_name. Each database in a distributed databsaes environment should have a UNIQUE global_name. It can be that db_name, instance_name or service_name are same for these but global_name should be different. Any database links created should have the same name as GLOBAL_NAME of the database to where it is connecting (remote database).

    GLOBAL_NAME should not be confused with the init.ora parameter GLOBAL_NAMES. If this parameter is set to TRUE then it enforces you to have the dblink name same as that of the GLOBAL_NAME of the remote database.
    This is the reason why you got ORA-02085 error in TRY 1. If GLOBAL_NAMES is set to FALSE then you can have the dblink name as your wish. But for any distributed environment (like Streams) GLOBAL_NAMES should be set to TRUE always.

    You can find the global_name using the following query:

    connect / as sysdba
    select * from global_name;

    The default GLOBAL_NAME is <db_name>.<db_domain>. So in TRY 2, you got ORA-02082 because your GLOBAL_NAME of the database was ORCL and you created a dblink with the name ORCL. This misleads the database and make it to believe that you are connecting back to the same database, so it throws ORA-02082.

    In TRY 3, the TNS Alias (in tnsnames.ora) 'ORCL' does not point to anywhere and hence you got ORA-12154. If you make the TNS Alias to point to the correct remote db, still you will receive ORA-02085 since the dblink name is ORCL_S1.

    What you should do:_

    1. In S1, let the db_name be ORCL change the global_name to the following:

    connect / as sysdba
    select * from global_name;
    --enforce global_names parameter
    alter system set global_names=TRUE scope=both;
    --change the global_name
    update global_name set global_name='ORCL_S1';
    commit;
    --verify whether it has changed
    select * from global_name;

    2. In S2, let the db_name be ORCL but change global_name to the following:

    connect / as sysdba
    select * from global_name;
    --enforce global_names parameter
    alter system set global_names=TRUE scope=both;
    --change the global_name
    update global_name set global_name='ORCL_S2';
    commit;
    --verify whether it has changed
    select * from global_name;

    3. Create the dblink on S1 as follows:

    connect <strmadmin>/<pwd>
    --remember the dblink name should be ORCL_S2
    --which should be global_name of the remote db
    create database link ORCL_S2 connect to strmadmin identified by strmadmin using '<TNS ALIAS to S2>';
    --verify the link works
    select * from dual@ORCL_S2;
    --Remember that <TNS ALIAS to S2> should be pointing to database ORCL on S2.

    4. Create dblink on S2 if needed.

    connect <strmadmin>/<pwd>
    create database link ORCL_S1 connect to strmadmin identified by strmadmin using '<TNS ALIAS to S1>';
    --verify the link works
    select * from dual@ORCL_S1;
    --Remember that <TNS ALIAS to S1> should be pointing to database ORCL on S1.

    Also remember that source_database in DBMS_STREAMS_ADM.ADD_SCHEMA_RULES (capture rules, propagation rules and apply rules) should be GLOBAL_NAME of the source database, not db_name or instance_name.

    Let me know if you still need any clarification.

    Thanks,
    Rijesh
  • 2. Re: Same database name at primary and replica site
    Mdht Newbie
    Currently Being Moderated
    Dear all,
    I configure streams on 2 DB have same name: mr

    I set global_name=true
    db source: global_name=mr.db01
    db target global_name=mr.db02
    DB link name mr.db02
    now I start capture and have error:
    LOGMINER: session#=3, preparer MS02 pid=100 OS id=29569 sid=216 started
    LOGMINER: Begin mining logfile during dictionary load for session 3 thread 1 sequence 28, /opt/oracle/archive/mr/arch_0001_1_28_801051752.dbf
    <krvrd.c: krvrdCBmgd>: Global name mismatch
    krvxerpt: Errors detected in process 96, role builder.
    krvxmrs: Leaving by exception: 1361

    do you have any experiences on this case. I don't know my error from where?
    Thanks so much!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points