程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> 數據庫數據文件沒有備份的恢復ORA-01157【續+2】

數據庫數據文件沒有備份的恢復ORA-01157【續+2】

編輯:Oracle教程

實驗

數據庫數據文件有備份的恢復 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: 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

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved