Wednesday, November 20, 2013

ORA-00600: internal error code, arguments: [6749]

When you see these errors in alert log this is block corruption on some files. Usually due to BUG 6127434 these objects are owned by SYS / SYSMAN and are residing on SYSAUX tablespace

Follow the example for the solution:

1. Error identification :

Thu Nov 14 07:01:07 GTB Standard Time 2013
Errors in file c:\oracle\product\10.2.0\admin\ergose\bdump\mysid_j000_3028.trc:
ORA-00600: internal error code, arguments: [6749], [3], [12614560], [1], [], [], [], []

From the trace file find find the FILE_ID and the BLOCK_NUMBER

Check for similar lines :

DBAs (file#, block#): (3, 30754) 

Run the foillwoing query:

SELECT tablespace_name, segment_type, owner, segment_name 
    FROM dba_extents 
    WHERE file_id = 3
    and 30754 between block_id AND block_id + blocks - 1;

SYSAUX  INDEX     SYSMAN MGMT_METRICS_RAW_PK


select owner,index_name,index_type,table_name from dba_indexes where index_name in ('MGMT_METRICS_RAW_PK','MGMT_CURRENT_METRICS_PK');

OWNER INDEX_NAME INDEX_TYPE TABLE_NAME
SYSMAN MGMT_METRICS_RAW_PK IOT - TOP MGMT_METRICS_RAW

Problem originated from MGMT_METRICS_RAW table (IOT)

With SQLPLUS As sys as sysdba:

SQL> create table SYSMAN.MGMT_METRICS_RAW_COPY 
as select * from SYSMAN.MGMT_METRICS_RAW;

SQL> insert into SYSMAN.MGMT_METRICS_RAW select * from SYSMAN.MGMT_METRICS_RAW_COPY; 
SQL> commit;

SQL> drop table SYSMAN.MGMT_METRICS_RAW_COPY; 

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;

Wednesday, August 7, 2013

ORA-00600 [kqlnrc_1]

ORA-00600 [kqlnrc_1] usually is about an invlaid object in the Library Cache.

When this error is raised an Oracle Trace file has been created so in order to find the object caused this ORA-600 error do the following :

Open the trace file with an editor (The following lines are from an 11.2.0.3 trace file) :

1. Find the first ORA_600 error argument
ORA-00600: internal error code, arguments: [kqlnrc_1], [0x143C6DAD0], [], [], [], [], [], [], [], [], [], []

2. Search the trace file for 0x143c6dad0 (its the first argument in lower case):

DDE: Problem Key 'ORA 600 [kqlnrc_1]' was flood controlled (0x2) (incident: 35561)
ORA-00600: internal error code, arguments: [kqlnrc_1], [0x143C6DAD0], [], [], [], [], [], [], [], [], [], []

LibraryHandle:  Address=0x143c6dad0 Hash=cd3b674a LockMode=S PinMode=S LoadLockMode=0 Status=INVL
  ObjectName:  Name=PUBLIC.TEST
    FullHashValue=5c820441b24ce2df1d9a0971cd3b674a Namespace=TABLE/PROCEDURE(01) Type=SYNONYM(05) Identifier=0 OwnerIdn=2147483644
  Statistics:  InvalidationCount=2 ExecutionCount=0 LoadCount=1 ActiveLocks=1 TotalLockCount=6 TotalPinCount=6
  Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=0 BucketInUse=6 HandleInUse=6 HandleReferenceCount=0
  Concurrency:  DependencyMutex=0x143c6db80(0, 8, 0, 0) Mutex=0x143c6dc00(233, 45, 0, 6)
  Flags=REM/PIN/TIM/[00022801]
  WaitersLists:
    Lock=0x143c6db60[0x143c6db60,0x143c6db60]
    Pin=0x143c6db40[0x143c6db40,0x143c6db40]
    LoadLock=0x143c6dbb8[0x143c6dbb8,0x143c6dbb8]
  Timestamp:  Current=04-25-2013 09:41:24
  HandleReference:  Address=0x143c6dc90 Handle=(nil) Flags=[00]
  ReferenceList:
    Reference:  Address=0x145badf28 Handle=0x12d7be980 Flags=DEP[01]
      Timestamp=04-25-2013 09:41:24 InvalidatedFrom=0
  LibraryObject:  Address=0x10dbe40b0 HeapMask=0000-0001-0001-0000 Flags=EXS/LOC[0004] Flags2=[0000] PublicFlags=[0000]
    DataBlocks:
      Block:  #='0' name=KGLH0^cd3b674a pins=0 Change=NONE
        Heap=0x12297cc10 Pointer=0x10dbe4150 Extent=0x10dbe4030 Flags=I/-/P/A/-/-
        FreedLocation=0 Alloc=0.257812 Size=4.000000 LoadTime=15174108210 

3. Find the object :

SELECT object_name,object_type,owner,status
FROM dba_objects
WHERE object_name='TEST' and owner='PUBLIC';

OBJECT_NAME OBJECT_TYPE OWNER STATUS
-------------- ------------- ---------------- -------
TEST SYNONYM PUBLIC VALID

4. Compile the synonym owned by public.

As sys :

SQL> alter public synonym TEST compile;

or Recreate it

SQL> create or replace public synonym v_o_thp_link_sup_ven for v_o_thp_link_sup_ven@si2p.world;

REFERENCES:
Oracle Support Note 1092832.1

Friday, June 21, 2013

11gR2 Database Point In Time Recovery to new Host using Recovery Catalog

The follwoing scenario recovers the DB from the tape backup to an another Host. In order to use the procedure you must have a recovery catalog database which does not requires a connection to the target DB. Tape Librabry parmeters here are using a Legato Client , Consult your tape library provider for the correct ones.

1. On the new host all the DB

- Ensure connectivity between the new host and the RMAN catalog DB through TNS
- Ensure sufficient space for the recovery
- If the primary DB uses a Block Change Tracking file please copy it on the same location the ne new host , or create a dummy file on the smae location and name
- Perform the following actions as the Oracle User:

export ORACLE_SID=aux
rman auxiliary / catalog user@db/pass
RMAN> startup clone nomount;
RMAN> run {
allocate auxiliary channel ch1 DEVICE TYPE 'SBT_TAPE' PARMS  'ENV=(NSR_SERVER=myserver,NSR_CLIENT=myclient,NSR_DATA_VOLUME_POOL=oracledb)';
duplicate database PRIMARY dbid 2648977915 to AUX
UNTIL TIME "to_date('13/06/2013 00:00:00', 'DD/MM/YYYY HH24:MI:SS')"
db_file_name_convert 'primary','aux'
spfile
parameter_value_convert 'primary','aux'
set log_file_name_convert 'primary','aux';
 }

2. On the new host specific tablespaces DB

- Ensure connectivity between the new host and the RMAN catalog DB through TNS
- Ensure sufficient space for the recovery
- If the primary DB uses a Block Change Tracking file please copy it on the same location the ne new host , or create a dummy file on the smae location and name
- Perform the following actions as the Oracle User:

export ORACLE_SID=aux
rman auxiliary / catalog user@db/pass
RMAN> startup clone nomount;
RMAN> run {
allocate auxiliary channel ch1 DEVICE TYPE 'SBT_TAPE' PARMS  'ENV=(NSR_SERVER=myserver,NSR_CLIENT=myclient,NSR_DATA_VOLUME_POOL=oracledb)';
duplicate database PRIMARY dbid 2648977915 to AUX
UNTIL TIME "to_date('13/06/2013 00:00:00', 'DD/MM/YYYY HH24:MI:SS')"
tablespace users
db_file_name_convert 'primary','aux'
spfile
parameter_value_convert 'primary','aux'
set log_file_name_convert 'primary','aux';
 }

Both Procedures will restore the spfile and perform file location convertion. If you want to use the same location use the nofilenamecheck as follows :

 run {
allocate auxiliary channel ch1 DEVICE TYPE 'SBT_TAPE' PARMS  'ENV=(NSR_SERVER=myserver,NSR_CLIENT=myclient,NSR_DATA_VOLUME_POOL=oracledb)';
duplicate database PRIMARY dbid 2648977915 to AUX
UNTIL TIME "to_date('13/06/2013 00:00:00', 'DD/MM/YYYY HH24:MI:SS')"
nofilenamecheck
spfile
}

Can can also setup an initfile of your choice and put it on $ORACLE_HOME/dbs. RMAN then will use this file to startup the clone and remove the spfile directive from the command.

Of cource you can do this with disk backups with the same commands allocating Disk Auxiliary Channels and make backups availiable to the new Host.

If you do not have a recovery catalog, you can still perform the operation but only for disk backups. This is achieved via backup location clause of the duplicate command e.g
 run {
allocate auxiliary channel ch1 DEVICE TYPE DISK;
duplicate database PRIMARY dbid 2648977915 to AUX
UNTIL TIME "to_date('13/06/2013 00:00:00', 'DD/MM/YYYY HH24:MI:SS')"
backup location '/backup'
nofilenamecheck
}

REFERENCES
Oracle Support Notes : 1375864.1 , 1113713.1 , 73974.1
Oracle® Database Backup and Recovery Reference 11g Release 2 (11.2)

Tuesday, June 18, 2013

ORA-00600 [kwqitnmphe:ltbagi]

ORA-600 [kwqitnmphe:ltbagi], [1], [0] reported in the alert log file. This issue arises on 11.2.0.2 is fixed on 11.2.0.3 but if the db has been upgraded from 11.2.0.2 -> 11.2.0.3 its also valid.

In order to be able to use the proposed workaround verify the issue :

1.

Inside the trace file reported in alert log you find :

Error 600 in Queue Table SYS.SCHEDULER$_EVENT_QTAB

2.
connect / as sysdba
Run:
select object_type from dba_objects where owner='SYS' and object_name='SCHEDULER$_EVENT_QTAB_HIST';

You should see :

OBJECT_TYPE
-------------------
INDEX


Solution :

connect / as sysdba

Drop the problematic index

drop index sys.scheduler$_event_qtab_hist;


Purge all messgaes in the queue along ith the problematic data

DECLARE
po dbms_aqadm.aq$_purge_options_t;
BEGIN
po.block := FALSE;
DBMS_AQADM.PURGE_QUEUE_TABLE(
queue_table => 'SYS.scheduler$_event_qtab',
purge_condition => NULL,
purge_options => po);
END;
/


Recompile all invalid objects

@?/rdbms/admin/utlrp.sql




Thursday, April 4, 2013

Oracle Text Error : DRG-50857: oracle error in drvddl.IndexOptimizeRebuild

When using Oracle text indexes privileges , several privileges are granting to the user owns the indexes. These privileges are enough if you want to create , sync and ptimize the indexes excpet the optimization with rebuild option. This option requires the create trigger option beacuse Optimize Rebuild uses a trigger to capture changes made during the optimize. 

e.g

connect myuser/pass

exec ctx_ddl.OPTIMIZE_INDEX('MY_TEXT_IDX','REBUILD');

BEGIN ctx_ddl.OPTIMIZE_INDEX('MY_TEXT_IDX','REBUILD'); END;

*
ERROR at line 1:
ORA-20000: Oracle Text error:
DRG-50857: oracle error in drvddl.IndexOptimizeRebuild
ORA-01031: insufficient privileges
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.CTX_DDL", line 1042
ORA-06512: at line 1

After granting the CREATE TRIGGER privilege to MYUSER command executed succesfully !!


Wednesday, February 27, 2013

Configure a DB Scheduler job to send e-mail notifications

Most of the time you want to configure a job to send e-mail notifications about its state. The different states that a job can raise an event and thus send an email are :


job_broken
job_chain_stalled
job_completed
job_disabled
job_failed
job_over_max_dur
job_run_completed
job_sch_lim_reached
job_started
job_stopped
job_succeeded

job_all_events --> all the above.

First of all we have to provide the DB Scheduler with the SMTP mail server to use and a default email sender to be used:


begin

  dbms_scheduler.set_scheduler_attribute('email_server','mysmtp.mail.server'); --> Using SMTP default port (25) 
--  dbms_scheduler.set_scheduler_attribute('email_server','mysmtp.mail.server:777'); --> Using SMTP 777 port 

  dbms_scheduler.set_scheduler_attribute('email_sender','myemail@myserver.com');
  commit;
end;
/


This must be configured once. 

Then assuming that a job named MYJOB has been created run this as the job owner.




begin
  dbms_scheduler.add_job_email_notification ( job_name => 'MYJOB',
 recipients => 'myrecipient1@myemail.com,myrecipient2@myemail.com',
  subject => 'Scheduler Job Notification : %job_owner%.%job_name%-%event_type%'
  , body => '%event_type% occurred at %event_timestamp%. %error_message%',
  events => 'job_all_events' );
  commit;

end;
/




The previous command will send an email for all job events to the recipients specified using the default sender. use the following to specify sender and specific job states:


begin
  dbms_scheduler.add_job_email_notification ( job_name => 'MYJOB', recipients
  => 'myrecipient1@myemail.com, myrecipient1@myemail.com', sender =>
  'db_job_notification@myemail.com', subject =>
  'Scheduler Job Notification-%job_owner%.%job_name%-%event_type%', body =>
  '%event_type% occurred at %event_timestamp%. %error_message%', events =>
  'JOB_FAILED, JOB_BROKEN, JOB_DISABLED, JOB_SCH_LIM_REACHED');
  commit;

end;
/



By using the following query you have information for which jobs email notifications have been setup.


select job_name,
  recipient,
  event
from user_scheduler_notifications;



REFERENCES
Oracle® Database Administrator's Guide 11g Release 2 (11.2)


Wednesday, February 6, 2013

Row lock contention during inserts on table with Bitmap Indexes

Recently i investigated an issue which several concurrent sessions running inserts statements were waiting on enq: TX - row lock contention. 

I run an ASH report form Oracle EM for the duration the problem observed and i found the objects where were the root cause for these waits were 2 indexes. 

I checked the table structure and the mentioned indexes and found out that they were BITMAP indexes. As mentioned in Oracle® Database Data Warehousing Guide "Bitmap indexes are primarily intended for data warehousing applications where users query the data rather than update it. They are not suitable for OLTP applications with large numbers of concurrent transactions modifying the data"  

I have seen this behavior and in other OLTP applications and its an index design problem on the table. After explain this to the DEV team i drop and recreated the indexes as normal B-Tree indexes and problem resolved.

Please check also the following Blog entries with details on that problem. 






Friday, February 1, 2013

Oracle 11gR2 : db file async I/O submit

Recently i have sheen the waits on thsi event on an Oracle 11gR2 Enterprise editon on Linux x86_64. Further invetigion in metalink did not revealed any usefull information , but i found the following post on the web:

http://andreynikolaev.wordpress.com/2010/07/29/db-file-async-io-submit-wait-event/