This discussion is archived
1 2 Previous Next 21 Replies Latest reply: Jul 31, 2013 9:11 AM by Abufazal RSS

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

Piotr91 Newbie
Currently Being Moderated

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 Journeyer
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Journeyer
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

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

     

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Journeyer
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Journeyer
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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

Legend

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