This discussion is archived
8 Replies Latest reply: Oct 11, 2012 5:20 PM by ozoracle RSS

DB_NAME used by the Logical Standby db

ozoracle Newbie
Currently Being Moderated
Hi all,
It's Oracle 11g R2

I have a general question about converting a Physical standby database to a Logical Standby database.

An important step of the conversion procedure is to issue the following command:
ALTER DATABASE RECOVER TO LOGICAL STANDBY <NEWDBNAME>;

My question is: this statement changes the database name of the Standby database.
How come I still can connect to the new database without having to change the SERVIC_NAME in the tnsnames.ora file?

Here's the connection names used in the file:
db2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oradb2.mydomain.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = oradb)
    )
  )
Here's the new db_name:
SQL> SELECT VALUE FROM V$PARAMETER 
WHERE NAME LIKE lower('DB_NAME%') 

VALUE
---------------------------------
ORADBLS


SQL> conn hr@db2  
Enter password: 
Connected.
  • 1. Re: DB_NAME used by the Logical Standby db
    mseberg Guru
    Currently Being Moderated
    Odd.

    The only thing I can think of is using a PFILE instead of an SPFILE file. If you are using a PFILE you must manually edit it and change the name. If you are using an SPFILE the parameter will be changed automatically.

    Best Regards

    mseberg
  • 2. Re: DB_NAME used by the Logical Standby db
    ozoracle Newbie
    Currently Being Moderated
    I'm using SPFILE. The DB_NAME was successfully and automatically changed. No problem with that.

    But I didn't change the tnsnames.ora. And I still could connect to the Standby database.

    I just followed "Creating Logical Standby db" procedure in the documentation and everything went fine.. but I just didn't understand this part.

    The db2 naming is the one used by the Primary for connecting to the Standby and it works just fine:
    LOG_ARCHIVE_DEST_2='service=db2 ASYNC DB_UNIQUE_NAME=oradb2 VALID_FOR=primary_role,online_logfile)';
  • 3. Re: DB_NAME used by the Logical Standby db
    JohnWatson Guru
    Currently Being Moderated
    If I understand you correctly, you are asking why changing the database name does not necessitate changing the service name? There is no direct relationship between them, you can define any number of service names to register with the listener. By default, there will be a service that does have the same name as the database (possibly with a domain appended) but you do not have to use that one. Query V$ACTIVE_SERVICES and DBA_SERVICES to see what services are available.

    Edited by: JohnWatson on Oct 11, 2012 1:50 PM
    Sorry - " a service that does have the same name as the database" should read " a service that does have the same unique name as the database"
  • 4. Re: DB_NAME used by the Logical Standby db
    ozoracle Newbie
    Currently Being Moderated
    Thanks for your reply John.

    Here's how the listener is configured in the Standby database:
    SID_LIST_LISTENER =
      (SID_LIST =
        (SID_DESC =
          (GLOBAL_DBNAME = oradb)
          (ORACLE_HOME = /app/oracle/product/11.2.0/db_1)
          (SID_NAME = oradb)
        )
      )
    The new DB_NAME is not registered at all!
    Only the old one (used when the Standby database was Physical) is still there.
  • 5. Re: DB_NAME used by the Logical Standby db
    JohnWatson Guru
    Currently Being Moderated
    The static registration in the listener doesn't matter (though of course you can connect to it if you want to). It is the db unique name that is registered dynamically as a service, and changing the db name won't change that.
  • 6. Re: DB_NAME used by the Logical Standby db
    ozoracle Newbie
    Currently Being Moderated
    JohnWatson wrote:
    The static registration in the listener doesn't matter (though of course you can connect to it if you want to). It is the db unique name that is registered dynamically as a service, and changing the db name won't change that.
    That's great, but the TNSNAMES used SERVICE_NAME=<db_name>, not the <DB_UNIQUE_NAME>!
  • 7. Re: DB_NAME used by the Logical Standby db
    JohnWatson Guru
    Currently Being Moderated
    Perhaps I didn't put it clearly. Your tnsnames alias is requesting a service. The service it asks for is the named after the db_unique_name. You haven't changed that. If you look at fhose views I mentioned, and the parameters dbname/db_unique_name/service_names, it should become clear.
    Hope this helps, I don't think I can add more.
  • 8. Re: DB_NAME used by the Logical Standby db
    ozoracle Newbie
    Currently Being Moderated
    Hi John,

    You last explanation was clear like crystal. Thanks for that.

    Now back to the practical side of it, when I query V$ACTIVE_SERVICES, I don't see the unique_db_name in the picture:
    SQL> select name, network_name from V$ACTIVE_SERVICES where name = 'oradb' ;
    
    NAME
    ---------------------------------------------------------------
    NETWORK_NAME
    ---------------------------------------------------------------
    oradb
    oradb
    Thanks and Cheers,

Legend

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