Incomplete recovery of an Oracle data file for accidental deletion

Application environment: One of my watch accidentally deleted accidentally, at this time, I can not restore the entire library back, that is too much trouble.

So now I'm going to go to a new library and just copy this data file and restore it.

Then we can not restore the file when Oracle is only a subset of data files, because Oracle to ensure that the data file size information consistent, so if we want to restore some of the files, we have to take the following method:

You can set up a different library, and then copy the data files to recover. (not just the data file, but also the system table space, undo tablespace)

1) Another library is very simple, you can get the parameter file, add a line *.db_unique_name= ' RT ' In the parameter file and modify the control file path.

$ORACLE _sid=rtsqlplus/as sysdba>startup nomount pfile= '/tmp/pfile.ora '

Then what about the control file?

2) Of course we can restore the previously backed up data file directly to the path of the control file in our configured parameter file.

Recovery control File:

Rman target > Restore controlfile to '/u01/app/oracle/oradata/test/rt_con01.ctl ' from '/tmp/full_04pe7jue_1_1.bak ';

Now it's time to mount.

Now is not open, if you open now, he will be the original logfile cover, it is certain that the original library will be problematic.

3) All we have to do here is to recover the data files first:

Use a newname in Rman, first determine the original System,undo, and the tablespace file number to recover.

Run {Allocate channel di type Disk;set newname for datafile 1 to '/tmp/disk1/system01.dbf ', set newname for datafile 3 to ' /TMP/DISK1/UNDOTBS01.DBF '; set newname for datafile 9 to '/tmp/disk1/test_01.dbf '; restore datafile 1,3,9;}

Of course you execute the above error, because we are the newly created control file, so to register a bit:

Rman >catalog start with '/tmp/full_04pe7jue_1_1.bak '

4) Then change the redo log in the main library:

select * from v$log;    group# thread# sequence# bytes  blocksize members arc status first_change# first_time next_change#----------  ---------- ---------- ---------- ---------- ---------- --- --------------- - ------------- ------------------- ------------Next_time-------------------          1 1 75 52428800 512 2 yes inactive  4215102 2014-07-26:22:18:25 42151952014-07-26:22:19:55          2 1 74 52428800 512 2 YES INACTIVE 4211699  2014-07-26:20:55:55 42151022014-07-26:22:18:25         3  1 76 52428800 512 2 NO CURRENT 4215195 2014-07-26:22:19:55  2.8147E+14

We are currently using Group 3, so we can delete group1;

[Email protected]_connect_identifier>alter database drop logfile Group 1; The database has changed. [Email protected]_connect_identifier>alter Database Add logfile Group 1 ('/u01/app/oracle/oradata/test/mredo01.log ') size 60m reuse; The database has changed.

Then delete Group 2:

[Email protected]_connect_identifier>alter database drop logfile Group 2; The database has changed. [Email protected]_connect_identifier>alter database Add logfile Group 2 ('/u01/app/oracle/oradata/test/mredo02.log ') size 60m reuse; The database has changed.

That 3 will follow the log, do a full check point:

[email protected]_connect_identifier>select * from v$log; group# thread#  sequence# bytes blocksize members arc status first_change# first_time  NEXT_CHANGE# ---------- ---------- ---------- ---------- ---------- ------ ---- --- ---------------- ------------- ------------------- ------------ next_ time ------------------- 1 1 0 62914560 512 1 yes unused 0  0 2 1  0 62914560 512 1 yes unused 0 0 3  1  76 52428800 512 2 no current 4215195 2014-07-26:22:19:55  2.8147E+14 [email protected]_connect_identifier>alter system switch logfile; The   system has changed. The  [email protected]_connect_identifier>alter system checkpoint;  system has changed.  [email protecThe ted]_connect_identifier>alter database drop logfile group 3;  database has changed.  [email protected]_connect_identifier>alter database add logfile group 3 ('/u01/app/oracle/oradata/test/mredo03.log ') the size 60m reuse;  database has changed.

Get rid of unused data files in the repository

ALTER DATABASE datafile 2 offline drop;

ALTER DATABASE datafile 4 offline drop;

ALTER DATABASE datafile 5 offline drop;

ALTER DATABASE datafile 6 offline drop;

ALTER DATABASE datafile 8 offline drop;

ALTER DATABASE datafile offline drop;

6) Standby repository change the data file path and archive log file path:

[Email protected]_connect_identifier>alter database rename file '/u01/app/oracle/oradata/test/system01.dbf ' to '/ TMP/DISK1/SYSTEM01.DBF ' 2; The database has changed. [Email protected]_connect_identifier>alter database rename file '/u01/app/oracle/oradata/test/undotbs01.dbf ' to '/ TMP/DISK1/UNDOTBS01.DBF ';   The database has changed. [Email protected]_connect_identifier>alter database rename file '/u01/app/oracle/oradata/test/test_01 ' to '/tmp/  DISK1/TEST_01.DBF '; The database has changed. [Email protected]_connect_identifier>set logsource '/tmp/disk1/arch ';

7) Recover log files :

[Email protected]_connect_identifier>recover database using BACKUP controlfile until cancel; ORA-00279: Change 4203853 (generated on 07/24/2014 19:57:38) is required for thread 1 ORA-00289: Recommended:/tmp/disk1/arch/1_73_831746264.dbf ORA-00280: Change 4203853 (for thread 1) in the sequence #73

Check the data file path, log file path:

[Email Protected]_connect_identifier>select * from V$dbfile; file# NAME--------------------------------------------------10/u01/app/oracle/oradata/test/rman01.dbf 9/tmp/ DISK1/TEST_01.DBF 8/tmp/perstat.ora 6/home/oracle/trans.dbf 5/u01/app/oracle/oradata/test/example01.d BF 4/u01/app/ ORACLE/ORADATA/TEST/USERS01.DBF 3/tmp/disk1/undotbs01.dbf 2/u01/app/oracle/oradata/test/sysaux01.db F FILE# NAME--- -----------------------------------------------1/tmp/disk1/system01.dbf has selected 9 rows.

8) Open the database and end the task:

[Email protected]_connect_identifier>alter database open resetlogs; The database has changed. [Email Protected]_connect_identifier>select * from V$log; group# thread# sequence# BYTES BLOCKSIZE members ARC STATUS first_change# first_time next_change#---------------------- -----------------------------------------------------------------------------------------------------next_time  -------------------1 1 1 52428800 2 NO current 4203854 2014-07-27:12:39:11 2.8147E+14 2 1 0 52428800 2 YES UNUSED 0 0 3 1 0 52428800 2 YES UNUSED 0 0 [email protected]_connect_identifier>

This article is from the "Tech Life" blog, so be sure to keep this source http://jesnridy.blog.51cto.com/5554751/1530757