Flashback Architecture In Oracle

Oracle Flashback Technology  is  a  group  of  Oracle  Database  features  that  let  us  view  past states  of  database  objects or  to  return  database  objects  to  a  previous  state  without  using point-in-time media  recovery. Flashback Database  is  a  part  of  the  backup &  recovery  enhancements  in  Oracle  10g Database that are called Flashback Features .                           

Flashback Database enables us to wind our entire database backward in time, reversing the effects of unwanted database changes within a given time window. The effects are similar to database point-in-time recovery.  It is similar to conventional point in time recovery in its effects, allowing us to return a database to its state at a time in the recent past.

Flashback Database can be used to reverse most unwanted changes to a database, as long as the datafiles are intact.  Oracle Flashback Database lets us quickly recover an Oracle database to a previous time to correct problems caused by logical data corruptions or user errors.

What are the Benefits?

According to many studies and reports, Human Error accounts for 30-35% of data loss episodes. This makes Human Errors one of the biggest single causes of downtime.  With Flashback Database feature Oracle is trying to fight against user and operator errors in an extremely fast and effective way. 

In most cases, a disastrous logical failure caused by human error can be solved by performing a Database Point-in-Time Recovery (DBPITR). Before 10g the only way to do a DBPITR was incomplete media recovery. Media Recovery is a slow and time-consuming process that can take a lot of hours. On the other side, by using of Flashback Database a DBPITR can be done in an extremely fast way: 25 to 105 times faster than usual incomplete media recovery and in result it can minimize the downtime significantly.

Flashback Database provides :

• Very effective way to recover from complex human errors.
• Faster database point-in-time recovery.
• Simplified management and administration .
• Little performance overhead .
• It provides a lot of benefits and almost no disadvantages.

The Flashback Database is not just our database “rewind” button. It is a “Time Machine” for our Database data that is one single command away from us.

The Flashback Database Architecture : 

Flashback Database uses its own type of log files, called Flashback Database Log Files. To support this mechanism, Oracle uses new background process called RVWR (Recovery Writer) and a new buffer in the SGA,  called Flashback Buffer.  The Oracle database periodically logs before images of data blocks in the flashback buffer. The flashback buffer records images of all changed data blocks in the database. This means that every time a data block in the database is altered, the database writes a before image of this block to the flashback buffer. This before image can be used to reconstruct a datafile to the current point of time.


The maximum allowed memory for the flashback buffer is 16 MB. We don’t have direct control on its size. The flashback buffer size depends on the size of the current redo log buffer that is controlled by Oracle. Starting at 10g R2, the log buffer size cannot be controlled manually by setting the initialization parameter LOG_BUFFER.



In 10G R2, Oracle combines fixed SGA area and redo buffer together. If there is a free space after Oracle puts the combined buffers into a granule, that space is added to the redo buffer. The sizing of the redo log buffer is fully controlled by Oracle. According to SGA and its atomic sizing by granules, Oracle will calculate automatically the size of the log buffer depending of the current granule size. For smaller SGA size and 4 MB granules, it is possible redo log buffer size + fixed SGA size to be multiple of the granule size. For SGAs bigger than 128 MB, the granule size is 16 MB. We can see current size of the redo log buffer, fixed SGA and granule by querying the V$SGAINFO view , and can query the V$SGASTAT view to display detailed information on the SGA and its structures.

To find current size of the flashback buffer, we can use the following query:
SQL> SELECT * FROM v$sgastat WHERE NAME = 'flashback generation buff';


There is no official information from Oracle that confirms the relation between 'flashback generation buff' structure in SGA and the real flashback buffer structure. This is only a suggestion. A similar message message is written to the alertSID.log file during opening of the database .   
Allocated 3981204 bytes in shared pool for flashback generation buffer Starting background process RVWR RVWR started with pid=16, OS id=5392 . 

RVWR writes periodically flashback buffer contents to flashback database logs. It is an asynchronous process and we don’t have control over it. All available sources are saying that RVWR writes periodically to flashback logs. The explanation for this behavior is that Oracle is trying to reduce the I/O and CPU overhead that can be an issue in many production environments.

Flashback log files can be created only under the Flash Recovery Area (that must be configured before enabling the Flashback Database functionality). RVWR creates flashback log files into a directory named “FLASHBACK” under FRA. The size of every generated flashback log file is again under Oracle’s control. According to current Oracle environment – during normal database activity flashback log files have size of 8200192 bytes. It is very close value to the current redo log buffer size. The size of a generated flashback log file can differs during shutdown and startup database activities. Flashback log file sizes can differ during high intensive write activity as well.

Flashback log files can be written only under FRA (Flash Recovery Area). FRA is closely related and is built on top of Oracle Managed Files (OMF). OMF is a service that automates naming, location, creation and deletion of database files. By using OMF and FRA, Oracle manages easily flashback log files. They are created with automatically generated names with extension .FLB. For instance, this is the name of one flashback log file: O1_MF_26ZYS69S_.FLB

By its nature flashback logs are similar to redo log files. LGWR writes contents of the redo log buffer to online redo log files, RVWR writes contents of the flashback buffer to flashback database log files. Redo log files contain all changes that are performed in the database, that data is needed in case of media or instance recovery. Flashback log files contain only changes that are needed in case of flashback operation. The main differences between redo log files and flashback log files are :

• Flashback log files are never archived - they are reused in a circular manner.
• Redo log files are used to forward changes in case of recovery while flashback log files are used to backward changes in case of flashback operation. 
• Flashback log files can be compared with UNDO data (contained in UNDO tablespaces) as well. While UNDO data contains changes at the transaction level, flashback log files contain UNDO data at the data block level. While UNDO tablespace doesn’t record all operations performed on the database (for instance, DDL operations), flashback log files record that data as well. In few words, flashback log files contain the UNDO data for our database.

To  Summarize   :

• UNDO data doesn’t contain all changes that are performed in the database while flashback logs contain all altered blocks in the database .
• UNDO data is used to backward changes at the transaction level while flashback logs are used to backward changes at the database level .

We can query the V$FLASHBACK_DATABASE_LOGFILE to find detailed info about our flashback log files. Although this view is not documented it can be very useful to check and monitor generated flashback logs.

There is a new record section within the control file header that is named FLASHBACK LOGFILE RECORDS. It is similar to LOG FILE RECORDS section and contains info about the lowest and highest SCN contained in every particular flashback database log file . 

