误删除数据文件、控制文件的非RMAN恢复方法
参考文档：http://www.xifenfei.com/2289.html http://www.askmaclean.com/archives/unix-linux%E4%B8%8B%E5%88%A0%E9%99%A4oracle%E6%8E%A7%E5%88%B6%E6%96%87%E4%BB%B6controlfile%E4%B8%BA%E4%BB%80%E4%B9%88%E5%AE%9E%E4%BE%8Binstance%E6%B2%A1%E6%9C%89
参考文档：http://www.xifenfei.com/2289.htmlhttp://www.askmaclean.com/archives/unix-linux%E4%B8%8B%E5%88%A0%E9%99%A4oracle%E6%8E%A7%E5%88%B6%E6%96%87%E4%BB%B6controlfile%E4%B8%BA%E4%BB%80%E4%B9%88%E5%AE%9E%E4%BE%8Binstance%E6%B2%A1%E6%9C%89%E7%AB%8B%E5%8D%B3%E5%A5%94%E6%BA%83.html
在Unix/Linux上删除所有的Controlfile控制文件后实例并不会在第一时间crash，相反居然还可以顺利完成FULL checkpoint 。
这是为什么呢？ ---注意这个问题仅适用于Unix/Linux， 在Windows平台上不允许删除 已经被其他进程打开的文件。
究其根本原因是在Linux/Unix上Read 或 Write一个文件，进程都会打开一个与此文件相关联的 打开文件描述符 Open File Descriptors (a file descriptor (FD) is an abstract indicator for accessing a file. The term is generally used in POSIX operating systems.In POSIX, a file descriptor is an integer, specifically of the C type int.)
而在Linux/Unix上当一个文件被删除时，它只是简单地被”unlink”即删除一个硬链接hard link； 实际包含文件数据的inode 在所有与该文件关联的 打开文件描述符(Open File Descriptors)被关闭之前 都不会被实际删除，已经获得 打开文件描述符的进程 只要不用close函数关闭掉这些描述符， 都可以继续正常地对文件进行读写。 当所有该文件管理的打开文件符都被关闭时 文件才真正意义上被删除。
注意：--SYSTEM不能在线RECOVER，需要重启数据库。
UNDO可以在线RECOVER，不过UNDO 文件OFFLINE了不能做操作了--不能DML，查询可以--不涉及回滚段中数据的可以。
其它数据文件可以在线恢复。
使用的命令主要是：
ps -ef |grep dbw |grep -v grep
ps -ef |grep ckpt |grep -v grep
ll /proc/10986/fd
cp /proc/10986/fd/258 /u01/oradata/bys3/system01.dbf
alter database datafile 6 offline;
recover datafile 6;
alter database datafile 6 online;
1.删除SYSTEM文件的恢复实验：
会话1：SYS@ bys3>select * from v$dbfile;
FILE# NAME
---------- ----------------------------------------
1 /u01/oradata/bys3/system01.dbf
2 /u01/oradata/bys3/sysaux01.dbf
3 /u01/oradata/bys3/undotbs01.dbf
4 /u01/oradata/bys3/user01.dbf
5 /u01/oradata/bys3/test1.dbf
6 /u01/oradata/bys3/test2.dbf
7 /u01/oradata/bys3/test22.dbf
8 /u01/oradata/bys3/test3.dbf
SYS@ bys3>select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
#############
新打开一个会话2：
查看DBWR的进程号，并删除/u01/oradata/bys3/system01.dbf
[oracle@bys3 bys3]$ ps -ef |grep dbw |grep -v grep 查出进程PID---10986，注意这里查的是DBWR进程，如是控制文件，则查CKPT进程。
oracle 10986 1 0 Mar01 ? 00:04:10 ora_dbw0_bys3
[oracle@bys3 bys3]$ pwd
/u01/oradata/bys3
[oracle@bys3 bys3]$ ls
control01.ctl redo02.log system01.dbf test22.dbf undotbs01.dbf
control02.ctl redo03.log temp01.dbf test2.dbf user01.dbf
redo01.log sysaux01.dbf test1.dbf test3.dbf
[oracle@bys3 bys3]$ rm -rf system01.dbf
#########################
回到会话1：
SYS@ bys3>conn bys/bys
ERROR:
ORA-00604: error occurred at recursive SQL level 2
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/oradata/bys3/system01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00604: error occurred at recursive SQL level 2
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/oradata/bys3/system01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
Warning: You are no longer connected to ORACLE.
@ >conn / as sysdba
Connected.
SYS@ bys3>select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
##########################################
会话2：
[oracle@bys3 ~]$ ll /proc/10986/fd
total 0
lr-x------ 1 oracle oinstall 64 Mar 7 11:23 0 -> /dev/null
l-wx------ 1 oracle oinstall 64 Mar 7 11:23 1 -> /dev/null
lrwx------ 1 oracle oinstall 64 Mar 7 11:23 10 -> /u01/app/oracle/product/11.2.0/dbhome_1/dbs/lkBYS3
lr-x------ 1 oracle oinstall 64 Mar 7 11:23 11 -> /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/mesg/oraus.msb
l-wx------ 1 oracle oinstall 64 Mar 7 11:23 2 -> /dev/null
lrwx------ 1 oracle oinstall 64 Mar 7 11:23 256 -> /u01/oradata/bys3/control01.ctl
lrwx------ 1 oracle oinstall 64 Mar 7 11:23 257 -> /u01/oradata/bys3/control02.ctl
lrwx------ 1 oracle oinstall 64 Mar 7 11:23 258 -> /u01/oradata/bys3/system01.dbf (deleted) ---这里会一直闪
lrwx------ 1 oracle oinstall 64 Mar 7 11:23 259 -> /u01/oradata/bys3/sysaux01.dbf
lrwx------ 1 oracle oinstall 64 Mar 7 11:23 260 -> /u01/oradata/bys3/undotbs01.dbf
lrwx------ 1 oracle oinstall 64 Mar 7 11:23 261 -> /u01/oradata/bys3/user01.dbf
lrwx------ 1 oracle oinstall 64 Mar 7 11:23 262 -> /u01/oradata/bys3/test1.dbf
lrwx------ 1 oracle oinstall 64 Mar 7 11:23 263 -> /u01/oradata/bys3/test2.dbf
lrwx------ 1 oracle oinstall 64 Mar 7 11:23 264 -> /u01/oradata/bys3/test22.dbf
lrwx------ 1 oracle oinstall 64 Mar 7 11:23 265 -> /u01/oradata/bys3/test3.dbf
lrwx------ 1 oracle oinstall 64 Mar 7 11:23 266 -> /u01/oradata/bys3/temp01.dbf
lr-x------ 1 oracle oinstall 64 Mar 7 11:23 3 -> /dev/null
lr-x------ 1 oracle oinstall 64 Mar 7 11:23 4 -> /dev/null
lr-x------ 1 oracle oinstall 64 Mar 7 11:23 5 -> /dev/null
lr-x------ 1 oracle oinstall 64 Mar 7 11:23 6 -> /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/mesg/oraus.msb
lr-x------ 1 oracle oinstall 64 Mar 7 11:23 7 -> /proc/10986/fd
lr-x------ 1 oracle oinstall 64 Mar 7 11:23 8 -> /dev/zero
lrwx------ 1 oracle oinstall 64 Mar 7 11:23 9 -> /u01/app/oracle/product/11.2.0/dbhome_1/dbs/hc_bys3.dat
[oracle@bys3 ~]$ cp /proc/10986/fd/258 /u01/oradata/bys3/system01.dbf
[oracle@bys3 ~]$ ll /u01/oradata/bys3/system01.dbf
-rw-r----- 1 oracle oinstall 524296192 Mar 7 11:25 /u01/oradata/bys3/system01.dbf
此时，ll /proc/10986/fd还是会显示文件状态是：(deleted)，如果是非SYSTEM文件，在线RECOVER后这里会变正常。
####################
SYS@ bys3>conn bys/bys
Connected.
BYS@ bys3>select TABLESPACE_NAME,STATUS,ONLINE_STATUS from dba_data_files;
TABLESPACE_NAME STATUS ONLINE_
------------------------------ --------- -------
SYSTEM AVAILABLE SYSTEM
SYSAUX AVAILABLE ONLINE
UNDOTBS1 AVAILABLE ONLINE
USERS AVAILABLE ONLINE
TEST1 AVAILABLE ONLINE
TEST1 AVAILABLE ONLINE
TEST2 AVAILABLE ONLINE
TEST3 AVAILABLE ONLINE
ALERT日志中的警告信息：
Fri Mar 07 11:17:39 2014
Errors in file /u01/diag/rdbms/bys3/bys3/trace/bys3_m000_14498.trc:
ORA-00604: error occurred at recursive SQL level 2
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/oradata/bys3/system01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
Checker run found 1 new persistent data failures
Fri Mar 07 11:18:41 2014
Errors in file /u01/diag/rdbms/bys3/bys3/trace/bys3_m000_14516.trc:
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/oradata/bys3/system01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
Fri Mar 07 11:18:42 2014
Errors in file /u01/diag/rdbms/bys3/bys3/trace/bys3_m001_14520.trc:
ORA-00604: error occurred at recursive SQL level 2
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/oradata/bys3/system01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
############
此时，应该关闭数据库，打开到MOUNT，恢复SYSTEM文件，再打开数据库，就可以正常使用了。
SYS@ bys3>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@ bys3>startup
ORACLE instance started.
Total System Global Area 225996800 bytes
Fixed Size 1363692 bytes
Variable Size 150995220 bytes
Database Buffers 67108864 bytes
Redo Buffers 6529024 bytes
Database mounted.
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u01/oradata/bys3/system01.dbf'
SYS@ bys3>recover datafile 1;
Media recovery complete.
SYS@ bys3>alter database open;
Database altered.
#############
2.如果丢失的是非SYSTEM/UNDO，则可以在线恢复：方法如下：
BYS@ bys3>alter database datafile 6 offline;Database altered.
BYS@ bys3>recover datafile 6;
Media recovery complete.
BYS@ bys3>alter database datafile 6 online;
Database altered.
################
3.UNDO的OFFLINE及恢复和上面一样：
SYS@ bys3>alter database datafile 3 offline;alter database datafile 3 offline
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '/u01/oradata/bys3/undotbs01.dbf'
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '/u01/oradata/bys3/undotbs01.dbf'
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '/u01/oradata/bys3/undotbs01.dbf'
Process ID: 15106
Session ID: 1 Serial number: 5
BYS@ bys3>update dept set deptno=22;
update dept set deptno=22
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '/u01/oradata/bys3/undotbs01.dbf'
#########
4.控制文件误删除的恢复的上面数据文件的恢复方法基本一样：
[oracle@bys3 ~]$ rm -rf /u01/oradata/bys3/control*[oracle@bys3 ~]$ ps -ef|grep ckpt|grep -v grep ----注意这里查的是CKPT进程。
oracle 15076 1 0 11:53 ? 00:00:01 ora_ckpt_bys3
[oracle@bys3 ~]$ ll /proc/15076/fd
total 0
lr-x------ 1 oracle oinstall 64 Mar 7 12:12 0 -> /dev/null
l-wx------ 1 oracle oinstall 64 Mar 7 12:12 1 -> /dev/null
lrwx------ 1 oracle oinstall 64 Mar 7 12:12 10 -> /u01/app/oracle/product/11.2.0/dbhome_1/dbs/lkBYS3
lr-x------ 1 oracle oinstall 64 Mar 7 12:12 11 -> /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/mesg/oraus.msb
l-wx------ 1 oracle oinstall 64 Mar 7 12:12 2 -> /dev/null
lrwx------ 1 oracle oinstall 64 Mar 7 12:12 256 -> /u01/oradata/bys3/control01.ctl (deleted)
lrwx------ 1 oracle oinstall 64 Mar 7 12:12 257 -> /u01/oradata/bys3/control02.ctl (deleted)
lr-x------ 1 oracle oinstall 64 Mar 7 12:12 3 -> /dev/null
lr-x------ 1 oracle oinstall 64 Mar 7 12:12 4 -> /dev/null
lr-x------ 1 oracle oinstall 64 Mar 7 12:12 5 -> /dev/null
lr-x------ 1 oracle oinstall 64 Mar 7 12:12 6 -> /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/mesg/oraus.msb
lr-x------ 1 oracle oinstall 64 Mar 7 12:12 7 -> /proc/15076/fd
lr-x------ 1 oracle oinstall 64 Mar 7 12:12 8 -> /dev/zero
lrwx------ 1 oracle oinstall 64 Mar 7 12:12 9 -> /u01/app/oracle/product/11.2.0/dbhome_1/dbs/hc_bys3.dat
[oracle@bys3 ~]$ cp /proc/15076/fd/256 /u01/oradata/bys3/control01.ctl
[oracle@bys3 ~]$ cp /proc/15076/fd/257 /u01/oradata/bys3/control02.ctl
[oracle@bys3 ~]$ ls /u01/oradata/bys3/control*
/u01/oradata/bys3/control01.ctl /u01/oradata/bys3/control02.ctl
日志：
Fri Mar 07 12:12:04 2014
Errors in file /u01/diag/rdbms/bys3/bys3/trace/bys3_m000_15540.trc:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/oradata/bys3/control01.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
关闭数据库：
BYS@ bys3>conn / as sysdbaConnected.
SYS@ bys3>shutdown immediate;
Database closed.
ORA-03113: end-of-file on communication channel
Process ID: 15566
Session ID: 51 Serial number: 223
关库时日志：
ALTER DATABASE DISMOUNTShutting down archive processes
Archiving is disabled
********************* ATTENTION: ********************
The controlfile header block returned by the OS
has a sequence number that is too old.
The controlfile might be corrupted.
PLEASE DO NOT ATTEMPT TO START UP THE INSTANCE
without following the steps below.
RE-STARTING THE INSTANCE CAN CAUSE SERIOUS DAMAGE
TO THE DATABASE, if the controlfile is truly corrupted.
In order to re-start the instance safely,
please do the following:
(1) Save all copies of the controlfile for later
analysis and contact your OS vendor and Oracle support.
(2) Mount the instance and issue:
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
(3) Unmount the instance.
(4) Use the script in the trace file to
RE-CREATE THE CONTROLFILE and open the database.
打开数据库：
[oracle@bys3 ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Fri Mar 7 12:16:10 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SYS@ bys3>startup
ORACLE instance started.
Total System Global Area 225996800 bytes
Fixed Size 1363692 bytes
Variable Size 150995220 bytes
Database Buffers 67108864 bytes
Redo Buffers 6529024 bytes
Database mounted.
Database opened.
打开库时的日志
ALTER DATABASE MOUNT
Fri Mar 07 12:16:21 2014
Sweep [inc][246535]: completed
……………………………………
Sweep [inc2][246423]: completed
Sweep [inc2][246415]: completed
Sweep [inc2][246407]: completed
Successful mount of redo thread 1, with mount id 3368434514
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE MOUNT
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
How to recover deleted contacts on WeChat (simple tutorial tells you how to recover deleted contacts)
May 01, 2024 pm 12:01 PM
Unfortunately, people often delete certain contacts accidentally for some reasons. WeChat is a widely used social software. To help users solve this problem, this article will introduce how to retrieve deleted contacts in a simple way. 1. Understand the WeChat contact deletion mechanism. This provides us with the possibility to retrieve deleted contacts. The contact deletion mechanism in WeChat removes them from the address book, but does not delete them completely. 2. Use WeChat’s built-in “Contact Book Recovery” function. WeChat provides “Contact Book Recovery” to save time and energy. Users can quickly retrieve previously deleted contacts through this function. 3. Enter the WeChat settings page and click the lower right corner, open the WeChat application "Me" and click the settings icon in the upper right corner to enter the settings page.
Is it true that you can be blocked and deleted on WeChat and permanently unable to be added?
Apr 08, 2024 am 11:41 AM
1. First of all, it is false to block and delete someone permanently and not add them permanently. If you want to add the other party after you have blocked them and deleted them, you only need the other party's consent. 2. If a user blocks someone, the other party will not be able to send messages to the user, view the user's circle of friends, or make calls with the user. 3. Blocking does not mean deleting the other party from the user's WeChat contact list. 4. If the user deletes the other party from the user's WeChat contact list after blocking them, there is no way to recover after deletion. 5. If the user wants to add the other party as a friend again, the other party needs to agree and add the user again.
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
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.
The secret of hatching mobile dragon eggs is revealed (step by step to teach you how to successfully hatch mobile dragon eggs)
May 04, 2024 pm 06:01 PM
Mobile games have become an integral part of people's lives with the development of technology. It has attracted the attention of many players with its cute dragon egg image and interesting hatching process, and one of the games that has attracted much attention is the mobile version of Dragon Egg. To help players better cultivate and grow their own dragons in the game, this article will introduce to you how to hatch dragon eggs in the mobile version. 1. Choose the appropriate type of dragon egg. Players need to carefully choose the type of dragon egg that they like and suit themselves, based on the different types of dragon egg attributes and abilities provided in the game. 2. Upgrade the level of the incubation machine. Players need to improve the level of the incubation machine by completing tasks and collecting props. The level of the incubation machine determines the hatching speed and hatching success rate. 3. Collect the resources required for hatching. Players need to be in the game
How to completely delete TikTok chat history
May 07, 2024 am 11:14 AM
1. Open the Douyin app, click [Message] at the bottom of the interface, and click the chat conversation entry that needs to be deleted. 2. Long press any chat record, click [Multiple Select], and check the chat records you want to delete. 3. Click the [Delete] button in the lower right corner and select [Confirm deletion] in the pop-up window to permanently delete these records.
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,
