drop table 并且回收站已经被情况了，如何恢复？
前提：数据库开规档，并且删除之前的归档没有被删掉。
思路：rman备份、创建pfile、 创建一个辅助实例恢复之后，再导入到原来实例;
1. 如果开了闪回，可闪回
2. 利用备份加archivelog 进行不完全恢复(该方法同样适用于truncate的恢复)
++++Session 1
SQL> conn zw/zw
Connected.
SQL> create table t1 as select * from dba_tables;
Table created.
SQL> select count(*) from t1;
COUNT(*)
----------
1204
+++++Session 2
run
{
allocate channel c1 type disk;
allocate channel c2 type disk;
backup database format '/oradata/backup/full_%d_%T_%s_%p';
sql 'alter system archive log current';
sql 'alter system archive log current';
sql 'alter system archive log current';
backup archivelog all format '/oradata/backup/arch_%d_%T_%s_%p';
backup current controlfile format '/oradata/backup/ctl_%d_%T_%s_%p';
}
+++++drop table
SQL> show user
USER is "ZW"
SQL> alter system switch logfile;
System altered.
SQL> drop table t1 purge;
Table dropped.
SQL> alter system checkpoint;
System altered.
3.创建一个pfile
SQL> conn /as sysdba
Connected.
SQL> create pfile='/tmp/zw.ora' from spfile;
File created.
SQL>
4. 修改pfile
node1new.__db_cache_size=335544320
node1new.__java_pool_size=4194304
node1new.__large_pool_size=4194304
node1new.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
node1new.__pga_aggregate_target=339738624
node1new.__sga_target=503316480
node1new.__shared_io_pool_size=0
node1new.__shared_pool_size=150994944
node1new.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/node1new/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/oradata/node1new/control01.ctl','/u01/app/oracle/fast_recovery_area/node1new/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='/oradata/node1new','/oradata/node2'
*.db_name='node1'
*.db_unique_name='node1new'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=5218762752
*.diagnostic_dest='/u01/app/oracle'
*.java_pool_size=0
*.log_archive_dest_1='location=/oradata/arch1'
*.memory_target=842006528
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
5. 创建各种dump目录
-----------------------------------------------------
[oracle@node1 tmp]$ export ORACLE_SID=node1new
[oracle@node1 tmp]$ echo $ORACLE_SID
node1new
11g要创建这些目录
rm -rf $ORACLE_BASE/admin/$ORACLE_SID
mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/adump
mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/dpdump
mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/pfile
mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/scripts
chmod -R 750 $ORACLE_BASE/admin
rm -rf $ORACLE_BASE/diag/rdbms/$ORACLE_SID
mkdir -p $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/alert
mkdir -p $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/cdump
mkdir -p $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/hm
mkdir -p $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/incident
mkdir -p $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/incpkg
mkdir -p $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/ir
mkdir -p $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/lck
mkdir -p $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/metadata
mkdir -p $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/stage
mkdir -p $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/sweep
mkdir -p $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace
chmod -R 750 $ORACLE_BASE/diag/rdbms/$ORACLE_SID
6.恢复controlfile
RMAN> startup nomount pfile='/tmp/pfile.ora';
Oracle instance started
Total System Global Area 167772160 bytes
Fixed Size 1272600 bytes
Variable Size 62915816 bytes
Database Buffers 100663296 bytes
Redo Buffers 2920448 bytes
RMAN> restore controlfile from '/oradata/backup/ctl_NODE1_20160123_18_1';
Starting restore at 23-JAN-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/oradata/node1new/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/node1new/control02.ctl
Finished restore at 23-JAN-16
SQL> alter database mount;
Database altered.
---restore datafile
RMAN>
run
{
set newname for datafile '/oradata/node1/system01.dbf' to '/oradata/node1new/system01.dbf';
set newname for datafile '/oradata/node1/sysaux01.dbf' to '/oradata/node1new/sysaux01.dbf';
set newname for datafile '/oradata/node1/undotbs01.dbf' to '/oradata/node1new/undotbs01.dbf';
set newname for datafile '/oradata/node1/users01.dbf' to '/oradata/node1new/users01.dbf';
restore database ;
switch datafile all;
}
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 23-JAN-16
Starting implicit crosscheck backup at 23-JAN-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
Crosschecked 7 objects
Finished implicit crosscheck backup at 23-JAN-16
Starting implicit crosscheck copy at 23-JAN-16
using channel ORA_DISK_1
Finished implicit crosscheck copy at 23-JAN-16
searching for all files in the recovery area
cataloging files...
no files cataloged
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 00002 to /oradata/node1new/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /oradata/node1new/users01.dbf
channel ORA_DISK_1: reading from backup piece /oradata/backup/full_NODE1_20160123_11_1
channel ORA_DISK_1: piece handle=/oradata/backup/full_NODE1_20160123_11_1 tag=TAG20160123T140220
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
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 /oradata/node1new/system01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /oradata/node1new/undotbs01.dbf
channel ORA_DISK_1: reading from backup piece /oradata/backup/full_NODE1_20160123_10_1
channel ORA_DISK_1: piece handle=/oradata/backup/full_NODE1_20160123_10_1 tag=TAG20160123T140220
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 23-JAN-16
datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=901898007 file name=/oradata/node1new/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=901898007 file name=/oradata/node1new/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=901898007 file name=/oradata/node1new/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=901898007 file name=/oradata/node1new/users01.dbf
7.拷贝归档，恢复到最新的时间点
[oracle@node1 arch]$ cp *.dbf /oradata/arch1/
[oracle@node1 node1new]$ env|grep SID
ORACLE_SID=node1new
[ora10g@killdb ~]$ rman target /
Recovery Manager: Release 10.2.0.5.0 - Production on Tue Aug 6 00:40:26 2013
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: ROGER (DBID=2525832133, not open)
8.注册归档日志
RMAN> catalog start with '/oradata/arch1';
using target database control file instead of recovery catalog
searching for all files that match the pattern /oradata/arch1
List of Files Unknown to the Database
=====================================
File Name: /oradata/arch1/1_27_901846980.dbf
File Name: /oradata/arch1/1_29_901846980.dbf
File Name: /oradata/arch1/1_37_901846980.dbf
File Name: /oradata/arch1/1_16_901846980.dbf
File Name: /oradata/arch1/1_32_901846980.dbf
File Name: /oradata/arch1/1_26_901846980.dbf
File Name: /oradata/arch1/1_24_901846980.dbf
File Name: /oradata/arch1/1_25_901846980.dbf
File Name: /oradata/arch1/1_33_901846980.dbf
File Name: /oradata/arch1/1_38_901846980.dbf
File Name: /oradata/arch1/1_23_901846980.dbf
File Name: /oradata/arch1/1_20_901846980.dbf
File Name: /oradata/arch1/1_31_901846980.dbf
File Name: /oradata/arch1/1_30_901846980.dbf
File Name: /oradata/arch1/1_21_901846980.dbf
File Name: /oradata/arch1/1_34_901846980.dbf
File Name: /oradata/arch1/1_36_901846980.dbf
File Name: /oradata/arch1/1_22_901846980.dbf
File Name: /oradata/arch1/1_28_901846980.dbf
File Name: /oradata/arch1/1_18_901846980.dbf
File Name: /oradata/arch1/1_35_901846980.dbf
File Name: /oradata/arch1/1_19_901846980.dbf
File Name: /oradata/arch1/1_17_901846980.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: /oradata/arch1/1_27_901846980.dbf
File Name: /oradata/arch1/1_29_901846980.dbf
File Name: /oradata/arch1/1_37_901846980.dbf
File Name: /oradata/arch1/1_16_901846980.dbf
File Name: /oradata/arch1/1_32_901846980.dbf
File Name: /oradata/arch1/1_26_901846980.dbf
File Name: /oradata/arch1/1_24_901846980.dbf
File Name: /oradata/arch1/1_25_901846980.dbf
File Name: /oradata/arch1/1_33_901846980.dbf
File Name: /oradata/arch1/1_38_901846980.dbf
File Name: /oradata/arch1/1_23_901846980.dbf
File Name: /oradata/arch1/1_20_901846980.dbf
File Name: /oradata/arch1/1_31_901846980.dbf
File Name: /oradata/arch1/1_30_901846980.dbf
File Name: /oradata/arch1/1_21_901846980.dbf
File Name: /oradata/arch1/1_34_901846980.dbf
File Name: /oradata/arch1/1_36_901846980.dbf
File Name: /oradata/arch1/1_22_901846980.dbf
File Name: /oradata/arch1/1_28_901846980.dbf
File Name: /oradata/arch1/1_18_901846980.dbf
File Name: /oradata/arch1/1_35_901846980.dbf
File Name: /oradata/arch1/1_19_901846980.dbf
File Name: /oradata/arch1/1_17_901846980.dbf
9.怎么找到这个点？
col SEQUENCE# format a40;
col name format a70;
SQL> col first_change# clear;
SQL> col next_change# clear;
SQL> select SEQUENCE#,FIRST_CHANGE#,NEXT_CHANGE#,name from v$archived_log where name like '/oradata/arch%' order by 2;
SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# NAME
-----------------------------------------------------------------
16 215824 215997 /oradata/arch/1_16_901846980.dbf
17 215997 216209 /oradata/arch/1_17_901846980.dbf
18 216209 216218 /oradata/arch/1_18_901846980.dbf
19 216218 216227 /oradata/arch/1_19_901846980.dbf
20 216227 216235 /oradata/arch/1_20_901846980.dbf
21 216235 216833 /oradata/arch/1_21_901846980.dbf
22 216833 216930 /oradata/arch/1_22_901846980.dbf
23 216930 225589 /oradata/arch/1_23_901846980.dbf
24 225589 226527 /oradata/arch/1_24_901846980.dbf
25 226527 226530 /oradata/arch/1_25_901846980.dbf
26 226530 226533 /oradata/arch/1_26_901846980.dbf
27 226533 226536 /oradata/arch/1_27_901846980.dbf
28 226536 226539 /oradata/arch/1_28_901846980.dbf
29 226539 226542 /oradata/arch/1_29_901846980.dbf
30 226542 226562 /oradata/arch/1_30_901846980.dbf
31 226562 226860 /oradata/arch/1_31_901846980.dbf
32 226860 226881 /oradata/arch/1_32_901846980.dbf
33 226881 249787 /oradata/arch/1_33_901846980.dbf
34 249787 249883 /oradata/arch/1_34_901846980.dbf
35 249883 249892 /oradata/arch/1_35_901846980.dbf
36 249892 249901 /oradata/arch/1_36_901846980.dbf
37 249901 249909 /oradata/arch/1_37_901846980.dbf
38 249909 249939 /oradata/arch/1_38_901846980.dbf
SQL>
RMAN> run {
set until scn 249939;
sql 'alter database datafile 1,2,3,4 online';
recover database skip forever tablespace users01;
}
recovery过程中可以临时将某个暂无法恢复出来的tablespace skip掉，先恢复其它部分，
待recovery database完成并open database后，再recover这个被skip掉的表空间;
executing command: SET until clause
sql statement: alter database datafile 1,2,3,4 online
Starting recover at 23-JAN-16
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 34 is already on disk as file /oradata/arch1/1_34_901846980.dbf
archived log for thread 1 with sequence 35 is already on disk as file /oradata/arch1/1_35_901846980.dbf
archived log for thread 1 with sequence 36 is already on disk as file /oradata/arch1/1_36_901846980.dbf
archived log for thread 1 with sequence 37 is already on disk as file /oradata/node1/redo01.log
archived log for thread 1 with sequence 38 is already on disk as file /oradata/node1/redo02.log
archived log file name=/oradata/arch1/1_34_901846980.dbf thread=1 sequence=34
archived log file name=/oradata/arch1/1_35_901846980.dbf thread=1 sequence=35
archived log file name=/oradata/arch1/1_36_901846980.dbf thread=1 sequence=36
archived log file name=/oradata/node1/redo01.log thread=1 sequence=37
archived log file name=/oradata/node1/redo02.log thread=1 sequence=38
media recovery complete, elapsed time: 00:00:00
Finished recover at 23-JAN-16
10. 查看logfile路径
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/oradata/node1/redo01.log
/oradata/node1/redo02.log
/oradata/node1/redo03.log
11.修改logfile路径
SQL> alter database rename file '/oradata/node1/redo01.log' to '/oradata/node1new/redo01.log';
Database altered.
SQL> alter database rename file '/oradata/node1/redo02.log' to '/oradata/node1new/redo02.log';
Database altered.
SQL> alter database rename file '/oradata/node1/redo03.log' to '/oradata/node1new/redo03.log';
Database altered.
12.查看并修改temp的路径
SQL> select name from v$tempfile;
NAME
-----------------------------------
/oradata/node1/temp01.dbf
SQL> alter database rename file '/oradata/node1/temp01.dbf' to '/oradata/node1new/temp01.dbf';
Database altered.
13.打开数据库
SQL> alter database open read only;
Database altered.
SQL> conn zw/zw
Connected.
SQL> select count(*) from t1;
COUNT(*)
----------
1204
SQL> exit
可以看到t1表已经恢复出来了
14.导出恢复出来的表数据
[oracle@node1 /]$ exp zw/zw file=/home/oracle/exp_t1.dmp tables=t1 direct=y
15. 查看之前实例的表，可以看到没有t1表
SQL> select count(*) from t1;
select count(*) from t1
*
ERROR at line 1:
ORA-00942: table or view does not exist
16.导入数据
[oracle@node1 arch1]$ imp zw/zw file=/home/oracle/exp_t1.dmp tables=t1;
SQL> conn zw/zw
Connected.
SQL> select count(*) from t1;
COUNT(*)
----------
1204
到此为止drop的表已经恢复成功！
