1 2 Previous Next 16 Replies Latest reply on Apr 28, 2013 2:14 PM by Hemant K Chitale

    database recovery

    pawii
      i am using 10g oracle on oel 5.5
      i had an cold backup of database
      while working on opened database i lost my redo log group and control files now how to recover??


      i tried to recover only the old control files from the backup and then applied archives on them but database is still inconsistent as it needs the redos to be applied as i had lost the all redos now so tell me how to make the database consistent now??
        • 1. Re: database recovery
          sb92075
          pawii wrote:
          i am using 10g oracle on oel 5.5
          i had an cold backup of database
          while working on opened database i lost my redo log group and control files now how to recover??
          restore again from cold backup.
          • 2. Re: database recovery
            pawii
            any other option available??? is it the last option i had??

            i had done many things after the last cold backup so they are lost now??
            • 3. Re: database recovery
              rp0428
              >
              any other option available???
              >
              No
              >
              is it the last option i had??
              >
              Yes
              >
              i had done many things after the last cold backup so they are lost now??
              >
              Yes

              You said you 'lost my redo log group and control files' and those are the only files that would have the changes in them. They are gone.
              • 4. Re: database recovery
                Hemant K Chitale
                If you
                a. Have a Cold Backup
                b. Have subsequent ArchiveLogs
                c. Lose the Online Redo Logs

                You can
                i. Restore the Cold Backup (including the controlfile)
                ii. Use SQL RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
                This will prompt you for the ArchiveLogs that were generated after the Cold Backup. Oracle doesn't know how many ArchiveLogs were generated (because the controlfile predates the archivelogs) so you have to manually enter the ArchiveLogs (or use the AUTO option when prompted for the ArchiveLog file name, allowing Oracle to automatically generate the ArchiveLog file names).
                iii. You will CANCEL after your last available ArchiveLog is applied..
                iv. ALTER DATABASE OPEN RESETLOGS


                Hemant K Chitale
                1 person found this helpful
                • 5. Re: database recovery
                  pawii
                  hmm my question is answered but this is what i was looking for :)

                  i had applied the archive logs by specifying auto but after it when i try alter database open resetlogs it says that system01.dbf requires more recovery
                  so tell me what to do now??
                  do i had to set up any parameter in control file before specifying the resetlog command???


                  before deletion of control files and redos i had trace of control file

                  after deletion of control and redo files i fired the create control file from trace using resetlogs
                  then i fired recover database until cancel then i let it through auto
                  and after that i opened the database alter database open resetlogs;

                  it says that system01.dbf needs more recovery
                  so tell me what to do now??

                  Edited by: pawii on Apr 23, 2013 3:14 PM
                  • 6. Re: database recovery
                    Hemant K Chitale
                    You need to do an Incomplete Recovery. So it has to be "USING BACKUP CONTROLFILE UNTIL CANCEL" and a CANCEL after the last ArchiveLog.

                    Hemant K Chitale
                    • 7. Re: database recovery
                      pawii
                      still the database is not opening and saying that system01.dbf requires media recovry
                      what i had done is


                      open database
                      created trace of controlfile
                      created a new tablespace
                      created some tables inserted records
                      switched the logfiles
                      created another few tables
                      thein i deleted the redo and control files
                      i recreated the control file using the trace create control file statement with no resetlogs option

                      now when i try to open the database using open database restlogs it opens
                      but
                      when i apply archives using recover database until cancel or recover database using backup control until cancel
                      and after applying archives
                      when i try to open database using resetlogs it is not opening
                      saying system01.dbf requires recovery

                      so how should i open the database beyond this point????
                      after applying archives???
                      • 8. Re: database recovery
                        Hemant K Chitale
                        when i try to open the database using open database restlogs it opens
                        and then
                        when i apply archives
                        You have the database OPENed. You have also RESET the SEQ#s. What archives are you applying thereafter ?
                        If you are retrying the recovery, you also need to restore / recreate the controlfile.


                        Hemant K Chitale
                        • 9. Re: database recovery
                          pawii
                          there are two scenarios
                          1.when i dont apply anything i just recreate the controlfiles and open the database using resetlogs it got opened

                          but this is not what i want
                          aal
                          2 i want is to recover the database to some extent by applying archives available
                          so i created the control files from trace and applied the archives but after it using the resetlogs the database is not opening saying that system01.dbf needs more recovery.

                          scenario 1 is aal ok but i want to perform the scenario 2 so tell me how can i open the db after applying archives????
                          • 10. Re: database recovery
                            Hemant K Chitale
                            Do the CREATE CONTROLFILE with RESETLOGS


                            Hemant K Chitale
                            • 11. Re: database recovery
                              Hemant K Chitale
                              Also check the alert.log Does it show any messages that are you doing a recovery of fuzzy datafiles ?


                              Hemant K Chitale
                              • 12. Re: database recovery
                                pawii
                                yes i created the controlfile using restlogs
                                and yes i am doing recovery of fuzzy datafiles


                                this is my alert log

                                ALTER DATABASE RECOVER database using backup controlfile until cancel
                                Thu Apr 25 15:07:12 2013
                                Media Recovery Start
                                WARNING! Recovering data file 1 from a fuzzy file. If not the current file
                                it might be an online backup taken without entering the begin backup command.
                                WARNING! Recovering data file 2 from a fuzzy file. If not the current file
                                it might be an online backup taken without entering the begin backup command.
                                WARNING! Recovering data file 3 from a fuzzy file. If not the current file
                                it might be an online backup taken without entering the begin backup command.
                                WARNING! Recovering data file 4 from a fuzzy file. If not the current file
                                it might be an online backup taken without entering the begin backup command.
                                WARNING! Recovering data file 5 from a fuzzy file. If not the current file
                                it might be an online backup taken without entering the begin backup command.
                                ORA-279 signalled during: ALTER DATABASE RECOVER database using backup control file until cancel ...
                                Thu Apr 25 15:07:15 2013
                                ALTER DATABASE RECOVER CONTINUE DEFAULT
                                Thu Apr 25 15:07:15 2013
                                Media Recovery Log /u01/app/flash_recovery_area/PAWII/archivelog/2013_04_25/o1_m f_1_4_8qky2k0r_.arc
                                ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
                                Thu Apr 25 15:07:15 2013
                                ALTER DATABASE RECOVER CONTINUE DEFAULT
                                Thu Apr 25 15:07:15 2013
                                Media Recovery Log /u01/app/flash_recovery_area/PAWII/archivelog/2013_04_25/o1_m f_1_5_8qkwbc2w_.arc
                                Errors with log /u01/app/flash_recovery_area/PAWII/archivelog/2013_04_25/o1_mf_1 _5_8qkwbc2w_.arc
                                ORA-328 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
                                Thu Apr 25 15:07:16 2013
                                ALTER DATABASE RECOVER CANCEL
                                ORA-1547 signalled during: ALTER DATABASE RECOVER CANCEL ...
                                Thu Apr 25 15:07:37 2013
                                alter database open resetlogs
                                Thu Apr 25 15:07:37 2013
                                ORA-1194 signalled during: alter database open resetlogs

                                so tell me how to open database now??

                                SQL> alter database open resetlogs;
                                alter database open resetlogs
                                *
                                ERROR at line 1:
                                ORA-01194: file 1 needs more recovery to be consistent
                                ORA-01110: data file 1: '/u01/app/oradata/pawii/system01.dbf'
                                • 13. Re: database recovery
                                  pawii
                                  so can you help me or not??
                                  or i can never recover database like this??
                                  • 14. Re: database recovery
                                    rp0428
                                    >
                                    so can you help me or not??
                                    >
                                    No - not when you don't follow the instructions provided.

                                    No - not when you keep telling people what you did instead of showing them by posting the exact statements and file movements that you executed.
                                    >
                                    yes i created the controlfile using restlogs
                                    >
                                    A statement like that is USELESS TO US in trying to understand EXACTLY what you did and the order you did it.

                                    Reread the entire thread - no one said ANYTHING about creating a control file so why are you doing that? A Cold Backup includes the control files.

                                    Start over and do what Hemant suggested above
                                    >
                                    If you
                                    a. Have a Cold Backup
                                    b. Have subsequent ArchiveLogs
                                    c. Lose the Online Redo Logs

                                    You can
                                    i. Restore the Cold Backup (including the controlfile)
                                    ii. Use SQL RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
                                    This will prompt you for the ArchiveLogs that were generated after the Cold Backup. Oracle doesn't know how many ArchiveLogs were generated (because the controlfile predates the archivelogs) so you have to manually enter the ArchiveLogs (or use the AUTO option when prompted for the ArchiveLog file name, allowing Oracle to automatically generate the ArchiveLog file names).
                                    iii. You will CANCEL after your last available ArchiveLog is applied..
                                    iv. ALTER DATABASE OPEN RESETLOGS
                                    >
                                    Post EVERY SINGLE COMMAND and EVERY SINGLE STEP you take and the EXACT results of each of them.

                                    If you move a file from one location to another post the EXACT command you used to move the file.

                                    If you create a folder post the EXACT command you used to create the folder.

                                    Don't leave out anything.
                                    1 person found this helpful
                                    1 2 Previous Next