RMAN 备份异机恢复 Oracle 数据库

原创

用户8006012

发布于 2022-08-20 22:05:36

2.3K0

发布于 2022-08-20 22:05:36

文章被收录于专栏：DATABASE

一、环境准备

源端和目标端的数据库版本需要保持一致，补丁版本可以不一致，目标端磁盘空间不能小于源端空间，目标端只需要安装 Oracle 软件即可！

二、恢复前准备

1. 准备工作

1、通过数据库主机连接
su - oracle
sqlplus / as sysdba
select sysdate from dual;
##创建数据库用户
create user test identified by test;
grant dba to test;
conn test/test
##创建表
create table test (id number primary key not null,name varchar2(100));
insert into test values (1,'hm');
insert into test values (2,'xk');
insert into test values (3,'whm');
commit;


create table t1 (id  int not null);
begin
  for i in 1..10000 loop
    insert into t1 values(dbms_random.value()*10000);
    --如果列是主键
    --insert into t1 values(i);
  end loop;
end;
/

打开数据库归档模式需要重启数据库，完整步骤如下：

[root@orcl ~]# mkdir /archivelog
[root@orcl ~]# chown -R oracle:oinstall /archivelog
chmod -R 775 /archivelog

-- 设置归档日志路径
alter system set log_archive_dest_1='LOCATION=/archivelog';
-- 关闭数据库，重启至mount模式
shutdown immediate
startup mount
-- 开启归档模式
alter database archivelog;
-- 打开数据库
alter database open;
-- 检查归档模式是否打开
archive log list

备份数据库
mkdir -p /home/oracle/scripts
cd /home/oracle/scripts
chmod +x dbbackup_lv0.sh
chmod +x dbbackup_lv1.sh
ls -lrth

2、源端创建 pfile 参数文件

开始恢复前需要先在源端创建参数文件，并按照目标端环境进行修改：

create pfile='/home/oracle/pfile.ora' from spfile;
!strings pfile.ora

SQL> create pfile='/home/oracle/pfile.ora' from spfile;

File created.

SQL> !strings pfile.ora
orcl.__db_cache_size=889192448
orcl.__java_pool_size=16777216
orcl.__large_pool_size=33554432
orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=419430400
orcl.__sga_target=1241513984
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=285212672
orcl.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/oradata/orcl/control01.ctl','/u01/app/oracle/fast_recovery_area/orcl/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=13576962048
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.open_cursors=300
*.pga_aggregate_target=411041792
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1235222528
*.undo_tablespace='UNDOTBS1'

3、目标端创建 pfile 参数文件

在目标端修改并创建 pfile 参数文件：

## oracle 用户下执行
cat<<EOF>/home/oracle/pfile.ora
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/oradata/orcl/control01.ctl','/u01/app/oracle/fast_recovery_area/orcl/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=13576962048
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.log_archive_dest_1='LOCATION=/archivelog'
*.open_cursors=300
*.pga_aggregate_target=411041792
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1235222528
*.undo_tablespace='UNDOTBS1'
EOF

目标端创建参数文件中的文件夹（建议源端和目标端文件夹路径一致）：

## root 用户下执行
mkdir -p /u01/app/oracle/admin/orcl/adump
mkdir -p /oradata/orcl/
mkdir -p /u01/app/oracle/fast_recovery_area/orcl/
mkdir /archivelog
mkdir /backup

chown -R oracle:oinstall /u01/app/oracle/admin/orcl
chown -R oracle:oinstall /oradata/
chown -R oracle:oinstall /u01/app/oracle/fast_recovery_area/orcl/
chown -R oracle:oinstall /archivelog
chown -R oracle:oinstall /backup


此处忘记设置打开数据库时报错
chown -R oracle:oinstall /u01/app/oracle/fast_recovery_area
chmod -R 755 /u01/app/oracle/fast_recovery_area/orcl/
chmod -R 755 /u01/app/oracle/fast_recovery_area

以上目录需根据 pfile 参数文件中的实际情况进行创建

4、源端拷贝密码文件

