DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Tuesday, March 29, 2016

Recover Datafile from Hot Backup
==========================


[oracle@test ~]$ cd /u01/app/oracle/oradata/proddb/

[oracle@test proddb]$ ls -l
total 1035664
-rw-r----- 1 oracle oinstall   7061504 Jul 27 10:00 control01.ctl
-rw-r----- 1 oracle oinstall   7061504 Jul 27 10:00 control02.ctl
-rw-r----- 1 oracle oinstall   7061504 Jul 27 10:00 control03.ctl
-rw-r----- 1 oracle oinstall 104865792 Jul 27 09:56 example01.dbf
-rw-r----- 1 oracle oinstall  52429312 Jul 27 09:59 redo01.log
-rw-r----- 1 oracle oinstall  52429312 Jul 27 09:49 redo02.log
-rw-r----- 1 oracle oinstall  52429312 Jul 27 09:49 redo03.log
-rw-r----- 1 oracle oinstall 241180672 Jul 27 09:56 sysaux01.dbf
-rw-r----- 1 oracle oinstall 503324672 Jul 27 09:56 system01.dbf
-rw-r----- 1 oracle oinstall  20979712 Jul 25 15:11 temp01.dbf
-rw-r----- 1 oracle oinstall  26222592 Jul 27 09:56 undotbs01.dbf
-rw-r----- 1 oracle oinstall   5251072 Jul 27 09:56 users01.dbf


SQL> startup

SQL> alter database begin backup;

Database altered.

Let's Copy take backup of datafiles.

[oracle@test proddb]$ cp *.dbf /u01/coldbkp/
[oracle@test proddb]$

Now we can check which files is been in backup state:

SQL> select * from v$backup;

     FILE# STATUS                CHANGE# TIME
---------- ------------------ ---------- ---------
         1 ACTIVE                 485758 06-JAN-16
         2 ACTIVE                 485758 06-JAN-16
         3 ACTIVE                 485758 06-JAN-16
         4 ACTIVE                 485758 06-JAN-16
         5 ACTIVE                 485758 06-JAN-16


Let's close the backup state.

SQL> alter database end backup;

Database altered.

Well now I will shutdown my database end drop one datafile.

Remember in this case didn't  happens any log switch.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

I will remove USERS01.DBF :

[oracle@test proddb]$ rm users01.dbf

And let's Start.

SQL> startup

Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/u01/app/oracle/oradata/proddb/users01.dbf'

As expect  error, ok in this case my database will be in MOUNT state so I just need to restore the file manually.

[oracle@test coldbkp]$ cp users01.dbf /u01/app/oracle/oradata/proddb/

If you try to open directly.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: '/u01/app/oracle/oradata/proddb/users01.dbf'

SQL> recover datafile 4;
Media recovery complete.

SQL> alter database open;

Database altered.

Done.

No comments: