共计 5238 个字符，预计需要花费 14 分钟才能阅读完成。
行业资讯
数据库
关系型数据库
Oracle 12C 如何使用 RMAN 将 Non-CDB 中分表的多个分区恢复到新用户方案中
这篇文章主要介绍了 Oracle 12C 如何使用 RMAN 将 Non-CDB 中分表的多个分区恢复到新用户方案中，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让丸趣 TV 小编带着大家一起了解一下。
Oracle 12C 使用 RMAN 将 Non-CDB 中分表的多个分区恢复到新用户方案中的操作如下
1. 对整个 Non-CDB(orcl) 生成 RMAN 备份
RMAN backup as compressed backupset database format +data/backup/%d_%I_%U_%t plus archivelog format arc_%d_%T_%U
Starting backup at 10-JAN-18
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=13 RECID=1 STAMP=964995986
input archived log thread=1 sequence=14 RECID=2 STAMP=965007422
input archived log thread=1 sequence=15 RECID=3 STAMP=965007493
input archived log thread=1 sequence=16 RECID=4 STAMP=965007542
input archived log thread=1 sequence=17 RECID=5 STAMP=965011311
input archived log thread=1 sequence=18 RECID=6 STAMP=965011687
channel ORA_DISK_1: starting piece 1 at 10-JAN-18
channel ORA_DISK_1: finished piece 1 at 10-JAN-18
piece handle=/u01/app/oracle/product/12.2.0/db/dbs/arc_ORCL_20180110_21so9q78_1_1 tag=TAG20180110T024807 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 10-JAN-18
Starting backup at 10-JAN-18
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00004 name=+DATA/orcl/datafile/users01.dbf
input datafile file number=00001 name=+DATA/orcl/datafile/system01.dbf
input datafile file number=00002 name=+DATA/orcl/datafile/sysaux01.dbf
input datafile file number=00003 name=+DATA/orcl/datafile/undotbs01.dbf
input datafile file number=00005 name=+DATA/orcl/datafile/usertbs01.dbf
channel ORA_DISK_1: starting piece 1 at 10-JAN-18
channel ORA_DISK_1: finished piece 1 at 10-JAN-18
piece handle=+DATA/backup/orcl_1492772871_22so9q7p_1_1_965011705 tag=TAG20180110T024824 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
Finished backup at 10-JAN-18
Starting backup at 10-JAN-18
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=19 RECID=7 STAMP=965011751
channel ORA_DISK_1: starting piece 1 at 10-JAN-18
channel ORA_DISK_1: finished piece 1 at 10-JAN-18
piece handle=/u01/app/oracle/product/12.2.0/db/dbs/arc_ORCL_20180110_23so9q98_1_1 tag=TAG20180110T024912 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 10-JAN-18
Starting Control File and SPFILE Autobackup at 10-JAN-18
piece handle=+DATA/backup/c-1492772871-20180110-01 comment=NONE
Finished Control File and SPFILE Autobackup at 10-JAN-182. 在删除表 sh.sales 的 SALES_Q3_1998 与 SALES_Q4_1998 分区中的数据之前记录当前时间, 在执行恢复时它们被用来指定恢复时间点
SQL select count(*) from sales partition(SALES_Q3_1998);
COUNT(*)
----------
50515
1 row selected.
SQL select count(*) from sales partition(SALES_Q4_1998);
COUNT(*)
----------
48874
1 row selected.
SQL select sysdate from dual;
SYSDATE
-------------------
2018-01-11 03:11:06
1 row selected.
SQL select current_scn from v$database;
CURRENT_SCN
-----------
525382
SQL delete from sales partition(SALES_Q3_1998);
50515 rows deleted.
SQL delete from sales partition(SALES_Q4_1998);
48874 rows deleted.
SQL commit;
Commit complete.
SQL select count(*) from sales partition(SALES_Q3_1998);
COUNT(*)
----------
0
1 row selected.
SQL select count(*) from sales partition(SALES_Q4_1998);
COUNT(*)
----------
0
1 row selected.3. 启动 RMAN 并使用有 sysbacup 或 sysdba 权限的用户连接到目标数据库
[oracle@jytest3 ~]$ export NLS_DATE_FORMAT= yyyy-mm-dd hh34:mi:ss
[oracle@jytest3 ~]$ rman target/
Recovery Manager: Release 12.2.0.1.0 - Production on Wed Jan 10 02:22:13 2018
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1492772871)4. 通过使用 recover table 命令将要被恢复的表或表分区恢复到指定的时间点。使用 auxiliary destination 子句（/ora_xtts/recover 辅助数据文件存储目录)与 until scn 来指定恢复时间点, 并且在 recover 命令中使用子句 dump file 与 datapump destination，指定包含被恢复表或表分区的导出 dump 文件的文件名 (t_emp_recvr.dmp) 与存储位置(/ora_xtts/dump)。使用 remap table 子句将分区 SH.SALES:SALES_Q3_1998 恢复成表 JY.SALES_Q3_1998，分区 SH.SALES:SALES_Q4_1998 恢复成表 JY.SALES_Q4_1998 使用 remap tablespace 子句将 sh 用户方案中的表分区从 user 表空间恢复到 usertbs 表空间中
RMAN run
2 3 recover table SH.SALES:SALES_Q3_1998, SH.SALES:SALES_Q4_1998
4 until time 2018-01-11 03:11:06
5 auxiliary destination /ora_xtts/recover
6 datapump destination /ora_xtts/dump
7 dump file t_emp_recvr.dmp
8 remap table SH.SALES:SALES_Q3_1998:JY.SALES_Q3_1998,SH.SALES:SALES_Q4_1998:JY.SALES_Q4_1998
9 remap tablespace USERS : USERTBS
10 }
Starting recover at 2018-01-11 03:21:12
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 UNDOTBS1
Creating automatic instance, with SID= xDyx
initialization parameters used for automatic instance:
db_name=ORCL
db_unique_name=xDyx_pitr_ORCL
compatible=12.2.0
db_block_size=8192
db_files=200
diagnostic_dest=/u01/app/oracle
_system_trig_enabled=FALSE
sga_target=1024M
processes=120
db_create_file_dest=/ora_xtts/recover
log_archive_dest_1= location=/ora_xtts/recover
#No auxiliary parameter file used
正文完
