ORACLE FAILURE ADVISE

最新推荐文章于 2021-04-08 07:56:54 发布

keepdba

最新推荐文章于 2021-04-08 07:56:54 发布

阅读量1k

点赞数

分类专栏： Oracle 文章标签：数据库 oracle 故障

本文链接：https://blog.csdn.net/yitu1314/article/details/72772359

版权

Oracle 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

Oracle数据故障恢复指导是内置工具，可自动诊断数据故障并报告相应的修复选项。

环境说明
数据库版本：11.2.0.4

rman登入

[oracle@vastdata1 ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Fri May 26 22:32:07 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PROD (DBID=356856088)

备份数据库

RMAN> backup database;

Starting backup at 2017-05-26 22:32:15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00003 name=/u01/app/oracle/oradata/PROD/undotbs01.dbf
input datafile file number=00001 name=/u01/app/oracle/oradata/PROD/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/PROD/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/PROD/test.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/PROD/users01.dbf
channel ORA_DISK_1: starting piece 1 at 2017-05-26 22:32:17
channel ORA_DISK_1: finished piece 1 at 2017-05-26 22:34:44
piece handle=/u01/app/oracle/fast_recovery_area/PROD/backupset/2017_05_26/o1_mf_nnndf_TAG20170526T223216_dljh3l96_.bkp tag=TAG20170526T223216 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:27
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 2017-05-26 22:34:45
channel ORA_DISK_1: finished piece 1 at 2017-05-26 22:34:46
piece handle=/u01/app/oracle/fast_recovery_area/PROD/backupset/2017_05_26/o1_mf_ncsnf_TAG20170526T223216_dljh85tp_.bkp tag=TAG20170526T223216 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2017-05-26 22:34:46

查看备份

RMAN> list backup;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ -------------------
1       Full    1.55G      DISK        00:02:21     2017-05-26 22:34:37
        BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: TAG20170526T223216
        Piece Name: /u01/app/oracle/fast_recovery_area/PROD/backupset/2017_05_26/o1_mf_nnndf_TAG20170526T223216_dljh3l96_.bkp
  List of Datafiles in backup set 1
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  1       Full 842013     2017-05-26 22:32:17 /u01/app/oracle/oradata/PROD/system01.dbf
  2       Full 842013     2017-05-26 22:32:17 /u01/app/oracle/oradata/PROD/sysaux01.dbf
  3       Full 842013     2017-05-26 22:32:17 /u01/app/oracle/oradata/PROD/undotbs01.dbf
  4       Full 842013     2017-05-26 22:32:17 /u01/app/oracle/oradata/PROD/users01.dbf
  5       Full 842013     2017-05-26 22:32:17 /u01/app/oracle/oradata/PROD/test.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ -------------------
2       Full    9.36M      DISK        00:00:02     2017-05-26 22:34:46
        BP Key: 2   Status: AVAILABLE  Compressed: NO  Tag: TAG20170526T223216
        Piece Name: /u01/app/oracle/fast_recovery_area/PROD/backupset/2017_05_26/o1_mf_ncsnf_TAG20170526T223216_dljh85tp_.bkp
  SPFILE Included: Modification time: 2017-05-26 22:33:30
  SPFILE db_unique_name: PROD
  Control File Included: Ckp SCN: 844278       Ckp time: 2017-05-26 22:34:44

删除数据文件test.dbf

SQL> ho rm /u01/app/oracle/oradata/PROD/test.dbf

重启数据库

SQL> shut abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1252663296 bytes
Fixed Size          2252824 bytes
Variable Size         788533224 bytes
Database Buffers      452984832 bytes
Redo Buffers            8892416 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/u01/app/oracle/oradata/PROD/test.dbf'

重新rman登入

[oracle@vastdata1 ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Fri May 26 22:57:23 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PROD (DBID=356856088, not open)

执行list failure，列出故障

RMAN> list failure;

using target database control file instead of recovery catalog
List of Database Failures
=========================

Failure ID Priority Status    Time Detected       Summary
---------- -------- --------- ------------------- -------
2          HIGH     OPEN      2017-05-26 22:56:54 One or more non-system datafiles are missing

执行advise failure，Oracle给出的故障修复建议

RMAN> advise failure;

List of Database Failures
=========================

Failure ID Priority Status    Time Detected       Summary
---------- -------- --------- ------------------- -------
2          HIGH     OPEN      2017-05-26 22:56:54 One or more non-system datafiles are missing

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 device type=DISK
analyzing automatic repair options complete

Mandatory Manual Actions
========================
no manual actions available

Optional Manual Actions
=======================
1. If file /u01/app/oracle/oradata/PROD/test.dbf was unintentionally renamed or moved, restore it

Automated Repair Options
========================
Option Repair Description
------ ------------------
1      Restore and recover datafile 5  
  Strategy: The repair includes complete media recovery with no data loss
  Repair script: /u01/app/oracle/diag/rdbms/prod/PROD/hm/reco_1058692893.hm

执行repair failure，修复故障

RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/prod/PROD/hm/reco_1058692893.hm

contents of repair script:
   # restore and recover datafile
   restore datafile 5;
   recover datafile 5;
   sql 'alter database datafile 5 online';

Do you really want to execute the above repair (enter YES or NO)? yes
executing repair script

Starting restore at 2017-05-26 22:58:19
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/PROD/test.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/PROD/backupset/2017_05_26/o1_mf_nnndf_TAG20170526T223216_dljh3l96_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/PROD/backupset/2017_05_26/o1_mf_nnndf_TAG20170526T223216_dljh3l96_.bkp tag=TAG20170526T223216
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 2017-05-26 22:58:20

Starting recover at 2017-05-26 22:58:20
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 2017-05-26 22:58:20

sql statement: alter database datafile 5 online
repair failure complete

Do you want to open the database (enter YES or NO)? no

再次list failure

RMAN> list failure;

no failures found that match specification

打开数据库

RMAN> alter database open;

database opened

end~

keepdba

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
ORACLE FAILURE ADVISE

Oracle数据故障恢复指导是内置工具，可自动诊断数据故障并报告相应的修复选项。环境说明数据库版本：11.2.0.4 rman登入[oracle@vastdata1 ~]$ rman target /Recovery Manager: Release 11.2.0.4.0 - Production on Fri May 26 22:32:07 2017Copyright (c) 1982,
复制链接

扫一扫