Useful OS commands for DBAs
1. RAM SIZE
2. Find
3. SAR
4. vmstat
5. iostat
6. swap
7. uptime
8. Top CPU users
9. Top memory users
10. Check disk space
11. TOP Command Examples
12. Find number of CPU
13. Trace SID
14. System logfile
15. Send mail
16. Tar Command Examples
17. ZIP Command Examples
18. GZIP Command Examples
1. RAM SIZE
/usr/sbin/prtconf | grep Mem -- For Solaris
dmesg -- For HP-UX
lsdev -C | grep mem -- For AIX
lsattr -El mem0 ( output from previous command) -- For AIX
free -- For Linux
uerf -r 300 | grep -I mem -- DEC-UNIX
2. Find
find / -print | grep -i z.sql -- In root mount point searching for file "z.sql"
find /usr/lib -name "*raj*" /*finding can’t remember the exact name of*/
find . -size +20000 /*find files bigger than 20000 blocks, about 10MB )*/
find . -size +20000 -mtime -1 -ls
find . -mtime -1 /*Find files or directories modified in last day*/
find /tmp -atime +45 /*find files not accessed (read) in 45 days*/
find . -name "*.c" |xargs grep -l libsocket /*look for a particular word in all your .c files*/
3. SAR (System Activity Report)
sar -u 1 10 /*if I run sar 1 10 it will report to the screen, 10 instances every one second*/
4. vmstat
vmstat 1 5 /*vmstat [interval] [count]*/
5. iostat
iostat -x 1 5 /*iostat [interval] [count]*/
6. swap space
/usr/sbin/swap -s /*solaris*/
7. uptime
uptime /*check system load average: an uptime more than double the number of CPUs is getting a bit busy*/
8. Top CPU users
/usr/ucb/ps uax |head /*solaris*/
9. Top memory users
/usr/ucb/ps vax |head /*solaris*/
10. Check disk space
df -h or df -k or df -g /*shows all mounted filesystems*/
df -h /u01 /*show space for specified mountpoing /u01 */
du -sh /home/oracle /*To find size of a directory */
du -sk
[root@rac1 ~]# du -k /u01 | sort -r -n -k 1 | head -20 | awk ' { print $2 "....."$1/1024 "MB" } '
/u01.....48585.8MB
/u01/app.....40167.6MB
/u01/app/oracle.....21521.9MB
/u01/app/oracle/product.....20262.3MB
/u01/app/grid/product/12.2.....17900.2MB
/u01/app/grid/product.....17900.2MB
/u01/app/grid.....17900.2MB
/u01/app/oracle/product/12.2.0/dbhome_1.....13300.9MB
/u01/app/oracle/product/12.2.0.....13300.9MB
/u01/app/oracle/product/12.2/db_1.....6961.39MB
/u01/app/oracle/product/12.2.....6961.39MB
/u01/stage/30920127.....5662.84MB
/u01/stage.....5662.84MB
/u01/app/oracle/product/12.2.0/dbhome_1/dbs.....3836.5MB
/u01/app/oracle/product/12.2/db_1/database.....3667.68MB
/u01/app/oracle/product/12.2/db_1/database/stage.....3665.52MB
/u01/app/grid/product/12.2/.patch_storage.....3249.18MB
/u01/stage/30920127/30869447.....3093.78MB
/u01/stage/30920127/30869447/files.....3093.64MB
/u01/app/oracle/product/12.2/db_1/database/stage/Components.....3053.15MB
[root@rac1 ~]#
11. TOP Command Examples
# top /*it will show information like tasks, memory, cpu and swap. Press ‘q‘ to quit window*/
# top -u oracle /*will display specific User process details*/
# top ---> then press z /*will display running process in color which may help you to identified running process easily*/
# top ---> then press c /*will display absolute path of running process*/
# top ---> then press d /*Default screen refresh interval is 3.0 seconds, can change pressing ‘d‘ option*/
# top ---> then press k /*You can kill PID in running top command without exiting from top window*/
# top ---> then press Shift+P /*to sort processes as per CPU utilization*/
# top ---> then press r /*To change the priority of the process (Renice a Process)*/
# top ---> then press Shift+W /*To save top command results*/
# top ---> then press h /*Getting Top Command Help*/
# top ---> then press Shift+O then press "a" /*press ‘a‘ letter to sort process with PID (Process ID)/*
12. Find number of CPU
psrinfo | wc -l /*Solaris --suppose virtual CPU*/
cat /proc/cpuinfo|grep processor|wc –l /*Linux*/
psrinfo -v|grep "Status of processor"|wc –l /*Solaris*/
lsdev -C|grep Process|wc –l /*AIX*/
ioscan -C processor | grep processor | wc -l /*HP-UX*/
13. Trace SID
truss -p /*Solaris and AIX*/
strace -p /*Linux*/
trace -p /*Tru 64*/
14. System logfile
Linux: /var/log/messages
Solaris, HP Tru64: /var/adm/messages
HP-UX: /var/adm/syslog/syslog.log
AIX: /bin/errpt
15. Send mail with attachments
uuencode 1.txt 2.txt | mailx -s "subject of mail" samiappsdba@gmail.com
OR
echo Body of the mail |mailx -s Attachment -a file.txt samiappsdba@gmail.com
16. Tar Command Examples
TAR
tar -cvf /u01/zzz.tar /home/zzz/ /*will create tar file for /home/zzz directory, it will keep original files as it is*/
c – Creates a new .tar archive file.
v – Verbosely show the .tar file progress.
f – File name type of the archive file.
UNTAR
tar -xvf /u01/zzz.tar -C /home/oracle /*Untar files in specified Directory*/
TAR Compress
Create tar.gz Archive File use option z
tar cvzf /u01/zzz.tar.gz /home/zzz /*will create tar file for /home/zzz directory*/
TAR UnCompress
tar -xvf /u01/zzz.tar.gz -C /home/oracle /*Untar files in specified Directory*/
TAR Compress tar.bz2
Create tar.bz2 Archive File
The bz2 feature compress and create archive file less than the size of the gzip.
The bz2 compression takes more time to compress and decompress files as compared to gzip which takes less time.
To create highly compressed tar file we use option as j.
tar cvfj /u01/zzz.tar.bz2 /home/zzz/ -- /*will create tar file for /home/zzz directory*/
TAR UnCompress tar.bz2
tar -xvf /u01/zzz.tar.bz2 -C /home/oracle /*Untar files in specified Directory*/
List Content of tar Archive File
tar -tvf /u01/zzz.tar
tar -tvf /u01/zzz.tar.gz
tar -tvf /u01/zzz.tar.bz2
Untar Single file from tar File
tar -xvf /u01/zzz.tar init.ora
tar -zxvf /u01/zzz.tar.gz init.ora
tar -jxvf /u01/zzz.tar.gz.bz2 init.ora
or
tar --extract --file=/u01/zzz.tar init.ora
tar --extract --file=/u01/zzz.tar.gz init.ora
tar --extract --file=/u01/zzz.tar.gz.bz2 init.ora
Untar Multiple files from tar, tar.gz and tar.bz2 File
tar -xvf /u01/zzz.tar "file 1" "file 2"
tar -zxvf /u01/zzz.tar.gz "file 1" "file 2"
tar -jxvf /u01/zzz.tar.bz2 "file 1" "file 2"
Extract Group of Files using Wildcard
tar -xvf /u01/zzz.tar --wildcards '*.php'
tar -zxvf /u01/zzz.tar.gz --wildcards '*.php'
tar -jxvf /u01/zzz.tar.bz2 --wildcards '*.php'
17. ZIP Command Examples
Zipping individual files
zip abc.zip file1 file2 file3
Zip a file or folder
zip archivename.zip file1 file2 folder1
Zip a folder (including all subdirectories)
zip -r folder1*
Zip a folder to a different location
zip -r /tmp/abc.zip /home/oracle/folder1
List all the files stored in a zip file
unzip -l abc.zip
Unzip
unzip abc.zip
To unzip to a specific directory use -d option
unzip abc.zip -d /tmp
Unzip specific file from an archive
unzip abc.zip test.sh
18. GZIP Command Examples
zipping a file with gzip command
gzip file1 file2 file3
oracle@rac1:~/test/test$ ls
dump.doc.gz file1.gz test.sh.gz <-- it will zip individually
oracle@rac1:~/test/test$
Note: Above gzip command will create files dump.doc.gz, file1.gz and test.sh.gz respectively by replacing original files.
To avoid deleting of original files, use -k(keep) option with zgip command
gzip -k dump.doc file1 test.sh
oracle@rac1:~/test/test$ ls
dump.doc dump.doc.gz file1 file1.gz test.sh test.sh.gz
oracle@rac1:~/test/test$
How to zip group of files to a gzip single compressed file
gzip don't know how to add files to a single comprss file and it just compress each file individually by default.
oracle@rac1:~/test/test$ cat dump.doc file1 test.sh | gzip > all.gz
oracle@rac1:~/test/test$ ls
all.gz dump.doc file1 test.sh
oracle@rac1:~/test/test$
Unzip the gzip file
gunzip all.gz
oracle@rac1:~/test/test$ ls
all.gz dump.doc file1 test.sh
oracle@rac1:~/test/test$ gunzip all.gz
oracle@rac1:~/test/test$ ls
all dump.doc file1 test.sh <-- gzip file automatically deleted once unzipped
Few Useful Scripts for Oracle DBAs
1. Database links
2. clone user script
3. How find your own session id
4. Datapump scripts
5. Find how frequently of Oracle log switches
6. Tablespace scripts
7. Undo Used/Free Space
8. Temp details
9. Identify OS process ID based on database SID
10. Identify database SID based on OS Process ID
11. Find out Index Name and Column Name for Table
12. Find DB Size
13. SQL Query to Get Hostname, IP Address and Terminal from Oracle Database
14. Archive Log Generation Report Thread Wise Daily
15. Redo generation per day
16. Redo Generated per Hour
17. Hourly archivelog switches
18. ASH Report for one service
19. Verify Archive Log GAP
20. Index Rebuild with DB shutdown
21. Index Rebuild with Index Partition and Index Sub Partition
22. Fragmented tables top 10
23. Monitor FRA
24. Delete archivelogs
25. DG LAG status
26. Monitor Archive log GAP
DB Link Scripts
/*Get the details about an existing dblink
Note: Please test this in test machine before use in production
*/
dblink_details.sql
set verify off
set pages 1200
set lines 132
column owner format a20
column db_link format a30
column username format a30
column host format a20
accept link_name char Prompt 'Enter Database Link Name : '
select owner, db_link, username, host , to_char(created,'MM/DD/YYYY HH24:MI:SS') creation_date
from dba_db_links
where upper(db_link) like '%'||upper('&&link_name')||'%'
order by owner
;
exit;
clone user script
clone_user.sql
set pages 0 feed off veri off lines 500
accept oldname prompt "Enter user to model new user to: "
accept newname prompt "Enter new user name: "
accept psw prompt "Enter new user's password: "
-- Create user...
select 'create user &&newname identified by &&psw'||
' default tablespace '||default_tablespace||
' temporary tablespace '||temporary_tablespace||' profile '||
profile||';'
from sys.dba_users
where username = upper('&&oldname');
-- Grant Roles...
select 'grant '||granted_role||' to &&newname'||
decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION')||';'
from sys.dba_role_privs
where grantee = upper('&&oldname');
-- Grant System Privs...
select 'grant '||privilege||' to &&newname'||
decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION')||';'
from sys.dba_sys_privs
where grantee = upper('&&oldname');
-- Grant Table Privs...
select 'grant '||privilege||' on '||owner||'.'||table_name||' to &&newname;'
from sys.dba_tab_privs
where grantee = upper('&&oldname');
-- Grant Column Privs...
select 'grant '||privilege||' on '||owner||'.'||table_name||
'('||column_name||') to &&newname;'
from sys.dba_col_privs
where grantee = upper('&&oldname');
-- Tablespace Quotas...
select 'alter user &&newname quota '||
decode(max_bytes, -1, 'UNLIMITED', ceil(max_bytes / 1024 / 1024) || 'M') ||
' on '||tablespace_name||';'
from sys.dba_ts_quotas
where username = upper('&&oldname');
-- Set Default Role...
select 'alter user &&newname default role '|| granted_role ||';'
from sys.dba_role_privs
where grantee = upper('&&oldname')
and default_role = 'YES';
set pages 500 feed on veri on lines 500
Find your own session id
SQL> SELECT sid FROM v$mystat WHERE rownum = 1;
SQL> SELECT sid FROM V$SESSION WHERE audsid = userenv('sessionid');
SQL> SELECT sid FROM V$SESSION WHERE audsid = SYS_CONTEXT('userenv','sessionid');
select sys_context('USERENV','SID') from dual;
select sid from v$mystat where rownum <=1;
select to_number(substr(dbms_session.unique_session_id,1,4),'XXXX') mysid from dual;
select distinct sid from v$mystat;
For RAC Database with Instance ID
select username,inst_id, sid, serial# FROM gV$SESSION WHERE audsid = userenv('sessionid');
Datapump
DBA_DATAPUMP_JOBS
This view will show the active Data Pump jobs, their state, degree of parallelism, and the number of sessions attached.
col OWNER_NAME for a12
col JOB_NAME for a20
col OPERATION for a10
col JOB_MODE for a10
select * from dba_datapump_jobs;
DBA_DATAPUMP_SESSIONS
This view give gives the SADDR that assist in determining why a Data Pump session may be having problems. Join to the V$SESSION view for further information.
SELECT * FROM DBA_DATAPUMP_SESSIONS;
V$SESSION_LONGOPS
This view helps determine how well a Data Pump export is doing. Basically gives you a progress indicator through the MESSAGE column.
col USERNAME for a10
col opname for a20
col target_desc a20
col target_desc for a20
col MESSAGE for a50
select username,opname,target_desc,sofar,totalwork,message from V$SESSION_LONGOPS;
Frequency of Oracle log switches
Get details about how frequently redo log switching
COL DAY FORMAT a15;
COL HOUR FORMAT a4;
COL TOTAL FORMAT 999;
SELECT TO_CHAR(FIRST_TIME,’YYYY-MM-DD’) DAY,
TO_CHAR(FIRST_TIME,’HH24') HOUR,
COUNT(*) TOTAL
FROM V$LOG_HISTORY
GROUP BY TO_CHAR(FIRST_TIME,’YYYY-MM-DD’),TO_CHAR(FIRST_TIME,’HH24')
ORDER BY TO_CHAR(FIRST_TIME,’YYYY-MM-DD’),TO_CHAR(FIRST_TIME,’HH24')
ASC;
Tablespace
-- All Tablespace in database with each datafile size
set line 1000
set pages 5000
col tablespace_name for a30
col file_name for a80
col free_space for 9999999
compute sum of total_space on report
compute sum of free_space on report
compute sum of MAX_SPACE on report
break on tablespace_name on report nodup
select c.tablespace_name,a.autoextensible,a.file_name,a.total_space,b.free_space, round(b.free_space/a.total_space *100,2) "Free%",a.max_space from (select file_id,file_name,sum(bytes)/1024/1024 total_space,sum(MAXBYTES)/1024/1024/1024 max_space,autoextensible from dba_data_files group by file_id,file_name,autoextensible) a,(select file_id,nvl(sum(bytes)/1024/1024,0) free_space from dba_free_space group by file_id) b, (select tablespace_name,file_id from dba_data_files) c where a.file_id=b.file_id(+) and a.file_id=c.file_id order by tablespace_name;
-- Tablespace Verification
set line 1000
col tablespace_name for a30
col file_name for a80
col free_space for 9999999
compute sum of total_space on report
compute sum of free_space on report
compute sum of max_space on report
break on tablespace_name on report nodup
select c.tablespace_name,a.autoextensible,a.file_name,a.total_space,b.free_space,
round(b.free_space/a.total_space *100,2) "Free%",a.max_space from
(select file_id,file_name,nvl(sum(bytes)/1024/1024,0) total_space,nvl(sum(MAXBYTES)/1024/1024/1024,0) max_space,autoextensible from dba_data_files group by file_id,file_name,autoextensible) a,
(select file_id,nvl(sum(bytes)/1024/1024/1024,0) free_space from dba_free_space group by file_id) b,
(select tablespace_name,file_id from dba_data_files where tablespace_name='&DEFAULT_TABLESPACE' ) c
where a.file_id=b.file_id(+) and a.file_id=c.file_id order by tablespace_name;
-- TBS with Allocation Total/Physical
set linesize 132 tab off trimspool on
set pagesize 105
set pause off
set echo off
set feedb on
column "TOTAL ALLOC (MB)" format 9,999,990.00
column "TOTAL PHYS ALLOC (MB)" format 9,999,990.00
column "USED (MB)" format 9,999,990.00
column "FREE (MB)" format 9,999,990.00
column "% USED" format 990.00
select a.tablespace_name,
a.bytes_alloc/(1024*1024) "TOTAL ALLOC (MB)",
a.physical_bytes/(1024*1024) "TOTAL PHYS ALLOC (MB)",
nvl(b.tot_used,0)/(1024*1024) "USED (MB)",
(nvl(b.tot_used,0)/a.bytes_alloc)*100 "% USED"
from ( select tablespace_name,
sum(bytes) physical_bytes,
sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)) bytes_alloc
from dba_data_files
group by tablespace_name ) a,
( select tablespace_name, sum(bytes) tot_used
from dba_segments
group by tablespace_name ) b
where a.tablespace_name = b.tablespace_name (+)
--and (nvl(b.tot_used,0)/a.bytes_alloc)*100 > 10
--and a.tablespace_name in ('NONEED')
--and a.tablespace_name not like 'UNDO%'
and a.tablespace_name='&tbs'
--- like 'Noneed%'
order by 1
--order by 5
/
-- check free space of particular tablespace
-- save the below script in file "free_space.sql" and then execute
set lines 132
set pages 100 verify off
clear break
clear compute
ttitle center 'Space Usage on Tablespaces' skip 1 center '*****************************************' skip 2
accept tablespace_name char prompt 'Enter Tablespace Name :'
select a.TABLESPACE_NAME,
round(a.bytes_used/(1024*1024),2) TOTAL_SPACE_IN_MB,
round(b.bytes_free/(1024*1024),2) FREE_SPACE_IN_MB,
round(b.smallest/(1024*1024),2) min_size_in_MB,
round(b.largest/(1024*1024),2) max_size_in_MB,
round(((a.bytes_used-b.bytes_free)/a.bytes_used)*100,2) percent_used
from
(
select TABLESPACE_NAME, sum(bytes) bytes_used
from dba_data_files
group by TABLESPACE_NAME
) a,
(
select TABLESPACE_NAME, sum(BYTES) bytes_free, min(BYTES) smallest, max(BYTES) largest
from dba_free_space
group by TABLESPACE_NAME
) b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME(+)
and a.tablespace_name = decode('&tablespace_name',null,a.tablespace_name,'&tablespace_name')
order by ((a.BYTES_used-b.BYTES_free)/a.BYTES_used) desc;
exit;
eg:
SQL> @free.sql
Enter Tablespace Name :SYSTEM
Space Usage on Tablespaces
*****************************************
TABLESPACE_NAME TOTAL_SPACE_IN_MB FREE_SPACE_IN_MB MIN_SIZE_IN_MB MAX_SIZE_IN_MB PERCENT_USED
------------------------------ ----------------- ---------------- -------------- -------------- ------------
SYSTEM 710 9.38 .38 9 98.68
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@rac2 ~]$
-- Check all tablespace size in the database
set linesize 150
column tablespace_name format a20 heading 'Tablespace'
column sumb format 999,999,999
column extents format 9999
column bytes format 999,999,999,999
column largest format 999,999,999,999
column Tot_Size format 999,999 Heading 'Total| Size(Mb)'
column Tot_Free format 999,999,999 heading 'Total Free(MB)'
column Pct_Free format 999.99 heading '% Free'
column Chunks_Free format 9999 heading 'No Of Ext.'
column Max_Free format 999,999,999 heading 'Max Free(Kb)'
set echo off
PROMPT FREE SPACE AVAILABLE IN TABLESPACES
select a.tablespace_name,sum(a.tots/1048576) Tot_Size,
sum(a.sumb/1048576) Tot_Free,
sum(a.sumb)*100/sum(a.tots) Pct_Free,
sum(a.largest/1024) Max_Free,sum(a.chunks) Chunks_Free
from
(
select tablespace_name,0 tots,sum(bytes) sumb,
max(bytes) largest,count(*) chunks
from dba_free_space a
group by tablespace_name
union
select tablespace_name,sum(bytes) tots,0,0,0 from
dba_data_files
group by tablespace_name) a
group by a.tablespace_name
order by pct_free;
eg:
Tablespace
(TBS)
Name File Count Size(MB) Free(MB) Used(MB) Max Ext(MB) % Free
---------------------------------------- ---------- ---------- ---------- ---------- ----------- ----------
SYSAUX 1 540 29 511 32767.9844 5.37037037
SYSTEM 1 710 9.37 700.62 32767.9844 1.32042254
UNDOTBS1 1 30 6.06 23.93 32767.9844 20.2083333
USERS 1 1024 846.37 177.62 32767.9844 82.6538086
SQL>
Undo Used/Free Space
col allocated for 999,999.999
col free for 999,999.999
col used for 999,999.999
select
( select sum(bytes)/1024/1024 from dba_data_files
where tablespace_name like 'UND%' ) Toal_allocated_in_MB,
( select sum(bytes)/1024/1024 from dba_free_space
where tablespace_name like 'UND%') free_in_MB,
( select sum(bytes)/1024/1024 from dba_undo_extents
where tablespace_name like 'UND%') USed_in_MB
from dual
/
eg:
TOAL_ALLOCATED_IN_MB FREE_IN_MB USED_IN_MB
-------------------- ---------- ----------
110 31.0625 77.9375
TEMP TABLESPACE USAGE
script 1:
column used_MBytes format 999,999
column free_Mbytes format 999,999
column total_MBytes format 999,999
column collect_time format A15
select
to_char(sysdate,'DD-MON-RR:HH24:MI') collect_time
,round(used_blocks*8192/1024/1024,0) used_Mbytes
,round(free_blocks*8192/1024/1024,0) free_Mbytes
,round(total_blocks*8192/1024/1024,0) total_Mbytes
from
V$sort_segment
where
tablespace_name like '%TEMP%'
/
eg:
COLLECT_TIME USED_MBYTES FREE_MBYTES TOTAL_MBYTES
--------------- ----------- ----------- ------------
17-JUL-16:17:23 5 24 29
script 2:
set lines 180
col FreeSpaceGB format 999,999
col UsedSpaceGB format 999,999
col TotalSpaceGB format 999,999
col host_name format a30
col tablespace_name format a30
select tablespace_name,
(free_blocks*8)/1024 FreeSpaceMB,
(used_blocks*8)/1024 UsedSpaceMB,
(total_blocks*8)/1024 TotalSpaceMB,
i.instance_name,i.host_name
from gv$sort_segment ss,gv$instance i where ss.tablespace_name in (select tablespace_name from dba_tablespaces where tablespace_name='&TEMPTBS' and contents='TEMPORARY') and
i.inst_id=ss.inst_id;
eg:
TABLESPACE_NAME FREESPACEMB USEDSPACEMB TOTALSPACEMB INSTANCE_NAME HOST_NAME
------------------------------ ----------- ----------- ------------ ---------------- ----------------------
TEMP 24 5 29 orcl rac1.rajasekhar.com
script 3:
SELECT TABLESPACE_NAME, TABLESPACE_SIZE/1024/1024 as TABLESPACE_SIZE_MB, ALLOCATED_SPACE/1024/1024 as ALLOCATED_SPACE_MB, FREE_SPACE/1024/1024 as FREE_SPACE_MB FROM dba_temp_free_space;
TABLESPACE_NAME TABLESPACE_SIZE_MB ALLOCATED_SPACE_MB FREE_SPACE_MB
------------------------------ ------------------ ------------------ -------------
TEMP 30 30 29
Script 4:
-- Temp TBS size for each datafile.
compute sum of Max on report
break on tablespace_name on report nodup
select tablespace_name,file_name,autoextensible,(maxbytes)/1024/1024/1024 Max,sum(bytes)/1024/1024/1024 from dba_temp_files where tablespace_name='&TEMP_TBS' group by tablespace_name,file_name,autoextensible,maxbytes order by tablespace_name;
Script 5: Which session using how much TEMP space
COLUMN tablespace FORMAT A25
COLUMN temp_size FORMAT A20
COLUMN sid_serial FORMAT A20
COLUMN username FORMAT A20
COLUMN program FORMAT A40
SET LINESIZE 200
SELECT
a.sid||','||a.serial# AS sid_serial,b.sql_id,b.segtype,
NVL(a.username, '(oracle)') AS username,
a.program, b.tablespace,ROUND(((b.blocks*p.value)/1024/1024),2)||'M' AS temp_size
FROM gv$session a,
gv$sort_usage b,
gv$parameter p
WHERE p.name = 'db_block_size'
AND a.saddr = b.session_addr and b.TABLESPACE='&TEMP_TABLESPACE'
ORDER BY b.tablespace, b.blocks
/
-- Database default temp
SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';
col file_name for a45
select tablespace_name,file_name,bytes/1024/1024,maxbytes/1024/1024,autoextensible from dba_temp_files order by file_name;
select username,default_tablespace,temporary_tablespace from dba_users order by temporary_tablespace;
Temp tablespace used by user
select
srt.tablespace,
srt.segfile#,
srt.segblk#,
srt.blocks,
a.sid,
a.serial#,
a.username,
a.osuser,
a.status
from
v$session a,
v$sort_usage srt
where
a.saddr = srt.session_addr
order by
srt.tablespace, srt.segfile#, srt.segblk#,
srt.blocks;
Add temp file
select FILE_NAME, TABLESPACE_NAME from dba_temp_files;
alter tablespace temp add tempfile '' size 1800M;
alter tablespace temp add tempfile '+DATAX' size 1800M;
resize tempfile
select FILE_NAME, TABLESPACE_NAME from dba_temp_files;
alter database tempfile '' resize 500M;
alter database tempfile '' autoextend on maxsize 1G;
Verify Alert log
show parameter background
cd /u01/app/oracle/diag/rdbms/orcl/orcl/trace
ls -ltr | tail
view alert_orcl.log
shift + G ---> to get the tail end...
?ORA-1652 ---- to search of the error...
shift + N ---- to step for next reported error...
Identify OS process ID based on database SID
- PID : Oracle process identifier
- SPID: Operating system process identifier
col sid format 999999
col username format a20
col osuser format a15
select a.sid, a.serial#,a.username, a.osuser, b.spid
from gv$session a, gv$process b
where a.paddr= b.addr
and a.sid='&sid'
order by a.sid;
Identify database SID based on OS Process ID
- PID : Oracle process identifier
- SPID: Operating system process identifier
col sid format 999999
col username format a20
col osuser format a15
select b.spid,a.sid, a.serial#,a.username, a.osuser
from gv$session a, gv$process b
where a.paddr= b.addr
and b.spid='&spid'
order by b.spid;
11. Find out Index Name and Column Name for Table
DBA_INDEXES gives just index names associated with the table and DBA_IND_COLUMNS gives the associated columns and their order.
SQL> column table_owner format a20
column table_name format a25
column index_name format a25
column column_name format a25
select owner, table_owner,TABLE_NAME,index_name
from dba_indexes
where table_name='COSTS';SQL> SQL> SQL> SQL> 2 3
OWNER TABLE_OWNER TABLE_NAME INDEX_NAME
------------------------------ -------------------- ------------------------- -------------------------
EOPS EOPS COSTS COSTS_PROD_BIX
EOPS EOPS COSTS COSTS_TIME_BIX
SH SH COSTS COSTS_TIME_BIX
SH SH COSTS COSTS_PROD_BIX
SQL> Select TABLE_OWNER, table_name, index_name, column_name, COLUMN_POSITION
FROM dba_ind_columns
Where TABLE_OWNER='SH'
AND table_name='COSTS'
AND INDEX_NAME='COSTS_PROD_BIX'
Order by TABLE_OWNER, table_name, column_name; 2 3 4 5 6
TABLE_OWNER TABLE_NAME INDEX_NAME COLUMN_NAME COLUMN_POSITION
-------------------- ------------------------- ------------------------- ------------------------- ---------------
SH COSTS COSTS_PROD_BIX PROD_ID 1
SQL>
12. Find DB Size
Find out total file size of a Oracle database
SQL> select b.name,
( select sum(bytes)/1024/1024/1024 DF_size from dba_data_files ) +
( select nvl(sum(bytes),0)/1024/1024/1024 TF_size from dba_temp_files ) +
2 3 4 ( select sum(bytes)/1024/1024/1024 RLF_size from sys.v_$log ) +
5 ( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 CF_size from v$controlfile) "Size in GB"
6 from dual, (select name from v$database ) b;
NAME Size in GB
--------- ----------
CAT 5.36999512
SQL>
Find out the data occupied size for an Oracle database
SQL> SELECT name,sum(bytes)/1024/1024/1024 AS "Size in GB" FROM dba_segments,
(select name from v$database) group by name; 2
NAME Size in GB
--------- ----------
CAT 2.37426758
SQL>
13. SQL Query to Get Hostname, IP Address and Terminal from Oracle Database
A. Using sys_context
SQL> SELECT SYS_CONTEXT ('USERENV', 'SERVER_HOST') from dual;
SYS_CONTEXT('USERENV','SERVER_HOST')
-----------------------------------------
rac2
SQL> SELECT SYS_CONTEXT('USERENV','HOST') FROM dual;
SYS_CONTEXT('USERENV','HOST')
----------------------------------------
rac2.rajasekhar.com
SQL>
B. Using v$instance
SQL> select host_name from v$instance;
HOST_NAME
---------------------------------------
rac2.rajasekhar.com
SQL>
C. UTL_INADDER.get_host_name
SQL> SELECT UTL_INADDR.get_host_name FROM dual;
GET_HOST_NAME
--------------------------------------------------------------------------------
rac2.rajasekhar.com
SQL>
D. UTL_INADDR.get_host_address
SQL> SELECT UTL_INADDR.get_host_address from dual;
GET_HOST_ADDRESS
-------------------------------------
192.168.2.102
SQL>
E. Query to find out TERMINAL
SQL> SELECT SYS_CONTEXT('USERENV','TERMINAL') FROM dual;
SYS_CONTEXT('USERENV','TERMINAL')
-----------------------------------
pts/2
SQL>
14. Archive Log Generation Report Thread Wise Daily
SET PAGESIZE 60
SET LINESIZE 300
SET VERIFY OFF
SELECT TRUNC(completion_time) "Generation Date" , thread#,count(*)
FROM gv$archived_log
GROUP BY TRUNC(completion_time),thread#
ORDER BY TRUNC(completion_time);
15. Redo generation per day
select trunc(completion_time) rundate
,count(*) logswitch
,round((sum(blocks*block_size)/1024/1024)) "REDO PER DAY (MB)"
from gv$archived_log
group by trunc(completion_time)
order by 1;
16. Redo Generated per Hour
SELECT Start_Date, Start_Time, Num_Logs,
Round(Num_Logs * (Vl.Bytes / (1024 * 1024)),2) AS Mbytes, Vdb.NAME AS Dbname
FROM (SELECT To_Char(Vlh.First_Time, 'YYYY-MM-DD') AS Start_Date,
To_Char(Vlh.First_Time, 'HH24') || ':00' AS Start_Time, COUNT(Vlh.Thread#) Num_Logs
FROM gv$log_History Vlh
GROUP BY To_Char(Vlh.First_Time, 'YYYY-MM-DD'),
To_Char(Vlh.First_Time, 'HH24') || ':00') Log_Hist,
gv$log Vl , gv$database Vdb
WHERE Vl.Group# = 1
ORDER BY Log_Hist.Start_Date, Log_Hist.Start_Time;
17. Hourly archivelog switches
SELECT to_date(first_time) DAY,
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'99') "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'99') "01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'99') "02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'99') "03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'99') "04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'99') "05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'99') "06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'99') "07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'99') "08",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'99') "09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'99') "10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'99') "11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'99') "12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'99') "13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'99') "14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'99') "15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'99') "16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'99') "17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'99') "18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'99') "19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'99') "20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'99') "21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'99') "22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'99') "23"
from
v$log_history
where to_date(first_time) > sysdate -8
GROUP by
to_char(first_time,'YYYY-MON-DD'), to_date(first_time)
order by to_date(first_time)
/
18. Generating Service based ASH Report
-- Generating Service based ASH Report
select output from table(
DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_HTML(
l_dbid =>(select dbid from v$database),
l_inst_num =>(select 1 from v$instance), --pls. replace with appropriate instance number
l_btime =>to_date('28-JAN-2016 07:05:00','dd-mon-yyyy hh24:mi:ss'),
l_etime =>to_date('28-JAN-2016 07:15:00','dd-mon-yyyy hh24:mi:ss'),
/* l_options IN NUMBER DEFAULT 0,
l_slot_width IN NUMBER DEFAULT 0,
l_sid IN NUMBER DEFAULT NULL,
l_sql_id IN VARCHAR2 DEFAULT NULL,
l_wait_class IN VARCHAR2 DEFAULT NULL,*/
l_service_hash =>(select name_hash from dba_services where name=upper(''))));
19. Verify Archive Log GAP
SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;
20. Index Rebuild with DB Shutdown
[oracle@dg2 ~]$ cat rebuild_ix.sh
export ORACLE_SID=$1
export ORACLE_HOME=/u01/app/oracle/product/1900/db_1
export PATH=$PATH:$ORACLE_HOME/bin
$ORACLE_HOME/bin/sqlplus -S /nolog << !
connect / as sysdba
shut immediate;
startup restrict;
SELECT NAME,OPEN_MODE,HOST_NAME,LOGINS FROM V\$DATABASE,V\$INSTANCE;
set linesize 1000
set pagesize 0
set feedback off
set trimspool on
spool /tmp/reduild_ix_dbname.sql
select 'alter index '||owner||'.'||index_name||' rebuild;'
from dba_indexes
where table_owner not in ('SYS','SYSTEM')
and index_type <> 'LOB'
and partitioned = 'NO'
and temporary ='N'
order by 1;
spool off
set feedback on
spool reduild_ix_dbname.log
@/tmp/reduild_ix_dbname.sql
alter system disable restricted session;
SELECT NAME,OPEN_MODE,HOST_NAME,LOGINS FROM V\$DATABASE,V\$INSTANCE;
spool off
exit
!
[oracle@dg2 ~]$
[oracle@dg2 ~]$ nohup ./rebuild_ix.sh PEPSI &
[1] 15204
[oracle@dg2 ~]$ nohup: ignoring input and appending output to ‘nohup.out’
[oracle@dg2 ~]$
21. Index Rebuild with Index Partition and Index Sub Partition
[oracle@rac1 ~]$ cat index_rebuild_list.sh
export ORACLE_SID=$1
export ORACLE_HOME=/u02/app/oracle/product/12102/db_1
export PATH=$PATH:$ORACLE_HOME/bin
if [ $? != 0 ]
then exit 1
fi
#if [ $2 != 0 ]
#then exit 1
#fi
if [ $2 != "" ]
then
SCHEMA=`echo ${2}|tr '[:upper:]' '[:lower:]'`
fi
$ORACLE_HOME/bin/sqlplus -S /nolog << !
connect / as sysdba
SET LINES 190
set echo off
set termout off
set verify off
set trimspool on
set feedback off
set heading off
set lines 300
set pages 0
spool /home/oracle/index_rebuild.sql
SELECT 'ALTER INDEX '||OWNER||'."'||INDEX_NAME||'" REBUILD;' FROM DBA_INDEXES WHERE OWNER=upper('${SCHEMA}') AND PARTITIONED='NO' AND index_type not in ('LOB','FUNCTION-BASED DOMAIN','FUNCTION-BASED NORMAL','BITMAP','CLUSTER') AND TEMPORARY='N';
SELECT 'ALTER INDEX '||INDEX_OWNER||'."'|| INDEX_NAME ||'" REBUILD PARTITION "' || PARTITION_NAME || '";' FROM DBA_IND_PARTITIONS WHERE INDEX_OWNER=upper('${SCHEMA}') AND index_name in (select index_name from dba_indexes where index_type not in ('LOB','FUNCTION-BASED DOMAIN','FUNCTION-BASED NORMAL','BITMAP','CLUSTER'));
SELECT 'ALTER INDEX '||INDEX_OWNER||'."'|| INDEX_NAME ||'" REBUILD SUBPARTITION "' || SUBPARTITION_NAME || '";' FROM DBA_IND_SUBPARTITIONS WHERE INDEX_OWNER=upper('${SCHEMA}') AND INDEX_NAME IN (SELECT INDEX_NAME FROM DBA_INDEXES WHERE INDEX_TYPE NOT IN ('LOB','FUNCTION-BASED DOMAIN','FUNCTION-BASED NORMAL','BITMAP','CLUSTER'));
spool off
exit
!
$ORACLE_HOME/bin/sqlplus /nolog << !
connect / as sysdba
set pages 9999
set echo on
set termout on
set verify on
set trimspool on
set feedback on
set heading on
set lines 300
set serverout on
col spoolend new_value spoolend
select 'DELL_'||to_char(sysdate, 'DD_MON_YYYY_HH24_MI_SS') spoolend from dual;
spool /home/oracle/DELL_IX_REBUILD_&spoolend
SELECT NAME,OPEN_MODE,HOST_NAME,LOGINS FROM V\$DATABASE,V\$INSTANCE;
SELECT to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') AS INDEX_REBUILD_START_TIME from dual;
set timing on
@/home/oracle/index_rebuild.sql
SELECT to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') AS INDEX_REBUILD_END_TIME from dual;
spool off
!
[oracle@rac1 ~]$
[oracle@rac1 ~]$ nohup ./index_rebuild_list.sh DELL SH &
[1] 17274
[oracle@rac1 ~]$ nohup: appending output to `nohup.out'
[oracle@rac1 ~]$ jobs -l
[1]+ 17274 Running nohup ./index_rebuild_list.sh DELL SH &
[oracle@rac1 ~]$
Top 10 Fragmented tables
select *
from (select table_name,
round(((blocks * 8)/1024/1024), 1) "size (GB)",
round((num_rows * avg_row_len / 1024/1024/1024), 1) "actual_data (GB)",
(round(((blocks * 8)/1024/1024), 1) -
round((num_rows * avg_row_len / 1024/1024/1024), 1)) "wasted_space (GB)"
from dba_tables
where (round((blocks * 8), 1) >
round((num_rows * avg_row_len / 1024/1024/1024), 1))
order by 4 desc)
WHERE ROWNUM <= 10;
Monitor FRA
download
#!/bin/bash
# set the environment for oracle database
export ORACLE_SID=$1
export ORACLE_HOME=/u01/app/oracle/product/1900/db_1
export PATH=$PATH:$ORACLE_HOME/bin
# Connect to the Oracle database
FLASH_RECOVERY_SIZE=$(sqlplus -s / as sysdba <<EOF
set pagesize 0
set linesize 1000
col "FLASH_RECOVERY_SIZE(MB)" format 9999999999
SELECT
NAME,
TO_CHAR(SPACE_LIMIT, '999,999,999,999') AS SPACE_LIMIT,
TO_CHAR(SPACE_LIMIT - SPACE_USED + SPACE_RECLAIMABLE, '999,999,999,999')
AS SPACE_AVAILABLE,
ROUND((SPACE_USED - SPACE_RECLAIMABLE)/SPACE_LIMIT * 100)
AS PERCENT_FULL
FROM V\$RECOVERY_FILE_DEST;
EOF
)
# Check the flash recovery area usage
USAGE=$(echo $FLASH_RECOVERY_SIZE | awk '{print $4}')
# Output a message based on the usage
if [ $USAGE -lt 70 ]; then
echo "Flash recovery area usage is at $USAGE%. Space is still available."
elif [ $USAGE -lt 80 ]; then
echo "Flash recovery area usage is at $USAGE%. Space is getting low."
elif [ $USAGE -lt 90 ]; then
echo "Flash recovery area usage is at $USAGE%. Space is running low."
else
echo "Flash recovery area usage is at $USAGE%. Space is critical."
fi
Delete archivelogs
download
#!/bin/bash
export ORACLE_SID=$1
export ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1
export PATH=$PATH:$ORACLE_HOME/bin
# Define the location of the log files
log_dir="/u01/app/oracle/logs"
# Define the name of the log file prefix
log_prefix="rman_delete_logs"
# Get the current date and time
today=$(date +%F_%H-%M-%S)
# Construct the full path and name of the log file
log_file="$log_dir/$log_prefix-$today.log"
# Connect to the database using RMAN and redirect output to the log file
rman target / >> $log_file 2>&1 << EOF
# Delete all archive logs
crosscheck archivelog all;
delete noprompt archivelog all;
delete noprompt expired archivelog all;
# End the RMAN session
exit;
EOF
# Rotate the log files
find $log_dir -type f -name "$log_prefix*" -mtime +7 -exec rm {} \;
# Output a message indicating that the archive logs have been deleted and logged
echo "All archive logs have been deleted using RMAN. Output has been logged to $log_file and older logs have been rotated."
Delete archivelogs
#!/bin/bash
# Set ORACLE_SID and ORACLE_HOME
ORACLE_SID=standby_db
ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1
# Export environment variables
export ORACLE_SID ORACLE_HOME
# Connect to the Data Guard broker and retrieve the apply lag
APPLY_LAG=$(dgmgrl << EOF
connect /
show database verbose 'standby_db';
quit;
EOF
)
# Extract the apply lag from the output
APPLY_LAG=$(echo "$APPLY_LAG" | grep -A1 "Apply Lag:" | tail -1 | awk '{print $3}')
# Print the apply lag
echo "Apply Lag: $APPLY_LAG"
Monitor Archive log GAP
#!/bin/bash
.... Method 1
monitor_gap.sh
monitor_gap.sql
.... Method 2
standby_gap.sh
#monitor_gap.sh
#!/bin/ksh
#This script monitors the Data Guard standby gap and sends an alert if it exceeds the threshold.
#Set variables
ORACLE_SID=$1
ORACLE_HOME=`ps -ef |grep pmon|grep $1|awk {'print "pwdx " $2 '}|sh|awk {'print $2 '}|sed 's/....$//'`
export PATH=$PATH:$ORACLE_HOME/bin
STANDBY_GAP_THRESHOLD=$2
export ORACLE_HOME PATH ORACLE_SID STANDBY_GAP_THRESHOLD
#Set output file and remove any existing log file
out_file=/home/oracle/monitor_gap.log
rm $out_file 2>/dev/null
touch $out_file
#Run SQL script to check standby gap
standby_gap()
{
sqlplus -s "/ as sysdba" > $out_file <<sqlconn
@/home/oracle/monitor_gap.sql
exit
sqlconn
}
standby_gap
#Check for standby gap exceeding threshold and set alert flag if necessary
grep -v '^[[:space:]]*$' $out_file | while read LINE
do
T1=`echo $LINE | cut -f3 -d' '`
if [ $T1 -gt $STANDBY_GAP_THRESHOLD ]
then
echo "Dataguard Error: Not in Sync"
else
echo "Dataguard is in Sync"
fi
done
#monitor_gap.sql
set heading off feedback off
set pages 0
column applied_time for a30
set linesize 140
--select to_char(sysdate,'mm-dd-yyyy hh24:mi:ss') "Current Time" from dual;
SELECT DB_NAME, APPLIED_TIME, LOG_ARCHIVED-LOG_APPLIED LOG_GAP ,
(case when ((APPLIED_TIME is not null and (LOG_ARCHIVED-LOG_APPLIED) is null) or
(APPLIED_TIME is null and (LOG_ARCHIVED-LOG_APPLIED) is not null) or
((LOG_ARCHIVED-LOG_APPLIED) > 10))
then 'Error! Log Gap '
else 'OK!'
end) Status
FROM
(
SELECT INSTANCE_NAME DB_NAME
FROM GV$INSTANCE
where INST_ID = 1
),
(
SELECT MAX(SEQUENCE#) LOG_ARCHIVED
FROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES' and THREAD#=1
),
(
SELECT MAX(SEQUENCE#) LOG_APPLIED
FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES' and THREAD#=1
),
(
SELECT TO_CHAR(MAX(COMPLETION_TIME),'DD-MON/HH24:MI') APPLIED_TIME
FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES' and THREAD#=1
)
UNION
SELECT DB_NAME, APPLIED_TIME, LOG_ARCHIVED-LOG_APPLIED LOG_GAP,
(case when ((APPLIED_TIME is not null and (LOG_ARCHIVED-LOG_APPLIED) is null) or
(APPLIED_TIME is null and (LOG_ARCHIVED-LOG_APPLIED) is not null) or
((LOG_ARCHIVED-LOG_APPLIED) > 10))
then 'Error! Log Gap '
else 'OK!'
end) Status
from (
SELECT INSTANCE_NAME DB_NAME
FROM GV$INSTANCE
where INST_ID = 2
),
(
SELECT MAX(SEQUENCE#) LOG_ARCHIVED
FROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES' and THREAD#=2
),
(
SELECT MAX(SEQUENCE#) LOG_APPLIED
FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES' and THREAD#=2
),
(
SELECT TO_CHAR(MAX(COMPLETION_TIME),'DD-MON/HH24:MI') APPLIED_TIME
FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES' and THREAD#=2
)
/#standby_gap.sh
## Modified by Sugivaradhan
#!/bin/ksh
#This script monitors the Data Guard standby gap and sends an alert if it exceeds the threshold.
#Set variables
ORACLE_SID=$1
ORACLE_HOME=`ps -ef |grep pmon|grep $1|awk {'print "pwdx " $2 '}|sh|awk {'print $2 '}|sed 's/....$//'`
export PATH=$PATH:$ORACLE_HOME/bin
STANDBY_GAP_THRESHOLD=$2
export ORACLE_HOME PATH ORACLE_SID STANDBY_GAP_THRESHOLD
#Set output file and remove any existing log file
out_file=/home/oracle/monitor_gap_NEW.log
rm $out_file 2>/dev/null
touch $out_file
#Run SQL script to check standby gap
standby_gap()
{
sqlplus -s "/ as sysdba" > $out_file <<sqlconn
set lines 160 pages 0
col GAP for a20
select (select sysdate from dual)"TIMESTAMP",s.thread#,s.MAX_SEQ#, a.MAX_APP_SEQ#, ' '||(s.MAX_SEQ#-a.MAX_APP_SEQ#)||' ' "GAP" from
( select thread#,max(sequence#) "MAX_SEQ#" from v\$archived_log where activation#=(select activation# from v\$database) group by thread# ) s,
(select thread#,max(sequence#) "MAX_APP_SEQ#" from v\$archived_log where activation#=(select activation# from v\$database )and applied='YES' group by thread#) a where a.thread#=s.thread#;
exit
sqlconn
}
standby_gap
#Check for standby gap exceeding threshold and set alert flag if necessary
grep -v '^[[:space:]]*$' $out_file | while read LINE
do
T1=`echo $LINE | cut -f5 -d' '`
if [ $T1 -gt $STANDBY_GAP_THRESHOLD ]
then
echo "Dataguard Error: Not in Sync"
else
echo "Dataguard is in Sync"
fi
done
