[20151125]資料檔案的unrecover.txt

lfree發表於2015-12-15

[20151125]資料檔案的unrecover.txt

--前一陣子我給別人演示truncate的不完全恢復,結果非常難堪的遇到無法恢復的情況。
--問題是我建立的資料庫按照這個連結建立的。
http://blog.itpub.net/267265/viewspace-1845062/

--而這樣建立的資料庫表空間example的屬性NOLOGGING。
CREATE TABLESPACE EXAMPLE DATAFILE
  '/mnt/ramdisk/book/example01.dbf' SIZE 320640K AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
NOLOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

--導致我建立的表在其上的資料無法恢復。

SYS@book> @ &r/ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SYS@book> select force_logging from v$database;
FOR
---
NO

--當在上面的情況下我們建立的表空間nologging,或者使用append hint插入資料,或者一些建表語句使用nologging屬性,
--這些操作都有可能導致在恢復遇到不可恢復的情況.前幾天我在給別人講解truncate後不完全恢復時,就遇到這種情況.
--但是我如果建立表後面正常插入修改,會出現什麼情況呢,透過例子來講解問題.

1.環境:
SCOTT@book> @ &r/ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

CREATE TABLESPACE sugar DATAFILE
  '/mnt/ramdisk/book/sugara01.dbf' SIZE 100M AUTOEXTEND ON NEXT 64M MAXSIZE UNLIMITED
NOLOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
--建立屬性NOLOGGING的表空間資料檔案.

RMAN> report schema ;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name BOOK

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    760      SYSTEM               ***     /mnt/ramdisk/book/system01.dbf
2    590      SYSAUX               ***     /mnt/ramdisk/book/sysaux01.dbf
3    85       UNDOTBS1             ***     /mnt/ramdisk/book/undotbs01.dbf
4    8        USERS                ***     /mnt/ramdisk/book/users01.dbf
5    346      EXAMPLE              ***     /mnt/ramdisk/book/example01.dbf
6    100      SUGAR                ***     /mnt/ramdisk/book/sugara01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    29       TEMP                 32767       /mnt/ramdisk/book/temp01.dbf

SCOTT@book> SELECT file#, CHECKPOINT_CHANGE#,CHECKPOINT_TIME,UNRECOVERABLE_CHANGE#,UNRECOVERABLE_TIME,LAST_CHANGE#,LAST_TIME, OFFLINE_CHANGE#, ONLINE_CHANGE#,status,name FROM v$datafile;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME     UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME  LAST_CHANGE# LAST_TIME OFFLINE_CHANGE# ONLINE_CHANGE# STATUS  NAME
------ ------------------ ------------------- --------------------- ------------------- ------------ --------- --------------- -------------- ------- ---------------------------------
     1            1313415 2015-12-15 10:37:59                     0                                                     925701         925702 SYSTEM  /mnt/ramdisk/book/system01.dbf
     2            1313415 2015-12-15 10:37:59                     0                                                     925701         925702 ONLINE  /mnt/ramdisk/book/sysaux01.dbf
     3            1313415 2015-12-15 10:37:59                     0                                                     925701         925702 ONLINE  /mnt/ramdisk/book/undotbs01.dbf
     4            1313415 2015-12-15 10:37:59                     0                                                     925701         925702 ONLINE  /mnt/ramdisk/book/users01.dbf
     5            1313415 2015-12-15 10:37:59                     0                                                     952916         952921 ONLINE  /mnt/ramdisk/book/example01.dbf
     6            1314509 2015-12-15 11:00:33                     0                                                          0              0 ONLINE  /mnt/ramdisk/book/sugara01.dbf
6 rows selected.

--做一個備份全備份加archive log日誌.步驟忽略。
RMAN> backup database format '/home/oracle/backup/full_%U';
RMAN> backup archivelog all format '/home/oracle/backup/archivelog_%U';

2.開始建立測試資料,為恢復做準備:
SCOTT@book> create table t1 tablespace sugar as select rownum id ,'aaaa' name from dual connect by level<=1e5;
Table created.

SCOTT@book> SELECT file#, CHECKPOINT_CHANGE#,CHECKPOINT_TIME,UNRECOVERABLE_CHANGE#,UNRECOVERABLE_TIME,LAST_CHANGE#,LAST_TIME, OFFLINE_CHANGE#, ONLINE_CHANGE#,status,name FROM v$datafile;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME     UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME  LAST_CHANGE# LAST_TIME OFFLINE_CHANGE# ONLINE_CHANGE# STATUS  NAME
------ ------------------ ------------------- --------------------- ------------------- ------------ --------- --------------- -------------- ------- ---------------------------------
     1            1316061 2015-12-15 11:11:32                     0                                                     925701         925702 SYSTEM  /mnt/ramdisk/book/system01.dbf
     2            1316061 2015-12-15 11:11:32                     0                                                     925701         925702 ONLINE  /mnt/ramdisk/book/sysaux01.dbf
     3            1316061 2015-12-15 11:11:32                     0                                                     925701         925702 ONLINE  /mnt/ramdisk/book/undotbs01.dbf
     4            1316061 2015-12-15 11:11:32                     0                                                     925701         925702 ONLINE  /mnt/ramdisk/book/users01.dbf
     5            1316061 2015-12-15 11:11:32                     0                                                     952916         952921 ONLINE  /mnt/ramdisk/book/example01.dbf
     6            1316061 2015-12-15 11:11:32               1316144 2015-12-15 11:12:34                                      0              0 ONLINE  /mnt/ramdisk/book/sugara01.dbf

--注意 FILE# = 6, UNRECOVERABLE_CHANGE#,UNRECOVERABLE_TIME有資訊。

SCOTT@book> select sysdate,current_scn from v$database;
SYSDATE             CURRENT_SCN
------------------- -----------
2015-12-15 11:13:53     1316185

SCOTT@book> truncate table t1;
Table truncated.

SCOTT@book> select sysdate,current_scn from v$database;
SYSDATE             CURRENT_SCN
------------------- -----------
2015-12-15 11:15:32     1316270

SCOTT@book> insert /*+ append */ into t1 select rownum id ,'bbbb' name from dual connect by level<=1e5;
100000 rows created.

SCOTT@book> commit ;
Commit complete.

SCOTT@book> select sysdate,current_scn from v$database;
SYSDATE             CURRENT_SCN
------------------- -----------
2015-12-15 11:15:49     1316316

SCOTT@book> SELECT file#, CHECKPOINT_CHANGE#,CHECKPOINT_TIME,UNRECOVERABLE_CHANGE#,UNRECOVERABLE_TIME,LAST_CHANGE#,LAST_TIME, OFFLINE_CHANGE#, ONLINE_CHANGE#,status,name FROM v$datafile;
     FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME     UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME  LAST_CHANGE# LAST_TIME           OFFLINE_CHANGE# ONLINE_CHANGE# STATUS  NAME
---------- ------------------ ------------------- --------------------- ------------------- ------------ ------------------- --------------- -------------- ------- --------------------------------------------------
         1            1316061 2015-12-15 11:11:32                     0                                                               925701         925702 SYSTEM  /mnt/ramdisk/book/system01.dbf
         2            1316061 2015-12-15 11:11:32                     0                                                               925701         925702 ONLINE  /mnt/ramdisk/book/sysaux01.dbf
         3            1316061 2015-12-15 11:11:32                     0                                                               925701         925702 ONLINE  /mnt/ramdisk/book/undotbs01.dbf
         4            1316061 2015-12-15 11:11:32                     0                                                               925701         925702 ONLINE  /mnt/ramdisk/book/users01.dbf
         5            1316061 2015-12-15 11:11:32                     0                                                               952916         952921 ONLINE  /mnt/ramdisk/book/example01.dbf
         6            1316061 2015-12-15 11:11:32               1316313 2015-12-15 11:15:45                                                0              0 ONLINE  /mnt/ramdisk/book/sugara01.dbf

6 rows selected.
--注意 FILE# = 6 那行,有存在變化, UNRECOVERABLE_CHANGE#,UNRECOVERABLE_TIME有資訊。也就是總是記錄最新不會恢復的scn。

SCOTT@book> select sysdate,current_scn from v$database;
SYSDATE             CURRENT_SCN
------------------- -----------
2015-12-15 11:17:22     1316353

SCOTT@book> insert into t1 select rownum+1e5 id ,'cccc' name from dual connect by level<=1e5;
100000 rows created.

SCOTT@book> commit ;
Commit complete.

SCOTT@book> select sysdate,current_scn from v$database;
SYSDATE             CURRENT_SCN
------------------- -----------
2015-12-15 11:18:45     1316386

SCOTT@book> update t1 set name='BBBB' where id<=1e5;
100000 rows updated.

SCOTT@book> commit ;
Commit complete.

SCOTT@book> select sysdate,current_scn from v$database;
SYSDATE             CURRENT_SCN
------------------- -----------
2015-12-15 11:19:33     1316673

SCOTT@book> insert into t1 select rownum+2e5 id ,'dddd' name from dual connect by level<=1e5;
100000 rows created.

SCOTT@book> commit;
Commit complete.

SCOTT@book> select sysdate,current_scn from v$database;
SYSDATE             CURRENT_SCN
------------------- -----------
2015-12-15 11:20:12     1316690

SYS@book> alter system archive log current ;
System altered.

SYS@book> alter system checkpoint ;
System altered.

--在做一次archive log的備份.顯示資訊忽略。
RMAN> backup archivelog all format '/home/oracle/backup/archivelog_%U';

$ ll -l /home/oracle/backup/full* /home/oracle/backup/archivelog*
-rw-r----- 1 oracle oinstall   20407296 2015-12-15 11:11:32 /home/oracle/backup/archivelog_03qosev4_1_1
-rw-r----- 1 oracle oinstall   58695680 2015-12-15 11:21:43 /home/oracle/backup/archivelog_04qosfi6_1_1
-rw-r----- 1 oracle oinstall 1230282752 2015-12-15 11:10:45 /home/oracle/backup/full_01qoseti_1_1
-rw-r----- 1 oracle oinstall    9830400 2015-12-15 11:10:50 /home/oracle/backup/full_02qosetp_1_1

3.開始恢復測試:
--我僅僅一臺機器,改名並且建立新目錄。
$ cd /mnt/ramdisk/
$ mv book book.org
$ mkdir -p book

SYS@book> startup nomount
ORACLE instance started.
Total System Global Area  626327552 bytes
Fixed Size                  2255832 bytes
Variable Size             243270696 bytes
Database Buffers          373293056 bytes
Redo Buffers                7507968 bytes

RMAN> restore controlfile from '/home/oracle/backup/full_02qosetp_1_1';
Starting restore at 2015-12-15 11:27:10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/mnt/ramdisk/book/control01.ctl
output file name=/mnt/ramdisk/book/control02.ctl
Finished restore at 2015-12-15 11:27:12

SYS@book> alter database mount ;
Database altered.

--建立恢復指令碼:

$ cat a.rman
run
{
        set until scn &1;
        restore database;
        recover database;
}

--先恢復到truncate之前,scn=1316185.
--執行以上指令碼,帶入引數1316185.

SYS@book> alter database open read only ;
Database altered.

SYS@book> select * from scott.t1;
select * from scott.t1
                    *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 131)
ORA-01110: data file 6: '/mnt/ramdisk/book/sugara01.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option

SYS@book> @ &r/desc scott.t1
Name  Null?    Type
----- -------- ---------
ID             NUMBER
NAME           CHAR(4)

--可以發現表的定義存在,但是前面插入的資訊沒有日誌,無法恢復,實際上ctas在nologging模式下是append模式插入的。
--註解restore,繼續使用這個指令碼恢復。當然資料庫要啟動到mount模式,恢復後在使用open read only開啟,以下不再說明。

$ cat a.rman
run
{
        set until scn &1;
##      restore database;
        recover database;
}

