shivenracdba

This problem occurred due to resource allocation on the db side . Here is the solution approach :- 1.check the kernel parameter and see if it configured at optimum level. 2.Check the swap space and their usage and re-config them if it is needed . 3.It may be your JOB_QUEUE_PROCESSES is too low comparison to processes parameter , increase it upto     32 and decrease the  processes parameter upto 800 for now . 4.Check the session,transaction and process parameter and tune the session and process parameter. 5.monitor the PROCESSES usage at the database level using the V$RESOURCE_LIMIT view and increase it if is    needed .

[oracle@linux251 ~]$ export ORACLE_SID=dwh02 [oracle@linux251 ~]$ sqlplus sys/sys as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 29 11:27:07 2013 Copyright (c) 1982, 2011, Oracle.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> alter tablespace TEST_TABLESPACE_2 read only; Tablespace altered. SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@linux251 ~]$ expdp  transportable=always directory=bkp dumpfile=part_part.dmp tables=SCOTT.PART_PART:P0032,SCOTT.PART_PART:P0033,SCOTT.PART_PART:P0034,SCOTT.PART_PART:P0035,SCOTT.PART_PART:P0036,SCOTT.PART_PART:P0037,SCOTT.PART_PART:P0038,SCOTT.PART_PART:P0039,SCOTT.PART_PART:P0040,SCOTT.PART_PART:P0041,SCOTT.PART_PART:P0042,

If you found errors  in the log file as:- Errors in file /data1/test/diag/diag/rdbms/art02/art02/trace/art02_ora_29254.trc  (incident=8785): ORA-00600: internal error code, arguments: [2730], [331], [1], [4], [110], [110], [512], [512], [], [], [], [] Incident details in: /data1/test/diag/diag/rdbms/art02/art02/incident/incdir_8785/art02_ora_29254_i8785.trc RCA :- This happens due to Unmatched compatibility setup on the primary and the standby.The value of compatible parameter in primary and standby is different. Solution :- 1. Change the value of the compatible parameter on standby SQL> ALTER SYSTEM SET COMPATIBLE= <same as value of primary> SCOPE=SPFILE; If you are using pfile then edit pfile and change the value of the parameter *. COMPATIBLE= <same as value of primary> 2. Shutdown and Restart the standby database in mount stage 3. Restart Managed recovery process.

The error appears in the ALRET log file AS :-  Check that the primary and standby are using a password file and remote_login_passwordfile is set to SHARED or EXCLUSIVE, and that the SYS password is same in the password files. returning error ORA-16191 ORA-16191 -Primary log shipping client not logged on standby  Solution :-  1.Defer the remote archival destination (log_archive_dest_state_n) parameter in primary. In case of RAC defer LOG_ARCHIVE_DEST_STATE_n in all nodes. 2.check parameter REMOTE_PASSWORDFILE is set to either EXCLUSIVE or SHARED on the both database . 3.Delete the old password file and create the new one for both the DBs.In case of RAC shutdown all instance of standby before recreating the password file by below command :- orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=sys entries=5 force=y ignorecase=Y  4. set the parameter SEC_CASE_SENSITIVE_LOGON=FALSE on both DBs. 5.Enable the remote archival destination (log_archive_dest_state_n) parameter in

ORA-01153: an incompatible media recovery is active SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;  2 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE * ERROR at line 1: ORA-01665: control file is not a standby control file SQL> SELECT database_role FROM v$database; DATABASE_ROLE ---------------- PRIMARY SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY; ALTER DATABASE CONVERT TO PHYSICAL STANDBY * ERROR at line 1: ORA-01126: database must be mounted in this instance and not open in any instance SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area  535662592 bytes Fixed Size                  1345376 bytes Variable Size             331352224 bytes Database Buffers          197132288 bytes Redo Buffers                5832704 bytes Database mounted. SQL> ALT

Below are the all steps which should be follow from db prospective. This is done by cold backup of Oracle Database  :- On  Existing Server :- Check the kernel parameters , memory , Oracle Software version ,Oracle_Base,Oracle_home, these should be same on new server . 1. Login to db as sys as sysdba . 2. select file_name, tablespace_name, status from dba_data_files; 3. select * from v$recover_file; 4. select name from v$controlfile; 5. show parameter control_file 6. Create pfile from spfile; 7. Size of the temp files :- SELECT   A.tablespace_name tablespace, D.mb_total,          SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,          D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free FROM     v$sort_segment A,          (          SELECT   B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total          FROM     v$tablespace B, v$tempfile C          WHERE    B.ts#= C.ts#          GROUP BY B.name, C.block_size          ) D WHER

SELECT   mymodule "Module", SUM (cpu_time) "CPU Time", SUM (wait_time) "Wait Time",          SUM (cpu_time) + SUM (wait_time) "Total Time"     FROM (SELECT a.module mymodule,                  (CASE (session_state)                      WHEN 'ON CPU'                         THEN wait_time / 100                   END                  ) cpu_time,                  (CASE (session_state)                      WHEN 'WAITING'                         THEN time_waited / 100                   END                  ) wait_time             FROM dba_hist_active_sess_history a, dba_hist_snapshot b            WHERE b.end_interval_time > sysdate-10              AND a.snap_id = b.snap_id              AND a.user_id NOT IN (0, 5)              AND a.instance_number = b.instance_number) GROUP BY mymodule   HAVING SUM (cpu_time) + SUM (wait_time) > 0 ORDER BY 2 DESC; select                  decode(n.wait_class,'User I/O

exec DBMS_MONITOR.SESSION_TRACE_ENABLE(waits => TRUE, binds => TRUE); select value from v$diag_info where name='Default Trace File'; select u_dump.value || '/' || instance.value || '_ora_' || v$process.spid || nvl2(v$process.traceid, '_' || v$process.traceid, null ) || '.trc' "Trace File" from V$PARAMETER u_dump cross join V$PARAMETER instance cross join V$PROCESS join V$SESSION on v$process.addr = V$SESSION.paddr where u_dump.name = 'user_dump_dest' and instance.name = 'instance_name' and V$SESSION.audsid=sys_context('userenv','sessionid'); exec DBMS_MONITOR.SESSION_TRACE_DISABLE;

Starting background process GTX4 Tue Jan 01 15:51:12 2012 GTX4 started with pid=64, OS id=11122 Tue Jan 01 15:51:43 2012 Auto-tuning: Starting background process GTX5 and Auto-tuning: Shutting down background process GTXi Like messages in alert log in RAC database This event is recorded in RAC environment only, which is normal.    Its normal for the database to automatically tune the number of these processes based on the workload of XA global transactions. If any application which using XA transaction and connecting to oracle RAC database, is it required. This normal behavior and you do not have to worry about this error messages.  If you are sure that you are not using XA and want to stop these messages. You can disable the GTXn background processes, then you must set GLOBAL_TXN_PROCESSES to 0 in your parameter file. Setting this parameter to 0 will disable the XA support on an Oracle RAC database. GLOBAL_TXN_PROCESSES specifies the initial numb

Search package name wise :- select object_name, object_type , status      from dba_objects      where object_type in ( 'PROCEDURE', 'FUNCTION','PACKAGE', 'PACKAGE BODY' )      and object_name in ('DBMS_JAVA' ,'UTL_DBWS')  ; OR  generic type search :- select object_name, object_type , status      from user_objects      where object_type in ( 'PROCEDURE', 'FUNCTION','PACKAGE', 'PACKAGE BODY' );     SELECT DISTINCT Owner, Object_Type, Object_Name FROM DBA_Objects_AE      WHERE Owner IN (        'SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP',        'LOGSTDBY_ADMINISTRATOR', 'ORDSYS',        'ORDPLUGINS', 'OEM_MONITOR', 'WKSYS', 'WKPROXY',        'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS',        'WMSYS', 'OLAPDBA', 'OLAPSVR', 'OLA

"Latch Free" wait event in this database, to solve this wait event  re-size these parameters using below query. alter system set open_cursors=600 scope=spfile; alter system set session_cached_cursors=250 scope=spfile; alter system set cursor_sharing='FORCE' scope=spfile;                                 and increase the SGA and PGA accordingly . We need to bounce DB for these parameters to implement.

select table_name, last_analyzed, num_rows from user_tables where last_analyzed < sysdate - 1 order by 3; select trunc(last_analyzed) "LAST_ANALYZED", count(*), decode(db_unique_name,null,name,db_unique_name) DBNAME, to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') "RUNTIME" from dba_tables, v$database where owner='<username>' and last_analyzed >= sysdate-20 group by trunc(last_analyzed), decode(db_unique_name,null,name,db_unique_name) order by trunc(last_analyzed);

There is a way to find the last dml time :- Alter Table Tabname Monitoring; Once this is there, Select * from User_Tab_Modifications; If you want all tables to be Checked, Run the script like this Spool Monitor.sql Select 'Alter table '||Table_Name||' Monitoring ;' From User_Tables; Spool Off; @Monitor.sql select to_char(scn_to_timestamp(max(ora_rowscn))) from <table_name>;  select * from all_tab_modifications where table_name = '<table_name>';

Use this: conn / as sysdba exec dbms_aqadm_syscalls.kwqa_3gl_validatequeue('<user_name>','RM_QUEUE_ID11'); commit; connect <user_name>/&PASSWORD exec DBMS_AQADM.DROP_QUEUE_TABLE (queue_table => '<queue_name>', force => true);

select 'declare lv_job number(10); begin ' ||        'sys.dbms_job.submit(job => lv_job, what => ''' || what ||        ''', next_date => (sysdate + 1000), interval => ''' || interval ||        '''); commit; end;'   from user_jobs;

1. Check for blocking sessions in DB. 2. Check for wait events in the system at time of job execution. 3. Execution plan of sql queries. 4. Stats/Fragmentation in the tables.  select EVENT,TOTAL_WAITS,TIME_WAITED from v$session_event where sid=75 order by time_waited desc;  select count(*),event from v$active_session_history where session_id=158 group by event order by count(*) desc; select PROGRAM,sid,status,last_call_et/(60*60) ,sql_id from gv$session where last_call_et/(60*60) > 3 and status='ACTIVE' AND PROGRAM LIKE '%J00%'; select * from dba_hist_sqltext where sql_id like 'a0du3u2yhkgnd'; select * from gv$sqltext where sql_id like 'a0du3u2yhkgnd'; select * from gv$active_session_history where session_id = 72 order by sample_id desc; select * from dba_tab_statistics  where owner like '<user_name>'AND table_name like '<table_name>'; select * from gv$system_event where wait_class !='Idle&#

First method ( and easy one too ) :- note:-  must perform this activity on each instance one by one. ......stop jobs which is assigning to the specific instance..... $ export ORACLE_SID=SID connect database using job owner .... $ sqlplus username/password list of jobs and its instance....  sql> select JOB,INSTANCE from user_jobs;        JOB   INSTANCE ---------- ----------       2510          0       2951          0       2992          0       3091          0       3111          0       1570          0       1567          0       1568          0       1569          0       1571          0       1572          0 stick the job to the specific instance..... Syntax DBMS_JOB.INSTANCE (    job        IN BINARY_INTEGER,    instance   IN BINARY_INTEGER,    force      IN BOOLEAN DEFAULT FALSE); sql>execute dbms_job.instance(job=>1571, instance=>2,force=>true); verify from the instance number........

Installing DBMS_JAVA package in Oracle following  files need to be run:-  sql> select comp_name, version, status from dba_registry where comp_name like '%JAVA%'; @?/javavm/install/initjvm.sql; @?/xdk/admin/initxml.sql; @?/xdk/admin/xmlja.sql; @?/rdbms/admin/catjava.sql; @?/rdbms/admin/catexf.sql; and verify your java installation in Oracle Database  sql> select comp_name, version, status from dba_registry where comp_name like '%JAVA%'; sql> select dbms_java.longname(object_name), status, object_type from all_objects where       object_type = 'JAVA CLASS'; ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ calling  UTL_DBWS  web services through Oracle Database :-  set the follwing parameters in .bash_profile of oracle user  export JAVA_HOME=$ORACLE_HOME/jdk ORACLE_BASE=/opt/oracle/product; export ORACLE_BASE ORACLE_HOME=/opt/oracle/product/11.2.0/dbhome_1; export ORACLE_HOME PATH=$PATH:/opt/o

SELECT   mymodule "Module", SUM (cpu_time) "CPU Time", SUM (wait_time) "Wait Time",          SUM (cpu_time) + SUM (wait_time) "Total Time"     FROM (SELECT a.module mymodule,                  (CASE (session_state)                      WHEN 'ON CPU'                         THEN wait_time / 100                   END                  ) cpu_time,                  (CASE (session_state)                      WHEN 'WAITING'                         THEN time_waited / 100                   END                  ) wait_time             FROM dba_hist_active_sess_history a, dba_hist_snapshot b            WHERE b.end_interval_time > sysdate-10              AND a.snap_id = b.snap_id              AND a.user_id NOT IN (0, 5)              AND a.instance_number = b.instance_number) GROUP BY mymodule   HAVING SUM (cpu_time) + SUM (wait_time) > 0 ORDER BY 2 DESC; ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Recently i found a interesting situation where a LQA server has only 3 GB of RAM and  5 GB of swap . I assigned the 20GB of memory to the Oracle and re-start it  and without any problem it started smoothly . I tried to found out the memory leakage on this server but in vain , and the most interesting part is when all the users log on this server , this worked fine without any problem .Again i re-assigned the memory to it  according to the RAM and requirement . :)

Find out invalid objects in Oracle and compile them :- select owner,count(*),object_type from dba_objects where status='INVALID' group by owner,object_type; select owner,object_name,object_type from dba_objects where status='INVALID' order by owner; ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Generate Oracle scripts to compile  procedure , function , package , package body,trigger, view :- select decode( OBJECT_TYPE, 'PACKAGE BODY', 'alter package ' || OWNER||'.'||OBJECT_NAME || ' compile body;', 'alter ' || OBJECT_TYPE || ' ' || OWNER||'.'||OBJECT_NAME || ' compile;' ) from dba_objects a where STATUS = 'INVALID' and OBJECT_TYPE in ( 'PACKAGE BODY', 'PACKAGE', 'FUNCTION', 'PROCEDURE', 'TRIGGER', 'VIEW' ) and owner='SYS' order by OBJECT_TYPE, OBJECT_NAME; ========================================

Get ddl of database links in Oracle Database :- SELECT 'CREATE '||DECODE(U.NAME,'PUBLIC','public ')||'DATABASE LINK '||CHR(10) ||DECODE(U.NAME,'PUBLIC',Null, 'SYS','',U.NAME||'.')|| L.NAME||chr(10) ||'CONNECT TO ' || L.USERID || ' IDENTIFIED BY "'||L.PASSWORD||'" USING '''||L.HOST||'''' ||chr(10)||';' TEXT FROM SYS.LINK$ L, SYS.USER$ U WHERE L.OWNER# = U.USER#; ================================================================== Get ddl of tablespace creation in Oracle Database :-   select 'create tablespace ' || df.tablespace_name || chr(10)  || ' datafile ''' || df.file_name || ''' size ' || df.bytes   || decode(autoextensible,'N',null, chr(10) || ' autoextend on maxsize '   || maxbytes)   || chr(10)   || 'default storage ( initial ' || initial_extent   || decode