Friday, 5 December 2014

restoring users.dbf from from oracle physical backup location

[oracle@oralinux ORCL1]$ rm users01.dbf

Recovering users01.dbf file if corrupted or lost

[oracle@oralinux ORCL1]$ sqlplus "/as sysdba"
SQL*Plus: Release Production on Fri Dec 5 18:58:11 2014
Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> conn scott/tiger
SQL> create table emp9 as select * from emp;
create table emp9 as select * from emp
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/u01/app/oracle/oradata/ORCL1/users01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

SQL> conn sys as sysdba
Enter password:

1) Make it offline
SQL> alter database datafile '/u01/app/oracle/oradata/ORCL1/users01.dbf' offline;
Database altered.
2)Copy the users01.dbf file from backup location
SQL> !cp /u01/app/oracle/backups/users01.dbf /u01/app/oracle/oradata/ORCL1/
3)Recover the datafile
SQL> recover datafile '/u01/app/oracle/oradata/ORCL1/users01.dbf';
Media recovery complete.

4)Make if online
SQL> alter database datafile '/u01/app/oracle/oradata/ORCL1/users01.dbf' online;
Database altered.

SQL> conn scott/tiger

Your datafile recovered ...

Popular Posts