用到2个机子
主节点 192.168.119.145 hostname=psd
辅助节点192.168.119.146 hostname=test
要求有rman备份

方法一：如果库上只有这一个业务用户，就可以直接用rman 做全库的时间点恢复。
方法二：如果有多个用户，大致思路，在psd节点上备份数据库，然后删除用户，之后将备份恢复到test节点上，然后将test节点的用户数据逻辑导出，最后导入到psd节点。

方法二的测试

确认数据库是归档模式，首先在scott用户下创建一个t1表，并插入数据

[oracle@psd ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue Mar 17 10:39:53 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 14
Next log sequence to archive 16
Current log sequence 16
SQL>
SQL> show parameter db_re
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /oracle/fast_recovery_area
db_recovery_file_dest_size big integer 4122M
db_recycle_cache_size big integer 0
SQL>
SQL> conn scott/scott
Connected.
SQL> create table t1 (id number);
Table created.
SQL> begin
2 for i in 1..5 loop
3 insert into t1 values (i);
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> select * from t1;
ID
----------
1
2
3
4
5
SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@psd backup]$

接下来开始备份数据库，备份完在向t1表插入数据，然后创建t2表，之后删掉scott用户

[oracle@psd backup]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Tue Mar 17 11:38:55 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: PSD (DBID=2007794869)
RMAN> backup database format '/home/oracle/backup/psd_%U.bak' plus archivelog format '/home/oracle/backup/arch_%U.bak';
Starting backup at 17-MAR-15
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=41 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=12 RECID=11 STAMP=873219765
input archived log thread=1 sequence=13 RECID=12 STAMP=874489166
input archived log thread=1 sequence=14 RECID=13 STAMP=874489173
input archived log thread=1 sequence=15 RECID=14 STAMP=874533680
input archived log thread=1 sequence=16 RECID=15 STAMP=874582892
channel ORA_DISK_1: starting piece 1 at 17-MAR-15
channel ORA_DISK_1: finished piece 1 at 17-MAR-15
piece handle=/home/oracle/backup/arch_02q224re_1_1.bak tag=TAG20150317T114134 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 17-MAR-15
Starting backup at 17-MAR-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/oracle/oradata/psd/system01.dbf
input datafile file number=00002 name=/oracle/oradata/psd/sysaux01.dbf
input datafile file number=00003 name=/oracle/oradata/psd/undotbs01.dbf
input datafile file number=00004 name=/oracle/oradata/psd/users01.dbf
channel ORA_DISK_1: starting piece 1 at 17-MAR-15
channel ORA_DISK_1: finished piece 1 at 17-MAR-15
piece handle=/home/oracle/backup/psd_03q224s8_1_1.bak tag=TAG20150317T114200 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:46
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 17-MAR-15
channel ORA_DISK_1: finished piece 1 at 17-MAR-15
piece handle=/home/oracle/backup/psd_04q224vi_1_1.bak tag=TAG20150317T114200 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 17-MAR-15
Starting backup at 17-MAR-15
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=17 RECID=16 STAMP=874583030
channel ORA_DISK_1: starting piece 1 at 17-MAR-15
channel ORA_DISK_1: finished piece 1 at 17-MAR-15
piece handle=/home/oracle/backup/arch_05q224vm_1_1.bak tag=TAG20150317T114350 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 17-MAR-15
RMAN>
RMAN> exit
Recovery Manager complete.
[oracle@psd backup]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Tue Mar 17 11:44:27 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> conn scott/scott
Connected.
SQL> begin
2 for i in 6..10 loop
3 insert into t1 values (i);
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> select * from t1;
ID
----------
1
2
3
4
5
6
7
8
9
10
10 rows selected.
SQL> create table t2 as select * from t1;
Table created.
SQL> conn /as sysdba
Connected.
SQL> drop user scott cascade;
User dropped.

现在scott用户已经被删掉了，要为恢复做准备，我们查看pfile文件，把相关的目录在辅助节点（192.168.119.146）都创建好，下面标红的目录

