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

最新推荐文章于 2021-04-11 11:42:52 发布
blockheading
最新推荐文章于 2021-04-11 11:42:52 发布
阅读量1k
点赞数
分类专栏： Oracle Backup/Recovery 文章标签： rman
本文链接：https://blog.csdn.net/u013169075/article/details/53313508
版权
Oracle Backup/Recovery 专栏收录该内容
38 篇文章 0 订阅
订阅专栏
--通过RMAN将数据库恢复到同类机异机
--下面开始讲源路径称为host1，新路径称为host2
SYS@PROD2> select dbid from v$database; --查询DBID

      DBID
----------
1564287740

RMAN> 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' ;
--全备份host1

Starting 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-16

Starting 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-16

Starting 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';  --恢复spfile

RMAN> shutdown immediate

Oracle instance shut down

RMAN> set DBID=1564287740

executing command: SET DBID

RMAN> STARTUP NOMOUNT

connected 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 -p

RMAN> shutdown immediate

Oracle instance shut down

RMAN> set DBID=1564287740

executing command: SET DBID

RMAN> STARTUP NOMOUNT  --再次启动到nomount阶段

connected to target database (not started)
Oracle instance started

Total System Global Area     958341120 bytes

Fixed Size                     1348972 bytes
Variable Size                314575508 bytes
Database Buffers             637534208 bytes
Redo Buffers                   4882432 bytes

RMAN> restore controlfile from '/tmp/bak/db_14_1_928703445';  --尝试恢复控制文件，此处报错需要创建文件夹

Starting restore at 24-NOV-16
using channel ORA_DISK_1

channel 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/PROD2

RMAN> restore controlfile from '/tmp/bak/db_14_1_928703445';  --再次尝试成功

Starting restore at 24-NOV-16
using channel ORA_DISK_1

channel 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-16

RMAN> alter database mount；  --启动到mount阶段

database mounted
released channel: ORA_DISK_1

RMAN> SQL "create spfile from pfile"；  --创建spfile

sql statement: create spfile from pfile

RMAN> shutdown immediate

database dismounted
Oracle instance shut down

RMAN> startup mount

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area     958341120 bytes

Fixed Size                     1348972 bytes
Variable Size                314575508 bytes
Database Buffers             637534208 bytes
Redo Buffers                   4882432 bytes


RMAN> 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-16

Starting implicit crosscheck copy at 24-NOV-16
using channel ORA_DISK_1
Finished implicit crosscheck copy at 24-NOV-16

searching for all files in the recovery area
cataloging files...
no files cataloged

using 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 objects


RMAN> delete expired backup;  --删除过期备份

using channel ORA_DISK_1

List 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_928702939

Do 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 objects
 
RMAN> catalog backuppiece '/tmp/bak/arch_12_1_928703365';  --注册相关的备份片

cataloged backup piece
backup piece handle=/tmp/bak/arch_12_1_928703365 RECID=13 STAMP=928791434

RMAN> catalog backuppiece '/tmp/bak/db_13_1_928703380';

cataloged backup piece
backup piece handle=/tmp/bak/db_13_1_928703380 RECID=14 STAMP=928791448

RMAN> catalog backuppiece '/tmp/bak/db_14_1_928703445';

cataloged backup piece
backup piece handle=/tmp/bak/db_14_1_928703445 RECID=15 STAMP=928791462

RMAN> 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 2016

Copyright (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 options

SQL> 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.dbf


SQL> 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.log

RMAN> restore database;  --restore数据库

Starting restore at 24-NOV-16
using channel ORA_DISK_1

channel 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-16

RMAN> 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 recovery

RMAN> 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 recovery

analyzing automatic repair options; this may take some time
using channel ORA_DISK_1
analyzing automatic repair options complete

Mandatory Manual Actions
========================
no manual actions available

Optional 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 one

Automated 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.hm

RMAN> 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.hm

contents 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)? yes
executing repair script

Starting restore at 24-NOV-16
using channel ORA_DISK_1

skipping 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-16

Starting recover at 24-NOV-16
using channel ORA_DISK_1

starting media recovery

channel 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-16

database opened
repair failure complete

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE
至此迁移成功。