课程第57/58次（2017-09-24星期天上下午）

botang

第三阶段New Feature/OCM Exam Preparation（31-58）
新特性第19章：

1. select  * from dba_objects
   
2. where object_name like 'DBMS_LOG%';\
   
3. ---
   
4. 
   
5. select  * from dba_directories;
   
6. 
   
7. create directory dir1 as '/home/oracle/dir1';
   
8. 
   
9.   
   
10. create directory dir2 as '/home/oracle/dir2';
    
11. 
    
12. grant all on directory dir1 to hr;
    
13. 
    
14. grant all on directory dir2 to hr;

复制代码

1. expdp hr/oracle_4U directory=dir1 dumpfile=dir1:a1_%U.dmp,dir2:a2_%U.dmp   tables=t_big filesize=10M

复制代码

在Linux上作,要考虑转意：

1. expdp hr/oracle_4U directory=dir1 dumpfile=a3.dmp  schemas=hr job_name=a3_job  exclude=table:"in \(\'T_BANK_INVOICE\'\)", procedure:" in \(\'SECURE_DML\'\)"  "query='employees:where department_id=90'"

复制代码

parfile:

1. directory=dir1
   
2. dumpfile=a4.dmp  
   
3. schemas=hr
   
4. job_name=a4
   
5. exclude=table:"in ('T_BANK_INVOICE')", procedure:"in ('SECURE_DML')"  , constraint
   
6. query='employees:where department_id=90'
   

复制代码

带建用户命令导出：

1. directory=dir1
   
2. dumpfile=a5.dmp
   
3. schemas=hr
   
4. job_name=a5
   
5. exclude=table:"in ('T_BANK_INVOICE')", procedure:"in ('SECURE_DML')" , constraint
   
6. query='hr.employees:where department_id=90'
   

复制代码

1. expdp system/oracle_4U parfile='/home/oracle/a5.par'

复制代码

1. impdp system/oracle_4U  directory=dir1 dumpfile=a5.dmp  remap_schema=hr:user3

复制代码

---

1. run{
   
2. duplicate target database to db11g
   
3. from active database
   
4. nofilenamecheck
   
5. spfile
   
6. set
   
7. control_files='/u01/app/oracle/oradata/db11g/control01.ctl','/u01/app/oracle/oradata/db11g/control02.ctl','/u01/app/oracle/oradata/db11g/control03.ctl'
   
8. set
   
9. db_file_name_convert='+DATA/orcl/datafile/example.265.832197353','/u01/app/oracle/oradata/db11g/example01.dbf','+DATA/orcl/datafile/users.259.832197065','/u01/app/oracle/oradata/db11g/users01.dbf','+DATA/orcl/datafile/undotbs1.258.832197065','/u01/app/oracle/oradata/db11g/undotbs01.dbf','+DATA/orcl/datafile/sysaux.257.832197065','/u01/app/oracle/oradata/db11g/sysaux01.dbf','+DATA/orcl/datafile/system.256.832197063','/u01/app/oracle/oradata/db11g/system01.dbf'
   
10. set
    
11. log_file_name_convert='+DATA/orcl/onlinelog/group_1.261.832197287','/u01/app/oracle/oradata/db11g/redo01a.log','+FRA/orcl/onlinelog/group_1.257.832197291','/u01/app/oracle/oradata/db11g/redo01b.log','+DATA/orcl/onlinelog/group_3.263.832197301','/u01/app/oracle/oradata/db11g/redo03a.log','+FRA/orcl/onlinelog/group_3.259.832197305','/u01/app/oracle/oradata/db11g/redo03b.log','+DATA/orcl/onlinelog/group_2.262.832197293','/u01/app/oracle/oradata/db11g/redo02a.log','+FRA/orcl/onlinelog/group_2.258.832197299','/u01/app/oracle/oradata/db11g/redo02b.log';
    
12. set
    
13. audit_file_dest='/u01/app/oracle/admin/db11g/adump'
    
14. set
    
15. db_create_file_dest=''
    
16. set
    
17. db_recovery_file_dest='/u01/app/oracle/flash_recovery_area';
    
18. }

复制代码

新的set newname 克隆语法：
ASM到ASM：

1. RUN
   
2. {
   
3. allocate channel c1 device type disk;
   
4. allocate channel c2 device type disk;
   
5. allocate channel c3 device type disk;
   
6. allocate channel c4 device type disk;
   
7. allocate channel c5 device type disk;
   
8. allocate channel c6 device type disk;
   
9. allocate channel c7 device type disk;
   
10. allocate channel c8 device type disk;
    
11. allocate auxiliary channel c9 device type disk;
    
12. allocate auxiliary channel c10 device type disk;
    
13. allocate auxiliary channel c11 device type disk;
    
14. allocate auxiliary channel c12 device type disk;
    
15. allocate auxiliary channel c13 device type disk;
    
16. allocate auxiliary channel c14 device type disk;
    
17. allocate auxiliary channel c15 device type disk;
    
18. allocate auxiliary channel c16 device type disk;
    
19. DUPLICATE TARGET DATABASE TO db11g
    
20. from active database
    
21. nofilenamecheck
    
22. spfile
    
23. set
    
24. control_files='+DATA','+FRA'
    
25. set
    
26. audit_file_dest='/u01/app/oracle/admin/db11g/adump';
    
27. }

复制代码

新的set newname 克隆语法：
ASM到FILE SYSTEM：

1.     RUN
   
2.     {
   
3.     allocate channel c1 device type disk;
   
4.     allocate channel c2 device type disk;
   
5.     allocate channel c3 device type disk;
   
6.     allocate channel c4 device type disk;
   
7.     allocate channel c5 device type disk;
   
8.     allocate channel c6 device type disk;
   
9.     allocate channel c7 device type disk;
   
10.     allocate channel c8 device type disk;
    
11.     allocate auxiliary channel c9 device type disk;
    
12.     allocate auxiliary channel c10 device type disk;
    
13.     allocate auxiliary channel c11 device type disk;
    
14.     allocate auxiliary channel c12 device type disk;
    
15.     allocate auxiliary channel c13 device type disk;
    
16.     allocate auxiliary channel c14 device type disk;
    
17.     allocate auxiliary channel c15 device type disk;
    
18.     allocate auxiliary channel c16 device type disk;
    
19.     SET NEWNAME FOR DATABASE TO '/u01/app/oracle/oradata/new11g/%b';
    
20.     DUPLICATE TARGET DATABASE TO new11g
    
21.     from active database
    
22.     nofilenamecheck
    
23.     LOGFILE
    
24.     GROUP 1 ('/u01/app/oracle/oradata/new11g/redo01a.log',
    
25.     '/u01/app/oracle/oradata/new11g/redo01b.log') SIZE 50M REUSE,
    
26.     GROUP 2 ('/u01/app/oracle/oradata/new11g/redo02a.log',
    
27.     '/u01/app/oracle/oradata/new11g/redo02b.log') SIZE 50M REUSE,
    
28.     GROUP 3 ('/u01/app/oracle/oradata/new11g/redo03a.log',
    
29.     '/u01/app/oracle/oradata/new11g/redo03b.log') SIZE 50M REUSE
    
30.     spfile
    
31.     set
    
32.     memory_max_target='2G'
    
33.     set
    
34.     memory_target='2G'
    
35.     set
    
36.     control_files='/u01/app/oracle/oradata/new11g/control01.ctl','/u01/app/oracle/oradata/new11g/control02.ctl','/u01/app/oracle/oradata/new11g/control03.ctl'
    
37.     set
    
38.     audit_file_dest='/u01/app/oracle/admin/new11g/adump'
    
39.     set
    
40.     db_create_file_dest=''
    
41.     set
    
42.     db_recovery_file_dest='/u01/app/oracle/flash_recovery_area';
    
43.     }
    
44. 
    
45. 
    
46. 
    

复制代码

TARGETLESS 克隆：
源头库作备份：

1.     run {
   
2.     allocate channel  c1 device type disk format '/home/oracle/backup/%U';
   
3.     allocate channel  c2 device type disk format '/home/oracle/backup/%U';
   
4.     allocate channel  c3 device type disk format '/home/oracle/backup/%U';
   
5.     allocate channel  c4 device type disk format '/home/oracle/backup/%U';
   
6.     allocate channel  c5 device type disk format '/home/oracle/backup/%U';
   
7.     allocate channel  c6 device type disk format '/home/oracle/backup/%U';
   
8.     allocate channel  c7 device type disk format '/home/oracle/backup/%U';
   
9.     allocate channel  c8 device type disk format '/home/oracle/backup/%U';
   
10.     backup database plus archivelog force;
    
11.     backup spfile;
    
12.     backup current controlfile;
    
13.     }

复制代码

rman auxiliary sys/oracle_4U@yourdb

1.     run{
   
2.     allocate  auxiliary channel c1 device type disk;
   
3.     allocate  auxiliary channel c2 device type disk;
   
4.     allocate  auxiliary channel c3 device type disk;
   
5.     allocate  auxiliary channel c4 device type disk;
   
6.     allocate  auxiliary channel c5 device type disk;
   
7.     allocate  auxiliary channel c6 device type disk;
   
8.     allocate  auxiliary channel c7 device type disk;
   
9.     allocate  auxiliary channel c8 device type disk;
   
10.     SET NEWNAME FOR DATABASE TO '/u01/app/oracle/oradata/yourdb/%b';
    
11.     duplicate target database to yourdb
    
12.     backup location '/home/oracle/backup'
    
13.     nofilenamecheck
    
14.     skip tablespace example
    
15.     LOGFILE
    
16.     GROUP 1 ('/u01/app/oracle/oradata/yourdb/redo01a.log',
    
17.     '/u01/app/oracle/oradata/yourdb/redo01b.log') SIZE 50M REUSE,
    
18.     GROUP 2 ('/u01/app/oracle/oradata/yourdb/redo02a.log',
    
19.     '/u01/app/oracle/oradata/yourdb/redo02b.log') SIZE 50M REUSE,
    
20.     GROUP 3 ('/u01/app/oracle/oradata/yourdb/redo03a.log',
    
21.     '/u01/app/oracle/oradata/yourdb/redo03b.log') SIZE 50M REUSE
    
22.     spfile
    
23.     set
    
24.     control_files='/u01/app/oracle/oradata/yourdb/control01.ctl','/u01/app/oracle/oradata/yourdb/control02.ctl','/u01/app/oracle/oradata/yourdb/control03.ctl'
    
25.     set
    
26.     audit_file_dest='/u01/app/oracle/admin/yourdb/adump'
    
27.     set
    
28.     db_create_file_dest=''
    
29.     set
    
30.     db_recovery_file_dest='/u01/app/oracle/flash_recovery_area';
    
31. 
    
32.     }

复制代码

TSPITR：

1. SQL> drop table TTESTBOTANG_5;
   
2. 
   
3. Table dropped.
   
4. 
   
5. SQL> alter database default tablespace example;
   
6. 
   
7. Database altered.
   

复制代码

1. RMAN> run {
   
2. 2> sql "alter session set nls_date_format=''YYYY-MM-DD:HH24:MI:SS''";
   
3. 3> recover tablespace users  until  time '2017-09-25:11:01:04'
   
4. 4> auxiliary destination '/u01/app/oracle/oradata';
   
5. 5> }
   
6. 
   
7. sql statement: alter session set nls_date_format=''YYYY-MM-DD:HH24:MI:SS''
   
8. 
   
9. Starting recover at 2017-09-25:11:23:34
   
10. using channel ORA_DISK_1
    
11. using channel ORA_DISK_2
    
12. using channel ORA_DISK_3
    
13. using channel ORA_DISK_4
    
14. using channel ORA_DISK_5
    
15. using channel ORA_DISK_6
    
16. using channel ORA_DISK_7
    
17. using channel ORA_DISK_8
    
18. using channel ORA_SBT_TAPE_1
    
19. using channel ORA_SBT_TAPE_2
    
20. 
    
21. Creating automatic instance, with SID='eEeq'
    
22. 
    
23. initialization parameters used for automatic instance:
    
24. db_name=ORCL
    
25. db_unique_name=eEeq_tspitr_ORCL
    
26. compatible=11.2.0.0.0
    
27. db_block_size=8192
    
28. db_files=200
    
29. sga_target=280M
    
30. processes=50
    
