14)incomplete recovery

14)incomplete recovery

    Incomplete recovery, or point-in-time recovery, uses a backup to produce a noncurrent version of the database.

    In other words, you do not apply all of the redo records generated after the most recent backup.

 

    Perform this type recovery only when absolutely necessary.

 

    To perform incomplete recovery, you need:

        A valid offline or online backup of all of the data files made before the recovery point.

        All archived logs from the time of the backup until the specified time of recovery .

 

    RESETLOGS:To synchronize data files with control files and redo logs.

              Oldest online log sequence 0

              Next log sequence to archive 1

              Current log sequence 1

 

    Roll Forward:  all changes made to data blocks that had not been written to the data files

                  on disk are performed again by reapplying (redoing) the images originally recorded

                  on the Redo Log.

 

    Rollback:  uncommitted transactions are rolled back

 

 

  

   a)situations

 

      1)missing archived log file.

 

      2)one or more unarchived redo log file and a data file are lost.

 

      3)a backup of control file is used to open or recover the database.

 

      4)complete recovery fails.

 

      5)User error:

          An important table was dropped.

          Invalid data was committed in a table

 

   b)type of incomplete recovery

       time-based recovery

       change-based recovery(scn)

       cancel-based recovery(sqlplus)

       log sequence recovery(rman)

 

   c)user-managed

 

      *Incomplete Recovery Steps with User-Managed Procedures

         1.shut down and back up the database.

         2.restore all datafile. do not restore the control file,

           redo logs,password file,parameter file.

         3.mount the database.

         4.recover tha datafile to a point before the time of failure.

         5.open tha database with resetlogs;

         6.perform a closed database backup.

 

         recover database until time '2008-06-27 10:10:00';

         recover database until cancel;

         recover database until change scn#

         recover database until time 'time' using backup controlfile;

         recover database using backup controlfile until cancel;

 

        shutdown immediate

        cp *

        startup mount

        recover database until time 'time';

        alter database open resetlogs;

        shutdown immediate

        cp (backup)

 

   d)rman

 

      *Incomplete Recovery Steps with rman Procedures

 

         1)mount the database

         2)restore all datafiles

         3)recover the database by using until time, until sequence, until scn

         4)open database by using resetlogs

         5)perform a whole database backup

 

      *until time

          RMAN>run{

               set until time "to_date('','')";

               restore database;

               recover database;

               alter database open resetlogs;

               }

 

      *until sequence

          RMAN>run(

               set sequence sequence# thread 1;

               restore database;

               recover database;

               alter database open resetlogs;

               }

 

      *until scn

          RMAN>run{

                  set until scn scn#;

                  restore database;

                  recover database;

                  alter database open resetlogs

               }

 

demo:

1)   time-incomplete:

  

     rman target /

        rman>backup database;(backup database plus archivelog  delete all input;)

 

     sys>alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

     sys>select sysdate from dual;

     sys>drop user hr cascade;

  

  

    rman:

        SYS>shutdown immediate;

        SYS>startup mount;

        RMAN> run{

               set until time "to_date('xxxxxx','yyyy-mm-dd hh24:mi:ss')";

               restore database;

               recover database;

              alter database open resetlogs;

              }

        or

        RMAN>run{

             shutdown immediate;

             startup mount;

             sql 'alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

             set until time 'xxxxxxxx';

             restore database;

             recover database;

             alter database open resetlogs;

         }  

 

  

   select recid,sequence#,resetlogs_change#,resetlogs_time from v$log_history;

  

2) scn-incomplete

 

  rman target /

        rman>backup database;(backup database plus archivelog  delete all input;)

 

  sys:

    select current_scn from v$database;

    drop table scott.emp purge;

 

  rman:

     startup force mount;

     run{

        set until scn=xxxxxx;

        restore database;

        recover database;

        alter database open resetlogs;

        }

 

3) sequence-incomplete

     sys>select name,SEQUENCE#,STATUS,DELETED,APPLIED,ARCHIVED from v$archived_log;

     sys>select * from v$log;

 

     rman>backup database

     sys>alter system switch logfile;

     $rm *.dbf, rm a archivelog

 

     rman>startup mount

     rman>run(

          set sequence xx thread 1;

          restore database;

          recover database;

          alter database open resetlogs;

          }

 

4) lost all

    rman>backup database;

    sql>insert into ...

    sql> alter system switch logfile;

    rm all file(spfile,controlfile,redo log file,data file)

    shutdown abort

    rman>start nomount;

         set dbid=;

         restore spfile from autobackup;

         shutdown abort;

         startup nomount;

         restore controlfile from autobackup;

         alter database mount;

         restore database;

         recover database;

         alter database open resetlogs;

 

5)lost all control file

 

    a) restore controlfile from backupset

 

       RMAN>backup database;

       $>rm *.ctl

       RMAN> restore controlfile from 'file_name';

       SYS> shutdown abort

       SYS> startup mount;

             SYS> select FILE#,CHECKPOINT_CHANGE# from v$datafile;

             select FILE#,CHECKPOINT_CHANGE# from v$datafile_header;

       RMAN>restore database;

       SYS> recover database using backup controlfile until cancel;(filename)

       SYS> alter database open resetlogs;

 

    b)re-create controlfile

             

       sys>alter database backup controlfile to trace;

       $>vi trace file

       sys>startup nomount;

       sys>@trace_file;

 

6)lost current redo log file;

    rman>backup database;

    sys>select * from v$log;

    $rm current redo log file

    sys>shutdown abort

    sys>startup

    sys>alter database clear unarchived logfile group 3;

    rman>restore database;

    sys>recover database until cancel;

    sys>alter database open resetlogs

 

_allow_resetlogs_corruption=true

_corrupted_rollback_segments=true

_offline_rollback_segments=true

select KSPPINM from x$ksppi

 

7)lost redo log file and a data file

   rman>restore database

   sys>recover database until cancel;

       cancel

   sys>alter database open resetlogs;