Database Monitoring scripts

Below is the monitoring script which is very useful for monitoring the health of database.This script has no adverse affects. There are no DML or DDL actions taken.The script must be run as sys user. 

REM Monitoring and tuning script for Oracle databases all versions

REM This script has no adverse affects. There are no DML or DDL actions taken

REM Parts will not work in all versions but useful info should be returned from other parts

REM Uses anonymous procedures to avoid storing objects in the SYS schema

REM therefore this script must be run as sys 

REM calls to v$parameter need to be moved into subblocks to prevent NO_DATA_FOUND exceptions

REM parameter numbers are different between Oracle Versions

REM This daily script is a subset of the weekly script 

REM This script monitors only those things that might cause an application or database failure

REM and not all of those

REM For nicer formatting run the following in vi: %s/  *$//

REM This strips the trailing whitespace returned from oracle

set pause off

set verify off

set echo off

set term off

set heading off

REM Set up dynamic spool filename

spool C:\spool.sql
select 'spool C:\'||name||'_'||'daily'||'_'||to_char(sysdate,'yymondd')||'.txt'
from sys.v_$database;
spool off


set heading on
set verify on
set term on
set serveroutput on size 1000000
set wrap on
set linesize 200
set pagesize 1000


/*************************  START REPORT  **********************************/
/* Run dynamic spool output name */
@c:\spool.sql


set feedback off
set heading off


select 'Report Date: '||to_char(sysdate,'Monthdd, yyyy hh:mi')
from dual;


set heading on
prompt =============================================================
prompt .                      DATABASE (V$DATABASE) (V$VERSION)
prompt =============================================================
select NAME "Database Name",
CREATED "Created",
LOG_MODE "Status"
  from sys.v_$database;
select banner "Current Versions"
  from sys.v_$version;


prompt ============================================================
prompt .                      UPTIME (V$DATABASE) (V$INSTANCE)
prompt =============================================================


set heading off
column sttime format A30


SELECT NAME, ' Database Started on ',TO_CHAR(STARTUP_TIME,'DD-MON-YYYY "at" HH24:MI')
FROM V$INSTANCE, v$database;
set heading on
prompt .
prompt ==============================================================
prompt .                      SGA SIZE (V$SGA) (V$SGASTAT)
prompt ==============================================================
column Size format 99,999,999,999
select decode(name, 'Database Buffers',
'Database Buffers (DB_BLOCK_SIZE*DB_BLOCK_BUFFERS)',
'Redo Buffers',
'Redo Buffers     (LOG_BUFFER)', name) "Memory",
value "Size"
from sys.v_$sga
UNION ALL
select '------------------------------------------------------' "Memory",
to_number(null) "Size"
  from dual
UNION ALL
select 'Total Memory' "Memory",
sum(value) "Size"
  from sys.v_$sga;


prompt .
prompt .
prompt Current Break Down of (SGA) Variable Size
prompt ------------------------------------------


column Bytes format 999,999,999
column "% Used" format 999.99
column "Var. Size" format 999,999,999


select a.name "Name",
bytes "Bytes",
(bytes / b.value) * 100 "% Used",
b.value "Var. Size"
from sys.v_$sgastat a,
sys.v_$sga b
where a.name not in ('db_block_buffers','fixed_sga','log_buffer')
and b.name='Variable Size'
order by 3 desc;


prompt .
set feedback ON
declare
h_char          varchar2(100);
h_char2 varchar(50);
h_num1          number(25);
result1         varchar2(50);
result2         varchar2(50);


cursor c1 is
        select lpad(namespace,17)||': gets(pins)='||rpad(to_char(pins),9)||
                                     ' misses(reloads)='||rpad(reloads,9)||
               ' Ratio='||decode(reloads,0,0,to_char((reloads/pins)*100,999.999))||'%'
        from v$librarycache;


begin
    dbms_output.put_line('========================================');
    dbms_output.put_line('.  SHARED POOL: LIBRARY CACHE (V$LIBRARYCACHE)');
    dbms_output.put_line('========================================');
    dbms_output.put_line('.');
    dbms_output.put_line('.         Goal: The library cache ratio < 1%' );
    dbms_output.put_line('.');
 
    Begin
    SELECT 'Current setting: '||substr(value,1,30) INTO result1
    FROM V$PARAMETER
    WHERE NUM = 23;
    SELECT 'Current setting: '||substr(value,1,30) INTO result2
    FROM V$PARAMETER
    WHERE NUM = 325;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    h_num1 :=1;
    END;
    dbms_output.put_line('Recommendation: Increase SHARED_POOL_SIZE '||rtrim(result1));
    dbms_output.put_line('.                        OPEN_CURSORS '    ||rtrim(result2));
    dbms_output.put_line('.               Also write identical sql statements.');
    dbms_output.put_line('.');
     
    open c1;
    loop
fetch c1 into h_char;
exit when c1%notfound;

dbms_output.put_line('.'||h_char);
    end loop;
    close c1;


    dbms_output.put_line('.');


    select lpad('Total',17)||': gets(pins)='||rpad(to_char(sum(pins)),9)||
                                 ' misses(reloads)='||rpad(sum(reloads),9),
               ' Your library cache ratio is '||
                decode(sum(reloads),0,0,to_char((sum(reloads)/sum(pins))*100,999.999))||'%'
    into h_char,h_char2
    from v$librarycache;
    dbms_output.put_line('.'||h_char);
    dbms_output.put_line('.           ..............................................');
    dbms_output.put_line('.           '||h_char2);
    dbms_output.put_line('.');
end;
/


declare
        h_num1          number(25);
        h_num2          number(25);
        h_num3          number(25);
        result1         varchar2(50);


begin
    dbms_output.put_line  ('==========================================');
        dbms_output.put_line('.    SHARED POOL: DATA DICTIONARY (V$ROWCACHE)');
    dbms_output.put_line   ('==========================================');
        dbms_output.put_line('.');
        dbms_output.put_line('.         Goal: The row cache ratio should be < 10% or 15%' );
        dbms_output.put_line('.');
        dbms_output.put_line('.         Recommendation: Increase SHARED_POOL_SIZE '||result1);
        dbms_output.put_line('.');


        select sum(gets) "gets", sum(getmisses) "misses", round((sum(getmisses)/sum(gets))*100 ,3)
        into h_num1,h_num2,h_num3
        from v$rowcache;


        dbms_output.put_line('.');
        dbms_output.put_line('.             Gets sum: '||h_num1);
        dbms_output.put_line('.        Getmisses sum: '||h_num2);


        dbms_output.put_line('         .......................................');
        dbms_output.put_line('.        Your row cache ratio is '||h_num3||'%');


end;
/


declare
        h_char          varchar2(100);
        h_num1          number(25);
        h_num2          number(25);
        h_num3          number(25);
        h_num4          number(25);
        result1         varchar2(50);
begin
    dbms_output.put_line('.');
    dbms_output.put_line ('===============================================');
        dbms_output.put_line('.          BUFFER CACHE (V$SYSSTAT)');
    dbms_output.put_line ('===============================================');
        dbms_output.put_line('.');
        dbms_output.put_line('.         Goal: The buffer cache ratio should be > 70% ');
        dbms_output.put_line('.');
Begin
    SELECT 'Current setting: '||substr(value,1,30) INTO result1
    FROM V$PARAMETER
    WHERE NUM = 125;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    result1 := 'Unknown parameter';
END;
        dbms_output.put_line('.          Recommendation: Increase DB_BLOCK_BUFFERS '||result1);
        dbms_output.put_line('.');


        select lpad(name,15)  ,value
        into h_char,h_num1
        from v$sysstat
        where name ='db block gets';
        dbms_output.put_line('.         '||h_char||': '||h_num1);


        select lpad(name,15)  ,value
        into h_char,h_num2
        from v$sysstat
        where name ='consistent gets';
        dbms_output.put_line('.         '||h_char||': '||h_num2);


        select lpad(name,15)  ,value
        into h_char,h_num3
        from v$sysstat
        where name ='physical reads';
        dbms_output.put_line('.         '||h_char||': '||h_num3);


        h_num4:=round(((1-(h_num3/(h_num1+h_num2))))*100,3);


        dbms_output.put_line('.          .......................................');
        dbms_output.put_line('.          Your buffer cache ratio is '||h_num4||'%');


    dbms_output.put_line('.');
end;
/


declare
        h_char          varchar2(100);
        h_num1          number(25);
        h_num2          number(25);
        h_num3          number(25);


        cursor buff2 is
        SELECT name
                ,consistent_gets+db_block_gets, physical_reads
                ,DECODE(consistent_gets+db_block_gets,0,TO_NUMBER(null)
                ,to_char((1-physical_reads/(consistent_gets+db_block_gets))*100, 999.999))
        FROM v$buffer_pool_statistics;
begin
     dbms_output.put_line  ('==========================================');
        dbms_output.put_line('.  BUFFER CACHE (V$buffer_pool_statistics)');
    dbms_output.put_line   ('==========================================');


        dbms_output.put_line('.');
        dbms_output.put_line('.');
        dbms_output.put_line('Buffer Pool:         Logical_Reads     Physical_Reads        HIT_RATIO');
        dbms_output.put_line('.');


        open buff2;
        loop
            fetch buff2 into h_char, h_num1, h_num2, h_num3;
            exit when buff2%notfound;


   dbms_output.put_line(rpad(h_char, 15, '.')||'         '||lpad(h_num1, 10, ' ')||'         '||
    lpad(h_num2, 10, ' ')||'       '||lpad(h_num3, 10, ' '));


        end loop;
        close buff2;


    dbms_output.put_line('.');
end;
/


declare
        h_char          varchar2(100);
        h_num1          number(25);
        result1         varchar2(50);


        cursor c2 is
        select name,value
        from v$sysstat
        where name in ('sorts (memory)','sorts (disk)')
        order by 1 desc;


begin
  dbms_output.put_line  ('==============================================');
        dbms_output.put_line('.       SORT STATUS (V$SYSSTAT)');
dbms_output.put_line  ('==============================================');
        dbms_output.put_line('.');
        dbms_output.put_line('.         Goal: Very low sort (disk)' );
        dbms_output.put_line('.');
        BEGIN
    SELECT 'Current setting: '||substr(value,1,30) INTO result1
    FROM V$PARAMETER
    WHERE NUM = 320;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    result1 := 'Unknown parameter';
    END;
        dbms_output.put_line('           Recommendation: Increase SORT_AREA_SIZE '||result1);
        dbms_output.put_line('.');
        dbms_output.put_line('.');
        dbms_output.put_line(rpad('Name',30)||'Count');
        dbms_output.put_line(rpad('-',25,'-')||'     -----------');


        open c2;
        loop
        fetch c2 into h_char,h_num1;
        exit when c2%notfound;
                dbms_output.put_line(rpad(h_char,30)||h_num1);
        end loop;
        close c2;
end;
/


prompt .
prompt =============================================================
prompt .          TABLESPACE USAGE (DBA_DATA_FILES, DBA_FREE_SPACE)
prompt =============================================================
column Tablespace format a30
column Size format 999,999,999,999
column Used format 999,999,999,999
column Free format 999,999,999,999
column "% Used" format 999.99
select tablespace_name "Tablesapce",
        bytes "Size",
        nvl(bytes-free,bytes) "Used",
        nvl(free,0) "Free",
        nvl(100*(bytes-free)/bytes,100) "% Used"
  from(
select ddf.tablespace_name, sum(dfs.bytes) free, ddf.bytes bytes
FROM (select tablespace_name, sum(bytes) bytes
from dba_data_files group by tablespace_name) ddf, dba_free_space dfs
where ddf.tablespace_name = dfs.tablespace_name(+)
group by ddf.tablespace_name, ddf.bytes)
  order by 5 desc;


set feedback off
set heading off
select rpad('Total',30,'.') "Tablespace",
  sum(bytes) "Size",
        sum(nvl(bytes-free,bytes)) "Used",
        sum(nvl(free,0)) "Free",
        (100*(sum(bytes)-sum(free))/sum(bytes)) "% Used"
  from(
select ddf.tablespace_name, sum(dfs.bytes) free, ddf.bytes bytes
FROM (select tablespace_name, sum(bytes) bytes
from dba_data_files group by tablespace_name) ddf, dba_free_space dfs
where ddf.tablespace_name = dfs.tablespace_name(+)
group by ddf.tablespace_name, ddf.bytes);


set feedback on
set heading on


prompt .
prompt ========================================================
prompt .       FREE SPACE FRAGMENTATION (DBA_FREE_SPACE)
prompt ========================================================
column Tablespace format a30
column "Available Size" format 99,999,999,999
column "Fragmentation" format 99,999
column "Average Size" format 9,999,999,999
column "   Max" format 9,999,999,999
column "   Min" format 9,999,999,999
select tablespace_name Tablespace,
count(*) Fragmentation,
sum(bytes) "Available Size",
avg(bytes) "Average size",
max(bytes) Max,
min(bytes) Min
from dba_free_space
group by tablespace_name
order by 3 desc ;


prompt .
prompt ============================================================
prompt .      SUMMARY OF INVALID OBJECTS (DBA_OBJECTS)
prompt ============================================================


select owner, object_type, substr(object_name,1,30) object_name, status
from dba_objects
where status='INVALID'
order by object_type;


prompt .
prompt ============================================================
prompt .       LAST REFRESH OF SNAPSHOTS (DBA_SNAPSHOTS)
prompt ============================================================


select owner, name, last_refresh
from dba_snapshots
where last_refresh < (SYSDATE - 1);




prompt .
prompt ============================================================
prompt .               LAST JOBS SCHEDULED (DBA_JOBS)
prompt ============================================================


set arraysize 10
set linesize 65
col what format a65
col log_user format a10
col job format 9999
select job, log_user, last_date, last_sec, next_date, next_sec,
failures, what
from dba_jobs
where failures > 0;


set linesize 100


prompt .
prompt ============================================================
prompt .     ERROR- These segments will fail during NEXT EXTENT (DBA_SEGMENTS)
prompt ============================================================
column Tablespaces format a30
column Segment format a40
column "NEXT Needed" format 999,999,999
column "MAX Available" format 999,999,999
select a.tablespace_name "Tablespaces",
a.owner "Owner",
a.segment_name "Segment",
a.next_extent "NEXT Needed",
b.next_ext "MAX Available"
  from sys.dba_segments a,
(select tablespace_name,max(bytes) next_ext
from sys.dba_free_space
group by tablespace_name) b
 where a.tablespace_name=b.tablespace_name(+)
   and b.next_ext < a.next_extent;


