RMAN Using the Flash Recovery Area

1. Disable the parameters log_archive_dest and log_archive_duplex_dest, if they are set
in the database. You can do that by issuing the following commands:

SQL> alter system set log_archive_duplex_dest = '';
SQL> alter system set log_archive_dest = '';

2. Log on as a user with the sysdba role (such as the user sys) in preparation to create the
flash recovery area:

sqlplus / as sysdba (if logged in as the Oracle software owner)
sqlplus sys/ as sysdba

3. Issue the following commands to size and create the flash recovery area:

SQL> alter system set db_recovery_size = 4G;
SQL> alter system set db_recovery_dest = '/home/oracle/flasharea';

SQL>show parameter log_archive_dest


SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-38760: This database instance failed to turn on flashback database

Solution 1: Increase Space

You can increase the size of the flashback area dynamically. To increase it to, say, 10GB, you
would issue the following:

SQL> alter system set db_recovery_file_dest_size = 10G;

Solution 2: Remove Restore Points

SQL> select name, storage_size
2* from v$restore_point;
NAME STORAGE_SIZE
------------------------- ------------
RP0 207028224
RP1 0
RP2 915701760
PRE_TEST1 0
POST_TEST1 0
GOOD_ONE 0
QA_GOLD 0

SQL> drop restore point rp2;
Restore point dropped.

Solution 3: Disable Flashback
If solutions 1 and 2 fail or are not applicable, you may want to disable flashback in the database
temporarily. First shut down the database (if not down already):

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
Then start the database in mount mode:

SQL> startup mount
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 67111156 bytes
Database Buffers 96468992 bytes
Redo Buffers 2973696 bytes
Database mounted.
Then disable flashback in the database:

SQL> alter database flashback off;
Database altered.

RMAN> delete noprompt backup of database;
RMAN> delete noprompt copy of database;

SQL> create restore point rp1;
SQL> flashback database to rp1;
SQL> create guaranteed restore point rp1;

Checking Space Usage in the FRA

SQL>select * from v$recovery_file_dest;

SQL> select * from v$flash_recovery_area_usage;

SQL> select
file_type,
space_used*percent_space_used/100/1024/1024 used,
space_reclaimable*percent_space_reclaimable/100/1024/1024 reclaimable,
frau.number_of_files
from v$recovery_file_dest rfd, v$flash_recovery_area_usage frau;

RMAN> backup as copy tablespace users;

Starting backup at 10-OCT-06
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=146 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=/home/oracle/oradata/PRODB2/USERS.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 10/10/2006 00:08:53
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 5242880 bytes disk space from 1073741824 limit
Note the error ORA-19804: cannot reclaim 5242880 bytes disk space from 1073741824
limit. To reclaim space from the flash recovery area at this time, you have to delete the redundant
backups yourself. For example:

RMAN> report obsolete;
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of obsolete backups and copies
Type Key Completion Time Filename/Handle
-------------------- ------ ------------------ --------------------
Archive Log 5442 08-OCT-06
/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/PRODB2/archivelog/➥
2006_10_08/o1_mf_1_40_2lmkv9mo_.arc
Datafile Copy 5461 27-SEP-06 /home/oracle/orabackup/Copy_data➥
D-PRODB2_I-3053038066_TS-SYSTEM_FNO-1_5ehubk8h
Datafile Copy 5462 27-SEP-06 /home/oracle/orabackup/Copy_data➥
D-PRODB2_I-3053038066_TS-UNDOTBS1_FNO-2_5hhubkbs
Datafile Copy 5463 27-SEP-06 /home/oracle/orabackup/Copy_data➥
D-PRODB2_I-3053038066_TS-SYSAUX_FNO-3_5fhubka9
Datafile Copy 5464 27-SEP-06 /home/oracle/orabackup/Copy_data➥
_D-PRODB2_I-3053038066_TS-USERS_FNO-4_5ihubkbv
Datafile Copy 5465 27-SEP-06 /home/oracle/orabackup/Copy_data➥
_D-PRODB2_I-3053038066_TS-EXAMPLE_FNO-5_5ghubkbc
To create even more space in the flash recovery area, you may want to remove the old
backups. The RMAN command delete obsolete does the trick:

RMAN> delete obsolete ;
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
using channel ORA_DISK_1


RMAN> delete noprompt obsolete;

Now that the backup piece is in the right directory, you must tell RMAN. First you need

to remove the identity of the backup piece from the RMAN repository using a process
generally known as uncataloging:

RMAN> change backuppiece
'/home/oracle/flasharea/PRODB2/backupset/2006_11_10➥
/o1_mf_nnndf_TAG20061110T175734_2ob0yzgp_.bkp' uncatalog;
uncataloged backuppiece
backup piece handle=/home/oracle/flasharea/PRODB2/backupset/2006_11_10/➥
o1_mf_nnndf_TA G20061110T175734_2ob0yzgp_.bkp recid=153 stamp=606160655
Uncataloged 1 objects

4. Then catalog the piece again with the correct file:

RMAN> catalog backuppiece
'/home/oracle/flasharea/PROD2/backupset/2006_11_10/o1_mf_n➥
nndf_TA G20061110T175734_2ob0yzgp_.bkp';

Sending Image Copies to the FRA

RMAN> backup as copy database;

Deleting Backup Sets from the FRA

rman target=/

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Nov 8 00:20:58 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: PRODB2 (DBID=3053038066)

RMAN> list backupset;

List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
157 Full 7.14M DISK 00:00:01 14-OCT-06
BP Key: 151 Status: AVAILABLE Compressed: NO Tag:➥
TAG20061014T233415
Piece Name: /home/oracle/flasharea/PRODB2/autobackup/2006_10_14/➥
o1_mf_n_6038
48055_2m3c1r1s_.bkp
Control File Included: Ckp SCN: 1909037 Ckp time: 14-OCT-06

2. Now, to delete a backup set, let’s say number 157, issue a delete backupset command:

RMAN> delete backupset 157;

RMAN> list archivelog all;

2. To delete the archived log sequences 78 and 79, you can use the following commands:

RMAN> delete archivelog from logseq=78 until logseq=79;