DIFFERENTIAL & CUMULATIVE INCREMENTAL BACKUP

A differential backup, which backs up all blocks changed after the most recent incremental backup at level 1 or 0

A cumulative backup, which backs up all blocks changed after the most recent incremental backup at level 0

The only difference between a level 0 incremental backup and a full backup is that a full backup is never included in an incremental strategy. Thus, an incremental level 0 backup is a full backup that happens to be the parent of incremental backups whose level is greater than 0. When you are planning for the Incremental backup in your system. You have start with incremental level 0 then you can proceed with Differential & cumulative incremental backup

RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE;

NOTE: you should consider whether you want to spend more time on backup (or) restore/recovery. 

If you are going for the FASTER BACKUP è differential incremental backup, it will do backup quickly. Since it has to take the backup only from last level 1 backup (if no level 1, then it takes a level 0 backup).  But when you want to restore then you need to have all the differential level 1 backup and LEVEL 0 backup to restore.

If you are going for the FASTER RESTORE è cumulative incremental backup, it will little time in doing the backup. Since it will be taking the backup from the last level 0 backup(it will ignore even though you have the level 1 backup).  During the restore, it requires only one LEVEL 0 backup and last LEVEL 1 cumulative backup.

A level 1 incremental backup can be either of the following types:

èDifferential incremental backup, which backs up all blocks changed after the most recent incremental backup at level 1 or 0

RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

IF YOU ARE NOT SPECIFYING THE WORD CUMULATIVE, THEN ORACLE WILL TAKE IT AS A DIFFERENTIAL BACKUP

è Cumulative incremental backup, which backs up all blocks changed after the most recent incremental backup at level 0

RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;

How to enable auditing in oracle database?

Following are steps to enable auditing in oracle database
1) In the initialization parameter file add an entry
audit_trail=db
2) Shutdown the database.
3) startup the database using the init file.
4) After logging into the database at the SQL prompt run the following script
SQL>@/rdbms/admin/cataudit.sql
With the help of this script various views are created which will help you to monitor the auditing information:-
a) STMT_AUDIT_OPTION_MAP:-Contains information about auditing option type codes. Created by the SQL.BSQ script at CREATE DATABASE time.
b) AUDIT_ACTIONS:- Contains descriptions for audit trail action type codes
c) ALL_DEF_AUDIT_OPTS:- Contains default object-auditing options that will be applied when objects are created
d) DBA_STMT_AUDIT_OPTS:-Describes current system auditing options across the system and by user
e) DBA_PRIV_AUDIT_OPTS:-Describes current system privileges being audited across the system and by user
f) USER_OBJ_AUDIT_OPTS & DBA_AUDIT_TRAIL describes auditing options on all objects. USER view describes auditing options on all objects owned by the current user.
g) USER_AUDIT_TRAIL & USER_AUDIT_TRAIL:- Lists all audit trail entries. USER view shows audit trail entries relating to current user.
h) DBA_AUDIT_OBJECT & USER_AUDIT_OBJECT:-Contains audit trail records for all objects in the system. USER view lists audit trail records for statements concerning objects that are accessible to the current user.
i) DBA_AUDIT_SESSION & USER_AUDIT_SESSION:-Lists all audit trail records concerning CONNECT and DISCONNECT. USER view lists all audit trail records concerning connections and disconnections for the current user.
j) DBA_AUDIT_STATEMENT & USER_AUDIT_STATEMENT :- Lists audit trail records concerning GRANT, REVOKE, AUDIT, NOAUDIT, and ALTER SYSTEM statements throughout the database, or for the USER view, issued by the user
k) DBA_AUDIT_EXISTS:-Lists audit trail entries produced BY AUDIT NOT EXISTS
5) After completion of the script SQL prompt will return.
6)Run the following commands to enable the various auditing
a)SQL>audit create session;
b)SQL>audit create user;
c)SQL>audit drop user;
d)SQL>audit create user;
e)SQL>audit drop tablespace;
f)SQL>audit grant any role;
g)SQL>audit grant any privelege;
h)SQL>audit alter system;
i)SQL>audit alter session;
j)SQL>audit delete on AUD$ by access;
k)SQL>audit insert on AUD$ by access;
l)SQL>audit update on AUD$ by access;
CAUTION:-
As the table AUD$ which contains all the auditing data is created in the system tablespace,
so as the auditing information grows the size of the system tablespace also increases,
so it is advisable to move this particular table AUD$ to some another tablespace.
THE COMMAND TO MOVE THE TABLE IS:-
SQL>create table AUDX tablespace as select * from AUD$;
SQL>rename AUD$ to AUD$$;
SQL>rename AUDX to AUD$;
TO check whether AUD$ table has shifted to the new tablespace write the following query in the SQL prompt
SQL>select table_name,tablespace_name from dba_tables where table_name=’AUD$’;

 


_________________________________________________________________________________

If you want to audit a specific user run the following:

audit alter table, select table, insert table, update table, delete table, grant table, grant
procedure by USERNAME;

To stop audit for that user run:

noaudit alter table, select table, insert table, update table, delete table, grant table,
grant procedure by USERNAME;

To enable auditing for a specific object do:

AUDIT SELECT, INSERT, UPDATE, DELETE ON SCHEMA.TABLE;

To stop auditing:

NOAUDIT SELECT, INSERT, UPDATE, DELETE ON SCHEMA.TABLE;


To see the results:

SQL> select * from dba_audit_trail;

Custom Schemas Naming Convention in Oracle Applications.

Have we ever questioned ourselves, why custom schema names always start with ‘XX<some_name>‘? Before getting into the detail let’s check what happens if the custom schema name doesn’t start with ‘XX’.

If you don’t have a custom schema name starting with ‘XX’, be assured that this schema will be no longer available in our instance when we perform an UPGRADE. During upgrade Oracle apps will clear all unregistered schemas, which doesn’t start with ‘XX’.

Oracle had assured its customers that it would not touch any schemas that start with ‘XX’. This is the main reason why we find custom schema names starting with ‘XX’.