Skip to main content

How to Monitor the Progress of (KBA4554)



KBA# 4554

To monitor the progress of the script.

There are several logs you can monitor when running the script. Which log(s) depends on the current stage the script is running. This article will focus on when the datafiles are actually being moved to ASM via RMAN. For instructions on how to run the script please see this product documentation.

The first log to check will be the move-to-asm.sh_<SID>_run<PID>.log to see where in the process the script is currently. This log is found in the working directory of the script. Example below:

Moving database DELPHIX_DB to ASM: started at Fri Sep  6 20:59:51 EDT 2019
    db_unique_name => DELPHIX_DB
    ORACLE_HOME => /u01/app/oracle/product/
    Datafile diskgroup => +DATA1
    Online redo diskgroup => +REDO1
    RMAN Channels => 8

Generate script to move tempfiles to ASM
Generate script to drop old tempfiles
Generate script to drop offline tablespaces
Generate script to make read-only tablespaces read-write
Make read-only tablespaces read-write
Remove offline tablespaces
Updating server parameter file with ASM locations
Move spfile to ASM
Move datafiles to ASM: started at Fri Sep  6 21:00:39 EDT 2019

From the example above we see that it has made it to the Move datafiles to ASM portion of the script, which is frequently the longest part of process and the stage of interest for this article. 

At this point you can log in to the database and run the following query to check the progress:

SELECT sid, opname, sofar, substr(to_char(sysdate,'dd:mon:yyyy hh24:mi:ss'),1,20) "Time Now", totalwork,time_remaining/60/60 "Time_To_Complete_Hours", round(sofar/totalwork*100,1) "%DONE", ELAPSED_SECONDS/60/60 "ELAPSED_TIME_HOURS"
FROM v$session_longops
WHERE opname LIKE 'RMAN: aggregate input%'
totalwork <> 0
sofar <> totalwork;
 SID    OPNAME                    SOFAR       Time Now            TOTALWORK       Time_To_Complete_Hours                       %DONE  ELAPSED_TIME_HOURS
---------- -------------------------------------------------------------------------- ------------------------------------------------------------
1138  RMAN: aggregate input   4579561088 10:sep:2019 12:54:12     5014002247               8.335                                91.3            87.8625

From the sample output above, we can tell that this part of the process is 91% complete with an expected completion time of 8.3 hours. Run the query over time to make sure it is progressing. 


Delphix-Only Internal Text

You can also provide the customer the following query if needed:

   p1, p2, p3
   v$process p, 
   v$session s
   p.addr = s.paddr
   client_info like 'rman channel=%';
SID     SPID      CLIENT_INFO            EVENT                SECONDS_IN_WAIT     P1    P2    P3
  1894  365377  rman channel=ORA_DISK_1  RMAN backup & recovery I/O    0  1      256   4294967295

The output from the query shows channel ORA_DISK_1 is running with 0 seconds_in_wait, which should mean it is processing the datafile. 

The move-to-asm.sh_<SID>_run<PID>.log log shows us the datafile in question:

channel ORA_DISK_1: starting datafile copy
input datafile file number=00282 name=/mnt/provision/DELPHIXDB/datafile/+DATA/DELPHIXDB/DATAFILE/undotbs11.1893.904163527

You can also check the size of the datafile above by looking in orcl_all_data_file in MDS. Sometimes customers are not aware of how large certain datafiles are. Once the datafile is complete you will see a message in the log like:

channel ORA_DISK_1: datafile copy complete, elapsed time: 38:37:10