31. db_create_file_dest=/u01/app/oracle/oradata
    
32. log_archive_dest_1='location=/u01/app/oracle/oradata'
    
33. #No auxiliary parameter file used
    
34. 
    
35. 
    
36. starting up automatic instance ORCL
    
37. 
    
38. Oracle instance started
    
39. 
    
40. Total System Global Area     292278272 bytes
    
41. 
    
42. Fixed Size                     2212736 bytes
    
43. Variable Size                100666496 bytes
    
44. Database Buffers             184549376 bytes
    
45. Redo Buffers                   4849664 bytes
    
46. Automatic instance created
    
47. Running TRANSPORT_SET_CHECK on recovery set tablespaces
    
48. TRANSPORT_SET_CHECK completed successfully
    
49. 
    
50. contents of Memory Script:
    
51. {
    
52. # set requested point in time
    
53. set until  time "2017-09-25:11:01:04";
    
54. # restore the controlfile
    
55. restore clone controlfile;
    
56. # mount the controlfile
    
57. sql clone 'alter database mount clone database';
    
58. # archive current online log
    
59. sql 'alter system archive log current';
    
60. # avoid unnecessary autobackups for structural changes during TSPITR
    
61. sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
    
62. # resync catalog
    
63. resync catalog;
    
64. }
    
65. executing Memory Script
    
66. 
    
67. executing command: SET until clause
    
68. 
    
69. Starting restore at 2017-09-25:11:24:41
    
70. allocated channel: ORA_AUX_DISK_1
    
71. channel ORA_AUX_DISK_1: SID=81 device type=DISK
    
72. allocated channel: ORA_AUX_DISK_2
    
73. channel ORA_AUX_DISK_2: SID=5 device type=DISK
    
74. allocated channel: ORA_AUX_DISK_3
    
75. channel ORA_AUX_DISK_3: SID=30 device type=DISK
    
76. allocated channel: ORA_AUX_DISK_4
    
77. channel ORA_AUX_DISK_4: SID=56 device type=DISK
    
78. allocated channel: ORA_AUX_DISK_5
    
79. channel ORA_AUX_DISK_5: SID=82 device type=DISK
    
80. allocated channel: ORA_AUX_DISK_6
    
81. channel ORA_AUX_DISK_6: SID=6 device type=DISK
    
82. allocated channel: ORA_AUX_DISK_7
    
83. channel ORA_AUX_DISK_7: SID=31 device type=DISK
    
84. allocated channel: ORA_AUX_DISK_8
    
85. channel ORA_AUX_DISK_8: SID=57 device type=DISK
    
86. allocated channel: ORA_AUX_SBT_TAPE_1
    
87. channel ORA_AUX_SBT_TAPE_1: SID=83 device type=SBT_TAPE
    
88. channel ORA_AUX_SBT_TAPE_1: Oracle Secure Backup
    
89. allocated channel: ORA_AUX_SBT_TAPE_2
    
90. channel ORA_AUX_SBT_TAPE_2: SID=7 device type=SBT_TAPE
    
91. channel ORA_AUX_SBT_TAPE_2: Oracle Secure Backup
    
92. 
    
93. channel ORA_AUX_SBT_TAPE_1: starting datafile backup set restore
    
94. channel ORA_AUX_SBT_TAPE_1: restoring control file
    
95. channel ORA_AUX_SBT_TAPE_1: reading from backup piece c-1343950367-20170518-00
    
96. channel ORA_AUX_SBT_TAPE_1: piece handle=c-1343950367-20170518-00 tag=TAG20170518T164400
    
97. channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
    
98. channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:25
    
99. output file name=/u01/app/oracle/oradata/ORCL/controlfile/o1_mf_dwjxrcnh_.ctl
    
100. Finished restore at 2017-09-25:11:25:09
     
101. 
     
102. sql statement: alter database mount clone database
     
103. 
     
104. sql statement: alter system archive log current
     
105. 
     
106. sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;
     
107. 
     
108. starting full resync of recovery catalog
     
109. full resync complete
     
110. 
     
111. contents of Memory Script:
     
112. {
     
113. # set requested point in time
     
114. set until  time "2017-09-25:11:01:04";
     
115. # set destinations for recovery set and auxiliary set datafiles
     
116. set newname for clone datafile  1 to new;
     
117. set newname for clone datafile  3 to new;
     
118. set newname for clone datafile  2 to new;
     
119. set newname for clone tempfile  1 to new;
     
120. set newname for datafile  4 to
     
121. "+DATA/orcl/datafile/users.259.816169553";
     
122. # switch all tempfiles
     
123. switch clone tempfile all;
     
124. # restore the tablespaces in the recovery set and the auxiliary set
     
125. restore clone datafile  1, 3, 2, 4;
     
126. switch clone datafile all;
     
127. }
     
128. executing Memory Script
     
129. 
     
130. executing command: SET until clause
     
131. 
     
132. executing command: SET NEWNAME
     
133. 
     
134. executing command: SET NEWNAME
     
135. 
     
136. executing command: SET NEWNAME
     
137. 
     
138. executing command: SET NEWNAME
     
139. 
     
140. executing command: SET NEWNAME
     
141. 
     
142. renamed tempfile 1 to /u01/app/oracle/oradata/ORCL/datafile/o1_mf_temp_%u_.tmp in control file
     
143. 
     
144. Starting restore at 2017-09-25:11:25:16
     
145. using channel ORA_AUX_DISK_1
     
146. using channel ORA_AUX_DISK_2
     
147. using channel ORA_AUX_DISK_3
     
148. using channel ORA_AUX_DISK_4
     
149. using channel ORA_AUX_DISK_5
     
150. using channel ORA_AUX_DISK_6
     
151. using channel ORA_AUX_DISK_7
     
152. using channel ORA_AUX_DISK_8
     
153. using channel ORA_AUX_SBT_TAPE_1
     
154. using channel ORA_AUX_SBT_TAPE_2
     
155. 
     
156. channel ORA_AUX_SBT_TAPE_1: starting datafile backup set restore
     
157. channel ORA_AUX_SBT_TAPE_1: specifying datafile(s) to restore from backup set
     
158. channel ORA_AUX_SBT_TAPE_1: restoring datafile 00003 to /u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_%u_.dbf
     
159. channel ORA_AUX_SBT_TAPE_1: restoring datafile 00002 to /u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_%u_.dbf
     
160. channel ORA_AUX_SBT_TAPE_1: restoring datafile 00004 to +DATA/orcl/datafile/users.259.816169553
     
161. channel ORA_AUX_SBT_TAPE_1: reading from backup piece 13s4ih4j_1_1
     
162. channel ORA_AUX_SBT_TAPE_2: starting datafile backup set restore
     
163. channel ORA_AUX_SBT_TAPE_2: specifying datafile(s) to restore from backup set
     
164. channel ORA_AUX_SBT_TAPE_2: restoring datafile 00001 to /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_%u_.dbf
     
165. channel ORA_AUX_SBT_TAPE_2: reading from backup piece 12s4ih4j_1_1
     
166. channel ORA_AUX_SBT_TAPE_2: piece handle=12s4ih4j_1_1 tag=0T_INCR0_WHOLE
     
167. channel ORA_AUX_SBT_TAPE_2: restored backup piece 1
     
168. channel ORA_AUX_SBT_TAPE_2: restore complete, elapsed time: 00:00:25
     
169. channel ORA_AUX_SBT_TAPE_1: piece handle=13s4ih4j_1_1 tag=0T_INCR0_WHOLE
     
170. channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
     
171. channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:35
     
172. Finished restore at 2017-09-25:11:25:55
     
173. 
     
174. datafile 1 switched to datafile copy
     
175. input datafile copy RECID=5 STAMP=955625155 file name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_dwjxsjvk_.dbf
     
176. datafile 3 switched to datafile copy
     
177. input datafile copy RECID=6 STAMP=955625155 file name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_dwjxslyk_.dbf
     
178. datafile 2 switched to datafile copy
     
179. input datafile copy RECID=7 STAMP=955625155 file name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_dwjxslp9_.dbf
     
180. 
     
181. contents of Memory Script:
     
