5 Replies Latest reply: Dec 6, 2011 11:48 AM by user642555 RSS

    How to recover from a lost redo log file using RMAN

    322074
      Hi.

      I'm simulating various recovery scenarios using RMAN and XE DB, and incremental backups:

      rman > backup incremental level 0 database;

      -- after some time and updates ---

      rman > backup incremental level 1 database;

      I've successfully tested several situations like restore and recover controlfile from autobackup, spfile from autobackup, restore and recover individual datafiles, tablespaces, etc. However i tried to simulate restore and recovery from and online redo log file and get :

      iniciando la recuperaci¾n del medio fÝsico
      fallo de la recuperaci¾n del medio fÝsico
      RMAN-00571: ===========================================================
      RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
      RMAN-00571: ===========================================================
      RMAN-03002: fallo del comando recover en 07/20/2006 11:34:22
      ORA-00283: recovery session canceled due to errors
      RMAN-11003: fallo al analizar/ejecutar la sentencia SQL: alter database recover
      if needed
      start
      ORA-00283: sesi¾n de recuperaci¾n cancelada debido a errores
      ORA-00313: fallo de apertura para miembros del grupo log 2 del thread 1
      ORA-00312: log online 2 thread 1: 'C:\ORA10G\APP\ORACLE\FLASH_RECOVERY_AREA\XE\
      NLINELOG\O1_MF_2_2CDGN99R_.LOG'
      ORA-27041: no se ha podido abrir el archivo
      OSD-04002: no se ha podido abrir el archivo
      O/S-Error: (OS 2) El sistema no puede hallar el archivo especificado.

      Obviously i have shutdown DB previously and relocate log file to simulate the loss.

      The procedure was (after mounting DB)

      RMAN > RESTORE DATABASE;
      RMAN> RECOVER DATABASE;

      - Am i missing something?.
      - How do i tell RMAN that a redolog is missing?
      - Shouldn't it just recover it from the archive log who is also in the flash recovery area?

      Thanks in advance, Luis ...!
        • 1. Re: How to recover from a lost redo log file using RMAN
          Daljit
          Hmm the recovery process from loss of redo log vary on the status of the missing log file. I hope that you are simulating the loss of all the members of group otherwise you can just copy the available member to the missing location and no recovery is needed after that.

          In your case first you need to check that what is the status of missing log group (active,inactive,current etc). Login to sqlplus mount the database and issue:

          select group#,status from v$log;

          Check the status of group2 bcz according to "ORA-00312: log online 2 thread 1" group 2 is missing.

          Daljit Singh
          • 2. Re: How to recover from a lost redo log file using RMAN
            322074
            Hi.

            I've rerun tested in the following situations:

            - Simulate the lost of a non current (INACTIVE) redo log: in this case it was easy to recover from it. All i did was: alter database clear logfile <missing_logfile> and alter database open;

            - Simulate the lost of a current redo log: According to Backup and Recovery Basics if the database was closed cleanly, all that's needed is to issue an alter database open resetlogs. Like i said, i did a clean shutdown, relocate the current logfile and tried to startup DB. However i can't issue an open resetlogs:

            (Message after startup command)

            ORA-00313: fallo de apertura para miembros del grupo log 2 del thread 1
            ORA-00312: log online 2 thread 1:
            'C:\ORA10G\APP\ORACLE\FLASH_RECOVERY_AREA\XE\ONLINELOG\O1_MF_2_2D1VH9XS_.LO


            SQL> alter database open resetlogs;
            alter database open resetlogs
            *
            ERROR en lÝnea 1:
            ORA-01139: la opci¾n RESETLOGS s¾lo es vßlida despuÚs de una recuperaci¾n
            incompleta de la base de datos


            SQL> select group#,status from v$log;

            GROUP# STATUS
            ---------- ----------------
            2 CURRENT
            1 INACTIVE

            (even tried to recover. get ora-00283).

            SQL> recover database;
            ORA-00283: sesi¾n de recuperaci¾n cancelada debido a errores
            ORA-00264: no es necesaria la recuperaci¾n

            Either this situation is not exactly as the Backup and Recovery Guide suggest or the only option is to do a point in time recovery.

            Please provide feedback on this situation, and thanks very much for your backup.

            Regards, Luis ...!
            • 3. Re: How to recover from a lost redo log file using RMAN
              Daljit
              SQL> recover database;
              ORA-00283: sesi¾n de recuperaci¾n cancelada debido a errores
              ORA-00264: no es necesaria la recuperaci¾n
              Try:

              SQL> recover database until cancel;

              SQL> alter database open resetlogs;

              It should solve your problem.

              Daljit Singh
              • 4. Re: How to recover from a lost redo log file using RMAN
                322074
                Hi.

                Thanks a lot for your backup. Figured that out through metalink note :177866.1.

                Regards, Luis ...!
                • 5. Re: How to recover from a lost redo log file using RMAN
                  user642555
                  thanks for the support. I had the same problem, pretty much, and know my xe database is working properly.