删除数据库关键数据文件后实例是否会崩溃

上次试验得出删除数据库的所有控制文件，其实实例是不会崩溃的，而且还可以提供“有所保留”的服务。

那么删除诸如system表空间或者undo表空间里的数据文件呢？这些可是所谓的关键数据文件额。

具体得试验一把，ok，以下试验我也是第一次做：

先冷备数据库文件（此处略）。

[oracle@db1 ~]$ ps -ef | grep ora_
oracle    1270  1235  1 10:01 pts/0    00:00:00 grep ora_
[oracle@db1 ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Mon Oct 5 10:01:18 2015

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

SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area  835104768 bytes
Fixed Size                  2217952 bytes
Variable Size             595593248 bytes
Database Buffers          230686720 bytes
Redo Buffers                6606848 bytes
Database mounted.
Database opened.
SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> 

启动数据库，然后使用scott用户进去dml：

SQL> conn scott/scott
Connected.
SQL> select * from tab;


TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BONUS                          TABLE
DEPT                           TABLE
EMP                            TABLE
SALGRADE                       TABLE
T1                             TABLE
V                              VIEW

6 rows selected.

SQL> insert into t1 select * from t1;

3072 rows created.

SQL>commit;

Commit complete.

SQL> 

此时我们在os下手工mv掉system表空间的数据文件：

[oracle@db1 denver]$ mv system01.dbf system01.dbf.kkk

然后继续再scott里进行insert操作：

SQL> insert into t1 select * from t1;

6144 rows created.

SQL> commit;

Commit complete.

SQL> insert into t1 select * from t1;

12288 rows created.

SQL> commit;

Commit complete.

SQL> 

我去，实例是没有崩溃的，scott的dml还可以继续。我们看看告警日志：

Mon Oct 05 10:10:07 2015
Checker run found 1 new persistent data failures
Mon Oct 05 10:12:02 2015
Errors in file /u01/diag/rdbms/denver/denver/trace/denver_j000_2072.trc:
ORA-12012: error on auto execute of job 4002
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/oradata/denver/system01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

实例首先是检查出了一个永久数据文件失败，然后接着在执行autojob的时候继续抛出了确切的错误。看看trace日志：

[oracle@db1 denver]$ more /u01/diag/rdbms/denver/denver/trace/denver_j000_2072.trc
Trace file /u01/diag/rdbms/denver/denver/trace/denver_j000_2072.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle
System name:    Linux
Node name:      db1
Release:        2.6.32-220.el6.x86_64
Version:        #1 SMP Wed Nov 9 08:03:13 EST 2011
Machine:        x86_64
Instance name: denver
Redo thread mounted by this instance: 1
Oracle process number: 24
Unix process pid: 2072, image: oracle@db1 (J000)




*** 2015-10-05 10:12:01.204
*** SESSION ID:(7.11) 2015-10-05 10:12:01.204
*** CLIENT ID:() 2015-10-05 10:12:01.204
*** SERVICE NAME:(SYS$USERS) 2015-10-05 10:12:01.204
*** MODULE NAME:() 2015-10-05 10:12:01.204
*** ACTION NAME:() 2015-10-05 10:12:01.204
 
DDE rules only execution for: ORA 1110
----- START Event Driven Actions Dump ----
---- END Event Driven Actions Dump ----
----- START DDE Actions Dump -----
Executing SYNC actions
----- START DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (Async) -----
Successfully dispatched
----- END DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (SUCCESS, 0 csec) -----
Executing ASYNC actions
----- END DDE Actions Dump (total 0 csec) -----


*** 2015-10-05 10:12:01.592
ORA-12012: error on auto execute of job 4002
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/oradata/denver/system01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


*** 2015-10-05 10:12:06.228
*** SESSION ID:(7.13) 2015-10-05 10:12:06.265
*** SERVICE NAME:(SYS$USERS) 2015-10-05 10:12:06.265
 
DDE: Problem Key 'ORA 1110' was flood controlled (0x1) (no incident)
ORA-01110: data file 1: '/u01/oradata/denver/system01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
[oracle@db1 denver]$ 

job是一个定时的数据库结构检查任务。很明显会检查到system表空间的数据文件丢失的问题。

其他高级操作呢？：

SQL> show user;
USER is "SYS"
SQL> alter user hr account lock;

User altered.

SQL> 

也成功了，刷出一次buffer cache：

SQL> alter system checkpoint;
alter system checkpoint
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 2024
Session ID: 191 Serial number: 3

SQL> 

出错了，再看看alert日志：

Mon Oct 05 10:17:00 2015
Errors in file /u01/diag/rdbms/denver/denver/trace/denver_ckpt_1959.trc:
ORA-01242: data file suffered media failure: database in NOARCHIVELOG mode
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/oradata/denver/system01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/diag/rdbms/denver/denver/trace/denver_ckpt_1959.trc:
ORA-01242: data file suffered media failure: database in NOARCHIVELOG mode
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/oradata/denver/system01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
CKPT (ospid: 1959): terminating the instance due to error 1242
Instance terminated by CKPT, pid = 1959

额，chpt进程终止了实例。

结论很明显，system表空间的数据文件丢失，实例也不会马上就崩溃，再涉及到相关数据文件访问时，才会发生实例终止的现象。

对于关键数据文件，还有一个是重要的，那就是undo表空间的数据文件。来看看undo表空间的数据文件丢失的情况：

<上面的数据库已经恢复>

SQL> insert into t1 select * from t1;

49152 rows created.

SQL> 

不提交，先把undo数据文件给干掉再提交：

[oracle@db1 denver]$ mv undotbs01.dbf undotbs01.dbf.lll

SQL> commit;

Commit complete.

SQL> 

成功提交，之所以成功，是因为buffer cache里脏数据的原因，dbwn进程及其懒惰。来看看alert日志：

Mon Oct 05 10:24:40 2015
Errors in file /u01/diag/rdbms/denver/denver/trace/denver_j000_2536.trc:
ORA-12012: error on auto execute of job 3
ORA-01116: error in opening database file 3
ORA-01110: data file 3: '/u01/oradata/denver/undotbs01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/diag/rdbms/denver/denver/trace/denver_j000_2536.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01116: error in opening database file 3
ORA-01110: data file 3: '/u01/oradata/denver/undotbs01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-12012: error on auto execute of job 3
ORA-01116: error in opening database file 3
ORA-01110: data file 3: '/u01/oradata/denver/undotbs01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Mon Oct 05 10:24:41 2015
Checker run found 1 new persistent data failures

很明显，这又是一个数据库结构定时检查任务抛出的错误。再看看rollback是否成功：

SQL> insert into t1 select * from t1;

98304 rows created.

SQL> insert into t1 select * from t1;

196608 rows created.

SQL> rollback;

Rollback complete.

SQL> 

rollback也成功，原因很简单，数据块的old image还在内存里，没有被dbwn进程写入undo文件中，不过如此下去实例最终会崩溃，因为buffer cache始终有不够用的时候。

根据前面system数据文件的试验，我知道如果我执行检查点，那么实例会崩溃：

SQL> alter system checkpoint;
alter system checkpoint
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 2499
Session ID: 191 Serial number: 3

SQL> 

道理和system数据文件丢失一样，来看看alert日志：

Mon Oct 05 10:33:49 2015
Errors in file /u01/diag/rdbms/denver/denver/trace/denver_ckpt_2433.trc:
ORA-01242: data file suffered media failure: database in NOARCHIVELOG mode
ORA-01116: error in opening database file 3
ORA-01110: data file 3: '/u01/oradata/denver/undotbs01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/diag/rdbms/denver/denver/trace/denver_ckpt_2433.trc:
ORA-01242: data file suffered media failure: database in NOARCHIVELOG mode
ORA-01116: error in opening database file 3
ORA-01110: data file 3: '/u01/oradata/denver/undotbs01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
CKPT (ospid: 2433): terminating the instance due to error 1242
Instance terminated by CKPT, pid = 2433

实例果然崩溃。看看trace，发现是ckpt进程在触发检查点事件时，会提前做一次DB_STRUCTURE_INTEGRITY_CHECK，从而失败。实例最终被终止。

直接startup数据库，即可恢复（前提是redo还在）。

下面看看这两则数据库的恢复细节，都是直接startup数据库就可以了，但是alert日志里面给出了细节：

system数据文件实例恢复：

Completed: alter database mount
alter database open
Beginning crash recovery of 1 threads
 parallel recovery started with 3 processes
Started redo scan
Completed redo scan
 read 28 KB redo, 34 data blocks need recovery
Started redo application at
 Thread 1: logseq 33, block 3446
Recovery of Online Redo Log: Thread 1 Group 3 Seq 33 Reading mem 0
  Mem# 0: /u01/oradata/denver/redo03.rdo
Completed redo application of 0.01MB
Completed crash recovery at
 Thread 1: logseq 33, block 3502, scn 1395795
 34 data blocks read, 0 data blocks written, 28 redo k-bytes read
Thread 1 advanced to log sequence 34 (thread open)
Thread 1 opened at log sequence 34
  Current log# 1 seq# 34 mem# 0: /u01/oradata/denver/redo01.rdo
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Mon Oct 05 10:21:31 2015
SMON: enabling cache recovery
Successfully onlined Undo Tablespace 2.
Verifying file header compatibility for 11g tablespace encryption..

undo数据文件实例恢复：

alter database open
Beginning crash recovery of 1 threads
 parallel recovery started with 3 processes
Started redo scan
Completed redo scan
 read 72 KB redo, 2 data blocks need recovery
Started redo application at
 Thread 1: logseq 34, block 19142
Recovery of Online Redo Log: Thread 1 Group 1 Seq 34 Reading mem 0
  Mem# 0: /u01/oradata/denver/redo01.rdo
Completed redo application of 0.00MB
Completed crash recovery at
 Thread 1: logseq 34, block 19286, scn 1416962
 2 data blocks read, 0 data blocks written, 72 redo k-bytes read
Mon Oct 05 10:40:27 2015
Thread 1 advanced to log sequence 35 (thread open)
Thread 1 opened at log sequence 35
  Current log# 2 seq# 35 mem# 0: /u01/oradata/denver/redo02.rdo
Successful open of redo thread 1

Oracle就是牛逼，我x。