CONTROL FILE 0 0 0 0
REDO LOG 7.45 0 7 0
ARCHIVED LOG 100 0 76 0
BACKUP PIECE 0 0 0 0
IMAGE COPY 0 0 0 0
FLASHBACK LOG 0 0 0 0
FOREIGN ARCHIVED LOG 0 0 0 0
AUXILIARY DATAFILE COPY 0 0 0 0
4)删除归档、调整闪回区大小
从上面看确实是闪回空间满了,而占用闪回区满的罪魁祸首就是归档日志。要解决的方法有两个:
方案1:删除多余的归档
最佳删除归档的途径是通过rman工具做,如果直接删除文件数据库是识别不到闪回区释放的。
[oracle@teststd trace]$ rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Sat Nov 26 13:00:28 2016
Copyright (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' ;
或者利用下面的语句删除七天前的归档日志
RMAN> DELETE NOPROMPT ARCHIVELOG UNTIL TIME 'SYSDATE-7' ;
注意:(一般删除归档腾出足够的闪回区后,建议调整归档路径或者调整足够闪回区大小)
方案2:调整闪回区大小
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
###数据库为12c,不允许在pdb下操作,需要切换到sys下操作
SQL> conn /as sysdba;
Connected.
SQL> alter system set db_recovery_file_dest_size=100G scope=both;
System altered.
5)查看闪回区使用情况和其大小
[oracle@teststd trace]$ du -hs /home/U01/app/oracle/fast_recovery_area
27G /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 0
REDO LOG 2.98 0 7 0
ARCHIVED LOG 17.08 0 82 0