Home >Database >Mysql Tutorial >数据文件的三个创建SCN一点点探讨

数据文件的三个创建SCN一点点探讨

WBOYOriginal: 2016-06-07 16:37:521214browse

在给一个朋友数据库恢复的过程中语句该库大量删除表空间,然后创建表空,由于在创建控制文件的时候,列出来不正确文件,导致出现v$datafile_header.error出现WRONG FILE CREATE错误.通过试验重现了该错误,并且进一步测试如果真的需要历史数据文件,该如何狸猫换太

在给一个朋友数据库恢复的过程中语句该库大量删除表空间,然后创建表空,由于在创建控制文件的时候,列出来不正确文件,导致出现v$datafile_header.error出现WRONG FILE CREATE错误.通过试验重现了该错误,并且进一步测试如果真的需要历史数据文件,该如何狸猫换太子(本实验为了进一步理解数据文件创建scn相关信息)
创建xifenfei表空间,然后删除表空间,但不删除数据文件,然后创建重名表空间

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') today,'www.xifenfei.com' xifenfei from dual;
TODAY               XIFENFEI
------------------- ----------------
2014-07-16 15:54:26 www.xifenfei.com
SQL> create tablespace xifenfei datafile '/u01/app/oracle/oradata/ORCL/xifenfei_old.dbf' size 10m;
Tablespace created.
SQL> select file#,name from v$datafile;
     FILE# NAME
---------- --------------------------------------------------
         1 /u01/app/oracle/oradata/ORCL/system01.dbf
         2 /u01/app/oracle/oradata/ORCL/sysaux01.dbf
         3 /u01/app/oracle/oradata/ORCL/undotbs01.dbf
         4 /u01/app/oracle/oradata/ORCL/users01.dbf
         5 /u01/app/oracle/oradata/ORCL/xifenfei_old.dbf
SQL> select file#,CREATION_CHANGE#,to_char(CREATION_TIME,'yyyy-mm-dd hh24:mi:ss') CREATION_TIME from v$datafile;
     FILE# CREATION_CHANGE# CREATION_TIME
---------- ---------------- -------------------
         1               18 2014-07-14 21:53:05
         2             2338 2014-07-14 21:53:42
         3             3130 2014-07-14 21:53:51
         4            15268 2014-07-14 21:54:25
         5           593520 2014-07-16 16:00:54
SQL> select file#,CREATION_CHANGE#,to_char(CREATION_TIME,'yyyy-mm-dd hh24:mi:ss') CREATION_TIME from v$datafile_header;
     FILE# CREATION_CHANGE# CREATION_TIME
---------- ---------------- -------------------
         1               18 2014-07-14 21:53:05
         2             2338 2014-07-14 21:53:42
         3             3130 2014-07-14 21:53:51
         4            15268 2014-07-14 21:54:25
         5           593520 2014-07-16 16:00:54
SQL> drop tablespace xifenfei;
Tablespace dropped.
SQL>  create tablespace xifenfei datafile '/u01/app/oracle/oradata/ORCL/xifenfei_new.dbf' size 10m;
Tablespace created.
SQL> select file#,CREATION_CHANGE#,to_char(CREATION_TIME,'yyyy-mm-dd hh24:mi:ss') CREATION_TIME from v$datafile;
     FILE# CREATION_CHANGE# CREATION_TIME
---------- ---------------- -------------------
         1               18 2014-07-14 21:53:05
         2             2338 2014-07-14 21:53:42
         3             3130 2014-07-14 21:53:51
         4            15268 2014-07-14 21:54:25
         5           593613 2014-07-16 16:02:45
SQL> select file#,CREATION_CHANGE#,to_char(CREATION_TIME,'yyyy-mm-dd hh24:mi:ss') CREATION_TIME from v$datafile_header;
     FILE# CREATION_CHANGE# CREATION_TIME
---------- ---------------- -------------------
         1               18 2014-07-14 21:53:05
         2             2338 2014-07-14 21:53:42
         3             3130 2014-07-14 21:53:51
         4            15268 2014-07-14 21:54:25
         5           593613 2014-07-16 16:02:45

rename xifenfei表空间数据文件到老数据文件

SQL> alter database datafile 5 offline drop;
Database altered.
SQL> alter database rename file '/u01/app/oracle/oradata/ORCL/xifenfei_new.dbf'
 2   to '/u01/app/oracle/oradata/ORCL/xifenfei_old.dbf';     
Database altered.
SQL> alter database datafile 5 online;
alter database datafile 5 online
*
ERROR at line 1:
ORA-01122: database file 5 failed verification check
ORA-01110: data file 5: '/u01/app/oracle/oradata/ORCL/xifenfei_old.dbf'
ORA-01203: wrong incarnation of this file - wrong creation SCN
SQL> select file#,CREATION_CHANGE#,to_char(CREATION_TIME,'yyyy-mm-dd hh24:mi:ss') CREATION_TIME from v$datafile;
     FILE# CREATION_CHANGE# CREATION_TIME
---------- ---------------- -------------------
         1               18 2014-07-14 21:53:05
         2             2338 2014-07-14 21:53:42
         3             3130 2014-07-14 21:53:51
         4            15268 2014-07-14 21:54:25
         5           593613 2014-07-16 16:02:45
SQL> select file#,CREATION_CHANGE#,to_char(CREATION_TIME,'yyyy-mm-dd hh24:mi:ss') CREATION_TIME from v$datafile_header;
     FILE# CREATION_CHANGE# CREATION_TIME
---------- ---------------- -------------------
         1               18 2014-07-14 21:53:05
         2             2338 2014-07-14 21:53:42
         3             3130 2014-07-14 21:53:51
         4            15268 2014-07-14 21:54:25
         5           593520 2014-07-16 16:00:54
SQL> select file#,error from v$datafile_header;
     FILE# ERROR
---------- -----------------------------------------------------------------
         1
         2
         3
         4
         5 WRONG FILE CREATE

至此今天数据库恢复的故障已经模拟出来,就是因为数据文件头的scn和控制文件中scn不一致,从而出现了v$datafile_header.error报WRONG FILE CREATE的现象.

因为控制文件中数据文件scn和数据文件头scn不一致,因此通过重建控制文件来实现两者scn一致

SQL> alter database backup controlfile to trace as '/tmp/ctl';
Database altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP NOMOUNT
ORACLE instance started.
Total System Global Area  718225408 bytes
Fixed Size                  2292432 bytes
Variable Size             373294384 bytes
Database Buffers          339738624 bytes
Redo Buffers                2899968 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  NOARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 '/u01/app/oracle/oradata/ORCL/redo01.log'  SIZE 50M BLOCKSIZE 512,
  9    GROUP 2 '/u01/app/oracle/oradata/ORCL/redo02.log'  SIZE 50M BLOCKSIZE 512,
 10    GROUP 3 '/u01/app/oracle/oradata/ORCL/redo03.log'  SIZE 50M BLOCKSIZE 512
 11  DATAFILE
 12    '/u01/app/oracle/oradata/ORCL/system01.dbf',
 13    '/u01/app/oracle/oradata/ORCL/sysaux01.dbf',
 14    '/u01/app/oracle/oradata/ORCL/undotbs01.dbf',
 15    '/u01/app/oracle/oradata/ORCL/users01.dbf',
 16    '/u01/app/oracle/oradata/ORCL/xifenfei_old.dbf'
 17  CHARACTER SET ZHS16GBK
 18  ;
Control file created.
SQL> select file#,CREATION_CHANGE#,to_char(CREATION_TIME,'yyyy-mm-dd hh24:mi:ss') CREATION_TIME from v$datafile_header;
     FILE# CREATION_CHANGE# CREATION_TIME
---------- ---------------- -------------------
         1               18 2014-07-14 21:53:05
         2             2338 2014-07-14 21:53:42
         3             3130 2014-07-14 21:53:51
         4            15268 2014-07-14 21:54:25
         5           593520 2014-07-16 16:00:54
SQL> select file#,CREATION_CHANGE#,to_char(CREATION_TIME,'yyyy-mm-dd hh24:mi:ss') CREATION_TIME from v$datafile;
     FILE# CREATION_CHANGE# CREATION_TIME
---------- ---------------- -------------------
         1               18 2014-07-14 21:53:05
         2             2338 2014-07-14 21:53:42
         3             3130 2014-07-14 21:53:51
         4            15268 2014-07-14 21:54:25
         5           593520 2014-07-16 16:00:54
