Oracle Database High Availability

Frequently Asked Questions

General High Availability

• How does Oracle Database 10g increase overall customer application availability?
• Where do I go for additional high availability information?

Data Protection and Disaster Recovery

• What are the benefits of using Oracle Data Guard?
• Does Oracle Data Guard support automatic switchover/failover?
• How long does it take to do a switchover/failover using Oracle Data Guard?
• How does Oracle Data Guard provide zero data loss capability?

Storage Management

• What are VERITAS Quick I/O and Cached Quick I/O? Does Oracle support them? Do they work with RMAN?(26-MAR-02)
• Does Oracle work with network attached (i.e. NFS) file servers? (15-JUN-00)
• Is there any advantage in using Oracle partitioning with RAID 5? (15-JUN-00)
• Does Oracle support or certify any HSM systems? (23-MAY-01)
• With disk capacity growing at the rate of 100% every 15 months, the future of 100GB drives is not far away. In the same time frame, the rate of disk seek time throughput improvement is only 35%. How does a database administrator configure these large capacity disks for OLTP environment where IOPs (I/O per second), hence seek time, is more important? (23-MAY-01)
• What happens to Oracle when an I/O is non-responsive? (16-MAY-03)
• Does Oracle support any asynchronous remote mirroring product? (26-MAR-02)
• Can Oracle database run on Storage Area Network(SAN)? How about Network Attached Storage (NAS) Does Oracle certify SAN or NAS products? (08-OCT-01)
• What's the new Oracle9i Oracle Disk Manager (ODM) interface? (05-OCT-01)
• Does Oracle recommend any storage vendor or products? (26-MAR-02)
• Does Oracle recommend any storage configuration? What is the S.A.M.E. storage configuration? (30-MAY-01)
• What is Oracle Storage Compatibility Program (OSCP)? (26-MAR-02)
• Does Oracle certify disk, RAID, or other storage products? (22-MAY-01)
• What's a SAN? What's NAS? And how does Oracle fit in SAN and NAS? (26-MAR-02)
• What are the difference between RAID 5 and RAID 0+1? Which implementation is better? (30-MAY-01)

Recovery Manager (RMAN)

• What are the Top 10 Reasons to integrate RMAN into your backup and recovery strategy? (08-JUN-01)
• Is there any method to encrypt a Recovery Manager(RMAN) backup while the backup is being written out to tape? (16-NOV-00)
• What do I need to do to be able to make RMAN backups to tape? (08-JUN-01)
• How does Legato LSM differ from Legato Networker (08-JUN-01)
• Can I have my target db on a sun box and the recovery catalog on a NT machine. (06-JUN-00)
• I have noticed the shadow (or server) processes spawned for "allocate channel" command wouldn't go away after rman completed and released the channel (at least the message in RMAN showed they were released), I noticed the shadow stayed for at least 8 minutes. I did wait for too long. As soon as I exit from rman, they disappeared. Were they waiting pmon to clean them up? How long does this usually take? (08-JUN-01)

• How many server sessions does RMAN use?
  How can the RMAN server sessions be identified?
  What is the purpose of each RMAN server sessions?
  When are the RMan server sessions released? (08-JUN-01)

• When performing a tablespace point in time recovery, are constraints automatically disabled after recovery is complete? (06-JUN-00)
• Can I still backup and recover an Oracle9i database using:
  
  alter ... begin backup
  dd of the file
  alter ... end backup
  recover database
  As well as manually backing up my archivelogs? (08-JUN-01)
• What is Recovery Manager (RMAN) and how do I get started? (16-NOV-00)
• How do you link a 3rd party Media Management Software with RMAN? (08-JUN-01)
• What is the recommended method for backing up the RMAN repository? (08-JUN-01)
• For an online backup, do you need to put tablespaces hot backup mode when using RMAN? (16-NOV-00)
• What is the difference between incremental and full RMAN backups? (16-NOV-00)
• When connecting to a target database with RMAN, how is a username and password validated? (16-NOV-00)
• How do you run a RMAN script using an OS file? (16-NOV-00)
• Can RMAN backup Real Application Cluster databases? (22-MAY-01)
• Why should a DBA use a 3rd party software to backup their server files and then learn RMAN to backup their database? (08-JUN-01)
• Can I perform Block Media Recovery (BMR) if I did not use RMAN for backups? (22-MAY-01)
• Will the RMAN8i scripts continue to work with RMAN9i? (22-MAY-01)
• In RAC environment, can we use NFS for the flash recovery area location? (17-DEC-03)

Recovery Manager (RMAN) -- 10g