prompt =============================================================
prompt .        WARNING- These segments > 70% of MAX EXTENT (DBA_SEGMENTS)
prompt =============================================================
column Tablespace format a30
column Segment format a40
column Used format 9999
column Max format 9999
select tablespace_name "Tablespace",
owner "Owner",
segment_name "Segment",
extents "Used",
max_extents "Max"
  from sys.dba_segments
 where (extents/decode(max_extents,0,1,max_extents))*100 > 70
   and max_extents >0;


prompt ============================================================
prompt .          LIST OF OBJECTS HAVING > 12 EXTENTS (DBA_EXTENTS)
prompt ============================================================
column Tablespace_ext format a30
column Segment format a40
column Count format 9999
break on "Tablespace_ext" skip 1
select tablespace_name "Tablespace_ext" ,
owner "Owner",
segment_name    "Segment",
count(*)        "Count"
  from sys.dba_extents
 group by tablespace_name,owner,segment_name
 having count(*)>12
 order by 1,3 desc;


prompt =============================================================
prompt End of Report


spool off
/* Remove  spool scripts */


Find the attached output of the above script  :  http://www.box.net/shared/zu755erl5s6nqg8am7nv


Enjoy     :-)

Profile in Oracle

Profiles are a means to limit resources a user can use.We can set up limits on the system resources used by setting up profiles with defined limits on resources.  Profiles are very useful in large, complex organizations with many users.  It allows us to regulate the amount of  resources used by each database user by creating and assigning profiles to users.

The limits resource can be categories into two parts  :  
1.) Kernel Limits
2.) Password Limits

1.) Kernel Limits : Kernel Resources covers the following options to limit the kernels resources.

• Composite_limits : it  specify the total resource cost for a session, expressed in service units. Oracle Database calculates the total service units as a weighted sum of CPU_PER_SESSION, CONNECT_TIME, LOGICAL_READS_PER_SESSION, and PRIVATE_SGA. composite_limit  <value | unlimited | default>

       e.g;    SQL> alter profile P1 LIMIT composite_limit 5000000;

• CONNECT_TIME : it specify the total elapsed time limit for a session, expressed in minutes. connect_time <value |  unlimited | default> . e.g ,

                  SQL> alter profile P1  LIMIT connect_time 600;

• CPU_PER_CALL : Specify the CPU time limit for a call (a parse, execute, or fetch), expressed in hundredths of seconds. cpu_per_call  < value |  unlimited | default > .  e.g;

                  SQL>  alter profile P1  LIMIT cpu_per_call 3000;

• CPU_PER_SESSION : Specify the CPU time limit for a session,expressed in hundredth of seconds. cpu_per_session   <value |  unlimited | default> .e.g ;

                  SQL>  alter profile  P1  LIMIT   cpu_per_session   UNLIMITED ; 

• IDLE_TIME : Specify the permitted periods of continuous inactive time during a session,expressed in minutes. Long-running queries and other operations are not subject to this limit . idle_time  <value | unlimited | default> . e.g;

                SQL>  alter profile  P1  LIMIT   idle_time  20 ; 

• LOGICAL_READS_PER_CALL : Specify the permitted number of data blocks read for a call to process a SQL statement (a parse, execute, or fetch). logical_reads_per_call  <value |  unlimited | default> .e.g;

                 SQL> ALTER PROFILE  P1  LIMIT logical_reads_per_call 1000;

• LOGICAL_READS_PER_SESSION : Specify the permitted number of data blocks read in a session, including blocks read from memory and disk. logical_reads_per_session <value | unlimited | default>  e.g;

               SQL> ALTER PROFILE  P1   LIMIT logical_reads_per_session UNLIMITED;

• PRIVATE_SGA : Specify the amount of private space a session can allocate in the shared pool of the system global area (SGA). private_sga  <value | unlimited | default>  Only valid with TP-monitor.  e.g;

               SQL> ALTER PROFILE  P1   LIMIT private_sga 15K ;

• SESSIONS_PER_USER : Specify the number of concurrent sessions to which you want to limit the user. sessions_per_user <value | unlimited | default>  e.g;

             SQL> ALTER PROFILE  P1   LIMIT sessions_per_user 1;

2 .)  Password Limits : Password Limits covers the following option to restrict the Password resources. Parameters that set lengths of time are interpreted in number of days .

• FAILED_LOGIN_ATTEMPTS : Specify the number of failed attempts to log in to the user account before the account is locked. failed_login_attempts <value | unlimited | default> e.g;

            SQL> ALTER PROFILE  P1  LIMIT failed_login_attempts 3;

--  to count failed log in attempts , fire the below command

             SQL> SELECT name, lcount FROM user$ WHERE lcount <> 0;

• PASSWORD_GRACE_TIME  :  The number of days during which a login is alowed but a  warning is issued. password_gracetime <value | unlimited | default> . e.g;

             SQL> ALTER PROFILE  P1  LIMIT password_grace_time 10;

• PASSWORD_LOCK_TIME  :  The number of days an account will be locked after the specified number of  consecutive failed login attempts defined by  FAILED_LOGIN_ATTEMPTS.    password_lock_time  <value | unlimited | default>  .  e.g;

               SQL> ALTER PROFILE  P1  LIMIT password_lock_time 30;

