Wednesday, September 25, 2013

ORA-27369 job of type EXECUTABLE failed : Permission denied

This error is raised when from the DB scheduler you try to execute an executable created on the O/S Level.
In order to fix this error you can do the following :

Unix like O/S

- From 10.2.0.3 and up (11gR2 validated) you just need to edit the file $ORACLE_HOME/rdbms/admin/externaljob.ora and put the user oracle instead of nobody. 

Usually this must be done as root user.

e.g 

run_user = oracle
run_group = oinstall

#run_user = nobody

#run_group = nobody


- In prior releases there is no $ORACLE_HOME/rdbms/admin/externaljob.ora and you need to change the owner of the extjob executable from nobody to oracle 

Windows O/S

Change the user that the external jobs extjob Windows service runs as



Wednesday, September 11, 2013

ORA-00845: MEMORY_TARGET not supported on this system using 11g on Linux

You are trying to use the Automatic Memory Management (AMM) has been implemented by setting parameters like MEMORY_TARGET and/or MEMORY_MAX_TARGET in a 11g database.

Starting with Oracle Database 11g, the Automatic Memory Management feature requires more shared memory (/dev/shm) and file descriptors. This feature requires the /dev/shm file system to be mounted with a size of bytes to accommodate the instance memory (SGA + PGA), meaning that it must be at least equal with the greater of the MEMORY_MAX_TARGET and MEMORY_TARGET.

The following example shows that if you try to start an instance with MEMORY_MAX_TARGET or MEMORY_TARGET with more than 4G the ORA-00845 error would be raised on startup

Filesystem            Size  Used Avail Use% Mounted on
tmpfs                  12G  8.3G  3.8G  69% /dev/shm

So do the following :

As Oracle stop all the running instances on the machine

AS root : 

# mount -t tmpfs shmfs -o size=20g /dev/shm 
# vi /etc/fstab 
add or change the line for the /dev/shm filesystem to the following : 
shmfs /dev/shm tmpfs size=20g


Tuesday, September 3, 2013

Guide to Oracle Data Guard Fast-Start Failover

Converting a varchar2 column to blob or clob

In order to convert a table column from varchar2 to blob or clob do the following :

VARCHAR2 -> BLOB

drop table test purge;

create table test
  (a varchar2(1000)
) ;

insert into test
select table_name from user_tables where rownum < 5;

commit;

select * from test;

alter table test add blob_f blob;

update test set blob_f = utl_raw.cast_to_raw(a);
commit;

alter table test drop column a;

alter table test rename column blob_f to a;

 select utl_raw.cast_to_varchar2(a) from test;

VARCHAR2 -> CLOB

drop table test purge;

create table test
   (a varchar2(1000)
) ;

insert into test
select table_name from user_tables where rownum < 5;
commit;

select * from test;

alter table test add clob_f clob;

update test set clob_f = a;
commit;

alter table test drop column a;

alter table test rename column clob_f to a;


 select a from test;