ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 数据文件OFFLINE的3种情况

数据文件OFFLINE的3种情况

原创 Linux操作系统 作者:anycall2010 时间:2009-03-29 10:30:57 0 删除 编辑

前言:

1、测试前对数据库进行一个完整备份

RMAN> backup database;

Starting backup at 28-MAR-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=143 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/oracle/oradata/dg1/system01.dbf
input datafile fno=00003 name=/oracle/oradata/dg1/sysaux01.dbf
input datafile fno=00005 name=/oracle/oradata/dg1/example01.dbf
input datafile fno=00006 name=/oracle/oradata/dg1/ws_app_data01.dbf
input datafile fno=00007 name=/oracle/oradata/dg1/ws_app_idx01.dbf
input datafile fno=00002 name=/oracle/oradata/dg1/undotbs01.dbf
input datafile fno=00008 name=/oracle/oradata/dg1/ws_app_sep01.dbf
input datafile fno=00009 name=/oracle/oradata/dg1/ws_app_oct01.dbf
input datafile fno=00010 name=/oracle/oradata/dg1/ws_app_nov01.dbf
input datafile fno=00011 name=/oracle/oradata/dg1/ws_app_dec01.dbf
input datafile fno=00012 name=/oracle/oradata/dg1/wdscr_part01.dbf
input datafile fno=00013 name=/oracle/oradata/dg1/wdscr_part02.dbf
input datafile fno=00004 name=/oracle/oradata/dg1/users01.dbf
channel ORA_DISK_1: starting piece 1 at 28-MAR-09
channel ORA_DISK_1: finished piece 1 at 28-MAR-09
piece handle=/oracle/flash_recovery_area/DG1/backupset/2009_03_28/o1_mf_nnndf_TAG20090328T201934_4wxhnr1f_.bkp tag=TAG20090328T201934 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:46
Finished backup at 28-MAR-09

Starting Control File and SPFILE Autobackup at 28-MAR-09
piece handle=/oracle/flash_recovery_area/DG1/autobackup/2009_03_28/o1_mf_s_682719681_4wxhr320_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 28-MAR-09


2、归档模式下OFFLINE 数据文件

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     10
Next log sequence to archive   12
Current log sequence           12

SQL> conn sys/oracle as sysdba
Connected.
SQL> alter database datafile '/oracle/oradata/dg1/wdscr_part01.dbf' offline drop;

Database altered.

SQL> conn ws_app/ws_app
Connected.
SQL> select * from woodscrew;
select * from woodscrew
              *
ERROR at line 1:
ORA-00376: file 12 cannot be read at this time
ORA-01110: data file 12: '/oracle/oradata/dg1/wdscr_part01.dbf'

归档模式下,日志都存在,因此直接介质恢复,将数据文件ONLINE就可以了。

SQL> conn sys/oracle as sysdba
Connected.
SQL> recover datafile 12;
Media recovery complete.
SQL> alter database datafile '/oracle/oradata/dg1/wdscr_part01.dbf' online;

Database altered.

3、非归档联机日志存在情况下恢复

其实非归档在日志组没有被覆盖之前和归档情况原理上是一样的。

SQL> alter database noarchivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     10
Current log sequence           12

SQL> alter database datafile '/oracle/oradata/dg1/wdscr_part01.dbf' offline drop;

Database altered.

SQL> conn ws_app/ws_app;
Connected.
SQL> select * from woodscrew;
select * from woodscrew
              *
ERROR at line 1:
ORA-00376: file 12 cannot be read at this time
ORA-01110: data file 12: '/oracle/oradata/dg1/wdscr_part01.dbf'
SQL> conn sys/oracle as sysdba
Connected.
SQL> recover datafile 12;
Media recovery complete.
SQL> alter database datafile '/oracle/oradata/dg1/wdscr_part01.dbf' online;

Database altered.

4、非归档联机日志不存在情况下恢复

很不幸,如果在没有归档日志,且联机日志被覆盖的情况,要介质恢复了。

SQL> conn sys/oracle as sysdba
Connected.
SQL> alter database datafile '/oracle/oradata/dg1/wdscr_part01.dbf' offline drop;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;
 
System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> recover datafile 12;
ORA-00279: change 711334 generated at 03/28/2009 20:45:36 needed for thread 1
ORA-00289: suggestion :
/oracle/flash_recovery_area/DG1/archivelog/2009_03_28/o1_mf_1_12_%u_.arc
ORA-00280: change 711334 for thread 1 is in sequence #12


Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log
'/oracle/flash_recovery_area/DG1/archivelog/2009_03_28/o1_mf_1_12_%u_.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3


ORA-00308: cannot open archived log
'/oracle/flash_recovery_area/DG1/archivelog/2009_03_28/o1_mf_1_12_%u_.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

