ORA-04031: unable to allocate 12312 bytes of shared memory ("shared pool","unknown object","KKSSP^212","kglseshtTable")

We recently encountered the following error in 19c, which typically occurs when the database needs additional shared memory. In most of the cases setting MAX_SGA_SIZE to a higher value will resolve the issue.

Below are some possible cause

•  Insufficient memory allocated via initialization parameters
•  Fragmentation in app design
•  Auto tuning issues
•  A Bug causing the issue
•  Memory leaks

ORA-04031: unable to allocate 12312 bytes of shared memory ("shared pool","unknown object","KKSSP^334","kglseshtTable")
< ORA-00604: error occurred at recursive SQL level 1 < ORA-04031: unable to allocate 40 bytes of shared memory ("shared pool","unknown object","KGLH0^f185eace","kglHeapInitialize:temp")
< ORA-04031: unable to allocate 12312 bytes of shared memory ("shared pool","unknown object","KKSSP^394","kglseshtTable")


The error message will provide the amount of memory unavailable, memory pool facing the issue and failed allocation details

 

I would highly suggest running below query to get the optimal value for SGA_TARGET
Select * from V$SGA_TARGET_ADVICE

Note that in order to initialization parameters to take into effect we need to bounce the instance.

AGLT>oerr ora 04031
04031, 00000, "unable to allocate %s bytes of shared memory (\"%s\",\"%s\",\"%s\",\"%s\")"
// *Cause: More shared memory is needed than was allocated in the shared
// pool or Streams pool.
// *Action: If the shared pool is out of memory, either use the
// DBMS_SHARED_POOL package to pin large packages,
// reduce your use of shared memory, or increase the amount of
// available shared memory by increasing the value of the
// initialization parameters SHARED_POOL_RESERVED_SIZE and
// SHARED_POOL_SIZE.
// If the large pool is out of memory, increase the initialization
// parameter LARGE_POOL_SIZE.
// If the error is issued from an Oracle Streams or XStream process,
// increase the initialization parameter STREAMS_POOL_SIZE or increase
// the capture or apply parameter MAX_SGA_SIZE.
// parameter MAX_SGA_SIZE.

Refer below links for Oracle support notes

• This Oracle support note provides information about ORA-04031 related bugs and which release they were fixed

OERR: ORA-4031 "unable to allocate %s bytes of shared memory ("%s","%s","%s")" (Doc ID 4031.1)

• This Oracle note provides detailed troubleshooting and diagnosing details

Troubleshooting and Diagnosing ORA-4031 Error [Video] (Doc ID 396940.1)

• This Oracle note provides detailed understanding and tuning of the of the shared pool

NOTE:62143.1 - Troubleshooting: Understanding and Tuning the Shared Pool

Thanks & Regards,

https://oracleracexpert.com

Exception in thread "main" java.lang.NoClassDefFoundError: Could not initialize class

When launching Database configuration assistant (DBCA) you may encounter this error. The “java.lang.NoClassDefFoundError” error encounter when it could not initialize class “sun.awt.X11.XToolkit”. 

This normally occur when launching Graphic user interface (GUI)

No protocol specified

Exception in thread "main" java.lang.NoClassDefFoundError: Could not initialize class sun.awt.X11.XToolkit

at java.lang.Class.forName0(Native Method)

at java.lang.Class.forName(Class.java:264)

at java.awt.Toolkit$2.run(Toolkit.java:860)

at java.awt.Toolkit$2.run(Toolkit.java:855)

at java.security.AccessController.doPrivileged(Native Method)

at java.awt.Toolkit.getDefaultToolkit(Toolkit.java:854)

at com.jgoodies.looks.LookUtils.isLowResolution(LookUtils.java:484)

at com.jgoodies.looks.LookUtils.<clinit>(LookUtils.java:249)

at com.jgoodies.looks.plastic.PlasticLookAndFeel.<clinit>(PlasticLookAndFeel.java:135)

at java.lang.Class.forName0(Native Method)

at java.lang.Class.forName(Class.java:348)

at javax.swing.SwingUtilities.loadSystemClass(SwingUtilities.java:1879)

at javax.swing.UIManager.setLookAndFeel(UIManager.java:582)

at oracle.install.commons.util.Application.startup(Application.java:976)

at oracle.install.commons.flow.FlowApplication.startup(FlowApplication.java:181)

at oracle.install.commons.flow.FlowApplication.startup(FlowApplication.java:198)

at oracle.assistants.common.base.driver.AssistantApplication.startup(AssistantApplication.java:336)

at oracle.assistants.dbca.driver.DBConfigurator.startup(DBConfigurator.java:378)
at oracle.assistants.dbca.driver.DBConfigurator.main(DBConfigurator.java:513)

The main causes of the issue is
1. The DISPLAY variable is NOT set or value is not correct.
2. X Windows is not installed
3. Oracle user not added to ACL (Access Control List)

If launching from server make sure you have any X Windows software installed and install org-x11-apps.x86_64 package

If you are launching from a local workstation, make sure you have XGraphics software such as Exceed, VLC…etc tools installed. Then You can set DISPLAY by using below command

CSH
$ setenv DISPLAY <IP Address of workstation or server>:0

SSH or KSH
$ export DISPLAY=<IP Address of workstation or server>:0

In Some cases, users may face issue when installing Oracle software on Ubuntu or Linux environment.

>>> Could not execute auto check for display colors using command /usr/bin/xdpyinfo. Check if the DISPLAY variable is set. Failed <<<<

Some requirement checks failed. You must fulfill these requirements before

continuing with the installation,

Continue? (y/n) [n] Y

>>> Ignoring required pre-requisite failures. Continuing...
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2019-08-01_12-17-01PM. Please wait ....oracle$:~$ No protocol specified
Exception in thread "main" java.lang.NoClassDefFoundError: Could not initialize class sun.awt.X11.XToolkit

In this scenario, verify weather JDK installed on the system or not using below command
$ java -version

If not installed then installing required JDK will resolve the issue. If the problem persists, then run below command by logging as root

$ xhost +

Switch back to Oracle user and run the installation.

Thanks,

https://oracleracexpert.com, Oracle ACE Pro

RMAN-04009: warning from auxiliary database: ORA-28002: the password will expire within 6 days

I have come across an issue when trying to duplicate a database by connection to Recovery catalog database. You will come across this warning when the user profile has reached PASSWORD_LIFE_TIME value limit and you have entered into PASSWORD_GRACE_TIME value

RMAN-04009: warning from auxiliary database: ORA-28002: the password will expire within 6 days

First check the profile that user has been assigned.

SQL> select username , profile from dba_users where username=’RMAN’;
username profile
------------- ---------------
RMAN DEFAULT

Check the current status of the user
SQL> select USERNAME, ACCOUNT_STATUS from dba_users where USERNAME = ‘RMAN’;
USERNAME ACCOUNT_STATUS
------------- ----------------- --------------------------------
RMAN EXPIRED(GRACE)

Check the RESOUCE_NAME, LIMIT for the Default profile.
SQL> select RESOURCE_NAME,LIMIT from dba_profiles where PROFILE='DEFAULT';

RESOURCE_NAME LIMIT
---------------------------  -------------------------------------------------
COMPOSITE_LIMIT UNLIMITED
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
IDLE_TIME UNLIMITED
CONNECT_TIME UNLIMITED
PRIVATE_SGA UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LIFE_TIME 180
RESOURCE_NAME LIMIT
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL
PASSWORD_LOCK_TIME 1
PASSWORD_GRACE_TIME 7
INACTIVE_ACCOUNT_TIME UNLIMITED

As we see password PASSWORD_LIFE_TIME set to 180 days

First you need to change the password for the issue to resolve.
SQL> Alter user rman identified by rman_new_password;

As current password life time set 180 days, You can change the password life time to avoid this issue in future. Either you can set to specific number of days or UNLIMITED.

SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
Profile altered.

You can check the modified resource limit for PASSWORD_LIFE_TIME
SQL> select RESOURCE_NAME,LIMIT from dba_profiles where PROFILE='DEFAULT' and RESOURCE_NAME='PASSWORD_LIFE_TIME';

RESOURCE_NAME   LIMIT
---------------------------  -------------------------------------------------
PASSWORD_LIFE_TIME UNLIMITED

Hope this helps

Thanks & Regards

http://oracleracexpert.com, Oracle ACE Pro

Webinar: Cloning an Oracle Home or Oracle Install

This webinar helps to Clone Oracle Home or Oracle Install while migrating the databases.

Date and time: Dec 15th 2022 8:00am-9:00am
Pacific Daylight Time (San Francisco, GMT-07:00)

This Webinar covers following Topics.

• What is Cloning
• When Cloning useful
• Different methods of Cloning
• How to perform Cloning
• References
• Q&A 

To register for this Webinar, please send an email to SatishbabuGunukula@gmail.com.

Note that registrations are limited and first come and first serve basis.

You will receive an email confirmation with meeting session link.

For Presentation link "Click here"

Regards,
Satishbabu Gunukula, Oracle ACE
http://www.oracleracexpert.com

Create Recovery Catalog for RMAN

Recovery catalog schema used by RMAN to store one or more Oracle databases metadata. This info is already stored in control file of each Target Database and using Recovery catalog provides redundancy. In case, if target control file and all backups are lost then you can still get the info from Recovery catalog.

RMAN Recovery catalog also helps as centralized metadata location for all your databases and it helps easily report from once location. In addition, you can store metadata history longer than Control file.

You must register database in order to store metadata in Recovery catalog. it is highly recommended to use enable ARCHIVELOG mode for Recovery Catalog database

· Recovery Catalog metadata has following database file info
· Data file and archived redo log backup sets and pieces
· Data file and Archive log copies
· Database structure such as tablespaces and data files
· Stored scripts
· RMAN configuration settings

Creating Recovery Catalog

1. Connect to Recovery catalog DB as admin and create the Schema.

SQL> CREATE USER RCAT IDENTIFIED BY password
DEFAULT TABLESPACE RCATTBS
TEMPORARY TABLESPACE temp;

2. The RECVEROY_CATALOG_OWNER role is required to maintain and query, so grant the role to the Schema

SQL> GRANT RECOVERY_CATALOG_OWNER TO RCAT;

3. Connect to the database as the recovery catalog owner and run CREATE CATALOG command to create the catalog.

RMAN> CREATE CATALOG

You can also specify tableaspace name for the catalog.
RMAN> CREATE CATALOG TABLESPACE RCATTBS;

4. Connect to recovery Catalog and Register Target Database using below nelow command

$ rman TARGET / CATALOG RCAT@RCATDB;
RMAN> REGISTER DATABASE;

Examples for Data file copy, Archive log backup and backup pieces
RMAN>CATALOG DATAFILECOPY '/bkp/dbfile/testuser01.dbf';
RMAN>CATALOG ARCHIVELOG '/arc/arc_DB_1_559.dbf', '/arc/arc_DB_1_560.dbf',
RMAN>CATALOG BACKUPPIECE '/disk1/backups/backup_820.bkp';

Catalog multiple backup files in a directory by using one single command
RMAN> CATALOG START WITH '/bkp/dbfile/';

Refer below link for Virtual Private Catalogs
https://docs.oracle.com/en/database/oracle/oracle-database/19/bradv/managing-recovery-catalog.html#GUID-DA362C55-5B94-4AB8-A8A1-163BF08FE594

Thanks & Regards,

http://oracleracpert.com, Oracle ACE

ORA-04031: unable to allocate nn bytes of shared memory

You will receive ORA-04031 error when allocated memory is small too small and does not have enough system global area (SGA).

Recently I got below error...

ORA-04031: unable to allocate 63176 bytes of shared memory ("shared pool","DBMS_STATS_INTERNAL","PLMCD^60dfd26c","BAMIMA: Bam Buffer") < ORA-04031: unable to allocate 63176 bytes of shared memory ("shared pool","DBMS_STATS_INTERNAL","PLMCD^60dfd26c","BAMIMA: Bam Buffer") < ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_STATS_INTERNAL"
< ORA-06512: at line 1

< ORA-04031: unable to allocate 63176 bytes of shared memory ("shared pool","DBMS_STATS_INTERNAL","PLMCD^60dfd26c","BAMIMA: Bam Buffer")

Please find the cause and action

ORA-04031: unable to allocate nn bytes of shared memory
Cause: More shared memory is needed than was allocated in the shared pool.
Action: If the shared pool is out of memory, either use the DBMS_SHARED_POOL package to pin large packages, reduce your use of shared memory, or increase the amount of available shared memory by increasing the value of the initialization parameters SHARED_POOL_RESERVED_SIZE and SHARED_POOL_SIZE. If the large pool is out of memory, increase the initialization parameter LARGE_POOL_SIZE. If the error is issued from an Oracle Streams or XStream process, increase the initialization parameter STREAMS_POOL_SIZE or increase the capture or apply parameter MAX_SGA_SIZE.

I will explain some of the possible root causes and how to fix it.

1. Insufficient RAM – This is most common issue when the database usage increased over the period. Increase the RAM and also adjust related SGA_MAX_TARGET or MEMORY_MAX_SIZE in case of AMM.

2. Shared pool fragmentation – This can be addressed by increasing the shared_pool_size in spfile or init.ora parameter. If you cannot bounce the database for changes to effect then I flush shared pool using “alter system flush shared pool;” command for temporary fix

3. Pinned packages – You will see this error if you have pinned lots of packages with dbms_shared_pool.keep and DB does not have enough resources

If your application is not using bind variables then it will cause SGA to become fragmented. In this case, if you turn on cursor_sharing it will help. Also make sure you use bind varibales. 


Also, run below queries to get SGA/PGA memory advice.
select * from v$sga_target_advice; select * from v$pga_target_advice;

Refer: MOSC notes 146599.1 and 396940.1 for more details for resolving the ORA-04031 error:

Regards,

http://oracleracexpert.com

ORA-16198: Received timed out error from KSR & LAD:2 network reconnect

I have setup Data Guard and log synchronization happening without any issue but recently I found below errors, which are happening rarely in alert.log file

