Oracle备份恢复案例3-控制文件的恢复

发表于 | 分类于

7.5.3 案例3 （控制文件的恢复）

7.5.3.1 场景1 控制文件没有全坏,有一个是好的

step 1: 查看控制文件记录的序列号，控制文件和数据文件的SCN

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
-- Control file sequence number incremented by control file transactions
-- V$DATABASE displays information about the database from the control file.
SQL> select CONTROLFILE_SEQUENCE# from v$database;
CONTROLFILE_SEQUENCE#
---------------------
                 1676
-- The x$kcvfh table is a fixed table in Oracle. The table naming stands for Kernel, Cache, recoVery, File, Header.
-- FHCSQ：controlfile sequence number in the Memory
SQL> select hxfil as file#, FHCSQ from x$kcvfh;
     FILE#      FHCSQ
---------- ----------
         1       1644
         2       1644
         3       1644
         4       1644
         5       1644
         6       1644
-- Last SCN in backup control file; null if the control file is not a backup
SQL> select controlfile_change# from v$database;
CONTROLFILE_CHANGE#
-------------------
            1152251
-- Last SCN checkpointed
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
           1146902
-- Data file checkpoint change# 
-- "V$DATAFILE_HEADER", which displays information from data file headers
SQL> select file#, checkpoint_change# from v$datafile_header;
     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            1146902
         2            1146902
         3            1146902
         4            1146902
         5            1146902
         6            1146902           
-- SCN at last checkpoint 
-- V$DATAFILE displays datafile information from the control file.
SQL> select file#, checkpoint_change# from v$datafile;
     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            1146902
         2            1146902
         3            1146902
         4            1146902
         5            1146902
         6            1146902

step 2: 模拟事故，删除一个control文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
[oracle@hzvscmdb mmpdb3]$ mv control01.ctl control01.ctl-bak
SQL> startup;
ORACLE instance started.
Total System Global Area 6664212480 bytes
Fixed Size                  2239072 bytes
Variable Size            3456107936 bytes
Database Buffers         3187671040 bytes
Redo Buffers               18194432 bytes
ORA-00205: error in identifying control file, check alert log for more info
[oracle@hzvscmdb alert]$ vi log.xml
<txt>ORA-00210: cannot open the specified control file
ORA-00202: control file: &apos;/home/oracle/app/oracle/oradata/mmpdb3/control01.ctl&apos;
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

step 3: 用好的control文件来，替换换掉的文件。

1
2
3
4
5
6
7
8
9
[oracle@hzvscmdb mmpdb3]$ cp -a control02.ctl ../../oradata/mmpdb3/control01.ctl
SQL> alter database mount;
Database altered.
SQL> alter database open;
Database altered.

7.5.3.2 场景2 所有控制文件都坏了,但损坏前有备份(二进制)

step 1: 备份control文件为二进制格式，并查看控制文件和数据文件的SCN

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
SQL> alter database backup controlfile to '/home/oracle/dbbak/control.bin';
Database altered.
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
           1152702
SQL> select File#, checkpoint_change# from v$datafile;
     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            1152702
         2            1152702
         3            1152702
         4            1152702
         5            1152702
         6            1152702
6 rows selected.
SQL> select file#, checkpoint_change# from v$datafile_header;
     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            1152702
         2            1152702
         3            1152702
         4            1152702
         5            1152702
         6            1152702
6 rows selected.

step 2: 插入新的数据，并提交，并查看控制文件和数据文件的SCN，发现SNC没有变化

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
SQL> insert into mp.obj(OBJECT_NAME, OBJECT_ID) values('control',1);
1 row created.
SQL> commit;
Commit complete.
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
           1152702
SQL> select File#, checkpoint_change# from v$datafile;
     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            1152702
         2            1152702
         3            1152702
         4            1152702
         5            1152702
         6            1152702
6 rows selected.
SQL> select file#, checkpoint_change# from v$datafile_header;
     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            1152702
         2            1152702
         3            1152702
         4            1152702
         5            1152702
         6            1152702