• In RAC environment, can we use NFS for the flash recovery area location? (17-DEC-03)
• How does RMAN perform corruption detection? (12-DEC-03)

Flashback Technology -- Flashback Database

• When should I use Flashback Database? (19-DEC-03)
• How large can the flashback logs grow to? (19-DEC-03)
• Does Flashback Database Support all data types? (19-DEC-03)
• Once I flashback the database, can I flash it back further in time? (19-DEC-03)
• Can Flashback Database be integrated or used with Data Guard? (19-DEC-03)
• Do you have to enable the Flash Recovery Area to activate Flashback Database? (19-DEC-03)
• How do I determine how far back in time I can flash back to? (19-DEC-03)

Flashback Technology -- Flashback Table

• What is Flashback Database? (19-DEC-03)
• What is Flashback Table? (19-DEC-03)
• When should I use Flashback Table? (19-DEC-03)
• Is referential integrity maintained during a flashback table operation? (19-DEC-03)
• How far back can I flash back a table? (19-DEC-03)

Flashback Technology -- Flashback Drop

• What is Flashback Drop? (19-DEC-03)
• Are all dropped tables placed into the recycle bin? (19-DEC-03)
• Can I turn off the Recycle Bin feature? (19-DEC-03)
• How do I view all of my objects in the Recycle Bin? (19-DEC-03)
• Is referential integrity maintained in a when my table is flashback dropped? (19-DEC-03)

Flashback Technology -- Flashback Versions Query

• What is Flashback Versions Query? (19-DEC-03)
• When would I use Flashback Versions Query? (19-DEC-03)
• How far back in time can I view the versions of a row? (19-DEC-03)

Flashback Technology -- Flashback Transaction Query

• What is Flashback Transaction Query? (19-DEC-03)
• When would I use Flashback Transaction Query? (19-DEC-03)
• How far back in time can I view the database transactions with Flashback Transaction Query? (19-DEC-03)

Flashback Technology -- Flashback Query

• How far back can I rewind my database? (19-DEC-03)
• What is the Flashback Query? (19-DEC-03)
• Does Flashback Query provide database and application level consistency? (19-DEC-03)
• Where does Oracle get the data to use with a Flashback Query? (19-DEC-03)
• How far back can a flashback query go? (19-DEC-03)
• What can Flashback Query be used for? (19-DEC-03)
• Does any of our competitors have any similar feature/product like Flashback Query? (19-DEC-03)

Answers

For a zero data loss requirement even in the face of disasters, Data Guard can be configured in the Maximum Protection or Maximum Availability modes. For either of these modes, redo transport is done using LGWR, SYNC and AFFIRM. Redo data transmission to the standby databases in these modes is synchronous. This means that as transactions cause changes on the primary database, they are not committed on the primary database unless the changes (in the form of redo data) are also available on disk (in Standby Redo Logs) on at least one of the standby databases that meets the minimum requirements of the Maximum Protection/Availability modes. 

One important point to note in this case is that the primary database transactions do not wait till these redo changes are actually applied on the standby database - they wait only till they are available on disk on the standby database. The subsequent application of these changes to the standby database is a separate process. As such, these protection modes should NOT be confused with expensive two-phase commit operations that are sometimes used in other databases. 

In the Maximum Protection mode, if the Log Writer (LGWR) process fails writing to all standby database destinations participating in Maximum Protection mode (say, because of network failures, or standby server failures), further processing will stop on the primary database, and the primary instance will be shut down (this is equivalent to 'shutdown abort'). Maximum Protection halts the production database to protect against the generation of unprotected data on the production database. Customers using Maximum Protection mode are recommended to use at least two standby databases in this mode, to ensure protection from double failures. 

In the Maximum Availability mode, if the LGWR process fails writing to all standby database destinations participating in Maximum Availability mode (e.g. because of a network failure), then the production database will not be shut down, and processing will continue on the production database, at the expense of leaving the production database unprotected. In this state, the protection mode temporarily changes to Maximum Performance, which effectively means that the Maximum Availability mode can tolerate single failures (network, or standby server) and thus maintaining the availability of the primary.

When this change occurs, redo data continues to get generated and archived on the primary database, and when network connectivity is restored, the accumulated archivelogs are transmitted to the standby database as part of the archivelog gap resolution mechanism, and the protection level is restored to Maximum Availability for the next log.

Note that customers using Data Guard in Oracle9i or later do not need any third party remote mirroring solutions to get the zero data loss capability (click here for more information on this topic). With the Maximum Protection and Maximum Availability modes, Data Guard natively offers this capability. Refer to the Maximum Availability Architecture (MAA) best practice papers available at the MAA web site on OTN, for site/network configuration issues in the Maximum Protection/Availability modes.

