Recover Datafile from Hot Backup - User Management - Part 2

Recover Datafile from Hot Backup - User Management - Part 2

Well,

Now I will make the recover in a different scenario.

I made the hot backup, my database is in Archive Log Mode and after the backup I had many DML(insert,update,delete) statements and I lost a non-critical datafile and my database must be set as online ASAP(As Soon As Possible).

Well how to make the hot backup using User Management check the Part 1.

Here I have the Tablespace TSCUSTOM with datafile CUSTOM.DBF

My user papa made many inserts in this tablespace that is his default tablespace and after database restart my datafile got crash, but I have 100 users needing to insert data in tablespace USERS so I can't let my database with downtime, so what I will do is:

SQL> startup
ORACLE instance started.

Total System Global Area  595591168 bytes
Fixed Size                  1220748 bytes
Variable Size             176164724 bytes
Database Buffers          411041792 bytes
Redo Buffers                7163904 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/u01/app/oracle/oradata/customdb/custom.dbf'

SQL> alter database datafile '/u01/app/oracle/oradata/customdb/custom.dbf' offline;

Database altered.

SQL> alter database open;

Database altered.

Now the Users can use our database.

So Let's recover our datafile 6, but first let's see what is the problem with him.

SQL> select name,error from v$datafile join v$recover_file using(file#);

NAME    ERROR
--------------------
/u01/app/oracle/oradata/customdb/custom.dbf
FILE NOT FOUND

Let's restore the file:

[oracle@test coldbkp]$ cp custom.dbf /u01/app/oracle/oradata/customdb/

SQL> recover datafile 6;
Media recovery complete.

SQL> alter database datafile 6 online;

Database altered.

SQL> conn custom/oracle
Connected.
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
T                              TABLE

Done.

Query to find users have same responsibilty from Backend

select usr.user_id, usr.user_name, res.RESPONSIBILITY_ID, res.RESPONSIBILITY_NAME

from apps.FND_USER usr, apps.FND_RESPONSIBILITY_TL res, apps.FND_USER_RESP_GROUPS grp

where upper(res.RESPONSIBILITY_NAME) like upper('%' || NVL('&EnterRespName', 'INV')|| '%')

and upper(res.RESPONSIBILITY_NAME) NOT like '%AX%'

and upper(res.RESPONSIBILITY_NAME) NOT like '%OPM%'

and grp.responsibility_id = res.responsibility_id

and grp.user_id = usr.user_id;

Query to find users all responsibilty from Backend

SELECT fu.user_name                "User Name",

       frt.responsibility_name     "Responsibility Name",

       furg.start_date             "Start Date",

       furg.end_date               "End Date",      

       fr.responsibility_key       "Responsibility Key",

       fa.application_short_name   "Application Short Name"

  FROM fnd_user_resp_groups_direct        furg,

       applsys.fnd_user                   fu,

       applsys.fnd_responsibility_tl      frt,

       applsys.fnd_responsibility         fr,

       applsys.fnd_application_tl         fat,

       applsys.fnd_application            fa

 WHERE furg.user_id             =  fu.user_id

   AND furg.responsibility_id   =  frt.responsibility_id

   AND fr.responsibility_id     =  frt.responsibility_id

   AND fa.application_id        =  fat.application_id

   AND fr.application_id        =  fat.application_id

   AND frt.language             =  USERENV('LANG')

   AND UPPER(fu.user_name)      =  UPPER('ARUN')  -- <change it>

   -- AND (furg.end_date IS NULL OR furg.end_date >= TRUNC(SYSDATE))

 ORDER BY frt.responsibility_name;