7 Replies Latest reply: Apr 24, 2013 5:42 AM by Christpj RSS

    RECOVER DATABASE ONLY WITH BACKUP SET(MISSED ARCHIVELOGS)

    Christpj
      Hello Experts,

      I am a newbie to ORACLE. While playing with datapump my instance crashed and it says System tbs needs a recovery.
      Yesterday i took a full backup so i thought there will be no problem in restoring process.But my bad luck, i missed the first two archivelog files following the backupset.

      Now my concern is how can i restore my database from the backupset without any archivelog files?
      I guess this is possible but no idea how to proceed...

      I had two choices
      1. DROP DATABASE,REBUILD AGAIN - Easy one -But i couldnt learn anything from that
      2.Google it,if couldn't achieve ask help from experts - time consuming but i will learn something new..

      So ,I chosen second choice and came here.Can anybody help me..

      DB : 10g r2
      Host : RETHAT

      Regards,
      Christo
        • 1. Re: RECOVER DATABASE ONLY WITH BACKUP SET(MISSED ARCHIVELOGS)
          Balazs Papp
          1. DROP DATABASE,REBUILD AGAIN - Easy one -But i couldnt learn anything from that
          Yes you could. Always have a good backup.
          No. 1 rule for a DBA.
          2.Google it,if couldn't achieve ask help from experts - time consuming but i will learn something new..
          There is no nice solution, since your backup is incomplete.

          after restoring the database:
          sqlplus / as sysdba
          alter system set "_allow_resetlogs_corruption"=true scope=spfile;
          if you dont have spfile, edit the pfile and add the following line:
          _allow_resetlogs_corruption=true
          then continue
          shutdown abort
          startup mount
          alter database open resetlogs;
          So congratulations, you have just corrupted your database, it may or may not work properly, but at least its open.
          Salvage all the data you can (make an export dump), and go to step 1 = rebuild the database.

          Note that, such parameters as above should not be used at all under normal circumstances.
          • 2. Re: RECOVER DATABASE ONLY WITH BACKUP SET(MISSED ARCHIVELOGS)
            Hemant K Chitale
            allowresetlogs_corruption also means that the data that you "salvage" may not be consistent. Portions of it (depending on what transactions were occurring before the crash, what had been checkpointed etc) may be logically corrupt.


            Hemant K Chitale
            • 3. Re: RECOVER DATABASE ONLY WITH BACKUP SET(MISSED ARCHIVELOGS)
              Christpj
              Dude,
              Thanks for suggestion.. But stil i am lagging...
              RMAN> restore database;
              
              Starting restore at 23-APR-13
              using channel ORA_DISK_1
              
              skipping datafile 1; already restored to file /u01/mydb/system01.dbf
              skipping datafile 2; already restored to file /u01/mydb/undotbs01.dbf
              skipping datafile 3; already restored to file /u01/mydb/sysaux01.dbf
              skipping datafile 4; already restored to file /u01/mydb/users01.dbf
              skipping datafile 5; already restored to file /u01/mydb/spare1.dbf
              restore not done; all files readonly, offline, or already restored
              Finished restore at 23-APR-13
              SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;
              
              System altered.
              SQL> shutdown abort
              ORACLE instance shut down.
              SQL> startup mount
              
              Database mounted.
              SQL> alter database open resetlogs;
              alter database open resetlogs
              *
              ERROR at line 1:
              ORA-01139: RESETLOGS option only valid after an incomplete database recovery
              
              
              SQL>
              Could you tell me what i missed here!
              • 4. Re: RECOVER DATABASE ONLY WITH BACKUP SET(MISSED ARCHIVELOGS)
                Balazs Papp
                recover database until cancel using backup controlfile;
                alter database open resetlogs;
                • 5. Re: RECOVER DATABASE ONLY WITH BACKUP SET(MISSED ARCHIVELOGS)
                  Christpj
                  Sorry... really really i am not understand why rman not taking my command..

                  SQL> shutdown immediate;
                  ORA-01109: database not open
                  
                  
                  Database dismounted.
                  ORACLE instance shut down.
                  SQL> exit
                  Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
                  With the Partitioning, OLAP and Data Mining options
                  [oracle@redhat ~]$
                  [oracle@redhat ~]$ sqlplus / as sysdba
                  
                  SQL*Plus: Release 10.2.0.1.0 - Production on Tue Apr 23 13:03:51 2013
                  
                  Copyright (c) 1982, 2005, Oracle.  All rights reserved.
                  
                  Connected to an idle instance.
                  
                  SQL> startup mount;
                  ORACLE instance started.
                  
                  Total System Global Area  285212672 bytes
                  Fixed Size                  1218992 bytes
                  Variable Size             121636432 bytes
                  Database Buffers          159383552 bytes
                  Redo Buffers                2973696 bytes
                  Database mounted.
                  SQL> alter database open;
                  alter database open
                  *
                  ERROR at line 1:
                  ORA-01113: file 1 needs media recovery
                  ORA-01110: data file 1: '/u01/mydb/system01.dbf'
                  
                  
                  SQL> !rman target /
                  
                  Recovery Manager: Release 10.2.0.1.0 - Production on Tue Apr 23 13:04:28 2013
                  
                  Copyright (c) 1982, 2005, Oracle.  All rights reserved.
                  
                  connected to target database: MYDB (DBID=2725504211, not open)
                  
                  RMAN> restore database;
                  
                  Starting restore at 23-APR-13
                  using target database control file instead of recovery catalog
                  allocated channel: ORA_DISK_1
                  channel ORA_DISK_1: sid=154 devtype=DISK
                  
                  skipping datafile 1; already restored to file /u01/mydb/system01.dbf
                  skipping datafile 2; already restored to file /u01/mydb/undotbs01.dbf
                  skipping datafile 3; already restored to file /u01/mydb/sysaux01.dbf
                  skipping datafile 4; already restored to file /u01/mydb/users01.dbf
                  skipping datafile 5; already restored to file /u01/mydb/spare1.dbf
                  restore not done; all files readonly, offline, or already restored
                  Finished restore at 23-APR-13
                  
                  RMAN> RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE;
                  
                  RMAN-00571: ===========================================================
                  RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
                  RMAN-00571: ===========================================================
                  RMAN-00558: error encountered while parsing input commands
                  RMAN-01009: syntax error: found "cancel": expecting one of: "logseq, restore, scn, sequence, time"
                  RMAN-01007: at line 1 column 24 file: standard input
                  
                  RMAN>
                  I checked in google, the syntax is correct :( :( :(
                  • 6. Re: RECOVER DATABASE ONLY WITH BACKUP SET(MISSED ARCHIVELOGS)
                    Hemant K Chitale
                    RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE
                    is an SQLPlus command to be issued from the SQL Command line, not the RMAN Command line.


                    Hemant K Chitale
                    • 7. Re: RECOVER DATABASE ONLY WITH BACKUP SET(MISSED ARCHIVELOGS)
                      Christpj
                      Thanks Dude..

                      Now i rescued my database,then rebuilt it (full exp/imp)

                      Thanks again :)