RECOVERING THE DATABASE IF SYSTEM DATAFILE IS LOST OR CORRUPTED

[oracle@dayananda ~]$ export ORACLE_SID=qsoft

[oracle@dayananda ~]$ sqlplus sys as sysdba

SQL*Plus: Release 10.2.0.1.0 – Production on Fri Oct 31 16:26:10 2008

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/qsoft/archive

Oldest online log sequence 0

Next log sequence to archive 1

Current log sequence 1

SQL> select name from v$datafile;

NAME

——————————————————————————–

/home/oracle/qsoft/datafiles/system01.dbf

/home/oracle/qsoft/datafiles/undotbs1.dbf

/home/oracle/qsoft/datafiles/sysaux01.dbf

/home/oracle/qsoft/datafiles/users01.dbf

SQL> select name from v$controlfile;

NAME

——————————————————————————–

/home/oracle/qsoft/control/c1.ctl

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.

SQL> !

[oracle@dayananda ~]$ mkdir backup

[oracle@dayananda ~]$ cd qsoft/

[oracle@dayananda qsoft]$ cd control/

[oracle@dayananda control]$ ls

c1.ctl

[oracle@dayananda control]$ cp c1.ctl /home/oracle/backup/

[oracle@dayananda control]$ cd ..

[oracle@dayananda qsoft]$ cd datafiles/

[oracle@dayananda datafiles]$ ls

redo01.log redo02.log sysaux01.dbf system01.dbf temp01.dbf undotbs1.dbf users01.dbf

[oracle@dayananda datafiles]$ cp *.dbf /home/oracle/backup/

[oracle@dayananda datafiles]$ cd ..

[oracle@dayananda qsoft]$ cd archive/

[oracle@dayananda archive]$ ls

[oracle@dayananda archive]$ cd $ORACLE_HOME/dbs

[oracle@dayananda dbs]$ ls

initqsoft.ora spfileqsoft.ora

[oracle@dayananda dbs]$ cp initqsoft.ora /home/oracle/backup/

[oracle@dayananda dbs]$ cp spfileqsoft.ora /home/oracle/backup/

[oracle@dayananda dbs]$ cd /home/oracle/backup/

[oracle@dayananda backup]$ ls

c1.ctl initqsoft.ora sysaux01.dbf system01.dbf temp01.dbf undotbs1.dbf users01.dbf spfileqsoft.ora

[oracle@dayananda backup]$ exit

exit

SQL> alter tablespace system end backup;

Tablespace altered.

SQL> alter tablespace undotbs1 end backup;

Tablespace altered.

SQL> alter tablespace sysaux end backup;

Tablespace altered.

SQL> alter tablespace users end backup;

Tablespace altered.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> !

[oracle@dayananda ~]$ cd qsoft/

[oracle@dayananda qsoft]$ cd datafiles/

[oracle@dayananda datafiles]$ ls

redo01.log redo02.log sysaux01.dbf system01.dbf temp01.dbf undotbs1.dbf users01.dbf

[oracle@dayananda datafiles]$ rm -rf system01.dbf

[oracle@dayananda datafiles]$ cd

[oracle@dayananda ~]$ 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 1 – see DBWR trace file

ORA-01110: data file 1: ‘/home/oracle/qsoft/datafiles/system01.dbf’

SQL> !

[oracle@dayananda ~]$ cd backup/

[oracle@dayananda backup]$ ls

c1.ctl initqsoft.ora sysaux01.dbf system01.dbf temp01.dbf undotbs1.dbf users01.dbf

[oracle@dayananda backup]$ cp system01.dbf /home/oracle/qsoft/datafiles/

[oracle@dayananda backup]$ cd

[oracle@dayananda ~]$ exit

exit

SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

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-01113: file 1 needs media recovery

ORA-01110: data file 1: ‘/home/oracle/qsoft/datafiles/system01.dbf’

SQL> recover datafile 1;

ORA-00279: change 373322 generated at 10/31/2008 16:30:29 needed for thread 1

ORA-00289: suggestion : /home/oracle/qsoft/archive/arch_1_669569864_1.arc

ORA-00280: change 373322 for thread 1 is in sequence #1

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

Log applied.

Media recovery complete.

SQL> alter database open;

Database altered.