4.恢復到trucate之後,scn=1316270。
RMAN> @ a.rman 1316270
... 資訊忽略。

SYS@book> select * from scott.t1;
no rows selected

--ok現在正常。

5.恢復到append name='bbbb', scn=1316316:

RMAN> @ a.rman 1316316

SYS@book> select * from scott.t1;
select * from scott.t1
                    *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 131)
ORA-01110: data file 6: '/mnt/ramdisk/book/sugara01.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option

--說明這個append插入也沒有日誌,導致無法恢復。

6. 以前是正常插入的資訊,name='cccc',恢復到scn=1316386:
RMAN> @ a.rman 1316386
...

SYS@book> select count(*) from scott.t1;
select count(*) from scott.t1
                           *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 131)
ORA-01110: data file 6: '/mnt/ramdisk/book/sugara01.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option

--但是這樣我後面的插入應該有記錄, 執行如下:
$ strings sugara01.dbf | grep bbbb |wc
      0       0       0
$ strings sugara01.dbf | grep cccc |wc
100000  100000  599793

--從這些提示可以看出name='cccc'的資訊是插入的。
--使用10231事件跳過壞塊。

SYS@book> ALTER SYSTEM SET EVENTS '10231 trace name context forever,level 10' ;
System altered.

SYS@book> select count(*) from scott.t1;
  COUNT(*)
----------
    100000

--正常的插入可以顯示,只要跳過壞塊。

7.恢復到update t1 set name='BBBB' where id<=1e5;之後。scn =1316673.
RMAN> @ a.rman 1316673
...

--實際上我修改這些記錄在redo裡面的,但是實際的記錄並不存在。
SYS@book> select count(*) from scott.t1;
select count(*) from scott.t1
                           *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 131)
ORA-01110: data file 6: '/mnt/ramdisk/book/sugara01.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option

SYS@book> select count(*) from scott.t1;
  COUNT(*)
----------
    100000
SYS@book> select rowid,t1.* from scott.t1 where name<>'cccc';
no rows selected
   
SYS@book> select rowid,t1.* from scott.t1 where rownum<=1;
ROWID                      ID NAME
------------------ ---------- --------------------
AAAVqqAAGAAAAFbAAA     100969 cccc

--說明這段產生的日誌無用,被跳過了。

8.恢復到insert into t1 select rownum+2e5 id ,'dddd' name from dual connect by level<=1e5;之後。scn=1316690.
RMAN> @ a.rman 1316690

SYS@book> select count(*) from scott.t1;
select count(*) from scott.t1
                           *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 131)
ORA-01110: data file 6: '/mnt/ramdisk/book/sugara01.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option

SYS@book> ALTER SYSTEM SET EVENTS '10231 trace name context forever,level 10' ;
System altered.

SYS@book> select count(*) from scott.t1;
  COUNT(*)
----------
    200000

$ strings sugara01.dbf | grep dddd |wc
100000  100000  599793

--說明可以正常恢復。

9.做了這麼多測試主要想說明幾點:

1.注意一些資料庫沒有開啟force_logging=no,有一些操作會存在不可恢復的情況,這個在一些生產系統要特別注意。特別沒有dg的環境。
  如果在生產系統做了這些操作,要注意檢查檢視v$datafile的UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME欄位,及時備份相關資料檔案。
2.一些正常的操作還是可以恢復的。即使中間操作了像上面的修改update t1 set name='BBBB' where id<=1e5;,恢復依舊繼續不會中斷。
3.遇到這種情況,可以使用ALTER SYSTEM SET EVENTS '10231 trace name context forever,level 10' ;跳過壞塊。
4.補充:使用bbed觀察:

BBED> set dba 6,131
        DBA             0x01800083 (25165955 6,131)

BBED> map /v
File: /mnt/ramdisk/book/sugara01.dbf (6)
Block: 131                                   Dba:0x01800083
------------------------------------------------------------
BBED-00400: invalid blocktype (00)

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-1872654/,如需轉載,請註明出處,否則將追究法律責任。

相關文章