Oracle 12C 新特性之恢复表

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.
-- 建立测试用户
SQL> show con_name
CON_NAME
------------------------------
PDB01
SQL> create user andy identified by andy default tablespace bbb;
User created.
SQL> grant dba to andy;
Grant succeeded.

-- 创建测试表：
SQL> conn andy/andy@10.219.24.16: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
Connected.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
-- 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/andy@10.219.24.16: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 状态。
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
-- 恢复命令
[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-00571: ===========================================================
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

-- 恢复查看
SQL> select * from andy;
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{
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';
}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
contents of Memory Script:
{
# set requested point in time
set until scn 2088202;
# 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 Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
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: starting datafile backup set restore
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: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:38
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
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: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:58
Finished restore at 21-MAY-17
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
contents of Memory Script:
{
# set requested point in time
set until scn 2088202;
# 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";
sql clone 'PDB01' "alter database datafile
11 online";
sql clone "alter database datafile 3 online";
sql clone 'PDB01' "alter database datafile
10 online";
# recover and open database read only
"UNDOTBS1", "SYSAUX", "PDB01":"SYSAUX";
sql clone 'alter database open read only';
}
executing Memory Script
executing command: SET until clause
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 recover at 21-MAY-17
using channel ORA_AUX_DISK_1
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
contents of Memory Script:
{
sql clone 'alter pluggable database PDB01 open read only';
}
executing Memory Script
sql statement: alter pluggable database PDB01 open read only
contents of Memory Script:
{
sql clone "create spfile from memory";
shutdown clone immediate;
startup clone nomount;
sql clone "alter system set control_files =
nt=
''RMAN set'' scope=spfile";
shutdown clone immediate;
startup clone nomount;
# mount database
sql clone 'alter database mount clone database';
}
executing Memory Script
sql statement: create spfile from memory
database closed
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
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
r/ANDYCDB/controlfile/o1_mf_dl2wpytg_.ctl'' comment= ''RMAN set'' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
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
sql statement: alter database mount clone database
contents of Memory Script:
{
# set requested point in time
set until scn 2088202;
# set destinations for recovery set and auxiliary set datafiles
set newname for datafile 13 to new;
set newname for datafile 14 to new;
set newname for datafile 15 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 13, 14, 15;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting 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: specifying datafile(s) to restore from backup set
f
f
f
2.2.0/dbhome_1/dbs/0ts4p2eu_1_1
1/dbs/0ts4p2eu_1_1 tag=TAG20170521T041359
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:16
Finished restore at 21-MAY-17

datafile 13 switched to datafile copy
x50h8_.dbf
datafile 14 switched to datafile copy
x50l4_.dbf
datafile 15 switched to datafile copy
x500q_.dbf
contents of Memory Script:
{
# set requested point in time
set until scn 2088202;
# online the datafiles restored or switched
sql clone 'PDB01' "alter database datafile
13 online";
sql clone 'PDB01' "alter database datafile
14 online";
sql clone 'PDB01' "alter database datafile
15 online";
# recover and open resetlogs
TBS1", "PDB01":"UNDOTBS1", "SYSAUX", "PDB01":"SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script
executing command: SET until clause
sql statement: alter database datafile 13 online
sql statement: alter database datafile 14 online
sql statement: alter database datafile 15 online
Starting recover at 21-MAY-17
using channel ORA_AUX_DISK_1
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:00:18
Finished recover at 21-MAY-17
database opened
contents of Memory Script:
{
sql clone 'alter pluggable database PDB01 open';
}
executing Memory Script
sql statement: alter pluggable database PDB01 open
contents of Memory Script:
{
# 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
TSPITR_DIROBJ_DPDIR as ''
/home/oracle/tmp/recover/dumpfiles''";
}
executing Memory Script
p/recover/dumpfiles''
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
contents of Memory Script:
{
# shutdown clone before import
shutdown clone abort
}
executing Memory Script
Oracle instance shut down
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
rows
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
Finished recover at 21-MAY-17

——————————————————————————-

大致过程：

Creating automatic instance, with SID='ajrg' -----这里开始创建auxiliary instance

Starting restore at 17-APR-17 -----先restore database

Starting recover at 17-APR-17 ----再recover

# create directory for datapump import -----使用数据泵导出相关数据

Performing import of tables... -------向target database导入数据

Removing automatic instance

Automatic instance removed ------删除环境

eg: 恢复表分区且不直接导入生产环境，手动导入
#### SH.SALES:SALES_Q3_1998 , SH用户 SALES表 SALES_Q3_1998 分区
#### notableimport 生成的dump_file不直接导入生产库

run
{
recover table SH.SALES:SALES_Q3_1998, SH.SALES:SALES_Q4_1998
until time '2018-01-11 03:11:06'
auxiliary destination '/ora_xtts/recover'
datapump destination '/ora_xtts/dump'
dump file 't_emp_recvr.dmp'
notableimport
remap table SH.SALES:SALES_Q3_1998:JY.SALES_Q3_1998,SH.SALES:SALES_Q4_1998:JY.SALES_Q4_1998
remap tablespace 'USERS':'USERTBS';
}

impdp system/system directory=DATA_PUMP_DIR dumpfile=t_emp_recvr.dmp tables=JY.SALES_Q4_1998,JY.SALES_Q3_1998

posted on 2017-05-21 11:34 张冲andy 阅读(3918) 评论(0) 编辑收藏举报

刷新页面返回顶部

张冲andy