Home Database Mysql Tutorial Oracle中模拟及修复数据块损坏

Oracle中模拟及修复数据块损坏

Jun 07, 2016 pm 04:53 PM
database

在Oracle数据库中,我们可能遭遇很多数据损坏的情况,在面对这些情况时如何进行数据恢复是非常重要的。在很多情况下,恢复是极其

在Oracle数据库中,我们可能遭遇很多数据损坏的情况,在面对这些情况时如何进行数据恢复是非常重要的。
在很多情况下,恢复是极其复杂的,而且需要谨慎,轻率的操作和尝试可能导致数据库的永久损坏。
所以我建议在恢复尝试前应当先进行数据备份。
对于重要的数据库,如果不能确定恢复步骤和操作后果,建议最好先向专业人士进行咨询,以避免不必要的数据损失。

本文通过试验模拟和解决数据块损坏问题,是对一类数据库损坏的恢复探讨

1.插入数据

E:\Oracle\ora92\bin>sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on 星期一 3月 8 20:27:15 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

连接到:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
E:\ORACLE\ORADATA\EYGLE\SYSTEM01.DBF
E:\ORACLE\ORADATA\EYGLE\UNDOTBS01.DBF
E:\ORACLE\ORADATA\EYGLE\EYGLE01.DBF

SQL> create tablespace block
2 datafile 'e:\oracle\oradata\eygle\block.dbf'
3 size 1M
4 extent management local;

表空间已创建。

SQL> alter user eygle default tablespace block;

用户已更改。

SQL> alter user eygle quota unlimited on block;

用户已更改。

SQL> connect eygle/eygle
已连接。

SQL> create table t as select * from dba_users;

表已创建。

SQL> insert into t select * from t;

已创建8行。

SQL> /

已创建16行。

SQL> /

已创建32行。

SQL> /

已创建64行。

SQL> /

已创建128行。

SQL> /

已创建256行。

SQL> /

已创建512行。

SQL> /

已创建1024行。

SQL> /

已创建2048行。

SQL> /

已创建4096行。

SQL> /
insert into t select * from t
*
ERROR 位于第 1 行:
ORA-01653: 表EYGLE.T无法通过8(在表空间BLOCK中)扩展

SQL> commit;

提交完成。

SQL> alter system checkpoint;

系统已更改。

SQL> select count(*) from t;

COUNT(*)
----------
8192

SQL> connect / as sysdba
已连接。
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。

2.损坏数据文件

关闭数据库后用Ultredit编辑数据文件,随便更改几个字符.然后启动数据库.

SQL> startup
ORACLE 例程已经启动。

Total System Global Area 101785252 bytes
Fixed Size 454308 bytes
Variable Size 75497472 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
数据库装载完毕。
数据库已经打开。
SQL> select count(*) from eygle.t;
select count(*) from eygle.t
*
ERROR 位于第 1 行:
ORA-01578: ORACLE 数据块损坏(文件号4,块号35)
ORA-01110: 数据文件 4: 'E:\ORACLE\ORADATA\EYGLE\BLOCK.DBF'

SQL>

此时如果查询该表,会出现错误,数据块损坏无法读取.

有个内部工具BBED也可以用来编辑数据块的内容

3.使用DBV检查数据文件

E:\Oracle\oradata\eygle>dbv file=block.dbf blocksize=8192

DBVERIFY: Release 9.2.0.4.0 - Production on 星期一 3月 8 20:48:50 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

DBVERIFY - 验证正在开始 : FILE = block.dbf
标记为损坏的页35
***
Corrupt block relative dba: 0x01000023 (file 4, block 35)
Bad check value found during dbv:
Data in bad block -
type: 6 format: 2 rdba: 0x01000023
last change scn: 0x0000.00049097 seq: 0x1 flg: 0x06
consistency value in tail: 0x90970601
check value in block header: 0xd6cb, computed block checksum: 0x2c0a
spare1: 0x0, spare2: 0x0, spare3: 0x0
***

标记为损坏的页69
***
Corrupt block relative dba: 0x01000045 (file 4, block 69)
Bad check value found during dbv:
Data in bad block -
type: 6 format: 2 rdba: 0x01000045
last change scn: 0x0000.00049097 seq: 0x1 flg: 0x06
consistency value in tail: 0x90970601
check value in block header: 0x33d1, computed block checksum: 0x653
spare1: 0x0, spare2: 0x0, spare3: 0x0
***

DBVERIFY - 验证完成

检查的页总数 :128
处理的页总数(数据):117
失败的页总数(数据):0
处理的页总数(索引):0
失败的页总数(索引):0
处理的页总数(其它):9
处理的总页数 (段) : 0
失败的总页数 (段) : 0
空的页总数 :0
标记为损坏的总页数:2
汇入的页总数 :0

dbv检测到坏块.

4.寻求恢复

在这种情况下,如果有备份,,需要从备份中恢复
如果没有备份,那么坏块部分的数据肯定要丢失了

在这个时候导出是不允许的:

E:\>exp eygle/eygle file=t.dmp tables=t

Export: Release 9.2.0.4.0 - Production on 星期一 3月 8 20:54:15 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

连接到: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集

即将导出指定的表通过常规路径 ...
. . 正在导出表 T
EXP-00056: 遇到 ORACLE 错误 1578
ORA-01578: ORACLE 数据块损坏(文件号4,块号35)
ORA-01110: 数据文件 4: 'E:\ORACLE\ORADATA\EYGLE\BLOCK.DBF'
导出成功终止,但出现警告。

5.恢复步骤

当然,对于不同的情况需要区别对待

首先你需要检查损坏的对象,使用以下SQL:

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
3 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

How does Go language implement the addition, deletion, modification and query operations of the database? How does Go language implement the addition, deletion, modification and query operations of the database? Mar 27, 2024 pm 09:39 PM

Go language is an efficient, concise and easy-to-learn programming language. It is favored by developers because of its advantages in concurrent programming and network programming. In actual development, database operations are an indispensable part. This article will introduce how to use Go language to implement database addition, deletion, modification and query operations. In Go language, we usually use third-party libraries to operate databases, such as commonly used sql packages, gorm, etc. Here we take the sql package as an example to introduce how to implement the addition, deletion, modification and query operations of the database. Assume we are using a MySQL database.

Detailed tutorial on establishing a database connection using MySQLi in PHP Detailed tutorial on establishing a database connection using MySQLi in PHP Jun 04, 2024 pm 01:42 PM

How to use MySQLi to establish a database connection in PHP: Include MySQLi extension (require_once) Create connection function (functionconnect_to_db) Call connection function ($conn=connect_to_db()) Execute query ($result=$conn->query()) Close connection ( $conn->close())

How does Hibernate implement polymorphic mapping? How does Hibernate implement polymorphic mapping? Apr 17, 2024 pm 12:09 PM

Hibernate polymorphic mapping can map inherited classes to the database and provides the following mapping types: joined-subclass: Create a separate table for the subclass, including all columns of the parent class. table-per-class: Create a separate table for subclasses, containing only subclass-specific columns. union-subclass: similar to joined-subclass, but the parent class table unions all subclass columns.

iOS 18 adds a new 'Recovered' album function to retrieve lost or damaged photos iOS 18 adds a new 'Recovered' album function to retrieve lost or damaged photos Jul 18, 2024 am 05:48 AM

Apple's latest releases of iOS18, iPadOS18 and macOS Sequoia systems have added an important feature to the Photos application, designed to help users easily recover photos and videos lost or damaged due to various reasons. The new feature introduces an album called "Recovered" in the Tools section of the Photos app that will automatically appear when a user has pictures or videos on their device that are not part of their photo library. The emergence of the "Recovered" album provides a solution for photos and videos lost due to database corruption, the camera application not saving to the photo library correctly, or a third-party application managing the photo library. Users only need a few simple steps

An in-depth analysis of how HTML reads the database An in-depth analysis of how HTML reads the database Apr 09, 2024 pm 12:36 PM

HTML cannot read the database directly, but it can be achieved through JavaScript and AJAX. The steps include establishing a database connection, sending a query, processing the response, and updating the page. This article provides a practical example of using JavaScript, AJAX and PHP to read data from a MySQL database, showing how to dynamically display query results in an HTML page. This example uses XMLHttpRequest to establish a database connection, send a query and process the response, thereby filling data into page elements and realizing the function of HTML reading the database.

Analysis of the basic principles of MySQL database management system Analysis of the basic principles of MySQL database management system Mar 25, 2024 pm 12:42 PM

Analysis of the basic principles of the MySQL database management system MySQL is a commonly used relational database management system that uses structured query language (SQL) for data storage and management. This article will introduce the basic principles of the MySQL database management system, including database creation, data table design, data addition, deletion, modification, and other operations, and provide specific code examples. 1. Database Creation In MySQL, you first need to create a database instance to store data. The following code can create a file named "my

Tips and practices for handling Chinese garbled characters in databases with PHP Tips and practices for handling Chinese garbled characters in databases with PHP Mar 27, 2024 pm 05:21 PM

PHP is a back-end programming language widely used in website development. It has powerful database operation functions and is often used to interact with databases such as MySQL. However, due to the complexity of Chinese character encoding, problems often arise when dealing with Chinese garbled characters in the database. This article will introduce the skills and practices of PHP in handling Chinese garbled characters in databases, including common causes of garbled characters, solutions and specific code examples. Common reasons for garbled characters are incorrect database character set settings: the correct character set needs to be selected when creating the database, such as utf8 or u

How to handle database connection errors in PHP How to handle database connection errors in PHP Jun 05, 2024 pm 02:16 PM

To handle database connection errors in PHP, you can use the following steps: Use mysqli_connect_errno() to obtain the error code. Use mysqli_connect_error() to get the error message. By capturing and logging these error messages, database connection issues can be easily identified and resolved, ensuring the smooth running of your application.

See all articles