设为首页收藏本站

Botang唐波's Oracle Station

查看: 39|回复: 0

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

[复制链接]

685

主题

1045

帖子

7638

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
7638
发表于 2018-7-7 10:29:01 | 显示全部楼层 |阅读模式
多租户AG书的P167页有错,在插件数据库OPEN时坏掉了SYSTEM表空间,不需要关CDB:
a.png


做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. SQL*Plus: Release 11.2.0.1.0 Production on Sat Jul 7 10:41:39 2018

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

  7. SQL> conn  / as sysdba
  8. Connected.
  9. SQL> exec dbms_tts.transport_set_check('USERS');

  10. PL/SQL procedure successfully completed.

  11. SQL> select  * from transport_set_violations;

  12. VIOLATIONS
  13. --------------------------------------------------------------------------------
  14. ORA-39917: SYS owned object TTESTBOTANG_5 in tablespace USERS not allowed in plu
  15. ggable set


  16. SQL> alter table TTESTBOTANG_5  move tablespace system ;

  17. Table altered.

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

  19. PL/SQL procedure successfully completed.

  20. SQL> select  * from transport_set_violations;

  21. no rows selected

  22. SQL>
复制代码

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

  1. /*  SQL> select  username from dba_users where username='OE';

  2. USERNAME
  3. ------------------------------
  4. OE
  5. SH

  6. SQL> drop user oe cascade;

  7. User dropped.

  8. SQL> drop user sh cascade;

  9. User dropped.   */

复制代码


做备份(之前有备份也不需要做)。

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

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

  1. SQL> select  PROPERTY_NAME  , PROPERTY_VALUE  from database_properties where PROPERTY_NAME like '%TABLESPACE%';

  2. PROPERTY_NAME
  3. ------------------------------
  4. PROPERTY_VALUE
  5. --------------------------------------------------------------------------------
  6. DEFAULT_TEMP_TABLESPACE
  7. TEMP

  8. DEFAULT_PERMANENT_TABLESPACE
  9. EXAMPLE
复制代码
  1. [oracle@station86 ~]$ sqlplus /nolog

  2. SQL*Plus: Release 11.2.0.1.0 Production on Sat Jul 7 11:03:06 2018

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

  4. SQL> conn / as sysdba
  5. Connected.
  6. SQL> alter database default tablespace example;

  7. Database altered.

  8. SQL>
复制代码

开始执行PITR:
  1. SQL> alter tablespace users offline immediate ;

  2. Tablespace altered.

  3. SQL>
复制代码
LINUX环境变量:export NLS_DATE_FORMAT='YYYY-MM-DD:HH24:MI:SS'

a1.png

命令和分配通道:
  1. [oracle@station86 ~]$ rman target /

  2. Recovery Manager: Release 11.2.0.1.0 - Production on Sat Jul 7 11:05:31 2018

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

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

  5. RMAN> recover tablespace users
  6. 2> until time '2018-07-07:10:58:17'
  7. 3> auxiliary destination '/u01/app/oracle/oradata';

  8. Starting recover at 2018-07-07:11:08:06
  9. using target database control file instead of recovery catalog
  10. allocated channel: ORA_DISK_1
  11. channel ORA_DISK_1: SID=13 device type=DISK
  12. allocated channel: ORA_DISK_2
  13. channel ORA_DISK_2: SID=99 device type=DISK
  14. allocated channel: ORA_DISK_3
  15. channel ORA_DISK_3: SID=142 device type=DISK
  16. allocated channel: ORA_DISK_4
  17. channel ORA_DISK_4: SID=11 device type=DISK
  18. allocated channel: ORA_DISK_5
  19. channel ORA_DISK_5: SID=52 device type=DISK
  20. allocated channel: ORA_DISK_6
  21. channel ORA_DISK_6: SID=96 device type=DISK
  22. allocated channel: ORA_DISK_7
  23. channel ORA_DISK_7: SID=140 device type=DISK
  24. allocated channel: ORA_DISK_8
  25. channel ORA_DISK_8: SID=9 device type=DISK
  26. allocated channel: ORA_SBT_TAPE_1
  27. channel ORA_SBT_TAPE_1: SID=143 device type=SBT_TAPE
  28. channel ORA_SBT_TAPE_1: Oracle Secure Backup
  29. allocated channel: ORA_SBT_TAPE_2
  30. channel ORA_SBT_TAPE_2: SID=58 device type=SBT_TAPE
  31. 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. List of tablespaces expected to have UNDO segments
  3. Tablespace SYSTEM
  4. Tablespace UNDOTBS1

  5. Creating automatic instance, with SID='epDa'

  6. initialization parameters used for automatic instance:
  7. db_name=ORCL
  8. db_unique_name=epDa_tspitr_ORCL
  9. compatible=11.2.0
  10. db_block_size=8192
  11. db_files=200
  12. sga_target=280M
  13. processes=50
  14. db_create_file_dest=/u01/app/oracle/oradata
  15. log_archive_dest_1='location=/u01/app/oracle/oradata'
  16. #No auxiliary parameter file used


  17. starting up automatic instance ORCL

  18. Oracle instance started

  19. Total System Global Area     292933632 bytes

  20. Fixed Size                     1336092 bytes
  21. Variable Size                100666596 bytes
  22. Database Buffers             184549376 bytes
  23. Redo Buffers                   6381568 bytes
  24. Automatic instance created
  25. Running TRANSPORT_SET_CHECK on recovery set tablespaces
  26. TRANSPORT_SET_CHECK completed successfully

  27. contents of Memory Script:
  28. {
  29. # set requested point in time
  30. set until  time "2018-07-07:10:58:17";
  31. # restore the controlfile
  32. restore clone controlfile;
  33. # mount the controlfile
  34. sql clone 'alter database mount clone database';
  35. # archive current online log
  36. sql 'alter system archive log current';
  37. # avoid unnecessary autobackups for structural changes during TSPITR
  38. sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
  39. }
  40. executing Memory Script

  41. executing command: SET until clause

  42. Starting restore at 2018-07-07:11:09:28
  43. allocated channel: ORA_AUX_DISK_1
  44. channel ORA_AUX_DISK_1: SID=81 device type=DISK
  45. allocated channel: ORA_AUX_DISK_2
  46. channel ORA_AUX_DISK_2: SID=5 device type=DISK
  47. allocated channel: ORA_AUX_DISK_3
  48. channel ORA_AUX_DISK_3: SID=30 device type=DISK
  49. allocated channel: ORA_AUX_DISK_4
  50. channel ORA_AUX_DISK_4: SID=56 device type=DISK
  51. allocated channel: ORA_AUX_DISK_5
  52. channel ORA_AUX_DISK_5: SID=82 device type=DISK
  53. allocated channel: ORA_AUX_DISK_6
  54. channel ORA_AUX_DISK_6: SID=6 device type=DISK
  55. allocated channel: ORA_AUX_DISK_7
  56. channel ORA_AUX_DISK_7: SID=31 device type=DISK
  57. allocated channel: ORA_AUX_DISK_8
  58. channel ORA_AUX_DISK_8: SID=57 device type=DISK
  59. allocated channel: ORA_AUX_SBT_TAPE_1
  60. channel ORA_AUX_SBT_TAPE_1: SID=83 device type=SBT_TAPE
  61. channel ORA_AUX_SBT_TAPE_1: Oracle Secure Backup
  62. allocated channel: ORA_AUX_SBT_TAPE_2
  63. channel ORA_AUX_SBT_TAPE_2: SID=7 device type=SBT_TAPE
  64. channel ORA_AUX_SBT_TAPE_2: Oracle Secure Backup

  65. channel ORA_AUX_SBT_TAPE_1: starting datafile backup set restore
  66. channel ORA_AUX_SBT_TAPE_1: restoring control file
  67. channel ORA_AUX_SBT_TAPE_1: reading from backup piece 58t7d78b_1_1
  68. channel ORA_AUX_SBT_TAPE_1: piece handle=58t7d78b_1_1 tag=TAG20180707T105052
  69. channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
  70. channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:58
  71. output file name=/u01/app/oracle/oradata/ORCL/controlfile/o1_mf_fn0crz7d_.ctl
  72. Finished restore at 2018-07-07:11:10:30

  73. sql statement: alter database mount clone database

  74. sql statement: alter system archive log current

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

  76. contents of Memory Script:
  77. {
  78. # set requested point in time
  79. set until  time "2018-07-07:10:58:17";
  80. # set destinations for recovery set and auxiliary set datafiles
  81. set newname for clone datafile  1 to new;
  82. set newname for clone datafile  3 to new;
  83. set newname for clone datafile  2 to new;
  84. set newname for clone tempfile  1 to new;
  85. set newname for datafile  4 to
  86. "+DATA/orcl/datafile/users.265.980714801";
  87. # switch all tempfiles
  88. switch clone tempfile all;
  89. # restore the tablespaces in the recovery set and the auxiliary set
  90. restore clone datafile  1, 3, 2, 4;
  91. switch clone datafile all;
  92. }
  93. executing Memory Script

  94. executing command: SET until clause

  95. executing command: SET NEWNAME

  96. executing command: SET NEWNAME

  97. executing command: SET NEWNAME

  98. executing command: SET NEWNAME

  99. executing command: SET NEWNAME

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

  101. Starting restore at 2018-07-07:11:10:37
  102. using channel ORA_AUX_DISK_1
  103. using channel ORA_AUX_DISK_2
  104. using channel ORA_AUX_DISK_3
  105. using channel ORA_AUX_DISK_4
  106. using channel ORA_AUX_DISK_5
  107. using channel ORA_AUX_DISK_6
  108. using channel ORA_AUX_DISK_7
  109. using channel ORA_AUX_DISK_8
  110. using channel ORA_AUX_SBT_TAPE_1
  111. using channel ORA_AUX_SBT_TAPE_2

  112. channel ORA_AUX_SBT_TAPE_1: starting datafile backup set restore
  113. channel ORA_AUX_SBT_TAPE_1: specifying datafile(s) to restore from backup set
  114. channel ORA_AUX_SBT_TAPE_1: restoring datafile 00003 to /u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_%u_.dbf
  115. channel ORA_AUX_SBT_TAPE_1: restoring datafile 00002 to /u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_%u_.dbf
  116. channel ORA_AUX_SBT_TAPE_1: reading from backup piece 57t7d74d_1_1
  117. channel ORA_AUX_SBT_TAPE_2: starting datafile backup set restore
  118. channel ORA_AUX_SBT_TAPE_2: specifying datafile(s) to restore from backup set
  119. channel ORA_AUX_SBT_TAPE_2: restoring datafile 00001 to /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_%u_.dbf
  120. channel ORA_AUX_SBT_TAPE_2: restoring datafile 00004 to +DATA/orcl/datafile/users.265.980714801
  121. channel ORA_AUX_SBT_TAPE_2: reading from backup piece 56t7d74d_1_1
  122. channel ORA_AUX_SBT_TAPE_1: piece handle=57t7d74d_1_1 tag=TAG20180707T105052
  123. channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
  124. channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:01:55
  125. channel ORA_AUX_SBT_TAPE_2: piece handle=56t7d74d_1_1 tag=TAG20180707T105052
  126. channel ORA_AUX_SBT_TAPE_2: restored backup piece 1
  127. channel ORA_AUX_SBT_TAPE_2: restore complete, elapsed time: 00:02:15
  128. Finished restore at 2018-07-07:11:12:57

  129. datafile 1 switched to datafile copy
  130. input datafile copy RECID=41 STAMP=980853177 file name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_fn0cvhtz_.dbf
  131. datafile 3 switched to datafile copy
  132. input datafile copy RECID=42 STAMP=980853177 file name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_fn0cvk93_.dbf
  133. datafile 2 switched to datafile copy
  134. 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. executing command: SET until clause

  16. sql statement: alter database datafile  1 online

  17. sql statement: alter database datafile  3 online

  18. sql statement: alter database datafile  2 online

  19. sql statement: alter database datafile  4 online

  20. Starting recover at 2018-07-07:11:12:58
  21. using channel ORA_AUX_DISK_1
  22. using channel ORA_AUX_DISK_2
  23. using channel ORA_AUX_DISK_3
  24. using channel ORA_AUX_DISK_4
  25. using channel ORA_AUX_DISK_5
  26. using channel ORA_AUX_DISK_6
  27. using channel ORA_AUX_DISK_7
  28. using channel ORA_AUX_DISK_8
  29. using channel ORA_AUX_SBT_TAPE_1
  30. using channel ORA_AUX_SBT_TAPE_2

  31. starting media recovery

  32. 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
  33. 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
  34. 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
  35. archived log file name=+FRA/orcl/archivelog/2018_07_07/thread_1_seq_86.257.980852293 thread=1 sequence=86
  36. archived log file name=+FRA/orcl/archivelog/2018_07_07/thread_1_seq_87.286.980852293 thread=1 sequence=87
  37. archived log file name=+FRA/orcl/archivelog/2018_07_07/thread_1_seq_88.355.980852305 thread=1 sequence=88
  38. media recovery complete, elapsed time: 00:00:05
  39. Finished recover at 2018-07-07:11:13:04

  40. 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. sql statement: alter tablespace  USERS read only

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

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

  16. Performing export of metadata...
  17.    EXPDP> Starting "SYS"."TSPITR_EXP_epDa":  
  18.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
  19.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_SPEC
  20.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_BODY
  21.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/XMLSCHEMA/XMLSCHEMA
  22.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
  23.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
  24.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
  25.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
  26.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
  27.    EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
  28.    EXPDP> Master table "SYS"."TSPITR_EXP_slhv" successfully loaded/unloaded
  29.    EXPDP> ******************************************************************************
  30.    EXPDP> Dump file set for SYS.TSPITR_EXP_slhv is:
  31.    EXPDP>   /u01/app/oracle/oradata/tspitr_slhv_63292.dmp
  32.    EXPDP> ******************************************************************************
  33.    EXPDP> Datafiles required for transportable tablespace USERS:
  34.    EXPDP>   +DATA/orcl/datafile/users.259.816169553
  35.    EXPDP> Job "SYS"."TSPITR_EXP_slhv" successfully completed at 15:46:05
  36. Export completed

