_allow_resetlogs_corruption 的搜索结果

数据库恢复的敏感性—重建控制文件使用不合适数据文件

发表于 2016 年 7 月 30 日由惜分飞

有客户数据库由于某种原因无法open,请求我们技术支持.通过检查alert日志发现数据库是由于ORA-600 kccpb_sanity_check_2错误.并且他们已经重建控制文件,通过我们的Oracle数据库异常恢复检查脚本(Oracle Database Recovery Check)
datafile 6 异常
wrong-file1

wrong-file-redo

通过这里我们发现datafile 6 数据文件头是干净的,而且对应的redo seq为5270,而其他文件头都是fuzzy,而且对应的redo为295902613.这里怀疑datafile 6 可能是错误的.当然对于这样scn相距比较大的情况,我们可以通过隐含参数,修改scn等方法强制让该库起来(或者该文件online),但是从现在看到的情况,文件很可能异常,这样强制恢复,可能没有实际意义.
分析alert日志
alert-log

这个里面可以发现是先删除了sde表空间,然后创建了同一个表空间,只是数据文件路径不一样了.而且正好在seq为5270的地方操作的.现在出现datafile 6异常的原因已经清楚,就是创建数据控制文件的时候,使用了错误的数据文件导致.

完美恢复数据库

D:\>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on 星期六 7月 30 16:31:01 2016

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> shutdown immediate;
ORA-01109: 数据库未打开


已经卸载数据库。
ORACLE 例程已经关闭。
SQL> STARTUP NOMOUNT
ORACLE 例程已经启动。

Total System Global Area 5133828096 bytes
Fixed Size                  2011360 bytes
Variable Size            2382368544 bytes
Database Buffers         2734686208 bytes
Redo Buffers               14761984 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "LANDDB" NORESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 'F:\ORADATA\LANDDB\ONLINELOG\O1_MF_1_4JCM05KL_.LOG'  SIZE 50M,
  9    GROUP 2 'F:\ORADATA\LANDDB\ONLINELOG\O1_MF_2_4JCM064D_.LOG'  SIZE 50M,
 10    GROUP 3 'F:\ORADATA\LANDDB\ONLINELOG\O1_MF_3_4JCM06PG_.LOG'  SIZE 50M
 11  DATAFILE
 12    'F:\ORADATA\LANDDB\DATAFILE\O1_MF_SYSTEM_4JCLYY6T_.DBF',
 13    'F:\ORADATA\LANDDB\DATAFILE\O1_MF_UNDOTBS1_4JCLYY8S_.DBF',
 14    'F:\ORADATA\LANDDB\DATAFILE\O1_MF_SYSAUX_4JCLYY7B_.DBF',
 15    'F:\ORADATA\LANDDB\DATAFILE\O1_MF_USERS_4JCLYY98_.DBF',
 16    'F:\ORADATA\LANDDB\DATAFILE\FUJIAN',
 17    'D:\data\sde.dbf'
 18  CHARACTER SET ZHS16GBK
 19  ;

控制文件已创建。

SQL> recover database;
完成介质恢复。
SQL> alter database open;

数据库已更改。

SQL>

这个库比较幸运,客户发现异常之后,里面停止了有风险性的操作(比如使用_allow_resetlogs_corruption参数,resetlogs库等),使得数据完美恢复0丢失.如果条件允许最好使用老的控制文件来重建新控制文件,而不要通过人工去系统中找数据文件来实现恢复,这样很可能有遗落或者使用错误的数据文件

发表在 Oracle备份恢复 | 标签为 kccpb_sanity_check_2, ORA-600 kccpb_sanity_check_2 | 评论关闭

Oracle 12c redo 丢失恢复

发表于 2016 年 7 月 11 日由惜分飞

模拟redo丢失
对数据库的一个pdb模拟事务操作，然后abort库，并且删除所有redo，模拟生产环境redo丢失的case

[oracle@ora1221 oradata]$ ss

SQL*Plus: Release 12.2.0.0.3 Production on Wed Jun 15 10:13:20 2016

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 2516582400 bytes
Fixed Size                  8260048 bytes
Variable Size             671090224 bytes
Database Buffers         1828716544 bytes
Redo Buffers                8515584 bytes
Database mounted.
Database opened.
SQL> 
SQL> 
SQL> set pages 100
SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           MOUNTED
         4 PDB2                           MOUNTED
SQL> select con_id,file#,checkpoint_change# from v$datafile_header order by 1;

    CON_ID      FILE# CHECKPOINT_CHANGE#
---------- ---------- ------------------
         1          1            1500157
         1          3            1500157
         1          4            1500157
         1          7            1500157
         2          5            1371280
         2          6            1371280
         2          8            1371280
         3          9            1499902
         3         12            1499902
         3         11            1499902
         3         10            1499902
         4         15            1499903
         4         14            1499903
         4         13            1499903
         4         16            1499903

15 rows selected.

SQL> alter PLUGGABLE database pdb1 open;                                      

Pluggable database altered.

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           MOUNTED
SQL> alter session set container=pdb1;

Session altered.

SQL> create user chf identified by oracle;

User created.

SQL> grant dba to chf;

Grant succeeded.

SQL> create table chf.t_pdb1_xifenfei as select * from dba_objects;

Table created.

SQL> delete from chf.t_pdb1_xifenfei;

72426 rows deleted.

--另外一个节点
[oracle@ora1221 ~]$ ss

SQL*Plus: Release 12.2.0.0.3 Production on Wed Jun 15 10:19:21 2016

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.0.3 - 64bit Production

SQL> shutdown abort
ORACLE instance shut down.

[oracle@ora1221 orcl12c2]$ ls redo*
redo01.log  redo02.log  redo03.log
[oracle@ora1221 orcl12c2]$ rm redo0*
[oracle@ora1221 orcl12c2]$ ls -l redo*
ls: cannot access redo*: No such file or directory

尝试启动数据库

[oracle@ora1221 orcl12c2]$ ss

SQL*Plus: Release 12.2.0.0.3 Production on Wed Jun 15 10:26:20 2016

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 2516582400 bytes
Fixed Size                  8260048 bytes
Variable Size             671090224 bytes
Database Buffers         1828716544 bytes
Redo Buffers                8515584 bytes
Database mounted.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/orcl12c2/redo01.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7

使用隐含参数启动

----pfile里面增加
_allow_error_simulation=TRUE
_allow_resetlogs_corruption=true
~

SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down

SQL> startup pfile='/tmp/pfile' mount
ORACLE instance started.

Total System Global Area 2516582400 bytes
Fixed Size                  8260048 bytes
Variable Size             671090224 bytes
Database Buffers         1828716544 bytes
Redo Buffers                8515584 bytes
Database mounted.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [kcbzib_kcrsds_1], [], [], [], [],
[], [], [], [], [], [], []
Process ID: 36797
Session ID: 16 Serial number: 24277

继续重启库
ORA-600 kcbzib_kcrsds_1错误尝试重启数据库,如果不行,考虑使用bbed修改文件头信息

SQL> startup mount pfile='/tmp/pfile'
ORACLE instance started.

Total System Global Area 2516582400 bytes
Fixed Size                  8260048 bytes
Variable Size             671090224 bytes
Database Buffers         1828716544 bytes
Redo Buffers                8515584 bytes
Database mounted.

SQL> recover database until cancel;
ORA-00283: recovery session canceled due to errors
ORA-16433: The database or pluggable database must be opened in read/write
mode.


SQL> alter database backup controlfile to trace as '/tmp/ctl';
alter database backup controlfile to trace as '/tmp/ctl'
*
ERROR at line 1:
ORA-16433: The database or pluggable database must be opened in read/write
mode.

重建控制文件

SQL> startup nomount pfile='/tmp/pfile'
ORACLE instance started.

