Oracle Database 23ai,19c — RMAN Progress Status Report

By -

 When performing a database restore using RMAN, Oracle provides progress information for each operation through the v$session_longops view. This allows DBAs to monitor the restore process and estimate its duration. For example:

set linesize 1000
col opname format a40
SELECT opname,
       totalwork,
       sofar AS procd_blocks,
       TIME_REMAINING,
       ROUND(sofar/totalwork*100,2) AS pct_done
FROM   v$session_longops
WHERE  opname LIKE 'RMAN%'
AND    totalwork > 0
AND    sofar <> totalwork;
OPNAME                                    TOTALWORK PROCD_BLOCKS TIME_REMAINING   PCT_DONE
---------------------------------------- ---------- ------------ -------------- ----------
RMAN: full datafile restore                67490026      3147741          26123       4.66

In this example, Oracle estimates that the restore will take over 26,000 seconds, and only 4.66% of the operation has been completed so far.

In Oracle versions 19c (possibly 19.28) and 23ai, if an RMAN operation runs longer than 900 seconds, Oracle automatically enables I/O statistics progress reporting. When this occurs, the following message is displayed:

I/O statistics progress reporting enabled with interval 900 seconds

After that, a progress report from v$session_longops is displayed on the screen, showing the status of each file being restored. Note that this report is not truly real-time; it is updated for each channel at 900-second intervals.

===================================================
RMAN Progress Status Report at 2025-08-15 21:16:37
===================================================
Progress for current step on channel: ORA_DISK_1 [INSTID = 1, SID = 1825, SERIAL = 62447, Polling channel id= 916]
-----------------------------------------------------------
Type  Total_Blocks  Procd_Blocks  Open_Time            status       setcount  File_Name
IN    0             606207        08/15/2025 21:01:32  IN PROGRESS  449       /backup/level_0_VAHIDxxx_e1416kch_449_1_1
OUT   4194302       276479        08/15/2025 21:01:33  IN PROGRESS  449       /oradata/datafile/o1_mf_users_n9yvqot4_.dbf
OUT   4194302       276479        08/15/2025 21:01:33  IN PROGRESS  449       /oradata/datafile/o1_mf_users_n9yvqols_.dbf
OUT   4194302       276479        08/15/2025 21:01:33  IN PROGRESS  449       /oradata/datafile/o1_mf_users_n9yvqo9o_.dbf
OUT   4194302       276543        08/15/2025 21:01:33  IN PROGRESS  449       /oradata/datafile/o1_mf_users_n9yvqo3w_.dbf
OUT   4194302       276991        08/15/2025 21:01:32  IN PROGRESS  449       /oradata/datafile/o1_mf_users_n9yvqnlr_.dbf
OUT   4194302       276991        08/15/2025 21:01:32  IN PROGRESS  449       /oradata/datafile/o1_mf_users_n9yvqnyv_.dbf
OUT   4194302       276991        08/15/2025 21:01:32  IN PROGRESS  449       /oradata/datafile/o1_mf_users_n9yvqntq_.dbf
OUT   4194302       276991        08/15/2025 21:01:32  IN PROGRESS  449       /oradata/datafile/o1_mf_users_n9yvqncg_.dbf
Total work = 33554416 blocks, blocks read/recovered= 2213944 blocks, Percentage = 6 %

After another 900 seconds, the report is updated:

===================================================
RMAN Progress Status Report at 2025-08-15 21:46:38
===================================================
Progress for current step on channel: ORA_DISK_1 [INSTID = 1, SID = 1825, SERIAL = 62447, Polling channel id= 916]
-----------------------------------------------------------
Type  Total_Blocks  Procd_Blocks  Open_Time            status       setcount  File_Name
IN    0             1812095       08/15/2025 21:01:32  IN PROGRESS  449       /backup/level_0_VAHIDxxx_e1416kch_449_1_1
OUT   4194302       858111        08/15/2025 21:01:33  IN PROGRESS  449       /oradata/datafile/o1_mf_users_n9yvqols_.dbf
OUT   4194302       858111        08/15/2025 21:01:33  IN PROGRESS  449       /oradata/datafile/o1_mf_users_n9yvqo9o_.dbf
OUT   4194302       858111        08/15/2025 21:01:32  IN PROGRESS  449       /oradata/datafile/o1_mf_users_n9yvqnyv_.dbf
OUT   4194302       858111        08/15/2025 21:01:32  IN PROGRESS  449       /oradata/datafile/o1_mf_users_n9yvqntq_.dbf
OUT   4194302       858111        08/15/2025 21:01:33  IN PROGRESS  449       /oradata/datafile/o1_mf_users_n9yvqo3w_.dbf
OUT   4194302       858111        08/15/2025 21:01:33  IN PROGRESS  449       /oradata/datafile/o1_mf_users_n9yvqot4_.dbf
OUT   4194302       858495        08/15/2025 21:01:32  IN PROGRESS  449       /oradata/datafile/o1_mf_users_n9yvqnlr_.dbf
OUT   4194302       858623        08/15/2025 21:01:32  IN PROGRESS  449       /oradata/datafile/o1_mf_users_n9yvqncg_.dbf
Total work = 33554416 blocks, blocks read/recovered= 6865784 blocks, Percentage = 20%
===================================================
RMAN Progress Status Report at 2025-08-15 22:01:38
===================================================
Progress for current step on channel: ORA_DISK_1 [INSTID = 1, SID = 1825, SERIAL = 62447, Polling channel id= 916]
-----------------------------------------------------------
Type  Total_Blocks  Procd_Blocks  Open_Time            status       setcount  File_Name
IN    0             2402303       08/15/2025 21:01:32  IN PROGRESS  449       
/backup/level_0_VAHIDxxx_e1416kch_449_1_1
OUT   4194302       1149951       08/15/2025 21:01:32  IN PROGRESS  449       /oradata/datafile/o1_mf_users_n9yvqnyv_.dbf
OUT   4194302       1149951       08/15/2025 21:01:33  IN PROGRESS  449       /oradata/datafile/o1_mf_users_n9yvqo3w_.dbf
OUT   4194302       1149951       08/15/2025 21:01:33  IN PROGRESS  449       /oradata/datafile/o1_mf_users_n9yvqot4_.dbf
OUT   4194302       1149951       08/15/2025 21:01:33  IN PROGRESS  449       /oradata/datafile/o1_mf_users_n9yvqols_.dbf
OUT   4194302       1149951       08/15/2025 21:01:33  IN PROGRESS  449       /oradata/datafile/o1_mf_users_n9yvqo9o_.dbf
OUT   4194302       1150207       08/15/2025 21:01:32  IN PROGRESS  449       /oradata/datafile/o1_mf_users_n9yvqntq_.dbf
OUT   4194302       1150463       08/15/2025 21:01:32  IN PROGRESS  449       /oradata/datafile/o1_mf_users_n9yvqnlr_.dbf
OUT   4194302       1150463       08/15/2025 21:01:32  IN PROGRESS  449       /oradata/datafile/o1_mf_users_n9yvqncg_.dbf
Total work = 33554416 blocks, blocks read/recovered= 9200888 blocks, Percentage = 27%

This report provides real-time visibility into the restore process, showing the total number of blocks for each file, how many have been processed, and the overall completion percentage.

Comments

Post a Comment

Popular posts from this blog

Oracle 23ai — Speed up IMPDP Using NOVALIDATE Constraints

By -
  When executing the   IMPDP   command, some operations do not support parallel execution. Creating constraints is one such operation. Oracle must validate the current data in the table before creating a constraint, which requires a full table scan for each validation. This process can be very time-consuming. Consider the following constraints: ALTER TABLE VAHID.MYTB ADD CONSTRAINT CHECK1 CHECK (col3 > 50 ); ALTER TABLE VAHID.MYTB ADD CONSTRAINT CHECK2 CHECK (col9 IN ( 5 , 15 )); We drop the  MYTB  table and recreate it using a dump file: SQL > drop table mytb; Table dropped. W -1 Processing object type TABLE_EXPORT / TABLE / TABLE_DATA W -1 . . imported "VAHID"."MYTB" 19 GB 337830912 rows in 83 seconds using external_table W -1 Processing object type TABLE_EXPORT / TABLE / CONSTRAINT / CONSTRAINT W -1 Completed 2 CONSTRAINT objects in 208 seconds As shown, creating the two constraints ta...
Read more

Staging Tables in Oracle 23ai

By -
  As you may know, using   staging tables   is a common practice in   Data Warehouse   environments, primarily during the data loading process into the   Warehouse Database . Typically, raw data received from   source databases   is first loaded into a temporary area, referred to as   staging tables . After cleansing inconsistent and invalid data and transforming it into a standard format, the cleaned data is stored in the final operational tables. In addition to initial data loading,   staging tables   are also used for updates and are often truncated before reuse. In   Oracle 23ai , a new minor feature has been introduced for this purpose, allowing users to create tables specifically designed as   staging tables . This is achieved by adding the   FOR STAGING   clause to the   CREATE TABLE   statement. Creating Staging Tables Using the   FOR STAGING   clause, staging tables can be created as ...
Read more

Boolean Data Type in Oracle ِDatabase 23ai

By -
Image
  Oracle introduced the Boolean data type in SQL with version 23ai (although this data type has existed in PL/SQL previously). With this feature, you can define table columns as Boolean during table creation. SQL > CREATE TABLE jadval1 (id NUMBER, is_accept BOOLEAN ); Table created You can also use the keyword  BOOL  instead of  BOOLEAN : SQL > CREATE TABLE jadval1 (id NUMBER, is_accept BOOL); Table created You can set default values for Boolean columns: SQL > CREATE TABLE table1 (id NUMBER, is_accept BOOL DEFAULT FALSE ); Table created To populate the  is_accept  column, you can use the three keywords:  TRUE ,  FALSE , and  NULL : SQL > insert into table1 values ( 1 , true ); 1 row inserted SQL > insert into table1 values ( 2 , null ); 1 row inserted SQL > insert into table1 values ( 3 , false ); 1 row inserted SQL > select * from table1; ID IS_ACCEPT ---------- ---------- ...
Read more