OutOfMemoryError while WLS Patching | GC overhead limit exceeded

Issue:
Exception in thread "main" java.lang.OutOfMemoryError: GC overhead limit exceeded


Error:
[applvision@test bsu]$ ./bsu.sh -install -patch_download_dir=/backups/appsvision/VISION/fs1/FMW_Home/utils                                            /bsu/cache_dir -patchlist=QMJP -prod_dir=/backups/appsvision/VISION/fs1/FMW_Home/wlserver_10.3
Exception in thread "main" java.lang.OutOfMemoryError: GC overhead limit exceeded
        at java.util.Arrays.copyOf(Arrays.java:2367)
        at java.lang.AbstractStringBuilder.expandCapacity(AbstractStringBuilder.java:130)
        at java.lang.AbstractStringBuilder.ensureCapacityInternal(AbstractStringBuilder.java:114)
        at java.lang.AbstractStringBuilder.append(AbstractStringBuilder.java:415)
        at java.lang.StringBuilder.append(StringBuilder.java:132)
        at java.lang.Class.getMethod(Class.java:1678)
        at com.bea.cie.common.dao.xbean.XBeanDataHandler.isValueSet(XBeanDataHandler.java:958)
        at com.bea.cie.common.dao.xbean.XBeanDataHandler.getValueFromObject(XBeanDataHandler.java:589)
        at com.bea.cie.common.dao.xbean.XBeanDataHandler.getSimpleValue(XBeanDataHandler.java:431)
        at com.bea.plateng.patch.dao.cat.PatchDependency.getRule(PatchDependency.java:48)
        at com.bea.plateng.patch.dao.cat.PatchCatalogHelper.getInvalidatedPatchMap(PatchCatalogHelper.java:1625)
        at com.bea.plateng.patch.PatchSystem.updatePatchCatalog(PatchSystem.java:436)
        at com.bea.plateng.patch.PatchSystem.refresh(PatchSystem.java:130)
        at com.bea.plateng.patch.PatchSystem.setCacheDir(PatchSystem.java:201)

        at com.bea.plateng.patch.Patch.main(Patch.java:281)

Reason:

The default java memory arguments  provided in bsu.sh file are not sufficient

Solution:

Increase the Java memory values in bsu.sh file (under $MW_HOME/utils/bsu) by adjusting the value MEM_ARGS values.

By default the values will be

MEM_ARGS="-Xms256m -Xmx512m"

I increased it to 1024-1024 as follows

"-Xms1024m -Xmx1024m"

Save the bsu.sh file and re-execute the patching command.

Applying WLS Patch and Patch Conflicts

Patch ID: K25M

Patch No: 23743997

(WLS) 10.3.6.0.7

23743997 [SU Patch [K25M]: WLS PSU 10.3.0.6.161018]

    - Filename: p23743997_1036_Generic.zip\

./bsu.sh ‐install ‐patch_download_dir=$FMW_HOME/utils/bsu/cache_dir ‐patchlist=K25M ‐prod_dir=$FMW_HOME/wlserver_10.3

Oracle E-Business Suite Release 12 Critical Patch Update Knowledge Document (October 2016) (Doc ID 2181748.1)

Set your environment:

cd $FMW_HOME

 cd wlserver_10.3/server/bin

.  ./setWLSEnv.sh

Verify the patch applied or not

bsu.sh -prod_dir=$FMW_HOME/wlserver_10.3 -status=applied -verbose -view | grep -i K25M

Unzip the patch to the cache_dir directory:

cd $FMW_HOME/utils/bsu/cache_dir

cp /oracle/software/ p23743997_1036_Generic.zip $FMW_HOME/utils/bsu/cache_dir

unzip p23743997_1036_Generic.zip

Stop all WebLogic servers

Install the patch using the Smart Update command utility:

cd $FMW_HOME/utils/bsu

./bsu.sh ‐install ‐patch_download_dir=$FMW_HOME/utils/bsu/cache_dir ‐patchlist=K25M ‐prod_dir=$FMW_HOME/wlserver_10.3

Patch Conflicts detected

Conflict condition details follow: 

Patch K25M is mutually exclusive and cannot coexist with patch(es): HSZB,Z62H,5C71,7Z9S,DEM4

This means that the new PSU patch is conflicting with the other patches listed above, so I have 3 options here:

1. Forget about the latest PSU patch and leave– not recommended.

2. Request an “overlay” patch from Oracle Support which effectively merges all of the patches ﴾if some patches listed above aren’t included

in the new PSU﴿.

3. If the new PSU contains all of the above patches, remove them first, then apply the new PSU.

Capture patches installed

cd $FMW_HOME/utils/bsu

/bsu.sh -prod_dir=$FMW_Home/wlserver_10.3 -status=applied -view -verbose > /tmp/bsu_patches_installed.log

Remove patches

cd $ FMW_Home/utils/bsu

The below patches are Oracle WebLogic Sever overlay patch for PSU 10.3.6.0.160419. We  need to remove these following patches in order to apply the latest PSU.

 HSZB,Z62H,5C71,7Z9S,DEM4

./bsu.sh -prod_dir= $FMW_Home /wlserver_10.3  -patchlist= <Patch ID> -verbose -remove

Now  Install  the patch.

cd $ FMW_Home/utils/bsu

bsu.sh -install -patch_download_dir=$FMW_HOME/utils/bsu/cache_dir  -patchlist=K25M -prod_dir=$WLS_HOME

  

cd  $FMW_HOME/wlserver_10.3/server/bin

.  ./setWLSEnv.sh

 java weblogic.version|grep PSU

WebLogic Server 10.3.6.0.161018 PSU Patch for BUG23743997 TUE AUG 30 18:34:42 IST 2016

That’s it, WebLogic is installed and patched up-to-date.

For more details on WebLogic PSU conflicts, following  are  good MOS notes.

Announcing Oracle WebLogic Server PSUs (Patch Set Updates) [ID 1306505.1]

Replacement Patches for WebLogic Server PSU Conflict Resolution [ID 1471192.1]

Recreating OraInventory(Corrupted/Lost) for already existing Oracle Home

Registering Oracle Home in the Central Inventory:

Symptoms: 

         Exception oracle.sysman.oii.oiil.OiilNativeException: S_OWNER_SYSTEM_EPERM occurred..

          'AttachHome' failed.

           LsInventorySession failed: Oracle Home inventory cannot be loaded.     

           Oracle Home inventory is corrupted.

           OPatch failed with error code 73

Cause:

             ORACLE_HOME is not registered in the Central Inventory.

Solution:

                Take a backup copy of oraInst.loc as oraInst.loc.bak

                Check $cat /etc/oraInst.loc file to know where you want to create OraInventory file.

                Give Read Permissions for all groups to the oraInventory file $chmod 644 oraInventory

                   $cd $ORACLE_HOME/oui/bin    (Go to Oracle Universal Installer)

                    [oravision@test bin]$ ./attachHome.sh

                    Starting Oracle Universal Installer...

                    Checking swap space: must be greater than 500 MB.   Actual 63999 MB    Passed

                   The inventory pointer is located at /etc/oraInst.loc

                   'AttachHome' was successful.

(OR)

               $./runInstaller -silent -attachHome ORACLE_HOME="/u01/oracle/product/12.1.0" ORACLE_HOME_NAME="Ora12CHome"

                

Backup Types and Differences

Full backup is complete backup of all the files (that includes every used data block in the file) on the database. RMAN copies all blocks into the backup set or image copy, skipping only data file blocks that have never been used. For a full Image copy, the entire file contents are reproduced exactly. You can perform a full backup with or without RMAN. If you do not use RMAN, then RMAN is called separately to catalog the backup. This means that you can use this backup as a reference backup for an incremental backup with RMAN. Most of the organizations only use them on a periodic basis because they are time consuming. A  Full backup is different from a whole database backup.

Whole backup

Backs up of the control file and all the database files belongs to a database, but this backup is not cataloged, which means that you cannot use it as a reference backup for an incremental backup with RMAN.

In a Full backup all datafile blocks are backed up. As it was said already, a full backup applies to one or more datafiles and up to all of them (database). The opposite of a full backup is an incremental backup. An incremental backup level 0 backs up all datafile blocks that have ever been used, and an incremental level 1 backs up anything that has changed afterwards. One can do full backups with either RMAN or O/S utilities.

A Full backup is non-incremental RMAN backup. Note that "full" does not refer to how much of the database is backed up, but to the fact that the backup is not incremental. Consequently, you can make a full backup of one data file.

Level1 backup: A level 1 backup includes only those blocks that have been changed since the "parent" backup was taken. Remember a parent backup may be either a level 0 or a level 1 backup.

Level 0 backup:  A level 0 incremental backup is physically identical to a full backup and it includes every data block in the file except empty blocks. The only difference is that the level 0 backup is recorded as an incremental backup in the RMAN repository, so it can be used as the parent for a level 1 backup.

Incremental backup:  An incremental backup can be either level 0 or level 1.

1.       Differential Incremental Backup:  When using differential incremental backup (the default type of incremental backup), RMAN looks for changed data blocks which were changed after last level 1 incremental backup. If there's no level 1 backup made before it, it takes a backup of the changed data blocks which were made after level 0 incremental backup.  Differential backups are faster because there are fewer changes stored, but they take longer at recovery time.

2.       Cumulative Incremental Backup:  In a cumulative incremental backup RMAN takes backup of all changed data blocks after level 0 or level 1 incremental backup.  Like a differential backup, incremental backups also back up only the changed data blocks, but an incremental backup only backs up the data that has changed since the last backup.  If the last backup was also an incremental backup, the current incremental backups only records "changes to the changes", a much smaller set of block changes, and hence, a much smaller recovery time than a differential backup.

Recovery Catalog

Recovery Catalog is a database schema that contains RMAN metadata for a set of target databases. Recovery catalog needs separate database or we can create one of the databases on the server. For creating catalog follow these steps.

On the Remote Database (Catalog Database): 

1. Create a user and grant connect,resource and recovery_catalog_owner privileges.
2. Create a tablespace or make a tablespace as default to the user and give unlimited quota on the tablespace.
3. Create a listener in Catalog database and TNS in target database.

On the Target Database (Local Database):

1. Connect to rman target database as $rman target / catalog rcat/rcat@tns
2. Create catalog as rman>create catalog;
3. Register database as rman>register database;
4. Then take the backup RMAN metadata will be stored in the Catalog Database which we have created.

RMAN Backup & Configuration

RMAN is a Oracle Utility to backup, restore and recovery of database.

RMAN configuration for first time:
       Let us assume the database is in NOARCHIVELOG mode (by default). We need to change it to ARCHIVELOG mode for RMAN backup configuration. We can configure RMAN backup with catalog/repository database as well as control file. It is strongly recommended & very good practice to configure RMAN backup with catalog/repository database. 
    
        Catalog/repository database: It’s central repository & it requires separate database for backup operation. All registered target databases information stored in catalog database.
Control file: It contains registered target database information at server level itself & RMAN utility directly connects to target database by command “RMAN target /”
Note: Create catalog/repository database with the help of DBCA.

Follow these steps to configure RMAN backup :

1. Connect to Target database(Target DB: The database on which Backup & Recovery to be performed) as sysdba.

          

Click here to see how Recovery Catalog was Created

Backup & Recovery

                   Backup & Recovery

A backup is a copy of data from your database that can be used to reconstruct that data. A backup is a safeguard against unexpected data loss and application errors. If you lose the original data, then you can reconstruct it by using a backup. Backups can be divided into physical backups and logical backups.

Physical backups are backups of the physical files used in storing and recovering your database, such as data files, control files, and archived redo logs. Ultimately, every physical backup is a copy of files storing database information to some other location, whether on disk or some offline storage such as tape. You can make physical backups with either the Recovery Manager (RMAN) utility or operating system utilities. In contrast, logical backups contain logical data (for example, tables and stored procedures) extracted with an Oracle utility and stored in a binary file. You can use logical backups to supplement physical backups.

Logical backups contain logical data (for example, tables or stored procedures) exported from a database with an Oracle export utility and stored in a binary file, for later re-importing into a database using the corresponding Oracle import utility.

There are two ways to perform Oracle backup and recovery: Recovery Manager and user-managed backup and recovery.

Recovery Manager (RMAN): is an Oracle utility that can back up, restore, and recover database files. It is a feature of the Oracle database server and does not require separate installation.

You can also use operating system commands for backups and SQL*Plus for recovery. This method, also called user-managed backup and recovery, is fully supported by Oracle, although use of RMAN is highly recommended because it is more robust and greatly simplifies administration.

Whether you use RMAN or user-managed methods, you can supplement your physical backups with logical backups of schema objects made using the Export utility. The utility writes data from an Oracle database to binary operating system files. You can later use Import to restore this data into a database.

Consistent and Inconsistent Backups

A consistent backup is one in which the files being backed up contain all changes up to the same system change number (SCN). This means that the files in the backup contain all the data taken from a same point in time. Unlike an inconsistent backup, a consistent whole database backup does not require recovery after it is restored.

An inconsistent backup is a backup of one or more database files that you make while the database is open or after the database has shut down abnormally.

Overview of Consistent Backups:

A consistent backup of a database or part of a database is a backup in which all read/write data files and control files are checkpointed with the same SCN.

The only way to make a consistent whole database backup is to shut down the database with the NORMAL, IMMEDIATE, or TRANSACTIONAL options and make the backup while the database is closed. If a database is not shut down cleanly, for example, an instance fails or you issue a SHUTDOWN ABORT statement, then the database's datafiles are always inconsistent—unless the database is a read-only database.

Oracle makes the control files and datafiles consistent to the same SCN during a database checkpoint. The only tablespaces in a consistent backup that are allowed to have older SCNs are read-only and offline normal tablespaces, which are still consistent with the other datafiles in the backup because no changes have been made to them.

The important point is that you can open the database after restoring a consistent whole database backup without needing recovery because the data is already consistent: no action is required to make the data in the restored datafiles correct. Hence, you can restore a year-old consistent backup of your database without performing media recovery and without Oracle performing instance recovery. Of course, when you restore a consistent whole database backup without applying redo, you lose all transactions that were made since the backup was taken.

A consistent whole database backup is the only valid backup option for databases operating in NOARCHIVELOG mode, because otherwise recovery is necessary for consistency. In NOARCHIVELOG mode, Oracle does not archive the redo logs, and so the required redo logs might not exist on disk. A consistent whole backup is also a valid backup option for databases operating in ARCHIVELOG mode. When this type of backup is restored and archived logs are available, you have the option of either opening the database immediately and losing transactions that were made since the backup was taken, or applying the archived logs to recover those transactions.

Overview of Inconsistent Backups:

An inconsistent backup is a backup in which the files being backed up do not contain all the changes made at all the SCNs. In other words, some changes are missing. This means that the files in the backup contain data taken from different points in time. This can occur because the datafiles are being modified as backups are being taken. Oracle recovery makes inconsistent backups consistent by reading all archived and online redo logs, starting with the earliest SCN in any of the datafile headers, and applying the changes from the logs back into the datafiles.

If the database must be up and running 24 hours a day, seven days a week, then you have no choice but to perform inconsistent backups of the whole database. A backup of online datafiles is called an online backup. This requires that you run your database in ARCHIVELOG mode.

If you run the database in ARCHIVELOG mode, then you do not have to back up the whole database at one time. For example, if your database contains seven tablespaces, and if you back up the control file as well as a different tablespace each night, then in a week you will back up all tablespaces in the database as well as the control file. You can consider this staggered backup as a whole database backup. However, if such a staggered backup must be restored, then you need to recover using all archived redo logs that were created since the earliest backup was taken.

Archiving Unarchived Redo Log Files

After an online backup or inconsistent closed backup, always ensure that you have the redo necessary to recover the backup by archiving the unarchived redo logs.

Backing Up the Archived Logs and the Control File

After open or inconsistent closed backups, Oracle recommends backing up all archived logs produced during the backup, and then backing up the control file after the backup completes. If you do not have all archived redo logs produced during the backup, then you cannot recover the backup because you do not have all the redo records necessary to make it consistent.

Whole Database and Partial Database Backups:

Whole Database Backups:

A whole database backup is a backup of every datafile in the database, plus the control file. Whole database backups are the most common type of backup.

Whole database backups can be taken in either ARCHIVELOG or NOARCHIVELOG mode. Before performing whole database backups, however, be aware of the implications of backing up in ARCHIVELOG and NOARCHIVELOG modes.

Whole Database Backup Options

A whole database backup is either a consistent backup or an inconsistent backup. Whether a backup is consistent determines whether you need to apply redo logs after restoring the backup.

Tablespace Backups

A tablespace backup is a backup of the datafiles that constitute the tablespace. For example, if tablespace users contains datafiles 2, 3, and 4, then a backup of tablespace usersbacks up these three datafiles.

Tablespace backups, whether online or offline, are valid only if the database is operating in ARCHIVELOG mode. The reason is that redo is required to make the restored tablespace consistent with the other tablespaces in the database.

Datafile Backups

A datafile backup is a backup of a single datafile. Datafile backups, which are not as common as tablespace backups, are valid in ARCHIVELOG databases. The only time a datafile backup is valid for a database in NOARCHIVELOG mode is if:

• Every datafile in a tablespace is backed up. You cannot restore the database unless all datafiles are backed up.
• The datafiles are read only or offline-normal.

RMAN and User-Managed Backups:

There are two types of backups: image copies and backup sets. An image copy is an exact duplicate of a datafile, control file, or archived log. You can create image copies of physical files with operating system utilities or RMAN, and you can restore them as-is without performing additional processing by using either operating system utilities or RMAN.

A backup set is a backup in a proprietary format that consists of one or more physical files called backup pieces. It differs from an image copy in that it can contain more than one database file, and it can also be backed up using special processing, such as compression or incremental backup. You must use RMAN to restore a backup set.

RMAN with Online Backups

Because the database continues writing to the file during an online backup, there is the possibility of backing up inconsistent data within a block. For example, assume that either RMAN or an operating system utility reads the block while database writer is in the middle of updating the block. In this case, RMAN or the copy utility could read the old data in the top half of the block and the new data in the bottom top half of the block. The block is a fractured block, meaning that the data in this block is not consistent.

During an RMAN backup, the Oracle database server reads the datafiles, not an operating system utility. The server reads each block and determines whether the block is fractured. If the block is fractured, then Oracle re-reads the block until it gets a consistent picture of the data.

When you back up an online datafile with an operating system utility (rather than with RMAN), you must use a different method to handle fractured blocks. You must first place the files in backup mode with the ALTER TABLESPACE BEGIN BACKUP statement (to back up an individual tablespace), or the ALTER DATABASE BEGIN BACKUP statement (to back up the entire database). After an online backup is completed, you must run the ALTER TABLESPACE ... END BACKUP or ALTER DATABASE END BACKUP statement to take the tablespace out of backup mode.

When updates are made to files in backup mode, additional redo data is logged. This additional data is needed to repair fractured blocks that might be backed up by the operating system utility.

Control File Backups

Backing up the control file is a crucial aspect of backup and recovery. Without a control file, you cannot mount or open the database.

You can instruct RMAN to automatically backup the control file whenever you run backup jobs. The command is CONFIGURE CONTROLFILE AUTOBACKUP. Because the autobackup uses a default filename, RMAN can restore this backup even if the RMAN repository is unavailable. Hence, this feature is extremely useful in a disaster recovery scenario.

You can make manual backups of the control file by using the following methods:

• The RMAN BACKUP CURRENT CONTROLFILE command makes a binary backup of the control file, as either a backup set or an image copy.
• The SQL statement ALTER DATABASE BACKUP CONTROLFILE makes a binary backup of the control file.
• The SQL statement ALTER DATABASE BACKUP CONTROLFILE TO TRACE exports the control file contents to a SQL script file. You can use the script to create a new control file. Trace file backups have one major disadvantage: they contain no records of archived redo logs, and RMAN backups and copies. For this reason, binary backups are preferable.

Archived Redo Log Backups

Archived redo logs are essential for recovering an inconsistent backup. The only way to recover an inconsistent backup without archived logs is to use RMAN incremental backups. To be able to recover a backup through the most recent log, every log generated between these two points must be available. In other words, you cannot recover from log 100 to log 200 if log 173 is missing. If log 173 is missing, then you must halt recovery at log 172 and open the database with the RESETLOGS option.

Because archived redo logs are essential to recovery, you should back them up regularly. If possible, then back them up regularly to tape.

You can make backups of archived logs by using the following methods:

• The RMAN BACKUP ARCHIVELOG command
• The RMAN BACKUP ... PLUS ARCHIVELOG command
• An operating system utility

Introduction to Recovery

To restore a physical backup of a datafile or control file is to reconstruct it and make it available to the Oracle database server. To recover a restored datafile is to update it by applying archived redo logs and online redo logs, that is, records of changes made to the database after the backup was taken. If you use RMAN, then you can also recover datafiles with incremental backups, which are backups of a datafile that contain only blocks that changed after a previous incremental backup.

After the necessary files are restored, media recovery must be initiated by the user. Media recovery involves various operations to restore, roll forward, and roll back a backup of database files.

Media recovery applies archived redo logs and online redo logs to recover the datafiles. Whenever a change is made to a datafile, the change is first recorded in the online redo logs. Media recovery selectively applies the changes recorded in the online and archived redo logs to the restored datafile to roll it forward.

To correct problems caused by logical data corruptions or user errors, you can use Oracle Flashback. Oracle Flashback Database and Oracle Flashback Table let you quickly recover to a previous time.

Unlike media recovery, Oracle performs crash recovery and instance recovery automatically after an instance failure. Crash and instance recovery recover a database to its transaction-consistent state just before instance failure. By definition, crash recovery is the recovery of a database in a single-instance configuration or an Oracle Real Application Clusters configuration in which all instances have crashed. In contrast, instance recovery is the recovery of one failed instance by a live instance in an Oracle Real Application Clusters configuration.

Overview of Media Recovery

The type of recovery that takes a backup and applies redo is called media recovery. Media recovery updates a backup to either to the current or to a specified prior time. Typically, the term "media recovery" refers to recovery of datafiles. Block media recovery is a more specialized operation that you use when just a few blocks in one or more files have been corrupted. In any case, you always use a restored backup to perform the recovery.

This section contains the following topics:

• Complete Recovery
• Incomplete Recovery
• Datafile Media Recovery
• Block Media Recovery

Complete Recovery

Complete recovery involves using redo data or incremental backups combined with a backup of a database, tablespace, or datafile to update it to the most current point in time. It is called complete because Oracle applies all of the redo changes contained in the archived and online logs to the backup. Typically, you perform complete media recovery after a media failure damages datafiles or the control file.

You can perform complete recovery on a database, tablespace, or datafile. If you are performing complete recovery on the whole database, then you must:

• Mount the database
• Ensure that all datafiles you want to recover are online
• Restore a backup of the whole database or the files you want to recover
• Apply online or archived redo logs, or a combination of the two

If you are performing complete recovery on a tablespace or datafile, then you must:

• Take the tablespace or datafile to be recovered offline if the database is open
• Restore a backup of the datafiles you want to recover
• Apply online or archived redo logs, or a combination of the two

Incomplete Recovery

Incomplete recovery, or point-in-time recovery, uses a backup to produce a noncurrent version of the database. In other words, you do not apply all of the redo records generated after the most recent backup. You usually perform incomplete recovery of the whole database in the following situations:

• Media failure destroys some or all of the online redo logs.
• A user error causes data loss, for example, a user inadvertently drops a table.
• You cannot perform complete recovery because an archived redo log is missing.
• You lose your current control file and must use a backup control file to open the database.

To perform incomplete media recovery, you must restore all datafiles from backups created prior to the time to which you want to recover and then open the database with the RESETLOGS option when recovery completes. The RESETLOGS operation creates a new incarnation of the database—in other words, a database with a new stream of log sequence numbers starting with log sequence 1.

Before using the OPEN RESETLOGS command to open the database in read/write mode after an incomplete recovery, it is a good idea to first open the database in read-only mode, and inspect the data to make sure that the database was recovered to the correct point. If the recovery was done to the wrong point, then it is easier to re-run the recovery if no OPENRESETLOGS has been done. If you open the database read-only and discover that not enough recovery was done, then just run the recovery again to the desired time. If you discover that too much recovery was done, then you must restore the database again and re-run the recovery.

Tablespace Point-in-Time Recovery

The tablespace point-in-time recovery (TSPITR) feature lets you recover one or more tablespaces to a point in time that is different from the rest of the database. TSPITR is most useful when you want to:

• Recover from an erroneous drop or truncate table operation
• Recover a table that has become logically corrupted
• Recover from an incorrect batch job or other DML statement that has affected only a subset of the database
• Recover one independent schema to a point different from the rest of a physical database (in cases where there are multiple independent schemas in separate tablespaces of one physical database)
• Recover a tablespace on a very large database (VLDB) rather than restore the whole database from a backup and perform a complete database roll-forward

TSPITR has the following limitations:

• You cannot use it on the SYSTEM tablespace, an UNDO tablespace, or any tablespace that contains rollback segments.
• Tablespaces that contain interdependent data must be recovered together. For example, if two tables are in separate tablespaces and have a foreign key relationship, then both tablespaces must be recovered at the same time; you cannot recover just one of them. Oracle can enforce this limitation when it detects data relationships that have been explicitly declared with database constraints. There could be other data relationships that are not declared with database constraints. Oracle cannot detect these, and the DBA must be careful to always restore a consistent set of tablespaces.

Incomplete Media Recovery Options

Because you are not completely recovering the database to the most current time, you must tell Oracle when to terminate recovery. You can perform the following types of media recovery.

Type of Recovery	Function
Time-based recovery	Recovers the data up to a specified point in time.
Cancel-based recovery	Recovers until you issue the CANCEL statement (not available when using Recovery Manager).
Change-based recovery	Recovers until the specified SCN.
Log sequence recovery	Recovers until the specified log sequence number (only available when using Recovery Manager).

Datafile Media Recovery

Datafile media recovery is used to recover from a lost or damaged current datafile or control file. It is also used to recover changes that were lost when a tablespace went offline without the OFFLINE NORMAL option. Both datafile media recovery and instance recovery must repair database integrity. However, these types of recovery differ with respect to their additional features. Media recovery has the following characteristics:

