Oracle real-world case-Flashback storage space depletion solution (ORA-19815 solution)

This is a real oracleORA-19815 solution) case, hope to help everyone.

Today, the platform of a friend company encountered slow login and slow data query, and the failure to log on to oracle monitored through spotlight. No problems were found by checking the system memory and processes, finally, I found myself. I first checked the memory and processes of the platform, and did not find any problems. Finally, I checked the oracle alarm log and found the following problems:

 
 

  
  
ARC0: Failed to archive thread 1 sequence 53 (19809)  
  
  
Sun Jun 10 23:12:12 2012  
  
  
Errors in file /home/oracle/admin/BGTP/bdump/bgtp_arc1_3906.trc:  
  
  
ORA-19815: WARNING: db_recovery_file_dest_size of 2147483648 bytes is 100.00% used, and has 0 remaining bytes available.  
  
  
Sun Jun 10 23:12:12 2012  
  
  
************************************************************************  
  
  
You have following choices to free up space from flash recovery area:  
  
  
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,  
  
  
   then consider changing RMAN ARCHIVELOG DELETION POLICY.  
  
  
2. Back up files to tertiary device such as tape using RMAN  
  
  
   BACKUP RECOVERY AREA command.  
  
  
3. Add disk space and increase db_recovery_file_dest_size parameter to  
  
  
   reflect the new space.  
  
  
4. Delete unnecessary files using RMAN DELETE command. If an operating  
  
  
   system command was used to delete files, then use RMAN CROSSCHECK and  
  
  
   DELETE EXPIRED commands.  
  
  
************************************************************************  
 
 

Is the ORA-19815 problem, through metalink query, is the flash back area space is exhausted, the solution I use to increase the flash back area storage space, to solve this problem now first check v $ recovery_file_dest try

650) this. width = 650; "border =" 0 "alt =" "src =" http://www.bkjia.com/uploads/allimg/131229/20061Q430-0.jpg "/>

It can be found that the available space is 0, and the usage in the view Flash back area

 

 
 

  
  
SQL> select file_type, percent_space_used as used,percent_space_reclaimable as reclaimable, number_of_files as "number" from v$flash_recovery_area_usage;   
  
  
 
  
  
FILE_TYPE      USED RECLAIMABLE number  
  
  
------------ ---------- ----------- ----------  
  
  
CONTROLFILE       0       0      0  
  
  
ONLINELOG         0       0      0  
  
  
ARCHIVELOG    98.65       0     51  
  
  
BACKUPPIECE       0       0      0  
  
  
IMAGECOPY         0       0      0  
  
  
FLASHBACKLOG          0       0      0  
  
  
 
  
  
6 rows selected.  
 
 

We found that 98.65% has been used. The following method is used to log on to the database.

 
 

  
  
[oracle@master ~]$ sqlplus / as sysdba  
  
  
 
  
  
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Jun 10 22:41:54 2012  
  
  
 
  
  
Copyright (c) 1982, 2005, Oracle.  All rights reserved.  
  
  
 
  
  
 
  
  
Connected to:  
  
  
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production  
  
  
With the Partitioning, OLAP and Data Mining options  
 
 

First, check the current flash back area size.

 
 

  
  
SQL> show parameter db_recovery_file_dest  
  
  
 
  
  
NAME                     TYPE    VALUE  
  
  
------------------------------------ ----------- ------------------------------  
  
  
db_recovery_file_dest            string  /home/oracle/flash_recovery_ar  
  
  
                         ea  
  
  
db_recovery_file_dest_size       big integer 2G  
  
  
SQL> archive log list;  
  
  
Database log mode          Archive Mode  
  
  
Automatic archival         Enabled  
  
  
Archive destination        USE_DB_RECOVERY_FILE_DEST  
  
  
Oldest online log sequence     53  
  
  
Next log sequence to archive   53  
  
  
Current log sequence           55  
 
 

We can see that the size of the flash back area is 2 GB, So I expanded it to 10 Gb.

 
 

  
  
SQL> alter system set db_recovery_file_dest_size=10G scope=both;  
  
  
 
  
  
System altered.  
 
 

Then, check the usage of the flash back area.

 
 

  
  
SQL>  select * from v$recovery_file_dest;  
  
  
 
  
  
NAME                      SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES  
  
  
---------------------------------------- ------------ ---------- ----------------- ---------------  
  
  
/home/oracle/flash_recovery_area      10737418240 2383963648         0      57  
  
  
 
  
  
SQL>  show parameter db_recovery_file_dest  
  
  
 
  
  
NAME                     TYPE    VALUE  
  
  
------------------------------------ ----------- ------------------------------  
  
  
db_recovery_file_dest            string  /home/oracle/flash_recovery_ar  
  
  
                         ea  
  
  
db_recovery_file_dest_size       big integer 10G  
 
 

Check the usage of the flash back area.

 
 

  
  
SQL> select file_type, percent_space_used as used,percent_space_reclaimable as reclaimable, number_of_files as "number" from v$flash_recovery_area_usage;   
  
  
 
  
  
FILE_TYPE      USED RECLAIMABLE number  
  
  
------------ ---------- ----------- ----------  
  
  
CONTROLFILE       0       0      0  
  
  
ONLINELOG         0       0      0  
  
  
ARCHIVELOG     22.2       0     57  
  
  
BACKUPPIECE       0       0      0  
  
  
IMAGECOPY         0       0      0  
  
  
FLASHBACKLOG          0       0      0  
  
  
 
  
  
6 rows selected.  
 
 

Reduced from 98.65% to 22.2%. Check the alarm log.

 
 

  
  
db_recovery_file_dest_size of 10240 MB is 20.14% used. This is a  
  
  
user-specified limit on the amount of space that will be used by this  
  
  
database for recovery-related files, and does not reflect the amount of  
  
  
space available in the underlying filesystem or ASM diskgroup.  
  
  
Sun Jun 10 23:12:12 2012  
  
  
ALTER SYSTEM SET db_recovery_file_dest_size='10G' SCOPE=BOTH;  
  
  
Sun Jun 10 23:12:15 2012  
  
  
Archiver process freed from errors. No longer stopped  
  
  
Sun Jun 10 23:12:15 2012  
  
  
Thread 1 advanced to log sequence 56  
  
  
  Current log# 1 seq# 56 mem# 0: /home/oracle/oradata/BGTP/redo01.log  
  
  
Thread 1 advanced to log sequence 57  
  
  
  Current log# 2 seq# 57 mem# 0: /home/oracle/oradata/BGTP/redo02.log  
  
  
Sun Jun 10 23:12:27 2012  
  
  
Thread 1 cannot allocate new log, sequence 58  
  
  
Checkpoint not complete  
  
  
  Current log# 2 seq# 57 mem# 0: /home/oracle/oradata/BGTP/redo02.log  
  
  
Thread 1 advanced to log sequence 58  
  
  
  Current log# 3 seq# 58 mem# 0: /home/oracle/oradata/BGTP/redo03.log  
  
  
Sun Jun 10 23:12:52 2012  
  
  
Thread 1 advanced to log sequence 59  
  
  
  Current log# 1 seq# 59 mem# 0: /home/oracle/oradata/BGTP/redo01.log  
  
  
Sun Jun 10 23:12:52 2012  
  
  
Trying to expand controlfile section 11 for Oracle Managed Files  
  
  
Expanded controlfile section 11 from 56 to 112 records  
  
  
Requested to grow by 56 records; added 2 blocks of records  
 
 

The ORA-19815 error is no longer reported repeatedly, and now the flash back area space usage is full problem has been solved. Then, my spotlight monitoring can monitor oracle. The following figure shows how spotlight monitors oracle.

650) this. width = 650; "border =" 0 "alt =" "src =" http://www.bkjia.com/uploads/allimg/131229/20061VK0-1.jpg "/>

There are a lot of ways to solve the ORA-19815, I am just one of them, thanks to oracle's metalink

This article is from the "Yin-Technical Exchange" blog, please be sure to keep this source http://dl528888.blog.51cto.com/2382721/895168