误删除数据文件、控制文件的非RMAN恢复方法

原创

wb94a78wq170rt0 2023-07-24 20:38:40 ©著作权

©著作权归作者所有：来自51CTO博客作者wb94a78wq170rt0的原创作品，请联系作者获取转载授权，否则将追究法律责任

在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

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 sysdba

Connected.

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 DISMOUNT

Shutting 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 sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Mar 7 12:16:10 2014

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

上一篇：buffer cache实验9-从buffer caceh中读取数据块解析-从逻辑读到物理读

下一篇：buffer cache实验2-详解Buffer Header--DUMP buffer结合X$BH视图各字段

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