• Applies changes to restored backups of damaged datafiles.
• Can use archived logs as well as online logs.
• Requires explicit invocation by a user.
• Does not detect media failure (that is, the need to restore a backup) automatically. After a backup has been restored, however, detection of the need to recover it through media recovery is automatic.
• Has a recovery time governed solely by user policy (for example, frequency of backups, parallel recovery parameters, number of database transactions since the last backup) rather than by Oracle internal mechanisms.

The database cannot be opened if any of the online datafiles needs media recovery, nor can a datafile that needs media recovery be brought online until media recovery is complete. The following scenarios necessitate media recovery:

• You restore a backup of a datafile.
• You restore a backup control file (even if all datafiles are current).
• A datafile is taken offline (either by you or automatically by Oracle) without the OFFLINE NORMAL option.

Unless the database is not open by any instance, datafile media recovery can only operate on offline datafiles. You can initiate datafile media recovery before opening a database even when crash recovery would have sufficed. If so, crash recovery still runs automatically at database open.

Note that when a file requires media recovery, you must perform media recovery even if all necessary changes are contained in the online logs. In other words, you must still run recovery even though the archived logs are not needed. Media recovery could find nothing to do — and signal the "no recovery required" error — if invoked for files that do not need recovery.

Block Media Recovery

Block media recovery is a technique for restoring and recovering individual data blocks while all database files remain online and available. If corruption is limited to only a few blocks among a subset of database files, then block media recovery might be preferable to datafile recovery.

The interface to block media recovery is provided by RMAN. If you do not already use RMAN as your principal backup and recovery solution, then you can still perform block media recovery by cataloging into the RMAN repository the necessary user-managed datafile and archived redo log backups.

Overview of RMAN and User-Managed Restore and Recovery

You have a choice between two basic methods for recovering physical files. You can:

• Use the RMAN utility to restore and recover the database
• Restore backups by means of operating system utilities, and then recover by running the SQL*Plus RECOVER command

Whichever method you choose, you can recover a database, tablespace, or datafile. Before performing media recovery, you need to determine which datafiles to recover. Often you can use the fixed view V$RECOVER_FILE. This view lists all files that require recovery and explains the error that necessitates recovery.

RMAN Restore and Recovery

The basic RMAN recovery commands are RESTORE and RECOVER. Use RESTORE to restore datafiles from backup sets or from image copies on disk, either to their current location or to a new location. You can also restore backup sets containing archived redo logs, but this is usually unnecessary, because RMAN automatically restores the archived logs that are needed for recovery and deletes them after the recovery is finished. Use the RMAN RECOVER command to perform media recovery and apply archived logs or incremental backups.

RMAN automates the procedure for recovering and restoring your backups and copies.

User-Managed Restore and Recovery

If you do not use RMAN, then you can restore backups with operating system utilities and then run the SQL*Plus RECOVER command to recover the database. You should follow these basic steps:

1. After identifying which files are damaged, place the database in the appropriate state for restore and recovery. For example, if some but not all datafiles are damaged, then take the affected tablespaces offline while the database is open.
2. Restore the files with an operating system utility. If you do not have a backup, it is sometimes possible to perform recovery if you have the necessary redo logs dating from the time when the datafiles were first created and the control file contains the name of the damaged file.
   If you cannot restore a datafile to its original location, then relocate the restored datafile and change the location in the control file.
3. Restore any necessary archived redo log files.
4. Use the SQL*Plus RECOVER command to recover the datafile backups.

Recovery Using Oracle Flashback Technology

To correct problems caused by logical data corruptions or user errors, you can use Oracle Flashback. Flashback Database and Flashback Table let you quickly recover to a previous time.

Overview of Oracle Flashback Database

Oracle Flashback Database lets you quickly recover an Oracle database to a previous time to correct problems caused by logical data corruptions or user errors.

If an Oracle managed disk area, called a flash recovery area is configured, and if you have enabled the Flashback functionality, then you can use the RMAN and SQL FLASHBACKDATABASE commands to return the database to a prior time. Flashback Database is not true media recovery, because it does not involve restoring physical files. However, Flashback is preferable to using the RESTORE and RECOVER commands in some cases, because it is faster and easier, and does not require restoring the whole database.

To Flashback a database, Oracle uses past block images to back out changes to the database. During normal database operation, Oracle occasionally logs these block images in Flashback logs. Flashback logs are written sequentially, and they are not archived. Oracle automatically creates, deletes, and resizes Flashback logs in the flash recovery area. You only need to be aware of Flashback logs for monitoring performance and deciding how much disk space to allocate to the flash recovery area for Flashback logs.

The amount of time it takes to Flashback a database is proportional to how far back you need to revert the database, rather than the time it would take to restore and recover the whole database, which could be much longer. The before images in the Flashback logs are only used to restore the database to a point in the past, and forward recovery is used to bring the database to a consistent state at some time in the past. Oracle returns datafiles to the previous point-in-time, but not auxiliary files, such as initialization parameter files.

Overview of Oracle Flashback Table

Oracle Flashback Table lets you recover tables to a specified point in time with a single statement. You can restore table data along with associated indexes, triggers, and constraints, while the database is online, undoing changes to only the specified tables. Flashback Table does not address physical corruption; for example, bad disks or data segment and index inconsistencies.

Flashback Table works like a self-service repair tool. Suppose a user accidentally deletes some important rows from a table and wants to recover the deleted rows. You can restore the table to the time before the deletion and see the missing rows in the table with the FLASHBACK TABLE statement.

