使用0级备份和1级备份做完全恢复

于 2020-02-25 19:38:01 发布
阅读量2.2k 收藏 9
点赞数 1
分类专栏： Oracle备份恢复专题
原文链接：https://blog.csdn.net/kiral07/article/details/87254533
版权

RMAN 单实例增量备份以及恢复测试

一、测试场景
二、测试步骤
三、RMAN相关注意点
四、拓展研究

一、测试场景

1、第一时间节点创建表test,并插入5条数据
2、RMAN level 0全备以及归档备份
3、第二时间节点表test再次插入5条数据库
4、RMAN level 1增量备份
5、数据库open状态下手动删除所有数据文件、控制文件、参数文件（模拟故障）
6、RMAN启动到nomount状态恢复spfile
7、数据库重新启动（用刚才恢复的spfile启动）
8、RMAN恢复控制文件
9、数据库mount
10、恢复数据文件（restore datbase）
11、应用日志（recover database）
12、验证表test完整性

二、测试步骤

一、创建表插入测试数据

SQL> create table test (id number);

Table created.

SQL> insert into test values(1);

1 row created.

SQL> insert into test values(2);

1 row created.

SQL> insert into test values(3);

1 row created.

SQL> insert into test values(4);

1 row created.

SQL> insert into test values(5);

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL>
SQL>
SQL> select * from test;

ID
1
2
3
4
5

二、RMAN 0级全备

RMAN> backup incremental level 0 database tag ‘db0’ format ‘/u01/rman/db0_%T.bak’;

Starting backup at 2018/06/22 08:50:51
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=389 device type=DISK
channel ORA_DISK_1: starting incremental level 0 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata/orcl/cs.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/orcl/secure.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 2018/06/22 08:50:52
channel ORA_DISK_1: finished piece 1 at 2018/06/22 08:50:59
piece handle=/u01/rman/db0_20180622.bak tag=DB0 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 2018/06/22 08:50:59

Starting Control File and SPFILE Autobackup at 2018/06/22 08:50:59
piece handle=/u01/app/oracle/product/11.2.0/db_1/dbs/control_c-1494212616-20180622-00.ctl comment=NONE
Finished Control File and SPFILE Autobackup at 2018/06/22 08:51:00

三、继续插入测试数据

SQL> insert into test values(6);

1 row created.

SQL> insert into test values(7);

1 row created.

SQL> insert into test values(8);

1 row created.

SQL> insert into test values(9);

1 row created.

SQL> insert into test values(10);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test;

ID
1
2
3
4
5
6
7
8
9
10

10 rows selected.

四、RMAN 1级增量备份

RMAN> backup incremental level 1 database tag ‘db1’ format ‘/u01/rman/db1_%T.bak’;

Starting backup at 2018/06/22 08:54:15
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata/orcl/cs.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/orcl/secure.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 2018/06/22 08:54:16
channel ORA_DISK_1: finished piece 1 at 2018/06/22 08:54:19
piece handle=/u01/rman/db1_20180622.bak tag=DB1 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 2018/06/22 08:54:19

Starting Control File and SPFILE Autobackup at 2018/06/22 08:54:19
piece handle=/u01/app/oracle/product/11.2.0/db_1/dbs/control_c-1494212616-20180622-01.ctl comment=NONE
Finished Control File and SPFILE Autobackup at 2018/06/22 08:54:20

五、备份归档

RMAN> backup archivelog all format ‘/u01/rman/arch_%T.bak’ tag ‘arch’;

Starting backup at 2018/06/22 09:29:59
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=11 RECID=931 STAMP=979320140
channel ORA_DISK_1: starting piece 1 at 2018/06/22 09:30:00
channel ORA_DISK_1: finished piece 1 at 2018/06/22 09:30:01
piece handle=/u01/rman/arch_20180622.bak tag=ARCH comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=932 STAMP=979375362
input archived log thread=1 sequence=3 RECID=934 STAMP=979464451
input archived log thread=1 sequence=4 RECID=935 STAMP=979464479
input archived log thread=1 sequence=5 RECID=936 STAMP=979464485
input archived log thread=1 sequence=6 RECID=937 STAMP=979464599
channel ORA_DISK_1: starting piece 1 at 2018/06/22 09:30:01

六、open状态下删除数据文件，参数文件，控制文件

[oracle@gs ~]$ rm -rf /u01/app/oracle/oradata/orcl/.dbf
[oracle@gs ~]$ rm -rf /u01/app/oracle/oradata/orcl/.ctl
[oracle@gs ~]$ rm /u01/app/oracle/product/11.2.0/db_1/dbs/spfileorcl.ora

[oracle@gs ~]$ ll /u01/app/oracle/oradata/orcl/
total 290484
-rw-r----- 1 oracle oinstall 52429312 Jun 22 09:31 redo01.log
-rw-r----- 1 oracle oinstall 52429312 Jun 22 09:31 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Jun 22 09:31 redo03.log
-rw-r----- 1 oracle oinstall 52429312 Jun 21 08:42 redo04b.log
-rw-r----- 1 oracle oinstall 52429312 Jun 22 09:29 redo04.log

七、强制关闭数据库

[oracle@gs ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jun 22 09:35:46 2018

Copyright © 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> shutdown abort
ORACLE instance shut down.

八、RMAN将数据库启动到nomount
（spfile丢失后可以直接使用rman来startup nomount ，rman会先使用dutty数据库名启动，这样就可以恢复spfile了，也可以手工创建init.ora只写dbname ，但是直接使用rman启动更加简单。）

[oracle@gs ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Jun 22 09:36:55 2018

Copyright © 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database (not started)

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_1/dbs/initorcl.ora’

此时Oracle会启动一个默认参数
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

九、nomount下恢复spfile

RMAN> restore spfile from ‘/backup/rman/autobackup/controlfilec-4098581401-20200225-0a’;

Starting restore at 2020-02-25 19:00:53
using channel ORA_DISK_1

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /backup/rman/autobackup/controlfilec-4098581401-20200225-0a
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 2020-02-25 19:00:54

十、让数据库用spfile启动

RMAN> shutdown immediate;

Oracle instance shut down

RMAN> startup nomount;

connected to target database (not started)
Oracle instance started

Total System Global Area 2137886720 bytes

Fixed Size 2254952 bytes
Variable Size 1224738712 bytes
Database Buffers 905969664 bytes
Redo Buffers 4923392 bytes

SYS@ghyyk> select instance_name,status from v$instance;

INSTANCE_NAME STATUS

ghyyk STARTED

十一、RMAN nomount下恢复控制文件

RMAN> restore controlfile from ‘/backup/rman/autobackup/controlfilec-4098581401-20200225-0a’;

Starting restore at 2020-02-25 19:08:27
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=23 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=+DATA/ghyyk/controlfile/current.268.1033326509
output file name=+FRA/ghyyk/controlfile/current.256.1031683867
Finished restore at 2020-02-25 19:08:29

十二、RMAN mount模式下还原数据文件、应用日志恢复数据库

RMAN> alter database mount;
RMAN> restore database;

Starting restore at 2020-02-25 19:09:25
Starting implicit crosscheck backup at 2020-02-25 19:09:25
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=23 device type=DISK
Crosschecked 59 objects
Finished implicit crosscheck backup at 2020-02-25 19:09:26

Starting implicit crosscheck copy at 2020-02-25 19:09:26
using channel ORA_DISK_1
Finished implicit crosscheck copy at 2020-02-25 19:09:26

searching for all files in the recovery area
cataloging files…
cataloging done

List of Cataloged Files

File Name: +fra/GHYYK/AUTOBACKUP/2020_02_21/s_1032960838.347.1032960839
File Name: +fra/GHYYK/AUTOBACKUP/2020_02_21/s_1032980249.349.1032980251
File Name: +fra/GHYYK/AUTOBACKUP/2020_02_14/s_1032373687.314.1032373689
File Name: +fra/GHYYK/AUTOBACKUP/2020_02_13/s_1032286270.296.1032286271
File Name: +fra/GHYYK/AUTOBACKUP/2020_02_07/s_1031765967.268.1031765971

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 +DATA/ghyyk/datafile/sysaux.257.976484729
channel ORA_DISK_1: restoring datafile 00003 to +DATA/ghyyk/datafile/undotbs1.258.976484729
channel ORA_DISK_1: restoring datafile 00007 to +DATA/ghyyk/datafile/aaa.263.1033063463
channel ORA_DISK_1: reading from backup piece /backup/rman/db/db_20200225_GHYYK_189_1
channel ORA_DISK_1: piece handle=/backup/rman/db/db_20200225_GHYYK_189_1 tag=TAG20200225T183721
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
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 +DATA/ghyyk/datafile/system.256.976484729
channel ORA_DISK_1: restoring datafile 00004 to +DATA/ghyyk/datafile/users.259.976484729
channel ORA_DISK_1: restoring datafile 00005 to +DATA/ghyyk/datafile/example.265.976484851
channel ORA_DISK_1: reading from backup piece /backup/rman/db/db_20200225_GHYYK_188_1
channel ORA_DISK_1: piece handle=/backup/rman/db/db_20200225_GHYYK_188_1 tag=TAG20200225T183721
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:36
Finished restore at 2020-02-25 19:10:28

RMAN> recover database;

Starting recover at 2020-02-25 19:12:15
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 00001: +DATA/ghyyk/datafile/system.259.1033326593
destination for restore of datafile 00004: +DATA/ghyyk/datafile/users.257.1033326593
destination for restore of datafile 00005: +DATA/ghyyk/datafile/example.258.1033326593
channel ORA_DISK_1: reading from backup piece /backup/rman/db/db_20200225_GHYYK_192_1
channel ORA_DISK_1: piece handle=/backup/rman/db/db_20200225_GHYYK_192_1 tag=TAG20200225T184250
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
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: +DATA/ghyyk/datafile/sysaux.273.1033326567
destination for restore of datafile 00003: +DATA/ghyyk/datafile/undotbs1.263.1033326567
destination for restore of datafile 00007: +DATA/ghyyk/datafile/aaa.265.1033326567
channel ORA_DISK_1: reading from backup piece /backup/rman/db/db_20200225_GHYYK_193_1
channel ORA_DISK_1: piece handle=/backup/rman/db/db_20200225_GHYYK_193_1 tag=TAG20200225T184250
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 47 is already on disk as file +DATA/ghyyk/onlinelog/group_3.260.1032375791
archived log for thread 1 with sequence 48 is already on disk as file +DATA/ghyyk/onlinelog/group_4.261.1032375793
archived log for thread 1 with sequence 49 is already on disk as file +DATA/ghyyk/onlinelog/group_1.272.1032375789
archived log file name=+DATA/ghyyk/onlinelog/group_3.260.1032375791 thread=1 sequence=47
archived log file name=+DATA/ghyyk/onlinelog/group_4.261.1032375793 thread=1 sequence=48
archived log file name=+DATA/ghyyk/onlinelog/group_1.272.1032375789 thread=1 sequence=49
media recovery complete, elapsed time: 00:00:01
Finished recover at 2020-02-25 19:12:21

十三、打开数据库

RMAN> alter database open resetlogs;

database opened

（这里由于恢复了控制文件，所以只能resetlogs打开数据库。）

Rman>backup full database；

（resetlogs之后必须做全备份）

十四、验证表test完整性

SQL> conn admin/oracle
Connected.

SQL> select * from test;
ID
1
2
3
4
5
6
7
8
9
10

10 rows selected.
恢复有效

三、RMAN相关注意点

1、增量备份还原数据文件（restore）是通过0级备份实现，而recover是通过N个1级备份实现。

假设在T0时间点做了0级和1级备份，T1时间点RMAN恢复数据并打开数据库(alter database open resetlogs)。此时如果有需求需要恢复T0时间点(resetlog之前)完整备份，就需要还原T0时间点控制文件，再进行restore，recover。(此场景也可通过list incarnation尝试https://blog.csdn.net/henrybai/article/details/38037255）
在nomount状态下RMAN可覆盖还原控制文件。
可使用代码块完成恢复

run {
set until time “to_date(‘T0时间点’,‘yyyy-mm-dd hh24:mi:ss’)”;
restore database;
recover database;
}

四、拓展研究

N个增量备份丢失情景
假设有T0时间点0级备份，T1时间点1级备份，T2时间点1级备份。如果T2的1级备份丢失，只能恢复到T1备份。

此时如果需要恢复到T1时间点，restore以及recover都要执行until time。

RMAN> recover database until time “to_date(‘2018-06-22 14:00:00’,‘yyyy-mm-dd hh24:mi:ss’)”;

Starting recover at 2018/06/22 16:43:08
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/22/2018 16:43:08
RMAN-06555: datafile 1 must be restored from backup created before 2018/06/22 14:00:00
此时只有recover执行了until time，无法还原

在2018-06-22 14:07:00分完成了1级备份，可还原稍早时间点。restore，recover一并使用until time。

RMAN> restore database until time “to_date(‘2018-06-22 14:06:00’,‘yyyy-mm-dd hh24:mi:ss’)”;

Starting restore at 2018/06/22 16:44:24
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 00001 to /u01/app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/orcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/orcl/secure.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/orcl/cs.dbf
channel ORA_DISK_1: reading from backup piece /u01/rman/db0_20180622.bak
channel ORA_DISK_1: piece handle=/u01/rman/db0_20180622.bak tag=DB0
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 2018/06/22 16:44:31

RMAN> recover database until time “to_date(‘2018-06-22 14:06:00’,‘yyyy-mm-dd hh24:mi:ss’)”;

Starting recover at 2018/06/22 16:44:50
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 7 is already on disk as file /u01/arch/1_7_979475128.dbf
archived log for thread 1 with sequence 8 is already on disk as file /u01/arch/1_8_979475128.dbf
archived log for thread 1 with sequence 9 is already on disk as file /u01/arch/1_9_979475128.dbf
archived log for thread 1 with sequence 10 is already on disk as file /u01/arch/1_10_979475128.dbf
archived log for thread 1 with sequence 11 is already on disk as file /u01/arch/1_11_979475128.dbf
archived log for thread 1 with sequence 12 is already on disk as file /u01/arch/1_12_979475128.dbf
archived log file name=/u01/arch/1_7_979475128.dbf thread=1 sequence=7
archived log file name=/u01/arch/1_8_979475128.dbf thread=1 sequence=8
archived log file name=/u01/arch/1_9_979475128.dbf thread=1 sequence=9
archived log file name=/u01/arch/1_10_979475128.dbf thread=1 sequence=10
archived log file name=/u01/arch/1_11_979475128.dbf thread=1 sequence=11
archived log file name=/u01/arch/1_12_979475128.dbf thread=1 sequence=12
media recovery complete, elapsed time: 00:00:01
Finished recover at 2018/06/22 16:44:51

RMAN> alter database open resetlogs;

database opened

恢复完毕

查看数据已恢复到T1时间点

Monkey·D·Boy
关注
  • 1
    点赞
  • 踩
  • 9
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值