Troubleshooting of ORA-00257 errors

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 the archiving of too many logs, occupying all the hard disk space remaining

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 the archiving of too many logs, occupying all the hard disk space remaining

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 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.

1. software and hardware environment
Server HP Proliant DL580G4
Operating System RHEL5.4
Database Oracle 10.2.0.4.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. 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, it only takes about 10 GB, why?

3. diagnosis process:
(1) view archiving logs of ORACLE databases
[Root @ db2/] # cd/oracle/flash_recovery_area/HKCHR/archivelog
[Root @ db2 archivelog] # ls
You can see that the database archiving process is normal before the problem occurs.
(2) view database REDOLOG Information
[Oracle @ db2 ~] $ Sqlplus "/as sysdba"
SQL> select * from v $ log;
It can be found that the ARC status is NO, indicating that the system cannot archive normally.
(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 @ db2 ~] $ Ps-ef | grep oracle
......
Oracle 20923 1 0 Jul24? 00:00:01 ora_pmon_hkchr
Oracle 20925 1 0 Jul24? 00:00:00 ora_psp0_hkchr
......
[Oracle @ hrmsdb ~] $
Background processes are running normally.
(5) view the space usage of FLASH_RECOVERY_AREA
[Root @ db2/] # cd/oracle
[Root @ db2 oracle] # ls
Admin flash_recovery_area oraInventory product
[Root @ db2 oracle] # du-a-k flash_recovery_area
......
[Root @ db2 oracle] #
FLASH_RECOVERY_AREA uses 3.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
Change the FLASH_RECOVERY_AREA space to 50 GB based on the fact that the current available storage space of the database is 200 GB and FLASH_RECOVERY_AREA is 2 GB.
SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE = 50g scope = both; (if an error is reported, use scope = spfile)
The system has been changed.