ORA-38706 无法开启FLASHBACK DATABASE

适用范围
Oracle Database 19.15+

问题概述
无法开启FLASHBACK DATABASE模式时，报ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-04031: unable to allocate 31874944 bytes of shared memory (“shared
pool”,“unknown object”,“sga heap(2,0)”,“flashback generation buffer”)

问题原因
Oracle数据库Share pool中的flashback generation buffer不足

解决方案
设置如下参数，并重启数据库使参数生效：

alter system set “_allocate_flashback_buffer”=TRUE scope=spfile sid=’*’;

操作过程

1、开启FLASHBACK DATABASE模式
SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-04031: unable to allocate 31874944 bytes of shared memory (“shared
pool”,“unknown object”,“sga heap(2,0)”,“flashback generation buffer”)
2、调整_allocate_flashback_buffer参数

alter system set “_allocate_flashback_buffer”=TRUE scope=spfile sid=’*’;
3、重启数据库

host01# srvctl status db -d DB
Instance DB1 is running on node host01
Instance DB2 is running on node host02
host01# srvctl stop db -d DB
host01# srvctl status db -d DB
Instance DB1 is not running on node host01
Instance DB2 is not running on node host01
host01# srvctl start db -d DB

参数生效后数据库启动期间预分配闪回缓冲区，即使我们以后不打算创建还原点，也会分配内存。
4、开启FLASHBACK DATABASE模式

SQL> alter database flashback on;
5、创建restore point

SQL> CREATE RESTORE POINT BEFORE_CHANGE GUARANTEE FLASHBACK DATABASE;
Restore point created.

SQL> select NAME,SCN,GUARANTEE_FLASHBACK_DATABASE,DATABASE_INCARNATION# from v$restore_point;
NAME SCN GUA DATABASE_INCARNATION#
———————-—————————–
BEFORE_CHANGE 6877750 YES 2
现在可以正常创建restore point

-the end-