search
HomeDatabaseMysql Tutorial ORACLE数据库一次意外宕机的分析处理实记(ora-1578)

一个安静的下午,测试环境中一台装有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

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

连接到目标数据库: 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

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

连接到目标数据库: 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
How to solve the problem of mysql cannot open shared libraryHow to solve the problem of mysql cannot open shared libraryMar 04, 2025 pm 04:01 PM

This article addresses MySQL's "unable to open shared library" error. The issue stems from MySQL's inability to locate necessary shared libraries (.so/.dll files). Solutions involve verifying library installation via the system's package m

Reduce the use of MySQL memory in DockerReduce the use of MySQL memory in DockerMar 04, 2025 pm 03:52 PM

This article explores optimizing MySQL memory usage in Docker. It discusses monitoring techniques (Docker stats, Performance Schema, external tools) and configuration strategies. These include Docker memory limits, swapping, and cgroups, alongside

How do you alter a table in MySQL using the ALTER TABLE statement?How do you alter a table in MySQL using the ALTER TABLE statement?Mar 19, 2025 pm 03:51 PM

The article discusses using MySQL's ALTER TABLE statement to modify tables, including adding/dropping columns, renaming tables/columns, and changing column data types.

Run MySQl in Linux (with/without podman container with phpmyadmin)Run MySQl in Linux (with/without podman container with phpmyadmin)Mar 04, 2025 pm 03:54 PM

This article compares installing MySQL on Linux directly versus using Podman containers, with/without phpMyAdmin. It details installation steps for each method, emphasizing Podman's advantages in isolation, portability, and reproducibility, but also

What is SQLite? Comprehensive overviewWhat is SQLite? Comprehensive overviewMar 04, 2025 pm 03:55 PM

This article provides a comprehensive overview of SQLite, a self-contained, serverless relational database. It details SQLite's advantages (simplicity, portability, ease of use) and disadvantages (concurrency limitations, scalability challenges). C

Running multiple MySQL versions on MacOS: A step-by-step guideRunning multiple MySQL versions on MacOS: A step-by-step guideMar 04, 2025 pm 03:49 PM

This guide demonstrates installing and managing multiple MySQL versions on macOS using Homebrew. It emphasizes using Homebrew to isolate installations, preventing conflicts. The article details installation, starting/stopping services, and best pra

How do I configure SSL/TLS encryption for MySQL connections?How do I configure SSL/TLS encryption for MySQL connections?Mar 18, 2025 pm 12:01 PM

Article discusses configuring SSL/TLS encryption for MySQL, including certificate generation and verification. Main issue is using self-signed certificates' security implications.[Character count: 159]

What are some popular MySQL GUI tools (e.g., MySQL Workbench, phpMyAdmin)?What are some popular MySQL GUI tools (e.g., MySQL Workbench, phpMyAdmin)?Mar 21, 2025 pm 06:28 PM

Article discusses popular MySQL GUI tools like MySQL Workbench and phpMyAdmin, comparing their features and suitability for beginners and advanced users.[159 characters]

See all articles

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

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

Hot Tools

MantisBT

MantisBT

Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.

mPDF

mPDF

mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

Safe Exam Browser

Safe Exam Browser

Safe Exam Browser is a secure browser environment for taking online exams securely. This software turns any computer into a secure workstation. It controls access to any utility and prevents students from using unauthorized resources.