Database Recovery #1 - System and Undo datafiles

Over the weeks I will be posting exercises to demonstrate database recovery after various disasters.

In summary, the basic form of each article will be:
-A cold back
-Disaster
-Recovery


The following steps are a guide to complete recovery after the loss of a system datafile.

--Firstly shutdown your instance neatly to perform a cold backup.
sqlplus '/ as sysdba'
shutdown immediate;

--Clear out any old backups previously taken from the backup locations!rm /u01/backup/*.dbf
!rm /u01/backup/log/*
!rm /redologs/stage1/*

--Make a brand new cold backup of your files, along with your log files
!cp /u01/app/oracle/oradata/orcl/* /u01/backup/
!cp /redologs/stage1/* /u01/backup/log

--Startup the database and switch some logs startup;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;

--Check current scn information inside the database, datafiles, redolog files and archived log files
set lines 200
column change# format 999999999999999
column current_scn format 999999999999999
column next_change# format 999999999999999
column name format a40

select current_scn from v$database;
select file#, status, enabled, checkpoint_change# from v$datafile;
select sequence#, status, first_change#, next_change# from v$log;
select name, sequence#, first_change#, archived from v$archived_log;
select change# from v$recover_file;

--Labour the database: Create a second session on the instance and run the following to generate activity. 
--While the loop is running, run the step following the pl/sql block.
drop table sys.database_objects;

create table sys.database_objects as select * from dba_objects where rownum < 1;

declare
  w_object_count number;
begin
  --basic work load to generate redo
  for x in 1..30 loop
  --count objects in table and output to screen
    select count(*)
      into w_object_count
      from sys.database_objects;
    dbms_output.put_line(w_object_count);

    --delete and repopulate
    delete from sys.database_objects;
    insert into sys.database_objects
    select * from dba_objects;

    dbms_lock.sleep(2);

  end loop;
end;
/


--Break the instance,
--PLEASE DONT RUN THE FOLLWING UNLESS YOU ARE ABSOLUTELY SURE YOU ARE ON A PRACTICE DATABASE --!rm /u01/app/oracle/oradata/orcl/system01.dbf

--Watch the log for errors. You should see ORA-27041 at some point when the DBW process tries to write to system01.dbf. !tail -f -n 100 /u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log

----------Error to expect 
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf'
ORA-27041: unable to open file
----------

--The instance shutdowns down. Restore datafile and mount the database. 
!cp /u01/backup/system01.dbf /u01/app/oracle/oradata/orcl/system01.dbf
startup mount;

--Check the available SCN information to see the disparity in database files. 
set lines 200
column change# format 999999999999999
column current_scn format 999999999999999
column next_change# format 999999999999999
column name format a40

select current_scn from v$database;
select file#, status, enabled, checkpoint_change# from v$datafile;
select sequence#, status, first_change#, next_change# from v$log;
select name, sequence#, first_change#, archived from v$archived_log;
select * from v$recover_file;

--Recover database
recover datafile 1;

--Open database
alter database open;

--Transact
alter system checkpoint;
alter system switch logfile;
alter system checkpoint;

--Check all the SCN values again to ensure system01.dbf is up to date and no longer needs recovering. Also check the log the the system switchlogile and wrote a check point 
set lines 200
column change# format 999999999999999
column current_scn format 999999999999999
column next_change# format 999999999999999
column name format a40

select current_scn from v$database;
select file#, status, enabled, checkpoint_change# from v$datafile;
select sequence#, status, first_change#, next_change# from v$log;
select name, sequence#, first_change#, archived from v$archived_log;
select * from v$recover_file;

!tail -f -n 100 /u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log