Total System Global Area 2516582400 bytes
Fixed Size                  8260048 bytes
Variable Size             671090224 bytes
Database Buffers         1828716544 bytes
Redo Buffers                8515584 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "orcl12c2" RESETLOGS  NOARCHIVELOG  
  2      MAXLOGFILES 50  
  3      MAXLOGMEMBERS 5  
  4      MAXDATAFILES 100  
  5      MAXINSTANCES 1  
  6      MAXLOGHISTORY 226  
  7  LOGFILE  
  8    GROUP 1 '/u01/app/oracle/oradata/orcl12c2/redo01.log'  SIZE 200M,  
  9    GROUP 2 '/u01/app/oracle/oradata/orcl12c2/redo02.log'  SIZE 200M,  
 10    GROUP 3 '/u01/app/oracle/oradata/orcl12c2/redo03.log'  SIZE 200M  
 11  DATAFILE  
 12  '/u01/app/oracle/oradata/orcl12c2/system01.dbf',
 13  '/u01/app/oracle/oradata/orcl12c2/sysaux01.dbf',
 14  '/u01/app/oracle/oradata/orcl12c2/undotbs01.dbf',
 15  '/u01/app/oracle/oradata/orcl12c2/pdbseed/system01.dbf',
 16  '/u01/app/oracle/oradata/orcl12c2/pdbseed/sysaux01.dbf',
 17  '/u01/app/oracle/oradata/orcl12c2/users01.dbf',
 18  '/u01/app/oracle/oradata/orcl12c2/pdbseed/undotbs01.dbf',
 19  '/u01/app/oracle/oradata/orcl12c2/pdb1/system01.dbf',
 20  '/u01/app/oracle/oradata/orcl12c2/pdb1/sysaux01.dbf',
 21  '/u01/app/oracle/oradata/orcl12c2/pdb1/undotbs01.dbf',
 22  '/u01/app/oracle/oradata/orcl12c2/pdb1/users01.dbf',
 23  '/u01/app/oracle/oradata/orcl12c2/pdb2/system01.dbf',
 24  '/u01/app/oracle/oradata/orcl12c2/pdb2/sysaux01.dbf',
 25  '/u01/app/oracle/oradata/orcl12c2/pdb2/undotbs01.dbf',
 26  '/u01/app/oracle/oradata/orcl12c2/pdb2/users01.dbf'
 27  CHARACTER SET AL32UTF8  
 28  ;  

Control file created.

SQL> recover database until cancel;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done


SQL> recover database until cancel using backup controlfile;
ORA-00279: change 1500161 generated at 06/15/2016 10:40:42 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/12.2.0/db_2/dbs/arch1_1_914582438.dbf
ORA-00280: change 1500161 for thread 1 is in sequence #1


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/orcl12c2/redo01.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.
1
<strong>open过程alert日志</strong>
1
<strong>open pdb1</strong>
1
SQL> alter PLUGGABLE database pdb1  open;

Pluggable database altered.

pdb1 open alert日志

2016-06-15T11:13:39.423057+08:00
alter PLUGGABLE database pdb1  open
PDB1(3):Autotune of undo retention is turned on. 
2016-06-15T11:13:39.495559+08:00
PDB1(3):Endian type of dictionary set to little
PDB1(3):[40547] Successfully onlined Undo Tablespace 2.
PDB1(3):Undo initialization finished serial:0 start:371149831 end:371149872 diff:41 ms (0.0 seconds)
PDB1(3):Database Characterset for PDB1 is AL32UTF8
PDB1(3):*********************************************************************
PDB1(3):WARNING: The following temporary tablespaces in container(PDB1)
PDB1(3):         contain no files.
PDB1(3):         This condition can occur when a backup controlfile has
PDB1(3):         been restored.  It may be necessary to add files to these
PDB1(3):         tablespaces.  That can be done using the SQL statement:
PDB1(3): 
PDB1(3):         ALTER TABLESPACE <tablespace_name> ADD TEMPFILE
PDB1(3): 
PDB1(3):         Alternatively, if these temporary tablespaces are no longer
PDB1(3):         needed, then they can be dropped.
PDB1(3):           Empty temporary tablespace: TEMP
PDB1(3):*********************************************************************
PDB1(3):Opatch validation is skipped for PDB PDB1 (con_id=0)
PDB1(3):Opening pdb with no Resource Manager plan active
Pluggable database PDB1 opened read write
Completed: alter PLUGGABLE database pdb1  open

open pdb2

SQL> alter PLUGGABLE database pdb2 open;
alter PLUGGABLE database pdb2 open
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [17090], [], [], [], [], [], [], [],
[], [], [], []

分析alert日志和trace文件

