1 2 3 4 Previous Next 58 Replies Latest reply: Nov 18, 2013 5:13 AM by petra-K Go to original post RSS
      • 30. Re: DG Primary Not sending arch to Standby

        Hi Anar,

         

        Still not working

         

        At Primary:

         

        SQL> select process,status from v$managed_standby where process='MRP0';

        select status,instance_name,database_role from v$database,v$instance;

        select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;

        select current_scn from v$database;

        select * from v$services;

        select name,value from v$dataguard_stats;

        no rows selected

         

         

        SQL> OPEN            orcl             PRIMARY

         

         

        SQL>

        no rows selected

         

         

        SQL>    15009017

         

         

        SQL>     5 orclXDB                                                          3468872077

        orclXDB

        30-OCT-09          814562693 NONE         N NO  LONG

         

         

                 6 orcl                                                             2392458149

        orcl

        30-OCT-09          814562693 NONE         N NO  LONG

         

         

                 1 SYS$BACKGROUND                                                    165959219

         

         

        13-AUG-09                  0 NONE         N NO  SHORT

         

         

                 2 SYS$USERS                                                        3427055676

         

         

        13-AUG-09          274466592 NONE         N NO  SHORT

         

         

         

         

        SQL>

         

         

        no rows selected

         

         

        SQL>

         

        =============================

         

        At Standby:

         

        select process,status from v$managed_standby where process='MRP0';

        select status,instance_name,database_role from v$database,v$instance;

        select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;

        select current_scn from v$database;

        select * from v$services;

        select name,value from v$dataguard_stats;

        SQL> SQL> SQL> MRP0       WAIT_FOR_LOG

         

         

        SQL> MOUNTED      orcl        PHYSICAL STANDBY

         

         

        SQL>     1            639

         

         

        SQL>    14471107

         

         

        SQL>     0                                                                           0

        orclXDB

                                   0 NONE         N NO  SHORT

         

         

                 0 orcl_stby                                                        1238890504

        orcl_stby

                                   0 NONE         N NO  SHORT

         

         

                 0 SYS$BACKGROUND                                                    165959219

         

         

                                   0 NONE         N NO  SHORT

         

         

                 0 SYS$USERS                                                        3427055676

         

         

                                   0 NONE         N NO  SHORT

         

         

         

         

        SQL> transport lag

        apply lag

        apply finish time

        estimated startup time           17

         

         

        SQL>

        • 31. Re: DG Primary Not sending arch to Standby

          Hi Anar,

           

          Could there be a problem with my Standby DB?

           

          I tried to open it without resetlogs but it was mounted and opened.

          How do I return it back to standby mode?

           

           

          SQL> shutdown immediate;

          ORA-01109: database not open

           

           

           

           

          Database dismounted.

          ORACLE instance shut down.

          SQL> startup

          ORACLE instance started.

           

           

          Total System Global Area  456146944 bytes

          Fixed Size                  1344840 bytes

          Variable Size             356518584 bytes

          Database Buffers           92274688 bytes

          Redo Buffers                6008832 bytes

          Database mounted.

          Database opened.

           

          =========================

           

          shut immediate

          STARTUP NOMOUNT;

          ALTER DATABASE MOUNT STANDBY DATABASE;

          ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

           

          went ok...

           

          Then I tried to replace the listener and tnsnames with IP instead of server name but still to no avail

          The alert log at Primary can not ping the heartbeat of Standby. Could it be cause by the net config at linux side?

           

          Fatal NI connect error 12504, connecting to:

          (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.102)(PORT=1521))(SERVER=DEDICATED)(SERVICE_NAME=orcl)(CONNECT_DATA=(CID=(PROGRAM=oracle)(HOST=server1.localdomain)(USER=oracle))))

           

           

            VERSION INFORMATION:

                  TNS for Linux: Version 11.2.0.2.0 - Production

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

            Time: 14-NOV-2013 19:07:46

            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 12504 received logging on to the standby

          PING[ARC2]: Heartbeat failed to connect to standby 'orcl_stby'. Error is 12504.

          • 32. Re: DG Primary Not sending arch to Standby
            DBsync

            Hi,

             

            Make sure that you have the same password file in your both primary and standby database.

             

            Can you post the output of the following query.

             

            Execute it from your primary database

            select error from v$archive_dest;

             

            And if you find any errors in your alert log please post that also.

            • 33. Re: DG Primary Not sending arch to Standby
              Anar Godjaev

              Hi,

               

              I have looked at configuration you sent me  . Could you please connect to Standby database  from Primary database with sqlplus"/ as sysdba" ?

               

              And  run below query in Primary database, send me error column status.

               

              select a.DEST_NAME, a.STATUS, a.TYPE, a.DATABASE_MODE, a.RECOVERY_MODE, a.DESTINATION, a.ERROR from V$ARCHIVE_DEST_STATUS a;

               

              Thank you

              • 34. Re: DG Primary Not sending arch to Standby
                DBsync

                Hi ,

                 

                Look into this You might get some clue.

                 

                *Cause:  The listener was not configured with a default service and

                //  SERVICE_NAME was missing from the CONNECT_DATA received by the listener.

                 

                // *Action: Possible solutions are:

                //  - Configure DEFAULT_SERVICE parameter in LISTENER.ORA with a valid service

                //    name. Reload the listener parameter file using reload [<listener_name>].

                //    This should be done by the LISTENER administrator.

                //  - If using a service name, Check that the connect descriptor corresponding

                //    to the service name in TNSNAMES.ORA has a SERVICE_NAME or SID component

                //    in the CONNECT_DATA.

                • 35. Re: DG Primary Not sending arch to Standby

                  DEST_NAME                                                                                                                                                    STATUS    TYPE    DATABASE_MODE   RECOVERY_MODE           DESTINATION                                                                                          ERROR

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

                  LOG_ARCHIVE_DEST_1                                                                                                                                           VALID      LOCAL          OPEN            IDLE                    /home/oracle/app/oracle/product/11.2.0/dbhome_2/dbs/arch

                  LOG_ARCHIVE_DEST_2                                                                                                                                           ERROR      UNKNOWN        UNKNOWN         IDLE                    orcl_stby                                                                                    ORA-12504: TNS:listener was not given the SERVICE_NAME in

                                                                                                                                                                                 CONNECT_DATA

                   

                   

                  LOG_ARCHIVE_DEST_3                                                                                                                                           INACTIVE  LOCAL   UNKNOWN         IDLE

                  LOG_ARCHIVE_DEST_4                                                                                                                                           INACTIVE  LOCAL   UNKNOWN         IDLE

                  LOG_ARCHIVE_DEST_5                                                                                                                                           INACTIVE  LOCAL   UNKNOWN         IDLE

                  LOG_ARCHIVE_DEST_6                                                                                                                                           INACTIVE  LOCAL   UNKNOWN         IDLE

                  LOG_ARCHIVE_DEST_7                                                                                                                                           INACTIVE  LOCAL   UNKNOWN         IDLE

                  LOG_ARCHIVE_DEST_8                                                                                                                                           INACTIVE  LOCAL   UNKNOWN         IDLE

                  LOG_ARCHIVE_DEST_9                                                                                                                                           INACTIVE  LOCAL   UNKNOWN         IDLE

                  LOG_ARCHIVE_DEST_10                                                                                                                                          INACTIVE  LOCAL   UNKNOWN         IDLE

                  LOG_ARCHIVE_DEST_11                                                                                                                                          INACTIVE  LOCAL   UNKNOWN         IDLE

                  LOG_ARCHIVE_DEST_12                                                                                                                                          INACTIVE  LOCAL   UNKNOWN         IDLE

                  LOG_ARCHIVE_DEST_13                                                                                                                                          INACTIVE  LOCAL   UNKNOWN         IDLE

                  LOG_ARCHIVE_DEST_14                                                                                                                                          INACTIVE  LOCAL   UNKNOWN         IDLE

                  LOG_ARCHIVE_DEST_15                                                                                                                                          INACTIVE  LOCAL   UNKNOWN         IDLE

                  LOG_ARCHIVE_DEST_16                                                                                                                                          INACTIVE  LOCAL   UNKNOWN         IDLE

                  LOG_ARCHIVE_DEST_17                                                                                                                                          INACTIVE  LOCAL   UNKNOWN         IDLE

                  LOG_ARCHIVE_DEST_18                                                                                                                                          INACTIVE  LOCAL   UNKNOWN         IDLE

                  LOG_ARCHIVE_DEST_19                                                                                                                                          INACTIVE  LOCAL   UNKNOWN         IDLE

                  LOG_ARCHIVE_DEST_20                                                                                                                                          INACTIVE  LOCAL   UNKNOWN         IDLE

                  LOG_ARCHIVE_DEST_21                                                                                                                                          INACTIVE  LOCAL   UNKNOWN         IDLE

                  LOG_ARCHIVE_DEST_22                                                                                                                                          INACTIVE  LOCAL   UNKNOWN         IDLE

                  LOG_ARCHIVE_DEST_23                                                                                                                                          INACTIVE  LOCAL   UNKNOWN         IDLE

                  LOG_ARCHIVE_DEST_24                                                                                                                                          INACTIVE  LOCAL   UNKNOWN         IDLE

                  LOG_ARCHIVE_DEST_25                                                                                                                                          INACTIVE  LOCAL   UNKNOWN         IDLE

                  LOG_ARCHIVE_DEST_26                                                                                                                                          INACTIVE  LOCAL   UNKNOWN         IDLE

                  LOG_ARCHIVE_DEST_27                                                                                                                                          INACTIVE  LOCAL   UNKNOWN         IDLE

                  LOG_ARCHIVE_DEST_28                                                                                                                                          INACTIVE  LOCAL   UNKNOWN         IDLE

                  LOG_ARCHIVE_DEST_29                                                                                                                                          INACTIVE  LOCAL   UNKNOWN         IDLE

                  LOG_ARCHIVE_DEST_30                                                                                                                                          INACTIVE  LOCAL   UNKNOWN         IDLE

                  LOG_ARCHIVE_DEST_31                                                                                                                                          INACTIVE  LOCAL   UNKNOWN         IDLE

                   

                   

                  31 rows selected.

                  • 36. Re: DG Primary Not sending arch to Standby

                    SQL> select error from v$archive_dest;

                     

                     

                    ERROR

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

                     

                     

                    ORA-12504: TNS:listener was not given the SERVICE_NAME in

                    CONNECT_DATA

                    • 37. Re: DG Primary Not sending arch to Standby

                      Where is this CONNECT_DATA?

                      • 38. Re: DG Primary Not sending arch to Standby
                        Anar Godjaev

                        Ok. and run this query in Primary:

                         

                         

                        SELECT a.DEST_NAME, a.STATUS, a.TYPE, a.DATABASE_MODE, a.RECOVERY_MODE, a.DESTINATION, a.ERROR from V$ARCHIVE_DEST_STATUS a

                        WHERE a.DEST_ID IN (1,2,3)

                        • 39. Re: DG Primary Not sending arch to Standby
                          Anar Godjaev

                          Could you please connect to Standby database  from Primary database with sqlplus"/ as sysdba" ?

                           

                          Primary database: sqlplus sys/pass@ORCL_STBY as sysdba;

                           


                          • 40. Re: DG Primary Not sending arch to Standby

                            DEST_NAME                                                                                                                          STATUS    TYPE    DATABASE_MODE   RECOVERY_MODE           DESTINATION                                                                ERROR

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

                            LOG_ARCHIVE_DEST_1                                                                                                                 VALID      LOCAL          OPEN            IDLE                    /home/oracle/app/oracle/product/11.2.0/dbhome_2/dbs/arch

                            LOG_ARCHIVE_DEST_2                                                                                                                 ERROR      UNKNOWN        UNKNOWN         IDLE                    orcl_stby                                                          ORA-12504: TNS:listener was not given the SERVICE_NAME in

                                                                                                                                                                 CONNECT_DATA

                             

                             

                            LOG_ARCHIVE_DEST_3                                                                                                                 INACTIVE  LOCAL   UNKNOWN         IDLE

                            • 41. Re: DG Primary Not sending arch to Standby

                              sqlplus system/oracle@orcl_stby as sysdba

                               

                               

                              SQL*Plus: Release 11.2.0.2.0 Production on Thu Nov 14 22:23:31 2013

                               

                               

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

                               

                               

                              ERROR:

                              ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

                               

                              Enter user-name:

                               

                               

                              But If I use this

                              Connecting from Primary to Standby using:

                               

                              sqlplus sys/oracle@server2:1521/orcl_stby as sysdba

                               

                               

                               

                              SQL*Plus: Release 11.2.0.2.0 Production on Thu Nov 14 22:33:13 2013

                               

                               

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

                               

                               

                               

                               

                              Connected to:

                              Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production

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

                               

                               

                              SQL>

                              • 42. Re: DG Primary Not sending arch to Standby
                                Anar Godjaev

                                Could you please connect to Standby database  from Primary database with sqlplus"/ as sysdba" ?

                                 

                                Primary database: sqlplus sys/pass@ORCL_STBY as sysdba;

                                 

                                and paste result.

                                • 43. Re: DG Primary Not sending arch to Standby
                                  Anar Godjaev

                                  And please Standby database : lsnrctl status listener

                                   

                                  paste here.

                                   

                                  (the latter for examination)


                                  • 44. Re: DG Primary Not sending arch to Standby

                                    [oracle@server2 admin]$ lsnrctl status listener

                                     

                                     

                                    LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 14-NOV-2013 22:27:29

                                     

                                     

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

                                     

                                     

                                    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.102)(PORT=1521)))

                                    STATUS of the LISTENER

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

                                    Alias                     LISTENER

                                    Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production

                                    Start Date                14-NOV-2013 18:57:53

                                    Uptime                    0 days 3 hr. 29 min. 35 sec

                                    Trace Level               off

                                    Security                  ON: Local OS Authentication

                                    SNMP                      OFF

                                    Listener Parameter File   /home/oracle/app/oracle/product/11.2.0/dbhome_2/network/admin/listener.ora

                                    Listener Log File         /home/oracle/app/oracle/diag/tnslsnr/server2/listener/alert/log.xml

                                    Listening Endpoints Summary...

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

                                      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.102)(PORT=80))(PROTOCOL_STACK=(PRESENTATION=HTTP)(SESSION=RAW)))

                                      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.102)(PORT=21))(PROTOCOL_STACK=(PRESENTATION=FTP)(SESSION=RAW)))

                                    Services Summary...

                                    Service "orcl_stby" has 1 instance(s).

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

                                    The command completed successfully