1 2 3 Previous Next 37 Replies Latest reply: Jan 22, 2013 8:01 AM by 986383 Go to original post RSS
      • 15. Re: ORA-12514 When trying to add the secondary physical database
        teits
        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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                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
                                  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
                                    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