误删表空间异机恢复

1、创建测试表空间
SQL> create tablespace test datafile '/opt/oracle/oradata/ORCL/test01.dbf' size 50m autoextend on;

Tablespace created.

2、创建测试表
SQL> create table test101 tablespace test as select * from dba_objects;

Table created.

SQL> select count(1) from test101;

  COUNT(1)
----------
     49798
3、备份
RMAN> backup database format='/opt/backup/dbfull_%U.bak';

Starting backup at 2013-10-01
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=135 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/opt/oracle/oradata/ORCL/system01.dbf
input datafile fno=00003 name=/opt/oracle/oradata/ORCL/sysaux01.dbf
input datafile fno=00005 name=/opt/oracle/oradata/ORCL/test01.dbf
input datafile fno=00002 name=/opt/oracle/oradata/ORCL/undotbs01.dbf
input datafile fno=00004 name=/opt/oracle/oradata/ORCL/users01.dbf
channel ORA_DISK_1: starting piece 1 at 2013-10-01
channel ORA_DISK_1: finished piece 1 at 2013-10-01
piece handle=/opt/backup/dbfull_0folb07j_1_1.bak tag=TAG20131001T170555 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:08
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 2013-10-01
channel ORA_DISK_1: finished piece 1 at 2013-10-01
piece handle=/opt/backup/dbfull_0golb0bk_1_1.bak tag=TAG20131001T170555 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04
Finished backup at 2013-10-01

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIV STATUS          FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- ------ --------------- ------------- -------------------
         1          1          2   52428800          1 YES    INACTIVE               731966 2013-09-15 14:01:47
         2          1          3   52428800          1 YES    INACTIVE               732161 2013-09-15 14:07:05
         3          1          4   52428800          1 NO     CURRENT                752480 2013-09-28 12:00:46

SQL>  delete from test101 where rownum <101;

100 rows deleted.

SQL> commit;

Commit complete.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIV STATUS          FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- ------ --------------- ------------- -------------------
         1          1          2   52428800          1 YES    INACTIVE               731966 2013-09-15 14:01:47
         2          1          3   52428800          1 YES    INACTIVE               732161 2013-09-15 14:07:05
         3          1          4   52428800          1 NO     CURRENT                752480 2013-09-28 12:00:46
4、误删表空间
SQL> drop tablespace test including contents and datafiles;

Tablespace dropped.

SQL>  select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIV STATUS          FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- ------ --------------- ------------- -------------------
         1          1          2   52428800          1 YES    INACTIVE               731966 2013-09-15 14:01:47
         2          1          3   52428800          1 YES    INACTIVE               732161 2013-09-15 14:07:05
         3          1          4   52428800          1 NO     CURRENT                752480 2013-09-28 12:00:46
SQL>  alter system switch logfile;

System altered.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIV STATUS          FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- ------ --------------- ------------- -------------------
         1          1          5   52428800          1 NO     CURRENT                766173 2013-10-01 17:15:42
         2          1          3   52428800          1 YES    INACTIVE               732161 2013-09-15 14:07:05
         3          1          4   52428800          1 YES    ACTIVE                 752480 2013-09-28 12:00:46

5、将备份集拷贝到异机并启动到no mount
RMAN> startup nomount pfile='/u01/backup/pfile.ora'

Oracle instance started

Total System Global Area     285212672 bytes

Fixed Size                     2020224 bytes
Variable Size                113249408 bytes
Database Buffers             167772160 bytes
Redo Buffers                   2170880 bytes
6、恢复控制文件
RMAN> restore controlfile from '/opt/backup/dbfull_0golb0bk_1_1.bak';

Starting restore at 2013-10-01
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
output filename=/u01/app/oracle/oradata/binbin/control01.ctl
output filename=/u01/app/oracle/oradata/binbin/control02.ctl
output filename=/u01/app/oracle/oradata/binbin/control03.ctl
Finished restore at 2013-10-01

RMAN> startup mount

database is already started
database mounted
released channel: ORA_DISK_1
RMAN> list backup of tablespace 'TEST';

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
9       Full    571.24M    DISK        00:02:04     2013-10-01
        BP Key: 9   Status: AVAILABLE  Compressed: NO  Tag: TAG20131001T170555
        Piece Name: /opt/backup/dbfull_0folb07j_1_1.bak
  List of Datafiles in backup set 9
  File LV Type Ckp SCN    Ckp Time   Name
  ---- -- ---- ---------- ---------- ----
  5       Full 765523     2013-10-01 /opt/oracle/oradata/ORCL/test01.dbf

7、restore 误删表空间
RMAN>
run {
 allocate channel a1 type disk;
set newname for datafile '/opt/oracle/oradata/ORCL/test01.dbf' to '/u01/app/oracle/oradata/binbin/test01.dbf';
set newname for datafile '/opt/oracle/oradata/ORCL/undotbs01.dbf' to '/u01/app/oracle/oradata/binbin/undotbs01.dbf';
set newname for datafile '/opt/oracle/oradata/ORCL/system01.dbf' to '/u01/app/oracle/oradata/binbin/system01.dbf';

restore datafile 1,2,5;
 switch datafile all;
release channel a1;
}RMAN> 2> 3> 4> 5> 6> 7> 8> 9>

allocated channel: a1
channel a1: sid=157 devtype=DISK

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 2013-10-01
Starting implicit crosscheck backup at 2013-10-01
Crosschecked 9 objects
Finished implicit crosscheck backup at 2013-10-01

Starting implicit crosscheck copy at 2013-10-01
Crosschecked 4 objects
Finished implicit crosscheck copy at 2013-10-01

searching for all files in the recovery area
cataloging files...
no files cataloged

channel a1: starting datafile backupset restore
channel a1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/oradata/binbin/system01.dbf
restoring datafile 00002 to /u01/app/oracle/oradata/binbin/undotbs01.dbf
restoring datafile 00005 to /u01/app/oracle/oradata/binbin/test01.dbf
channel a1: reading from backup piece /opt/backup/dbfull_0folb07j_1_1.bak
channel a1: restored backup piece 1
piece handle=/opt/backup/dbfull_0folb07j_1_1.bak tag=TAG20131001T170555
channel a1: restore complete, elapsed time: 00:01:05
Finished restore at 2013-10-01

datafile 5 switched to datafile copy
input datafile copy recid=17 stamp=827690188 filename=/u01/app/oracle/oradata/binbin/test01.dbf
datafile 2 switched to datafile copy
input datafile copy recid=18 stamp=827690188 filename=/u01/app/oracle/oradata/binbin/undotbs01.dbf
datafile 1 switched to datafile copy
input datafile copy recid=19 stamp=827690188 filename=/u01/app/oracle/oradata/binbin/system01.dbf

released channel: a1
8、将归档拷贝到异机
 cp *dbf /u01/archivelog/
[oracle@localhost archivelog]$ ls -rlt
total 73668
-rw-r----- 1 oracle oinstall   169472 Oct  1 18:01 1_2_826201608.dbf
-rw-r----- 1 oracle oinstall  3673600 Oct  1 18:01 1_1_826201608.dbf
-rw-r----- 1 oracle oinstall 50710016 Oct  1 18:01 1_3_826201608.dbf
-rw-r----- 1 oracle oinstall 20767232 Oct  1 18:01 1_4_826201608.dbf
9、注册归档

RMAN> catalog start with '/u01/archivelog/';

using target database control file instead of recovery catalog
searching for all files that match the pattern /u01/archivelog/

List of Files Unknown to the Database
=====================================
File Name: /u01/archivelog/1_3_826201608.dbf
File Name: /u01/archivelog/1_4_826201608.dbf
File Name: /u01/archivelog/1_2_826201608.dbf
File Name: /u01/archivelog/1_1_826201608.dbf

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/archivelog/1_3_826201608.dbf
File Name: /u01/archivelog/1_4_826201608.dbf
File Name: /u01/archivelog/1_2_826201608.dbf
File Name: /u01/archivelog/1_1_826201608.dbf

10、查看alter日志 ：
Tue Oct  1 17:14:10 2013
drop tablespace test including contents and datafiles
Tue Oct  1 17:14:15 2013
Deleted file /opt/oracle/oradata/ORCL/test01.dbf
Completed: drop tablespace test including contents and datafiles
Tue Oct  1 17:15:43 2013
Thread 1 advanced to log sequence 5
  Current log# 1 seq# 5 mem# 0: /opt/oracle/oradata/ORCL/redo01.log
11、recover 数据库
run {
 allocate channel a1 type disk;
sql 'alter session set nls_date_format= "YYYY-MM-DD HH24:MI:SS"';
set until time '2013-10-1 17:14:09';
 sql 'alter database datafile 1,2,5 online';
recover database skip forever tablespace sysaux,users;
release channel a1;
}
RMAN>
RMAN> 2> 3> 4> 5> 6> 7> 8>

allocated channel: a1
channel a1: sid=159 devtype=DISK

sql statement: alter session set nls_date_format= "YYYY-MM-DD HH24:MI:SS"

executing command: SET until clause

sql statement: alter database datafile 1,2,5 online

Starting recover at 2013-10-01

starting media recovery

archive log thread 1 sequence 4 is already on disk as file /opt/oracle/oradata/ORCL/redo03.log
archive log filename=/opt/oracle/oradata/ORCL/redo03.log thread=1 sequence=4
media recovery complete, elapsed time: 00:00:03
Finished recover at 2013-10-01

released channel: a1

12、rename redo
SQL> alter database rename file '/opt/oracle/oradata/ORCL/redo03.log'  to '/u01/app/oracle/oradata/binbin/redo03.log';

alter database rename file '/opt/oracle/oradata/ORCL/redo02.log'  to '/u01/app/oracle/oradata/binbin/redo02.log';

alter database rename file '/opt/oracle/oradata/ORCL/redo01.log'  to '/u01/app/oracle/oradata/binbin/redo01.log';

13、打开数据库
SQL> alter database open resetlogs;

Database altered.
14、验证误删表空间的恢复

SQL> select count(1) from test101;

  COUNT(1)
----------
     49698
15、将误删的表空间导出，再导入原库即可。