数据文件的三个创建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
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
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
1671
14
1428
52
1329
25
1276
29
1256
24
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
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
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
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
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
How to create a folder on Realme Phone?
Mar 23, 2024 pm 02:30 PM
Title: Realme Phone Beginner’s Guide: How to Create Folders on Realme Phone? In today's society, mobile phones have become an indispensable tool in people's lives. As a popular smartphone brand, Realme Phone is loved by users for its simple and practical operating system. In the process of using Realme phones, many people may encounter situations where they need to organize files and applications on their phones, and creating folders is an effective way. This article will introduce how to create folders on Realme phones to help users better manage their phone content. No.
Alibaba 7B multi-modal document understanding large model wins new SOTA
Apr 02, 2024 am 11:31 AM
New SOTA for multimodal document understanding capabilities! Alibaba's mPLUG team released the latest open source work mPLUG-DocOwl1.5, which proposed a series of solutions to address the four major challenges of high-resolution image text recognition, general document structure understanding, instruction following, and introduction of external knowledge. Without further ado, let’s look at the effects first. One-click recognition and conversion of charts with complex structures into Markdown format: Charts of different styles are available: More detailed text recognition and positioning can also be easily handled: Detailed explanations of document understanding can also be given: You know, "Document Understanding" is currently An important scenario for the implementation of large language models. There are many products on the market to assist document reading. Some of them mainly use OCR systems for text recognition and cooperate with LLM for text processing.