--alert日志部分
PDB1(3):alter PLUGGABLE database pdb2 open
PDB1(3):ORA-65118 signalled during: alter PLUGGABLE database pdb2 open...
2016-06-15T11:28:57.439963+08:00
PDB1(3):Unified Audit: Audit record write to table failed due to ORA-25153. 
Writing the audit record to OS spillover file. Please grep in the trace files for ORA-25153 for more diagnostic information.
Errors in file /u01/app/oracle/diag/rdbms/orcl12c2/orcl12c2/trace/orcl12c2_ora_40547.trc  (incident=29073) (PDBNAME=PDB1):
ORA-00600: internal error code, arguments: [17090], [], [], [], [], [], [], [], [], [], [], []
PDB1(3):Incident details in: /u01/app/oracle/diag/rdbms/orcl12c2/orcl12c2/incident/incdir_29073/orcl12c2_ora_40547_i29073.trc
PDB1(3):*****************************************************************
PDB1(3):An internal routine has requested a dump of selected redo.
PDB1(3):This usually happens following a specific internal error, when
PDB1(3):analysis of the redo logs will help Oracle Support with the
PDB1(3):diagnosis.
PDB1(3):It is recommended that you retain all the redo logs generated (by
PDB1(3):all the instances) during the past 12 hours, in case additional
PDB1(3):redo dumps are required to help with the diagnosis.
PDB1(3):*****************************************************************
2016-06-15T11:28:59.123041+08:00
PDB1(3):Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2016-06-15T11:28:59.945667+08:00
Dumping diagnostic data in directory=[cdmp_20160615112859], requested by (instance=1, osid=40547), summary=[incident=29073].
2016-06-15T11:35:59.987419+08:00
PDB1(3): alter PLUGGABLE database pdb2 open
PDB1(3):ORA-65118 signalled during:  alter PLUGGABLE database pdb2 open...


--trace部分
PARSING IN CURSOR #0x7f051a3d7650 len=118 dep=1 uid=0 oct=3 lid=0 tim=372490287736 hv=1128335472 ad='0x6ca82f00' sqlid='gu930gd1n223h'
select tablespace_name, tablespace_size, allocated_space, free_space, con_id  from cdb_temp_free_space order by con_id
END OF STMT
EXEC #0x7f051a3d7650:c=0,e=144,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=2538033465,tim=372490287732
FETCH #0x7f051a3d7650:c=0,e=290,p=0,cr=14,cu=0,mis=0,r=0,dep=1,og=4,plh=2538033465,tim=372490288109
WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 52 FileOperation=2 fileno=0 filetype=36 obj#=402 tim=372490288373
WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 17 FileOperation=8 fileno=1 filetype=36 obj#=402 tim=372490288577
WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 3 FileOperation=8 fileno=1 filetype=36 obj#=402 tim=372490288655
WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 690 FileOperation=8 fileno=1 filetype=36 obj#=402 tim=372490289365
WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 6 FileOperation=8 fileno=1 filetype=36 obj#=402 tim=372490289470
WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 445 FileOperation=8 fileno=1 filetype=36 obj#=402 tim=372490289934
WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 3 FileOperation=8 fileno=1 filetype=36 obj#=402 tim=372490289983
WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 375 FileOperation=8 fileno=1 filetype=36 obj#=402 tim=372490290374
WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 6 FileOperation=8 fileno=1 filetype=36 obj#=402 tim=372490290453
WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 367 FileOperation=8 fileno=1 filetype=36 obj#=402 tim=372490290839
WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 3 FileOperation=8 fileno=1 filetype=36 obj#=402 tim=372490290882
WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 355 FileOperation=8 fileno=1 filetype=36 obj#=402 tim=372490291252
WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 4 FileOperation=8 fileno=1 filetype=36 obj#=402 tim=372490291298
WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 276 FileOperation=8 fileno=1 filetype=36 obj#=402 tim=372490291590
WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 1 FileOperation=8 fileno=1 filetype=36 obj#=402 tim=372490291614
WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 256 FileOperation=8 fileno=1 filetype=36 obj#=402 tim=372490291879
WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 2 FileOperation=8 fileno=1 filetype=36 obj#=402 tim=372490291903
WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 261 FileOperation=8 fileno=1 filetype=36 obj#=402 tim=372490292172
WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 30 FileOperation=3 fileno=0 filetype=36 obj#=402 tim=372490292225
WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 934 FileOperation=8 fileno=1 filetype=36 obj#=402 tim=372490293171
WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 3 FileOperation=8 fileno=1 filetype=36 obj#=402 tim=372490293208
WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 245 FileOperation=8 fileno=1 filetype=36 obj#=402 tim=372490293465
WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 262 FileOperation=8 fileno=1 filetype=36 obj#=402 tim=372490293755
WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 1 FileOperation=8 fileno=1 filetype=36 obj#=402 tim=372490293780
WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 250 FileOperation=8 fileno=1 filetype=36 obj#=402 tim=372490294039
WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 256 FileOperation=8 fileno=1 filetype=36 obj#=402 tim=372490294323
WAIT #0x7f051bd5f870: nam='Disk file operations I/O' ela= 8 FileOperation=5 fileno=0 filetype=36 obj#=402 tim=372490294359
2016-06-15T11:36:00.055196+08:00
Incident 29074 created, dump file: /u01/app/oracle/diag/rdbms/orcl12c2/orcl12c2/incident/incdir_29074/orcl12c2_ora_40547_i29074.trc
ORA-00600: internal error code, arguments: [17090], [], [], [], [], [], [], [], [], [], [], []

