How to clear and recreate the NH_TEMP01 datafile.

Document ID : KB000050677
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

Eventually the Database Monitor window in OneClick will display a message stating that NH_TEMP01.DBF is approaching file size limit of 32GB. Even with the auto-extend option set as YES, once any datafile reaches 32GB in size it cannot be extended, and a new file must be created. The NH_TEMP tablespace is different from other tablespaces in the eHealth database in that instead of adding a new file, we can remove and recreate this file without a negative impact to the system.

This knowledge document explains the procedure to recreate the NH_TEMP01 datafile, and is valid for all versions of eHealth from 5.7 and up.

Solution:

Please follow the steps below to delete the current NH_TEMP01.dbf file (or whatever variation on the name you have on your system, such as NH_TEMP01a.dbf) and recreate it. The notation <fileName> indicates the full path and name of your temp file; for example, 'd:/oradata/eHealth/nh_temp01.dbf (including the single quotes). Also note that we recommend that you have a good database backup before performing the steps listed.

  1. Stop the eHealth server processes

    nhServer stop

  2. Start sqlplus as the sysdba

    If you are on a Unix server:
    sqlplus "sys/ehealth@$NH_DB_CONNECT_STRING as sysdba"

    If you are on a Windows server:
    sqlplus "sys/ehealth@%NH_DB_CONNECT_STRING% as sysdba"

  3. Enter the following commands from the SQL> prompt:

    alter database tempfile <fileName> offline;
    alter database tempfile <fileName> drop including datafiles;
    alter tablespace NH_TEMP add tempfile size 1000M reuse autoextend on next 100M;
    alter database tempfile <fileName> online;
    exit

  4. Stop the database

    nhStopDb immediate

  5. Start the database and eHealth server processes

    nhStartDb
    nhServer start