Thursday, November 29, 2012

ORA-00600: [ksmlsge1] , ORA-12518

This error indicates O/S lack of resources due to heavy load on the system. Clients usually when trying to connect recieve ORA-12518: TNS:listener could not hand off client connection which is an other idication for lacking of resources, cause listener cannot open the required server process/thread to handle the new conenction request.

DB alert.log should have similiar entries to the follwoing :

Error: skgmattach - Unable to register map, error 4210

ORA-00600: internal error code, arguments: [ksmlsge1], [], [], [], [], [], [], [] ORA-27103: internal error OSD-00013: Message 13 not found; product=RDBMS; facility=SOSD

Thursday, November 8, 2012

Change on Redo Logs on a Data Guard Configuration

Some times for perfomance reason there is a need to recreate the Online Redo logs on the primary database. Based on Oracles recomendation  there should exist also Stanby Redo Log files which will need also to be recreated. If you do not recreate a new control file fo the standby you compromise the DG configuration. So here are the steps for this operation (10g & 11g):

1. Find the location of the control files , Standby and Online Redo Log files on standby and check if the db_file_name_convert & log_file_name_convert has been set.

     As sysdba on standby issue (using SQLPLUS) :

     show parameter control_files
     show parameter db_file_name_convert
     show parameter log_file_name_convert
     select * from v$logfile

2. Shutdown the stanby database.

3. Make all the changes related to the Online and Standby Redo log Groups in the Primary Database.

4. Drop all  old Online and Standby Redo log Groups from the Standby Database.

5. Recreate the Standby Controlfile

    a. On primary as sysdba issue :
        alter database create standby controlfile as '/tmp/myfile';

    b. Transfer the new control file to location of the control files to the standby server

    c. With the Standby Database still down drop the old control files and rename the new one to the same    name(s)
    d. Mount the standby database
        startup mount

    e. Switch Flashback off on standby if has been enabled (If you are in a Fast FailOver Configuration it will be enabled)
        alter database flashback off;

    e. Wait a few minutes for the standby to clear the online redo logfiles and recreate everything on the correct locations

    g. If you do not have set the  db_file_name_convert & log_file_name_convert  parameters  you must manulally rename all files

    h. Stop Managed Recovery
        alter database recover managed standby database cancel;

    i. Re-Enable FlashBack Database and restart database
       alter database flashback on;
       shutdown immediate;

And you are again in sync.
Oracle Support Notes : 740675.1 & 459411.1

Tuesday, November 6, 2012

RMAN crashes with kgefec: fatal error 0 kgepop: no error frame to pop to for error 603

If an RMAN session crashes with the following error report :

kgefec: fatal error 0

kgepop: no error frame to pop to for error 603

Then the problem is the stack size of the UNIX user running the RMAN session (Usually oracle).

Check the stack size of the user : 

[oracle@s-eacea-rh-dbo02-p ~]$ ulimit -a | grep stack
stack size              (kbytes, -s) 10240
[oracle@s-eacea-rh-dbo02-p ~]$

The as root add the following lines in /etc/profile (RHEL , OEL):

if [ $USER = "oracle" ]; then
  ulimit -s unlimited

To verify , open a ne terminal login an oracle and repeat :

[oracle@s-eacea-rh-dbo02-p ~]$ ulimit -a | grep stack
stack size              (kbytes, -s) unlimited
[oracle@s-eacea-rh-dbo02-p ~]$

Add/Change as appropriate the etnries on /etc/security/limits.conf

oracle              soft    stack   unlimited
oracle              hard    stack   unlimited

Oracle Support Note ID : 1458718.1

Monday, November 5, 2012

Oracle 11g Online Patching

In 11g a new feature is the Online Patching of an Oracle Home with no DB Instance Shutdown.You will know that a patch can be from its README file.



cd 11111111

$ORACLE_HOME/OPatch/opatch apply online -connectString mySID:sys:syspass

The previous commands will apply online the 11111111 to SID : mySID

If you have another SID running on the same home then you must issue the following command to enable the patch.

$ORACLE_HOME/OPatch/opatch util enableonlinepatch -connectString mySID2:sys:syspass -id 11111111

In order to uninstall an online patch run from a single instance :

$ORACLE_HOME/OPatch/opatch rollback -id 11111111 -connectString mySID:sys:syspass 

In order to uninstall an online patch run from multiple instances :

$ORACLE_HOME/OPatch/opatch rollback -id 11111111 -connectString mySID:sys:syspass,mySID2:sys:syspass 


If you do not know in how many SIDs an online patch has been applied issue the command to remove it from the first instance and then opatch will report if its has been applied to other instances. In this situation, cancel the opatch session (answer n in the question if you want to proceed) and run the command for all the instances as decsribed above.

Friday, October 12, 2012

Cleanup Orphaned DATAPUMP jobs

Some times you cannot stop / kill a datapump job using the attach command , possibly with an error , and its status can be undefined or not running either on EM or in dba_datapump_jobs. If you are sure that this job must be cleared then perform the following as sys:

connect sys as sysdba on SQLPLUS

-- locate Data Pump jobs:

SELECT owner_name, job_name, operation, job_mode,
state, attached_sessions
FROM dba_datapump_jobs
WHERE job_name NOT LIKE 'BIN$%'


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

-- locate Data Pump master tables:

SELECT o.status, o.object_id, o.object_type,
o.owner||'.'||object_name "OWNER.OBJECT"
FROM dba_objects o, dba_datapump_jobs j
WHERE o.owner=j.owner_name AND o.object_name=j.job_name
AND j.job_name NOT LIKE 'BIN$%' ORDER BY 4,2;

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

-- drop the master table

DROP TABLE scott.sys_export_table_01 purge;

Wednesday, October 3, 2012

Truncate table makes previously UNUSABLE indexes USABLE

Check the example below :

alter index my_idx unusable; --> This make the index unusable
truncate table my_table;
The index my_idx becomes usable !!!

References :

Tuesday, September 4, 2012

aq_tm_processes significance for Oracle Streams

From 10.1 and up it is no longer necessary to set AQ_TM_PROCESSES when Oracle Streams AQ or Streams is used, but if you a value is specified, then that value is taken into account but the number of processes can still be auto-tuned.

If  AQ_TM_PROCESSES is explicitly specified then the process(es) started will only maintain persistent messages. For example if aq_tm_processes=1 then at least one queue monitor slave process will be dedicated to maintaining persistent messages. Other process can still be automatically started to maintain buffered messages. 

Up to and including version 11.1 if you explicitly set aq_tm_processes = 10 then there will be no processes available to maintain buffered messages. This should be borne in mind in environments which use Streams replication and from 10.2 onwards user enqueued buffered messages.

In addition you should never disable the Queue Monitor processes by setting aq_tm_processes=0 on a permanent basis. .

To check whether auto-tuning is enabled or aq_tm_processes=0 do the following:

connect / as sysdba

set serveroutput on

mycheck number;
select 1 into mycheck from v$parameter where name = 'aq_tm_processes' and value = '0' and (ismodified != 'FALSE' OR isdefault = 'FALSE');
if mycheck = 1 then
dbms_output.put_line('The parameter ''aq_tm_processes'' is explicitly set to 0!');
end if;
exception when no_data_found then
dbms_output.put_line('The parameter ''aq_tm_processes'' is not explicitly set to 0.');

If it is, then it is recommended to unset the parameter. However, this requires bouncing the database. In the meantime, if the database cannot be immediately bounced, the recommended value to set it to is '1', and this can be done dynamically:

References : Oracle Support Note: 305662.1

Thursday, July 26, 2012

Manually delete flashback logs

Usually when you stop the flashback database feature the flashback logs are automatically increased. In order to manually delete the flashback logs follow this procedure ;

shutdown immediate;
startup mount;
alter database flashback off;
--> Delete using O/S commands
alter database flashback on;
alter database open;

For and Up you can disable flashback database with bouncing the DB.

Configure one way schema replication with Oracle Streams

Oracle Streams can be used to perform replication of databases , schemas , tablespaces and tables between heterogeneous O/S and different DB versions.

In this post i will present the procedure to replicate a schema from a 10gR2 Db on Windows to a 11gR2 DB on RHEL. The instantiation of the schema will take place using Data Pump import (impdp) using the network (via the DB Links that will be created)

A. Configuration Parameters for both Source and Target 

1. COMPATIBLE must be >=
From 10gR2 and up is it advisable to be unset and let system autotune. If it was set to a specific value then the recomended is to set to 1

B.  Create Oracle Streams administrator user on both DBs with the same credentials. Its advisable to create a specific tablespace for this user and give him the necessary privileges.

As SYSDBA user:

create user strmadmin identified by strmadmin  
  default tablespace streams_tbs quota  unlimited on streams_tbs;
grant connect, resource, aq_administrator_role,dba to strmadmin;  

grantee => 'strmadmin',  
grant_privileges => true);  

C. Create the directory object which will be used to store the script that will enable the Streams on both source and target DB Server

create directory streams_files as '[DIRECTORY_PATH]';

grant read,write on directory streams_files to strmadmin;

The directory path can be different on source and target DB server.

D. Create the appropriate TNS entries and both source and target

Ensure that you will use the global_name of each database to the TNS connect string and that DB Link is working properly from both DBs.

As The Streams Administrator User

create database link [DB_GLOBAL_NAME] connect to strmadmin identified by strmadmin using '[DB_TNS_ENTRY]';

E. Execute on target the following script to replicate schema AGIS which has a table named T for the test purposes

As Streams Admin User on Source DB

  schema_names=> 'agis',
  source_directory_object=> null,
  destination_directory_object=> null,
  source_database=> 'ODIN.NET1.CEC.EU.INT',
  destination_database => 'EROSD.NET1.CEC.EU.INT',
  perform_actions => true,
  script_name =>'Schema_maintain_streams.sql',
  bi_directional=> false,
  include_ddl => true ,

If The PL/SQL block terminates without errors then check Streams Functionality.

"instantiation=>dbms_streams_adm.instantiation_schema_network" on the procedure will instantiate the desired schema using impdp via DBLINK.

F. Test that Streams Replication is working

For example : 

- Insert some records on T@SOURCE and check if the changes are propagated on T@TARGET
- Add a new column on T@SOURCE and check if the changes are propagated on T@TARGET

Oracle Support Note ID: 878638.1

Thursday, July 12, 2012

Web services callouts from Oracle DB 10g and 11g PL/SQL

In order to perform Web Service calls using PL/SQL use must install the UTL_DBWS PL/SQL package.

First connect as sys and check if has been installed (In 11g its isntaleld by default).

SQL> describe sys.utl_dbws

If this command does not return the package declarations the you must install it manual:

1. Run the following scripts under SYS:


2. The dbwsclientws.jar must be loaded into the database for Web services call-outs and you can verify whether it is already loaded by running the following query in the SYS schema:

select status, object_type
from   all_objects
where  dbms_java.longname(object_name)='oracle/jpub/runtime/dbws/DbwsProxy$1';

The following result indicates that the file is already loaded:

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

If its not loaded use the follwoing commands to load it on the DB:

cd $ORACLE_HOME/sqlj/lib 

loadjava -u username/password -r -v -f -s -grant public -genmissing dbwsclientws.jar dbwsclientdb11.jar

Then grant to above username the following permissions as sys:

execute dbms_java.grant_permission('<SCHEMA>','SYS:java.util.PropertyPermission','http.proxySet','write');
execute dbms_java.grant_permission('<SCHEMA>','SYS:java.util.PropertyPermission','http.proxyHost', 'write');
execute dbms_java.grant_permission('<SCHEMA>','SYS:java.util.PropertyPermission','http.proxyPort', 'write');
execute dbms_java.grant_permission('<SCHEMA>','SYS:java.lang.RuntimePermission', 'accessClassInPackage.sun.util.calendar','');
execute dbms_java.grant_permission('<SCHEMA>','SYS:java.lang.RuntimePermission','getClassLoader','');
execute dbms_java.grant_permission('<SCHEMA>','','*','connect,resolve');
execute dbms_java.grant_permission('<SCHEMA>','SYS:java.util.PropertyPermission','*','read,write');
execute dbms_java.grant_permission('<SCHEMA>','SYS:java.lang.RuntimePermission','setFactory','');

It is recommended that a user created schema be used for this, (CONNECT, RESOURCE and CREATE PUBLIC SYNONYM are the minimum grants that have to be performed on this schema). In the 11g version of the database, it has been deteremined that loading the jars into ths SYS schema will cause conflicts with existing classes already loaded in this schema by default, so it is best to not use SYS for loading the jars. 

Oracle Support Note: 1166206.1

Wednesday, July 4, 2012

Oracle Database on Windows Server Memory Fucntionality

A very nice series of articles about Windows Server Memory Architecture. A must read if you are building an Oracle Database Server on Windows(but not only).

Monday, July 2, 2012

Handling ORA-0376 (ORA-376) Errors

ORA-376 means at some point in time a datafile made unavailiable on the database. Usually when you come across this error with the database opened, unless SYSTEM tablespace has been unavailiable. Most of the times and depending on the cases you can bypass this error by just recovering the offended datafiles with the database in open or mounted state.


 1. Check the alert.log of the instance and find the file and the tablespace affected when the error generated. You can also check the corresponding tracefile for further info.

Mon Jul 02 01:01:53 2012
KCF: write/open error block=0x13e5e online=1
     error=27070 txt: 'OSD-04016: Message 4016 not found; No message file for product=RDBMS, facility=SOSD
O/S-Error: (OS 1450) Insufficient system resources exist to complete the requested service.'
Mon Jul 02 01:01:53 2012
ARC0: Failed to archive thread 1 sequence 84025 (4)
ARCH: Archival stopped, error occurred. Will continue retrying
Mon Jul 02 01:01:53 2012
Automatic datafile offline due to write error on

2. Find out the status of the offened tablespacetablespaces:

select status,count(*) from dba_tablespaces group by status;

3. Find out the status of the datafiles using the number you found out before.

select file#,name,status,enabled from v$datafile where file#=3;

a. Check that the name of the datafile is matching the one from alert.log and the status column. If status is offline then you can bring the tablespace / datafile online and you have finished.
alter tablespace [TABLESPACE_NAME] online;
alter database datafile [FULL_PATH_DATAFILE_NAME] online;

b. If the status is recover then you must try media recovery for the datafile and then try to bring it online. If the affected tablespace is SYSTEM, SYSAUX, UNDOTBS then you must cleanly shutdown the database (if its still open) and perform the following steps using sqlplus:
shutdown immediate;

startup mount

recover datafile [FULL_PATH_DATAFILE_NAME];
Use AUTO if you prompeted for an Archive Log name to continue recovery using the online redo.logs

alter database datafile [FULL_PATH_DATAFILE_NAME] online;

alter database open;
If database opens without an error then restart the DB.

shutdown immediate;


Its advisable to take a new backup for the affected tablespaces.

1. References

Oracle Support Document ID(s): 183327.1, 1013912.6, 

Friday, June 8, 2012

Make Tablespace Read Only takes too long

Many time when making a tablespace read only it can seem that the operation has hanged. Usually your session is waiting for transaction(s) to finish that have started prior the alter tablespace ... read only statement. So in order to decide what to do you must find those transactions and their corresponding sessions and then decide what to do. Here is the procedure :

- Find the your session address

AND lower(SQL_TEXT) LIKE 'alter tablespace%read only%' ;

- Find transactions that started prior your session (note that the order is on START_SCNB)


- Decide what to do with the sessions

Friday, May 25, 2012

Oracle 11gR2 Installation Tips (Linux x86_64)


1. Create a listener prior the database creation , since dbca would not continue as in 10g
2. In order to use the new MEMORY_TARGET feature you must have the /dev/shm at least the same amount.
    as root :
    mount -t tmpfs shmfs -o size=your_size_in_bytes /dev/shm
    and this line to /etc/fstab
    shmfs /dev/shm tmpfs size=your_size_in_bytes 0


i. The parameter STATISTICS_LEVEL must be set to TYPICAL
ii. The parameter MEMORY_MAX_SIZE controls the maximum value you can set for  MEMORY_TARGET. If you do not set a value for this parameter, it defaults to MEMORY_TARGET.
iii. If you set the parameters SGA_TARGET and PGA_TARGET, Oracle will consider the values as the minimum values for SGA and PGA.
iv. If you do not set the parameters SGA_TARGET and PGA_TARGET (or set them to zero), no minimum value is considered by Oracle for the SGA and PGA. When the instance starts, it assigns 60 percent to SGA and 40 percent to the PGA.
v. When MEMORY_TARGET is configured, the following components are auto tuned: DB BUFFER CACHE, SHARED POOL, JAVA POOL, LARGE POOL and STREAMS POOL. software release

1. Starting with Oracle Database 11g Release 2 (, all the 32-bit packages, except for gcc-32bit-4.3, listed in the following table are no longer required for installing a database on Linux x86-64. Only the 64-bit packages are required.

Oracle® Database Installation Guide 11g Release 2 (11.2) for Linux

Monday, April 23, 2012

ORA-14257: cannot move partition other than a Range or Hash partition

ORA-14257 raises because the partitions tryied to be moved are composite . When you have composite partitioning you have to move the subpartitions instead of the partitions because the row data is physically located in the subpartitions.

If you find out that the partitions point to other tablespaces than the subpartiions and you want to point to the same tablespace then you have to modify the default attributes.

alter table [table_name] modify default attributes for partition [partition_name] tablespace [tablespace_name];

Monday, March 19, 2012

Oracle Golden Gate

Hi all,

I would like to start a discussion through my blog about Golden Gate.
Is this tool something that deserves to be learned ?
Please post opinions , experience , tips and trick sources , docs everything !!!

Check the comments for updates !!

Wednesday, February 8, 2012

Oracle Scheduler does not works as expected

It is possible that the Oracle Scheduler to hang if you change a windows attribute and make a mistake or after upgrades from - >

Usually you will see that shceduled jobs that no executed correct and in the cjq trace files you will find the follwoing:

jsksGetCurWindowId:1 got error 27468
jsksGetCurWindowId:1 got error 27468

So if you fall into this please execute the follwoing as sysdba (Assuming default scheduler windows):

variable v number;

 dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED', 'TRUE');
select value from v$parameter where name='job_queue_processes';
--Please keep this number (assume J)

alter system set job_queue_processes=0;


-- 2 times is a must
alter system flush shared_pool;
alter system flush shared_pool;

cwo number;
select o.obj# into cwo from sys.obj$ o where = 'CURRENT_OPEN_WINDOW' and o.namespace = 51;

update sys.scheduler$_global_attribute set value = null, attr_tstamp = null,
attr_intv = null, additional_info = null where obj# = cwo;



 dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED', 'FALSE');

--Set job_queue_processes to its original setting using J
alter system set job_queue_processes=J;


Please check the the windows group you have created (MAINTENACE_WINDOW_GROUP) is the default in order to see if your windows are members or need to be added again(Use EM).

Oracle Support Note ID: 731678.1