从中可以判断出来是由于CDB$ROOT的未增加tempfile导致

SQL> alter tablespace temp add tempfile '/u01/app/oracle/oradata/orcl12c2/temp01.dbf' reuse;

Tablespace altered.

SQL>  alter PLUGGABLE database pdb2 open;

Pluggable database altered.

查看数据库恢复情况

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           READ WRITE NO

SQL> select con_id,file#,checkpoint_change#,resetlogs_change# from v$datafile_header;

    CON_ID      FILE# CHECKPOINT_CHANGE# RESETLOGS_CHANGE#
---------- ---------- ------------------ -----------------
         1          1            2500167           1500164
         1          3            2500167           1500164
         1          4            2500167           1500164
         2          5            1371280           1341067
         2          6            1371280           1341067
         1          7            2500167           1500164
         2          8            1371280           1341067
         3          9            2501017           1500164
         3         10            2501017           1500164
         3         11            2501017           1500164
         3         12            2501017           1500164
         4         13            2502748           1500164
         4         14            2502748           1500164
         4         15            2502748           1500164
         4         16            2502748           1500164

15 rows selected.

至此基本上测试完成在在cdb环境中丢失redo的恢复。在生产中，需要把temp加全，并且建议重建数据库

发表在 ORACLE 12C, Oracle备份恢复 | 标签为 kcbzib_kcrsds_1, ORA-16433, ORA-600 17090, ORA-600 kcbzib_kcrsds_1, redo异常 | 评论关闭

ORA-00354 ORA-00353 ORA-00312异常处理

发表于 2016 年 1 月 24 日由惜分飞

数据库启动报错
WIN平台oracle 9.2.0.6版本数据库redo log block header损坏，ORA-00354 ORA-00353 ORA-00312错误导致数据库无法启动

SQL >alter database open;
*
ERROR at line 1:

ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 1892904 change 281470950178815
ORA-00312: online log 3 thread 1: 'D:\ORACLE\ORADATA\ZOYO\REDO03.LOG'

Sun Jan 24 15:44:05 2016
Database mounted in Exclusive Mode.
Completed: alter database mount exclusive
Sun Jan 24 15:44:05 2016
alter database open
Sun Jan 24 15:44:05 2016
Beginning crash recovery of 1 threads
Sun Jan 24 15:44:05 2016
Started redo scan
ORA-354 signalled during: alter database open...
Shutting down instance: further logons disabled
Shutting down instance (immediate)
License high water mark = 3
Sun Jan 24 15:44:32 2016
ALTER DATABASE CLOSE NORMAL
ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...

通过分析,确定损坏的redo03为当前redo,无法使用正常方法打开,加上_allow_resetlogs_corruption参数,尝试打开库,依旧失败

数据库报ORA-600 2662错误

Sun Jan 24 16:26:30 2016
SMON: enabling cache recovery
Sun Jan 24 16:26:30 2016
Errors in file d:\oracle\admin\zoyo\udump\zoyo_ora_640.trc:
ORA-00600: 内部错误代码，参数: [2662], [0], [31563641], [0], [31563654], [4194721], [], []

Sun Jan 24 16:26:31 2016
Errors in file d:\oracle\admin\zoyo\udump\zoyo_ora_640.trc:
ORA-00704: 引导程序进程失败
ORA-00600: 内部错误代码，参数: [2662], [0], [31563641], [0], [31563654], [4194721], [], []

Sun Jan 24 16:26:31 2016
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Instance terminated by USER, pid = 640
ORA-1092 signalled during: alter database open resetlogs...

ORA 600 2662的错误处理
根据经验,这个错误只需要推scn即可,可以通过bbed,隐含参数,event,oradebug,修改控制文件等方法进行,推scn之后,数据库报熟悉的ORA-00604 ORA-00607 ORA-600 4194错误,以前我们遇到的block大部分是128,这次报异常block为9.实际中跟版本有关系,在ORACLE 9.2.0.6中该错误为file 1 block 9.大部分版本为128