VERITAS Quick I/O is a special feature of the VERITAS File System (VxFS) for the VERITAS Database Edition for Oracle. The Database Edition is an integrated suite of system software enhancements and configuration guidelines that combine to help system administrators and database administors configure Oracle database servers.

VERITAS Quick I/O provides a raw device interface to regular files created under VxFS. It bypasses certain file system overhead operations (i.e. locking layer) and supports kernel asynchronous I/O (KAIO), hence Quick I/O provides the benefits of both file system manageability and raw device performance. Cached Quick I/O is an enhancement to Quick I/O that enables Oracle DBAs to utilize their large system memories more effectively.

VERITAS has tested Quick I/O extensively with Oracle and supports the Database Edition directly. Quick I/O does work with RMAN, there are some special steps to take during the restore operation. They are documented in the VERITAS documentation.

You can find out more about the VERITAS product at their web site

   Modified: 26-MAR-02    Ref #: ID-1711

   Modified: 15-JUN-00    Ref #: ID-1714

   Modified: 15-JUN-00    Ref #: ID-1715

   Modified: 23-MAY-01    Ref #: ID-1716

Ultimately the disk configuration will be driven by IOPs (I/Os per second) requirement in an OLTP environment. For example, if the requirement is to be able to sustain 10,000 IOPs, and if each disk can provide 100 IOPs; then minimum 100 disks are required. This means some disk capacity may not be used.

Given that, there may be ways to optimize the configuration. 1. Stripe the data across all drives to maximize spindle utilization (in parallel) and reduce disk hot spots 2. Use outside portion of disk first to get better performance 3. Use inside portion of disk for online backup, which shouldn't be accessed during normal operation 4. Configure a large buffer cache

For availability reasons, Oracle also recommends mirroring all data and backing to tapes regularly.

Another option is to consider storage vendors that offer "high perfomance" or IOPs centric, small form factor disk, fast RPM disk, storage subsystems.

   Modified: 23-MAY-01    Ref #: ID-1723

   Modified: 16-MAY-03    Ref #: ID-1732

   Modified: 26-MAR-02    Ref #: ID-1797

   Modified: 08-OCT-01    Ref #: ID-1809

   Modified: 05-OCT-01    Ref #: ID-1835

   Modified: 26-MAR-02    Ref #: ID-1837

   Modified: 30-MAY-01    Ref #: ID-1838

   Modified: 26-MAR-02    Ref #: ID-1839

   Modified: 22-MAY-01    Ref #: ID-1841

   Modified: 26-MAR-02    Ref #: ID-1842

   Modified: 30-MAY-01    Ref #: ID-1845

• 10. Extensive Reporting
  RMAN reports provide analysis of your backup and recovery environment. An RMAN report answers the questions, "Is the database recoverable?" and "What do I need to do if it isnt?".
• 9. Incremental Backups
  Incremental backups provide shorter recovery time, saves backup storage, uses less bandwidth when backing up over a network, and provides the capability to recover objects created with the NOLOGGING option.
• 8. Downtime Free Backups
  No need to ever shutdown the database to make online backups with RMAN. You set the schedule and RMAN will backup your database while never generating extra redo.
• 7. Restore and Backup Validation
  Feel confident that the backups you made are good by rehearsing recovery to ensure it works.
• 6. Backup and Restore Optimization
  Why subject your system to additional overhead? RMAN provides faster backup and restore times because it only does the work if it is required.
• 5. Easily Integrated with Top Media Managers
  Learn and use RMAN directly or learn and use ONE tool to manage your backup and recovery strategy. The proxy copy feature utilized by the MML uses their new efficient technology for faster backups and restores.
• 4. Block Media Recovery (BMR)
  BMR performs media recovery on individual blocks so less data is impacted by a failure. Increase data availability and reduce recovery time with RMAN and BMR.
• 3. RMAN Knows Archive Logs
  RMAN backs up the datafiles and archive logs with one command while searching all destinations for valid archive logs to provide the ability to recover your data. RMAN automatically manages the archive log disk space by deleting logs once successfully backed up.
• 2. Corrupt Block Detection
  During backup operations, corrupt blocks are detected and reported while preventing unusable backed up files.
• 1. Trouble Free Backup and Recovery
  Its 4am and do you know what database files need to be restored? Prevent a bad situation from becoming worse. RMAN automates backup and recovery operations so all necessary database files are backed up and restores only the files required for recovery.

   Modified: 08-JUN-01    Ref #: ID-1851

RMAN does not use an explicit encryption method, although the binary format of backup files offers some security.

The Media Management Vendor may offer encryption because of the security issue revolves around physical security of the media. The encryption may be a transport bit filtering option. All Media Manager's support some degree of security regarding who can load and read their tapes.

Note that the proprietary binary format of the backup output file from RMAN is not easily decipherable. To extract information from a datafile, you would have to have a copy of the same media management software and a copy of the media management backup catalog, and a in- depth knowledge of Oracle proprietary information. With the correct hardware, software, and knowledge it is theoretical possible. This security risk is not specific to the database. A customer has the same concerns with operating system backups of non-database files.

   Modified: 16-NOV-00    Ref #: ID-1737

RMAN9i is not able to write directly to tape. As this is the case, Oracle has published an API specification which Media Management Vendor's who are members of Oracle's Backup Solutions Partner program have access to. Media Management Vendors (MMVs) then write an interface library which the Oracle9i server uses to write and read to and from tape. You need to have Media Management Server Software installed. You also need to have the interface library which is written by the Media Manager installed.

   Modified: 08-JUN-01    Ref #: ID-1738

   Modified: 08-JUN-01    Ref #: ID-1687

   Modified: 06-JUN-00    Ref #: ID-1693

Server processes for channels will not disappear until RMAN exists. The idea is that these processes will be reused for further backup jobs.

   Modified: 08-JUN-01    Ref #: ID-1695

The number of RMAN server sessions can be calculated by the formula C+N+2, where:

C is the number of allocated channels. These are used for backup and restore work. Server processes for channels will not disappear until rman exits. They will be re-used as necessary for further work.

N is the number of distinct 'connect' options used on the allocate channel commands (or 1 if no connect options are used). One 'polling' session is opened at each node to query the status of the channels that are performing backup or restore work at this node.

And the remaining 2 connections are:
The 'default connection,' which performs action such as:
-- recovery (application of redo)
-- snapshot controlfile creation

The connection to the recovery catalog.

To check RMan processes, you would use normal operating system tools. Examples: On WinNT, the processes are easily found in the Task Manager. On Unix, you would use something like:

$ ps -ef | grep rman
osupport 12388 6982 0 13:25:11 pts/14 0:02 rman rcvcat
$ ps -ef | grep 12388
osupport 12396 12388 0 13:25:53 ? 0:00 oraclePROD
osupport 12395 12388 0 13:25:52 ? 0:00 oraclePROD

   Modified: 08-JUN-01    Ref #: ID-1696

Constraints that straddle the recovery set must be disabled before the TSPITR has begun. Constraints within the recovery set are in the same state as at the time the tablespaces are recovered to.

   Modified: 06-JUN-00    Ref #: ID-1697

Yes, this syntax is supported. This type of backup is called an User Managed backup. For more information, see the Oracle9i User Managed Backup and Recovery Guide.

   Modified: 08-JUN-01    Ref #: ID-1700

   Modified: 16-NOV-00    Ref #: ID-1741

1.Delete the old symbolic link for libobk.so:

% rm $ORACLE_HOME/lib/libok.so

2.Create a symbolic link between libobk.so and the shared library
that you want to use. For example, on SunSolaris you can
create the symbolic link libobk.so to a shared library such as
liblsm.so using the ln command:

% ln -s $ORACLE_HOME/lib/libobk.so $ORACLE_HOME/lib/liblsm.so

   Modified: 08-JUN-01    Ref #: ID-1744

Here is how we recommend deploying the Recovery Catalog:

• It should be a separate database, not contained in any of the target databases whose backup data it contains.
• Create a new shema in the Enterprise Manager Database to house the Recovery Manager catalog


• High availability of the catalog can be achieved by any of the following techniques:
  • standy database
  • replication
  • maintaining two separate Recovery Catalog databases: one is kept in sync as it is used for everyday production operation, and the other is kept in sync manually, by periodically issuing the RESYNC CATALOG command.
• Use RMAN to back up the Recovery Catalog database, but for these backups, start RMAN with the NOCATALOG option, so that the backup repository for the Recovery Catalog is its own control file. The CONTROL_FILE_RECORD_KEEP_TIME parameter should be set to a value which is high enough to store an adequate amount of historical backup data for the Recovery Catalog.


• Back up the Recovery Catalog controlfile, so that you can restore it if you ever need to restore the Recovery Catalog.



   Modified: 08-JUN-01    Ref #: ID-1746

---

For an online backup, do you need to put tablespaces hot backup mode when using RMAN?

To understand why RMAN does not require extra logging or backup mode, you must first understand why those features are required for non-RMAN online backups.

