backup and recovery workshop

backup and recovery workshop
     1、a datafile is lost
     2、a tablespace is lost
     3  read-only tablespace is lost
     4  all controlfiles are lost
     5  loss of all datafiles one hes no backup
     6  non-current redologfiles are lost
     7  all redo file are lost
     8  a tempfile is lost
     9  loss of complete database
     10 loss of an important table
     11 loss of an important tablespace
     12 recovery through resetlogs
     13 transportable tablespace
    
    
1、 a data file is lost

    problem: a datafile is lost,the problem tablespace has more than one datafile
    backup:  rman online full database backup
   
    .check v$recover_file,alert.log,and os file
    .rman target sys/change_on_install
    .if catalog database is not available,another alternative is to use target database controlfile;
    .check whether the datafile is backup
   
    rman{
        allocate channel d1 type disk;
        sql "alter database datafile 5 offline";
        restore datafile 5;
        recover datafile 5;
        sql "alter database datafile 5 online";
        }
       
    select file#,status from v$datafile;
   
   
2、 a tablespace is lost
    problem:the users tablespace has been moved to another disk(in our case,it's oradata1 directory)
    afterword the users tablespace is lost due to disk crash,and the disk is not repaired yet,database is close.
   
    backup:rman online full database backup
   
    startup open will fail with:
    ORA-01157:can not identify/lock data file 5--see dbwr trace file
    ora-01110:datafile 5:/oradata1/users1.dbf
   
   
    select   a.name,b.file#,b.name,c.error
       from v$tablespace a,v$datafile b,v$recover_file c
       where a.ts#=b.ts#
       and   b.file#=c.file#
   
   
    rman{
      allocate channel ch1 type disk;
      sql "alter database datafile 5,6 offline";
      sql "alter database open";
      set newname for datafile 5 to '../oradata/users01.dbf';
      set newname for datafile 6 to '../oradata/users02.dbf';
      restore tablespace users;
      switch datafile 5; --switch datafile 修改了控制文件
      switch datafile 6; --
      recover tablesapce users;
      sql "alter tablespace users online";        
    }
   
    if necessary,you can move them backup to original location:
    --- take the tablespace offline
    --- use an operating system command to move or copy the files
    --- execute the alter databse rename file commadn
    --- bring the tablespce online
    --- use an os command to delete the file if necessary
   
   
 3、loss of system tablespace
    if the lost datafile belongs to system tablespace
   
    RMAN>startup mount
    RMAN-06196:oracle instance started
    RMAN-06199:database mounted
   
    rman>run{
       allocate channel c1 type disk;
       restore datafile 1;
       recover datafile 1;
       sql "alter database open";
    }
   
   
  4、Loss of non-essential datafiles;
 
    startup mount
    alter database datafile .. offline;
    alter database open;
    if it's a temporary tablespace(with no tempfile);
    --drop tablespace temp;  如果是临时表空间，则需要drop掉数据
    --create tablespace temp;
   
    if it's an index tablespace
    --alter table .. disable primary key cascade;
    --drop tablespace indx including contents cascade constraints;
    --create tablespace indx ..?
    --alter table enable primary key using index tablespace indx;
    --recreate foreign key indx;
   
 3、read-only tablespace is lost
    problem: a read-only tablespace is lost of damaged
    RMAN backup:RMAN online full database backup,the problem tablespace is writable when the last backup occurred.
   
    SQL>select status from dba_tablespaces where tablespace_name='TOOLS'
   
    RMAN>run{
       allocate channel c1 type disk;
       sql "alter tablespace tools offline immediate";
       restore tablespace tools;
       recover tablespace tools;
       sql "alter tablespace tools online";
   
    }
   
    Tips: always backup your controlfiles and affected datafiles immediatel whenever your database structure changes.
   
    read-only tablespace recovery
   
    4、all controlfiles are lost//控制文件丢了 数据库打不开，RMAN 有备份的控制文件
   
    problem:all controlfiles have been lost,and the  database is closed now,the database has a read-only tablespace.
    rman :have  online full backup.
   
    >startup nomount
    >check the backup of the controlfile
    >run{
       allocate channel c1 type disk ;
       restore controlfile;
       sql "alter database mount";
       recover database;
       sql "alter database open resetlogs";         
    }
   
    >select open_mode from v$database;
   
    .backup your database immediately after open with resetlogs option
    .before you can use RMAN again with a target database that you have opend with the RESETLOGS option,notify RMAN that you
     have reset the database incarnation.
    .RMAN>reset database;
    .
    otehr recovery issues
    .recovery will fail with ora-1244 if the controlfiles backup or reocevery catalog does not has lastest database schema,you can
    check this with:
    RMAN>report schema;
   
    .you can also recreate control files with command if you know the database schema.
   
    >startup nomount;
    >create controlfile..
    >recover database;
    open database will fail with error
    >alter database rename file '..' to '..';
    >alter database datafile '..' online;
    >alter database open;
   
   
 5  loss of all datafiles one has no backup.
 
    problem:all database are lost,the database has a read-only tablespace
   
    rman backup:the lastest rman online full database backup does not include a new datafiles ,however we have all
    archive log since the new datafile has been created.
   
    datafile 8 has no backup.
   
    run{
       startup mount;
       allocate channel d1 type disk;
       restore datafile 1,2,3,4,5,6,7;
       sql "alter database create datafile "/" to "/"";>>重新建立数据文件8
       recover database;>>恢复数据库
       alter database open;>>打开数据库  
       }
      
     donot use restore database  unliess you have all datafiles backup;
     backup datafiles which have no backup;
    
 6、non-current redo log files are lost;
   
    problem: non -current redo log files are lost;
    backup: the redo log group only has one memeber;rman onlien full database backup;
   
    if the lost redo log file in inactive and archived
    >> alter database clear logfile group group#;
   
    if the lost redo log file in inactive and noarchived
    >>alter database clear unarchived logfile group group#;
   
 7、 all redo log are lost
    
     problem:all redo log files are lost,database is closed
     backup:rman online full database backup.
    
     .startup mount    
     .check archived log destination to get the sequnece number of the last archivedlog.
     .if the last archived log sequnece number is 22;
    
     run{
         startup mount;
         allocate channel t1 type disk;
         set until logseq 23 thread 1; //恢复到序列23号日志
         restore database check readonly;
         recover database;
         alter database open resetlogs;
     }
    
     if the database has read-only tablespaces,you have to restore database with check readonly;
    
     RMAN>restore database check readonly;
    
8、 a tempfile is lost

    select file#,name from v$tempfile;
   
    you can drop the logical tempfile and add a new one;
   
    alter database tempfile '../tmp.dbf' drop;
    alter tablespace tmp  add tempfile '../tmp.dbf' size 5M;
   
9、loss of compete database
   problem: current complete database(include redo log files and control files)  has been lost;
   the database contains a read-only tablespace and tempfile
   .rman backup :rman online full database backup.
  
  .check the sequnce number of the last archive log,for example the last one is 9.
 
   RMAN>run{
      startup nomount;
      allocate channel d1 type disk;
      restore controlfile;
      alter database mount;
      set until logseq 10 thread 1;
      restore database check readonly;
      recover database;
      sql "alter database open resetlogs";
    }
   
     PS:the trainee should modify the last archived log sequnece number according to their cases before starting recovery.
    
   SQL> alter tablespace tmp add tempfile '../tmp.dbf' size 5m;
   RMAN>reset database;
  
   Backup your database immediately.
  
10、loss of an important table
  
   problem: one importat table is dropped by accident after 21:04:51
   RMAN backup:rman online full database backup
  
   .set nls_date_format before time-based incomplete recocvery proceed
     nls_date_format='yyyy-mm-dd:hh24:mi:ss'
   .backup your current control files and redo log files to other location.
  
   run{
      startup mount;
      allocate channel d1 type disk;
      set until time '2002-12-28:21:04:51';
      restore database check readonly;
      recover database;
      sql "alter database open resetlogs"; 
   } 
 
11、problem:one import tablespace is dropped by accident;
    RMAN backup: RMAN online full database backup
   
    set nls_date_format before time_based incomplete recovery proceed.
    nls_date_forma='yyyy-mm-dd:hh24:mi:ss';
   
    backup your current control files and redo log files to other location
   
    in alert log,you can find the exact time of dropping tablespace.
    set dec 28 21:13:49 2002
    Drop tablespace test including contents and datafiles
   
    run{
       startup nomount;
       allocate channel d1 type disk;
       set until time '2002-12-28:21:13:48';  //set until scn
       restore controlfile;
       sql "alter database mount";
       restore database check readonly;
       recover database;
       sql "alter database open resetlogs";
    }
   
   RMAN>reset database;
   RMAN>BACKUP DATABASE;