RECOVERING THE DATABASE IF USER DATAFILE IS LOST OR CORRUPTD


[oracle@AZDB ~]$ export ORACLE_SID=ANARDB
[oracle@AZDB ~]$ sqlplus sys as sysdba
SQL*Plus: Release 10.2.0.1.0 – Production on Fri NOV 22 17:23:18 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter password:
Connected to an idle instance.

SQL> startup;
ORACLE instance started.
Total System Global Area 348127232 bytes
Fixed Size 1219328 bytes
Variable Size 289408256 bytes
Database Buffers 50331648 bytes
Redo Buffers 7168000 bytes
Database mounted.
Database opened.

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/ANARDB/archive
Oldest online log sequence 2
Next log sequence to archive 3
Current log sequence 3

SQL> select name from v$datafile;
NAME
——————————————————————————–
/home/oracle/ANARDB/datafiles/system01.dbf
/home/oracle/ANARDB/datafiles/undotbs1.dbf
/home/oracle/ANARDB/datafiles/sysaux01.dbf
/home/oracle/ANARDB/datafiles/users01.dbf

SQL> select name from v$controlfile;
NAME
——————————————————————————–
/home/oracle/ANARDB/control/a1.ctl

SQL> show parameter pfile;
NAME TYPE VALUE
———————————— ———– ——————————
spfile string /home/oracle/oracle/product/10.2.0/db_1/dbs/spfileANARDB.ora

SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
——————————
SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS

SQL> alter tablespace system begin backup;
Tablespace altered.

SQL> alter tablespace undotbs1 begin backup;
Tablespace altered.

SQL> alter tablespace sysaux begin backup;
Tablespace altered.

SQL> alter tablespace users begin backup;
Tablespace altered.

[oracle@AZDB ~]$ mkdir backup
[oracle@AZDB ~]$
[oracle@AZDB ~]$ cd ANARDB/
[oracle@AZDB ANARDB]$ ls
archive bdump control datafiles dbc.sql udump

[oracle@AZDB ANARDB]$ cd control/
[oracle@AZDB control]$ ls
a1.ctl

[oracle@AZDB control]$ cp *.ctl /home/oracle/backup/
[oracle@AZDB control]$ cd ..
[oracle@AZDB ANARDB]$ cd datafiles/
[oracle@AZDB datafiles]$ ls
redo01.log redo02.log sysaux01.dbf system01.dbf temp01.dbf undotbs1.dbf users01.dbf

[oracle@AZDB datafiles]$ cp *.dbf /home/oracle/backup/
[oracle@AZDB datafiles]$ cd ..
[oracle@AZDB ANARDB]$ cd archive/
[oracle@AZDB archive]$ ls
arch_1_669569864_1.arc arch_1_669569864_2.arc

[oracle@AZDB archive]$ cp *.arc /home/oracle/backup/
[oracle@AZDB archive]$ cd $ORACLE_HOME/dbs
[oracle@AZDB dbs]$ ls
initANARDB.ora spfileANARDB.ora

[oracle@AZDB dbs]$ cp initANARDB.ora /home/oracle/backup/
[oracle@AZDB dbs]$ cp spfileANARDB.ora /home/oracle/backup/
[oracle@AZDB dbs]$ cd /home/oracle/backup/
[oracle@AZDB backup]$ ls
arch_1_669569864_1.arc a1.ctl spfileANARDB.ora system01.dbf undotbs1.dbf
arch_1_669569864_2.arc initANARDB.ora sysaux01.dbf temp01.dbf users01.dbf

[oracle@AZDB backup]$ cd
[oracle@AZDB ~]$ exit
exit
SQL> alter tablespace system end backup;
Tablespace altered.

SQL> alter tablespace sysaux end backup;
Tablespace altered.

SQL> alter tablespace undotbs1 end backup;
Tablespace altered.

SQL> alter tablespace users end backup;
Tablespace altered.

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

SQL> exit

[oracle@AZDB ~]$ cd ANARDB/
[oracle@AZDB ANARDB]$ cd datafiles/
[oracle@AZDB datafiles]$ ls
redo01.log redo02.log sysaux01.dbf system01.dbf temp01.dbf undotbs1.dbf users01.dbf

[oracle@AZDB datafiles]$ rm -rf u
undotbs1.dbf users01.dbf

[oracle@AZDB datafiles]$ rm -rf users01.dbf
[oracle@AZDB datafiles]$ cd
[oracle@AZDB ~]$ exit
exit

SQL> startup;
ORACLE instance started.
Total System Global Area 348127232 bytes
Fixed Size 1219328 bytes
Variable Size 289408256 bytes
Database Buffers 50331648 bytes
Redo Buffers 7168000 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 – see DBWR trace file
ORA-01110: data file 4: ‘/home/oracle/ANARDB/datafiles/users01.dbf’

SQL> exit

[oracle@AZDB ~]$ cd backup/
[oracle@AZDB backup]$ ls
arch_1_669569864_1.arc a1.ctl spfileANARDB.ora system01.dbf undotbs1.dbf
arch_1_669569864_2.arc initANARDB.ora sysaux01.dbf temp01.dbf users01.dbf

[oracle@AZDB backup]$ cp users01.dbf /home/oracle/ANARDB/datafiles/
[oracle@AZDB backup]$ cd
[oracle@AZDB ~]$ exit
exit

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: ‘/home/oracle/ANARDB/datafiles/users01.dbf’

SQL> recover datafile 4;
Media recovery complete.
SQL> alter database open;
Database altered.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: