开启辅助访问

Bo's Oracle Station

Bo's Oracle Station»社区 › SECOUG活动（活动结束图标变灰，但是欢迎继续发帖！） › SECOUG数据库管理SIG › 活动第19/20次（2018-05-19星期六上下午）
返回列表 发新帖
查看: 2380|回复: 0

活动第19/20次（2018-05-19星期六上下午）

[复制链接]
botang

1005

主题

1469

帖子

1万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
12012
发表于 2018-5-19 09:15:59 | 显示全部楼层 |阅读模式
做实验时，在bcpserver虚拟机的以下目录建选黑的文件：
Screenshot.png

文件内容：
  1. 192.168.0.11  192.168.0.4 192.168.0.8 192.168.0.10  192.168.0.87
复制代码
对自己的环境执行实验破坏，命令是：
  1. [root@station90 ~]# bcl --O11g05 6b
复制代码

删除表空间的恢复，如果不把控制文件restore，只是做了时间点恢复，必然出现uname000X的错误：
  1. RMAN> run {
  2. 2> sql "alter session set nls_Date_format=''YYYY-MM-DD:HH24:MI:SS''";
  3. 3> set until time '2018-05-19:19:44:46';
  4. 4> restore database;
  5. 5> recover database;
  6. 6> }

  8. using target database control file instead of recovery catalog
  9. sql statement: alter session set nls_Date_format=''YYYY-MM-DD:HH24:MI:SS''

  11. executing command: SET until clause

  13. Starting restore at 19-MAY-18
  14. allocated channel: ORA_DISK_1
  15. channel ORA_DISK_1: SID=137 device type=DISK
  16. allocated channel: ORA_DISK_2
  17. channel ORA_DISK_2: SID=12 device type=DISK
  18. allocated channel: ORA_DISK_3
  19. channel ORA_DISK_3: SID=140 device type=DISK
  20. allocated channel: ORA_DISK_4
  21. channel ORA_DISK_4: SID=13 device type=DISK
  22. allocated channel: ORA_DISK_5
  23. channel ORA_DISK_5: SID=141 device type=DISK
  24. allocated channel: ORA_DISK_6
  25. channel ORA_DISK_6: SID=14 device type=DISK
  26. allocated channel: ORA_DISK_7
  27. channel ORA_DISK_7: SID=142 device type=DISK
  28. allocated channel: ORA_DISK_8
  29. channel ORA_DISK_8: SID=15 device type=DISK
  30. flashing back control file to SCN 1115587

  32. RMAN-00571: ===========================================================
  33. RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
  34. RMAN-00571: ===========================================================
  35. RMAN-03002: failure of restore command at 05/19/2018 19:53:33
  36. RMAN-06085: must use SET NEWNAME command to restore datafile /u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00004
复制代码
restore point 以及它在数据库化身时间轴上的应用：

  1. select  * from v$flashback_database_stat;

  3. select  * from v$restore_point;

  5. create restore point rsp1;