***************************************************************************
FLASHBACK LOGFILE RECORDS
***************************************************************************
(size = 84, compat size = 84, section max = 2048, section in-use = 136,
last-recid= 0, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 139, numrecs = 2048)
FLASHBACK LOG FILE #1:
(name #4) E:\ORACLE\FLASH_RECOVERY_AREA\ORCL102\FLASHBACK\O1_MF_26YR1CQ4_.FLB
Thread 1 flashback log links: forward: 2 backward: 26
size: 1000 seq: 1 bsz: 8192 nab: 0x3e9 flg: 0x0 magic: 3 dup: 1
Low scn: 0x0000.f5c5a505 05/20/2006 21:30:04
High scn: 0x0000.f5c5b325 05/20/2006 22:00:38


What does a Flashback Database operation ?

When we perform a flashback operation, Oracle needs all flashback logs from now on to the desired time. They will be applied consecutively starting from the newest to the oldest.  For instance, if we want to flashback the database to SCN 4123376440, Oracle will read flashback logfile section in control file and will check for the availability of all needed flashback log files. The last needed flashback log should be this with Low scn and High scn values between the desired SCN 4123376440 .

In current environment this is the file with name: O1_MF_26YSTQ6S_.FLB and with values of:

Low SCN    : 4123374373
High SCN   : 4123376446

Note:  If we want to perform successfully a flashback operation we will always need to have available at least one archived (or online redo) log file. This is a particular file that contains redo log information about changes around the desired flashback point in time (SCN 4123376440). In this case, this is the archived redo log with name: ARC00097_0587681349.001 that has values of:

First change#: 4123361850
Next change#: 4123380675

The flashback operation will not succeed without this particular archived redo log. The reason for this : Flashback log files contain information about before-images of data blocks, related to some SCN (System Change Number). When we  perform flashback operation to SCN 4123376440, Oracle cannot apply all needed flashback logs and to complete successfully the operation because it applying before-images of data. Oracle needs to restore each data block copy (by applying flashback log files) to its state at a closest possible point in time before SCN 4123376440. This will guarantee that the subsequent “redo apply” operation will forward the database to SCN 4123376440 and the database will be in consistent state. After applying flashback logs, Oracle will perform a forward operation by applying all needed archive log files (in this case redo information from the file: ARC00097_0587681349.001) that will forward the database state to the desired SCN.


Oracle cannot start applying redo log files before to be sure that all data blocks are returned to their state before the desired point in time. So, if desired restore point of time is 10:00 AM and the oldest restored data block is from 09:47 AM then we will need all archived log files that contain redo data for the time interval between 09:47 AM and 10:00 AM. Without that redo data, the flashback operation cannot succeed.   When a database is restored to its state at some past target time using Flashback Database, each block changed since that time is restored from the copy of the block in the flashback logs most immediately prior to the desired target time. The redo log is then used to re-apply changes since the time that block was copied to the flashback logs.

Note  :  Redo logs must be available for the entire time period spanned by the flashback logs, whether on tape or on disk. (In practice, however, redo logs are generally needed much longer than the flashback retention target to support point-in-time recovery.)

Flashback logs are not independent. They can be used only with the redo data that contains database changes around the desired SCN. This means that if we want to have working flashback window (and to be able to restore the database to any point in time within this window) we need to ensure the availability of redo logs as well.  If we are familiar with this information then we will be able to work in a better way with this feature and to ensure that it will help us  to perform faster recovery without unexpected problems.

Rules for Retention and Deletion of Flashback Logs : 
The following rules govern the flash recovery area's creation, retention, overwriting and deletion of flashback logs:

• A flashback log is created whenever necessary to satisfy the flashback retention target, as long as there is enough space in the flash recovery area.
• A flashback log can be reused, once it is old enough that it is no longer needed to satisfy the flashback retention target.
• If the database needs to create a new flashback log and the flash recovery area is full or there is no disk space, then the oldest flashback log is reused instead.
• If the flash recovery area is full, then an archived redo log may be automatically deleted by the flash recovery area to make space for other files. In such a case, any flashback logs that would require the use of that redo log file for the use of FLASHBACK DATABASE are also deleted.

Note : Re-using the oldest flashback log shortens the flashback database window. If enough flashback logs are reused due to a lack of disk space, the flashback retention target may not be satisfied.

 Limitations  of  Flashback  Database :

• Since Flashback Database works by undoing changes to the datafiles that exist at the moment that we run the command, it has the following limitations:Flashback Database can only undo changes to a datafile made by an Oracle database. It cannot be used to repair media failures, or to recover from accidental deletion of datafiles.
• we cannot use Flashback Database to undo a shrink datafile operation.
• If the database control file is restored from backup or re-created, all accumulated flashback log information is discarded. We cannot use FLASHBACK DATABASE to return to a point in time before the restore or re-creation of a control file.

When using Flashback Database with a target time at which a NOLOGGING operation was in progress, block corruption is likely in the database objects and datafiles affected by the NOLOGGING operation. For example, if  we perform a direct-path INSERT operation in NOLOGGING mode, and that operation runs from 9:00 to 9:15 , and we later need to use Flashback Database to return to the target time 09:07 on that date, the objects and datafiles updated by the direct-path INSERT may be left with block corruption after the Flashback Database operation completes.

If possible, avoid using Flashback Database with a target time or SCN that coincides with a NOLOGGING operation. Also, perform a full or incremental backup of the affected datafiles immediately after any NOLOGGING operation to ensure recoverability to points in time after the operation. If we expect to use Flashback Database to return to a point in time during an operation such as a direct-path INSERT, consider performing the operation in LOGGING mode.


Finally few important point to be noted : 

• The Flashback Database should be part of our Backup & Recovery Strategy but it not supersedes the normal physical backup & recovery strategy. It is only an additional protection of our database data.
• The Flashback Database can be used to flashes back a database to its state to any point in time into the flashback window, only if all flashback logs and their related archived redo logs for the spanned time period are physically available and accessible.
• Always ensure that archived redo logs covering the flashback window are available on either the tape or disk.
• We cannot perform flashback database operation if we have media failure. In this case we must use the traditional database point-in-time media recovery method.
• Always write down the current SCN or/and create a restore point (10g R2) before any significant change over our database: applying of patches, running of batch jobs that can can corrupt the data, etc. As we know: The most common cause for downtime is change.
• Always write down the current SCN or/and create a restore point (10g R2) before to start a flashback operation .
• Flashback database is the only one flashback operation that can be performed to undone result of a TRUNCATE command (FLASHBACK DROP, FLASHBACK TABLE, or FLASHBACK QUERY cannot be used for this).
• Dropping of tablespace cannot be reversed with Flashback Database. After such an operation, the flashback database window begins at the time immediately following that operation.
• Shrink a datafile cannot be reversed with Flashback Database. After such an operation, the flashback database window begins at the time immediately following that operation.
• Resizing of datafile cannot be reversed with Flashback Database. After such an operation, the flashback database window begins at the time immediately following that operation. If we need to perform flashback operation in this time period, we must offline this datafile before performing of flashback operation.
• Recreating or restoring of control file prevents using of Flashback Database before this point of time.
• We can flashback database to a point in time before a RESETLOGS operation. This feature is available from 10g R2 because the flashback log files are not deleted after RESETLOGS operation. We cannot do this in 10g R1 because old flashback logs are deleted immediately after an RESETLOGS operation.
• Don’t exclude the SYSTEM tablespace from flashback logging. Otherwise we will not be able to flashback the database.
• The DB_FLASHBACK_RETENTION_TARGET parameter is a TARGET parameter. It doesn’t guarantee the flashback database window. Our proper configuration of the Flashback Database should guarantee it.
• Monitor regularly the size of the FRA and generated flashback logs to ensure that there is no space pressure and the flashback log data is within the desired flashback window

For Flashback Configuration and examples .Click Here


Reference :   http://docs.oracle.com/cd/B19306_01/backup.102/b14192/rpfbdb003.htm
                     http://dba-blog.blogspot.in/

Enjoy   :-) 

