RMAN> SHUTDOWN IMMEDIATE;
SQL> select prior_incarnation# from v$database_incarnation where status = ‘CURRENT’;
sql> select prior_incarnation# from v$database_incarnation where status =
sql> select prior_incarnation# from v$database_incarnation where status = ‘CURRENT’;
sql> select prior_incarnation# from v$database_incarnation where status = ‘CURRENT’;When space pressure arises Oracle will select objects for automatic purging from the recycle bin, objects are selected for purging based on a first-in, first-out basis, that is, the first objects dropped are the first select for purging. Actual purging of objects is done only as needed to meet ongoing space pressure, that is, the database purges the minimum possible number of objects selected for purging to meet the immediate demand for space. This policy serves two purposes: 1) it minimizes the overhead by not performing unnecessary purging this is especially crucial during transaction processing during which time Oracle determines it has an issue with space pressure by not spending any more resources than necessary by purging only the minimum number of objects to relieve the space pressure and 2) it maximizes the length of time that objects will remain in the recycle bin, by leaving them there until space is needed.
Dependent objects such as indexes belonging to a table has been selected for purging will be selected first, purged and then if the space pressure demands have been met the table will remain in the recycle bin. If space pressure is due to an individual user’s quota on a tablespace becoming exhausted, the recycle bin purges objects belonging to the tablespace which count against that user’s space quota.
Tablespaces that are set to AUTO EXTEND – objects are purged from the recycle bin to reclaim space before the datafile is extended.
Recycle Bin Objects and Segments – The recycle bin operates at the object level, in terms of tables, indexes and so on. An object may have multiple segments associated with it, such as partitioned tables, partitioned indexes, lob segments, nested tables, and so on. Because the database reclaims only the segments needed to immediately statisfy the space pressure demand, it can happen that not all of the segments belonging to an object are reclaimed. When this happens, any segment(s) of the objects not reclaimed immediately are marked as temporary segments. These temporary segments are the first candidates to be reclaimed the next time a space pressure arises.
In the case the partially-reclaimed object can no longer be removed from the recycle bin with the Flashback drop command. For example, if one partition of a partitioned table is reclaimed, the table can no longer be the object of a Flashback Drop.
Performing Flashback Database to Guarantee Restore Point – You can list the available guaranteed restore points using the V$RESTORE_POINT view as follows:
select name, scn, time, database_incarnation#, guarantee_flashback_database
from v$restore_point
where guarantee_flashback_database = ‘YES’;
Having identified the restore point to use, mount the database and run the FLASHBACK DATABASE command, using the restore point. For example:
RMAN> SHUTDOWN IMMEDIATE;
RMAN> STARTUP MOUNT;
RMAN> FLASHBACK DATABASE TO RESTORE POINT ‘BEFORE_TAX_UPDATE’;
When the command completes, you may open the database read-only and inspect the effects of the operation and if satisfied, open the database with the RESETLOGS option.
Performing Flashback Database to Undo and OPEN RESETLOGS
The basic procedure for using the Flashback Database to reverse an unwanted OPEN RESETLOGS is similar to the open example, however, rather than specifying a particular SCN or a point in time mark for the FLASHBACK DATABASE command, you use the FLASHBACK DATABASE TO BEFORE RESETLOGS, as in the following example:
Before performing the flashback, verify that the beginning of the flashback window is earlier than the time of the most recent OPEN RESETLOGS:
SQL> select resetlogs_change# from v$database;
SQL> select oldest_flashback_scn from v$flashback_database_log;
If V$DATABASE.RESETLOGS_CHANGE# is greater than the value of V$FLASHBACK_DATABASE_LOG.OLDEST_FLASHBACK_SCN, then you can use the Flashback Database to reverse the OPEN RESETLOGS by following the steps outlined below:
Shutdown the database, mount it, and re-check the flashback window. If the resetlogs SCN is still within the flashback window, then use this form of the FLASHBACK DATABASE command:
RMAN> FLASHBACK DATABASE TO BEFORE RESETLOGS;
As with other uses of FLASHBACK DATABASE, if the target SCN is prior to the beginning of the flashback database window, an error is returned and the database is not modified. If the command completes successfully, then the database is left mounted and recovered to the last SCN before the OPEN RESETLOGS in the previous incarnation.
You can open the database read-only and perform queries to make sure that the data is in the proper state To make the database available for updates again, use ALTER DATABASE OPEN RESETLOGS.
Flashback Database Across OPEN RESETLOGS With Standby Databases
Support for Flashback Database across OPEN RESETLOGS enables several applications of Flashback Database with standby databases. These include:
- Flashback to undo logical standby switchovers, in which the database reverts back to its role (primary or standby) at the target time for the Flashback Operation.
- Undo of a physical standby activation, so that you can temporarily activate a physical standby database, use it for testing or reporting purposes, then use Flashback Database to return it to its role as a physical standby
- Ongoing use of a clone or standby database for testing, without requiring the use of storage snapshots
Flashback Database to the RIght of Open Resetlogs:
In some cases, you may need to return the database to a point in time in its parent incarnation, later than the SCN of the OPEN RESETLOGS at which the current incarnation path branched from the old incarnation. These points, which correspond to abandoned changes in the parent incarnation, can be described as being “to the right” of the last OPEN RESETLOGS, with reference to an incarnation diagram such as the following. An example would be, in the diagram, the database might be in incarnation 3, and you might need to return to the abandoned SCN 1500 in incarnation 1. You can use the RMAN RESET DATABASE TO INCARNATION command to specify the current incarnation referred to by the SCN to use with the Flashback Database. The process works as follows:
- Verify that the flashback logs contain enough information to flash back to that SCN
sql> select oldest_flashback_scn from v$flashback_database_log;
- Determine the target incarnation number for the flashback, that is, the incarnation key for the parent incarnation:
SQL> select prior_incarnation# from v$database_incarnation where status = ‘CURRENT’;
- In RMAN, shut down the database, then mount it:
RMAN> SHUTDOWN IMMEDIATE;
RMAN> STARTUP MOUNT;
- Set the database incarnation to the parent incarnation:
RMAN> RESET DATABASE TO INCARNATION 1;
- Run the FLASHBACK DATABASE command:
RMAN> FLASHBACK DATABASE TO SCN 1500;
Once the flashback is complete, you can verify the results, and if successful, open the database with RESETLOGS.
Performing Database Point-In-Time Recovery
Database point-in-time recovery (DBPITR) restores the database from backups prior to the target time for recovery, then uses the incremental backups and redo to roll the database forward to the target time. Understanding DBPITR requires background information on database incarnations and how RMAN treats backups from times not in the current incarnation path. In particular, there are special considerations if you are returning your database to a point in time prior to the most recent OPEN RESETLOGS. The following section contains the following topics: Understanding Parent, Ancestor and Sibling Database Incarnations, Incarnation History of a Database, and Database Incarnations and Orphaned Backups.
Understanding Parent, Ancestor and Sibling Database Incarnations
A new incarnation of a database is created whenever each time the database is opened with the RESETLOGS option. Performing an OPEN RESETLOGS archives the current online redo logs, Incarnation resets the log sequence number to 1 and then gives the online redo logs a new time stamp and SCN. It also increments the incarnation number, which is used to uniquely tag and identify a stream of redo. Incarnations can stand in several relationships to each other as described below:
- The incarnation from which the current incarnation branched following an OPEN RESETLOGS operation is called the parent incarnation of the current incarnation.
- The parent incarnation and all of its parent incarnations are the ancestor incarnations of the current incarnation.
- Two incarnations that share a common ancestor are sibling incarnations if neither one is an ancestor of the other.
Incarnation History of a Database: Example
“Database Incarnation History with Multiple Resetlogs” shows a database that goes through several incarnations. Incarnation 1 of the database starts at SCN 1 and continues through SCN 65366080601 (see example). For illustration purposes say at SCN 2000 in incarnation 1, you perform a point-in-time recovery back to SCN 1000, and open the database with a RESETLOGS operation. This creates incarnation 2, which begins at SCN 1000 and continues to SCN 3000. At SCN 3000 in incarnation 2, you perform another point-in-time recovery and RESETLOGS operation. This creates incarnation 3, starting at SCN 2000.
RMAN> list incarnation;
starting full resync of recovery catalog
full resync complete
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
——- ——- ——– —————- — ———- ———-
1 2 PS89FNPD 509360695 PARENT 1 06-APR-07
1 3284 PS89FNPD 509360695 CURRENT 65366080601 23-OCT-07
The value in the Reset SCN column is the SCN at which the RESETLOGS was performed. The Inc Key column is the incarnation key. RMAN uses the incarnation key to identify the database incarnation in some commands, such as using RESET DATABASE TO INCARNATION to change the current incarnation in some complex recovery scenarios.
Sibling Incarnations, Ambiguous SCNs and RESET DATABASE INCARNATION:
When working with a database where flashback or point-in-time recovery operations have produced sibling incarnations, note that when a given SCN value can refer to more than one point in time, depending upon which incarnation has been set as the current incarnation.
You can run a CHANGE … UNCATALOG command for the backups that you deleted from the Operating System with Operating System commands. Optionally, you can view the relevant recovery catalog view, for example, RC_DATAFILE_COPY or RC_CONTROLFILE_COPY, to confirm that a given record was removed.
Flash Recovery Area Maintenance:
While the Flash Recovery Area is largely self-managing, there are some situations in which DBA intervention may be required. Resolving a Full Flash Recovery Area - you have a number of choices on how to resolve a full flash recovery area when there are no files marked for deletion.
- Make some 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 space 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.
- Delete unnecessary files from the flash recovery area using the RMAN DELETE command.
(Note that if you use the 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.)
Note: Flashback logs, by design, cannot be backed up outside the flash recovery area. Therefore, in a BACKUP RECOVERY AREA operation the 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.
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 to a new location, you can invoke this procedure
- Invoke SQL*Plus to change the DB_RECOVERY_FILE_DEST initialization parameter. For example
alter system set db_recovery_file_dest=’+disk1′ scope=both sid=’*';
After you change this parameter, all new flash recovery files will be created in the new location.
2. 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.
If you need to actually move your current permanent files, transient files or flashback logs to the new flash recovery area, see the Oracle Database Backup and Recovery Advanced User’s Guide for detailed instructions. The process outlined there for moving database files into and out of an ASM disk group with RMAN will also work when moving files into and out of a flash recovery area location.
Oracle will clean up transient files remaining in the old flash recovery area location as they become eligible for deletion.
Flash Recovery Area Behavior when Instance Crashes During File Creation – As a rule, the flash recovery area is self-maintaining, but when an instance crashes during the creation of a file in the flash recovery area, Oracle may leave the file in the flash recovery area. When this occurs, you will see the following error in the alert log:
ORA-19816: WARNING: Files may exist in a location that are not known to the database. Where location is the location of the flash recovery area. In such a situation, you should use the RMAN command CATALOG RECOVERY AREA to re-catalog any such files so that they appear in the RMAN repository. If the file header of the file in question is corrupted, then you will have to delete the file manually using an operating system-level utility.
You can query the V$RECOVER_FILE_DEST view to find out the current location, disk quota, space in use, space reclaimable by deleting files and total number of files in the Flash Recovery Area.
Oracle does not delete eligible files from the Flash Recovery Area until the space must be reclaimed for some other purpose. The effect is that files recently moved to tape are often still available on disk for use in recovery. The recovery area can thus serve as a kind of cache for tape. Once the FRA is full, Oracle automatically deletes eligible files to reclamin space in the FRA as needed.
The following rules apply for files to become eligible for deletion from the FRA:
- Files that are obsolete under the configured retention policy
- Transient files that have been copied to tape
- In a Data Guard environment, archived redolog deletion policy governs when archived redolog files can be deleted from the Flash Recovery Area
The database issues a warning alert when reclaimable space is less than 15% and a critical alert when reclaimable space is less than 3%. To warn the DBA of this condition, an entry is added to the alert log and to the DBA_OUTSTANDING_ALERTS table (used by Enterprise Manager). However, the database continues to consume space in the Flash Recovery Area until there isn’t any reclaimable space left. When the Flash Recovery Area is completely full, the following error will be reported:
ORA-19809: Limit exceeded for recovery files ORA-19804: Cannot reclaim <nnnn> bytes disk space from <nnnnnnnn> limitwhere <nnn> is the number of bytes required and <nnnnnn> is the disk quota for the Flash Recovery Area.
The following error would be reported in the alert.log:
ORA-19815: WARNING: db_recovery_file_dest_size of <size of FRA configured> bytes is 100.00% used, and has 0 remaining bytes available.
Issue the following query to see the message:
SQL> select object_type, message_type, message_level, reason, suggested_action from dba_outstanding_alerts;
The following actions can be taken to resolve the space issue:
- Add disk space to the Flash Recovery Area
- Back up your files to a tertiary device
- Delete the files from the Flash Recovery Area using RMAN
- Changing the RMAN retention policy.
The database handles a Flash Recovery Area with insufficient reclaimable space just at it handles a disk full condition. Often, the result is a hang of the database.
