Check tablespace usage

One of the most problem when monitoring team or dba wants to check tablespace usage is real space.

In fact in the most query the query shows free space base on amount of extended space and somebody has problem because you have to calculate that how much space is remaining on your tablespace of whole. I changed a script for getting to this purpose and also it supports bigfile. you can  use it below query o check your real free space of your tablespaces.

--if bigfile=no the each datafile=32 GB

--if bigfile=YES the each datafile=32768 GB

--and also  db_block_size=8192

SELECT *

  FROM (SELECT SUBSTR(c.contents, 1, 1) C,

               a.tablespace_name,

               a.alloc_gb,

               a.alloc_gb - nvl(b.free_gb, 0) used_gb,

               nvl(b.free_gb, 0) free_gb,

               

               a.file_count,

               CASE

                 WHEN (a.max_gb - a.alloc_gb) <= 0 THEN

                  ROUND((a.alloc_gb - nvl(b.free_gb, 0)) / a.alloc_gb * 100)

                 ELSE

                  ROUND((a.alloc_gb - nvl(b.free_gb, 0)) / a.max_gb * 100)

               END pct_used,

               

               case

                 when (c.bigfile = 'NO') then

                  (32 * a.file_count) - (a.alloc_gb - nvl(b.free_gb, 0))

                 when (c.bigfile = 'YES') then

                  (32768 * a.file_count) - (a.alloc_gb - nvl(b.free_gb, 0))

               end real_free_SIZE_GB

        

          FROM (SELECT tablespace_name,

                       COUNT(*) file_count,

                       ROUND(SUM(bytes) / 1024 / 1024 / 1024) alloc_gb,

                       ROUND(SUM(DECODE(maxbytes, 0, bytes, maxbytes) / 1024 / 1024 / 1024)) max_gb

                  FROM dba_data_files

                 GROUP BY tablespace_name) a,

               (SELECT tablespace_name,

                       ROUND(SUM(bytes) / 1024 / 1024 / 1024) free_gb

                  FROM dba_free_space

                 GROUP BY tablespace_name) b,

               dba_tablespaces c

         WHERE a.tablespace_name = b.tablespace_name(+)

           AND a.tablespace_name = c.tablespace_name

        

        UNION

        

        SELECT SUBSTR(c.contents, 1, 1) C,

               a.tablespace_name,

               a.alloc_gb,

               NVL(b.used_gb, 0) used_gb,

               a.alloc_gb - NVL(b.used_gb, 0) free_gb,

               

               a.file_count,

               CASE

                 WHEN (a.max_gb - a.alloc_gb) <= 0 THEN

                  ROUND(NVL(b.used_gb, 0) / a.alloc_gb * 100)

                 ELSE

                  ROUND(NVL(b.used_gb, 0) / a.max_gb * 100)

               END

               

               pct_used,

               

               case

                 when (c.bigfile = 'NO') then

                  (32 * a.file_count) - (used_gb)

                 when (c.bigfile = 'YES') then

                  (32768 * a.file_count) - (used_gb)

               end real_free_SIZE_GB

        

          FROM (SELECT tablespace_name,

                       COUNT(*) file_count,

                       ROUND(SUM(bytes) / 1024 / 1024 / 1024) alloc_gb,

                       ROUND(SUM(DECODE(maxbytes, 0, bytes, maxbytes) / 1024 / 1024 / 1024)) max_gb

                  FROM dba_temp_files

                 GROUP BY tablespace_name) a,

               (SELECT tablespace tablespace_name,

                       ROUND(SUM(blocks * b.VALUE) / 1024 / 1024 / 1024, 2) used_gb

                  FROM v$tempseg_usage a, v$parameter b

                 WHERE b.name = 'db_block_size'

                 GROUP BY tablespace) b,

               dba_tablespaces c

         WHERE a.tablespace_name = b.tablespace_name(+)

           AND a.tablespace_name = c.tablespace_name)

 ORDER BY 7 DESC NULLS LAST, 2

Drop datafiles from a tablespace

This is an important question that I have heard a lot these days. The question is how we can remove a data file from a tablespace in oracle and what is the solution for doing this issue  and It is possible or not.I should say please concentrate on this subject that when you add a datafile in a specific tablespace Oracle distribute the extends on all datafiles for improving I/O, therefore be careful If you remove a datafile you will lost your data.What is the solution If we want to drop some datafiles from a tablespace?

Create a new tablespace in your database and move all of your segments on it for example Table, Index, MVW, Partititon and Subpartitions.

--Move Table

ALTER TABLE <TABLE NAME to be moved> MOVE TABLESPACE <destination TABLESPACE NAME>;

--Move Index

select 'alter index '||owner||'.'||index_name||' rebuild tablespace TO_TABLESPACE_NAME;' from all_indexes where owner='OWNERNAME';

--Move Partition Index

ALTER INDEX <index_name> REBUILD PARTITION <partition_name> TABLESPACE <new_tablespace> NOLOGGING;

--Move Partition

alter table owner.your_table move partition p1 tablespace TBS4 update global indexes;

in 12c:

alter table owner.your_table move partition p1 tablespace TBS4 online;

--Move LOBs:

SELECT 'ALTER TABLE <schema_name>.'||LOWER(TABLE_NAME)||' MOVE LOB('||LOWER(COLUMN_NAME)||') STORE AS (TABLESPACE <table_space>);'

FROM DBA_TAB_COLS

WHERE OWNER = '<schema_name>' AND DATA_TYPE like '%LOB%';

ALTER TABLE SCOT.bin$6t926o3phqjgqkjabaetqg==$0 MOVE LOB(calendar) STORE AS (TABLESPACE USERS);

After that you move all your segments you can drop old tablespace and rename new tablespace with old tablespace name.

Calculate kernel parameters for running Oracle 11g Release 2 on RHEL Linux

Values required by Oracle according to the documentation

The following tables list the required MINIMUM values

Calculate the required values

The following table shows calculation formulas for setting the required kernel parameters for running oracle 11g release 2 on Linux.

The shell limits shown above should be enough initially. However if you have many data files (> 1000) i would increase the number of open files by doubling the value. All other parameters are sized big enough for almost every environment.

For using the new Automatic Memory Management Feature which automatically sized SGA and PGA Oracle uses a pseudo file system /dev/shm. If activating this feature by setting MEMORY_MAX_SIZE or MEMORY_TARGET in your init.ora you have to size /dev/shm appropriately. The following table outlines how to do it:

Flashback Archive data for keep history of all changes (FDA)

Flashback Data Archive provides the automated ability to track and store all transactional changes to a table over its lifetime without having to build this intelligence into your application.Flashback Data Archive uses a background process fbda to capture data asynchronously.

Creating a Flashback Data Archive (lets call it FBDA)

·  Create a new tablespace or use existing tablespace – tablespace needs to be ASSM

·  Specify the FBDA as the default (optional)

·  Assign a quota for the FBDA (optional)

·  Assign a retention period for the FBDA

·  Retention period integer denoting days,months or years

·  Enable flashback archive for a specific table via the CREATE TABLE or ALTER TABLE clause. By default it is turned off.    

SQL> create tablespace historical_data_01;

Tablespace created.

----------------------------------------------------------------------

SQL>  create flashback archive fba1 tablespace historical_data_01 retention 2 DAY;

Flashback archive created.

----------------------------------------------------------------------

SQL> select flashback_archive_name,to_char(create_time,'dd-mon-yyyy') created,retention_in_days,status from dba_flashback_archive;

                FLASHBACK_ARCHIVE_NAME            CREATED                       RETENTION_IN_DAYS  STATUS

    ---------------------- --------         -----------------          ----------------------     -------------------------------------   ------------------

                          FBA1                                      28-nov-2015                                                                2          

----------------------------------------------------------------------

SQL> grant flashback archive on fba1 to sys;

SQL> create table vahid(id number,name varchar2(50));

SQL>  insert into vahid(id,name) values(1,'Alex');

1 row created.

SQL> commit;

 ----------------------------------------------------------------------

SQL> alter table vahid flashback archive fba1;

----------------------------------------------------------------------

SQL> select table_name ,flashback_archive_name,archive_table_name,status from user_flashback_archive_tables;

            TABLE_NAME FLASHBACK_ARCHIVE_NAME          ARCHIVE_TABLE_NAME          STATUS

    ----------  ----------------------  -------------------   --------

      VAHID                FBA1                      SYS_FBA_HIST_221804      ENABLED

----------------------------------------------------------------------

 SQL>insert into vahid(id,name) values(2,'Anjel');

  commit;

 ----------------------------------------------------------------------

SQL>  select to_char(sysdate,'dd-mm-yyyy hh24:mi:ss') date_system from dual;

            DATE_SYSTEM

    -------------------------------

            28-11-2015 14:45:51

----------------------------------------------------------------------

 SQL>update vahid set name='vahid' where ID=2;

  commit;

 ----------------------------------------------------------------------

  SQL> select * from vahid;

            ID         NAME

             1            Alex

             2           vahid

 ----------------------------------------------------------------------

SQL> select * from vahid as of timestamp to_timestamp('28-11-2015 14:45:00','dd-mm-yyyy hh24:mi:ss');

            ID         NAME

             1          Alex

              2        Anjel

Now you can see history of changing record with ID=2.

 ----------------------------------------------------------------------

For dropping flashback archive  :

SQL> drop flashback archive fba1;

SQL> drop tablespace historical_data_01;

What Is the Recycle Bin

When you drop a table, the database does not immediately remove the space associated with the table. The database renames the table and places it and any associated objects in a recycle bin.
It can be recovered at a later time. This feature is called Flashback Drop.

What Is the Recycle Bin?

The recycle bin is actually a data dictionary table containing information about dropped objects. Dropped tables and any associated objects such as indexes, constraints, nested tables, and the likes are not removed and still occupy space.

Enabling and Disabling the Recycle Bin

ALTER SESSION SET recyclebin = OFF;

ALTER SYSTEM SET recyclebin = OFF;

ALTER SESSION SET recyclebin = ON;

ALTER SYSTEM SET recyclebin = ON;

For example:

1-

create user vahid identified by vahid account unlock;

grant connect , resource to vahid;

--------------------------------------------------------------------

2-

create table vahid.TBL_UNCOMIITED_TRN2(id number,name varchar2(30));

insert into vahid.TBL_UNCOMIITED_TRN2 values(1,'test1');

commit;

create index IDX_ID_PK on vahid.TBL_UNCOMIITED_TRN2(ID);

--------------------------------------------------------------------

3-

select * from vahid.TBL_UNCOMIITED_TRN2;

--------------------------------------------------------------------

4-

drop table vahid.TBL_UNCOMIITED_TRN2;

--------------------------------------------------------------------

5-

select object_name,original_name,type,can_undrop as "undo",can_purge as "PUR",droptime 

       from dba_recyclebin where original_name like 'TBL_UNCOMIITED_TRN2%' order by droptime;

  OBJECT_NAME                     ORIGINAL_NAME    TYPE undo  PUR   DROPTIME

BIN$JZgAyoU+u1DgUxQVCApelQ==$0 TBL_UNCOMIITED_TRN2  TABLE YES YES   2015-11-28:13:39:27

select * from vahid."BIN$JZgAyoU+u1DgUxQVCApelQ==$0";

--------------------------------------------------------------------

6-

flashback table vahid."BIN$JZgAyoU+u1DgUxQVCApelQ==$0" to before drop;

--------------------------------------------------------------------

7-

select * from vahid.TBL_UNCOMIITED_TRN2;

--------------------------------------------------------------------

8-

--Restoring Dependent objects

select object_name,original_name,type from recyclebin;

select index_name from dba_indexes where table_name='TBL_UNCOMIITED_TRN2';

    INDEX_NAME

          -----------------

 BIN$JZgAyoU9u1DgUxQVCApelQ==$0

--Restore the original name of the index as follow: 

alter index "BIN$JZgAyoU9u1DgUxQVCApelQ==$0" rename to IDX_ID_PK;

--------------------------------------------------------------------

If you decide that you are never going to restore an item from the recycle bin, you can use the PURGE statement to remove the items and their associated objects from the recycle bin and release their storage space.

PURGE TABLE JZgAyoU9u1DgUxQVCApelQ==$0;

Users can purge the recycle bin of their own objects, and release space for objects

PURGE RECYCLEBIN;

set password life time to unlimited in Oracle

Error code: ORA-28032

Description: Your password has expired and the database is set to read-only

Cause:
Your password expired and needs to be changed before your login request can be processed. Because the database is currently open in read-only mode, the password change operation cannot be performed on this database.

How to modify password life time in oracle for resolving above error. The following statement modifies the profile:

Action:
Log in to the primary database to change your password, then retry your login request at the standby database.

SQL> ALTER PROFILE DEFAULT LIMIT password_life_time UNLIMITED;

What is a Checkpoint?

A Checkpoint is a database event which synchronizes the modified data blocks in memory with the datafiles on disk and CKPT process updates the control file and datafile headers with last SCN.

A checkpoint has two purposes: (1) to establish data consistency, and (2) enable faster database recovery.   How is recovery faster?  Because all database changes up to the checkpoint have been recorded in the datafiles, making it unnecessary to apply redo log entries prior to the checkpoint. The checkpoint must ensure that all the modified buffers in the cache are really written to the corresponding datafiles to avoid the loss of data which may occur with a crash (instance or disk failure).

Oracle writes the dirty buffers to disk only on certain conditions:
  - A shadow process must scan more than one-quarter of the db_block_buffer parameter.
  - Every three seconds.
  - When a checkpoint is produced.

A checkpoint is realized on five types of events:
  - At each switch of the redo log files.
  - When the delay for LOG_CHECKPOINT_TIMEOUT is reached.
  - When the size in bytes corresponding to :
     (LOG_CHECKPOINT_INTERVAL* size of IO OS blocks) is written on the current redo log file.
  -  Directly by the ALTER SYSTEM SWITCH LOGFILE command.
  - Directly with the ALTER SYSTEM CHECKPOINT command.

During a checkpoint the following occurs:
 -  The database writer (DBWR) writes all modified database  blocks in the buffer cache back to datafiles,
 -  Log writer (LGWR) updates both the controlfile and  the datafiles to indicate when the last checkpoint  occurred (SCN)

Various types of checkpoints  in Oracle :

• Full checkpoint
• Thread checkpoint
• File checkpoint
• Parallel Query checkpoint
• Object checkpoint
• Log switch checkpoint
• Incremental checkpoints

ORA-20000: Unable to analyze TABLE ' ' , insufficient privileges or does not exist

When you want to gather stat of a table and oracle throw ORA-20000:

begin 

dbms_stats.gather_table_stats(

ownname=> 'your_username',

tabname=> 'jbm' ,

estimate_percent=> DBMS_STATS.AUTO_SAMPLE_SIZE,

cascade=> DBMS_STATS.AUTO_CASCADE,

degree=> null,

no_invalidate=> DBMS_STATS.AUTO_INVALIDATE,

granularity=> 'AUTO',

method_opt=> 'FOR ALL COLUMNS SIZE AUTO');

end;

ERROR at line 1:

ORA-20000: Unable to analyze TABLE "your_username"."jbm", insufficient

privileges or does not exist

ORA-06512: at "SYS.DBMS_STATS", line 24270

ORA-06512: at "SYS.DBMS_STATS", line 24332

ORA-06512: at line 2

SQL> grant analyze any to your_username;

SQL> exec dbms_stats.unlock_table_stats(ownname => 'your_username',tabname => 'jbm');

SQL> begin 

dbms_stats.gather_table_stats(

ownname=> 'your_username',

tabname=> 'jbm' ,

estimate_percent=> DBMS_STATS.AUTO_SAMPLE_SIZE,

cascade=> DBMS_STATS.AUTO_CASCADE,

degree=> null,

no_invalidate=> DBMS_STATS.AUTO_INVALIDATE,

granularity=> 'AUTO',

method_opt=> 'FOR ALL COLUMNS SIZE AUTO');

end;

/

PL/SQL procdure successfully completed.

Flash/Fast Recovery Area (FRA) in Oracle

The flash recovery area is the most powerful tool available from Oracle 10g, that plays a vital role in performing database backup & recovery operations. From Oracle 11g release2, flash recovery area is called as fast recovery area.

Flash Recovery Area can be defined as a single, centralized, unified storage area that keep all the database backup & recovery related files and performs those activities in Oracle databases.

Unified Backup Files Storage, all backup components can be stored in one consolidated spot. The flash recovery area is managed via Oracle Managed Files (OMF), and it can utilize disk resources managed by Automatic Storage Management (ASM). Flash recovery area can be configured for use by multiple database instances.

Automated Disk-Based Backup and Recovery, once the flash recovery area is configured, all backup components are managed automatically by Oracle.

Automatic Deletion of Backup Components, once backup components have been successfully created, RMAN (Recovery Manager) can be configured to automatically clean up files that are no longer needed (thus reducing risk of insufficient disk space for backups).

Disk Cache for Tape Copies, if your disaster recovery (DR) plan involves backing up to alternate media, the flash recovery area can act as a disk cache area for those backup components that are eventually copied to tape.

Flashback Logs, the FRA is also used to store and manage flashback logs, which are used during flashback backup operations to quickly restore a database to a prior desired state.

You can designate the FRA as the location for one of the control files and redo log members to limit the exposure in case of disk failure.

In case of a media failure or a logical error, the flash recovery area is referred to retrieve all the files needed to recover a database.

Following are the various entities that can be considered as FRA:

File System:
1. A single directory
2. An entire file system

Raw Devices:
1. Automatic storage management (ASM)

FRA Components

The flash/fast recovery area can contain the following:

• Control files: During database creation, a copy of the control file is created in the flash recovery area.
• Online redologs: Online redologs can be kept in FRA.
• Archived log files: During the configuration of the FRA, the LOG_ARCHIVE_DEST_10 parameter in init.ora file is automatically set to the flash recovery area location. Archived log files are created by ARCn processes in the flash recovery area location and the location defined by LOG_ARCHIVE_DEST_n.
• Flashback logs: Flashback logs are kept in the flash recovery area when flashback database is enabled.
• Control file and SPFILE backups: The flash recovery area also keeps the control file and SPFILE backups, which is automatically generated by Recovery Manager (RMAN) only if RMAN has been configured for control file autobackup.
• Datafile copies: The flash recovery area also keeps the datafile copies.
• RMAN backup sets: The default destination of backup sets and image copies generated by RMAN is the flash recovery area.

Notes:

• The FRA is shared among databases in order to optimize the usage of disk space for database recovery operations.
• Before any backup and recovery activity can take place, the Flash Recovery Area must be set up. The flash recovery area is a specific area of disk storage that is set aside exclusively for retention of backup components such as datafile image copies, archived redo logs, and control file auto backup copies.
• RMAN also transfers the restored archive files from tape to the flash recovery area in order to perform recovery operations.

Configuring FRA
Following are the three initialization parameters that should be defined in order to set up the flash recovery area:

• DB_RECOVERY_FILE_DEST_SIZE
• DB_RECOVERY_FILE_DEST
• DB_FLASHBACK_RETENTION_TARGET

DB_RECOVERY_FILE_DEST_SIZE specifies the total size of all files that can be stored in the Flash Recovery Area. The size of the flash recovery area should be large enough to hold a copy of all data files, all incremental backups, online redo logs, archived redo log not yet backed up on tape, control files, and control file auto backups.

SQL> ALTER SYSTEM SET db_recovery_file_dest_size = 10g SCOPE = BOTH;DB_RECOVERY_FILE_DEST parameter is to specify the physical location where all the flash recovery files are to be stored. Oracle recommends that this be a separate location from the datafiles, control files, and redo logs.

SQL> ALTER SYSTEM SET db_recovery_file_dest = '/OFR1' SCOPE = BOTH;

If the database is using Automatic Storage Management (ASM) feature, then the shared disk area that ASM manages can be targeted for the Flashback Recovery Area.

SQL> ALTER SYSTEM SET db_recovery_file_dest = '+dgroup1' SCOPE = BOTH;The DB_RECOVERY_FILE_DEST_SIZE and DB_RECOVERY_FILE_DEST are defined to make the flash recovery area usable without shutting down and restarting the database instance i.e. these two parameters are dynamic.

SQL> ALTER SYSTEM SET db_flashback_retention_target = 1440 SCOPE = BOTH;

Notes:

• DB_RECOVERY_FILE_DEST_SIZE is defined before DB_RECOVERY_FILE_DEST in order to define the size of the flash recovery area.
• If the value specified in the DB_RECOVERY_FILE_DEST parameter is cleared then as a result the flash recovery area is disabled.
• DB_RECOVERY_FILE_DEST_SIZE parameter cannot be cleared up prior to the DB_RECOVERY_FILE_DEST parameter.

