This discussion is archived
1 2 3 Previous Next 37 Replies Latest reply: Jan 22, 2013 6:01 AM by 986383 RSS

ORA-12514 When trying to add the secondary physical database

894939 Newbie
Currently Being Moderated
Hi All,

I'm getting ORA-12514 when I'm trying to add my physical standby database in DGMGRL. Please find below my listner.ora configurations in both primary and secondary DB.

Primary
--------------

ADMIN_RESTRICTIONS_LISTENER = on
LISTENER =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = IPC)
(KEY = DGP.WORLD)
)
(ADDRESS=
(PROTOCOL = IPC)
(KEY = DGP)
)
(ADDRESS =
(COMMUNITY = SAP.WORLD)
(PROTOCOL = TCP)
(HOST = oracledgp)
(PORT = 1527)
)
)
STARTUP_WAIT_TIME_LISTENER = 0
CONNECT_TIMEOUT_LISTENER = 10
TRACE_LEVEL_LISTENER = OFF
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = DGP)
(ORACLE_HOME = /oracle/DGP/112_64)
(GLOBAL_DBNAME=DGP.DGMGRL.oracledgp)
)
(SID_DESC =
(SID_NAME = DGP)
(ORACLE_HOME = /oracle/DGP/112_64)
(GLOBAL_DBNAME=DGR.DGMGRL.oracledgr)
)

)

Secondary
----------------------

################
ADMIN_RESTRICTIONS_LISTENER = on
LISTENER =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = IPC)
(KEY = DGP.WORLD)
)
(ADDRESS=
(PROTOCOL = IPC)
(KEY = DGP)
)
(ADDRESS =
(COMMUNITY = SAP.WORLD)
(PROTOCOL = TCP)
(HOST = oracledgr)
(PORT = 1527)
)
)
STARTUP_WAIT_TIME_LISTENER = 0
CONNECT_TIMEOUT_LISTENER = 10
TRACE_LEVEL_LISTENER = OFF
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = DGP)
(ORACLE_HOME = /oracle/DGP/112_64)
(GLOBAL_DBNAME=DGR)
)
(SID_DESC =
(SID_NAME = DGP)
(ORACLE_HOME = /oracle/DGP/112_64)
(GLOBAL_DBNAME=DGP)
)
)



Please help I tried but as this is common error still I cannot find the specific error here...

