This discussion is archived
1 2 3 Previous Next 37 Replies Latest reply: Jan 22, 2013 6:01 AM by 986383 Go to original post RSS
  • 15. Re: ORA-12514 When trying to add the secondary physical database
    teits Journeyer
    Currently Being Moderated
    Hi Shanaka,

    the configuration is wrong.

    1. remove "DGR_DGMGRL.oracledgr" section from your primary listener.ora.
    2. remove "DGP_DGMGRL.oracledgp" section from your standby listener.ora.

    i would advise your SID_LIST_LISTENER section should look like this:
    FOR PRIMARY
    
    SID_LIST_LISTENER =
      (SID_LIST =
        (SID_DESC =
          (GLOBAL_DBNAME = DGP_DGMGRL.oracledgp)
          (ORACLE_HOME =  /oracle/DGP/112_64)
          (SID_NAME = DGP)
        )
      ) 
      
      
    FOR STANDBY
      
    SID_LIST_LISTENER =
      (SID_LIST =
        (SID_DESC =
          (GLOBAL_DBNAME = DGR_DGMGRL.oracledgr)
          (ORACLE_HOME =  /oracle/DGP/112_64)
          (SID_NAME = DGP)
        )
      ) 
    assuming your SID and ORACLE_HOME for both DBs are DGP,/oracle/DGP/112_64 rsp.
    i have configure your static listener for you.

    i believe some of this post are helpful. see https://forums.oracle.com/forums/ann.jspa?annID=718

    HTH
    Tobi

    Edited by: teits on Nov 15, 2012 10:13 AM
  • 16. Re: ORA-12514 When trying to add the secondary physical database
    894939 Newbie
    Currently Being Moderated
    Dear Todi,

    I have edited those files accordingly

    DR
    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_DGMGRL.oracledgp)
    # )
    (SID_DESC =
    (SID_NAME = DGP)
    (ORACLE_HOME = /oracle/DGP/112_64)
    (GLOBAL_DBNAME=DGP_DGMGRL.oracledgr)

    )
    )

    PR
    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 =
    (GLOBAL_DBNAME=DGP_DGMGRL.oracledgp)
    (ORACLE_HOME = /oracle/DGP/112_64)
    (SID_NAME = DGP)

    )
    )
    # (SID_DESC =
    # (SID_NAME = DGP)
    # (ORACLE_HOME = /oracle/DGP/112_64)
    # (GLOBAL_DBNAME=DGR_DGMGRL.oracledgr)
    # )

    # )


    Can you please let me know how can I execute the command in dgmgrl to add my secondary DB.

    Is it as below?
    add database dgr as connect identifier is DGP_DGMGRL.oracledgr maintained as physical;

    When i execute this as above I'm getting ORA-12154.

    Sorry for bothering as this is my first ever attempt to implement Data guard :) ...

    Thanks in advance,
    Shanaka.
  • 17. Re: ORA-12514 When trying to add the secondary physical database
    teits Journeyer
    Currently Being Moderated
    Can you please let me know how can I execute the command in dgmgrl to add my secondary DB.

    Is it as below?
    add database dgr as connect identifier is DGP_DGMGRL.oracledgr maintained as physical;

    When i execute this as above I'm getting ORA-12154.
    the statements is wrong. see syntax.
    ADD DATABASE <database name>    [AS CONNECT IDENTIFIER IS <connect   [MAINTAINED AS {PHYSICAL|LOGICAL}];
    e.g
    ADD DATABASE dgp AS CONNECT IDENTIFIER IS dgpTNS
    NOTE: dgpTNS should be in your tnsnames.ora. DGP_DGMGRL.oracledgr i doubt this is what you use as your connect identifier in tnsnames.ora. infact based on ORA-12154, it is not.
    dgp is your standby db_unique_name

    i will encourage you to read manuals on data guard and broker.

    >
    Sorry for bothering as this is my first ever attempt to implement Data guard :) ...
    Ok Shanaka but mark posts as "Helpful". this is only way we know we are really helping you! and when your question is answered, mark as "correct".


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

    Please find below my tnsnames.ora content of primary database.

    DGP.WORLD =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS =
    (COMMUNITY = SAP.WORLD)
    (PROTOCOL = TCP)
    (HOST = oracledgp)
    (PORT = 1527)
    )
    )

    (CONNECT_DATA =
    (SID = DGP)
    (GLOBAL_NAME = DGP)
    (SERVICE_NAME = LISTNER)
    )
    )

    DGR.WORLD =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS =
    (COMMUNITY = SAP.WORLD)
    (PROTOCOL = TCP)
    (HOST = oracledgr)
    (PORT = 1527)
    )
    )
    (CONNECT_DATA =
    (SID = DGP)
    (GLOBAL_NAME = DGR)
    (SERVICE_NAME= LISTNER)
    )
    )

    So I'm using DGR as connect identifier of my DR database , in this case can I add dgmgrl command as follow

    ADD DATABASE dgr AS CONNECT IDENTIFIER IS DGR

    Cos I can ping my DR database using name "DGR" with tnsping .....

    Thanks and best regards,
    Shanaka.
  • 19. Re: ORA-12514 When trying to add the secondary physical database
    teits Journeyer
    Currently Being Moderated
    Hi Shanaka,

    your listener.ora is still wrong. take not of your db_unique_name. i have posted earlier what your listener.ora should look like. plz, study it.

    ADD DATABASE dgr AS CONNECT IDENTIFIER IS DGR .
    yes it correct if dgr is your db_unique_name and DGR is your tnsname FOR STANDBY.

    here is a good doc
    http://apunhiran.blogspot.com/2009/09/how-to-configure-data-guard-broker.html

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

    What I have put there is my TNSNAMES.ORA ... listner.ora I have changed accordingly . My db_unique_name of DR database is DGR and tnsname FOR STANDBY as I have mentioned above.

    But when i execute DGMGRL command I still get that oracle error :(


    Thanks,
    Shanaka
  • 21. Re: ORA-12514 When trying to add the secondary physical database
    teits Journeyer
    Currently Being Moderated
    Shanaka Chandrasekera wrote:
    Dear Todi,

    I have edited those files accordingly

    DR
    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_DGMGRL.oracledgp)
    # )
    (SID_DESC =
    (SID_NAME = DGP)
    (ORACLE_HOME = /oracle/DGP/112_64)
    (GLOBAL_DBNAME=DGP_DGMGRL.oracledgr)
    ===================================
    what is your db_unique_name for DR?
    ===================================
    >
    )
    )

    PR
    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 =
    (GLOBAL_DBNAME=DGP_DGMGRL.oracledgp)
    ===================================
    what is your db_unique_name for PR?
    ===================================
    (ORACLE_HOME = /oracle/DGP/112_64)
    (SID_NAME = DGP)

    )
    )
    # (SID_DESC =
    # (SID_NAME = DGP)
    # (ORACLE_HOME = /oracle/DGP/112_64)
    # (GLOBAL_DBNAME=DGR_DGMGRL.oracledgr)
    # )

    # )


    Can you please let me know how can I execute the command in dgmgrl to add my secondary DB.

    Is it as below?
    add database dgr as connect identifier is DGP_DGMGRL.oracledgr maintained as physical;

    When i execute this as above I'm getting ORA-12154.

    Sorry for bothering as this is my first ever attempt to implement Data guard :) ...

    Thanks in advance,
    Shanaka.
    debug and paste the error here: how-to
    dgmgrl -debug

    connect dgmgrl from your primary DB. (just for troubleshooting..not mandatory)
    show configuration verbose

    Tobi

    Edited by: teits on Nov 16, 2012 10:14 AM
  • 22. Re: ORA-12514 When trying to add the secondary physical database
    894939 Newbie
    Currently Being Moderated
    Hi Todi,

    Please find details below as requested

    Configuration - dg_pr

    Protection Mode: MaxPerformance
    Databases:
    dgp - Primary database

    Properties:
    FastStartFailoverThreshold = '30'
    OperationTimeout = '30'
    FastStartFailoverLagLimit = '30'
    CommunicationTimeout = '180'
    FastStartFailoverAutoReinstate = 'TRUE'
    FastStartFailoverPmyShutdown = 'TRUE'
    BystandersFollowRoleChange = 'ALL'

    Fast-Start Failover: DISABLED

    Configuration Status:
    DISABLED


    Thanks,
    Shanaka.
  • 23. Re: ORA-12514 When trying to add the secondary physical database
    teits Journeyer
    Currently Being Moderated
    Hi,

    From primary DB.
    $lsnrctl services
    $sqlplus sys/password@DGR   --EDITED: Connect to the standby DB. not primary db!. from your previous post DGR tns is for your standby. secondly do not edit the result or the command you execute. thks
    SQL>SELECT DB_UNIQUE_NAME,PRIMARY_DB_UNIQUE_NAME, DATABASE_ROLE FROM V$DATABASE;
    SQL> exit
    dgmgrl sys/password
    dgmgrl>ADD DATABASE dgr AS CONNECT IDENTIFIER IS DGR
    paste the complete result here. exactly.


    Tobi

    Edited by: teits on Nov 18, 2012 12:47 PM
  • 24. Re: ORA-12514 When trying to add the secondary physical database
    894939 Newbie
    Currently Being Moderated
    Hi Todi,

    I really appreciate and thank you very much for your continuous help on my error. Please find those details below


    oracledgp:oradgp 50> lsnrctl services

    LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 18-NOV-2012 17:32:02

    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:998 refused:0
    LOCAL SERVER
    The command completed successfully
    oracledgp:oradgp 51>


    SQL> SELECT DB_UNIQUE_NAME,PRIMARY_DB_UNIQUE_NAME, DATABASE_ROLE FROM V$DATABASE;

    DB_UNIQUE_NAME PRIMARY_DB_UNIQUE_NAME DATABASE_ROLE
    ................ ....................... ...................
    DGP PRIMARY

    ADD DATABASE dgr AS CONNECT IDENTIFIER IS DGR;

    Error: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

    Failed.

    Thanks,
    Shanaka.
  • 25. Re: ORA-12514 When trying to add the secondary physical database
    894939 Newbie
    Currently Being Moderated
    DB_UNIQUE_NAME :- DGP

    DATABASE_ROLE:- PRIMARY

    PRIMARY_DB_UNIQUE_NAME:- (BLANK)

    Edited by: Shanaka Chandrasekera on Nov 19, 2012 12:12 AM
  • 26. Re: ORA-12514 When trying to add the secondary physical database
    mseberg Guru
    Currently Being Moderated
    Hello;

    Not sure if you have checked this document out :

    Setting up Oracle 11g Data Guard for SAP customers

    http://www.oracle.com/us/solutions/sap/wp-ora4sap-dataguard11g-303811.pdf

    If you search for TNS-12514 you may see something you not tried yet.

    Also when you post something like this use { code } ( without spaces ) to protect the formatting. (
    { code }
    
    LISTENER =
        (ADDRESS_LIST =
          (ADDRESS =
           (PROTOCOL = IPC)
            (KEY = DGP.WORLD)
         )
         (ADDRESS= (PROTOCOL = IPC) (KEY = DGP))
         (ADDRESS = (COMMUNITY = SAP.WORLD) (PROTOCOL = TCP)(HOST = oracledgp) (PORT = 1527))
    )
    
    SID_LIST_LISTENER =(SID_LIST =
                            (SID_DESC =
                            (GLOBAL_DBNAME=DGP_DGMGRL.oracledgp)
                            (ORACLE_HOME = /oracle/DGP/112_64)
                            (SID_NAME = DGP)
                            )
                       )
    
    ADMIN_RESTRICTIONS_LISTENER = on
    
    
    STARTUP_WAIT_TIME_LISTENER = 0
    CONNECT_TIMEOUT_LISTENER = 10
    TRACE_LEVEL_LISTENER = OFF
    Would look for more information in drc<sid>.log and sqlnet.log log files as they state.

    My listener example (works)
    # listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0.3/network/admin/listener.ora
    # Generated by Oracle configuration tools.
    
    LISTENER =
      (DESCRIPTION_LIST =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = primary.localdomain)(PORT = 1521))
        )
      )
      
    SID_LIST_LISTENER = (SID_LIST =
                            (SID_DESC =
                            (SID_NAME = PRIMARY)
                            (ORACLE_HOME = /u01/app/oracle/product/11.2.0.3)
                            (SDU=32767)
                            (GLOBAL_DBNAME = PRIMARY_DGMGRL.localdomain)
                            )
                           (SID_DESC =
                           (global_dbname = PRIMARY)
                           (ORACLE_HOME = /u01/app/oracle/product/11.2.0.3)
                           (sid_name = PRIMARY)
                           )
                        )    
    
      
      
    ADR_BASE_LISTENER = /u01/app/oracle
    Also put both static entries in the listener.ora and make sure you restart the listener
    SID_LIST_LISTENER =
           (SID_LIST =
             (SID_DESC =
               (GLOBAL_DBNAME = DG1.oracledgp)
               (ORACLE_HOME = /oracle/DGP/112_64)
               (SID_NAME = DGP)
             )
             (SID_DESC =
               (GLOBAL_DBNAME = DG1_DGMGRL.oracledgp)
               (ORACLE_HOME = /oracle/DGP/112_64)
               (SID_NAME = DGP)
             )
           )
    Diagnosing Connection Problems with an active Data Guard Broker Configuration [ID 745201.1]

    Interaction Between the Data Guard Broker and a Data Guard Configuration [ID 249703.1]

    Best Regards

    mseberg

    Edited by: mseberg on Nov 18, 2012 8:31 AM
  • 27. Re: ORA-12514 When trying to add the secondary physical database
    894939 Newbie
    Currently Being Moderated
    Hello mseberg,

    Thanks for those information regarding CODE :) .... Yes I'm following the same document which you have mentioned here

    I have tried those steps in that error


    AUTOMATIC_IPC = ON
    TRACE_LEVEL_CLIENT = OFF
    NAMES.DEFAULT_DOMAIN = WORLD
    NAMES.DIRECTORY_PATH = (TNSNAMES)
    # 05.01.06 unsorported parameter now
    #NAME.DEFAULT_ZONE = WORLD
    # 05.01.06 set the default to 10
    SQLNET.EXPIRE_TIME = 10
    # 05.01.06 set to default
    #TCP.NODELAY=YES
    # 05.01.06 set to 32768
    DEFAULT_SDU_SIZE=32768
    +



    Code didn't work here :(
  • 28. Re: ORA-12514 When trying to add the secondary physical database
    teits Journeyer
    Currently Being Moderated
    Hi Shanaka,

    check my last post. you didn't do exactly what i requested. plz, take time to go thru it careful.
    see my last post on your thread.

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

    Sorry I didn't read the post completely , Please find them below , My dgmgrl commands are executed from my secondary DB.

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

    SQL> SELECT DB_UNIQUE_NAME,PRIMARY_DB_UNIQUE_NAME, DATABASE_ROLE FROM V$DATABASE;

    DB_UNIQUE_NAMEPRIMARY_DB_UNIQUE_NAME DATABASE_ROLE
    ------------------------------ ------------------------------ ----------------
    DGR PRIMARY

    SQL> exit
    Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    oracledgr:oradgp 51> dgmgrl sys/passw0rd
    DGMGRL for Linux: Version 11.2.0.2.0 - 64bit Production

    Copyright (c) 2000, 2009, Oracle. All rights reserved.

    Welcome to DGMGRL, type "help" for information.
    Connected.
    DGMGRL> ADD DATABASE dgr AS CONNECT IDENTIFIER IS DGR;
    Error: ORA-16532: Data Guard broker configuration does not exist

    Failed.
    DGMGRL>


    Even in secondary DB the role assigned is PRIMARY :(


    This is what I found in drcDGP.log

    2012-11-19 13:38:59.176 DMON: Add Database failed. Cannot get database type of the database to be added. Error is: 12514.
    2012-11-19 13:38:59.177 NSV1: NetSlave shutting down.
    2012-11-19 13:39:00.176 DMON: NSV1 has been stopped
    2012-11-19 13:39:00.177 00000000 1905911710 DMON: ADD_DATABASE: (error=ORA-12514)
    2012-11-19 13:39:00.178 DMON: Reverting metadata (1.1.1905911710), loading from "/oracle/DGP/112_64/dbs/dr1DGP.dat"
    2012-11-19 13:39:00.199 DMON: ...committing load to memory, Seq.MIV = 0.2
    2012-11-19 13:39:00.200 00000000 1905911710 DMON: Entered rfm_release_chief_lock() for ADD_DATABASE
    2012-11-20 09:06:12.220 00001000 1905911711 DMON: Entered rfm_get_chief_lock() for MON_VERIFY, reason 0
    2012-11-20 09:06:12.246 00001000 1905911711 DMON: start task execution: client healthcheck
    2012-11-20 09:06:12.247 00001000 1905911711 DMON: Entered rfm_release_chief_lock() for MON_VERIFY




    Thanks,
    Shanaka.

    Edited by: Shanaka Chandrasekera on Nov 20, 2012 1:43 AM

Legend

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