Thursday, August 18, 2011

Tracking Rman Backup

In Oracle 10g it is possible to track changed blocks using a change tracking file. Enabling change tracking does produce a small overhead, but it greatly improves the performance of incremental backups. The current change tracking status can be displayed by below query :

SQL> select  status from  v$block_change_tracking ; 

Change tracking is enabled using the ALTER DATABASE command.

SQL> alter database enable block change  tracking ;
The tracking file is created with a minumum size of 10M and grows in 10M increments. It's size is typically 1/30,000 the size of the datablocks to be tracked.Change tracking can be disabled using the following command.

SQL>alter database disable block change tracking ; 

We can track the Rman Job at session level done sofar.

SQL> select SID, START_TIME,TOTALWORK, sofar, (sofar/totalwork) * 100 done, 
          sysdate + TIME_REMAINING/3600/24 end_at  
          from     v$session_longops  
         where  totalwork > sofar   
         and     opname NOT LIKE '%aggregate%' 
         and     opname like 'RMAN%' ; 

 ----     ------------    --------------    ----------     -------------    -----------
142    18-AUG-11   138240         47230    34.1652199    18-AUG-11

We can also  track the Rman job status in running session by using below query :

        FROM    V$PROCESS p, V$SESSION s 
        WHERE   p.ADDR = s.PADDR 
       AND     CLIENT_INFO LIKE 'rman%';
SID      SPID              CLIENT_INFO
-----      ------         -------------------------------
142      8924       rman channel=ORA_DISK_1

Rman job status at Database Active session : 

SQL> SELECT  /**** session active users ****/ 
s.sid sid , s.serial# serial_id ,lpad(s.status,9) session_status , 
lpad(s.username,35) ora_user, lpad(s.osuser,12) os_user , 
lpad(p.spid,7) os_pid , s.program SES_PRGM , 
lpad(s.terminal,10) session_terminal ,lpad(s.machine,19) session_machine 
FROM v$process p , v$session s 
WHERE   p.addr (+) = s.paddr 
AND     s.status = 'ACTIVE' 
AND     s.username IS NOT NULL 
ORDER BY sid ;
-----   ----------      --------------      ------------    -----------      ---------       ----------------                        
142         16          ACTIVE               SYS         Neerajs         8924         rman.exe
157          1           ACTIVE               SYS         Neerajs         10280       sqlplus.exe

Enjoy      :-)

No comments: