Anuroop Challa

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

Scripts to check DBMS jobs running ====================================== select job_name, session_id, running_instance, elapsed_time, cpu_used from dba_scheduler_running_jobs; if you want to stop job, you can stop by using  below command (or) kill by using SID =============================================================== exec DBMS_SCHEDULER.stop_JOB (job_name => '<JOB_NAME>');

archive log from db if you want to delete older item with out any backup you can fallow below one of the procedure. ============SYSDATE===in below is 7day u can change per yourt need================= rman connect target / crosscheck archivelog all; delete noprompt archivelog until time 'sysdate-7'; crosscheck archivelog all; delete noprompt expired archivelog all; LIST ARCHIVELOG ALL; crosscheck archivelog all; exit ======Using until time and date===in below you can change per your need================= rman connect target / crosscheck archivelog all; run{ DELETE archivelog until time "to_date('2017-04-11:08:00:00','YYYY-MM-DD:hh24:mi:ss')"; } crosscheck archivelog all; delete noprompt expired archivelog all; LIST ARCHIVELOG ALL; crosscheck archivelog all; exit

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;

How to check actual undo size and needed undo size in oracle =============================================== select d.undo_size/(1024*1024) "ACTUAL UNDO SIZE (MEGS)",   (to_number(e.value) * to_number(f.value) * g.undo_block_per_sec) /   (1024*1024) "NEEDED UNDO SIZE (MEGS)"   from (select sum(a.bytes) undo_size   from v$datafile a,v$tablespace b,dba_tablespaces c   where c.contents = 'UNDO'   and c.status = 'ONLINE'   and b.name = c.tablespace_name   and a.ts# = b.ts#) d,   v$parameter e,   v$parameter f,   (select max(undoblks/((end_time-begin_time)*3600*24))   undo_block_per_sec   from v$undostat) g   where e.name = 'undo_retention'   and f.name = 'db_block_size';

Data-file size check and get information for all databases on the server :- ============================================== SELECT      STATS.Volume_Mount_Point AS 'Drive',             DB_NAME(STATS.Database_ID) AS 'Database Name',             FILES.[Name] AS 'File Name',             FILES.[Size] / 128 AS 'File Size',             ((STATS.Total_Bytes / 1024) / 1024)             AS 'Drive Capacity (MB)',             ((STATS.Available_Bytes / 1024) / 1024)             AS 'Drive Free Space (MB)' FROM  sys.master_files FILES CROSS APPLY             sys.dm_os_volume_stats             (FILES.database_id, FILES.file_id) STATS

MS-SQL server Uptime or last restart check script ======================================== DECLARE @server_start_time DATETIME, @seconds_diff INT, @years_online INT, @days_online INT, @hours_online INT, @minutes_online INT, @seconds_online INT ; SELECT @server_start_time = login_time FROM master.sys.sysprocesses WHERE spid = 1 ; SELECT @seconds_diff = DATEDIFF(SECOND, @server_start_time, GETDATE()), @years_online = @seconds_diff / 31536000, @seconds_diff = @seconds_diff % 31536000, @days_online = @seconds_diff / 86400, @seconds_diff = @seconds_diff % 86400, @hours_online = @seconds_diff / 3600, @seconds_diff = @seconds_diff % 3600, @minutes_online = @seconds_diff / 60, @seconds_online = @seconds_diff % 60 ; SELECT @server_start_time AS server_start_time, @years_online AS years_online, @days_online AS days_online, @hours_online AS hours_online, @minutes_online AS minutes_online, @seconds_online AS seconds_online ;

How to check session deatils with blocked in MS_SQL ======================================= ====================================================== Full session deatils with blocked ========================================================= USE Master GO EXEC sp_who2 GO ----------------------------------or-------------------------- SELECT spid, kpid, blocked, d.name, open_tran, status, hostname, cmd, login_time, loginame, net_library FROM sys.sysprocesses p INNER JOIN sys.databases d  on p.dbid=d.database_id