Thursday, March 15, 2012

How to Solve Oracle Error ora-1114 in SAP Instance using brtools

Steps would be like
1. Log into each of the SAP DB Servers thru Telnet
2. login as ora
3. goto all the /oracle/SID/sapdata? FS
4. Check whether these FS have Temp Data files created in these Directory
5. check the Free Space left in the FS using the command
5.1 $ cd /oracle/SID/sapdata??
5.2 $ bdf . in Unix
5.3 $ df -k . in Unix & linux

6. Check the Size allocated to the Temp files
6.1 $ ls -lt ./temp_?/* replace ? with the actual number.

the value will be in bytes and not in KiloBytes. so divide this value (8589934592) by 1024 to convert the value into Kilobytes.

7. Check the space currently used by the Temp Files.
7.1 $ du -sk ./temp_2/*


8. So the temp data file can maximum grow, as in this example, to the size 3,817,552+324,960 KB
but the datafile created is sized (8589934592 / 1024) KB = 8,388,608 KB

9. if the Temp Data file size in the FS is more than free space left.
i.e.
if 8,388,608 KB > (3,817,552 + 324,960 - 317,440) KB
then resize the Temp date File to 3,817,552 + 324,960 - 317,440
where the 3,817,552 is the current used by the Temp File
324,960 is the free space left in the mount point
317,440 Min Free space required in the Mount Point for OS.

so resize the the Temp file to 3735 MB (3825072 KB / 1024 => 3735 MB)

10. Check whether the Temp Table Space is being used or not
$brtools
2 - Space management ->
7 - Additional space functions ->
1 = Show tablespaces ->
5 ~ Database tabelspace (tablespace) . [PSAPTEMP]
press "c - cont" -> "c - cont"
The space in the Temp table Space must not have been used.

11. Resizing the Data File, please use brtools
$brtools
-> 2 - Space management
-> 5 - Alter data file
-> 3 ~ Alter data file action (action) . [resize]
-> 4 ~ Tablespace names (tablespace) ... [PSAPTEMP]
-> 5 ~ Data file names (file) .......... [/oracle/AEM/sapdata10/temp_2/temp.data2]
press "c - cont" -> "c - cont"
-> 7 ? New data file size in MB (size) ......... [3735]
press "c - cont" -> "c - cont"

to confirm the corrections, do the same checkes which are listed in steps from 5-9

12. Add a new Temporary Data File in a separate file system of a reasonable Size.

No comments: