Posts

Showing posts from 2013

kkjcre1p: unable to spawn jobq slave process,TNS-12514,TNS-12523,TNS-12502,TNS-12520,TNS-12528

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 export partition wise from one database to another database

[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,SCOTT.PART_PART:P0…

ORA-00600: [2730], [331] after Switchover in New Standby Database

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.

ORA-16191: Primary log shipping client not logged on standby

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 primary.
6. Log into stan…

ORA-01153: an incompatible media recovery is active standby database oracle

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> ALTER DATABASE CONVERT TO PHYSICAL STANDB…

oracle database migration by cold backup

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
WHERE    A.tablespace_name = D.name
GR…

Oracle PGA Memory Usage ,input output Usage query ,background process

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','User I/O',

location of trace file

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 and GLOBAL_TXN_PROCESSES

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 number of GTXn background processes (GTX0, ... GTX9 a…

list all stored procedures: 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY'

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', 'OLAP_USER',
       '…

Latch Free wait event

"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.

last_analyzed Oracle tables

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);

finding last dml on tables in Oracle

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>';

If Oracle Queue is invalid in Oracle Streams

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);

Oracle Job Backup script

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;

job stuck in RAC , Process and locks in Oracle RAC

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' order by time_waited desc ;

stick the job to the specific instance in Oracle RAC

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........
sql>select JOB,INSTANCE from user_jobs;        JOB   INSTANCE ---------- ---------- …

Installing DBMS_JAVA package in Oracle and calling UTL_DBWS web services through Oracle database

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/oracle/product/11.2.0/dbhome_1:$JAVA_HOME/bin:$PATH; export PATH CLASSPA…

Oracle DB Health Scripts 2 - I/O,cpu_time,user_time,wait_time,PGA_memory

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
    …

memory leak

Image
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 Database and Generate scripts to compile

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;
===========================================================


query …

get ddl of dblinks ,tablespace,user creation, role granted

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 (next_extent, null, null, '…