ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

在做一个rman异机恢复完成后，数据库无法打开，报错提示

1
2
3
4
Oracle Error: 
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: '+DG/xxx/datafile/system01.dbf'

用的源库一个rman 0级备份以及后来的部分归档日志，拷贝到这个测试环境，恢复完controlfile之后，进行了基于scn的recover，scn则是restore之后控制文件里显示的scn号

1
recover database until scn 75238614094;

尝试open时报错

1
2
3
4
5
6
16:54:07 sys. >alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '+DG/xxx/datafile/system01.dbf'

但是检查scn均一致，有点奇怪,因为一般来说如果这三个视图的checkpoint_change#一致的话，数据库是应该可以正常打开的。

1
2
3
4
5
sys >select (select distinct checkpoint_change# from v$datafile) dfile_ckp,(select checkpoint_change# from v$database) db_ckp,(select distinct checkpoint_change# from v$datafile_header) dh_ckp from dual;

     DFILE_CKP         DB_CKP         DH_CKP
------------------ ------------------ ------------------
       75238614094    75238614094        75238614094

检测数据文件的状态

1
2
3
4
5
6
7
8
9
10
set numwidth 30;
set pagesize 50000;
alter session set nls_date_format = 'DD-MON-RRRR HH24:MI:SS';

sys.>select status,checkpoint_change#,checkpoint_time, resetlogs_change#, resetlogs_time, count(*), fuzzy from v$datafile_header group by status,checkpoint_change#,checkpoint_time, resetlogs_change#, resetlogs_time, fuzzy;

STATUS  CHECKPOINT_CHANGE# CHECKPOINT_TIME          RESETLOGS_CHANGE# RESETLOGS_TIME                COUNT(*) FUZ
------- ------------------ ----------------------- ------------------------------ ----------------------- ------------------------------ ---
ONLINE         75238614094 23-FEB-2019 00:15:46               67622732054 02-NOV-2018 22:00:36                    45 NO
ONLINE         75238614094 23-FEB-2019 00:15:46               67622732054 02-NOV-2018 22:00:36                     8 YES       >>>>====有8个文件的状态不一致

fuzzy值为yes表示数据文件在checkpoint以后仍然有些写入的动作，比如有一些比存储在v$datafile_header.checkpoint_change#字段里scn更高的scn事务对数据文件进行了修改操作，所以为了让数据文件保持一致性，则需要前滚应用日志

查看需要哪些归档日志来恢复数据文件

1
2
3
4
5
select min(fhrba_Seq), max(fhrba_Seq) from X$KCVFH;

        MIN(FHRBA_SEQ)             MAX(FHRBA_SEQ)
------------------------------ ------------------------------
             21201              21201

这个查询结果表示所需要的最小和最大日志文件，为了让数据文件保持一致，你必须要应用这之间所有的日志。

查看当前使用的controlfile

1
2
3
4
5
select name, controlfile_type from v$database ;

NAME                   CONTROL
------------------------------ -------
XXX                BACKUP               >>>>====使用恢复出来的控制文件

查看当前redo log

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
select     substr(L.GROUP#,1,6)       GROUP#
        ,substr(L.THREAD#,1,7)         THREAD#
        ,substr(L.SEQUENCE#,1,10)      SEQUENCE#
        ,substr(L.MEMBERS,1,7)         MEMBERS
        ,substr(L.ARCHIVED,1,8)        ARCHIVED
        ,substr(L.STATUS,1,10)         STATUS
        ,substr(L.FIRST_CHANGE#,1,16)  FIRST_CHANGE#
        ,substr(LF.member,1,60)        REDO_LOGFILE
  from GV$LOG L, GV$LOGFILE LF
 where L.GROUP# = LF.GROUP# ;

GROUP#       THREAD#        SEQUENCE#        MEMBERS    ARCHIV STATUS           FIRST_CHANGE#            REDO_LOGFILE
------------ -------------- -------------------- -------------- ------ -------------------- -------------------------------- ----------------------------------
1        1          21201        1      YES    ACTIVE           75238614094              /home/oradata/xxx/redo01.log                
2        1          21199        1      YES    INACTIVE         75238587795              /home/oradata/xxx/redo02.log
11       1          21203        1      NO     CURRENT          75238632711              /home/oradata/xxx/redo11.log               >>>>====这是当前日志，包含最近的redo
10       1          21202        1      YES    ACTIVE           75238624057              /home/oradata/xxx/redo10.log
3        1          21200        1      YES    INACTIVE         75238601713              /home/oradata/xxx/redo03.log

通过前面查询的数据文件头的最小checkpoint_change#，用于查询所要从哪个日志开始恢复，最小的日志号一直到current redo中间的所有日志都必须可用，才能保证恢复成功。

1
2
3
4
5
6
7
8
 select thread#, sequence#, substr(name,1,80) from v$Archived_log
  where 75238614094 between first_change# and next_change#;


               THREAD#              SEQUENCE# SUBSTR(NAME,1,80)
------------------------------ ------------------------------ ----------------------------------------------------------------------
                 1              21200 xxxx
                 1              21200

根据以上结果表示需要恢复21200到21203之间的日志，我们当前已经到了21201，所以总共需要恢复21201和21202 2个文件。

将备份集中的归档日志恢复出来

1
2
3
4
5
6
7
8
9
10
11
RMAN> RESTORE ARCHIVELOG FROM SEQUENCE 21201 UNTIL SEQUENCE 21202;

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=21201
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=21202
channel ORA_DISK_1: reading from backup piece /bak/backup/arch_u8tqihpn_1_1
channel ORA_DISK_1: piece handle=/bak/backup/arch_u8tqihpn_1_1 
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:10:35

这里先处理控制文件里的redo路径问题，原路径是文件系统，新环境是asm，所有对所有的redo日志进行了rename

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                 IS_
---------- ------- ------- ---------------------------------------------------------------------- ---
     3     ONLINE  +REDO/xxx/redo03.log                           NO
     2     ONLINE  +REDO/xxx/redo02.log                           NO
     1     ONLINE  +REDO/xxx/redo01.log                           NO
     4     STANDBY +REDO/xxx/stdredo01.log                        NO
     5     STANDBY +REDO/xxx/stdredo02.log                        NO
     6     STANDBY +REDO/xxx/stdredo03.log                        NO
     7     STANDBY +REDO/xxx/stdredo04.log                        NO
    10     ONLINE  +REDO/xxx/redo10.log                           NO
    11     ONLINE  +REDO/xxx/redo11.log                           NO

ALTER DATABASE RENAME FILE '/home/oradata/xxx/redo03.log' to '+REDO/xxx/redo03.log';
ALTER DATABASE RENAME FILE '/home/oradata/xxx/redo02.log' to '+REDO/xxx/redo02.log';
ALTER DATABASE RENAME FILE '/home/oradata/xxx/redo01.log' to '+REDO/xxx/redo01.log';

ALTER DATABASE RENAME FILE '/home/oradata/xxx/stdredo01.log' to '+REDO/xxx/stdredo01.log';
ALTER DATABASE RENAME FILE '/home/oradata/xxx/stdredo02.log' to '+REDO/xxx/stdredo02.log';
ALTER DATABASE RENAME FILE '/home/oradata/xxx/stdredo03.log' to '+REDO/xxx/stdredo03.log';
ALTER DATABASE RENAME FILE '/home/oradata/xxx/stdredo04.log' to '+REDO/xxx/stdredo04.log';
ALTER DATABASE RENAME FILE '/home/oradata/xxx/redo10.log' to '+REDO/xxx/redo10.log';
ALTER DATABASE RENAME FILE '/home/oradata/xxx/redo11.log' to '+REDO/xxx/redo11.log';

当使用的是备份控制文件时，可以使用基于CANCEL的恢复

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
22:52:18 sys. xxx>RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL ;
ORA-00279: change 75238614094 generated at 02/23/2019 00:15:46 needed for thread 1
ORA-00289: suggestion : +NVMEDG/xxx/archivelog/2019_03_01/thread_1_seq_21201.664.1001796429
ORA-00280: change 75238614094 for thread 1 is in sequence #21201


22:52:35 Spexxxy log: {>RET>=suggested | filename | AUTO | CANCEL}
+NVMEDG/xxx/archivelog/2019_03_01/thread_1_seq_21201.664.1001796429
ORA-00279: change 75238624057 generated at 02/23/2019 00:19:25 needed for thread 1
ORA-00289: suggestion : +NVMEDG/xxx/archivelog/2019_03_01/thread_1_seq_21202.663.1001796179
ORA-00280: change 75238624057 for thread 1 is in sequence #21202
ORA-00278: log file '+NVMEDG/xxx/archivelog/2019_03_01/thread_1_seq_21201.664.1001796429' no longer needed for this recovery


22:52:49 Spexxxy log: {>RET>=suggested | filename | AUTO | CANCEL}
+NVMEDG/xxx/archivelog/2019_03_01/thread_1_seq_21202.663.1001796179
ORA-00279: change 75238632711 generated at 02/23/2019 00:22:37 needed for thread 1
ORA-00289: suggestion : +nvmedg
ORA-00280: change 75238632711 for thread 1 is in sequence #21203
ORA-00278: log file '+NVMEDG/xxx/archivelog/2019_03_01/thread_1_seq_21202.663.1001796179' no longer needed for this recovery


22:53:03 Spexxxy log: {>RET>=suggested | filename | AUTO | CANCEL}        >>>>====缺少进一步的日志用于恢复
auto
ORA-00308: cannot open archived log '+nvmedg'
ORA-17503: ksfdopn:2 Failed to open file +nvmedg
ORA-15045: ASM file name '+nvmedg' is not in reference form


ORA-00308: cannot open archived log '+nvmedg'
ORA-17503: ksfdopn:2 Failed to open file +nvmedg
ORA-15045: ASM file name '+nvmedg' is not in reference form


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: '+NVMEDG/xxx/datafile/system01.dbf'

当我恢复完21201和21202两个文件以后，发现依然无法打开数据库。

1
2
3
4
5
6
sys. >alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '+DG/xxx/datafile/system01.dbf'

那就表示目前数据文件仍然有不一致的地方，最后需要的部分则在current redo当中，因为我这个是测试环境，所以虽然没有current redo，但是我有21203号之后的归档日志，所以直接采用这些归档日志来进行recover，那具体要恢复到哪个scn呢，可以通过下面语句查询出来

1
2
3
4
5
6
7
8
select  min(FHSCN) "LOW FILEHDR SCN"
           , max(FHSCN) "MAX FILEHDR SCN"
           , max(FHAFS) "Min PITR ABSSCN"
        from X$KCVFH ;

LOW FILEHDR SCN  MAX FILEHDR SCN  Min PITR ABSSCN
---------------- ---------------- ----------------
75238632711  75238632711      75238770594

“LOW FILEHDR SCN” – 恢复进程开始的scn

“MAX FILEHDR SCN” – 为了使所有数据文件保持一致必须要恢复到的scn

IF “Min PITR ABSSCN” != 0 AND > “MAX FILEHDR SCN”

THEN “Min PITR ABSSCN” 则是为了使所有数据文件保持一致必须要恢复到的scn

所以根据上述原则必须要恢复到75238770594

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
RMAN> recover database until scn 75238770594;

Starting recover at 2019-03-01 23:16:30
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=2065 device type=DISK

starting media recovery

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=21203
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=21204
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=21205
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=21206
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=21207
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=21208
channel ORA_DISK_1: reading from backup piece /bak/backup/arch_u8tqihpn_1_1
channel ORA_DISK_1: piece handle=/bak/backup/arch_u8tqihpn_1_1
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:13:25
archived log file name=+NVMEDG/xxx/archivelog/2019_03_01/thread_1_seq_21203.677.1001805529 thread=1 sequence=21203
channel default: deleting archived log(s)
archived log file name=+NVMEDG/xxx/archivelog/2019_03_01/thread_1_seq_21203.677.1001805529 RECID=66515 STAMP=1001805655
archived log file name=+NVMEDG/xxx/archivelog/2019_03_01/thread_1_seq_21204.678.1001805657 thread=1 sequence=21204
channel default: deleting archived log(s)
archived log file name=+NVMEDG/xxx/archivelog/2019_03_01/thread_1_seq_21204.678.1001805657 RECID=66516 STAMP=1001805790
archived log file name=+NVMEDG/xxx/archivelog/2019_03_01/thread_1_seq_21205.679.1001806059 thread=1 sequence=21205
channel default: deleting archived log(s)
archived log file name=+NVMEDG/xxx/archivelog/2019_03_01/thread_1_seq_21205.679.1001806059 RECID=66517 STAMP=1001806190
archived log file name=+NVMEDG/xxx/archivelog/2019_03_01/thread_1_seq_21206.675.1001805395 thread=1 sequence=21206
channel default: deleting archived log(s)
archived log file name=+NVMEDG/xxx/archivelog/2019_03_01/thread_1_seq_21206.675.1001805395 RECID=66513 STAMP=1001805528
archived log file name=+NVMEDG/xxx/archivelog/2019_03_01/thread_1_seq_21207.676.1001805395 thread=1 sequence=21207
channel default: deleting archived log(s)
archived log file name=+NVMEDG/xxx/archivelog/2019_03_01/thread_1_seq_21207.676.1001805395 RECID=66512 STAMP=1001805524
archived log file name=+NVMEDG/xxx/archivelog/2019_03_01/thread_1_seq_21208.674.1001805393 thread=1 sequence=21208
channel default: deleting archived log(s)
archived log file name=+NVMEDG/xxx/archivelog/2019_03_01/thread_1_seq_21208.674.1001805393 RECID=66514 STAMP=1001805536
media recovery complete, elapsed time: 00:00:17
Finished recover at 2019-03-01 23:30:16

这次的恢复就正常没有报错，检查数据文件的scn和状态等

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
sys >select (select distinct checkpoint_change# from v$datafile) dfile_ckp,(select checkpoint_change# from v$database) db_ckp,(select distinct checkpoint_change# from v$datafile_header) dh_ckp from dual;

     DFILE_CKP         DB_CKP         DH_CKP
------------------ ------------------ ------------------
       75238770595    75238770595        75238770595

set numwidth 30;
set pagesize 50000;
alter session set nls_date_format = 'DD-MON-RRRR HH24:MI:SS';

select status,checkpoint_change#,checkpoint_time, resetlogs_change#, resetlogs_time, count(*), fuzzy from v$datafile_header group by status,checkpoint_change#,checkpoint_time, resetlogs_change#, resetlogs_time, fuzzy;

STATUS          CHECKPOINT_CHANGE# CHECKPOINT_TIME              RESETLOGS_CHANGE# RESETLOGS_TIME                    COUNT(*) FUZ
------- ------------------------------ ----------------------- ------------------------------ ----------------------- ------------------------------ ---
ONLINE             75238770595 01-MAR-2019 23:36:12           75238770595 01-MAR-2019 23:36:12                53 NO

尝试打开数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery


alter database open;
alter database open
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 97239
Session ID: 1921 Serial number: 1

重建控制文件

1
2
3
4
5
6
7
8
9
10
11
12
CREATE CONTROLFILE REUSE DATABASE "XXX" NORESETLOGS  ARCHIVELOG
......省略

select name, controlfile_type from v$database ;

NAME                   CONTROLFILE_TY
------------------------------ --------------
XXX                CREATED

alter database open;

Database altered.

至此恢复完毕。

Troubleshooting
#Backup & Recovery #ora-
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
https://www.xbdba.com/2019/03/02/ora-01547-warning-recover-succeeded-but-open-resetlogs-would-get-error-below/
作者
xbdba
发布于
2019年3月2日
许可协议