Resource Manager In Oracle

Manager (the Resource Manager) enables us to optimize resource allocation among the many concurrent database sessions.Although we can manage resources via profiles a better option is to use the resource manager, by creating resource plans which specify how much of our resources should go to the various consumer groups, we can prioritize users and jobs. The resource manager can kill long running jobs, switch jobs to higher priority all automatically, the DBA can also manually switch users between resource groups.

What Problems Does the Resource Manager Address?
When database resource allocation decisions are left to the operating system, we may encounter the following problems:

Excessive overhead  :  Excessive overhead results from operating system context switching between Oracle Database server processes when the number of server processes is high.

Inefficient scheduling  :  The operating system deschedules database servers while they hold latches, which is inefficient.

Inappropriate allocation of resources  :  The operating system distributes resources equally among all active processes and is unable to prioritize one task over another.

Inability to manage database-specific resources, such as parallel execution servers and active sessions

How Does the Resource Manager Address These Problems?

The Resource Manager helps to overcome these problems by allowing the database more control over how hardware resources are allocated. The Resource Manager enables us to classify sessions into groups based on session attributes, and to then allocate resources to those groups in a way that optimizes hardware utilization for our application environment.

With the Resource Manager, we can:

• Guarantee certain sessions a minimum amount of processing resources regardless of the load on the system and the number of users.
• Distribute available processing resources by allocating percentages of CPU time to different users and applications. In a data warehouse, a higher percentage can be given to ROLAP (relational online analytical processing) applications than to batch jobs.
• Limit the degree of parallelism of any operation performed by members of a group of users.
• Create an active session pool. An active session pool consists of a specified maximum number of user sessions allowed to be concurrently active within a group of users. The active session pool limits the total number of sessions actively competing for resources, thereby enabling active sessions to make faster progress.
• Manage runaway sessions or calls by detecting when they consume more than a specified amount of CPU or I/O. Such sessions can be automatically terminated or switched into a different (lower priority) group.
• Prevent the execution of operations that the optimizer estimates will run for a longer time than a specified limit.
• Limit the amount of time that a session can be idle. This can be further defined to mean only sessions that are blocking other sessions.

Example: A Simple Resource Plan

                     


There are four elements to the Database Resource Manager (DRM) :

1.) Resource Consumer Group   -  A resource consumer group is a collection of users with similar requirements for resource consumption. Users can be assigned to more than one resource consumer group, but each user's active session can only be assigned to one resource consumer group at a time. For example, I can construct separate consumer groups for OLTP users, one for decision support users and one for users who typically create ad hoc queries.

2.) Resource plan -  A resource plan can also have multiple subplans that specify a finer grain of resource allocation detail. For example, I can create two subplans for my OLTP users, one for high-volume users and one for lower-volume users. Note that a subplan can have more than one parent plan, but Oracle will not permit a subplan to "loop back" upon itself.

3.) Resource allocation method - dictates the specific method we choose to use to allocate resources like CPU.  

4.) Resource Plan Directive -  Resource plan directives allocate resources among the resource consumer groups in the resource plan. Essentially, directives connect resource consumer groups or subplans to their resource plans. There is at least one resource plan directive for each entry in the resource plan.

Resource plan directives use resource allocation methods to determine how the DRM will allocate resources to a resource consumer group or resource plan. Several allocation methods are available:

CPU  :  As its name implies, this method controls how much CPU utilization will be permitted to user sessions for different resource consumer groups within a specific resource plan. CPU utilization is parceled out in declining levels from 1 to 8, and percentages are used to assign how much CPU should be granted to each consumer group at each level. User sessions for a consumer group with the highest level will receive resources before sessions assigned to a lower level are allowed to receive resources, so this method provides a mechanism to insure the highest-priority sessions receive at least some CPU resources.

Automatic Consumer Group Switching :  In some circumstances, I would like to have a user session that has begun to consume too many resources get switched automatically to a lower priority (for example, an ad hoc query user starts a "not in our lifetime" query, or a user runs a large report during peak OLTP processing hours). Using this method, the DRM can be directed to automatically switch such a user session to a different resource consumer group if the session is actively consuming resources beyond a certain amount of time. Even more impressive, this method can estimate an approximate time for the completion of the query and switch the session to a different group before the query is executed.

Active Session Pool Queueing  :  In some cases, I would like to be able to limit the activity for some groups of users based upon their maximum number of active sessions. This method detects when a specified threshold of user sessions has been reached for a consumer group and queues sessions until a "slot" is available. This method can also detect if a queued task has exceeded a specified time threshold, automatically terminate the queued task and return an error.

Maximum Estimated Execution Time  :  The DRM can also be directed to terminate an operation that has exceeded a specified maximum execution time. DRM will terminate the session and return a trappable error.

Maximum Degree of Parallelism  :  The DRM can limit the maximum degree of parallel processing for any operation within the plan.

Undo Pool Quota  :  Finally, the DRM can be directed to monitor the amount of undo (i.e. rollback) space being used by a resource consumer group. When undo utilization reaches the specified threshold, the DRM will terminate the current DML generating the redo log entries; no other members of that consumer group will be allowed to continue DML operations until sufficient redo space is available.

For more detail and examples click the below scripts

http://www.datadisk.co.uk/html_docs/oracle/resource_manager.htm

http://www.rittmanmead.com/2010/01/oracle-database-resource-manager-and-obiee/

http://oradbpedia.com/wiki/DBMS_RESOURCE_MANAGER_-_Overview

http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/dbrm001.htm


Enjoy   :-)

Auditing in Oracle

Auditing is the monitoring and recording of selected user database actions. Auditing is normally used to:

1.) Investigate suspicious activity. For example, if an unauthorized user is deleting data from tables,the security administrator might decide to audit all connections to the database and all successful and unsuccessful deletions of rows from all tables in the database.

2.) Monitor and gather data about specific database activities. For example, the database administrator can gather statistics about which tables are being updated, how many logical I/Os are performed, or how many concurrent users connect at peak times.

There are two distinct types of auditing

1.) Standard        ->>  auditing based on statement, privilege and object-level
2.) Fine-grained  ->>  auditing on a finer granule which is based on content i.e. value > 10,000

Standard Auditing is further classified into 3 levels of standards auditing.The following are 

 i.)    Statement    ->>  audit all actions on any type of object.
ii.)   Privilege       ->>  audit actions that stem from system privileges.
iii.)  Object-level ->>  specific audit actions like select, update, insert or delete.

For all 3 levels of auditing we can choose to audit by access (audit every time we access) or by session (audit only once per access during the session), we can also auditing on if the access was successful (whenever successful) or not (whenever not successful).

How to eanable auditing : 
Below are some basic methods for auditing : 

1.) AUDIT_SYS_OPERATION : This parameter audits the SYS and SYSDBA connections. when we enable the parameter audit_sys_operations the database will write a trace file of the session action to the udump directory. This parameter should be enables on ALL production databases. Here is the method of enabling auditing .

SQL> alter system set audit_sys_operations=true scope=spfile;
SQL> shut immediate
SQL> startup
SQL> sho parameter audit
NAME                                TYPE                       VALUE
------------------                -----------            --------------------------------------------------------------
audit_file_dest                string               D:\ORACLE\PRODUCT\10.2.0\ADMIN\NOIDA\ADUMP
audit_sys_operations     boolean           TRUE
audit_trail                       string               DB
We can audit sys and sysdba connections from event logfile .(eventvwr.msc)


2.) AUDIT_TRAIL  Parameter : This parameter audit SESSION, DCL, DDL, DML, Select statements to table AUD$ or OS files.This is the easiest and most common method of auditing an oracle database, and this parameter should be set on ALL production databases. The audit_trail parameter can take the following values :

none  = disables auditing
os      = records the information to a o/s file (uses parameter audit_file_dest for the file location)
db      = records the information in the database, use the view dba_audit_trail ( view accesses table sys.aud$ )   to display audit information.
db, extended   = as per the db value but also populates the sqlbind and sqltext clob columns
xml     = audits to the o/s file but in xml format (uses parameter audit_file_dest for the file location)
xml,extended  =  as per xml option but also populates the sqlbind and sqltext clob columns


A basic list of audit option are as : 


==>Turn off auditing : 
SQL> alter system set audit_trail=none scope=spfile;


==> Auditing written to o/s :
SQL> alter system set audit_file_dest='c:\oracle\auditing';
SQL> alter system set audit_trail=os scope=spfile;


Note: if the audit_file_dest is not set then the default location is $oracle_home/rdbms/audit/


To start auditing we can use the below (there are many more options than stated below)
==> On Session    
SQL> audit session by neer;


==> On Table 
SQL> audit table;


==>Table and Specific user   
SQL>audit table by neer;


==>Table, Specific User and Access
SQL> audit table by neer by access;

==>Privilege auditing
SQL>audit create any table;

SQL>audit create any table by neer;


==>Object auditing
SQL> audit select on neer.employees by access whenever successful;

SQL> audit select on neer.employees by access whenever not successful;


==>Disabling audit
SQL>noaudit table;
SQL>noaudit all privileges;
SQL>noaudit create any table by neer;


==>Turn off all auditing
SQL> noaudit all;                              (turn off all statement auditing)

SQL> noaudit all privileges;               (turn off all privilege auditing)
SQL> noaudit all on default;               (turn off all object auditing)


==>Purge audit table
SQL>delete from sys.aud$;
SQL>truncate from sys.aud$;


Here is demo of the Standard auditting  :


C:\>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Sat Jul 23 13:58:26 2011
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options


SQL> alter system set audit_trail=db scope=spfile;   
System altered.


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


Total System Global Area      167772160  bytes
Fixed Size                             1247876      bytes
Variable Size                         75498876    bytes
Database Buffers                   83886080    bytes
Redo Buffers                         7139328      bytes
Database mounted.
Database opened.
SQL> create user test identified by test
  2  default tablespace users
  3  quota unlimited on users;
User created.


SQL> grant connect to test;
Grant succeeded.


SQL> grant create table,create procedure to test;
Grant succeeded.


SQL> audit select table,update table,insert table,delete table by test by access;
Audit succeeded.


SQL> audit all by test by access;
Audit succeeded.


SQL> conn test/test@noida
Connected.


SQL> create table aud_table(id number);
Table created.


SQL> insert into aud_table values (&A);
Enter value for a: 1
old   1: insert into aud_table values (&A)
new   1: insert into aud_table values (1)
1 row created.


SQL> insert into aud_table values (&A);
Enter value for a: 2
old   1: insert into aud_table values (&A)
new   1: insert into aud_table values (2)
1 row created.


SQL> /
Enter value for a: 3
old   1: insert into aud_table values (&A)
new   1: insert into aud_table values (3)
1 row created.


SQL> update aud_table set id=123 where id=2;
1 row updated.


SQL> select  *  from aud_table;
        ID
----------
         1
       123
         3


SQL> delete from aud_table;
3 rows deleted.


SQL> drop table aud_table;
Table dropped.


SQL> conn / as sysdba
Connected.


SQL> select username,obj_name,action_name from dba_audit_trail
  2       where owner='TEST' order by timestamp;


USERNAME              OBJ_NAME             ACTION_NAME
---------                   ---------------           -------------------
TEST                      AUD_TABLE              CREATE TABLE
TEST                      AUD_TABLE              INSERT
TEST                      AUD_TABLE              INSERT
TEST                      AUD_TABLE              INSERT
TEST                      AUD_TABLE              UPDATE
TEST                      AUD_TABLE              SELECT
TEST                      AUD_TABLE              DELETE
TEST                      AUD_TABLE              DROP TABLE
8 rows selected.

FGA (fine-grained auditing)  :  Fine-grain auditing (FGA) allows us to audit users accessing data of a certain criteria. As per standard auditing we can audit select, insert, update and delete operations. We use the package dbms_fga to add, remove, enable and disable FGA auditing policies, it is virtually impossible to bypass these policies, one nice feature is that we can attach handlers (like a trigger) to the policies which can execute procedures.

There are many options that can be applied to the dbms_fga package, here are some simple examples : 
==> Creating  :  


SQL> begin
dbms_fga.add_policy (
object_schema=>'neer',
object_name=>'employees',
policy_name=>'aud_test',
audit_column=>'salary,commission_pct',
enable=>false,
statement_types=>'select'); 
end;
/


==> Removing  :  


SQL> begin
dbms_fga.drop_policy (
object_schema=>'neer',
object_name=>'employees',
policy_name=>'aud_test');
end;
/


==>Enabling  :  


SQL>begin
dbms_fga.enable_policy (
object_schema=>'neer',
object_name=>'employees',
policy_name=>'aud_test');
end;
/


