课程第7次

botang

2019-05-22

第一个advisor


备份控制文件恢复（8a）：

1. [oracle@station80 ~]$ . oraenv
   
2. ORACLE_SID = [orcl] ? +ASM
   
3. The Oracle base remains unchanged with value /u01/app/oracle
   
4. [oracle@station80 ~]$ asmcmd
   
5. ASMCMD> ls
   
6. DATA/
   
7. FRA/
   
8. ASMCMD> cd data
   
9. ASMCMD> ls
   
10. ASM/
    
11. ORCL/
    
12. ASMCMD> cd orcl
    
13. ASMCMD> ls
    
14. CONTROLFILE/
    
15. DATAFILE/
    
16. PARAMETERFILE/
    
17. TEMPFILE/
    
18. spfileorcl.ora
    
19. ASMCMD> cd +FRA
    
20. ASMCMD> ls
    
21. ORCL/
    
22. ASMCMD> cd orcl
    
23. ASMCMD> ls
    
24. CONTROLFILE/
    
25. ASMCMD> exit
    
26. [oracle@station80 ~]$ . oraenv
    
27. ORACLE_SID = [+ASM] ? orcl
    
28. The Oracle base remains unchanged with value /u01/app/oracle
    
29. [oracle@station80 ~]$ sqlplus /nolog
    
30. 
    
31. SQL*Plus: Release 11.2.0.4.0 Production on Wed May 22 21:08:19 2019
    
32. 
    
33. Copyright (c) 1982, 2013, Oracle.  All rights reserved.
    
34. 
    
35. SQL> conn / as sysdba
    
36. Connected to an idle instance.
    
37. SQL> startup mount
    
38. ORACLE instance started.
    
39. 
    
40. Total System Global Area 1536602112 bytes
    
41. Fixed Size                  2253504 bytes
    
42. Variable Size             939527488 bytes
    
43. Database Buffers          587202560 bytes
    
44. Redo Buffers                7618560 bytes
    
45. Database mounted.
    
46. SQL> alter database open resetlogs;
    
47. alter database open resetlogs
    
48. *
    
49. ERROR at line 1:
    
50. ORA-01139: RESETLOGS option only valid after an incomplete database recovery
    
51. 
    
52. 
    
53. SQL> recover database until cancel ;
    
54. Media recovery complete.
    
55. SQL> alter database open ;
    
56. alter database open
    
57. *
    
58. ERROR at line 1:
    
59. ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
    
60. 
    
61. 
    
62. SQL> alter database open resetlogs;
    
63. 
    
64. Database altered.
    
65. 
    
66. SQL> select  open_mode from v$database;
    
67. 
    
68. OPEN_MODE
    
69. --------------------
    
70. READ WRITE
    
71. 
    
72. SQL> archive log list
    
73. Database log mode              No Archive Mode
    
74. Automatic archival             Disabled
    
75. Archive destination            USE_DB_RECOVERY_FILE_DEST
    
76. Oldest online log sequence     1
    
77. Current log sequence           1
    
78. SQL> alter database backup controlfile to '/home/oracle/control.f';
    
79. 
    
80. Database altered.
    
81. 
    
82. SQL> exit
    
83. Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64                                                                             bit Production
    
84. With the Partitioning, Automatic Storage Management, OLAP, Data Mining
    
85. and Real Application Testing options
    
86. [oracle@station80 ~]$ rman target /
    
87. 
    
88. Recovery Manager: Release 11.2.0.4.0 - Production on Wed May 22 21:28:41 2019
    
89. 
    
90. Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
    
91. 
    
92. connected to target database: ORCL (DBID=1535788302)
    
93. 
    
94. RMAN> backup current controlfile;
    
95. 
    
96. Starting backup at 22-MAY-19
    
97. using target database control file instead of recovery catalog
    
98. allocated channel: ORA_DISK_1
    
99. channel ORA_DISK_1: SID=34 device type=DISK
    
100. channel ORA_DISK_1: starting full datafile backup set
     
101. channel ORA_DISK_1: specifying datafile(s) in backup set
     
102. including current control file in backup set
     
103. channel ORA_DISK_1: starting piece 1 at 22-MAY-19
     
104. channel ORA_DISK_1: finished piece 1 at 22-MAY-19
     
105. piece handle=+FRA/orcl/backupset/2019_05_22/ncnnf0_tag20190522t212937_0.260.1008                                                                             970179 tag=TAG20190522T212937 comment=NONE
     
106. channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
     
107. Finished backup at 22-MAY-19
     
108. 
     
109. RMAN> list backup;
     
110. 
     
111. 
     
112. List of Backup Sets
     
113. ===================
     
114. 
     
115. 
     
116. BS Key  Type LV Size       Device Type Elapsed Time Completion Time
     
117. ------- ---- -- ---------- ----------- ------------ ---------------
     
118. 1       Full    9.33M      DISK        00:00:01     22-MAY-19
     
119.         BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: TAG20190522T212937
     
120.         Piece Name: +FRA/orcl/backupset/2019_05_22/ncnnf0_tag20190522t212937_0.2                                                                             60.1008970179
     
121.   Control File Included: Ckp SCN: 1137211      Ckp time: 22-MAY-19
     
122. 
     
123. RMAN> exit
     
124. 
     
125. 
     
126. Recovery Manager complete.
     
127. [oracle@station80 ~]$ ls -l /home/oracle/control.f
     
128. -rw-r-----. 1 oracle asmadmin 9748480 May 22 21:27 /home/oracle/control.f
     
129. [oracle@station80 ~]$ file /home/oracle/control.f
     
130. /home/oracle/control.f: data
     
131. [oracle@station80 ~]
     1. select  * from v$log;
        
     2. 
        
     3. select  * from v$logfile;
        
     4. 
        
     5. 
        
     6. alter database add logfile member '/home/oracle/redo02c.log' to group 2;
        
     7. 
        
     8. alter database drop logfile member '/home/oracle/redo02c.log';
        
     9. 
        
     10. alter system switch logfile;
         
     11. 
         
     12. alter database drop logfile member '/home/oracle/redo02c.log';

     复制代码
     nbsp; . oraenv
     
132. -bash: ▒.: command not found
     
133. [oracle@station80 ~]$ . oraenv
     
134. ORACLE_SID = [orcl] ? +ASM
     
135. The Oracle base remains unchanged with value /u01/app/oracle
     
136. [oracle@station80 ~]$ asmcmd
     
137. ASMCMD> ls
     
138. DATA/
     
139. FRA/
     
140. ASMCMD> cd DATA/
     
141. ASMCMD> ls
     
142. ASM/
     
143. ORCL/
     
144. ASMCMD> cd orcl
     
145. ASMCMD> ls
     
146. DATAFILE/
     
147. ONLINELOG/
     
148. PARAMETERFILE/
     
149. TEMPFILE/
     
150. spfileorcl.ora
     
151. ASMCMD> cd +FRA
     
152. ASMCMD> ls
     
153. ORCL/
     
154. ASMCMD> cd orcl
     
155. ASMCMD> ls
     
156. BACKUPSET/
     
157. ONLINELOG/
     
158. ASMCMD> exit
     
159. [oracle@station80 ~]$ . oraenv
     
160. ORACLE_SID = [+ASM] ? orcl
     
161. The Oracle base remains unchanged with value /u01/app/oracle
     
162. [oracle@station80 ~]$ sqlplus /nolog
     
163. 
     
164. SQL*Plus: Release 11.2.0.4.0 Production on Wed May 22 21:41:49 2019
     
165. 
     
166. Copyright (c) 1982, 2013, Oracle.  All rights reserved.
     
167. 
     
168. SQL> conn / as sysdba
     
169. Connected to an idle instance.
     
