Error:
ORA-04031: unable to allocate 67280 bytes of shared memory ("large pool","unknown object","large pool","PX msg pool")
Cause:
An ORA-04031 error occurs in any of the memory pools in the SGA when Oracle cannot find a memory chunk large enough to satisfy an internal allocation request on behalf of a user's operation.
Resolution:
1. Increased the SGA_TARGET value.
2. Changed cursor_sharing parameter to SIMILAR
3. Recommended using bind variables instead of literals.
| 11g New features | Basic Testing | Advanced Testing | On Demand | comments |
| 11g New DBA Features | ||||
| Partitioning for tables | X | Interval, system, Composite, virtual column, reference | ||
| New load balancing utilities | X | web-server, listener | ||
| New table Data Type "simple_integer" | X | |||
| Improved table/index compression | X | |||
| Faster DML triggers | X | |||
| Improved NFS data file management | X | |||
| Server side connection pooling | X | |||
| Incident Packaging Service (IPS) | X | |||
| Feature Based Patching | X | |||
| New parameters & deprecated and obsolete parameters | X | |||
| 11g High Availability & RAC new features | ||||
| Oracle 11g RAC parallel upgrades | X | |||
| Oracle RAC load balancing advisor | X | |||
| ADDM for RAC | X | |||
| Optimized RAC cache fusion protocols | X | |||
| Hot patching | If available | If available | Oracle has not created any required patches using "hot Patching" | |
| Data Guard supports "Flashback Standby". | X | X | ||
| Data Guard Load Balancing | X | |||
| Streams enhancements | X | |||
| 11g language & PL/SQL New Features | ||||
| PL/SQL "continue" keyword | X | |||
| Disabled state for PL/SQL | X | |||
| Improved native Java & PL/SQL compilers. | X | |||
| Easy PL/SQL compiling | X | |||
| Oracle 11g XML Enhancements & data storage | X | |||
| Improved PL/SQL stored procedure | X | |||
| object-oriented DDL keyword | X | |||
| New Trigger features | X | |||
| New high-performance LOB features. | X | |||
| Enhanced Read only tables | X | |||
| Table trigger firing order | X | |||
| Oracle 11g SQL New Features | ||||
| The /*+result_cache*/ SQL hint | X | |||
| XML SQL queries | X | |||
| SQL Replay | X | |||
| Improved optimizer statistics collection speed | X | |||
| SQL execution Plan Management | X | |||
| Dynamic SQL | X | |||
| Fully Automatic SQL Tuning | X | |||
| Improved SQL Access Advisor | X | |||
| New Oracle11g Performance and Tuning | X | |||
| SQL Performance Analyzer (SPA) | X | |||
| Multi-column hybrid histograms | X | |||
| CBO correlating statistics | X | |||
| Automatic Diagnostic Repository (ADR) | X | |||
| Automatic Memory Tuning | X | |||
| Resource Manager | X | |||
| ADDM for RAC | X | X | ||
| Faster sorting | X | |||
| SQL Plan Manageability (SQL Plan baselines) | X | |||
| AWR Baselines | X | |||
| Oracle 11g Security & Auditing new features | ||||
| Database Replay | X | |||
| Secure Passwords | X | |||
| Auditing Defaults in 11g | X | |||
| RMAN Catalog Security Model | X | |||
| Database Administrator Security Setup | X | |||
| Transparent Data Encryption (TDE) enhancements | X | |||
| TDE and Logminer | X | |||
| Encrypted Tablespaces | X | |||
| Encrypted LOB columns using Oracle SecureFiles | X | |||
| Hardware Secure Module versus Oracle Wallet | X | |||
| TDE and Logical Standby Database | X | |||
| FGAC for UTL_SMTP, UTL_TCP and UTL_HTTP | X | |||
| VPD configuration | X | |||
| Application Contexts configuration | X | |||
| 11g Flashback New Features | ||||
| Optimized UNDO BACKUP | X | |||
| Tracking transactional changes using FLASHBACK DATA | X | |||
| Flashback Data Archive | X | |||
| Flashing back transactions:using DBMS_FLASHBACK | X | |||
| 11g ASM enhancements | ||||
| ASM Fast Mirror Resync-not for external | X | |||
| ASM Preferred Mirror Read | X | |||
| ASM Performance Enhancements-increase extents size | X | |||
| ASM Disk Group compatibility-db and asm level | X | |||
| ASM Disk Group Checks enhanced | X | |||
| SYSASM ROLE on ASM instance | X | |||
| ASMCMD new features | X | |||
| 11g RMAN enhancements | ||||
| RMAN Compatibility 10g and 11g | X | |||
| Duplicate target DB from active DB | X | |||
| Create Standby from duplicate command | X | |||
| Merge Catalog functionality | X |
Error :
While connect to database as a normal user it fails with
ORA-28031: maximum of 148 enabled roles exceeded
Cause:
In a database at one time 148 user-defined roles can be enabled. Enabling more than 148 user-defined roles causes ORA-28031.
Resolution
At first time you can think about max_enabled_roles parameter but max_enabled_roles parameter has nothing to do if you enable 148 user-defined roles.
Possible that Data Import may also cause this.
In fact this parameter is deprecated. It is retained for backward compatibility only. You can see this parameter settings by,
SQL> show parameter max_enable
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
max_enabled_roles integer 150
Though this parameter is set to 150 and you have enabled 148 user defined roles but additional user-defined roles can't be enabled because of the actual number of roles that users can enable is 148. So max_enabled_roles is 2 plus 148, because each user has two additional roles, PUBLIC and the user's own role.
As there is limit to 148 so you have to disable fewer roles or drop some roles and reorganize your database role policy.
You can enable or disable roles by using the SET ROLE statement for your current session. Also regroup the roles.
Here is the list of Alters in general to be considered for Oracle Alerts Monitoring.
| Severity | Oracle MessageNo | Message |
| critical | ORA-00376 | file %s cannot be read at this time |
| critical | ORA-00449 | background process '%s' unexpectedly terminated with error %s |
| critical | ORA-00490 | PSP process terminated with error |
| critical | ORA-00600 | internal error code, arguments: [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s] |
| critical | ORA-01110 | data file %s: '%s' |
| critical | ORA-01113 | file %s needs media recovery |
| critical | ORA-01114 | IO error writing block to file %s (block # %s) |
| critical | ORA-01115 | IO error reading block from file %s (block # %s) |
| critical | ORA-01122 | database file %s failed verification check |
| critical | ORA-01157 | cannot identify/lock data file %s - see DBWR trace file |
| critical | ORA-01171 | datafile %s going offline due to error advancing checkpoint |
| critical | ORA-01186 | file %s failed verification tests |
| critical | ORA-01208 | data file is an old version - not accessing current version |
| critical | ORA-12535 | TNS:operation timed out |
| critical | ORA-01251 | Unknown File Header Version read for file number %s |
| critical | ORA-01259 | unable to delete datafile %s |
| critical | ORA-01578 | ORACLE data block corrupted (file # %s, block # %s) |
| critical | ORA-1632 | max # extents (%s) reached in index %s.%s |
| critical | ORA-1652 | unable to extend temp segment by %s in tablespace %s |
| critical | ORA-1653 | unable to extend table %s.%s by %s in tablespace %s |
| critical | ORA-01658 | unable to create INITIAL extent for segment in tablespace %s |
| critical | ORA-16038 | log %s sequence# %s cannot be archived |
| critical | ORA-1683 | unable to extend index %s.%s partition %s by %s in tablespace %s |
| critical | ORA-01688 | unable to extend table %s.%s partition %s by %s in tablespace %s |
| critical | ORA-02068 | following severe error from %s%s |
| critical | ORA-03113 | end-of-file on communication channel |
| critical | ORA-04031 | unable to allocate %s bytes of shared memory (\%s\",\"%s\",\"%s\",\"%s\")" |
| critical | ORA-07445 | exception encountered: core dump [%s] [%s] [%s] [%s] [%s] [%s] |
| critical | ORA-17500 | ODM err:%s |
| critical | ORA-17502 | ksfdcre:%s Failed to create file %s |
| critical | ORA-17503 | ksfdopn:%s Failed to open file %s |
| critical | ORA-19504 | failed to create file \%s\"" |
| critical | terminating instance due to error | |
| critical | PMON failed to acquire latch, see PMON dump | |
| warning | ORA- | |
| exclude | ORA-00001 | Unique constraint violated |
| exclude | ORA-00028 | your session has been killed |
| exclude | ORA-00060 | deadlock detected while waiting for resource |
| exclude | ORA-00601 | cleanup lock conflict |
| exclude | ORA-00604 | error occurred at recursive SQL level %s |
| exclude | ORA-00918 | column ambiguously defined |
| exclude | ORA-01001 | invalid cursor |
| exclude | ORA-01013 | user requested cancel of current operation |
| exclude | ORA-01017 | invalid username/pwd; logon denied |
| exclude | ORA-01034 | oracle not available |
| exclude | ORA-01089 | immediate shutdown in progress |
| exclude | ORA-01119 | error in creating database file '%s' |
| exclude | ORA-01403 | no data found |
| exclude | ORA-01551 | extended rollback seg, pinned blocks released |
| exclude | ORA-01555 | snapshot too old |
| exclude | ORA-01575 | timeout waiting for space mgmt resource |
| exclude | ORA-01591 | lock held by in-doubt distributed transaction |
| exclude | ORA-01595 | error freeing extent %s of rollback seg %s |
| exclude | ORA-01634 | rollback seg number '%s' is about to go offline |
| exclude | ORA-01722 | invalid number |
| exclude | ORA-01841 | (full) year must be between -4713 and +9999, and not be 0 |
| exclude | ORA-01843 | not a valid month |
| exclude | ORA-02050 | transaction %s rolled back, some remote DBs may be in-doubt |
| exclude | ORA-02052 | remote transaction failure at %s |
| exclude | ORA-02063 | preceding %s%s from %s%s (error recvd from remote db link) |
| exclude | ORA-02067 | transaction or savepoint rollback required |
| exclude | ORA-02068 | following severe error from %s%s (a severe error recvd from indicated db link) |
| exclude | ORA-02091 | transaction rolled back |
| exclude | ORA-02291 | integrity constraint violated - parent key not found |
| exclude | ORA-03106 | fatal two-task communication protocol error |
| exclude | ORA-03113 | end-of-file on communication channel |
| exclude | ORA-03297 | file contains used data beyond requested RESIZE value |
| exclude | ORA-04060 | insufficient privs to execute stored proc |
| exclude | ORA-04061 | existing state of stored proc has been invalidated |
| exclude | ORA-04062 | timestamp of stored proc has been changed |
| exclude | ORA-04063 | object has errors |
| exclude | ORA-04064 | not executed, invalidated stored proc |
| exclude | ORA-04065 | not executed, altered or dropped stored proc |
| exclude | ORA-04066 | non-executable object |
| exclude | ORA-04067 | not executed, stored proc does not exist |
| exclude | ORA-04068 | existing state of packages has been discarded |
| exclude | ORA-06508 | PL/SQL: could not find program unit being called |
| exclude | ORA-06512 | at %s line %s (backtrace msg as the stack is unwound by unhandled exeptions) |
| exclude | ORA-1031 | insufficient privs (an attempt was made to chg the current username/pwd) |
| exclude | ORA-10387 | parallel query server interrupt (normal) |
| exclude | ORA-1142 | cannot end online bkup - none of the files are in bkup mode |
| exclude | ORA-1146 | cannot start online bkup - file is already in bkup mode |
| exclude | ORA-12008 | error in snapshot refresh path |
| exclude | ORA-12012 | error on auto execute of job %s |
| exclude | ORA-12203 | TNS: unable to connect to destination |
| exclude | ORA-12535 | TNS: operation timed out |
| exclude | ORA-1537 | cannot add datafile %s - file already part of db |
| exclude | ORA-1636 | rollback seg %s is already online |
| exclude | ORA-29549 | class %s.%s has changed, Java session state cleared |
| exclude | ORA-3217 | invalid option for alter of TEMPORARY TABLESPACE |
| exclude | ORA-959 | tablsp %s does not exist |
| exclude | ORA-00600 | internal error code, arguments: [12235] Oracle process has no purpose in life. |
