最近遭遇几次有人因为对oracle不太理解,由于各种情况下,删除了file$中的部分记录,从而使得该文件之后的文件都丢失,使得数据库出现各种异常情况。这里演示了可以重启数据库的情况下两种常见的删除file$中记录的恢复
创建表空间和表
SQL> startup
ORACLE instance started.
Total System
Global
Area 2421825536 bytes
Fixed
Size
2215744 bytes
Variable
Size
1828716736 bytes
Database
Buffers 570425344 bytes
Redo Buffers 20467712 bytes
Database
mounted.
Database
opened.
SQL>
create
tablespace tbs_delete_file datafile
'/home/oracle/oradata/xifenfei/file01.dbf'
size
128M;
Tablespace created.
SQL>
alter
tablespace tbs_delete_file
add
datafile
'/home/oracle/oradata/xifenfei/file02.dbf'
size
128M;
Tablespace altered.
SQL>
alter
tablespace tbs_delete_file
add
datafile
'/home/oracle/oradata/xifenfei/file03.dbf'
size
128M;
Tablespace altered.
SQL>
create
table
t_xifenfei tablespace tbs_delete_file
2
as
select
*
from
dba_objects;
Table
created.
SQL>
insert
into
t_xifenfei
select
*
from
dba_objects;
71895
rows
created.
SQL> /
71895
rows
created.
SQL> /
71895
rows
created.
SQL> /
71895
rows
created.
SQL>
commit
;
Commit
complete.
SQL>
select
count
(*)
from
t_xifenfei;
COUNT
(*)
----------
359475
|
删除file$中记录
SQL>
select
ts#,file#
from
file$;
TS# FILE#
---------- ----------
0 1
1 2
2 3
4 4
6 5
6 6
6 7
7
rows
selected.
[oracle@localhost ~]$ ss
SQL*Plus: Release 11.2.0.1.0 Production
on
Fri Jun 2 23:30:57 2017
Copyright (c) 1982, 2009, Oracle.
All
rights reserved.
Connected
to
:
Oracle
Database
11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With
the Partitioning, OLAP, Data Mining
and
Real
Application Testing options
SQL>
delete
from
file$
where
file#=5;
1 row deleted.
SQL>
commit
;
Commit
complete.
SQL> shutdown immediate;
Database
closed.
Database
dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected
from
Oracle
Database
11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With
the Partitioning, OLAP, Data Mining
and
Real
Application Testing options
|
不重建控制文件,重启数据库
[oracle@localhost tmp]$ ss
SQL*Plus: Release 11.2.0.1.0 Production
on
Fri Jun 2 23:46:33 2017
Copyright (c) 1982, 2009, Oracle.
All
rights reserved.
Connected
to
an idle instance.
SQL> startup
ORACLE instance started.
Total System
Global
Area 2421825536 bytes
Fixed
Size
2215744 bytes
Variable
Size
1828716736 bytes
Database
Buffers 570425344 bytes
Redo Buffers 20467712 bytes
Database
mounted.
Database
opened.
SQL>
select
file#
from
file$;
FILE#
----------
1
2
3
4
6
7
6
rows
selected.
SQL>
select
file#
from
v$datafile;
FILE#
----------
1
2
3
4
5
6
7
7
rows
selected.
SQL>
select
count
(*)
from
t_xifenfei;
COUNT
(*)
----------
359475
|
数据库启动正常,而且文件也未从控制文件中删除,而且记录查询正常,考虑通过逻辑方式迁移数据。
测试重建控制文件
SQL> startup nomount
ORACLE instance started.
Total System
Global
Area 2421825536 bytes
Fixed
Size
2215744 bytes
Variable
Size
1828716736 bytes
Database
Buffers 570425344 bytes
Redo Buffers 20467712 bytes
SQL>
CREATE
CONTROLFILE REUSE
DATABASE
"TEST"
NORESETLOGS NOARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
4 5 6 7 LOGFILE
8
GROUP
1
'/home/oracle/oradata/xifenfei/redo01.log'
SIZE
50M BLOCKSIZE 512,
GROUP
2
'/home/oracle/oradata/xifenfei/redo02.log'
SIZE
50M BLOCKSIZE 512,
GROUP
3
'/home/oracle/oradata/xifenfei/redo03.log'
SIZE
50M BLOCKSIZE 512
9 10 11 DATAFILE
12
'/home/oracle/oradata/xifenfei/system01.dbf'
,
'/home/oracle/oradata/xifenfei/sysaux01.dbf'
,
'/home/oracle/oradata/xifenfei/undotbs01.dbf'
,
13 14 15
'/home/oracle/oradata/xifenfei/users01.dbf'
,
'/home/oracle/oradata/xifenfei/file01.dbf'
,
16 17
'/home/oracle/oradata/xifenfei/file02.dbf'
,
'/home/oracle/oradata/xifenfei/file03.dbf'
CHARACTER
SET
AL32UTF8
18 19 20 ;
Control file created.
SQL>
alter
database
open
;
Database
altered.
SQL>
select
file#
from
v$datafile;
FILE#
----------
1
2
3
4
SQL>
select
file#
from
file$;
FILE#
----------
1
2
3
4
6
7
6
rows
selected.
SQL>
select
count
(*)
from
t_xifenfei;
select
count
(*)
from
t_xifenfei
*
ERROR
at
line 1:
ORA-00600: internal error code, arguments: [25029], [6], [], [], [], [], [],[], [], [], [], []
|
删除file$中记录,然后重启库之后,大于删除的file#之后的数据文件全部丢失.
alert日志报错
Fri Jun 02 23:49:42 2017
alter database
open
Fri Jun 02 23:49:42 2017
Thread 1 advanced to log sequence 9 (thread
open
)
Thread 1 opened at log sequence 9
Current log
# 3 seq# 9 mem# 0: /home/oracle/oradata/xifenfei/redo03.log
Successful
open
of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not
set
Fri Jun 02 23:49:42 2017
SMON: enabling cache recovery
Successfully onlined Undo Tablespace 2.
Dictionary check beginning
Tablespace
'TEMP'
#3 found in data dictionary,
but not
in
the controlfile. Adding to controlfile.
File
#5 in the controlfile not found in data dictionary.
Removing
file
from controlfile.
data
file
5:
'/home/oracle/oradata/xifenfei/file01.dbf'
File
#6 in the controlfile not found in data dictionary.
Removing
file
from controlfile.
data
file
6:
'/home/oracle/oradata/xifenfei/file02.dbf'
File
#7 in the controlfile not found in data dictionary.
Removing
file
from controlfile.
data
file
7:
'/home/oracle/oradata/xifenfei/file03.dbf'
Dictionary check complete
Verifying
file
header compatibility
for
11g tablespace encryption..
Verifying 11g
file
header compatibility
for
tablespace encryption completed
SMON: enabling tx recovery
*********************************************************************
WARNING: The following temporary tablespaces contain no files.
This condition can occur when a backup controlfile has
been restored. It may be necessary to add files to these
tablespaces. That can be
done
using the SQL statement:
ALTER TABLESPACE <tablespace_name> ADD TEMPFILE
Alternatively,
if
these temporary tablespaces are no longer
needed,
then
they can be dropped.
Empty temporary tablespace: TEMP
*********************************************************************
Database Characterset is AL32UTF8
No Resource Manager plan active
**********************************************************
WARNING: Files may exists
in
db_recovery_file_dest
that are not known to the database. Use the RMAN
command
CATALOG RECOVERY AREA to re-catalog any such files.
If files cannot be cataloged,
then
manually delete them
using OS
command
.
One of the following events caused this:
1. A backup controlfile was restored.
2. A standby controlfile was restored.
3. The controlfile was re-created.
4. db_recovery_file_dest had previously been enabled and
then
disabled.
**********************************************************
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Fri Jun 02 23:49:43 2017
QMNC started with pid=20, OS
id
=11886
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Completed: alter database
open
Fri Jun 02 23:49:44 2017
db_recovery_file_dest_size of 3882 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database
for
recovery-related files, and does not reflect the amount of
space available
in
the underlying filesystem or ASM diskgroup.
Fri Jun 02 23:49:44 2017
Errors
in
file
/opt/oracle/diag/rdbms/test/test/trace/test_m001_11890
.trc (incident=84344):
ORA-00600: internal error code, arguments: [25029], [6], [], [], [], [], [], [], [], [], [], []
Incident details
in
:
/opt/oracle/diag/rdbms/test/test/incident/incdir_84344/test_m001_11890_i84344
.trc
Errors
in
file
/opt/oracle/diag/rdbms/test/test/trace/test_m001_11890
.trc:
ORA-00600: internal error code, arguments: [25029], [6], [], [], [], [], [], [], [], [], [], []
Fri Jun 02 23:49:45 2017
Trace dumping is performing
id
=[cdmp_20170602234945]
Fri Jun 02 23:49:46 2017
Starting background process CJQ0
Fri Jun 02 23:49:46 2017
CJQ0 started with pid=21, OS
id
=11902
|
这里报错明显,由于file$.file#=5被删除,重建控制文件之后,file#在5之后的数据文件全部从控制文件中删除,类似提示File #5 in the controlfile not found in data dictionary. Removing file from controlfile.
插入file$记录恢复
--找出来被删除的file$中记录
[oracle@localhost dump]$ more SYS_FILE\$.dat
5|2|16384|6|5|0|0|0|993135||20971522||||
--插入被file$删除记录
SQL>
insert
into
file$
values
(5,2,16384,6,5,0,0,0,993135,
null
,20971522,
null
,
null
,
null
);
1 row created.
SQL>
commit
;
Commit
complete.
SQL>
select
file#
from
file$;
FILE#
----------
1
2
3
4
5
6
7
7
rows
selected.
--重启数据库,创建控制文件
SQL> shutdown immediate
Database
closed.
Database
dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.
Total System
Global
Area 2421825536 bytes
Fixed
Size
2215744 bytes
Variable
Size
1828716736 bytes
Database
Buffers 570425344 bytes
Redo Buffers 20467712 bytes
SQL>
CREATE
CONTROLFILE REUSE
DATABASE
"TEST"
NORESETLOGS NOARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
4 5 6 MAXLOGHISTORY 292
7 LOGFILE
GROUP
1
'/home/oracle/oradata/xifenfei/redo01.log'
SIZE
50M BLOCKSIZE 512,
GROUP
2
'/home/oracle/oradata/xifenfei/redo02.log'
SIZE
50M BLOCKSIZE 512,
8 9 10
GROUP
3
'/home/oracle/oradata/xifenfei/redo03.log'
SIZE
50M BLOCKSIZE 512
DATAFILE
11 12
'/home/oracle/oradata/xifenfei/system01.dbf'
,
'/home/oracle/oradata/xifenfei/sysaux01.dbf'
,
13 14
'/home/oracle/oradata/xifenfei/undotbs01.dbf'
,
15
'/home/oracle/oradata/xifenfei/users01.dbf'
,
'/home/oracle/oradata/xifenfei/file01.dbf'
,
'/home/oracle/oradata/xifenfei/file02.dbf'
,
16 17 18
'/home/oracle/oradata/xifenfei/file03.dbf'
19
CHARACTER
SET
AL32UTF8
; 20
Control file created.
SQL>
alter
database
open
;
alter
database
open
*
ERROR
at
line 1:
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5:
'/home/oracle/oradata/xifenfei/file01.dbf'
SQL> recover
database
;
Media recovery complete.
SQL>
alter
database
open
;
Database
altered.
SQL>
select
file#
from
file$;
FILE#
----------
1
2
3
4
5
6
7
7
rows
selected.
SQL>
select
file#
from
v$datafile;
FILE#
----------
1
2
3
4
5
6
7
7
rows
selected.
SQL>
select
count
(*)
from
t_xifenfei;
COUNT
(*)
----------
359475
|
alert日志正常,未提示删除控制文件中数据文件
alter database
open
Fri Jun 02 23:56:52 2017
Thread 1 advanced to log sequence 10 (thread
open
)
Thread 1 opened at log sequence 10
Current log
# 1 seq# 10 mem# 0: /home/oracle/oradata/xifenfei/redo01.log
Successful
open
of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not
set
Fri Jun 02 23:56:52 2017
SMON: enabling cache recovery
Successfully onlined Undo Tablespace 2.
Dictionary check beginning
Tablespace
'TEMP'
#3 found in data dictionary,
but not
in
the controlfile. Adding to controlfile.
Dictionary check complete
Verifying
file
header compatibility
for
11g tablespace encryption..
Verifying 11g
file
header compatibility
for
tablespace encryption completed
SMON: enabling tx recovery
*********************************************************************
WARNING: The following temporary tablespaces contain no files.
This condition can occur when a backup controlfile has
been restored. It may be necessary to add files to these
tablespaces. That can be
done
using the SQL statement:
ALTER TABLESPACE <tablespace_name> ADD TEMPFILE
Alternatively,
if
these temporary tablespaces are no longer
needed,
then
they can be dropped.
Empty temporary tablespace: TEMP
*********************************************************************
Database Characterset is AL32UTF8
No Resource Manager plan active
**********************************************************
WARNING: Files may exists
in
db_recovery_file_dest
that are not known to the database. Use the RMAN
command
CATALOG RECOVERY AREA to re-catalog any such files.
If files cannot be cataloged,
then
manually delete them
using OS
command
.
One of the following events caused this:
1. A backup controlfile was restored.
2. A standby controlfile was restored.
3. The controlfile was re-created.
4. db_recovery_file_dest had previously been enabled and
then
disabled.
**********************************************************
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Fri Jun 02 23:56:53 2017
QMNC started with pid=20, OS
id
=12127
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Completed: alter database
open
|
通过插入删除记录,重建控制文件,数据库恢复正常,而且相关记录也可以查询。
结论总结
1. 如果删除file$中的记录,而且控制文件未重建,数据库可以正常启动,而且可以查询数据
2. 如果删除file$中的记录,而且控制文件被重建,在数据库启动过程中,从被删除文件之后的所有文件记录从控制文件中删除(类似:File N in the controlfile not found in data dictionary. Removing file from controlfile.).可以在数据库open之后,插入被删除的记录,重建控制文件,数据会被恢复回来.如果数据库无法启动,需要通过其他方式进行恢复被删除记录