《oracle误删除表空间的数据文件》由会员分享，可在线阅读，更多相关《oracle误删除表空间的数据文件（12页珍藏版）》请在金锄头文库上搜索。
1、事故原因:1.由于误操作用hp unix 命令 rm -f datafilename 删除表空间的数据文件2.alter tablespace tablespacenaem drop datafile datafile ;3.drop tablespace tablespacename including content and datafiles;上述两个步骤我用了近三个小时都没有执行完，最后导致数据库宕机。下面把我当时启动数据的后台页面展现给大家，为以后出现同样的问题，提供一个参照的作用.SP2-0734: unknown command beginning sqlplus /n. - r
2、est of line ignored.SQL conn sys/passwd as sysdba;Connected to an idle instance.SQL startupORACLE instance started.Total System Global Area 3.2212E 10 bytesFixed Size 2115136 bytesVariable Size 3204450752 bytesDatabase Buffers 2.8991E 10 bytesRedo Buffers 14659584 bytesDatabase mounted.ORA-01157: ca
3、nnot identify/lock data file 39 - see DBWR trace fileORA-01110: data file 39: /data/tbs_db_bas2.dbfSQL shutdown immediate;ORA-01109: database not openDatabase dismounted.ORACLE instance shut down.SQL startup mount;ORACLE instance started.Total System Global Area 3.2212E 10 bytesFixed Size 2115136 by
4、tesVariable Size 3204450752 bytesDatabase Buffers 2.8991E 10 bytesRedo Buffers 14659584 bytesDatabase mounted.SQL recover datafile tbs_db_bas2.dbf;ORA-02236: invalid file nameSQL recover datafile /data/tbs_db_bas2.dbf;ORA-00283: recovery session canceled due to errorsORA-01110: data file 39: /data/t
5、bs_db_bas2.dbfORA-01157: cannot identify/lock data file 39 - see DBWR trace fileORA-01110: data file 39: /data/tbs_db_bas2.dbfSQL revover database;SP2-0734: unknown command beginning revover da. - rest of line ignored.SQL recover database;ORA-00283: recovery session canceled due to errorsORA-01110:
6、data file 39: /data/tbs_db_bas2.dbfORA-01157: cannot identify/lock data file 39 - see DBWR trace fileORA-01110: data file 39: /data/tbs_db_bas2.dbfSQL shutdown immediat;SP2-0717: illegal SHUTDOWN optionSQL shutdown immediate;ORA-01109: database not openDatabase dismounted.ORACLE instance shut down.S
7、QL startup mount;ORACLE instance started.Total System Global Area 3.2212E 10 bytesFixed Size 2115136 bytesVariable Size 3204450752 bytesDatabase Buffers 2.8991E 10 bytesRedo Buffers 14659584 bytesDatabase mounted.SQL alter database datafile /data/tbs_db_bas1.dbf offline drop;Database altered.SQL alt
8、er database open;alter database open*ERROR at line 1:ORA-01157: cannot identify/lock data file 39 - see DBWR trace fileORA-01110: data file 39: /data/tbs_db_bas2.dbfSQL alter database datafile /data/tbs_db_bas2.dbf offline drop;Database altered.SQL alter database open;Database altered.SQL exitDiscon
9、nected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsoraclehljww:/oracle/oracle/OraHome_1/network/admin$ lsnrctlLSNRCTL for HPUX: Version 10.2.0.4.0 - Production on 03-MAY-2010 20:13:51Cop
10、yright (c) 1991, 2007, Oracle. All rights reserved.Welcome to LSNRCTL, type help for information.LSNRCTL stopConnecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hljww)(PORT=1521)The command completed successfullyLSNRCTL startStarting /oracle/oracle/OraHome_1/bin/tnslsnr: please wait.TNSLSNR for
11、HPUX: Version 10.2.0.4.0 - ProductionSystem parameter file is /oracle/oracle/OraHome_1/network/admin/listener.oraLog messages written to /oracle/oracle/OraHome_1/network/log/listener.logListening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hljww)(PORT=1521)Listening on: (DESCRIPTION=(ADDRESS=(PROT
12、OCOL=ipc)(KEY=EXTPROC0)Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hljww)(PORT=1521)STATUS of the LISTENER-Alias LISTENERVersion TNSLSNR for HPUX: Version 10.2.0.4.0 - ProductionStart Date 03-MAY-2010 20:14:08Uptime 0 days 0 hr. 0 min. 0 secTrace Level offSecurity ON: Local OS Authentica
13、tionSNMP OFFListener Parameter File /oracle/oracle/OraHome_1/network/admin/listener.oraListener Log File /oracle/oracle/OraHome_1/network/log/listener.logListening Endpoints Summary.(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hljww)(PORT=1521)(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)Services
14、Summary.Service hljwxwl has 1 instance(s).Instance hljwxwl, status UNKNOWN, has 1 handler(s) for this service.The command completed successfully- 总结上述报错原因是由于我的数据文件没有在oracle内部进行删除导致数据库重新启动时找不到相应的数据文件，报上述错误，所以我建议大家遇到问题时，要沉着，冷静，不要乱，做好备份工作，特别是遇到错误时我们上网查一下oracle错误，进行相应的处理。下面我把这次我用到的文档分享给大家，其中粉红色字体为本次用到的解
15、决方案。ORA-1157, cannot identify/lock data file %s - see DBWR trace file引起的原因：因为数据文件已经在被使用了从而导致数据库的后台进程不能找到相应的数据文件或者不能锁定相应的数据文件，这样数据库将禁止访问这些数据文件而其他的数据文件则没有影响。伴随这个错误操作系统将会提示是哪个数据文件不能被识别。ORA-01157错误一般和ORA-01110错误一起出现，往往还有操作系统级别上的错误，例如ORA-07360，同时一个DBWR的trace文件会在background_dump_dest的目录下生成。例如，在Solaris的平台上，
16、会有如下的错误信息显示：more.less.ORA-01157: cannot identify/lock data file 5 - see DBWR trace fileORA-01110: data file 5: /export/home/Oracle/oradata/817/users01.dbf然后查看DBWR的trace文件内容，会有如下的内容：ORA-01157: cannot identify/lock data file 5 - see DBWR trace fileORA-01110: data file 5: /export/home /Oracle/oradata/817/users01.dbfORA-27037: unable to obtain file statusSVR4 Error: 2: No such f
