Friday, 19 February 2016

minact-scn: useg scan erroring out with error e:12751

Lately I've met in alert.log entries the following:
minact-scn: useg scan erroring out with error e:12751
According to the Metalink it is bound with high load on the system and that an undo segment scan has failed with an ORA-12751 error.
And ORA-12751 is described as "cpu time or run time policy violation"

In my case though the load on the system is not very high and the system is actually used currently for one task only, which is a massive data pump import. The import leads to temporary lack of space for archivelogs (ORA-16038, ORA-19504, ORA-16014). Of course there is a pressure on the instance trying to save archivelogs and I suppose that this state causes such notes in alert.log.

Also emergency flushes occur then:
Active Session History (ASH) performed an emergency flush. This may mean that ASH is undersized. If emergency flushes are a recurring issue, you may consider increasing ASH size by setting the value of _ASH_SIZE to a sufficiently large value. Currently, ASH size is 8388608 bytes. Both ASH size and the total number of emergency flushes since instance startup can be monitored by running the following query:
select total_size,awr_flush_emergency_count from v$ash_info; 

ORA-00093: _shared_pool_reserved_min_alloc must be between 4000 and 0

Today I run into the following error:
ORA-00093: _shared_pool_reserved_min_alloc must be between 4000 and 0
What was the most annoying, it was the fact, I can not start the instance due to this error occurence. In general within a screen session I opened sqlplus session and within it the vim for editing the newly generated pfile. Then I shut down the instance and further I edited some memory settings. So far, so good - I started the instance and got this error. And I did not even specified this parameter in the configuration.
Tried to find something on the internet and on the Metalink:
  1. http://askdba.org/weblog/2008/04/tweaking-_shared_pool_reserved_min_alloc-and-ora-4031/
  2. Ora-00828 With Automatic Shared Memory Management (Doc ID 1251784.1)
I have checked all the possibilities - look for mistakes and misspellings in settings specification and all for nothing. Finally the colleague of mine created a pfile, then created a spfile from that pfile (no changes at all), then started instance with force and voila, the instance worked like a charm.
Not sure what was wrong - the only clue is the possibility I introduced into pfile some hidden character, so the true reason was similar to the one described under the 1st link mentioned above.
Such theory is supported by the following facts:
  • the error message as the upper bound displays 0 (which is nonsense) - however I suppose the lower bound is simply hard coded as 4000 (or 4400) bytes and the upper bound derives its value from the calculation based on the value of shared pool size.
  • assuming something was wrong with the shared pool size specification I suppose it would be set to 0 and hence the upper bound also would be 0 (as any % of 0 is still 0 ;-))
The conclusion: if the upper bound of the ORA-00093 message is set to 0, check Your pfile and spfile byte by byte.
The update Hit this error again, and from this occurrence I draw another conclusion. This is rather not about character in the pfile or spfile. This time the version used is 12c. I set the SGA settings too low and here is the dialog with the database:
-- SGA set to 1G
SQL> startup
ORA-00821: Specified value of sga_target 1024M is too small, needs to be at least 1456M
SQL> create spfile from pfile='db4b.pfile.20170321' ;

File created.

-- SGA increased to 1600M, 1800M, finally 2G (which was the initial setting)
-- still ORA-00093
SQL> startup
ORA-00093: _shared_pool_reserved_min_alloc must be between 4000 and 0
[..]
SQL> startup
ORA-00093: _shared_pool_reserved_min_alloc must be between 4000 and 0

-- and here the only difference - force
SQL> startup force
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size      3712904 bytes
Variable Size   1577060472 bytes
Database Buffers   553648128 bytes
Redo Buffers     13062144 bytes
Database mounted.
Database opened.

As You see the startup force show up as the key step to solve the problem - apparently when I opened sqlplus some settings were established and not cleared on startup failure.