ora-01113,ora-01110错误，请帮忙看下，谢谢

[wildoracle]

报ora-01113,ora-01110错误是我以下操作产生的，请各位知道情况的给讲解下，谢谢

1.alter tablespace ha offline normal;
2.到操作系统上把ha_1000.dbf 复制了一份在同文件夹下，修改名为ha1_2000.DBF'
3.alter tablespace ha rename datafile 'E:\Oradata\ha\ha1_1000.DBF' to 'E:\Oradata\ha\ha1_2000.DBF';
4.Alter tablespace ha online;

在执行第4步的时候就把ora-01113,ora-01110错误，提示需要介质恢复

我用pl/sql客户端还能登陆数据库,但ha表空间下的有些表能查询,有些就不可以,谢谢

用的windows系统,在cmd - sqlplus命令提示符下执行操作,请大家给出解决方法,谢谢

[wildoracle]

补充，数据库不是在归档模式下运行的，谢谢

[iihero_]

startup mount;
recover datafile filename;
alter database open;

参考　一下：
http://q.sohu.com/forum/5/topic/1648766

[zjwssg]

ha在被offline之前是不是已经有ha的某个datafile是offline的了？

[碧水幽幽泉]

做个实验，希望帮你理解一下。

SQL> select * from v$tablespace where name='TEST';

       TS# NAME                                               INC BIG FLA ENC
---------- -------------------------------------------------- --- --- --- ---
         9 TEST                                               YES NO  YES

SQL> select name,checkpoint_change#,status from v$datafile where ts#=9;

NAME                                               CHECKPOINT_CHANGE# STATUS
-------------------------------------------------- ------------------ -------
/oracle/product/oradata/luobo/test01.dbf                      2908328 ONLINE

SQL> select name,recover,fuzzy,status,checkpoint_change# from v$datafile_header where ts#=9;

NAME                                               REC FUZ STATUS  CHECKPOINT_CHANGE#
-------------------------------------------------- --- --- ------- ------------------
/oracle/product/oradata/luobo/test01.dbf           NO  YES ONLINE             2908328

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     58
Next log sequence to archive   60
Current log sequence           60
SQL> 
SQL> alter tablespace test offline;

Tablespace altered.

SQL> select name,checkpoint_change#,status from v$datafile where ts#=9;

NAME                                               CHECKPOINT_CHANGE# STATUS
-------------------------------------------------- ------------------ -------
/oracle/product/oradata/luobo/test01.dbf                      2912584 OFFLINE

SQL> select name,recover,fuzzy,status,checkpoint_change# from v$datafile_header where ts#=9;

no rows selected

SQL> alter tablespace test online;

Tablespace altered.

SQL> select name,checkpoint_change#,status from v$datafile where ts#=9;

NAME                                               CHECKPOINT_CHANGE# STATUS
-------------------------------------------------- ------------------ -------
/oracle/product/oradata/luobo/test01.dbf                      2912729 ONLINE

SQL> select name,recover,fuzzy,status,checkpoint_change# from v$datafile_header where ts#=9;

NAME                                               REC FUZ STATUS  CHECKPOINT_CHANGE#
-------------------------------------------------- --- --- ------- ------------------
/oracle/product/oradata/luobo/test01.dbf           NO  YES ONLINE             2912729

SQL> alter tablespace test offline immediate;

Tablespace altered.

SQL> select name,checkpoint_change#,status from v$datafile where ts#=9;

NAME                                               CHECKPOINT_CHANGE# STATUS
-------------------------------------------------- ------------------ -------
/oracle/product/oradata/luobo/test01.dbf                      2912729 RECOVER

SQL> select name,recover,fuzzy,status,checkpoint_change# from v$datafile_header where ts#=9;

NAME                                               REC FUZ STATUS  CHECKPOINT_CHANGE#
-------------------------------------------------- --- --- ------- ------------------
/oracle/product/oradata/luobo/test01.dbf           YES YES OFFLINE            2912729

SQL> alter tablespace test online;
alter tablespace test online
*
ERROR at line 1:
ORA-01113: file 11 needs media recovery
ORA-01110: data file 11: '/oracle/product/oradata/luobo/test01.dbf'


SQL> recover tablespace test;
Media recovery complete.
SQL> select name,checkpoint_change#,status from v$datafile where ts#=9;

NAME                                               CHECKPOINT_CHANGE# STATUS
-------------------------------------------------- ------------------ -------
/oracle/product/oradata/luobo/test01.dbf                      2912770 OFFLINE

SQL> select name,recover,fuzzy,status,checkpoint_change# from v$datafile_header where ts#=9;

NAME                                               REC FUZ STATUS  CHECKPOINT_CHANGE#
-------------------------------------------------- --- --- ------- ------------------
/oracle/product/oradata/luobo/test01.dbf           NO  NO  OFFLINE            2912770

SQL> alter tablespace test online;

Tablespace altered.

SQL> select name,checkpoint_change#,status from v$datafile where ts#=9;

NAME                                               CHECKPOINT_CHANGE# STATUS
-------------------------------------------------- ------------------ -------
/oracle/product/oradata/luobo/test01.dbf                      2912890 ONLINE

SQL> select name,recover,fuzzy,status,checkpoint_change# from v$datafile_header where ts#=9;

NAME                                               REC FUZ STATUS  CHECKPOINT_CHANGE#
-------------------------------------------------- --- --- ------- ------------------
/oracle/product/oradata/luobo/test01.dbf           NO  YES ONLINE

[zjwssg]

楼上的，楼主不是immediate

[wen12128]

误删dbf文件造成ORA-01109,ORA-01110: 数据库未打开.

1.cmd-sqlplus /nolog-conn system/pwd as sysdba

2.shutdown immediate;

3.startup mount;

4.alter database datafile 'd:\*.dbf' offline drop;

alter database open;

可参考：http://hi.baidu.com/tned/blog/item/07ab8525ff3e026734a80f08.html