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

一同事通过客户端连接到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>

SQL>

SQL>

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

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

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

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

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

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博客 ” ，链接：http://blog.itpub.net/12238161/viewspace-758505/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12238161/viewspace-758505/