oracle异常断电导致数据文件中的scn不一致

评论 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u01/oradata/orcl/system01.dbf'
SQL> recover database using backup controlfile until cancel;
ORA-00283: recovery session canceled due to errors
ORA-16433: The database must be opened in read/write mode.
 SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-16433: The database must be opened in read/write mode.
 SQL> alter database backup controlfile to trace;
alter database backup controlfile to trace
*
ERROR at line 1:
ORA-16433: The database must be opened in read/write mode.

数据库，没有任何备份，于是决定重建控制文件。
重建控制文件的语句如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE CONTROLFILE REUSE DATABASE ORCL RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/oradata/orcl/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u01/oradata/orcl/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/oradata/orcl/redo03.log'  SIZE 50M BLOCKSIZE 512
DATAFILE
  '/u01/oradata/orcl/system01.dbf',
  '/u01/oradata/orcl/sysaux01.dbf',
  '/u01/oradata/orcl/undotbs03.db',
  '/u01/oradata/orcl/users01.dbf',
  '/u01/oradata/orcl/test01.dbf'
CHARACTER SET WE8MSWIN1252
;

进行recover操作。将所有的redo日志文件都进行了应用，应用第一组redo日志时有报错，根据报错信息，判断redo文件也有坏块。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 2147582117 generated at 07/26/2021 05:47:47 needed for thread
1
ORA-00289: suggestion : /arch/1_1_1078897661.dbf
ORA-00280: change 2147582117 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/oradata/orcl/redo01.log
ORA-00283: recovery session canceled due to errors
ORA-00399: corrupt change description in redo log
ORA-00353: log corruption near block 3 change 2147582696 time 07/26/2021
05:47:48
ORA-00334: archived log: '/u01/oradata/orcl/redo01.log'
ORA-01112: media recovery not started
SQL>

停止数据库，修改pfile文件，修改undo_management、undo_tablespace，添加隐含参数_allow_resetlogs_corruption

*.undo_management=’MANUAL’
*.undo_tablespace=’SYSTEM’
*._allow_resetlogs_corruption=TRUE

使用pfile启动数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SQL> shutdown immediate;
ORA-01109: database not open
 
 
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount pfile='/home/oracle/pfile.ora';
ORACLE instance started.
 
Total System Global Area  626327552 bytes
Fixed Size                  2215944 bytes
Variable Size             192942072 bytes
Database Buffers          427819008 bytes
Redo Buffers                3350528 bytes
SQL>

重建控制文件

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> CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 '/u01/oradata/orcl/redo01.log'  SIZE 50M BLOCKSIZE 512,
  9    GROUP 2 '/u01/oradata/orcl/redo02.log'  SIZE 50M BLOCKSIZE 512,
 10    GROUP 3 '/u01/oradata/orcl/redo03.log'  SIZE 50M BLOCKSIZE 512
 11  -- STANDBY LOGFILE
 12  DATAFILE
 13    '/u01/oradata/orcl/system01.dbf',
 14    '/u01/oradata/orcl/sysaux01.dbf',
 15    '/u01/oradata/orcl/undotbs03.db',
 16    '/u01/oradata/orcl/users01.dbf',
 17    '/u01/oradata/orcl/test01.dbf'
 18  --  ,'/u01/oradata/orcl/test_ind01.dbf'
 19  CHARACTER SET WE8MSWIN1252
 20  ;
 
Control file created.
 
SQL>

以resetlog打开数据库，报ORA-00600错误，数据库挡掉了。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [2662], [0], [2147582701], [0],
[2147583575], [33554720], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [2662], [0], [2147582700], [0],
[2147583575], [33554720], [], [], [], [], [], []
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2662], [0], [2147582699], [0],
[2147583575], [33554720], [], [], [], [], [], []
Process ID: 15912
Session ID: 1 Serial number: 3
 
 
SQL>

设置scn增进，再次拉起数据库。
本案中设置level为3

SQL> alter database open resetlogs;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
alter database open resetlogs
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [2662], [0], [2147582701], [0],
[2147583575], [33554720], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [2662], [0], [2147582700], [0],
[2147583575], [33554720], [], [], [], [], [], []
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2662], [0], [2147582699], [0],
[2147583575], [33554720], [], [], [], [], [], []
Process ID: 15912
Session ID: 1 Serial number: 3
SQL>

