This content has been marked as final. Show 6 replies
Please help me in figuring out the difference for below query outputs.Sorry, I don't know..
Also, if there any chance of any ORA-01555 or ORA-30036??Usually, V$UNDOSTAT will be used for investigating 1555 or 30036.
- Master Note for ORA-1555 Errors [ID 1307334.1]
- Common Diagnostic Scripts for AUM problems [ID 746173.1]
It is a bug due the dependent parameters for undo retention are instance-specific and really time-sensitive.
It's difficult to come up with a perfect 'global' solution. So there is no fix for this.
So use the workaround of note bellow to determine the correct result.
*DBA_UNDO_EXTENTS shows wrong results from different nodes of RAC [ID 1492313.1]*
Thanks for the correct response. Work around mentioned by ORACLE is to get information from active undo. But in our case both the undo tablespaces are active. So how to pro actively monitor UNDO usage.
As of now on node 2, we can see that UNEXPIRED blocks are very high as compared to expired and active blocks. UNDO_RETENTION is set to 36000 (As per application requirement i.e. remedy 7.2).
SQL> select status, sum(bytes)/1024/1024/1024 sum_bytes
group by status
2 3 4
1 select status, sum(bytes)/1024/1024/1024 sum_bytes
2 from dba_undo_extents
3* group by status
You must connect on each Instance and execute SQL Query below:
e.g Instance 1
sqlplus sys/<passoword>@node1:1521/<SID> as sysdba SQL> select status, sum(bytes)/1024/1024/1024 sum_bytes from dba_undo_extents where TABLESPACE_NAME in (select value from v$parameter where name = 'undo_tablespace') group by status