Monitoring Online ASM Datafile Moves

I’m moving a ~7TB database online as the development group that uses it doesn’t want to take any down time. Thankfully the database isn’t horribly active right now so moving the data files online isn’t causing a huge problem. The only other times I have moved data files online was in an incredibly busy database and it could take half a day per data file.

I don’t want them to continually bother me so I gave them a view that they could monitor the process through. Here are the caveat, I know when the move started, that is hard coded, also my volume names are easy to tell if they are on flash storage or spinning media as they are two different asm pools, so extracting the second character of the file name yields a ‘D’ or an ‘F’ where D is spinning media and F is flash media.

create or replace view storage_migration_status
                 'D', 'Spinning Media',
                 'F', 'Flash Media')
             AS Metric,
         TO_CHAR (ROUND (SUM (bytes) / 1024 / 1024 / 1024, 0) || ' GB')
             AS VALUE
    FROM v$datafile
GROUP BY SUBSTR (name, 2, 1)
UNION ALL
SELECT 'Average Transfer Speed'
           AS metric,
       TO_CHAR (
              ROUND (
                    SUM (bytes / 1024 / 1024 / 1024)
                  / (  24
                     * (  SYSDATE
                        - TO_DATE ('2019/01/04 10:30', 'yy/mm/dd HH:MI'))),
                  0)
           || ' GB/hr')
           AS VALUE
  FROM v$datafile
 WHERE SUBSTR (name, 2, 1) = 'F'
UNION ALL
SELECT 'Estimated time remaining'
           AS metric,
       TO_CHAR (
              ROUND (
                    SUM (bytes / 1024 / 1024 / 1024)
                  / (  SUM (bytes / 1024 / 1024 / 1024)
                     / (  24
                        * (  SYSDATE
                           - TO_DATE ('2019/01/04 9:00', 'yy/mm/dd HH:MI')))),
                  2)
           || ' Days')
           AS VALUE
  FROM v$datafile
 WHERE SUBSTR (name, 2, 1) = 'D'NION ALL
SELECT 'Average Transfer Speed'
           AS metric,
       TO_CHAR (
              ROUND (
                    SUM (bytes / 1024 / 1024 / 1024)
                  / (  24
                     * (  SYSDATE
                        - TO_DATE ('2019/01/04 10:30', 'yy/mm/dd HH:MI'))),
                  0)
           || ' GB/hr')
           AS VALUE
  FROM v$datafile
 WHERE SUBSTR (name, 2, 1) = 'F'
UNION ALL
SELECT 'Estimated time remaining'
           AS metric,
       TO_CHAR (
                SUM (bytes / 1024 / 1024 / 1024)
              / (ROUND (
                       SUM (bytes / 1024 / 1024 / 1024)
                     / (  24
                        * (  SYSDATE
                           - TO_DATE ('2019/01/04 9:00', 'yy/mm/dd HH:MI'))),
                     0))
           || ' Days')
           AS VALUE
  FROM v$datafile
 WHERE SUBSTR (name, 2, 1) = 'D'
/

Then they can check on the stats for themselves

SYS oradb1> select * from storage_migration_status;

METRIC                   VALUE
------------------------ ----------------------------------------------
Spinning Media           6451 GB
Flash Media              128 GB
Average Transfer Speed   99 GB/hr
Estimated time remaining 2.8 Days

It’s not pretty but it gets the job done, leaving me with less contact with people, which is great 🙂