一同事通过客户端连接到oracle数据库，然后运行一个大小将近400M的txt脚本，往数据库中插入数据。这个脚本只在最后才有一个commit操作，之前的都是insert语句，结果没跑多久，数据库就崩溃了。

Insert into QG_ZTRY_JBXX_II

*

ERROR at line 1:

ORA-01109: database not open

Insert into QG_ZTRY_JBXX_II

*

ERROR at line 1:

ORA-01109: database not open

--提示数据库没有打开

--------------------------------------------------------------------------------------

SQL> startup

ORA-01081: cannot start already-running ORACLE - shut it down first

--实际上数据库是打开的

---------------------------------------------------------------------------------------

SQL>

SQL> quti

SP2-0042: unknown command "quti" - rest of line ignored.

SQL> quit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

[oracle@softsz orcl]$ lsnrctl start

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 28-MAR-2013 07:36:36

TNS-01106: Listener using listener name LISTENER has already been started

[oracle@softsz orcl]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Mar 28 07:36:48 2013

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> startup

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 1218992 bytes

Variable Size 130025040 bytes

Database Buffers 150994944 bytes

Redo Buffers 2973696 bytes

Database mounted.

ORA-01110: data file 7: '/opt/app/oracle/oradata/orcl/ztry.dbf'

ORA-01115: IO error reading block from file 7 (block # 1)

ORA-27069: attempt to do I/O beyond the range of the file

Additional information: 1

SQL> !ls /opt/app/oracle/oradata/orcl/

control01.ctl oracleTSP.dbf redo02.log system01.dbf users01.dbf

control02.ctl PIAP01.dbf redo03.log temp01.dbf ztry.dbf

control03.ctl redo01.log sysaux01.dbf undotbs01.dbf

SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 1218992 bytes

Variable Size 130025040 bytes

Database Buffers 150994944 bytes

Redo Buffers 2973696 bytes

SQL> alter database mount;

Database altered.

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01157: cannot identify/lock data file 7 - see DBWR trace file

ORA-01110: data file 7: '/opt/app/oracle/oradata/orcl/ztry.dbf'

SQL> alter database '/opt/app/oracle/oradata/orcl/ztry.dbf' drop;

alter database '/opt/app/oracle/oradata/orcl/ztry.dbf' drop

*

ERROR at line 1:

ORA-02231: missing or invalid option to ALTER DATABASE

SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 1218992 bytes

Variable Size 130025040 bytes

Database Buffers 150994944 bytes

Redo Buffers 2973696 bytes

SQL> alter database mount;

Database altered.

SQL> alter database datafile '/opt/app/oracle/oradata/orcl/ztry.dbf' drop;

alter database datafile '/opt/app/oracle/oradata/orcl/ztry.dbf' drop

*

ERROR at line 1:

ORA-01916: keyword ONLINE, OFFLINE, RESIZE, AUTOEXTEND or END/DROP expected

SQL> alter database datafile '/opt/app/oracle/oradata/orcl/ztry.dbf' offline;

alter database datafile '/opt/app/oracle/oradata/orcl/ztry.dbf' offline

*

ERROR at line 1:

ORA-01145: offline immediate disallowed unless media recovery enabled

SQL> alter database create datafile 7;

Database altered.

SQL> !ls /opt/app/oracle/oradata/orcl/

control01.ctl oracleTSP.dbf redo02.log system01.dbf users01.dbf

control02.ctl PIAP01.dbf redo03.log temp01.dbf ztry.dbf

control03.ctl redo01.log sysaux01.dbf undotbs01.dbf

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01113: file 7 needs media recovery

ORA-01110: data file 7: '/opt/app/oracle/oradata/orcl/ztry.dbf'

SQL> recoer datafile 7;

SP2-0734: unknown command beginning "recoer dat..." - rest of line ignored.

SQL> recover datafile 7;

Media recovery complete.

SQL> alter database open;

Database altered.

SQL> conn ztry/admin

Connected.

SQL> select table_name,logging,status from tabs;

TABLE_NAME LOG STATUS

------------------------------ --- --------

QG_ZTRY_JBXX_II NO VALID

SQL> select count(*) from QG_ZTRY_JBXX_II;

COUNT(*)

----------

31982

SQL>

ITPub博客

大量insert造成的数据库崩溃实例