一次由于关归档的‘小事’引发的‘事故’。。。。。-mysql教程

一次由于关归档的 ‘小事’ 引发的 ‘ 事故 ’。。。。。 今天在一个客户那做巡检是碰到了一个有趣的问题，差点把自己给吓死。 因客户要求关掉一个库的归档，如此easy，但是，but，出了一个问题，差点吓死。。。 模拟故障报错如下： SQL startup mount ORACL

今天在一个客户那做巡检是碰到了一个有趣的问题，差点把自己给吓死。

因客户要求关掉一个库的归档，如此easy，但是，but，出了一个问题，差点吓死。。。

模拟故障报错如下：

SQL> startup mount
ORACLE instance started.

Total System Global Area 234344448 bytes
Fixed Size 1335696 bytes
Variable Size 142610032 bytes
Database Buffers 88080384 bytes
Redo Buffers 2318336 bytes
Database mounted.
SQL> alter database noarchivelog;
alter database noarchivelog
*
ERROR at line 1:
ORA-01143: cannot disable media recovery - file 7 needs media recovery
ORA-01110: data file 7:
'/u01/app/oracle/oradata/MYNEWDB/datafile/o1_mf_admin_tb_8y6kbmb4_.dbf'

数据库没有备份策略，之前的归档空间就10g左右，客户在db hang住是手工去删归档，由于之前在登入数据前已发现此库应用压力有点大，但是当时也没多想。于是直接一个alter system checkpoint；先切了几次。然后再shutdown immediate： 但是半天没反应。客户又在催，于是乎，手工cancel 了，接着一个shutdown abort：再startup一把，然后再shutdown immediate：这是后台 alert。log 里没有任何报错，然后接着再startup mount： 开始关归档。就出现上述报错：

SQL> alter database noarchivelog;
alter database noarchivelog
*
ERROR at line 1:
ORA-01143: cannot disable media recovery - file 7 needs media recovery
ORA-01110: data file 7:
'/u01/app/oracle/oradata/MYNEWDB/datafile/o1_mf_admin_tb_8y6kbmb4_.dbf'

这下彻底吓坏了，应为见过在搞压力下，由于shutdown abort 后数据库器不来的case，而且，更吓人的是这个库没有任何备份，最经24的归档都刚刚给删了，这是瞬间，bbed dul。。。全跳到我脑门，but 这个屠龙之技哪敢在客户的生产上用，。。。。。。。。各种忐忑中。。。。。。

!!!!!!!注意：现在回想起来，以后打死也不随便在业务高峰期间敲shutdown 命令了。需要根据当前数据库系统实际情况，及先查明系统负载情况，在提出合理建议，说明可能存在的风险情况，让客户在业务低峰期，再做某些操作。

后来，重新检查数据文件的情况信息，搞清楚了是什么原因。

以下为整个问题的模拟再现：

当数据库中有数据文件在数据库open是 offline 时，这时再去禁用归档是不允许的。以下为测试文档：

[root@oracle11g ~]# !su
su - oracle
![oracle@oracle11g ~]$ !sql
sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jan 15 04:33:23 2014

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> desc dba_data_files;
Name Null? Type
----------------------------------------- -------- ----------------------------
FILE_NAME VARCHAR2(513)
FILE_ID NUMBER
TABLESPACE_NAME VARCHAR2(30)
BYTES NUMBER
BLOCKS NUMBER
STATUS VARCHAR2(9)
RELATIVE_FNO NUMBER
AUTOEXTENSIBLE VARCHAR2(3)
MAXBYTES NUMBER
MAXBLOCKS NUMBER
INCREMENT_BY NUMBER
USER_BYTES NUMBER
USER_BLOCKS NUMBER
ONLINE_STATUS VARCHAR2(7)

SQL> select file_name, file_id ,status from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
FILE_ID STATUS
---------- ---------
/u01/app/oracle/oradata/MYNEWDB/datafile/o1_mf_admin_tb_8y6k9o20_.dbf
6 AVAILABLE

/u01/app/oracle/oradata/MYNEWDB/datafile/o1_mf_users_814jk5bg_.dbf
4 AVAILABLE

/u01/app/oracle/oradata/MYNEWDB/datafile/o1_mf_undotbs1_814jk569_.dbf
3 AVAILABLE
/u01/app/oracle/oradata/MYNEWDB/datafile/o1_mf_sysaux_814jk53d_.dbf
2 AVAILABLE

/u01/app/oracle/oradata/MYNEWDB/datafile/o1_mf_system_814jk4rs_.dbf
1 AVAILABLE

/u01/app/oracle/oradata/MYNEWDB/datafile/o1_mf_example_814jq4jj_.dbf
5 AVAILABLE
/u01/app/oracle/oradata/MYNEWDB/datafile/o1_mf_admin_tb_8y6kbmb4_.dbf
7 AVAILABLE

7 rows selected.

先将数据文件7将数据离线：
SQL> alter database datafile 7 offline;

Database altered.

SQL> select file_name, file_id ,status from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
FILE_ID STATUS
---------- ---------
/u01/app/oracle/oradata/MYNEWDB/datafile/o1_mf_admin_tb_8y6k9o20_.dbf
6 AVAILABLE

/u01/app/oracle/oradata/MYNEWDB/datafile/o1_mf_users_814jk5bg_.dbf
4 AVAILABLE

/u01/app/oracle/oradata/MYNEWDB/datafile/o1_mf_undotbs1_814jk569_.dbf
3 AVAILABLE
/u01/app/oracle/oradata/MYNEWDB/datafile/o1_mf_sysaux_814jk53d_.dbf
2 AVAILABLE

/u01/app/oracle/oradata/MYNEWDB/datafile/o1_mf_system_814jk4rs_.dbf
1 AVAILABLE

/u01/app/oracle/oradata/MYNEWDB/datafile/o1_mf_example_814jq4jj_.dbf
5 AVAILABLE
/u01/app/oracle/oradata/MYNEWDB/datafile/o1_mf_admin_tb_8y6kbmb4_.dbf
7 AVAILABLE

7 rows selected.

SQL> select * from v$recover_file;

FILE# ONLINE ONLINE_
---------- ------- -------
ERROR CHANGE#
----------------------------------------------------------------- ----------
TIME
---------
7 OFFLINE OFFLINE
4111255
13-JAN-14

SQL>
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/test
Oldest online log sequence 45
Next log sequence to archive 47
Current log sequence 47
SQL>

当前环境为归档模式，在数据库open状态下将数据文件7离线。
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 234344448 bytes
Fixed Size 1335696 bytes
Variable Size 142610032 bytes
Database Buffers 88080384 bytes
Redo Buffers 2318336 bytes
Database mounted.

禁用数据库归档模式：
SQL> alter database noarchivelog;
alter database noarchivelog
*
ERROR at line 1:
ORA-01143: cannot disable media recovery - file 7 needs media recovery
ORA-01110: data file 7:
'/u01/app/oracle/oradata/MYNEWDB/datafile/o1_mf_admin_tb_8y6kbmb4_.dbf'

对离线的数据文件进行恢复：
SQL> startup force
ORACLE instance started.

Total System Global Area 234344448 bytes
Fixed Size 1335696 bytes
Variable Size 142610032 bytes
Database Buffers 88080384 bytes
Redo Buffers 2318336 bytes
Database mounted.
Database opened.
SQL> recover datafile 7;
ORA-00279: change 4111255 generated at 01/13/2014 08:47:27 needed for thread 1
ORA-00289: suggestion : /home/oracle/test/1_47_832034536.arc
ORA-00280: change 4111255 for thread 1 is in sequence #47

Specify log: {=suggested | filename | AUTO | CANCEL}
auto
Log applied.
Media recovery complete.

