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 :)