复制代码
  1. [root@station90 ~]# ssh station87
  2. Last login: Sat May 19 23:30:16 2018 from station90.example.com
  3. [root@station87 ~]# su - oracle
  4. [oracle@station87 ~]$ sqlplus /nolog

  6. SQL*Plus: Release 11.2.0.1.0 Production on Sat May 19 23:55:54 2018

  8. Copyright (c) 1982, 2009, Oracle.  All rights reserved.

  10. SQL> exit
  11. [oracle@station87 ~]$ rman target /

  13. Recovery Manager: Release 11.2.0.1.0 - Production on Sat May 19 23:56:02 2018

  15. Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

  17. connected to target database: ORCL (DBID=1359978017)

  19. RMAN> list incarnation of database;

  21. using target database control file instead of recovery catalog

  23. List of Database Incarnations
  24. DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
  25. ------- ------- -------- ---------------- --- ---------- ----------
  26. 1       1       ORCL     1359978017       PARENT  1          13-AUG-09
  27. 2       2       ORCL     1359978017       PARENT  754488     22-NOV-13
  28. 3       3       ORCL     1359978017       PARENT  1108732    19-MAY-18
  29. 4       4       ORCL     1359978017       PARENT  1121631    19-MAY-18
  30. 5       5       ORCL     1359978017       ORPHAN  1144059    19-MAY-18
  31. 6       6       ORCL     1359978017       CURRENT 1144059    19-MAY-18

  33. RMAN> shutdown abort

  35. Oracle instance shut down

  37. RMAN> startup mount

  39. connected to target database (not started)
  40. Oracle instance started
  41. database mounted

  43. Total System Global Area     820236288 bytes

  45. Fixed Size                     1339628 bytes
  46. Variable Size                587206420 bytes
  47. Database Buffers             226492416 bytes
  48. Redo Buffers                   5197824 bytes

  50. RMAN> exit


  53. Recovery Manager complete.
  54. [oracle@station87 ~]$ sqlplus /nolog

  56. SQL*Plus: Release 11.2.0.1.0 Production on Sat May 19 23:57:20 2018

  58. Copyright (c) 1982, 2009, Oracle.  All rights reserved.

  60. SQL> flashback database to restore point rsp1;
  61. SP2-0640: Not connected
  62. SQL> conn / as sysdba
  63. Connected.
  64. SQL>  flashback database to restore point rsp1;

  66. Flashback complete.

  68. SQL> alter database open resetlogs;

  70. Database altered.

  72. SQL> exit
  73. Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
  74. With the Partitioning, Automatic Storage Management, OLAP, Data Mining
  75. and Real Application Testing options
  76. [oracle@station87 ~]$ rman target /

  78. Recovery Manager: Release 11.2.0.1.0 - Production on Sat May 19 23:58:17 2018

  80. Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

  82. connected to target database: ORCL (DBID=1359978017)

  84. RMAN> list incarnation of database;

  86. using target database control file instead of recovery catalog

  88. List of Database Incarnations
  89. DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
  90. ------- ------- -------- ---------------- --- ---------- ----------
  91. 1       1       ORCL     1359978017       PARENT  1          13-AUG-09
  92. 2       2       ORCL     1359978017       PARENT  754488     22-NOV-13
  93. 3       3       ORCL     1359978017       PARENT  1108732    19-MAY-18
  94. 4       4       ORCL     1359978017       PARENT  1121631    19-MAY-18
  95. 5       5       ORCL     1359978017       ORPHAN  1144059    19-MAY-18
  96. 6       6       ORCL     1359978017       PARENT  1144059    19-MAY-18
  97. 7       7       ORCL     1359978017       CURRENT 1144593    19-MAY-18

  99. RMAN> exit


  102. Recovery Manager complete.
  103. [oracle@station87 ~]$ sqlplus /nolog

  105. SQL*Plus: Release 11.2.0.1.0 Production on Sat May 19 23:59:21 2018

  107. Copyright (c) 1982, 2009, Oracle.  All rights reserved.

  109. SQL> conn / as sysdba
  110. Connected.
  111. SQL> alter database flashback off;

  113. Database altered.

  115. SQL> shutdown abort
  116. ORACLE instance shut down.
  117. SQL> startup mount
  118. ORACLE instance started.

  120. Total System Global Area  820236288 bytes
  121. Fixed Size                    1339628 bytes
  122. Variable Size                  587206420 bytes
  123. Database Buffers          226492416 bytes
  124. Redo Buffers                    5197824 bytes
  125. Database mounted.
  126. SQL> exit
  127. Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
  128. With the Partitioning, Automatic Storage Management, OLAP, Data Mining
  129. and Real Application Testing options
  130. [oracle@station87 ~]$ rman target /

  132. Recovery Manager: Release 11.2.0.1.0 - Production on Sun May 20 00:00:38 2018

  134. Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

  136. connected to target database: ORCL (DBID=1359978017, not open)

  138. RMAN> run {
  139. 2> set until restore point rsp1;
  140. 3> restore database;
  141. 4> recover database;
  142. 5> }

  144. executing command: SET until clause

  146. Starting restore at 20-MAY-18
  147. using target database control file instead of recovery catalog
  148. RMAN-00571: ===========================================================
  149. RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
  150. RMAN-00571: ===========================================================
  151. RMAN-03002: failure of restore command at 05/20/2018 00:01:01
  152. RMAN-20208: UNTIL CHANGE is before RESETLOGS change

  154. RMAN> list incarnation of database;


  157. List of Database Incarnations
  158. DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
  159. ------- ------- -------- ---------------- --- ---------- ----------
  160. 1       1       ORCL     1359978017       PARENT  1          13-AUG-09
  161. 2       2       ORCL     1359978017       PARENT  754488     22-NOV-13
  162. 3       3       ORCL     1359978017       PARENT  1108732    19-MAY-18
  163. 4       4       ORCL     1359978017       PARENT  1121631    19-MAY-18
  164. 5       5       ORCL     1359978017       ORPHAN  1144059    19-MAY-18
  165. 6       6       ORCL     1359978017       PARENT  1144059    19-MAY-18
  166. 7       7       ORCL     1359978017       CURRENT 1144593    19-MAY-18

  168. RMAN> reset database to incarnation 6;

  170. database reset to incarnation 6

  172. RMAN> list incarnation of database;


  175. List of Database Incarnations
  176. DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
  177. ------- ------- -------- ---------------- --- ---------- ----------
  178. 1       1       ORCL     1359978017       PARENT  1          13-AUG-09
  179. 2       2       ORCL     1359978017       PARENT  754488     22-NOV-13
  180. 3       3       ORCL     1359978017       PARENT  1108732    19-MAY-18
  181. 4       4       ORCL     1359978017       PARENT  1121631    19-MAY-18
  182. 5       5       ORCL     1359978017       ORPHAN  1144059    19-MAY-18
  183. 6       6       ORCL     1359978017       CURRENT 1144059    19-MAY-18
  184. 7       7       ORCL     1359978017       ORPHAN  1144593    19-MAY-18

  186. RMAN> run {
  187. 2> set until restore point rsp1;
  188. 3> restore database;
  189. 4> recover database;
  190. 5> }

  192. executing command: SET until clause

  194. Starting restore at 20-MAY-18
  195. allocated channel: ORA_DISK_1
  196. channel ORA_DISK_1: SID=14 device type=DISK
  197. allocated channel: ORA_DISK_2
  198. channel ORA_DISK_2: SID=136 device type=DISK
  199. allocated channel: ORA_DISK_3
  200. channel ORA_DISK_3: SID=15 device type=DISK
  201. allocated channel: ORA_DISK_4
  202. channel ORA_DISK_4: SID=137 device type=DISK
  203. allocated channel: ORA_DISK_5
  204. channel ORA_DISK_5: SID=16 device type=DISK
  205. allocated channel: ORA_DISK_6
  206. channel ORA_DISK_6: SID=138 device type=DISK
  207. allocated channel: ORA_DISK_7
  208. channel ORA_DISK_7: SID=17 device type=DISK
  209. allocated channel: ORA_DISK_8
  210. channel ORA_DISK_8: SID=139 device type=DISK

  212. channel ORA_DISK_1: starting datafile backup set restore
  213. channel ORA_DISK_1: specifying datafile(s) to restore from backup set
  214. channel ORA_DISK_1: restoring datafile 00003 to +DATA/orcl/datafile/undotbs1.259.976558179
  215. channel ORA_DISK_1: reading from backup piece +FRA/orcl/backupset/2018_05_19/nnndf0_tag20180519t191110_0.301.976561875
  216. channel ORA_DISK_2: starting datafile backup set restore
  217. channel ORA_DISK_2: specifying datafile(s) to restore from backup set
  218. channel ORA_DISK_2: restoring datafile 00004 to +DATA/orcl/datafile/users.265.976575785
  219. channel ORA_DISK_2: reading from backup piece +FRA/orcl/backupset/2018_05_19/nnndf0_tag20180519t191110_0.298.976561883
  220. channel ORA_DISK_3: starting datafile backup set restore
  221. channel ORA_DISK_3: specifying datafile(s) to restore from backup set
  222. channel ORA_DISK_3: restoring datafile 00005 to +DATA/orcl/datafile/example.257.976558179
  223. channel ORA_DISK_3: reading from backup piece +FRA/orcl/backupset/2018_05_19/nnndf0_tag20180519t191110_0.299.976561877
  224. channel ORA_DISK_4: starting datafile backup set restore
  225. channel ORA_DISK_4: specifying datafile(s) to restore from backup set
  226. channel ORA_DISK_4: restoring datafile 00002 to +DATA/orcl/datafile/sysaux.258.976558179
  227. channel ORA_DISK_4: reading from backup piece +FRA/orcl/backupset/2018_05_19/nnndf0_tag20180519t191110_0.302.976561873
  228. channel ORA_DISK_5: starting datafile backup set restore
  229. channel ORA_DISK_5: specifying datafile(s) to restore from backup set
  230. channel ORA_DISK_5: restoring datafile 00001 to +DATA/orcl/datafile/system.256.976558181
  231. channel ORA_DISK_5: reading from backup piece +FRA/orcl/backupset/2018_05_19/nnndf0_tag20180519t191110_0.300.976561871
  232. channel ORA_DISK_2: piece handle=+FRA/orcl/backupset/2018_05_19/nnndf0_tag20180519t191110_0.298.976561883 tag=TAG20180519T191110
  233. channel ORA_DISK_2: restored backup piece 1
  234. channel ORA_DISK_2: restore complete, elapsed time: 00:00:03
  235. channel ORA_DISK_3: piece handle=+FRA/orcl/backupset/2018_05_19/nnndf0_tag20180519t191110_0.299.976561877 tag=TAG20180519T191110
  236. channel ORA_DISK_3: restored backup piece 1
  237. channel ORA_DISK_3: restore complete, elapsed time: 00:00:37
  238. channel ORA_DISK_1: piece handle=+FRA/orcl/backupset/2018_05_19/nnndf0_tag20180519t191110_0.301.976561875 tag=TAG20180519T191110
  239. channel ORA_DISK_1: restored backup piece 1
  240. channel ORA_DISK_1: restore complete, elapsed time: 00:00:47
  241. channel ORA_DISK_4: piece handle=+FRA/orcl/backupset/2018_05_19/nnndf0_tag20180519t191110_0.302.976561873 tag=TAG20180519T191110
  242. channel ORA_DISK_4: restored backup piece 1
  243. channel ORA_DISK_4: restore complete, elapsed time: 00:01:37
  244. channel ORA_DISK_5: piece handle=+FRA/orcl/backupset/2018_05_19/nnndf0_tag20180519t191110_0.300.976561871 tag=TAG20180519T191110
  245. channel ORA_DISK_5: restored backup piece 1
  246. channel ORA_DISK_5: restore complete, elapsed time: 00:01:56
  247. Finished restore at 20-MAY-18

  249. Starting recover at 20-MAY-18
  250. using channel ORA_DISK_1
  251. using channel ORA_DISK_2
  252. using channel ORA_DISK_3
  253. using channel ORA_DISK_4
  254. using channel ORA_DISK_5
  255. using channel ORA_DISK_6
  256. using channel ORA_DISK_7
  257. using channel ORA_DISK_8

  259. starting media recovery

  261. archived log for thread 1 with sequence 1 is already on disk as file +FRA/orcl/archivelog/2018_05_19/thread_1_seq_1.285.976563839
  262. archived log for thread 1 with sequence 2 is already on disk as file +FRA/orcl/archivelog/2018_05_19/thread_1_seq_2.284.976563839
  263. archived log for thread 1 with sequence 3 is already on disk as file +FRA/orcl/archivelog/2018_05_19/thread_1_seq_3.282.976563839
  264. archived log for thread 1 with sequence 4 is already on disk as file +FRA/orcl/archivelog/2018_05_19/thread_1_seq_4.281.976563843
  265. archived log for thread 1 with sequence 5 is already on disk as file +FRA/orcl/archivelog/2018_05_19/thread_1_seq_5.280.976563845
  266. archived log for thread 1 with sequence 6 is already on disk as file +FRA/orcl/archivelog/2018_05_19/thread_1_seq_6.277.976575919
  267. archived log for thread 1 with sequence 7 is already on disk as file +FRA/orcl/archivelog/2018_05_19/thread_1_seq_7.275.976575917
  268. archived log for thread 1 with sequence 1 is already on disk as file +FRA/orcl/archivelog/2018_05_19/thread_1_seq_1.283.976577949
  269. archived log for thread 1 with sequence 2 is already on disk as file +FRA/orcl/archivelog/2018_05_19/thread_1_seq_2.308.976577967
  270. archived log for thread 1 with sequence 3 is already on disk as file +FRA/orcl/archivelog/2018_05_19/thread_1_seq_3.307.976577967
  271. archived log for thread 1 with sequence 4 is already on disk as file +FRA/orcl/archivelog/2018_05_19/thread_1_seq_4.306.976577971
  272. archived log for thread 1 with sequence 1 is already on disk as file +FRA/orcl/archivelog/2018_05_19/thread_1_seq_1.269.976579079
  273. archived log file name=+FRA/orcl/archivelog/2018_05_19/thread_1_seq_1.285.976563839 thread=1 sequence=1
  274. archived log file name=+FRA/orcl/archivelog/2018_05_19/thread_1_seq_2.284.976563839 thread=1 sequence=2
  275. archived log file name=+FRA/orcl/archivelog/2018_05_19/thread_1_seq_3.282.976563839 thread=1 sequence=3
  276. archived log file name=+FRA/orcl/archivelog/2018_05_19/thread_1_seq_4.281.976563843 thread=1 sequence=4
  277. archived log file name=+FRA/orcl/archivelog/2018_05_19/thread_1_seq_5.280.976563845 thread=1 sequence=5
  278. archived log file name=+FRA/orcl/archivelog/2018_05_19/thread_1_seq_6.277.976575919 thread=1 sequence=6
  279. archived log file name=+FRA/orcl/archivelog/2018_05_19/thread_1_seq_7.275.976575917 thread=1 sequence=7
  280. archived log file name=+FRA/orcl/archivelog/2018_05_19/thread_1_seq_1.283.976577949 thread=1 sequence=1
  281. archived log file name=+FRA/orcl/archivelog/2018_05_19/thread_1_seq_2.308.976577967 thread=1 sequence=2
  282. archived log file name=+FRA/orcl/archivelog/2018_05_19/thread_1_seq_3.307.976577967 thread=1 sequence=3
  283. archived log file name=+FRA/orcl/archivelog/2018_05_19/thread_1_seq_4.306.976577971 thread=1 sequence=4
  284. archived log file name=+FRA/orcl/archivelog/2018_05_19/thread_1_seq_1.269.976579079 thread=1 sequence=1
  285. media recovery complete, elapsed time: 00:00:05
  286. Finished recover at 20-MAY-18

  288. RMAN> alter database open resetlogs;

  290. database opened

  292. RMAN>
复制代码
保障闪回还原点：
  1. select  * from v$flashback_database_stat;

  3. select  * from v$restore_point;

  5. create restore point rsp1;

  7. create restore point rsp2 guarantee flashback database;

  9. select  * from v$restore_point;
复制代码







回复

使用道具 举报

返回列表 发新帖
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|Bo's Oracle Station   

GMT+8, 2024-11-21 22:36 , Processed in 0.090331 second(s), 28 queries .

快速回复 返回顶部 返回列表