ORACLE数据库一次意外宕机的分析处理实记（ora-1578）-Mysql Tutorial-php.cn

Home

Database

Mysql Tutorial

ORACLE数据库一次意外宕机的分析处理实记（ora-1578）

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 07, 2016 pm 05:41 PM

一个安静的下午，测试环境中一台装有ORACLE数据库的AIX小机因意外断电而导致其上的oracle数据库宕机了。由于是测试环境，安排了一个工程师过去解决了，具体是这

一个安静的下午，测试环境中一台装有ORACLE数据库的AIX小机因意外断电而导致其上的oracle数据库宕机了。由于是测试环境，安排了一个工程师过去解决了，具体是这样解决的：首先重启了小机服务器，启动完后，发现oracle所在的/app目录没有mount上。然后通过smitty fs修复了一下，mount上了app，再接着启动oracle就起来了。

事后搜集了system.txt 系统日志(通过errpt -a获得)和alert_soa.log以及oracle的跟踪日志trc，分析trc日志看到如下：

/app/oracle/product/10.2.0/admin/soa/bdump/soa_mmon_307366.trc

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

ORACLE_HOME = /app/oracle/product/10.2.0

System name:AIX

Node name:data2

Release:3

Version:5

Machine:00CE993C4C00

Instance name: soa

Redo thread mounted by this instance: 1

Oracle process number: 11

Unix process pid: 307366, image: oracle@data2 (MMON)

*** 2013-03-01 14:06:10.308

*** SERVICE NAME:(SYS$BACKGROUND) 2013-03-01 14:06:10.212

*** SESSION ID:(161.1) 2013-03-01 14:06:10.212

Hex dump of (file 3, block 49259)

Dump of memory from 0x07000000C5934000 to 0x07000000C5936000

7000000C5934000 06A20000 00C0C06B 0178F614 00000104 [.......k.x......]

7000000C5934010 45A30000 010A0025 0000224D 0178F614 [E......%.."M.x..]

7000000C5934020 00000000 1F023200 00C0C069 00010003 [......2....i....]

又观察另两个文件，发现有较多ORA-1578报错和DISK OPERATION ERROR。

分析：一般在进行CLUSTER双机切换、意外断电或其它情况下，有时会发生某个共享盘MOUNT不上的情况，需要使用FSCK对共享盘进行修复，然后再MOUNT.当修复完成后，顺利的话数据库可以直接起来，否则在数据库启动过程中就会报出"数据块损坏，无法启动数据库"的现象。此时，我们可以根据不同的数据块损坏类型，检测并修复错误并确定解决问题的方案。

一、数据块损坏产生原因：

1．硬件问题（磁盘控制器问题或磁盘本身故障问题）

2．物理级的数据块损坏（通常由前一原因造成）

3、逻辑的数据块损坏

二、坏块的原理分析：

Oracle的数据块有固定的格式和结构，分三层： Cache layer、Transaction layer和Data layer.

对数据块进行读写操作时，做一致性检查：

–Block type

–DBA

–Scn

–Header and tail

发现不一致，标记为坏块。坏块有两种：物理坏块和逻辑坏块。坏块产生的影响：数据字典表、回滚段表、临时段和用户数据表和索引。

三、确定故障原因与对应的解决办法：

1、查看alert.log文件中，还有无其它ORA-的错误，美国空间，如果报错指向不同磁盘的文件，则是磁盘控制器的问题，查看V$DATAFILE，看有哪些文件位于该控制器下，需要查找磁盘控制器(一般控制器有两个A控和B控)是否正常。

2、如果报错指向相同磁盘的不同文件，则是磁盘的问题，需要查看磁盘有无报警，LVM有无报错等。

3、如果指向相同磁盘的同一个文件，则可以执行以下语句查找文件名：

SELECT SEGMENT_NAME,SEGMENT_TYPE FROM DBA_EXTENTS WHERE FILE_ID= AND BETWEEN BLOCK_ID AND BLOCK_ID+BLOCKS-1;

其中，文件号与块号在报错日志中可以查到，如果该查询持续指向某表或索引，则重建它们即可。

4、如果文件是SYSTEM表空间，或处于NOARCHIVELOG模式，在数据库还在运行状态时，EXP导出全部数据，重建库，再IMP灌入新库即可。

5、如果数据库处于ARCHIVELOG模式，可以使用DBV校验坏块，然后通过RMAN来修复坏块，成功后启动数据库。

或者另一种方案

关闭数据库，如果不能关闭数据库，则将相应的数据文件脱机：

ALTER DATABASE DATAFILE '文件名' OFFLINE；

试着将数据文件拷贝到别的磁盘。如果拷贝失败，香港服务器租用，则文件将丢失。

然后STARTUP MOUNT;

将数据文件重命名为成功拷贝到别的磁盘的文件名

ALTER DATABASE RENAME FILE '老路径文件名' TO '新路径文件名';

ALTER DATABASE OPEN;

RECOVER DATAFILE 文件名;

ALTER DATABASE DATAFILE '文件名' ONLINE;

四、本例的解决办法

由于本案例中，数据库有备份和归档且备份可用，所以使用rman命令修复坏块

先DBV校验坏块

$show parameter db_block_size
$select BYTES/2048 from v$datafile where FILE#=3;
$dbv file=/app/oracle/product/10.2.0/oradata/soa/user01.dbf blocksize=8192

$rman target /

恢复管理器: Release 10.2.0.1.0 - Production on 星期五 3月 1 15:07:14 2013

连接到目标数据库: soa (DBID=1281151392)

RMAN> blockrecover datafile 3 block 49259;

启动 blockrecover

使用目标数据库控制文件替代恢复目录

分配的通道: ORA_DISK_1

通道 ORA_DISK_1: sid=187 devtype=DISK

通道 ORA_DISK_1: 正在恢复块

通道 ORA_DISK_1: 正在指定要从备份集恢复的块

正在恢复数据文件 049259 的块

通道 ORA_DISK_1: 正在读取备份段ORACLE\FLASH_RECOVERY_AREA\DB01\BACKUPSET

\2013_02_28\O1_MF_NNNDF_TAG201302287_3\YCS579G_.BKP

通道 ORA_DISK_1: 已从备份段 1 恢复块

通道 ORA_DISK_1: 块恢复完成, 用时: 00:00:02

正在开始介质的恢复

介质恢复完成, 用时: 00:00:05

完成 blockrecover 于 1-3-13

RMAN> exit

恢复管理器完成。

SQL> select count(*) from buffer.t;

COUNT(*)

----------

3298

坏块修复后，并不会更新v$database_block_corruption，需要下次备份的时候更新

SQL> select * from v$database_block_corruption;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO

---------- ---------- ---------- ------------------ ---------

3 49259 1 0 CHECKSUM

$rman target /

恢复管理器: Release 10.2.0.1.0 - Production on 星期日 3月 1 16:09:43 2013

连接到目标数据库: soa (DBID=1281151392)

RMAN> backup validate datafile 3;

启动 backup

使用目标数据库控制文件替代恢复目录

分配的通道: ORA_DISK_1

通道 ORA_DISK_1: sid=132 devtype=DISK

通道 ORA_DISK_1: 启动全部数据文件备份集

通道 ORA_DISK_1: 正在指定备份集中的数据文件

通道 ORA_DISK_1: 备份集已完成, 经过时间:00:00:03

完成 backup 于 1-3-13

RMAN> exit

恢复管理器完成。

SQL> select * from v$database_block_corruption;

未选定行

注：如果数据库没有备份的话，可以考虑使用dbms_repair包来补救，但是会丢数据库。

至此数据库恢复完成，再次重启已正常。

本文出自 “滴水穿石” 博客，谢绝转载！

，香港虚拟主机

Statement

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

What are stored procedures in MySQL?May 01, 2025 am 12:27 AM

Stored procedures are precompiled SQL statements in MySQL for improving performance and simplifying complex operations. 1. Improve performance: After the first compilation, subsequent calls do not need to be recompiled. 2. Improve security: Restrict data table access through permission control. 3. Simplify complex operations: combine multiple SQL statements to simplify application layer logic.

How does query caching work in MySQL?May 01, 2025 am 12:26 AM

The working principle of MySQL query cache is to store the results of SELECT query, and when the same query is executed again, the cached results are directly returned. 1) Query cache improves database reading performance and finds cached results through hash values. 2) Simple configuration, set query_cache_type and query_cache_size in MySQL configuration file. 3) Use the SQL_NO_CACHE keyword to disable the cache of specific queries. 4) In high-frequency update environments, query cache may cause performance bottlenecks and needs to be optimized for use through monitoring and adjustment of parameters.