• PASSWORD_REUSE_MAX :Times a password can be reused .password_reuse_max  <value | unlimited | default> .  e.g;

          SQL> ALTER PROFILE  P1  LIMIT password_reuse_max 0 ; 

• PASSWORD_REUSE_TIME : Days between password reuses.password_reuse_time  <value | unlimited | default> .  e.g ; 

          SQL> ALTER PROFILE  P1  LIMIT password_reuse_time 0 ; 

To view existing profiles fire the below query :

SQL> select  profile, resource_name, limit FROM dba_profiles order by  profile, resource_name ; 

How to create the profile   :

SQL Create profile CREATE PROFILE <profile_name>  LIMIT

           <profile_item_name> <value>

           <profile_item_name> <value>

            ....;

e.g; 

SQL> create profile P1 limit 

 password_life_time 90

password_grace_time 15

password_reuse_time  0

password_reuse_max  0

failed_login_attempts  4

password__login_atempts  4

password_lock_time  2

cpu_per_call  2

private_sga 500K

logical_read_per_call   1000;

How to assign profile to user : we can assign the profile to user while creating the user or by altering the user. Here is demo to assign a profile to user .Say we have profile P1 and we assign to user ABC.

SQL> create user abc identified by abc 

default tablespace users  

 quota unlimited on users 

profile P1;

or  

SQL> alter user abc identified by abc  profile P1;

Enjoy     :-) 

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%' ; 

 SID    START_TIM   TOTALWORK  SOFAR          DONE          END_AT
 ----     ------------    --------------    ----------     -------------    -----------
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 :

SQL> SELECT  s.SID, p.SPID, s.CLIENT_INFO 
        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 ;
SID   SERIAL_ID    SESSION_S     ORA_USER  OS_USER     OS_PID      SES_PRGM
-----   ----------      --------------      ------------    -----------      ---------       ----------------                        
142         16          ACTIVE               SYS         Neerajs         8924         rman.exe
157          1           ACTIVE               SYS         Neerajs         10280       sqlplus.exe


Enjoy      :-)

Flashback Features in Oracle 10g

As I have cover the "Architecture Of Flashback"  in Oracle 10g  in my previous post. Here i am going further to explain and perform the some demo of the flashback  features of Oracle 10g.

How to check Flashback Status : 
Flashback status of a database can be checked from the below query and system parameters.

SQL> select NAME,FLASHBACK_ON from v$database ;

SQL> show parameter undo_retention
NAME                        TYPE                VALUE
---------------             ----------           -----------
undo_retention       integer               900

SQL> show parameter db_flashback_retention
NAME                                                    TYPE           VALUE
------------------------------------              --------          ---------
db_flashback_retention_target          integer          1440

SQL> show parameter   db_recovery_file_dest
NAME                                              TYPE              VALUE
----------------------------                ---------           -----------------------------------------------------
db_recovery_file_dest                 string             D:\oracle\product\10.2.0\flash_recovery_area
db_recovery_file_dest_size         big integer            5G

If the database Flashback feature is off then follow the below steps : 

1.) The Database must be started through SPFILE.

SQL> show parameter spfile
NAME                TYPE                       VALUE
---------           ---------             ----------------------------------------------
spfile                string               D:\ORACLE\PRODUCT\10.2.0\DB_1\ 
                                                DATABASE\SPFILENOIDA.ORA
2.) The Database must be in Archive log mode. 

SQL> shut immediate 
SQL> startup mount 
SQL> alter database archivelog ; 
SQL> alter database open ; 

3.) Undo management should be AUTO

SQL> show parameter undo_management
NAME                                TYPE            VALUE
--------------------              ---------        ----------
undo_management          string            AUTO

4.) Set the Recovery file destination or flashback area which will contain all flashback logs depending on the undo retention period

SQL> alter system set db_recovery_file_dest='D:\oracle\product\10.2.0\flash_recovery_area' scope=both;

System altered.

5.) Set the recovery file destination size. This is the hard limit on the total space to be used by target database recovery files created in the flash recovery area .

SQL> alter system set db_recovery_file_dest_size=5G scope=both;

System altered.

6.) Set the flash back retention target . This is the upper limit (in minutes) on how far back in time the database may be flashed back. How far back one can flashback a database depends on how much flashback data Oracle has kept in the flash recovery area.

SQL> alter system set db_flashback_retention_target=1440 scope=both;

System altered.

7.) Convert the Database to FLASHBACK ON state.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  830472192 bytes
Fixed Size                  2074760 bytes
Variable Size             213911416 bytes
Database Buffers          608174080 bytes
Redo Buffers                6311936 bytes
Database mounted.
SQL> ALTER DATABASE FLASHBACK ON ;
Database altered.
SQL> alter database open;
Database altered.

SQL> select NAME, FLASHBACK_ON   from   v$database  ;
NAME             FLASHBACK_ON
---------         ----------------------
NOIDA                YES

Flashback technology provides a set of features to view and rewind data back and forth in time. The flashback features offer the capability to query past versions of schema objects, query historical data, perform change analysis, and perform self-service repair to recover from logical corruption while the database is online.Here we will discuss some more features of  FlashBack .

The Flashback  features are : 

1.) Flashback Query
2.) Flashback Version Query
3.) Flashback Transaction Query
4.)Flashback Table
5.) Flashback Drop (Recycle Bin)
6.) Flashback Database
7.) Flashback Query Functions

1.) Flashback Query  :  Flashback Query allows the contents of a table to be queried with reference to a specific point in time, using the AS OF clause. Essentially it is the same as the DBMS_FLASHBACK functionality , but in a more convenient form. For example, Here is a Demo of Flashback Query  : 

SQL> CREATE TABLE flashback_query_test (id  NUMBER(10));
Table created.

SQL> SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;

CURRENT_SCN                 TO_CHAR(SYSTIMESTAM
-------------------               ------------------------------
    1365842                       2011-08-12 13:44:15

SQL> INSERT INTO flashback_query_test (id) VALUES (1);
1 row created.

SQL> commit;
Commit complete.

SQL> SELECT COUNT(*) FROM flashback_query_test;
  COUNT(*)
----------
         1

SQL> SELECT COUNT(*) FROM flashback_query_test AS OF TIMESTAMP TO_TIMESTAMP('2011-08-12 13:44:15', 'YYYY-MM-DD HH24:MI:SS');
  COUNT(*)
----------
         0

SQL> SELECT COUNT(*) FROM flashback_query_test AS OF SCN 1365842;
  COUNT(*)
----------
         0

2.)  Flashback Version Query :  Oracle Flashback Versions Query is an extension to SQL that can be used to retrieve the versions of rows in a given table that existed in a specific time interval. Oracle Flashback Versions Query returns a row for each version of the row that existed in the specified time interval. For any given table, a new row version is created each time the COMMIT statement is executed. Flashback version query allows the versions of a specific row to be tracked during a specified time period using the VERSIONS 

BETWEEN clause .  Here is  Demo  of   Flashback Version Query  :

SQL> CREATE TABLE flashback_version_query_test (id NUMBER(10),description  VARCHAR2(50));

Table created.

SQL> INSERT INTO flashback_version_query_test (id, description) VALUES (1, 'ONE');
1 row created.

SQL> COMMIT;
Commit complete.

SQL> SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;

CURRENT_SCN                 TO_CHAR(SYSTIMESTAMP)
------------------               ---------------------------------
    1366200                     2011-08-12 13:53:16

SQL> UPDATE flashback_version_query_test SET description = 'TWO' WHERE id = 1;
1 row updated.

SQL> COMMIT;
Commit complete.

SQL> UPDATE flashback_version_query_test SET description = 'THREE' WHERE id = 1;
1 row updated.

SQL> COMMIT;
Commit complete.

SQL> SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;

CURRENT_SCN                   TO_CHAR(SYSTIMESTAM
-------------------                 ---------------------------------
    1366214                          2011-08-12 13:54:38

SQL>SELECT versions_startscn, versions_starttime,  versions_endscn, versions_endtime,      versions_operation, description     FROM   flashback_version_query_test 
VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP('2011-08-12 13:53:11', 'YYYY-MM-DD HH24:MI:SS')      AND TO_TIMESTAMP('2011-08-12 13:54:38', 'YYYY-MM-DD HH24:MI:SS')
WHERE  id = 1;

VERSIONS_STARTSCN           VERSIONS_STARTTIME           VERSIONS_ENDSCN   VERSIONS_ENDTIME          VERSIONS_OPERATION            DESCRIPTION
1366212                                       12.08.11 13:53:35.000 U                                                 THREE
1366209                                12.08.11 13:53:35.000 1366212            12.08.11 13:53:35.000     
U                                                  TWO 
                                                                                            1366209  12.08.11 13:53:35.000                                                         ONE 
3 rows selected 


The available pseudocolumn meanings are:

• VERSIONS_STARTSCN or VERSIONS_STARTTIME - Starting SCN and TIMESTAMP when row took on this value. The value of NULL is returned if the row was created before the lower bound SCN or TIMESTAMP.
• VERSIONS_ENDSCN or VERSIONS_ENDTIME - Ending SCN and TIMESTAMP when row last contained this value. The value of NULL is returned if the value of the row is still current at the upper bound SCN ot TIMESTAMP.
• VERSIONS_XID - ID of the transaction that created the row in it's current state.
• VERSIONS_OPERATION - Operation performed by the transaction ( (I)nsert, (U)pdate or (D)elete) .

3.)  Flashback Transaction Query  :  Flashback transaction query can be used to get extra information about the transactions listed by flashback version queries. The VERSIONS_XID column values from a flashback version query can be used to query the FLASHBACK_TRANSACTION_QUERY view.

SQL> SELECT xid, operation, start_scn,commit_scn, logon_user, undo_sql    FROM   flashback_transaction_query   WHERE  xid = HEXTORAW('0600030021000000');

