设为首页收藏本站

Botang唐波's Oracle Station

查看: 339|回复: 6

课程第38/39/40次(2018-05-06星期日上下午,2018-05-07星期一)

[复制链接]

719

主题

1081

帖子

7868

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
7868
发表于 2018-5-6 09:31:05 | 显示全部楼层 |阅读模式
改数据库名字的控制文件脚本与Set2重建控制文件的脚本,只差一个单词,前者在create controlfile后多"set"。
Set1重建控制文件的脚本与Set2重建控制文件的脚本,只差两个字母,前者是"noresetlogs"后者是"resetlogs"。

克隆脚本1(ASM-ASM):
  1. run {
  2. duplicate target database to dbclone1
  3. from active database
  4. nofilenamecheck
  5. spfile
  6. set
  7. control_files='+DATA','+FRA'
  8. set
  9. db_file_name_convert='+DATA/orcl/','+DATA/dbclone1'
  10. set
  11. log_file_name_convert='+DATA/orcl','+DATA/dbclone1','+FRA/orcl','+FRA/dbclone1'
  12. set
  13. audit_file_dest='/u01/app/oracle/admin/dbclone1/adump'
  14. set
  15. memory_target='4G';
  16. }
复制代码

克隆脚本2(ASM-ext4,顺便试验跳过表空间):

检查表空间能不能“自包含”:

  1. begin
  2.    dbms_tts.transport_set_check('tbs1,tbs2,example');
  3. end;

  4. select  * from  transport_set_violations;
复制代码
  1. run{
  2. duplicate target database to dbclone2
  3. from active database
  4. nofilenamecheck
  5. skip tablespace 'TBS1','TBS2'
  6. spfile
  7. set
  8. control_files='/u01/app/oracle/oradata/dbclone2/control01.ctl','/u01/app/oracle/oradata/dbclone2/control02.ctl','/u01/app/oracle/oradata/dbclone2/control03.ctl'
  9. set
  10. db_file_name_convert='+DATA/orcl/datafile/example.265.816169651','/u01/app/oracle/oradata/dbclone2/example01.dbf','+DATA/orcl/datafile/users.259.816169553','/u01/app/oracle/oradata/dbclone2/users01.dbf','+DATA/orcl/datafile/undotbs1.258.816169553','/u01/app/oracle/oradata/dbclone2/undotbs01.dbf','+DATA/orcl/datafile/sysaux.257.816169553','/u01/app/oracle/oradata/dbclone2/sysaux01.dbf','+DATA/orcl/datafile/system.256.816169553','/u01/app/oracle/oradata/dbclone2/system01.dbf'
  11. set
  12. log_file_name_convert='+DATA/orcl/onlinelog/group_1.261.816169635','/u01/app/oracle/oradata/dbclone2/redo01a.log','+FRA/orcl/onlinelog/group_1.257.816169637','/u01/app/oracle/oradata/dbclone2/redo01b.log','+DATA/orcl/onlinelog/group_3.263.816169641','/u01/app/oracle/oradata/dbclone2/redo03a.log','+FRA/orcl/onlinelog/group_3.259.816169641','/u01/app/oracle/oradata/dbclone2/redo03b.log','+DATA/orcl/onlinelog/group_2.262.816169639','/u01/app/oracle/oradata/dbclone2/redo02a.log','+FRA/orcl/onlinelog/group_2.258.816169639','/u01/app/oracle/oradata/dbclone2/redo02b.log'
  13. set
  14. audit_file_dest='/u01/app/oracle/admin/dbclone2/adump'
  15. set
  16. db_create_file_dest=''
  17. set
  18. db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
  19. set
  20. memory_target='4G'
  21. ;
  22. }
复制代码

输出:
[oracle@station87 ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Mon May 7 00:10:10 2018

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

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area  271437824 bytes
Fixed Size            2227456 bytes
Variable Size          213910272 bytes
Database Buffers       50331648 bytes
Redo Buffers            4968448 bytes
ORA-00205: error in identifying control file, check alert log for more info


SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@station87 ~]$ rman target sys/oracle_4U@orcl auxiliary sys/oracle_4U@dbclone2 cmdfile=dbclone2.rcv

Recovery Manager: Release 11.2.0.3.0 - Production on Mon May 7 00:10:24 2018

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

connected to target database: ORCL (DBID=1343950367)
connected to auxiliary database: DBCLONE2 (not mounted)

