ORA-00257 Solution

Original: http://blog.chinaunix.net/u/26381/showart_373304.html

Starting from Oracle9i, the UNDO log file provides the flash-back query function. Due to its limitations, transactions dependent on undo logs cannot be overwritten, therefore, at the beginning of Oracle10g, we adopted a new flashback log to implement this function, which is more powerful and can return the database to a certain time point in the past. The default file size is 2 GB. But after a period of time, quickly reached 2g, at this time there will be a ORA-00257 error.
Two solutions:
The first is to disable the log flash function, which is a good method for the development environment, because the development environment does not pursue data security or anything. Use the following statement.
Alter database flashback off

The second method is to increase the maximum size of the flash log file. As follows:
Alter system set db_recovery_file_dest_size = 10g
At this time, you can view the usage in the V $ flash_recovery_area_usage view. At this time, the usage (value of the percent_space_used column) has been greatly reduced. Use the following statement to view the system log file.

Select * from V $ log
Now the redo log file can be written normally.

-------------------------------------------------------------------
Explain the troubleshooting process of Oracle Database ORA-00257 faults
Http://developer.weaseek.com/2008/0715/47199469_1.shtml 15:39:13 Source: SCID
In the actual project encountered a ORA-00257 error (insufficient space error), by looking for information, the vast majority of said this is due to archiving too many logs, occupying all the hard disk space caused, you can simply delete logs or increase storage space. However, I found that there is still a large storage space on Oracle 10 Gb, but this error is also reported. It turned out to be a new feature in Oracle 10 Gb, resulting from the management of Flash recovery.

Oracle Database is currently the most common large database system in the industry, I encountered a ORA-00257 error in the actual project (insufficient space error), by looking for information, the vast majority of said this is due to too many archiving logs, this problem can be solved by simply deleting logs or increasing the storage space. However, I found that there is still a large storage space on Oracle 10 Gb, but this error is also reported. It turned out to be a new feature in Oracle 10 Gb, resulting from the management of Flash recovery.

1. software and hardware environment

Server HP ProLiant dl580g4 (Intel Xeon 3.16 GHz/4 GB/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

The database system has been trial run for more than half a month, on the evening of July 24 after connecting to the database for data updates when a ORA-00257 error occurs, such.

An archive error is prompted. You can find an oracle error.CodeThe explanation is that the hard disk space is insufficient and archive logs need to be deleted to increase space, but the server space is 200 GB. Currently, only about 10 Gb is used. Why?

3. diagnosis process:

(1) view archiving logs of Oracle databases

[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_000098_2d969d5h _. Arc

O%mf_%95_2d9537cs _. Arc o%mf_%97_2d956km0 _. Arc

It indicates that the database archiving process is normal before the problem occurs.

(2) view database redolog Information

[Oracle @ hrmsdb ~] $ Sqlplus/nolog

SQL * Plus: Release 10.2.0.1.0-production on Tuesday July 25 10:44:18 2006

Copyright (c) 1982,200 5, Oracle. All rights reserved.

SQL> connect/As sysdba

Connected.

SQL> select * from V $ log;

Group # thread # sequence # bytes members arc status first_change # first_time

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

1 1 101 52428800 1 no current 3621973 24-7 month-06

2 1 99 52428800 1 No inactive 3600145 24-7 month-06

3 1 100 52428800 1 No inactive 3611932 24-7 month-06

If the arc status is no, the system cannot perform automatic archiving.

(3) manually switch logs

SQL> alter system switch logfile;

Alter system switch logfile

*

Row 3 has an error:

ORA-01013: user request to cancel current action

After waiting for a long time for no response, the operation is interrupted, and the manual switch log fails.

(4) view the Oracle database archive 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_arcw.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_q0020.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_j0020.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? 00:00:00 login -- Oracle

Oracle 23188 23187 0 00:00:00 pts/0-Bash

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

Oracle 23217 23216 0? At 00:00:00 oraclehkchr (description = (local =

Yes) (address = (Protocol = beq )))

Root 23224 23223 0? 00:00:00 login -- Oracle

Oracle 23225 23224 0 00:00:00 pts/1-Bash

Oracle 23310 23225 0 00:00:00 pts/1 PS-ef

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

[Oracle @ hrmsdb ~] $

Background processes are running normally.

(5) view the space usage of flash_recovery_area

[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_000068_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] #

Flash_recovery_area uses 2.35 GB Space

(6) view the usage of each part in 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

You have selected 6 rows.

It is found that archivelog accounts for nearly 70%, and backuppircr accounts for 30%. In this way, the flash_recovery_area space is fully occupied.

4. Solution Process

The flash_recovery_area space is changed to 20 GB based on the actual available storage space of the database of 200 GB and 2 GB.

SQL> alter system set db_recovery_file_dest_size = 20g;

The system has been 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, check the log status and find that the redo log is in the normal archiving status.

SQL> select * from V $ log;

Group # thread # sequence # bytes members arc status first_change # first_time

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

1 1 101 52428800 1 Yes active 3621973 24-7 month-06

2 1 102 52428800 1 no current 3650399 25-7 month-06

3 1 100 52428800 1 Yes inactive 3611932 24-7 month-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

You have selected 6 rows.

SQL>

Summary

The cause of this fault is caused by two reasons:

· The first is that the flash_recovery_area space is relatively small by default and only 2 GB is used up;

· The second reason is that the archive log is not deleted in time because the backup software is not running through Veritas Backup.

From this troubleshooting, we can draw lessons:

· The physical space management mode of Oracle 10 Gb database has changed from the previous Oracle, and additional restrictions are imposed on the flash_recovery_area space where archiving logs are stored;

· Database System Administrators should regularly check the running status of Oracle Database archiving logs and backup software to detect and handle possible faults in advance.