A non-RMAN online backup consists of a non-Oracle tool, such as cp or dd, backing up a datafile at the same time that DBWR is updating the file. We can't prevent the tool from reading a particular block at the exact same time that DBWR is updating that block. When that happens, the non-Oracle tool might read a block in a half-updated state, so that the block which is copied to the backup media might only have been updated in its first half, while the second half contains older data. This is called a "fractured block". If this backup needs to be restored later, and that block needs to be recovered, recovery will fail because that block is not usable.

The 'alter tablespace begin backup' command is our solution for the fractured block problem. When a tablespace is in backup mode, and a change is made to a data block, instead of logging just the changed bytes to the redo log, we also log a copy of the entire block image before the change, so that we can reconstruct this block if media recovery finds that this block was fractured. That block image logging is what causes extra redo to be generated while files are in backup mode.

The reason that RMAN does not require extra logging is that it guarantees that it will never back up a fractured block. We can make that guarantee because we know the format of Oracle data blocks, and we verify that each block that we read is complete before we copy it to the backup. If we read a fractured block, we read the block again to obtain a complete block before backing it up. non-Oracle tools are not able to do the same thing because they do not know how to verify the contents of an Oracle data block.

Backup mode has another effect, which is to 'freeze' the checkpoint in the header of the file until the file is removed from backup mode. We do this because we cannot guarantee that the third-party backup tool will copy the file header prior to copying the data blocks. RMAN does not need to freeze the file header checkpoint because we know the order in which we will read the blocks, which enables us to capture a known good checkpoint for the file.

   Modified: 16-NOV-00    Ref #: ID-1749

---

What is the difference between incremental and full RMAN backups?

See [NOTE:69328.1].

   Modified: 16-NOV-00    Ref #: ID-1747

---

When connecting to a target database with RMAN, how is a username and password validated?

First, let's take a look at how username and password is validated when connecting to SVRMGRL.

• Example 1:
  SVRMGRL> connect foo/bar;
  # This has to work only if users are valid db user.
  # The db must be open.
  # Authentication path is through database.
• Example 2:
  SVRMGRL> connect foo/bar as sysdba;
  # if SQL over sqlnet then check password file
  # if not over net then check UNIX user group if user
  # is in oracle dbs group.

Now let's look at the RMAN connection.

• Example 3:
  RMAN> connect catalog foo/bar;
  # Same like example 1 with SVRMGRL


• Example 4:
  RMAN> connect target foo/bar;
  # Same like example 2 in SVRMGRL.
  # (we append AS SYSDBA to what user types)

   Modified: 16-NOV-00    Ref #: ID-1748

---

How do you run a RMAN script using an OS file?

There are two ways to do this.
1. To run the script and connect in the same command (as you might want to do in a CRON job):
rman target / nocatalog cmdfile "fullback.rmn"
this will startup RMAN, connect to the target database, and run the script fullback.rmn

2. If already in an RMAN session, from the RMAN prompt: @fullback.rmn

You can find some sample scripts in $ORACLE_HOME/rdbms/demo. Search for rman*.sh.

   Modified: 16-NOV-00    Ref #: ID-1750

---

Can RMAN backup Real Application Cluster databases?

Absolutely. RMAN can be configured to connect to all nodes within the cluster to parallelize the backup of the database files and archive logs. If files need to be restored, using set AUTOLOCATE ON alerts RMAN to search for backed up files and archive logs on all nodes.

   Modified: 22-MAY-01    Ref #: ID-1827

---

Why should a DBA use a 3rd party software to backup their server files and then learn RMAN to backup their database?

• One tool  One Interface
• Backup server files - Backup database
• RMAN integration ensures that your database is precisely backed up

Oracle BSP Program Members

   Modified: 08-JUN-01    Ref #: ID-1828

---

Can I perform Block Media Recovery (BMR) if I did not use RMAN for backups?

If you have image copies or OS backups of the datafiles you can perform BMR. You must first register the datafile image copies or OS backups to RMAN as well as the current archive log and all logs since the image backup time. This can be accomplished by using the RMAN CATALOG command.

   Modified: 22-MAY-01    Ref #: ID-1829

---

Will the RMAN8i scripts continue to work with RMAN9i?

Yes all of the RMAN8i commands work with RMAN9i. Oracle encourages DBAs to use the new configuration commands with the Oracle9i databases so the scripts will be more streamlined. As a side note, the command release channel is not required to be executed as RMAN will release channels implicitly.

   Modified: 22-MAY-01    Ref #: ID-1830

---

In RAC environment, can we use NFS for the flash recovery area location?

