今天查看表空间发现个MISSING00005数据文件 --->原因:编辑控制文件的时候被遗漏掉的数据文件(example表空间)
1) select file#,status,name from v$datafile;
file# status name
--------------------------------------------------------------------
1 SYSTEM D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSTEM01.DBF
2 ONLINE D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS01.DBF
3 ONLINE D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSAUX01.DBF
4 ONLINE D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF
5 RECOVER C:\WINDOWS\SYSTEM32\MISSING000052)看下MISSING00005
是什么表空间
---- EXAMPLE 表空间
select * from v$tablespace;
TS#
NAME INCLUDED_IN_DATABASE_BACKUP BIGFILE
FLASHBACK_ON
--------------------------------------------------------------------
0 SYSTEM YES NO YES
1 UNDOTBS1 YES NO YES
2 SYSAUX YES NO YES
4 USERS YES NO YES
6 EXAMPLE YES NO YES
12 TEMP01 YES NO YES
EXAMPLE表空间存放的是系统自带的一些例子表,不要这些例子的话,该表空间可以删除
drop tablespace EXAMPLE including contents and datafiles;
报错~~ ORA-29857: domain indexes and/or secondary objects exist in the
tablespace(表空间中存在域索引和/或次级对象)
查看哪些objects存储在EXAMPLE表空间:
select distinct owner,segment_name, segment_type from dba_segments
where tablespace_name = 'EXAMPLE';
select distinct owner,segment_name,
segment_type from dba_extents where
tablespace_name='EXAMPLE';
select * from dba_tables where tablespace_name='EXAMPLE';
发现HR/OM/PM等用户下得好多表在这个空间里
select * from
OC.CUSTOMERS;任意的看张表。显示表/视图不存在。(前提此库ctl没指定example文件)
~~~意味着:这个example表空间在startup没有指定的话,并不是那么强大的要求,对数据库没有什么影响
所以可以考虑 将其drop或者move,然后再drop
tablespace
drop user HR cascade;...
drop tablespace tablespace_name including contents and datafiles;
命令删除表空间
select file#,name from v$datafile;
select * from
v$tablespace; -->查看下
OK了
总结:在startup的时候遗落了数据文件的话,要是example直接可以先删除该表空间内的表,之后在drop
tablespace,如果是其他数据文件
或者重新配置下控制文件 进行恢复 openSQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> create spfile from pfile;
File created.
SQL> startup mount
ORACLE instance started.
Total System Global
Area 171966464
bytes
Fixed
Size 787988
bytes
Variable
Size 145488364
bytes
Database
Buffers 25165824
bytes
Redo
Buffers 524288
bytes
ORA-00205: error in identifying controlfile, check alert log for
more info
SQL>
SQL> CREATE
CONTROLFILE REUSE DATABASE "ORCL"
NORESETLOGS ARCHIVELOG
2 MAXLOGFILES
16
3 MAXLOGMEMBERS
3
4 MAXDATAFILES
100
5 MAXINSTANCES
8
6 MAXLOGHISTORY
454
7 LOGFILE
8 GROUP
1 'D:\oracle\product\10.1.0\oradata\orcl\REDO01.LOG' ,
9 GROUP
2 'D:\oracle\product\10.1.0\oradata\orcl\REDO02.LOG' ,
10 GROUP
3 'D:\oracle\product\10.1.0\oradata\orcl\REDO03.LOG'
11 DATAFILE
12 'D:\oracle\product\10.1.0\oradata\orcl\SYSTEM01.DBF',
13 'D:\oracle\product\10.1.0\oradata\orcl\UNDOTBS01.DBF',
14 'D:\oracle\product\10.1.0\oradata\orcl\SYSAUX01.DBF',
15 'D:\oracle\product\10.1.0\oradata\orcl\USERS01.DBF',
16 'D:\oracle\product\10.1.0\oradata\orcl\EXAMPLE01.DBF'
17 CHARACTER
SET ZHS16GBK;
Control file created.
SQL> alter database backup controlfile to trace as
'D:\oracle\product\10.1.0\oradata\orcl\ctl01.ctl';
Database altered.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5:
'D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\EXAMPLE01.DBF'
SQL> recover datafile 5;
Media recovery complete.
SQL> alter database open;
Database altered.
SQL> select file#,status,name from v$datafile;
FILE#
STATUS NAME
----------
------- ------------------------------------------------
1
SYSTEM D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSTEM01.DBF
2
ONLINE D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS01.DBF
3
ONLINE D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSAUX01.DBF
4
ONLINE D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF
5
ONLINE D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\EXAMPLE01.DBF
SQL>
备份下给重新命名。。。等等
有备份归档首先考虑的是recover datafile 5;--->在此就不做试验了
在此就不一一演示了