通过RMAN将数据库恢复到同类机异机

--通过RMAN将数据库恢复到同类机异机

--下面开始讲源路径称为host1，新路径称为host2

SYS@PROD2> select dbid from v$database; --查询DBID      DBID----------1564287740RMAN> backup incremental level 0 as compressed backupset database include current controlfile tag='full' format '/tmp/bak/db_%s_%p_%t' plus archivelog tag='arch_all' format  '/tmp/bak/arch_%s_%p_%t' ;--全备份host1Starting backup at 23-NOV-16current log archivedusing target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=44 device type=DISKchannel ORA_DISK_1: starting compressed archived log backup setchannel ORA_DISK_1: specifying archived log(s) in backup setinput archived log thread=1 sequence=16 RECID=1 STAMP=928617160input archived log thread=1 sequence=17 RECID=2 STAMP=928617187input archived log thread=1 sequence=18 RECID=3 STAMP=928617299input archived log thread=1 sequence=19 RECID=4 STAMP=928619001input archived log thread=1 sequence=20 RECID=5 STAMP=928619007input archived log thread=1 sequence=21 RECID=6 STAMP=928619021input archived log thread=1 sequence=22 RECID=7 STAMP=928619031input archived log thread=1 sequence=23 RECID=8 STAMP=928702784input archived log thread=1 sequence=24 RECID=9 STAMP=928702801input archived log thread=1 sequence=25 RECID=10 STAMP=928702939input archived log thread=1 sequence=26 RECID=11 STAMP=928703364channel ORA_DISK_1: starting piece 1 at 23-NOV-16channel ORA_DISK_1: finished piece 1 at 23-NOV-16piece handle=/tmp/bak/arch_12_1_928703365 tag=ARCH_ALL comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:15Finished backup at 23-NOV-16Starting backup at 23-NOV-16using channel ORA_DISK_1channel ORA_DISK_1: starting compressed incremental level 0 datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setinput datafile file number=00001 name=/u01/app/oracle/oradata/PROD2/system01.dbfinput datafile file number=00002 name=/u01/app/oracle/oradata/PROD2/sysaux01.dbfinput datafile file number=00005 name=/u01/app/oracle/oradata/PROD2/example01.dbfinput datafile file number=00003 name=/u01/app/oracle/oradata/PROD2/undotbs01.dbfinput datafile file number=00004 name=/u01/app/oracle/oradata/PROD2/users01.dbfchannel ORA_DISK_1: starting piece 1 at 23-NOV-16channel ORA_DISK_1: finished piece 1 at 23-NOV-16piece handle=/tmp/bak/db_13_1_928703380 tag=FULL comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:01:05channel ORA_DISK_1: starting compressed incremental level 0 datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setincluding current control file in backup setincluding current SPFILE in backup setchannel ORA_DISK_1: starting piece 1 at 23-NOV-16channel ORA_DISK_1: finished piece 1 at 23-NOV-16piece handle=/tmp/bak/db_14_1_928703445 tag=FULL comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01Finished backup at 23-NOV-16Starting backup at 23-NOV-16current log archivedusing channel ORA_DISK_1channel ORA_DISK_1: starting compressed archived log backup setchannel ORA_DISK_1: specifying archived log(s) in backup setinput archived log thread=1 sequence=27 RECID=12 STAMP=928703448channel ORA_DISK_1: starting piece 1 at 23-NOV-16channel ORA_DISK_1: finished piece 1 at 23-NOV-16piece handle=/tmp/bak/arch_15_1_928703448 tag=ARCH_ALL comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01Finished backup at 23-NOV-16[root@ocm1 ~]# scp /tmp/bak/* ocm2:/tmp/bak  --传输到host2上root@ocm2's password: arch_12_1_928703365                              100%   64MB  64.3MB/s   00:01    arch_15_1_928703448                              100% 1011KB   1.0MB/s   00:01    db_13_1_928703380                                100%  312MB  44.6MB/s   00:07    db_14_1_928703445                                100% 1072KB   1.1MB/s   00:00  在host2上RMAN> restore spfile from '/tmp/bak/db_14_1_928703445';  --恢复spfileRMAN> shutdown immediateOracle instance shut downRMAN> set DBID=1564287740executing command: SET DBIDRMAN> STARTUP NOMOUNTconnected to target database (not started)RMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of startup command at 11/24/2016 21:16:20RMAN-04014: startup failed: ORA-09925: Unable to create audit trail fileLinux Error: 2: No such file or directoryAdditional information: 9925--需要创建对应的目录[oracle@ocm2 ~]$ cat /u01/app/oracle/product/11.2.0/dbhome_1/dbs/initPROD2.ora  --查看存在哪些目录PROD2.__db_cache_size=637534208PROD2.__java_pool_size=4194304PROD2.__large_pool_size=4194304PROD2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environmentPROD2.__pga_aggregate_target=322961408PROD2.__sga_target=960495616PROD2.__shared_io_pool_size=0PROD2.__shared_pool_size=306184192PROD2.__streams_pool_size=0*.audit_file_dest='/u01/app/oracle/admin/PROD2/adump'*.audit_trail='db'*.compatible='11.2.0.0.0'*.control_files='/u01/app/oracle/oradata/PROD2/control01.ctl','/u01/app/oracle/fast_recovery_area/PROD2/control02.ctl'*.db_block_size=8192*.db_domain=''*.db_name='PROD2'*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'*.db_recovery_file_dest_size=4322230272*.diagnostic_dest='/u01/app/oracle'*.dispatchers='(PROTOCOL=TCP) (SERVICE=PROD2XDB)'*.open_cursors=300*.pga_aggregate_target=319815680*.processes=150*.remote_login_passwordfile='EXCLUSIVE'*.sga_target=960495616*.undo_tablespace='UNDOTBS1'[oracle@ocm2 ~]$ mkdir /u01/app/oracle/oradata/PROD2  --创建[oracle@ocm2 ~]$ mkdir /u01/app/oracle/admin/PROD2/adump -pRMAN> shutdown immediateOracle instance shut downRMAN> set DBID=1564287740executing command: SET DBIDRMAN> STARTUP NOMOUNT  --再次启动到nomount阶段connected to target database (not started)Oracle instance startedTotal System Global Area     958341120 bytesFixed Size                     1348972 bytesVariable Size                314575508 bytesDatabase Buffers             637534208 bytesRedo Buffers                   4882432 bytesRMAN> restore controlfile from '/tmp/bak/db_14_1_928703445';  --尝试恢复控制文件，此处报错需要创建文件夹Starting restore at 24-NOV-16using channel ORA_DISK_1channel ORA_DISK_1: restoring control fileRMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of restore command at 11/24/2016 21:22:19ORA-19504: failed to create file "/u01/app/oracle/fast_recovery_area/PROD2/control02.ctl"ORA-27040: file create error, unable to create fileLinux Error: 2: No such file or directoryAdditional information: 1ORA-19600: input file is control file  (/u01/app/oracle/oradata/PROD2/control01.ctl)ORA-19601: output file is control file  (/u01/app/oracle/fast_recovery_area/PROD2/control02.ctl)[oracle@ocm2 ~]$ mkdir /u01/app/oracle/fast_recovery_area/PROD2RMAN> restore controlfile from '/tmp/bak/db_14_1_928703445';  --再次尝试成功Starting restore at 24-NOV-16using channel ORA_DISK_1channel ORA_DISK_1: restoring control filechannel ORA_DISK_1: restore complete, elapsed time: 00:00:02output file name=/u01/app/oracle/oradata/PROD2/control01.ctloutput file name=/u01/app/oracle/fast_recovery_area/PROD2/control02.ctlFinished restore at 24-NOV-16RMAN> alter database mount；  --启动到mount阶段database mountedreleased channel: ORA_DISK_1RMAN> SQL "create spfile from pfile"；  --创建spfilesql statement: create spfile from pfileRMAN> shutdown immediatedatabase dismountedOracle instance shut downRMAN> startup mountconnected to target database (not started)Oracle instance starteddatabase mountedTotal System Global Area     958341120 bytesFixed Size                     1348972 bytesVariable Size                314575508 bytesDatabase Buffers             637534208 bytesRedo Buffers                   4882432 bytesRMAN> crosscheck backup;  --因为还原的控制文件中，包含有备份的信息，不过状态都是expired的Starting implicit crosscheck backup at 24-NOV-16allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=18 device type=DISKCrosschecked 12 objectsFinished implicit crosscheck backup at 24-NOV-16Starting implicit crosscheck copy at 24-NOV-16using channel ORA_DISK_1Finished implicit crosscheck copy at 24-NOV-16searching for all files in the recovery areacataloging files...no files catalogedusing channel ORA_DISK_1crosschecked backup piece: found to be 'EXPIRED'backup piece handle=/tmp/bak/full_03rlj4n3_3_1 RECID=2 STAMP=928617187crosschecked backup piece: found to be 'EXPIRED'backup piece handle=/tmp/bak/full_06rlj4qj_6_1 RECID=5 STAMP=928617299crosschecked backup piece: found to be 'EXPIRED'backup piece handle=/tmp/bak/arch_8_1_928702803 RECID=7 STAMP=928702804crosschecked backup piece: found to be 'EXPIRED'backup piece handle=/tmp/bak/db_9_1_928702829 RECID=8 STAMP=928702829crosschecked backup piece: found to be 'EXPIRED'backup piece handle=/tmp/bak/db_10_1_928702935 RECID=9 STAMP=928702938crosschecked backup piece: found to be 'EXPIRED'backup piece handle=/tmp/bak/arch_11_1_928702939 RECID=10 STAMP=928702939crosschecked backup piece: found to be 'AVAILABLE'backup piece handle=/tmp/bak/arch_12_1_928703365 RECID=11 STAMP=928703365crosschecked backup piece: found to be 'AVAILABLE'backup piece handle=/tmp/bak/db_13_1_928703380 RECID=12 STAMP=928703380Crosschecked 8 objectsRMAN> delete expired backup;  --删除过期备份using channel ORA_DISK_1List of Backup PiecesBP Key  BS Key  Pc# Cp# Status      Device Type Piece Name------- ------- --- --- ----------- ----------- ----------2       2       1   1   EXPIRED     DISK        /tmp/bak/full_03rlj4n3_3_15       5       1   1   EXPIRED     DISK        /tmp/bak/full_06rlj4qj_6_17       7       1   1   EXPIRED     DISK        /tmp/bak/arch_8_1_9287028038       8       1   1   EXPIRED     DISK        /tmp/bak/db_9_1_9287028299       9       1   1   EXPIRED     DISK        /tmp/bak/db_10_1_92870293510      10      1   1   EXPIRED     DISK        /tmp/bak/arch_11_1_928702939Do you really want to delete the above objects (enter YES or NO)? yesdeleted backup piecebackup piece handle=/tmp/bak/full_03rlj4n3_3_1 RECID=2 STAMP=928617187deleted backup piecebackup piece handle=/tmp/bak/full_06rlj4qj_6_1 RECID=5 STAMP=928617299deleted backup piecebackup piece handle=/tmp/bak/arch_8_1_928702803 RECID=7 STAMP=928702804deleted backup piecebackup piece handle=/tmp/bak/db_9_1_928702829 RECID=8 STAMP=928702829deleted backup piecebackup piece handle=/tmp/bak/db_10_1_928702935 RECID=9 STAMP=928702938deleted backup piecebackup piece handle=/tmp/bak/arch_11_1_928702939 RECID=10 STAMP=928702939Deleted 6 EXPIRED objects RMAN> catalog backuppiece '/tmp/bak/arch_12_1_928703365';  --注册相关的备份片cataloged backup piecebackup piece handle=/tmp/bak/arch_12_1_928703365 RECID=13 STAMP=928791434RMAN> catalog backuppiece '/tmp/bak/db_13_1_928703380';cataloged backup piecebackup piece handle=/tmp/bak/db_13_1_928703380 RECID=14 STAMP=928791448RMAN> catalog backuppiece '/tmp/bak/db_14_1_928703445';cataloged backup piecebackup piece handle=/tmp/bak/db_14_1_928703445 RECID=15 STAMP=928791462RMAN> catalog backuppiece '/tmp/bak/arch_15_1_928703448';cataloged backup piecebackup piece handle=/tmp/bak/arch_15_1_928703448 RECID=16 STAMP=928791476[oracle@ocm2 ~]$ export ORACLE_SID=PROD2[oracle@ocm2 ~]$ sqlplus / as sysdba    --查看控制文件是否已经有数据文件和日志文件的结构SQL*Plus: Release 11.2.0.3.0 Production on Thu Nov 24 21:40:47 2016Copyright (c) 1982, 2011, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> select file#, name from v$datafile;     FILE#----------NAME-------------------------------------------------------------------------------- 1/u01/app/oracle/oradata/PROD2/system01.dbf 2/u01/app/oracle/oradata/PROD2/sysaux01.dbf 3/u01/app/oracle/oradata/PROD2/undotbs01.dbf     FILE#----------NAME-------------------------------------------------------------------------------- 4/u01/app/oracle/oradata/PROD2/users01.dbf 5/u01/app/oracle/oradata/PROD2/example01.dbfSQL> select group#, member from v$logfile;    GROUP#----------MEMBER-------------------------------------------------------------------------------- 3/u01/app/oracle/oradata/PROD2/redo03.log 2/u01/app/oracle/oradata/PROD2/redo02.log 1/u01/app/oracle/oradata/PROD2/redo01.logRMAN> restore database;  --restore数据库Starting restore at 24-NOV-16using channel ORA_DISK_1channel ORA_DISK_1: starting datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setchannel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/PROD2/system01.dbfchannel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/PROD2/sysaux01.dbfchannel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/PROD2/undotbs01.dbfchannel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/PROD2/users01.dbfchannel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/PROD2/example01.dbfchannel ORA_DISK_1: reading from backup piece /tmp/bak/db_13_1_928703380channel ORA_DISK_1: piece handle=/tmp/bak/db_13_1_928703380 tag=FULLchannel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:03:02Finished restore at 24-NOV-16RMAN> alter database open resetlogs;  --尝试打开失败RMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of alter db command at 11/24/2016 21:47:07ORA-01152: file 1 was not restored from a sufficiently old backup ORA-01110: data file 1: '/u01/app/oracle/oradata/PROD2/system01.dbf'RMAN> list failure;List of Database Failures=========================Failure ID Priority Status    Time Detected Summary---------- -------- --------- ------------- -------125        CRITICAL OPEN      24-NOV-16     System datafile 1: '/u01/app/oracle/oradata/PROD2/system01.dbf' needs media recovery42         CRITICAL OPEN      24-NOV-16     Control file needs media recovery100        HIGH     OPEN      24-NOV-16     One or more non-system datafiles need media recoveryRMAN> advise failure;List of Database Failures=========================Failure ID Priority Status    Time Detected Summary---------- -------- --------- ------------- -------125        CRITICAL OPEN      24-NOV-16     System datafile 1: '/u01/app/oracle/oradata/PROD2/system01.dbf' needs media recovery42         CRITICAL OPEN      24-NOV-16     Control file needs media recovery100        HIGH     OPEN      24-NOV-16     One or more non-system datafiles need media recoveryanalyzing automatic repair options; this may take some timeusing channel ORA_DISK_1analyzing automatic repair options completeMandatory Manual Actions========================no manual actions availableOptional Manual Actions=======================1. If you have the correct version of the control file, then shutdown the database and replace the old control file2. If you restored the wrong version of data file /u01/app/oracle/oradata/PROD2/system01.dbf, then replace it with the correct one3. If you restored the wrong version of data file /u01/app/oracle/oradata/PROD2/users01.dbf, then replace it with the correct one4. If you restored the wrong version of data file /u01/app/oracle/oradata/PROD2/sysaux01.dbf, then replace it with the correct one5. If you restored the wrong version of data file /u01/app/oracle/oradata/PROD2/undotbs01.dbf, then replace it with the correct one6. If you restored the wrong version of data file /u01/app/oracle/oradata/PROD2/example01.dbf, then replace it with the correct oneAutomated Repair Options========================Option Repair Description------ ------------------1      Perform incomplete database recovery    Strategy: The repair includes point-in-time recovery with some data loss  Repair script: /u01/app/oracle/diag/rdbms/prod2/PROD2/hm/reco_428213938.hmRMAN> repair failure;  --执行不完全恢复Strategy: The repair includes point-in-time recovery with some data lossRepair script: /u01/app/oracle/diag/rdbms/prod2/PROD2/hm/reco_428213938.hmcontents of repair script:   # database point-in-time recovery until a missing log   restore database until scn 1482695;   recover database until scn 1482695;   alter database open resetlogs;Do you really want to execute the above repair (enter YES or NO)? yesexecuting repair scriptStarting restore at 24-NOV-16using channel ORA_DISK_1skipping datafile 1; already restored to file /u01/app/oracle/oradata/PROD2/system01.dbfskipping datafile 2; already restored to file /u01/app/oracle/oradata/PROD2/sysaux01.dbfskipping datafile 3; already restored to file /u01/app/oracle/oradata/PROD2/undotbs01.dbfskipping datafile 4; already restored to file /u01/app/oracle/oradata/PROD2/users01.dbfskipping datafile 5; already restored to file /u01/app/oracle/oradata/PROD2/example01.dbfrestore not done; all files read only, offline, or already restoredFinished restore at 24-NOV-16Starting recover at 24-NOV-16using channel ORA_DISK_1starting media recoverychannel ORA_DISK_1: starting archived log restore to default destinationchannel ORA_DISK_1: restoring archived logarchived log thread=1 sequence=27channel ORA_DISK_1: reading from backup piece /tmp/bak/arch_15_1_928703448channel ORA_DISK_1: piece handle=/tmp/bak/arch_15_1_928703448 tag=ARCH_ALLchannel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:00:01archived log file name=/u01/app/oracle/fast_recovery_area/PROD2/archivelog/2016_11_24/o1_mf_1_27_d3fvwl33_.arc thread=1 sequence=27channel default: deleting archived log(s)archived log file name=/u01/app/oracle/fast_recovery_area/PROD2/archivelog/2016_11_24/o1_mf_1_27_d3fvwl33_.arc RECID=12 STAMP=928792082media recovery complete, elapsed time: 00:00:03Finished recover at 24-NOV-16database openedrepair failure completeSQL> select open_mode from v$database;OPEN_MODE--------------------READ WRITE

至此迁移成功。