活动第36/37次（2018-07-07星期六上下午）

botang

多租户AG书的P167页有错，在插件数据库OPEN时坏掉了SYSTEM表空间，不需要关CDB：



做PITR，对照实验（5），先准备一下环境：
先进行自包含集检查：

1. [oracle@station86 ~]$ . oraenv
   
2. ORACLE_SID = [orcl] ?
   
3. The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 is /u01/app/oracle
   
4. [oracle@station86 ~]$ sqlplus /nolog
   
5. 
   
6. SQL*Plus: Release 11.2.0.1.0 Production on Sat Jul 7 10:41:39 2018
   
7. 
   
8. Copyright (c) 1982, 2009, Oracle.  All rights reserved.
   
9. 
   
10. SQL> conn  / as sysdba
    
11. Connected.
    
12. SQL> exec dbms_tts.transport_set_check('USERS');
    
13. 
    
14. PL/SQL procedure successfully completed.
    
15. 
    
16. SQL> select  * from transport_set_violations;
    
17. 
    
18. VIOLATIONS
    
19. --------------------------------------------------------------------------------
    
20. ORA-39917: SYS owned object TTESTBOTANG_5 in tablespace USERS not allowed in plu
    
21. ggable set
    
22. 
    
23. 
    
24. SQL> alter table TTESTBOTANG_5  move tablespace system ;
    
25. 
    
26. Table altered.
    
27. 
    
28. SQL> exec dbms_tts.transport_set_check('USERS');
    
29. 
    
30. PL/SQL procedure successfully completed.
    
31. 
    
32. SQL> select  * from transport_set_violations;
    
33. 
    
34. no rows selected
    
35. 
    
36. SQL>
    

复制代码

11.2.0.3以后，以下删除用户的命令是不需要的(对环境没有要求)：

1. /*  SQL> select  username from dba_users where username='OE';
   
2. 
   
3. USERNAME
   
4. ------------------------------
   
5. OE
   
6. SH
   
7. 
   
8. SQL> drop user oe cascade;
   
9. 
   
10. User dropped.
    
11. 
    
12. SQL> drop user sh cascade;
    
13. 
    
14. User dropped.   */
    
15. 
    

复制代码

做备份（之前有备份也不需要做）。

运行bcl --O11g05 5，做实验环境。

再检查是不是默认永久表空间：

1. SQL> select  PROPERTY_NAME  , PROPERTY_VALUE  from database_properties where PROPERTY_NAME like '%TABLESPACE%';
   
2. 
   
3. PROPERTY_NAME
   
4. ------------------------------
   
5. PROPERTY_VALUE
   
6. --------------------------------------------------------------------------------
   
7. DEFAULT_TEMP_TABLESPACE
   
8. TEMP
   
9. 
   
10. DEFAULT_PERMANENT_TABLESPACE
    
11. EXAMPLE
    

复制代码

1. [oracle@station86 ~]$ sqlplus /nolog
   
2. 
   
3. SQL*Plus: Release 11.2.0.1.0 Production on Sat Jul 7 11:03:06 2018
   
4. 
   
5. Copyright (c) 1982, 2009, Oracle.  All rights reserved.
   
6. 
   
7. SQL> conn / as sysdba
   
8. Connected.
   
9. SQL> alter database default tablespace example;
   
10. 
    
11. Database altered.
    
12. 
    
13. SQL>
    

复制代码

开始执行PITR：

1. SQL> alter tablespace users offline immediate ;
   
2. 
   
3. Tablespace altered.
   
4. 
   
5. SQL>
   

复制代码

LINUX环境变量：export NLS_DATE_FORMAT='YYYY-MM-DD:HH24:MI:SS'



命令和分配通道：

1. [oracle@station86 ~]$ rman target /
   
2. 
   
3. Recovery Manager: Release 11.2.0.1.0 - Production on Sat Jul 7 11:05:31 2018
   
4. 
   
5. Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
   
6. 
   
7. connected to target database: ORCL (DBID=1359978017)
   
8. 
   
9. RMAN> recover tablespace users
   
10. 2> until time '2018-07-07:10:58:17'
    
11. 3> auxiliary destination '/u01/app/oracle/oradata';
    
12. 
    
13. Starting recover at 2018-07-07:11:08:06
    
14. using target database control file instead of recovery catalog
    
15. allocated channel: ORA_DISK_1
    
16. channel ORA_DISK_1: SID=13 device type=DISK
    
17. allocated channel: ORA_DISK_2
    
18. channel ORA_DISK_2: SID=99 device type=DISK
    
19. allocated channel: ORA_DISK_3
    
20. channel ORA_DISK_3: SID=142 device type=DISK
    
21. allocated channel: ORA_DISK_4
    
22. channel ORA_DISK_4: SID=11 device type=DISK
    
23. allocated channel: ORA_DISK_5
    
24. channel ORA_DISK_5: SID=52 device type=DISK
    
25. allocated channel: ORA_DISK_6
    
26. channel ORA_DISK_6: SID=96 device type=DISK
    
27. allocated channel: ORA_DISK_7
    
28. channel ORA_DISK_7: SID=140 device type=DISK
    
29. allocated channel: ORA_DISK_8
    
30. channel ORA_DISK_8: SID=9 device type=DISK
    
31. allocated channel: ORA_SBT_TAPE_1
    
32. channel ORA_SBT_TAPE_1: SID=143 device type=SBT_TAPE
    
33. channel ORA_SBT_TAPE_1: Oracle Secure Backup
    
34. allocated channel: ORA_SBT_TAPE_2
    
35. channel ORA_SBT_TAPE_2: SID=58 device type=SBT_TAPE
    
36. channel ORA_SBT_TAPE_2: Oracle Secure Backup
    

复制代码

自动启动克隆实例（它的USERS指向真正的USERS，其他的文件是新克隆的）：

1. RMAN-05026: WARNING: presuming following set of tablespaces applies to specified point-in-time
   
2. 
   
3. List of tablespaces expected to have UNDO segments
   
4. Tablespace SYSTEM
   
5. Tablespace UNDOTBS1
   
6. 
   
7. Creating automatic instance, with SID='epDa'
   
8. 
   
9. initialization parameters used for automatic instance:
   
10. db_name=ORCL
    
11. db_unique_name=epDa_tspitr_ORCL
    
12. compatible=11.2.0
    
13. db_block_size=8192
    
14. db_files=200
    
15. sga_target=280M
    
16. processes=50
    
17. db_create_file_dest=/u01/app/oracle/oradata
    
18. log_archive_dest_1='location=/u01/app/oracle/oradata'
    
19. #No auxiliary parameter file used
    
20. 
    
21. 
    
22. starting up automatic instance ORCL
    
23. 
    
24. Oracle instance started
    
25. 
    
26. Total System Global Area     292933632 bytes
    
27. 
    
28. Fixed Size                     1336092 bytes
    
29. Variable Size                100666596 bytes
    
30. Database Buffers             184549376 bytes
    
31. Redo Buffers                   6381568 bytes
    
32. Automatic instance created
    
33. Running TRANSPORT_SET_CHECK on recovery set tablespaces
    
34. TRANSPORT_SET_CHECK completed successfully
    
35. 
    
36. contents of Memory Script:
    
37. {
    
38. # set requested point in time
    
39. set until  time "2018-07-07:10:58:17";
    
40. # restore the controlfile
    
41. restore clone controlfile;
    
42. # mount the controlfile
    
43. sql clone 'alter database mount clone database';
    
44. # archive current online log
    
45. sql 'alter system archive log current';
    
46. # avoid unnecessary autobackups for structural changes during TSPITR
    
47. sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
    
48. }
    
49. executing Memory Script
    
50. 
    
51. executing command: SET until clause
    
52. 
    
53. Starting restore at 2018-07-07:11:09:28
    
54. allocated channel: ORA_AUX_DISK_1
    
55. channel ORA_AUX_DISK_1: SID=81 device type=DISK
    
56. allocated channel: ORA_AUX_DISK_2
    
57. channel ORA_AUX_DISK_2: SID=5 device type=DISK
    
58. allocated channel: ORA_AUX_DISK_3
    
59. channel ORA_AUX_DISK_3: SID=30 device type=DISK
    
60. allocated channel: ORA_AUX_DISK_4
    
61. channel ORA_AUX_DISK_4: SID=56 device type=DISK
    
62. allocated channel: ORA_AUX_DISK_5
    
63. channel ORA_AUX_DISK_5: SID=82 device type=DISK
    
64. allocated channel: ORA_AUX_DISK_6
    
65. channel ORA_AUX_DISK_6: SID=6 device type=DISK
    
66. allocated channel: ORA_AUX_DISK_7
    
67. channel ORA_AUX_DISK_7: SID=31 device type=DISK
    
68. allocated channel: ORA_AUX_DISK_8
    
69. channel ORA_AUX_DISK_8: SID=57 device type=DISK
    
70. allocated channel: ORA_AUX_SBT_TAPE_1
    
71. channel ORA_AUX_SBT_TAPE_1: SID=83 device type=SBT_TAPE
    
72. channel ORA_AUX_SBT_TAPE_1: Oracle Secure Backup
    
73. allocated channel: ORA_AUX_SBT_TAPE_2
    
74. channel ORA_AUX_SBT_TAPE_2: SID=7 device type=SBT_TAPE
    
75. channel ORA_AUX_SBT_TAPE_2: Oracle Secure Backup
    
76. 
    
77. channel ORA_AUX_SBT_TAPE_1: starting datafile backup set restore
    
78. channel ORA_AUX_SBT_TAPE_1: restoring control file
    
79. channel ORA_AUX_SBT_TAPE_1: reading from backup piece 58t7d78b_1_1
    
80. channel ORA_AUX_SBT_TAPE_1: piece handle=58t7d78b_1_1 tag=TAG20180707T105052
    
81. channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
    
82. channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:58
    
83. output file name=/u01/app/oracle/oradata/ORCL/controlfile/o1_mf_fn0crz7d_.ctl
    
84. Finished restore at 2018-07-07:11:10:30
    
85. 
    
86. sql statement: alter database mount clone database
    
87. 
    
88. sql statement: alter system archive log current
    
89. 
    
90. sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;
    
91. 
    
92. contents of Memory Script:
    
93. {
    
94. # set requested point in time
    
95. set until  time "2018-07-07:10:58:17";
    
96. # set destinations for recovery set and auxiliary set datafiles
    
97. set newname for clone datafile  1 to new;
    
98. set newname for clone datafile  3 to new;
    
99. set newname for clone datafile  2 to new;
    
100. set newname for clone tempfile  1 to new;
     
101. set newname for datafile  4 to
     
102. "+DATA/orcl/datafile/users.265.980714801";
     
103. # switch all tempfiles
     
104. switch clone tempfile all;
     
105. # restore the tablespaces in the recovery set and the auxiliary set
     
106. restore clone datafile  1, 3, 2, 4;
     
107. switch clone datafile all;
     
108. }
     
109. executing Memory Script
     
110. 
     
111. executing command: SET until clause
     
112. 
     
113. executing command: SET NEWNAME
     
114. 
     
115. executing command: SET NEWNAME
     
116. 
     
117. executing command: SET NEWNAME
     
118. 
     
119. executing command: SET NEWNAME
     
120. 
     
121. executing command: SET NEWNAME
     
122. 
     
123. renamed tempfile 1 to /u01/app/oracle/oradata/ORCL/datafile/o1_mf_temp_%u_.tmp in control file
     
124. 
     
125. Starting restore at 2018-07-07:11:10:37
     
126. using channel ORA_AUX_DISK_1
     
127. using channel ORA_AUX_DISK_2
     
128. using channel ORA_AUX_DISK_3
     
129. using channel ORA_AUX_DISK_4
     
130. using channel ORA_AUX_DISK_5
     
131. using channel ORA_AUX_DISK_6
     
132. using channel ORA_AUX_DISK_7
     
133. using channel ORA_AUX_DISK_8
     
134. using channel ORA_AUX_SBT_TAPE_1
     
135. using channel ORA_AUX_SBT_TAPE_2
     
136. 
     
137. channel ORA_AUX_SBT_TAPE_1: starting datafile backup set restore
     
138. channel ORA_AUX_SBT_TAPE_1: specifying datafile(s) to restore from backup set
     
139. channel ORA_AUX_SBT_TAPE_1: restoring datafile 00003 to /u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_%u_.dbf
     
140. channel ORA_AUX_SBT_TAPE_1: restoring datafile 00002 to /u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_%u_.dbf
     
141. channel ORA_AUX_SBT_TAPE_1: reading from backup piece 57t7d74d_1_1
     
142. channel ORA_AUX_SBT_TAPE_2: starting datafile backup set restore
     
143. channel ORA_AUX_SBT_TAPE_2: specifying datafile(s) to restore from backup set
     
144. channel ORA_AUX_SBT_TAPE_2: restoring datafile 00001 to /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_%u_.dbf
     
145. channel ORA_AUX_SBT_TAPE_2: restoring datafile 00004 to +DATA/orcl/datafile/users.265.980714801
     
146. channel ORA_AUX_SBT_TAPE_2: reading from backup piece 56t7d74d_1_1
     
147. channel ORA_AUX_SBT_TAPE_1: piece handle=57t7d74d_1_1 tag=TAG20180707T105052
     
148. channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
     
149. channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:01:55
     
150. channel ORA_AUX_SBT_TAPE_2: piece handle=56t7d74d_1_1 tag=TAG20180707T105052
     
151. channel ORA_AUX_SBT_TAPE_2: restored backup piece 1
     
152. channel ORA_AUX_SBT_TAPE_2: restore complete, elapsed time: 00:02:15
     
153. Finished restore at 2018-07-07:11:12:57
     
154. 
     
155. datafile 1 switched to datafile copy
     
156. input datafile copy RECID=41 STAMP=980853177 file name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_fn0cvhtz_.dbf
     
157. datafile 3 switched to datafile copy
     
158. input datafile copy RECID=42 STAMP=980853177 file name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_fn0cvk93_.dbf
     
159. datafile 2 switched to datafile copy
     
160. input datafile copy RECID=43 STAMP=980853177 file name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_fn0cvjz2_.dbf
     

复制代码

对克隆实例做传统的不完恢复：

1. contents of Memory Script:
   
2. {
   
3. # set requested point in time
   
4. set until  time "2018-07-07:10:58:17";
   
5. # online the datafiles restored or switched
   
6. sql clone "alter database datafile  1 online";
   
7. sql clone "alter database datafile  3 online";
   
8. sql clone "alter database datafile  2 online";
   
9. sql clone "alter database datafile  4 online";
   
10. # recover and open resetlogs
    
11. recover clone database tablespace  "USERS", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;
    
12. alter clone database open resetlogs;
    
13. }
    
14. executing Memory Script
    
15. 
    
16. executing command: SET until clause
    
17. 
    
18. sql statement: alter database datafile  1 online
    
19. 
    
20. sql statement: alter database datafile  3 online
    
21. 
    
22. sql statement: alter database datafile  2 online
    
23. 
    
24. sql statement: alter database datafile  4 online
    
25. 
    
26. Starting recover at 2018-07-07:11:12:58
    
27. using channel ORA_AUX_DISK_1
    
28. using channel ORA_AUX_DISK_2
    
29. using channel ORA_AUX_DISK_3
    
30. using channel ORA_AUX_DISK_4
    
31. using channel ORA_AUX_DISK_5
    
32. using channel ORA_AUX_DISK_6
    
33. using channel ORA_AUX_DISK_7
    
34. using channel ORA_AUX_DISK_8
    
35. using channel ORA_AUX_SBT_TAPE_1
    
36. using channel ORA_AUX_SBT_TAPE_2
    
37. 
    
38. starting media recovery
    
39. 
    
40. archived log for thread 1 with sequence 86 is already on disk as file +FRA/orcl/archivelog/2018_07_07/thread_1_seq_86.257.980852293
    
41. archived log for thread 1 with sequence 87 is already on disk as file +FRA/orcl/archivelog/2018_07_07/thread_1_seq_87.286.980852293
    
42. archived log for thread 1 with sequence 88 is already on disk as file +FRA/orcl/archivelog/2018_07_07/thread_1_seq_88.355.980852305
    
43. archived log file name=+FRA/orcl/archivelog/2018_07_07/thread_1_seq_86.257.980852293 thread=1 sequence=86
    
44. archived log file name=+FRA/orcl/archivelog/2018_07_07/thread_1_seq_87.286.980852293 thread=1 sequence=87
    
45. archived log file name=+FRA/orcl/archivelog/2018_07_07/thread_1_seq_88.355.980852305 thread=1 sequence=88
    
46. media recovery complete, elapsed time: 00:00:05
    
47. Finished recover at 2018-07-07:11:13:04
    
48. 
    
49. database opened
    

复制代码

在克隆数据上，只读USERS，准备以传送表空间的方式数据泵导出USERS：

1. contents of Memory Script:
   
2. {
   
3. # make read only the tablespace that will be exported
   
4. sql clone 'alter tablespace  USERS read only';
   
5. # create directory for datapump import
   
6. sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
   
7. /u01/app/oracle/oradata''";
   
8. # create directory for datapump export
   
9. sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
   
10. /u01/app/oracle/oradata''";
    
11. }
    
12. executing Memory Script
    
13. 
    
14. sql statement: alter tablespace  USERS read only
    
15. 
    
16. sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/app/oracle/oradata''
    
17. 
    
18. sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/app/oracle/oradata''
    
19. 
    
20. Performing export of metadata...
    
21.    EXPDP> Starting "SYS"."TSPITR_EXP_epDa":  
    
22.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
    
23.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_SPEC
    
24.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_BODY
    
25.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/XMLSCHEMA/XMLSCHEMA
    
26.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
    
27.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
    
28.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
    
29.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
    
30.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
    
31.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
    
32.    EXPDP> Master table "SYS"."TSPITR_EXP_slhv" successfully loaded/unloaded
    
33.    EXPDP> ******************************************************************************
    
34.    EXPDP> Dump file set for SYS.TSPITR_EXP_slhv is:
    
35.    EXPDP>   /u01/app/oracle/oradata/tspitr_slhv_63292.dmp
    
36.    EXPDP> ******************************************************************************
    
37.    EXPDP> Datafiles required for transportable tablespace USERS:
    
38.    EXPDP>   +DATA/orcl/datafile/users.259.816169553
    
39.    EXPDP> Job "SYS"."TSPITR_EXP_slhv" successfully completed at 15:46:05
    
40. Export completed
    
41. 
    

复制代码

自动地导入。




-------------以上是对照普通数据库的PITR，以下是多租户教材206页的内容但是下图选中部分是不需要做的：



-----------------------------------------插件数据库PITR：

1. SQL*Plus: Release 12.1.0.2.0 Production on Sat Jul 7 17:01:03 2018
   
2. 
   
3. Copyright (c) 1982, 2014, Oracle.  All rights reserved.
   
4. 
   
5. SQL> conn / as sysdba
   
6. Connected.
   
7. SQL> alter pluggable database pdb2 close immediate;
   
8. 
   
9. Pluggable database altered.
   
10. 
    
11. SQL> exit
    

复制代码

命令和分配通道：

1. [oracle@station90 ~]$ rman target /
   
2. 
   
3. Recovery Manager: Release 12.1.0.2.0 - Production on Sat Jul 7 17:07:35 2018
   
4. 
   
5. Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
   
6. 
   
7. connected to target database: CDB2 (DBID=727229478)
   
8. 
   
9. RMAN> run {
   
10. 2> set until time '2018-07-07:16:46:16';
    
11. 3> restore pluggable database pdb2;
    
12. 4> recover pluggable database pdb2
    
13. 5>  auxiliary destination '/u01/app/oracle/oradata';
    
14. 6> }
    
15. 
    
16. executing command: SET until clause
    
17. 
    
18. Starting restore at 2018-07-07:17:07:54
    
19. using target database control file instead of recovery catalog
    
20. allocated channel: ORA_DISK_1
    
21. channel ORA_DISK_1: SID=330 device type=DISK
    
22. allocated channel: ORA_DISK_2
    
23. channel ORA_DISK_2: SID=631 device type=DISK
    
24. allocated channel: ORA_DISK_3
    
25. channel ORA_DISK_3: SID=921 device type=DISK
    
26. allocated channel: ORA_DISK_4
    
27. channel ORA_DISK_4: SID=335 device type=DISK
    
28. allocated channel: ORA_DISK_5
    
29. channel ORA_DISK_5: SID=937 device type=DISK
    
30. allocated channel: ORA_DISK_6
    
31. channel ORA_DISK_6: SID=316 device type=DISK
    
32. allocated channel: ORA_DISK_7
    
33. channel ORA_DISK_7: SID=927 device type=DISK
    
34. allocated channel: ORA_DISK_8
    
35. channel ORA_DISK_8: SID=25 device type=DISK
    
36. allocated channel: ORA_DISK_9
    
37. channel ORA_DISK_9: SID=325 device type=DISK
    
38. allocated channel: ORA_DISK_10
    
39. channel ORA_DISK_10: SID=630 device type=DISK
    
40. allocated channel: ORA_DISK_11
    
41. channel ORA_DISK_11: SID=935 device type=DISK
    
