ORA-00257 Fault resolution process for Oracle Database

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.