在归档模式下，恢复一个被offline drop的datafile的方法

最新推荐文章于 2021-11-18 17:52:54 发布

翻译最新推荐文章于 2021-11-18 17:52:54 发布 · 2.2k 阅读

文章标签：

#datafile #offline #drop #archivelog

Oracle DB 专栏收录该内容

772 篇文章

订阅专栏

本文演示了如何在Oracle 11g数据库归档模式下，通过离线删除(datafile offline drop)并重新恢复一个数据文件的过程。文章详细记录了使用SQL*Plus进行的一系列操作，包括将数据文件置为离线状态、切换日志文件、应用归档日志进行恢复等步骤。

参考自：
HOW TO RECOVER OFFLINE DROPPED DATAFILE IN ARCHIVELOG MODE (文档 ID 286355.1)

如下的实验基于oracle 11.2.0.4 linux x86-64bit完成

[oracle@rhel63single u02]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sun Feb 15 20:33:17 2015
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u02/arch
Oldest online log sequence     126
Next log sequence to archive   128
Current log sequence           128
SQL> select file_id from dba_data_files;

   FILE_ID
----------
         4
         3
         2
         1
         5
         6
         7
         8
         9
        10
        11

11 rows selected.

SQL> select name from v$dbfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/test/users01.dbf
/u01/app/oracle/oradata/test/undotbs01.dbf
/u01/app/oracle/oradata/test/sysaux01.dbf
/u01/app/oracle/oradata/test/system01.dbf
/u01/app/oracle/oradata/test/ten01.dbf
/u01/app/oracle/oradata/test/tb_test_01.dbf
/u01/app/oracle/oradata/test/ts1.dbf
/u01/app/oracle/oradata/test/ts2.dbf
/u01/app/oracle/oradata/test/test01.dbf
/u01/app/oracle/oradata/test/test_uni_sz_2m_01.dbf
/u01/app/oracle/oradata/test/test_uni_sz_1m_01.dbf

11 rows selected.

SQL> set lines 290
SQL> col file_name format a60
SQL> select FILE_NAME,file_Id from v$dbfile;

FILE_NAME                                                       FILE_ID
------------------------------------------------------------ ----------
/u01/app/oracle/oradata/test/users01.dbf                              4
/u01/app/oracle/oradata/test/undotbs01.dbf                            3
/u01/app/oracle/oradata/test/sysaux01.dbf                             2
/u01/app/oracle/oradata/test/system01.dbf                             1
/u01/app/oracle/oradata/test/ten01.dbf                                5
/u01/app/oracle/oradata/test/tb_test_01.dbf                           6
/u01/app/oracle/oradata/test/ts1.dbf                                  7
/u01/app/oracle/oradata/test/ts2.dbf                                  8
/u01/app/oracle/oradata/test/test01.dbf                               9
/u01/app/oracle/oradata/test/test_uni_sz_2m_01.dbf                   10
/u01/app/oracle/oradata/test/test_uni_sz_1m_01.dbf                   11

11 rows selected.

SQL> alter database datafile 9 offline drop;

Database altered.

SQL> select file#, status from v$datafile where file#='9';

     FILE# STATUS
---------- -------
         9 RECOVER

SQL> select file#, status from v$datafile_header where file#='9';

     FILE# STATUS
---------- -------
         9 OFFLINE

SQL> alter system switch logfile ;

System altered.

SQL> /

System altered.

SQL> /
/
/

System altered.

SQL> 
System altered.

SQL> 

System altered.

SQL> SQL> 
SQL> 
SQL> /

System altered.

SQL> 
SQL> 
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u02/arch
Oldest online log sequence     132
Next log sequence to archive   134
Current log sequence           134
SQL> recover datafile 9;
ORA-00279: change 3155176 generated at 02/15/2015 20:34:05 needed for thread 1
ORA-00289: suggestion : /u02/arch/1_128_807882551.dbf
ORA-00280: change 3155176 for thread 1 is in sequence #128


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto --------------->敲入auto
Log applied.
Media recovery complete.
SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
         1          1        133   52428800        512          1 YES INACTIVE               3155684 15-FEB-15      3155687 15-FEB-15
         2          1        134   52428800        512          1 NO  CURRENT                3155687 15-FEB-15   2.8147E+14
         3          1        132   52428800        512          1 YES INACTIVE               3155681 15-FEB-15      3155684 15-FEB-15

SQL> select file#,status from v$datafile where file#=9;

     FILE# STATUS
---------- -------
         9 OFFLINE

SQL> select file#,status from v$datafile_header where file#=9;

     FILE# STATUS
---------- -------
         9 OFFLINE

SQL> alter database datafile 9 online;

Database altered.

SQL> select file#,status from v$datafile where file#=9;

     FILE# STATUS
---------- -------
         9 ONLINE

SQL>

知识点：
1.The only case in which the offline dropped datafile can not be online is
when you have added to many datafiles in the database after offline drop

2.在非归档模式下，为了让一个datafile 变成offline，必须带drop关键字。
drop关键字不会把datafile从database 中 remove掉。
To do that, you must drop the tablespace in which the datafile resides. Until you
do so, the datafile remains in the data dictionary with the status RECOVER or OFFLINE.

若是database处于归档模式，Oracle会忽略掉drop 关键字.