一次由于关归档的‘小事’引发的‘事故’。。。。。(二)

/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
------------ --- --