RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row

Problem details

When login with RMAN, it returned the messages below

-bash-4.1$ rman target /

Recovery Manager: Release 12.1.0.1.0 – Production on Tue Dec 15 10:37:47 2020

Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.

connected to target database: PCMS (DBID=1114729796)
RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row
RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT rows
ORACLE error from target database:
ORA-04031: unable to allocate 36424 bytes of shared memory (“shared pool”,”unknown object”,”sga heap(1,0)”,”krbmror”)

Root cause

It may be that there are too many parsed SQL in shared pool.

Run the following SQL to find out pared SQL

with potential_duplicates as(select
SUM(executions) as “Executions”,
SUM(elapsed_time/1000000) as “Elapsed Time”,
SUM(cpu_time/1000000) as “CPU Time”,
COUNT() as “Statements”, SUBSTR(sql_text,1,40) as “SQL” from v$sqlarea group by SUBSTR(sql_text,1,40) having COUNT() > 5
order by count(*) DESC)
select *
from potential_duplicates
where rownum < 11;

The statement “UPDATE restful_traffic_t set maxuploadsp” show 1827 executions and 1827 statements. This has occupied high shared memory.

---

  1827      4.12118   3.810151       1827 UPDATE restful_traffic_t set maxuploadsp
   563     1.333165   1.218421        563 update bgp_pe_interf_t set bgp_status_n
    22      .075963    .064438         20 update bgp_pe_interf_t set tra_v = '-4'
    95      .061647    .052679         12 update bgp_pe_interf_t set tra_v = '-1'
    10      .029803    .028177         10 update bgp_pe_interf_t set tra_v = '0:29
     7      .035988    .020511          7 update bgp_pe_interf_t set tra_v = '0:0'

---

Solutions

1. Flush the shared pool
2. SQL> alter system flush shared_pool;
4. Check if the cursor_sharing is set to force

SQL> show parameter curs

NAME TYPE VALUE

---

cursor_bind_capture_destination string memory+disk
cursor_sharing string EXACT
cursor_space_for_time boolean FALSE
open_cursors integer 300
session_cached_cursors integer 50
SQL> alter system set cursor_sharing=force scope=both;