SQL> create pfile from spfile;
File created.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@psd backup]$ cd $ORACLE_HOME/dbs
[oracle@psd dbs]$ cat initpsd.ora
psd.__db_cache_size=96468992
psd.__java_pool_size=4194304
psd.__large_pool_size=4194304
psd.__oracle_base='/oracle'#ORACLE_BASE set from environment
psd.__pga_aggregate_target=142606336
psd.__sga_target=272629760
psd.__shared_io_pool_size=0
psd.__shared_pool_size=159383552
psd.__streams_pool_size=0
*.audit_file_dest='/oracle/admin/psd/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/oracle/oradata/psd/control01.ctl','/oracle/fast_recovery_area/psd/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='psd'
*.db_recovery_file_dest='/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4322230272
*.diagnostic_dest='/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=psdXDB)'
*.memory_target=414187520
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

把刚刚的rman备份、pfile文件以及密码文件拷贝到192.168.119.146上，然后我们就要确定drop scott的具体时间，用logminer来搞定

[oracle@psd backup]$ cd /oracle/fast_recovery_area/PSD/archivelog/
[oracle@psd archivelog]$ cd 2015_03_17/
[oracle@psd 2015_03_17]$ ll
total 10248
-rw-r-----. 1 oracle oinstall 7795200 Mar 17 11:41 o1_mf_1_16_bjh8mbov_.arc
-rw-r-----. 1 oracle oinstall 2048 Mar 17 11:43 o1_mf_1_17_bjh8qp80_.arc
-rw-r-----. 1 oracle oinstall 2674688 Mar 17 12:03 o1_mf_1_18_bjh9wyqj_.arc
-rw-r-----. 1 oracle oinstall 1024 Mar 17 12:03 o1_mf_1_19_bjh9x0hy_.arc
-rw-r-----. 1 oracle oinstall 9728 Mar 17 12:03 o1_mf_1_20_bjh9x3xb_.arc
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/oracle/fast_recovery_area/PSD/archivelog/2015_03_17/o1_mf_1_16_bjh8mbov_.arc',OPTIONS => DBMS_LOGMNR.NEW);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/oracle/fast_recovery_area/PSD/archivelog/2015_03_17/o1_mf_1_17_bjh8qp80_.arc',OPTIONS => DBMS_LOGMNR.ADDFILE);
PL/SQL procedure successfully completed.
SQL>
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/oracle/fast_recovery_area/PSD/archivelog/2015_03_17/o1_mf_1_18_bjh9wyqj_.arc',OPTIONS => DBMS_LOGMNR.ADDFILE);
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
PL/SQL procedure successfully completed.
SQL> col sql_redo format a50
SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
SQL> select sql_redo,scn,start_scn,TIMESTAMP,START_TIMESTAMP from V$LOGMNR_CONTENTS where lower(sql_redo) like \'%drop%\' and lower(sql_redo) like \'%scott%\';
SQL_REDO SCN START_SCN TIMESTAMP START_TIMESTAMP
------------------------------------------------------------ ---------- ---------- ------------------- -------------------
drop table scott.emp1 AS "BIN$EWlpmtpQdoXgU5F3qMAiMw==$0" ; 1272611 2015-03-16 22:10:29
drop table "SCOTT"."BIN$EWNzFGZja8TgU5F3qMDpoQ==$0" purge; 1278724 2015-03-17 11:46:13
drop table "SCOTT"."BIN$EWOQLk80bF3gU5F3qMBi4g==$0" purge; 1278746 2015-03-17 11:46:14
drop table "SCOTT"."BIN$EWNb3bwua9TgU5F3qMA7BA==$0" purge; 1278770 2015-03-17 11:46:14
drop table "SCOTT"."BIN$EWNb3bwva9TgU5F3qMA7BA==$0" purge; 1278791 2015-03-17 11:46:15
drop table "SCOTT"."BIN$EWNzFGZka8TgU5F3qMDpoQ==$0" purge; 1278813 2015-03-17 11:46:15
drop table "SCOTT"."BIN$EWOU7d6fbGPgU5F3qMA5vw==$0" purge; 1278834 2015-03-17 11:46:15
drop table "SCOTT"."BIN$EWOQLk81bF3gU5F3qMBi4g==$0" purge; 1278856 2015-03-17 11:46:15
drop table "SCOTT"."BIN$EWOniXMLbHvgU5F3qMAS1g==$0" purge; 1278878 2015-03-17 11:46:15
drop table "SCOTT"."BIN$EWOniXMMbHvgU5F3qMAS1g==$0" purge; 1278900 2015-03-17 11:46:15
drop table "SCOTT"."BIN$EWOniXMNbHvgU5F3qMAS1g==$0" purge; 1278921 2015-03-17 11:46:15

SQL_REDO SCN START_SCN TIMESTAMP START_TIMESTAMP
------------------------------------------------------------ ---------- ---------- ------------------- -------------------
drop table "SCOTT"."BIN$EWOniXMObHvgU5F3qMAS1g==$0" purge; 1278943 2015-03-17 11:46:15
drop table "SCOTT"."BIN$EWPg+H8lbNLgU5F3qMCj4g==$0" purge; 1278965 2015-03-17 11:46:15
drop table "SCOTT"."BIN$EWPg+H8mbNLgU5F3qMCj4g==$0" purge; 1278986 2015-03-17 11:46:15
drop table "SCOTT"."BIN$EWPg+H8nbNLgU5F3qMCj4g==$0" purge; 1279008 2015-03-17 11:46:16
drop table "SCOTT"."BIN$EWTPDfF1bmPgU5F3qMBohA==$0" purge; 1279030 2015-03-17 11:46:16
drop table "SCOTT"."BIN$EWTPDfF2bmPgU5F3qMBohA==$0" purge; 1279052 2015-03-17 11:46:16
drop table "SCOTT"."BIN$EWTPDfF3bmPgU5F3qMBohA==$0" purge; 1279074 2015-03-17 11:46:16
drop table "SCOTT"."BIN$EWTSV1AibnLgU5F3qMCKBg==$0" purge; 1279096 2015-03-17 11:46:16
drop table "SCOTT"."BIN$EWTaumLtbn7gU5F3qMAgNQ==$0" purge; 1279118 2015-03-17 11:46:16
drop table "SCOTT"."BIN$EWTdffDnboXgU5F3qMCDZg==$0" purge; 1279140 2015-03-17 11:46:16
drop table "SCOTT"."BIN$EWTdffDoboXgU5F3qMCDZg==$0" purge; 1279162 2015-03-17 11:46:16

SQL_REDO SCN START_SCN TIMESTAMP START_TIMESTAMP
------------------------------------------------------------ ---------- ---------- ------------------- -------------------
drop table "SCOTT"."BIN$EWTdffDpboXgU5F3qMCDZg==$0" purge; 1279184 2015-03-17 11:46:16
drop table "SCOTT"."BIN$EWVlDuUgb2jgU5F3qMAyEQ==$0" purge; 1279206 2015-03-17 11:46:16
drop table "SCOTT"."BIN$EWi11zOecYPgU5F3qMAYuA==$0" purge; 1279228 2015-03-17 11:46:17
drop table "SCOTT"."BIN$EWjjuPdrc7fgU5F3qMD+ag==$0" purge; 1279250 2015-03-17 11:46:17
drop table "SCOTT"."BIN$EWjqnThDc77gU5F3qMC90A==$0" purge; 1279272 2015-03-17 11:46:17
drop table "SCOTT"."BIN$EWjqnThEc77gU5F3qMC90A==$0" purge; 1279294 2015-03-17 11:46:17
drop table "SCOTT"."BIN$EWlYdSbqdmHgU5F3qMBLJg==$0" purge; 1279314 2015-03-17 11:46:17
drop table "SCOTT"."BIN$EWlpmtpQdoXgU5F3qMAiMw==$0" purge; 1279336 2015-03-17 11:46:17
drop table "SCOTT"."T2" cascade constraints purge force; 1279359 2015-03-17 11:46:22
drop table "SCOTT"."T1" cascade constraints purge force; 1279379 2015-03-17 11:46:22
drop table "SCOTT"."DEPT1" cascade constraints purge force; 1279401 2015-03-17 11:46:22

