RECOVERING THE DATABASE IF SYSTEM DATAFILE IS LOST OR CORRUPTED


[oracle@TEST ~]$ export ORACLE_SID=AZDB
[oracle@TEST ~]$ sqlplus sys as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Dec 5 12:20:27 2013
Copyright (c) 1982, 2011, 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/AZDB/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/AZDB/datafiles/system01.dbf
/home/oracle/AZDB/datafiles/undotbs1.dbf
/home/oracle/AZDB/datafiles/sysaux01.dbf
/home/oracle/AZDB/datafiles/users01.dbf

SQL> select name from v$controlfile;
NAME
——————————————————————————–
/home/oracle/AZDB/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> exit

[oracle@TEST ~]$ mkdir backup
[oracle@TEST ~]$ cd AZDB/
[oracle@TEST AZDB]$ cd control/
[oracle@TEST control]$ ls
c1.ctl

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

[oracle@TEST datafiles]$ cp *.dbf /home/oracle/backup/
[oracle@TEST datafiles]$ cd ..
[oracle@TEST AZDB]$ cd archive/
[oracle@TEST archive]$ ls
[oracle@TEST archive]$ cd $ORACLE_HOME/dbs
[oracle@TEST dbs]$ ls
initAZDB.ora spfileAZDB.ora

[oracle@TEST dbs]$ cp initAZDB.ora /home/oracle/backup/
[oracle@TEST dbs]$ cp spfileAZDB.ora /home/oracle/backup/
[oracle@TEST dbs]$ cd /home/oracle/backup/
[oracle@TEST backup]$ ls
c1.ctl initAZDB.ora sysaux01.dbf system01.dbf temp01.dbf undotbs1.dbf users01.dbf spfileAZDB.ora
[oracle@TEST backup]$ 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> exit

[oracle@TEST ~]$ cd AZDB/
[oracle@TEST AZDB]$ cd datafiles/
[oracle@TEST datafiles]$ ls
redo01.log redo02.log sysaux01.dbf system01.dbf temp01.dbf undotbs1.dbf users01.dbf
[oracle@TEST datafiles]$ rm -rf system01.dbf
[oracle@TEST datafiles]$ cd
[oracle@TEST ~]$ 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/AZDB/datafiles/system01.dbf’

SQL> exit

[oracle@TEST ~]$ cd backup/
[oracle@TEST backup]$ ls
c1.ctl initAZDB.ora sysaux01.dbf system01.dbf temp01.dbf undotbs1.dbf users01.dbf
[oracle@TEST backup]$ cp system01.dbf /home/oracle/AZDB/datafiles/
[oracle@TEST backup]$ cd
[oracle@TEST ~]$ 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/AZDB/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/AZDB/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
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: