Hello friends,
Below are the few commands and scripts used for doing some basic checks, when user complains about the slowness/no connectivity/unable to connect to the database. Please note, these are just few of the aspects which you can look into to check the performance/issues in the database. There can be few/many aspects which you need to look into to check/resolve the issues.
*********************************************************************************
Check DB up time, to see whether there was a database bounce recently without your knowledge:
col host_name for a20
select host_name,
instance_name,
to_char(startup_time, 'dd-mm-yyyy hh24:mi:ss') startup_time,
floor(sysdate-startup_time) days
from sys.v_$instance;
*********************************************************************************
Check the listener status
lsnrctl status LISTENER_NAME
*********************************************************************************
Check if the mount points are full
df -h
*********************************************************************************
Check the alert log for errors:
SQL> show parameter background_dump_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest string /oracle/admin/cpowerp2/bdump
open the log and search for errors
*********************************************************************************
To kill a session:
alter system kill session '88,20708' immediate;
*********************************************************************************
The Linux top command is an interactive utility that displays a character-based screen of all processes running on the current system. The screen updates itself every few seconds, and you can sort the screen contents by characteristics like CPU Usage or Memory Use.
top
It will show information like tasks, memory, cpu and swap. Press 'ctrl+c' to quit window.
Press (Shift+O) to Sort field via field letter, for example press 'a' letter to sort process with PID (Process ID). Press 'ctrl+c' to quit exit the window.
Use top command with 'u' option will display specific User process details.
top -u oracle
Press 'z' option in running top command will display running process in color which may help you to identified running process easily.
Press 'c' option in running top command, it will display absolute path of running process.
By default screen refresh interval is 3.0 seconds, same can be change pressing 'd' option in running top command and change it as desired.
You can kill a process after finding PID of process by pressing 'k' option in running top command without exiting from top window.
Press (Shift+P) to sort processes as per CPU utilization.
You can use 'r' option to change the priority of the process.
Press (Shift+W) to save the running top command results under /root/.toprc.
Press 'h' option to obtain the top command help.
*********************************************************************************
To check which mode, role, the database is at present.
select name,open_mode, database_role from v$database;
*********************************************************************************
Check Tablespace details
select a.tablespace_name,a.allocated, b.freespace,round(b.freespace/a.allocated*100) "% free" from
(select tablespace_name ,sum(bytes)/1024/1024/1024 allocated from dba_data_files group by tablespace_name) a,
(select tablespace_name,sum(bytes)/1024/1024/1024 freespace from dba_free_space group by tablespace_name) b
where a.tablespace_name=b.tablespace_name order by 4;
*********************************************************************************
Check the archive location, if the archive destination is full(please note, the check is different when you have FRA as archive destination)
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /archive/oradata/cpowerp2/archive
Oldest online log sequence 346812
Next log sequence to archive 346814
Current log sequence 346814
exit the sqlplus environment, on the OS level, check using below command:
[oracle@GCPLINCIN1CPW01 ~]$ df -h /archive/oradata/cpowerp2/archive
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/emcvg4-archive 92G 42G 46G 48% /archive
If your archive destination is full, the database will not allow connections to the database and the users will get the "ORA-00257: archiver error. Connect internal only, until freed" message.
clear the archive destination by taking archive log backups and switch logfile.
The "ALTER SYSTEM SWITCH LOGFILE" does not wait for the archiver process (ARCH) to complete writing the online redo log to the archivelog log filesystem:
It issues database checkpoint
It immediately starts writing to the next redo log
In the background, the "switch logfile" command tells the ARCH background process to copy the "old" redo log file to the redo log filesystem.
*********************************************************************************
Check the datafile size and details.
col file_name for a25
set lines 200
select substr (df.name, 1, 40) file_name, df.bytes / 1024 / 1024 "allocated size(mb)",
((df.bytes / 1024 / 1024) - nvl (sum (dfs.bytes) / 1024 / 1024, 0)) "used size (mb)",
nvl (sum (dfs.bytes) / 1024 / 1024, 0) "free size(mb)"
from v$datafile df, dba_free_space dfs
where df.file# = dfs.file_id(+)
group by dfs.file_id, df.name, df.file#, df.bytes
order by file_name;
Check the datafiles of a particular tablespace
select sum(bytes)/1024/1024/1024,file_name from dba_data_files where tablespace_name='SYSAUX' group by file_name;
*********************************************************************************
To check the sql for a particular SID:
select sid,username,machine,program,logon_time from v$session where sid=496;
--------------------------------
to find the query depends on sid :
--------------------------------
select a.sql_text
from v$sqltext a,
v$session b
where a.address = b.sql_address
and a.hash_value = b.sql_hash_value
and b.sid = &1 --> from above sql
order by a.piece;
*********************************************************************************
To check the session counts and the username connected to the database:
select username,status,count(*) from v$session group by username,status;
*********************************************************************************
Long running queries:
select * from v$session_longops where time_remaining>0;
select sid, serial#, opname, target, sofar, totalwork, units,
to_char(start_time,'dd/mon/yyyy hh24:mi:ss') start_time,
to_char(last_update_time,'dd/mon/yyyy hh24:mi:ss') last_update_time,
time_remaining, elapsed_seconds, message, username
from v$session_longops
where time_remaining != 0;
select s.username,s.type,s.sid,s.serial#,s.last_call_et seconds_running,q.sql_text from v$session s
join v$sql q
on s.sql_id = q.sql_id
where status='ACTIVE'
and type <> 'BACKGROUND'
and last_call_et> 60
order by sid,serial#;
*********************************************************************************
Check the lock:
select * from v$lock where block > 0;
select * from dba_2pc_pending;
select * from dba_blockers;
select * from v$resource_limit;
*********************************************************************************
select event,count(*) from v$session_event group by event;
*********************************************************************************
select local_tran_id,state from dba_2pc_pending where state='prepared';
*********************************************************************************
alter system flush buffer_cache;
alter system flush shared_pool;
*********************************************************************************
