Database file management against filesystems

How to check whether filesystems can accommodate a database autoextensible datafiles?

First of all, we need to retrieve information about the filesystems in our server, so since this is a HP-UX server, we will use the "bdf" command and create a bdf.ksh file as below. If it is for Linux you can use df command.

#!/bin/ksh
/usr/bin/bdf |grep PSHR/sapdata

PS: I am grepping only for PSHR database filesystems.

Execute it:

Filesystem    1024-blocks Used Available Capacity Mounted on
/dev/vgdbPSHRdata1/PSHR_sapdata1
                   268304384 266199060 1973855   99% /oracle/PSHR/sapdata1
/dev/vgdbPSHRdata1/PSHR_sapdata2
                   268304384 261378897 6492762   98% /oracle/PSHR/sapdata2
/dev/vgdbPSHRdata1/PSHR_sapdata3
                   268304384 261514849 6365306   98% /oracle/PSHR/sapdata3


For instance, we can see /sapdata3 filesystem has 6365306 Kbytes of unused space.

Then we create a directory to point to any directory:

create or replace directory exec_dir as '/oracle/PSHR';

create or replace directory log_dir as '/oracle/PSHR/logs';

And an external table to query this information:

CREATE TABLE DF_DATA
(
  FSNAME     VARCHAR2(50 BYTE),
  BLOCKS     NUMBER,
  USED       NUMBER,
  AVAILABLE  NUMBER,
  CAPACITY   VARCHAR2(10 BYTE),
  MOUNT      VARCHAR2(50 BYTE)
)
ORGANIZATION EXTERNAL
  (  TYPE ORACLE_LOADER
     DEFAULT DIRECTORY EXE_DIR
     ACCESS PARAMETERS 
       ( records delimited by newline
logfile log_dir:'dfmonitor.log'
preprocessor
exe_dir:'bdf.ksh'
skip 1
fields terminated by
whitespace ldrtrim
 )
     LOCATION (EXE_DIR:'bdf.ksh')
  )
REJECT LIMIT 0
NOPARALLEL
NOMONITORING;

Let's use it:

SELECT * FROM DF_DATA;
    
Filesystem          kbytes    used   avail %used Mounted on
/dev/vgdbPSHRdata1/PSHR_sapdata1
                   268304384 266199060 1973855   99% /oracle/PSHR/sapdata1
/dev/vgdbPSHRdata1/PSHR_sapdata2
                   268304384 261378897 6492762   98% /oracle/PSHR/sapdata2
/dev/vgdbPSHRdata1/PSHR_sapdata3
                   268304384 261514849 6365306   98% /oracle/PSHR/sapdata3

We create one more table [DF_DATA_INFO], identical to DF_DATA, plus 2 more columns: TO_BE_USED, where we will sum the space still to be claimed by the autoextensible datafiles from DBA_DATA_FILES and DBA_TEMP_FILES, and OK, which will get value 'YES' if TO_BE_USED<AVAILABLE and 'NO' otherwise.

CREATE TABLE DF_DATA_INFO
(
  FSNAME      VARCHAR2(50 BYTE),
  MOUNT       VARCHAR2(40 BYTE),
  BLOCKS      NUMBER,
  USED        NUMBER,
  AVAILABLE   NUMBER,
  CAPACITY    VARCHAR2(8 BYTE),
  TO_BE_USED  NUMBER,
  OK          VARCHAR2(3 BYTE)
)
TABLESPACE DBA_TOOLS
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

We run the following PL/SQL code:

DECLARE

BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE DF_DATA_INFO';

INSERT INTO DF_DATA_INFO(FSNAME, MOUNT, BLOCKS, USED, AVAILABLE, CAPACITY, TO_BE_USED, OK)
SELECT FSNAME, MOUNT, BLOCKS, USED, AVAILABLE, CAPACITY, 0, 'YES'
FROM DF_DATA;

UPDATE DF_DATA_INFO B SET (B.TO_BE_USED, B.OK)=
(SELECT NVL(SUM(A.MAXBYTES - A.USER_BYTES)/1024, 0), CASE WHEN SUM(A.MAXBYTES - A.USER_BYTES)/1024 >= B.AVAILABLE THEN 'NO' ELSE 'YES' END
FROM (SELECT FILE_NAME, AUTOEXTENSIBLE, MAXBYTES, USER_BYTES FROM DBA_DATA_FILES UNION SELECT FILE_NAME, AUTOEXTENSIBLE, MAXBYTES, USER_BYTES FROM  DBA_TEMP_FILES) A
WHERE A.AUTOEXTENSIBLE = 'YES'
AND INSTR(A.FILE_NAME, B.MOUNT||'/', 1, 1) = 1);

COMMIT;

END;
/

Now, we query DF_DATA_INFO:

SELECT MOUNT, ROUND(AVAILABLE/1024/1024, 2) "AVAILABLE GB", ROUND(TO_BE_USED/1024/1024, 2) "TO BE USED GB", OK FROM DF_DATA_INFO;
    
MOUNT                                    AVAILABLE_GB TO_B_USED_GB OK
---------------------------------------- ------------ ------------ ---
/oracle/PSHR/sapdata2                             6.19       135.59 NO
/oracle/PSHR/sapdata1                             1.88        17.13 NO
/oracle/PSHR/sapdata3                             61.07        49.25 YES


/sapdata3 filesystem is OK, because it has 61.07 GBytes of free space and all autoextensible datafiles in it could claim 49,25 GBytes maximum.
On the other hand, /sapdata2 has 6.19 GBytes of free space and 135,59 GBytes could be claimed. We should decrease one or more autoextensible datafiles' MAXSIZE.

Easy isn't it :)