You can revert the table and its contents to a certain wall clock time or user-specified system change number (SCN). Use Flashback Table with Oracle Flashback Version query and Flashback Transaction Query to find a time to which the table should be restored back to.

For Flashback Table to succeed, the system must retain enough undo information to satisfy the specified SCN or timestamp, and the integrity constraints specified on the tables cannot be violated. Also, row movement must be enabled.

The point of time in the past that you use Flashback Table to go to is controlled by the undo retention of the system. Oracle Database 10g automatically tunes a parameter called the undo retention period. The undo retention period indicates the amount of time that must pass before old undo information—that is, undo information for committed transactions—can be overwritten. The database collects usage statistics and tunes the undo retention period based on these statistics and on undo tablespace size.

Other Types of Oracle Recovery

Overview of Redo Application

Database buffers in the buffer cache in the SGA are written to disk only when necessary, using a least-recently-used (LRU) algorithm. Because of the way that the database writer process uses this algorithm to write database buffers to datafiles, datafiles could contain some data blocks modified by uncommitted transactions and some data blocks missing changes from committed transactions.

Two potential problems can result if an instance failure occurs:

• Data blocks modified by a transaction might not be written to the datafiles at commit time and might only appear in the redo log. Therefore, the redo log contains changes that must be reapplied to the database during recovery.
• After the roll forward phase, the datafiles could contain changes that had not been committed at the time of the failure. These uncommitted changes must be rolled back to ensure transactional consistency. These changes were either saved to the datafiles before the failure, or introduced during the roll forward phase.

To solve this dilemma, two separate steps are generally used by Oracle for a successful recovery of a system failure: rolling forward with the redo log (cache recovery) and rolling back with the rollback or undo segments (transaction recovery).

Overview of Cache Recovery

The online redo log is a set of operating system files that record all changes made to any database block, including data, index, and rollback segments, whether the changes are committed or uncommitted. All changes to Oracle blocks are recorded in the online log.

The first step of recovery from an instance or disk failure is called cache recovery or rolling forward, and involves reapplying all of the changes recorded in the redo log to the datafiles. Because rollback data is also recorded in the redo log, rolling forward also regenerates the corresponding rollback segments

Rolling forward proceeds through as many redo log files as necessary to bring the database forward in time. Rolling forward usually includes online redo log files (instance recovery or media recovery) and could include archived redo log files (media recovery only).

After rolling forward, the data blocks contain all committed changes. They could also contain uncommitted changes that were either saved to the datafiles before the failure, or were recorded in the redo log and introduced during cache recovery.

Overview of Transaction Recovery

You can run Oracle in either manual undo management mode or automatic undo management mode. In manual mode, you must create and manage rollback segments to record the before-image of changes to the database. In automatic undo management mode, you create one or more undo tablespaces. These undo tablespaces contain undo segments similar to traditional rollback segments. The main difference is that Oracle manages the undo for you.

Undo blocks (whether in rollback segments or automatic undo tablespaces) record database actions that should be undone during certain database operations. In database recovery, the undo blocks roll back the effects of uncommitted transactions previously applied by the rolling forward phase.

After the roll forward, any changes that were not committed must be undone. Oracle applies undo blocks to roll back uncommitted changes in data blocks that were either written before the failure or introduced by redo application during cache recovery. This process is called rolling back or transaction recovery.

Oracle can roll back multiple transactions simultaneously as needed. All transactions systemwide that were active at the time of failure are marked as terminated. Instead of waiting for SMON to roll back terminated transactions, new transactions can recover blocking transactions themselves to get the row locks they need.

Overview of Instance and Crash Recovery

Crash recovery is used to recover from a failure either when a single-instance database fails or all instances of an Oracle Real Application Clusters database fail. Instance recovery refers to the case where a surviving instance recovers a failed instance in an Oracle Real Application Clusters database.

The goal of crash and instance recovery is to restore the data block changes located in the cache of the terminated instance and to close the redo thread that was left open. Instance and crash recovery use only online redo log files and current online datafiles. Oracle recovers the redo threads of the terminated instances together.

Crash and instance recovery involve two distinct operations: rolling forward the current, online datafiles by applying both committed and uncommitted transactions contained in online redo records, and then rolling back changes made in uncommitted transactions to their original state.

Crash and instance recovery have the following shared characteristics:

• Redo the changes using the current online datafiles (as left on disk after the failure or SHUTDOWN ABORT)
• Use only the online redo logs and never require the use of the archived logs
• Have a recovery time governed by the number of terminated instances, amount of redo generated in each terminated redo thread since the last checkpoint, and by user-configurable factors such as the number and size of redo log files, checkpoint frequency, and the parallel recovery setting

Oracle performs this recovery automatically on two occasions:

• At the first database open after the failure of a single-instance database or all instances of an Oracle Real Applications Cluster database (crash recovery).
• When some but not all instances of an Oracle Real Application Clusters configuration fail (instance recovery). The recovery is performed automatically by a surviving instance in the configuration.

The important point is that in both crash and instance recovery, Oracle applies the redo automatically: no user intervention is required to supply redo logs. However, you can set parameters in the database server that can tune the duration of instance and crash recovery performance. Also, you can tune the rolling forward and rolling back phases of instance recovery separately.

Deciding Which Recovery Technique to Use

When to Use Media Recovery

Use media recovery when one or more datafiles has been physically damaged. This can happen due to hardware errors or user errors, such as accidentally deleting a file. Complete media recovery is used with individual datafiles, tablespaces, or the entire database.