XID                                 OPERATION        START_SCN         COMMIT_SCN         LOGON_USER
---------------                    --------------       --------------           ----------------          --------------
UNDO_SQL
--------------          
0600030021000000        UPDATE             725208                 725209                   SCOTT                      
update  "SCOTT"."FLASHBACK_VERSION_QUERY_TEST" set  "DESCRIPTION" = 'ONE'   where ROWID = 'AAAMP9AAEAAAAAYAAA' ;
1 rows selected.

4.)  Flashback Table  :   There are two distinct table related flashback table features in oracle, flashback table which relies on undo segments and flashback drop which lies on the recyclebin not the undo segments.

Flashback table lets we recover a table to a previous point in time, we don't have to take the tablespace offline during a recovery, however oracle acquires exclusive DML locks on the table or tables that we are recovering, but the table continues to be online. When using flashback table oracle does not preserve the ROWIDS when it restores the rows in the changed data blocks of the tables, since it uses DML operations to perform its work, we must have enabled row movement in the tables that we are going to flashback, only flashback table requires we to enable row movement.  If the data is not in the undo segments then we cannot recover the table by using flashback table, however we can use other means to recover the table.

Restriction on flashback table recovery :  we cannot use flashback table on SYS objects we cannot flashback a table that has had preceding DDL operations on the table like table structure changes, dropping columns, etc The flashback must entirely exceed or it will fail, if flashing back multiple tables all tables must be flashed back or none. Any constraint violations will abort the flashback operation we cannot flashback a table that has had any shrink or storage changes to the table (pct-free, initrans and maxtrans. The following example creates a table, inserts some data and flashbacks to a point prior to the data insertion. Finally it  flashbacks to the time after the data insertion.Here is demo of the Flashback Table :

SQL> CREATE TABLE flashback_table_test (id  NUMBER(10));
Table created.

SQL> ALTER TABLE flashback_table_test ENABLE ROW MOVEMENT;
Table altered.

SQL> SELECT current_scn FROM v$database;
CURRENT_SCN
---------------
    1368791

SQL> INSERT INTO flashback_table_test (id) VALUES (1);
1 row created.

SQL> COMMIT;
Commit complete.

SQL> SELECT current_scn FROM v$database;
CURRENT_SCN
----------------
    1368802

SQL> FLASHBACK TABLE flashback_table_test TO SCN 1368791;
Flashback complete.

SQL> SELECT COUNT(*) FROM flashback_table_test;
  COUNT(*)
----------
         0

SQL> FLASHBACK TABLE flashback_table_test TO SCN 1368802;
Flashback complete.

SQL> SELECT COUNT(*) FROM flashback_table_test;
  COUNT(*)
-------------
         1

Flashback of tables can also be performed using timestamps.

SQL> FLASHBACK TABLE flashback_table_test TO TIMESTAMP TO_TIMESTAMP('2004-03-03 10:00:00', 'YYYY-MM-DD HH:MI:SS');

5.) Flashback Drop (Recycle Bin) :  Prior to Oracle 10g, a DROP command permanently removed objects from the database. In Oracle 10g, a DROP command places the object in the recycle bin. The extents allocated to the segment are not reallocated until we purge the object. we can restore the object from the recycle bin at any time.  This feature eliminates the need to perform a point-in-time recovery operation. Therefore, it has minimum impact to other database users.

In Oracle 10g the default action of a DROP TABLE command is to move the table to the recycle bin (or rename it), rather than  actually dropping it. The PURGE option can be used to  permanently drop a table.

The recycle bin is a logical collection of previously dropped objects, with access tied to the DROP privilege. The contents of the recycle bin can be shown using the SHOW RECYCLEBIN command and purged using the PURGE TABLE command. As a result, a previously dropped table can be recovered from the recycle bin.

Recycle Bin :   A recycle bin contains all the dropped database objects until :

• we permanently drop them with the PURGE command.we
•  recover the dropped objects with the UNDROP command.
• There is no room in the tablespace for new rows or updates to existing rows.
• The tablespace must be extended.
• We can view the dropped objects in the recycle bin from two dictionary views:

user_recyclebin   —    list all dropped user objects.
dba_recyclebin   —     list all dropped system-wide objects.

Here is  Demo  of Flashback Drop  :

SQL> CREATE TABLE flashback_drop_test (  2    id  NUMBER(10) ) ;
Table created.

SQL> INSERT INTO flashback_drop_test (id) VALUES (1) ;
1 row created.

SQL> COMMIT ;
Commit complete.

SQL> DROP TABLE flashback_drop_test ;
Table dropped.

SQL> SHOW RECYCLEBIN ;
ORIGINAL NAME                 RECYCLEBIN NAME                            OBJECT TYPE       DROPTIME
----------------------          ------------------------------------                -------------         ---------------
flashback_drop_test     BIN$KEZB6YXdRfW1925mCoGOlg==$0      table            201108:15:58:31EST

SQL> FLASHBACK TABLE flashback_drop_test TO BEFORE DROP;
Flashback complete.

SQL> SELECT * FROM flashback_drop_test;
        ID