SQL_REDO SCN START_SCN TIMESTAMP START_TIMESTAMP
------------------------------------------------------------ ---------- ---------- ------------------- -------------------
drop table "SCOTT"."SALGRADE" cascade constraints purge forc 1279424 2015-03-17 11:46:22
e;

drop table "SCOTT"."BONUS" cascade constraints purge force; 1279447 2015-03-17 11:46:22
drop table "SCOTT"."EMP" cascade constraints purge force; 1279459 2015-03-17 11:46:23
drop table "SCOTT"."DEPT" cascade constraints purge force; 1279497 2015-03-17 11:46:23
drop procedure "SCOTT"."P_EMP1"; 1279533 2015-03-17 11:46:23
drop procedure "SCOTT"."P_INSERT"; 1279555 2015-03-17 11:46:24
drop procedure "SCOTT"."P_INSERT_T"; 1279573 2015-03-17 11:46:24
drop user scott cascade; 1279609 2015-03-17 11:46:40
41 rows selected.
SQL>

从这里我们可以判断，drop用户的时间是 2015-03-17 11:46:13，我的测试机器上没有多少归档，但是在生产环境归档会很多，想确定drop的时间就得多看几个归档日志了。
确定时间，我们就开始在辅助节点做恢复操作

[oracle@test backup]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Tue Mar 17 12:27:21 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> startup nomount;
Oracle instance started
Total System Global Area 413372416 bytes
Fixed Size 2228904 bytes
Variable Size 310381912 bytes
Database Buffers 96468992 bytes
Redo Buffers 4292608 bytes
RMAN> restore controlfile from '/home/oracle/backup/psd_04q224vi_1_1.bak';
Starting restore at 17-MAR-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/oracle/oradata/psd/control01.ctl
output file name=/oracle/fast_recovery_area/psd/control02.ctl
Finished restore at 17-MAR-15
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> restore database until time "to_date(\'2015-03-17 11:46:10\',\'yyyy-mm-dd hh24:mi:ss\')";
Starting restore at 17-MAR-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /oracle/oradata/psd/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /oracle/oradata/psd/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /oracle/oradata/psd/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /oracle/oradata/psd/users01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/psd_03q224s8_1_1.bak
channel ORA_DISK_1: piece handle=/home/oracle/backup/psd_03q224s8_1_1.bak tag=TAG20150317T114200
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:56
Finished restore at 17-MAR-15
RMAN> recover database until time "to_date(\'2015-03-17 11:46:10\',\'yyyy-mm-dd hh24:mi:ss\')";
Starting recover at 17-MAR-15
using channel ORA_DISK_1
starting media recovery
unable to find archived log
archived log thread=1 sequence=17
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/17/2015 12:35:48
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 17 and starting SCN of 1278496

这是报了个错无，说17号归档找不到，我们把主库的17号归档考过来，我考到了/home/oracle/backup 下面了，然后继续恢复

