课程第42次（2017-01-20星期五）春节前最后一课

botang · 发表于 2017-1-21 17:38:19

本帖最后由 botang 于 2017-1-21 17:48 编辑

模拟获取 MOS支持：制造逻辑坏块。

--sys
create tablespace tbs05218 datafile size 5M nologging;
--hr
create table t05218 ( a varchar2(20), b number ) tablespace tbs05218;
insert into t05218 values ('zenglinhuang',225788);
create index i05218_a on t05218(a) tablespace users;
create index i05218_b on t05218(b) tablespace users;
--sys
rman 备份tbs05218
--hr
insert /*+ append */ into t05218 select * from t05218;
--sys
alter tablespace tbs05218 offline immediate;
rman 恢复tbs05218
--hr
select * from t05218;
立即会发现逻辑坏块。

复制代码

[root@station90 ~]# su - oracle
[oracle@station90 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Fri Jan 20 21:54:29 2017
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> conn hr/hr
Connected.
SQL> select * from t05218 ;
ERROR:
ORA-01578: ORACLE data block corrupted (file # 7, block # 136)
ORA-01110: data file 7: '+DATA/winorcl/datafile/tbs05218.269.933800115'
ORA-26040: Data block was loaded using the NOLOGGING option
no rows selected
SQL> conn / as sysdba
Connected.
SQL> desc dbms_repair
PROCEDURE ADMIN_TABLES
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
TABLE_NAME VARCHAR2 IN DEFAULT
TABLE_TYPE BINARY_INTEGER IN
ACTION BINARY_INTEGER IN
TABLESPACE VARCHAR2 IN DEFAULT
PROCEDURE CHECK_OBJECT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SCHEMA_NAME VARCHAR2 IN
OBJECT_NAME VARCHAR2 IN
PARTITION_NAME VARCHAR2 IN DEFAULT
OBJECT_TYPE BINARY_INTEGER IN DEFAULT
REPAIR_TABLE_NAME VARCHAR2 IN DEFAULT
FLAGS BINARY_INTEGER IN DEFAULT
RELATIVE_FNO BINARY_INTEGER IN DEFAULT
BLOCK_START BINARY_INTEGER IN DEFAULT
BLOCK_END BINARY_INTEGER IN DEFAULT
CORRUPT_COUNT BINARY_INTEGER OUT
PROCEDURE DUMP_ORPHAN_KEYS
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SCHEMA_NAME VARCHAR2 IN
OBJECT_NAME VARCHAR2 IN
PARTITION_NAME VARCHAR2 IN DEFAULT
OBJECT_TYPE BINARY_INTEGER IN DEFAULT
REPAIR_TABLE_NAME VARCHAR2 IN DEFAULT
ORPHAN_TABLE_NAME VARCHAR2 IN DEFAULT
FLAGS BINARY_INTEGER IN DEFAULT
KEY_COUNT BINARY_INTEGER OUT
PROCEDURE FIX_CORRUPT_BLOCKS
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SCHEMA_NAME VARCHAR2 IN
OBJECT_NAME VARCHAR2 IN
PARTITION_NAME VARCHAR2 IN DEFAULT
OBJECT_TYPE BINARY_INTEGER IN DEFAULT
REPAIR_TABLE_NAME VARCHAR2 IN DEFAULT
FLAGS BINARY_INTEGER IN DEFAULT
FIX_COUNT BINARY_INTEGER OUT
FUNCTION ONLINE_INDEX_CLEAN RETURNS BOOLEAN
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OBJECT_ID BINARY_INTEGER IN DEFAULT
WAIT_FOR_LOCK BINARY_INTEGER IN DEFAULT
PROCEDURE REBUILD_FREELISTS
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SCHEMA_NAME VARCHAR2 IN
OBJECT_NAME VARCHAR2 IN
PARTITION_NAME VARCHAR2 IN DEFAULT
OBJECT_TYPE BINARY_INTEGER IN DEFAULT
PROCEDURE REBUILD_SHC_INDEX
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SEGMENT_OWNER VARCHAR2 IN
CLUSTER_NAME VARCHAR2 IN
PROCEDURE SEGMENT_FIX_STATUS
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SEGMENT_OWNER VARCHAR2 IN
SEGMENT_NAME VARCHAR2 IN
SEGMENT_TYPE BINARY_INTEGER IN DEFAULT
FILE_NUMBER BINARY_INTEGER IN DEFAULT
BLOCK_NUMBER BINARY_INTEGER IN DEFAULT
STATUS_VALUE BINARY_INTEGER IN DEFAULT
PARTITION_NAME VARCHAR2 IN DEFAULT
PROCEDURE SKIP_CORRUPT_BLOCKS
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SCHEMA_NAME VARCHAR2 IN
OBJECT_NAME VARCHAR2 IN
OBJECT_TYPE BINARY_INTEGER IN DEFAULT
FLAGS BINARY_INTEGER IN DEFAULT
SQL>
SQL> conn hr/hr
Connected.
SQL> select * from t05218;
ERROR:
ORA-01578: ORACLE data block corrupted (file # 7, block # 136)
ORA-01110: data file 7: '+DATA/winorcl/datafile/tbs05218.269.933800115'
ORA-26040: Data block was loaded using the NOLOGGING option
no rows selected
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[oracle@station90 ~]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Fri Jan 20 21:59:38 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: WINORCL (DBID=2081444222)
RMAN> backup validate check logical tablespace tbs05218;
Starting backup at 20-JAN-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=7 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=00007 name=+DATA/winorcl/datafile/tbs05218.269.933800115
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
7 OK 1 56 641 2588016
File Name: +DATA/winorcl/datafile/tbs05218.269.933800115
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 5
Index 0 0
Other 0 579
Finished backup at 20-JAN-17
RMAN>

复制代码

select * from v$database_block_corruption;
select * from v$backup_corruption;
select * from v$copy_corruption;

复制代码