I have many DB servers to monitor, running on both Windows/Solaris servers. Power and network connectivity are issues. Often I will lose disks or mount points, sometimes new disks will suddenly show up. Mirroring in ASM is often the only thing that keeps the RAC systems alive. When old disks "reappear" they've shrunk or grown. Third-party backup server(s) die and suddenly my disks/ASM are full of archivelogs (or the sysads copy OS patching files where they shouldn't). The list of disk-related (alone) issues that will bring my DBs down is endless.
I need a way to be notified when these events occur, log them into a table for historical reasons, and try to proactively identify disks whose growth trends will require immediate attention. I'd like to create a PL/SQL job that can query the DB servers (db_links?) and grab the needed disk info. I can identify when a tablespace is nearly full, but I cannot determine whether any of the existing datafiles can be extended, without logging into the server. Ideally I could generate some dynamic SQL to automatically add/extend datafiles, but I'd first need info on the underlying disks.
How do I get that information in PL/SQL.
External tables are awesome for this kind of thing. I'll demo the Windows one, but easily adapted to Unix (by using df or any command you like).
Step 1
======
A program to list disk space. So I did this:
@echo off
for /d %%i in ( c: d: ) do dir %%i | findstr "drive free"
and saved it as C:\BIN\DISK_FREE.BAT
Step 2
======
External table to make use of it
SQL> create or replace directory log_dir as 'c:\temp';
Directory created.
SQL> grant read, write on directory log_dir to mcdonac;
Grant succeeded.
SQL> create or replace directory bin_dir as 'c:\bin';
Directory created.
SQL> grant execute on directory bin_dir to mcdonac;
Grant succeeded.
SQL>
SQL> drop table mcdonac.windows_disk_free;
Table dropped.
SQL> create table mcdonac.windows_disk_free
2 ( line varchar2(200)
3 )
4 organization external
5 (
6 type oracle_loader
7 default directory log_dir
8 access parameters
9 (
10 records delimited by newline
11 preprocessor bin_dir: 'disk_free.bat'
12 fields terminated by "~"
13 )
14 location (log_dir:'dummy.dat')
15 )
16 reject limit unlimited;
Table created.
SQL>
SQL>
SQL> declare
2 f utl_file.file_type;
3 begin
4 f := utl_file.fopen('LOG_DIR','dummy.dat','w');
5 utl_file.fclose(f);
6 end;
7 /
PL/SQL procedure successfully completed.
I need to create "dummy.dat" because an external table must have a default location to read from, but we'll be getting nothing from that file.
Step 3
======
Manipulate the output to suit
SQL> select * from mcdonac.windows_disk_free;
LINE
-----------------------------------------------------------------------------------------
Volume in drive C is System
3 Dir(s) 69,837,852,672 bytes free
Volume in drive D is Data
3 Dir(s) 147,848,654,848 bytes free
SQL> select
2 min(case when mod(r,2) = 0 then x end ) drive,
3 to_number(min(case when mod(r,2) = 1 then x end )) space
4 from (
5 select
6 rownum+1 r,
7 case
8 when line like '%Volume in%' then substr(line,18,1)
9 else replace(regexp_substr(line,'(.*Dir...)(.*) (bytes.*)',1,1,'i',2),',')
10 end x
11 from mcdonac.windows_disk_free
12 )
13 group by trunc(r/2);
DRIVE SPACE
---------- ----------
C 6.9838E+10
D 1.4785E+11
Hope this helps.