182. {
     
183. # set requested point in time
     
184. set until  time "2017-09-25:11:01:04";
     
185. # online the datafiles restored or switched
     
186. sql clone "alter database datafile  1 online";
     
187. sql clone "alter database datafile  3 online";
     
188. sql clone "alter database datafile  2 online";
     
189. sql clone "alter database datafile  4 online";
     
190. # recover and open resetlogs
     
191. recover clone database tablespace  "USERS", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;
     
192. alter clone database open resetlogs;
     
193. }
     
194. executing Memory Script
     
195. 
     
196. executing command: SET until clause
     
197. 
     
198. sql statement: alter database datafile  1 online
     
199. 
     
200. sql statement: alter database datafile  3 online
     
201. 
     
202. sql statement: alter database datafile  2 online
     
203. 
     
204. sql statement: alter database datafile  4 online
     
205. 
     
206. Starting recover at 2017-09-25:11:25:55
     
207. using channel ORA_AUX_DISK_1
     
208. using channel ORA_AUX_DISK_2
     
209. using channel ORA_AUX_DISK_3
     
210. using channel ORA_AUX_DISK_4
     
211. using channel ORA_AUX_DISK_5
     
212. using channel ORA_AUX_DISK_6
     
213. using channel ORA_AUX_DISK_7
     
214. using channel ORA_AUX_DISK_8
     
215. using channel ORA_AUX_SBT_TAPE_1
     
216. using channel ORA_AUX_SBT_TAPE_2
     
217. 
     
218. starting media recovery
     
219. 
     
220. archived log for thread 1 with sequence 20 is already on disk as file +FRA/orcl/archivelog/2017_09_25/thread_1_seq_20.262.955623659
     
221. archived log for thread 1 with sequence 21 is already on disk as file +FRA/orcl/archivelog/2017_09_25/thread_1_seq_21.261.955623659
     
222. archived log for thread 1 with sequence 22 is already on disk as file +FRA/orcl/archivelog/2017_09_25/thread_1_seq_22.263.955623671
     
223. channel ORA_AUX_SBT_TAPE_1: starting archived log restore to default destination
     
224. channel ORA_AUX_SBT_TAPE_1: restoring archived log
     
225. archived log thread=1 sequence=19
     
226. channel ORA_AUX_SBT_TAPE_1: reading from backup piece 14s4ih5n_1_1
     
227. channel ORA_AUX_SBT_TAPE_1: piece handle=14s4ih5n_1_1 tag=0T_INCR0_WHOLE
     
228. channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
     
229. channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:25
     
230. archived log file name=/u01/app/oracle/oradata/1_19_816169635.dbf thread=1 sequence=19
     
231. channel clone_default: deleting archived log(s)
     
232. archived log file name=/u01/app/oracle/oradata/1_19_816169635.dbf RECID=12 STAMP=955625173
     
233. archived log file name=+FRA/orcl/archivelog/2017_09_25/thread_1_seq_20.262.955623659 thread=1 sequence=20
     
234. archived log file name=+FRA/orcl/archivelog/2017_09_25/thread_1_seq_21.261.955623659 thread=1 sequence=21
     
235. archived log file name=+FRA/orcl/archivelog/2017_09_25/thread_1_seq_22.263.955623671 thread=1 sequence=22
     
236. media recovery complete, elapsed time: 00:00:05
     
237. Finished recover at 2017-09-25:11:26:28
     
238. 
     
239. database opened
     
240. 
     
241. contents of Memory Script:
     
242. {
     
243. # make read only the tablespace that will be exported
     
244. sql clone 'alter tablespace  USERS read only';
     
245. # create directory for datapump import
     
246. sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
     
247. /u01/app/oracle/oradata''";
     
248. # create directory for datapump export
     
249. sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
     
250. /u01/app/oracle/oradata''";
     
251. }
     
252. executing Memory Script
     
253. 
     
254. sql statement: alter tablespace  USERS read only
     
255. 
     
256. sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/app/oracle/oradata''
     
257. 
     
258. sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/app/oracle/oradata''
     
259. 
     
260. Performing export of metadata...
     
261.    EXPDP> Starting "SYS"."TSPITR_EXP_eEeq":  
     
262.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
     
263.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_SPEC
     
264.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_BODY
     
265.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/XMLSCHEMA/XMLSCHEMA
     
