实验
数据库数据文件有备份的恢复 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