[bbk5383] 第90集 第11章 数据库诊断 06

/*

　　实验目的:db_block corrupt recover

*/

  1. config
    1. archive log
    2. flashback
    3. parameter
  2. create tablespace---app_test
  3. backup-rman
  4. 破坏
  5. check block-dbv-rman
  6. startup
  7. recover block
  8. check block

1、config

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     194
Next log sequence to archive   196
Current log sequence           196
验证数据库是否处于归档模式
SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
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.
SQL> alter database flashback on;

Database altered.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

SQL> alter database open;

Database altered.
启用数据库闪回功能
SQL> show parameter db_block_checking

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_checking string FALSE
SQL> show parameter db_block_checksum

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_checksum string TYPICAL
SQL> show parameter db_lost

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_lost_write_protect string NONE
SQL> show parameter safe

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_ultra_safe string OFF

SQL> alter system set db_ultra_safe='DATA_AND_INDEX' scope=spfile;

System altered.

SQL> alter database open;

Database altered.

SQL> show parameter safe

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_ultra_safe string OFF

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.
Database opened.
SQL> show parameter safe

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_ultra_safe string DATA_AND_INDEX
db_ultra_safe参数设置
SQL> show parameter db_block_checking

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_checking                    string      FULL
SQL> show parameter db_block_checksum

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_checksum                    string      FULL
SQL> show parameter db_lost

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_lost_write_protect                string      TYPICAL
set db_ultra_safe='DATA_AND_INDEX'之后，再查看db_block相关参数信息

2、create tablespace---app_test

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
D:\APP\MARYHU\ORADATA\ARCERZHANGDB\SYSTEM01.DBF
D:\APP\MARYHU\ORADATA\ARCERZHANGDB\SYSAUX01.DBF
D:\APP\MARYHU\ORADATA\ARCERZHANGDB\UNDOTBS01.DBF
D:\APP\MARYHU\ORADATA\ARCERZHANGDB\USERS01.DBF
D:\APP\MARYHU\ORADATA\ARCERZHANGDB\EXAMPLE01.DBF
D:\APP\MARYHU\ORADATA\ARCERZHANGDB\ASSM_1.DBF
D:\APP\MARYHU\ORADATA\ARCERZHANGDB\MSSM_1.DBF
D:\APP\MARYHU\ORADATA\ARCERZHANGDB\APP_DATA_01.DBF

8 rows selected.

SQL> create tablespace app_test
  2  datafile 'D:\APP\MARYHU\ORADATA\ARCERZHANGDB\app_test01.dbf' size 20M;

Tablespace created.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
D:\APP\MARYHU\ORADATA\ARCERZHANGDB\SYSTEM01.DBF
D:\APP\MARYHU\ORADATA\ARCERZHANGDB\SYSAUX01.DBF
D:\APP\MARYHU\ORADATA\ARCERZHANGDB\UNDOTBS01.DBF
D:\APP\MARYHU\ORADATA\ARCERZHANGDB\USERS01.DBF
D:\APP\MARYHU\ORADATA\ARCERZHANGDB\EXAMPLE01.DBF
D:\APP\MARYHU\ORADATA\ARCERZHANGDB\ASSM_1.DBF
D:\APP\MARYHU\ORADATA\ARCERZHANGDB\MSSM_1.DBF
D:\APP\MARYHU\ORADATA\ARCERZHANGDB\APP_DATA_01.DBF
D:\APP\MARYHU\ORADATA\ARCERZHANGDB\APP_TEST01.DBF

9 rows selected.

SQL> create user user1
  2  identified by user1
  3  default tablespace app_test;

User created.

SQL> grant connect,resource to user1;

Grant succeeded.

SQL> conn user1/user1;
Connected.
创建表空间、创建测试用户
SQL> conn user1/user1
Connected.
SQL> select * from tab;

no rows selected

SQL> create table t (id int,name varchar2(32));

Table created.

SQL> begin
  2  for i in 1..10 loop
  3      insert into t values(i,'arcerzhang');
  4  end loop;
  5  commit;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> select * from t;

        ID NAME
---------- --------------------------------
         1 arcerzhang
         2 arcerzhang
         3 arcerzhang
         4 arcerzhang
         5 arcerzhang
         6 arcerzhang
         7 arcerzhang
         8 arcerzhang
         9 arcerzhang
        10 arcerzhang

10 rows selected.
创建实验数据
SQL> select segment_name,tablespace_name from user_segments;

SEGMENT_NAME                   TABLESPACE_NAME
------------------------------ ------------------------------
T                              APP_TEST
验证数据是否存储在新建的表空间app_test上

3、使用rman备份数据文件app_test

C:\Users\MaryHu>rman target sys/arcerzhang_db168 nocatalog;

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Jun 5 22:27:55 2013

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

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

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
9    20       APP_TEST             ***     D:\APP\MARYHU\ORADATA\ARCERZHANGDB\APP_TEST01.DBF

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

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

allocated channel: d1
channel d1: SID=133 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=00009 name=D:\APP\MARYHU\ORADATA\ARCERZHANGDB\APP_TEST01.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\APP_TEST_6_1.BUS tag=TAG20130605T223130 comment=NONE
channel d1: backup set complete, elapsed time: 00:00:01
Finished backup at 05-JUN-13
released channel: d1
使用rman备份data file :app_test

备份成功的数据文件

 

4、对数据文件app_test01.dbf进行破坏性操作

先决条件：需要将数据库down下来

使用emeditor编辑数据文件APP_TEST01.DBF,模拟块损坏.如下图所示

 

此时，虽然数据文件被认为损坏，但是数据库照旧可以startup

SQL> startup
ORACLE instance started.

Total System Global Area  778387456 bytes
Fixed Size                  1374808 bytes
Variable Size             419431848 bytes
Database Buffers          352321536 bytes
Redo Buffers                5259264 bytes
Database mounted.
Database opened.
启动数据库
SQL> select count(*) from t2;
select count(*) from t2
                     *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 9, block # 2124)
ORA-01110: data file 9: 'D:\APP\MARYHU\ORADATA\ARCERZHANGDB\APP_TEST01.DBF'
访问具有坏块的表，出现问题;

SQL> select * from t2 where rownum=12;
select * from t2 where rownum=12
              *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 9, block # 2124)
ORA-01110: data file 9: 'D:\APP\MARYHU\ORADATA\ARCERZHANGDB\APP_TEST01.DBF'


SQL> select * from t2 where rownum=1000;
select * from t2 where rownum=1000
              *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 9, block # 2124)
ORA-01110: data file 9: 'D:\APP\MARYHU\ORADATA\ARCERZHANGDB\APP_TEST01.DBF'


SQL> select * from t2 where rownum=100000;
select * from t2 where rownum=100000
              *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 9, block # 2124)
ORA-01110: data file 9: 'D:\APP\MARYHU\ORADATA\ARCERZHANGDB\APP_TEST01.DBF'


SQL> select * from t;

        ID NAME
---------- --------------------------------
         1 arcerzhang
         2 arcerzhang
         3 arcerzhang
         4 arcerzhang
         5 arcerzhang
         6 arcerzhang
         7 arcerzhang
         8 arcerzhang
         9 arcerzhang
        10 arcerzhang

10 rows selected.
此时访问坏块上的数据，会出问题；非坏块上数据，则能正常访问.
SQL> conn /as sysdba
Connected.
SQL> desc v$database_block_corruption;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 FILE#                                              NUMBER
 BLOCK#                                             NUMBER
 BLOCKS                                             NUMBER
 CORRUPTION_CHANGE#                                 NUMBER
 CORRUPTION_TYPE                                    VARCHAR2(9)

SQL> select * from v$database_block_corruption;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
         9       2124          1                  0 CHECKSUM
通过动态性能视图，查看哪个文件的哪个块出现了问题;

5、check

RMAN> validate database;

Starting validate at 06-JUN-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=13 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00001 name=D:\APP\MARYHU\ORADATA\ARCERZHANGDB\SYSTEM01.DBF
input datafile file number=00002 name=D:\APP\MARYHU\ORADATA\ARCERZHANGDB\SYSAUX01.DBF
input datafile file number=00003 name=D:\APP\MARYHU\ORADATA\ARCERZHANGDB\UNDOTBS01.DBF
input datafile file number=00008 name=D:\APP\MARYHU\ORADATA\ARCERZHANGDB\APP_DATA_01.DBF
input datafile file number=00005 name=D:\APP\MARYHU\ORADATA\ARCERZHANGDB\EXAMPLE01.DBF
input datafile file number=00006 name=D:\APP\MARYHU\ORADATA\ARCERZHANGDB\ASSM_1.DBF
input datafile file number=00007 name=D:\APP\MARYHU\ORADATA\ARCERZHANGDB\MSSM_1.DBF
input datafile file number=00004 name=D:\APP\MARYHU\ORADATA\ARCERZHANGDB\USERS01.DBF
input datafile file number=00009 name=D:\APP\MARYHU\ORADATA\ARCERZHANGDB\APP_TEST01.DBF
channel ORA_DISK_1: validation complete, elapsed time: 00:00:25
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1    OK     0              13577        96003           5646175
  File Name: D:\APP\MARYHU\ORADATA\ARCERZHANGDB\SYSTEM01.DBF
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              66508
  Index      0              12595
  Other      0              3320

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2    OK     0              18419        92179           5646173
  File Name: D:\APP\MARYHU\ORADATA\ARCERZHANGDB\SYSAUX01.DBF
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              23733
  Index      0              19773
  Other      0              30235

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
3    OK     0              1            38400           5646175
  File Name: D:\APP\MARYHU\ORADATA\ARCERZHANGDB\UNDOTBS01.DBF
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0
  Index      0              0
  Other      0              38399

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4    OK     0              505          4960            5602322
  File Name: D:\APP\MARYHU\ORADATA\ARCERZHANGDB\USERS01.DBF
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              3772
  Index      0              98
  Other      0              585

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5    OK     0              1689         12804           5425183
  File Name: D:\APP\MARYHU\ORADATA\ARCERZHANGDB\EXAMPLE01.DBF
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              6596
  Index      0              1261
  Other      0              3254

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
6    OK     0              12640        12800           4741311
  File Name: D:\APP\MARYHU\ORADATA\ARCERZHANGDB\ASSM_1.DBF
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              26
  Index      0              0
  Other      0              134

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
7    OK     0              12656        12800           4526479
  File Name: D:\APP\MARYHU\ORADATA\ARCERZHANGDB\MSSM_1.DBF
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              4
  Index      0              9
  Other      0              131

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
8    OK     0              25473        25600           5534503
  File Name: D:\APP\MARYHU\ORADATA\ARCERZHANGDB\APP_DATA_01.DBF
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0
  Index      0              0
  Other      0              127

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
9    FAILED 0              121          2560            5630995
  File Name: D:\APP\MARYHU\ORADATA\ARCERZHANGDB\APP_TEST01.DBF
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       1              2265
  Index      0              0
  Other      0              174

validate found one or more corrupt blocks
See trace file d:\app\maryhu\diag\rdbms\arcerzhangdb\arcerzhangdb\trace\arcerzhangdb_ora_22276.trc for details
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
including current control file for validation
including current SPFILE in backup set
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Control File and SPFILE
===============================
File Type    Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
SPFILE       OK     0              2
Control File OK     0              594
Finished validate at 06-JUN-13
使用rman中的validate database命令对数据文件进行坏块检查.

7、recover & checkblock 

RMAN> recover datafile 9 block 2124;

Starting recover at 06-JUN-13
using channel ORA_DISK_1
searching flashback logs for block images until SCN 5632722
finished flashback log search, restored 0 blocks

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00009
channel ORA_DISK_1: reading from backup piece D:\APP\MARYHU\ORADATA\BACKUP\APP_TEST_7_1.BUS
channel ORA_DISK_1: piece handle=D:\APP\MARYHU\ORADATA\BACKUP\APP_TEST_7_1.BUS tag=TAG20130605T230909
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01

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

Finished recover at 06-JUN-13
使用rman中的recover datafile file# block block#;对指定文件中的块进行恢复操作.
RMAN> validate datafile 9;

Starting validate at 06-JUN-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00009 name=D:\APP\MARYHU\ORADATA\ARCERZHANGDB\APP_TEST01.DBF
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
9    OK     0              121          2560            5630995
  File Name: D:\APP\MARYHU\ORADATA\ARCERZHANGDB\APP_TEST01.DBF
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              2265
  Index      0              0
  Other      0              174

Finished validate at 06-JUN-13
再次检查数据文件11,验证之前损坏的文件是否恢复成功.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from v$database_block_corruption;

no rows selected
再次检查动态性能视图v$database_block_corruption,发现坏块已经被修复完成.

9、再次访问之前无法访问的数据块，验证是否可以访问

SQL> conn user1/user1
Connected.
SQL> select count(*) from t2;

  COUNT(*)
----------
    399000
验证之前被破坏的数据库表，是否可以正常访问.

 

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/arcer/p/3119511.html