42. allocated channel: ORA_DISK_12
    
43. channel ORA_DISK_12: SID=332 device type=DISK
    
44. allocated channel: ORA_SBT_TAPE_1
    
45. channel ORA_SBT_TAPE_1: SID=941 device type=SBT_TAPE
    
46. channel ORA_SBT_TAPE_1: Oracle Secure Backup
    
47. allocated channel: ORA_SBT_TAPE_2
    
48. channel ORA_SBT_TAPE_2: SID=336 device type=SBT_TAPE
    
49. channel ORA_SBT_TAPE_2: Oracle Secure Backup
    
50. 
    
51. creating datafile file number=28 name=/u01/app/oracle/oradata/cdb2/pdb2_1/dj_pdb2.f
    
52. skipping datafile 10; already restored to file /u01/app/oracle/oradata/cdb2/pdb2_1/system01.dbf
    
53. skipping datafile 25; already restored to file /u01/app/oracle/oradata/cdb2/pdb2_1/ldata_01.dbf
    
54. skipping datafile 11; already restored to file /u01/app/oracle/oradata/cdb2/pdb2_1/sysaux01.dbf
    
55. Finished restore at 2018-07-07:17:08:11
    

复制代码

自动启动克隆实例：

1. Starting recover at 2018-07-07:17:08:11
   
2. using channel ORA_DISK_1
   
3. using channel ORA_DISK_2
   
4. using channel ORA_DISK_3
   
5. using channel ORA_DISK_4
   
6. using channel ORA_DISK_5
   
7. using channel ORA_DISK_6
   
8. using channel ORA_DISK_7
   
9. using channel ORA_DISK_8
   
10. using channel ORA_DISK_9
    
11. using channel ORA_DISK_10
    
12. using channel ORA_DISK_11
    
13. using channel ORA_DISK_12
    
14. using channel ORA_SBT_TAPE_1
    
15. using channel ORA_SBT_TAPE_2
    
16. RMAN-05026: WARNING: presuming following set of tablespaces applies to specified Point-in-Time
    
17. 
    
18. List of tablespaces expected to have UNDO segments
    
19. Tablespace SYSTEM
    
20. Tablespace UNDOTBS1
    
21. 
    
22. Creating automatic instance, with SID='Fnhk'
    
23. 
    
24. initialization parameters used for automatic instance:
    
25. db_name=CDB2
    
26. db_unique_name=Fnhk_pitr_pdb2_CDB2
    
27. compatible=12.1.0.2.0
    
28. db_block_size=8192
    
29. db_files=200
    
30. diagnostic_dest=/u01/app/oracle
    
31. _system_trig_enabled=FALSE
    
32. sga_target=2560M
    
33. processes=200
    
34. db_create_file_dest=/u01/app/oracle/oradata
    
35. log_archive_dest_1='location=/u01/app/oracle/oradata'
    
36. enable_pluggable_database=true
    
37. _clone_one_pdb_recovery=true
    
38. #No auxiliary parameter file used
    
39. 
    
40. 
    
41. starting up automatic instance CDB2
    
42. 
    
43. Oracle instance started
    
44. 
    
45. Total System Global Area    2684354560 bytes
    
46. 
    
47. Fixed Size                     2928008 bytes
    
48. Variable Size                603980408 bytes
    
49. Database Buffers            2063597568 bytes
    
50. Redo Buffers                  13848576 bytes
    
51. Automatic instance created
    
52. 
    
53. contents of Memory Script:
    
54. {
    
55. # set requested point in time
    
56. set until  time "2018-07-07:16:46:16";
    
57. # restore the controlfile
    
58. restore clone controlfile;
    
59. 
    
60. # mount the controlfile
    
61. sql clone 'alter database mount clone database';
    
62. }
    
63. executing Memory Script
    
64. 
    
65. executing command: SET until clause
    
66. 
    
67. Starting restore at 2018-07-07:17:08:53
    
68. allocated channel: ORA_AUX_DISK_1
    
69. channel ORA_AUX_DISK_1: SID=6 device type=DISK
    
70. allocated channel: ORA_AUX_DISK_2
    
71. channel ORA_AUX_DISK_2: SID=87 device type=DISK
    
72. allocated channel: ORA_AUX_DISK_3
    
73. channel ORA_AUX_DISK_3: SID=169 device type=DISK
    
74. allocated channel: ORA_AUX_DISK_4
    
75. channel ORA_AUX_DISK_4: SID=250 device type=DISK
    
76. allocated channel: ORA_AUX_DISK_5
    
77. channel ORA_AUX_DISK_5: SID=7 device type=DISK
    
78. allocated channel: ORA_AUX_DISK_6
    
79. channel ORA_AUX_DISK_6: SID=89 device type=DISK
    
80. allocated channel: ORA_AUX_DISK_7
    
81. channel ORA_AUX_DISK_7: SID=170 device type=DISK
    
82. allocated channel: ORA_AUX_DISK_8
    
83. channel ORA_AUX_DISK_8: SID=251 device type=DISK
    
84. allocated channel: ORA_AUX_DISK_9
    
85. channel ORA_AUX_DISK_9: SID=8 device type=DISK
    
86. allocated channel: ORA_AUX_DISK_10
    
87. channel ORA_AUX_DISK_10: SID=90 device type=DISK
    
88. allocated channel: ORA_AUX_DISK_11
    
89. channel ORA_AUX_DISK_11: SID=171 device type=DISK
    