266.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
     
267.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX
     
268.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
     
269.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
     
270.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
     
271.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/TRIGGER
     
272.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/RLS_POLICY
     
273.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
     
274.    EXPDP> Master table "SYS"."TSPITR_EXP_eEeq" successfully loaded/unloaded
     
275.    EXPDP> ******************************************************************************
     
276.    EXPDP> Dump file set for SYS.TSPITR_EXP_eEeq is:
     
277.    EXPDP>   /u01/app/oracle/oradata/tspitr_eEeq_35248.dmp
     
278.    EXPDP> ******************************************************************************
     
279.    EXPDP> Datafiles required for transportable tablespace USERS:
     
280.    EXPDP>   +DATA/orcl/datafile/users.259.816169553
     
281.    EXPDP> Job "SYS"."TSPITR_EXP_eEeq" successfully completed at 11:27:22
     
282. Export completed
     
283. 
     
284. 
     
285. contents of Memory Script:
     
286. {
     
287. # shutdown clone before import
     
288. shutdown clone immediate
     
289. # drop target tablespaces before importing them back
     
290. sql 'drop tablespace  USERS including contents keep datafiles';
     
291. }
     
292. executing Memory Script
     
293. 
     
294. database closed
     
295. database dismounted
     
296. Oracle instance shut down
     
297. 
     
298. sql statement: drop tablespace  USERS including contents keep datafiles
     
299. 
     
300. Performing import of metadata...
     
301.    IMPDP> Master table "SYS"."TSPITR_IMP_eEeq" successfully loaded/unloaded
     
302.    IMPDP> Starting "SYS"."TSPITR_IMP_eEeq":  
     
303.    IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
     
304.    IMPDP> Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_SPEC
     
305. 
     
306. Removing automatic instance
     
307. Automatic instance removed
     
308. auxiliary instance file /u01/app/oracle/oradata/ORCL/datafile/o1_mf_temp_dwjxv6jw_.tmp deleted
     
309. auxiliary instance file /u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_3_dwjxv5dz_.log deleted
     
310. auxiliary instance file /u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_2_dwjxv57n_.log deleted
     
311. auxiliary instance file /u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_1_dwjxv528_.log deleted
     
312. auxiliary instance file /u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_dwjxslp9_.dbf deleted
     
313. auxiliary instance file /u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_dwjxslyk_.dbf deleted
     
314. auxiliary instance file /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_dwjxsjvk_.dbf deleted
     
315. auxiliary instance file /u01/app/oracle/oradata/ORCL/controlfile/o1_mf_dwjxrcnh_.ctl deleted
     
316. RMAN-00571: ===========================================================
     
317. RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
     
318. RMAN-00571: ===========================================================
     
319. RMAN-03002: failure of recover command at 09/25/2017 11:27:41
     
320. RMAN-06963: Error received during import of metadata
     
321. RMAN-06961:    IMPDP> ORA-31684: Object type TYPE:"OE"."SHIPPING_INSTRUCTIONS_T" already existsORA-31684: Object type TYPE:"OE"."REJECTION_T" already existsORA-31684: Object type TYPE:"OE"."ACTION_T" already existsORA-31684: Object type TYPE:"OE"."PART_T" already existsORA-31684: Object type TYPE:"OE"."PRODUCT_REF_LIST_TYP" already existsORA-31684: Object type TYPE:"OE"."CATEGORY_TYP" already existsORA-31684: Object type TYPE:"OE"."SUBCATEGORY_REF_LIST_TYP" already existsORA-31684: Object type TYPE:"OE"."ACTION_V" already exists
     
322. 
     
323. RMAN>
     

复制代码

1. 
   
2. ---------------------------------------------- LAB 5 ----------------------------------------------
   
3.              5_基于时间的不完全恢复
   
4. ---------------------------------------------- LAB 5 ----------------------------------------------
   
5. 
   
6. 
   
7. ******************
   
8. 你的主机信息如下：
   
9. ******************
   
10. 
    
11. 
    
12. station90.example.com
    
13. 
    
14. Linux station90.example.com 2.6.32-696.el6.x86_64 #1 SMP Tue Feb 21 00:53:17 EST 2017 x86_64 x86_64 x86_64 GNU/Linux
    
