Flashback database in Oracle 11g

You can use the FLASHBACK DATABASE command to rewind the database to a target time, SCN, or log sequence number. This command works by undoing changes made by Oracle Database to the data files that exist when you run the command. 

FLASHBACK DATABASE is usually much faster than a RESTORE operation followed by point-in-time recovery, because the time needed to perform FLASHBACK DATABASE depends on the number of changes made to the database since the desired flashback time. On the other hand, the time needed to do a traditional point-in-time recovery from restored backups depends on the size of the database.

The limitation of FLASHBACK is, it can not fix the physical failures; In a day to day scenario, this is unlikely. But, DBAs should be aware of it.

Is Flashback Enabled?

Use the below query to find out whether the FLASHBACK is enabled or not.

select flashback_on from v$database; 

How to Enable FLASHBACK?

ALTER DATABASE FLASHBACK ON;

ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=5760; 

CREATE RESTORE POINT Mil_ref_point GUARANTEE FLASHBACK DATABASE;

DB_FLASHBACK_RETENTION_TARGET --> The Parameter (in minutes) defines the upper limit how far the database can be flashed back.

How do we FLASHBACK?

Please make sure the point of time, you need to restore.

1. shutdown immediate;
2. startup mount
3. FLASHBACK DATABASE TO RESTORE POINT Mil_ref_point;
4. alter database open resetlogs;

How to Disable the FLASHBACK?

ALTER DATABASE FLASHBACK OFF;

How to know the Oldest Snapshot the database can be flashback to? 

SELECT oldest_flashback_time FROM v$flashback_database_log;

Change Username in Oracle EBS

It's a kind of rare requirement to change the username in EBS.  This can be accomplished by using 2 methods.

1. Either by using Front end.
2. Or by using the Oracle Provided Package APPS.FND_USER_PKG.

It is not recommended to update any FND_USER column by using direct UPDATE SQL.  Oracle uses this column to maintain the foreign key relationship with few WF tables(For Ex: WF_LOCAL_USER_ROLES)

1. By using Front end.

           Login to ERP -->  System Administrator  --> Security --> User --> Define.  It launches Java form similar to the below screenshot.

            

Press F11, Enter the old username and search by using Ctrl + F11.  Change the User Name field to New value.  Don't forget to save. 

      2. By using FND_USER_PKG

begin

     fnd_user_pkg.change_user_name(x_old_user_name => 'oldusername', x_new_user_name => 'new_user_name');

     commit;

end;

/

This is really helpful when you are integrating EBS with OID or IDAM.