Based on the solution of ORA-19815 flash back space explosion problem, ora-19815 flash back

Based on the solution of ORA-19815 flash back space explosion problem, ora-19815 flash back

The problem that the flash back area is full is also frequently encountered. The most important thing is that the flash back setting size and archive are stored in the flash back directory by default. This problem happened again today, record the handling steps for your reference only.

I. Error description

1) application error message

Error: 2016-11-26 11:45:25 ORA-00257: Archiver error. Connect AS SYSDBA only until resolved.Error: 2016-11-26 11:45:25 ORA-00257: Archiver error. Connect AS SYSDBA only until resolved.Error: 2016-11-26 11:45:25 init connpool:one or more conn open error.

2) database errors

Sat Nov 26 12:13:14 2016Errors in file /home/U01/app/oracle/diag/rdbms/testdb/testdb/trace/testdb_arc2_929.trc:ORA-19815: WARNING: db_recovery_file_dest_size of 42949672960 bytes is 100.00% used, and has 0 remaining bytes available.Sat Nov 26 12:13:14 2016************************************************************************You have following choices to free up space from 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.************************************************************************Sat Nov 26 12:13:14 2016Errors in file /home/U01/app/oracle/diag/rdbms/testdb/testdb/trace/testdb_arc2_929.trc:ORA-19809: limit exceeded for recovery filesORA-19804: cannot reclaim 524288000 bytes disk space from 42949672960 limit

Ii. Error Analysis

From the perspective of application logs, the Database Connection Pool cannot be initialized and opened because it cannot be archived. It can be determined that the disk space of the database archive file is full.

Check the database log and find that the flash back space is full. Check the server disk space and the size of the flash back space to verify again.

Iii. Detailed handling process

1) log on to the database server and view disk space usage information.

[oracle@teststd trace]$ df -hFilesystem   Size Used Avail Use% Mounted on/dev/sda5    9.9G 2.6G 6.9G 28% /tmpfs      32G  18M  32G  1% /dev/shm/dev/sda1    388M  62M 307M 17% /boot/dev/sda6    1.6T 506G 1017G 34% /home/dev/sda2    20G 508M  19G  3% /var

2) Check the Database Error Log and find the flash back space is full.

cd /home/U01/app/oracle/diag/rdbms/testdb/testdb/tracetail -n 35 alert_testdb.log

ORA-19815: WARNING: db_recovery_file_dest_size of 42949672960 bytes is 100.00% used, and has 0 remaining bytes available.

3) log on to the database to view the flash path and usage of the flash space.

View the size of the flash bucket settings

SQL> show parameter recover; NAME         TYPE    VALUE------------------------------------ ----------- ------------------------------db_recovery_file_dest   string   /home/U01/app/oracle/fast_recovery_areadb_recovery_file_dest_size      big integer 40Gdb_unrecoverable_scn_tracking    boolean   TRUErecovery_parallelism         integer   0

Or use SQL to view the flash path.

Col name for a60;Set line 200;SQL> select * from v$recovery_file_dest ; NAME                  SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES   CON_ID------------------------------------------------------- ----------- ---------- ----------------- --------------- ----------/home/U01/app/oracle/fast_recovery_area         4.2950E+10 2.1538E+10         0       89     0

View the disk size of the flash drive.

SQL> !df -h /home/U01/app/oracle/fast_recovery_areaFilesystem   Size Used Avail Use% Mounted on/dev/sda6    1.6T 504G 1019G 34% /home

View the size of the flashback directory

SQL> !du -hs /home/U01/app/oracle/fast_recovery_area40G /home/U01/app/oracle/fast_recovery_area

View the usage of the flash back space

SQL> select * from V$RECOVERY_AREA_USAGE; FILE_TYPE    PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES   CON_ID----------------------- ------------------ ------------------------- --------------- ----------CONTROL FILE               0             0        0     0REDO LOG           7.45             0        7     0ARCHIVED LOG             100             0       76     0BACKUP PIECE               0             0        0     0IMAGE COPY                0             0        0     0FLASHBACK LOG              0             0        0     0FOREIGN ARCHIVED LOG           0             0        0     0AUXILIARY DATAFILE COPY         0             0        0     0

4) Delete the archive and adjust the size of the flash back area

From the above, it seems that the flash back space is full, and archive logs are the culprit for occupying the flash back space. There are two solutions:

Solution 1: Delete redundant Archives

The best way to delete an archive is through the rman tool. If you delete a file database directly, you cannot identify the flash back zone release.

[oracle@teststd trace]$ rman target /Recovery Manager: Release 12.1.0.2.0 - Production on Sat Nov 26 13:00:28 2016Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.connected to target database: TESTDB (DBID=2708971821)RMAN> crosscheck archivelog all;RMAN> delete expired archivelog all;RMAN> DELETE NOPROMPT ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7' ;

You can also use the following statement to delete the archived logs seven days ago.

RMAN> DELETE NOPROMPT ARCHIVELOG UNTIL TIME 'SYSDATE-7' ;

Note: (it is recommended that you adjust the archiving path or the size of the flashing area after you delete the archive)

Solution 2: adjust the size of the flash back area

SQL> alter system set db_recovery_file_dest_size = 100G scope = both; alter system set db_recovery_file_dest_size = 100G scope = both * ERROR at line 1: ORA-65040: operation not allowed from within a pluggable database ### the database is 12c and cannot be operated under pdb. You need to switch to SQL> conn/as sysdba; Connected under sys. SQL> alter system set db_recovery_file_dest_size = 100G scope = both; System altered.

5) view the usage and size of the flash back area

[oracle@teststd trace]$ du -hs /home/U01/app/oracle/fast_recovery_area27G   /home/U01/app/oracle/fast_recovery_area SQL> select * from V$RECOVERY_AREA_USAGE;FILE_TYPE    PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES   CON_ID----------------------- ------------------ ------------------------- --------------- ----------CONTROL FILE           0       0         0     0REDO LOG          2.98        0         7     0ARCHIVED LOG         17.08       0         82     0BACKUP PIECE           0       0         0     0IMAGE COPY            0       0         0     0FLASHBACK LOG          0       0         0     0FOREIGN ARCHIVED LOG       0       0         0     0AUXILIARY DATAFILE COPY         0     0        0     0 8 rows selected.

6) notify the system administrator to start the application and start the application normally.

Now the problem has been completely solved.

The above based on the ORA-19815 flash back space Full problem is small make up to share with everyone all the content, hope to give you a reference, also hope you can support a lot of help house.