设为首页收藏本站

Botang唐波's Oracle Station

查看: 66|回复: 0

课程第19次(2018-07-23星期一)

[复制链接]

708

主题

1070

帖子

7759

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
7759
发表于 2018-7-23 19:46:12 | 显示全部楼层 |阅读模式
图形界面做Dataguard请务必验证一下:

a.png

在转换最大可用模式之前,必须验证通过:
b.png

主库和备库的属性都要改:
c.png

TSPITR:
做自包含集检查,并处理:
  1. [oracle@station90 ~]$ sqlplus /nolog

  2. SQL*Plus: Release 11.2.0.3.0 Production on Mon Jul 23 20:27:34 2018

  3. Copyright (c) 1982, 2011, Oracle.  All rights reserved.

  4. SQL> conn / as sysdba
  5. Connected.
  6. SQL> exec dbms_tts.transport_set_check('USERS') ;

  7. PL/SQL procedure successfully completed.

  8. SQL> set linesize 1000
  9. SQL> select  * from transport_set_violations;

  10. VIOLATIONS
  11. ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  12. ORA-39929: Table HR.FUNC_SPEC in tablespace USERS has encrypted columns which are not supported.
  13. ORA-39929: Table HR.T_PIC in tablespace USERS has encrypted columns which are not supported.
  14. ORA-39905: Table HR.SYS_LOB0000080641C00002$ in tablespace EXAMPLE points to LOB segment HR.T_PIC in tablespace USERS.
  15. ORA-39905: Table HR.SYS_LOB0000080631C00002$ in tablespace EXAMPLE points to LOB segment HR.T_PIC1 in tablespace USERS.

  16. SQL> alter table HR.FUNC_SPEC  move tablespace example;

  17. Table altered.

  18. SQL> alter table hr.T_PIC  move tablespace example;

  19. Table altered.

  20. SQL> exec dbms_tts.transport_set_check('USERS') ;

  21. PL/SQL procedure successfully completed.

  22. SQL> select  * from transport_set_violations;

  23. VIOLATIONS
  24. ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  25. ORA-39907: Index HR.SYS_IL0000080644C00002$ in tablespace USERS points to table HR.FUNC_SPEC in tablespace EXAMPLE.
  26. ORA-39905: Table HR.FUNC_SPEC in tablespace EXAMPLE points to LOB segment HR.SYS_LOB0000080644C00002$ in tablespace USERS.
  27. ORA-39905: Table HR.SYS_LOB0000080631C00002$ in tablespace EXAMPLE points to LOB segment HR.T_PIC1 in tablespace USERS.

  28. SQL> alter table HR.FUNC_SPEC  move tablespace example;

  29. Table altered.

  30. SQL> exec dbms_tts.transport_set_check('USERS') ;

  31. PL/SQL procedure successfully completed.

  32. SQL> select  * from transport_set_violations;

  33. VIOLATIONS
  34. ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  35. ORA-39907: Index HR.SYS_IL0000080644C00002$ in tablespace USERS points to table HR.FUNC_SPEC in tablespace EXAMPLE.
  36. ORA-39905: Table HR.FUNC_SPEC in tablespace EXAMPLE points to LOB segment HR.SYS_LOB0000080644C00002$ in tablespace USERS.
  37. ORA-39905: Table HR.SYS_LOB0000080631C00002$ in tablespace EXAMPLE points to LOB segment HR.T_PIC1 in tablespace USERS.


  38. SQL> alter table HR.FUNC_SPEC   move    lob (doc )  store as  ( tablespace example)   ;
  39. alter table HR.FUNC_SPEC   move    lob (doc )  store as  ( tablespace example)
  40.                *
  41. ERROR at line 1:
  42. ORA-28365: wallet is not open


  43. SQL> alter system set encryption wallet open identified by "oracle123";

  44. System altered.

  45. SQL> alter table HR.FUNC_SPEC   move    lob (doc )  store as  ( tablespace example)   ;

  46. Table altered.

  47. SQL> exec dbms_tts.transport_set_check('USERS') ;

  48. PL/SQL procedure successfully completed.

  49. SQL> select  * from transport_set_violations;

  50. VIOLATIONS
  51. ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  52. ORA-39905: Table HR.SYS_LOB0000080631C00002$ in tablespace EXAMPLE points to LOB segment HR.T_PIC1 in tablespace USERS.

  53. SQL> alter table  HR.T_PIC1   move lob (pic)   store as  ( tablespace example)   ;

  54. Table altered.

  55. SQL> exec dbms_tts.transport_set_check('USERS') ;

  56. PL/SQL procedure successfully completed.

  57. SQL> select  * from transport_set_violations;

  58. VIOLATIONS
  59. ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  60. ORA-39905: Table HR.SYS_LOB0000080631C00002$ in tablespace EXAMPLE points to LOB segment HR.T_PIC1 in tablespace USERS.

  61. SQL> alter table HR.T_PIC1 move tablespace example;

  62. Table altered.

  63. SQL> exec dbms_tts.transport_set_check('USERS') ;

  64. PL/SQL procedure successfully completed.

  65. SQL> select  * from transport_set_violations;

  66. no rows selected

  67. SQL>
  68. SQL> alter database default tablespace example;

  69. Database altered.

  70. SQL> select table_name  , owner from dba_Tables where tablespace_name='USERS';

  71. TABLE_NAME                       OWNER
  72. ------------------------------ ------------------------------
  73. TTESTBOTANG_5                       SYS
  74. DEPT                               SCOTT
  75. EMP                               SCOTT
  76. BONUS                               SCOTT
  77. SALGRADE                       SCOTT
  78. MLOG$_TAB1                       HR
  79. T24351                               HR
  80. PRODUCT_REF_LIST_NESTEDTAB     OE
  81. SUBCATEGORY_REF_LIST_NESTEDTAB OE
  82. DIMENSION_EXCEPTIONS               SH

  83. 10 rows selected.

  84. SQL> alter table SYS.TTESTBOTANG_5  move tablespace system ;

  85. Table altered.

  86. SQL> select table_name  , owner from dba_Tables where tablespace_name='USERS';

  87. TABLE_NAME                       OWNER
  88. ------------------------------ ------------------------------
  89. DEPT                               SCOTT
  90. EMP                               SCOTT
  91. BONUS                               SCOTT
  92. SALGRADE                       SCOTT
  93. MLOG$_TAB1                       HR
  94. T24351                               HR
  95. PRODUCT_REF_LIST_NESTEDTAB     OE
  96. SUBCATEGORY_REF_LIST_NESTEDTAB OE
  97. DIMENSION_EXCEPTIONS               SH

  98. 9 rows selected.

  99. SQL>
复制代码
查询物化视图:
  1. select * from dba_mviews  mv
  2. where mv.MVIEW_NAME in (
  3. select s.segment_name from dba_segments s
  4. where  s.tablespace_name='USERS'
  5. );
复制代码
  1. drop materialized view log on  hr.TAB1;
复制代码



自动克隆了一个实例:
  1. [root@station90 ~]# ps aux | grep ora_smon
  2. oracle   15057  0.0  0.2 523720 40584 ?        Ss   20:43   0:00 ora_smon_Agrx
  3. root     15352  0.0  0.0 103308  2032 pts/11   S+   20:45   0:00 grep ora_smon
  4. oracle   27168  0.0  1.2 6795216 200232 ?      Ss   20:11   0:00 ora_smon_orcl
复制代码

TSPITR:
  1. [oracle@station90 ~]$ export NLS_DATE_FORMAT='YYYY-MM-DD:HH24:MI:SS'
  2. [oracle@station90 ~]$ rman target /

  3. Recovery Manager: Release 11.2.0.3.0 - Production on Mon Jul 23 21:44:23 2018

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

  5. connected to target database: ORCL (DBID=1343950367)

  6. RMAN> recover tablespace users
  7. 2> until time '2018-07-23:21:43:18'
  8. 3> auxiliary destination '/u01/app/oracle/oradata';

  9. Starting recover at 2018-07-23:21:45:03
  10. using target database control file instead of recovery catalog
  11. allocated channel: ORA_DISK_1
  12. channel ORA_DISK_1: SID=78 device type=DISK
  13. allocated channel: ORA_DISK_2
  14. channel ORA_DISK_2: SID=9 device type=DISK
  15. allocated channel: ORA_DISK_3
  16. channel ORA_DISK_3: SID=76 device type=DISK
  17. allocated channel: ORA_DISK_4
  18. channel ORA_DISK_4: SID=143 device type=DISK
  19. allocated channel: ORA_DISK_5
  20. channel ORA_DISK_5: SID=202 device type=DISK
  21. allocated channel: ORA_DISK_6
  22. channel ORA_DISK_6: SID=11 device type=DISK
  23. allocated channel: ORA_DISK_7
  24. channel ORA_DISK_7: SID=73 device type=DISK
  25. allocated channel: ORA_DISK_8
  26. channel ORA_DISK_8: SID=14 device type=DISK
  27. allocated channel: ORA_SBT_TAPE_1
  28. channel ORA_SBT_TAPE_1: SID=71 device type=SBT_TAPE
  29. channel ORA_SBT_TAPE_1: Oracle Secure Backup
  30. allocated channel: ORA_SBT_TAPE_2
  31. channel ORA_SBT_TAPE_2: SID=75 device type=SBT_TAPE
  32. channel ORA_SBT_TAPE_2: Oracle Secure Backup
  33. RMAN-05026: WARNING: presuming following set of tablespaces applies to specified point-in-time

  34. List of tablespaces expected to have UNDO segments
  35. Tablespace SYSTEM
  36. Tablespace UNDOTBS1

  37. Creating automatic instance, with SID='BcBD'

  38. initialization parameters used for automatic instance:
  39. db_name=ORCL
  40. db_unique_name=BcBD_tspitr_ORCL
  41. compatible=11.2.0.0.0
  42. db_block_size=8192
  43. db_files=200
  44. sga_target=280M
  45. processes=50
  46. db_create_file_dest=/u01/app/oracle/oradata
  47. log_archive_dest_1='location=/u01/app/oracle/oradata'
  48. #No auxiliary parameter file used


  49. starting up automatic instance ORCL

  50. Oracle instance started

  51. Total System Global Area     292278272 bytes

  52. Fixed Size                     2227744 bytes
  53. Variable Size                100663776 bytes
  54. Database Buffers             184549376 bytes
  55. Redo Buffers                   4837376 bytes
  56. Automatic instance created
  57. Running TRANSPORT_SET_CHECK on recovery set tablespaces
  58. TRANSPORT_SET_CHECK completed successfully

  59. contents of Memory Script:
  60. {
  61. # set requested point in time
  62. set until  time "2018-07-23:21:43:18";
  63. # restore the controlfile
  64. restore clone controlfile;
  65. # mount the controlfile
  66. sql clone 'alter database mount clone database';
  67. # archive current online log
  68. sql 'alter system archive log current';
  69. # avoid unnecessary autobackups for structural changes during TSPITR
  70. sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
  71. }
  72. executing Memory Script

  73. executing command: SET until clause

  74. Starting restore at 2018-07-23:21:45:50
  75. allocated channel: ORA_AUX_DISK_1
  76. channel ORA_AUX_DISK_1: SID=80 device type=DISK
  77. allocated channel: ORA_AUX_DISK_2
  78. channel ORA_AUX_DISK_2: SID=5 device type=DISK
  79. allocated channel: ORA_AUX_DISK_3
  80. channel ORA_AUX_DISK_3: SID=30 device type=DISK
  81. allocated channel: ORA_AUX_DISK_4
  82. channel ORA_AUX_DISK_4: SID=56 device type=DISK
  83. allocated channel: ORA_AUX_DISK_5
  84. channel ORA_AUX_DISK_5: SID=81 device type=DISK
  85. allocated channel: ORA_AUX_DISK_6
  86. channel ORA_AUX_DISK_6: SID=6 device type=DISK
  87. allocated channel: ORA_AUX_DISK_7
  88. channel ORA_AUX_DISK_7: SID=31 device type=DISK
  89. allocated channel: ORA_AUX_DISK_8
  90. channel ORA_AUX_DISK_8: SID=57 device type=DISK
  91. allocated channel: ORA_AUX_SBT_TAPE_1
  92. channel ORA_AUX_SBT_TAPE_1: SID=82 device type=SBT_TAPE
  93. channel ORA_AUX_SBT_TAPE_1: Oracle Secure Backup
  94. allocated channel: ORA_AUX_SBT_TAPE_2
  95. channel ORA_AUX_SBT_TAPE_2: SID=7 device type=SBT_TAPE
  96. channel ORA_AUX_SBT_TAPE_2: Oracle Secure Backup

  97. channel ORA_AUX_DISK_1: starting datafile backup set restore
  98. channel ORA_AUX_DISK_1: restoring control file
  99. channel ORA_AUX_DISK_1: reading from backup piece +FRA/orcl/autobackup/2018_07_23/s_982272726.326.982272727
  100. channel ORA_AUX_DISK_1: piece handle=+FRA/orcl/autobackup/2018_07_23/s_982272726.326.982272727 tag=TAG20180723T213205
  101. channel ORA_AUX_DISK_1: restored backup piece 1
  102. channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
  103. output file name=/u01/app/oracle/oradata/ORCL/controlfile/o1_mf_focq0r3q_.ctl
  104. Finished restore at 2018-07-23:21:46:00

  105. sql statement: alter database mount clone database

  106. sql statement: alter system archive log current

  107. sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;

  108. contents of Memory Script:
  109. {
  110. # set requested point in time
  111. set until  time "2018-07-23:21:43:18";
  112. plsql <<<-- tspitr_2
  113. declare
  114.   sqlstatement       varchar2(512);
  115.   offline_not_needed exception;
  116.   pragma exception_init(offline_not_needed, -01539);
  117. begin
  118.   sqlstatement := 'alter tablespace '||  'USERS' ||' offline immediate';
  119.   krmicd.writeMsg(6162, sqlstatement);
  120.   krmicd.execSql(sqlstatement);
  121. exception
  122.   when offline_not_needed then
  123.     null;
  124. end; >>>;
  125. # set destinations for recovery set and auxiliary set datafiles
  126. set newname for clone datafile  1 to new;
  127. set newname for clone datafile  3 to new;
  128. set newname for clone datafile  2 to new;
  129. set newname for clone tempfile  1 to new;
  130. set newname for datafile  4 to
  131. "+DATA/orcl/datafile/users.259.816169553";
  132. # switch all tempfiles
  133. switch clone tempfile all;
  134. # restore the tablespaces in the recovery set and the auxiliary set
  135. restore clone datafile  1, 3, 2, 4;
  136. switch clone datafile all;
  137. }
  138. executing Memory Script

  139. executing command: SET until clause

  140. sql statement: alter tablespace USERS offline immediate

  141. executing command: SET NEWNAME

  142. executing command: SET NEWNAME

  143. executing command: SET NEWNAME

  144. executing command: SET NEWNAME

  145. executing command: SET NEWNAME

  146. renamed tempfile 1 to /u01/app/oracle/oradata/ORCL/datafile/o1_mf_temp_%u_.tmp in control file

  147. Starting restore at 2018-07-23:21:46:06
  148. using channel ORA_AUX_DISK_1
  149. using channel ORA_AUX_DISK_2
  150. using channel ORA_AUX_DISK_3
  151. using channel ORA_AUX_DISK_4
  152. using channel ORA_AUX_DISK_5
  153. using channel ORA_AUX_DISK_6
  154. using channel ORA_AUX_DISK_7
  155. using channel ORA_AUX_DISK_8
  156. using channel ORA_AUX_SBT_TAPE_1
  157. using channel ORA_AUX_SBT_TAPE_2

  158. channel ORA_AUX_SBT_TAPE_1: starting datafile backup set restore
  159. channel ORA_AUX_SBT_TAPE_1: specifying datafile(s) to restore from backup set
  160. channel ORA_AUX_SBT_TAPE_1: restoring datafile 00001 to /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_%u_.dbf
  161. channel ORA_AUX_SBT_TAPE_1: reading from backup piece 1rt88jb3_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 00003 to /u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_%u_.dbf
  165. channel ORA_AUX_SBT_TAPE_2: restoring datafile 00002 to /u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_%u_.dbf
  166. channel ORA_AUX_SBT_TAPE_2: restoring datafile 00004 to +DATA/orcl/datafile/users.259.816169553
  167. channel ORA_AUX_SBT_TAPE_2: reading from backup piece 1qt88jb3_1_1
  168. channel ORA_AUX_SBT_TAPE_1: piece handle=1rt88jb3_1_1 tag=100T_WHOLE_FULL
  169. channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
  170. channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:35
  171. channel ORA_AUX_SBT_TAPE_2: piece handle=1qt88jb3_1_1 tag=100T_WHOLE_FULL
  172. channel ORA_AUX_SBT_TAPE_2: restored backup piece 1
  173. channel ORA_AUX_SBT_TAPE_2: restore complete, elapsed time: 00:00:35
  174. Finished restore at 2018-07-23:21:46:42

  175. datafile 1 switched to datafile copy
  176. input datafile copy RECID=8 STAMP=982273603 file name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_focq1l57_.dbf
  177. datafile 3 switched to datafile copy
  178. input datafile copy RECID=9 STAMP=982273603 file name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_focq1q5x_.dbf
  179. datafile 2 switched to datafile copy
  180. input datafile copy RECID=10 STAMP=982273603 file name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_focq1q59_.dbf

  181. contents of Memory Script:
  182. {
  183. # set requested point in time
  184. set until  time "2018-07-23:21:43:18";
  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. executing command: SET until clause

  196. sql statement: alter database datafile  1 online

  197. sql statement: alter database datafile  3 online

  198. sql statement: alter database datafile  2 online

  199. sql statement: alter database datafile  4 online

  200. Starting recover at 2018-07-23:21:46:43
  201. using channel ORA_AUX_DISK_1
  202. using channel ORA_AUX_DISK_2
  203. using channel ORA_AUX_DISK_3
  204. using channel ORA_AUX_DISK_4
  205. using channel ORA_AUX_DISK_5
  206. using channel ORA_AUX_DISK_6
  207. using channel ORA_AUX_DISK_7
  208. using channel ORA_AUX_DISK_8
  209. using channel ORA_AUX_SBT_TAPE_1
  210. using channel ORA_AUX_SBT_TAPE_2

  211. starting media recovery

  212. archived log for thread 1 with sequence 160 is already on disk as file +FRA/orcl/archivelog/2018_07_17/thread_1_seq_160.283.981749477
  213. archived log for thread 1 with sequence 161 is already on disk as file +FRA/orcl/archivelog/2018_07_17/thread_1_seq_161.284.981749479
  214. archived log for thread 1 with sequence 162 is already on disk as file +FRA/orcl/archivelog/2018_07_17/thread_1_seq_162.285.981749481
  215. archived log for thread 1 with sequence 163 is already on disk as file +FRA/orcl/archivelog/2018_07_17/thread_1_seq_163.286.981749481
  216. archived log for thread 1 with sequence 164 is already on disk as file +FRA/orcl/archivelog/2018_07_17/thread_1_seq_164.287.981749483
  217. archived log for thread 1 with sequence 165 is already on disk as file +FRA/orcl/archivelog/2018_07_17/thread_1_seq_165.288.981749485
  218. archived log for thread 1 with sequence 1 is already on disk as file +FRA/orcl/archivelog/2018_07_19/thread_1_seq_1.292.981927643
  219. archived log for thread 1 with sequence 2 is already on disk as file +FRA/orcl/archivelog/2018_07_22/thread_1_seq_2.293.982145289
  220. archived log for thread 1 with sequence 3 is already on disk as file +FRA/orcl/archivelog/2018_07_22/thread_1_seq_3.294.982145393
  221. archived log for thread 1 with sequence 4 is already on disk as file +FRA/orcl/archivelog/2018_07_22/thread_1_seq_4.295.982159249
  222. archived log for thread 1 with sequence 5 is already on disk as file +FRA/orcl/archivelog/2018_07_23/thread_1_seq_5.296.982267971
  223. archived log for thread 1 with sequence 6 is already on disk as file +FRA/orcl/archivelog/2018_07_23/thread_1_seq_6.297.982268257
  224. archived log for thread 1 with sequence 7 is already on disk as file +FRA/orcl/archivelog/2018_07_23/thread_1_seq_7.298.982268257
  225. archived log for thread 1 with sequence 8 is already on disk as file +FRA/orcl/archivelog/2018_07_23/thread_1_seq_8.299.982268269
  226. archived log for thread 1 with sequence 9 is already on disk as file +FRA/orcl/archivelog/2018_07_23/thread_1_seq_9.300.982268269
  227. archived log for thread 1 with sequence 10 is already on disk as file +FRA/orcl/archivelog/2018_07_23/thread_1_seq_10.301.982268271
  228. archived log for thread 1 with sequence 11 is already on disk as file +FRA/orcl/archivelog/2018_07_23/thread_1_seq_11.302.982268275
  229. archived log for thread 1 with sequence 12 is already on disk as file +FRA/orcl/archivelog/2018_07_23/thread_1_seq_12.303.982269845
  230. archived log for thread 1 with sequence 13 is already on disk as file +FRA/orcl/archivelog/2018_07_23/thread_1_seq_13.304.982270181
  231. archived log for thread 1 with sequence 14 is already on disk as file +FRA/orcl/archivelog/2018_07_23/thread_1_seq_14.305.982270719
  232. archived log for thread 1 with sequence 15 is already on disk as file +FRA/orcl/archivelog/2018_07_23/thread_1_seq_15.306.982270721
  233. archived log for thread 1 with sequence 16 is already on disk as file +FRA/orcl/archivelog/2018_07_23/thread_1_seq_16.307.982270729
  234. archived log for thread 1 with sequence 17 is already on disk as file +FRA/orcl/archivelog/2018_07_23/thread_1_seq_17.308.982270729
  235. archived log for thread 1 with sequence 18 is already on disk as file +FRA/orcl/archivelog/2018_07_23/thread_1_seq_18.309.982270729
  236. archived log for thread 1 with sequence 19 is already on disk as file +FRA/orcl/archivelog/2018_07_23/thread_1_seq_19.310.982270731
  237. archived log for thread 1 with sequence 20 is already on disk as file +FRA/orcl/archivelog/2018_07_23/thread_1_seq_20.311.982270909
  238. archived log for thread 1 with sequence 21 is already on disk as file +FRA/orcl/archivelog/2018_07_23/thread_1_seq_21.313.982272133
  239. archived log for thread 1 with sequence 22 is already on disk as file +FRA/orcl/archivelog/2018_07_23/thread_1_seq_22.314.982272133
  240. archived log for thread 1 with sequence 23 is already on disk as file +FRA/orcl/archivelog/2018_07_23/thread_1_seq_23.315.982272145
  241. archived log for thread 1 with sequence 24 is already on disk as file +FRA/orcl/archivelog/2018_07_23/thread_1_seq_24.316.982272151
  242. archived log for thread 1 with sequence 25 is already on disk as file +FRA/orcl/archivelog/2018_07_23/thread_1_seq_25.317.982272151
  243. archived log for thread 1 with sequence 26 is already on disk as file +FRA/orcl/archivelog/2018_07_23/thread_1_seq_26.318.982272153
  244. archived log for thread 1 with sequence 27 is already on disk as file +FRA/orcl/archivelog/2018_07_23/thread_1_seq_27.319.982272459
  245. archived log for thread 1 with sequence 28 is already on disk as file +FRA/orcl/archivelog/2018_07_23/thread_1_seq_28.320.982272459
  246. archived log for thread 1 with sequence 29 is already on disk as file +FRA/orcl/archivelog/2018_07_23/thread_1_seq_29.321.982272469
  247. archived log for thread 1 with sequence 30 is already on disk as file +FRA/orcl/archivelog/2018_07_23/thread_1_seq_30.322.982272473
  248. archived log for thread 1 with sequence 31 is already on disk as file +FRA/orcl/archivelog/2018_07_23/thread_1_seq_31.323.982272473
  249. archived log for thread 1 with sequence 32 is already on disk as file +FRA/orcl/archivelog/2018_07_23/thread_1_seq_32.324.982272475
  250. archived log for thread 1 with sequence 33 is already on disk as file +FRA/orcl/archivelog/2018_07_23/thread_1_seq_33.325.982272615
  251. archived log for thread 1 with sequence 34 is already on disk as file +FRA/orcl/archivelog/2018_07_23/thread_1_seq_34.327.982273393
  252. archived log for thread 1 with sequence 35 is already on disk as file +FRA/orcl/archivelog/2018_07_23/thread_1_seq_35.328.982273393
  253. archived log for thread 1 with sequence 36 is already on disk as file +FRA/orcl/archivelog/2018_07_23/thread_1_seq_36.333.982273403
  254. channel ORA_AUX_SBT_TAPE_1: starting archived log restore to default destination
  255. channel ORA_AUX_SBT_TAPE_1: restoring archived log
  256. archived log thread=1 sequence=159
  257. channel ORA_AUX_SBT_TAPE_1: reading from backup piece 1st88jbu_1_1
  258. channel ORA_AUX_SBT_TAPE_1: piece handle=1st88jbu_1_1 tag=100T_WHOLE_FULL
  259. channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
  260. channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:22
  261. archived log file name=/u01/app/oracle/oradata/1_159_816169635.dbf thread=1 sequence=159
  262. channel clone_default: deleting archived log(s)
  263. archived log file name=/u01/app/oracle/oradata/1_159_816169635.dbf RECID=194 STAMP=982273621
  264. archived log file name=+FRA/orcl/archivelog/2018_07_17/thread_1_seq_160.283.981749477 thread=1 sequence=160
  265. archived log file name=+FRA/orcl/archivelog/2018_07_17/thread_1_seq_161.284.981749479 thread=1 sequence=161
  266. archived log file name=+FRA/orcl/archivelog/2018_07_17/thread_1_seq_162.285.981749481 thread=1 sequence=162
  267. archived log file name=+FRA/orcl/archivelog/2018_07_17/thread_1_seq_163.286.981749481 thread=1 sequence=163
  268. archived log file name=+FRA/orcl/archivelog/2018_07_17/thread_1_seq_164.287.981749483 thread=1 sequence=164
  269. archived log file name=+FRA/orcl/archivelog/2018_07_17/thread_1_seq_165.288.981749485 thread=1 sequence=165
  270. archived log file name=+FRA/orcl/archivelog/2018_07_19/thread_1_seq_1.292.981927643 thread=1 sequence=1
  271. archived log file name=+FRA/orcl/archivelog/2018_07_22/thread_1_seq_2.293.982145289 thread=1 sequence=2
  272. archived log file name=+FRA/orcl/archivelog/2018_07_22/thread_1_seq_3.294.982145393 thread=1 sequence=3
  273. archived log file name=+FRA/orcl/archivelog/2018_07_22/thread_1_seq_4.295.982159249 thread=1 sequence=4
  274. archived log file name=+FRA/orcl/archivelog/2018_07_23/thread_1_seq_5.296.982267971 thread=1 sequence=5
  275. archived log file name=+FRA/orcl/archivelog/2018_07_23/thread_1_seq_6.297.982268257 thread=1 sequence=6
  276. archived log file name=+FRA/orcl/archivelog/2018_07_23/thread_1_seq_7.298.982268257 thread=1 sequence=7
  277. archived log file name=+FRA/orcl/archivelog/2018_07_23/thread_1_seq_8.299.982268269 thread=1 sequence=8
  278. archived log file name=+FRA/orcl/archivelog/2018_07_23/thread_1_seq_9.300.982268269 thread=1 sequence=9
  279. archived log file name=+FRA/orcl/archivelog/2018_07_23/thread_1_seq_10.301.982268271 thread=1 sequence=10
  280. archived log file name=+FRA/orcl/archivelog/2018_07_23/thread_1_seq_11.302.982268275 thread=1 sequence=11
  281. archived log file name=+FRA/orcl/archivelog/2018_07_23/thread_1_seq_12.303.982269845 thread=1 sequence=12
  282. archived log file name=+FRA/orcl/archivelog/2018_07_23/thread_1_seq_13.304.982270181 thread=1 sequence=13
  283. archived log file name=+FRA/orcl/archivelog/2018_07_23/thread_1_seq_14.305.982270719 thread=1 sequence=14
  284. archived log file name=+FRA/orcl/archivelog/2018_07_23/thread_1_seq_15.306.982270721 thread=1 sequence=15
  285. archived log file name=+FRA/orcl/archivelog/2018_07_23/thread_1_seq_16.307.982270729 thread=1 sequence=16
  286. archived log file name=+FRA/orcl/archivelog/2018_07_23/thread_1_seq_17.308.982270729 thread=1 sequence=17
  287. archived log file name=+FRA/orcl/archivelog/2018_07_23/thread_1_seq_18.309.982270729 thread=1 sequence=18
  288. archived log file name=+FRA/orcl/archivelog/2018_07_23/thread_1_seq_19.310.982270731 thread=1 sequence=19
  289. archived log file name=+FRA/orcl/archivelog/2018_07_23/thread_1_seq_20.311.982270909 thread=1 sequence=20
  290. archived log file name=+FRA/orcl/archivelog/2018_07_23/thread_1_seq_21.313.982272133 thread=1 sequence=21
  291. archived log file name=+FRA/orcl/archivelog/2018_07_23/thread_1_seq_22.314.982272133 thread=1 sequence=22
  292. archived log file name=+FRA/orcl/archivelog/2018_07_23/thread_1_seq_23.315.982272145 thread=1 sequence=23
  293. archived log file name=+FRA/orcl/archivelog/2018_07_23/thread_1_seq_24.316.982272151 thread=1 sequence=24
  294. archived log file name=+FRA/orcl/archivelog/2018_07_23/thread_1_seq_25.317.982272151 thread=1 sequence=25
  295. archived log file name=+FRA/orcl/archivelog/2018_07_23/thread_1_seq_26.318.982272153 thread=1 sequence=26
  296. archived log file name=+FRA/orcl/archivelog/2018_07_23/thread_1_seq_27.319.982272459 thread=1 sequence=27
  297. archived log file name=+FRA/orcl/archivelog/2018_07_23/thread_1_seq_28.320.982272459 thread=1 sequence=28
  298. archived log file name=+FRA/orcl/archivelog/2018_07_23/thread_1_seq_29.321.982272469 thread=1 sequence=29
  299. archived log file name=+FRA/orcl/archivelog/2018_07_23/thread_1_seq_30.322.982272473 thread=1 sequence=30
  300. archived log file name=+FRA/orcl/archivelog/2018_07_23/thread_1_seq_31.323.982272473 thread=1 sequence=31
  301. archived log file name=+FRA/orcl/archivelog/2018_07_23/thread_1_seq_32.324.982272475 thread=1 sequence=32
  302. archived log file name=+FRA/orcl/archivelog/2018_07_23/thread_1_seq_33.325.982272615 thread=1 sequence=33
  303. archived log file name=+FRA/orcl/archivelog/2018_07_23/thread_1_seq_34.327.982273393 thread=1 sequence=34
  304. archived log file name=+FRA/orcl/archivelog/2018_07_23/thread_1_seq_35.328.982273393 thread=1 sequence=35
  305. archived log file name=+FRA/orcl/archivelog/2018_07_23/thread_1_seq_36.333.982273403 thread=1 sequence=36
  306. media recovery complete, elapsed time: 00:01:09
  307. Finished recover at 2018-07-23:21:48:17

  308. database opened

  309. contents of Memory Script:
  310. {
  311. # make read only the tablespace that will be exported
  312. sql clone 'alter tablespace  USERS read only';
  313. # create directory for datapump import
  314. sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
  315. /u01/app/oracle/oradata''";
  316. # create directory for datapump export
  317. sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
  318. /u01/app/oracle/oradata''";
  319. }
  320. executing Memory Script

  321. sql statement: alter tablespace  USERS read only

  322. sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/app/oracle/oradata''

  323. sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/app/oracle/oradata''

  324. Performing export of metadata...
  325.    EXPDP> Starting "SYS"."TSPITR_EXP_BcBD":  
  326.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
  327.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_SPEC
  328.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_BODY
  329.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/XMLSCHEMA/XMLSCHEMA
  330.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
  331.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
  332.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
  333.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
  334.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/COMMENT
  335.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
  336.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
  337.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
  338.    EXPDP> Master table "SYS"."TSPITR_EXP_BcBD" successfully loaded/unloaded
  339.    EXPDP> ******************************************************************************
  340.    EXPDP> Dump file set for SYS.TSPITR_EXP_BcBD is:
  341.    EXPDP>   /u01/app/oracle/oradata/tspitr_BcBD_77380.dmp
  342.    EXPDP> ******************************************************************************
  343.    EXPDP> Datafiles required for transportable tablespace USERS:
  344.    EXPDP>   +DATA/orcl/datafile/users.259.816169553
  345.    EXPDP> Job "SYS"."TSPITR_EXP_BcBD" successfully completed at 21:49:21
  346. Export completed


  347. contents of Memory Script:
  348. {
  349. # shutdown clone before import
  350. shutdown clone immediate
  351. # drop target tablespaces before importing them back
  352. sql 'drop tablespace  USERS including contents keep datafiles';
  353. }
  354. executing Memory Script

  355. database closed
  356. database dismounted
  357. Oracle instance shut down

  358. sql statement: drop tablespace  USERS including contents keep datafiles

  359. Performing import of metadata...
  360.    IMPDP> Master table "SYS"."TSPITR_IMP_BcBD" successfully loaded/unloaded
  361.    IMPDP> Starting "SYS"."TSPITR_IMP_BcBD":  
  362.    IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
  363.    IMPDP> Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_SPEC
  364.    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 existsORA-31684: Object type TYPE:"OE"."LINEITEM_T" already existsORA-31684: Object type TYPE:"OE"."LEAF_CATEGORY_TYP" already existsORA-31684: Object type TYPE:"OE"."COMPOSITE_CATEGORY_TYP" already existsORA-31684: Object type TYPE:"OE"."ACTIONS_T" already existsORA-31684: Object type TYPE:"OE"."LINEITEM_V" already existsORA-31684: Object type TYPE:"OE"."CATALOG_TYP" already existsORA-31684: Object type TYPE:"OE"."LINEITEMS_T" already existsORA-31684: Object type TYPE:"OE"."PURCHASEORDER_T" already exists
  365.    IMPDP> Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_BODY
  366.    IMPDP> ORA-31684: Object type TYPE_BODY:"OE"."LEAF_CATEGORY_TYP" already existsORA-31684: Object type TYPE_BODY:"OE"."COMPOSITE_CATEGORY_TYP" already existsORA-31684: Object type TYPE_BODY:"OE"."CATALOG_TYP" already exists
  367.    IMPDP> Processing object type TRANSPORTABLE_EXPORT/XMLSCHEMA/XMLSCHEMA
  368.    IMPDP> ORA-39083: Object type XMLSCHEMA failed to create with error:
  369. ORA-31085: schema "http://localhost:8080/source/schemas/poSource/xsd/purchaseOrder.xsd" already registered
  370. Failing sql is:
  371. BEGIN dbms_xmlschema.registerSchema(:1, :2, (:3 = 1), FALSE,FALSE,FALSE,FALSE, :4, options=> :5, schemaoid => :6, import_options => :7); END;
  372.    IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
  373.    IMPDP> ORA-39083: Object type TABLE:"OE"."PURCHASEORDER" failed to create with error:
  374. ORA-31061: XDB error: DBMS_XDBZ.ENABLE_HIERARCHY
  375. ORA-06512: at "XDB.DBMS_XDBZ0", line 131
  376. ORA-06512: at "XDB.DBMS_XDBZ0", line 588
  377. ORA-01031: insufficient privileges
  378. Failing sql is:
  379. BEGIN DBMS_XDBZ.ENABLE_HIERARCHY('"OE"','"PURCHASEORDER"'); END;
  380.    IMPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
  381.    IMPDP> Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
  382.    IMPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
  383.    IMPDP> ORA-39083: Object type INDEX_STATISTICS failed to create with error:
  384. ORA-20000: INDEX "OE"."LINEITEM_TABLE_MEMBERS" does not exist or insufficient privileges
  385. Failing sql is:
  386. DECLARE I_N VARCHAR2(60);   I_O VARCHAR2(60);   NV VARCHAR2(1);   c DBMS_METADATA.T_VAR_COLL;   df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS';  stmt varchar2(300) := ' INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,d1,cl1) VALUES (''I'',6,:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,NULORA-39112: Dependent object type INDEX_STATISTICS skipped, base object type TABLE:"OE"."PURCHASEORDER" creation failedORA-39083: Object type INDEX_STATISTICS failed to create with error:
  387. ORA-20000: INDEX "OE"."ACTION_TABLE_MEMBERS" does not exist or insufficient privileges
  388. Failing sql is:
  389. DECLARE I_N VARCHAR2(60);   I_O VARCHAR2(60);   NV VARCHAR2(1);   c DBMS_METADATA.T_VAR_COLL;   df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS';  stmt varchar2(300) := ' INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,d1,cl1) VALUES (''I'',6,:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,NULL,
  390.    IMPDP> Processing object type TRANSPORTABLE_EXPORT/COMMENT
  391.    IMPDP> Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
  392.    IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
  393.    IMPDP> ORA-39112: Dependent object type TABLE_STATISTICS skipped, base object type TABLE:"OE"."PURCHASEORDER" creation failed
  394.    IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
  395.    IMPDP> Job "SYS"."TSPITR_IMP_BcBD" completed with 25 error(s) at 21:49:53
  396. Import completed


  397. contents of Memory Script:
  398. {
  399. # make read write and offline the imported tablespaces
  400. sql 'alter tablespace  USERS read write';
  401. sql 'alter tablespace  USERS offline';
  402. # enable autobackups after TSPITR is finished
  403. sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
  404. }
  405. executing Memory Script

  406. sql statement: alter tablespace  USERS read write

  407. sql statement: alter tablespace  USERS offline

  408. sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;

  409. Removing automatic instance
  410. Automatic instance removed
  411. auxiliary instance file /u01/app/oracle/oradata/ORCL/datafile/o1_mf_temp_focq53vc_.tmp deleted
  412. auxiliary instance file /u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_3_focq52oo_.log deleted
  413. auxiliary instance file /u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_2_focq52bt_.log deleted
  414. auxiliary instance file /u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_1_focq51yc_.log deleted
  415. auxiliary instance file /u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_focq1q59_.dbf deleted
  416. auxiliary instance file /u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_focq1q5x_.dbf deleted
  417. auxiliary instance file /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_focq1l57_.dbf deleted
  418. auxiliary instance file /u01/app/oracle/oradata/ORCL/controlfile/o1_mf_focq0r3q_.ctl deleted
  419. Finished recover at 2018-07-23:21:49:56

  420. RMAN>
复制代码


回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|Botang唐波's Oracle Station   

GMT+8, 2018-8-20 02:56 , Processed in 0.137766 second(s), 27 queries .

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