在给一个朋友数据库恢复的过程中语句该库大量删除表空间,然后创建表空,由于在创建控制文件的时候,列出来不正确文件,导致出现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.comSQL> 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.dbfSQL> 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:54SQL> 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:54SQL> drop tablespace xifenfei;Tablespace dropped.SQL> create tablespace xifenfei datafile '/u01/app/oracle/oradata/ORCL/xifenfe<strong style="color:transparent">本文来源gao@daima#com搞(%代@#码@网&</strong>i_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:45SQL> 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 checkORA-01110: data file 5: '/u01/app/oracle/oradata/ORCL/xifenfei_old.dbf'ORA-01203: wrong incarnation of this file - wrong creation SCNSQL> 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:45SQL> 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:54SQL> select file#,error from v$datafile_header; FILE# ERROR---------- ----------------------------------------------------------------- 1 2 3 4 5 WRONG FILE CREATE
搞代码网（gaodaima.com）提供的所有资源部分来自互联网，如果有侵犯您的版权或其他权益，请说明详细缘由并提供版权或权益证明然后发送到邮箱[email protected]，我们会在看到邮件的第一时间内为您处理，或直接联系QQ：872152909。本网站采用BY-NC-SA协议进行授权
转载请注明原文链接：数据文件的三个创建SCN一点点探讨
转载请注明原文链接：数据文件的三个创建SCN一点点探讨
