Oracle 10g ORA-00257 solution example

Today I also encountered a ORA-00257, although not exactly the same, but also almost, are flash_recovery_area is too small. The following is reproduced from the network.

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.

The error message "ARCHIVE error" is displayed. by searching for the ORACLE error code, it is explained that the hard disk space is insufficient. You need to delete the archive log to increase the 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 is not successful.
(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.

1 2 3 Next Page