Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

What if there is an ora-1578 error in the ORACLE database

2025-01-15 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Servers >

Share

Shulou(Shulou.com)06/01 Report--

This article mainly introduces the ORACLE database in the ora-1578 error how to do, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let Xiaobian with you to understand.

In the test environment, an AIX minicomputer with ORACLE database lost power unexpectedly, which caused the oracle database to go down. As it was a test environment, an engineer was arranged to solve the problem, specifically as follows: first, the minicomputer server was restarted, and after startup, it was found that the / app directory where oracle was located was not on mount. Then fix it through smitty fs, mount on app, and then start oracle to get up.

After collecting the system.txt system log (obtained through errpt-a) and the trace log trc of alert_soa.log and oracle, we can see that the trc log is as follows:

/ 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, p_w_picpath: oracle@data2 (MMON)

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

* SERVICE NAME: (SYS$BACKGROUND) 2013-03-01 1415 0615 10.212

* SESSION ID: (161.1) 2013-03-01 1415 0615 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....]

After observing the other two files, it is found that there are more ORA-1578 errors and DISK OPERATION ERROR.

Analysis: generally in the CLUSTER dual-computer switching, accidental power outage or other cases, sometimes a shared disk MOUNT does not occur, it is necessary to use FSCK to repair the shared disk, and then MOUNT. When the repair is completed, if the database is smooth, the database can be started directly, otherwise the phenomenon of "data block damage and unable to start the database" will be reported during the database startup process. At this point, we can detect and fix the error and determine the solution to the problem according to the different types of block damage.

First, the cause of data block damage:

1. Hardware problem (disk controller problem or disk itself failure problem)

2. Physical level data block corruption (usually caused by the previous reason)

3. Logical data blocks are damaged.

Second, the principle analysis of bad blocks:

The data block of Oracle has a fixed format and structure, which is divided into three layers: Cache layer, Transaction layer and Data layer.

When reading and writing data blocks, do a consistency check:

-Block type

-DBA

-Scn

-Header and tail

Inconsistencies were found and marked as bad blocks. There are two kinds of bad blocks: physical bad blocks and logical bad blocks. The impact of bad blocks: data dictionary tables, rollback segment tables, temporary and user data tables and indexes.

Third, determine the cause of the failure and the corresponding solutions:

1. Check the alert.log file and see if there are no other ORA- errors. If the error points to files on different disks, it is the problem of the disk controller. Check the V$DATAFILE to see which files are under the controller. You need to find whether the disk controller (the general controller has two A-controllers and B-controllers) is normal.

2. If the error points to different files on the same disk, it is the disk problem. You need to check whether there is an alarm on the disk, whether there is an error in LVM, and so on.

3. If you point to the same file on the same disk, you can execute the following statement to find the file name:

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

The file number and block number can be found in the error log, and if the query continues to point to a table or index, they can be rebuilt.

4, if the file is a SYSTEM tablespace, or in NOARCHIVELOG mode, while the database is still running, EXP exports all the data, rebuilds the library, and then IMP infuses the new library.

5. If the database is in ARCHIVELOG mode, you can use DBV to verify the bad block, then repair the bad block through RMAN, and start the database after success.

Or another option.

Close the database, or if the database cannot be closed, take the appropriate data file offline:

ALTER DATABASE DATAFILE 'filename' OFFLINE

Try to copy the data file to another disk. If the copy fails, the file will be lost.

And then STARTUP MOUNT

Rename the data file to the file name that was successfully copied to another disk

ALTER DATABASE RENAME FILE 'old path file name' TO 'new path file name'

ALTER DATABASE OPEN

RECOVER DATAFILE file name

ALTER DATABASE DATAFILE 'filename' ONLINE

IV. the solution to this example

Since the database is backed up and archived and the backup is available in this case, use the rman command to fix the bad block

Check the bad block by DBV first

$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 /

Recovery Manager: Release 10.2.0.1.0-Production on Friday March 1 15:07:14 2013

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

Connect to the target database: soa (DBID=1281151392)

RMAN > blockrecover datafile 3 block 49259

Start blockrecover

Replace the recovery directory with the target database control file

Assigned channel: ORA_DISK_1

Channel ORA_DISK_1: sid=187 devtype=DISK

Channel ORA_DISK_1: restoring blocks

Channel ORA_DISK_1: specifying blocks to restore from backup set

Restoring blocks of data file 049259

Channel ORA_DISK_1: reading backup segment ORACLE\ FLASH_RECOVERY_AREA\ DB01\ BACKUPSET

\ 2013 / 02 / 28 / O1_MF_NNNDF_TAG201302287_3\ YCS579G_.BKP

Channel ORA_DISK_1: block restored from backup segment 1

Channel ORA_DISK_1: block recovery complete, time: 00:00:02

Starting media recovery

Media recovery complete, time: 00:00:05

Complete blockrecover in 1-3-13

RMAN > exit

The recovery Manager is complete.

SQL > select count (*) from buffer.t

COUNT (*)

-

3298

After the bad block is repaired, the v$database_block_corruption will not be updated. It needs to be updated on the next backup.

SQL > select * from v$database_block_corruption

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO

--

3 49259 1 0 CHECKSUM

$rman target /

Recovery Manager: Release 10.2.0.1.0-Production on Sunday March 1 16:09:43 2013

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

Connect to the target database: soa (DBID=1281151392)

RMAN > backup validate datafile 3

Start backup

Replace the recovery directory with the target database control file

Assigned channel: ORA_DISK_1

Channel ORA_DISK_1: sid=132 devtype=DISK

Channel ORA_DISK_1: starts the backup set of all data files

Channel ORA_DISK_1: specifying data files in the backup set

Channel ORA_DISK_1: backup set completed, elapsed time: 00:00:03

Complete backup in 1-3-13

RMAN > exit

The recovery Manager is complete.

SQL > select * from v$database_block_corruption

No rows selected

Note: if the database is not backed up, you can consider using the dbms_repair package to remedy it, but the database will be lost.

At this point, the database recovery is complete, and it is normal to restart again.

Thank you for reading this article carefully. I hope the article "what to do if there are ora-1578 errors in the ORACLE database" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Servers

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report