90. allocated channel: ORA_AUX_DISK_12
    
91. channel ORA_AUX_DISK_12: SID=252 device type=DISK
    
92. allocated channel: ORA_AUX_SBT_TAPE_1
    
93. channel ORA_AUX_SBT_TAPE_1: SID=9 device type=SBT_TAPE
    
94. channel ORA_AUX_SBT_TAPE_1: Oracle Secure Backup
    
95. allocated channel: ORA_AUX_SBT_TAPE_2
    
96. channel ORA_AUX_SBT_TAPE_2: SID=91 device type=SBT_TAPE
    
97. channel ORA_AUX_SBT_TAPE_2: Oracle Secure Backup
    
98. 
    
99. channel ORA_AUX_DISK_1: starting datafile backup set restore
    
100. channel ORA_AUX_DISK_1: restoring control file
     
101. channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/CDB2/autobackup/2018_07_07/o1_mf_s_980872729_fn0z0tmw_.bkp
     
102. channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/CDB2/autobackup/2018_07_07/o1_mf_s_980872729_fn0z0tmw_.bkp tag=TAG20180707T163849
     
103. channel ORA_AUX_DISK_1: restored backup piece 1
     
104. channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
     
105. output file name=/u01/app/oracle/oradata/CDB2/controlfile/o1_mf_fn10s9mz_.ctl
     
106. Finished restore at 2018-07-07:17:08:58
     
107. 
     
108. sql statement: alter database mount clone database
     
109. 
     

复制代码

在克隆数据库上做不完全恢复，然后交换身份：

1. contents of Memory Script:
   
2. {
   
3. # set requested point in time
   
4. set until  time "2018-07-07:17:27:45";
   
5. # switch to valid datafilecopies
   
6. switch clone datafile  10 to datafilecopy
   
7. "/u01/app/oracle/oradata/cdb2/pdb2_1/system01.dbf";
   
8. switch clone datafile  11 to datafilecopy
   
9. "/u01/app/oracle/oradata/cdb2/pdb2_1/sysaux01.dbf";
   
10. switch clone datafile  25 to datafilecopy
    
11. "/u01/app/oracle/oradata/cdb2/pdb2_1/ldata_01.dbf";
    
12. switch clone datafile  28 to datafilecopy
    
13. "/u01/app/oracle/oradata/cdb2/pdb2_1/dj_pdb2.f";
    
14. # set destinations for recovery set and auxiliary set datafiles
    
15. set newname for clone datafile  1 to new;
    
16. set newname for clone datafile  4 to new;
    
17. set newname for clone datafile  3 to new;
    
18. set newname for clone datafile  6 to new;
    
19. set newname for clone datafile  24 to new;
    
20. # restore the tablespaces in the recovery set and the auxiliary set
    
21. restore clone datafile  1, 4, 3, 6, 24;
    
22. 
    
23. switch clone datafile all;
    
24. }
    
25. executing Memory Script
    
26. 
    
27. executing command: SET until clause
    
28. 
    
29. datafile 10 switched to datafile copy
    
30. input datafile copy RECID=3 STAMP=980876013 file name=/u01/app/oracle/oradata/cdb2/pdb2_1/system01.dbf
    
31. 
    
32. datafile 11 switched to datafile copy
    
33. input datafile copy RECID=4 STAMP=980876013 file name=/u01/app/oracle/oradata/cdb2/pdb2_1/sysaux01.dbf
    
34. 
    
35. datafile 25 switched to datafile copy
    
36. input datafile copy RECID=5 STAMP=980876013 file name=/u01/app/oracle/oradata/cdb2/pdb2_1/ldata_01.dbf
    
37. 
    
38. datafile 28 switched to datafile copy
    
39. input datafile copy RECID=6 STAMP=980876013 file name=/u01/app/oracle/oradata/cdb2/pdb2_1/dj_pdb2.f
    
40. 
    
41. executing command: SET NEWNAME
    
42. 
    
43. executing command: SET NEWNAME
    
44. 
    
45. executing command: SET NEWNAME
    
46. 
    
47. executing command: SET NEWNAME
    
48. 
    
49. executing command: SET NEWNAME
    
50. 
    
51. Starting restore at 2018-07-07:17:33:34
    
52. using channel ORA_AUX_DISK_1
    
53. using channel ORA_AUX_DISK_2
    
54. using channel ORA_AUX_DISK_3
    
55. using channel ORA_AUX_DISK_4
    
56. using channel ORA_AUX_DISK_5
    
57. using channel ORA_AUX_DISK_6
    
58. using channel ORA_AUX_DISK_7
    
59. using channel ORA_AUX_DISK_8
    
60. using channel ORA_AUX_DISK_9
    
61. using channel ORA_AUX_DISK_10
    
62. using channel ORA_AUX_DISK_11
    
63. using channel ORA_AUX_DISK_12
    
64. using channel ORA_AUX_SBT_TAPE_1
    
65. using channel ORA_AUX_SBT_TAPE_2
    
66. 
    
67. channel ORA_AUX_SBT_TAPE_1: starting datafile backup set restore
    
68. channel ORA_AUX_SBT_TAPE_1: specifying datafile(s) to restore from backup set
    
69. channel ORA_AUX_SBT_TAPE_1: restoring datafile 00003 to /u01/app/oracle/oradata/CDB2/datafile/o1_mf_sysaux_%u_.dbf
    
