SQL> alter database backup controlfile to '/home/oracle/con.ctl'; #備份控制檔案
Database altered.
#删除後會記錄到目前的控制檔案中,恢複的時候不可以使用目前的控制檔案
SQL> drop tablespace tbs10 including contents and datafiles;
SQL> drop tablespace tbs11 including contents and datafiles;
SQL> select file_id,file_name from dba_data_files;
FILE_ID FILE_NAME
---------- ------------------------------------------------------------------------------------------
4 +DATA/orcl/datafile/users.272.1033601261
3 +DATA/orcl/datafile/undotbs1.271.1033601261
2 +DATA/orcl/datafile/sysaux.270.1033601259
1 +DATA/orcl/datafile/system.269.1033601257
5 +DATA/orcl/datafile/example.277.1033601505
6 +DATA/orcl/datafile/undotbs2.278.1033601957
10 +DATA/orcl/datafile/gg.293.1036275463
11 +DATA/orcl/datafile/ogg_2020.259.1033961107
8 rows selected.
SQL> drop tablespace gg including contents and datafiles;
Tablespace dropped.
SQL> select file_id,file_name from dba_data_files;
FILE_ID FILE_NAME
---------- ------------------------------------------------------------------------------------------
4 +DATA/orcl/datafile/users.272.1033601261
3 +DATA/orcl/datafile/undotbs1.271.1033601261
2 +DATA/orcl/datafile/sysaux.270.1033601259
1 +DATA/orcl/datafile/system.269.1033601257
5 +DATA/orcl/datafile/example.277.1033601505
6 +DATA/orcl/datafile/undotbs2.278.1033601957
11 +DATA/orcl/datafile/ogg_2020.259.1033961107
7 rows selected.
SQL>
[oracle@rac01 ~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Sat Mar 28 22:42:56 2020
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN>
RMAN>
RMAN> startup nomount
Oracle instance started
Total System Global Area 835104768 bytes
Fixed Size 2217952 bytes
Variable Size 809502752 bytes
Database Buffers 20971520 bytes
Redo Buffers 2412544 bytes
RMAN> restore controlfile from '/home/oracle/con.ctl'; #利用備份的控制檔案進行恢複。
Starting restore at 2020/03/28 22:44:04
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=33 instance=orcl1 device type=DISK
channel ORA_DISK_1: copied control file copy
output file name=+DATA/orcl/controlfile/current.273.1033601477
Finished restore at 2020/03/28 22:44:09
RMAN> restore database;
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> restore database;
Starting restore at 2020/03/28 22:44:52
Starting implicit crosscheck backup at 2020/03/28 22:44:52
allocated channel: ORA_DISK_1
Crosschecked 7 objects
Finished implicit crosscheck backup at 2020/03/28 22:44:56
Starting implicit crosscheck copy at 2020/03/28 22:44:56
using channel ORA_DISK_1
Finished implicit crosscheck copy at 2020/03/28 22:44:56
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 00001 to +DATA/orcl/datafile/system.269.1033601257
channel ORA_DISK_1: restoring datafile 00002 to +DATA/orcl/datafile/sysaux.270.1033601259
channel ORA_DISK_1: restoring datafile 00003 to +DATA/orcl/datafile/undotbs1.271.1033601261
channel ORA_DISK_1: restoring datafile 00004 to +DATA/orcl/datafile/users.272.1033601261
channel ORA_DISK_1: restoring datafile 00005 to +DATA/orcl/datafile/example.277.1033601505
channel ORA_DISK_1: restoring datafile 00006 to +DATA/orcl/datafile/undotbs2.278.1033601957
channel ORA_DISK_1: restoring datafile 00008 to +DATA/orcl/datafile/tbs10.294.1036253653
channel ORA_DISK_1: restoring datafile 00009 to +DATA/orcl/datafile/tbs11.283.1036253831
channel ORA_DISK_1: restoring datafile 00010 to +DATA/orcl/datafile/gg.293.1036275463
channel ORA_DISK_1: restoring datafile 00011 to +DATA/orcl/datafile/ogg_2020.259.1033961107
channel ORA_DISK_1: reading from backup piece +DATA/orcl/backupset/2020_03_28/nnndf0_tag20200328t222242_0.265.1036275763
channel ORA_DISK_1: piece handle=+DATA/orcl/backupset/2020_03_28/nnndf0_tag20200328t222242_0.265.1036275763 tag=TAG20200328T222242
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:25
Finished restore at 2020/03/28 22:47:27
RMAN> recover database
#通過查詢alert.log日志得到drop tablespace 。。。語句的時間
RMAN> recover database until time "to_date('2020-03-28 22:39:43','yyyy-mm-dd hh24:mi:ss')";
Starting recover at 2020/03/28 22:49:30
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 53 is already on disk as file +ARCH/orcl/archivelog/2020_03_28/thread_1_seq_53.312.1036276683
archived log for thread 1 with sequence 54 is already on disk as file +DATA/orcl/onlinelog/group_2.275.1033601485
archived log for thread 1 with sequence 55 is already on disk as file +DATA/orcl/onlinelog/group_1.274.1033601481
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=52
channel ORA_DISK_1: reading from backup piece +DATA/orcl/backupset/2020_03_28/annnf0_tag20200328t222450_0.256.1036275891
channel ORA_DISK_1: piece handle=+DATA/orcl/backupset/2020_03_28/annnf0_tag20200328t222450_0.256.1036275891 tag=TAG20200328T222450
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=+ARCH/orcl/archivelog/2020_03_28/thread_1_seq_52.313.1036277389 thread=1 sequence=52
archived log file name=+ARCH/orcl/archivelog/2020_03_28/thread_1_seq_53.312.1036276683 thread=1 sequence=53
archived log file name=+DATA/orcl/onlinelog/group_2.275.1033601485 thread=1 sequence=54
archived log file name=+DATA/orcl/onlinelog/group_1.274.1033601481 thread=1 sequence=55
media recovery complete, elapsed time: 00:00:03
Finished recover at 2020/03/28 22:49:55
RMAN> sql 'alter database open resetlogs';
sql statement: alter database open resetlogs
RMAN>
#恢複後檢視表空間資料庫
SQL> select file_id,file_name from dba_data_files;
FILE_ID FILE_NAME
---------- ------------------------------------------------------------------------------------------
1 +DATA/orcl/datafile/system.269.1033601257
2 +DATA/orcl/datafile/sysaux.270.1033601259
3 +DATA/orcl/datafile/undotbs1.271.1033601261
4 +DATA/orcl/datafile/users.272.1033601261
5 +DATA/orcl/datafile/example.277.1033601505
6 +DATA/orcl/datafile/undotbs2.278.1033601957
8 +DATA/orcl/datafile/tbs10.283.1036277119
9 +DATA/orcl/datafile/tbs11.294.1036277119
10 +DATA/orcl/datafile/gg.293.1036277103
11 +DATA/orcl/datafile/ogg_2020.259.1033961107
10 rows selected.
SQL>