15. 
    
16. 
    
17. Name           Type           Target    State     Host        
    
18. ------------------------------------------------------------
    
19. ora.DATA.dg    ora....up.type ONLINE    ONLINE    station90   
    
20. ora.FRA.dg     ora....up.type ONLINE    ONLINE    station90   
    
21. ora....ER.lsnr ora....er.type ONLINE    ONLINE    station90   
    
22. ora.asm        ora.asm.type   ONLINE    ONLINE    station90   
    
23. ora.cssd       ora.cssd.type  ONLINE    ONLINE    station90   
    
24. ora.diskmon    ora....on.type ONLINE    ONLINE    station90   
    
25. ora.orcl.db    ora....se.type ONLINE    ONLINE    station90   
    
26. 
    
27. SQL*Plus: Release 11.2.0.1.0 Production on Mon Sep 25 11:29:46 2017
    
28. 
    
29. Copyright (c) 1982, 2009, Oracle.  All rights reserved.
    
30. 
    
31. SQL> Connected.
    
32. SQL> SQL> SQL>
    
33. A
    
34. --------------------
    
35. Botang
    
36. ****************************
    
37. The database open mode here:
    
38. ****************************
    
39. 
    
40. D
    
41. -
    
42. X
    
43. 
    
44. OPEN_MODE
    
45. --------------------
    
46. READ WRITE
    
47. **********************************
    
48. The database archivelog mode here:
    
49. **********************************
    
50. Database log mode               Archive Mode
    
51. Automatic archival               Enabled
    
52. Archive destination               USE_DB_RECOVERY_FILE_DEST
    
53. Oldest online log sequence     29
    
54. Next log sequence to archive   31
    
55. Current log sequence               31
    
56. ***************************
    
57. The database datafile here:
    
58. ***************************
    
59. 
    
60. TABLESPACE_NAME                STATUS
    
61. ------------------------------ ---------------
    
62. SYSTEM                               ONLINE
    
63. SYSAUX                               ONLINE
    
64. UNDOTBS1                       ONLINE
    
65. TEMP                               ONLINE
    
66. USERS                               READ ONLY
    
67. EXAMPLE                        ONLINE
    
68. 
    
69. NAME                                                                  STATUS
    
70. ----------------------------------------------------------------- ---------------
    
71. +DATA/orcl/datafile/system.256.816169553                          SYSTEM
    
72. +DATA/orcl/datafile/sysaux.257.816169553                          ONLINE
    
73. +DATA/orcl/datafile/undotbs1.258.816169553                          ONLINE
    
74. +DATA/orcl/datafile/users.259.816169553                           ONLINE
    
75. +DATA/orcl/datafile/example.265.816169651                          ONLINE
    
76. ******************************
    
77. The database controlfile here:
    
78. ******************************
    
79. 
    
80. NAME
    
81. -----------------------------------------------------------------
    
82. +DATA/orcl/controlfile/current.260.816169631
    
83. +FRA/orcl/controlfile/current.256.816169633
    
84. **************************
    
85. The database logfile here:
    
86. **************************
    
87. 
    
88. MEMBER
    
89. --------------------------------------------------------------------------------
    
90. +DATA/orcl/onlinelog/group_3.263.816169641
    
91. +FRA/orcl/onlinelog/group_3.259.816169641
    
92. +DATA/orcl/onlinelog/group_2.262.816169639
    
93. +FRA/orcl/onlinelog/group_2.258.816169639
    
94. +DATA/orcl/onlinelog/group_1.261.816169635
    
95. +FRA/orcl/onlinelog/group_1.257.816169637
    
96. **************************
    
97. The database tempfile here:
    
98. **************************
    
99. 
    
100. NAME
     
101. --------------------------------------------------------------------------------
     
102. +DATA/orcl/tempfile/temp.264.816169645
     
103. *****************
     
104. The values found:
     
105. *****************
     
106. 
     
107.          A
     
108. ----------
     
109.          1
     
110. SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
     
111. With the Partitioning, Automatic Storage Management, OLAP, Data Mining
     
112. and Real Application Testing options
     
113. 
     
114. THE END
     

复制代码