170. SQL> startup
     
171. ORACLE instance started.
     
172. 
     
173. Total System Global Area 1536602112 bytes
     
174. Fixed Size                  2253504 bytes
     
175. Variable Size             939527488 bytes
     
176. Database Buffers          587202560 bytes
     
177. Redo Buffers                7618560 bytes
     
178. ORA-00205: error in identifying control file, check alert log for more info
     
179. 
     
180. 
     
181. SQL> show parameter control
     
182. 
     
183. NAME                                 TYPE
     
184. ------------------------------------ ---------------------------------
     
185. VALUE
     
186. ------------------------------
     
187. control_file_record_keep_time        integer
     
188. 7
     
189. control_files                        string
     
190. +DATA/orcl/controlfile/current
     
191. .260.1008007567, +FRA/orcl/con
     
192. trolfile/current.256.100800756
     
193. 7
     
194. control_management_pack_access       string
     
195. DIAGNOSTIC+TUNING
     
196. SQL> exit
     
197. Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64                                                                             bit Production
     
198. With the Partitioning, OLAP, Data Mining and Real Application Testing options
     
199. [oracle@station80 ~]$ rman target /
     
200. 
     
201. Recovery Manager: Release 11.2.0.4.0 - Production on Wed May 22 21:43:56 2019
     
202. 
     
203. Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
     
204. 
     
205. connected to target database: ORCL (not mounted)
     
206. 
     
207. RMAN> list backup of controlfile;
     
208. 
     
209. using target database control file instead of recovery catalog
     
210. RMAN-00571: ===========================================================
     
211. RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
     
212. RMAN-00571: ===========================================================
     
213. RMAN-03002: failure of list command at 05/22/2019 21:45:23
     
214. ORA-01507: database not mounted
     
215. 
     
216. RMAN> exit
     
217. 
     
218. 
     
219. Recovery Manager complete.
     
220. [oracle@station80 ~]$ . oraenv
     
221. ORACLE_SID = [orcl] ? +ASM
     
222. ORACLE_HOME = [/home/oracle] ?
     
223. ORACLE_BASE environment variable is not being set since this
     
224. information is not available for the current user ID oracle.
     
225. You can set ORACLE_BASE manually if it is required.
     
226. [oracle@station80 ~]$ . oraenv
     
