ORACLE简单易懂之Rman备份恢复概念

发表于 | 分类于

8.1 Rman恢复的概念

8.1.1 Restore 和 Recovery的区别

Restoration: A corrupted data file is overwritten from a backup of the data file .The data file is at a prior point of time than the current database.

Recovery: Recovery applies the changes to the individual blocks,using archive and redo information,to move the database forward to the current point in time.

从原理上解释，Restore是使用备份文件，将数据库还原到过去的某个状态。

Recovery是使用redo日志和归档日志将数据库向前恢复，一步步的恢复到现在这个时点。

举个例子。
某生产环境的数据库，每天凌晨一点会作一次备份。某天下午两点时数据库文件损害，同时数据库宕机。
接着DBA开始恢复数据库。

1.首先,使用最近一次的备份文件还原数据库到当天凌晨一点的状态。但是凌晨一点到下午两点的数据丢失了。
2.接着,使用redo日志和归档日志，把当天凌晨一点开始的数据库操作重做一遍，直到下午两点数据库宕机前。
这样数据库就一点都不差的被恢复起来了。

操作步骤

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
#设置表空间为offline
RMAN> SQL 'alter tablespace tab01 offline immediate';
sql statement: alter tablespace tab01 offline immediate
#Restore还原物理文件
RMAN> restore tablespace tab01;
Starting restore at 30-DEC-16
using channel ORA_DISK_1
skipping datafile 5; already restored to file /home/oracle/app/oracle/oradata/mmpdb3/tab01.dbf
restore not done; all files read only, offline, or already restored
Finished restore at 30-DEC-16
#Recover恢复数据
RMAN> recover tablespace tab01;
Starting recover at 30-DEC-16
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 00005: /home/oracle/app/oracle/oradata/mmpdb3/tab01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/dbbak/rman/mmpdb3_level_1_database_MMPDB3_20161229_328
channel ORA_DISK_1: piece handle=/home/oracle/dbbak/rman/mmpdb3_level_1_database_MMPDB3_20161229_328 tag=TAG20161229T230036
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 30-DEC-16
#设置表空间为online
RMAN> SQL 'alter tablespace tab01 online';
sql statement: alter tablespace tab01 online

8.1.2 数据文件恢复–nologging创建表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
#对数据库在20170101做0级zhen备份
RMAN> list backup tag TAG20170101T230014;
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
101223  Incr 0  2.65G      DISK        00:01:24     01-JAN-17
        BP Key: 101230   Status: AVAILABLE  Compressed: NO  Tag: TAG20170101T230014
        Piece Name: /home/oracle/dbbak/rman/mmpdb3_level_0_database_MMPDB3_20170101_350
  List of Datafiles in backup set 101223
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1    0  Incr 6243420    01-JAN-17 /home/oracle/app/oracle/oradata/mmpdb3/system01.dbf
  2    0  Incr 6243420    01-JAN-17 /home/oracle/app/oracle/oradata/mmpdb3/sysaux01.dbf
  3    0  Incr 6243420    01-JAN-17 /home/oracle/app/oracle/oradata/mmpdb3/undotbs01.dbf
  4    0  Incr 6243420    01-JAN-17 /home/oracle/app/oracle/oradata/mmpdb3/users01.dbf
  5    0  Incr 6243420    01-JAN-17 /home/oracle/app/oracle/oradata/mmpdb3/bak/tab01.dbf
  6    0  Incr 6243420    01-JAN-17 /home/oracle/app/oracle/oradata/mmpdb3/tab02.dbf
  7    0  Incr 6243420    01-JAN-17 /home/oracle/app/oracle/oradata/mmpdb3/tab03.dbf
#对数据库在20170102做1级差异增量备份
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
102676  Incr 1  90.34M     DISK        00:00:02     02-JAN-17
        BP Key: 102682   Status: AVAILABLE  Compressed: NO  Tag: TAG20170102T230015
        Piece Name: /home/oracle/dbbak/rman/mmpdb3_level_1_database_MMPDB3_20170102_354
  List of Datafiles in backup set 102676
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1    1  Incr 6324265    02-JAN-17 /home/oracle/app/oracle/oradata/mmpdb3/system01.dbf
  2    1  Incr 6324265    02-JAN-17 /home/oracle/app/oracle/oradata/mmpdb3/sysaux01.dbf
  3    1  Incr 6324265    02-JAN-17 /home/oracle/app/oracle/oradata/mmpdb3/undotbs01.dbf
  4    1  Incr 6324265    02-JAN-17 /home/oracle/app/oracle/oradata/mmpdb3/users01.dbf
  5    1  Incr 6324265    02-JAN-17 /home/oracle/app/oracle/oradata/mmpdb3/bak/tab01.dbf
  6    1  Incr 6324265    02-JAN-17 /home/oracle/app/oracle/oradata/mmpdb3/tab02.dbf
  7    1  Incr 6324265    02-JAN-17 /home/oracle/app/oracle/oradata/mmpdb3/tab03.dbf
#对数据库在20170103做1级差异增量备份
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
104200  Incr 1  91.21M     DISK        00:00:02     03-JAN-17
        BP Key: 104206   Status: AVAILABLE  Compressed: NO  Tag: TAG20170103T230015
        Piece Name: /home/oracle/dbbak/rman/mmpdb3_level_1_database_MMPDB3_20170103_358
  List of Datafiles in backup set 104200
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1    1  Incr 6405618    03-JAN-17 /home/oracle/app/oracle/oradata/mmpdb3/system01.dbf
  2    1  Incr 6405618    03-JAN-17 /home/oracle/app/oracle/oradata/mmpdb3/sysaux01.dbf
  3    1  Incr 6405618    03-JAN-17 /home/oracle/app/oracle/oradata/mmpdb3/undotbs01.dbf
  4    1  Incr 6405618    03-JAN-17 /home/oracle/app/oracle/oradata/mmpdb3/users01.dbf
  5    1  Incr 6405618    03-JAN-17 /home/oracle/app/oracle/oradata/mmpdb3/bak/tab01.dbf
  6    1  Incr 6405618    03-JAN-17 /home/oracle/app/oracle/oradata/mmpdb3/tab02.dbf
  7    1  Incr 6405618    03-JAN-17 /home/oracle/app/oracle/oradata/mmpdb3/tab03.dbf
#对数据库在20170104创建表mp.t170104_2 nologging
SQL> variable redo number;
SQL>
SQL> execute :redo :=get_stat_val('redo size');
SQL> create table mp.t170104_2 nologging as select * from mp.objects;
Table created.
#查看redo size
SQL> exec dbms_output.put_line((get_stat_val('redo size')-:redo) || ' byte of redo....');
325836 byte of redo....
#模拟删除数据文件
[root@hzvscmdb bak]# ll
-rw-r----- 1 oracle oinstall 2313428992 Jan  4 07:12 tab01.dbf-bak
#还原数据库，恢复数据
RMAN> sql 'alter database datafile 5 offline';
starting full resync of recovery catalog
full resync complete
sql statement: alter database datafile 5 offline
RMAN> restore datafile 5;
Starting restore at 04-JAN-17
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 00005 to /home/oracle/app/oracle/oradata/mmpdb3/bak/tab01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/dbbak/rman/mmpdb3_level_0_database_MMPDB3_20170101_350
channel ORA_DISK_1: piece handle=/home/oracle/dbbak/rman/mmpdb3_level_0_database_MMPDB3_20170101_350 tag=TAG20170101T230014
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 04-JAN-17
RMAN> recover datafile 5;
Starting recover at 04-JAN-17
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 00005: /home/oracle/app/oracle/oradata/mmpdb3/bak/tab01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/dbbak/rman/mmpdb3_level_1_database_MMPDB3_20170102_354
channel ORA_DISK_1: piece handle=/home/oracle/dbbak/rman/mmpdb3_level_1_database_MMPDB3_20170102_354 tag=TAG20170102T230015
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 00005: /home/oracle/app/oracle/oradata/mmpdb3/bak/tab01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/dbbak/rman/mmpdb3_level_1_database_MMPDB3_20170103_358
channel ORA_DISK_1: piece handle=/home/oracle/dbbak/rman/mmpdb3_level_1_database_MMPDB3_20170103_358 tag=TAG20170103T230015
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
starting media recovery
........
........
media recovery complete, elapsed time: 00:00:48
Finished recover at 04-JAN-17
starting full resync of recovery catalog
full resync complete
RMAN> sql 'alter database datafile 5 online';
sql statement: alter database datafile 5 online
[root@hzvscmdb bak]# ll
total 4518424
-rw-r----- 1 oracle oinstall 2313428992 Jan  4 07:17 tab01.dbf
-rw-r----- 1 oracle oinstall 2313428992 Jan  4 07:12 tab01.dbf-bak
SQL> select count(*) from mp.t170104_2;
select count(*) from mp.t170104_2
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 208651)
ORA-01110: data file 5: '/home/oracle/app/oracle/oradata/mmpdb3/bak/tab01.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
SQL> select count(*) from mp.t170104_1;
  COUNT(*)
----------
   3619650
   
ORA-26040: Data block was loaded using the NOLOGGING option
Cause: Trying to access data in block that was loaded without redo generation using the NOLOGGING/UNRECOVERABLE option
Action: Drop the object containing the block.

8.1.3 恢复初始化参数文件spfile

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
[root@hzvscmdb dbs]# mv spfilemmpdb3.ora spfilemmpdb3.ora-bak
SQL> startup;
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/initmmpdb3.ora'
SQL>
[oracle@hzvscmdb 2017_01_10]$ rman target / catalog rmanmmp/pass@rmanmmpcatalog
Recovery Manager: Release 11.2.0.2.0 - Production on Wed Jan 11 05:13:20 2017
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database (not started)
connected to recovery catalog database
RMAN> startup nomount;
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/initmmpdb3.ora'
starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started
Total System Global Area     158662656 bytes
Fixed Size                     2224584 bytes
Variable Size                100666936 bytes
Database Buffers              50331648 bytes
Redo Buffers                   5439488 bytes
RMAN> restore spfile from autobackup;
Starting restore at 11-JAN-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=67 device type=DISK
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20170111
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20170110
channel ORA_DISK_1: AUTOBACKUP found: /home/oracle/dbbak/rman/controlfile_c-1519826042-20170110-03
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /home/oracle/dbbak/rman/controlfile_c-1519826042-20170110-03
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 11-JAN-17
[root@hzvscmdb dbs]# ll spfilemmpdb3.ora
-rw-r-----  1 oracle oinstall     3584 Jan 11 05:16 spfilemmpdb3.ora
-rw-r-----  1 oracle oinstall     3584 Jan 11 05:10 spfilemmpdb3.ora-bak
[oracle@hzvscmdb ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Wed Jan 11 05:15:37 2017
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning option
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 6664212480 bytes
Fixed Size                  2239072 bytes
Variable Size            4311745952 bytes
Database Buffers         2332033024 bytes
Redo Buffers               18194432 bytes
Database mounted.
Database opened.
SQL>