Yes, the Flash Recovery Area can be NFS for RAC. It should be a NFS location that has been verified by our OSCP. It can also be a cluster file system for RAC.

   Modified: 17-DEC-03    Ref #: ID-3944

---

In RAC environment, can we use NFS for the flash recovery area location?

Yes, the Flash Recovery Area can be NFS for RAC. It should be a NFS location that has been verified by our OSCP. It can also be a cluster file system for RAC.

   Modified: 17-DEC-03    Ref #: ID-3945

---

How does RMAN perform corruption detection?

Corruption detection is actually performed by the Oracle server.

RMAN does NOT perform the backup, but just directs the server to backup a specified list of datafiles. During backup, the Oracle server detects certain types of corruptions.

Many of the verification checks that are performed when a block is read into the buffer cache are also performed by a server process performing a backup. Some types of corruptions cannot be detected because the exact structure of table/index block distribution in the database object is unknown; this can happen during a closed database backup, as the server process is unavailable. If a block is detected as newly corrupted, it will be identified as such in the alert log and also in the database controlfile. You can view this information by querying the V$COPY_CORRUPTION or V$BACKUP_CORRUPTION views. If a block was already detected as corrupted by the server, then the block will not be identified in the same way as a newly corrupted block.

   Modified: 12-DEC-03    Ref #: ID-1735

---

When should I use Flashback Database?

You should use FLASHBACK Database to
• Eliminate the time to restore a backup when performing a Database Point-In-Time-Recovery operation. Restoring and recovering a database can take a long time. When a database is down because it runs into a catastrophic problem, millions of dollars in revenue may be lost because the company may no longer be able to do business.
• Eliminate standby database redo apply delay. Flashback database is seamlessly integrated with Data Guard. A standby database can now be quickly and easily flashed back to an arbitrary point in time so a delay in the redo apply is not necessary.
• Unanticipated Error Correction. Flashback Database provides a continuous snapshot of the Oracle database. The database can be rewound back to a SCN or timestamp to quickly recover the whole database from logical data corruptions or user errors. You can analyze the lost transactions in the redo logs later with LogMiner.

   Modified: 19-DEC-03    Ref #: ID-3967

---

How large can the flashback logs grow to?

Flashback Database utilizes the Flash Recovery Area to organize and manage the flashback logs, therefore the log size is limited to the space allocated to the Flash Recovery Area. This may also impact your need to flash back to your desired time in the past.

   Modified: 19-DEC-03    Ref #: ID-3969

---

Does Flashback Database Support all data types?

Yes. All data that is located in the database is supported.

   Modified: 19-DEC-03    Ref #: ID-3971

---

Once I flashback the database, can I flash it back further in time?

You can issue a Flashback Database command and then open the database READ-ONLY. This will allow you to verify this is the point in which you want to open and start working again. Until you open the database with a resetlogs operation, you can roll-forwards and backwards using the recover database until and flashback commands.

   Modified: 19-DEC-03    Ref #: ID-3973

---

Can Flashback Database be integrated or used with Data Guard?

Data Guard provides several easy-to-use means to avoid user errors. Flashback Database can be used on both the primary and standby database to quickly revert the databases to an earlier point-in-time to back out user errors. Alternatively, if the administrator decides to failover to a standby database, but those user-errors were already applied to the standby database (say, because Real Time Apply was enabled), the administrator may simply flashback the standby database to a safe point in time.

   Modified: 19-DEC-03    Ref #: ID-3975

---

Do you have to enable the Flash Recovery Area to activate Flashback Database?

Yes. Flashback Database utilizes the Flash Recovery Area to organize and manage the flashback logs.

   Modified: 19-DEC-03    Ref #: ID-3977

---

How do I determine how far back in time I can flash back to?

The actual earliest time to which you could rewind your database using flashback technology is determined by querying the V$FLASHBACK_DATABASE_LOG view. The V$FLASHBACK_DATABASE_LOG view can also help you decide how much space needed in the flash recovery area for flashback logs to rewind back a specified number of hours.

   Modified: 19-DEC-03    Ref #: ID-3979

---

What is Flashback Database?

FLASHBACK DATABASE provides the ability to quickly revert an Oracle database to a previous time--without restoring datafiles and performing media recovery. When you enable the flashback functionality, Oracle automatically creates, deletes, and manages flashback database logs inside the flash recovery area. When you run the FLASHBACK DATABASE command, Oracle applies the flashback logs as well as the archived redo logs to return the database to the specified time. Its a rewind button for the Oracle database.

   Modified: 19-DEC-03    Ref #: ID-3963

---

What is Flashback Table?