*ORA-600 [2662] “Block SCN is ahead of Current SCN”*，说明当前数据库的数据块的SCN早于当前的SCN，主要是和存储在UGA变量中的dependent SCN进行比较，如果当前的SCN小于它，数据库就会产生这个ORA-600 [2662]的错误了。这个错误一共有五个参数，分别代表不同的含义，
ORA-600 [2662] [a] [b] [c] [d] [e]
Arg [a] Current SCN WRAP
Arg [b] Current SCN BASE
Arg [c] dependent SCN WRAP
Arg [d] dependent SCN BASE
Arg [e] Where present this is the DBA where the dependent SCN came from.
我们分析错误中的提示，它的参数b=431267754,d=431272752,表明当前的SCN确实是小于dependent SCN，所以产生了这个600的错误
查询资料我们知道通过这几个参数值，根据一定的规则可以计算出我们需要的level。

计算规则如下：
Arg [c]*4得出一个数值，假设为t_wrap
如果Arg [d]=0，则t_wrap值为需要的level
Arg [d] < 1073741824，t_wrap+1为需要的level
Arg [d] < 2147483648，t_wrap+2为需要的level
Arg [d] < 3221225472，t_wrap+3为需要的level
下面根据报错信息计算level的值
t_wrap=Arg [c]*4=0 * 4 = 0
2147483648<Arg [d]<3221225472
所以level的值应该为：t_wrap+3 = 0 + 3 = 3

1
2
3
4
5
6
7
8
9
10
11
12
SQL> alter session set events '10015 trace name adjust_scn level 3';
session altered. 
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u01/oradata/orcl/system01.dbf'
SQL> recover database ;
ORA-00283: recovery session canceled due to errors
ORA-16433: The database must be opened in read/write mode.
SQL>

又报了ORA-16433错误，重建控制文件，再来一遍啊。。。

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
SQL> shutdown abort;
ORACLE instance shut down.
SQL> 
SQL> 
SQL> startup nomount pfile='/home/oracle/pfile.ora';
ORACLE instance started.
 
Total System Global Area  626327552 bytes
Fixed Size                  2215944 bytes
Variable Size             192942072 bytes
Database Buffers          427819008 bytes
Redo Buffers                3350528 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 '/u01/oradata/orcl/redo01.log'  SIZE 50M BLOCKSIZE 512,
  9    GROUP 2 '/u01/oradata/orcl/redo02.log'  SIZE 50M BLOCKSIZE 512,
 10    GROUP 3 '/u01/oradata/orcl/redo03.log'  SIZE 50M BLOCKSIZE 512
 11  -- STANDBY LOGFILE
 12  DATAFILE
 13    '/u01/oradata/orcl/system01.dbf',
 14    '/u01/oradata/orcl/sysaux01.dbf',
 15    '/u01/oradata/orcl/undotbs03.db',
 16    '/u01/oradata/orcl/users01.dbf',
 17    '/u01/oradata/orcl/test01.dbf'
 18  --  ,'/u01/oradata/orcl/test_ind01.dbf'
 19  CHARACTER SET WE8MSWIN1252
 20  ;
Control file created. 
SQL> alter session set events '10015 trace name adjust_scn level 3'; 
Session altered. 
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 2147582121 generated at 07/26/2021 21:47:05 needed for thread 1
ORA-00289: suggestion : /arch/1_1_1078955221.dbf
ORA-00280: change 2147582121 for thread 1 is in sequence #1 
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
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: '/u01/oradata/orcl/system01.dbf'
ORA-01112: media recovery not started
SQL> alter database open resetlogs;
Database altered.
SQL>

此时数据库可以正常open，但是日志文件里面再次有ORA-00600报错。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Trace dumping is performing id=[cdmp_20210726230100]
Doing block recovery for file 8 block 641
Resuming block recovery (PMON) for file 8 block 641
Block recovery from logseq 1, block 3207 to scn 3221226677
Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0
  Mem# 0: /u01/oradata/orcl/redo01.log
Block recovery completed at rba 1.3209.16, scn 0.3221226679
Trace dumping is performing id=[cdmp_20210726230101]
Doing block recovery for file 8 block 368
Resuming block recovery (PMON) for file 8 block 368
Block recovery from logseq 1, block 3207 to scn 3221226824
Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0
  Mem# 0: /u01/oradata/orcl/redo01.log
Block recovery completed at rba 1.3265.16, scn 0.3221226825
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_m000_21333.trc  (incident=143234):
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_143234/orcl_m000_21333_i143234.trc
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_m000_21333.trc:
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []

说明undo表空间还是有问题的。创建新的undo表空间，删除旧的undo表空间

1
2
3
4
5
6
7
SQL> create undo tablespace UNDOTBS4 datafile '/u01/oradata/orcl/undotbs04.db' size 50M;
Tablespace created. 
SQL> alter system set undo_tablespace='UNDOTBS4' ;
System altered. 
SQL> drop tablespace UNDOTBS3 including contents and datafiles; 
Tablespace dropped.
SQL>

注：新建了控制文件之后数据库并没有指定临时文件。需创建临时表空间，然后数据库指定到默认临时表空间

gladlyDBA

个人简介。