Sunday, 29 August 2021

Oracle RMAN backup expected time to complete

COL DBSIZE_MBYTES      FOR 99,999,990.00 JUSTIFY RIGHT HEAD "DBSIZE_MB"
COL INPUT_MBYTES       FOR 99,999,990.00 JUSTIFY RIGHT HEAD "READ_MB"
COL OUTPUT_MBYTES      FOR 99,999,990.00 JUSTIFY RIGHT HEAD "WRITTEN_MB"
COL OUTPUT_DEVICE_TYPE FOR A10           JUSTIFY LEFT HEAD "DEVICE"
COL COMPLETE           FOR 990.00        JUSTIFY RIGHT HEAD "COMPLETE %""
COL COMPRESSION        FOR 990.00        JUSTIFY RIGHT HEAD "COMPRESS|% ORIG"
COL EST_COMPLETE       FOR A20           HEAD "ESTIMATED COMPLETION"
COL RECID              FOR 9999999       HEAD "ID"
COL INSTANCE_NAME FOR A10;
COL HOST_NAME FOR A10;

SELECT RECID
     , OUTPUT_DEVICE_TYPE
     , DBSIZE_MBYTES
     , INPUT_BYTES/1024/1024 INPUT_MBYTES
     , OUTPUT_BYTES/1024/1024 OUTPUT_MBYTES
     , (OUTPUT_BYTES/INPUT_BYTES*100) COMPRESSION
     , (MBYTES_PROCESSED/DBSIZE_MBYTES*100) COMPLETE
     , TO_CHAR(START_TIME + (SYSDATE-START_TIME)/(MBYTES_PROCESSED/DBSIZE_MBYTES),'DD-MON-YYYY HH24:MI:SS') EST_COMPLETE
  FROM V$RMAN_STATUS RS
     , (SELECT SUM(BYTES)/1024/1024 DBSIZE_MBYTES FROM V$DATAFILE)
     , (SELECT NAME FROM V$DATABASE)
     , (SELECT INSTANCE_NAME,SUBSTR(HOST_NAME,1,10) FROM V$INSTANCE)
 WHERE STATUS='RUNNING'
   AND OUTPUT_DEVICE_TYPE IS NOT NULL;

No comments:

Post a Comment

Rename Oracle database using NID

  SQL> set pages 999 lines 999; col instance_name for a10; col host_name for a20; col startup_time for a30; set colsep | set underline = ...