What is a Flash / Fast Recovery Area and how to configure it

What is a Flash / Fast Recovery Area and how to configure it

1. What is a 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. All the files which is needed for complete recovery of the database from a media failure are part of the Flash Recovery Area. Oracle creates archived logs and flashback logs in the flash recovery area. RMAN can store its backup sets and image copies 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.

Flash recovery extends the functionality of Oracle Managed Files to all recovery related files (backup sets, image copies, and archived logs). Oracle Database automatically manages this storage, deleting files that are no longer needed. The user specifies only the location of a Flash Recovery Area, and the amount of disk space that Oracle is allowed to use for recovery related files. This feature is integrated with the 10G MMON feature, so that out of space conditions can be handled through the standard Oracle monitoring framework.

Periodically copying backups to tape frees space in the flash recovery area for other files. Oracle recommends that enable a recovery area to simplify backup management.

The following recovery-related files are stored in the flash recovery area: 

v  Current control file

v  Online redo logs

v  Archived redo logs

v  Flashback logs

v  Control file autobackups

v  Datafile and control file copies

v  Backup pieces

v  Foreign archived redo log ( An archived redo log received by a logical standby database for a LogMiner session.)

2. Why should we use a Flash Recovery Area?

As disk storage media is now competitive to tape with respect to purchase costs, disk-based backup is an optimal and preferable storage mechanism. By using disks for storage purposes, we gain significant benefits in terms of mass storage, and we can randomly access our data in milliseconds rather than hours. The previously expensive downtime in traditional recovery is exchanged for rapid data access and recovery times using cheap disk space.

The advantage that we have over tape is that tape is a sequential access device and disk is a random access device. Hence the amount of time needed for restoring from the tape is eliminated or reduced.

3. What should be the location of Flash Recovery Area?

The flash recovery area should place on a separate disk from the working set of database files. Otherwise, the disk becomes a single point of failure for our database.

4. What should be the size of Flash Recovery Area ?

The larger the flash recovery area is, the more useful it becomes. Ideally, the flash recovery area should be large enough to contain the required files. The recovery area should be able to contain a copy of all datafiles in the database and the incremental backups used by our chosen backup strategy.

If providing this much space is impractical, then it is best to create an area large enough to keep a backup of the most important tablespaces and all the archived logs not yet on tape. At an absolute minimum, the flash recovery area must be large enough to contain the archived redo logs not yet on tape. If the recovery area has insufficient space to store flashback logs and meet other backup retention requirements, then the recovery area may delete flashback logs to make room.

Formulas for estimating a useful flash recovery area size depend on whether:

v  Database has a small or large number of data blocks that change frequently.

v  Store backups only on disk, or on disk and tape.

v  Use a redundancy-based backup retention policy, or a recovery window-based retention policy.

v  Plan to use Flashback Database or a guaranteed restore point as alternatives to point-in-time recovery.
 

If we plan to enable flashback logging, then note that the volume of flashback log generation is approximately the same order of magnitude as redo log generation. For example, if we intend to set DB_FLASHBACK_RETENTION_TARGET to 24 hours, and if the database generates 20 GB of redo in a day, then a rule of thumb is to allow 20 GB to 30 GB disk space for the flashback logs. The same rule applies to guaranteed restore points when flashback logging is enabled. For example, if the database generates 20 GB redo every day, and if the guaranteed restore point will be kept for a day, then plan to allocate 20 to 30 GB.

For an example suppose that we want to determine the size of a flash recovery when the backup retention policy is set to REDUNDANCY 1 and we intend to follow the Oracle Suggested Strategy of using an incrementally updated backup. We use the following formula to estimate the disk quota

Disk Quota =
Size of a copy of database +
Size of an incremental backup +
Size of (n+1) days of archived redo logs +
Size of (y+1) days of foreign archived redo logs (for logical standby) +
Size of control file +
Size of an online redo log member * number of log groups +
Size of flashback logs (based on DB_FLASHBACK_RETENTION_TARGET value)
Where n is the interval in days between incremental updates and y is the delay in applying the foreign archived redo logs on a logical standby database:

5. Can I have same Flashback Recovery Area for multiple databases?

Multiple databases can have the same value for DB_RECOVERY_FILE_DEST, but one of the following must be true:

v  No two databases for which the DB_UNIQUE_NAME initialization parameters are specified have the same value for DB_UNIQUE_NAME.

v  For those databases where no DB_UNIQUE_NAME is provided, no two databases have the same value for DB_NAME.
When databases share a single recovery area in this way, the location should be large enough to hold the files for all databases. Add the values for DB_RECOVERY_FILE_DEST_SIZE for the databases, then allow for overhead such as mirroring or compression.

6. ASM (Automatic Storage Management) support for Flash Recovery Area:

The Flash Recovery Area can be an ASM disk group. RMAN can back up and restore files that are stored in ASM disk groups. In fact, RMAN is the only way to back up and restore ASM files. Backup sets and image copies can be created in ASM, either by automatically creating them in the Flash Recovery Area, or by explicitly specifying an ASM disk group for their location.

7. OMF (Oracle Managed Files) support for Flash Recovery Area:

Backup/Restore of OMF files is easier with RMAN as RMAN does not use the long OMF file names in any commands.
Backup sets and image copies can be created as OMF files by creating them in the Flash Recovery Area.
RMAN can be used to migrate existing files to OMF.

8. Flash Recovery Area for RAC:

For RAC database the location and disk quota must be the same on all instances. To accomplish this, Oracle recommends that we place the flash recovery area on the shared ASM disks. In addition, we must set the DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE parameters to the same values on all instances.
To use the Flash Recovery feature, we must first configure the flash recovery area for each instance in our Oracle RAC cluster.

9. What type of files can be stored in Flash Recovery Area?

The files in Flash Recovery Area are classified as permanent or transient.

v  Permanent Files

The permanent files (assuming these are configured to be stored in the recovery area) are multiplexed copies of the current control file and online redo logs. These cannot be deleted without causing the instance to fail.

v  Transient Files

Transient files include archived redo logs, datafile copies, control file copies, control file autobackups, backup pieces and flashback logs. Oracle manages these files automatically for deletion whenever space is required in the Flash Recovery Area. They are deleted once they become obsolete under the retention policy or have been backed up to tape. Any transient file in the flash recovery area once backed up to tape even if not deleted are internally placed on a file can be deleted list. Until there is a backup of the file on disk made to a tertiary storage device it cannot be obsolete.

9. Initialization Parameters required for Flash Recovery Area

To enable the Flash Recovery Area, we must set the two initialization parameters:

--DB_RECOVERY_FILE_DEST_SIZE:

It is the disk limit, which is the amount of space the flash recovery area is permitted to use. 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.
Note: This value does not include certain kinds of disk overhead:
-Block 0 or the OS block header of each Oracle file is not included in this size, so make sure to allow an extra 10% for this data when computing the actual disk usage required for the Flash Recovery Area.
-DB_RECOVERY_FILE_DEST_SIZE does not indicate the real size occupied on disk when the underlying filesystem is mirrored, compressed, or in some other way affected by overhead not known to Oracle.
--DB_RECOVERY_FILE_DEST:
This initialization parameter is a valid destination to create the Flash Recovery Area. The destination can be defined as a directory, file system, or ASM disk group.

Note1: The Flash Recovery Area cannot be stored on a raw file system.
Note2 : DB_RECOVERY_FILE_DEST_SIZE must be set before DB_RECOVERY_FILE_DEST.

10. Restrictions on Initialization Parameters for Flash Recovery Area

