Oracle Controlfile Corrupted ORA-27048: skgfifi: file header information is invalid

首页 » ORACLE 9i-23ai » Oracle Controlfile Corrupted ORA-27048: skgfifi: file header information is invalid

Oracle Controlfile Corrupted ORA-27048: skgfifi: file header information is invalid

2024/07/16
ORACLE 9i-23ai
136 views
Oracle Controlfile Corrupted ORA-27048: skgfifi: file header information is invalid已关闭评论

今天一个客户的文件系统空间耗尽，数据库重启后，数据库无法启动，mount后提示ORA-00205: error in identifying control file, check alert log for more info, 查看DB ALERT LOG提示ORA-27048: skgfifi: file header information is invalid，因为控制文件头损坏。感觉好久没有研究恢复了，简单记录.

启动报错

SQL> startup 
ORACLE instance started.

Total System Global Area  849530880 bytes
Fixed Size                  1339824 bytes
Variable Size             528485968 bytes
Database Buffers          314572800 bytes
Redo Buffers                5132288 bytes
ORA-00205: error in identifying control file, check alert log for more info

查看DB ALERT LOG

ALTER DATABASE MOUNT
ORA-00210: cannot open the specified control file
ORA-00202: control file: '<path and name>'
ORA-27048: skgfifi: file header information is invalid

常见原因

Header of Oracle Blocks got corrupted due to OS / Hardware configuration change

ORA-27048 error implies there is Oracle Block got corrupted or NOT in readable format

恢复方法

因为当前的控制文件仅头损坏，可以使用strings读取损坏的控制文件，拼接创建控制文件的SQL.