这里可以看出需要一个归档日志文件来恢复数据文件7，因此，如果某个数据文件在过去某个时间点在数据库处于open模式下，且归档是开了的情况下，被offline了。如果这时没有备份，且历史归档都不完整存在的话。对于这个

数据文件只能采用非常规方式恢复了。而这时如果必须要关掉归档的话，只能drop掉相应的表空间了。

SQL> select * from v$recover_file;

no rows selected

SQL>

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 234344448 bytes
Fixed Size 1335696 bytes
Variable Size 142610032 bytes
Database Buffers 88080384 bytes
Redo Buffers 2318336 bytes
Database mounted.

SQL> alter database noarchivelog;

Database altered.

SQL> select * from v$recover_file;

no rows selected

SQL> select * from dba_data_files;
select * from dba_data_files
*
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only

SQL> alter database open;

Database altered.

SQL> select * from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
ONLINE_
-------
/u01/app/oracle/oradata/MYNEWDB/datafile/o1_mf_admin_tb_8y6k9o20_.dbf
6 ADMIN_TBS 104857600 12800 AVAILABLE
6 NO 0 0 0 103809024 12672
ONLINE

FILE_NAME
--------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
ONLINE_
-------
/u01/app/oracle/oradata/MYNEWDB/datafile/o1_mf_users_814jk5bg_.dbf
4 USERS AVAILABLE
4
ONLINE

FILE_NAME
--------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
ONLINE_
-------
/u01/app/oracle/oradata/MYNEWDB/datafile/o1_mf_undotbs1_814jk569_.dbf
3 UNDOTBS1 89128960 10880 AVAILABLE
3 YES 3.4360E&＃43;10 4194302 640 88080384 10752
ONLINE

FILE_NAME
--------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
ONLINE_
-------
/u01/app/oracle/oradata/MYNEWDB/datafile/o1_mf_sysaux_814jk53d_.dbf
2 SYSAUX 660602880 80640 AVAILABLE
2 YES 3.4360E&＃43;10 4194302 1280 659554304 80512
ONLINE

FILE_NAME
--------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
ONLINE_
-------
/u01/app/oracle/oradata/MYNEWDB/datafile/o1_mf_system_814jk4rs_.dbf
1 SYSTEM 786432000 96000 AVAILABLE
1 YES 3.4360E&＃43;10 4194302 1280 785383424 95872
SYSTEM

FILE_NAME
--------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
ONLINE_
-------
/u01/app/oracle/oradata/MYNEWDB/datafile/o1_mf_example_814jq4jj_.dbf
5 EXAMPLE 104857600 12800 AVAILABLE
5 YES 3.4360E&＃43;10 4194302 80 103809024 12672
ONLINE

FILE_NAME
--------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
ONLINE_
-------
/u01/app/oracle/oradata/MYNEWDB/datafile/o1_mf_admin_tb_8y6kbmb4_.dbf
7 ADMIN_TBS2 AVAILABLE
7
OFFLINE

7 rows selected.

SQL> select * from v$recover_file;

no rows selected

SQL> alter database datafile 7 online;

Database altered.

SQL> select * from v$recover_file;

no rows selected

在shutdown abort 下，且非归档模式offline数据文件： 结论如下

SQL> shutdown abort
ORACLE instance shut down.
SQL> alter database datafile 7 offline;
alter database datafile 7 offline
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 12122
Session ID: 1 Serial number: 5

SQL> startup mount
ORACLE instance started.

Total System Global Area 234344448 bytes
Fixed Size 1335696 bytes
Variable Size 142610032 bytes
Database Buffers 88080384 bytes
Redo Buffers 2318336 bytes
Database mounted.
SQL> alter database datafile 7 offline;
alter database datafile 7 offline
*
ERROR at line 1:
ORA-01145: offline immediate disallowed unless media recovery enabled

SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /home/oracle/test
Oldest online log sequence 54
Current log sequence 56
SQL>