[bbk5383] 第90集 -第11章 -数据库诊断 06
/*
实验目的:db_block corrupt recover
*/
- config
- archive log
- flashback
- parameter
- create tablespace---app_test
- backup-rman
- 破坏
- check block-dbv-rman
- startup
- recover block
- 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
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
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
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
备份成功的数据文件
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
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> 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
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
9、再次访问之前无法访问的数据块，验证是否可以访问
SQL> conn user1/user1 Connected. SQL> select count(*) from t2; COUNT(*) ---------- 399000