Flashback Table is a new Oracle Database 10g feature that enables users to recover a table to a previous point in time. It provides a fast, online solution for recovering a table that has been accidentally modified or deleted by a user or application. In many cases, Flashback Table alleviates the need for administrators to perform more complicated point in time recovery operations. Even after a flashback, the data in the original table is not lost. You can later revert it back to the original state.

Flashback Table:

• provides fast recovery on a table(s) to a point in time in the past without restoring a backup.
• restores all data in a specified table to a previous point in time described by a timestamp or SCN. An exclusive DML lock is held on a table while it is being restored.
• performs the restore operation online.
• automatically restores all of the table attributes, such as indexes, triggers, and the likes that are necessary for an application to function with the flashed-back table.
• maintains any remote state in a distributed environment.
• maintains data integrity as specified by constraints.

   Modified: 19-DEC-03    Ref #: ID-3993

---

When should I use Flashback Table?

Flashback Table should be used when a logical corruption has occurred, such as erroneously deleted rows or updates that have omitted the WHERE clause.

   Modified: 19-DEC-03    Ref #: ID-3995

---

Is referential integrity maintained during a flashback table operation?

Referential integrity can be enforced by Oracle via constraints. Flashback Table enforces those constraints for all dependencies. If you do not enforce referential integrity with software, then it is a manual process.

   Modified: 19-DEC-03    Ref #: ID-3997

---

How far back can I flash back a table?

You can flash back a table
• for as long as you have undo information retained in the undo tablespace to satisfy the specified timestamp or SCN.
• to a time that is before a DDL operation which altered the form of the table A few examples of such DDL operations are:
• ALTER TABLE ... DROP COLUMN
• ALTER TABLE ... DROP PARTITION
• CREATE CLUSTER
• TRUNCATE TABLE
• ALTER TABLE ... MOVE

   Modified: 19-DEC-03    Ref #: ID-3999

---

What is Flashback Drop?

Flashback Drop provides a safety net when dropping objects in Oracle Database 10g. When a user drops a table, Oracle will place it in a Recycle Bin. Objects in the Recycle Bin will remain there until user decides to permanently remove them or the space pressure is placed on the tablespace containing the table. The recycle bin is a virtual container where all dropped objects reside. Users can look in the Recycle Bin and undrop the dropped table and its dependent objects.

   Modified: 19-DEC-03    Ref #: ID-4013

---

Are all dropped tables placed into the recycle bin?

Objects that are physically created in the SYSTEM tablespace and tables using VPD are not placed into the recycle bin. Therefore, traditional recovery methods must be used to restore these tables.

   Modified: 19-DEC-03    Ref #: ID-4015

---

Can I turn off the Recycle Bin feature?

No. The recycle bin feature is on by default. Flashback Drop is a non-intrusive feature. Objects in the Recycle Bin will be automatically purged by the space reclamation process if
• a user creates a new table or adds data that causes their quota to be exceeded.
• the tablespace needs to extend its file size to accommodate create/insert operations.

A PURGE operation can be issued by any use to remove their objects from the recycle bin.

   Modified: 19-DEC-03    Ref #: ID-4017

---

How do I view all of my objects in the Recycle Bin?

Select * from RECYCLEBIN;

   Modified: 19-DEC-03    Ref #: ID-4019

---

Is referential integrity maintained in a when my table is flashback dropped?

Referential integrity can be enforced by Oracle via constraints. Flashback Drop enforces those constraints for all dependencies. If you do not enforce referential integrity with software, then it is a manual process.

   Modified: 19-DEC-03    Ref #: ID-4021

---

What is Flashback Versions Query?

Flashback Versions Query is an extension to SQL that allows you to retrieve the different versions of rows in a given table that existed in a specific time interval. It returns a row for each version of the row that existed in the time interval you specify. For any given table, a new row version is created every time the COMMIT statement is executed. You specify the flashback versions query using the VERSIONS BETWEEN clause of the SELECT statement.

   Modified: 19-DEC-03    Ref #: ID-4001

---

When would I use Flashback Versions Query?

Flashback Technology should be used when a logical corruption (human error) occurs in the Oracle database and you need to recovery quickly and easily. As with human errors, it is difficult to identify the object(s) and row(s) that were affected by an erroneous transaction. Flashback aids you in the diagnosis of how errors were introduced into the database so you can repair the damage and prevent it from happening again.

Using Flashback Versions Query, a DBA can pinpoint when and how data is changed and trace it back to the user, application, or transaction. This allows the DBA to track down the source of a logical corruption in the database and correct it. It also enables the application developer to debug their code.

   Modified: 19-DEC-03    Ref #: ID-4003

---

How far back in time can I view the versions of a row?