The flash recovery area can be created and maintained using Oracle Enterprise Manager Database Control.

Enabling Flashback

SQL> alter database flashback on;

The database must be in archive log mode to enable flashback.
Configuring Online Redolog Creation in Flash Recovery Area
To store online redologs in FRA, you have to set DB_CREATE_ONLINE_LOG_DEST_1 (OMF init parameter) to FRA location and create the online log groups/members.

The initialization parameters that determine where online redolog files are created are DB_CREATE_ONLINE_LOG_DEST_n, DB_RECOVERY_FILE_DEST and DB_CREATE_FILE_DEST.

Configuring Control File Creation in Flash Recovery Area
To store control file in FRA, you have to set CONTROL_FILES parameter to FRA location.

The initialization parameters CONTROL_FILES, DB_CREATE_ONLINE_LOG_DEST_n, DB_RECOVERY_FILE_DEST and DB_CREATE_FILE_DEST all interact to determine the location where the database control files are created.

Configuring Archived Redolog Creation in Flash Recovery Area
If Archive log mode is enabled and LOG_ARCHIVE_DEST & DB_RECOVERY_FILE_DEST are not set, then the archive logs will be generated in $ORACLE_HOME/dbs directory.

If LOG_ARCHIVE_DEST is set & DB_RECOVERY_FILE_DEST is not set, then the archive logs will be generated at LOG_ARCHIVE_DEST path.

If you enable FRA (DB_RECOVERY_FILE_DEST is set), then the archive log files will be generated in FRA, and it will ignore the LOG_ARCHIVE_DEST and LOG_ARCHIVE_FORMAT i.e. FRA will follow its own naming convention. The generated filenames for the archived redologs in the flash recovery area are Oracle Managed Filenames and are not determined by LOG_ARCHIVE_FORMAT.

It is recommended to use flash recovery area as an archived log location because the archived logs are automatically managed by the database. Whatever archiving scheme you choose, it is always advisable to create multiple copies of archived logs.

You can always define a different location for archive redo logs, if you use a different location, then you can’t just erase the values of the parameters for LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST in order to specify the location of the FRA.

To place your log files somewhere else other than the FRA you should use a different parameter to specify the archived redo log locations: use LOG_ARCHIVE_DEST_1 instead of LOG_ARCHIVE_DEST.

Suppose log_archive_dest was set to ‘+arc_disk3′, you can use LOG_ARCHIVE_DEST_1 to specify the same location for the archived redologs.
Query the parameter to verify its current value:
SQL> show parameter log_archive_dest
SQL> show parameter log_archive_dest_1
SQL> alter system set log_archive_dest_1=’location=+arc_disk3′ scope=both;
SQL> alter system set log_archive_dest=” scope=both;

Managing Flash/Fast Recovery Area

As the DB_RECOVERY_FILE_DEST_SIZE parameter specifies the space for the flash recovery area. In a situation when the space does not prove enough for all flash recovery files, then in such a case Oracle itself keeps track of those files that are not required on the disk. These unnecessary files are then deleted to resolve the space issue in the flash recovery area.

Whenever a file is deleted from the flash recovery area, a message is written in the alert log.

There are various other circumstances in which messages are written in the alert log:
1. When none of the files are deleted.
2. When the used space in the FRA is 85 percentage (a warning).
3. When the used space in the FRA is 97 percentage (a critical warning).
4. The warning messages issued can be viewed in the DBA_OUTSTANDING_ALERTS data dictionary view and are also available in the OEM Database Control main window.

To recover from these alerts, a number of steps can be taken as remedial options:
1. Adjust the retention policy to keep fewer copies of data files.
In case the retention policy is sounds good, then the steps taken to recover from the alerts are:

• More disk space should be added.
• Backup some of the flash recovery files to another destination such as another disk or tape drive.

2. Reduce the number of days in the recovery window

RMAN files creation in the Flash Recovery Area

This section describes RMAN commands or implicit actions (such as control file auto backup) that can create files in the flash recovery area, and how to control whether a specific command creates files there or in some other destination. The assumption in all cases is that a flash recovery area has already been configured for your database. The commands are:

· BACKUP
Do not specify a FORMAT option to the BACKUP command, and do not configure a FORMAT option for disk backups. In such a case, RMAN creates backup pieces and image copies in the flash recovery area, with names in Oracle Managed Files name format.

· CONTROLFILE AUTOBACKUP
RMAN can create control file autobackups in the flash recovery area. Use the RMAN command CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR to clear any configured format option for the control file autobackup location on disk. Control file autobackups will be placed in the flash recovery area when no other destination is configured.

· RESTORE ARCHIVELOG
Explicitly or implicitly (as in the case of, set one of the LOG_ARCHIVE_DEST_n) parameters to 'LOCATION=USE_DB_RECOVERY_FILE_DEST'. If you do not specify SET ARCHIVELOG DESTINATION to override this behavior, then restored archived redo log files will be stored in the flash recovery area.

· RECOVER DATABASE or TABLESPACE, BLOCKRECOVER, and FLASHBACK DATABASE
These commands restore archived redo logs from backup for use during media recovery, as required by the command. RMAN restores any redo log files needed during these operations to the flash recovery area, and delete them once they are applied during media recovery.

To direct the restored archived redo logs to the flash recovery area, set one of the LOG_ARCHIVE_DEST_n parameters to 'LOCATION=USE_DB_RECOVERY_FILE_DEST", and make sure you are not using SET ARCHIVELOG DESTINATION to direct restored archived logs to some other destination.

You can use RMAN to remove old archivelog:
$ rman target=/
RMAN> delete noprompt archivelog all;
RMAN> delete noprompt backup of database;
RMNA> delete noprompt copy of database;

Resolving full Flash Recovery Area

You have a number of choices on how to resolve full flash/fast recovery area when there are no files eligible for deletion:

• Make more disk space available, and increase DB_RECOVERY_FILE_DEST_SIZE to reflect the new space.
• Move backups from the flash recovery area to a tertiary device such as tape. One convenient way to back up all of your flash recovery area files to tape at once is the BACKUP RECOVERY AREA command.

After you transfer backups from the flash recovery area to tape, you can resolve the full recovery area condition by deleting files from the flash recovery area, using forms of the RMAN DELETE command.

Note:

• Flashback logs cannot be backed up outside the flash recovery area. Therefore, in a BACKUP RECOVERY AREA operation the flashback logs are not backed up to tape.
• Flashback logs are deleted automatically to satisfy the need for space for other files in the flash recovery area. However, a guaranteed restore point can force the retention of flashback logs required to perform Flashback Database to the restore point SCN. See
• Delete unnecessary files from the flash recovery area using the RMAN DELETE command. (Note that if you use host operating system commands to delete files, then the database will not be aware of the resulting free space. You can run the RMAN CROSSCHECK command to have RMAN re-check the contents of the flash recovery area and identify expired files, and then use the DELETE EXPIRED command to remove missing files from the RMAN repository.)

You may also need to consider changing your backup retention policy and, if using Data Guard, consider changing your archivelog deletion policy.

Changing the Flash Recovery Area to a new location

If you need to move the flash recovery area of your database to a new location, you can follow this procedure:

1. Change the DB_RECOVERY_FILE_DEST initialization parameter.
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='+disk1' SCOPE=BOTH SID='*';
2. After you change this parameter, all new flash recovery area files will be created in the new location.
3. The permanent files (control files and online redo log files), flashback logs and transient files can be left in the old flash recovery area location. The database will delete the transient files from the old flash recovery area location as they become eligible for deletion.

Oracle will clean up transient files remaining in the old flash recovery area location as they become eligible for deletion.

In Oracle Database 11g, a new feature introduced i.e. Flashback Data Archive - flashback will make use offlashback logs, explicitly created for that table, in FRA, will not use undo. Flashback data archives can be defined on any table/tablespace. Flashback data archives are written by a dedicated background process called FBDA so there is less impact on performance. Can be purged at regular intervals automatically.

Related views
V$RECOVERY_FILE_DEST
V$FLASH_RECOVERY_AREA_USAGE
V$DBA_OUTSTANDING_ALERTS
V$FLASHBACK_DATABASE_LOGFILE

Source: 

Sachin's DBA Blog