Sun Jan 24 16:29:39 2016
SMON: enabling cache recovery
Sun Jan 24 16:29:39 2016
Errors in file d:\oracle\admin\zoyo\udump\zoyo_ora_3432.trc:
ORA-00600: 内部错误代码，参数: [4194], [14], [5], [], [], [], [], []

Sun Jan 24 16:29:39 2016
Doing block recovery for fno: 1 blk: 401
Sun Jan 24 16:29:39 2016
Recovery of Online Redo Log: Thread 1 Group 1 Seq 2 Reading mem 0
  Mem# 0 errs 0: D:\ORACLE\ORADATA\ZOYO\REDO01.LOG
Doing block recovery for fno: 1 blk: 9
Sun Jan 24 16:29:40 2016
Recovery of Online Redo Log: Thread 1 Group 1 Seq 2 Reading mem 0
  Mem# 0 errs 0: D:\ORACLE\ORADATA\ZOYO\REDO01.LOG
Sun Jan 24 16:29:40 2016
Errors in file d:\oracle\admin\zoyo\udump\zoyo_ora_3432.trc:
ORA-00604: 递归 SQL 层 1 出现错误
ORA-00607: 当更改数据块时出现内部错误
ORA-00600: 内部错误代码，参数: [4194], [14], [5], [], [], [], [], []

Error 604 happened during db open, shutting down database
USER: terminating instance due to error 604
Instance terminated by USER, pid = 3432

ORA-00604 ORA-00607 ORA-600 4194分析trace文件

*** 2016-01-24 16:29:40.031
Recovery of Online Redo Log: Thread 1 Group 1 Seq 2 Reading mem 0
 
Block image after block recovery:
buffer tsn: 0 rdba: 0x00400009 (1/9)
scn: 0x0000.01e112e1 seq: 0x01 flg: 0x04 tail: 0x12e10e01
frmt: 0x02 chkval: 0xba76 type: 0x0e=KTU UNDO HEADER W/UNLIMITED EXTENTS
  Extent Control Header
  -----------------------------------------------------------------
  Extent Header:: spare1: 0      spare2: 0      #extents: 6      #blocks: 47    
                  last map  0x00000000  #maps: 0      offset: 4128  
      Highwater::  0x00400191  ext#: 4      blk#: 0      ext size: 8     
  #blocks in seg. hdr's freelists: 0     
  #blocks below: 0     
  mapblk  0x00000000  offset: 4     
                   Unlocked
     Map Header:: next  0x00000000  #extents: 6    obj#: 0      flag: 0x40000000
  Extent Map
  -----------------------------------------------------------------
   0x0040000a  length: 7     
   0x00400011  length: 8     
   0x00400181  length: 8     
   0x00400189  length: 8     
   0x00400191  length: 8     
   0x00400199  length: 8     
  
  TRN CTL:: seq: 0x008e chd: 0x0060 ctl: 0x0024 inc: 0x00000000 nfb: 0x0001
            mgc: 0x8002 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
            uba: 0x00400191.008e.04 scn: 0x0000.01ded29c
Version: 0x01
  FREE BLOCK POOL::
    uba: 0x00400191.008e.04 ext: 0x4  spc: 0x1c3e  
    uba: 0x00000000.002f.21 ext: 0x5  spc: 0x1334  
    uba: 0x00000000.002e.37 ext: 0x4  spc: 0x788   
    uba: 0x00000000.0000.00 ext: 0x0  spc: 0x0     
    uba: 0x00000000.0000.00 ext: 0x0  spc: 0x0     
  TRN TBL::