RMAN> catalog start with '/home/oracle/backup';
searching for all files that match the pattern /home/oracle/backup
List of Files Unknown to the Database
=====================================
File Name: /home/oracle/backup/psd_04q224vi_1_1.bak
File Name: /home/oracle/backup/o1_mf_1_17_bjh8qp80_.arc
File Name: /home/oracle/backup/o1_mf_1_16_bjh8mbov_.arc
File Name: /home/oracle/backup/o1_mf_1_20_bjh9x3xb_.arc
File Name: /home/oracle/backup/o1_mf_1_18_bjh9wyqj_.arc
File Name: /home/oracle/backup/o1_mf_1_19_bjh9x0hy_.arc
File Name: /home/oracle/backup/arch_05q224vm_1_1.bak
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /home/oracle/backup/psd_04q224vi_1_1.bak
File Name: /home/oracle/backup/o1_mf_1_17_bjh8qp80_.arc
File Name: /home/oracle/backup/o1_mf_1_16_bjh8mbov_.arc
File Name: /home/oracle/backup/o1_mf_1_20_bjh9x3xb_.arc
File Name: /home/oracle/backup/o1_mf_1_18_bjh9wyqj_.arc
File Name: /home/oracle/backup/o1_mf_1_19_bjh9x0hy_.arc
File Name: /home/oracle/backup/arch_05q224vm_1_1.bak
RMAN> recover database until time "to_date(\'2015-03-17 11:46:10\',\'yyyy-mm-dd hh24:mi:ss\')";
Starting recover at 17-MAR-15
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 17 is already on disk as file /home/oracle/backup/o1_mf_1_17_bjh8qp80_.arc
archived log for thread 1 with sequence 18 is already on disk as file /home/oracle/backup/o1_mf_1_18_bjh9wyqj_.arc
archived log file name=/home/oracle/backup/o1_mf_1_17_bjh8qp80_.arc thread=1 sequence=17
archived log file name=/home/oracle/backup/o1_mf_1_18_bjh9wyqj_.arc thread=1 sequence=18
media recovery complete, elapsed time: 00:00:01
Finished recover at 17-MAR-15
RMAN> alter database open;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 03/17/2015 12:38:58
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
RMAN> alter database open resetlogs;
database opened

这时候数据库已经打开，我们看看scott用户下的数据

点击(此处)折叠或打开

SQL> conn scott/scott
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$EWNb3bwua9TgU5F3qMA7BA==$0 TABLE
BIN$EWNb3bwva9TgU5F3qMA7BA==$0 TABLE
BIN$EWNzFGZja8TgU5F3qMDpoQ==$0 TABLE
BIN$EWNzFGZka8TgU5F3qMDpoQ==$0 TABLE
BIN$EWOQLk80bF3gU5F3qMBi4g==$0 TABLE
BIN$EWOQLk81bF3gU5F3qMBi4g==$0 TABLE
BIN$EWOU7d6fbGPgU5F3qMA5vw==$0 TABLE
BIN$EWOniXMLbHvgU5F3qMAS1g==$0 TABLE
BIN$EWOniXMMbHvgU5F3qMAS1g==$0 TABLE
BIN$EWOniXMNbHvgU5F3qMAS1g==$0 TABLE
BIN$EWOniXMObHvgU5F3qMAS1g==$0 TABLE
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$EWPg+H8lbNLgU5F3qMCj4g==$0 TABLE
BIN$EWPg+H8mbNLgU5F3qMCj4g==$0 TABLE
BIN$EWPg+H8nbNLgU5F3qMCj4g==$0 TABLE
BIN$EWTPDfF1bmPgU5F3qMBohA==$0 TABLE
BIN$EWTPDfF2bmPgU5F3qMBohA==$0 TABLE
BIN$EWTPDfF3bmPgU5F3qMBohA==$0 TABLE
BIN$EWTSV1AibnLgU5F3qMCKBg==$0 TABLE
BIN$EWTaumLtbn7gU5F3qMAgNQ==$0 TABLE
BIN$EWTdffDnboXgU5F3qMCDZg==$0 TABLE
BIN$EWTdffDoboXgU5F3qMCDZg==$0 TABLE
BIN$EWTdffDpboXgU5F3qMCDZg==$0 TABLE
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$EWVlDuUgb2jgU5F3qMAyEQ==$0 TABLE
BIN$EWi11zOecYPgU5F3qMAYuA==$0 TABLE
BIN$EWjjuPdrc7fgU5F3qMD+ag==$0 TABLE
BIN$EWjqnThDc77gU5F3qMC90A==$0 TABLE
BIN$EWjqnThEc77gU5F3qMC90A==$0 TABLE
BIN$EWlYdSbqdmHgU5F3qMBLJg==$0 TABLE
BIN$EWlpmtpQdoXgU5F3qMAiMw==$0 TABLE
BONUS TABLE
DEPT TABLE
DEPT1 TABLE
EMP TABLE
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
SALGRADE TABLE
T1 TABLE
T2 TABLE
36 rows selected.
SQL> select * from t1;
ID
----------
1
2
3
4
5
6
7
8
9
10
10 rows selected.
SQL> select * from t2;
ID
----------
1
2
3
4
5
6
7
8
9
10
10 rows selected.
SQL>

