oracle 11g rman备份恢复—数据文件丢失场景

文档课题:oracle 11g rman备份恢复—数据文件丢失场景.
1、前期准备
1.1、归档模式
RMAN联机备份需数据库处于归档模式,确认数据库处于归档模式.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/fast_recovery_area/archivelog
Oldest online log sequence 148
Next log sequence to archive 150
Current log sequence 150
说明:开归档步骤—关数据库;启动到mount;修改为归档模式;open数据库.
1.2、恢复目录
恢复目录是RMAN专用的备份信息存储地,若没有恢复目录时,RMAN相关备份信息,如归档文件路径、备份集路径等均存储在目标数据库的控制文件中,考虑到控制文件并不能无限增长,而且控制文件也不仅仅是用来存储与备份相关的信息.当待备份的数据库注册到恢复目录之后,RMAN相关的信息除了保存在控制文件中外(控制文件实际上只保存一部分),更加详细的信息就都被存储在恢复目录中.
--恢复目录创建过程,创建表空间
SQL> show user
USER is "SYS"
SQL> create tablespace rman_tbs datafile '/u01/app/oracle/oradata/orcl150/rman_ts1.dbf' size 2G autoextend on next 500M maxsize 6G;

Tablespace created.
--创建rman用户
SQL> create user rman_user identified by rman123 default tablespace rman_tbs temporary tablespace temp;

User created.
--授权
SQL> grant connect,resource,recovery_catalog_owner to rman_user;

Grant succeeded.
--创建完表空间和用户之后,在rman模式下创建恢复目录rman_tbs,注意名字和表空间一样.
[oracle@oel ~]$ rman catalog rman_user/rman123

Recovery Manager: Release 11.2.0.4.0 - Production on Mon Dec 5 17:18:38 2022

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

connected to recovery catalog database

RMAN> create catalog tablespace rman_tbs;

recovery catalog created
--连接目标
RMAN> connect target /

connected to target database: ORCL150 (DBID=4050762520)
--注册数据库
RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
1.3、保存路径
--设置备份文件保存路径.
[root@oel ~]# mkdir -p /databack/rmandata
[root@oel ~]# chown -R oracle:oinstall /databack/
RMAN> configure channel device type disk format '/databack/rmandata/data_%d_%M_%U';

new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/databack/rmandata/data_%d_%M_%U';
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete
--设置控制文件默认存放位置
RMAN> configure controlfile autobackup format for device type disk to '/databack/rmandata/ctl_%d_%M_%F';

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/databack/rmandata/ctl_%d_%M_%F';
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

说明:到此备份准备工作完成.
2、备份
2.1、0级备份
rman 0级全库备份脚本.
[oracle@oel rmandata]$ cat backup_level_0.sh
#!/bin/bash
source /home/oracle/.bash_profile
DATE=`date +%F`
rman target rman_user/rman123 msglog /databack/rmandata/rlog_$DATE append <<EOF
run
{
allocate channel ch1 device type disk;
allocate channel ch2 device type disk;
backup incremental level 0 database format '/databack/rmandata/data_%d_%M_%U'
plus archivelog format '/databack/rmandata/arch_%d_%M_%U';
backup current controlfile format '/databack/rmandata/ctl_%d_%M_%U';
backup spfile format '/databack/rmandata/spf_%d_%M_%U';
release channel ch1;
release channel ch2;
}
report obsolete;
delete noprompt obsolete;
crosscheck backup;
delete noprompt expired backup;
list backup summary;
EOF
2.2、1级备份
rman 1级备份脚本.
[oracle@oel rmandata]$ cat backup_level_1.sh
#!/bin/bash
source /home/oracle/.bash_profile
DATE=`date +%F`
rman target rman_user/rman123 msglog /databack/rmandata/rlog_$DATE append <<EOF
run
{
allocate channel ch1 device type disk;
allocate channel ch2 device type disk;
backup incremental level 1 database format '/databack/rmandata/data_%d_%M_%U'
plus archivelog format '/databack/rmandata/arch_%d_%M_%U';
backup current controlfile format '/databack/rmandata/ctl_%d_%M_%U';
backup spfile format '/databack/rmandata/spf_%d_%M_%U';
release channel ch1;
release channel ch2;
}
report obsolete;
delete noprompt obsolete;
crosscheck backup;
delete noprompt expired backup;
list backup summary;
EOF
说明:0级备份代表全量备份,1级备份代表增量备份.
2.3、权限
--赋予执行权限
[oracle@oel rmandata]$ chmod u+x backup_level_0.sh
[oracle@oel rmandata]$ chmod u+x backup_level_1.sh
3、定时任务
--添加定时任务。
[oracle@oel rmandata]$ crontab -l
0 0 7 * * /bin/sh /databack/rmandata/backup_level_0.sh
0 0 1-6 * * /bin/sh /databack/rmandata/backup_level_1.sh

说明:周日0级全库备份,周一到周六1级增量备份.
4、全库恢复
4.1、删除文件
模拟丢失user表空间数据文件.
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl150/system01.dbf
/u01/app/oracle/oradata/orcl150/sysaux01.dbf
/u01/app/oracle/oradata/orcl150/undotbs01.dbf
/u01/app/oracle/oradata/orcl150/users01.dbf
/u01/app/oracle/oradata/orcl150/rman_ts1.dbf
[oracle@oel orcl150]$ rm -rf users01.dbf
4.2、恢复前准备
注意:使用rman备份前要先查看dbid,恢复前要设置dbid,否则恢复不成功.
--查看和设置dbid
SQL> select dbid from v$database;

DBID
----------
4050762520
[oracle@oel rmandata]$ ps -ef|grep ora_smon
oracle 2582 1 0 20:49 ? 00:00:00 ora_smon_orcl150
oracle 3541 2499 0 21:01 pts/0 00:00:00 grep --color=auto ora_smon
[oracle@oel rmandata]$ kill -9 2582
[oracle@oel rmandata]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Dec 5 21:01:47 2022

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

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 2455228416 bytes
Fixed Size 2255712 bytes
Variable Size 620758176 bytes
Database Buffers 1811939328 bytes
Redo Buffers 20275200 bytes
[oracle@oel orcl150]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Mon Dec 5 21:49:44 2022

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

connected to target database: ORCL150 (not mounted)

RMAN> set dbid=4050762520;

executing command: SET DBID

RMAN> restore controlfile from '/databack/rmandata/ctl_ORCL150_12_101el5r7_1_1';

Starting restore at 05-DEC-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=/u01/app/oracle/oradata/orcl150/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/orcl150/control02.ctl
Finished restore at 05-DEC-22
SQL> alter database mount;

Database altered.
4.2、开始恢复
RMAN> restore database;

Starting restore at 05-DEC-22
Starting implicit crosscheck backup at 05-DEC-22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=129 device type=DISK
Crosschecked 12 objects
Finished implicit crosscheck backup at 05-DEC-22

Starting implicit crosscheck copy at 05-DEC-22
using channel ORA_DISK_1
Finished implicit crosscheck copy at 05-DEC-22

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

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 00002 to /u01/app/oracle/oradata/orcl150/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/orcl150/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/orcl150/rman_ts1.dbf
channel ORA_DISK_1: reading from backup piece /databack/rmandata/data_ORCL150_12_0j1ekr91_1_1
channel ORA_DISK_1: piece handle=/databack/rmandata/data_ORCL150_12_0j1ekr91_1_1 tag=TAG20221205T175337
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
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/orcl150/system01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/orcl150/users01.dbf
channel ORA_DISK_1: reading from backup piece /databack/rmandata/data_ORCL150_12_0i1ekr91_1_1
channel ORA_DISK_1: piece handle=/databack/rmandata/data_ORCL150_12_0i1ekr91_1_1 tag=TAG20221205T175337
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:45
Finished restore at 05-DEC-22
RMAN> recover database;

Starting recover at 05-DEC-22
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00002: /u01/app/oracle/oradata/orcl150/sysaux01.dbf
destination for restore of datafile 00003: /u01/app/oracle/oradata/orcl150/undotbs01.dbf
destination for restore of datafile 00005: /u01/app/oracle/oradata/orcl150/rman_ts1.dbf
channel ORA_DISK_1: reading from backup piece /databack/rmandata/data_ORCL150_12_0s1el5qh_1_1
channel ORA_DISK_1: piece handle=/databack/rmandata/data_ORCL150_12_0s1el5qh_1_1 tag=TAG20221205T205337
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/app/oracle/oradata/orcl150/system01.dbf
destination for restore of datafile 00004: /u01/app/oracle/oradata/orcl150/users01.dbf
channel ORA_DISK_1: reading from backup piece /databack/rmandata/data_ORCL150_12_0r1el5qh_1_1
channel ORA_DISK_1: piece handle=/databack/rmandata/data_ORCL150_12_0r1el5qh_1_1 tag=TAG20221205T205337
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

starting media recovery

archived log for thread 1 with sequence 156 is already on disk as file /u01/app/oracle/oradata/orcl150/redo03.log
archived log for thread 1 with sequence 157 is already on disk as file /u01/app/oracle/oradata/orcl150/redo01.log
archived log file name=/u01/app/oracle/oradata/orcl150/redo03.log thread=1 sequence=156
archived log file name=/u01/app/oracle/oradata/orcl150/redo01.log thread=1 sequence=157
media recovery complete, elapsed time: 00:00:01
Finished recover at 05-DEC-22
SQL> alter database open resetlogs;

Database altered.

说明:
restore:转储,也是还原被损坏的文件.
recover:恢复,通过redo log&archive log恢复.
5、验证
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl150/system01.dbf
/u01/app/oracle/oradata/orcl150/sysaux01.dbf
/u01/app/oracle/oradata/orcl150/undotbs01.dbf
/u01/app/oracle/oradata/orcl150/users01.dbf
/u01/app/oracle/oradata/orcl150/rman_ts1.dbf

参考文档:
https://blog.51cto.com/riverxyz/4744236