RMAN> run{
2> duplicate target database to dbclone2
3> from active database
4> nofilenamecheck
5> skip tablespace 'TBS1','TBS2'
6> spfile
7> set
8> control_files='/u01/app/oracle/oradata/dbclone2/control01.ctl','/u01/app/oracle/oradata/dbclone2/control02.ctl','/u01/app/oracle/oradata/dbclone2/control03.ctl'
9> set
10> db_file_name_convert='+DATA/orcl/datafile/example.265.816169651','/u01/app/oracle/oradata/dbclone2/example01.dbf','+DATA/orcl/datafile/users.259.816169553','/u01/app/oracle/oradata/dbclone2/users01.dbf','+DATA/orcl/datafile/undotbs1.258.816169553','/u01/app/oracle/oradata/dbclone2/undotbs01.dbf','+DATA/orcl/datafile/sysaux.257.816169553','/u01/app/oracle/oradata/dbclone2/sysaux01.dbf','+DATA/orcl/datafile/system.256.816169553','/u01/app/oracle/oradata/dbclone2/system01.dbf'
11> set
12> log_file_name_convert='+DATA/orcl/onlinelog/group_1.261.816169635','/u01/app/oracle/oradata/dbclone2/redo01a.log','+FRA/orcl/onlinelog/group_1.257.816169637','/u01/app/oracle/oradata/dbclone2/redo01b.log','+DATA/orcl/onlinelog/group_3.263.816169641','/u01/app/oracle/oradata/dbclone2/redo03a.log','+FRA/orcl/onlinelog/group_3.259.816169641','/u01/app/oracle/oradata/dbclone2/redo03b.log','+DATA/orcl/onlinelog/group_2.262.816169639','/u01/app/oracle/oradata/dbclone2/redo02a.log','+FRA/orcl/onlinelog/group_2.258.816169639','/u01/app/oracle/oradata/dbclone2/redo02b.log'
13> set
14> audit_file_dest='/u01/app/oracle/admin/dbclone2/adump'
15> set
16> db_create_file_dest=''
17> set
18> db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
19> set
20> memory_target='4G'
21> ;
22> }
23>
Starting Duplicate Db at 07-MAY-18
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=429 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=5 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=146 device type=DISK
allocated channel: ORA_AUX_DISK_4
channel ORA_AUX_DISK_4: SID=288 device type=DISK
allocated channel: ORA_AUX_DISK_5
channel ORA_AUX_DISK_5: SID=430 device type=DISK
allocated channel: ORA_AUX_DISK_6
channel ORA_AUX_DISK_6: SID=6 device type=DISK
allocated channel: ORA_AUX_DISK_7
channel ORA_AUX_DISK_7: SID=147 device type=DISK
allocated channel: ORA_AUX_DISK_8
channel ORA_AUX_DISK_8: SID=289 device type=DISK

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '+DATA/orcl/spfileorcl.ora' auxiliary format
'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfiledbclone2.ora'   ;
   sql clone "alter system set spfile= ''/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfiledbclone2.ora''";
}
executing Memory Script

Starting backup at 07-MAY-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=15 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=138 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=79 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=9 device type=DISK
allocated channel: ORA_DISK_5
channel ORA_DISK_5: SID=78 device type=DISK
allocated channel: ORA_DISK_6
channel ORA_DISK_6: SID=135 device type=DISK
allocated channel: ORA_DISK_7
channel ORA_DISK_7: SID=200 device type=DISK
allocated channel: ORA_DISK_8
channel ORA_DISK_8: SID=11 device type=DISK
Finished backup at 07-MAY-18

sql statement: alter system set spfile= ''/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfiledbclone2.ora''

contents of Memory Script:
{
   sql clone "alter system set  db_name =
''DBCLONE2'' comment=
''duplicate'' scope=spfile";
   sql clone "alter system set  control_files =
''/u01/app/oracle/oradata/dbclone2/control01.ctl'', ''/u01/app/oracle/oradata/dbclone2/control02.ctl'', ''/u01/app/oracle/oradata/dbclone2/control03.ctl'' comment=
'''' scope=spfile";
   sql clone "alter system set  db_file_name_convert =
''+DATA/orcl/datafile/example.265.816169651'', ''/u01/app/oracle/oradata/dbclone2/example01.dbf'', ''+DATA/orcl/datafile/users.259.816169553'', ''/u01/app/oracle/oradata/dbclone2/users01.dbf'', ''+DATA/orcl/datafile/undotbs1.258.816169553'', ''/u01/app/oracle/oradata/dbclone2/undotbs01.dbf'', ''+DATA/orcl/datafile/sysaux.257.816169553'', ''/u01/app/oracle/oradata/dbclone2/sysaux01.dbf'', ''+DATA/orcl/datafile/system.256.816169553'', ''/u01/app/oracle/oradata/dbclone2/system01.dbf'' comment=
'''' scope=spfile";
   sql clone "alter system set  log_file_name_convert =
''+DATA/orcl/onlinelog/group_1.261.816169635'', ''/u01/app/oracle/oradata/dbclone2/redo01a.log'', ''+FRA/orcl/onlinelog/group_1.257.816169637'', ''/u01/app/oracle/oradata/dbclone2/redo01b.log'', ''+DATA/orcl/onlinelog/group_3.263.816169641'', ''/u01/app/oracle/oradata/dbclone2/redo03a.log'', ''+FRA/orcl/onlinelog/group_3.259.816169641'', ''/u01/app/oracle/oradata/dbclone2/redo03b.log'', ''+DATA/orcl/onlinelog/group_2.262.816169639'', ''/u01/app/oracle/oradata/dbclone2/redo02a.log'', ''+FRA/orcl/onlinelog/group_2.258.816169639'', ''/u01/app/oracle/oradata/dbclone2/redo02b.log'' comment=
'''' scope=spfile";
   sql clone "alter system set  audit_file_dest =
''/u01/app/oracle/admin/dbclone2/adump'' comment=
'''' scope=spfile";
   sql clone "alter system set  db_create_file_dest =
'''' comment=
'''' scope=spfile";
   sql clone "alter system set  db_recovery_file_dest =
''/u01/app/oracle/fast_recovery_area'' comment=
'''' scope=spfile";
   sql clone "alter system set  memory_target =
4G comment=
'''' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''DBCLONE2'' comment= ''duplicate'' scope=spfile

sql statement: alter system set  control_files =  ''/u01/app/oracle/oradata/dbclone2/control01.ctl'', ''/u01/app/oracle/oradata/dbclone2/control02.ctl'', ''/u01/app/oracle/oradata/dbclone2/control03.ctl'' comment= '''' scope=spfile

sql statement: alter system set  db_file_name_convert =  ''+DATA/orcl/datafile/example.265.816169651'', ''/u01/app/oracle/oradata/dbclone2/example01.dbf'', ''+DATA/orcl/datafile/users.259.816169553'', ''/u01/app/oracle/oradata/dbclone2/users01.dbf'', ''+DATA/orcl/datafile/undotbs1.258.816169553'', ''/u01/app/oracle/oradata/dbclone2/undotbs01.dbf'', ''+DATA/orcl/datafile/sysaux.257.816169553'', ''/u01/app/oracle/oradata/dbclone2/sysaux01.dbf'', ''+DATA/orcl/datafile/system.256.816169553'', ''/u01/app/oracle/oradata/dbclone2/system01.dbf'' comment= '''' scope=spfile

sql statement: alter system set  log_file_name_convert =  ''+DATA/orcl/onlinelog/group_1.261.816169635'', ''/u01/app/oracle/oradata/dbclone2/redo01a.log'', ''+FRA/orcl/onlinelog/group_1.257.816169637'', ''/u01/app/oracle/oradata/dbclone2/redo01b.log'', ''+DATA/orcl/onlinelog/group_3.263.816169641'', ''/u01/app/oracle/oradata/dbclone2/redo03a.log'', ''+FRA/orcl/onlinelog/group_3.259.816169641'', ''/u01/app/oracle/oradata/dbclone2/redo03b.log'', ''+DATA/orcl/onlinelog/group_2.262.816169639'', ''/u01/app/oracle/oradata/dbclone2/redo02a.log'', ''+FRA/orcl/onlinelog/group_2.258.816169639'', ''/u01/app/oracle/oradata/dbclone2/redo02b.log'' comment= '''' scope=spfile

sql statement: alter system set  audit_file_dest =  ''/u01/app/oracle/admin/dbclone2/adump'' comment= '''' scope=spfile

sql statement: alter system set  db_create_file_dest =  '''' comment= '''' scope=spfile

sql statement: alter system set  db_recovery_file_dest =  ''/u01/app/oracle/fast_recovery_area'' comment= '''' scope=spfile

sql statement: alter system set  memory_target =  4G comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    4275781632 bytes

Fixed Size                     2235208 bytes
Variable Size               2298479800 bytes
Database Buffers            1962934272 bytes
Redo Buffers                  12132352 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
''ORCL'' comment=
''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
''DBCLONE2'' comment=
''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   backup as copy current controlfile auxiliary format  '/u01/app/oracle/oradata/dbclone2/control01.ctl';
   restore clone controlfile to  '/u01/app/oracle/oradata/dbclone2/control02.ctl' from
'/u01/app/oracle/oradata/dbclone2/control01.ctl';
   restore clone controlfile to  '/u01/app/oracle/oradata/dbclone2/control03.ctl' from
'/u01/app/oracle/oradata/dbclone2/control01.ctl';
   alter clone database mount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''ORCL'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''DBCLONE2'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area    4275781632 bytes

Fixed Size                     2235208 bytes
Variable Size               2298479800 bytes
Database Buffers            1962934272 bytes
Redo Buffers                  12132352 bytes

Starting backup at 07-MAY-18
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5
using channel ORA_DISK_6
using channel ORA_DISK_7
using channel ORA_DISK_8
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_orcl.f tag=TAG20180506T161051 RECID=9 STAMP=975427852
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 07-MAY-18

Starting restore at 07-MAY-18
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=63 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=129 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=192 device type=DISK
allocated channel: ORA_AUX_DISK_4
channel ORA_AUX_DISK_4: SID=6 device type=DISK
allocated channel: ORA_AUX_DISK_5
channel ORA_AUX_DISK_5: SID=67 device type=DISK
allocated channel: ORA_AUX_DISK_6
channel ORA_AUX_DISK_6: SID=130 device type=DISK
allocated channel: ORA_AUX_DISK_7
channel ORA_AUX_DISK_7: SID=194 device type=DISK
allocated channel: ORA_AUX_DISK_8
channel ORA_AUX_DISK_8: SID=7 device type=DISK

channel ORA_AUX_DISK_2: skipped, AUTOBACKUP already found
channel ORA_AUX_DISK_3: skipped, AUTOBACKUP already found
channel ORA_AUX_DISK_4: skipped, AUTOBACKUP already found
channel ORA_AUX_DISK_5: skipped, AUTOBACKUP already found
channel ORA_AUX_DISK_6: skipped, AUTOBACKUP already found
channel ORA_AUX_DISK_7: skipped, AUTOBACKUP already found
channel ORA_AUX_DISK_8: skipped, AUTOBACKUP already found
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 07-MAY-18

Starting restore at 07-MAY-18
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4
using channel ORA_AUX_DISK_5
using channel ORA_AUX_DISK_6
using channel ORA_AUX_DISK_7
using channel ORA_AUX_DISK_8

channel ORA_AUX_DISK_2: skipped, AUTOBACKUP already found
channel ORA_AUX_DISK_3: skipped, AUTOBACKUP already found
channel ORA_AUX_DISK_4: skipped, AUTOBACKUP already found
channel ORA_AUX_DISK_5: skipped, AUTOBACKUP already found
channel ORA_AUX_DISK_6: skipped, AUTOBACKUP already found
channel ORA_AUX_DISK_7: skipped, AUTOBACKUP already found
channel ORA_AUX_DISK_8: skipped, AUTOBACKUP already found
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 07-MAY-18

database mounted
Checking that duplicated tablespaces are self-contained
Datafile 6 skipped by request
Datafile 7 skipped by request
RMAN-05529: WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.

contents of Memory Script:
{
   set newname for datafile  1 to
"/u01/app/oracle/oradata/dbclone2/system01.dbf";
   set newname for datafile  2 to
"/u01/app/oracle/oradata/dbclone2/sysaux01.dbf";
   set newname for datafile  3 to
"/u01/app/oracle/oradata/dbclone2/undotbs01.dbf";
   set newname for datafile  4 to
"/u01/app/oracle/oradata/dbclone2/users01.dbf";
   set newname for datafile  5 to
"/u01/app/oracle/oradata/dbclone2/example01.dbf";
   backup as copy reuse
   datafile  1 auxiliary format
"/u01/app/oracle/oradata/dbclone2/system01.dbf"   datafile
2 auxiliary format
"/u01/app/oracle/oradata/dbclone2/sysaux01.dbf"   datafile
3 auxiliary format
"/u01/app/oracle/oradata/dbclone2/undotbs01.dbf"   datafile
4 auxiliary format
"/u01/app/oracle/oradata/dbclone2/users01.dbf"   datafile
5 auxiliary format
"/u01/app/oracle/oradata/dbclone2/example01.dbf"   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 07-MAY-18
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5
using channel ORA_DISK_6
using channel ORA_DISK_7
using channel ORA_DISK_8
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATA/orcl/datafile/system.256.816169553
channel ORA_DISK_2: starting datafile copy
input datafile file number=00003 name=+DATA/orcl/datafile/undotbs1.258.816169553
channel ORA_DISK_3: starting datafile copy
input datafile file number=00002 name=+DATA/orcl/datafile/sysaux.257.816169553
channel ORA_DISK_4: starting datafile copy
input datafile file number=00005 name=+DATA/orcl/datafile/example.265.816169651
channel ORA_DISK_5: starting datafile copy
input datafile file number=00004 name=+DATA/orcl/datafile/users.259.816169553
output file name=/u01/app/oracle/oradata/dbclone2/users01.dbf tag=TAG20180506T161118
channel ORA_DISK_5: datafile copy complete, elapsed time: 00:00:02
output file name=/u01/app/oracle/oradata/dbclone2/example01.dbf tag=TAG20180506T161118
channel ORA_DISK_4: datafile copy complete, elapsed time: 00:00:08
output file name=/u01/app/oracle/oradata/dbclone2/system01.dbf tag=TAG20180506T161118
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:16
output file name=/u01/app/oracle/oradata/dbclone2/undotbs01.dbf tag=TAG20180506T161118
channel ORA_DISK_2: datafile copy complete, elapsed time: 00:00:16
output file name=/u01/app/oracle/oradata/dbclone2/sysaux01.dbf tag=TAG20180506T161118
channel ORA_DISK_3: datafile copy complete, elapsed time: 00:00:16
Finished backup at 07-MAY-18

sql statement: alter system archive log current

contents of Memory Script:
{
   backup as copy reuse
   archivelog like  "+FRA/orcl/archivelog/2018_05_06/thread_1_seq_114.326.975427895" auxiliary format
"/u01/app/oracle/fast_recovery_area/DBCLONE2/archivelog/2018_05_07/o1_mf_1_114_%u_.arc"   ;
   catalog clone recovery area;
   switch clone datafile all;
}
executing Memory Script

Starting backup at 07-MAY-18
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5
using channel ORA_DISK_6
using channel ORA_DISK_7
using channel ORA_DISK_8
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=114 RECID=106 STAMP=975427895
output file name=/u01/app/oracle/fast_recovery_area/DBCLONE2/archivelog/2018_05_07/o1_mf_1_114_2ot27m9o_.arc RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 07-MAY-18

searching for all files in the recovery area

List of Files Unknown to the Database
=====================================
File Name: /u01/app/oracle/fast_recovery_area/DBCLONE2/archivelog/2018_05_07/o1_mf_1_114_2ot27m9o_.arc
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/fast_recovery_area/DBCLONE2/archivelog/2018_05_07/o1_mf_1_114_2ot27m9o_.arc

datafile 1 switched to datafile copy
input datafile copy RECID=9 STAMP=975456696 file name=/u01/app/oracle/oradata/dbclone2/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=10 STAMP=975456697 file name=/u01/app/oracle/oradata/dbclone2/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=11 STAMP=975456697 file name=/u01/app/oracle/oradata/dbclone2/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=12 STAMP=975456697 file name=/u01/app/oracle/oradata/dbclone2/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=13 STAMP=975456697 file name=/u01/app/oracle/oradata/dbclone2/example01.dbf

contents of Memory Script:
{
   set until scn  2082579;
   recover
   clone database
   skip forever tablespace  "TBS2",
"TBS1"    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 07-MAY-18
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4
using channel ORA_AUX_DISK_5
using channel ORA_AUX_DISK_6
using channel ORA_AUX_DISK_7
using channel ORA_AUX_DISK_8

Executing: alter database datafile 7 offline drop
Executing: alter database datafile 6 offline drop
starting media recovery

archived log for thread 1 with sequence 114 is already on disk as file /u01/app/oracle/fast_recovery_area/DBCLONE2/archivelog/2018_05_07/o1_mf_1_114_2ot27m9o_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/DBCLONE2/archivelog/2018_05_07/o1_mf_1_114_2ot27m9o_.arc thread=1 sequence=114
media recovery complete, elapsed time: 00:00:01
Finished recover at 07-MAY-18
Oracle instance started

Total System Global Area    4275781632 bytes

Fixed Size                     2235208 bytes
Variable Size               2298479800 bytes
Database Buffers            1962934272 bytes
Redo Buffers                  12132352 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
''DBCLONE2'' comment=
''Reset to original value by RMAN'' scope=spfile";
   sql clone "alter system reset  db_unique_name scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''DBCLONE2'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    4275781632 bytes

Fixed Size                     2235208 bytes
Variable Size               2298479800 bytes
Database Buffers            1962934272 bytes
Redo Buffers                  12132352 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DBCLONE2" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
LOGFILE
  GROUP   1 ( '/u01/app/oracle/oradata/dbclone2/redo01a.log', '/u01/app/oracle/oradata/dbclone2/redo01b.log' ) SIZE 50 M  REUSE,
  GROUP   2 ( '/u01/app/oracle/oradata/dbclone2/redo02a.log', '/u01/app/oracle/oradata/dbclone2/redo02b.log' ) SIZE 50 M  REUSE,
  GROUP   3 ( '/u01/app/oracle/oradata/dbclone2/redo03a.log', '/u01/app/oracle/oradata/dbclone2/redo03b.log' ) SIZE 50 M  REUSE
DATAFILE
  '/u01/app/oracle/oradata/dbclone2/system01.dbf'
CHARACTER SET AL32UTF8


contents of Memory Script:
{
   set newname for tempfile  1 to
"+data";
   switch clone tempfile all;
   catalog clone datafilecopy  "/u01/app/oracle/oradata/dbclone2/sysaux01.dbf",
"/u01/app/oracle/oradata/dbclone2/undotbs01.dbf",
"/u01/app/oracle/oradata/dbclone2/users01.dbf",
"/u01/app/oracle/oradata/dbclone2/example01.dbf";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +data in control file

cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/dbclone2/sysaux01.dbf RECID=1 STAMP=975456718
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/dbclone2/undotbs01.dbf RECID=2 STAMP=975456718
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/dbclone2/users01.dbf RECID=3 STAMP=975456718
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/dbclone2/example01.dbf RECID=4 STAMP=975456718

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=975456718 file name=/u01/app/oracle/oradata/dbclone2/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=975456718 file name=/u01/app/oracle/oradata/dbclone2/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=975456718 file name=/u01/app/oracle/oradata/dbclone2/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=975456718 file name=/u01/app/oracle/oradata/dbclone2/example01.dbf
Reenabling controlfile options for auxiliary database
Executing: alter database enable block change tracking using file '+FRA/orcl/changetracking/ctf.260.829329115'
ORACLE error from auxiliary database: ORA-19751: could not create the change tracking file
ORA-19750: change tracking file: '+FRA/orcl/changetracking/ctf.260.829329115'
ORA-17502: ksfdcre:4 Failed to create file +FRA/orcl/changetracking/ctf.260.829329115
ORA-15046: ASM file name '+FRA/orcl/changetracking/ctf.260.829329115' is not in single-file creation form

Ignoring error, reattempt command after duplicate finishes

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Dropping offline and skipped tablespaces
Executing: drop tablespace "TBS2" including contents cascade constraints
Executing: drop tablespace "TBS1" including contents cascade constraints
Finished Duplicate Db at 07-MAY-18

Recovery Manager complete.
[oracle@station87 ~]$

---------------------------------------------------------------
纯用备份(顺便试一下书上后部新语法,同时手工分配auxiliary通道):

先在源头数据库做:
  1. run {
  2. allocate channel  c1 device type disk format '/home/oracle/backup/%U';
  3. allocate channel  c2 device type disk format '/home/oracle/backup/%U';
  4. allocate channel  c3 device type disk format '/home/oracle/backup/%U';
  5. allocate channel  c4 device type disk format '/home/oracle/backup/%U';
  6. allocate channel  c5 device type disk format '/home/oracle/backup/%U';
  7. allocate channel  c6 device type disk format '/home/oracle/backup/%U';
  8. allocate channel  c7 device type disk format '/home/oracle/backup/%U';
  9. allocate channel  c8 device type disk format '/home/oracle/backup/%U';
  10. backup database plus archivelog force;
  11. backup spfile;
  12. backup current controlfile;
  13. }
复制代码
把/home/oracle/backup/拷贝到远程主机,保持目录结构:

  1. run {
  2. allocate  auxiliary channel c1 device type disk;
  3. allocate  auxiliary channel c2 device type disk;
  4. allocate  auxiliary channel c3 device type disk;
  5. allocate  auxiliary channel c4 device type disk;
  6. allocate  auxiliary channel c5 device type disk;
  7. allocate  auxiliary channel c6 device type disk;
  8. allocate  auxiliary channel c7 device type disk;
  9. allocate  auxiliary channel c8 device type disk;
  10. duplicate database to dbclone3
  11. backup location '/home/oracle/backup'
  12. nofilenamecheck  
  13. db_file_name_convert '+DATA/orcl/datafile','/u01/app/oracle/oradata/dbclone3'
  14. logfile
  15. group 1 ('/u01/app/oracle/oradata/dbclone3/redo01.log') size 50M,
  16. group 2 ('/u01/app/oracle/oradata/dbclone3/redo02.log') size 50M,
  17. group 3 ('/u01/app/oracle/oradata/dbclone3/redo03.log') size 50M
  18. spfile
  19. parameter_value_convert '+DATA','/u01/app/oracle/oradata/dbclone3','+FRA','/u01/app/oracle/fast_recovery_area','/u01/app/oracle/admin/orcl/adump','/u01/app/oracle/admin/dbclone3/adump'
  20. set
  21. control_files='/u01/app/oracle/oradata/dbclone3/control01.ctl','/u01/app/oracle/oradata/dbclone3/control02.ctl','/u01/app/oracle/oradata/dbclone3/control03.ctl'
  22. set
  23. memory_target='4G';
  24. }
复制代码

课程最后要做一下物理坏块和逻辑坏块样本:
block_corrupt.png

MOS视频:

http://124.16.180.178:8080/studentguide_sec_O11g/MOS/

游客,本帖隐藏的内容需要积分高于 100 才可浏览,您当前积分为 0



RMAN> run {
2> set maxcorrupt for datafile 9 to 1;
3> backup datafile 9;
4> }

RMAN> recover datafile 9 block 136  restore until time 'sysdate-1';

-------------------
物理坏块破坏脚本11.2.0.3适用:

  1. #!/bin/sh
  2. v_dbname=orcl
  3. su - oracle -c "/u01/app/oracle/product/11.2.0/dbhome_1/bin/srvctl stop database -d $v_dbname -o abort"
  4. su - oracle -c "/u01/app/oracle/product/11.2.0/dbhome_1/bin/sqlplus /nolog" <<EOF
  5. conn / as sysdba
  6. shutdown abort
  7. startup mount exclusive
  8. EOF

  9. rm -f /home/oracle/example01.dbf 2>/dev/null

  10. su - oracle -c "/u01/app/oracle/product/11.2.0/dbhome_1/bin/rman target /" <<EOF
  11. backup as copy datafile 5 format '/home/oracle/example01.dbf';
  12. EOF

  13. dd if=/dev/zero of=/home/oracle/example01.dbf bs=8192 count=1 seek=175 conv=notrunc

  14. su - oracle -c "export ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/grid ; export ORACLE_SID=+ASM ; /u01/app/oracle/product/11.2.0.3/grid/bin/asmcmd" <<EOF
  15. rm -f +data/$v_dbname/DATAFILE/example*
  16. EOF

  17. su - oracle -c "/u01/app/oracle/product/11.2.0/dbhome_1/bin/rman target /" <<EOF
  18. run {
  19. set maxcorrupt for datafile 5 to 1;
  20. backup as copy  datafilecopy '/home/oracle/example01.dbf' format '+data';
  21. }
  22. EOF

  23. sleep 3

  24. su - oracle -c "/u01/app/oracle/product/11.2.0/dbhome_1/bin/rman target /" <<EOF
  25. delete noprompt datafilecopy '/home/oracle/example01.dbf';
  26. switch datafile 5 to copy;
  27. EOF


  28. su - oracle -c "/u01/app/oracle/product/11.2.0/dbhome_1/bin/sqlplus /nolog" <<EOF
  29. conn / as sysdba
  30. alter database open;
  31. EOF
复制代码
11.2.0.1:
  1. #!/bin/sh
  2. v_dbname=orcl
  3. su - oracle -c "/u01/app/oracle/product/11.2.0/dbhome_1/bin/srvctl stop database -d $v_dbname -o abort"
  4. su - oracle -c "/u01/app/oracle/product/11.2.0/dbhome_1/bin/sqlplus /nolog" <<EOF
  5. conn / as sysdba
  6. startup mount exclusive
  7. EOF

  8. rm -f /home/oracle/example01.dbf 2>/dev/null

  9. su - oracle -c "/u01/app/oracle/product/11.2.0/dbhome_1/bin/rman target /" <<EOF
  10. backup as copy datafile 5 format '/home/oracle/example01.dbf';
  11. EOF

  12. dd if=/dev/zero of=/home/oracle/example01.dbf bs=8192 count=1 seek=175 conv=notrunc

  13. su - oracle -c "export ORACLE_HOME=/u01/app/oracle/product/11.2.0/grid ; export ORACLE_SID=+ASM ; /u01/app/oracle/product/11.2.0/grid/bin/asmcmd" <<EOF
  14. rm -f +data/$v_dbname/DATAFILE/example*
  15. EOF

  16. su - oracle -c "/u01/app/oracle/product/11.2.0/dbhome_1/bin/rman target /" <<EOF
  17. run {
  18. set maxcorrupt for datafile 5 to 1;
  19. backup as copy  datafilecopy '/home/oracle/example01.dbf' format '+data';
  20. }
  21. EOF

  22. sleep 3

  23. su - oracle -c "/u01/app/oracle/product/11.2.0/dbhome_1/bin/rman target /" <<EOF
  24. delete noprompt datafilecopy '/home/oracle/example01.dbf';
  25. switch datafile 5 to copy;
  26. EOF


  27. su - oracle -c "/u01/app/oracle/product/11.2.0/dbhome_1/bin/sqlplus /nolog" <<EOF
  28. conn / as sysdba
  29. alter database open;
  30. EOF
复制代码



回复

使用道具 举报

0

主题

3

帖子

24

积分

新手上路

Rank: 1

积分
24
发表于 2018-5-8 17:31:59 | 显示全部楼层
克隆脚本2(ASM-ext4,顺便试验跳过表空间):出现RMAN-06019: 无法转换表空间名称"TBS1",脚本中skip tablespace 'TBS1'

启动 backup 于 08-5月 -18
使用通道 ORA_DISK_1
使用通道 ORA_DISK_2
通道 ORA_DISK_1: 启动数据文件副本
复制当前控制文件
输出文件名=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_orcl.f 标记=TA
80508T172053 RECID=5 STAMP=975604854
通道 ORA_DISK_1: 数据文件复制完毕, 经过时间: 00:00:01
完成 backup 于 08-5月 -18

启动 restore 于 08-5月 -18
分配的通道: ORA_AUX_DISK_1
通道 ORA_AUX_DISK_1: SID=572 设备类型=DISK
分配的通道: ORA_AUX_DISK_2
通道 ORA_AUX_DISK_2: SID=1141 设备类型=DISK

通道 ORA_AUX_DISK_2: 跳过, AUTOBACKUP 已经找到
通道 ORA_AUX_DISK_1: 已复制控制文件副本
完成 restore 于 08-5月 -18

启动 restore 于 08-5月 -18
使用通道 ORA_AUX_DISK_1
使用通道 ORA_AUX_DISK_2

通道 ORA_AUX_DISK_2: 跳过, AUTOBACKUP 已经找到
通道 ORA_AUX_DISK_1: 已复制控制文件副本
完成 restore 于 08-5月 -18

数据库已装载
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: Duplicate Db 命令 (在 05/08/2018 17:21:05 上) 失败
RMAN-20202: Tablespace not found in the recovery catalog
RMAN-06019: 无法转换表空间名称"TBS1"

恢复管理器完成。
回复 支持 反对

使用道具 举报

719

主题

1081

帖子

7868

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
7868
 楼主| 发表于 2018-5-9 09:15:37 | 显示全部楼层
hongzheng 发表于 2018-5-8 17:31
克隆脚本2(ASM-ext4,顺便试验跳过表空间):出现RMAN-06019: 无法转换表空间名称"TBS1",脚本中skip tabl ...

源头库里有这个表空间吧。
回复 支持 反对

使用道具 举报

0

主题

3

帖子

24

积分

新手上路

Rank: 1

积分
24
发表于 2018-5-9 10:16:31 | 显示全部楼层
是的,源数据库是由tbs1这个表空间。
SQL> select  tablespace_name,status from dba_tablespaces  where  tablespace_name='TBS1';

TABLESPACE_NAME                STATUS
------------------------------ ---------
TBS1                           ONLINE


回复 支持 反对

使用道具 举报

3

主题

12

帖子

79

积分

注册会员

Rank: 2

积分
79
发表于 2018-5-9 12:05:48 | 显示全部楼层
老师我按照你的步骤无法制造出逻辑坏块,感觉应该是我无法下线数据文件,在我offline表空间后,对应的datafile依然是available的。
过程:
  1. [oracle@station4 ~]$ conn

  2. SQL*Plus: Release 11.2.0.4.0 Production on Wed May 9 19:15:35 2018

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


  4. Connected to:
  5. Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
  6. With the Partitioning, Automatic Storage Management, OLAP, Data Mining
  7. and Real Application Testing options

  8. SQL> create tablespace tbs_logical2 datafile size 10M nologging;

  9. Tablespace created.


  10. SQL> conn hr/oracle_4U
  11. Connected.
  12. SQL>
  13. SQL>
  14. SQL>
  15. SQL> create table t_logical2 ( a number , b varchar2(20)) tablespace tbs_logical2;

  16. Table created.

  17. SQL> insert into t_logical2 values (20001,'mysql') ;

  18. 1 row created.

  19. SQL> insert /* +append */ into t_logical2 select * from t_logical2;

  20. 1 row created.

  21. SQL> commit;

  22. Commit complete.

  23. SQL>
  24. SQL> create index idx_a2 on t_logical2(a);

  25. Index created.

  26. SQL> create index idx_b2 on t_logical2(b);

  27. Index created.


  28. SQL> commit;

  29. Commit complete.

  30. SQL> conn / as sysdba
  31. Connected.
  32. SQL> alter tablespace tbs_logical2 offline immediate;

  33. Tablespace altered.

  34. SQL>
  35. SQL> select t.TABLESPACE_NAME,t.STATUS from DBA_TABLESPACES t where t.TABLESPACE_NAME='TBS_LOGICAL2';

  36. TABLESPACE_NAME                STATUS
  37. ------------------------------ ---------
  38. TBS_LOGICAL2                       OFFLINE


  39. SQL> set linesize 100
  40. SQL> select t.FILE_NAME,t.FILE_ID,t.STATUS from DBA_DATA_FILES t where t.TABLESPACE_NAME='TBS_LOGICAL2';

  41. FILE_NAME
  42. ----------------------------------------------------------------------------------------------------
  43.    FILE_ID STATUS
  44. ---------- ---------
  45. +DATA/orcl/datafile/tbs_logical2.259.975698171
  46.          7 AVAILABLE


  47. SQL>  alter tablespace tbs_logical2 online;
  48. alter tablespace tbs_logical2 online
  49. *
  50. ERROR at line 1:
  51. ORA-01113: file 7 needs media recovery
  52. ORA-01110: data file 7: '+DATA/orcl/datafile/tbs_logical2.259.975698171'


  53. SQL> exit
  54. Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
  55. With the Partitioning, Automatic Storage Management, OLAP, Data Mining
  56. and Real Application Testing options
  57. [oracle@station4 ~]$ . oraenv
  58. ORACLE_SID = [orcl] ? +ASM
  59. The Oracle base remains unchanged with value /u01/app/oracle
  60. [oracle@station4 ~]$ asmcmd rm +DATA/orcl/datafile/tbs_logical2.259.975698171
  61. [oracle@station4 ~]$ . oraenv
  62. ORACLE_SID = [+ASM] ? orcl
  63. The Oracle base remains unchanged with value /u01/app/oracle
  64. [oracle@station4 ~]$ conn

  65. SQL*Plus: Release 11.2.0.4.0 Production on Wed May 9 19:57:49 2018

  66. Copyright (c) 1982, 2013, Oracle.  All rights reserved.


  67. Connected to:
  68. Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
  69. With the Partitioning, Automatic Storage Management, OLAP, Data Mining
  70. and Real Application Testing options

  71. SQL> alter database create datafile 7 as new;

  72. Database altered.

  73. SQL> recover datafile 7;
  74. Media recovery complete.
  75. SQL> alter tablespace  tbs_logical2 online;

  76. Tablespace altered.

  77. SQL> conn hr/oracle_4U
  78. Connected.
  79. SQL> select * from t_logical2;

  80.          A B
  81. ---------- --------------------
  82.      20001 mysql
  83.      20001 mysql

  84. SQL>
复制代码


回复 支持 反对

使用道具 举报

719

主题

1081

帖子

7868

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
7868
 楼主| 发表于 2018-5-9 15:59:17 | 显示全部楼层
hongzheng 发表于 2018-5-9 10:16
是的,源数据库是由tbs1这个表空间。
SQL> select  tablespace_name,status from dba_tablespaces  where  ...
  1. run{
  2. duplicate target database to dbclone2
  3. from active database
  4. nofilenamecheck
  5. skip tablespace 'TBS1','TBS2'
  6. spfile
  7. ......
复制代码


以上语法,确认一下。
回复 支持 反对

使用道具 举报

719

主题

1081

帖子

7868

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
7868
 楼主| 发表于 2018-5-9 16:00:08 | 显示全部楼层
何帆 发表于 2018-5-9 12:05
老师我按照你的步骤无法制造出逻辑坏块,感觉应该是我无法下线数据文件,在我offline表空间后,对应的dataf ...

那么直接面对数据文件下线吧。
回复 支持 反对

使用道具 举报

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

本版积分规则

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

GMT+8, 2018-9-20 14:51 , Processed in 0.138167 second(s), 26 queries .

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