从这里可以确定undo segment header中的分配block记录有问题,清除ktuxc.fbp.fbp[N].kuba.kdba相关记录,数据库正常打开

    . struct ktuxc  kernel transaction undo xaction table control with 15 members
    . {
    .   struct kscn   scn with 3 members
    .   {
04148     ub4           bas      = 0X9CD2DE01 = 31380124
04152     ub2           wrp      = 0X0000 = 0
04154     cc32          pad      = 0X0000 = 0
    .   }
    .   struct kuba   uba with 4 members
    .   {
04156     kdba          dba      = 0X91014000 = 0x00400191 file 1 block 401
04160     ub2           seq      = 0X8E00 = 142
04162     ub1           rec      = 0X04 = 4
04163     cc16          pad      = 0X00 = 0
    .   }
04164   sb2           flg      = 0X0100 = 1
04166   ub2           seq      = 0X8E00 = 142
04168   sb2           nfb      = 0X0100 = 1
04170   cc32          pad1     = 0X0000 = 0
04172   ub4           inc      = 0X00000000 = 0
04176   sb2           chd      = 0X6000 = 96
04178   sb2           ctl      = 0X2400 = 36
04180   ub2x          mgc      = 0X0280 = 0x8002
04182   ub2           ver      = 0X0100 = 1
04184   ub2           xts      = 0X6800 = 104
04186   cc32          pad2     = 0X0000 = 0
04188   ub4           opt      = 0XFEFFFF7F = 2147483646
    .   ktufb fbp[5] (array with 5 elements)
    .     struct fbp   [0] with 3 members
    .     {
    .       struct kuba   uba with 4 members
    .       {
04192         kdba          dba      = 0X91014000 = 0x00400191 file 1 block 401
04196         ub2           seq      = 0X8E00 = 142
04198         ub1           rec      = 0X04 = 4
04199         cc16          pad      = 0X00 = 0
    .       }
04200       sb2           ext      = 0X0400 = 4
04202       sb2           spc      = 0X3E1C = 7230
    .     }
    .     struct fbp   [1] with 3 members
    .     {
    .       struct kuba   uba with 4 members
    .       {
04204         kdba          dba      = 0X00000000 = 0x00000000 file 0 block 0
04208         ub2           seq      = 0X2F00 = 47
04210         ub1           rec      = 0X21 = 33
04211         cc16          pad      = 0X00 = 0
    .       }
04212       sb2           ext      = 0X0500 = 5
04214       sb2           spc      = 0X3413 = 4916
    .     }
    .     struct fbp   [2] with 3 members
    .     {
    .       struct kuba   uba with 4 members
    .       {
04216         kdba          dba      = 0X00000000 = 0x00000000 file 0 block 0
04220         ub2           seq      = 0X2E00 = 46
04222         ub1           rec      = 0X37 = 55
04223         cc16          pad      = 0X00 = 0
    .       }
04224       sb2           ext      = 0X0400 = 4
04226       sb2           spc      = 0X8807 = 1928
    .     }
    .     struct fbp   [3] with 3 members
    .     {
    .       struct kuba   uba with 4 members
    .       {
04228         kdba          dba      = 0X00000000 = 0x00000000 file 0 block 0
04232         ub2           seq      = 0X0000 = 0
04234         ub1           rec      = 0X00 = 0
04235         cc16          pad      = 0X00 = 0
    .       }
04236       sb2           ext      = 0X0000 = 0
04238       sb2           spc      = 0X0000 = 0
    .     }
    .     struct fbp   [4] with 3 members
    .     {
    .       struct kuba   uba with 4 members
    .       {
04240         kdba          dba      = 0X00000000 = 0x00000000 file 0 block 0
04244         ub2           seq      = 0X0000 = 0
04246         ub1           rec      = 0X00 = 0
04247         cc16          pad      = 0X00 = 0
    .       }
04248       sb2           ext      = 0X0000 = 0
04250       sb2           spc      = 0X0000 = 0
    .     }
    . }

Sun Jan 24 16:44:52 2016
SMON: enabling tx recovery
Sun Jan 24 16:44:52 2016
Database Characterset is ZHS16GBK
replication_dependency_tracking turned off (no async multimaster replication found)
Completed: ALTER DATABASE OPEN

发表在非常规恢复 | 标签为 ORA-00312, ORA-00353, ORA-00354, ORA-00604, ORA-00607, ORA-600 2662, ORA-600 4194 | 评论关闭

17813235971

QQ 咨询

_allow_resetlogs_corruption 的搜索结果

数据库恢复的敏感性—重建控制文件使用不合适数据文件

Oracle 12c redo 丢失恢复

ORA-00354 ORA-00353 ORA-00312异常处理

文章分类

最近发表

_allow_resetlogs_corruption 的搜索结果

数据库恢复的敏感性—重建控制文件使用不合适数据文件

Oracle 12c redo 丢失恢复

ORA-00354 ORA-00353 ORA-00312异常处理

标签云

文章分类

最近发表