发现scott回收站里有很多垃圾表，可以无视，我们发现t1,t2的数据都正常，这时候只要把scott的数据导出来再导入到主库就好了

[oracle@test ~]$ expdp scott/scott dumpfile=scott.dmp directory=dir schemas=scott;
Export: Release 11.2.0.3.0 - Production on Tue Mar 17 13:17:05 2015
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting \"SCOTT\".\"SYS_EXPORT_SCHEMA_01\": scott/******** dumpfile=scott.dmp directory=dir schemas=scott
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 384 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported \"SCOTT\".\"DEPT\" 5.937 KB 4 rows
. . exported \"SCOTT\".\"DEPT1\" 5.515 KB 4 rows
. . exported \"SCOTT\".\"EMP\" 8.570 KB 14 rows
. . exported \"SCOTT\".\"SALGRADE\" 5.867 KB 5 rows
. . exported \"SCOTT\".\"T1\" 5.070 KB 10 rows
. . exported \"SCOTT\".\"T2\" 5.070 KB 10 rows
. . exported \"SCOTT\".\"BONUS\" 0 KB 0 rows
Master table \"SCOTT\".\"SYS_EXPORT_SCHEMA_01\" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/scott.dmp
Job \"SCOTT\".\"SYS_EXPORT_SCHEMA_01\" successfully completed at 13:18:34
[oracle@test ~]$ scp scott.dmp psd:/home/oracle
The authenticity of host \'psd (192.168.119.145)\' can\'t be established.
RSA key fingerprint is 67:e2:04:8e:aa:42:f1:97:c6:14:69:36:ef:86:2e:b0.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added \'psd,192.168.119.145\' (RSA) to the list of known hosts.
oracle@psd\'s password:
scott.dmp 100% 288KB 288.0KB/s 00:00
[oracle@test ~]$
[oracle@psd ~]$ impdp system/oracle dumpfile=scott.dmp directory=dir ;
Import: Release 11.2.0.3.0 - Production on Tue Mar 17 13:20:05 2015
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table \"SYSTEM\".\"SYS_IMPORT_FULL_01\" successfully loaded/unloaded
Starting \"SYSTEM\".\"SYS_IMPORT_FULL_01\": system/******** dumpfile=scott.dmp directory=dir
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported \"SCOTT\".\"DEPT\" 5.937 KB 4 rows
. . imported \"SCOTT\".\"DEPT1\" 5.515 KB 4 rows
. . imported \"SCOTT\".\"EMP\" 8.570 KB 14 rows
. . imported \"SCOTT\".\"SALGRADE\" 5.867 KB 5 rows
. . imported \"SCOTT\".\"T1\" 5.070 KB 10 rows
. . imported \"SCOTT\".\"T2\" 5.070 KB 10 rows
. . imported \"SCOTT\".\"BONUS\" 0 KB 0 rows
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
ORA-39082: Object type ALTER_PROCEDURE:\"SCOTT\".\"P_INSERT\" created with compilation warnings
ORA-39082: Object type ALTER_PROCEDURE:\"SCOTT\".\"P_INSERT_T\" created with compilation warnings
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job \"SYSTEM\".\"SYS_IMPORT_FULL_01\" completed with 2 error(s) at 13:20:33
[oracle@psd ~]$ sqlplus scott/scott
SQL*Plus: Release 11.2.0.3.0 Production on Tue Mar 17 13:20:47 2015
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from t1;
ID
----------
1
2
3
4
5
6
7
8
9
10
10 rows selected.
SQL> select * from t2;
ID
----------
1
2
3
4
5
6
7
8
9
10
10 rows selected.

用户误删恢复完成。

ITPub博客

oracle删除用户后的恢复测试

最新文章