Thanks,
Shanaka.
  • 1. Re: ORA-12514 When trying to add the secondary physical database
    Balazs Papp Expert
    Currently Being Moderated
    listener.log contains the details of CONNECT_DATA
    there you can find the SID/SERVICE_NAME that is used for connection (which is missing on the secondary site)
  • 2. Re: ORA-12514 When trying to add the secondary physical database
    teits Journeyer
    Currently Being Moderated
    Hi,

    12514, 00000, "TNS:listener does not currently know of service requested in connect descriptor"
    // *Cause:  The listener received a request to establish a connection to a
    // database or other service. The connect descriptor received by the listener
    // specified a service name for a service (usually a database service)
    // that either has not yet dynamically registered with the listener or has
    // not been statically configured for the listener. This may be a temporary
    // condition such as after the listener has started, but before the database
    // instance has registered with the listener.
    // *Action:
    // - Wait a moment and try to connect a second time.
    // - Check which services are currently known by the listener by executing:
    // lsnrctl services <listener name>
    // - Check that the SERVICE_NAME parameter in the connect descriptor of the
    // net service name used specifies a service known by the listener.
    // - If an easy connect naming connect identifier was used, check that
    // the service name specified is a service known by the listener.
    // - Check for an event in the listener.log file.


    firstly, your GLOBAL_DBNAME seem not to be configured properly.
    it should look like:
    SID_LIST_LISTENER=(SID_LIST=(SID_DESC=(SID_NAME=sid_name)
    (GLOBAL_DBNAME=db_unique_name_DGMGRL.db_domain) ---this section edited to ur db_unique_name
    (ORACLE_HOME=oracle_home)))
    --Alternatively, you can use a different static service name. If you do, be sure to modify the StaticConnectIdentifier instance-specific property to reflect the different service name.

    http://gavinsoorma.com/2010/03/11g-data-guard-broker-dgmgrl-configuration-quick-steps/

    Tobi
  • 3. Re: ORA-12514 When trying to add the secondary physical database
    894939 Newbie
    Currently Being Moderated
    Hi Todi,

    I have edited the file as below....

    STARTUP_WAIT_TIME_LISTENER = 0
    CONNECT_TIMEOUT_LISTENER = 10
    TRACE_LEVEL_LISTENER = OFF
    SID_LIST_LISTENER =
    (SID_LIST =
    (SID_DESC =
    (SID_NAME = DGP)
    (ORACLE_HOME = /oracle/DGP/112_64)
    (GLOBAL_DBNAME=DGP.DGMGRL.oracledgp)
    )
    (SID_DESC =
    (SID_NAME = DGP)
    (ORACLE_HOME = /oracle/DGP/112_64)
    (GLOBAL_DBNAME=DGR.DGMGRL.oracledgr)
    )

    )


    Hope this is correct...

    Thanks
  • 4. Re: ORA-12514 When trying to add the secondary physical database
    mseberg Guru
    Currently Being Moderated
    Hello;

    It should be :

    <db_unique_name>_DGMGRL.domain

    incorerct

    GLOBAL_DBNAME=DGP.DGMGRL.oracledgp


    Use _DGMGRL
    _DGMGRL
    not
    .DGMGRL
    Data Guard Listener Example

    http://www.visi.com/~mseberg/Data_Guard_Listener_Example.html

    So you still need the _DGMGRL or at least a static entry in the listener.ora.

    For additional information see -

    Data Guard Broker 11g Release 2 (11.2) E17023-04




    Best Regards

    mseberg

    Edited by: mseberg on Nov 14, 2012 4:43 AM
  • 5. Re: ORA-12514 When trying to add the secondary physical database
    894939 Newbie
    Currently Being Moderated
    Dear mseberg,

    Thank you very much for the information , even after I edited the listner.ora accordingly I still get the same error when try to add secondary DB. Is there any place where we can check regarding this error.

    Regards,
    Shanaka.
  • 6. Re: ORA-12514 When trying to add the secondary physical database
    Balazs Papp Expert
    Currently Being Moderated
    Did you read my post?
    Did you check the listener log?
  • 7. Re: ORA-12514 When trying to add the secondary physical database
    Shivananda Rao Guru
    Currently Being Moderated
    (SID_DESC =
    (SID_NAME = DGP)
    (ORACLE_HOME = /oracle/DGP/112_64)
    (GLOBAL_DBNAME=DGR.DGMGRL.oracledgr)
    You have mentioned GLOBAL_DBNAME as DGP.DGMGRL.oracledgr but you have also mentioned GLOBAL_DBNAME as DGR as shown above. Change it to DGP_DGMGRL.oracledgr in primary
  • 8. Re: ORA-12514 When trying to add the secondary physical database
    894939 Newbie
    Currently Being Moderated
    Hi Balazs ,

    Yes boss I have red the log you've mentioned but it doesn't contain any failure message regarding trying to connect to DR database.

    The only message it contains is

    <msg time='2012-11-12T06:10:59.661+05:30' org_id='oracle' comp_id='tnslsnr'
    type='UNKNOWN' level='16' host_id='oracledgp'
    host_addr='PR_IP'>
    <txt>12-NOV-2012 06:10:59 * (CONNECT_DATA=(SID=DGP)(GLOBAL_NAME=DGP.WORLD)(CID=(PROGRAM=dw.sapDGP_DVEBMGS00)(HOST=oracledgp)(USER=dgpadm))) * (ADDRESS=(PROTOCOL=tcp)(HOST=PR_IP)(PORT=41057)) * establish * DGP * 0
    </txt>
    </msg>
    <msg time='2012-11-12T06:11:01.836+05:30' org_id='oracle' comp_id='tnslsnr'
    type='UNKNOWN' level='16' host_id='oracledgp'
    host_addr='PR_IP'>



    Thanks,
    Shanaka.

    Edited by: user9163607 on Nov 14, 2012 3:36 AM
  • 9. Re: ORA-12514 When trying to add the secondary physical database
    894939 Newbie
    Currently Being Moderated
    Rao, I have changed that in both PR and DR :)
  • 10. Re: ORA-12514 When trying to add the secondary physical database
    CKPT Guru
    Currently Being Moderated
    Am not sure you have bounced the listener or not after making changes in GLOBAL_DBNAME. Please do if you haven't done.

    and besides give the output of
    $lnsrctl status
    $lsnrctl services

    also share the oracle net service you are using to add in DGMGRL.
  • 11. Re: ORA-12514 When trying to add the secondary physical database
    mseberg Guru
    Currently Being Moderated
    Hello again;

    Since you are using ADMIN_RESTRICTIONS_LISTENER your best bet is to modify the listener.ora file manually and then stop and start the listener.

    A lsnrctl status should show something like this :
    Service "STANDBY_DGMGRL.domainname" has 1 instance(s).
    You need to see this for for the Primary and Standby Databases.


    If your GLOBAL_DBNAME is not set to this format on all listeners you will not only have this issue ( ORA-12514 ), but you will probably have switchover issues
    down the line if any part is setup wrong.
    GLOBAL_DBNAME = db_unique_name_DGMGRL.db_domain
    Take a look at this note since you are not using the default port.


    ORA-16664 ORA-16792 Data guard Broker [ID 1228797.1]

    These two notes may also help :

    Switchover Failed With ORA-12521 using Dataguard Broker [ID 1380949.1]
    Automatic Restart of Databases during Switchover fail with ORA-12514 in DGMGRL [ID 308943.1]

    Best Regards

    mseberg
  • 12. Re: ORA-12514 When trying to add the secondary physical database
    teits Journeyer
    Currently Being Moderated
    NO. but i believe you have gotten that line right by now. should be *<db_unique_name>_DGMGRL.domain*


    If you you still have issues, do on both DB and paste here:
    $ lsnrctl reload
    $ lsnrctl status
    
    SQL> show parameter db_unique_name
    SQL> show parameter db_domain     
    Tobi
  • 13. Re: ORA-12514 When trying to add the secondary physical database
    894939 Newbie
    Currently Being Moderated
    HI All,

    Please find the lsnrctl status output below.

    Primary
    -----------------------------------
    oracledgp:oradgp 51> lsnrctl status

    LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 15-NOV-2012 08:56:10

    Copyright (c) 1991, 2010, Oracle. All rights reserved.

    Connecting to (ADDRESS=(PROTOCOL=IPC)(KEY=DGP.WORLD))
    STATUS of the LISTENER
    ------------------------
    Alias LISTENER
    Version TNSLSNR for Linux: Version 11.2.0.2.0 - Production
    Start Date 14-NOV-2012 16:36:23
    Uptime 0 days 16 hr. 19 min. 46 sec
    Trace Level off
    Security ON: Local OS Authentication
    SNMP OFF
    Listener Parameter File /oracle/DGP/112_64/network/admin/listener.ora
    Listener Log File /oracle/diag/tnslsnr/oracledgp/listener/alert/log.xml
    Listening Endpoints Summary...
    (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=DGP.WORLD)))
    (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=DGP)))
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracledgp.keells.int)(PORT=1527)))
    Services Summary...
    Service "DGP_DGMGRL.oracledgp" has 1 instance(s).
    Instance "DGP", status UNKNOWN, has 1 handler(s) for this service...
    Service "DGR_DGMGRL.oracledgr" has 1 instance(s).
    Instance "DGP", status UNKNOWN, has 1 handler(s) for this service...
    The command completed successfully


    Secondary
    ------------------------------------------
    oracledgr:oradgp 51> lsnrctl status

    LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 15-NOV-2012 14:28:44

    Copyright (c) 1991, 2010, Oracle. All rights reserved.

    Connecting to (ADDRESS=(PROTOCOL=IPC)(KEY=DGP.WORLD))
    STATUS of the LISTENER
    ------------------------
    Alias LISTENER
    Version TNSLSNR for Linux: Version 11.2.0.2.0 - Production
    Start Date 14-NOV-2012 22:06:38
    Uptime 0 days 16 hr. 22 min. 6 sec
    Trace Level off
    Security ON: Local OS Authentication
    SNMP OFF
    Listener Parameter File /oracle/DGP/112_64/network/admin/listener.ora
    Listener Log File /oracle/diag/tnslsnr/oracledgr/listener/alert/log.xml
    Listening Endpoints Summary...
    (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=DGP.WORLD)))
    (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=DGP)))
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracledgr.keells.int)(PORT=1527)))
    Services Summary...
    Service "DGP_DGMGRL.oracledgr" has 1 instance(s).
    Instance "DGP", status UNKNOWN, has 1 handler(s) for this service...
    Service "DGR_DGMGRL.oracledgp" has 1 instance(s).
    Instance "DGP", status UNKNOWN, has 1 handler(s) for this service...
    The command completed successfully



    lsnrctl services_

    Primary

    oracledgp:oradgp 52> lsnrctl services

    LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 15-NOV-2012 08:59:47

    Copyright (c) 1991, 2010, Oracle. All rights reserved.

    Connecting to (ADDRESS=(PROTOCOL=IPC)(KEY=DGP.WORLD))
    Services Summary...
    Service "DGP_DGMGRL.oracledgp" has 1 instance(s).
    Instance "DGP", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
    "DEDICATED" established:0 refused:0
    LOCAL SERVER
    Service "DGR_DGMGRL.oracledgr" has 1 instance(s).
    Instance "DGP", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
    "DEDICATED" established:0 refused:0
    LOCAL SERVER
    The command completed successfully


    secondary

    oracledgr:oradgp 52> lsnrctl services

    LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 15-NOV-2012 14:29:07

    Copyright (c) 1991, 2010, Oracle. All rights reserved.

    Connecting to (ADDRESS=(PROTOCOL=IPC)(KEY=DGP.WORLD))
    Services Summary...
    Service "DGP_DGMGRL.oracledgr" has 1 instance(s).
    Instance "DGP", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
    "DEDICATED" established:0 refused:0
    LOCAL SERVER
    Service "DGR_DGMGRL.oracledgp" has 1 instance(s).
    Instance "DGP", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
    "DEDICATED" established:0 refused:0
    LOCAL SERVER
    The command completed successfully



    Thanks,
    Shanaka.

    Edited by: Shanaka Chandrasekera on Nov 14, 2012 7:36 PM
  • 14. Re: ORA-12514 When trying to add the secondary physical database
    894939 Newbie
    Currently Being Moderated
    Dear Todi,

    Please find details below

    lsnrctl reload_

    PR
    SQL> host
    oracledgp:oradgp 51> lsnrctl reload

    LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 15-NOV-2012 09:12:09

    Copyright (c) 1991, 2010, Oracle. All rights reserved.

    Connecting to (ADDRESS=(PROTOCOL=IPC)(KEY=DGP.WORLD))
    The command completed successfully
    oracledgp:oradgp 52>

    DR

    oracledgr:oradgp 54> lsnrctl reload

    LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 15-NOV-2012 14:43:24

    Copyright (c) 1991, 2010, Oracle. All rights reserved.

    Connecting to (ADDRESS=(PROTOCOL=IPC)(KEY=DGP.WORLD))
    The command completed successfully
    oracledgr:oradgp 55>


    SQL_

    PR

    SQL> show parameter db_unique_name

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    db_unique_name string DGP
    SQL> show parameter db_domain

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    db_domain string oracledgp
    SQL>


    DR

    SQL> show parameter db_unique_name

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    db_unique_name string DGR
    SQL> show parameter db_domain

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    db_domain string oracledgr


    Thanks,
    Shanaka.
1 2 3 Previous Next

Legend

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