[bbk5366] 第87集 -第11章 -数据库诊断 03

　　说明：oracle 11g才拥有数据库恢复专家功能

　　实验目的：使用数据库恢复专家恢复数据库

　　实验步骤：

1. tablespace--app_data
2. backup---datafile-app_data_01.dbf-rman
3. 破坏
4. recover-data recover advisor-rman

1、准备实验环境

　　创建表空间APP_DATA

　　创建用户TEST

SQL> CREATE TABLESPACE APP_DATA DATAFILE  'D:\APP\MARYHU\ORADATA\ARCERZHANGDB\APP_DATA_01.dbf' SIZE 200M AUTOEXTEND ON NEXT 10M MAXSIZE 500M;

Tablespace created.

SQL> CREATE USER TEST IDENTIFIED BY TEST DEFAULT TABLESPACE APP_DATA;

User created.

SQL> GRANT CONNECT,RESOURCE TO TEST;

Grant succeeded.

SQL> CONN TEST/TEST;
Connected.
SQL> select * from tab;

no rows selected

2、使用RMAN，进行数据文件备份

RMAN> report schema;

Report of database schema for database with db_unique_name ARCERZHANGDB

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    750      SYSTEM               ***     D:\APP\MARYHU\ORADATA\ARCERZHANGDB\SYSTEM01.DBF
2    720      SYSAUX               ***     D:\APP\MARYHU\ORADATA\ARCERZHANGDB\SYSAUX01.DBF
3    300      UNDOTBS1             ***     D:\APP\MARYHU\ORADATA\ARCERZHANGDB\UNDOTBS01.DBF
4    38       USERS                ***     D:\APP\MARYHU\ORADATA\ARCERZHANGDB\USERS01.DBF
5    100      EXAMPLE              ***     D:\APP\MARYHU\ORADATA\ARCERZHANGDB\EXAMPLE01.DBF
6    100      ASSM                 ***     D:\APP\MARYHU\ORADATA\ARCERZHANGDB\ASSM_1.DBF
7    100      MSSM                 ***     D:\APP\MARYHU\ORADATA\ARCERZHANGDB\MSSM_1.DBF
8    200      APP_DATA             ***     D:\APP\MARYHU\ORADATA\ARCERZHANGDB\APP_DATA_01.DBF

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    29       TEMP                 32767       D:\APP\MARYHU\ORADATA\ARCERZHANGDB\TEMP01.DBF

run{

　　allocate channel d1 type disk format 'D:\app\MaryHu\oradata\backup\datafile_app_data_01_%s_%p.bak';

　　backup datafile 8;　　

}

RMAN> run{
2> allocate channel d1 type disk format 'D:\app\MaryHu\oradata\backup\datafile_%s_%p.bus';
3> backup datafile 8;
4> }

released channel: ORA_DISK_1
allocated channel: d1
channel d1: SID=70 device type=DISK

Starting backup at 05-JUN-13
channel d1: starting full datafile backup set
channel d1: specifying datafile(s) in backup set
input datafile file number=00008 name=D:\APP\MARYHU\ORADATA\ARCERZHANGDB\APP_DATA_01.DBF
channel d1: starting piece 1 at 05-JUN-13
channel d1: finished piece 1 at 05-JUN-13
piece handle=D:\APP\MARYHU\ORADATA\BACKUP\DATAFILE_5_1.BUS tag=TAG20130605T132340 comment=NONE
channel d1: backup set complete, elapsed time: 00:00:01
Finished backup at 05-JUN-13
released channel: d1

3、关闭数据库，然后删除数据库文件app_data_01.dbf，然后再重新启动数据库，将会报错

C:\Users\MaryHu>sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jun 5 13:26:55 2013

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

SQL> conn /as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area  778387456 bytes
Fixed Size                  1374808 bytes
Variable Size             377488808 bytes
Database Buffers          394264576 bytes
Redo Buffers                5259264 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
ORA-01110: data file 8: 'D:\APP\MARYHU\ORADATA\ARCERZHANGDB\APP_DATA_01.DBF'

4、开始使用数据库恢复专家，开展恢复工作

C:\Users\MaryHu>rman target SYS/arcerzhang_db168@DB168 NOCATALOG

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Jun 5 13:33:04 2013

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

connected to target database: ARCERZHA (DBID=3117207478, not open)
using target database control file instead of recovery catalog

RMAN> list failure;

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

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
122        HIGH     OPEN      05-JUN-13     One or more non-system datafiles are missing

RMAN> advise failure;

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

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
122        HIGH     OPEN      05-JUN-13     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=63 device type=DISK
analyzing automatic repair options complete

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

Optional Manual Actions
=======================
1. If file D:\APP\MARYHU\ORADATA\ARCERZHANGDB\APP_DATA_01.DBF was unintentionally renamed or moved, restore it

Automated Repair Options
========================
Option Repair Description
------ ------------------
1      Restore and recover datafile 8
  Strategy: The repair includes complete media recovery with no data loss
  Repair script: d:\app\maryhu\diag\rdbms\arcerzhangdb\arcerzhangdb\hm\reco_2686889394.hm

数据库恢复专家给出的修复建议脚本:

d:\app\maryhu\diag\rdbms\arcerzhangdb\arcerzhangdb\hm\reco_2686889394.hm

   # restore and recover datafile
   restore datafile 8;
   recover datafile 8

同样，我们也可以通过动态性能视图查看数据库修复专家给出修复建议信息

C:\Users\MaryHu>sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jun 5 13:43:36 2013

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

SQL> conn /as sysdba
Connected.

SQL> desc v$ir_repair;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 REPAIR_ID                                          NUMBER
 ADVISE_ID                                          NUMBER
 SUMMARY                                            VARCHAR2(32)
 RANK                                               NUMBER
 TIME_DETECTED                                      DATE
 EXECUTED                                           DATE
 ESTIMATED_DATA_LOSS                                VARCHAR2(20)
 DETAILED_DESCRIPTION                               VARCHAR2(1024)
 REPAIR_SCRIPT                                      VARCHAR2(512)
 ESTIMATED_REPAIR_TIME                              NUMBER
 ACTUAL_REPAIR_TIME                                 NUMBER
 STATUS                                             VARCHAR2(7)

SQL> select repair_id,advise_id,summary,rank,status from v$ir_repair;

 REPAIR_ID  ADVISE_ID SUMMARY                                RANK STATUS
---------- ---------- -------------------------------- ---------- -------
       142        141 NO DATA LOSS                              1 NOT RUN

SQL> desc v$ir_manula_checklist;
ERROR:
ORA-04043: object v$ir_manula_checklist does not exist


SQL> desc v$ir_manual_checklist;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ADVISE_ID                                          NUMBER
 RANK                                               NUMBER
 REQUIRED                                           VARCHAR2(3)
 MESSAGE                                            VARCHAR2(1024)

SQL> col message format a50;

SQL> select * from v$ir_manual_checklist;

 ADVISE_ID       RANK REQ MESSAGE
---------- ---------- --- --------------------------------------------------
       141          0 NO  If file D:\APP\MARYHU\ORADATA\ARCERZHANGDB\APP_DAT
                          A_01.DBF was unintentionally renamed or moved, res
                          tore it

开始使用数据库恢复专家，对数据库进行恢复操作

C:\Users\MaryHu>rman target SYS/arcerzhang_db168@DB168 NOCATALOG

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Jun 5 13:52:32 2013

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

connected to target database: ARCERZHA (DBID=3117207478, not open)
using target database control file instead of recovery catalog

RMAN> list failure;

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

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
122        HIGH     OPEN      05-JUN-13     One or more non-system datafiles are missing

RMAN> advise failure;

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

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
122        HIGH     OPEN      05-JUN-13     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=63 device type=DISK
analyzing automatic repair options complete

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

Optional Manual Actions
=======================
1. If file D:\APP\MARYHU\ORADATA\ARCERZHANGDB\APP_DATA_01.DBF was unintentionally renamed or moved, restore i

Automated Repair Options
========================
Option Repair Description
------ ------------------
1      Restore and recover datafile 8
  Strategy: The repair includes complete media recovery with no data loss
  Repair script: d:\app\maryhu\diag\rdbms\arcerzhangdb\arcerzhangdb\hm\reco_3363844532.hm

RMAN> repair failure preview;

Strategy: The repair includes complete media recovery with no data loss
Repair script: d:\app\maryhu\diag\rdbms\arcerzhangdb\arcerzhangdb\hm\reco_3363844532.hm

contents of repair script:
   # restore and recover datafile
   restore datafile 8;
   recover datafile 8;

RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss
Repair script: d:\app\maryhu\diag\rdbms\arcerzhangdb\arcerzhangdb\hm\reco_3363844532.hm

contents of repair script:
   # restore and recover datafile
   restore datafile 8;
   recover datafile 8;

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

Starting restore at 05-JUN-13
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 00008 to D:\APP\MARYHU\ORADATA\ARCERZHANGDB\APP_DATA_01.DBF
channel ORA_DISK_1: reading from backup piece D:\APP\MARYHU\ORADATA\BACKUP\DATAFILE_5_1.BUS
channel ORA_DISK_1: piece handle=D:\APP\MARYHU\ORADATA\BACKUP\DATAFILE_5_1.BUS tag=TAG20130605T132340
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
Finished restore at 05-JUN-13

Starting recover at 05-JUN-13
using channel ORA_DISK_1

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

Finished recover at 05-JUN-13
repair failure complete

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

注意：list failure;advise failure;repair failure;命令一定要处于同一个session当中，否则会出错.

修复完成之后，查看已经关闭级别的错误信息，即可查到刚才被修复的记录信息

RMAN> list failure closed;

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

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
5          CRITICAL CLOSED    22-JAN-13     System datafile 1: 'D:\APP\MARYHU\ORADATA\ARCERZHANGDB\SYSTEM01.DBF' needs media recovery
2          CRITICAL CLOSED    22-JAN-13     Control file needs media recovery
125        HIGH     CLOSED    05-JUN-13     Datafile 8: 'D:\APP\MARYHU\ORADATA\ARCERZHANGDB\APP_DATA_01.DBF' is missing
  Impact: Some objects in tablespace APP_DATA might be unavailable
122        HIGH     CLOSED    05-JUN-13     One or more non-system datafiles are missing
11         HIGH     CLOSED    22-JAN-13     Datafile 2: 'D:\APP\MARYHU\ORADATA\ARCERZHANGDB\SYSAUX01.DBF' needs media recovery
  Impact: Some objects in tablespace SYSAUX might be unavailable
8          HIGH     CLOSED    22-JAN-13     One or more non-system datafiles need media recovery

再次执行list failure命令，便不会再出现错误信息

RMAN> list failure;

no failures found that match specification

posted @ 2013-06-05 12:47 ArcerZhang 阅读(237) 评论(0) 编辑收藏举报

刷新页面返回顶部

ArcerZhang`s Technology Blog

走在技术的道路上，享受这个过程

[bbk5366] 第87集 -第11章 -数据库诊断 03

公告