Reover DB - SQL Plus

recover database until cancel

Do the following to test the scenerio

1- Take full db backup

RMAN> run {
2>  allocate channel CHNL1 type disk;
3>  backup database format 'D:\Home_Backup\ASMRMAN\DB_%d_%u_%s';
4>  backup archivelog all format 'D:\Home_Backup\ASMRMAN\ARC_%d_%u_%s';
5>  #release channel CHNL1;
6>  }

2- Take any tablespace offline

SQL> alter tablespace TS1 offline;

3- Delete  file associated to above mentioned tablespace like TS1

ASMCMD> pwd
+DB_DATA/ASMDB/DATAFILE

ASMCMD> rm TS1.270.737212145

4- Shutdown the DB

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

You will not be able to startup the DB as there is a missing datafile.

5- Startup database with mount option so that recovery may be possible.
SQL> startup mount pfile=D:\ASMTEST\ASMDB\pfile\initASMDB.ora
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  1253124 bytes
Variable Size             264241404 bytes
Database Buffers          801112064 bytes
Redo Buffers                7135232 bytes
Database mounted.

6- Recover the Database

SQL> recover database until cancel
Media recovery complete.
OR
SQL> recover database until time '2010-12-08:13:40:00'
OR
SQL> recover database until change 58  --- where 58 is sequence#
You can find the SCN with query like below
SQL> select max(sequence#) from v$log_history;
56
select sequence#,first_change#,first_time
from v$archived_log

7-  open DB
SQL> alter database open noresetlogs;
Database altered.