Anuroop Challa

ORA 700 [kskvmstatact: excessive swapping observed] ======================================== One of my database down suddenly. Reason:  ORA-700 SWAP error, as the dump it's taking more that 10 minutes pmon killed the process and the database was down. this database running with less memory . Solution alter system set "_kse_die_timeout"=XX or 0 if we want to disable the pmon killer during dumping . note: The error is documented in:  -  Note:1919850.1  - 12c: Warning Message About Heavy Swapping Observation and ORA-700 [kskvmstatact: excessive swapping observed]  Because your swap memory has low values of swap in / swap out, this warning message can be safely ignored.  Given both the alert log message and ORA-700 are expected behavior for 12c, no action is required.  An enhancement request (ER) was created to change the threshold in future releases in  Bug 19495842  - CHANGE THRESHOLD IN THE SWAP WARNING IN THE ALERT_<...

ORA-04030: out of process memory when trying to allocate 123416 bytes ==================================================== we revive alert from database due to less memory. user run query consuming more. It impacts other process to fail. This error causing lack of memory in pga and makes other process to fail. You can also monitor the PGA usage and high consumption of the pga process with below queries during the error time. ================================================= spool memory_results.txt append set pages 999 select to_char(sysdate,'DD-MON-YY HH:MI:SS') from dual; PROMPT PROMPT PROCESS CONSUMING MORE PGA: PROMPT ------------------------- set line 200 col logon for a10 col spid for a10 col username for a10 col osuer for a10 col machine for a10 col program for a15 col module for a10 SELECT DECODE(TRUNC(SYSDATE - LOGON_TIME), 0, NULL, TRUNC(SYSDATE - LOGON_TIME) || ' Days' || ' + ') || TO_CHAR(TO_DATE(TRUNC(MOD(SYSDATE-LOGO...

ORA-07445: exception encountered: core dump ===================================== Note: ORA-07445 please note that internal error all are not same depends on argument it vary. this was one of the internal error we most cases, the best practice we follow was contact oracle support. and upload the file to them(even in starting trace file and alertlog verfiy and find the cause) in my case cause was one our app team member created view and executed from toad.(he did his testing in production, so better you suggest them to test in test and qa) solution:-    1. tune the sql and try to gather stats on objects.   2. incase if still first solution does not work follow below procedure set parameter to avoid.   alter session set "_simple_view_merging"=false; in my case contacted the app team member applied suggestion number 1.

ORA-01436: CONNECT BY loop in user data ====================================== this error will produce mostly from CONNECT BY looping from staement, this looping from sql cause never ending so it generates error.  check with your trace file and check with  application team to know what they runing and rectify it 

ORA-01000: maximum open cursors exceeded   cause : this issue produce reason OPEN_CURSORS parameter size, in your database please check present size and increase it more value to avoid error repetition in future.  To check present value:- sho parameter OPEN_CURSORS  NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ open_cursors                         integer     300 increase value by using below command alter system set open_cursors = 1000 scope=both;

TYPE difference between source and target after import :-  in our source database we have objects(TYPE) in schame_1 some x after import in target db the number show TYPE count Y.  REASON:- subtype , from 11g sub-type show multiple number, when we import schema by using datapump as per below picture, But type count show diff  we can ignore in this case count if it is related to subtype. select OWNER,OBJECT_NAME,OBJECT_TYPE,CREATED,TIMESTAMP from dba_objects where OWNER='<SCHEMA_NAME>' and OBJECT_TYPE='TYPE';

ORA-00020 maximum number of processes exceeded. ========================================   this situation happen when maximum number of processes exceeded than present database process settings.   usually this case have multiple scenario.  scenario 1:- error persist with sudden spike and reduce level than database settings, so you still can logon database as normal.   in this case you have to trouble shoot the cause, incase some went wrong from application sudden increase session check/verfiy with them and incase it happen by mistake from app to reduce it avoid in future from app to take preventive action. (or)   Per requirement need more number in setting change the setting in database level with proper down time.  To check Current Parameter Settings show parameter sessions show parameter processes show parameter transactions when you try to increase above parameter should fallow best practice to increase by using below formula. proce...

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired ============================================================ this error generate while you try to alter object when that object was already been in use, to check who is accessing this object and check if we can kill that session or not if can kill and proceed. if previous running need to wait until completer wait and rerun your job. How to check (please modify below scripts as per your requirement owner, object_name, object_type fields) =========== SELECT OBJECT_ID, SESSION_ID, inst_id FROM GV$LOCKED_OBJECT WHERE OBJECT_ID=(select object_id FROM dba_objects where object_name='<OBJECT_NAME>' and object_type='<TABLE(or)INDEX>' and owner='<SCHEMA_NAME') ;

ORA-03136: inbound connection timed out ---------------------------------------------------------- usually this error we can rectify by adding below paramter in listner and sqlnet file and re bounce the listener  Step 1. In $ORACLE_HOME/network/admin/listener.ora, add the line: INBOUND_CONNECT_TIMEOUT_LISTENER = 0 Step 2. In the Oracle Server 10g sqlnet.ora, add the line: SQLNET.INBOUND_CONNECT_TIMEOUT = 0 Step 3. Stop and restart the listener/rebounce listner for the changes to take effect. thanks for your visit! please comment if you have any suggestions!!! 

Oracle Job not running AUTO in schedule task ============================= select value from v$parameter where name='job_queue_processes'; ------------------If the value is 0 job will not run auto -------------------------- If this is the problem you can increase the parameter using SQL> alter system set job_queue_processes= xxxx;

ORA-04030: out of process memory when trying to allocate 16328 bytes (koh-kghu call ,pmuccst: adt/record)   we face this issue in our environment when new function created and when it triggered in database level error generated, enthronement  was oracle SE edition 11gR2 on linux server. Errors in file -----------------------------------------------------------------------------_ora_15857.trc  (incident=280769): ORA-04030: out of process memory when trying to allocate 16328 bytes (koh-kghu call ,pmuccst: adt/record) Incident details in:  ----------------------------------------------------------------------------- _ora_15857_i280769.trc Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Errors in file  ----------------------------------------------------------------------------- _ora_15857.trc  (incident=280770): ORA-04030: out of process memory when trying to al...

ORA-03137: TTC protocol internal error : [12333] [255] [255] [255] [] [] [] [] even though i post same error before but with diffrent argument  these internal errors have any arguments and many soluotions .  [12333] [255] [255] [255] [] [] [] [] solution  Add this parameter to the sqlnet.ora file at BOTH the origin (or client) and at the server , instance:  SQLNET.SEND_TIMEOUT=n  Where n is a value in seconds. The setting itself, and not the value, should resolve this issue so a relatively high setting eliminate this problem.  E.g.  SQLNET.SEND_TIMEOUT=60000  Thanks for the visit !  Any doubts or correction/suggestions, please comment below it helps me to improve in further posts. --------above sol not resolved after apply so for this 3137 traditional parameter we applied for fix Hello Dipak - Please verify/perform the following and let us know the status. 1. Check the client software version. An ...

sharing This error shown when load high from database and unix server(as we have unix type veritas cluster ) detect hang and db failover to another node. even in alertlog show only one errror and no other info(no trace file generation) trace cause so  in this case below is help to trace(generate trace file immediate when impact time by using below mentod) and raise case to vedor and upload trace file(even if you can able to trace cause from file as you can) Hanganalyze need to be collected when the situation occurs, below are the steps to perform such task.  Hanganalyze  sqlplus '/ as sysdba'  oradebug setmypid  oradebug unlimit  oradebug hanganalyze 3  -- Wait one minute before getting the second hanganalyze  oradebug hanganalyze 3  oradebug tracefile_name  exit  Thanks for the visit !  Any doubts or correction/suggestions, please comment below it helps me to improve in further posts.

  Doc ID 130866.1 REM Script is meant for Oracle version 9 and higher REM ----------------------------------------------- set serveroutput on exec dbms_output.enable(1000000); declare cursor c_dbfile is select f.tablespace_name,f.file_name,f.file_id,f.blocks,t.block_size ,decode(t.allocation_type,'UNIFORM',t.initial_extent/t.block_size,0) uni_extent ,decode(t.allocation_type,'UNIFORM',(128+(t.initial_extent/t.block_size)),128) file_min_size from dba_data_files f, dba_tablespaces t where f.tablespace_name = t.tablespace_name and t.status = 'ONLINE' order by f.tablespace_name,f.file_id; cursor c_freespace(v_file_id in number) is select block_id, block_id+blocks max_block from dba_free_space where file_id = v_file_id order by block_id desc; /* variables to check settings/values */ dummy number; checkval varchar2(10); block_correction1 number; block_correction2 number; /* running variable to show (possible) end-of-file */ file_min_block number; /* variables to ch...

=================================== Active Session History (ASH) performed an emergency flush. This may mean that ASH is undersized. If emergency flushes are a recurring issue, you may consider increasing ASH size by setting the value of _ASH_SIZE to a sufficiently large value. Currently, ASH size is 83886080 bytes. Both ASH size and the total number of emergency flushes since instance startup can be monitored by running the following query:  select total_size,awr_flush_emergency_count from v$ash_info;  ==================================================== The ASH buffers may need to be increased if the message is logged frequently. Please review: Active Session History (ASH) Performed An Emergency Flush Messages In The Alert Log (  Doc ID 1385872.1  ) Thanks for the visit !  Any doubts or correction/suggestions, please comment below it helps me to improve in further posts.

===================REFRENCE ============== This is an informational error that you are getting this error because somebody is running a DDL (create table/index) that is specifying the maxtrans parameter.  Please refer the below note for more details:  DataPump Export/Import Generate Messages "The Value (30) Of Maxtrans Parameter Ignored" in Alert Log (  Doc ID 455021.1  )  ======================================

Cause: ====== This error is only affecting the session seeing the error. This error does not relate to any potential data corruption or any data loss. This error is only telling us that the client and server process communication was terminated due to some event. Most of the customers tried workaround of setting "_optim_peek_user_binds"=false to avoid this problem OR In Some cases Bug 18841764 Network related error like ORA-12592 or ORA-3137 or ORA-3106 may be signaled Network/TTC related error ORA-12592, ORA-3137, ORA-3106 may be signaled on SQL*Net TCP/IP transport. Usually this problem is seen with following circumstances. - Sending large size data to database server, for example, using sqlldr, expdp Solution: ========= workaround 1 ============ one time occurrences can be safely ignored as there wasn't any impact. OR If error is consistent, SQL> alter system set "_optim_peek_user_binds"=false; Restart the db instance and listener. I...

normal run slow as i used in older days =========================== exec dbms_stats.gather_schema_stats(ownname=>'USER_NAME',estimate_percent=>30,cascade=>TRUE); To speed up stats.gather_schema ============================ exec dbms_stats.gather_schema_stats('USER_NAME',options=>'GATHER',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE, degree => DBMS_STATS.DEFAULT_DEGREE); Thanks for the visit !  Any doubts or correction/suggestions, please comment below it helps me to improve in further posts.

Please set the below: ============== suggestions from oracle(Service request) as fallows=============== alter system set "_dbms_sql_security_level" = 384 scope=spfile; and bounce the instance. =================================================================== Cause of this error is setting the security level using parameter _dbms_sql_security_level. The system parameter "_dbms_sql_security_level" controls the security in DBMS_SQL in the following ways: a. Scan protection DBMS_SQL will be locked out with access denied error once an invalid cursor is detected. This scan protection security feature is on by default. To back out or disable this security feature set _dbms_sql_security_level system parameter with 0x80 (128) bit. b. Privilege checking DBMS_SQL will error out if the effective userid and roles of the caller to dbms_sql subprogram is not the same as those of the caller of the most recent parse operation. Level 0 Level 0 allows all DBM...

set markup html on spool on spool db_undo_info.html set echo on show parameter undo select * from v$database; select * from v$instance; select tablespace_name, file_id, sum(bytes)/1024/1024 a,sum(maxbytes)/1024/1024 b,autoextensible from dba_data_files where tablespace_name in (select tablespace_name from dba_tablespaces where retention like '%GUARANTEE' ) group by file_id, tablespace_name, autoextensible order by tablespace_name; select tablespace_name, file_id, bytes/1024/1024/1024,maxbytes/1024/1024/1024,autoextensible,STATUS,ONLINE_STATUS from dba_data_files where tablespace_name in (select tablespace_name from dba_tablespaces where retention like '%GUARANTEE' ) order by tablespace_name; select count(status) from dba_undo_extents where status = 'UNEXPIRED'; select count(status) from dba_undo_extents where status = 'EXPIRED'; select count(status) from dba_undo_extents where status = 'ACTIVE'; select a.inst_id, a.ksppinm ...