复制代码
自动地导入。




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


-----------------------------------------插件数据库PITR:

  1. SQL*Plus: Release 12.1.0.2.0 Production on Sat Jul 7 17:01:03 2018

  2. Copyright (c) 1982, 2014, Oracle.  All rights reserved.

  3. SQL> conn / as sysdba
  4. Connected.
  5. SQL> alter pluggable database pdb2 close immediate;

  6. Pluggable database altered.

  7. SQL> exit
复制代码
命令和分配通道:
  1. [oracle@station90 ~]$ rman target /

  2. Recovery Manager: Release 12.1.0.2.0 - Production on Sat Jul 7 17:07:35 2018

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

  4. connected to target database: CDB2 (DBID=727229478)

  5. RMAN> run {
  6. 2> set until time '2018-07-07:16:46:16';
  7. 3> restore pluggable database pdb2;
  8. 4> recover pluggable database pdb2
  9. 5>  auxiliary destination '/u01/app/oracle/oradata';
  10. 6> }

  11. executing command: SET until clause

  12. Starting restore at 2018-07-07:17:07:54
  13. using target database control file instead of recovery catalog
  14. allocated channel: ORA_DISK_1
  15. channel ORA_DISK_1: SID=330 device type=DISK
  16. allocated channel: ORA_DISK_2
  17. channel ORA_DISK_2: SID=631 device type=DISK
  18. allocated channel: ORA_DISK_3
  19. channel ORA_DISK_3: SID=921 device type=DISK
  20. allocated channel: ORA_DISK_4
  21. channel ORA_DISK_4: SID=335 device type=DISK
  22. allocated channel: ORA_DISK_5
  23. channel ORA_DISK_5: SID=937 device type=DISK
  24. allocated channel: ORA_DISK_6
  25. channel ORA_DISK_6: SID=316 device type=DISK
  26. allocated channel: ORA_DISK_7
  27. channel ORA_DISK_7: SID=927 device type=DISK
  28. allocated channel: ORA_DISK_8
  29. channel ORA_DISK_8: SID=25 device type=DISK
  30. allocated channel: ORA_DISK_9
  31. channel ORA_DISK_9: SID=325 device type=DISK
  32. allocated channel: ORA_DISK_10
  33. channel ORA_DISK_10: SID=630 device type=DISK
  34. allocated channel: ORA_DISK_11
  35. channel ORA_DISK_11: SID=935 device type=DISK
  36. allocated channel: ORA_DISK_12
  37. channel ORA_DISK_12: SID=332 device type=DISK
  38. allocated channel: ORA_SBT_TAPE_1
  39. channel ORA_SBT_TAPE_1: SID=941 device type=SBT_TAPE
  40. channel ORA_SBT_TAPE_1: Oracle Secure Backup
  41. allocated channel: ORA_SBT_TAPE_2
  42. channel ORA_SBT_TAPE_2: SID=336 device type=SBT_TAPE
  43. channel ORA_SBT_TAPE_2: Oracle Secure Backup

  44. creating datafile file number=28 name=/u01/app/oracle/oradata/cdb2/pdb2_1/dj_pdb2.f
  45. skipping datafile 10; already restored to file /u01/app/oracle/oradata/cdb2/pdb2_1/system01.dbf
  46. skipping datafile 25; already restored to file /u01/app/oracle/oradata/cdb2/pdb2_1/ldata_01.dbf
  47. skipping datafile 11; already restored to file /u01/app/oracle/oradata/cdb2/pdb2_1/sysaux01.dbf
  48. 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. List of tablespaces expected to have UNDO segments
  18. Tablespace SYSTEM
  19. Tablespace UNDOTBS1

  20. Creating automatic instance, with SID='Fnhk'

  21. initialization parameters used for automatic instance:
  22. db_name=CDB2
  23. db_unique_name=Fnhk_pitr_pdb2_CDB2
  24. compatible=12.1.0.2.0
  25. db_block_size=8192
  26. db_files=200
  27. diagnostic_dest=/u01/app/oracle
  28. _system_trig_enabled=FALSE
  29. sga_target=2560M
  30. processes=200
  31. db_create_file_dest=/u01/app/oracle/oradata
  32. log_archive_dest_1='location=/u01/app/oracle/oradata'
  33. enable_pluggable_database=true
  34. _clone_one_pdb_recovery=true
  35. #No auxiliary parameter file used


  36. starting up automatic instance CDB2

  37. Oracle instance started

  38. Total System Global Area    2684354560 bytes

  39. Fixed Size                     2928008 bytes
  40. Variable Size                603980408 bytes
  41. Database Buffers            2063597568 bytes
  42. Redo Buffers                  13848576 bytes
  43. Automatic instance created

  44. contents of Memory Script:
  45. {
  46. # set requested point in time
  47. set until  time "2018-07-07:16:46:16";
  48. # restore the controlfile
  49. restore clone controlfile;

  50. # mount the controlfile
  51. sql clone 'alter database mount clone database';
  52. }
  53. executing Memory Script

  54. executing command: SET until clause

  55. Starting restore at 2018-07-07:17:08:53
  56. allocated channel: ORA_AUX_DISK_1
  57. channel ORA_AUX_DISK_1: SID=6 device type=DISK
  58. allocated channel: ORA_AUX_DISK_2
  59. channel ORA_AUX_DISK_2: SID=87 device type=DISK
  60. allocated channel: ORA_AUX_DISK_3
  61. channel ORA_AUX_DISK_3: SID=169 device type=DISK
  62. allocated channel: ORA_AUX_DISK_4
  63. channel ORA_AUX_DISK_4: SID=250 device type=DISK
  64. allocated channel: ORA_AUX_DISK_5
  65. channel ORA_AUX_DISK_5: SID=7 device type=DISK
  66. allocated channel: ORA_AUX_DISK_6
  67. channel ORA_AUX_DISK_6: SID=89 device type=DISK
  68. allocated channel: ORA_AUX_DISK_7
  69. channel ORA_AUX_DISK_7: SID=170 device type=DISK
  70. allocated channel: ORA_AUX_DISK_8
  71. channel ORA_AUX_DISK_8: SID=251 device type=DISK
  72. allocated channel: ORA_AUX_DISK_9
  73. channel ORA_AUX_DISK_9: SID=8 device type=DISK
  74. allocated channel: ORA_AUX_DISK_10
  75. channel ORA_AUX_DISK_10: SID=90 device type=DISK
  76. allocated channel: ORA_AUX_DISK_11
  77. channel ORA_AUX_DISK_11: SID=171 device type=DISK
  78. allocated channel: ORA_AUX_DISK_12
  79. channel ORA_AUX_DISK_12: SID=252 device type=DISK
  80. allocated channel: ORA_AUX_SBT_TAPE_1
  81. channel ORA_AUX_SBT_TAPE_1: SID=9 device type=SBT_TAPE
  82. channel ORA_AUX_SBT_TAPE_1: Oracle Secure Backup
  83. allocated channel: ORA_AUX_SBT_TAPE_2
  84. channel ORA_AUX_SBT_TAPE_2: SID=91 device type=SBT_TAPE
  85. channel ORA_AUX_SBT_TAPE_2: Oracle Secure Backup

  86. channel ORA_AUX_DISK_1: starting datafile backup set restore
  87. channel ORA_AUX_DISK_1: restoring control file
  88. 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
  89. 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
  90. channel ORA_AUX_DISK_1: restored backup piece 1
  91. channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
  92. output file name=/u01/app/oracle/oradata/CDB2/controlfile/o1_mf_fn10s9mz_.ctl
  93. Finished restore at 2018-07-07:17:08:58

  94. sql statement: alter database mount clone database

复制代码

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

  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. switch clone datafile all;
  23. }
  24. executing Memory Script

  25. executing command: SET until clause

  26. datafile 10 switched to datafile copy
  27. input datafile copy RECID=3 STAMP=980876013 file name=/u01/app/oracle/oradata/cdb2/pdb2_1/system01.dbf

  28. datafile 11 switched to datafile copy
  29. input datafile copy RECID=4 STAMP=980876013 file name=/u01/app/oracle/oradata/cdb2/pdb2_1/sysaux01.dbf

  30. datafile 25 switched to datafile copy
  31. input datafile copy RECID=5 STAMP=980876013 file name=/u01/app/oracle/oradata/cdb2/pdb2_1/ldata_01.dbf

  32. datafile 28 switched to datafile copy
  33. input datafile copy RECID=6 STAMP=980876013 file name=/u01/app/oracle/oradata/cdb2/pdb2_1/dj_pdb2.f

  34. executing command: SET NEWNAME

  35. executing command: SET NEWNAME

  36. executing command: SET NEWNAME

  37. executing command: SET NEWNAME

  38. executing command: SET NEWNAME

  39. Starting restore at 2018-07-07:17:33:34
  40. using channel ORA_AUX_DISK_1
  41. using channel ORA_AUX_DISK_2
  42. using channel ORA_AUX_DISK_3
  43. using channel ORA_AUX_DISK_4
  44. using channel ORA_AUX_DISK_5
  45. using channel ORA_AUX_DISK_6
  46. using channel ORA_AUX_DISK_7
  47. using channel ORA_AUX_DISK_8
  48. using channel ORA_AUX_DISK_9
  49. using channel ORA_AUX_DISK_10
  50. using channel ORA_AUX_DISK_11
  51. using channel ORA_AUX_DISK_12
  52. using channel ORA_AUX_SBT_TAPE_1
  53. using channel ORA_AUX_SBT_TAPE_2

  54. channel ORA_AUX_SBT_TAPE_1: starting datafile backup set restore
  55. channel ORA_AUX_SBT_TAPE_1: specifying datafile(s) to restore from backup set
  56. channel ORA_AUX_SBT_TAPE_1: restoring datafile 00003 to /u01/app/oracle/oradata/CDB2/datafile/o1_mf_sysaux_%u_.dbf
  57. channel ORA_AUX_SBT_TAPE_1: restoring datafile 00006 to /u01/app/oracle/oradata/CDB2/datafile/o1_mf_users_%u_.dbf
  58. channel ORA_AUX_SBT_TAPE_1: restoring datafile 00024 to /u01/app/oracle/oradata/CDB2/datafile/o1_mf_cdata_%u_.dbf
  59. channel ORA_AUX_SBT_TAPE_1: reading from backup piece 0st7dp06_1_1
  60. channel ORA_AUX_SBT_TAPE_2: starting datafile backup set restore
  61. channel ORA_AUX_SBT_TAPE_2: specifying datafile(s) to restore from backup set
  62. channel ORA_AUX_SBT_TAPE_2: restoring datafile 00001 to /u01/app/oracle/oradata/CDB2/datafile/o1_mf_system_%u_.dbf
  63. channel ORA_AUX_SBT_TAPE_2: restoring datafile 00004 to /u01/app/oracle/oradata/CDB2/datafile/o1_mf_undotbs1_%u_.dbf
  64. channel ORA_AUX_SBT_TAPE_2: reading from backup piece 0rt7dp06_1_1
  65. channel ORA_AUX_SBT_TAPE_1: piece handle=0st7dp06_1_1 tag=TAG20180707T155550
  66. channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
  67. channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:35
  68. channel ORA_AUX_SBT_TAPE_2: piece handle=0rt7dp06_1_1 tag=TAG20180707T155550
  69. channel ORA_AUX_SBT_TAPE_2: restored backup piece 1
  70. channel ORA_AUX_SBT_TAPE_2: restore complete, elapsed time: 00:00:35
  71. Finished restore at 2018-07-07:17:34:10

  72. datafile 1 switched to datafile copy
  73. input datafile copy RECID=12 STAMP=980876050 file name=/u01/app/oracle/oradata/CDB2/datafile/o1_mf_system_fn12874l_.dbf
  74. datafile 4 switched to datafile copy
  75. input datafile copy RECID=13 STAMP=980876050 file name=/u01/app/oracle/oradata/CDB2/datafile/o1_mf_undotbs1_fn12875h_.dbf
  76. datafile 3 switched to datafile copy
  77. input datafile copy RECID=14 STAMP=980876051 file name=/u01/app/oracle/oradata/CDB2/datafile/o1_mf_sysaux_fn1286nd_.dbf
  78. datafile 6 switched to datafile copy
  79. input datafile copy RECID=15 STAMP=980876051 file name=/u01/app/oracle/oradata/CDB2/datafile/o1_mf_users_fn1286or_.dbf
  80. datafile 24 switched to datafile copy
  81. input datafile copy RECID=16 STAMP=980876051 file name=/u01/app/oracle/oradata/CDB2/datafile/o1_mf_cdata_fn1286o2_.dbf

  82. contents of Memory Script:
  83. {
  84. # set requested point in time
  85. set until  time "2018-07-07:17:27:45";
  86. # online the datafiles restored or switched
  87. sql clone "alter database datafile  1 online";
  88. sql clone "alter database datafile  4 online";
  89. sql clone "alter database datafile  3 online";
  90. sql clone 'PDB2' "alter database datafile
  91. 10 online";
  92. sql clone 'PDB2' "alter database datafile
  93. 11 online";
  94. sql clone 'PDB2' "alter database datafile
  95. 25 online";
  96. sql clone 'PDB2' "alter database datafile
  97. 28 online";
  98. sql clone "alter database datafile  6 online";
  99. sql clone "alter database datafile  24 online";
  100. # recover pdb
  101. recover clone database tablespace  "SYSTEM", "UNDOTBS1", "SYSAUX", "USERS", "CDATA" pluggable database
  102. 'PDB2'   delete archivelog;
  103. sql clone 'alter database open read only';
  104. plsql <<<begin
  105.    add_dropped_ts;
  106. end; >>>;
  107. plsql <<<begin
  108.    save_pdb_clean_scn;
  109. end; >>>;
  110. # shutdown clone before import
  111. shutdown clone abort
  112. plsql <<<begin
  113.    pdbpitr_inspect(pdbname =>  'PDB2');
  114. end; >>>;
  115. }
  116. executing Memory Script

  117. executing command: SET until clause

  118. sql statement: alter database datafile  1 online

  119. sql statement: alter database datafile  4 online

  120. sql statement: alter database datafile  3 online

  121. sql statement: alter database datafile  10 online

  122. sql statement: alter database datafile  11 online

  123. sql statement: alter database datafile  25 online

  124. sql statement: alter database datafile  28 online

  125. sql statement: alter database datafile  6 online

  126. sql statement: alter database datafile  24 online

  127. Starting recover at 2018-07-07:17:34:12
  128. using channel ORA_AUX_DISK_1
  129. using channel ORA_AUX_DISK_2
  130. using channel ORA_AUX_DISK_3
  131. using channel ORA_AUX_DISK_4
  132. using channel ORA_AUX_DISK_5
  133. using channel ORA_AUX_DISK_6
  134. using channel ORA_AUX_DISK_7
  135. using channel ORA_AUX_DISK_8
  136. using channel ORA_AUX_DISK_9
  137. using channel ORA_AUX_DISK_10
  138. using channel ORA_AUX_DISK_11
  139. using channel ORA_AUX_DISK_12
  140. using channel ORA_AUX_SBT_TAPE_1
  141. using channel ORA_AUX_SBT_TAPE_2

  142. starting media recovery

  143. 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
  144. 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
  145. 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
  146. 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
  147. 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
  148. 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
  149. 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
  150. 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
  151. 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
  152. 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
  153. 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
  154. 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
  155. media recovery complete, elapsed time: 00:00:15
  156. Finished recover at 2018-07-07:17:34:28

  157. sql statement: alter database open read only



  158. Oracle instance shut down


  159. Removing automatic instance
  160. Automatic instance removed
  161. auxiliary instance file /u01/app/oracle/oradata/CDB2/datafile/o1_mf_sysaux_fn1286nd_.dbf deleted
  162. auxiliary instance file /u01/app/oracle/oradata/CDB2/controlfile/o1_mf_fn126z64_.ctl deleted
  163. Finished recover at 2018-07-07:17:34:35

  164. RMAN>
复制代码
这时候,就需要执行pluggable database的resetlogs;
  1. [oracle@station90 ~]$ sqlplus /nolog

  2. SQL*Plus: Release 12.1.0.2.0 Production on Sat Jul 7 17:37:38 2018

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

  4. SQL> conn / as sysdba
  5. Connected.
  6. SQL> select  con_id, name , open_mode from v$pdbs;

  7.     CON_ID NAME                           OPEN_MODE
  8. ---------- ------------------------------ ----------
  9.          2 PDB$SEED                          READ ONLY
  10.          3 PDB2                           MOUNTED
  11.          4 PDB2_2                          READ WRITE

  12. SQL> alter pluggable database PDB2;
  13. alter pluggable database PDB2
  14.                             *
  15. ERROR at line 1:
  16. ORA-00922: missing or invalid option


  17. SQL> alter pluggable database PDB2 open;
  18. alter pluggable database PDB2 open
  19. *
  20. ERROR at line 1:
  21. ORA-01113: file 28 needs media recovery
  22. ORA-01110: data file 28: '/u01/app/oracle/oradata/cdb2/pdb2_1/dj_pdb2.f'


  23. SQL> alter pluggable database PDB2 open resetlogs;

  24. Pluggable database altered.

复制代码

------------------------多租户书的P224的实验7-15用的Cloud Control可以改成dbca:
a.png

12.1.0.2的时候,绝对不能选择下图的默认按钮:
b.png




b.png
回复

使用道具 举报

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

本版积分规则

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

GMT+8, 2018-7-22 12:29 , Processed in 0.237859 second(s), 27 queries .

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