共計 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
正文完
