7 Replies Latest reply: Aug 28, 2009 7:57 AM by user9808 RSS

    recover standby database

    user9808
      Our primary linux 10g db is in standard edition and we would like to manually create a standby database

      After copying the control and datafiles from primary to standby database, started the standby instance ..

      SQL> startup nomount pfile=/path/to/pfile/initSID.standby
      SQL> alter database mount standby database;
      SQL> recover standby database;
      ORA-00279: change 2342934 generated at 8/27/2009 21:10:35 needed for thread 1
      ORA-00289: suggestion : /opt/oracle/arch/SID/1_833_682861383.arc
      ORA-00280: change 2342934 for thread 1 is in sequence #833

      Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
      AUTO
      ORA-00308: cannot open archived log
      '/opt/oracle/arch/SID/1_833_682861383.arc'
      ORA-27037: unable to obtain file status
      Linux Error: 2: No such file or directory
      Additional information: 3

      ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
      ORA-01195: online backup of file 1 needs more recovery to be consistent
      ORA-01110: data file 1: '/opt/oracle/oradata/SID/system01.dbf'


      How do I resolve this problem? Log sequence 833 is the current log on the primary db, archive log haven't been written yet. When I try to "alter database open read only" on the standby db, I get an error "ORA-01156: recovery in progress may need access to file".

      I then went back to my primary database, which already had another log switch to 834, log 833 is now available, I then moved that archive log file to the standby db. Try to recover standby database again, but still got errors..

      SQL> startup nomount pfile=/path/to/pfile/initSID.standby
      SQL> alter database mount standby database;
      SQL> recover standby database;
      ORA-00279: change 2342934 generated at 8/27/2009 21:10:35 needed for thread 1
      ORA-00289: suggestion : /opt/oracle/arch/SID/1_833_682861383.arc
      ORA-00280: change 2342934 for thread 1 is in sequence #833

      Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
      AUTO
      ORA-00317: file type 0 in header is not log file
      ORA-00334: archived log: '/opt/oracle/arch/SID/1_833_682861383.arc'

      ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
      ORA-01195: online backup of file 1 needs more recovery to be consistent
      ORA-01110: data file 1: '/opt/oracle/oradata/SID/system01.dbf'

      SQL> alter database open read only;

      ERROR at line 1:
      ORA-16004: backup database requires recovery
      ORA-01195: online backup of file 1 needs more recovery to be consistent
      ORA-01110: data file 1: '/opt/oracle/oradata/SID/system01.dbf'


      Would something please explain this to me? What am I doing wrong? Thanks in advance.
        • 1. Re: recover standby database
          ivw
          SQL>ALTER DATABASE OPEN RESETLOGS ;
          • 2. Re: recover standby database
            709382
            HI ,

            Did you create and copy standby controlfile.

            After copying datafiles,controlfiles to standby site,you have to copy standby controlfile.Follow these steps

            1) Copy files to DR.
            2) On prmary .

            SQL >alter database create standby controlfile as '/tmp/stdbyctl.ctl' (or any name and directory).

            3) transfer this file to DR.

            4) copy this file into DR's controlfile (s) (Which is mentioned in init file).

            5) Then mount standby database and then recover

            SQL >startup nomount
            SQL> alter database mount standby database;
            SQL> recover managed standby database;


            Hope this helps

            Thanks
            Pramod
            • 3. Re: recover standby database
              Satishbabu Gunukula
              Please see the metalink note 434164.1 for simple and easy solution.

              Hope this helps,

              Regards,
              Satishbabu Gunukula
              http://oracleracexpert.blogspot.com
              [Click here to learn how to validate rman backup|http://oracleracexpert.blogspot.com/2009/08/how-to-validate-rman-backup.html]
              • 4. Re: recover standby database
                Hemant K Chitale
                You need to do a CANCEL based recovery using
                'recover database using backup controlfile until cancel' 
                (see MetaLink Note#333749.1 )

                ByTheWay : These indicate that your file transfers are incomplete or corrupt :
                ORA-00317: file type 0 in header is not log file
                ORA-00334: archived log: '/opt/oracle/arch/SID/1_833_682861383.arc'


                Hemant K Chitale
                http://hemantoracledba.blogspot.com
                • 5. Re: recover standby database
                  424684
                  Try copying 833 archive again and try recovery, if not successful then take incremental backup from production and apply it at standby.
                  After that try to put in managed mode.

                  One more thing, how you copied datafiles to standby, through RMAN or putting files in begin backup mode?
                  • 6. Re: recover standby database
                    user9808
                    I tried copying 833 archive again and try recovery, still not successful ... keep getting the same errors. I copied all the datafiles to standby using begin backup mode.

                    Thanks in advance.
                    • 7. Re: recover standby database
                      user9808
                      I copied the missing archive log over to standby db, still got the same error.

                      1) SQL> recover standby database;
                      ORA-00279: change 2342934 generated at 8/27/2009 21:10:35 needed for thread 1
                      ORA-00289: suggestion : /opt/oracle/arch/SID/1_833_682861383.arc
                      ORA-00280: change 2342934 for thread 1 is in sequence #833

                      Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
                      AUTO
                      ORA-00317: file type 0 in header is not log file
                      ORA-00334: archived log: '/opt/oracle/arch/SID/1_833_682861383.arc'

                      ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
                      ORA-01195: online backup of file 1 needs more recovery to be consistent
                      ORA-01110: data file 1: '/opt/oracle/oradata/SID/system01.dbf'

                      I also tried..
                      2) SQL> recover standby database using backup controlfile until cancel;
                      ORA-00279: change 2342934 generated at 8/27/2009 21:10:35 needed for thread 1
                      ORA-00289: suggestion : /opt/oracle/arch/SID/1_833_682861383.arc
                      ORA-00280: change 2342934 for thread 1 is in sequence #833

                      Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
                      AUTO
                      ORA-00317: file type 0 in header is not log file
                      ORA-00334: archived log: '/opt/oracle/arch/SID/1_833_682861383.arc'

                      ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
                      ORA-01195: online backup of file 1 needs more recovery to be consistent
                      ORA-01110: data file 1: '/opt/oracle/oradata/SID/system01.dbf'

                      Edited by: user10427867 on Aug 28, 2009 5:56 AM