==> Disabling  :


SQL> begin
dbms_fga.edisable_policy (
object_schema=>'neer',
object_name=>'employees',
policy_name=>'aud_test');
end;
/

Here is Demo of the FGA Auditing  :  

First,we will create the FGA policy say "FGA_AUD"

SQL>  begin
  2  dbms_fga.add_policy (
  3  object_schema=>'test',
  4  object_name=>'hot',
  5  policy_name=>'FGA_AUD',
  6  audit_column=>'id',
  7  enable=>true,
  8  statement_types=>'select,update');
  9  end;
 10  /
PL/SQL procedure successfully completed.

Now we will perform some "select" and "update" statements to audit  :

SQL> update hot set id=2222 where id=123;
0 rows updated.

SQL> update hot set id=2222 where id=2;
1 row updated.

SQL> select * from hot;
        ID
   ----------
       22
      2222
      2342

SQL> update hot set id=8888 where id=2342;
1 row updated.

SQL> commit;
Commit complete.

To audit the table and the statements fired by db_user, fire the below query :

SQL> select   db_user  ,scn,  sql_text   from  dba_fga_audit_trail   where   db_user='TEST';
db_user         SCN                        SQL_TEXT
--------        ----------              ------------------------------------
TEST          1186412              update hot set id=2222 where id=123
TEST          1186421              update hot set id=2222 where id=2
TEST          1186431              select * from hot
TEST          1186440              update hot set id=8888 where id=2342

SYS.AUD$ table :  Make sure that the sys.aud$ table gets purged from time to time as connections and DML activity in the database might come to a stand still if it becomes full.


 Purge Audit Table : 
SQL> delete from sys.aud$;
SQL> truncate from sys.aud$;   

For Schemas level auditing check here :



Enjoy   :-) 

Create Read-only user for a Schema

Sometimes the DBA requires  to create the read-only user for a particular schemas.While creating the user,one should always keep in mind that the user should have minimum privileges.  There is no any direct command to create the read-only user in oracle.Sometimes I find people grants  “read any table ”  privileges to create the read-only  user ,which is not correct . Here is demo for creating read-only users. 

In this demo, I have created a user name “neer” and given  access to select only  “hr”  schemas tables.

Step  1  :  Create User  “NEER”

SQL> create user neer identified by neer;

User created.

Step  2  :  Grant session and create synonym privileges

SQL> grant create session ,create synonym to  neer ;

Grant succeeded.

Step  3  :  Make script to grant  select privileges to neer 

Here I have created the script with the help of spool  by selecting the tables of the HR schemas .

SQL> SPOOL  C:\select_privs.sql

SQL> select 'grant select on hr.'||table_name|| '  to neer;' from dba_tables where owner='HR';

SQL> select 'grant select on hr.'||view_name||  '  to neer;' from dba_views where owner='HR';

SQL> spool off

Now , we will check the spool  “select _privs.sql”  and prepare this as script for grant permission.

Step  4  :  Run the script to grant the permission

SQL> @C:\select_privs.sql

The  script  “select_privs.sql”  script  after modification  is .

grant select on hr.REGIONS to neer;                                             

grant select on hr.LOCATIONS to neer;                                           

grant select on hr.DEPARTMENTS to neer;                                         

grant select on hr.JOBS to neer;                                                

grant select on hr.EMPLOYEES to neer;                                           

grant select on hr.JOB_HISTORY to neer;                                         

grant select on hr.COUNTRIES to neer;                                                                

grant select on hr.EMP_DETAILS_VIEW to neer;                                    

  

Step  5  :  Create the synonym    

Connect to “NEER” schemas and create the synonym so that the “NEER” user can access the “HR”  schemas without any  dot(.)  like  “select * from employees”   instead of   “select * from HR.employees” .Here again we use the above method.

SQL> conn neer/neer@noida

SQL> SPOOL  C:\synonym_privs.sql

SQL> select 'create synonym '||view_name|| '  for HR.'||view_name||';' from all_views where owner='HR';

SQL> select 'create synonym '||table_name|| '  for HR.'||table_name||';' from all_tables where owner='HR';

SQL> spool off

Now we have the script to create the synonym

SQL>@ C:\synonym_privs.sql

Now we have successfully created a read-only user for a particular schemas.

Step  6  :   Check the tables

SQL> select Count(*) from employees ;

  COUNT(*)

----------

       107

SQL> select Count(*) from hr.employees;

  COUNT(*)

----------

       107

SQL> sho user

USER is "NEER"

Enjoy   :-) 

Five DBA best practices

I have read a very good post of Arup Nanda and wish to share with you all. The post was regarding the best practices of DBA in day to day life. The practices are :

1. ) Multiple Oracle Homes : 

My   favorite  best  practice   is  the  one  about  multiple  Oracle  Homes. Here  it  how  it g oes. When applying  a patch  or  a  patchset, I  recommend  against  applying  to  the  existing  Oracle  Home. Instead, I suggest  creating  a  new  Oracle  Home, and  apply  the  patches  there. I create the first Oracle  Home  at /app/oracle/db_1, for instance.  When a  patch comes  out, I i nstall the  whole  Oracle  software  in a different home -- /app/oracle/db_2 -- and   then apply the patch there. During the process of installation and patch application, the  database is up and running, as it runs off the home /db_1. When the outage window comes, all I have to   do is to shut down Oracle; change Oracle Home to db_2 and bring the database up. If there is a  problem, I can reset the Oracle Home back to the old one.

So, here is the conventional approach:
1.                 Shut down the database
2.                 Apply patch to the Oracle Home
3.                 Start the database
4.                 In case of problems:
5.                 Shut down the database
6.                 Roll back the patch
7.                 Start the database
Steps 2 and 6 could take as much as three hours depending on the amount of patching.


The database is down during these times. 
In the new approach:
1.                 Install new Oracle Home
2.                 Apply the patch to the new Home
3.                 Shut down the database
4.                 Change Oracle Home to the new location
5.                 Start the database
6.                 In case of problems:
7.                 Shut down the database
8.                 Change Oracle Home to the old one
9.                 Start the database

The database is down only during steps 4 and 8, which takes a couple of minutes at the most, not hours.

So, here are the advantages:

1.) The downtime is significantly reduced, to one 60th of the original time.

2. ) The risk is reduced significantly, as there is no rolling back a patch; you just go to the older version.

3.) You can perform a "diff" on these two homes to see what changed. You can see the differences across   multiple homes as well.

4.)You can take several databases running on the same server to the new Oracle Home one by one.

5.) You can see the various Oracle Homes and what patch level they are on using the inventory.

The only negative part is the space consumption -- you need space for two Oracle Homes. But considering a typical Oracle Home takes about 4 GB or less, this aspect of the suggestion is trivial.


2.) Set audit trail to DB : 

Set the audit trail to DB by placing the parameter AUDIT_TRAIL = DB in   the initialization parameter file during  the  database creation. Setting this parameter  does  not  start  the auditing, because an explicit AUDIT  command  must  be  given on the object. But the parameter must be set to a value other than FALSE (the default)  for the command to take  effect. Being a non-dynamic parameter, the database  must                      be bounced to change the value of AUDIT_TRAIL. To save the trouble and avoid an outage,                           always  set  the  value  to  DB, even  if  you  never intend  to audit a nything. It  does  not break anything  and you  will always be ready to audit when the time comes.

3.) Don't use.log : 

Don't use.log as the extension of redo logs. Someone   may run a script to remove all the log files assuming they are redundant and you will end up losing the online redo logs as well, forcing a database recovery. Instead, name them with extension "redo" or "rdo."

4.) Preview RMAN Restore : 

Preview  RMAN  Restore  to  identify  all the  various backup pieces that will be used in the recovery process without doing an actual recovery. This eliminates any surprises from missing pieces during an actual recovery process.

5.) Create a new Oracle user for clients running on the same server as the DB : 

The  Oracle  Database server  software  also contains  the  client  piece, which  allows the  clients  to  connect to the database on the same server. But  as  a best  practice  do  not  use  the  same  user or  the software; use  a new one. For instance, if "oracle"  is  the  user  to  install  Oracle  software,  create  a  new  user  called, say, "oraapp" and  install  the  client-only software using that user. The user "oraapp" should not be part  of  the  dba  or  the oinstall group; so this  user  can't  log  on  to  the  database  as sysdba. Create a new  group  called "appgrp" and assign  the user o raaap  to  this group. All the  application users on the box should also be part of the appgrp group. This way they can use the sqlplus, sqlldr and other executables on the server,  but  be  able  to  connect  a s sysdba.

The  common  practice  is  to  use  the  client  oftware  in  the  same  user  as  the database  software  owner; but s tarting  with 10.2, Oracle has changed  the  security  policy that takes  away the  global  execution permissions  from  the  Oracle Home. So the only option is  to  let  app  users  be  part  of  the  dba  group  or change the permissions on Oracle Home -- both make the database extremely vulnerable.

For more click here 

Enjoy   :-)