Procedure to check if the filesystems can accommodate a database's autoextensible datafiles

The following procedure will sum the space still to be claimed by the autoextensible datafiles per filesystem and compare those sums will the filesystems' free space. Be aware, this assumes there are no more than one database's autoextensible datafiles per filesystem. Otherwise, you may try to use database links to gather all required information in one database and get an aggregated result.

First of all, we need to retrieve information about the filesystems in our server, so since this is a Linux server, we will use the "df" command. In the home directory [/home/oracle], create a df.sh:

#!/bin/sh
/bin/df -Pl

And make it executable:

chmod u+x df.sh

Execute it:

Filesystem    1024-blocks Used Available Capacity Mounted on
/dev/mapper/rootvg00-lvol_root  18368380 5043772 12391540 29%  /
tmpfs     1961068  683460 1277608  35%  /dev/shm
/dev/sda1    198337  73094 115003  39%  /boot
/dev/mapper/rootvg00-lvol_home  2064208  137952 1821400  8%  /home
/dev/mapper/rootvg00-lvol_tmp  4128448  139296 3779440  4%  /tmp
/dev/mapper/rootvg00-lvol_var  8260812  3771552 4069628  49%  /var
/dev/mapper/rootvg00-lvol_vartmp 4128448  227884 3690852  6%  /var/tmp
/dev/mapper/OracleVG-lvbinaries  11123304 6210032 4348232  59%  /orabin
/dev/mapper/OracleVG-lvredo2  444276  177541 243798  43%  /redo2
/dev/mapper/OracleVG-lvredo1  444276  177541 243798  43%  /redo1
/dev/mapper/OracleVG-lvarch  21778916 35900 20644052 1%  /orarch
/dev/mapper/OracleVG-lvdata  9079280  7751656 866416  90%  /oradata
/dev/mapper/vgoradata2-lvoradata2 20428668 6516652 12874296 34%  /oradata2

For instance, we can see /oradata filesystem has 866416 Kbytes of unused space.

Now, we need to make this information available to our database.
We create a directory to point to our home directory:

CREATE OR REPLACE DIRECTORY HOME_DIR AS '/home/oracle';

And an external table to query this information:

CREATE TABLE FILESYSTEMS
(FSNAME VARCHAR2(100), BLOCKS NUMBER, USED NUMBER, AVAILABLE NUMBER, CAPACITY VARCHAR2(5), MOUNT VARCHAR2(100))
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
 DEFAULT DIRECTORY HOME_DIR
 ACCESS PARAMETERS
 (RECORDS DELIMITED BY NEWLINE
  NOLOGFILE
  PREPROCESSOR
  HOME_DIR:'df.sh'
  SKIP 1
  FIELDS TERMINATED BY WHITESPACE LDRTRIM)
 LOCATION
 (HOME_DIR:'df.sh'));

Let's use it:

SELECT * FROM FILESYSTEMS;

   

FSNAME	BLOCKS	USED	AVAILABLE	CAPACITY	MOUNT
/dev/mapper/rootvg00-lvol_root	18368380	5043772	12391540	29%	/
tmpfs	1961068	683460	1277608	35%	/dev/shm
/dev/sda1	198337	73094	115003	39%	/boot
/dev/mapper/rootvg00-lvol_home	2064208	137952	1821400	8%	/home
/dev/mapper/rootvg00-lvol_tmp	4128448	139296	3779440	4%	/tmp
/dev/mapper/rootvg00-lvol_var	8260812	3772356	4068824	49%	/var
/dev/mapper/rootvg00-lvol_vartmp	4128448	227952	3690784	6%	/var/tmp
/dev/mapper/OracleVG-lvbinaries	11123304	6210100	4348164	59%	/orabin
/dev/mapper/OracleVG-lvredo2	444276	177541	243798	43%	/redo2
/dev/mapper/OracleVG-lvredo1	444276	177541	243798	43%	/redo1
/dev/mapper/OracleVG-lvarch	21778916	197192	243798	1%	/orarch
/dev/mapper/OracleVG-lvdata	9079280	7751656	866416	90%	/oradata
/dev/mapper/vgoradata2-lvoradata2	20428668	6516652	12874296	34%	/oradata2

We create one more table [FILESYSTEMS_INFO], identical to FILESYSTEMS, 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 FILESYSTEMS_INFO
(FSNAME VARCHAR2(100), MOUNT VARCHAR2(100), BLOCKS NUMBER, USED NUMBER, AVAILABLE NUMBER, CAPACITY VARCHAR2(5), TO_BE_USED NUMBER, OK VARCHAR2(3))
TABLESPACE USERS;

 We run the following PL/SQL code:

DECLARE

BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE FILESYSTEMS_INFO';

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

UPDATE FILESYSTEMS_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 FILESYSTEMS_INFO:

SELECT MOUNT, ROUND(AVAILABLE/1024/1024, 2) "AVAILABLE GB", ROUND(TO_BE_USED/1024/1024, 2) "TO BE USED GB", OK
FROM FILESYSTEMS_INFO;

   

MOUNT	AVAILABLE GB	TO BE USED GB	OK
/	11,82	0	YES
/dev/shm	1,22	0	YES
/boot	0,11	0	YES
/home	1,74	0	YES
/tmp	3,6	0	YES
/var	3,88	0	YES
/var/tmp	3,52	0	YES
/orabin	4,15	0	YES
/redo2	0,23	0	YES
/redo1	0,23	0	YES
/orarch	19,57	0	YES
/oradata	0,83	0,63	YES
/oradata2	12,28	35,09	NO

/oradata filesystem is OK, because it has 0,83 GBytes of free space and all autoextensible datafiles in it could claim 0,63 GBytes maximum.
On the other hand, /oradata2 has 12,28 GBytes of free space and 35,09 GBytes could be claimed. We should decrease one or more autoextensible datafiles' MAXSIZE.

Optimize Siebel Repository Import in Oracle Database

It was noticed during siebel repository import in an Oracle Database 11.2.0.3, most of database wait time was log file switch.

srvrupgwiz /m master_imprep.ucf

It took 163 minutes for 3,639,432 rows (98% waits log file switch).

2012-11-18 09:53:02     TOTAL TABLES: 328
2012-11-18 09:53:02     TOTAL ROWS  : 3639432
2012-11-18 09:53:02     Cleaning up, disconnecting from the database.
2012-11-18 09:53:02     Elapsed time: 163 min 28 sec.

The database session did ~730.000 commits (meaning 1 commit every 5 rows).

repimexp /a I /G ENU /u sadmin /p ***** /c siebelpro_DSN /d siebel /r "Siebel Repository" /Z 5000 /h 5000 /f siebel_rep.data /l imprep_prim.log

If the /Z and /h option are used (with 5000 as argument) the time is less than 10 minutes for the same  repository.

2013-01-27 11:34:35     Verified successfully.
2013-01-27 11:34:35     TOTAL TABLES: 328
2013-01-27 11:34:35     TOTAL ROWS  : 3928282
2013-01-27 11:34:35     Cleaning up, disconnecting from the database.
2013-01-27 11:34:35     Elapsed time: 9 min 46 sec.

There is no way (until Dec 2012) to put /Z or /h in ucm file, so the only solution is to use the command line interface (Oracle support replied that is a product defect).

Reference from Oracle documentation:
Importing a Siebel Repository

NOTE:  When you import data into the Siebel Repository tables, a commit is performed once for each table into which repository data is imported. Alternatively, the commit frequency can be set to a specified number of rows by including the command-line option /h num_rows_per_commit when the repimexp.exe utility is invoked.
 /H [number]        (Optional) Number of rows per commit
 /Z [number]        (Optional) Array Insert Size (Default: 5)