RMAN异机恢复实验 RAC+ASM恢复到单实例文件系统

OS： RH Linux

DB： Oracle 11.2.0.4

已完成源库RMAN全备，传至实验机。

备份集位置改变，恢复到新位置。实验机原有spfile文件。

$ rman target /

--启动到nomount状态

RMAN> startup nomount;

connected to target database (not started)

Oracle instance started


Total System Global Area   68412682240 bytes


Fixed Size                     2270360 bytes
Variable Size               6845107048 bytes
Database Buffers           61203283968 bytes
Redo Buffers                 362020864 bytes
（如果不成功，startup force nomount；）

--恢复控制文件

RMAN> restore controlfile from '/data/u01/oracle/backups05/rmanbak/20170614/ctl_ORCL_20170614_3262_1';

Starting restore at 14-6月 -17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=529 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/oradata/orcl/control01.ctl
output file name=/oradata/orcl/control02.ctl
Finished restore at 14-6月 -17

--启动数据库到mount状态
RMAN> alter database mount;


database mounted
released channel: ORA_DISK_1

--因为备份集更改了位置，所以删除控制文件中记录的旧信息，注册现在的备份集

RMAN> crosscheck backup;


RMAN> delete noprompt expired backup;


RMAN> CATALOG START WITH '/data/u01/oracle/backups05/rmanbak/20170614/';


searching for all files that match the pattern /data/u01/oracle/backups05/rmanbak/20170614/


List of Files Unknown to the Database
=====================================
File Name: /data/u01/oracle/backups05/rmanbak/20170614/arch_ORCL_20170614_3255_1
File Name: /data/u01/oracle/backups05/rmanbak/20170614/arch_ORCL_20170614_3256_1
File Name: /data/u01/oracle/backups05/rmanbak/20170614/arch_ORCL_20170614_3254_1
File Name: /data/u01/oracle/backups05/rmanbak/20170614/full_ORCL_20170614_3245_1
File Name: /data/u01/oracle/backups05/rmanbak/20170614/arch_ORCL_20170614_3257_1
File Name: /data/u01/oracle/backups05/rmanbak/20170614/arch_ORCL_20170614_3259_1
File Name: /data/u01/oracle/backups05/rmanbak/20170614/full_ORCL_20170614_3246_1
File Name: /data/u01/oracle/backups05/rmanbak/20170614/arch_ORCL_20170614_3258_1
File Name: /data/u01/oracle/backups05/rmanbak/20170614/ctl_ORCL_20170614_3262_1
File Name: /data/u01/oracle/backups05/rmanbak/20170614/full_ORCL_20170614_3248_1
File Name: /data/u01/oracle/backups05/rmanbak/20170614/arch_ORCL_20170614_3253_1
File Name: /data/u01/oracle/backups05/rmanbak/20170614/arch_ORCL_20170614_3260_1
File Name: /data/u01/oracle/backups05/rmanbak/20170614/arch_ORCL_20170614_3261_1
File Name: /data/u01/oracle/backups05/rmanbak/20170614/full_ORCL_20170614_3249_1
File Name: /data/u01/oracle/backups05/rmanbak/20170614/full_ORCL_20170614_3244_1
File Name: /data/u01/oracle/backups05/rmanbak/20170614/full_ORCL_20170614_3247_1


Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done


List of Cataloged Files
=======================
File Name: /data/u01/oracle/backups05/rmanbak/20170614/arch_ORCL_20170614_3255_1
File Name: /data/u01/oracle/backups05/rmanbak/20170614/arch_ORCL_20170614_3256_1
File Name: /data/u01/oracle/backups05/rmanbak/20170614/arch_ORCL_20170614_3254_1
File Name: /data/u01/oracle/backups05/rmanbak/20170614/full_ORCL_20170614_3245_1
File Name: /data/u01/oracle/backups05/rmanbak/20170614/arch_ORCL_20170614_3257_1
File Name: /data/u01/oracle/backups05/rmanbak/20170614/arch_ORCL_20170614_3259_1
File Name: /data/u01/oracle/backups05/rmanbak/20170614/full_ORCL_20170614_3246_1
File Name: /data/u01/oracle/backups05/rmanbak/20170614/arch_ORCL_20170614_3258_1
File Name: /data/u01/oracle/backups05/rmanbak/20170614/ctl_ORCL_20170614_3262_1
File Name: /data/u01/oracle/backups05/rmanbak/20170614/full_ORCL_20170614_3248_1
File Name: /data/u01/oracle/backups05/rmanbak/20170614/arch_ORCL_20170614_3253_1
File Name: /data/u01/oracle/backups05/rmanbak/20170614/arch_ORCL_20170614_3260_1
File Name: /data/u01/oracle/backups05/rmanbak/20170614/arch_ORCL_20170614_3261_1
File Name: /data/u01/oracle/backups05/rmanbak/20170614/full_ORCL_20170614_3249_1
File Name: /data/u01/oracle/backups05/rmanbak/20170614/full_ORCL_20170614_3244_1
File Name: /data/u01/oracle/backups05/rmanbak/20170614/full_ORCL_20170614_3247_1

RMAN> crosscheck backup;

--更改数据文件位置，restore数据文件

RMAN> run{
set newname for datafile 1 to '/oradata/orcl/system01.dbf'; 
set newname for datafile 2 to '/oradata/orcl/sysaux01.dbf';
set newname for datafile 3 to '/oradata/orcl/undotbs01.dbf';
set newname for datafile 4 to '/oradata/orcl/users01.dbf';
set newname for datafile 5 to '/oradata/orcl/undotbs02.dbf';
set newname for datafile 6 to '/oradata/orcl/ouchndb01.dbf';
set newname for datafile 7 to '/oradata/orcl/ouchndb02.dbf';
restore database;
switch datafile all;
}

set newname for datafile 1 to '/oradata/orcl/system01.dbf'; 
set newname for datafile 2 to '/oradata/orcl/sysaux01.dbf';
set newname for datafile 3 to '/oradata/orcl/undotbs01.dbf';
set newname for datafile 4 to '/oradata/orcl/users01.dbf';
set newname for datafile 5 to '/oradata/orcl/undotbs02.dbf';
set newname for datafile 6 to '/oradata/orcl/ouchndb01.dbf';
set newname for datafile 7 to '/oradata/orcl/ouchndb02.dbf';
restore database;
switch datafile all;

}

--恢复数据库

RMAN> recover database; 

Starting recover at 14-6月 -17
using channel ORA_DISK_1


starting media recovery


Oracle Error: 
ORA-01547: 警告: RECOVER 成功但 OPEN RESETLOGS 将出现如下错误
ORA-01152: 文件 1 没有从过旧的备份中还原
ORA-01110: 数据文件 1: '/oradata/orcl/system01.dbf'


RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/14/2017 10:05:24
RMAN-06053: unable to perform media recovery because of missing log

RMAN-06025: no backup of archived log for thread 1 with sequence 49229 and starting SCN of 14632662965 found to restore

出现报错，缺少归档文件

--根据错误提示，查找备份信息，结果显示确实不存在

RMAN> list backup of archivelog sequence 49229 thread 1;

specification does not match any backup in the repository

--查找之前一个sequence，确实是归档备份的最后一个备份片

RMAN> list backup of archivelog sequence 49228 thread 1;

List of Backup Sets
===================




BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
3278    4.85G      DISK        00:00:00     14-6月 -17     
        BP Key: 3279   Status: AVAILABLE  Compressed: NO  Tag: TAG20170614T020303
        Piece Name: /data/u01/oracle/backups05/rmanbak/20170614/arch_ORCL_20170614_3261_1


  List of Archived Logs in backup set 3278
  Thrd Seq     Low SCN    Low Time   Next SCN   Next Time
  ---- ------- ---------- ---------- ---------- ---------
  1    49228   14632659852 14-6月 -17 14632662965 14-6月 -17

--确认此备份片中的信息

RMAN> list backupset 3278;

List of Backup Sets
===================




BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
3278    4.85G      DISK        00:00:00     14-6月 -17     
        BP Key: 3279   Status: AVAILABLE  Compressed: NO  Tag: TAG20170614T020303
        Piece Name: /data/u01/oracle/backups05/rmanbak/20170614/arch_ORCL_20170614_3261_1


  List of Archived Logs in backup set 3278
  Thrd Seq     Low SCN    Low Time   Next SCN   Next Time
  ---- ------- ---------- ---------- ---------- ---------
  1    49220   14627015588 14-6月 -17 14627724597 14-6月 -17
  1    49221   14627724597 14-6月 -17 14628459684 14-6月 -17
  1    49222   14628459684 14-6月 -17 14629194244 14-6月 -17
  1    49223   14629194244 14-6月 -17 14629929682 14-6月 -17
  1    49224   14629929682 14-6月 -17 14630651519 14-6月 -17
  1    49225   14630651519 14-6月 -17 14631379441 14-6月 -17
  1    49226   14631379441 14-6月 -17 14632097287 14-6月 -17
  1    49227   14632097287 14-6月 -17 14632659852 14-6月 -17
  1    49228   14632659852 14-6月 -17 14632662965 14-6月 -17
  2    21257   14626302982 14-6月 -17 14628459978 14-6月 -17
  2    21258   14628459978 14-6月 -17 14630539227 14-6月 -17
  2    21259   14630539227 14-6月 -17 14632099519 14-6月 -17
  2    21260   14632099519 14-6月 -17 14632656690 14-6月 -17
  2    21261   14632656690 14-6月 -17 14632664254 14-6月 -17

此次备份确实没有包含提示的所需要的归档

查看源库alert日志，原因应为归档时间过久，未能在备份时完成归档，所以没有备份到。

疑问：为什么归档要这么久？

--此时可以有2个选择：

--1. 将数据库恢复到提示缺少归档的scn之前（实验中采用此方法，但此方法丢失数据会多于方法二）

RMAN> run{
set until scn 14632662965;
recover database;
}

set until scn 14632662965;
recover database;
}


executing command: SET until clause


Starting recover at 14-6月 -17
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=49227
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=21260
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=21261
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=49228
channel ORA_DISK_1: reading from backup piece /data/u01/oracle/backups05/rmanbak/20170614/arch_ORCL_20170614_3261_1
channel ORA_DISK_1: piece handle=/data/u01/oracle/backups05/rmanbak/20170614/arch_ORCL_20170614_3261_1 tag=TAG20170614T020303
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
archived log file name=/data/u01/oracle/product/11.2.0/dbs/arch2_21260_920901484.dbf thread=2 sequence=21260
archived log file name=/data/u01/oracle/product/11.2.0/dbs/arch1_49227_920901484.dbf thread=1 sequence=49227
archived log file name=/data/u01/oracle/product/11.2.0/dbs/arch2_21261_920901484.dbf thread=2 sequence=21261
archived log file name=/data/u01/oracle/product/11.2.0/dbs/arch1_49228_920901484.dbf thread=1 sequence=49228
media recovery complete, elapsed time: 00:01:19
Finished recover at 14-6月 -17

RMAN> 

--2. 从源库中找到提示的归档文件，传至恢复机，注册归档信息，恢复数据库。（在所提示缺少的归档文件可以找到时，建议使用此方法）

--源库操作

# su - grid

$ asmcmd
ASMCMD> cd bkup
ASMCMD> cd arch
ASMCMD> ls *_49229_*
1_49229_920901484.dbf
ASMCMD> cp 1_49229_920901484.dbf /home/grid/
copying +bkup/arch/1_49229_920901484.dbf -> /home/grid//1_49229_920901484.dbf
ASMCMD> exit  
$ ll
-rw-r----- 1 grid oinstall 652512768 Jun 14 14:51 1_49229_920901484.dbf 

$ scp 1_49229_920901484.dbf [email protected]:/data/u01/oracle/backups05/rmanbak/20170614/
The authenticity of host 'ouchn6425 (172.16.165.60)' can't be established.
RSA key fingerprint is 43:bb:39:a6:bc:53:80:4d:86:28:52:0b:7e:e7:8f:f4.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'ouchn6425,172.16.165.60' (RSA) to the list of known hosts.
[email protected]'s password: 这里输入目标主机的oracle用户密码

1_49229_920901484.dbf                                                            100%  622MB 103.7MB/s   00:06    

--恢复机

RMAN> CATALOG ARCHIVELOG '/data/u01/oracle/backups05/rmanbak/20170614/1_49229_920901484.dbf'; 

RMAN> recover database;

（恢复结束会提示找不到一个unknow的归档，不是错误，恢复完成）

RMAN> 

--至此，恢复结束

--更改redo，resetlogs方式打开数据库，更改临时数据文件

[[email protected] ~]$ sqlplus / as sysdba

SQL> alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;
alter database drop logfile group 4;
alter database drop logfile group 5;
alter database drop logfile group 6;
alter database drop logfile group 7;
alter database drop logfile group 8;
alter database drop logfile group 9;
alter database drop logfile group 10;
alter database drop logfile group 11;
alter database drop logfile group 12;
alter database drop logfile group 13;
alter database drop logfile group 14;
alter database drop logfile group 15;
alter database drop logfile group 16;
alter database drop logfile group 17;
alter database drop logfile group 18;
alter database drop logfile group 19;
alter database drop logfile group 20;
alter database drop logfile group 21;
alter database drop logfile group 22;

（通过select group# from v$logfile可以查询到共有多少组redo，这里因为知道源库中redo情况，所以未做查询。drop日志组的操作非必需，可以直接进行下面的改名，这里为了节省空间和少改几个名字，选择把不需要的日志组删除）

SQL> select member from v$logfile;


MEMBER
--------------------------------------------------------------------------------
+DATA01/orcl/redo05.log
+DATA01/orcl/redo04.log
+DATA01/orcl/redo07.log
+DATA01/orcl/redo10.log


SQL> alter database rename file '+DATA01/orcl/redo05.log' to '/oradata/orcl/redo05.log';
alter database rename file '+DATA01/orcl/redo04.log' to '/oradata/orcl/redo04.log';
alter database rename file '+DATA01/orcl/redo07.log' to '/oradata/orcl/redo07.log';
alter database rename file '+DATA01/orcl/redo10.log' to '/oradata/orcl/redo10.log';


SQL> select group#,bytes/1024/1024||'M',status from v$log;


    GROUP#    BYTES/1024/1024||'M'                  STATUS
-----------------   -----------------------------------------    ----------------
         4            1024M                                            ACTIVE
         5            1024M                                            CURRENT
         7            1024M                                            CURRENT
        10           1024M                                            INACTIVE

（如果查出STATUS是CLEARING或CLEARING，则ALTER DATABASE CLEAR LOGFILE GROUP XX;

或者这步先不做，open resetlogs打开报错的话，直接clear。）

SQL> alter database open resetlogs;


Database altered.


SQL> select file#, name, status from v$tempfile;


FILE#  NAME                                                                                         STATUS
--------- ---------------------------------------------------------------------------------  --------------
    1      +DATA01/orcl/temp01.dbf                                                          ONLINE
    2      +DATA01/orcl/tempfile/temp_user.275.922123215                    ONLINE


SQL> alter database tempfile '+DATA01/orcl/temp01.dbf' drop;
alter database tempfile '+DATA01/orcl/tempfile/temp_user.275.922123215' drop;
alter tablespace temp add tempfile '/oradata/orcl/temp01.dbf' size 8192M autoextend on next 10M maxsize 30G;

alter tablespace temp_user add tempfile '/oradata/orcl/temp_user01.dbf' size 8192M autoextend on next 10M maxsize 30G;

--全部工作完成