Shulou(Shulou.com)06/01 Report--

The problem of full flashback area is also a common problem. The most important thing is that the flashback setting size and archive are stored in the flashback directory by default. Coincidentally, when we encounter this problem again today, we record the processing steps for reference only to those who encounter such problems.

I. description of error phenomena

1) error message on the application side

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-side error message

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

Second, error analysis

From the application log, the DB connection pool cannot be initialized because it cannot be archived, and it can be judged that it is due to the full disk space of the archived files in the database.

From the database log, it is found that the flashback space is indeed full, which can be verified again by looking at the server disk space and the size of the flashback space.

Third, the detailed processing process

1) Log in to the database server to 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 that the flashback space is full

Cd / home/U01/app/oracle/diag/rdbms/testdb/testdb/tracetail-n 35 alert_testdb.logORA-19815: WARNING: db_recovery_file_dest_size of 42949672960 bytes is 100.005% used, and has 0 remaining bytes available.

3) Log in to the database to check the flashback path and the usage of flashback space

View the flashback space setting size

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 flashback path

Col name for a 60th set line 200th 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

Check the disk size of the flashback space

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 flashback directory usage size

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

View flashback space usage

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

4) delete archives and resize the flashback area

From above, it is true that the flashback space is full, and the main culprit occupying the flashback area is the archived log. There are two ways to solve the problem:

Option 1: delete redundant archives

The best way to delete an archive is through the rman tool. If you delete the file database directly, you will not recognize the release of the flashback area.

[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'

Or delete the archive log from seven days ago using the following statement

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

Note: (it is recommended to adjust the archive path or adjust the size of the flashback area after deleting the archive to vacate enough flashback area.)

Option 2: resize the flashback 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### database is 12c, so it is not allowed to operate under pdb. You need to switch to sys to operate SQL > conn / as sysdba;Connected.SQL > alter system set db_recovery_file_dest_size=100G scope=both; System altered.

5) check the usage and size of the flashback 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 8 rows selected.

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

So far, the problem has been completely solved.

The above article is based on the ORA-19815 flashback space to deal with the full problem is the editor to share with you all the content, I hope to give you a reference, but also hope that you support more.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.