ORA-00257 Fault resolution process for Oracle Database

Source: Internet
Author: User
Tags manual error code imagecopy log sql thread oracle database sqlplus
Overview

Oracle database is currently the industry's most commonly used large database systems, I encountered in the actual project ORA-00257 error (lack of space error), by looking for information, most of this is due to the archive log too much, occupy all the hard disk space caused by, It can be resolved by simply deleting the log or increasing storage space. But I found on Oracle 10g that there is still a lot of storage space, but I also reported this error. It turns out to be a new feature in Oracle 10g, which is responsible for the management of flash recovery.

   1, software and hardware environment

Server HP Proliant dl580g4 (Intel Xeon 3.16ghz/4gb/72.8*4/raid4)

Operating system Red Flag DC Server Release 5.0 (Trinity) for x86-64 Linux

Database Oracle 10.2.0.1.0

   2, problem phenomenon

The database system has been running for more than half a month, in the evening of July 24 after the database to do data update ORA-00257 error, the following figure.


Tip Archive Error, by looking for Oracle error code, explained as hard disk space, need to remove the archive log increase space, but the server free space 200GB, currently only 10GB or so, this is why?

   3. Diagnosis Process

1 View Oracle Database archive logs

[Root@hrmsdb/]# Cd/oracle/flash_recovery_area/hkchr/archivelog

[Root@hrmsdb archivelog]# ls

2006_07_04 2006_07_13 2006_07_17 2006_07_20 2006_07_23

2006_07_11 2006_07_14 2006_07_18 2006_07_21 2006_07_24

2006_07_12 2006_07_15 2006_07_19 2006_07_22 2006_07_25

[Root@hrmsdb archivelog]# CD 2006_07_25

[Root@hrmsdb 2006_07_25]# ls

[Root@hrmsdb 2006_07_25]# CD ... /2006_07_24

[Root@hrmsdb 2006_07_24]# ls

O1_mf_1_92_2d933vgb_.arc O1_mf_1_96_2d954ns7_.arc O1_mf_1_98_2d969d5h_.arc

O1_mf_1_95_2d9537cs_.arc O1_mf_1_97_2d956km0_.arc

Explains that database archiving processing is always normal before a problem occurs.

2) View the database Redolog situation

[Oracle@hrmsdb ~]$ Sqlplus/nolog

Sql*plus:release 10.2.0.1.0-production on Tuesday July 25 10:44:18 2006

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

Sql> Connect/as SYSDBA

is connected.

Sql> select * from V$log;

group# thread# sequence# BYTES members ARC STATUS first_change# first_time

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

1 1 52428800 1 NO current 3621973 2 April-July-06

2 1 52428800 1 NO INACTIVE 3600145 2 April-July-06

3 1 52428800 1 NO INACTIVE 3611932 2 April-July-06

found that the ARC State is no, indicating that the system cannot be automatically archived.

3 Manual Switch Log

sql> alter system switch logfile;

ALTER SYSTEM switch logfile

*
Line 1th Error:

ORA-01013: User requests to cancel the current operation

After waiting for a long time no response, interrupted operation, manual switch log did not succeed.

4 View the Oracle Database background archive service process

[Oracle@hrmsdb ~]$ Ps-ef|grep Oracle

Oracle 4601 1 0 Jul11? 00:00:04/oracle/product/10.2.0/db_1/bin/

Tnslsnr Listener-inherit

Oracle 5025 1 0 Jul11? 00:00:00/usr/bin/ssh-agent-s

Oracle 20923 1 0 Jul24? 00:00:01 ORA_PMON_HKCHR

Oracle 20925 1 0 Jul24? 00:00:00 ORA_PSP0_HKCHR

Oracle 20927 1 0 Jul24? 00:00:00 ORA_MMAN_HKCHR

Oracle 20929 1 0 Jul24? 00:00:01 ORA_DBW0_HKCHR

Oracle 20931 1 0 Jul24? 00:01:07 ORA_LGWR_HKCHR

Oracle 20933 1 0 Jul24? 00:00:05 ORA_CKPT_HKCHR

Oracle 20935 1 0 Jul24? 00:00:01 ORA_SMON_HKCHR

Oracle 20937 1 0 Jul24? 00:00:00 ORA_RECO_HKCHR

Oracle 20939 1 0 Jul24? 00:00:00 ORA_CJQ0_HKCHR

Oracle 20941 1 0 Jul24? 00:00:01 ORA_MMON_HKCHR

Oracle 20943 1 0 Jul24? 00:00:05 ORA_MMNL_HKCHR

Oracle 20945 1 0 Jul24? 00:00:00 ORA_D000_HKCHR

Oracle 20947 1 0 Jul24? 00:00:00 ORA_S000_HKCHR

Oracle 20953 1 0 Jul24? 00:09:41 ORA_ARC0_HKCHR

Oracle 20955 1 1 Jul24? 00:10:29 ORA_ARC1_HKCHR

Oracle 20959 1 0 Jul24? 00:00:00 ORA_QMNC_HKCHR

Oracle 20967 1 0 Jul24? 00:00:00 ORA_Q000_HKCHR

Oracle 20969 1 0 Jul24? 00:00:00 ORA_Q001_HKCHR

Oracle 21715 1 0 Jul24? 00:00:19 ORACLEHKCHR (Local=no)