下面做RMAN的恢复:

[oracle@dg1 ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Sat Mar 28 20:53:51 2009

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

connected to target database: DG1 (DBID=1646839347, not open)

RMAN> restore database;

Starting restore at 28-MAR-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /oracle/oradata/dg1/system01.dbf
restoring datafile 00002 to /oracle/oradata/dg1/undotbs01.dbf
restoring datafile 00003 to /oracle/oradata/dg1/sysaux01.dbf
restoring datafile 00004 to /oracle/oradata/dg1/users01.dbf
restoring datafile 00005 to /oracle/oradata/dg1/example01.dbf
restoring datafile 00006 to /oracle/oradata/dg1/ws_app_data01.dbf
restoring datafile 00007 to /oracle/oradata/dg1/ws_app_idx01.dbf
restoring datafile 00008 to /oracle/oradata/dg1/ws_app_sep01.dbf
restoring datafile 00009 to /oracle/oradata/dg1/ws_app_oct01.dbf
restoring datafile 00010 to /oracle/oradata/dg1/ws_app_nov01.dbf
restoring datafile 00011 to /oracle/oradata/dg1/ws_app_dec01.dbf
restoring datafile 00012 to /oracle/oradata/dg1/wdscr_part01.dbf
restoring datafile 00013 to /oracle/oradata/dg1/wdscr_part02.dbf
channel ORA_DISK_1: reading from backup piece /oracle/flash_recovery_area/DG1/backupset/2009_03_28/o1_mf_nnndf_TAG20090328T201934_4wxhnr1f_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle/flash_recovery_area/DG1/backupset/2009_03_28/o1_mf_nnndf_TAG20090328T201934_4wxhnr1f_.bkp tag=TAG20090328T201934
channel ORA_DISK_1: restore complete, elapsed time: 00:01:36
Finished restore at 28-MAR-09

RMAN> exit

Recovery Manager complete.

SQL> alter database open resetlogs;

Database altered.

SQL> conn ws_app/ws_app
Connected.
SQL> select * from woodscrew;
select * from woodscrew
              *
ERROR at line 1:
ORA-00376: file 12 cannot be read at this time
ORA-01110: data file 12: '/oracle/oradata/dg1/wdscr_part01.dbf'


SQL> conn sys/oracle as sysdba
Connected.
SQL> recover datafile 12;
Media recovery complete.
SQL> alter datafile '/oracle/oradata/dg1/wdscr_part01.dbf' online;
alter datafile '/oracle/oradata/dg1/wdscr_part01.dbf' online
      *
ERROR at line 1:
ORA-00940: invalid ALTER command


SQL> alter database datafile '/oracle/oradata/dg1/wdscr_part01.dbf' online;

Database altered.

SQL> conn ws_app/ws_app
Connected.
SQL> select * from woodscrew;

    SCR_ID MANUFACTR_ID         SCR_TYPE             THREAD_CNT     LENGTH
---------- -------------------- -------------------- ---------- ----------
HEAD_CONFIG
--------------------
      1000 Tommy Hardware       Finish                       30        1.5
Phillips

      1000 Balaji Parts,Inc.    Finish                       30        1.5
Phillips

      1003 Tommy Hardware       Finish                       20          1
Phillips


    SCR_ID MANUFACTR_ID         SCR_TYPE             THREAD_CNT     LENGTH
---------- -------------------- -------------------- ---------- ----------
HEAD_CONFIG
--------------------
      1003 Balaji Parts,Inc.    Finish                       20          1
Phillips

      1004 Tommy Hardware       Finish                       30          2
Phillips

      1004 Balaji Parts,Inc.    Finish                       30          2
Phillips


    SCR_ID MANUFACTR_ID         SCR_TYPE             THREAD_CNT     LENGTH
---------- -------------------- -------------------- ---------- ----------
HEAD_CONFIG
--------------------
      1001 Tommy Hardware       Finish                       30          1
Phillips

      1001 Balaji Parts,Inc.    Finish                       30          1
Phillips

      1002 Tommy Hardware       Finish                       20        1.5
Phillips


    SCR_ID MANUFACTR_ID         SCR_TYPE             THREAD_CNT     LENGTH
---------- -------------------- -------------------- ---------- ----------
HEAD_CONFIG
--------------------
      1002 Balaji Parts,Inc.    Finish                       20        1.5
Phillips

      1005 Tommy Hardware       Finish                       20          2
Phillips

      1005 Balaji Parts,Inc.    Finish                       20          2
Phillips


12 rows selected.

因此,数据库恢复完毕。基本遇到的也是这3种情况,因此在删除数据文件的时候,一定要小心。不要讲数据库至于危险境地。。。。。

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8334342/viewspace-580490/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-06-24

  • 博文量
    182
  • 访问量
    561889