70. channel ORA_AUX_SBT_TAPE_1: restoring datafile 00006 to /u01/app/oracle/oradata/CDB2/datafile/o1_mf_users_%u_.dbf
    
71. channel ORA_AUX_SBT_TAPE_1: restoring datafile 00024 to /u01/app/oracle/oradata/CDB2/datafile/o1_mf_cdata_%u_.dbf
    
72. channel ORA_AUX_SBT_TAPE_1: reading from backup piece 0st7dp06_1_1
    
73. channel ORA_AUX_SBT_TAPE_2: starting datafile backup set restore
    
74. channel ORA_AUX_SBT_TAPE_2: specifying datafile(s) to restore from backup set
    
75. channel ORA_AUX_SBT_TAPE_2: restoring datafile 00001 to /u01/app/oracle/oradata/CDB2/datafile/o1_mf_system_%u_.dbf
    
76. channel ORA_AUX_SBT_TAPE_2: restoring datafile 00004 to /u01/app/oracle/oradata/CDB2/datafile/o1_mf_undotbs1_%u_.dbf
    
77. channel ORA_AUX_SBT_TAPE_2: reading from backup piece 0rt7dp06_1_1
    
78. channel ORA_AUX_SBT_TAPE_1: piece handle=0st7dp06_1_1 tag=TAG20180707T155550
    
79. channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
    
80. channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:35
    
81. channel ORA_AUX_SBT_TAPE_2: piece handle=0rt7dp06_1_1 tag=TAG20180707T155550
    
82. channel ORA_AUX_SBT_TAPE_2: restored backup piece 1
    
83. channel ORA_AUX_SBT_TAPE_2: restore complete, elapsed time: 00:00:35
    
84. Finished restore at 2018-07-07:17:34:10
    
85. 
    
86. datafile 1 switched to datafile copy
    
87. input datafile copy RECID=12 STAMP=980876050 file name=/u01/app/oracle/oradata/CDB2/datafile/o1_mf_system_fn12874l_.dbf
    
88. datafile 4 switched to datafile copy
    
89. input datafile copy RECID=13 STAMP=980876050 file name=/u01/app/oracle/oradata/CDB2/datafile/o1_mf_undotbs1_fn12875h_.dbf
    
90. datafile 3 switched to datafile copy
    
91. input datafile copy RECID=14 STAMP=980876051 file name=/u01/app/oracle/oradata/CDB2/datafile/o1_mf_sysaux_fn1286nd_.dbf
    
92. datafile 6 switched to datafile copy
    
93. input datafile copy RECID=15 STAMP=980876051 file name=/u01/app/oracle/oradata/CDB2/datafile/o1_mf_users_fn1286or_.dbf
    
94. datafile 24 switched to datafile copy
    
95. input datafile copy RECID=16 STAMP=980876051 file name=/u01/app/oracle/oradata/CDB2/datafile/o1_mf_cdata_fn1286o2_.dbf
    
96. 
    
97. contents of Memory Script:
    
98. {
    
99. # set requested point in time
    
100. set until  time "2018-07-07:17:27:45";
     
101. # online the datafiles restored or switched
     
102. sql clone "alter database datafile  1 online";
     
103. sql clone "alter database datafile  4 online";
     
104. sql clone "alter database datafile  3 online";
     
105. sql clone 'PDB2' "alter database datafile
     
106. 10 online";
     
107. sql clone 'PDB2' "alter database datafile
     
108. 11 online";
     
109. sql clone 'PDB2' "alter database datafile
     
110. 25 online";
     
111. sql clone 'PDB2' "alter database datafile
     
112. 28 online";
     
113. sql clone "alter database datafile  6 online";
     
114. sql clone "alter database datafile  24 online";
     
115. # recover pdb
     
116. recover clone database tablespace  "SYSTEM", "UNDOTBS1", "SYSAUX", "USERS", "CDATA" pluggable database
     
117. 'PDB2'   delete archivelog;
     
118. sql clone 'alter database open read only';
     
119. plsql <<<begin
     
120.    add_dropped_ts;
     
121. end; >>>;
     
122. plsql <<<begin
     
123.    save_pdb_clean_scn;
     
124. end; >>>;
     
125. # shutdown clone before import
     
126. shutdown clone abort
     
127. plsql <<<begin
     
128.    pdbpitr_inspect(pdbname =>  'PDB2');
     
129. end; >>>;
     
130. }
     
131. executing Memory Script
     
132. 
     
133. executing command: SET until clause
     
134. 
     
135. sql statement: alter database datafile  1 online
     
136. 
     
137. sql statement: alter database datafile  4 online
     
138. 
     
139. sql statement: alter database datafile  3 online
     
140. 
     
141. sql statement: alter database datafile  10 online
     
142. 
     
143. sql statement: alter database datafile  11 online
     
144. 
     
145. sql statement: alter database datafile  25 online
     
146. 
     
147. sql statement: alter database datafile  28 online
     
148. 
     
149. sql statement: alter database datafile  6 online
     
150. 
     
151. sql statement: alter database datafile  24 online
     
152. 
     
153. Starting recover at 2018-07-07:17:34:12
     
154. using channel ORA_AUX_DISK_1
     
155. using channel ORA_AUX_DISK_2
     
156. using channel ORA_AUX_DISK_3
     
157. using channel ORA_AUX_DISK_4
     
158. using channel ORA_AUX_DISK_5
     
159. using channel ORA_AUX_DISK_6
     
