欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

linux下只有rman备份集的异机不同目录恢复

程序员文章站 2022-06-12 12:48:24
...

昨天在客户那里做了一次rman异机的恢复,把生产库弄一份给测试库用,总库大概80G,总共花费了2个小时,当时客户的环境是windows 11.2.0.3,今天早晨在linux下重新测试了一下,记录下来供大家参考 环境: 所有操作都是按主机名区分 源库: 主机名 bre1 实例名为

昨天在客户那里做了一次rman异机的恢复,把生产库弄一份给测试库用,总库大概80G,总共花费了2个小时,当时客户的环境是windows 11.2.0.3,今天早晨在linux下重新测试了一下,记录下来供大家参考

环境:

所有操作都是按主机名区分

源库:

主机名 bre1 实例名为bre1 数据文件目录在/u01/app/oradata下

目标库:

主机名 bre2 数据文件准备放在/bre1/oradata下

废话不多说,下面是实际操作步骤

1.在源库创建备份集,并且拷贝到目标库

拷贝的目录可用和原来的备份的时候的目录一致,也可以不一致

备份system表空间rman会自动备份参数文件和控制文件

RMAN> backup database format '/home/oracle/%U.bak';

Starting backup at 21-AUG-2014 10:03:35
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/bre1/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/bre1/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/bre1/undotbs01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/bre1/test.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/bre1/users01.dbf
channel ORA_DISK_1: starting piece 1 at 21-AUG-2014 10:03:36
channel ORA_DISK_1: finished piece 1 at 21-AUG-2014 10:03:51
piece handle=/home/oracle/03pggb7o_1_1.bak tag=TAG20140821T100336 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting full 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 21-AUG-2014 10:03:52
channel ORA_DISK_1: finished piece 1 at 21-AUG-2014 10:03:53
piece handle=/home/oracle/04pggb87_1_1.bak tag=TAG20140821T100336 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 21-AUG-2014 10:03:53

[oracle@bre2 ~]$ scp bre1:/home/oracle/03pggb7o_1_1.bak .
The authenticity of host 'bre1 (192.168.56.45)' can't be established.
RSA key fingerprint is 73:56:4c:3a:01:3f:50:c8:d8:3a:5d:d5:21:00:6a:fe.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'bre1,192.168.56.45' (RSA) to the list of known hosts.
oracle@bre1's password: 
03pggb7o_1_1.bak                                                                                     100%  336MB  37.3MB/s   00:09    
[oracle@bre2 ~]$ scp bre1:/home/oracle/04pggb87_1_1.bak .
oracle@bre1's password: 
04pggb87_1_1.bak                                                                                     100% 9600KB   9.4MB/s   00:00  
2.还原spfile,使用nomount打开数据库

编辑一个最简单的pfile,只有db_name即可:

[oracle@bre2 dbs]$ vi initbre1.ora
[oracle@bre2 dbs]$ cat initbre1.ora 
db_name=bre1
然后就可以nomount打开数据库了:
SQL> startup nomount;
ORACLE instance started.

Total System Global Area  229683200 bytes
Fixed Size		    2251936 bytes
Variable Size		  171967328 bytes
Database Buffers	   50331648 bytes
Redo Buffers		    5132288 bytes
SQL> 
再使用rman来恢复spfile:
RMAN> restore spfile from '/home/oracle/backup/04pggb87_1_1.bak';

Starting restore at 21-AUG-2014 9:39:31
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=171 device type=DISK

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /home/oracle/backup/04pggb87_1_1.bak
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 21-AUG-2014 9:39:32

RMAN> 
使用spfile来打理数据库试试
[oracle@bre2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Aug 21 10:08:01 2014

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

Connected to an idle instance.

SQL> startup nomount;
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/db_1/dbs/initbre1.ora'
SQL> startup nomount;
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/db_1/dbs/initbre1.ora'
SQL> startup nomount;
ORA-09925: Unable to create audit trail file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 9925
有很多相关目录没有创建,可以使用strings spfilebre1.ora来查看一下spfile里面的内容,建好相关的目录,并且赋予权限。。这些都搞好后nomount打开数据库不再报错。

3.恢复控制文件

登陆到rman,控制文件还原的时候将还原到参数文件指定的位置,所以参数文件指定的控制文件目录必须存在,否则报错,例如下面一开始我没有创建就报错,后来创建完成就不报错了,如果你想还原到其他地方,可以修改参数文件指定到相应地址。

RMAN> restore controlfile from '/home/oracle/04pggb87_1_1.bak';

Starting restore at 21-AUG-2014 10:26:14
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 08/21/2014 10:26:15
ORA-19870: error while restoring backup piece /home/oracle/04pggb87_1_1.bak
ORA-19504: failed to create file "/u01/app/oracle/oradata/bre1/control01.ctl"
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 1

RMAN> restore controlfile from '/home/oracle/04pggb87_1_1.bak';

Starting restore at 21-AUG-2014 10:26:50
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 08/21/2014 10:26:51
ORA-19504: failed to create file "/u01/app/oracle/fast_recovery_area/bre1/control02.ctl"
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 1
ORA-19600: input file is control file  (/u01/app/oracle/oradata/bre1/control01.ctl)
ORA-19601: output file is control file  (/u01/app/oracle/fast_recovery_area/bre1/control02.ctl)

RMAN> restore controlfile from '/home/oracle/04pggb87_1_1.bak';

Starting restore at 21-AUG-2014 10:27:24
using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/bre1/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/bre1/control02.ctl
Finished restore at 21-AUG-2014 10:27:25

4.mount数据库,注册备份集

重启数据库,直接就可以启动到mount

SQL> startup mount;
ORACLE instance started.

Total System Global Area  835104768 bytes
Fixed Size		    2257840 bytes
Variable Size		  503319632 bytes
Database Buffers	  322961408 bytes
Redo Buffers		    6565888 bytes
Database mounted.
SQL> 
如果此时备份集所在文件目录改变,需要手工catalog注册备份集,这里我们将拷过来的备份文件复制到/home/oracle/backup下,演示一下手工catalog备份集
[oracle@bre2 ~]$ ls
03pggb7o_1_1.bak  04pggb87_1_1.bak  pfile.ora  test
[oracle@bre2 ~]$ mkdir backup
[oracle@bre2 ~]$ mv *.bak backup
[oracle@bre2 ~]$ ls backup
03pggb7o_1_1.bak  04pggb87_1_1.bak

RMAN> catalog backuppiece '/home/oracle/backup/03pggb7o_1_1.bak';

Starting implicit crosscheck backup at 21-AUG-2014 10:32:10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 device type=DISK
Crosschecked 1 objects
Finished implicit crosscheck backup at 21-AUG-2014 10:32:10

Starting implicit crosscheck copy at 21-AUG-2014 10:32:10
using channel ORA_DISK_1
Finished implicit crosscheck copy at 21-AUG-2014 10:32:11

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

cataloged backup piece
backup piece handle=/home/oracle/backup/03pggb7o_1_1.bak RECID=2 STAMP=856175531

RMAN>  catalog backuppiece '/home/oracle/backup/04pggb87_1_1.bak';

cataloged backup piece
backup piece handle=/home/oracle/backup/04pggb87_1_1.bak RECID=3 STAMP=856175550
5.使用set newname将数据文件还原到不同目录

原来的数据文件目录在/u01/admin/oradata下面,我们将数据文件恢复到/bre1/oradata下面,%b的意思是只获取文件名,没有目录信息

[root@bre2 ~]# mkdir -p /bre1/oradata
[root@bre2 ~]# chown -R oracle:oinstall /bre1


[oracle@bre2 ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Aug 21 10:36:10 2014

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

connected to target database: BRE1 (DBID=2522432392, not open)

run{
allocate channel ch1 device type disk;
set newname for database to '/bre1/oradata/%b';
restore database;
release channel ch1;
6> }

using target database control file instead of recovery catalog
allocated channel: ch1
channel ch1: SID=396 device type=DISK

executing command: SET NEWNAME

Starting restore at 21-AUG-2014 10:38:03

channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00001 to /bre1/oradata/system01.dbf
channel ch1: restoring datafile 00002 to /bre1/oradata/sysaux01.dbf
channel ch1: restoring datafile 00003 to /bre1/oradata/undotbs01.dbf
channel ch1: restoring datafile 00004 to /bre1/oradata/users01.dbf
channel ch1: restoring datafile 00005 to /bre1/oradata/test.dbf
channel ch1: reading from backup piece /home/oracle/backup/03pggb7o_1_1.bak
channel ch1: piece handle=/home/oracle/backup/03pggb7o_1_1.bak tag=TAG20140821T100336
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:45
Finished restore at 21-AUG-2014 10:38:48

released channel: ch1


数据文件都在了:
[root@bre2 ~]# ls -l /bre1/oradata
total 1564468
-rw-r----- 1 oracle oinstall 629153792 Aug 21 10:38 sysaux01.dbf
-rw-r----- 1 oracle oinstall 734011392 Aug 21 10:38 system01.dbf
-rw-r----- 1 oracle oinstall  20979712 Aug 21 10:38 test.dbf
-rw-r----- 1 oracle oinstall 209723392 Aug 21 10:38 undotbs01.dbf
-rw-r----- 1 oracle oinstall   6561792 Aug 21 10:38 users01.dbf

资料上说可以使用switch datafile all来直接修改控制文件中的文件目录,但是我试了一下貌似不行,还是需要在手工rename,下面是我手工修改控制文件中的数据文件目录,先看一下当前的数据文件目录

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/bre1/system01.dbf
/u01/app/oracle/oradata/bre1/sysaux01.dbf
/u01/app/oracle/oradata/bre1/undotbs01.dbf
/u01/app/oracle/oradata/bre1/users01.dbf
/u01/app/oracle/oradata/bre1/test.dbf

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/bre1/temp01.dbf

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/bre1/redo01.log
/u01/app/oracle/oradata/bre1/redo02.log
/u01/app/oracle/oradata/bre1/redo03.log

我自己写了一个拼接sql来将数据文件目录做转换:

select 'alter database rename file '''||name||''' to ''/bre1/oradata'||substr(name,length(name)-INSTR(reverse(name),'/')+1,INSTR(reverse(name),'/'))||''';' from v$datafile
union all
select 'alter database rename file '''||name||''' to ''/bre1/oradata'||substr(name,length(name)-INSTR(reverse(name),'/')+1,INSTR(reverse(name),'/'))||''';' from v$tempfile
union all
select 'alter database rename file '''||member||''' to ''/bre1/oradata'||substr(member,length(member)-INSTR(reverse(member),'/')+1,INSTR(reverse(member),'/'))||''';' from v$logfile
;

select 'alter database rename file '''||name||''' to ''/bre1/oradata'||substr(name,length(name)-INSTR(reverse(name),'/')+1,INSTR(reverse(name),'/'))||''';' from v$datafile
union all
select 'alter database rename file '''||name||''' to ''/bre1/oradata'||substr(name,length(name)-INSTR(reverse(name),'/')+1,INSTR(reverse(name),'/'))||''';' from v$tempfile
union all
select 'alter database rename file '''||member||''' to ''/bre1/oradata'||substr(member,length(member)-INSTR(reverse(member),'/')+1,INSTR(reverse(member),'/'))||''';' from v$logfile
  6  ;

'ALTERDATABASERENAMEFILE'''||NAME||'''TO''/BRE1/ORADATA'||SUBSTR(NAME,LENGTH(NAME)-INSTR(REVERSE(NAME),'/')+1,INSTR(REVERSE(NAME),'/'))||''';'
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
alter database rename file '/bre1/oradata/system01.dbf' to '/bre1/oradata/system01.dbf';
alter database rename file '/bre1/oradata/sysaux01.dbf' to '/bre1/oradata/sysaux01.dbf';
alter database rename file '/bre1/oradata/undotbs01.dbf' to '/bre1/oradata/undotbs01.dbf';
alter database rename file '/bre1/oradata/users01.dbf' to '/bre1/oradata/users01.dbf';
alter database rename file '/bre1/oradata/test.dbf' to '/bre1/oradata/test.dbf';
alter database rename file '/u01/app/oracle/oradata/bre1/temp01.dbf' to '/bre1/oradata/temp01.dbf';
alter database rename file '/bre1/oradata/redo01.log' to '/bre1/oradata/redo01.log';
alter database rename file '/bre1/oradata/redo02.log' to '/bre1/oradata/redo02.log';
alter database rename file '/bre1/oradata/redo03.log' to '/bre1/oradata/redo03.log';


alter database rename file '/u01/app/oracle/oradata/bre1/system01.dbf' to '/bre1/oradata/system01.dbf';
alter database rename file '/u01/app/oracle/oradata/bre1/sysaux01.dbf' to '/bre1/oradata/sysaux01.dbf';
alter database rename file '/u01/app/oracle/oradata/bre1/undotbs01.dbf' to '/bre1/oradata/undotbs01.dbf';
alter database rename file '/u01/app/oracle/oradata/bre1/users01.dbf' to '/bre1/oradata/users01.dbf';
alter database rename file '/u01/app/oracle/oradata/bre1/test.dbf' to '/bre1/oradata/test.dbf';
alter database rename file '/u01/app/oracle/oradata/bre1/redo01.log' to '/bre1/oradata/redo01.log';
alter database rename file '/u01/app/oracle/oradata/bre1/redo02.log' to '/bre1/oradata/redo02.log';

Database altered.

SQL> 
Database altered.

SQL> 
Database altered.

SQL> 
Database altered.

SQL> 
Database altered.
SQL> 
Database altered.

SQL> 
Database altered.

SQL> alter database rename file '/u01/app/oracle/oradata/bre1/redo03.log' to '/bre1/oradata/redo03.log';

Database altered.
rename之后控制文件中的数据文件目录都已经改到了/bre1/oradata下,那么就可以进行恢复了。
6.恢复数据库

我这里是测试就没有恢复什么归档日志,如果是正式生产库,还需要恢复归档日志,可能还需要使用到CATALOG ARCHIVELOG 来注册归档的备份集和在RMAN中使用
RUN
{
SET ARCHIVELOG DESTINATION TO '/home/oracle';
RESTORE ARCHIVELOG all;
}
来还原出归档日志。
或者还可以在controlfile中修改归档目录来恢复。恢复出归档日志之后,就可以进行数据库恢复。

[oracle@bre2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Aug 21 11:16:06 2014

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 830841 generated at 08/21/2014 10:03:36 needed for thread 1
ORA-00289: suggestion : /arch1_42_851018056.dbf
ORA-00280: change 830841 for thread 1 is in sequence #42

Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;

Database altered.
open 过程中可能会遇到ora-00392错误,
解决办法:
使用SQL> alter database clear logfile group 2;
到此恢复成功!

总结:
主要的难点是需要对rman比较熟悉,需要使用语句修改控制文件中的文件位置,否则会报错找不到相关文件。
如果备份集的目录改变了,需要手工catalog注册备份集到控制文件,并且rename file。