- We cannot use the LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST parameters to specify redo log archive destinations. We must always use the LOG_ARCHIVE_DEST_n parameters in case we have configured flash recovery area.
- LOG_ARCHIVE_DEST_10 is implicitly set to USE_DB_RECOVERY_FILE_DEST if we create a recovery area and do not set any other local archiving destinations.
- Multiple database can have the same DB_RECOVERY_FILE_DEST only if the DB_NAME are different or if the DB_NAME is same (example the primary and standby database) then the DB_UNIQUE_NAME parameter must be different for the databases.
- For RAC the location of Flash Recovery Area must be on a cluster file system, ASM or a shared directory configured through NFS. The location and disk quota must be the same on all instances.

Oracle Identity Manager Tables and SQL Queries

Oracle Identity Manager Tables and Description/SQL Queries

Table Name	Table Description
OIU	Object Instance Request Target User Information. Associate user information to the resource object instance when provisioning take places.
OST	Object Status Information.
OBI	Object Instance Information. Once resource provisioned to user, OIM created resource instance for each resource provisioning.
OBJ	Resource Object definition information This contains detail about resource such as resource name, auto-save enable or not and auto-prepopulate is enable or not, and whether or not the resource object allows multiple instances.
USR	It contains user information like login id, password, etc.,
ORCHPROCESS	Stores the process instances that are being executed.
ORCHEVENTS	Stores event handler names, status and result for all orchestration processes. Event status like COMPLETED, FAILED, PENDING, etc.
ORCHFAILEDEVENTS	Stores event handler information that are executed because of failures in main flow.
UPA	User profile audit information
USG	Role assigned to user

Query to list the resource that are in different status for given user:-

 SELECT oiu.oiu_key,

       oiu.obi_key,

       oiu.orc_key,

       ost.ost_status,

       obj.obj_name,

       obj.obj_key,

       oiu.req_key

  FROM oiu

       INNER JOIN ost ON oiu.ost_key = ost.ost_key

       INNER JOIN obi ON oiu.obi_key = obi.obi_key

       INNER JOIN obj ON obi.obj_key = obj.obj_key

 WHERE oiu.usr_key = (SELECT usr_key

                        FROM usr

                       WHERE usr_login = 'TSTUSR01'); 

  

Changing Resource Status in OIM in Account Tab:-

Through below query we can change the resource (AD, LDAP, Exchange etc..) Status.

UPDATE oiu

   SET ost_key =

           (SELECT ost_key

              FROM ost

             WHERE     obj_key = (SELECT obj_key

                                    FROM obj

                                   WHERE obj.obj_name = 'LDAP User')

                   AND ost_status = 'Revoked')

 WHERE oiu.orc_key IN (SELECT orc_key

                         FROM ud_ldap_usr

                        WHERE ud_ldap_usr_userid IN ('TSTUSR01')); 

Force users to change password on next login:-

In case user’s password reset by either OIM Admin or API, user will be prompt to reset on next login. to avoid the force user password on next login by update column 'USR_CHANGE_PWD_AT_NEXT_LOGON' in table ‘usr’. Column takes values 0 or 1. 

The column value 0 means User not forced to reset password on next login.

 UPDATE usr

   SET USR_CHANGE_PWD_AT_NEXT_LOGON = '0'

 WHERE usr_login = 'TSTUSR01';

 The column value 1 means User forced to reset password on next login.

 UPDATE usr

   SET USR_CHANGE_PWD_AT_NEXT_LOGON = '1'

 WHERE usr_login = ‘TSTUSR01’;

Orchestration Query:-

Orchestration is main Component in OIM, Operations, such as create user, modify user, Delete, Enable etc., were closely integrate with OIM Orchestration.

Known Issue: OIM Orchestration will retry failed event handlers ONLY 2 times and will ignore after that. Because, the retry limit was hard coded in OIM.

SQL Query:

Below sql query is to get list of event handlers, which are executed for a particular users during enable process:

This query used to get user key from usr table-

SELECT usr_key

  FROM USR

 WHERE usr_login = 'TSTUSR01'; 

This query get process instance of enabled user ‘TSTUSR01’

SELECT id

  FROM orchprocess