6 rows selected.

step 3: 将所有的数据变化，写入到disk，发现SNC有变化

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
-- Specify CHECKPOINT to explicitly force Oracle Database to perform a checkpoint, 
-- ensuring that all changes made by committed transactions are written to datafiles on disk. 
SQL> alter system checkpoint;
System altered.
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
           1157349
SQL> select File#, checkpoint_change# from v$datafile;
     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            1157349
         2            1157349
         3            1157349
         4            1157349
         5            1157349
         6            1157349
6 rows selected.
SQL> select file#, checkpoint_change# from v$datafile_header;
     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            1157349
         2            1157349
         3            1157349
         4            1157349
         5            1157349
         6            1157349
6 rows selected.

step 4: 模拟事故，删除所有的control file

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
[oracle@hzvscmdb mmpdb3]$ mv control01.ctl control01.ctl-bak
[oracle@hzvscmdb mmpdb3]$ mv control02.ctl control02.ctl-bak
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 6664212480 bytes
Fixed Size                  2239072 bytes
Variable Size            3456107936 bytes
Database Buffers         3187671040 bytes
Redo Buffers               18194432 bytes
ORA-00205: error in identifying control file, check alert log for more info

step 5: copy 备份的control file 二进制文件到默认相应的目录，然后开启数据库发现错误，查看数据文件记录的SCN与控制文件记录的SCN不一致。

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
[oracle@hzvscmdb mmpdb3]$ cp -a /home/oracle/dbbak/control.bin control01.ctl
[oracle@hzvscmdb mmpdb3]$ cp -a /home/oracle/dbbak/control.bin /home/oracle/app/oracle/fast_recovery_area/mmpdb3/control02.ctl
SQL> startup;
ORACLE instance started.
Total System Global Area 6664212480 bytes
Fixed Size                  2239072 bytes
Variable Size            3456107936 bytes
Database Buffers         3187671040 bytes
Redo Buffers               18194432 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
           1152702
SQL> select File#, checkpoint_change# from v$datafile;
     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            1152702
         2            1152702
         3            1152702
         4            1152702
         5            1152702
         6            1152702
6 rows selected.
SQL> select file#, checkpoint_change# from v$datafile_header;
     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            1157349
         2            1157349
         3            1157349
         4            1157349
         5            1157349
         6            1157349
6 rows selected.

step 6: 进行恢复

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
SQL> alter database open noresetlogs;
alter database open noresetlogs
*
ERROR at line 1:
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
SQL> recover database using backup controlfile until cancel
ORA-00275: media recovery has already been started
SQL> recover database using backup controlfile until cancel
ORA-00279: change 1157117 generated at 11/15/2016 23:37:55 needed for thread 1
ORA-00289: suggestion : /home/oracle/app/oracle/fast_recovery_area/MMPDB3/archivelog/2016_11_16/o1_mf_1_11_%u_.arc
ORA-00280: change 1157117 for thread 1 is in sequence #11
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/home/oracle/app/oracle/oradata/mmpdb3/redo01.log
ORA-00328: archived log ends at change 1146901, need later change 1157117
ORA-00334: archived log: '/home/oracle/app/oracle/oradata/mmpdb3/redo01.log'
ORA-10879: error signaled in parallel recovery slave
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/home/oracle/app/oracle/oradata/mmpdb3/system01.dbf'
SQL> alter database recover cancel;
Database altered.
SQL> select GROUP#, STATUS, ARCHIVED, MEMBERS, FIRST_CHANGE#, to_char(NEXT_CHANGE#, 9999999999999999999)  from v$log;
    GROUP# STATUS           ARC    MEMBERS FIRST_CHANGE# TO_CHAR(NEXT_CHANGE#
---------- ---------------- --- ---------- ------------- --------------------
         1 INACTIVE         YES          1       1127994              1146902
         3 INACTIVE         YES          1       1095072              1127994
         2 CURRENT          NO           1       1146902      281474976710655
-- Note: You can specify the name of an online CURRENT redo log 
SQL> recover database using backup controlfile until cancel
ORA-00279: change 1157117 generated at 11/15/2016 23:37:55 needed for thread 1
ORA-00289: suggestion : /home/oracle/app/oracle/fast_recovery_area/MMPDB3/archivelog/2016_11_16/o1_mf_1_11_%u_.arc
ORA-00280: change 1157117 for thread 1 is in sequence #11
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/home/oracle/app/oracle/oradata/mmpdb3/redo02.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.
SQL> select GROUP#, STATUS, ARCHIVED, MEMBERS, FIRST_CHANGE#, to_char(NEXT_CHANGE#, 9999999999999999999)  from v$log;
    GROUP# STATUS           ARC    MEMBERS FIRST_CHANGE# TO_CHAR(NEXT_CHANGE#
---------- ---------------- --- ---------- ------------- --------------------
         1 CURRENT          NO           1       1157419      281474976710655
         2 UNUSED           YES          1             0                    0
         3 UNUSED           YES          1             0                    0
         
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
           1157422
SQL> select File#, checkpoint_change# from v$datafile;
     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            1157422
         2            1157422
         3            1157422
         4            1157422
         5            1157422
         6            1157422
6 rows selected.
SQL> select file#, checkpoint_change# from v$datafile_header;
     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            1157422
         2            1157422
         3            1157422
         4            1157422
         5            1157422
         6            1157422
6 rows selected.

7.5.3.3 场景3 所有控制文件都坏了,但损坏前有备份(文本)

step 1: 备份control文件为文本，并查看控制文件和数据文件的SCN

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
SQL> alter database backup controlfile to trace as '/home/oracle/dbbak/control.txt';
Database altered.
SQL> insert into mp.T_LOAD_DATA values(1888, 't888');
1 row created.
SQL> commit;
Commit complete.
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
           1875087
SQL> select File#, checkpoint_change# from v$datafile;
     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            1875087
         2            1875087
         3            1875087
         4            1875087
         5            1875087
         6            1875087
6 rows selected.
SQL> select file#, checkpoint_change# from v$datafile_header;
     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            1875087
         2            1875087
         3            1875087
         4            1875087
         5            1875087
         6            1875087
6 rows selected.

step 2: 模拟事故，删除control 文件

1
2
3
4
5
6
7
8
9
10
11
12
13
[oracle@hzvscmdb mmpdb3]$ rm -rf control01.ctl
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 6664212480 bytes
Fixed Size                  2239072 bytes
Variable Size            3724543392 bytes
Database Buffers         2919235584 bytes
Redo Buffers               18194432 bytes
ORA-00205: error in identifying control file, check alert log for more info

step 3: 利用备份的control文件，来恢复

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
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "MMPDB3" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/home/oracle/app/oracle/oradata/mmpdb3/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/home/oracle/app/oracle/oradata/mmpdb3/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/home/oracle/app/oracle/oradata/mmpdb3/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/home/oracle/app/oracle/oradata/mmpdb3/system01.dbf',
  '/home/oracle/app/oracle/oradata/mmpdb3/sysaux01.dbf',
  '/home/oracle/app/oracle/oradata/mmpdb3/undotbs01.dbf',
  '/home/oracle/app/oracle/oradata/mmpdb3/users01.dbf',
  '/home/oracle/app/oracle/oradata/mmpdb3/tab01.dbf',
  '/home/oracle/app/oracle/oradata/mmpdb3/tab02.dbf'
CHARACTER SET WE8ISO8859P1
;
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
           1875087
SQL> select File#, checkpoint_change# from v$datafile;
     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            1875087
         2            1875087
         3            1875087
         4            1875087
         5            1875087
         6            1875087
6 rows selected.
SQL> select file#, checkpoint_change# from v$datafile_header;
     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            1875087
         2            1875087
         3            1875087
         4            1875087
         5            1875087
         6            1875087
6 rows selected.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/home/oracle/app/oracle/oradata/mmpdb3/system01.dbf'
SQL> recover database;
Media recovery complete.
SQL> ALTER SYSTEM ARCHIVE LOG ALL;
System altered.
SQL> ALTER DATABASE OPEN;
Database altered.

7.5.3.4 场景4 备份控制文件的恢复,控制文件中不包含数据文件,但联机日志中有

step 1: 先备份control文件为二进制，然后创建新的表空间，并查看控制文件与数据文件的SCN，发现新创建的表空间tab03 CHECKPOINT_CHANGE# 与其它的不一致。

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
SQL> alter database backup controlfile to '/home/oracle/dbbak/control.ctl';
Database altered.
SQL> create tablespace tab03 datafile '/home/oracle/app/oracle/oradata/mmpdb3/tab03.dbf' size 10m;
Tablespace created.
SQL> select file#,name from v$datafile;
     FILE# NAME
---------- ------------------------------------------------------------
         1 /home/oracle/app/oracle/oradata/mmpdb3/system01.dbf
         2 /home/oracle/app/oracle/oradata/mmpdb3/sysaux01.dbf
         3 /home/oracle/app/oracle/oradata/mmpdb3/undotbs01.dbf
         4 /home/oracle/app/oracle/oradata/mmpdb3/users01.dbf
         5 /home/oracle/app/oracle/oradata/mmpdb3/tab01.dbf
         6 /home/oracle/app/oracle/oradata/mmpdb3/tab02.dbf
         7 /home/oracle/app/oracle/oradata/mmpdb3/tab03.dbf
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
           1900834
SQL> select File#, checkpoint_change# from v$datafile;
     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            1900834
         2            1900834
         3            1900834
         4            1900834
         5            1900834
         6            1900834
         7            1901199
7 rows selected.
SQL> select file#, checkpoint_change# from v$datafile_header;
     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            1900834
         2            1900834
         3            1900834
         4            1900834
         5            1900834
         6            1900834
         7            1901199
7 rows selected.
SQL> create table mp.t03(id number(10), name varchar(10)) tablespace tab03;
Table created.
SQL> insert into mp.t03 values(1,'t1');
1 row created.
SQL> commit;
Commit complete.

step 2: 模拟事故，删除控制文件。

1
2
3
4
[oracle@hzvscmdb mmpdb3]$ mv control01.ctl control01.ctl-bak
SQL> shutdown abort;
ORACLE instance shut down.

step 3: copy备份的control 文件到默认的目录，启动数据库。

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
[oracle@hzvscmdb mmpdb3]$ cp -a /home/oracle/dbbak/control.ctl /home/oracle/app/oracle/oradata/control01.ctl
[oracle@hzvscmdb mmpdb3]$ cp -a /home/oracle/dbbak/control.ctl /home/oracle/app/oracle/fast_recovery_area/mmpdb3/control02.ctl
SQL> startup;
ORACLE instance started.
Total System Global Area 6664212480 bytes
Fixed Size                  2239072 bytes
Variable Size            3724543392 bytes
Database Buffers         2919235584 bytes
Redo Buffers               18194432 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
           1900834
SQL> select File#, checkpoint_change# from v$datafile;
     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            1900834
         2            1900834
         3            1900834
         4            1900834
         5            1900834
         6            1900834
6 rows selected.
SQL> select file#, checkpoint_change# from v$datafile_header;
     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            1900834
         2            1900834
         3            1900834
         4            1900834
         5            1900834
         6            1900834
6 rows selected.

step 4: 利用redo log 进行不完全恢复。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SQL> select GROUP#, ARCHIVED, STATUS, to_char(NEXT_CHANGE#, 99999999999999999) from v$log;
    GROUP# ARC STATUS           TO_CHAR(NEXT_CHANG
---------- --- ---------------- ------------------
         1 YES INACTIVE                    1899844
         3 YES INACTIVE                    1875087
         2 NO  CURRENT             281474976710655
SQL> recover database using backup controlfile;
ORA-00279: change 1900834 generated at 11/17/2016 05:40:17 needed for thread 1
ORA-00289: suggestion : /home/oracle/app/oracle/fast_recovery_area/MMPDB3/archivelog/2016_11_17/o1_mf_1_167_%u_.arc
ORA-00280: change 1900834 for thread 1 is in sequence #167
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/home/oracle/app/oracle/oradata/mmpdb3/redo02.log
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 7: '/home/oracle/app/oracle/oradata/mmpdb3/tab03.dbf'
ORA-01112: media recovery not started

step 5: 发现redo log里面记录的数据文件‘tab03’不存在， 查看v$datafile发现名字不一致，将其重命名后，进行恢复

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
SQL> select file#, CHECKPOINT_CHANGE#, name from v$datafile;
     FILE# CHECKPOINT_CHANGE# NAME
---------- ------------------ ------------------------------------------------------------
         1            1900834 /home/oracle/app/oracle/oradata/mmpdb3/system01.dbf
         2            1900834 /home/oracle/app/oracle/oradata/mmpdb3/sysaux01.dbf
         3            1900834 /home/oracle/app/oracle/oradata/mmpdb3/undotbs01.dbf
         4            1900834 /home/oracle/app/oracle/oradata/mmpdb3/users01.dbf
         5            1900834 /home/oracle/app/oracle/oradata/mmpdb3/tab01.dbf
         6            1900834 /home/oracle/app/oracle/oradata/mmpdb3/tab02.dbf
         7            1901198 /home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED0
                              0007
SQL> alter database rename file '/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00007'
     to '/home/oracle/app/oracle/oradata/mmpdb3/tab03.dbf';
Database altered.
SQL> select file#, CHECKPOINT_CHANGE#, name from v$datafile;
     FILE# CHECKPOINT_CHANGE# NAME
---------- ------------------ ------------------------------------------------------------
         1            1900834 /home/oracle/app/oracle/oradata/mmpdb3/system01.dbf
         2            1900834 /home/oracle/app/oracle/oradata/mmpdb3/sysaux01.dbf
         3            1900834 /home/oracle/app/oracle/oradata/mmpdb3/undotbs01.dbf
         4            1900834 /home/oracle/app/oracle/oradata/mmpdb3/users01.dbf
         5            1900834 /home/oracle/app/oracle/oradata/mmpdb3/tab01.dbf
         6            1900834 /home/oracle/app/oracle/oradata/mmpdb3/tab02.dbf
         7            1901198 /home/oracle/app/oracle/oradata/mmpdb3/tab03.dbf
7 rows selected.
SQL> recover database using backup controlfile;
ORA-00279: change 1901199 generated at 11/17/2016 05:43:21 needed for thread 1
ORA-00289: suggestion : /home/oracle/app/oracle/fast_recovery_area/MMPDB3/archivelog/2016_11_17/o1_mf_1_167_%u_.arc
ORA-00280: change 1901199 for thread 1 is in sequence #167
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/home/oracle/app/oracle/oradata/mmpdb3/redo02.log
Log applied.
Media recovery complete.

step 6: 查看系统检查点，数据文件与控制文件的SCN，发现系统检查点与数据文件SCN，不一致，用resetlogs的方式打开数据库。

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
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
           1900834
SQL> select File#, checkpoint_change# from v$datafile;
     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            1901610
         2            1901610
         3            1901610
         4            1901610
         5            1901610
         6            1901610
         7            1901610
7 rows selected.
SQL> select file#, checkpoint_change# from v$datafile_header;
     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            1901610
         2            1901610
         3            1901610
         4            1901610
         5            1901610
         6            1901610
         7            1901610
7 rows selected.
SQL> alter database open resetlogs;
Database altered.
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
           1901614
SQL> select File#, checkpoint_change# from v$datafile;
     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            1901614
         2            1901614
         3            1901614
         4            1901614
         5            1901614
         6            1901614
         7            1901614
7 rows selected.
SQL> select file#, checkpoint_change# from v$datafile_header;
     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            1901614
         2            1901614
         3            1901614
         4            1901614
         5            1901614
         6            1901614
         7            1901614
7 rows selected.