记一次RMAN全备恢复

一次RMAN全备恢复

当前环境，客户已经将相应的备份文件导入测试环境，测试环境Oracle软件已经安装完成，同时测试环境存在数据库。无法确定测试环境的数据是否可以删除，同时考虑到测试环境磁盘空间比较充裕。选择新建实例的方法。

以下操作为流程，鉴于数据安全性为我自己还原的场景

源库执行RMAN备份全库

1）全备前操作

#查看当前数据库状态
SYS@proe>select instance_name,status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
proe             OPEN

#切换日志
SYS@proe>alter system archive log current;

System altered.

SYS@proe>/

System altered.

SYS@proe>/

System altered.

#校验归档日志
RMAN> crosscheck archivelog all;

2）执行全备

[oracle@server1 ~]$ ./backup.sh 
备份目录不存在，已创建
RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> RMAN> 日志目录不存在，已创建
2020-08-13 14:16:05 rman备份执行成功，开始压缩备份
tar: Removing leading `/' from member names
/backup/files/20200813/
/backup/files/20200813/db_back_PROE_20200813_05v7os00_1_1.dbf
/backup/files/20200813/db_back_PROE_20200813_03v7orsc_1_1.dbf
/backup/files/20200813/con_back_PROE_20200813_0_02v7orsa_1_1
/backup/files/20200813/full_back_log_20200813.log
/backup/files/20200813/db_back_PROE_20200813_04v7orsc_1_1.dbf
/backup/files/20200813/full_back_arch_PROE_20200813_07v7os01_1_1.arc
/backup/files/20200813/db_back_PROE_20200813_06v7os00_1_1.dbf
/backup/files/20200813/full_back_arch_PROE_20200813_08v7os01_1_1.arc
/backup/files/20200813/full_back_arch_PROE_20200813_09v7os14_1_1.arc
2020-08-13 14:16:38 压缩备份成功

3）查看备份并将备份文件发送给恢复目标端

[oracle@server1 files]$ ls
20200813  fullback-20200813.tar.gz
[oracle@server1 files]$ pwd
/backup/files

[root@server1 files]# scp fullback-20200813.tar.gz 192.168.0.35:/root
The authenticity of host '192.168.0.35 (192.168.0.35)' can't be established.
RSA key fingerprint is be:bb:a9:62:e6:2a:51:7e:c7:0b:c9:03:fb:c6:24:4e.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.0.35' (RSA) to the list of known hosts.
[email protected]'s password: 
fullback-20200813.tar.gz

恢复目标端操作

1）授权传输过来的文件并解压

[[email protected] ~]$ ll
total 431772
-rw-r--r-- 1 oracle oinstall 442133681 Aug 13 14:20 fullback-20200813.tar.gz
[[email protected] ~]$ tar -zxvf fullback-20200813.tar.gz 
backup/files/20200813/
backup/files/20200813/db_back_PROE_20200813_05v7os00_1_1.dbf
backup/files/20200813/db_back_PROE_20200813_03v7orsc_1_1.dbf
backup/files/20200813/con_back_PROE_20200813_0_02v7orsa_1_1
backup/files/20200813/full_back_log_20200813.log
backup/files/20200813/db_back_PROE_20200813_04v7orsc_1_1.dbf
backup/files/20200813/full_back_arch_PROE_20200813_07v7os01_1_1.arc
backup/files/20200813/db_back_PROE_20200813_06v7os00_1_1.dbf
backup/files/20200813/full_back_arch_PROE_20200813_08v7os01_1_1.arc
backup/files/20200813/full_back_arch_PROE_20200813_09v7os14_1_1.arc

[[email protected] 20200813]$ pwd
/home/oracle/backup/files/20200813
[[email protected] 20200813]$ ls
con_back_PROE_20200813_0_02v7orsa_1_1   db_back_PROE_20200813_05v7os00_1_1.dbf         full_back_arch_PROE_20200813_08v7os01_1_1.arc
db_back_PROE_20200813_03v7orsc_1_1.dbf  db_back_PROE_20200813_06v7os00_1_1.dbf         full_back_arch_PROE_20200813_09v7os14_1_1.arc
db_back_PROE_20200813_04v7orsc_1_1.dbf  full_back_arch_PROE_20200813_07v7os01_1_1.arc  full_back_log_20200813.log

2）在解压出来的文件的日志中抓取文件编号对应的数据文件（数据量较小可以忽略这步，主要为了数据量较大时多路恢复提高速度或者需要更改数据文件路径使用，这里我数据量不大所以没有使用多路恢复，只是展示一下怎样查找编号和文件的对应关系）

[[email protected] 20200813]$ more full_back_log_20200813.log 
...
Starting backup at 13-AUG-20
channel c1: starting compressed full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00002 name=/u01/app/oracle/oradata/proe/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/proe/example01.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata/proe/test_1.dbf
channel c1: starting piece 1 at 13-AUG-20
channel c2: starting compressed full datafile backup set
channel c2: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/proe/system01.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/proe/tbs_tran01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/proe/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/proe/users01.dbf
...
number 对应数据文件

3）前面说了在这个恢复目标库原本存在一个数据库，在空间足够的情况下我又新建了一个实例用来这次数据恢复。所以需要把环境变量进行修改。

[[email protected] ~]$ vim .bash_profile 
[[email protected] ~]$ source .bash_profile 
[[email protected] ~]$ echo $ORACLE_SID
proe

开始恢复

4）使用RMAN启动伪实例来恢复参数文件(参数文件通常是.dbf最后一个，而且比较小。)

RMAN> startup nomount;

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/db_home1/dbs/initproe.ora'

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area    1068937216 bytes

Fixed Size                     2260088 bytes
Variable Size                285213576 bytes
Database Buffers             775946240 bytes
Redo Buffers                   5517312 bytes

RMAN> restore spfile from '/home/oracle/backup/files/20200813/db_back_PROE_20200813_06v7os00_1_1.dbf';

Starting restore at 2020-08-13 14:28:37
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=429 device type=DISK

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /home/oracle/backup/files/20200813/db_back_PROE_20200813_06v7os00_1_1.dbf
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 2020-08-13 14:28:38

5）恢复完成后关闭数据库重新启动到nomount状态，创建pfile

oracle@orcldb 20200813]$ sqlplus  / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Aug 13 14:28:56 2020

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

SYS@proebk>create pfile from spfile;

File created.

#查看pfile
[oracle@orcldb dbs]$ pwd                
/u01/app/oracle/product/11.2.0/db_home1/dbs
[oracle@orcldb dbs]$ vim initproe.ora 

6）根据pfile创建相应的目录

[[email protected] ~]$ mkdir -pv /u01/app/oracle/admin/proe/adump
mkdir: created directory '/u01/app/oracle/admin/proe'
mkdir: created directory '/u01/app/oracle/admin/proe/adump'

[[email protected] ~]$ mkdir -pv /u01/app/oracle/oradata/proe/
mkdir: created directory '/u01/app/oracle/oradata/proe/'

7）恢复控制文件

RMAN> restore controlfile from '/home/oracle/backup/files/20200813/con_back_PROE_20200813_0_02v7orsa_1_1';

Starting restore at 2020-08-13 16:58:10
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/proe/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/proe/control02.ctl
Finished restore at 2020-08-13 16:58:11

8）恢复完成后将数据库启动到mount状态

SYS@proe>alter database mount;

Database altered.

9）全库恢复

RMAN> restore database;

Starting restore at 2020-08-13 17:20:22
using channel ORA_DISK_1
......

RMAN> recover database;

Starting recover at 2020-08-13 17:21:10
using channel ORA_DISK_1
datafile 7 not processed because file is read-only

starting media recovery
....
archived log thread=1 sequence=57
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 08/13/2020 17:21:13
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 57 and starting SCN of 2090017

10）不完全恢复

MAN> recover database until sequence 57;

Starting recover at 2020-08-13 17:21:36
using channel ORA_DISK_1
datafile 7 not processed because file is read-only

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 2020-08-13 17:21:36

11）用resetlogs方式打开

RMAN> alter database open resetlogs;

database opened

SYS@proe>select status from v$instance;

STATUS
------------------------
OPEN

恢复完成