GWR (PID:13557): ORA-16198: Received timed out error from KSR
LGWR (PID:13557): Attempting LAD:2 network reconnect (16198)
LGWR (PID:13557): LAD:2 network reconnect abandoned
2020-07-13T15:32:25.865034-07:00
Errors in file /oracle/diag/rdbms/orcl/ORCL/trace/orcl_lgwr_13557.trc:
ORA-16198: Timeout incurred on internal channel during remote archival
LGWR (PID:13557): Error 16198 for LNO:2 to 'orcl_stdby'
2020-07-13T15:32:25.865253-07:00
LGWR (PID:13557): LAD:2 is UNSYNCHRONIZED
LGWR (PID:13557): Failed to archive LNO:2 T-1.S-5003, error=16198
LGWR (PID:13557): Error 16198 disconnecting from LAD:2 standby host 'orcl_stdby'

Redo Transport Services failed with ORA-16198 error from primary database standby database using LGWR SYNC mode.

Data Guard Broker log shows below error messages…

Data Guard Broker Status Summary:

  Type                        Name                             Severity  Status

  Configuration               orcl_dg_fsfconf                   Warning  ORA-16608: one or more members have warnings

  Primary Database            orcl                              Success  ORA-0: normal, successful completion

  Physical Standby Database   orcl_stdby                        Warning  ORA-16853: apply lag has exceeded specified threshold

When I very configuration everything looks good and no issues.

DGMGRL for Linux: Release 19.0.0.0.0 - Production on Mon Aug 3 15:19:29 2020
Version 19.5.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "ORCL"
Connected as SYSDBA.
DGMGRL> show configuration;
Configuration - orcl_dg_fsfconf
Protection Mode: MaxAvailability
Members:
orcl - Primary database
orcl_stdby - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 34 seconds ago)

If this is happening frequently then it is critical and must take action immediately. In my case it is happening rarely which lean towards time out setting. I see that my current DB has net_timeout as 30.

After research, I have increased the net_timeout to 60 and issue has been resolved.

In case if you are not using Data Guard broker then you can run below command from SQL*Plus

SQL> alter system set log_archive_dest_2 service="orcl_stdby", SYNC AFFIRM delay=0 optional compression=disable max_failure=0 reopen=300 db_unique_name="orcl_stdby" net_timeout=30, valid_for=(online_logfile,all_roles)

In case if you are using DGMGRL command-line interface, then you can run below command

DGMGRL> EDIT DATABASE 'ORCL' SET PROPERTY NetTimeout = 30;

Thanks,
Satishbabu G, Oracle ACE
http://oracleracepxert.com

RENAME Table or Column or Index in Oracle

RENAME Table
You can rename an existing table in any schema except the schema SYS. To rename a table you must be either DB owner or Table owner.

Use RENAME TABLE to rename a table.

Syntax: RENAME TABLE table-Name TO new-Table-Name
Ex:- RENAME TABLE EMPLOYEE TO EMPLOYEE_ACT

If you have a view or foreign key that references the table, attempts to rename it will generate an error. Also if there are any check constraints or triggers on the table, attempts to rename it will also generate an error.

RENAME Column: Use the RENAME COLUMN to rename a column in a table.

ex: RENAME COLUMN EMPLOYEE.Employee_ID TO EMP_ID;

You can use ALTER TABLE and RENAME COLUMN to modify Column data type

ALTER TABLE EMPLOYEE ADD COLUMN EMP_ID NEWTYPE
UPDATE EMPLOYEE SET EMP_ID = Employee_ID
ALTER TABLE EMPLOYEE DROP COLUMN Employee_ID
RENAME COLUMN t. Employee_ID TO EMP_ID

If a view, trigger, check constraint, foreign key constraint then attempt to rename it will generate an error. Also RENAME COLUMN is not allowed if you have any open cursors that reference the column that is being altered.

If there is an index defined on the column then you can still rename. The index will update automatically to refer by its new name

RENAME Index: Use the RENAME Index to rename an index, you cannot rename indexes in SYS schema

Ex:- RENAME INDEX EMPLOYEE_ID_INDEX TO EMP_ID_INDEX

In case if there are any open cursors that reference the index being renamed then RENAME INDEX is not allowed .

Thanks

Satishbabu G, Oracle ACE

http://oracleracepxert.com

ORA-00942: table or view does not exist

When calling a table directly or through procedure/function...etc user may receive error “ORA-00942: table or view does not exist”.

For ex:-
CREATE OR REPLACE FUNCTION PROC1
(Num IN NUMBER)
RETURN NUMBER
IS
BEGIN
INSERT INTO Table1 SELECT * FROM SCOTT.Table2 WHERE ID = Num;
END;

PL/SQL: ORA-00942: table or view does not exist

SQL> select * from table1;

select * from table1

              *

ERROR at line 1:

ORA-00942: table or view does not exist

Possible reasons:
1. The table name or view name spelled wrongly
2. The table or view doesn’t exist
3. The user doesn’t have required permissions

In some cases users have select access and able to query data but when running from a procedure they still receive “ORA-00942: table or view does not exist”

The reason for this error is the access granted trough a ROLE not directly. In order to access another user table from a procedure you need to have SELECT privilege granted directly.

Regards,

Satishbabu Gunukula, Oracle ACE

http://www.oracleracexpert.com

opidcl aborting process unknown ospid (xxxx) as a result of ORA-1000

By looking the error message we can easily find out the issue

01000, 00000, "maximum open cursors exceeded"

Cause: A host language program attempted to open too many cursors. The initialization parameter OPEN_CURSORS determines the maximum number of cursors per user.

Action: Modify the program to use fewer cursors. If this error occurs often, shut down Oracle, increase the value of OPEN_CURSORS, and then restart Oracle.

First you need to work with application team and find out why the cursors are not closing.

If required you can increase the OPEN_CURSORS value by running below command.

ALTER SYSTEM SET open_cursors = 400 SCOPE=BOTH;

Note that to increase the value of OPEN_CURSORS no downtime required, but cursor storing require more memory.

Regards
Satishbabu Gunukula
htt://oracleracexpert.com

ORA-03206: maximum file size of (6553600) blocks in AUTOEXTEND clause is out of range

I received below error while extending the datafile. You might see below error when creating tables space too.

SQL> alter database datafile '/oradata1/ORCL/orcl101.dbf' autoextend ON maxsize 50g;
alter database datafile '/oradata1/ORCL/orcl101.dbf' autoextend ON maxsize 50g;
*
ERROR at line 1:
ORA-03206: maximum file size of (6553600) blocks in AUTOEXTEND clause is out of range

The reason is maximum file size for an autoenxtendable file has exceeded the maximum number of blocks allowed. Note that Oracle allows only up to 32GB datafile with 8k blocks. If you want to create a tablespace or datafile more than 32GB then you must use BIGFILE keyword.

To calicuate max datafile size:
max datafile size = db_block_size * maximum number of blocks

Solution: create a file less than 32 GB size or if you need to create datafile more than 32GB then use BIGFILE keyword.

SQL> alter database datafile '/oradata1/ORCL/orcl101.dbf' autoextend ON maxsize 32767M ;
Tablespace altered.

Hope this helps,

Regards
Satishbabu Gunukula, Oracle ACE
http://oracleracexpert.com

CATPROC and CATALOG are INVALID in the Registry

We are working on pre-upgrade steps and noticed that “Oracle Database Packages and Types” and “Oracle Database Catalog Views “ components are  showing as INVALID.

SQL> select comp_id, comp_name,version, statusfrom dba_registry;
COMP_ID    COMP_NAME                                VERSION                        STATUS

---------- ---------------------------------------- ------------------------------ --------------------------------------------
ORDIM      Oracle Multimedia                                        11.2.0.4.0                     VALID

XDB        Oracle XML Database                                       11.2.0.4.0                     VALID

EXF        Oracle Expression Filter                                    11.2.0.4.0                     VALID

RUL        Oracle Rules Manager                                       11.2.0.4.0                     VALID

OWM        Oracle Workspace Manager                         11.2.0.4.0                     VALID

CATALOG    Oracle Database Catalog Views                 11.2.0.4.0                     INVALID

CATPROC    Oracle Database Packages and Types       11.2.0.4.0                     INVALID

JAVAVM     JServer JAVA Virtual Machine                     11.2.0.4.0                     VALID

XML        Oracle XDK                                                          11.2.0.4.0                     VALID

CATJAVA    Oracle Database Java Packages                   11.2.0.4.0                     VALID

I see that CATPROC, CATALOG  is INVALID in the registry and followed below steps to validate.

Step1: Validate ‘packages and types’ by running catalog and catproc scripts

$sqlplus "/as sysdba"
SQL> spool cata_logfile.txt
SQL> startup upgrade
SQL > @?/rdbms/admin/catalog.sql
SQL > @?/rdbms/admin/catproc.sql
SQL > @?/rdbms/admin/utlrp.sql
SQL> spool off

Note that you need to use Use 'startup migrate' instead of ‘startup upgrade’ if database version is lower than 10g.

This should resolve the issue. If CATPROC, CATALOG is still invalid in the registry then follow next step

Step2:- Run below script as SYSDBA until it returns 'CATPROC can be validated now'. This script recompiles all dependent objects. 

 Note that you need to replace CATPROC with CATALOG in below script and execute to recompile the objects related to CATALOG.

