1 2 Previous Next 21 Replies Latest reply: Jul 31, 2013 11:11 AM by Abufazal RSS

    [DataGuard][Error 12514]  Error received logging on to the standby

    Piotr91

      Hello everyone,

      At first I'd like to say I'm a beginer in Oracle databases and I need help. I need to configure simple standby database. I have followed this http://www.oracle-base.com/articles/11g/data-guard-setup-11gr2.php tutorial to do that. Problem is that primary db cannot log on to the standby db. Informations privided below:

      Primary DB:

      CentOS 6.4

      Oracle 11gR2

      ORACLE_SID=primdb1

      SQL> SELECT MESSAGE FROM V$DATAGUARD_STATUS;

       

      MESSAGE

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

      ARC0: Archival started

      ARC1: Archival started

      ARC2: Archival started

      ARC1: Becoming the 'no FAL' ARCH

      ARC1: Becoming the 'no SRL' ARCH

      ARC2: Becoming the heartbeat ARCH

      ARC1: Beginning to archive thread 1 sequence 31 (336165-356856)

      Error 12514 received logging on to the standby

      PING[ARC2]: Heartbeat failed to connect to standby 'stbydb1'. Error is 12514.

      ARC1: Completed archiving thread 1 sequence 31 (336165-356856)

      ARC3: Archival started

       

      MESSAGE

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

      Error 12514 received logging on to the standby

      FAL[server, ARC2]: Error 12514 creating remote archivelog file 'stbydb1'

      ARC3: Beginning to archive thread 1 sequence 32 (356856-357024)

      ARC3: Completed archiving thread 1 sequence 32 (356856-357024)

      ARC0: Beginning to archive thread 1 sequence 33 (357024-357138)

      ARC0: Completed archiving thread 1 sequence 33 (357024-357138)

       

      17 rows selected.

       

      [oracle@oracled dbhome_1]$ bin/lsnrctl status

       

      LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 31-JUL-2013 11:00:13

       

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

       

      Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

      STATUS of the LISTENER

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

      Alias                     LISTENER

      Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production

      Start Date                31-JUL-2013 10:37:38

      Uptime                    0 days 0 hr. 22 min. 35 sec

      Trace Level               off

      Security                  ON: Local OS Authentication

      SNMP                      OFF

      Listener Log File         /u01/app/oracle/diag/tnslsnr/oracled/listener/alert/log.xml

      Listening Endpoints Summary...

        (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracled)(PORT=1521)))

      Services Summary...

      Service "primdb1" has 1 instance(s).

        Instance "primdb1", status READY, has 1 handler(s) for this service...

      The command completed successfully

       

      [oracle@oracled dbhome_1]$ bin/tnsping stbydb1

       

      TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 31-JUL-2013 11:00:55

       

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

       

      Used parameter files:

       

       

      Used TNSNAMES adapter to resolve the alias

      Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST =10.132.28.33)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = stbydb1)))

      OK (0 msec)

       

      [oracle@oracled dbhome_1]$ cat network/admin/tnsnames.ora

      primdb1 =

        (DESCRIPTION =

          (ADDRESS_LIST =

            (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.234)(PORT = 1521))

          )

          (CONNECT_DATA =

            (SERVICE_NAME = primdb1)

          )

        )

       

      stbydb1 =

        (DESCRIPTION =

          (ADDRESS_LIST =

            (ADDRESS = (PROTOCOL = TCP)(HOST =10.132.28.33)(PORT = 1521))

          )

          (CONNECT_DATA =

            (SERVICE_NAME = stbydb1)

          )

        )

       

      Standby DB:

      CentOS 6.4

      Oracle 11gR2

      ORACLE_SID=primdb1

       

      SQL> SELECT MESSAGE FROM V$DATAGUARD_STATUS;   

       

      MESSAGE

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

      ARC0: Archival started

      ARC1: Archival started

      ARC2: Archival started

      ARC1: Becoming the 'no FAL' ARCH

      ARC2: Becoming the heartbeat ARCH

      Error 1017 received logging on to the standby

      FAL[client, ARC2]: Error 16191 connecting to primdb1 for fetching gap sequence

      ARC3: Archival started

      Attempt to start background Managed Standby Recovery process

      MRP0: Background Managed Standby Recovery process started

      Managed Standby Recovery not using Real Time Apply

       

      MESSAGE

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

      Clearing online redo logfile 1 /u01/app/oracle/oradata/primdb1/redo01.log

      Clearing online redo logfile 1 complete

      Media Recovery Waiting for thread 1 sequence 22

       

      14 rows selected.

       

      [oracle@rh1 dbhome_1]$  bin/lsnrctl status


       

      LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 31-JUL-2013 07:05:55

       

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

       

      Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

      STATUS of the LISTENER

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

      Alias                     LISTENER

      Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production

      Start Date                31-JUL-2013 06:39:13

      Uptime                    0 days 0 hr. 26 min. 42 sec

      Trace Level               off

      Security                  ON: Local OS Authentication

      SNMP                      OFF

      Listener Log File         /u01/app/oracle/diag/tnslsnr/rh1/listener/alert/log.xml

      Listening Endpoints Summary...

        (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rh1.localdomain)(PORT=1521)))

      Services Summary...

      Service "db" has 1 instance(s).

        Instance "db", status READY, has 1 handler(s) for this service...

      Service "stbydb1" has 1 instance(s).

        Instance "primdb1", status READY, has 1 handler(s) for this service...

      The command completed successfully

      [oracle@rh1 dbhome_1]$ cat network/admin/tnsnames.ora

      db =

        (DESCRIPTION =

          (ADDRESS_LIST =

            (ADDRESS = (PROTOCOL = TCP)(HOST = 10.132.28.33)(PORT = 1521))

          )

          (CONNECT_DATA =

            (SERVICE_NAME = db.WORLD)

          )

        )

       

      db2 =

        (DESCRIPTION =

          (ADDRESS_LIST =

            (ADDRESS = (PROTOCOL = TCP)(HOST = 10.132.28.37)(PORT = 1521))

          )

          (CONNECT_DATA =

            (SERVICE_NAME = db.WORLD)

          )

        )

      primdb1 =

        (DESCRIPTION =

          (ADDRESS_LIST =

            (ADDRESS = (PROTOCOL = TCP)(HOST = 10.132.28.31)(PORT = 1521))

          )

          (CONNECT_DATA =

            (SERVICE_NAME = primdb1)

          )

        )

       

      stbydb1 =

        (DESCRIPTION =

          (ADDRESS_LIST =

            (ADDRESS = (PROTOCOL = TCP)(HOST = 10.132.28.33)(PORT = 1521))

          )

          (CONNECT_DATA =

            (SERVICE_NAME = stbydb1)

          )

        )

       

      Thanks in advance for help.

      Cheers.

        • 2. Re: [DataGuard][Error 12514]  Error received logging on to the standby
          Abufazal

          Looks like, there is connectivity issue between your primary and standby database.

          Are you able to do remote login to each of the database?



          • 3. Re: [DataGuard][Error 12514]  Error received logging on to the standby
            JohnWatson

            What is your log_archive_dest_n parameter set to on the primary? It would seem likely that it is attempting to send redo to a wrong (or non-existent) service.

            • 4. Re: [DataGuard][Error 12514]  Error received logging on to the standby
              Piotr91

              How can I do this? My attempt shown below:

              [oracle@oracled dbhome_1]$ $SQLP sys/oracle@stbydb1

               

              SQL*Plus: Release 11.2.0.1.0 Production on Wed Jul 31 12:02:10 2013

               

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

               

              ERROR:

              ORA-01033: ORACLE initialization or shutdown in progress

              Process ID: 0

              Session ID: 0 Serial number: 0

               

               

              Enter user-name: sys

              Enter password: oracle

              ERROR:

              ORA-01017: invalid username/password; logon denied

               

               

              Enter user-name: sys

              Enter password: sys_password

              ERROR:

              ORA-01017: invalid username/password; logon denied

               

               

              SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

               

              none of passwords works. pwd file for stbydb1 is 'oracle', however during creating primary db (primdb1) i have specified password for sys user 'sys_password'.

              CREATE DATABASE primdb1

              USER SYS IDENTIFIED BY sys_password

               

              .

              .

              .

              and the standby db stbydb1 was created using RMAN's hotbackup feature.

              • 5. Re: [DataGuard][Error 12514]  Error received logging on to the standby
                Piotr91

                SQL> show parameter log_archive_dest;

                 

                NAME                     TYPE     VALUE

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

                log_archive_dest             string

                log_archive_dest_1             string     location=$ORADATA/primdb1/arch

                                         ive_logs/ valid_for=(all_logfi

                                         les,all_roles) db_unique_name=

                                         primdb1

                log_archive_dest_10             string

                log_archive_dest_11             string

                log_archive_dest_12             string

                log_archive_dest_13             string

                log_archive_dest_14             string

                log_archive_dest_15             string

                 

                NAME                     TYPE     VALUE

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

                log_archive_dest_16             string

                log_archive_dest_17             string

                log_archive_dest_18             string

                log_archive_dest_19             string

                log_archive_dest_2             string     service=stbydb1 ASYNC valid_fo

                                         r=(online_logfiles,primary_rol

                                         e) db_unique_name=stbydb1

                • 6. Re: [DataGuard][Error 12514]  Error received logging on to the standby
                  JohnWatson

                  That looks OK. But you have not configured static registration for the service in the listeners, you need to do that.

                  • 7. Re: [DataGuard][Error 12514]  Error received logging on to the standby
                    Abufazal

                    While logging in to standby database, should connect as sys/<password>@<tns_alias> as sysdba

                     

                    Could you paste the errors from both primary and standby alert log files

                    • 8. Re: [DataGuard][Error 12514]  Error received logging on to the standby
                      Piotr91

                      ***********************************************************************

                       

                      Fatal NI connect error 12514, connecting to:

                      (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.132.28.33)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=stbydb1)(CID=(PROGRAM=oracle)(HOST=oracled)(USER=oracle))))

                       

                        VERSION INFORMATION:

                          TNS for Linux: Version 11.2.0.1.0 - Production

                          TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production

                        Time: 31-JUL-2013 12:18:07

                        Tracing not turned on.

                        Tns error struct:

                          ns main err code: 12564

                        

                      TNS-12564: TNS:connection refused

                          ns secondary err code: 0

                          nt main err code: 0

                          nt secondary err code: 0

                          nt OS err code: 0

                      Error 12514 received logging on to the standby

                      Errors in file /u01/app/oracle/diag/rdbms/primdb1/primdb1/trace/primdb1_arc2_1305.trc:

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

                      PING[ARC2]: Heartbeat failed to connect to standby 'stbydb1'. Error is 12514.

                      Wed Jul 31 12:24:07 2013

                      Errors in file /u01/app/oracle/diag/rdbms/primdb1/primdb1/trace/primdb1_arc2_1305.trc:

                      ORA-01017: invalid username/password; logon denied

                      Error 1017 received logging on to the standby

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

                      Check that the primary and standby are using a password file

                      and remote_login_passwordfile is set to SHARED or EXCLUSIVE,

                      and that the SYS password is same in the password files.

                            returning error ORA-16191

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

                      Errors in file /u01/app/oracle/diag/rdbms/primdb1/primdb1/trace/primdb1_arc2_1305.trc:

                      ORA-16191: Primary log shipping client not logged on standby

                      PING[ARC2]: Heartbeat failed to connect to standby 'stbydb1'. Error is 16191.

                      Wed Jul 31 12:30:07 2013

                      Errors in file /u01/app/oracle/diag/rdbms/primdb1/primdb1/trace/primdb1_arc2_1305.trc:

                      ORA-01017: invalid username/password; logon denied

                      Error 1017 received logging on to the standby

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

                      Check that the primary and standby are using a password file

                      and remote_login_passwordfile is set to SHARED or EXCLUSIVE,

                      and that the SYS password is same in the password files.

                            returning error ORA-16191

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

                       

                       

                      1. remote_login_passwordfile is set to exclusive-yes, it is.

                      2. how to check if sys password is the same in password files?

                      • 9. Re: [DataGuard][Error 12514]  Error received logging on to the standby
                        Piotr91

                        Do I really need to do listener static configuration if listeners status are as follows?

                        The status is 'READY' so correct me if I'm wrong but I think that default listeners are registered automatically.

                        PRIMARY DB:

                        [oracle@oracled dbhome_1]$ bin/lsnrctl status

                         

                        LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 31-JUL-2013 11:00:13

                         

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

                         

                        Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

                        STATUS of the LISTENER

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

                        Alias                     LISTENER

                        Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production

                        Start Date                31-JUL-2013 10:37:38

                        Uptime                    0 days 0 hr. 22 min. 35 sec

                        Trace Level               off

                        Security                  ON: Local OS Authentication

                        SNMP                      OFF

                        Listener Log File         /u01/app/oracle/diag/tnslsnr/oracled/listener/alert/log.xml

                        Listening Endpoints Summary...

                          (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracled)(PORT=1521)))

                        Services Summary...

                        Service "primdb1" has 1 instance(s).

                          Instance "primdb1", status READY, has 1 handler(s) for this service...

                        The command completed successfully

                         

                        STANDBY DB:

                        [oracle@rh1 dbhome_1]$  bin/lsnrctl status


                         

                        LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 31-JUL-2013 07:05:55

                         

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

                         

                        Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

                        STATUS of the LISTENER

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

                        Alias                     LISTENER

                        Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production

                        Start Date                31-JUL-2013 06:39:13

                        Uptime                    0 days 0 hr. 26 min. 42 sec

                        Trace Level               off

                        Security                  ON: Local OS Authentication

                        SNMP                      OFF

                        Listener Log File         /u01/app/oracle/diag/tnslsnr/rh1/listener/alert/log.xml

                        Listening Endpoints Summary...

                          (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rh1.localdomain)(PORT=1521)))

                        Services Summary...

                        Service "db" has 1 instance(s).

                          Instance "db", status READY, has 1 handler(s) for this service...

                        Service "stbydb1" has 1 instance(s).

                          Instance "primdb1", status READY, has 1 handler(s) for this service...

                        The command completed successfully

                        • 10. Re: [DataGuard][Error 12514]  Error received logging on to the standby
                          JohnWatson

                          Piotr91 wrote:

                           

                          Do I really need to do listener static configuration if listeners status are as follows?

                          The status is 'READY' so correct me if I'm wrong but I think that default listeners are registered automatically.

                          Trust me. I'm a DBA.

                          • 11. Re: [DataGuard][Error 12514]  Error received logging on to the standby
                            Abufazal

                            Have you copied the password file from primary to standby as part of the standby creation?

                             

                            Also, please provide the output of the following command from both primary and standby

                             

                            SQL> show parameter service

                            • 12. Re: [DataGuard][Error 12514]  Error received logging on to the standby
                              Piotr91

                              Abufazal wrote:

                               

                              Have you copied the password file from primary to standby as part of the standby creation?

                              Yes, i did. However I don't understand why my primdb1 does not use password file.

                               

                              [oracle@oracled trace]$ $SQLP sys/oracle as sysdba

                               

                              SQL*Plus: Release 11.2.0.1.0 Production on Wed Jul 31 13:30:36 2013

                               

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

                               

                               

                              Connected to:

                              Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

                              With the Partitioning, OLAP, Data Mining and Real Application Testing options

                               

                              SQL> select * from v$pwfile_users;

                               

                              no rows selected

                               

                               

                              Also, please provide the output of the following command from both primary and standby

                               

                              SQL> show parameter service

                              PRIMDB:

                              SQL> show parameter service

                               

                              NAME                     TYPE     VALUE

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

                              service_names                 string     PRIMDB1

                               

                              STANDBY DB:

                              SQL> show parameter service

                               

                              NAME                     TYPE     VALUE

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

                              service_names                 string     stbydb1

                              • 13. Re: [DataGuard][Error 12514]  Error received logging on to the standby
                                Abufazal

                                Looks like, the password file is not working.

                                Try to generate a new password file using orapwd on primary and then copy it to standby server and rename accordingly.

                                Once the password file is created and copied, try connecting to standby database from primary server as follows

                                 

                                sqlplus sys/<password given during orapwd>@stbydb1 as sysdba

                                • 14. Re: [DataGuard][Error 12514]  Error received logging on to the standby
                                  Piotr91

                                  Try to generate a new password file using orapwd on primary and then copy it to standby server and rename accordingly.

                                  What name should have the password file on standby server?

                                  On standby and primary server ORACLE_SID=primdb1. The db_unique_name=stbydb1 for standby and db_unique_name=primdb1 for primary.

                                  1 2 Previous Next