Article About Data Recovery Method

1-  If you lost all data files

SQL> startup mount;
RMAN> restore database;
RMAN> recover database;
SQL> alter database open;

2- If you lost a tablespace

SQL> alter tablespace users offline;
RMAN> restore tablespace users;
RMAN> recover tablespace users;
SQL> alter tablespace users online;

 if you can not offline tablespace;

$ sqlplus “/ as sysdba”
SQL> shutdown abort;
SQL> startup mount;
$ rman target / catalog_user/catalog_user_password@catalogdb
RMAN> restore tablespace users;
RMAN> recover tablespace users;
SQL> alter database open;

3- if you lost a datafile

SQL> alter database datafile '/oracle/oradata/users.dbf' offline;
RMAN> restore datafile '/oracle/oradata/users.dbf'
RMAN> recover datafile '/oracle/oradata/users.dbf'
SQL> alter database datafile '/oracle/oradata/users.dbf' online;
 

if you cannot offline datafile;

$ sqlplus “/ as sysdba”
SQL> shutdown abort;
SQL> startup mount;
$ rman target / catalog_user/catalog_user_password@catalogdb
RMAN> restore datafile '/oracle/oradata/users.dbf';
RMAN> recover datafile '/oracle/oradata/users.dbf';
SQL> alter database open;
 

4-  if you lost your controlfiles

$ sqlplus “/ as sysdba”
SQL> shutdown abort;
SQL> startup nomount;
$ rman target / catalog_user/catalog_user_password@catalogdb
RMAN> set dbid = 3970640872;
RMAN> restore controlfile;
SQL> alter database mount;
SQL> alter database open;

you will receive an error ORA-01589 when you open database
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> shutdown abort;
SQL> startup mount;
$ rman target / catalog_user/catalog_user_password@catalogdb
RMAN> recover database;
SQL> alter database open resetlogs;
RMAN> reset database;

if you open database with resetlogs, SCN number will be zero. In this situation
all previous backups will be invalid. You must full backup.

5- May be a special situation. You need to incomplete recovery

A. Time-Based incomplete recovery

$ sqlplus "/ as sysdba"
SQL> shutdown abort;
SQL> startup mount;
$ rman target / catalog_user/catalog_user_password@catalogdb
RMAN> restore database until time "to_date('06/05/11 12:0:00','DD/MM/YY HH24:MI:SS')";
RMAN> recover database until time "to_date('06/05/11 12:0:00','DD/MM/YY HH24:MI:SS')";
SQL> alter database open resetlogs;
 
 
B. SCN-Based incomplete recovery
$ sqlplus "/ as sysdba"
SQL> shutdown abort;
SQL> startup mount;
$ rman target / catalog_user/catalog_user_password@catalogdb
RMAN> restore database until scn 1000;
RMAN> recover database until scn 1000;
SQL> alter database open resetlogs;
C. Archive log sequence based incomplete recovery

$ sqlplus "/ as sysdba"
SQL> shutdown abort;
SQL> startup mount;
$ rman target / catalog_user/catalog_user_password@catalogdb
RMAN> restore database until sequence 9923;
RMAN> recover database until sequence 9923;
SQL> alter database open resetlogs;

6-  if you need some archive logs in your backup
$ rman target / catalog_user/catalog_user_password@catalogdb
RMAN> restore ARCHIVELOG FROM TIME 'SYSDATE-1' UNTIL TIME 'SYSDATE';
 

OR

RMAN> restore ARCHIVELOG FROM TIME "to_date('07/11/05 00:00:01','MM/DD/YY HH24:MI:SS')
UNTIL TIME 'SYSDATE';
 
7- if your data block is corrupted you will receive an error below.

Error:
ORA-01578: ORACLE data block corrupted (file # 8, block # 13)
ORA-01110: data file 8: ‘/oracle/oradata/users.dbf’

for recover data block;

$ rman target / catalog_user/catalog_user_password@catalogdb
RMAN>blockrecover datafile 8 block 13;
 

For Block-Level Media Recovery – Concept & Example (Doc ID 144911.1)

To recover, we can give a specific backup set;

# recovery from backupset
RMAN> BLOCKRECOVER DATAFILE 8 BLOCK 13 DATAFILE 2 BLOCK 19 FROM BACKUPSET;
# recovery from image copy
RMAN> BLOCKRECOVER DATAFILE 8 BLOCK 13 DATAFILE 2 BLOCK 19
FROM DATAFILECOPY;
# recovery from backupset which have "FULL" tag
RMAN> BLOCKRECOVER DATAFILE 8 BLOCK 13 DATAFILE 2 BLOCK 199
FROM TAG = FULL;

During backup or “Validate Backup” command, RMAN finds corrupted blocks and writes to V$DATABASE_BLOCK_CORRUPTION view. When the RMAN recover the corrupt block then automatically updates this view. List of all the corruption of the past, can be viewed over V$BACKUP_CORRUPTION and V$COPY_CORRUPTION views.  If you run the following command,  RMAN will recover all the corrupted blocks in view V$DATABASE_BLOCK_CORRUPTION.

  RMAN>BLOCKRECOVER CORRUPTION LIST RESTORE UNTIL TIME ‘SYSDATE-10’;

8- if you have a image copy backup and your datafile number 2 has problems then you
can switch datafile number2 to image copy.

RMAN>sql ‘alter database datafile 2 offline’;
RMAN>switch datafile 2 to copy;
RMAN>recover datafile 2;
RMAN>sql ‘alter database datafile 2 online’;

Orginial Article
 Thank You
 Osama Mustafa

ORA-16038 ORA-19504

Archiver Hung in Oracle database 
Summary
1. check how much space is used by archiver
2. check the last good backup of the database
3. delete archive log files older than last good backup
4. crosscheck archive log
1. check how much space is used by archiver
  Sql>  select count(*),sum(blocks*block_size) from v$archived_log where    backup_count=0 and      deleted=’NO’;
   Sql> select * from v$flash_recovery_area_usage;
2. check the last good backup of the database

  set pages 999 lines 120

   col STATUS format a9
   col hrs format 999.99
   col start_time format a15
   col end_time format a15
   col dev format a5
   col inbytes format a10
   col outbytes format a10
   select
       INPUT_TYPE, STATUS,
       to_char(START_TIME,’mm/dd/yy hh24:mi’) start_time,
       to_char(END_TIME,’mm/dd/yy hh24:mi’)   end_time,
       elapsed_seconds/3600                   hrs,
       output_device_type dev,
       input_bytes_display inbytes,
       output_bytes_display outbytes
    from V$RMAN_BACKUP_JOB_DETAILS
   order by session_key;

   3. delete archive log files older than last good backup
rman target / nocatalog
 allocate channel for maintenance device type disk;
 crosscheck archivelog all;
 delete noprompt archivelog until time ‘sysdate – 1’;
 delete noprompt expired archivelog all;
 delete noprompt obsolete device type disk;   

4. crosscheck archive log
crosscheck archivelog all;
 release channel;
 exit;  
Thank you 
Osama mustafa