How to clear and recreate the NH_TEMP01 datafile.

Products

• CA eHealth

Issue/Introduction

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/[email protected]$NH_DB_CONNECT_STRING as sysdba"
   
   If you are on a Windows server:
   sqlplus "sys/[email protected]%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

Environment

Release: LHDEVC99000-6.3-eHealth-Live Health Upgrade Elements
Component: