在本机建一个空实例恢复被truncate的表

1、先在原库用rman做好备份。
[oracle@localhost backup]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on ÐÇÆÚÈÕ 9ÔÂ 15 12:28:43 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: ORCL (DBID=1344415282)

RMAN> backup database format='/u01/backup/dbfull_%U.bak';

Starting backup at 2013-09-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 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-09-15
channel ORA_DISK_1: finished piece 1 at 2013-09-15
piece handle=/u01/backup/dbfull_0bojtljv_1_1.bak tag=TAG20130915T122847 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:25
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-09-15
channel ORA_DISK_1: finished piece 1 at 2013-09-15
piece handle=/u01/backup/dbfull_0cojtlmk_1_1.bak tag=TAG20130915T122847 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04
Finished backup at 2013-09-15
2、查看原库的数据库文件路径和表空间。
   select file_id,file_name,tablespace_name from dba_data_files;

   FILE_ID FILE_NAME                                TABLESPACE_NAME
---------- ---------------------------------------- ---------------
         4 /opt/oracle/oradata/ORCL/users01.dbf     USERS
         3 /opt/oracle/oradata/ORCL/sysaux01.dbf    SYSAUX
         2 /opt/oracle/oradata/ORCL/undotbs01.dbf   UNDOTBS1
         1 /opt/oracle/oradata/ORCL/system01.dbf    SYSTEM
         5 /opt/oracle/oradata/ORCL/test01.dbf      TEST
3、查看原库日志文件路径。
SQL>  select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/opt/oracle/oradata/ORCL/redo03.log
/opt/oracle/oradata/ORCL/redo02.log
/opt/oracle/oradata/ORCL/redo01.log

4、创建测试表
SQL>  create table tt as select * from user_users;

Table created.

SQL>  select count(1) from tt;

  COUNT(1)
----------
         1

SQL>  alter system switch logfile;

System altered.

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> select sysdate from dual; --查看truncate表之前的时间

SYSDATE
-------------------
2013-09-15 14:02:07
5、truncate 表
SQL> truncate table tt;

Table truncated.

SQL> alter system switch logfile;

System altered.

6、在本机创建空实例binbin。
并将pfile中db_name 改为原库的db_name ,
*.db_name='ORCL'
7、切换实例ORACLE_SID=binbin
8、用修改后的pifle启动实例binbin。
SQL> 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
9、restore 控制文件。
RMAN>  restore controlfile from '/u01/backup/dbfull_0cojtlmk_1_1.bak';

Starting restore at 2013-09-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 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-09-15
10、将实例mount
RMAN>  startup mount;

database is already started
database mounted
released channel: ORA_DISK_1

11、将数据库恢复到truncate表之前的时间点，恢复时只需要恢复被drop表所在表的表空间，其它表空间可跳过，这样可大大节省恢复时间。

run {
 allocate channel a1 type disk;
sql 'alter session set nls_date_format= "YYYY-MM-DD HH24:MI:SS"';
set until time '2013-09-15 14:02:07';
set newname for datafile '/opt/oracle/oradata/ORCL/users01.dbf' to '/u01/app/oracle/oradata/binbin/users01.dbf';
set newname for datafile '/opt/oracle/oradata/ORCL/sysaux01.dbf' to '/u01/app/oracle/oradata/binbin/sysaux01.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,3,4;
 switch datafile all;
sql 'alter database datafile 1,2,3,4 online';
recover database skip forever tablespace 'TEMP','TEST';
release channel a1;
}

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

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

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 2013-09-15
Starting implicit crosscheck backup at 2013-09-15
Crosschecked 5 objects
Finished implicit crosscheck backup at 2013-09-15

Starting implicit crosscheck copy at 2013-09-15
Crosschecked 4 objects
Finished implicit crosscheck copy at 2013-09-15

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 00003 to /u01/app/oracle/oradata/binbin/sysaux01.dbf
restoring datafile 00004 to /u01/app/oracle/oradata/binbin/users01.dbf
channel a1: reading from backup piece /u01/backup/dbfull_0bojtljv_1_1.bak
channel a1: restored backup piece 1
piece handle=/u01/backup/dbfull_0bojtljv_1_1.bak tag=TAG20130915T122847
channel a1: restore complete, elapsed time: 00:01:05
Finished restore at 2013-09-15

datafile 4 switched to datafile copy
input datafile copy recid=18 stamp=826210387 filename=/u01/app/oracle/oradata/bi                                                                                       nbin/users01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=19 stamp=826210387 filename=/u01/app/oracle/oradata/bi                                                                                       nbin/sysaux01.dbf
datafile 2 switched to datafile copy
input datafile copy recid=20 stamp=826210387 filename=/u01/app/oracle/oradata/bi                                                                                       nbin/undotbs01.dbf
datafile 1 switched to datafile copy
input datafile copy recid=21 stamp=826210387 filename=/u01/app/oracle/oradata/bi                                                                                       nbin/system01.dbf

sql statement: alter database datafile 1,2,3,4 online

Starting recover at 2013-09-15

starting media recovery

archive log thread 1 sequence 1 is already on disk as file /opt/oracle/oradata/ORCL/redo03.log
archive log thread 1 sequence 2 is already on disk as file /opt/oracle/oradata/O RCL/redo01.log
archive log filename=/opt/oracle/oradata/ORCL/redo03.log thread=1 sequence=1
archive log filename=/opt/oracle/oradata/ORCL/redo01.log thread=1 sequence=2
media recovery complete, elapsed time: 00:00:07
Finished recover at 2013-09-15

released channel: a1

12、在打开数据库之前要将redo log文件 rename file一下，否则覆盖原库的redo log
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、验证truncate的表是否被还原。

SQL> select count(1) from tt;

  COUNT(1)
----------
         1
14、将表tt导出。
ORACLE_SID=binbin

[oracle@localhost ~]$ exp \"/ as sysdba\"  tables=tt file=/u01/backup/tt.dmp log=/u01/backup/tt.log

Export: Release 10.2.0.1.0 - Production on ÐÇÆÚÈÕ 9ÔÂ 15 15:40:02 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                             TT          1 rows exported
Export terminated successfully without warnings.

15、将表tt导入原库。
ORACLE_SID=ORCL

[oracle@localhost ~]$ imp \"/ as sysdba\"  tables=tt file=/u01/backup/tt.dmp log=/u01/backup/ttimp.log

Import: Release 10.2.0.1.0 - Production on ÐÇÆÚÈÕ 9ÔÂ 15 15:51:14 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing SYS's objects into SYS
. importing SYS's objects into SYS
. . importing table                           "TT"          1 rows imported
Import terminated successfully without warnings.

16、验证表是否被成功导入原库。
[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on ÐÇÆÚÈÕ 9ÔÂ 15 15:51:19 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> select count(1) from tt;

  COUNT(1)
----------
         1