Use incomplete media recovery when the database has been logically damaged. This can happen due to application error or user error, such as accidentally deleting a table or tablespace. Incomplete media recovery is used only with the whole database, not with individual datafiles or tablespaces. (If you do not want to do incomplete media recovery of the entire database, you can do tablespace point-in-time recovery with individual tablespaces.)

Use block media recovery when a small number of blocks in one or more files have been physically damaged. This usually happens due to hardware errors, such as a bad disk controller, or operating system I/O errors. Block media recovery is used with individual data blocks, and the remainder of the database remains online and available during the recovery.

When to Use Oracle Flashback

Flashback Table is a push button solution to restore the contents of a table to a given point in time. An application on top of Flashback Query can achieve this, but with less efficiency.

Flashback Database applies to the entire database. It requires configuration and resources, but it provides a fast alternative to performing incomplete database recovery.

Flashback Table uses information in the undo tablespace to restore the table. This provides significant benefits over media recovery in terms of ease of use, availability, and faster restoration.

Flashback Database and Flashback Table differ in granularity, performance, and restrictions. For a primary database, consider using Flashback Database rather than Flashback Table in the following situations:

• There is a logical data corruption, particularly undo corruption.
• A user error affected the whole database.
• A user error affected a table or a small set of tables, but the impact of reverting this set of tables is not clear because of the logical relationships between tables.
• A user error affected a table or a small set of tables, but using Flashback Table would fail because of its DDL restrictions.
• Flashback Database works through all DDL operations, whereas Flashback Table does not. Also, because Flashback Database moves the entire database back in time, constraints are not an issue, whereas they are with Flashback Table. Flashback Table cannot be used on a standby database.

When to Use CREATE TABLE AS SELECT Recovery

To do an out of place restore of the data, perform a CTAS (CREATE TABLE AS SELECT … AS OF …) using the Flashback Query SQL "AS OF …" clause. For example, to create a copy of the table as of a specific time:

CREATE TABLE old_emp AS SELECT * 
FROM employees AS OF TIMESTAMP '2002-02-05  14:15:00'

For out of place creation of the table, you only get data back. Constraints, indexes, and so on are not restored. This could take significantly more time and space than Flashback Table. However, Flashback Table only restores rows in blocks that were modified after the specified time, making it more efficient.

When to Use Import/Export Utilities Recovery

In contrast to physical backups, logical backups are exports of schema objects, like tables and stored procedures, into a binary file. Oracle utilities are used to move Oracle schema objects in and out of Oracle. Export, or Data Pump Export, writes data from an Oracle database to binary operating system files. Import, or Data Pump Import, reads export files and restores the corresponding data into an existing database.

Although import and export are designed for moving Oracle data, you can also use them as a supplemental method of protecting data in an Oracle database. You should not use Oracle import and export utilities as the sole method of backing up your data.

Oracle import and export utilities work similarly to CTAS, but they restore constraints, indexes, and so on. They effectively re-create the whole table if an export was performed earlier corresponding to the Flashback time. Flashback Table is more performance efficient than import/export utilities, because it restores only the subset of rows that got modified.

When to Use Tablespace Point-in-Time Recovery

Use tablespace point-in-time recovery when one or more tablespaces have been logically damaged, and you do not want to do incomplete media recovery of the entire database. Tablespace point-in-time recovery is used with individual tablespaces.

Flash Recovery Area

The flash recovery area is an Oracle-managed directory, file system, or Automatic Storage Management disk group that provides a centralized disk location for backup and recovery files. Oracle creates archived logs in the flash recovery area. RMAN can store its backups in the flash recovery area, and it uses it when restoring files during media recovery. The flash recovery area also acts as a disk cache for tape.

Oracle recovery components interact with the flash recovery area ensuring that the database is completely recoverable using files in flash recovery area. All files necessary to recover the database following a media failure are part of flash recovery area.

Following is a list of recovery-related files in flash recovery area:

• Current control file
• Online logs
• Archived logs
• Flashback logs
• Control file autobackups
• Control file copies
• Datafile copies
• Backup pieces

Flash Recovery Area Disk Limit

Oracle lets you define a disk limit, which is the amount of space that Oracle can use in the flash recovery area. A disk limit lets you use the remaining disk space for other purposes and not to dedicate a complete disk for the flash recovery area. It does not include any overhead that is not known to Oracle. For example, the flash recovery area disk limit does not include the extra size of a file system that is compressed, mirrored, or some other redundancy mechanism.

Oracle and RMAN create files in the flash recovery area until the space used reaches the flash recovery area disk limit. Then, Oracle deletes the minimum set of existing files from the flash recovery area that are obsolete, redundant copies, or backed up to tertiary storage. Oracle warns the user when available disk space is less than 15%, but it continues to fill the disk to 100% of the flash recovery area disk limit.

The bigger the flash recovery area, the more useful it becomes. The recommended disk limit is the sum of the database size, the size of incremental backups, and the size of all archive logs that have not been copied to tape.

If the flash recovery area is big enough to keep a copy of the tablespaces, then those tablespaces do not need to access tertiary storage. The minimum size of the flash recovery area should be at least large enough to contain archive logs that have not been copied to tape. For example, if an ASM disk group of size 100 GB is used with normal redundancy for the flash recovery area, then the flash recovery area disk limit must be set to 50 GB.