ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > RMAN tablespace point-in-time recovery (TSPITR)

RMAN tablespace point-in-time recovery (TSPITR)

Oracle 作者：lsy721 时间：2018-06-22 15:56:34 0 删除 编辑
TSPITR 简单的说就是基于时间点的表空间恢复，它可以把一个或多个表空间恢复到过去某个时间点而不影响剩余的表空间或是数据库对象。
RMAN TSPITR主要应用场景如下：
(1)恢复逻辑数据库到某一个时间点，而在数据库物理结构上与其它表空间时间点不同
(2)恢复因使用DDL语言造成的数据丢失
(3)使用purge删除的table 
(4)表的逻辑损坏
(5)恢复删除的表空间，即使不使用catalog，RMAN 一样可以使用TSPITR

在实验中使用的相关技术名词
name exxxasdfasdf
Target instance contains the tablespace to be recovered to the target time    
target time point in time or SCN of the  tablespace after TSPITR completes
auxiliary destination an optional disk location that RMAN uses to temporarily store the auxiliary set files,only used with an RMAN-managed auxiliary instance.
auxiliary instance a database instance  used in the recovery process to perform the work of recovery
recovery set datafiles in the tablespaces that  you  intend to recover
auxiliary set data files requried  fo TSPITR that are not part of the recovery set.
The auxiliary set typically includes: 1.the SYSTEM or SYSAUX tablespaces 2.data file containing rollback or undo segments from the target database instance. 
3. temporary tablespace 4.control file from source database 5.archived redo logs  that must be restored to recover the auxiliary instance to specified point in time.
6. online redo logs of the auxiliary intance.

一、创建实验环境

点击(此处)折叠或打开

  1. SQL> col name for a60
  2. SQL>  select name from v$datafile;
  4. NAME
  5. ------------------------------------------------------------
  6. /oracle/database/oradata/mhxy/system01.dbf
  7. /oracle/database/oradata/mhxy/sysaux01.dbf
  8. /oracle/database/oradata/mhxy/undotbs01.dbf
  9. /oracle/database/oradata/mhxy/users01.dbf
  11. SQL> create tablespace test datafile '/oracle/database/oradata/mhxy/test.dbf' size 10m autoextend off extent management local;
  13. Tablespace created.
  15. SQL> select name from v$datafile;
  17. NAME
  18. ------------------------------------------------------------
  19. /oracle/database/oradata/mhxy/system01.dbf
  20. /oracle/database/oradata/mhxy/sysaux01.dbf
  21. /oracle/database/oradata/mhxy/undotbs01.dbf
  22. /oracle/database/oradata/mhxy/users01.dbf
  23. /oracle/database/oradata/mhxy/test.dbf
  25. SQL> create user test identified by test default tablespace test temporary tablespace temp;
  27. User created.

  29. SQL>  grant dba to test;
  31. Grant succeeded.
  33. SQL> conn test/test
  34. Connected.
  35. SQL> create table tt as select * from user_objects;
  37. Table created.

  39. SQL> create table test.t1 as select * from dba_objects;
  40. create table test.t1 as select * from dba_objects
  41. *
  42. ERROR at line 1:
  43. ORA-01652: unable to extend temp segment by 128 in tablespace TEST

  45. SQL> alter database datafile '/oracle/database/oradata/mhxy/test.dbf' resize 50m;
  47. Database altered.
  49. SQL> create table test.t1 as select * from dba_objects;
  51. Table created.
  53. SQL> conn / as sysdba
  54. Connected.
  55. SQL> select count(*) from test.t1;
  57. COUNT(*)
  58. ----------
  59. 71904
  61. SQL> exit

  63. #通过RMAN 备份数据库#
  64. RMAN> backup as backupset database plus archivelog;

    Starting backup at 2016-07-26 04:45:02
    current log archived
    using target database control file instead of recovery catalog
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=150 device type=DISK
    channel ORA_DISK_1: starting archived log backup set
    channel ORA_DISK_1: specifying archived log(s) in backup set
    input archived log thread=1 sequence=5 RECID=1 STAMP=918187812
    input archived log thread=1 sequence=6 RECID=2 STAMP=918189905
    channel ORA_DISK_1: starting piece 1 at 2016-07-26 04:45:06
    channel ORA_DISK_1: finished piece 1 at 2016-07-26 04:45:13
    piece handle=/oracle/database/flash_recovery_area/MHXY/backupset/2016_07_26/o1_mf_annnn_TAG20160726T044506_csdyllfs_.bkp tag=TAG20160726T044506 comment=NONE
    channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
    Finished backup at 2016-07-26 04:45:13

    Starting backup at 2016-07-26 04:45:13
    using channel ORA_DISK_1
    channel ORA_DISK_1: starting full datafile backup set
    channel ORA_DISK_1: specifying datafile(s) in backup set
    input datafile file number=00001 name=/oracle/database/oradata/mhxy/system01.dbf
    input datafile file number=00002 name=/oracle/database/oradata/mhxy/sysaux01.dbf
    input datafile file number=00003 name=/oracle/database/oradata/mhxy/undotbs01.dbf
    input datafile file number=00005 name=/oracle/database/oradata/mhxy/test.dbf
    input datafile file number=00004 name=/oracle/database/oradata/mhxy/users01.dbf
    channel ORA_DISK_1: starting piece 1 at 2016-07-26 04:45:14
    channel ORA_DISK_1: finished piece 1 at 2016-07-26 04:46:51
    piece handle=/oracle/database/flash_recovery_area/MHXY/backupset/2016_07_26/o1_mf_nnndf_TAG20160726T044513_csdyltqt_.bkp tag=TAG20160726T044513 comment=NONE
    channel ORA_DISK_1: backup set complete, elapsed time: 00:01:37
    channel ORA_DISK_1: starting full datafile backup set
    channel ORA_DISK_1: specifying datafile(s) in backup set
    including current control file in backup set
    including current SPFILE in backup set
    channel ORA_DISK_1: starting piece 1 at 2016-07-26 04:46:55
    channel ORA_DISK_1: finished piece 1 at 2016-07-26 04:46:56
    piece handle=/oracle/database/flash_recovery_area/MHXY/backupset/2016_07_26/o1_mf_ncsnf_TAG20160726T044513_csdyoz8x_.bkp tag=TAG20160726T044513 comment=NONE
    channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
    Finished backup at 2016-07-26 04:46:56

    Starting backup at 2016-07-26 04:46:56
    current log archived
    using channel ORA_DISK_1
    channel ORA_DISK_1: starting archived log backup set
    channel ORA_DISK_1: specifying archived log(s) in backup set
    input archived log thread=1 sequence=7 RECID=3 STAMP=918190016
    channel ORA_DISK_1: starting piece 1 at 2016-07-26 04:46:57
    channel ORA_DISK_1: finished piece 1 at 2016-07-26 04:46:58
    piece handle=/oracle/database/flash_recovery_area/MHXY/backupset/2016_07_26/o1_mf_annnn_TAG20160726T044656_csdyp11c_.bkp tag=TAG20160726T044656 comment=NONE
    channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
    Finished backup at 2016-07-26 04:46:58

    RMAN> exit

二、确定需要恢复的时间点（point-in-time），这个时间在实验环境中可以通过自己判断，然后drop tablespace test。但在实际环境中，需要通过logmintor确定，下一篇会阐述下logmintor的使用。

点击(此处)折叠或打开

  1. SQL> set linesize 300
  2. SQL>
  3. SQL> select to_char(scn_to_timestamp(current_scn),'yyyy-mm-dd hh24:mi:ss'),current_scn from v$database;
  5. TO_CHAR(SCN_TO_TIMESTAMP(CURRENT_SCN), CURRENT_SCN
  6. -------------------------------------- -----------
  7. 2016-07-26 04:48:56 1008558
  9. SQL> drop tablespace test including contents and datafiles;
  11. Tablespace dropped.

  13. SQL> select tablespace_name,status from dba_tablespaces;
  15. TABLESPACE_NAME                                              STATUS
    ------------------------------------------------------------ ------------------
    SYSTEM                                                       ONLINE
    SYSAUX                                                       ONLINE
    UNDOTBS1                                                     ONLINE
    TEMP                                                         ONLINE
    USERS                                                        ONLINE
  18. SQL> col name for a50
  19. SQL> select name from v$datafile;   
  21. NAME
  22. --------------------------------------------------
  23. /oracle/database/oradata/mhxy/system01.dbf
  24. /oracle/database/oradata/mhxy/sysaux01.dbf
  25. /oracle/database/oradata/mhxy/undotbs01.dbf
  26. /oracle/database/oradata/mhxy/users01.dbf
  28. SQL> host ll -lrt /oracle/database/oradata/mhxy/
  29. /bin/bash: ll: command not found
  31. SQL> host ls -lrt /oracle/database/oradata/mhxy/
  32. total 1476888
  33. -rw-r----- 1 oracle oinstall 30416896 Jul 26 04:23 temp01.dbf
  34. -rw-r----- 1 oracle oinstall 52429312 Jul 26 04:45 redo03.log
  35. -rw-r----- 1 oracle oinstall 702554112 Jul 26 04:45 system01.dbf
  36. -rw-r----- 1 oracle oinstall 99622912 Jul 26 04:45 undotbs01.dbf
  37. -rw-r----- 1 oracle oinstall 534781952 Jul 26 04:45 sysaux01.dbf
  38. -rw-r----- 1 oracle oinstall 5251072 Jul 26 04:45 users01.dbf
  39. -rw-r----- 1 oracle oinstall 52429312 Jul 26 04:46 redo01.log
  40. -rw-r----- 1 oracle oinstall 52429312 Jul 26 04:50 redo02.log
  41. -rw-r----- 1 oracle oinstall 9748480 Jul 26 04:50 control01.ctl
  43. SQL> exit
  44. #
  45. # 通过视图和操作系统命令发现，表空间test 数据文件test.dbf以及在操作系统目录下面的test.dbf都已经不存在了
三、通过RMAN 进行TSPITR操作，在执行过程很简单，但是在其中也遇到的很多问题。1. auxiliary destination 目录需要在操作系统中通mkdir创建  2. 表空间的名称需要用引号括起来并行字母大写 ，如"TEST"
点击(此处)折叠或打开
  1. RMAN> recover tablespace "test" until time '2016-07-26 04:48:56' auxiliary destination '/oracle/auxiliary';
  2. # 未创建auxiliary destination，表空间名称未大写
  3. Starting recover at 2016-07-26 04:54:30
  4. using target database control file instead of recovery catalog
  5. allocated channel: ORA_DISK_1
  6. channel ORA_DISK_1: SID=138 device type=DISK
  7. RMAN-05026: WARNING: presuming following set of tablespaces applies to specified point-in-time
  9. List of tablespaces expected to have UNDO segments
  10. Tablespace SYSTEM
  11. Tablespace UNDOTBS1
  13. Creating automatic instance, with SID='knwx'
  15. initialization parameters used for automatic instance:
  16. db_name=MHXY
  17. db_unique_name=knwx_tspitr_MHXY
  18. compatible=11.2.0.0.0
  19. db_block_size=8192
  20. db_files=200
  21. sga_target=280M
  22. processes=50
  23. db_create_file_dest=/oracle/auxiliary
  24. log_archive_dest_1='location=/oracle/auxiliary'
  25. #No auxiliary parameter file used
  28. starting up automatic instance MHXY
  30. RMAN-00571: ===========================================================
  31. RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
  32. RMAN-00571: ===========================================================
  33. RMAN-03002: failure of recover command at 07/26/2016 04:54:40
  34. RMAN-04014: startup failed: ORA-01261: Parameter db_create_file_dest destination string cannot be translated
  35. ORA-01262: Stat failed on a file destination directory
  36. Linux-x86_64 Error: 2: No such file or directory
  38. RMAN> exit
  41. Recovery Manager complete.
  42. [oracle@mhxy164 ~]$ mkdir -p /oracle/auxiliary
  43. [oracle@mhxy164 ~]$ ll !$
  44. ll /oracle/auxiliary
  45. total 0
  46. [oracle@mhxy164 ~]$ echo $？
  47. $？
  48. [oracle@mhxy164 ~]$ rman target /
  50. Recovery Manager: Release 11.2.0.1.0 - Production on Tue Jul 26 04:55:14 2016
  52. Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
  54. connected to target database: MHXY (DBID=4238569140)
  56. RMAN> recover tablespace "test" until time '2016-07-26 04:48:56' auxiliary destination '/oracle/auxiliary';
  57. #表空间名称未大写
  58. Starting recover at 2016-07-26 04:55:19
  59. using target database control file instead of recovery catalog
  60. allocated channel: ORA_DISK_1
  61. channel ORA_DISK_1: SID=26 device type=DISK
  62. RMAN-05026: WARNING: presuming following set of tablespaces applies to specified point-in-time
  64. List of tablespaces expected to have UNDO segments
  65. Tablespace SYSTEM
  66. Tablespace UNDOTBS1
  68. Creating automatic instance, with SID='cmmh'
  70. initialization parameters used for automatic instance:
  71. db_name=MHXY
  72. db_unique_name=cmmh_tspitr_MHXY
  73. compatible=11.2.0.0.0
  74. db_block_size=8192
  75. db_files=200
  76. sga_target=280M
  77. processes=50
  78. db_create_file_dest=/oracle/auxiliary
  79. log_archive_dest_1='location=/oracle/auxiliary'
  80. #No auxiliary parameter file used
  83. starting up automatic instance MHXY
  85. Oracle instance started
  87. Total System Global Area 292278272 bytes
  89. Fixed Size 2212736 bytes
  90. Variable Size 100666496 bytes
  91. Database Buffers 184549376 bytes
  92. Redo Buffers 4849664 bytes
  93. Automatic instance created
  96. List of tablespaces that have been dropped from the target database:
  97. Tablespace test
  99. contents of Memory Script:
  100. {
  101. # set requested point in time
  102. set until time "2016-07-26 04:48:56";
  103. # restore the controlfile
  104. restore clone controlfile;
  105. # mount the controlfile
  106. sql clone 'alter database mount clone database';
  107. # archive current online log
  108. sql 'alter system archive log current';
  109. # avoid unnecessary autobackups for structural changes during TSPITR
  110. sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
  111. }
  112. executing Memory Script
  114. executing command: SET until clause
  116. Starting restore at 2016-07-26 04:55:29
  117. allocated channel: ORA_AUX_DISK_1
  118. channel ORA_AUX_DISK_1: SID=59 device type=DISK
  120. channel ORA_AUX_DISK_1: starting datafile backup set restore
  121. channel ORA_AUX_DISK_1: restoring control file
  122. channel ORA_AUX_DISK_1: reading from backup piece /oracle/database/flash_recovery_area/MHXY/backupset/2016_07_26/o1_mf_ncsnf_TAG20160726T044513_csdyoz8x_.bkp
  123. channel ORA_AUX_DISK_1: piece handle=/oracle/database/flash_recovery_area/MHXY/backupset/2016_07_26/o1_mf_ncsnf_TAG20160726T044513_csdyoz8x_.bkp tag=TAG20160726T044513
  124. channel ORA_AUX_DISK_1: restored backup piece 1
  125. channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
  126. output file name=/oracle/auxiliary/MHXY/controlfile/o1_mf_csdz6245_.ctl
  127. Finished restore at 2016-07-26 04:55:31
  129. sql statement: alter database mount clone database
  131. sql statement: alter system archive log current
  133. sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;
  135. Removing automatic instance
  136. shutting down automatic instance
  137. database dismounted
  138. Oracle instance shut down
  139. Automatic instance removed
  140. auxiliary instance file /oracle/auxiliary/MHXY/controlfile/o1_mf_csdz6245_.ctl deleted
  141. RMAN-00571: ===========================================================
  142. RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
  143. RMAN-00571: ===========================================================
  144. RMAN-03002: failure of recover command at 07/26/2016 04:55:41
  145. RMAN-06136: ORACLE error from auxiliary database: RMAN-20202: Tablespace not found in the recovery catalog
  146. RMAN-06019: could not translate tablespace name "test"

  148. #正确的命令
  149. RMAN> recover tablespace "TEST" until time '2016-07-26 04:48:56' auxiliary destination '/oracle/auxiliary';
  151. Starting recover at 2016-07-26 04:57:30
  152. using channel ORA_DISK_1
  153. RMAN-05026: WARNING: presuming following set of tablespaces applies to specified point-in-time
  155. List of tablespaces expected to have UNDO segments
  156. Tablespace SYSTEM
  157. Tablespace UNDOTBS1
  159. Creating automatic instance, with SID='nAif'
  161. initialization parameters used for automatic instance:
  162. db_name=MHXY
  163. db_unique_name=nAif_tspitr_MHXY
  164. compatible=11.2.0.0.0
  165. db_block_size=8192
  166. db_files=200
  167. sga_target=280M
  168. processes=50
  169. db_create_file_dest=/oracle/auxiliary
  170. log_archive_dest_1='location=/oracle/auxiliary'
  171. #No auxiliary parameter file used
  174. starting up automatic instance MHXY
  176. Oracle instance started
  178. Total System Global Area 292278272 bytes
  180. Fixed Size 2212736 bytes
  181. Variable Size 100666496 bytes
  182. Database Buffers 184549376 bytes
  183. Redo Buffers 4849664 bytes
  184. Automatic instance created
  187. List of tablespaces that have been dropped from the target database:
  188. Tablespace TEST
  190. contents of Memory Script:
  191. {
  192. # set requested point in time
  193. set until time "2016-07-26 04:48:56";
  194. # restore the controlfile
  195. restore clone controlfile;
  196. # mount the controlfile
  197. sql clone 'alter database mount clone database';
  198. # archive current online log
  199. sql 'alter system archive log current';
  200. # avoid unnecessary autobackups for structural changes during TSPITR
  201. sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
  202. }
  203. executing Memory Script
  205. executing command: SET until clause
  207. Starting restore at 2016-07-26 04:57:40
  208. allocated channel: ORA_AUX_DISK_1
  209. channel ORA_AUX_DISK_1: SID=59 device type=DISK
  211. channel ORA_AUX_DISK_1: starting datafile backup set restore
  212. channel ORA_AUX_DISK_1: restoring control file
  213. channel ORA_AUX_DISK_1: reading from backup piece /oracle/database/flash_recovery_area/MHXY/backupset/2016_07_26/o1_mf_ncsnf_TAG20160726T044513_csdyoz8x_.bkp
  214. channel ORA_AUX_DISK_1: piece handle=/oracle/database/flash_recovery_area/MHXY/backupset/2016_07_26/o1_mf_ncsnf_TAG20160726T044513_csdyoz8x_.bkp tag=TAG20160726T044513
  215. channel ORA_AUX_DISK_1: restored backup piece 1
  216. channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
  217. output file name=/oracle/auxiliary/MHXY/controlfile/o1_mf_csdzb4pk_.ctl
  218. Finished restore at 2016-07-26 04:57:41
  220. sql statement: alter database mount clone database
  222. sql statement: alter system archive log current
  224. sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;
  226. contents of Memory Script:
  227. {
  228. # set requested point in time
  229. set until time "2016-07-26 04:48:56";
  230. # set destinations for recovery set and auxiliary set datafiles
  231. set newname for clone datafile 1 to new;
  232. set newname for clone datafile 3 to new;
  233. set newname for clone datafile 2 to new;
  234. set newname for clone tempfile 1 to new;
  235. set newname for datafile 5 to
  236. "/oracle/database/oradata/mhxy/test.dbf";
  237. # switch all tempfiles
  238. switch clone tempfile all;
  239. # restore the tablespaces in the recovery set and the auxiliary set
  240. restore clone datafile 1, 3, 2, 5;
  241. switch clone datafile all;
  242. }
  243. executing Memory Script
  245. executing command: SET until clause
  247. executing command: SET NEWNAME
  249. executing command: SET NEWNAME
  251. executing command: SET NEWNAME
  253. executing command: SET NEWNAME
  255. executing command: SET NEWNAME
  257. renamed tempfile 1 to /oracle/auxiliary/MHXY/datafile/o1_mf_temp_%u_.tmp in control file
  259. Starting restore at 2016-07-26 04:57:46
  260. using channel ORA_AUX_DISK_1
  262. channel ORA_AUX_DISK_1: starting datafile backup set restore
  263. channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
  264. channel ORA_AUX_DISK_1: restoring datafile 00001 to /oracle/auxiliary/MHXY/datafile/o1_mf_system_%u_.dbf
  265. channel ORA_AUX_DISK_1: restoring datafile 00003 to /oracle/auxiliary/MHXY/datafile/o1_mf_undotbs1_%u_.dbf
  266. channel ORA_AUX_DISK_1: restoring datafile 00002 to /oracle/auxiliary/MHXY/datafile/o1_mf_sysaux_%u_.dbf
  267. channel ORA_AUX_DISK_1: restoring datafile 00005 to /oracle/database/oradata/mhxy/test.dbf
  268. channel ORA_AUX_DISK_1: reading from backup piece /oracle/database/flash_recovery_area/MHXY/backupset/2016_07_26/o1_mf_nnndf_TAG20160726T044513_csdyltqt_.bkp
  269. channel ORA_AUX_DISK_1: piece handle=/oracle/database/flash_recovery_area/MHXY/backupset/2016_07_26/o1_mf_nnndf_TAG20160726T044513_csdyltqt_.bkp tag=TAG20160726T044513
  270. channel ORA_AUX_DISK_1: restored backup piece 1
  271. channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:05
  272. Finished restore at 2016-07-26 04:58:52
  274. datafile 1 switched to datafile copy
  275. input datafile copy RECID=4 STAMP=918190732 file name=/oracle/auxiliary/MHXY/datafile/o1_mf_system_csdzbc66_.dbf
  276. datafile 3 switched to datafile copy
  277. input datafile copy RECID=5 STAMP=918190732 file name=/oracle/auxiliary/MHXY/datafile/o1_mf_undotbs1_csdzbc7g_.dbf
  278. datafile 2 switched to datafile copy
  279. input datafile copy RECID=6 STAMP=918190732 file name=/oracle/auxiliary/MHXY/datafile/o1_mf_sysaux_csdzbc79_.dbf
  281. contents of Memory Script:
  282. {
  283. # set requested point in time
  284. set until time "2016-07-26 04:48:56";
  285. # online the datafiles restored or switched
  286. sql clone "alter database datafile 1 online";
  287. sql clone "alter database datafile 3 online";
  288. sql clone "alter database datafile 2 online";
  289. sql clone "alter database datafile 5 online";
  290. # recover and open resetlogs
  291. recover clone database tablespace "TEST", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;
  292. alter clone database open resetlogs;
  293. }
  294. executing Memory Script
  296. executing command: SET until clause
  298. sql statement: alter database datafile 1 online
  300. sql statement: alter database datafile 3 online
  302. sql statement: alter database datafile 2 online
  304. sql statement: alter database datafile 5 online
  306. Starting recover at 2016-07-26 04:58:52
  307. using channel ORA_AUX_DISK_1
  309. starting media recovery
  311. archived log for thread 1 with sequence 7 is already on disk as file /oracle/database/flash_recovery_area/MHXY/archivelog/2016_07_26/o1_mf_1_7_csdyp0pw_.arc
  312. archived log for thread 1 with sequence 8 is already on disk as file /oracle/database/flash_recovery_area/MHXY/archivelog/2016_07_26/o1_mf_1_8_csdz67oz_.arc
  313. archived log file name=/oracle/database/flash_recovery_area/MHXY/archivelog/2016_07_26/o1_mf_1_7_csdyp0pw_.arc thread=1 sequence=7
  314. archived log file name=/oracle/database/flash_recovery_area/MHXY/archivelog/2016_07_26/o1_mf_1_8_csdz67oz_.arc thread=1 sequence=8
  315. media recovery complete, elapsed time: 00:00:02
  316. Finished recover at 2016-07-26 04:58:55
  318. database opened
  320. contents of Memory Script:
  321. {
  322. # make read only the tablespace that will be exported
  323. sql clone 'alter tablespace "TEST" read only';
  324. # create directory for datapump import
  325. sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
  326. /oracle/auxiliary''";
  327. # create directory for datapump export
  328. sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
  329. /oracle/auxiliary''";
  330. }
  331. executing Memory Script
  333. sql statement: alter tablespace "TEST" read only
  335. sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/oracle/auxiliary''
  337. sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/oracle/auxiliary''
  339. Performing export of metadata...
  340. EXPDP> Starting "SYS"."TSPITR_EXP_nAif":
  341. EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
  342. EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
  343. EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
  344. EXPDP> Master table "SYS"."TSPITR_EXP_nAif" successfully loaded/unloaded
  345. EXPDP> ******************************************************************************
  346. EXPDP> Dump file set for SYS.TSPITR_EXP_nAif is:
  347. EXPDP> /oracle/auxiliary/tspitr_nAif_78214.dmp
  348. EXPDP> ******************************************************************************
  349. EXPDP> Datafiles required for transportable tablespace TEST:
  350. EXPDP> /oracle/database/oradata/mhxy/test.dbf
  351. EXPDP> Job "SYS"."TSPITR_EXP_nAif" successfully completed at 05:00:20
  352. Export completed
  355. contents of Memory Script:
  356. {
  357. # shutdown clone before import
  358. shutdown clone immediate
  359. }
  360. executing Memory Script
  362. database closed
  363. database dismounted
  364. Oracle instance shut down
  366. Performing import of metadata...
  367. IMPDP> Master table "SYS"."TSPITR_IMP_nAif" successfully loaded/unloaded
  368. IMPDP> Starting "SYS"."TSPITR_IMP_nAif":
  369. IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
  370. IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
  371. IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
  372. IMPDP> Job "SYS"."TSPITR_IMP_nAif" successfully completed at 05:00:57
  373. Import completed
  376. contents of Memory Script:
  377. {
  378. # make read write and offline the imported tablespaces
  379. sql 'alter tablespace "TEST" read write';
  380. sql 'alter tablespace "TEST" offline';
  381. # enable autobackups after TSPITR is finished
  382. sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
  383. }
  384. executing Memory Script
  386. sql statement: alter tablespace "TEST" read write
  388. sql statement: alter tablespace "TEST" offline
  390. sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;
  392. Removing automatic instance
  393. Automatic instance removed
  394. auxiliary instance file /oracle/auxiliary/MHXY/datafile/o1_mf_temp_csdzds8s_.tmp deleted
  395. auxiliary instance file /oracle/auxiliary/MHXY/onlinelog/o1_mf_3_csdzdnmk_.log deleted
  396. auxiliary instance file /oracle/auxiliary/MHXY/onlinelog/o1_mf_2_csdzdlfx_.log deleted
  397. auxiliary instance file /oracle/auxiliary/MHXY/onlinelog/o1_mf_1_csdzdhj7_.log deleted
  398. auxiliary instance file /oracle/auxiliary/MHXY/datafile/o1_mf_sysaux_csdzbc79_.dbf deleted
  399. auxiliary instance file /oracle/auxiliary/MHXY/datafile/o1_mf_undotbs1_csdzbc7g_.dbf deleted
  400. auxiliary instance file /oracle/auxiliary/MHXY/datafile/o1_mf_system_csdzbc66_.dbf deleted
  401. auxiliary instance file /oracle/auxiliary/MHXY/controlfile/o1_mf_csdzb4pk_.ctl deleted
  402. Finished recover at 2016-07-26 05:01:00
  403. RMAN>
四、在sqlplus中验证，发现通过TSPITR恢复的表空间状态是offline，需要alter 到online

点击(此处)折叠或打开

  1. SQL> col name for a60
  2. SQL> select name from v$datafile;
  4. NAME
  5. ------------------------------------------------------------
  6. /oracle/database/oradata/mhxy/system01.dbf
  7. /oracle/database/oradata/mhxy/sysaux01.dbf
  8. /oracle/database/oradata/mhxy/undotbs01.dbf
  9. /oracle/database/oradata/mhxy/users01.dbf
  10. /oracle/database/oradata/mhxy/test.dbf
  12. SQL> select tablespace_name,status from dba_tablespaces;
  14. TABLESPACE_NAME                                              STATUS
    ------------------------------------------------------------ ------------------
    SYSTEM                                                       ONLINE
    SYSAUX                                                       ONLINE
    UNDOTBS1                                                     ONLINE
    TEMP                                                         ONLINE
    USERS                                                        ONLINE
    TEST                                                         OFFLINE


  16. 6 rows selected.
  18. SQL> alter tablespace test online;
  20. Tablespace altered.
  22. SQL> select count(*) from test.t1;
  24. COUNT(*)
  25. ----------
  26. 71904
五、思考，在实际生产环境中需要坚持是否自包含对象，如下就会有自包含现象，需要删除索引等表空间TEST恢复后重新创建

点击(此处)折叠或打开

  1. SQL> create index idx1 on test.t1(owner) tablespace users;
  3. Index created.
  5. SQL> select * from SYS.TS_PITR_CHECK a WHERE a.ts1_name='TEST';
  6. OBJ1_OWNER    OBJ1_NAME   OBJ1_SUBNAME    OBJ1_TYPE    TS1_NAME   OBJ2_NAME  OBJ2_SUBNAME    OBJ2_TYPE    OBJ2_OWNER  TS2_NAME    CONSTRAINT_NAME  REASON
    ----------  -----------   -------------   ----------   ---------  ---------  ------------    ----------  -----------  ----------  ---------------  ----------
    TEST           T1                         BLE           TEST         IDX1         INDEX                    SYS           USERS                      Tables and associated indexes not fully
                                                                                                                                                        contained in the recovery set                                                           
     

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/24222399/viewspace-2156516/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： 【OMF】使用Oracle的OMF 特性
下一篇： 没有了~
请登录后发表评论 登录
全部评论
lsy721

注册时间：2014-03-31

  • 博文量
    26
  • 访问量
    34328

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593