REM ***************
REM CHECKVALID.SQL
REM ***************
set serveroutput on;
declare
start_time date;
end_time date;
object_name varchar(100);
object_id char(10);
begin
SELECT date_loading, date_loaded into start_time, end_time FROM registry$ WHERE
cid = 'CATPROC';
SELECT obj#,name into object_id,object_name
FROM obj$
WHERE status > 1 AND
(ctime BETWEEN start_time AND end_time OR
mtime BETWEEN start_time AND end_time OR
stime BETWEEN start_time AND end_time) AND
ROWNUM <=1;
dbms_output.put_line('Please compile Invalid object '||object_name||'
Object_id '||object_id );
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('CATPROC can be validated now' );
end;
/

Validate CATPROC. CATALOG by executing following command
SQL> execute DBMS_REGISTRY_SYS.VALIDATE_CATPROC;

SQL> execute DBMS_REGISTRY_SYS.VALIDATE_CATALOG;

Now I can see that all Database components are objects are VALID.

SQL> select comp_id, comp_name,version, statusfrom dba_registry;
COMP_ID    COMP_NAME                                VERSION                        STATUS

---------- ---------------------------------------- ------------------------------ --------------------------------------------
ORDIM      Oracle Multimedia                                        11.2.0.4.0                     VALID

XDB        Oracle XML Database                                       11.2.0.4.0                     VALID

EXF        Oracle Expression Filter                                    11.2.0.4.0                     VALID

RUL        Oracle Rules Manager                                       11.2.0.4.0                     VALID

OWM        Oracle Workspace Manager                         11.2.0.4.0                     VALID

CATALOG    Oracle Database Catalog Views                 11.2.0.4.0                     VALID

CATPROC    Oracle Database Packages and Types       11.2.0.4.0                     VALID

JAVAVM     JServer JAVA Virtual Machine                     11.2.0.4.0                     VALID

XML        Oracle XDK                                                          11.2.0.4.0                     VALID

CATJAVA    Oracle Database Java Packages                   11.2.0.4.0                     VALID

Thanks,
Satishbabu Gunukula, Oracle ACE
http://oracleracexpert.com

Find current and Max utilization of processes, sessions,resources..etc

I see that many users are trying to find the current utilization and max utilization of processes, sessions, enqueue_locks, resources…etic.

You will get the resource limit details from v$resoruce_limit

SQL> select resource_name, current_utilization, max_utilization from v$resource_limit;

RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION

------------------------------ ------------------- ---------------

processes                                      223             235

sessions                                       232             250

enqueue_locks                                  231             264

enqueue_resources                               34              72

ges_procs                                        0               0

ges_ress                                         0               0

ges_locks                                        0               0

ges_cache_ress                                   0               0

ges_reg_msgs                                     0               0

ges_big_msgs                                     0               0

ges_rsv_msgs                                     0               0

RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION

------------------------------ ------------------- ---------------

gcs_resources                                    0               0

gcs_shadows                                      0               0

smartio_overhead_memory                          0           71704

smartio_buffer_memory                            0               0

smartio_metadata_memory                          0               0

smartio_sessions                                 0               1

dml_locks                                        0              27

temporary_table_locks                            0               3

transactions                                     0               8

branches                                         0               0

cmtcallbk                                        0               5

RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION

------------------------------ ------------------- ---------------

max_rollback_segments                           11              11

sort_segment_locks                               5              18

k2q_locks                                        0               0

max_shared_servers                               0               0

parallel_max_servers                             0               5

Database access error. Reason Loading shared object failed

User might receive below error during Business Objects(Boxi ) or Data services installation when using Oracle as database.

Error
An error has occurred.

Database access error. Reason Loading shared object failed. First tried to load library clntsh and failed because of error: . Second tried to load library libclntsh.so and failed because of error: .
(FWB 00090)

The system cannot verify the database logon information. That means the path might not be correct.

User will receive this error when below environment variables are not set properly.

1. The environment variable "LD_LIBRARY_PATH" is not set.
2. environment variable for ORACLE client is not setup properly
3. the user where you are running the BOXI install not able to tnsping to the listener service

Resolution

Make sure you set the below library paths in .profile of the user by which Business objects is installed

• ORACLE_HOME=<Oracle client home path>
• LD_LIBRARY_PATH=<$ORACLE_HOME/lib>

Note : ORACLE_HOME may be set also in the bash_profile. After setting variables, tomcat should be restarted.

Regards,
Satishbabu Gunukula, Oracle ACE

ORA-01102 cannot mount database in EXCLUSIVE mode

Users might receive error “ORA-01102” when trying to startup

01102, 00000, "cannot mount database in EXCLUSIVE mode"
// *Cause: Some other instance has the database mounted exclusive or shared.
// *Action: Shutdown other instance or mount in a compatible mode.

You will receive this error when instance is already opening in parallel. You can resolve this issue by shutting down all the instances before mounting the database.

Verify that no background processes running owner as oracle
Ps –ef |grep <$ORACLE_SID>

If you found any processes then shutdown the instance, if you are not able to shutdown then you can kill by using “kill -9 <processed>.

Now you should be able to startup without any issues.

Regards
Satishbabu Gunukula, Oracle ACE

http://www.oracleracexpert.com

Oracle Optimized Data Center Reference Architecture

IT departments are under constant pressure to increase their ability to respond rapidly to rapidly changing business requirements and select best technology while reducing cost and risk

This results in complexity and integration challenges.These effects add up to drive agility, cost, and risk in the wrong directions. While it is clear that cloud computing offers many potential benefits

Oracle’s optimized data center is a pragmatic solution to these problems. By providing a fully integrated stack, “from Applications to Disk”, the data center is greatly simplified while integration is already built- in. This paves the way for reduced cost and risk.

The key drivers for the optimized data center can be summarized as follows

• Cost management: Virtualization/consolidation, simplification
• Business gro wth: Removing barriers to infrastructure performance and flexibility
• Service continuity: Maximum availability architecture, disaster recovery planning
• Security and conformity: Comprehensive, end-to-end security and compliance approach

This paper published by Oracle Corporation and it will show in architectural terms how reduced risk reduced cost , and increased agility can be achieved through consistent integration and reduced complexity.

You can download the White paper "Oracle Optimized Data Center Reference Architecture"

Regards
Satishbabu Gunukula
http://www.oracleracexpert.com


http://www.oracle.com/technetwork/topics/entarch/oracle-wp-optmizd-dc-ref-arch-1918519.pdf

ORA-27102: out of memory Linux-x86_64 Error: 28: No space left on device

When trying to start-up a database received below error on a REHL 5.x

ORA-27102: out of memory
Linux-x86_64 Error: 28: No space left on device 

By looking the error, you might think that it is a space issue. But note that the actual error is “Out of Memory”.

You can also check available memory on the server and you should see enough FREE memory.

$free -g
total used free shared buffers cached
Mem: 125 80 45 0 0 88
-/+ buffers/cache: 36 89
Swap: 23 2 21

The server has enough memory but out of shared memory pages that can be used system wide. You should increase the kernel.shmall value to fix the issue.

The default size for SHMALL in Red Hat Enterprise Linux 2.1, 3, 4 and 5 is 2097152. Refer My Oracle Support document ID 169706.1 for recommended values.

Once you change the value you no need to reboot the server, you should reload the system configuration file as root using below command

$ sysctl –p

Hope this should resolve your issue

Regards
Satishbabu Gunukula, Oracle ACE
http://www.oracleracexpert.com

RMAN Debug and Trace

If any RMAN operation fails, then you can use RMAN Debug to collect and diagnosis the failure. The RMAN Debug command generates more extensive output, use debugging for the following purposes.

•  To see the actual programs generated by RMAN
•  To determine the failure of RMAN Command

The RMAN debug option can be used trace RMAN session, and it will be very useful to troubleshoot an issue.

The RMAN Debug command will be available in from Oracle 8i onwards.

RMAN Debug Command

$ rman target / debug trace rman.trc log rman.log

Or
$ rman target / catalog xxx/xxxx@rmancat debug trace = /tmp/rman.trc log=/tmp/rman.log

Once you are connected to RMAN, you can run the commands that you want to debug.

The log file will shows how much percentage (%) completed and time left, It will help to estimate the time.

The rman.log file will look like below.

orclsr@oracle> cat rman.log
Recovery Manager: Release 11.2.0.2.0 - Production on Thu Oct 11 15:21:05 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
RMAN-06005: connected to target database: ORCL (DBID=2284874908408)
RMAN>

RMAN-03090: Starting backup at 11-OCT-12

RMAN-06009: using target database control file instead of recovery catalog

RMAN-08030: allocated channel: ORA_DISK_1

RMAN-08500: channel ORA_DISK_1: SID=618 device type=DISK

RMAN-08008: channel ORA_DISK_1: starting full datafile backup set

RMAN-08010: channel ORA_DISK_1: specifying datafile(s) in backup set

RMAN-08522: input datafile file number=00071 name=/oracle/ORCL/sapdata8/prd_57/prd.data57

RMAN-08522: input datafile file number=00073 name=/oracle/ORCL/sapdata9/prd_59/prd.data59

RMAN-08522: input datafile file number=00078 name=/oracle/ORCL/sapdata9/prd701_8/prd701.data8

RMAN-08522: input datafile file number=00069 name=/oracle/ORCL/sapdata4/prd_55/prd.data55

RMAN-08522: input datafile file number=00006 name=/oracle/ORCL/sapdata1/prd_3/prd.data3

RMAN-08522: input datafile file number=00001 name=/oracle/ORCL/sapdata1/system_1/system.data1

RMAN-08522: input datafile file number=00003 name=/oracle/ORCL/sapdata1/sysaux_1/sysaux.data1

RMAN-08522: input datafile file number=00030 name=/oracle/ORCL/sapdata1/prdusr_1/prdusr.data1

RMAN-08522: input datafile file number=00011 name=/oracle/ORCL/sapdata2/prd_8/prd.data8

RMAN-06731: command backup:1.0% complete, time left 03:27:06

RMAN-06731: command backup:1.9% complete, time left 03:32:09

The trace file will have all information running behind the screen.

The “rman.trc” will look like below

orclsr@oracle> cat rman.trc |more
Recovery Manager: Release 11.2.0.2.0 - Production on Thu Oct 11 15:21:05 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
ORACLE_HOME = /oracle/ORCL/112_64

System name:    Linux

Node name:      sapORCL

Release:        2.6.18-194.17.1.el5

Version:        #1 SMP Mon Sep 20 07:12:06 EDT 2010

Machine:        x86_64

Starting with debugging set to level=9, types=ALL
DBGMISC:    ENTERED krmksimronly [15:21:06.175]
DBGSQL:      CHANNEL> alter session set events '19737 trace name context forever'

DBGSQL:         sqlcode = 0

DBGSQL:      CHANNEL> select decode(archiver, 'FAILED', 1, 0) into :archstuck from v$instance

DBGSQL:         sqlcode = 0

DBGSQL:          D :archstuck = 0

You can refer mentalink note 375386.1 for more information.

Regards,
Satishbabu Gunukula, Oracle ACE
http://www.oracleracexpert.com

Send mails using UTL_SMTP in Oracle

The UTL_SMTP Oracle package provides capability to send emails from PL/SQL program using Simple Mail Transfer Protocol. UTL_MAIL is easy to use as it eliminates steps of formatting the message body and explicit coding of details required by SMTP protocol.

Use below simple example to send mails using UTL_MAIL

begin
utl_mail.send(
sender => 'fromuser@oracleracexpert.com',
recipients => 'touser@oracleracexpert.com',
message => 'Test mail for UTL_MAIL'
);
end;

If you can want to use UTL_MAIL refer “Send EMAILs using UTL_MAIL”

UTL_SMTP it is more fine-grained API that enables the attachments of larger size and it is very powerful utility.

Use below simple example to send mails using UTL_SMTP

DECLARE
mailhost VARCHAR2(64) := ‘localsmtp.oracleracexpert.com;
sender VARCHAR2(64) := 'fromuser@oracleracexpert.com';
recipient VARCHAR2(64) := 'touser@ oracleracexpert.com';
smtp_mail_conn utl_smtp.connection;
BEGIN
mail_conn := utl_smtp.open_connection (mailhost, 25);
utl_smtp.helo (smtp_mail_conn, mailhost);
utl_smtp.mail (smtp_mail_conn, sender);
utl_smtp.rcpt (smtp_mail_conn, recipient);
utl_smtp.data (smtp_mail_conn, 'This is a test mail using UTL_SMTP '
chr(10));
utl_smtp.quit (smtp_mail_conn);
END;
/

You can write multiple line messages using the UTL_SMTP.WRITE_DTA command, use following example

CREATE OR REPLACE PROCEDURE send_mail_smtp(recipient IN VARCHAR2,
sender IN VARCHAR2,
subject IN VARCHAR2,
message IN VARCHAR2,
mailhost IN VARCHAR2,
smpt_port IN NUMBER DEFAULT 25)
AS
smtp_mail_conn UTL_SMTP.connection;
BEGIN
Smtp_mail_conn := UTL_SMTP.open_connection(mailhost, smtp_port);
UTL_SMTP.helo(smtp_mail_conn, mailhost);
UTL_SMTP.mail(smtp_mail_conn, sender);
UTL_SMTP.rcpt(smtp_mail_conn, recipient);
UTL_SMTP.open_data(smtp_mail_conn);
UTL_SMTP.write_data(smtp_mail_conn, 'To: '||recipient||UTL_TCP.crlf);
UTL_SMTP.write_data(smtp_mail_conn, 'From: '||sender||UTL_TCP.crlf);
UTL_SMTP.write_data(smtp_mail_conn, 'Subject: '||subject||UTL_TCP.crlf);
UTL_SMTP.write_data(smtp_mail_conn, message||UTL_TCP.crlf||UTL_TCP.crlf);
UTL_SMTP.close_data(smtp_mail_conn);
UTL_SMTP.quit(smtp_mail_conn);
END;
/

Call the procedure by providing the below details

BEGIN
send_mail_smtp(recipient => 'touser@mycompany.com',
sender => 'fromuser@oracleracexpert.com',
subject => 'Test mail',
message => 'This is a test mail using UTL_SMTP.',
mailhost => localsmtp.oracleracexpert.com');
END;
/

To send emails to multiple recipients use UTL_SMTP.rcpt procedure for each separate email
For more information you can also refer Oracle Documentation http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/u_smtp.htm#ARPLS074

Common errors when using UTL_SMTP:-
1. The UTL_SMTP package requires the ORACLE xmldb component, please install the same to fix below error

ERROR at line 1:
ORA-24248: XML DB extensible security not installed
ORA-06512: at "SYS.UTL_TCP"

2. You may receive below error if the “mailhost” was not set correctly

ERROR at line 1:
ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at "SYS.UTL_SMTP", line 20

3. You will receive below error if ACL is not assigned to target host and necessary privileges are not granted

ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_TCP", line 17
ORA-06512: at "SYS.UTL_TCP", line 246
ORA-06512: at "SYS.UTL_SMTP", line 127
ORA-06512: at "SYS.UTL_SMTP", line 150
ORA-06512: at line 7