This discussion is archived
3 Replies Latest reply: Feb 6, 2013 9:00 AM by CKPT RSS

Finding Standby DB name from Primary

N.Page Newbie
Currently Being Moderated
11.2.0.3/Solaris 10

Setup Type : Physical Standby


I only have toad/SQL*Plus access to the primary DB and I want to know the DB_UNIQUE_NAME of the standby DB.
LOG_ARCHIVE_DEST_2 will only have the TNS entry of the standby DB and I don't have server level access to the primary DB server to check the tnsnames.ora file !

Is there any other way (like an init.ora parameter) to find the DB_UNIQUE_NAME of the standby DB when connected to the DB from SQL*Plus/Toad ?
  • 1. Re: Finding Standby DB name from Primary
    P.Forstmann Guru
    Currently Being Moderated
    You could have it in LOG_ARCHIVE_CONFIG instance parameter with the DG_CONFIG clause:
    http://docs.oracle.com/cd/E11882_01/server.112/e25513/initparams121.htm#REFRN10237.

    You should also find primary and standby unique db names in V$DATAGUARD_CONFIG
    http://docs.oracle.com/cd/E11882_01/server.112/e25513/dynviews_1102.htm#REFRN30053 :
    SQL> show parameter log_archive_config
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    log_archive_config                   string      dg_config=(p10,s10)
    
    SQL> select * from v$dataguard_config;
    
    DB_UNIQUE_NAME
    ------------------------------
    P10
    s10
    Edited by: P. Forstmann on 6 févr. 2013 17:58
  • 2. Re: Finding Standby DB name from Primary
    mseberg Guru
    Currently Being Moderated
    Hello;


    The parameter log_archive_dest_2 or log_archive_dest_N should show the DB_UNIQUE_NAME of the standby.

    Example :

    log_archive_dest_2='SERVICE=STANDBY LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STANDBY'
    SQL> show parameter log_archive_dest_2
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    log_archive_dest_2                   string      SERVICE=STANDBY LGWR ASYNC VAL
                                                     ID_FOR=(ONLINE_LOGFILES,PRIMAR
                                                     Y_ROLE) DB_UNIQUE_NAME=STANDBY
    From Toad you can query v$spparmeter

    select value from v$spparameter where name = 'log_archive_dest_2';

    Best Regards

    mseberg

    Edited by: mseberg on Feb 6, 2013 11:07 AM
  • 3. Re: Finding Standby DB name from Primary
    CKPT Guru
    Currently Being Moderated
    N.Page wrote:
    11.2.0.3/Solaris 10

    Setup Type : Physical Standby


    I only have toad/SQL*Plus access to the primary DB and I want to know the DB_UNIQUE_NAME of the standby DB.
    LOG_ARCHIVE_DEST_2 will only have the TNS entry of the standby DB and I don't have server level access to the primary DB server to check the tnsnames.ora file !

    Is there any other way (like an init.ora parameter) to find the DB_UNIQUE_NAME of the standby DB when connected to the DB from SQL*Plus/Toad ?
    If you want to find out DB_UNIQUE_NAME from primary, Then you can use this below query
    SQL> select db_unique_name from v$archive_dest where dest_id in (2,3);
    
    DB_UNIQUE_NAME
    ------------------------------
    STANDBY_UN
    NONE
    SQL> 
    I have given two destinations , One destination which is configured and another destination is not configured.

Legend

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