----------
         1
If an object is dropped and recreated multiple times all dropped versions will be kept in the recycle bin, subject to space. Where multiple versions are present it's best to reference the tables via the recyclebin_name. For any references to the ORIGINAL_NAME  it is assumed the most recent object is drop version in the referenced question. During the flashback operation the table can be renamed.

FLASHBACK TABLE flashback_drop_test TO BEFORE DROP RENAME TO flashback_drop_test_old;

Several purge options exist :


PURGE TABLE tablename;                                          -- Specific table.
PURGE INDEX indexname;                                          -- Specific index.
PURGE TABLESPACE ts_name;                                    -- All tables in a specific tablespace.
PURGE TABLESPACE ts_name USER username;             -- All tables in a specific tablespace for a  specific user.
PURGE RECYCLEBIN;                                                -- The current users entire recycle bin.
PURGE DBA_RECYCLEBIN;                                          -- The whole recycle bin.

Several restrictions apply relating to the recycle bin : 

• Only available for non-system, locally managed tablespaces.
• There is no fixed size for the recycle bin. The time an object remains in the recycle bin can vary.
• The objects in the recycle bin are restricted to query operations only (no DDL or DML).
• Flashback query operations must reference the recycle bin name.
• Tables and all dependent objects are placed into, recovered and purged from the recycle bin at the same time.
• Tables with Fine Grained Access policies are not protected by the recycle bin.
• Partitioned index-organized tables are not protected by the recycle bin.
• The recycle bin does not preserve referential integrity .
• Flashback Database

6.) The FLASHBACK DATABASE   :    Flashback Database command is a fast alternative to performing an incomplete recovery. In order to flashback the  database we must have SYSDBA privilege and the flash recovery area must have been prepared in advance.The database can be taken back in time by reversing all work done sequentially. The database must be opened with resetlogs as if an incomplete recovery has happened. This is ideal if we have a database corruption (wrong transaction, etc) and require the database to be rewound before the corruption occurred. If we have media or a physical problem a normal recovery is required.

Flashback database is not enabled by default, when enabled flashback database a process (RVWR – recovery Writer) copies modified blocks to the flashback buffer. This buffer is then flushed to disk (flashback logs). Remember the flashback logging is not a log of changes but a log of the complete block images. Not every changed block is logged as this would be too much for the database to cope with, so only as many blocks are copied such that performance is not impacted. Flashback database will construct a version of the data files that is just before the time we want. The data files probably will be in a inconsistent state as different blocks will be at different SCN’s, to complete the flashback process, Oracle then uses the redo logs to recover all the blocks to the exact time requested thus synchronizing all the data files to the same SCN. Archiving mode must be enabled to use flashback database. An important note to remember is that Flashback can never reserve a change only to redo them.

The advantage in using flashback database is speed and convenience with which we can take the database back in time.we can use rman, sql and Enterprise manager to flashback a database. If the flash recovery area does not have enough room the database will continue to function but flashback operations may fail. It is not possible to flashback one tablespace, we must flashback the whole database. If performance is being affected by flashback data collection turn some tablespace flashbacking off .

we cannot undo a resized data file to a smaller size. When using ‘backup recovery area’ and ‘backup recovery files’ controlfiles , redo logs, permanent files and flashback logs will not be backed up.

SQL> CREATE TABLE flashback_database_test (id  NUMBER(10));
Table created.

SQL> conn / as sysdba
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount exclusive;
Database mounted.

SQL> FLASHBACK DATABASE TO TIMESTAMP SYSDATE-(1/24/12) ;         -----( 5 min back)
Flashback complete.

SQL> alter database open resetlogs ;
Database altered.

SQL> conn neer/neer@noida
Connected.
SQL> desc flashback_database_test
ERROR :  ORA-04043 : object flashback_database_test does not exist .

Some other variations of the flashback database command includes  :

• FLASHBACK DATABASE TO TIMESTAMP my_date ;
• FLASHBACK DATABASE TO BEFORE TIMESTAMP my_date;
• FLASHBACK DATABASE TO SCN my_scn;
• FLASHBACK DATABASE TO BEFORE SCN my_scn;  

The window of time that is available for flashback is determined by the db_flashback_retention_target parameter . The maximum  flashback can be determined by querying the v$flashback_database_log  view . It is only possible to flashback to a point in time after flashback was enabled on the database and since the last RESETLOGS command.

7.)Flashback Query Functions :  The TIMESTAMP_TO_SCN  and  SCN_TO_TIMESTAMP functions have been added to SQL and PL/SQL to simplify flashback operations.

SQL> selet  *  from emp as of scn timestamp_to_scn(systimestamp - 1/24) ;

SQL>select * from emp as of timestamp scn_to_timestamp(9945365);  

SQL> declare 
  l_scn              number ;
  l_timestamp   timestamp;
begin 
 l_scn              := timestamp_to_scn(systimestamp - 1/24);
 l_timestamp   := scn_to_timestamp(l_scn);
end ;
/


Enjoy      :-)