Hello DBA-Mates,
[oracle@sku ORA-DATA3]$ clear
We are back after a long time with ORA-01110: data file 1:
'/u01/oracle/oradata/ORA-DATA3/system01.dbf'. Got so many messages and
comments as why stopped writing but we didn’t stopped , there was some bad time
which slowly passing away and we came back.
Thank you all for your supporting
and loving.
Recently, one of our friend faced this below issue and
they were not able to recover the database. So, friends before playing with
database take the backup always either online or offline.
Here, we have just shown as if we
missed system datafile or deleted, how can we recover it and start the
database.
1.
Take the offline backup.
SQL> select name,open_mode,log_mode,flashback_on from
v$database;
NAME
OPEN_MODE LOG_MODE FLASHBACK_ON
--------- ---------- ------------ ------------------
ORA-DATA3 READ
WRITE ARCHIVELOG NO
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 2
Next log sequence to archive 4
Current log sequence 4
******************************************
Make some transactions inorder to generate archive logs
(if account is locked then):
SQL> alter user scott identified by tiger account
unlock;
SQL> conn scott/tiger
Connected.
SQL> create table student(name varchar2(50))
tablespace users;
Table created.
SQL> begin
2 for i in 1..300000 loop
3 insert into student values('10g DBA Training
and R12 Apps DBA Training student');
4 end loop;
5 commit;
6 end;
7 /
*************************************************************
sql>conn /as sysdba;
SQL> select *
from v$recover_file;
no rows selected
SQL> column name format a40
SQL> select name,file#,status from v$datafile;
NAME FILE# STATUS
---------------------------------------- ----------
-------
/u01/oracle/oradata/ORA-DATA3/system01.dbf 1 SYSTEM
/u01/oracle/oradata/ORA-DATA3/undotbs01.dbf 2 ONLINE
/u01/oracle/oradata/ORA-DATA3/sysaux01.dbf 3 ONLINE
/u01/oracle/oradata/ORA-DATA3/users01.dbf 4 ONLINE
SIMULATE THE FAILURE OF SYSTEM DATAFILE LOSS
[oracle@sku ORA-DATA3]$ clear
[oracle@sku ORA-DATA3]$ pwd
/u01/oracle/oradata/ORA-DATA3
[oracle@sku ORA-DATA3]$ ls -l syst*
-rw-r----- 1 oracle oinstall 503324672 Sep 14 23:37
system01.dbf
[oracle@sku ORA-DATA3]$ ls
control01.ctl
control04.ctl redo01.log redo05a.log
sysaux01.dbf
control01.ctl_old
control04.ctl_old redo02a redo05.log system01.dbf
control02.ctl
example01.dbf
redo02a.log redo06a.log temp01.dbf
control02.ctl_old
indx01.dbf redo02.log redo06.log
undotbs01.dbf
control03.ctl
redo01a
redo03a.log redo07a.log users01.dbf
control03.ctl_old
redo01a.log redo03.log redo07.log
[oracle@sku ORA-DATA3]$ mv
system01.dbf system01.dbf_old
[oracle@sku ORA-DATA3]$
*********************************************
SQL> shutdown immediate
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/oracle/oradata/ORA-DATA3/system01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or
directory
Additional information: 3
SQL> shutdown abort
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 1090519040 bytes
Fixed Size 2020128 bytes
Variable Size
301993184 bytes
Database Buffers
771751936 bytes
Redo Buffers
14753792 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 -
see DBWR trace file
ORA-01110: data file 1:
'/u01/oracle/oradata/ORA-DATA3/system01.dbf'
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1090519040 bytes
Fixed Size 2020128 bytes
Variable Size
301993184 bytes
Database Buffers
771751936 bytes
Redo Buffers
14753792 bytes
Database mounted.
SQL>
SQL> select * from v$recover_file;
FILE#
ONLINE ONLINE_
---------- ------- -------
ERROR
CHANGE#
-----------------------------------------------------------------
----------
TIME
---------
1
ONLINE ONLINE
535300
15-SEP-11
Restore the system01.dbf from
Offline Backup
[oracle@sku ORA-DATA3]$ pwd
/u01/oracle/oradata/ORA-DATA3
$cp
/u01/oracle/offline_backup/system01.dbf .
##(This will also work if you restore system01.dbf from online backup)
[oracle@sku ORA-DATA3]$ ls -l *dbf
-rw-r----- 1 oracle oinstall 104865792 Sep 14 23:50
example01.dbf
-rw-r----- 1 oracle oinstall 4202496 Sep 14 23:50 indx01.dbf
-rw-r----- 1 oracle oinstall 251666432 Sep 14 23:50
sysaux01.dbf
-rw-r----- 1 oracle oinstall 503324672 Sep 14 23:56
system01.dbf
-rw-r----- 1 oracle oinstall 20979712 Sep 14 20:12 temp01.dbf
-rw-r----- 1 oracle oinstall 26222592 Sep 14 23:50 undotbs01.dbf
-rw-r----- 1 oracle oinstall 10493952 Sep 14 23:50 users01.dbf
******************
[oracle@sku 2011_09_14]$ pwd
/u01/oracle/flash_recovery_area/ORA-DATA3/archivelog/2011_09_15
[oracle@sku 2011_09_15]$ ls -alrt
total 33708
drwxr-x--- 3 oracle oinstall 4096 Sep 15 02:47 ..
-rw-r----- 1 oracle oinstall 34460160 Sep 15 02:47
o1_mf_1_1_7726hlvy_.arc
-rw-r----- 1 oracle oinstall 1024 Sep 15 02:48 o1_mf_1_2_7726j9bo_.arc
drwxr-x--- 2 oracle oinstall 4096 Sep 15 02:48 .
*************************************
SQL> recover database;
ORA-00279: change 680758 generated at 09/14/2011 20:31:38
needed for thread 1
ORA-00289: suggestion :
/u01/oracle/flash_recovery_area/ORA-DATA3/archivelog/2011_09_15/o1_mf_1_9_%u_.arc
ORA-00280: change 680758 for thread 1 is in sequence #9
Specify log: {<RET>=suggested | filename | AUTO |
CANCEL}
AUTO
ORA-00279: change 681408 generated at 09/14/2011 20:45:01
needed for thread 1
ORA-00289: suggestion :
/u01/oracle/flash_recovery_area/ORA-DATA3/archivelog/2011_09_15/o1_mf_1_10_%u_.arc
ORA-00280: change 681408 for thread 1 is in sequence #10
ORA-00278: log file
'/u01/oracle/flash_recovery_area/ORA-DATA3/archivelog/2011_09_14/o1_mf_1_9_771kpqbq_.
arc' no longer needed for this recovery
ORA-00279: change 681427 generated at 09/14/2011 20:53:03
needed for thread 1
ORA-00289: suggestion :
/u01/oracle/flash_recovery_area/ORA-DATA3/archivelog/2011_09_15/o1_mf_1_11_%u_.arc
ORA-00280: change 681427 for thread 1 is in sequence #11
ORA-00278: log file
'/u01/oracle/flash_recovery_area/ORA-DATA3/archivelog/2011_09_14/o1_mf_1_10_771kpqrc_
.arc' no longer needed for this recovery
ORA-00279: change 684925 generated at 09/14/2011 22:00:07
needed for thread 1
ORA-00289: suggestion :
/u01/oracle/flash_recovery_area/ORA-DATA3/archivelog/2011_09_15/o1_mf_1_12_%u_.arc
ORA-00280: change 684925 for thread 1 is in sequence #12
ORA-00278: log file
'/u01/oracle/flash_recovery_area/ORA-DATA3/archivelog/2011_09_14/o1_mf_1_11_771onh9m_
.arc' no longer needed for this recovery
Log applied.
Media recovery complete.
SQL> alter database open;
Database altered.
SQL> select * from v$recover_file;
no rows selected
SQL> select file#,name,status from v$datafile;
FILE#
NAME
STATUS
---------- ----------------------------------------
-------
1
/u01/oracle/oradata/ORA-DATA3/system01.dbf
SYSTEM
2
/u01/oracle/oradata/ORA-DATA3/undotbs01.dbf
ONLINE
3
/u01/oracle/oradata/ORA-DATA3/sysaux01.dbf
ONLINE
4
/u01/oracle/oradata/ORA-DATA3/users01.dbf
ONLINE
===============================================
IF
Database mounted.
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1:
'/opt/oracle/oradata/orcl/system01.dbf'
SQL> recover datafile 1;
Media recovery complete.
SQL> alter database open;
Database altered.
SQL>