What are the advantages of using MySQL over other relational databases?May 01, 2025 am 12:18 AM

The reasons why MySQL is widely used in various projects include: 1. High performance and scalability, supporting multiple storage engines; 2. Easy to use and maintain, simple configuration and rich tools; 3. Rich ecosystem, attracting a large number of community and third-party tool support; 4. Cross-platform support, suitable for multiple operating systems.

How do you handle database upgrades in MySQL?Apr 30, 2025 am 12:28 AM

The steps for upgrading MySQL database include: 1. Backup the database, 2. Stop the current MySQL service, 3. Install the new version of MySQL, 4. Start the new version of MySQL service, 5. Recover the database. Compatibility issues are required during the upgrade process, and advanced tools such as PerconaToolkit can be used for testing and optimization.

What are the different backup strategies you can use for MySQL?Apr 30, 2025 am 12:28 AM

MySQL backup policies include logical backup, physical backup, incremental backup, replication-based backup, and cloud backup. 1. Logical backup uses mysqldump to export database structure and data, which is suitable for small databases and version migrations. 2. Physical backups are fast and comprehensive by copying data files, but require database consistency. 3. Incremental backup uses binary logging to record changes, which is suitable for large databases. 4. Replication-based backup reduces the impact on the production system by backing up from the server. 5. Cloud backups such as AmazonRDS provide automation solutions, but costs and control need to be considered. When selecting a policy, database size, downtime tolerance, recovery time, and recovery point goals should be considered.

What is MySQL clustering?Apr 30, 2025 am 12:28 AM

MySQLclusteringenhancesdatabaserobustnessandscalabilitybydistributingdataacrossmultiplenodes.ItusestheNDBenginefordatareplicationandfaulttolerance,ensuringhighavailability.Setupinvolvesconfiguringmanagement,data,andSQLnodes,withcarefulmonitoringandpe

How do you optimize database schema design for performance in MySQL?Apr 30, 2025 am 12:27 AM

Optimizing database schema design in MySQL can improve performance through the following steps: 1. Index optimization: Create indexes on common query columns, balancing the overhead of query and inserting updates. 2. Table structure optimization: Reduce data redundancy through normalization or anti-normalization and improve access efficiency. 3. Data type selection: Use appropriate data types, such as INT instead of VARCHAR, to reduce storage space. 4. Partitioning and sub-table: For large data volumes, use partitioning and sub-table to disperse data to improve query and maintenance efficiency.

How can you optimize MySQL performance?Apr 30, 2025 am 12:26 AM

TooptimizeMySQLperformance,followthesesteps:1)Implementproperindexingtospeedupqueries,2)UseEXPLAINtoanalyzeandoptimizequeryperformance,3)Adjustserverconfigurationsettingslikeinnodb_buffer_pool_sizeandmax_connections,4)Usepartitioningforlargetablestoi

See all articles