- 論壇徽章:
- 0
|
大家好：
我的數(shù)據(jù)庫是noarchivlog非歸檔模式。
我在關(guān)閉數(shù)據(jù)庫后，備份了users01.dbf 數(shù)據(jù)文件。之后重新打開數(shù)據(jù)庫，建立一個表
create table scott.ddd logging as select * from scott.dept;
然后再次關(guān)閉數(shù)據(jù)庫，刪除users01.dbf數(shù)據(jù)文件，再復制之前創(chuàng)建scott.ddd表前的數(shù)據(jù)文件users01.dbf，然后恢復該數(shù)據(jù)文件，打開數(shù)據(jù)庫后，當 select * from scott.ddd; 時提示數(shù)據(jù)塊損壞。我創(chuàng)建soott.ddd表時的重做日志還存在的，沒有切換過重做日志，執(zhí)行recover datafile 4;時也沒提示錯誤，為什么打開數(shù)據(jù)庫后， 提示users01.dbf數(shù)據(jù)文件的數(shù)據(jù)塊損壞呢？
但如果是先創(chuàng)建scott.ddd表，關(guān)閉數(shù)據(jù)庫再備份users01.dbf數(shù)據(jù)文件，然后打開數(shù)據(jù)庫，在scott.ddd表中刪除和插入數(shù)據(jù)，關(guān)閉數(shù)據(jù)庫，再復制創(chuàng)建表時的users01.dbf備份數(shù)據(jù)文件，這樣卻就可以恢復刪除插入的數(shù)據(jù)。
如下：
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> host cp /ora01/oracle/oradata/orcl/users01.dbf /ora01/oracle/oradata/orcl/users01.dbf.bak
SQL> startup;
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218292 bytes
Variable Size 83888396 bytes
Database Buffers 75497472 bytes
Redo Buffers 7168000 bytes
Database mounted.
Database opened.
SQL> create table scott.ddd logging as select * from scott.dept;
Table created.
SQL> select * from scott.ddd;
DEPTNO DNAME LOC
------------------ ------------------------------------------ ---------------------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> host rm /ora01/oracle/oradata/orcl/users01.dbf
SQL> startup;
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218292 bytes
Variable Size 83888396 bytes
Database Buffers 75497472 bytes
Redo Buffers 7168000 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/ora01/oracle/oradata/orcl/users01.dbf'
SQL> host cp /ora01/oracle/oradata/orcl/users01.dbf.bak /ora01/oracle/oradata/orcl/users01.dbf
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: '/ora01/oracle/oradata/orcl/users01.dbf'
SQL> recover datafile 4;
Media recovery complete.
SQL> alter database open;
Database altered.
SQL> select * from scott.ddd;
select * from scott.ddd
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 38
ORA-01110: data file 4: '/ora01/oracle/oradata/orcl/users01.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
-------------------------------------------------------------------
小弟是菜鳥，想請問各位以下兩個問題：
1、為什么是這樣，如果是創(chuàng)建表前，備份數(shù)據(jù)文件，就不能恢復該表，提示數(shù)據(jù)塊損壞。
如果是創(chuàng)建表后，再備份數(shù)據(jù)文件，就能恢復該表。請問這是什么原因？是不是重做日志是不記錄創(chuàng)建表時的信息的？
2、請問如何用創(chuàng)建表前備份的數(shù)據(jù)文件，進行恢復表的數(shù)據(jù)呢？（重做日志未切換）
----
以上問題，還望各位大俠能幫解答一下，感謝。
|
|
免費注冊
發(fā)表于 2013-01-29 11:30