160. using channel ORA_AUX_DISK_7
     
161. using channel ORA_AUX_DISK_8
     
162. using channel ORA_AUX_DISK_9
     
163. using channel ORA_AUX_DISK_10
     
164. using channel ORA_AUX_DISK_11
     
165. using channel ORA_AUX_DISK_12
     
166. using channel ORA_AUX_SBT_TAPE_1
     
167. using channel ORA_AUX_SBT_TAPE_2
     
168. 
     
169. starting media recovery
     
170. 
     
171. archived log for thread 1 with sequence 75 is already on disk as file /u01/app/oracle/fast_recovery_area/CDB2/archivelog/2018_07_07/o1_mf_1_75_fn0xky39_.arc
     
172. archived log for thread 1 with sequence 76 is already on disk as file /u01/app/oracle/fast_recovery_area/CDB2/archivelog/2018_07_07/o1_mf_1_76_fn0xo05c_.arc
     
173. archived log for thread 1 with sequence 77 is already on disk as file /u01/app/oracle/fast_recovery_area/CDB2/archivelog/2018_07_07/o1_mf_1_77_fn0y8bos_.arc
     
174. archived log for thread 1 with sequence 78 is already on disk as file /u01/app/oracle/fast_recovery_area/CDB2/archivelog/2018_07_07/o1_mf_1_78_fn0yccqb_.arc
     
175. archived log for thread 1 with sequence 79 is already on disk as file /u01/app/oracle/fast_recovery_area/CDB2/archivelog/2018_07_07/o1_mf_1_79_fn10ffrg_.arc
     
176. archived log for thread 1 with sequence 80 is already on disk as file /u01/app/oracle/fast_recovery_area/CDB2/archivelog/2018_07_07/o1_mf_1_80_fn1250l0_.arc
     
177. archived log file name=/u01/app/oracle/fast_recovery_area/CDB2/archivelog/2018_07_07/o1_mf_1_75_fn0xky39_.arc thread=1 sequence=75
     
178. archived log file name=/u01/app/oracle/fast_recovery_area/CDB2/archivelog/2018_07_07/o1_mf_1_76_fn0xo05c_.arc thread=1 sequence=76
     
179. archived log file name=/u01/app/oracle/fast_recovery_area/CDB2/archivelog/2018_07_07/o1_mf_1_77_fn0y8bos_.arc thread=1 sequence=77
     
180. archived log file name=/u01/app/oracle/fast_recovery_area/CDB2/archivelog/2018_07_07/o1_mf_1_78_fn0yccqb_.arc thread=1 sequence=78
     
181. archived log file name=/u01/app/oracle/fast_recovery_area/CDB2/archivelog/2018_07_07/o1_mf_1_79_fn10ffrg_.arc thread=1 sequence=79
     
182. archived log file name=/u01/app/oracle/fast_recovery_area/CDB2/archivelog/2018_07_07/o1_mf_1_80_fn1250l0_.arc thread=1 sequence=80
     
183. media recovery complete, elapsed time: 00:00:15
     
184. Finished recover at 2018-07-07:17:34:28
     
185. 
     
186. sql statement: alter database open read only
     
187. 
     
188. 
     
189. 
     
190. Oracle instance shut down
     
191. 
     
192. 
     
193. Removing automatic instance
     
194. Automatic instance removed
     
195. auxiliary instance file /u01/app/oracle/oradata/CDB2/datafile/o1_mf_sysaux_fn1286nd_.dbf deleted
     
196. auxiliary instance file /u01/app/oracle/oradata/CDB2/controlfile/o1_mf_fn126z64_.ctl deleted
     
197. Finished recover at 2018-07-07:17:34:35
     
198. 
     
199. RMAN>
     

复制代码

这时候，就需要执行pluggable database的resetlogs;

1. [oracle@station90 ~]$ sqlplus /nolog
   
2. 
   
3. SQL*Plus: Release 12.1.0.2.0 Production on Sat Jul 7 17:37:38 2018
   
4. 
   
5. Copyright (c) 1982, 2014, Oracle.  All rights reserved.
   
6. 
   
7. SQL> conn / as sysdba
   
8. Connected.
   
9. SQL> select  con_id, name , open_mode from v$pdbs;
   
10. 
    
11.     CON_ID NAME                           OPEN_MODE
    
12. ---------- ------------------------------ ----------
    
13.          2 PDB$SEED                          READ ONLY
    
14.          3 PDB2                           MOUNTED
    
15.          4 PDB2_2                          READ WRITE
    
16. 
    
17. SQL> alter pluggable database PDB2;
    
18. alter pluggable database PDB2
    
19.                             *
    
20. ERROR at line 1:
    
21. ORA-00922: missing or invalid option
    
22. 
    
23. 
    
24. SQL> alter pluggable database PDB2 open;
    
25. alter pluggable database PDB2 open
    
26. *
    
27. ERROR at line 1:
    
28. ORA-01113: file 28 needs media recovery
    
29. ORA-01110: data file 28: '/u01/app/oracle/oradata/cdb2/pdb2_1/dj_pdb2.f'
    
30. 
    
31. 
    
32. SQL> alter pluggable database PDB2 open resetlogs;
    
33. 
    
34. Pluggable database altered.
    
35. 
    

复制代码

------------------------多租户书的P224的实验7-15用的Cloud Control可以改成dbca：


12.1.0.2的时候，绝对不能选择下图的默认按钮：