linux下oracle数据库文件丢失恢复

数据文件丢失之后的恢复

错误现象：

sql> startup

oracle instance started.

total system global area 7310Array664 bytes fixed size 73888 bytes variable size 56086528 bytes database buffers 16777216 bytes redo buffers 172032 bytes database mounted.

ora-03113: end-of-file on communication channel

产生缘由分析：

我的环境是linuxArray oracle 8.1.7.4

出现问题的当时是，一台机器连到上面做insert into 操作，数据大概有63万条。 正在执行的过程中因为到了下班的时间，服务器设置的定时自动关机的功能，服务器

在五点半的时候关机，导致insert into 操作中断，等服务器起来之后，提示一个表数据文件有问题，我就执行了

shutdown immediate startup mount

recover datafile /datafile.dbf 提示恢复成功 startup

就出现上面的错误提示

终于搞定了，采取的步骤是把，受到影响的表空间何数据文件drop 掉 sql> startup

oracle instance started.

total system global area 7310Array664 bytes fixed size 73888 bytes variable size 56086528 bytes database buffers 16777216 bytes redo buffers 172032 bytes database mounted.

ora-03113: end-of-file on communication channel

sql> conn system/manager as sysdba

connected.

sql> select name from datafiles 2 ;

select name from datafiles * error at line 1:

ora-0121Array: database not open: queries allowed on fixed tables/views only

sql> select name from v$datafile 2 ; name

-------------------------------------------------------------------------------- /u01/oradata/emcdb/system01.dbf /u01/oradata/emcdb/tools01.dbf /u01/oradata/emcdb/rbs01.dbf /u01/oradata/emcdb/temp01.dbf /u01/oradata/emcdb/users01.dbf /u01/oradata/emcdb/indx01.dbf /u01/oradata/emcdb/drsys01.dbf /u01/oradata/emcdb/emcbase.dbf /home/oracle/test.dbf

/home/adonis/dwbx_wmstat.dbf //home/adonis/iwbx_wmindx.dbf

11 rows selected.

sql> alter database datafile //home/adonis/iwbx_wmindx.dbf offline 2 ;

alter database datafile //home/adonis/iwbx_wmindx.dbf offline *

error at line 1:

ora-01145: offline immediate disallowed unless media recovery enabled

sql> alter database datafile //home/adonis/iwbx_wmindx.dbf offline drop;

database altered.

sql> alter database datafile /home/adonis/dwbx_wmstat.dbf offline drop;

database altered.

sql> alter database open; alter database open *

error at line 1:

ora-03113: end-of-file on communication channel

sql> select name from v$tablespace; select name from v$tablespace *

error at line 1:

ora-03114: not connected to oracle

sql> connect system/manager as sysdba; connected.

sql> select name from v$tablespace; name

------------------------------ system temp rbs indx users drsys tools emcbase test_user dwbx_wmstat iwbx_wmindx

11 rows selected.

sql> alter database tablespace dwbx_wmstat offline; alter database tablespace dwbx_wmstat offline * error at line 1:

ora-02231: missing or invalid option to alter database

sql> drop tablespace dwbx_wmstat; drop tablespace dwbx_wmstat *

error at line 1:

ora-0154Array: tablespace not empty, use including contents option

sql> drop tablespace dwbx_wmstat including contents;

tablespace dropped.

sql> drop tablespace iwbx_wmindx including contents;

tablespace dropped.

sql> alter database open 2 ;

alter database open *

error at line 1:

ora-01531: a database already open by the instance

database open success！！！！！