You can view all the versions of a row for as long as you have undo information retained in the undo tablespace to satisfy the specified timestamp or SCN.

   Modified: 19-DEC-03    Ref #: ID-4005

---

What is Flashback Transaction Query?

Flashback Transaction Query provides a way for you to view all changes made to the database at the transaction level. When used in conjunction with Flashback Versions Query, it allows you to easily recover from user or application errors. It increases online diagnosability of problems in your database and performs analysis and audits of transactions.

Flashback Transaction Query shows all the resultant changes made by this transaction. In addition, compensating SQL statements are returned and can be used to undo changes made to all rows by this transaction. Using a precision tool like this, the DBA and application developer can precisely diagnose and correct logical problems in the database or application.

   Modified: 19-DEC-03    Ref #: ID-4007

---

When would I use Flashback Transaction Query?

Flashback Technology should be used when a logical corruption (human error) occurs in the Oracle database and you need to recovery quickly and easily. As with human errors, it is difficult to identify the object(s) and row(s) that were affected by an erroneous transaction. Flashback aids you in the diagnosis of how errors were introduced into the database so you can repair the damage and prevent it from happening again.

Once you identify an erroneous transaction, you can then use the Flashback Transaction Query feature to identify other changes that were done by the transaction, and to request the undo SQL to reverse those changes. The FLASHBACK_TRANSACTION_QUERY view is the means through which you obtain transaction history and undo SQL.

   Modified: 19-DEC-03    Ref #: ID-4009

---

How far back in time can I view the database transactions with Flashback Transaction Query?

You can view all the transactions for as long as you have undo information retained in the undo tablespace to satisfy the specified timestamp or SCN.

   Modified: 19-DEC-03    Ref #: ID-4011

---

How far back can I rewind my database?

There are 2 limiting factors to how far back in time you can rewind.
• Specify the how far back to rewind. The setting of the DB_FLASHBACK_RETENTION_TARGET initialization parameter determines, indirectly, how much flashback data that Oracle should keep. This limit is contingent upon sufficient space existing in the flash recovery area.
• Flash Recovery Area. Flashback Database utilizes the Flash Recovery Area to organize and manage the flashback logs. If you do not allocated enough space to the Flash Recovery Area, then you will be limited to how far back in time you will be able to rewind.

   Modified: 19-DEC-03    Ref #: ID-3965

---

What is the Flashback Query?

A database query, by default, always shows a consistent set of data containing most recently committed changes. Introduced Oracle9i, users are able to query a consistent version of the database as it was at some point in the past either by specifying a wall clock time or a System Change Number (SCN).

   Modified: 19-DEC-03    Ref #: ID-3981

---

Does Flashback Query provide database and application level consistency?

Flashback query uses consistent data as of a certain time in the past. Database and application level constraints will be preserved. For example, referential integrity of data will be preserved. As a result, a reporting tool can be adapted to use flashback without changing any application logic.

   Modified: 19-DEC-03    Ref #: ID-3983

---

Where does Oracle get the data to use with a Flashback Query?

Oracle Database saves the undo for the changed data in the undo tablespace. It is possible to specify a target retention period for the undo or rollback data in the undo tablespace in terms of wall clock time. Oracle then tries not to overwrite this data unless it truly runs out of space in the undo tablespace.

This "retained" undo data is used to reconstruct a snapshot of the data as it existed at a time in the past. However, since the retention of the undo data is not guaranteed, successful execution of a Flashback Query depends on the existence of the needed undo data. The configuration of undo retention time and sizing of the undo tablespace, therefore, are very critical for successful execution of Flashback Queries.

   Modified: 19-DEC-03    Ref #: ID-3985

---

How far back can a flashback query go?

In Oracle9i, the flashback query allows you to view up to 120 hours (5 days) of past data. Oracle Database 10g will allow you to view up to the value of undo_retention as long as there is enough space in the UNDO tablespace.

   Modified: 19-DEC-03    Ref #: ID-3987

---

What can Flashback Query be used for?

The Flashback Query is essentially a self service tool which allows application users to correct their mistakes with minimal DBA intervention. Once the DBA configures the undo tablespaces with appropriate size and undo retention period, no further administrator intervention is required to query the database at a past point of time.

These is a powerful feature since application developers can take advantage of the PL/SQL interface and provide a "WHOOPS" button in an application with minimal code change (self service recovery application).

   Modified: 19-DEC-03    Ref #: ID-3989

---

Does any of our competitors have any similar feature/product like Flashback Query?

Thanks to our superior multiversioning read consistency technology, Oracle is the only database product with such a capability.

   Modified: 19-DEC-03    Ref #: ID-3991

---