Backup and recovery workshop partII --scenarios

ORACLE SOFTWARE POWERS THE INTERNET
Backup and recovery workshop partII  --scenarios

 

>scenario1: a datafile is lost
>scenario2: a tablespace is lost
>scenario3: read-only tablespace is lost
>scenario4: all controlfiles are lost
>scenario5: loss of all datafiles,one has no backup
>scenario6: non-current redo log files are lost
>scenario7: all redo log files are lost
>scenario8: a tempfile is lost
>scenario9: loss of complete database
>scenario10: loss of an important table
>scenario11: loss of an important tablespace
>scenario12: recovery through resetlogs
>scenarit13: transportable tablespace

 

1. scenario 1 a datafile 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 files
    .rman target catalog
    .if catalog database is not available,another alternative is to use target database controlfile;
    rman target nocatalog;
   
    .check whether the lost datafile has backup.
   
    RMAN>run{
        allocate channel d1 type disk;
        sql "alter database datafile 5 offline";
        restore datafile 5;
        recover datafile 5;
        sql "alter database datafile 5 online";    
          }
         
         
         
2. scenario 2 a tablespace is lost

        problem :the users tablespace has been moved to another disk(
          in our case,it's oradata1 directory}afterward the users tablespace is lost
          due to disk crash,and the disk is not repaired yet,database is closed.
       
        backup: RMAN online full database backup
        startup open will fail with:
       
        startup open will fail with:
        ORA-01157
        ORA-01110
        SQL>select a.name,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#;
       
        .check whether the lost datafile have backup.
       
        run{
            startup mount;
            allocate channel d1 type disk;
            sql "alter database datafile 5,6 offline";
            sql "alter database open";
            set newname datafile 5 to './oradata/user1.dbf';
            set newname datafile 6 to './oradata/user2.dbf';
            restore tablespace users;
            switch datafile 5;
            switch datafile 6;
            recover tablespace users;
            sql "alter tablespace user online";     
            }
           
3.      select name,status from v$datafile where file# in (5,6);
        select status from dba_tablespaces where tablespace_name='USERS';

       if necessary,you can move them backup to original location.
       
        take the tablespace offline
        use an operation system command to move or copy the files
        execute the alter database rename file command
        being the tablespace online.
        use an operating system command to delete the file if necessary.
       
         if the lost datafile belongs to system tablespace
        RMAN>startup mount
        RMAN>{
           allocate channel d1 tyep disk;
           restore datafile 1;
           recover datafile 1;
           sql "alter database open";
              }
    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;
       create tablespace temp;
      
       if it's a 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 index;
      
      
 4.   all controlfiles are lost
      problem: all controlfiles have benn lost,and the database is closed now,the database has a read-only tablespace.
      RMAN backup:RMAN online full database backup.
     
      RMAN>startup nomount
      .check whether controlfiles backup is available
      run{
         allocate channel d1 type disk;
         restore controlfile;
         alter database mount;
         recover database;
         alter database open resetlogs;
      }      
      SQL>select open_mode from v$database;
     
 5.    backup your database immediately after open with resetlogs option.
       before you can use RMAN again with a target database that you have opened with resetlogs options
       notify RMAN that you have reset the database incarnation;
      
       RMAN>reset database;
      
       in oracle9i,new incarnation of database will be registered in recovery catalog automatically in this case.
       recovery will fail with ora-1244 if the controlfiles backup or recovery catalog does not has lastest database schema,
       you can check this with;
       RMAN>report schema;
       .you can also recreate controlfiles with command if you know the databse schema.
      
       to recreate the controlfile
       use the sql statement in oracle nomount status;
      
        CREATE CONTROLFILE REUSE DATABASE "S01"
        NORESETLOGS  ARCHIVELOG
        MAXLOGFILES 32
        MAXLOGMEMBERS 2
        MAXDATAFILES 254
        MAXINSTANCES 8
        MAXLOGHISTORY 904
        LOGFILE
           GROUP 1 '/u01/dennis/s00/oradata/redo01.log'  SIZE 500K,
           GROUP 2 '/u01/dennis/s00/oradata/redo02.log'  SIZE 500K,
           GROUP 3 '/u01/dennis/s00/oradata/redo03.log'  SIZE 500K
        DATAFILE
           '/u01/dennis/s00/oradata/system01.dbf',
           '/u01/dennis/s00/oradata/rbs1.dbf',
           '/u01/dennis/s00/oradata/temp1.dbf',
           '/u01/dennis/s00/oradata/users1.dbf',
           '/u01/dennis/s00/oradata/users2.dbf',
           '/u01/dennis/s00/oradata/indx1.dbf'
        CHARACTER SET UTF8;
       
       
       1.startup nomount;
       2.create controlfile..using the create control file statement;
       3.recover database;(if the previous shutdown is not normal or immediate);
      
5. scenario loss of all datafiles,one has no backup(create after full backup)
     
      
       problem: all datafile are lost,the database has a read-only tablespace
      
       RMAN backup:  the latest RMAN online full database backup does not include a new
       datafiles,however we have all archived logs since this 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 '/oradata/users3.dbf' as '../oradata/users3.dbf'";
          recover database;
          alter dabase open;
       }

       do not use restore databse,unless you have all datafiles backups!
       backup datafile 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 member RMAN online full databse backup.
       
       if the lost log redo log file in inactive and archived
   --  alter database clear logfile group group#;
       if the lost redo log file is inactive and un archivedl
   --  alter database clear unarchived logfile group group#;
       every redo log group is advised to have multiple members and put in different disk drivs.
    
 