将源端的密码文件拷贝至目标端：

## oracle 用户下执行
cd $ORACLE_HOME/dbs
scp orapworcl oracle@192.168.30.81:$ORACLE_HOME/dbs
## 如果是恢复到 RAC，还需要拷贝 init[SID].ora 文件
## scp init[SID].ora oracle@192.168.30.81:$ORACLE_HOME/dbs

5、目标端开启到 nomount 模式

目标端使用新建的 pfile 参数文件开启到 nomount 模式：

-- 使用 pfile 文件创建 spfile 参数文件
create spfile from pfile='/home/oracle/pfile.ora';
-- 如果是 RAC 恢复到 RAC，需要将 spfile 文件创建到 ASM 磁盘组下，确保和 init[SID].ora 中的目录保持一致 
-- !cat $ORACLE_HOME/dbs/init[SID].ora
-- create spfile='init[SID]文件中的路径' from pfile='/home/oracle/pfile.ora';
startup nomount


[oracle@standby:/home/oracle]$ sas

SQL*Plus: Release 11.2.0.4.0 Production on Fri Aug 19 17:40:16 2022

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

Connected to an idle instance.

SQL> create spfile from pfile='/home/oracle/pfile.ora';

File created.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 1235959808 bytes
Fixed Size                  2252784 bytes
Variable Size             402653200 bytes
Database Buffers          822083584 bytes
Redo Buffers                8970240 bytes
SQL>

6、源端拷贝备份文件

如果是使用本地或者 NFS 备份，则需要拷贝最新的备份文件至目标端同路径的目录下：

## oracle 用户下执行
cd /backup
scp * oracle@192.168.30.81:/backup

拷贝完成后，检查备份文件目录的权限是否正确。

[oracle@orcl:/backup]$ ll
total 1081836
-rw-r----- 1 oracle oinstall      3072 Aug 20 14:47 arch_ORCL_20220820_1113230861_6_1
-rw-r----- 1 oracle oinstall   2440704 Aug 20 15:13 arch_ORCL_20220820_1113232404_11_1
-rw-r----- 1 oracle oinstall      3584 Aug 20 15:13 arch_ORCL_20220820_1113232404_12_1
-rw-r----- 1 oracle oinstall 679845888 Aug 20 14:47 backlv0_ORCL_20220820_1113230838_1_1
-rw-r----- 1 oracle oinstall 400220160 Aug 20 14:47 backlv0_ORCL_20220820_1113230838_2_1
-rw-r----- 1 oracle oinstall   9797632 Aug 20 14:47 backlv0_ORCL_20220820_1113230859_3_1
-rw-r----- 1 oracle oinstall     98304 Aug 20 14:47 backlv0_ORCL_20220820_1113230859_4_1
-rw-r----- 1 oracle oinstall    417792 Aug 20 15:13 backlv1_ORCL_20220820_1113232393_7_1
-rw-r----- 1 oracle oinstall   5062656 Aug 20 15:13 backlv1_ORCL_20220820_1113232393_8_1
-rw-r----- 1 oracle oinstall   9797632 Aug 20 15:13 backlv1_ORCL_20220820_1113232396_9_1
-rw-r----- 1 oracle oinstall     98304 Aug 20 15:13 backlv1_ORCL_20220820_1113232397_10_1
-rw-r--r-- 1 oracle oinstall      3754 Aug 20 14:47 level0_backup_20220820144716.log
-rw-r--r-- 1 oracle oinstall      5009 Aug 20 15:13 level1_backup_20220820151311.log

7、记录控制文件

在源端查询并记录最新的控制文件名称（不是最新也行）

## 可以直接在 rman 中查看
list backup of controlfile;
## 本地备份的控制文件：/backup/backlv1_ORCL_20220820_1113232396_9_1

RMAN> list backup of controlfile;


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


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4       Incr 0  9.33M      DISK        00:00:01     20-AUG-22
        BP Key: 4   Status: AVAILABLE  Compressed: NO  Tag: TAG20220820T144718
        Piece Name: /backup/backlv0_ORCL_20220820_1113230859_3_1
  Control File Included: Ckp SCN: 1012500      Ckp time: 20-AUG-22

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
10      Incr 1  9.33M      DISK        00:00:06     20-AUG-22
        BP Key: 10   Status: AVAILABLE  Compressed: NO  Tag: TAG20220820T151312
        Piece Name: /backup/backlv1_ORCL_20220820_1113232396_9_1
  Control File Included: Ckp SCN: 1013371      Ckp time: 20-AUG-22

8、目标端恢复控制文件

8.1、本地备份方式

使用本地备份中最新的控制文件进行恢复：

## oracle 用户下执行
rman target /
## 恢复控制文件
restore controlfile from '/backup/backlv0_ORCL_20220821_1113333411_9_1';
## 开启数据库到 mount 模式
alter database mount;


[oracle@standby:/home/oracle]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Aug 19 17:59:52 2022

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

connected to target database: ORCL (not mounted)

RMAN> restore controlfile from '/backup/backlv1_ORCL_20220820_1113232396_9_1';

Starting restore at 19-AUG-22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=129 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=/u01/app/oracle/fast_recovery_area/orcl/control02.ctl
Finished restore at 19-AUG-22

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN>

9、注册备份集

一般源端和目标端的备份目录一致的话，是不需要手动注册备份集的，但是以防万一，这里还是建议最好手动注册一下备份集（以防选取的控制文件不是最新）：

rman target /
## rman 下执行，catalog 注册备份集，防止上方恢复的控制文件不是最新的控制文件
catalog start with '/backup/';
## 如果需要注册，则会提示输入 YES，否则不会输出


RMAN> catalog start with '/backup/';

Starting implicit crosscheck backup at 19-AUG-22
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=129 device type=DISK
Crosschecked 7 objects
Finished implicit crosscheck backup at 19-AUG-22

Starting implicit crosscheck copy at 19-AUG-22
using channel ORA_DISK_1
Finished implicit crosscheck copy at 19-AUG-22

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

searching for all files that match the pattern /backup/

List of Files Unknown to the Database
=====================================
File Name: /backup/arch_ORCL_20220820_1113232404_11_1
File Name: /backup/arch_ORCL_20220820_1113232404_12_1
File Name: /backup/backlv1_ORCL_20220820_1113232396_9_1
File Name: /backup/backlv1_ORCL_20220820_1113232397_10_1
File Name: /backup/level0_backup_20220820144716.log
File Name: /backup/level1_backup_20220820151311.log

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

List of Cataloged Files
=======================
File Name: /backup/arch_ORCL_20220820_1113232404_11_1
File Name: /backup/arch_ORCL_20220820_1113232404_12_1
File Name: /backup/backlv1_ORCL_20220820_1113232396_9_1
File Name: /backup/backlv1_ORCL_20220820_1113232397_10_1

List of Files Which Where Not Cataloged
=======================================
File Name: /backup/level0_backup_20220820144716.log
  RMAN-07517: Reason: The file header is corrupted
File Name: /backup/level1_backup_20220820151311.log
  RMAN-07517: Reason: The file header is corrupted

RMAN>

至此，RMAN 恢复前准备已经完成，接下来需要通过备份来恢复数据。

三、恢复数据库

恢复数据库可以分为两种情况：

源端和目标端的数据文件路径一致
源端和目标端的数据文件路径不一致

📢 注意： 如果是 RAC 和单实例之间的恢复，由于是在 ASM 和 FS 之间切换，所以肯定是源端和目标端的数据文件路径不一致。

1、源端和目标端的数据文件路径一致

如果源端和目标端的数据文件路径保持一致，不用管数据库架构，可以直接进行恢复。

1.1、本地备份方式

本地备份恢复比较简单，注意通道类型使用的是 disk，一般数据量大的话可以多开几个通道：

## rman 下继续恢复
run {
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
restore database;
recover database;
}

RMAN> run {
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
restore database;
recover database;
}2> 3> 4> 5> 6> 7>

released channel: ORA_DISK_1
allocated channel: c1
channel c1: SID=129 device type=DISK

allocated channel: c2
channel c2: SID=193 device type=DISK

allocated channel: c3
channel c3: SID=6 device type=DISK

Starting restore at 19-AUG-22

channel c1: starting datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00002 to /oradata/orcl/sysaux01.dbf
channel c1: restoring datafile 00003 to /oradata/orcl/undotbs01.dbf
channel c1: reading from backup piece /backup/backlv0_ORCL_20220820_1113230838_2_1
channel c2: starting datafile backup set restore
channel c2: specifying datafile(s) to restore from backup set
channel c2: restoring datafile 00001 to /oradata/orcl/system01.dbf
channel c2: restoring datafile 00004 to /oradata/orcl/users01.dbf
channel c2: reading from backup piece /backup/backlv0_ORCL_20220820_1113230838_1_1
channel c1: piece handle=/backup/backlv0_ORCL_20220820_1113230838_2_1 tag=TAG20220820T144718
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:25
channel c2: piece handle=/backup/backlv0_ORCL_20220820_1113230838_1_1 tag=TAG20220820T144718
channel c2: restored backup piece 1
channel c2: restore complete, elapsed time: 00:00:25
Finished restore at 19-AUG-22

Starting recover at 19-AUG-22
channel c1: starting incremental datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00002: /oradata/orcl/sysaux01.dbf
destination for restore of datafile 00003: /oradata/orcl/undotbs01.dbf
channel c1: reading from backup piece /backup/backlv1_ORCL_20220820_1113232393_8_1
channel c2: starting incremental datafile backup set restore
channel c2: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /oradata/orcl/system01.dbf
destination for restore of datafile 00004: /oradata/orcl/users01.dbf
channel c2: reading from backup piece /backup/backlv1_ORCL_20220820_1113232393_7_1
channel c1: piece handle=/backup/backlv1_ORCL_20220820_1113232393_8_1 tag=TAG20220820T151312
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:01
channel c2: piece handle=/backup/backlv1_ORCL_20220820_1113232393_7_1 tag=TAG20220820T151312
channel c2: restored backup piece 1
channel c2: restore complete, elapsed time: 00:00:01

starting media recovery

channel c1: starting archived log restore to default destination
channel c1: restoring archived log
archived log thread=1 sequence=6
channel c1: reading from backup piece /backup/arch_ORCL_20220820_1113232404_12_1
channel c1: piece handle=/backup/arch_ORCL_20220820_1113232404_12_1 tag=TAG20220820T151324
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:01
archived log file name=/archivelog/1_6_1103058738.dbf thread=1 sequence=6
unable to find archived log
archived log thread=1 sequence=7
released channel: c1
released channel: c2
released channel: c3
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 08/19/2022 18:25:57
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 7 and starting SCN of 1013389

📢 注意：恢复完最后的提示不是报错，这个缺少归档日志文件是正常的。

四、打开数据库

1、目标端开启数据库（resetlogs）

在确认数据没有问题后，目标端打开数据库到 resetlogs 状态：

alter database open resetlogs;

SQL> alter database open resetlogs;

报错问题处理

RMAN> alter database open resetlogs;

using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 08/20/2022 20:59:57
ORA-01264: Unable to create logfile file name
ORA-19800: Unable to initialize Oracle Managed Destination
Linux-x86_64 Error: 13: Permission denied

在实验中，/u01/app/oracle/fast_recovery_area是闪回区。不小心用root用户建立上了，结果在用RMAN时进行操作时报错。

ORA-01264: Unable to create logfile file name

ORA-19800: Unable to initialize Oracle Managed Destination

处理方法就是:将/u01/app/oracle/fast_recovery_area的属主改为oracle用户：

chown oracle:oinstall /u01/app/oracle/fast_recovery_area
[root@standby ~]# chown -R oracle:oinstall /u01/app/oracle/fast_recovery_area

RMAN>  alter database open resetlogs;

database opened

至此，RMAN 异机恢复结束

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

oracle

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

oracle

作者已关闭评论

0 条评论

热度