Please see my other blog for Oracle EBusiness Suite Posts - EBMentors

Search This Blog

Note: All the posts are based on practical approach avoiding lengthy theory. All have been tested on some development servers. Please don’t test any post on production servers until you are sure.

Wednesday, December 08, 2010

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> 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.
SQL> recover database until time '2010-12-08:13:40:00'
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;
select sequence#,first_change#,first_time
from v$archived_log

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

No comments: