数据库数据文件没有备份的恢复 ORA-01157 【续+2】

浏览数：38 / 时间：2015年06月12日

实验

数据库数据文件有备份的恢复 ORA-01157 【续+1】

http://blog.csdn.net/wanghui5767260/article/details/20297661

是恢复有备份的实验，那没有备份如何恢复呢？

这个实验前提感觉必须是在归档模式下！！！

但是测试后发现非归档模式也可以恢复。为什么呢？？因为还有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: table or view does not exist

SYS@ORCL>select count(*) from u1.u1_t;
select count(*) from u1.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‘

SYS@ORCL>alter database datafile 6 online;

Database altered.

SYS@ORCL>select count(*) from u1.u1_t;

COUNT(*)
----------
114688

附：

在非归档模式下连续多次切换日志后，在做恢复的时候就报错

SYS@ORCL>archive log file;
SP2-0718: illegal ARCHIVE LOG option
SYS@ORCL>archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 20
Current log sequence 22
SYS@ORCL>select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME FILE_NAME
-------------------- --------------------------------------------------
USERS /u01/app/oracle/oradata/ORCL/users01.dbf
SYSAUX /u01/app/oracle/oradata/ORCL/sysaux01.dbf
UNDOTBS1 /u01/app/oracle/oradata/ORCL/undotbs01.dbf
SYSTEM /u01/app/oracle/oradata/ORCL/system01.dbf
EXAMPLE /u01/app/oracle/oradata/ORCL/example01.dbf
TYGER1 /u01/app/oracle/oradata/ORCL/tyger1_01.dbf

6 rows selected.

SYS@ORCL>create tablespace tyger2 datafile ‘/u01/app/oracle/oradata/ORCL/tyger2_01.dbf‘ size 1m;

Tablespace created.

SYS@ORCL>drop user u2;

User dropped.

SYS@ORCL>create user u2 identified by u2 account unlock default tablespace tyger2;

User created.

SYS@ORCL>grant connect,resource,select any table to u2;

Grant succeeded.

SYS@ORCL>conn u2/u2
Connected.
U2@ORCL>create table u2_t as select * from scott.emp;

Table created.

U2@ORCL>commit;

Commit complete.

U2@ORCL>select count(*) from u2_t;

COUNT(*)
----------
14

U2@ORCL>conn / as sysdba
Connected.
SYS@ORCL>alter system switch logfile;

System altered.

SYS@ORCL>/

System altered.

......... 多切换几次
SYS@ORCL>/

System altered.

SYS@ORCL>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@ORCL>!
[oracle@tyger ORCL]$ ls
control01.ctl redo03.log standbylog6b.log tyger1_01.dbf
control02.ctl standbylog4a.log standbylog7a.log tyger2_01.dbf
control03.ctl standbylog4b.log standbylog7b.log undotbs01.dbf
example01.dbf standbylog5a.log sysaux01.dbf users01.dbf
redo01.log standbylog5b.log system01.dbf
redo02.log standbylog6a.log temp01.dbf
[oracle@tyger ORCL]$ rm -rf tyger2_01.dbf
[oracle@tyger ORCL]$ exit
exit

SYS@ORCL>startup
ORACLE instance started.

Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 171968080 bytes
Database Buffers 109051904 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: ‘/u01/app/oracle/oradata/ORCL/tyger2_01.dbf‘

SYS@ORCL>alter database datafile 7 offline drop;

Database altered.

SYS@ORCL>alter database open;

Database altered.

SYS@ORCL>select count(*) from u2.u2_t;
select count(*) from u2.u2_t
*
ERROR at line 1:
ORA-00376: file 7 cannot be read at this time
ORA-01110: data file 7: ‘/u01/app/oracle/oradata/ORCL/tyger2_01.dbf‘

SYS@ORCL>alter database create datafile 7 as ‘/u01/app/oracle/oradata/ORCL/tyger2_01.dbf‘;

Database altered.

SYS@ORCL>recover datafile 7;
ORA-00279: change 820758 generated at 03/03/2014 17:33:12 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_03_03/o1_mf_1_22_%u_.ar
c
ORA-00280: change 820758 for thread 1 is in sequence #22

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

ORA-00308: cannot open archived log
‘/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2014_03_03/o1_mf_1_22_%u_.a
rc‘
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3