227. ORACLE_SID = ['+ASM] ? +ASM
     
228. The Oracle base has been set to /u01/app/oracle
     
229. [oracle@station80 ~]$ asmcmd
     
230. ASMCMD> ls
     
231. DATA/
     
232. FRA/
     
233. ASMCMD> cd fra
     
234. ASMCMD> ls
     
235. ORCL/
     
236. ASMCMD> cd orcl
     
237. ASMCMD> ls
     
238. BACKUPSET/
     
239. ONLINELOG/
     
240. ASMCMD> cd BACKUPSET/
     
241. ASMCMD> ls
     
242. 2019_05_22/
     
243. ASMCMD> cd 2019_05_22/
     
244. ASMCMD> ls
     
245. ncnnf0_LABS-8A-CONTROLFILE_0.261.1008970613
     
246. ncnnf0_TAG20190522T212937_0.260.1008970179
     
247. ASMCMD> pwd
     
248. +fra/orcl/BACKUPSET/2019_05_22
     
249. ASMCMD> exit
     
250. [oracle@station80 ~]$ . oraenv
     
251. ORACLE_SID = [+ASM] ? orcl
     
252. The Oracle base remains unchanged with value /u01/app/oracle
     
253. [oracle@station80 ~]$ rman target  /
     
254. 
     
255. Recovery Manager: Release 11.2.0.4.0 - Production on Wed May 22 21:46:45 2019
     
256. 
     
257. Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
     
258. 
     
259. connected to target database: ORCL (not mounted)
     
260. 
     
261. RMAN> restore controlfile from '+fra/orcl/BACKUPSET/2019_05_22/ncnnf0_LABS-8A-CO                                                                             NTROLFILE_0.261.1008970613';
     
262. 
     
263. Starting restore at 22-MAY-19
     
264. using target database control file instead of recovery catalog
     
265. allocated channel: ORA_DISK_1
     
266. channel ORA_DISK_1: SID=12 device type=DISK
     
267. 
     
268. channel ORA_DISK_1: restoring control file
     
269. channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
     
270. output file name=+DATA/orcl/controlfile/current.260.1008971239
     
271. output file name=+FRA/orcl/controlfile/current.256.1008971239
     
272. Finished restore at 22-MAY-19
     
273. 
     
274. RMAN> alter database mount;
     
275. 
     
276. database mounted
     
277. released channel: ORA_DISK_1
     
278. 
     
279. RMAN> exit
     
280. 
     
281. 
     
282. Recovery Manager complete.
     
283. [oracle@station80 ~]$ sqlplus /nolog
     
284. 
     
285. SQL*Plus: Release 11.2.0.4.0 Production on Wed May 22 21:47:51 2019
     
286. 
     
287. Copyright (c) 1982, 2013, Oracle.  All rights reserved.
     
288. 
     
289. SQL> conn / as sysdba
     
290. Connected.
     
291. SQL> show parameter control
     
292. 
     
293. NAME                                 TYPE        VALUE
     
294. ------------------------------------ ----------- ------------------------------
     
295. control_file_record_keep_time        integer     7
     
296. control_files                        string      +DATA/orcl/controlfile/current
     
297.                                                  .260.1008971239, +FRA/orcl/con
     
298.                                                  trolfile/current.256.100897123
     
299.                                                  9
     
300. control_management_pack_access       string      DIAGNOSTIC+TUNING
     
301. SQL> exit
     
302. Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64                                                                             bit Production
     
303. With the Partitioning, Automatic Storage Management, OLAP, Data Mining
     
304. and Real Application Testing options
     
305. [oracle@station80 ~]$rman target /
     
306. -bash: ▒rman: command not found
     
307. [oracle@station80 ~]$ ▒rman target /
     
308. -bash: ▒rman: command not found
     
309. [oracle@station80 ~]$
     
310. [oracle@station80 ~]$
     
311. [oracle@station80 ~]$
     
312. [oracle@station80 ~]$ rman target /
     
313. -bash: ▒rman: command not found
     
314. [oracle@station80 ~]$
     
315. [oracle@station80 ~]$
     
316. [oracle@station80 ~]$
     
317. [oracle@station80 ~]$ . oraenv
     
318. ORACLE_SID = [orcl] ?
     
319. The Oracle base remains unchanged with value /u01/app/oracle
     
320. [oracle@station80 ~]$ rman target  /
     
321. 
     
322. Recovery Manager: Release 11.2.0.4.0 - Production on Wed May 22 21:49:57 2019
     
323. 
     
324. Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
     
325. 
     
326. connected to target database: ORCL (DBID=1535788302, not open)
     
327. 
     
328. RMAN> alter database open ;
     
329. 
     
330. using target database control file instead of recovery catalog
     
331. RMAN-00571: ===========================================================
     
332. RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
     
333. RMAN-00571: ===========================================================
     
334. RMAN-03002: failure of alter db command at 05/22/2019 21:50:13
     
335. ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
     
336. 
     
337. RMAN> alter database open  resetlogs;
     
338. 
     
339. RMAN-00571: ===========================================================
     
340. RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
     
341. RMAN-00571: ===========================================================
     
342. RMAN-03002: failure of alter db command at 05/22/2019 21:50:23
     
343. ORA-01194: file 1 needs more recovery to be consistent
     
344. ORA-01110: data file 1: '+DATA/orcl/datafile/system.256.1008007421'
     
345. 
     
346. RMAN> recover database;
     
347. 
     
348. Starting recover at 22-MAY-19
     
349. Starting implicit crosscheck backup at 22-MAY-19
     
350. allocated channel: ORA_DISK_1
     
351. channel ORA_DISK_1: SID=12 device type=DISK
     
352. Crosschecked 1 objects
     
353. Finished implicit crosscheck backup at 22-MAY-19
     
354. 
     
355. Starting implicit crosscheck copy at 22-MAY-19
     
356. using channel ORA_DISK_1
     
357. Crosschecked 2 objects
     
358. Finished implicit crosscheck copy at 22-MAY-19
     
359. 
     
360. searching for all files in the recovery area
     
361. cataloging files...
     
362. cataloging done
     
363. 
     
364. List of Cataloged Files
     
365. =======================
     
366. File Name: +fra/ORCL/BACKUPSET/2019_05_22/ncnnf0_LABS-8A-CONTROLFILE_0.261.10089                                                                             70613
     
367. 
     
368. using channel ORA_DISK_1
     
369. 
     
370. starting media recovery
     
371. 
     
372. archived log for thread 1 with sequence 5 is already on disk as file +DATA/orcl/                                                                             onlinelog/group_2.262.1008969079
     
373. archived log for thread 1 with sequence 6 is already on disk as file +DATA/orcl/                                                                             onlinelog/group_3.261.1008969081
     
374. archived log for thread 1 with sequence 7 is already on disk as file +DATA/orcl/                                                                             onlinelog/group_1.263.1008969079
     
375. archived log file name=+DATA/orcl/onlinelog/group_2.262.1008969079 thread=1 sequ                                                                             ence=5
     
376. archived log file name=+DATA/orcl/onlinelog/group_3.261.1008969081 thread=1 sequ                                                                             ence=6
     
377. archived log file name=+DATA/orcl/onlinelog/group_1.263.1008969079 thread=1 sequ                                                                             ence=7
     
378. media recovery complete, elapsed time: 00:00:00
     
379. Finished recover at 22-MAY-19
     
380. 
     
381. RMAN> alter database open;
     
382. 
     
383. RMAN-00571: ===========================================================
     
384. RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
     
385. RMAN-00571: ===========================================================
     
386. RMAN-03002: failure of alter db command at 05/22/2019 21:51:36
     
387. ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
     
388. 
     
389. RMAN> alter database open resetogs;
     
390. 
     
391. RMAN-00571: ===========================================================
     
392. RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
     
393. RMAN-00571: ===========================================================
     
394. RMAN-00558: error encountered while parsing input commands
     
395. RMAN-01009: syntax error: found "identifier": expecting one of: "resetlogs, ;"
     
396. RMAN-01008: the bad identifier was: resetogs
     
397. RMAN-01007: at line 1 column 21 file: standard input
     
398. 
     
399. RMAN> alter database open resetlogs;
     
400. 
     
401. database opened
     
402. 
     
403. RMAN> list incarnation of database ;
     
404. 
     
405. 
     
406. List of Database Incarnations
     
407. DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
     
408. ------- ------- -------- ---------------- --- ---------- ----------
     
409. 1       1       ORCL     1535788302       PARENT  1          24-AUG-13
     
410. 2       2       ORCL     1535788302       PARENT  925702     11-MAY-19
     
411. 3       3       ORCL     1535788302       PARENT  1134795    22-MAY-19
     
412. 4       4       ORCL     1535788302       CURRENT 1138641    22-MAY-19
     
413. 
     
414. RMAN>
     

复制代码

关于日志成员：

1. select  * from v$log;
   
2. 
   
3. select  * from v$logfile;
   
4. 
   
5. 
   
6. alter database add logfile member '/home/oracle/redo02c.log' to group 2;
   
7. 
   
8. alter database drop logfile member '/home/oracle/redo02c.log';
   
9. 
   
10. alter system switch logfile;
    
11. 
    
12. alter database drop logfile member '/home/oracle/redo02c.log';

复制代码