重庆思庄Oracle、Redhat认证学习论坛

标题: 由于GUARANTEE RESTORE POINT未及时清理导致数据库挂起 [打印本页]

作者: jiawang 时间: 2020-6-3 10:11
标题: 由于GUARANTEE RESTORE POINT未及时清理导致数据库挂起
报错现象：2020-06-03T09:34:03.744340+08:00
*************************************************************
Unable to allocate flashback log of 7389 blocks from
current recovery area of size 107374182400 bytes.
Recovery Writer (RVWR) is stuck until more space
is available in the recovery area.
Unable to write Flashback database log data because the
recovery area is full, presence of a guaranteed
restore point and no reusable flashback logs.
2020-06-03T09:34:44.139590+08:00
License high water mark = 837

解决办法：
SQL> show parameter db_recovery_file

NAME                               TYPE       VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest             string    /dbbackup
db_recovery_file_dest_size          big integer 100G

SQL> alter system db_recovery_file_dest_size=150G scope=spfile;
alter system db_recovery_file_dest_size=150G scope=spfile

SQL> shutdown abort
ORACLE instance shut down.

SQL> startup;
ORACLE instance started.

Total System Global Area 6442448984 bytes
Fixed Size                8910936 bytes
Variable Size          805306368 bytes
Database Buffers       5620367360 bytes
Redo Buffers             7864320 bytes
Database mounted.
Database opened.

SQL> r
  1* select * from v$flash_recovery_area_usage

FILE_TYPE             PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES    CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE                            0                      0             0       0
REDO LOG                            .33                      0             1       0
ARCHIVED LOG                            0                      0             0       0
BACKUP PIECE                         .19                      0             2       0
IMAGE COPY                            0                      0             0       0
FLASHBACK LOG                      66.49                      0          380       0
FOREIGN ARCHIVED LOG                   0                      0             0       0
AUXILIARY DATAFILE COPY                0                      0             0       0

8 rows selected.
可以看出FLASHBACK LOG 占用了大量空间

SQL> show parameter flash
NAME                               TYPE       VALUE
------------------------------------ ----------- ------------------------------
db_flash_cache_file                string
db_flash_cache_size                big integer 0
db_flashback_retention_target       integer    1440

FALSHBACK RETENTION只设置为1440分钟

SQL> select * from v$restore_point;

   SCN DATABASE_INCARNATION# GUA STORAGE_SIZE
---------- --------------------- --- ------------
TIME
---------------------------------------------------------------------------
RESTORE_POINT_TIME                                                       PRE
--------------------------------------------------------------------------- ---
NAME
--------------------------------------------------------------------------------
PDB CLE PDB_INCARNATION# REP    CON_ID
--- --- ---------------- --- ----------
1.4484E+13                   7 YES 1.0676E+11
12-MAY-20 09.14.38.000000000 AM
                                                                        YES

   SCN DATABASE_INCARNATION# GUA STORAGE_SIZE
---------- --------------------- --- ------------
TIME
---------------------------------------------------------------------------
RESTORE_POINT_TIME                                                       PRE
--------------------------------------------------------------------------- ---
NAME
--------------------------------------------------------------------------------
PDB CLE PDB_INCARNATION# REP    CON_ID
--- --- ---------------- --- ----------
UPDATE_RSP
NO  NO                0 NO          0

RESTORE POINT还存着，一直没删除，那FLASHBACK空间不足很可能就是由它导致的，和相关人员确认后，该RESTORE POINT早就已经不需要了，但是一直遗漏没删除。

SQL> drop restore point UPDATE_RSP；
Restore point dropped.

SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;

FILE_TYPE             PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES    CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE                            0                      0             0       0
REDO LOG                            .33                      0             1       0
ARCHIVED LOG                            0                      0             0       0
BACKUP PIECE                         .19                      0             2       0
IMAGE COPY                            0                      0             0       0
FLASHBACK LOG                   .93                         0          380       0
FOREIGN ARCHIVED LOG                   0                      0             0       0
AUXILIARY DATAFILE COPY                0                      0             0       0

删除GUARANTEE RESTORE POINT后，FLASHBACK的空间也立即就降下来了。

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)