OCI Dataguard Creation Failing with DCS-12542:Enabling flashback on Data Guar...

刘泽宇

现象：
Creating Dataguard via Console for OCI DB System is failing. On the primary DB System, we can see a failed "DG Config service creation" job with error: DCS-12542:Enabling flashback on Data Guard environment failed.

"jobId" : "95661cc5-03bb-4592-8861-b9a73d7ed3e8",
"status" : "Failure",
"message" : "DCS-12542:Enabling flashback on Data Guard environment failed. ",
"percentageProgress" : "66%",
"description" : "DG Config service creation",
"cause" : "*Cause:  Database might not be available to perform the operation.",
"action" : "*Action:  Please verify the database availability before retry. If retry fails, contact Oracle Support Services for assistance."


原因：
Analyzing the DCS Agent log, we can see errors while enabling flashback

alter database flashback on
*
ERROR at line 1:
ORA-04031: unable to allocate 31874944 bytes of shared memory ("shared pool","unknown object","sga heap(1,0)","flashback generation buffer")
During the Dataguard creation workflow, we first provision the standby db system. Before starting the duplicate operation, we are trying to enable Flashback Database on the primary and it is failing with ORA-04031. In above case, it could not get a contiguous space of 31874944 bytes in Shared Pool for the purpose of flashback generation buffers.

处理方法：
Below queries can be used to check SGA usage and specifically memory used by top shared pool constituents:

SQL> set lines 180
SQL> col COMPONENT format a30
SQL> select INST_ID,COMPONENT,CURRENT_SIZE/1024/1024,MIN_SIZE/1024/1024,MAX_SIZE/1024/1024,USER_SPECIFIED_SIZE/1024/1024,LAST_OPER_TYPE,LAST_OPER_MODE,LAST_OPER_TIME from GV$MEMORY_DYNAMIC_COMPONENTS;
SQL> select INST_ID,CURRENT_SIZE/1024/1024 from GV$SGA_DYNAMIC_FREE_MEMORY;
SQL> select * from (select INST_ID,NAME,BYTES/1024/1024 from gv$sgastat where POOL = 'shared pool' order by BYTES DESC) where rownum < 20;
Based on above, we can take a call whether a shared pool flush is enough or database instance needs to be restarted. Below can be used to flush the shared pool and try to see if flashback can be turned on:

SQL> alter system flush SHARED_POOL;
SQL> alter database flashback on;
Re-try the dataguard creation process from OCI Console.