oracle@11g-node2:/tmp $strings ctl.1|more
}|{z
RAC11G
fhE;N
fhERAC11G
fhEi
fhERAC11G
fhEi
rac11g1
TghE
rac11g2
rac11g1
TghE
rac11g2
@khE
@khE
+DATA/rac11g/onlinelog/group_1.262.1164470465
+DATA/rac11g/onlinelog/group_2.261.1164470467
+DATA/rac11g/onlinelog/group_11.274.1164470467
+DATA/rac11g/onlinelog/group_12.275.1164470469
+DATA/rac11g/datafile/users.259.1164469933
+DATA/rac11g/datafile/undotbs1.258.1164469933
+DATA/rac11g/datafile/sysaux.257.1164469933
+DATA/rac11g/datafile/system.256.1164469933
+DATA/rac11g/tempfile/temp.263.1164470017
+DATA/rac11g/datafile/undotbs2.264.1164470041
+DATA/rac11g/datafile/ts_ogg.266.1164471015

...

编辑重建脚本,样式如下

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "PRIMARY" RESETLOGS  ARCHIVELOG
--  SET STANDBY TO MAXIMIZE PERFORMANCE
    MAXLOGFILES 5
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 1
    MAXLOGHISTORY 226
LOGFILE
  GROUP 1 '/opt/oracle/oradata/primary/redo01.log'  SIZE 10M,
  GROUP 2 '/opt/oracle/oradata/primary/redo02.log'  SIZE 10M,
  GROUP 3 '/opt/oracle/oradata/primary/redo03.log'  SIZE 10M
-- STANDBY LOGFILE
DATAFILE
  '/opt/oracle/oradata/primary/system01.dbf',
  '/opt/oracle/oradata/primary/undotbs01.dbf',
  '/opt/oracle/oradata/primary/users01.dbf'
CHARACTER SET ZHS16GBK
;
SQL> recover database using backup controlfile until cancel;

使用resetlogs打开数据库：
SQL> alter database open resetlogs;

控制文件中的字符集从 DB ALERT LOG中可以找到

SMON: enabling tx recovery
Database Characterset is AL32UTF8
2024-05-30 14:04:07.555000 +08:00

控制文件中的字符集

SQL> col DESCRIPTION for a30
SQL> col PROPERTY_VALUE for a50
SQL> select * from database_properties where PROPERTY_NAME like '%CHAR%';

PROPERTY_NAME                                                PROPERTY_VALUE                                     DESCRIPTION
------------------------------------------------------------ -------------------------------------------------- ------------------------------
NLS_NUMERIC_CHARACTERS                                       .,                                                 Numeric characters
NLS_CHARACTERSET                                             AL32UTF8                                           Character set
NLS_NCHAR_CONV_EXCP                                          FALSE                                              NLS conversion exception
NLS_NCHAR_CHARACTERSET                                       AL16UTF16                                          NCHAR Character set

SQL> select NLS_CHARSET_ID('AL32UTF8') from dual;

NLS_CHARSET_ID('AL32UTF8')
--------------------------
                       873
SQL>  @dec 873

                                DEC                  HEX
----------------------------------- --------------------
                         873.000000                  369

dump 控制文件没有找到字符集

Oracle Control file:dump
Use the following command to dumpe controlfile

alter session set events ‘immediate trace name controlf level ‘;
Regarding the level:

level : level 1: Generic File Header
Level 2: Level 1 + database information + database entry + check point progress records + Extended database entry
level 3 or Higher< 9: level 2 + reuse record section
level 10: Memory dump of all the control file logical blocks

SQL> alter session set events 'immediate trace name controlf level 1';
Session altered.

SQL> oradebug setmypid
 
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/rac11g/rac11g2/trace/rac11g2_ora_20489.trc
SQL> host vi /u01/app/oracle/diag/rdbms/rac11g/rac11g2/trace/rac11g2_ora_20489.trc



DUMP OF CONTROL FILES, Seq # 208669 = 0x32f1d
 V10 STYLE FILE HEADER:
        Compatibility Vsn = 186647552=0xb200400   ------------- the database version is 11.2.0.4
        Db ID=2216717757=0x84206dbd, Db Name='RAC11G'
        Activation ID=0=0x0
        Control Seq=208669=0x32f1d, File size=1382=0x566
        File Number=0, Blksiz=16384, File Type=1 CONTROL
*** END OF DUMP ***


DUMP OF CONTROL FILES, Seq # 1170461872 = 0x45c3d4b0
 V10 STYLE FILE HEADER:
        Compatibility Vsn = 318767104=0x13000000   ------------- the database version is 19.0.0.0
        Db ID=1198917080=0x477605d8, Db Name='ORA19C'
        Activation ID=0=0x0
        Control Seq=1170461872=0x45c3d4b0, File size=1650=0x672
        File Number=0, Blksiz=16384, File Type=4 BACKUP CONTROL
*** END OF DUMP ***


alter session set events 'immediate trace name controlf level 2';


***************************************************************************
DATABASE ENTRY
***************************************************************************
 (size = 316, compat size = 316, section max = 1, section in-use = 1,
  last-recid= 0, old-recno = 0, last-recno = 0)
 (extent = 1, blkno = 1, numrecs = 1)
 11/16/2023 16:17:03
 DB Name "ORCL"
 Database flags = 0x00404000 0x00001000 0x00000082
 Controlfile Creation Timestamp  11/16/2023 16:17:03
 Incmplt recovery scn: 0x0000000000000000
 Resetlogs scn: 0x00000000001d4fd1 Resetlogs Timestamp  11/16/2023 16:17:05
 Prior resetlogs scn: 0x0000000000000001 Prior resetlogs Timestamp  04/17/2019 00:55:59
 Redo Version: compatible=0x13000000
 #Data files = 13, #Online files = 10
 Database checkpoint: Thread=1 scn: 0x00000000018def56
 Threads: #Enabled=1, #Open=1, Head=1, Tail=1
 enabled  threads:  01000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000
 Max log members = 3, Max data members = 1
 Arch list: Head=0, Tail=0, Force scn: 0x00000000018c3480scn: 0x0000000000000000
 Activation ID: 1680832767
 SCN compatibility 3
 Auto-rollover enabled
 Controlfile Checkpointed at scn:  0x00000000018e03c1 07/17/2024 03:17:20
 thread:0 rba:(0x0.0.0)
 enabled  threads:  00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000 00000000 00000000 00000000 00000000

***************************************************************************
CHECKPOINT PROGRESS RECORDS
***************************************************************************
 (size = 8180, compat size = 8180, section max = 11, section in-use = 0,
  last-recid= 0, old-recno = 0, last-recno = 0)
 (extent = 1, blkno = 2, numrecs = 11)
THREAD #1 - status:0x2 flags:0x0 dirty:30
low cache rba:(0x188.af50.0) on disk rba:(0x188.af7d.0)
on disk scn: 0x00000000018e03d7 07/17/2024 07:14:27
resetlogs scn: 0x00000000001d4fd1 11/16/2023 16:17:05
heartbeat: 1160102899 mount id: 1680849790
THREAD #2 - status:0x0 flags:0x0 dirty:0
low cache rba:(0x0.0.0) on disk rba:(0x0.0.0)
on disk scn: 0x0000000000000000 01/01/1988 00:00:00
resetlogs scn: 0x0000000000000000 01/01/1988 00:00:00
heartbeat: 0 mount id: 0
THREAD #3 - status:0x0 flags:0x0 dirty:0
low cache rba:(0x0.0.0) on disk rba:(0x0.0.0)
on disk scn: 0x0000000000000000 01/01/1988 00:00:00
resetlogs scn: 0x0000000000000000 01/01/1988 00:00:00
heartbeat: 0 mount id: 0
THREAD #4 - status:0x0 flags:0x0 dirty:0
low cache rba:(0x0.0.0) on disk rba:(0x0.0.0)
on disk scn: 0x0000000000000000 01/01/1988 00:00:00
resetlogs scn: 0x0000000000000000 01/01/1988 00:00:00
heartbeat: 0 mount id: 0
THREAD #5 - status:0x0 flags:0x0 dirty:0
low cache rba:(0x0.0.0) on disk rba:(0x0.0.0)
on disk scn: 0x0000000000000000 01/01/1988 00:00:00
resetlogs scn: 0x0000000000000000 01/01/1988 00:00:00
heartbeat: 0 mount id: 0
THREAD #6 - status:0x0 flags:0x0 dirty:0
low cache rba:(0x0.0.0) on disk rba:(0x0.0.0)
on disk scn: 0x0000000000000000 01/01/1988 00:00:00
resetlogs scn: 0x0000000000000000 01/01/1988 00:00:00
heartbeat: 0 mount id: 0
THREAD #7 - status:0x0 flags:0x0 dirty:0
low cache rba:(0x0.0.0) on disk rba:(0x0.0.0)
on disk scn: 0x0000000000000000 01/01/1988 00:00:00
resetlogs scn: 0x0000000000000000 01/01/1988 00:00:00
heartbeat: 0 mount id: 0
THREAD #8 - status:0x0 flags:0x0 dirty:0
low cache rba:(0x0.0.0) on disk rba:(0x0.0.0)
on disk scn: 0x0000000000000000 01/01/1988 00:00:00
resetlogs scn: 0x0000000000000000 01/01/1988 00:00:00
heartbeat: 0 mount id: 0




***************************************************************************
EXTENDED DATABASE ENTRY
***************************************************************************
 (size = 900, compat size = 900, section max = 1, section in-use = 1,
  last-recid= 0, old-recno = 0, last-recno = 0)
 (extent = 1, blkno = 390, numrecs = 1)
Control AutoBackup date(dd/mm/yyyy)=16/11/2023
Next AutoBackup sequence= 0
Database recovery target inc#:2, Last open inc#:2
flg:0x0, flag:0x2000
Change tracking state=0, file index=0, checkpoint count=0scn: 0x0000000000000000
Flashback log count=0, block count=0
Desired flashback log size=0 blocks
Oldest guarantee restore point=0
Highest thread enable/disable scn: 0x0000000000000000
Number of Open thread with finite next SCN in last log: 0
Number of half-enabled redo threads: 0
Sum of absolute file numbers for files currently being moved online: 0
Minimum flashback scn: 0x0000000000000000
Cross-endian dictionary SCN scn: 0x0000000000000000
Old recovery timestamp:  01/01/1988 00:00:00
New recovery timestamp:  01/01/1988 00:00:00
Head filename record number for pre-created datafiles=0
Tail filename record number for pre-created datafiles=0
*** END OF DUMP ***

*** 2024-07-16T19:14:36.392172-04:00 (CDB$ROOT

应该16进制记录的字符集编码.

oracle@11g-node2:/tmp $hexdump ctl.1 |grep -n  0369
356:00a4120 0044 0000 000a 000a 66fd 4568 0369 0001
461:00a8120 0044 0000 000a 000a 66fd 4568 0369 0001
18347:0da4000 c200 0000 0369 0000 0000 0000 0000 0501
28450:11f7f80 0000 0000 38bc 456c 0002 0000 0369 0000
29474:11fbf80 0000 0000 38bc 456c 0002 0000 0369 0000
31379:1203690 0239 0000 9232 00ba 0000 0000 5b72 456d
34534:120fbc0 0369 0000 129a 011c 0000 0000 240f 4570
35558:1213bc0 0369 0000 129a 011c 0000 0000 240f 4570
35640:12140e0 0369 0000 f20a 011f 0000 0000 4024 4570
...

其它恢复控制文件的方法，发现十几年前整理过一篇《rman之丢失controlfile 控制文件恢复》，很潦草的笔记。

打赏

ORA-27048

对不起，这篇文章暂时关闭评论。

上一篇： Alert: 不要升级到 MySQL 8.0.38( 8.4.1, 9.0.0) 任何版本?

下一篇： Oracle Incremental Statistics 忽略ESTIMATE_PERCENT ?

ANBOB™

Oracle Controlfile Corrupted ORA-27048: skgfifi: file header information is invalid

对不起，这篇文章暂时关闭评论。

最新文章

标签云集

文章索引

MySql Link

ORACLE Link

Others Link

国内好友

管理功能

微信公众号/Wechat