WHERE entityid = '1045' AND entitytype = 'User' AND operation = 'ENABLE';

This query gets all the event handler for enabled user ‘TSTUSR01’:-

SELECT *

    FROM orchevents

   WHERE processid = ’237254’

ORDER BY orchorder; 

In the same way we can use for Create, Modify, Delete, Disable...etc Operation.

Query to find who modified user attributes:-

We can identify when and who made change for user profile attributes for example, email address.

Below query fetch the email address value for user ‘TSTUSR01’ from audit table: 

SELECT field_name, field_old_value, field_new_value

  FROM upa_fields fields

 WHERE upa_usr_key IN

           (SELECT upa_key

              FROM upa

             WHERE upa_key IN (SELECT usr_key

                                 FROM usr

                                WHERE LOWER (usr_login) LIKE 'TSTUSR01'))

and field_name = 'Users.Email'

order by upa_usr_key, field_name;

Query to find who assigned role to users:-

OIM provides strong auditing features that will capture all user profile modification. It will be stored on UPA table.

Below query gets list of roles when was assigned to user ‘TSTUSR01’:

SELECT *

  FROM upa

 WHERE     usr_key = (SELECT usr_key

                        FROM usr

                       WHERE LOWER (usr_login) = 'TSTUSR01')

       AND src LIKE '%RoleManager%CREATE%'; 

Similarly, we can check for user role revoked by using src with ‘%RoleManager%DELETE%'

Oracle Schema Version Registry:-

Most of the Oracle Fusion Middleware components require existence of schemas in database prior to install. These schemas created and loaded using RCU. 

You can run query to get list of schema created though RCU:

SELECT * FROM schema_version_registry;

Query to get users whose specific role:-

We often may need to find user who has specific role in OIM.

I have used query to get users who have role called ‘System Administrator’.

SELECT usr.usr_display_name,

       usr.usr_login,

       usr.usr_email,

       ugp.ugp_name

  FROM usg  usg

       LEFT OUTER JOIN usr usr ON (usg.usr_key = usr.usr_key)

       LEFT OUTER JOIN ugp ugp ON (ugp.ugp_key = usg.ugp_key)

 WHERE UPPER (ugp_name) IN (UPPER ('System Administrator')); 

Query to update the ldap common name on process form:-

UPDATE ud_ldap_usr

   SET UD_LDAP_USR_COMMON_NAME = 'Tst01 User01'

 WHERE UD_LDAP_USR_USERID = 'TSTUSR01'

Query to find all the Schedule tasks:-

SELECT * FROM QRTZ92_JOB_DETAILS          --contains list of all schedule task

SELECT * FROM JOB_HISTORY   --Contains the details about the execution history

Query to list the resource that are in different status for given user:-

SELECT oiu.oiu_key,

       oiu.obi_key,

       oiu.orc_key,

       ost.ost_status,

       obj.obj_name,

       obj.obj_key,

       oiu.req_key

  FROM oiu

       INNER JOIN ost ON oiu.ost_key = ost.ost_key

       INNER JOIN obi ON oiu.obi_key = obi.obi_key

       INNER JOIN obj ON obi.obj_key = obj.obj_key

 WHERE oiu.usr_key = (SELECT usr_key

                        FROM usr

                       WHERE usr_login = 'MUQTHIYAR.PASHA');

Changing Resource Status in OIM in Account Tab:-

Through below query we can change the resource (AD, LDAP, Exchange etc..) Status.

UPDATE oiu

   SET ost_key =

           (SELECT ost_key

              FROM ost

             WHERE     obj_key = (SELECT obj_key

                                    FROM obj

                                   WHERE obj.obj_name = 'LDAP User')

                   AND ost_status = 'Revoked')

 WHERE oiu.orc_key IN (SELECT orc_key

                         FROM ud_ldap_usr

                        WHERE ud_ldap_usr_userid IN ('TESTUSR01'));

In the same way we can use for Create, Modify, Delete, Disable...etc Operation.

Query to find who modified user attributes:-

