Monitoring the recovery progress of Standard/Standby database using v$recovery_process

Here it is script to track recovery progress of your standard/standby database:

set linesize 255
set pagesize 60
col type format a30
col sofar_formatted format a20
col total_formatted format a15
col comments format a30

select to_char(start_time, 'dd-mon-rr hh24:mi:ss') start_time, type, item, units,
  sofar,
  case units
      when 'KB/sec' then round(sofar/1024,2) || ' MB/Sec'
      when 'Megabytes' then round(sofar/1024,2) || ' G'
      when 'Seconds' then to_char(cast(numtodsinterval(sofar, 'SECOND') as interval day(2) to second(2)), 'DD HH24:MI:SS')
      else to_char(sofar)
  end sofar_formatted,
  total,
    case units
      when 'KB/sec' then round(total/1024,2) || ' MB/Sec'
      when 'Megabytes' then round(total/1024,2) || ' G'
      when 'Seconds' then to_char(cast(numtodsinterval(total, 'SECOND') as interval day(2) to second(2)), 'DD HH24:MI:SS')
      else to_char(total)
  end total_formatted,
  comments
from v$recovery_progress;

Output:

set linesize 255
START_TIME                  TYPE                           ITEM                             UNITS                                 SOFAR SOFAR_FORMATTED           TOTAL TOTAL_FORMATTED COMMENTS
--------------------------- ------------------------------ -------------------------------- -------------------------------- ---------- -------------------- ---------- --------------- ------------------------------
11-feb-16 22:50:00          Media Recovery                 Log Files                        Files                                     8 8                             8 8
11-feb-16 22:50:00          Media Recovery                 Active Apply Rate                KB/sec                                 3316 3.24 MB/Sec                3316 3.24 MB/Sec
11-feb-16 22:50:00          Media Recovery                 Average Apply Rate               KB/sec                                  762 .74 MB/Sec                  762 .74 MB/Sec
11-feb-16 22:50:00          Media Recovery                 Maximum Apply Rate               KB/sec                                 9617 9.39 MB/Sec                9617 9.39 MB/Sec
11-feb-16 22:50:00          Media Recovery                 Redo Applied                     Megabytes                              1144 1.12 G                     1144 1.12 G
11-feb-16 22:50:00          Media Recovery                 Last Applied Redo                SCN+Time                                  0 0                             0 0               SCN: 6296684740127
11-feb-16 22:50:00          Media Recovery                 Active Time                      Seconds                                 214 +00 00:03:34.00             214 +00 00:03:34.00
11-feb-16 22:50:00          Media Recovery                 Apply Time per Log               Seconds                                  22 +00 00:00:22.00              22 +00 00:00:22.00
11-feb-16 22:50:00          Media Recovery                 Checkpoint Time per Log          Seconds                                   5 +00 00:00:05.00               5 +00 00:00:05.00
11-feb-16 22:50:00          Media Recovery                 Elapsed Time                     Seconds                                1537 +00 00:25:37.00            1537 +00 00:25:37.00

References

• Redo Processing
• V$RECOVERY_PROGRESS

Index Rebuild vs. Coalesce vs. Shrink Space

Index Rebuild vs. Coalesce vs. Shrink Space (Pigs – 3 Different Ones) contains demo Differences between a Coalesce, Shrink Space and Rebuild

ANALYZE INDEX bowie_stuff_i VALIDATE STRUCTURE;

SELECT height, blocks, lf_blks, br_blks, del_lf_rows, btree_space, pct_used FROM index_stats;

SELECT n.name, s.value FROM v$mystat s, v$statname n
WHERE s.statistic# = n.statistic# AND n.name = 'redo size';

ALTER INDEX bowie_stuff_i COALESCE;
-- or
ALTER INDEX bowie_stuff_i SHRINK SPACE COMPACT;
-- or
ALTER INDEX bowie_stuff_i SHRINK SPACE;
-- or
ALTER INDEX bowie_stuff_i REBUILD;
-- or
ALTER INDEX bowie_stuff_i REBUILD ONLINE NOLOGGING;

SELECT n.name, s.value FROM v$mystat s, v$statname n
WHERE s.statistic# = n.statistic# AND n.name = 'redo size';

ANALYZE INDEX bowie_stuff_i VALIDATE STRUCTURE;

SELECT height, blocks, lf_blks, br_blks, del_lf_rows, btree_space, pct_used FROM index_stats;

Restart QMon process

Event "Streams AQ: waiting for time management or cleanup tasks" in running state

SELECT event, state, COUNT(*) AS cnt, sum(seconds_in_wait) AS wait_in_seconds
FROM v$session_wait
WHERE event LIKE 'Streams AQ%'
GROUP BY event, state;

select * from GV$QMON_TASKS

select * from GV$QMON_SERVER_STATS

select isdefault from v$parameter where name='aq_tm_processes';

Restart QMon process

show parameter aq_tm_processes

alter system set aq_tm_processes = 0;

alter system set aq_tm_processes = 1;

show parameter aq_tm_processes

alter system reset aq_tm_processes scope=spfile;

Shrinking undo tablespace

Useful links

Reclaiming Unused Space in Datafiles

Undo tablespace

The simplest way to reclaim space from the undo tablespace is to create a new undo tablespace:

CREATE UNDO TABLESPACE undotbs2 DATAFILE '/u01/app/oracle/oradata/DB11G/undotbs02.dbf' SIZE 2G AUTOEXTEND ON NEXT 1M;

ALTER SYSTEM SET UNDO_TABLESPACE=undotbs2;

DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;

Undo segments in DBA_ROLLBACK_SEGS view

select owner, segment_name, tablespace_name, status
--select *
from dba_rollback_segs
 where 1 = 1
   --and tablespace_name='UNDOTBS1'
   --and status='ONLINE'
   --and segment_name ='_SYSSMU51_2438556163$'
;

Who holds UNDO tablespace

select  
   substr(a.os_user_name,1,15)   "OS User",
   substr(a.oracle_username,1,8) "DB User",
   substr(b.owner,1,8)           "Schema",
   substr(b.object_name,1,20)    "Object Name",
   substr(b.object_type,1,10)    "Type",
   substr(c.segment_name,1,15)   "RBS",
   substr(d.used_urec,1,12)      "# of Records",
   e.sid,
   e.serial#
from v$locked_object    a,
     dba_objects        b,
     dba_rollback_segs  c,
     v$transaction      d,
     v$session e
where  
   a.object_id =  b.object_id
 and
   a.xidusn    =  c.segment_id
and
   a.xidusn    =  d.xidusn
and
   a.xidslot   =  d.xidslot
and
   d.addr      =  e.taddr;

Issue: Drop UNDO including datafiles does not delete files at OS level

Find oracle process which hold file :

[root@hostname][/]:lsof | grep undo | grep deleted
oracle     3818     oracle  258u      REG               8,65 32421978112      17089 /u01/app/oracle/oradata/DB11G/undotbs01.dbf (deleted)

sqlplus / as sysdba
SQL> SELECT sid, serial#, process, terminal, program, event FROM v$session WHERE process in ('3818')

Look at this SID and it event, and find solution how to release hold of file by this session, for example I have issue with Restart QMon process

Solving event I/O and Cluster wait and freeing wasted space in LOBs

Issue: I/O and Cluster wait for LOBs

Sometimes in ADDM Report you can finding in "Top SQL Statements" following action:

Action
      Run SQL Tuning Advisor on the INSERT statement with SQL_ID 
      "2adh0x2quv6uh". Additionally, investigate this statement for possible 
      performance improvements. You can supplement the information given here 
      with an ASH report for this SQL_ID.
      Related Object
         SQL statement with SQL_ID 2adh0x2quv6uh.
         INSERT INTO USERINFO (SESSIONID, USERID, DICTIONARY, EXPIRES) 
         VALUES(:1 , :2 , :3 , :4)
   Rationale
      I/O and Cluster wait for LOB "SCOTT.SYS_LOB0000155472C00008$$" with 
      object ID 154473 consumed 60% of the database time spent on this SQL 
      statement.

Segment Advisor recommend shrink table SESSIONINFORMATION for reclaim space, but shrinking does not work for LOBs::

ALTER TABLE "SCOTT"."USERINFO" ENABLE ROW MOVEMENT
ALTER TABLE "SCOTT"."USERINFO" SHRINK SPACE

Solution

Reclaiming Wasted Space:

1. Use CASCADE option for shrink a table and all of its dependent segments (including LOB segments).
   
   When you specify COMPACT, Oracle Database defragments the segment space and compacts the table rows
   but postpones the resetting of the high water mark and the deallocation of the space
   until you reissue the SHRINK SPACE clause without the COMPACT clause during off-peak hours to complete
   

   ALTER TABLE SCOTT.USERINFO ENABLE ROW MOVEMENT;
   ALTER TABLE SCOTT.USERINFO SHRINK SPACE CASCADE COMPACT;
   ALTER TABLE SCOTT.USERINFO DISABLE ROW MOVEMENT;
   

2. Check free unused bytes, returns information about the position of the high water mark and the amount of unused space in a segment.

   ALTER TABLE SCOTT.USERINFO ENABLE ROW MOVEMENT;
   VARIABLE total_blocks NUMBER
   VARIABLE total_bytes NUMBER
   VARIABLE unused_blocks NUMBER
   VARIABLE unused_bytes NUMBER
   VARIABLE lastextf NUMBER
   VARIABLE last_extb NUMBER
   VARIABLE lastusedblock NUMBER
   exec DBMS_SPACE.UNUSED_SPACE('SCOTT', 'USERINFO', 'TABLE', :total_blocks, -
       :total_bytes,:unused_blocks, :unused_bytes, :lastextf, -
       :last_extb, :lastusedblock)
   print
   

3. Reissue the SHRINK SPACE clause without the COMPACT clause during off-peak hours to complete.
   

   ALTER TABLE SCOTT.USERINFO ENABLE ROW MOVEMENT;
   ALTER TABLE SCOTT.USERINFO SHRINK SPACE CASCADE;
   ALTER TABLE SCOTT.USERINFO DISABLE ROW MOVEMENT;
   

4. Check unused bytes again, script from step 2.
5. Issue deallocate unused space. Prior to deallocation, you can run the UNUSED_SPACE procedure, script from step 2.
   

   ALTER TABLE SCOTT.USERINFO  DEALLOCATE UNUSED;
   

6. Check unused bytes again, script from step 2

The LOBSEGMENT SYS_LOB0000155472C00008$$ has been reclaimed wasted space.

Resources

Oracle 10g New Features: Oracle Reclaiming Unused Space
Space, Object, and Transaction Management in Oracle Database 10g: Online Segment Shrink