RMAN Backup Optimization

From 11G R1 onwards only undo data blocks containing undo data for not yet committed --- or rolled back --- transactions are backed up. Undo data in the undo tablespace still required to satisfy the undo retention but only containing undo records for already committed transactions are not backed up anymore. Nice if you have undo tablespaces of multiple Gb !!!

If backup optimization is configured ( see below ) then
1. Data files belonging to read only tablespaces are not backed up all the time. It is not needed anymore to use the SKIP READONLY clause. ( Note that the SKIP READONLY could not be used for RMAN backups of standby databases. )
2. Data files belonging to tablespaces which were offlined normal are not backed up all the time. It is not needed anymore to use the SKIP TABLESPACE clause.
3. The archivelog sequence is not backed up all the time. This is nice if you keep a large amount of archived redo logs on disk for whatever reason ( Logminer, Streams, Data Guard )

How to configure backup optimization ?

RMAN> configure backup optimization on;

new RMAN configuration parameters:
CONFIGURE BACKUP OPTIMIZATION ON;
new RMAN configuration parameters are successfully stored

What is the RMAN output when files are skipped during backup ?

RMAN> backup as compressed backupset database;

Starting backup at 23-JUN-09
using channel ORA_DISK_1
using channel ORA_DISK_2
skipping datafile 4; already backed up 2 time(s)
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=C:\ORACLE\PRODUCT\11.1.0\ORADATA\PLATINUM\DATAFILE\O1_MF_SYSAUX_0MK30K1T_.DBF
input datafile file number=00003 name=C:\ORACLE\PRODUCT\11.1.0\ORADATA\PLATINUM\DATAFILE\O1_MF_UNDOTBS1_0NK30K84_.DBF
input datafile file number=00006 name=C:\ORACLE\PRODUCT\11.1.0\ORADATA\PLATINUM\DATAFILE\O1_MF_USERS_ARCHIVE_02_0PK30K9S_.DBF
input datafile file number=00009 name=C:\ORACLE\PRODUCT\11.1.0\ORADATA\PLATINUM\DATAFILE\O1_MF_PM2_4ZM8LTBW_.DBF
input datafile file number=00011 name=C:\ORACLE\PRODUCT\11.1.0\ORADATA\PLATINUM\DATAFILE\O1_MF_CATALOG_53ZL1X82_.DBF

TRIGGER TO GET SERVERERROR ON DATABASE

Lets create a trigger that will log all the errors in the database:

It is better to create this trigger under SYS or DBA user's so that you don't have to grant all permission if you have to grant permissions separately.

--------------------------------------------------------
--  DDL for Trigger AFTER_ERROR
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER "AFTER_ERROR"
    AFTER SERVERERROR ON DATABASE
DECLARE
    pragma autonomous_transaction;
    id NUMBER;
    sql_text ORA_NAME_LIST_T;
    v_stmt CLOB;
    n NUMBER;
BEGIN
    SELECT oraerror_seq.nextval INTO id FROM dual; --- Seq no's
    --
    n := ora_sql_txt(sql_text);
    --
    IF n >= 1
    THEN
        FOR i IN 1..n LOOP
            v_stmt := v_stmt || sql_text(i);
        END LOOP;
    END IF;
    --
    FOR n IN 1..ora_server_error_depth
    LOOP
       IF ( ora_server_error(n) in (  '6512','25228','25254')
          AND ora_login_User in ('SYSMAN','DBSNMP') )
       THEN
           -- ignore this error
           NULL;
       ELSE
           INSERT INTO oraerror VALUES (id, sysdate, ora_login_user, ora_client_ip_address, ora_server_error(n), ora_server_error_msg(n),v_stmt);
           COMMIT;
       END IF;
    END LOOP;

/*  You can un comment this part to make this table auto clean on every insert. This will automatically cleanup the data in the table which is older than  30 days (you can change as per your requirement )
Delete from oraerror where log_date <=sysdate -30;
    commit; */
 --
END after_error;
/
ALTER TRIGGER "AFTER_ERROR" ENABLE;





Now create a seq needed for id generation :

--------------------------------------------------------
--  DDL for Sequence ORAERROR_SEQ
--------------------------------------------------------

   CREATE SEQUENCE  "ORAERROR_SEQ"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1040 CACHE 20 NOORDER  NOCYCLE ;

Table to capture err :


 CREATE TABLE "ORAERROR"
   (    "ID" NUMBER,
    "LOG_DATE" DATE,
    "LOG_USR" VARCHAR2(30 BYTE),
    "TERMINAL" VARCHAR2(50 BYTE),
    "ERR_NR" NUMBER(10,0),
    "ERR_MSG" VARCHAR2(4000 BYTE),
    "STMT" CLOB
   )  TABLESPACE ) ;

Find index skewed, rebuild
                                                

Summary
It is important to periodically examine your indexes to determine if they have become skewed and might need to be rebuilt. When an index is skewed, parts of an index are accessed more frequently than others. As a result, disk contention may occur, creating a bottleneck in performance.

The key column to decide index skewed is blevel. You must estimate statistics for the index or analyze index validate structure.

If the BLEVEL were to be more than 4, it is recommended to rebuild the index.

SELECT OWNER, INDEX_NAME, TABLE_NAME, LAST_ANALYZED, BLEVEL
FROM DBA_INDEXES 
WHERE OWNER NOT IN ('SYS', 'SYSTEM') 
AND BLEVEL >= 4
ORDER BY BLEVEL DESC;

Which SQL are doing a lot of disk I/O
                                                

Which SQL are doing a lot of disk I/O

SELECT * FROM 
   (SELECT  SUBSTR(sql_text,1,500) SQL,  
            ELAPSED_TIME, CPU_TIME, 
            disk_reads, executions,  
            disk_reads/executions "Reads/Exec", 
            hash_value,address   
     FROM V$SQLAREA   
     WHERE  ( hash_value, address ) IN (  
         SELECT DISTINCT HASH_VALUE, address 
           FROM v$sql_plan  
           WHERE DISTRIBUTION IS NOT NULL ) 
      AND disk_reads > 100
      AND executions > 0
     ORDER BY ELAPSED_TIME DESC) 
  WHERE ROWNUM <=30;

Disk I/O

Script

Datafiles Disk I/O

Tablespace Disk I/O

Which segments have top Logical I/O & Physical I/O

Which SQL are doing a lot of disk I/O

Which segments have top Logical I/O & Physical I/O
                                                

Summary
Do you know which segments in your Oracle Database have the largest amount of I/O, physical and logical? This SQL helps to find out which segments are heavily accessed and helps to target tuning efforts on these segments:

SELECT ROWNUM AS Rank, Seg_Lio.* FROM 
         (SELECT St.Owner, St.Obj#, St.Object_Type, St.Object_Name, St.VALUE, 'LIO' AS Unit
          FROM V$segment_Statistics St
         WHERE St.Statistic_Name = 'logical reads'
         ORDER BY St.VALUE DESC) Seg_Lio
 WHERE ROWNUM <= 10
UNION ALL
SELECT ROWNUM AS Rank, Seq_Pio_r.* FROM 
         (SELECT St.Owner, St.Obj#, St.Object_Type, St.Object_Name, St.VALUE, 'PIO Reads' AS Unit
          FROM V$segment_Statistics St
         WHERE St.Statistic_Name = 'physical reads'
         ORDER BY St.VALUE DESC) Seq_Pio_r
 WHERE ROWNUM <= 10
UNION ALL
SELECT ROWNUM AS Rank, Seq_Pio_w.* FROM 
         (SELECT St.Owner, St.Obj#, St.Object_Type, St.Object_Name, St.VALUE, 'PIO Writes' AS Unit
          FROM V$segment_Statistics St
         WHERE St.Statistic_Name = 'physical writes'
         ORDER BY St.VALUE DESC) Seq_Pio_w
 WHERE ROWNUM <= 10;

Tablespace Disk I/O
                                               

Summary
The Physical design of the database reassures optimal performance for DISK I/O. Storing the datafiles in different filesystems (Disks) is a good technique to minimize disk contention for I/O.

How I/O is spread per Tablespace

SELECT T.NAME, SUM(Physical_READS) Physical_READS, 
ROUND((RATIO_TO_REPORT(SUM(Physical_READS)) OVER ())*100, 2) || '%' PERC_READS, 
SUM(Physical_WRITES) Physical_WRITES, 
ROUND((RATIO_TO_REPORT(SUM(Physical_WRITES)) OVER ())*100, 2) || '%' PERC_WRITES, 
SUM(total) total, ROUND((RATIO_TO_REPORT(SUM(total)) OVER ())*100, 2) || '%' PERC_TOTAL 
FROM (SELECT ts#, NAME, phyrds Physical_READS, phywrts Physical_WRITES,
phyrds + phywrts total
FROM v$datafile df, v$filestat fs
WHERE df.FILE# = fs.FILE#
ORDER BY phyrds DESC ) A, sys.ts$ T
WHERE A.ts# = T.ts#
GROUP BY T.NAME 
ORDER BY Physical_READS DESC;