1 2 Previous Next 19 Replies Latest reply: Apr 20, 2011 9:21 PM by Hemant K Chitale RSS

    can not apply redo file

    820797
      I have configured Oracle Data Guard [Oracle 11g R2] with the help of steps from http://www.orafaq.com/node/2030. "select sequence#, applied from v$archived_log order by sequence#;" show 10 rows with applied status "NO" on primary db, and "no rows selected" on standby db.

      Primary DB
      --------------
      db_name : orcl
      db_unique_name : prim

      Standby DB
      --------------
      db_name : orcl
      db_unique_name : stan

      both databases were setup on different machines with same configuration and folder structure.

      Please help to solve this issue.

      Thanks in advance,
      Manu
        • 1. Re: can not apply redo file
          mseberg
          Hello;

          This is expected results on the Primary as it does not apply logs.

          Can you run this on the Primary and post the results?


          set linesize 100

          column STANDBY format a20
          column applied format a10

          SELECT name as STANDBY, SEQUENCE#, applied, completion_time from v$archived_log WHERE DEST_ID = 2 AND NEXT_TIME > SYSDATE -1;

          --------

          and then run this and post ?

          select max(sequence#) from v$archived_log where NEXT_TIME > sysdate -1;


          Best Regards

          mseberg
          • 2. Re: can not apply redo file
            820797
            I have done this configuration on SQLPLUS using sys/**** as sysdba account. I have alias names in tnsname.ora for primary and standby db (prim and stan). But I am not able to connect to sqlplus using sys/***@prim as sysdba.

            Also alert_orcl.log show the following error when running alter system switch logfile;

            Fatal NI connect error 12514, connecting to:
            (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=N1359-W7.domain)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl.domain)(CID=(PROGRAM=f:\app\product\11.2.0\dbhome_1\bin\ORACLE.EXE)(HOST=N1225-W7)(USER=SYSTEM))))

            VERSION INFORMATION:
                 TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
                 Windows NT TCP/IP NT Protocol Adapter for 32-bit Windows: Version 11.2.0.1.0 - Production
            Time: 19-APR-2011 18:35:37
            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
            FAL[client, ARC3]: Error 12514 connecting to PRIM for fetching gap sequence
            Errors in file f:\app\diag\rdbms\stan\orcl\trace\orcl_arc3_2872.trc:
            ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
            Errors in file f:\app\diag\rdbms\stan\orcl\trace\orcl_arc3_2872.trc:
            ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
            Tue Apr 19 18:35:36 2011
            ARC8 started with pid=28, OS id=3112
            Tue Apr 19 18:35:36 2011
            ARCa started with pid=30, OS id=2740
            Tue Apr 19 18:35:36 2011
            ARCq started with pid=46, OS id=5040
            ------------

            This information may help you to findout the solution for the above issue. Please help.
            • 3. Re: can not apply redo file
              820797
              Please find the results of the queries
              SQL> set linesize 100
              SQL> column STANDBY format a20
              SQL> column applied format a10
              SQL> SELECT name as STANDBY, SEQUENCE#, applied, completion_time from v$archived_log WHERE DEST_ID = 2 AND NEXT_TIME > SYSDATE -1;
              
              no rows selected
              
              SQL> select max(sequence#) from v$archived_log where NEXT_TIME > sysdate -1;
              
              MAX(SEQUENCE#)
              --------------
                          24
              • 4. Re: can not apply redo file
                mseberg
                OK

                Looks like your Standby is not getting redo from the Primary for sure.

                It appears your TNS/NET80 is incorrect or your standby password file is wrong.

                Generally I copy the Primary password to the Standby and rename it.

                Try this first and then try to connect or tnsping from the primary again.


                Best Regards

                mseberg
                • 5. Re: can not apply redo file
                  Dr. Paranoid-Oracle
                  Until you can do the following:

                  sqlplus sys/pass@stan as sysdba (from the primary system)

                  and

                  sqlplus sys/pass@prim as sysdba (from the standby system)

                  You will not be able to ship redo. You have an error in your tnsnames and listener configuration.

                  Larry
                  • 6. Re: can not apply redo file
                    820797
                    I copied the password file from primarydb to standbydb and try to connect,but still i cannot connect..

                    When i installed the oracle database,i can connect both from both machines using
                    sqlplus sys/pass@stan as sysdba (from the primary system)

                    and

                    sqlplus sys/***@prim as sysdba (from the standby system)

                    but this error occured when i shutdown and try to startup afterwards i cannot connect as above.
                    At tnsnames.ora i configured like below
                    ORACLR_CONNECTION_DATA =
                    (DESCRIPTION =
                    (ADDRESS_LIST =
                    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
                    )
                    (CONNECT_DATA =
                    (SID = CLRExtProc)
                    (PRESENTATION = RO)
                    )
                    )

                    STAN =
                    (DESCRIPTION =
                    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.27.44)(PORT = 1521))
                    (CONNECT_DATA =
                    (SERVER = DEDICATED)
                    (SERVICE_NAME = orcl.tvm.nestgroup.net)
                    )
                    )

                    PRIM =
                    (DESCRIPTION =
                    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.26.178)(PORT = 1521))
                    (CONNECT_DATA =
                    (SERVER = DEDICATED)
                    (SERVICE_NAME = orcl.tvm.nestgroup.net)
                    )
                    )
                    and
                    Listener.ora
                    SID_LIST_LISTENER =
                    (SID_LIST =
                    (SID_DESC =
                    (SID_NAME = CLRExtProc)
                    (ORACLE_HOME = F:\app\product\11.2.0\dbhome_1)
                    (PROGRAM = extproc)
                    (ENVS = "EXTPROC_DLLS=ONLY:F:\app\product\11.2.0\dbhome_1\bin\oraclr11.dll")
                    )
                    )

                    LISTENER =
                    (DESCRIPTION_LIST =
                    (DESCRIPTION =
                    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
                    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.27.44)(PORT = 1521))
                    )
                    )

                    ADR_BASE_LISTENER = F:\app\

                    Note:We installed both database with same db_name and sid(orcl) and we only changed the db_unique_name as prim and stan.Both are installed in the same folder structer(F:\app\product\11.2.0\dbhome_1)
                    • 7. Re: can not apply redo file
                      Hemant K Chitale
                      What is LOG_ARCHIVE_DEST_2 configured as in the PRIM database instance ?

                      PRIM uses LOG_ARCHIVE_DEST_2 to identify where the Standby is so that it can ship the redo to that (Standby) database instance.

                      Hemant K Chitale
                      • 8. Re: can not apply redo file
                        820797
                        I have some doubts about setting up the parameter file.....please check the below pfile parameters are correct or not
                        orcl.__db_cache_size=520093696
                        orcl.__java_pool_size=8388608
                        orcl.__large_pool_size=8388608
                        orcl.__oracle_base='F:\app\sujina.a'#ORACLE_BASE set from environment
                        orcl.__pga_aggregate_target=511705088
                        orcl.__sga_target=763363328
                        orcl.__shared_io_pool_size=0
                        orcl.__shared_pool_size=218103808
                        orcl.__streams_pool_size=0
                        *.audit_file_dest='F:\app\sujina.a\admin\orcl\adump'
                        *.core_dump_dest='F:\app\sujina.a\admin\orcl\cdump'
                        *.audit_trail='db'
                        *.compatible='11.2.0.0.0'
                        *.control_files='F:\app\sujina.a\oradata\orcl\STAN.CTL','F:\app\sujina.a\flash_recovery_area\orcl\STAN.CTL'
                        *.db_block_size=8192
                        *.db_domain='tvm.nestgroup.net'
                        *.db_name='orcl'
                        *.db_recovery_file_dest='F:\app\sujina.a\flash_recovery_area'
                        *.db_recovery_file_dest_size=4039114752
                        *.db_unique_name='STAN'
                        *.diagnostic_dest='F:\app\sujina.a'
                        *.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
                        *.memory_target=1272971264
                        *.open_cursors=300
                        *.processes=150
                        *.remote_login_passwordfile='EXCLUSIVE'
                        *.undo_tablespace='UNDOTBS1'
                        LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRIM,STAN)'
                        LOG_ARCHIVE_DEST_1='LOCATION=F:\app\sujina.a\flash_recovery_area\STAN\ARCHIVELOG VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=STAN'
                        LOG_ARCHIVE_DEST_2='SERVICE=PRIM LGWR ASYNC
                        VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
                        DB_UNIQUE_NAME=PRIM'
                        LOG_ARCHIVE_DEST_STATE_1=ENABLE
                        LOG_ARCHIVE_DEST_STATE_2=ENABLE
                        LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
                        LOG_ARCHIVE_MAX_PROCESSES=30
                        FAL_SERVER=PRIM
                        FAL_CLIENT=STAN
                        STANDBY_FILE_MANAGEMENT=AUTO
                        LOG_FILE_NAME_CONVERT='F:\app\sujina.a\oradata\orcl','F:\app\sujina.a\oradata\orcl','F:\app\sujina.a\flash_recovery_area\STAN\ONLINELOG','F:\app\sujina.a\flash_recovery_area\PRIM\ONLINELOG'
                        • 9. Re: can not apply redo file
                          820797
                          This is the parameter at the primary side
                          orcl.__db_cache_size=520093696
                          orcl.__java_pool_size=8388608
                          orcl.__large_pool_size=8388608
                          orcl.__oracle_base='F:\app\sujina.a'#ORACLE_BASE set from environment
                          orcl.__pga_aggregate_target=511705088
                          orcl.__sga_target=763363328
                          orcl.__shared_io_pool_size=0
                          orcl.__shared_pool_size=218103808
                          orcl.__streams_pool_size=0
                          *.audit_file_dest='F:\app\sujina.a\admin\orcl\adump'
                          *.audit_trail='db'
                          *.compatible='11.2.0.0.0'
                          *.control_files='F:\app\sujina.a\oradata\orcl\control01.ctl','F:\app\sujina.a\flash_recovery_area\orcl\control02.ctl'
                          *.db_block_size=8192
                          *.db_domain='tvm.nestgroup.net'
                          *.db_name='orcl'
                          *.db_recovery_file_dest='F:\app\sujina.a\flash_recovery_area'
                          *.db_recovery_file_dest_size=4039114752
                          *.db_unique_name='PRIM'
                          *.diagnostic_dest='F:\app\sujina.a'
                          *.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
                          *.memory_target=1272971264
                          *.open_cursors=300
                          *.processes=150
                          *.remote_login_passwordfile='EXCLUSIVE'
                          *.undo_tablespace='UNDOTBS1'
                          LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRIM,STAN)'
                          LOG_ARCHIVE_DEST_1='LOCATION=F:\app\sujina.a\flash_recovery_area\PRIM\ARCHIVELOG VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRIM'
                          LOG_ARCHIVE_DEST_2='SERVICE=STAN LGWR ASYNC
                          VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
                          DB_UNIQUE_NAME=STAN'
                          LOG_ARCHIVE_DEST_STATE_1=ENABLE
                          LOG_ARCHIVE_DEST_STATE_2=ENABLE
                          LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
                          LOG_ARCHIVE_MAX_PROCESSES=30
                          FAL_SERVER=STAN
                          FAL_CLIENT=PRIM
                          STANDBY_FILE_MANAGEMENT=AUTO
                          LOG_FILE_NAME_CONVERT='F:\app\sujina.a\oradata\orcl','F:\app\sujina.a\oradata\orcl','F:\app\sujina.a\flash_recovery_area\PRIM\ONLINELOG','F:\app\sujina.a\flash_recovery_area\STAN\ONLINELOG'
                          • 10. Re: can not apply redo file
                            Hemant K Chitale
                            LISTENER =
                            (DESCRIPTION_LIST =
                            (DESCRIPTION =
                            (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
                            (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.27.44)(PORT = 1521))
                            )
                            )
                            The Listener HOST IP would be different on the PRIM and STAND. Which is the one that you have posted ? Does the other node have the listener setup properly ?


                            Hemant K Chitale
                            • 11. Re: can not apply redo file
                              820797
                              yes.The listener.ora that i have posted was the listener of standbyside.
                              At primaryside it is
                              SID_LIST_LISTENER =
                              (SID_LIST =
                              (SID_DESC =
                              (SID_NAME = CLRExtProc)
                              (ORACLE_HOME = F:\app\sujina.a\product\11.2.0\dbhome_1)
                              (PROGRAM = extproc)
                              (ENVS = "EXTPROC_DLLS=ONLY:F:\app\sujina.a\product\11.2.0\dbhome_1\bin\oraclr11.dll")
                              )
                              )

                              LISTENER =
                              (DESCRIPTION_LIST =
                              (DESCRIPTION =
                              (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
                              (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.26.178)(PORT = 1521))
                              )
                              )

                              ADR_BASE_LISTENER = F:\app\sujina.a

                              And the tnsnames.ora at primary is

                              ORACLR_CONNECTION_DATA =
                              (DESCRIPTION =
                              (ADDRESS_LIST =
                              (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
                              )
                              (CONNECT_DATA =
                              (SID = CLRExtProc)
                              (PRESENTATION = RO)
                              )
                              )
                              PRIM =
                              (DESCRIPTION =
                              (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.26.178)(PORT = 1521))
                              (CONNECT_DATA =
                              (SERVER = DEDICATED)
                              (SERVICE_NAME = orcl.tvm.nestgroup.net)
                              )
                              )

                              STAN =
                              (DESCRIPTION =
                              (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.27.44)(PORT = 1521))
                              (CONNECT_DATA =
                              (SERVER = DEDICATED)
                              (SERVICE_NAME = orcl.tvm.nestgroup.net)
                              )
                              )

                              I think my listener.ora and tnsnames are correct,because i connected with this once.but i got this tnserror when i shutdown and try to startup using this net_service_name.
                              • 12. Re: can not apply redo file
                                Hemant K Chitale
                                If you shutdown the database, you cannot startup using a net service name that uses SERVICE instead of SID.

                                This has nothing to do with Standby and Dataguard implementation.

                                When a database instance shuts down, it "de-registers" from the listener. If you attempt to connect via the Listener after this, the listener is NOT aware of the SERVICE anymore.

                                Startup the standby database instance (MOUNT and then RECOVER) manually (i.e. using ORACLE_SID) on the standby server.

                                .......... and so, your "cannot apply redo" is because your Standby is not started up (and it must be in Recovery mode, not OPEN).

                                Hemant K Chitale

                                Edited by: Hemant K Chitale on Apr 20, 2011 2:26 PM
                                • 13. Re: can not apply redo file
                                  820797
                                  K.At standby side i started database as follow
                                  sql>startup open recover;
                                  After that i execute at standby
                                  sql> alter database recover managed standby database cancel;

                                  when i execute this some error logs are generated in the primary side"F:\app\sujina.a\diag\rdbms\prim\orcl\trace\alert_orcl.txt"
                                  This is the error logs:

                                  Fatal NI connect error 12514, connecting to:
                                  (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.27.44)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl.tvm.nestgroup.net)(CID=(PROGRAM=f:\app\sujina.a\product\11.2.0\dbhome_1\bin\ORACLE.EXE)(HOST=N1359-W7)(USER=SYSTEM))))

                                  VERSION INFORMATION:
                                       TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
                                       Windows NT TCP/IP NT Protocol Adapter for 32-bit Windows: Version 11.2.0.1.0 - Production
                                  Time: 20-APR-2011 12:33:13
                                  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


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

                                  Fatal NI connect error 12514, connecting to:
                                  (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.27.44)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl.tvm.nestgroup.net)(CID=(PROGRAM=f:\app\sujina.a\product\11.2.0\dbhome_1\bin\ORACLE.EXE)(HOST=N1359-W7)(USER=SYSTEM))))

                                  VERSION INFORMATION:
                                       TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
                                       Windows NT TCP/IP NT Protocol Adapter for 32-bit Windows: Version 11.2.0.1.0 - Production
                                  Time: 20-APR-2011 12:33:13
                                  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 f:\app\sujina.a\diag\rdbms\prim\orcl\trace\orcl_arc1_3616.trc:
                                  ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
                                  PING[ARC1]: Heartbeat failed to connect to standby 'STAN'. Error is 12514.


                                  pls help..
                                  And one thing i want to know that to which mode i have to startup the primary side.
                                  Then,
                                  When shutdown occurs it will dergister from the listener,that was ok.But after that i connected to sys/*** as sysdba and startup the database.Also i restarted the listener...After all when i try to connect sys/***@PRIM as sysdba or sys/***@STAN,it shows the tnslistener error..does it happen?Actually i want to know when it again register with listener?If I cannot connect primary from the standby and standby from primary using this net_service_name,does redo log apply occurs?

                                  sujina
                                  • 14. Re: can not apply redo file
                                    Hemant K Chitale
                                    When shutdown occurs it will dergister from the listener,that was ok.But after that i connected to sys/*** as sysdba and startup the database.Also i restarted the listener...
                                    You surprise me.  I don't know how you do that.

                                    (BTW : The DG setup blog post that you reference at the beginning of this thread does not use the net_connect_string to shutdown and startup).

                                    Actually i want to know when it again register with listener?
                                    In a few minutes. Have patience. Look at the listener log file.

                                    You keep reporting error messages from the primary alert.log. Had you looked at the standby alert.log to see if it had been started up earlier ?


                                    Hemant K Chitale

                                    Edited by: Hemant K Chitale on Apr 20, 2011 3:48 PM
                                    1 2 Previous Next