SQL> select file#,error from v$datafile_header;
     FILE# ERROR
---------- -----------------------------------------------------------------
         1
         2
         3
         4
         5

通过重建控制文件消除了v$datafile_header.error报WRONG FILE CREATE错误,继续尝试online文件

SQL> recover datafile 5;
Media recovery complete.
SQL> alter database datafile 5 online;
Database altered.
SQL> select file#,name from v$datafile;
     FILE# NAME
---------- --------------------------------------------------
         1 /u01/app/oracle/oradata/ORCL/system01.dbf
         2 /u01/app/oracle/oradata/ORCL/sysaux01.dbf
         3 /u01/app/oracle/oradata/ORCL/undotbs01.dbf
         4 /u01/app/oracle/oradata/ORCL/users01.dbf
         5 /u01/app/oracle/oradata/ORCL/xifenfei_old.dbf
SQL> alter database open;
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-01177: data file does not match dictionary - probably old incarnation
ORA-01110: data file 5: '/u01/app/oracle/oradata/ORCL/xifenfei_old.dbf'
Process ID: 7437
Session ID: 7 Serial number: 5

出现这个错误,是由于数据库中,还有file$中也记录了数据文件创建scn,而这个scn现在和数据文件头和控制文件中的scn不相等,因此无法启动数据库成功.现在需要做的就是在数据库未启动状态下修改file$中的数据文件创建scn相关值,让其和数据文件头(控制文件中记录)一致

使用第三方工具定位file$记录

1|2|89600|0|1|4194302|1280|0|18||4194306|0x004000e9|0
2|2|70400|1|2|4194302|1280|0|2338||8388610|0x004000e9|1
3|2|25600|2|3|4194302|640|0|3130||12582914|0x004000e9|2
4|2|640|4|4|4194302|160|0|15268||16777218|0x004000e9|3
5|2|1280|7|5|0|0|0|593613||20971522|0x004000e9|4   
6|1|3840|||0|0|0|586295||25165826|0x004000e9|5
7|1|3840|||3932160|1280|0|587030||29360130|0x004000e9|6
对应file$结构确定每列含义,以及确定需要修改的列
每行倒数第二列为rdba地址,可以通过转换为file and block,这里对应的就是file 1 block 233
每行最后一列为该条记录在该rdba中的记录顺序

使用工具修改593613为593520,使得file$中的scn与现在控制文件和数据文件头一致,具体参考bbed修改数据内容

修改好file$中数据文件创建scn后,尝试继续操作

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: '/u01/app/oracle/oradata/ORCL/xifenfei_old.dbf'
SQL> recover datafile 5;
Media recovery complete.
SQL> alter database open;
Database altered.
SQL> select file#,name from v$datafile;
     FILE# NAME
---------- --------------------------------------------------
         1 /u01/app/oracle/oradata/ORCL/system01.dbf
         2 /u01/app/oracle/oradata/ORCL/sysaux01.dbf
         3 /u01/app/oracle/oradata/ORCL/undotbs01.dbf
         4 /u01/app/oracle/oradata/ORCL/users01.dbf
         5 /u01/app/oracle/oradata/ORCL/xifenfei_old.dbf

通过这里的简单测试,发现几个问题
1.v$datafile_header.error报WRONG FILE CREATE错误不一定就是数据文件异常,而其本质是数据文件头scn和控制文件中scn不一致
2.数据文件online需要file$,v$datafile_header,v$datafile中关于数据文件创建scn都一致
3.通过该分析,证明在一些极端情况下,考虑考虑该替换思路实现删除数据文件重新加入数据库

记录一次ORA-600 3004 恢复过程和处理思路
误drop tablespace后使用flashback database闪回异常处理
分享一次ORA-01113 ORA-01110故障处理过程
ORA-00600[kcrf_resilver_log_1]异常恢复
记录一次ORA-00316 ORA-00312 redo异常恢复
数据文件的CREATION_TIME来源和算法
Oracle安全警示录:加错裸设备导致redo异常
ORACLE 12C 控制文件异常恢复

原文地址：数据文件的三个创建SCN一点点探讨, 感谢原作者分享。

Statement：

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Previous article：关于包含函数表达式的复合索引优化查询Next article：cursor_sharing=similar参数引起version_count high|libra

See more

数据文件的三个创建SCN一点点探讨

Related articles