本文主要是介绍通过RMAN将数据库恢复到同类机异机,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!
--通过RMAN将数据库恢复到同类机异机--下面开始讲源路径称为host1,新路径称为host2
SYS@PROD2> select dbid from v$database; --查询DBIDDBID
----------
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-16
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=44 device type=DISK
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=16 RECID=1 STAMP=928617160
input archived log thread=1 sequence=17 RECID=2 STAMP=928617187
input archived log thread=1 sequence=18 RECID=3 STAMP=928617299
input archived log thread=1 sequence=19 RECID=4 STAMP=928619001
input archived log thread=1 sequence=20 RECID=5 STAMP=928619007
input archived log thread=1 sequence=21 RECID=6 STAMP=928619021
input archived log thread=1 sequence=22 RECID=7 STAMP=928619031
input archived log thread=1 sequence=23 RECID=8 STAMP=928702784
input archived log thread=1 sequence=24 RECID=9 STAMP=928702801
input archived log thread=1 sequence=25 RECID=10 STAMP=928702939
input archived log thread=1 sequence=26 RECID=11 STAMP=928703364
channel ORA_DISK_1: starting piece 1 at 23-NOV-16
channel ORA_DISK_1: finished piece 1 at 23-NOV-16
piece handle=/tmp/bak/arch_12_1_928703365 tag=ARCH_ALL comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 23-NOV-16Starting backup at 23-NOV-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed incremental level 0 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/PROD2/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/PROD2/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/PROD2/example01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/PROD2/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/PROD2/users01.dbf
channel ORA_DISK_1: starting piece 1 at 23-NOV-16
channel ORA_DISK_1: finished piece 1 at 23-NOV-16
piece handle=/tmp/bak/db_13_1_928703380 tag=FULL comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting compressed incremental level 0 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 23-NOV-16
channel ORA_DISK_1: finished piece 1 at 23-NOV-16
piece handle=/tmp/bak/db_14_1_928703445 tag=FULL comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 23-NOV-16Starting backup at 23-NOV-16
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=27 RECID=12 STAMP=928703448
channel ORA_DISK_1: starting piece 1 at 23-NOV-16
channel ORA_DISK_1: finished piece 1 at 23-NOV-16
piece handle=/tmp/bak/arch_15_1_928703448 tag=ARCH_ALL comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished 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:20
RMAN-04014: startup failed: ORA-09925: Unable to create audit trail file
Linux Error: 2: No such file or directory
Additional information: 9925--需要创建对应的目录
[oracle@ocm2 ~]$ cat /u01/app/oracle/product/11.2.0/dbhome_1/dbs/initPROD2.ora --查看存在哪些目录
PROD2.__db_cache_size=637534208
PROD2.__java_pool_size=4194304
PROD2.__large_pool_size=4194304
PROD2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
PROD2.__pga_aggregate_target=322961408
PROD2.__sga_target=960495616
PROD2.__shared_io_pool_size=0
PROD2.__shared_pool_size=306184192
PROD2.__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 bytes
Variable Size 314575508 bytes
Database Buffers 637534208 bytes
Redo Buffers 4882432 bytesRMAN> restore controlfile from '/tmp/bak/db_14_1_928703445'; --尝试恢复控制文件,此处报错需要创建文件夹Starting restore at 24-NOV-16
using channel ORA_DISK_1channel ORA_DISK_1: restoring control file
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 11/24/2016 21:22:19
ORA-19504: failed to create file "/u01/app/oracle/fast_recovery_area/PROD2/control02.ctl"
ORA-27040: file create error, unable to create file
Linux Error: 2: No such file or directory
Additional information: 1
ORA-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-16
using channel ORA_DISK_1channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output file name=/u01/app/oracle/oradata/PROD2/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/PROD2/control02.ctl
Finished restore at 24-NOV-16RMAN> alter database mount; --启动到mount阶段database mounted
released channel: ORA_DISK_1RMAN> SQL "create spfile from pfile"; --创建spfilesql statement: create spfile from pfileRMAN> shutdown immediatedatabase dismounted
Oracle instance shut downRMAN> startup mountconnected to target database (not started)
Oracle instance started
database mountedTotal System Global Area 958341120 bytesFixed Size 1348972 bytes
Variable Size 314575508 bytes
Database Buffers 637534208 bytes
Redo Buffers 4882432 bytesRMAN> crosscheck backup; --因为还原的控制文件中,包含有备份的信息,不过状态都是expired的Starting implicit crosscheck backup at 24-NOV-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
Crosschecked 12 objects
Finished implicit crosscheck backup at 24-NOV-16Starting implicit crosscheck copy at 24-NOV-16
using channel ORA_DISK_1
Finished implicit crosscheck copy at 24-NOV-16searching for all files in the recovery area
cataloging files...
no files catalogedusing channel ORA_DISK_1
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/tmp/bak/full_03rlj4n3_3_1 RECID=2 STAMP=928617187
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/tmp/bak/full_06rlj4qj_6_1 RECID=5 STAMP=928617299
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/tmp/bak/arch_8_1_928702803 RECID=7 STAMP=928702804
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/tmp/bak/db_9_1_928702829 RECID=8 STAMP=928702829
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/tmp/bak/db_10_1_928702935 RECID=9 STAMP=928702938
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/tmp/bak/arch_11_1_928702939 RECID=10 STAMP=928702939
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/tmp/bak/arch_12_1_928703365 RECID=11 STAMP=928703365
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/tmp/bak/db_13_1_928703380 RECID=12 STAMP=928703380
Crosschecked 8 objectsRMAN> delete expired backup; --删除过期备份using channel ORA_DISK_1List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
2 2 1 1 EXPIRED DISK /tmp/bak/full_03rlj4n3_3_1
5 5 1 1 EXPIRED DISK /tmp/bak/full_06rlj4qj_6_1
7 7 1 1 EXPIRED DISK /tmp/bak/arch_8_1_928702803
8 8 1 1 EXPIRED DISK /tmp/bak/db_9_1_928702829
9 9 1 1 EXPIRED DISK /tmp/bak/db_10_1_928702935
10 10 1 1 EXPIRED DISK /tmp/bak/arch_11_1_928702939Do you really want to delete the above objects (enter YES or NO)? yes
deleted backup piece
backup piece handle=/tmp/bak/full_03rlj4n3_3_1 RECID=2 STAMP=928617187
deleted backup piece
backup piece handle=/tmp/bak/full_06rlj4qj_6_1 RECID=5 STAMP=928617299
deleted backup piece
backup piece handle=/tmp/bak/arch_8_1_928702803 RECID=7 STAMP=928702804
deleted backup piece
backup piece handle=/tmp/bak/db_9_1_928702829 RECID=8 STAMP=928702829
deleted backup piece
backup piece handle=/tmp/bak/db_10_1_928702935 RECID=9 STAMP=928702938
deleted backup piece
backup piece handle=/tmp/bak/arch_11_1_928702939 RECID=10 STAMP=928702939
Deleted 6 EXPIRED objectsRMAN> catalog backuppiece '/tmp/bak/arch_12_1_928703365'; --注册相关的备份片cataloged backup piece
backup piece handle=/tmp/bak/arch_12_1_928703365 RECID=13 STAMP=928791434RMAN> catalog backuppiece '/tmp/bak/db_13_1_928703380';cataloged backup piece
backup piece handle=/tmp/bak/db_13_1_928703380 RECID=14 STAMP=928791448RMAN> catalog backuppiece '/tmp/bak/db_14_1_928703445';cataloged backup piece
backup piece handle=/tmp/bak/db_14_1_928703445 RECID=15 STAMP=928791462RMAN> catalog backuppiece '/tmp/bak/arch_15_1_928703448';cataloged backup piece
backup 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 - Production
With 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.dbf2
/u01/app/oracle/oradata/PROD2/sysaux01.dbf3
/u01/app/oracle/oradata/PROD2/undotbs01.dbfFILE#
----------
NAME
--------------------------------------------------------------------------------4
/u01/app/oracle/oradata/PROD2/users01.dbf5
/u01/app/oracle/oradata/PROD2/example01.dbfSQL> select group#, member from v$logfile;GROUP#
----------
MEMBER
--------------------------------------------------------------------------------3
/u01/app/oracle/oradata/PROD2/redo03.log2
/u01/app/oracle/oradata/PROD2/redo02.log1
/u01/app/oracle/oradata/PROD2/redo01.logRMAN> restore database; --restore数据库Starting restore at 24-NOV-16
using channel ORA_DISK_1channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/PROD2/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/PROD2/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/PROD2/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/PROD2/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/PROD2/example01.dbf
channel ORA_DISK_1: reading from backup piece /tmp/bak/db_13_1_928703380
channel ORA_DISK_1: piece handle=/tmp/bak/db_13_1_928703380 tag=FULL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:03:02
Finished 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:07
ORA-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 recovery
42 CRITICAL OPEN 24-NOV-16 Control file needs media recovery
100 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 recovery
42 CRITICAL OPEN 24-NOV-16 Control file needs media recovery
100 HIGH OPEN 24-NOV-16 One or more non-system datafiles need media recoveryanalyzing automatic repair options; this may take some time
using channel ORA_DISK_1
analyzing 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 file
2. If you restored the wrong version of data file /u01/app/oracle/oradata/PROD2/system01.dbf, then replace it with the correct one
3. If you restored the wrong version of data file /u01/app/oracle/oradata/PROD2/users01.dbf, then replace it with the correct one
4. If you restored the wrong version of data file /u01/app/oracle/oradata/PROD2/sysaux01.dbf, then replace it with the correct one
5. If you restored the wrong version of data file /u01/app/oracle/oradata/PROD2/undotbs01.dbf, then replace it with the correct one
6. 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 lossRepair 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 loss
Repair script: /u01/app/oracle/diag/rdbms/prod2/PROD2/hm/reco_428213938.hmcontents of repair script:# database point-in-time recovery until a missing logrestore 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)? yes
executing repair scriptStarting restore at 24-NOV-16
using channel ORA_DISK_1skipping datafile 1; already restored to file /u01/app/oracle/oradata/PROD2/system01.dbf
skipping datafile 2; already restored to file /u01/app/oracle/oradata/PROD2/sysaux01.dbf
skipping datafile 3; already restored to file /u01/app/oracle/oradata/PROD2/undotbs01.dbf
skipping datafile 4; already restored to file /u01/app/oracle/oradata/PROD2/users01.dbf
skipping datafile 5; already restored to file /u01/app/oracle/oradata/PROD2/example01.dbf
restore not done; all files read only, offline, or already restored
Finished restore at 24-NOV-16Starting recover at 24-NOV-16
using channel ORA_DISK_1starting media recoverychannel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=27
channel ORA_DISK_1: reading from backup piece /tmp/bak/arch_15_1_928703448
channel ORA_DISK_1: piece handle=/tmp/bak/arch_15_1_928703448 tag=ARCH_ALL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/fast_recovery_area/PROD2/archivelog/2016_11_24/o1_mf_1_27_d3fvwl33_.arc thread=1 sequence=27
channel 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=928792082
media recovery complete, elapsed time: 00:00:03
Finished recover at 24-NOV-16database opened
repair failure completeSQL> select open_mode from v$database;OPEN_MODE
--------------------
READ WRITE
至此迁移成功。
这篇关于通过RMAN将数据库恢复到同类机异机的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!