ORA-01189;ORA-01111;ORA-01190;ORA-00600[2662]

原创

食品安全辛吉飞 2023-06-11 19:42:52 博主文章分类：Oracle ©著作权

文章标签 ORA-01189 ORA-01111 ORA-01190 ORA-00600[2662] 文章分类 Oracle 数据库 私藏项目实操分享

©著作权归作者所有：来自51CTO博客作者食品安全辛吉飞的原创作品，请联系作者获取转载授权，否则将追究法律责任

1. 环境模拟

1.1 创建测试表空间

create tablespace enmo5 datafile '/oracle/app/oradata/zydb/enmo05.dbf' size 10M;
create tablespace enmo6 datafile '/oracle/app/oradata/zydb/enmo06.dbf' size 10M;
create tablespace enmo7 datafile '/oracle/app/oradata/zydb/enmo07.dbf' size 10M;
create tablespace enmo8 datafile '/oracle/app/oradata/zydb/enmo08.dbf' size 10M;
create tablespace enmo9 datafile '/oracle/app/oradata/zydb/enmo09.dbf' size 10M;

1.2. 创建测试表

create table sys.test tablespace enmo5 as select * from dba_objects;

1.3. 重建控制文件

查看控制文件
alter database backup controlfile to trace as '/oracle/control.trc';

控制文件内容
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ZYDB" RESETLOGS FORCE LOGGING ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/oracle/app/oradata/zydb/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/oracle/app/oradata/zydb/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/oracle/app/oradata/zydb/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/oracle/app/oradata/zydb/system01.dbf',
  '/oracle/app/oradata/zydb/sysaux01.dbf',
  '/oracle/app/oradata/zydb/undotbs01.dbf',
  '/oracle/app/oradata/zydb/users01.dbf',
  '/oracle/app/oradata/zydb/itpuxdb.dbf',
  '/oracle/app/oradata/zydb/enmo05.dbf',
  '/oracle/app/oradata/zydb/enmo06.dbf',
  '/oracle/app/oradata/zydb/enmo07.dbf',
  '/oracle/app/oradata/zydb/enmo08.dbf',
  '/oracle/app/oradata/zydb/enmo09.dbf'
CHARACTER SET AL32UTF8
;


shutdown immediate;

STARTUP NOMOUNT

新建控制文件
CREATE CONTROLFILE REUSE DATABASE "ZYDB" RESETLOGS FORCE LOGGING ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/oracle/app/oradata/zydb/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/oracle/app/oradata/zydb/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/oracle/app/oradata/zydb/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/oracle/app/oradata/zydb/system01.dbf',
  '/oracle/app/oradata/zydb/sysaux01.dbf',
  '/oracle/app/oradata/zydb/undotbs01.dbf',
  '/oracle/app/oradata/zydb/users01.dbf',
  '/oracle/app/oradata/zydb/itpuxdb.dbf'
  --'/oracle/app/oradata/zydb/enmo05.dbf',
  --'/oracle/app/oradata/zydb/enmo06.dbf',
  --'/oracle/app/oradata/zydb/enmo07.dbf',
  --'/oracle/app/oradata/zydb/enmo08.dbf',
  --'/oracle/app/oradata/zydb/enmo09.dbf'
CHARACTER SET AL32UTF8
;


alter database open resetlogs;

1.4. 发现部分业务有无法访问

SQL> select * from test;   
select * from test
              *
ERROR at line 1:
ORA-00376: file 6 cannot be read at this time
ORA-01111: name for data file 6 is unknown - rename to correct file
ORA-01110: data file 6:
'/oracle/app/oracle/product/11.2.0/db_1/dbs/MISSING00006'

1.5. 查询数据文件状态

set linesize 300 pagesize 2000
col ERROR for a20
col NAME for a60
set numw 20
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

select FILE#,STATUS,TS#,RFILE#,RESETLOGS_CHANGE#,RESETLOGS_TIME,CHECKPOINT_CHANGE#,CHECKPOINT_TIME,CHECKPOINT_COUNT,status,ERROR,RECOVER,FUZZY from v$datafile_header;

ORA-01189;ORA-01111;ORA-01190;ORA-00600[2662]_ORA-01111

2. 恢复过程

2.1 尝试重建控制文件

shutdown immediate;

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "ZYDB" RESETLOGS FORCE LOGGING ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/oracle/app/oradata/zydb/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/oracle/app/oradata/zydb/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/oracle/app/oradata/zydb/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/oracle/app/oradata/zydb/system01.dbf',
  '/oracle/app/oradata/zydb/sysaux01.dbf',
  '/oracle/app/oradata/zydb/undotbs01.dbf',
  '/oracle/app/oradata/zydb/users01.dbf',
  '/oracle/app/oradata/zydb/itpuxdb.dbf',
  '/oracle/app/oradata/zydb/enmo05.dbf',
  '/oracle/app/oradata/zydb/enmo06.dbf',
  '/oracle/app/oradata/zydb/enmo07.dbf',
  '/oracle/app/oradata/zydb/enmo08.dbf',
  '/oracle/app/oradata/zydb/enmo09.dbf'
CHARACTER SET AL32UTF8
;

CREATE CONTROLFILE REUSE DATABASE "ZYDB" RESETLOGS FORCE LOGGING ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01189: file is from a different RESETLOGS than previous files
ORA-01110: data file 6: '/oracle/app/oradata/zydb/enmo05.dbf'

2.2 数据库恢复

SELECT TS#,FILE#,NAME,STATUS,CHECKPOINT_CHANGE# FROM V$DATAFILE;

alter database rename file '/oracle/app/oracle/product/11.2.0/db_1/dbs/MISSING00006' to '/oracle/app/oradata/zydb/enmo05.dbf';
alter database rename file '/oracle/app/oracle/product/11.2.0/db_1/dbs/MISSING00007' to '/oracle/app/oradata/zydb/enmo06.dbf';
alter database rename file '/oracle/app/oracle/product/11.2.0/db_1/dbs/MISSING00008' to '/oracle/app/oradata/zydb/enmo07.dbf';
alter database rename file '/oracle/app/oracle/product/11.2.0/db_1/dbs/MISSING00009' to '/oracle/app/oradata/zydb/enmo08.dbf';
alter database rename file '/oracle/app/oracle/product/11.2.0/db_1/dbs/MISSING00010' to '/oracle/app/oradata/zydb/enmo09.dbf';

set linesize 300 pagesize 2000
col ERROR for a20
col NAME for a60
set numw 20
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

select FILE#,STATUS,TS#,RFILE#,RESETLOGS_CHANGE#,RESETLOGS_TIME,CHECKPOINT_CHANGE#,CHECKPOINT_TIME,CHECKPOINT_COUNT,status,ERROR,RECOVER,FUZZY from v$datafile_header;

ORA-01189;ORA-01111;ORA-01190;ORA-00600[2662]_ORA-00600[2662]_02

SQL> alter database datafile 6,7,8,9,10 online;

ERROR at line 1:
ORA-01190: control file or data file 6 is from before the last RESETLOGS
ORA-01110: data file 6: '/oracle/app/oradata/zydb/enmo05.dbf'

 查看当前数据文件的reset scn与reset time的值
select hxfil   file_id,
       FHRLC   RESET_TIME,
       fhrlc_i RESET_COUNT,
       FHRLS   RESET_SCN,
       FHPRC   LAST_RESET_TIME,
       FHPRC_I LAST_RESET_COUNT,
       FHPRS   LAST_RESET_SCN
  from x$kcvfh;

ORA-01189;ORA-01111;ORA-01190;ORA-00600[2662]_ORA-01189_03

2.3推进SCN值

SQL> shu immediate;

SQL> startup mount;

SQL> select checkpoint_change# from v$datafile;

CHECKPOINT_CHANGE#
------------------
    2807658
    2807658
    2807658
    2807658
    2807658
   0
   0
   0
   0
   0

10 rows selected.

SQL> oradebug setmypid
Statement processed.
SQL> oradebug dumpvar sga kcsgscn_
kcslf kcsgscn_ [060019598, 0600195C8) = 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 60019278 00000000
SQL> oradebug poke 0x060019598 8 3807658
BEFORE: [060019598, 0600195A0) = 00000000 00000000
AFTER:	[060019598, 0600195A0) = 003A19AA 00000000
SQL> oradebug dumpvar sga kcsgscn_
kcslf kcsgscn_ [060019598, 0600195C8) = 003A19AA 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 60019278 00000000
SQL> alter database datafile 6,7,8,9,10 online;

Database altered.

???recover database using backup controlfile;

SQL> select file#,RESETLOGS_CHANGE#,status from v$datafile_header;

     FILE# RESETLOGS_CHANGE# STATUS
---------- ----------------- --------------
	 1	     2804201 ONLINE
	 2	     2804201 ONLINE
	 3	     2804201 ONLINE
	 4	     2804201 ONLINE
	 5	     2804201 ONLINE
	 6	     2804201 ONLINE
	 7	     2804201 ONLINE
	 8	     2804201 ONLINE
	 9	     2804201 ONLINE
	10	     2804201 ONLINE

10 rows selected.

2.4 恢复验证

查看_allow_resetlogs_corruption的值

SELECT ksppinm, ksppstvl, ksppdesc FROM x$ksppi x, x$ksppcv y WHERE x.indx = y.indx AND ksppinm = '_allow_resetlogs_corruption';

修改_allow_resetlogs_corruption的值

alter system set "_allow_resetlogs_corruption"=true scope=spfile;

重启

startup mount;

SQL> show parameter _allow_resetlogs_corruption

NAME				     TYPE		    VALUE
------------------------------------ ---------------------- ------------------------------
_allow_resetlogs_corruption	     boolean		    TRUE


SQL> alter database open resetlogs;

ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2662], [0], [2804210], [0], [2807654], [4194432], [], [], [], [], [], []
Process ID: 89190
Session ID: 387 Serial number: 5

推SCN

SQL> startup mount;

SQL> select checkpoint_change# from v$datafile;

CHECKPOINT_CHANGE#
------------------
	   2804206
	   2804206
	   2804206
	   2804206
	   2804206
	   2804206
	   2804206
	   2804206
	   2804206
	   2804206

10 rows selected.

SQL> oradebug setmypid
Statement processed.
SQL> oradebug dumpvar sga kcsgscn_
kcslf kcsgscn_ [060019598, 0600195C8) = 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 60019278 00000000
SQL> oradebug poke 0x060019598 8 3804206
BEFORE: [060019598, 0600195A0) = 00000000 00000000
AFTER:	[060019598, 0600195A0) = 003A0C2E 00000000
SQL> oradebug dumpvar sga kcsgscn_
kcslf kcsgscn_ [060019598, 0600195C8) = 003A0C2E 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 60019278 00000000
SQL> 
SQL> alter database open;

Database altered.

SQL> select count(1) from test;

  COUNT(1)
----------
     71192

解法：

设置隐藏参数 "_allow_resetlogs_corruption" = true , 强制起库；

推进SCN；