实验
数据库数据文件有备份的恢复 ORA-01157 【续+1】
是恢复有备份的实验，那没有备份如何恢复呢？
这个实验前提感觉必须是在归档模式下！！！
但是测试后发现非归档模式也可以恢复。 为什么呢？？因为还有redo日志 测试过程在下面附啊。
[oracle@tyger ORCL]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Mar 3 16:47:55 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SYS@ORCL>archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 17
Next log sequence to archive 19
Current log sequence 19
SYS@ORCL>create tablespace tyger1 datafile '/u01/app/oracle/oradata/ORCL/tyger1_01.dbf' size 10m;
Tablespace created.
SYS@ORCL>create user u1 identified by u1 account unlock default tablespace tyger1;
User created.
SYS@ORCL>grant connect,resource,select any table to u1;
Grant succeeded.
SYS@ORCL>conn u1/u1
Connected.
U1@ORCL>create table u1_t as select * from scott.emp;
Table created.
U1@ORCL>insert into u1_t select * from u1_t;
14 rows created.
U1@ORCL>/
28 rows created.
..............
U1@ORCL>/
57344 rows created.
U1@ORCL>/
/
insert into u1_t select * from u1_t
*
ERROR at line 1:
ORA-01653: unable to extend table U1.U1_T by 128 in tablespace TYGER1
U1@ORCL>select count(*) from u1_t;
COUNT(*)
----------
114688
U1@ORCL>commit;
Commit complete.
U1@ORCL>!
[oracle@tyger ORCL]$ ls
control01.ctl redo02.log standbylog5b.log sysaux01.dbf users01.dbf
control02.ctl redo03.log standbylog6a.log system01.dbf
control03.ctl standbylog4a.log standbylog6b.log temp01.dbf
example01.dbf standbylog4b.log standbylog7a.log tyger1_01.dbf
redo01.log standbylog5a.log standbylog7b.log undotbs01.dbf
[oracle@tyger ORCL]$ rm -rf tyger1_01.dbf
[oracle@tyger ORCL]$ exit
exit
U1@ORCL>
U1@ORCL>select count(*) from u1_t; ---------------没有进行重启数据库操作，所以数据还在
COUNT(*)
----------
114688
U1@ORCL>conn / as sysdba
Connected.
SYS@ORCL>select count(*) from u1.u1_t;
COUNT(*)
----------
114688
SYS@ORCL>shutdown abort
ORACLE instance shut down.
SYS@ORCL>startup
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 163579472 bytes
Database Buffers 117440512 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/u01/app/oracle/oradata/ORCL/tyger1_01.dbf'
SYS@ORCL>alter database datafile 6 offline;
Database altered.
SYS@ORCL>alter database open;
Database altered.
SYS@ORCL>conn u1/u1
Connected.
U1@ORCL>select count(*) from u1_t;
select count(*) from u1_t
*
ERROR at line 1:
ORA-00376: file 6 cannot be read at this time
ORA-01110: data file 6: '/u01/app/oracle/oradata/ORCL/tyger1_01.dbf'
U1@ORCL>conn / as sysdba
Connected.
SYS@ORCL>alter database create datafile 6 as '/u01/app/oracle/oradata/ORCL/tyger1_01.dbf';
Database altered.
SYS@ORCL>recover datafile 6; ------使用recover命令对数据文件进行恢复，这时一定要注意归档文件一定要在Oracle能 够访问到的磁盘上，不能放在磁带上，最好在归档日志文件定义的目录内
Media recovery complete.
SYS@ORCL>conn u1/u1
Connected.
U1@ORCL>select count(*) from u1_t;
select count(*) from u1_t
*
ERROR at line 1:
ORA-00376: file 6 cannot be read at this time
ORA-01110: data file 6: '/u01/app/oracle/oradata/ORCL/tyger1_01.dbf'
U1@ORCL>conn / as sysdba
Connected.
SYS@ORCL>select count(*) from u1_t;
select count(*) from u1_t
*
ERROR at line 1:
ORA-00942: tabl