文档课题：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