报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命令提示符下执行操作,请大家给出解决方法,谢谢
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命令提示符下执行操作,请大家给出解决方法,谢谢
recover datafile filename;
alter database open;参考 一下:
http://q.sohu.com/forum/5/topic/1648766
做个实验,希望帮你理解一下。SQL> select * from v$tablespace where name='TEST'; TS# NAME INC BIG FLA ENC
---------- -------------------------------------------------- --- --- --- ---
9 TEST YES NO YESSQL> select name,checkpoint_change#,status from v$datafile where ts#=9;NAME CHECKPOINT_CHANGE# STATUS
-------------------------------------------------- ------------------ -------
/oracle/product/oradata/luobo/test01.dbf 2908328 ONLINESQL> 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 2908328SQL> 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 OFFLINESQL> select name,recover,fuzzy,status,checkpoint_change# from v$datafile_header where ts#=9;no rows selectedSQL> 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 ONLINESQL> 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 2912729SQL> 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 RECOVERSQL> 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 2912729SQL> 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 OFFLINESQL> 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 2912770SQL> 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 ONLINESQL> 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