7.   scenario7
     All redo log files 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 sequence number of the last archived log.
    
     if the last archived log sequence number is 22;
     run{
        startup mount;
        allocate channel d1 type disk;
        set until logseq 23 thread 1;
        restore database check readonly;
        recover database;
        alter database open resetlogs;
     }
    
     ps:teh trainee should modify the last archived log sequence number according to there cases before starting recovery.
     .backup databse immediately;
    
 
      if a database has read-only tabelspaces,you have to restore database with check readonly
      RMAN>restroe database check readonly;
     
     
 8.   scenario 8 a tempfile is lost
 
     problem : a file is lost,users get errotrs: ora-01110,ora-27041
     (the file was created with
      create temporary tablespace ...tempfile ...;)
     
     backup :rman online full database backup;
    
     because space management is much simpler and more efficient in locally maneged tablespaces,use temporary
     tablespaces since they are the only temporary tablespaces compatible with local management
     .you can find tempfiles in v$tempfile instead of v$datafile;
     .rman does not backup tempfiles with full database backup proceeds.
    
     SQL>select file#,name from v$tempfile;
     .you can drop the logical tempfile and add a new one.
      alter databse tempfile '../oradata/tmp.dbf' drop;
     
      alter tablespace tmp add tempfile '../oradata/tmp.dbf' size 5m;
     
     check the sequence number of the
     
9.   loss complete of database
     current complete database file are loss include(redo log file,control files)
     the database contains a temp tablespaced and a readonly tablespace
    
     rman: backup online full database
     check the number of the last sequence archivelog
     for example the last numnber of the archivelog
    
     RMAN> run{
        startup nomount;
        allocate channel c1 type disk;
        restore controlfile;
        alter database mount;
        set until logseq 10 thread 1;
        restore database check readonly;
        recover database;
        sql "alter database open resetlogs"; 
             }
    
     sqlplus : alter tablespace tmp add tempfile './..tmp.dbf' size 200m;
    
     rman> reset database;
     backup your database immediate;
    
 
10.    loss of an important table
       one important table have dropped after 21:04:51
       backup: RMAN online full backup
      
       set nls_date_format='yyyy-mm-dd:hh24:mi:ss';
      
       backup you current control file and redo log 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.     loss of an important tablespace
         problem:one important tablespace is dropped by accident.
         rman backup:rman online full database backup
        
        . set nls_date_format
        . backup you 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
       
         run{
            startup nomount;
            allocate channel c1 type disk;
            set until time '2002-12-28:21:13:48';
            restore controlfile;
            alter database mount;
            restore database check readonly;
            recover database;
            sql "alter database open resetlogs";
        
         }