Oracle 21765 1 0 Jul24? 00:00:00 ORA_J000_HKCHR

Oracle 21816 1 0 Jul24? 00:00:00 ORA_J001_HKCHR

Oracle 21832 1 0 Jul24? 00:00:00 ORA_J002_HKCHR

Oracle 21839 1 0 Jul24? 00:00:00 ORA_J003_HKCHR

Oracle 21859 1 0 Jul24? 00:00:00 ORA_J004_HKCHR

Oracle 21861 1 0 Jul24? 00:00:00 ORA_J005_HKCHR

Oracle 21886 1 0 Jul24? 00:00:00 ORA_J006_HKCHR

Oracle 21888 1 0 Jul24? 00:00:00 ORA_J007_HKCHR

Root 23187 23186 0 10:39? 00:00:00 Login-Oracle

Oracle 23188 23187 0 10:39 pts/0 00:00:00-bash

Oracle 23216 23188 0 10:39 pts/0 00:00:00 sqlplus

Oracle 23217 23216 0 10:39? 00:00:00 ORACLEHKCHR (description= (local=

YES) (address= (PROTOCOL=BEQ))

Root 23224 23223 0 10:40? 00:00:00 Login-Oracle

Oracle 23225 23224 0 10:40 pts/1 00:00:00-bash

Oracle 23310 23225 0 10:46 pts/1 00:00:00 ps-ef

Oracle 23311 23225 0 10:46 pts/1 00:00:00 grep Oracle

[Oracle@hrmsdb ~]$

Background processes are running correctly.

5) To view the use of Flash_recovery_area space

[Root@hrmsdb/]# Cd/oracle

[Root@hrmsdb oracle]# ls

Admin Flash_recovery_area orainventory Product

[Root@hrmsdb oracle]# du-a-K Flash_recovery_area

4 Flash_recovery_area/hkchr/onlinelog

42456 Flash_recovery_area/hkchr/archivelog/2006_07_15/o1_mf_1_74_2cj1h1jz_.arc

...................

42448 Flash_recovery_area/hkchr/archivelog/2006_07_14/o1_mf_1_68_2cfzwwvt_.arc

512560 flash_recovery_area/hkchr/archivelog/2006_07_14

1469224 Flash_recovery_area/hkchr/archivelog

6988 flash_recovery_area/hkchr/backupset/2006_07_04/o1_mf_ncsnf_tag20060704t1

74229_2bng1o0b_.bkp

876916 flash_recovery_area/hkchr/backupset/2006_07_04/o1_mf_nnndf_tag20060704t1

74229_2bng0cx4_.bkp

883908 flash_recovery_area/hkchr/backupset/2006_07_04

883912 Flash_recovery_area/hkchr/backupset

2353144 FLASH_RECOVERY_AREA/HKCHR

2353148 Flash_recovery_area

[Root@hrmsdb oracle]#


The Flash_recovery_area space uses 2.35GB

6 View the use of each part of the Flash_recovery_area space

Sql> select * from V$recovery_file_dest;

NAME space_limit space_used space_reclaimable number_of_files

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

/oracle/flash_recovery_area 2147483648 2134212608 0 35

Sql> select * from V$flash_recovery_area_usage;

File_type percent_space_used percent_space_reclaimable Number_of_files

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

Controlfile 0 0 0

Onlinelog 0 0 0

Archivelog 69.97 0 40

Backuppiece 30.01 0 2

Imagecopy 0 0 0

Flashbacklog 0 0 0

6 rows have been selected.

found that archivelog accounted for nearly 30% of the 70%,BACKUPPIRCR, so that the space of Flash_recovery_area space has been fully occupied.
4, the resolution process

According to the actual situation that the database currently available storage space is 200GB, Flash_recovery_area space is 2GB, the space of the Flash_recovery_area is modified to 20GB.

Sql> alter system set db_recovery_file_dest_size=20g;

The system has changed.

Sql> select * from V$recovery_file_dest;

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

NAME space_limit space_used space_reclaimable number_of_files

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

/oracle/flash_recovery_area 2.1475E+10 2264587776 0 38

Then look at the status of the log, found redo log in the normal file state.

Sql> select * from V$log;

group# thread# sequence# BYTES members ARC STATUS first_change# first_time

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

1 1 52428800 1 YES ACTIVE 3621973 2 April-July-06

2 1 102 52428800 1 NO current 3650399 2 May-July-06

3 1 52428800 1 YES INACTIVE 3611932 2 April-July-06

Sql> select * from V$flash_recovery_area_usage;

File_type percent_space_used percent_space_reclaimable Number_of_files

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

Controlfile 0 0 0

Onlinelog 0 0 0

Archivelog 7.6 0 43

Backuppiece 4.21 0 2

Imagecopy 0 0 0

Flashbacklog 0 0 0

6 rows have been selected.

Sql>

   5. Summary

The cause of this failure is caused by two simultaneous occurrences:

• One is the Flash_recovery_area space default installation is relatively small, only 2GB, easy to run out;

• The second is due to the archiving of the Veritas Backup, because the backup software is not running, resulting in the archive log has not been deleted in a timely manner.

From this troubleshooting process, we can draw lessons:

· Oracle 10g Database physical space management has changed with previous Oracle, and the Flash_recovery_area space of the archive log is also restricted;
 
• The database system administrator will periodically check the Oracle database log, backup software health, and identify and handle possible failures in advance.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.