How to Monitor the Progress of an ASCII Database Save or Load in eHealth

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

SUMMARY

 

Creating an ASCII database backup with nhSaveDb, or restoring an ASCII backup with nhLoadDb, is a time consuming process that gives little indication of whether the process is running or in a hung state. Standard output to screen does not update until the process has completed or has failed, which can legitimately take several hours. Customers would like a way to determine progress on a backup or restore process.

 

INSTRUCTIONS

 

  1. While an ASCII backup or restore is in progress, open a new command line interface window.
     
  2. Start sqlplus

    In Windows:  sqlplus %NH_USER%/%NH_USER%@%NH_DB_CONNECT_STRING%
    In Solaris/Linux:  sqlplus $NH_USER/$NH_USER@$NH_DB_CONNECT_STRING
     
  3. Enter the following query to check progress of the backup or restore.

    SELECT sid, serial#, sofar, totalwork, round(sofar/totalwork*100,2) “% Complete”, dp.state 

    FROM gv$session_longops sl, gv$datapump_job dp 

    WHERE sl.opname = dp.job_name;

The table below shows an example of the output the query might display. 

SID

SERIAL#

SOFAR

TOTALWORK

% Complete

STATE

1026

26544

2081106

10752000

19.36

EXECUTING

1002

28050

102401569

269149445

38.05

EXECUTING

1037

16054

9125198

10854400

84.07

EXECUTING

962

18556

9226142

10854400

85

EXECUTING

 

 

Output will display each process, how far that process has gone out of total work to be completed, a simple percentage value of progress, and the current state of the process.