数据文件的三个创建SCN一点点探讨
在给一个朋友数据库恢复的过程中语句该库大量删除表空间,然后创建表空,由于在创建控制文件的时候,列出来不正确文件,导致出现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一点点探讨, 感谢原作者分享。
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
AI Hentai Generator
Generate AI Hentai for free.
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
Use ddrescue to recover data on Linux
Mar 20, 2024 pm 01:37 PM
DDREASE is a tool for recovering data from file or block devices such as hard drives, SSDs, RAM disks, CDs, DVDs and USB storage devices. It copies data from one block device to another, leaving corrupted data blocks behind and moving only good data blocks. ddreasue is a powerful recovery tool that is fully automated as it does not require any interference during recovery operations. Additionally, thanks to the ddasue map file, it can be stopped and resumed at any time. Other key features of DDREASE are as follows: It does not overwrite recovered data but fills the gaps in case of iterative recovery. However, it can be truncated if the tool is instructed to do so explicitly. Recover data from multiple files or blocks to a single
Open source! Beyond ZoeDepth! DepthFM: Fast and accurate monocular depth estimation!
Apr 03, 2024 pm 12:04 PM
0.What does this article do? We propose DepthFM: a versatile and fast state-of-the-art generative monocular depth estimation model. In addition to traditional depth estimation tasks, DepthFM also demonstrates state-of-the-art capabilities in downstream tasks such as depth inpainting. DepthFM is efficient and can synthesize depth maps within a few inference steps. Let’s read about this work together ~ 1. Paper information title: DepthFM: FastMonocularDepthEstimationwithFlowMatching Author: MingGui, JohannesS.Fischer, UlrichPrestel, PingchuanMa, Dmytr
What to do if the 0x80004005 error code appears. The editor will teach you how to solve the 0x80004005 error code.
Mar 21, 2024 pm 09:17 PM
When deleting or decompressing a folder on your computer, sometimes a prompt dialog box "Error 0x80004005: Unspecified Error" will pop up. How should you solve this situation? There are actually many reasons why the error code 0x80004005 is prompted, but most of them are caused by viruses. We can re-register the dll to solve the problem. Below, the editor will explain to you the experience of handling the 0x80004005 error code. Some users are prompted with error code 0X80004005 when using their computers. The 0x80004005 error is mainly caused by the computer not correctly registering certain dynamic link library files, or by a firewall that does not allow HTTPS connections between the computer and the Internet. So how about
Google is ecstatic: JAX performance surpasses Pytorch and TensorFlow! It may become the fastest choice for GPU inference training
Apr 01, 2024 pm 07:46 PM
The performance of JAX, promoted by Google, has surpassed that of Pytorch and TensorFlow in recent benchmark tests, ranking first in 7 indicators. And the test was not done on the TPU with the best JAX performance. Although among developers, Pytorch is still more popular than Tensorflow. But in the future, perhaps more large models will be trained and run based on the JAX platform. Models Recently, the Keras team benchmarked three backends (TensorFlow, JAX, PyTorch) with the native PyTorch implementation and Keras2 with TensorFlow. First, they select a set of mainstream
The vitality of super intelligence awakens! But with the arrival of self-updating AI, mothers no longer have to worry about data bottlenecks
Apr 29, 2024 pm 06:55 PM
I cry to death. The world is madly building big models. The data on the Internet is not enough. It is not enough at all. The training model looks like "The Hunger Games", and AI researchers around the world are worrying about how to feed these data voracious eaters. This problem is particularly prominent in multi-modal tasks. At a time when nothing could be done, a start-up team from the Department of Renmin University of China used its own new model to become the first in China to make "model-generated data feed itself" a reality. Moreover, it is a two-pronged approach on the understanding side and the generation side. Both sides can generate high-quality, multi-modal new data and provide data feedback to the model itself. What is a model? Awaker 1.0, a large multi-modal model that just appeared on the Zhongguancun Forum. Who is the team? Sophon engine. Founded by Gao Yizhao, a doctoral student at Renmin University’s Hillhouse School of Artificial Intelligence.
Slow Cellular Data Internet Speeds on iPhone: Fixes
May 03, 2024 pm 09:01 PM
Facing lag, slow mobile data connection on iPhone? Typically, the strength of cellular internet on your phone depends on several factors such as region, cellular network type, roaming type, etc. There are some things you can do to get a faster, more reliable cellular Internet connection. Fix 1 – Force Restart iPhone Sometimes, force restarting your device just resets a lot of things, including the cellular connection. Step 1 – Just press the volume up key once and release. Next, press the Volume Down key and release it again. Step 2 – The next part of the process is to hold the button on the right side. Let the iPhone finish restarting. Enable cellular data and check network speed. Check again Fix 2 – Change data mode While 5G offers better network speeds, it works better when the signal is weaker
The U.S. Air Force showcases its first AI fighter jet with high profile! The minister personally conducted the test drive without interfering during the whole process, and 100,000 lines of code were tested for 21 times.
May 07, 2024 pm 05:00 PM
Recently, the military circle has been overwhelmed by the news: US military fighter jets can now complete fully automatic air combat using AI. Yes, just recently, the US military’s AI fighter jet was made public for the first time and the mystery was unveiled. The full name of this fighter is the Variable Stability Simulator Test Aircraft (VISTA). It was personally flown by the Secretary of the US Air Force to simulate a one-on-one air battle. On May 2, U.S. Air Force Secretary Frank Kendall took off in an X-62AVISTA at Edwards Air Force Base. Note that during the one-hour flight, all flight actions were completed autonomously by AI! Kendall said - "For the past few decades, we have been thinking about the unlimited potential of autonomous air-to-air combat, but it has always seemed out of reach." However now,
Tesla robots work in factories, Musk: The degree of freedom of hands will reach 22 this year!
May 06, 2024 pm 04:13 PM
The latest video of Tesla's robot Optimus is released, and it can already work in the factory. At normal speed, it sorts batteries (Tesla's 4680 batteries) like this: The official also released what it looks like at 20x speed - on a small "workstation", picking and picking and picking: This time it is released One of the highlights of the video is that Optimus completes this work in the factory, completely autonomously, without human intervention throughout the process. And from the perspective of Optimus, it can also pick up and place the crooked battery, focusing on automatic error correction: Regarding Optimus's hand, NVIDIA scientist Jim Fan gave a high evaluation: Optimus's hand is the world's five-fingered robot. One of the most dexterous. Its hands are not only tactile
