RMAN的表級和表分區級恢複應用場景：
1、You need to recover a very small number of tables to a particular point in time. In this situation, TSPITR is not the most effective solution because it moves all the objects in the tablespace to a specified point in time.
2、You need to recover tables that have been logically corrupted or have been dropped and purged.
3、Flashback Table is not possible because the desired point-in-time is older than available undo.
4、You want to recover data that is lost after a DDL operation modified the structure of tables. Using Flashback Table is not possible because a DDL was run on the tables between the desired point in time and the current time. Flashback Table cannot rewind tables through structural changes such as a truncate table operation.
譯文：
1、您需要将非常少量的表恢複到特定的時間點。在這種情況下，TSPITR 不是最有效的解決方案，因為它将表空間中的所有對象都移動到指定的時間點。
2、您需要恢複已被邏輯損壞或已被删除和清除的表。
3、Flashback Table 不可用，如undo 資料已經被覆寫。
4、恢複在DDL操作修改表結構之後丢失的資料。使用Flashback表是不可能的，因為在需要的時間點和目前時間之間的表上運作一個DDL。閃回表不能通過諸如截斷表操作之類的結構更改來倒表。
RMAN的表級和表分區級恢複限制：
1、Tables and table partitions belonging to SYS schema cannot be recovered.
2、Tables and table partitions from SYSTEM and SYSAUX tablespaces cannot be recovered.
3、Tables and table partitions on standby databases cannot be recovered.
4、Tables with named NOT NULL constraints cannot be recovered with the REMAP option.
RMAN的表級和表分區級恢複前提：
1、The target database must be in read-write mode.
2、The target database must be in ARCHIVELOG mode.
3、You must have RMAN backups of the tables or table partitions as they existed at the point in time to which you want recover these objects.
4、To recover single table partitions, the COMPATIBLE initialization parameter for target database must be set to 11.1.0 or higher.
恢複方法：
1、SCN
2、Time
3、Sequence number
RMAN從備份中自動處理恢複表或者表分區時的步驟：
1、Determines which backup contains the tables or table partitions that need to be recovered, based on the point in time specified for the recovery.
2、Determines if there is sufficient space on the target host to create the auxiliary instance that will be used during the table or partition recovery process.
If the required space is not available, then RMAN displays an error and exits the recovery operation.
3、Creates an auxiliary database and recovers the specified tables or table partitions, until the specified point in time, into this auxiliary database.
You can specify the location to which the recovered data files are stored in the auxiliary database.
4、Creates a Data Pump export dump file that contains the recovered tables or table partitions.
You can specify the name and the location of the export dump file used to store the metadata of the recovered tables or table partitions.
5、(Optional) Imports the Data Pump export dump file into the target instance.
You can choose not to import the export dump file that contains the recovered tables or table partitions into the target database. If you do not import the export dump file as part of the recovery process, you must manually import it later using the Data Pump Import utility.
6、(Optional) Renames the recovered tables or table partitions in the target database.
You can also import recovered objects into a tablespace or schema that is different from the one in which they originally existed.
譯：
1.确定哪些備份包含需要恢複的表或表分區，根據指定的時間來進行恢複。
2.确定目标主機上是否有足夠的空間來建立将在表或分區恢複過程中使用的輔助執行個體。 如果需要的空間不足，那麼RMAN會報錯并退出恢複操作。
3.建立一個輔助資料庫并恢複指定的表或表分區，并根據指定的時間來恢複指定的表或表分區到輔助資料庫中。 可以指定用于存儲已恢複表或表分區的中繼資料的導出轉儲檔案的名稱和位置。
4.建立一個資料泵導出轉儲檔案，其中包含已恢複的表或表分區。可以指定用于存儲已恢複表或表分區的中繼資料的導出轉儲檔案的名稱和位置。
5. （可選操作）将上一步生産的資料泵檔案導入到目标執行個體中。您可以選擇不導入包含已恢複的表或表分區到目标資料庫的導出轉儲檔案。如果您不導入導出轉儲檔案作為恢複過程的一部分，那麼您必須在稍後使用 impdp 手工導入。
6. （可選操作）在目标資料庫中rename 恢複表或表分區。
PDB操作流程：
-- 準備測試環境
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB01 READ WRITE NO
4 PDB02 READ WRITE NO
SQL> alter session set container=pdb01;
Session altered.
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
BBB
6 rows selected.
-- 建立測試使用者
PDB01
SQL> create user andy identified by andy default tablespace bbb;
User created.
SQL> grant dba to andy;
Grant succeeded.
-- 建立測試表：
SQL> conn andy/[email protected]:1521/pdb01
Connected.
SQL> create table andy(id int);
Table created.
SQL> insert into andy values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> conn / as sysdba
SQL> alter system switch logfile;
System altered.
SQL> /
-- RMAN 備份CDB
--使用如下指令備份CDB的組建：ROOT,SEED,PDBS：
[oracle@12c ~]$ rman target /
RMAN> backup database plus archivelog;
Finished Control File and SPFILE Autobackup at 21-MAY-17
說明： 關于 Oracle 12c 多租戶 CDB 與 PDB 備份 請參考 ->http://blog.csdn.net/zhang123456456/article/details/71540927
-- 恢複資料
drop andy purge 表，然後執行恢複操作：
SQL> conn andy/[email protected]:1521/pdb01
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
2088202
SQL> drop table andy purge;
Table dropped.
SQL> select * from andy;
ERROR at line 1:
ORA-00942: table or view does not exist
-- 建立輔助目錄
[oracle@12c ~]$ mkdir -p /tmp/oracle/recover
[oracle@12c ~]$ mkdir -p /tmp/recover/dumpfiles
-- 恢複時，cdb 與 pdb 都是Open read writer 狀态。
3 PDB01 READ WRITE NO
-- 恢複指令
[oracle@12c ~]$ rman target /
RMAN>
run{
RECOVER TABLE andy.andy of pluggable database pdb01
UNTIL SCN 2088202
AUXILIARY DESTINATION '/home/oracle/tmp/oracle/recover'
datapump destination '/home/oracle/tmp/recover/dumpfiles';
}
補充：恢複表不支援公共使用者，開始作者使用的是公共使用者做實驗，報錯如下，也沒有很明顯的提示，後換本地使用者沒有這類報錯。
RMAN>recover table c##andy.andy_recover_t of pluggable database pdb01
until scn 2060046
auxiliary destination '/home/oracle/tmp/oracle/recover'
datapump destination '/home/oracle/tmp/recover/dumpfiles';
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "datapump": expecting one of: "advise, allocate, alter, analyze, associate statistics, audit, backup, begin, @, call, catalog, change, comment, commit, configure, connect, convert, copy, create, create catalog, create global, create script, create virtual, crosscheck, declare, delete, delete from, describe, describe catalog, disassociate statistics, drop, drop catalog, drop database, duplicate, exit, explain plan, flashback, flashback table, grant, grant catalog, grant register, host, import, insert, list, lock, merge, mount, noaudit, open, print, purge, quit, recover, register, release, rename, repair, replace, report, "
RMAN-01007: at line 1 column 1 file: standard input
-- 恢複檢視
ID
----------
1 >恢複成功
恢複過程:還原system,undo,sysaux表空間,然後read only資料庫,然後重新開機資料庫還原表所在表空間,然後expdp導出表,根據需要決定是否導入表到原PDB資料庫中，最後删除輔助資料庫。 整個過程對原PDB沒有影響。
補充：恢複過程監控
[oracle@12c dumpfiles]$ cd /home/oracle/tmp/oracle/recover
[oracle@12c recover]$ ll
total 8
drwxr-x---. 6 oracle oinstall 4096 May 21 18:26 ANDYCDB
drwxr-x---. 4 oracle oinstall 4096 May 21 18:35 PCAS_PITR_PDB01_ANDYCDB
[oracle@12c recover]$ cd /home/oracle/tmp/recover/dumpfiles
[oracle@12c dumpfiles]$ ll
total 164
-rw-r-----. 1 oracle oinstall 167936 May 21 19:12 tspitr_fgxA_79856.dmp
[root@12c ~]# ps -ef|grep smon
oracle 3838 1 0 17:45 ? 00:00:00 ora_smon_andycdb
oracle 5769 1 0 18:58 ? 00:00:00 ora_smon_fgxA
root 5941 3772 0 19:03 pts/3 00:00:00 grep smon
說明：輔助執行個體有啟動執行個體程序fgxA
恢複過程日志如下：
RMAN> run{
}2> 3> 4> 5> 6>
Starting recover at 21-MAY-17
using channel ORA_DISK_1
RMAN-05026: warning: presuming following set of tablespaces applies to specified point-in-time
List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace PDB01:SYSTEM
Tablespace UNDOTBS1
Tablespace PDB01:UNDOTBS1
Creating automatic instance, with SID='fgxA'
initialization parameters used for automatic instance:
db_name=ANDYCDB
db_unique_name=fgxA_pitr_pdb01_ANDYCDB
compatible=12.2.0
db_block_size=8192
db_files=200
diagnostic_dest=/home/oracle/app/oracle
_system_trig_enabled=FALSE
sga_target=692M
processes=200
db_create_file_dest=/home/oracle/tmp/oracle/recover
log_archive_dest_1='location=/home/oracle/tmp/oracle/recover'
enable_pluggable_database=true
_clone_one_pdb_recovery=true
max_string_size=EXTENDED
#No auxiliary parameter file used
starting up automatic instance ANDYCDB
Oracle instance started
Total System Global Area 725614592 bytes
Fixed Size 8797008 bytes
Variable Size 205522096 bytes
Database Buffers 507510784 bytes
Redo Buffers 3784704 bytes
Automatic instance created
contents of Memory Script:
{
# set requested point in time
set until scn 2088202;
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log
sql 'alter system archive log current';
executing Memory Script
executing command: SET until clause
Staring restore at 21-MAY-17
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=35 device type=DISK
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/app/oracle/product/12.2.0/dbhome_1/dbs/c-4182839949-20170521-00
channel ORA_AUX_DISK_1: piece handle=/home/oracle/app/oracle/product/12.2.0/dbhome_1/dbs/c-4182839949-20170521-00 tag=TAG20170521T041813
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/home/oracle/tmp/oracle/recover/ANDYCDB/controlfile/o1_mf_dl2wpytg_.ctl
Finished restore at 21-MAY-17
sql statement: alter database mount clone database
sql statement: alter system archive log current
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile 1 to new;
set newname for clone datafile 9 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 11 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 10 to new;
set newname for clone tempfile 1 to new;
set newname for clone tempfile 3 to new;
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 1, 9, 4, 11, 3, 10;
switch clone datafile all;
executing command: SET NEWNAME
renamed tempfile 1 to /home/oracle/tmp/oracle/recover/ANDYCDB/datafile/o1_mf_temp_%u_.tmp in control file
renamed tempfile 3 to /home/oracle/tmp/oracle/recover/ANDYCDB/4F44590EB7B74390E0531018DB0A1976/datafile/o1_mf_temp_%u_.tmp in control file
Starting restore at 21-MAY-17
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /home/oracle/tmp/oracle/recover/ANDYCDB/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /home/oracle/tmp/oracle/recover/ANDYCDB/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /home/oracle/tmp/oracle/recover/ANDYCDB/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/app/oracle/product/12.2.0/dbhome_1/dbs/0ss4p2c8_1_1
channel ORA_AUX_DISK_1: piece handle=/home/oracle/app/oracle/product/12.2.0/dbhome_1/dbs/0ss4p2c8_1_1 tag=TAG20170521T041359
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:38
channel ORA_AUX_DISK_1: restoring datafile 00009 to /home/oracle/tmp/oracle/recover/ANDYCDB/4F44590EB7B74390E0531018DB0A1976/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00011 to /home/oracle/tmp/oracle/recover/ANDYCDB/4F44590EB7B74390E0531018DB0A1976/datafile/o1_mf_undotbs1_%u_.dbf
/ANDYCDB/4F44590EB7B74390E0531018DB0A1976/datafile/o1_mf_sysaux_%u_.dbf
2.2.0/dbhome_1/dbs/0ts4p2eu_1_1
1/dbs/0ts4p2eu_1_1 tag=TAG20170521T041359
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:58
datafile 1 switched to datafile copy
ver/ANDYCDB/datafile/o1_mf_system_dl2wqg9o_.dbf
datafile 9 switched to datafile copy
ver/ANDYCDB/4F44590EB7B74390E0531018DB0A1976/datafile/o1_mf_system_dl2wtl6g_.dbf
datafile 4 switched to datafile copy
ver/ANDYCDB/datafile/o1_mf_undotbs1_dl2wqgcc_.dbf
datafile 11 switched to datafile copy
ver/ANDYCDB/4F44590EB7B74390E0531018DB0A1976/datafile/o1_mf_undotbs1_dl2wtlf0_.dbf
datafile 3 switched to datafile copy
ver/ANDYCDB/datafile/o1_mf_sysaux_dl2wqgc0_.dbf
datafile 10 switched to datafile copy
ver/ANDYCDB/4F44590EB7B74390E0531018DB0A1976/datafile/o1_mf_sysaux_dl2wtl06_.db
# online the datafiles restored or switched
sql clone "alter database datafile 1 online";
sql clone 'PDB01' "alter database datafile
9 online";
sql clone "alter database datafile 4 online";
11 online";
sql clone "alter database datafile 3 online";
10 online";
# recover and open database read only
"UNDOTBS1", "SYSAUX", "PDB01":"SYSAUX";
sql clone 'alter database open read only';
sql statement: alter database datafile 1 online
sql statement: alter database datafile 9 online
sql statement: alter database datafile 4 online
sql statement: alter database datafile 11 online
sql statement: alter database datafile 3 online
sql statement: alter database datafile 10 online
starting media recovery
app/oracle/product/12.2.0/dbhome_1/dbs/arch1_20_943753232.dbf
app/oracle/product/12.2.0/dbhome_1/dbs/arch1_21_943753232.dbf
_943753232.dbf thread=1 sequence=20
_943753232.dbf thread=1 sequence=21
media recovery complete, elapsed time: 00:01:32
Finished recover at 21-MAY-17
sql statement: alter database open read only
sql clone 'alter pluggable database PDB01 open read only';
sql statement: alter pluggable database PDB01 open read only
sql clone "create spfile from memory";
shutdown clone immediate;
startup clone nomount;
sql clone "alter system set control_files =
nt=
''RMAN set'' scope=spfile";
# mount database
sql statement: create spfile from memory
database closed
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
r/ANDYCDB/controlfile/o1_mf_dl2wpytg_.ctl'' comment= ''RMAN set'' scope=spfile
set newname for datafile 13 to new;
set newname for datafile 14 to new;
set newname for datafile 15 to new;
restore clone datafile 13, 14, 15;
f
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:16
datafile 13 switched to datafile copy
x50h8_.dbf
datafile 14 switched to datafile copy
x50l4_.dbf
datafile 15 switched to datafile copy
x500q_.dbf
13 online";
14 online";
15 online";
# recover and open resetlogs
TBS1", "PDB01":"UNDOTBS1", "SYSAUX", "PDB01":"SYSAUX" delete archivelog;
alter clone database open resetlogs;
sql statement: alter database datafile 13 online
sql statement: alter database datafile 14 online
sql statement: alter database datafile 15 online
media recovery complete, elapsed time: 00:00:18
database opened
sql clone 'alter pluggable database PDB01 open';
sql statement: alter pluggable database PDB01 open
# create directory for datapump import
sql 'PDB01' "create or replace directory
TSPITR_DIROBJ_DPDIR as ''
/home/oracle/tmp/recover/dumpfiles''";
# create directory for datapump export
sql clone 'PDB01' "create or replace directory
p/recover/dumpfiles''
Performing export of tables...
EXPDP> Starting "SYS"."TSPITR_EXP_fgxA_Fvnl":
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
rows
EXPDP> Master table "SYS"."TSPITR_EXP_fgxA_Fvnl" successfully loaded/unloaded
*****
EXPDP> Dump file set for SYS.TSPITR_EXP_fgxA_Fvnl is:
EXPDP> /home/oracle/tmp/recover/dumpfiles/tspitr_fgxA_79856.dmp
12:59 2017 elapsed 0 00:02:24
Export completed
# shutdown clone before import
shutdown clone abort
Performing import of tables...
IMPDP> Master table "SYS"."TSPITR_IMP_fgxA_txhb" successfully loaded/unloaded
IMPDP> Starting "SYS"."TSPITR_IMP_fgxA_txhb":
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
16:32 2017 elapsed 0 00:01:06
Import completed
Removing automatic instance
Automatic instance removed
31018DB0A1976/datafile/o1_mf_temp_dl2x08jv_.tmp deleted
_dl2wzlwf_.tmp deleted
inelog/o1_mf_3_dl2x6vbp_.log deleted
inelog/o1_mf_2_dl2x6gt1_.log deleted
inelog/o1_mf_1_dl2x6gt1_.log deleted
4590EB7B74390E0531018DB0A1976/datafile/o1_mf_bbb_dl2x500q_.dbf deleted
4590EB7B74390E0531018DB0A1976/datafile/o1_mf_bbb_dl2x50l4_.dbf deleted
4590EB7B74390E0531018DB0A1976/datafile/o1_mf_bbb_dl2x50h8_.dbf deleted
31018DB0A1976/datafile/o1_mf_sysaux_dl2wtl06_.dbf deleted
ux_dl2wqgc0_.dbf deleted
31018DB0A1976/datafile/o1_mf_undotbs1_dl2wtlf0_.dbf deleted
tbs1_dl2wqgcc_.dbf deleted
31018DB0A1976/datafile/o1_mf_system_dl2wtl6g_.dbf deleted
em_dl2wqg9o_.dbf deleted
l2wpytg_.ctl deleted
auxiliary instance file tspitr_fgxA_79856.dmp deleted
本文轉自 張沖andy 部落格園部落格，原文連結： http://www.cnblogs.com/andy6/p/6884320.html ，如需轉載請自行聯系原作者
![Windows下配置Apache的SSL服務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)