We can identify when and who made change for user profile attributes for example, email address.

Below query fetch the email address value for user ‘TSTUSR01’ from audit table:

SELECT field_name, field_old_value, field_new_value

    FROM upa_fields fields

   WHERE     upa_usr_key IN

                 (SELECT upa_key

                    FROM upa

                   WHERE upa_key IN

                             (SELECT usr_key

                                FROM usr

                               WHERE LOWER (usr_login) LIKE 'MUQTHIYAR.PASHA'))

         AND field_name = 'Users.Email'

ORDER BY upa_usr_key, field_name;

Query to Generate Audit Report of Users:-

 SELECT b.usr_login     LoginID,

         a.field_name    attribute_Name,

         a.field_old_value Old_Value,

         a.field_new_value New_Value,

         a.create_date   CreateDate,

         a.update_date   UpdateDate

    FROM PRDOIM_OIM.usr b, PRDOIM_OIM.upa_usr c, PRDOIM_OIM.upa_fields a

   WHERE     1 = 1

         AND c.usr_key = b.usr_key

         AND a.upa_usr_key = c.upa_usr_key

         AND a.field_name != 'Users.Role'

ORDER BY a.UPA_USR_KEY DESC;

Query to get info on all the 'Active' users:-

  SELECT U.USR_FIRST_NAME || ' ' || U.USR_LAST_NAME

             Name,

         U.USR_LOGIN

             LoginName,

         R.UGP_DISPLAY_NAME

             RoleName,

         A.STATUS

             RoleStatus,

         A.USG_UPDATEBY_LOGIN

             Assigned_RevokedBy_LoginName,

         USR.USR_DISPLAY_NAME

             Assigned_RevokedBy_Name,

         A.CREATE_DATE

             Assigned_RevokedDate

    FROM PRDOIM_OIM.UPA_USR           U,

         PRDOIM_OIM.UGP               R,

         PRDOIM_OIM.UPA_GRP_MEMBERSHIP A,

         PRDOIM_OIM.USR               USR

   WHERE     U.USR_STATUS = 'Active'

         AND U.USR_LOGIN NOT IN ('oim_write',

                                 'XELSYSADM',

                                 'FAAdmin',

                                 'OCLOUD9_OSN_APPID',

                                 'FUSION_APPS_HCM_SOA_SPML_APPID',

                                 'oamAdminUser',

                                 'weblogic_idm')

         AND A.UPA_USR_KEY = U.UPA_USR_KEY

         AND A.UGP_KEY = R.UGP_KEY

         AND A.USG_UPDATEBY_LOGIN = USR.USR_LOGIN

ORDER BY A.CREATE_DATE, A.STATUS, R.UGP_DISPLAY_NAME;

Query to get info for a single user:-

SELECT U.USR_FIRST_NAME || ' ' || U.USR_LAST_NAME

             Name,

         U.USR_LOGIN

             LoginName,

         R.UGP_DISPLAY_NAME

             RoleName,

         A.STATUS

             RoleStatus,

         A.USG_UPDATEBY_LOGIN

             Assigned_RevokedBy_LoginName,

         USR.USR_DISPLAY_NAME

             Assigned_RevokedBy_Name,

         A.CREATE_DATE

             Assigned_RevokedDate

    FROM PRDOIM_OIM.UPA_USR           U,

         PRDOIM_OIM.UGP               R,

         PRDOIM_OIM.UPA_GRP_MEMBERSHIP A,

         PRDOIM_OIM.USR               USR

   WHERE     U.USR_STATUS = 'Active'

         AND U.USR_LOGIN = 'MUQTHIYAR.PASHA'

         AND A.UPA_USR_KEY = U.UPA_USR_KEY

         AND A.UGP_KEY = R.UGP_KEY

         AND A.USG_UPDATEBY_LOGIN = USR.USR_LOGIN

ORDER BY A.CREATE_DATE, A.STATUS, R.UGP_DISPLAY_NAME;