Table restore from complete RMAN backup

Scenario:
Nisha works at premier bank headquarters in Virginia.They have databases primarily running with versions 8i(8.1.7.4) and 9i (9.2.0.6).Full backups are scheduled to run every Sunday using rman. Archivelog backups are taken every two hours. On Tuesday night at 8pm Nisha receives call from management team that, one of application person had dropped a SALES table from "SALE" database.

Nisha is engaged on a conference call to gather more details. Soon she realizes that, she may be out of options other than doing full restore/recovery of database and that will take long time. At this point of time she plans to consult with other DBA team members from branch office. Brian came up with alternate plan which will allow to perform table restore up to point in time in past using RMAN backups.


Plan of action:

Brian lays out complete Process and scripts and explain details to Nisha,

1] Find out last full successful backup.
2] Pickup date and time of restore and calculate duration between last full backup and restore time.
3] Find archivelogs required to roll forward from last full backup till restore time.
4] Now find out tablespace in which table was created.
5] Identify datafiles in that tablespace.
6] Calculate space required for datafiles and other required files. Lets assume SALES_DATA tablespace had 4 datafiles each of 2G, so total of 8G then we have system tablespace of 300M and 2 redo log groups with 2 members each of 100M that will be total of 400M. Finally total archivelogs required are 24 and hence space for archivelog required for recovery will be 2.4G.
Total space will be 8G (datafiles )+ 2.4G (archive) + 400M( redo) + 300M(system) + 200M (control files and other scripts)
is 11.3G.
7] Find out oracle home which have binaries installed and running with same version
8] If you don't want to perform restore/recovery on production server and you have available test server with similar configuration, you can add space on TEST server and NFS mount to prod server to avoid redirected restore and recovery effort.
In this case, test server has similar version and patchset available and hence we will use TEST server to run restore/recovery.

We had temp space /u_stage of 15G which we mounted on TEST server and then NFS mounted to PROD server which will allow us to perform restore operation. Once restore is complete, we will remove nfs mount and perform recovery.

9] run restore of datafiles with system and 4 datafiles

run{

allocate channel t1 type 'SBT_TAPE' parms
'ENV=;

set until time "to_date(''Time of restore before drop",'MON-DD-YYYY HH24:MI:SS')";


set newname for datafile '/u01/system_01.dbf' to '/u_stage/system_01.dbf';
restore datafile '/u01/system_01.dbf';

set newname for datafile '/u02/sales_01.dbf' to '/u_stage/sales_01.dbf';
restore datafile '/u02/sales_01.dbf';

set newname for datafile '/u02/sales_02.dbf' to '/u_stage/sales_02.dbf';
restore datafile '/u02/sales_02.dbf';

set newname for datafile '/u04/sales_03.dbf' to '/u_stage/sales_03.dbf';
restore datafile '/u04/sales_03.dbf';

set newname for datafile '/u04/sales_04.dbf' to '/u_stage/sales_04.dbf';
restore datafile '/u04/sales_04.dbf';

release channel t1;
}

Set until time is time of exact restore just before table drop
set newname for datafile will allow us to restore at alternate location other than production and hence we can perform

restore and recovery without touching production instance.

10] Run restore of archivelogs
run{

allocate channel t1 type 'SBT_TAPE' parms
'ENV=;

set archivelog destination "/u_stage/archive

restore archivelog from logseq "X" until logseq "Y";

release channel t1;
}


This will restore archivelogs from sequence # X to sequence # Y to destination /u_stage/archive which will be different than production location and hence it can be done on separate node without affecting production. Where X and Y are lowest and highest archivelog sequnece number required to recover database until time specified.

11] Now dump controlfile from current production to trace
SQL> alter database backup controlfile to trace;

12] Edit controlfile and keep only datafiles which are being restored along with SYSTEM datafile and redolog members.

Edit path of redolog files to point to /u_stageWe will need to edit database name from "SALE" to "SALEDUP" to avoid any issues.
No archivelog mode is set in order to avoid any archive issues and also resetlogs is necessary since we are doing partial restore/recovery effort.

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "SALEDUP" RESETLOGS NOARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 3
MAXDATAFILES 1000
MAXINSTANCES 1
MAXLOGHISTORY 900
LOGFILE
GROUP 1 (
'/u_stage/redo1_a.dbf',
'/u_stage/redo1_b.dbf'
) SIZE 100M,
GROUP 2 (
'/u_stage/redo2_a.dbf',
'/u_stage/redo2_b.dbf'
) SIZE 100M

DATAFILE
'/u_stage/system_01.dbf',
'/u_stage/sales_01.dbf',
'/u_stage/sales_02.dbf',
'/u_stage/sales_03.dbf',
'/u_stage/sales_04.dbf'
CHARACTER SET US7ASCII
;


13] Edit initSALEDUP.ora file and set db_name to SALEDUP also remove remote_login_password file change path of bdump,udump,adump,cdump and any directories to /u_stage. Also change controlfiles to new location /u_stage. We have created /u_stage/bdump, /u_stage/adump respectively and point dump directories to these one. Also we created /u_stage/controlfiles and put all controlfiles in here.

14] Startup database in nomount mode using binaries from test server(which was selected earlier) which should be of same version and patchset as of databse which we are planning to restore

#sqlplus /nolog
SQL>connect / as sysdba;
SQL>startup nomount;

15] Once database is up in nomount mode, run create controlfile script and recreate new controlfiles

SQL>@create_controlfile.lst
SQL>

Controfile created.

SQL>

16] Mount database and set autorecovery on with time specified before drop.

SQL> set autorecovery on;
SQL> alter session set nls_date_format='mm-dd-yyyy:hh24:mi:ss';
SQL> recover database using backup controlfile until time ''time of restore before drop";

..
... LOGS applied
....

Media recovery complete.

17] Open database in resetlog mode

SQL>Alter database open resetlogs;

18] Now you have restored and successfully recovered objects in SALES_DATA tablespaces upto point in time (just before table drop) specified in recovery script. Make sure there are no index/es on table, if there are any drop them otherwise when queried it will try to access indexes and since we havent restored any indexes tablespaces query will fail.

19] Check and run export of object which was recovred.
SQL> select count(1) from PROJECT.SALES;
count(1)
--------
3900356

SQL>

#exp project/tmp123 file=sales_restore.dmp log=sales_restore.log tables=sales
..
...
....
Exporting table SALES 3900356 rows exported.
Export terminated successfully without warnings.

20] We can alternatively add SALEDUP sid in listener configuration and reload listener file on TEST server and then create dblink to prod and queries from prod via dblink to restored table. SQLPLUS copy is also possible option once SALEDUP is added in listener configuration.


Conclusion:
Nisha was successfully able to restore one table point in time to get required data back from full rman backup within 4 hrs vis-a-vis a total database recovery which may have taken more than 30hours. Within short time, required data was pulled over and recreated table and business resumed as normal with a minimal downtime of functionality.