ITPub博客

首页 > 数据库 > Oracle > ora_01578 数据库坏块

ora_01578 数据库坏块

原创 Oracle 作者:fei890910 时间:2014-02-13 11:02:33 0 删除 编辑
sys@TESTDB>create tablespace test datafile '/u01/app/oracle/oradata/testdb/test01a.dbf' size 20m;
Tablespace created.
sys@TESTDB>create user test identified by test default tablespace test;
User created.

sys@TESTDB>conn test/test
ERROR:
ORA-01045: user TEST lacks CREATE SESSION privilege; logon denied
Warning: You are no longer connected to ORACLE.
sys@TESTDB>conn / as sysdba
Connected.
sys@TESTDB>alter user test account unlock;

User altered.
test@TESTDB>create table dept1 as select * from scott.dept;
create table dept1 as select * from scott.dept
                                          *
ERROR at line 1:
ORA-00942: table or view does not exist
sys@TESTDB>conn scott/oracle
Connected.
scott@TESTDB>grant select on dept to test;
Grant succeeded.

scott@TESTDB>conn test/test
Connected.
test@TESTDB>create table dept as select * from scott.dept;
Table created.

test@TESTDB>select * from dept;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
非归档模式不能offline 数据文件
sys@TESTDB>alter database datafile 5 offline;
alter database datafile 5 offline
*
ERROR at line 1:
ORA-01145: offline immediate disallowed unless media recovery enabled

sys@TESTDB>archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
Oldest online log sequence     32
Current log sequence           34

sys@TESTDB>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@TESTDB>startup mount;
ORACLE instance started.

Total System Global Area  835104768 bytes
Fixed Size                  2230592 bytes
Variable Size             230688448 bytes
Database Buffers          599785472 bytes
Redo Buffers                2400256 bytes
Database mounted.
sys@TESTDB>alter database archivelog;
Database altered.

sys@TESTDB>archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
Oldest online log sequence     32
Next log sequence to archive   34
Current log sequence           34
sys@TESTDB>alter database open;
Database altered.

sys@TESTDB>alter database datafile 5 offline;

Database altered.
test@TESTDB>insert into dept select * from dept;
insert into dept select * from dept
            *
ERROR at line 1:
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5: '/u01/app/oracle/oradata/testdb/test01a.dbf'

修复错误
RMAN> list failure;
using target database control file instead of recovery catalog
List of Database Failures
=========================
Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
62         HIGH     OPEN      13-FEB-14     One or more non-system datafiles need media recovery

RMAN> advise failure;
List of Database Failures
=========================
Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
62         HIGH     OPEN      13-FEB-14     One or more non-system datafiles need media recovery

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK
analyzing automatic repair options complete
Mandatory Manual Actions
========================
no manual actions available

Optional Manual Actions
=======================
1. If you restored the wrong version of data file /u01/app/oracle/oradata/testdb/test01a.dbf, then 

replace it with the correct one
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/testdb/test/hm/reco_1444514361.hm
RMAN> repair failure;
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/testdb/test/hm/reco_1444514361.hm
contents of repair script:
   # restore and recover datafile
   sql 'alter database datafile 5 offline';
   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

sql statement: alter database datafile 5 offline
Starting restore at 13-FEB-14
using channel ORA_DISK_1
creating datafile file number=5 name=/u01/app/oracle/oradata/testdb/test01a.dbf
restore not done; all files read only, offline, or already restored
Finished restore at 13-FEB-14

Starting recover at 13-FEB-14
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 13-FEB-14
sql statement: alter database datafile 5 online
repair failure complete

RMAN> list failure;
no failures found that match specification
sys@TESTDB>conn test/test
Connected.
test@TESTDB>insert into dept select * from dept;
insert into dept select * from dept
            *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 131)
ORA-01110: data file 5: '/u01/app/oracle/oradata/testdb/test01a.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option

sys@TESTDB>select name,checkpoint_change# from v$datafile;
NAME                                               CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
/u01/app/oracle/oradata/testdb/system01.dbf                    708295
/u01/app/oracle/oradata/testdb/sysaux01.dbf                    708295
/u01/app/oracle/oradata/testdb/undotbs01.dbf                   708295
/u01/app/oracle/oradata/testdb/users01.dbf                     708295
/u01/app/oracle/oradata/testdb/test01a.dbf                     708618

sys@TESTDB>alter system checkpoint;
System altered.

sys@TESTDB>select name,checkpoint_change# from v$datafile;
NAME                                               CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
/u01/app/oracle/oradata/testdb/system01.dbf                    708731
/u01/app/oracle/oradata/testdb/sysaux01.dbf                    708731
/u01/app/oracle/oradata/testdb/undotbs01.dbf                   708731
/u01/app/oracle/oradata/testdb/users01.dbf                     708731
/u01/app/oracle/oradata/testdb/test01a.dbf                     708731
恢复完成后出现坏块
sys@TESTDB>conn test/test
Connected.
test@TESTDB>insert into dept select * from dept;
insert into dept select * from dept
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 131)
ORA-01110: data file 5: '/u01/app/oracle/oradata/testdb/test01a.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
验证数据库坏块
[oracle@solaris102:/export/home/oracle]$ dbv file=/u01/app/oracle/oradata/testdb/test01a.dbf 
blocksize=8192;
DBVERIFY: Release 11.2.0.3.0 - Production on Thu Feb 13 10:31:39 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/testdb/test01a.dbf
DBV-00201: Block, DBA 20971651, marked corrupt for invalid redo application
DBVERIFY - Verification complete

Total Pages Examined         : 2560
Total Pages Processed (Data) : 0
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 131
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 2429
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 707806 (0.707806)

确定坏块在哪个数据段,这里如果坏的索引段,那么不会丢数据只要重建索引就行了
sys@TESTDB>select tablespace_name,segment_type,owner,segment_name from dba_extents
  2   where file_id=5 and 131 between block_id and block_id+blocks-1;
TABLESPACE_NAME                SEGMENT_TYPE       OWNER                          SEGMENT_NA
------------------------------ ------------------ ---------------              ----------
TEST                           TABLE              TEST                           DEPT

如果是表段的数据块坏掉,那么设置10231事件 跳过坏块。并将好的数据块利用数据泵导出来
sys@TESTDB> alter system set events='10231 trace name context forever,level 10';  
System altered.

sys@TESTDB>create directory test_dmp as '/export/home/oracle/dump';
Directory created.


sys@TESTDB>grant read,write on directory test_dmp to public;
Grant succeeded.

[oracle@solaris102:/export/home/oracle/dump]$ expdp test/test file=dept.dmp tables=dept;
Export: Release 11.2.0.3.0 - Production on Thu Feb 13 10:44:55 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "file=dept.dmp" Location: Command Line, Replaced with: 

"dumpfile=TEST_DMP:dept.dmp"
Database Directory Object "TEST_DMP" has been added to file specification: "dept.dmp".
Legacy Mode has set reuse_dumpfiles=true parameter.
Legacy Mode has set nologfile=true parameter.
Starting "TEST"."SYS_EXPORT_TABLE_01":  test/******** dumpfile=TEST_DMP:dept.dmp tables=dept 

reuse_dumpfiles=true nologfile=true 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "TEST"."DEPT"                                   0 KB       0 rows
Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_TABLE_01 is:
  /export/home/oracle/dump/dept.dmp
Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at 10:45:13

[oracle@solaris102:/export/home/oracle/dump]$ expdp test/test file=dept.dmp tables=dept;
Export: Release 11.2.0.3.0 - Production on Thu Feb 13 10:44:55 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "file=dept.dmp" Location: Command Line, Replaced with: 

"dumpfile=TEST_DMP:dept.dmp"
Database Directory Object "TEST_DMP" has been added to file specification: "dept.dmp".
Legacy Mode has set reuse_dumpfiles=true parameter.
Legacy Mode has set nologfile=true parameter.
Starting "TEST"."SYS_EXPORT_TABLE_01":  test/******** dumpfile=TEST_DMP:dept.dmp tables=dept 
reuse_dumpfiles=true nologfile=true 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "TEST"."DEPT"                                   0 KB       0 rows
Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_TABLE_01 is:
  /export/home/oracle/dump/dept.dmp
Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at 10:45:13

将远离的表删除
test@TESTDB>drop table dept;
Table dropped.

test@TESTDB>commit;
Commit complete.

重新导入表
[oracle@solaris102:/export/home/oracle/dump]$ impdp test/test file=dept.dmp tables=dept;
Import: Release 11.2.0.3.0 - Production on Thu Feb 13 10:47:04 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "file=dept.dmp" Location: Command Line, Replaced with: 

"dumpfile=TEST_DMP:dept.dmp"
Database Directory Object "TEST_DMP" has been added to file specification: "dept.dmp".
Legacy Mode has set nologfile=true parameter.
Master table "TEST"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "TEST"."SYS_IMPORT_TABLE_01":  test/******** dumpfile=TEST_DMP:dept.dmp tables=dept 


nologfile=true 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."DEPT"                                   0 KB       0 rows
Job "TEST"."SYS_IMPORT_TABLE_01" successfully completed at 10:47:10


test@TESTDB>insert into dept select * from scott.dept;
4 rows created.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29108064/viewspace-1080655/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 北京记
请登录后发表评论 登录
全部评论

注册时间:2013-08-15

  • 博文量
    120
  • 访问量
    748532