5 Replies Latest reply: Jul 1, 2012 1:33 PM by gzmzpz RSS

    Logs not applying on Standby Database

    gzmzpz
      I am afraid I am back.

      I now have a primary database and a standby database and logs are being shipped but not applied. Done some research and I have got to the point where in my alert log I find the following when I try to start MRP:
      Fri Jun 29 15:03:40 2012
      alter database recover managed standby database disconnect from session
      Attempt to start background Managed Standby Recovery process (SAPDS)
      Fri Jun 29 15:03:40 2012
      MRP0 started with pid=29, OS id=23272
      MRP0: Background Managed Standby Recovery process started (SAPDS)
      started logmerger process
      Fri Jun 29 15:03:45 2012
      Managed Standby Recovery not using Real Time Apply
      Read of datafile '/var/hpsrp/drforp03/oradata/u04/SAPDS/DRFSAPDS/sysaux01.dbf' (fno 2) header failed with ORA-01206
      Rereading datafile 2 header failed with ORA-01206
      MRP0: Background Media Recovery terminated with error 1110
      Errors in file /var/hpsrp/drforp03/oradata/u04/SAPDS/admin/diag/rdbms/drfsapds/SAPDS/trace/SAPDS_pr00_23308.trc:
      ORA-01110: data file 2: '/var/hpsrp/drforp03/oradata/u04/SAPDS/DRFSAPDS/sysaux01.dbf'
      ORA-01122: database file 2 failed verification check
      ORA-01110: data file 2: '/var/hpsrp/drforp03/oradata/u04/SAPDS/DRFSAPDS/sysaux01.dbf'
      ORA-01206: file is not part of this database - wrong database id
      Recovery Slave PR00 previously exited with exception 1110
      Errors in file /var/hpsrp/drforp03/oradata/u04/SAPDS/admin/diag/rdbms/drfsapds/SAPDS/trace/SAPDS_mrp0_23272.trc:
      ORA-01110: data file 2: '/var/hpsrp/drforp03/oradata/u04/SAPDS/DRFSAPDS/sysaux01.dbf'
      ORA-01122: database file 2 failed verification check
      ORA-01110: data file 2: '/var/hpsrp/drforp03/oradata/u04/SAPDS/DRFSAPDS/sysaux01.dbf'
      ORA-01206: file is not part of this database - wrong database id
      MRP0: Background Media Recovery process shutdown (SAPDS)
      Completed: alter database recover managed standby database disconnect from session

      My question therefore is how can I get out of this and get my logs to apply on standby?
        • 1. Re: Logs not applying on Standby Database
          mseberg
          Hello again;

          This is not good. It points to an issue with the Standby control file(s). I never seen this in Oracle 11. The thought is to recreate the control file, but I don't like to advise on something I have not tested.

          I think before I did anything I would shutdown the Standby and startup mount and try starting apply again.

          Which method did you finally use to create the Standby? Maybe this will offer a greater clue.

          RMAN should handle the DBID. Any chance a duplicate was done instead of a duplicate for standby?


          Found similar issue here but not much help I'm thinking :

          http://oracleinstance.blogspot.com/2010/08/ora-01122-ora-01110-ora-01206-after.html


          Best Regards

          mseberg

          Edited by: mseberg on Jun 29, 2012 10:32 AM
          • 2. Re: Logs not applying on Standby Database
            gzmzpz
            Thanks for the speedy reply.

            I did:
            duplicate target database for standby from active database NOFILENAMECHECK;

            I also have done shutdown; startup mount; alter database recover managed standby database disconnect from session; previously to raising this thread.

            I do have a fear that the duplicate target database didn't fully complete though as due to the setup here my session stopped. I monitored the end of the rman process before continuing though but there is still a big question mark over did it get any further than datafile 1 the system file.

            Do you think if I tried to repeat the duplicate this might resolve the issue or just makes things worse?
            • 3. Re: Logs not applying on Standby Database
              mseberg
              Hello again;

              Do you think if I tried to repeat the duplicate?

              Yes I think you should do this.

              Give me a few minutes and I will review my Word document and provide a step by step overview here as insurance.

              I always cleanup the standby before trying another dup


              OVERVIEW

              Step 1 - Password file fro standby - Copy from primary and rename
              Step 2 - Directory Structure on the remote server - Make sure noting is missing
              Step 3 - Oracle Net Setup - entry for the CLONE in your TNSNAMES.ORA on both servers
              Step 4 - SID_LIST_LISTENER addition ( assumes listener named LISTENER )
              Step 5 - Timeouts set in listener.ora and sqlnet.ora Both Servers
              Step 6 - Initialization Parameter File for the Auxiliary Instance
              Step 7 - Set SID for Auxiliary Instance
              Step 8 - Create an SPFILE for the new database by using a pfile with the INIT settings
              Step 9 - Shutdown and startup nomount on new Spfile ( Auxiliary Instance )
              Step 10 - Start RMAN and run the DUPLICATE Command



              SID_LIST_LISTENER Example from mine
              SID_LIST_LISTENER =
                 (SID_LIST =
                     (SID_DESC =
                     (SID_NAME = PLSExtProc)
                     (ORACLE_HOME = /u01/app/oracle/product/11.2.0.2)
                     (PROGRAM = extproc)
                   )
                     (SID_DESC =
                     (global_dbname = CLONE.hostname)
                     (ORACLE_HOME = /u01/app/oracle/product/11.2.0.2)
                     (sid_name = CLONE)
                 )
              )
              Prevent Timeouts
              Add these to both servers
              
              To listener.ora
              INBOUND_CONNECT_TIMEOUT_ = 120
              
              To sqlnet.ora
              SQLNET.INBOUND_CONNECT_TIMEOUT = 120
              
              
              Then stop and start the listener.
              RMAN

              $ORACLE_HOME/bin/rman target=sys/@primary auxiliary=sys/@standby


              Connect should return something like this
              connected to target database: RECOVER9 (DBID=3806912436)
              connected to auxiliary database: CLONE (not mounted)
              RMAN>duplicate target database for standby from active database NOFILENAMECHECK;




              INIT Extras

              To avoid ORA-09925 make sure the PFILE has audit_file_dest and core_dump_dest set


              Best Regards

              mseberg

              Edited by: mseberg on Jun 29, 2012 11:12 AM
              • 4. Re: Logs not applying on Standby Database
                CKPT
                i suspect there is something wrong in duplicate, please provide below details from both primary & standby

                SQL>select file#,error,status,resetlogs_change#,resetlogs_time from v$datafile_header;
                • 5. Re: Logs not applying on Standby Database
                  gzmzpz
                  Thank you all for your help.

                  In the end I had to manually copy the files across and merge them with the files on the standby server as the connection kept dropping out for the duplicate command, this is due to our network not an Oracle issue.