有一测试库,由于是测试库,所以某人比较随便的直接在数据库关闭的状态下删除了一数据文件现在数据中此表空间的状态是:
SQL> select file#,status,name from v$datafile where file#=3; FILE# STATUS NAME
---------- ------- ----------------------------------------------
3 RECOVER /oracle/product/10.2.0/dbs/MISSING00003想要把此表空间删除,然后再重新建立空的同名表空间
SQL> drop tablespace platform including contents;
drop tablespace platform including contents and datafiles
*
第 1 行出现错误:
ORA-00604: error occurred at recursive SQL level 2
ORA-00376: file 3 cannot be read at this time
ORA-01111: name for data file 3 is unknown - rename to correct file
ORA-01110: data file 3: '/oracle/product/10.2.0/dbs/MISSING00003'
ORA-06512: at line 19
请问要如何处理呢
SQL> select file#,status,name from v$datafile where file#=3; FILE# STATUS NAME
---------- ------- ----------------------------------------------
3 RECOVER /oracle/product/10.2.0/dbs/MISSING00003想要把此表空间删除,然后再重新建立空的同名表空间
SQL> drop tablespace platform including contents;
drop tablespace platform including contents and datafiles
*
第 1 行出现错误:
ORA-00604: error occurred at recursive SQL level 2
ORA-00376: file 3 cannot be read at this time
ORA-01111: name for data file 3 is unknown - rename to correct file
ORA-01110: data file 3: '/oracle/product/10.2.0/dbs/MISSING00003'
ORA-06512: at line 19
请问要如何处理呢
解决方案 »
- oracle启动的时候问题java出错
- dataguard SWITCHOVER_STATUS
- oracle 中两个到三表级联查询且要进行分页
- ORALCE數據庫備份與恢復的問題?請高手指點?
- Toad问题
- 返回一个数据集的存储过程怎么写~?
- 資料庫數據的導出
- 求:linux8.0下安装oracle817
- OCISessionBegin: ORA-12705: invalid or unknown NLS parameter value specified 错误是什么错误,该如何解决?
- 如何插Date型数据?
- oracle中怎么定时的执行某个存储过程?
- 如何读取一个表中每个系列中的最新一条数据??
1、shutdown immediate --启动数据库、此时提示数据文件丢失,并且有丢失的序列号例如:ORA-01110: 数据文件 5: 'D:\APP\MKF18858\ORADATA\MAHANSO\TEST.DBF'
2、startup --把丢失的数据文件卸下
3、alter database datafile '丢失文件路径和文件名称' offline drop--打开数据库
4、alter database open--创建数据文件
5、alter database create datafile '丢失文件路径和文件名称';--恢复数据库文件
6、recover datafile 丢失的序列号--装载数据文件
7、alter database datafile '丢失文件路径和文件名称' online;
startup mount;
alter database datafile 'full_name' offline drop;
alter database open;
drop tablespace tablespace_name;
SQL> alter database create datafile '/oradata/mydata/platform.dbf';
alter database create datafile '/oradata/mydata/platform.dbf'
*
ERROR at line 1:
ORA-01516: nonexistent log file, datafile, or tempfile
"/oradata/mydata/platform.dbf"
如果用v$datafile里的名字
SQL> alter database create datafile '/oracle/product/10.2.0/dbs/MISSING00003';
alter database create datafile '/oracle/product/10.2.0/dbs/MISSING00003'
*
ERROR at line 1:
ORA-01178: file 3 created before last CREATE CONTROLFILE, cannot recreate
ORA-01111: name for data file 3 is unknown - rename to correct file
ORA-01110: data file 3: '/oracle/product/10.2.0/dbs/MISSING00003'直接想删除表空间也不行
SQL> drop tablespace platform including contents;
drop tablespace platform including contents
*
第 1 行出现错误:
ORA-00604: error occurred at recursive SQL level 2
ORA-00376: file 3 cannot be read at this time
ORA-01111: name for data file 3 is unknown - rename to correct file
ORA-01110: data file 3: '/oracle/product/10.2.0/dbs/MISSING00003'
ORA-06512: at line 19
现在的数据文件名是变成了'/oracle/product/10.2.0/dbs/MISSING00003' 这样的SQL> alter database create datafile '/oracle/product/10.2.0/dbs/MISSING00003' as '/oradata/mydata/platform.dbf';
alter database create datafile '/oracle/product/10.2.0/dbs/MISSING00003' as '/oradata/mydata/platform.dbf'
*
ERROR at line 1:
ORA-01178: file 3 created before last CREATE CONTROLFILE, cannot recreate
ORA-01111: name for data file 3 is unknown - rename to correct file
ORA-01110: data file 3: '/oracle/product/10.2.0/dbs/MISSING00003'