[20151125]数据文件的unrecover.txt

[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)

时间： 2024-10-11 13:08:46

[20151125]数据文件的unrecover.txt的相关文章

[20151218]数据文件的unrecover与增量备份

[20151218]数据文件的unrecover与增量备份.txt --前一阵子我给别人演示truncate的不完全恢复,结果非常难堪的遇到无法恢复的情况. --问题是我建立的数据库按照这个链接建立的. http://blog.itpub.net/267265/viewspace-1845062/ --而这样建立的数据库表空间example的属性NOLOGGING. CREATE TABLESPACE EXAMPLE DATAFILE   '/mnt/ramdisk/book/example01

[20161101]rman备份与数据文件变化7.txt

[20161101]rman备份与数据文件变化7.txt --//想象一下,如果备份文件时间很长,而这个时候数据文件大小发生了变化,oracle的备份如何解决这个问题呢? --//去年已经测试了建立备份集的情况,一直想做一次image copy的测试,一直脱,主要原因自己不想做这个测试.... --//而且当时的测试很乱,自己主要一边做一边想.... --//链接: http://blog.itpub.net/267265/viewspace-2127386/ http://blog.itpub

[20171206]位图区一定在数据文件开头吗.txt

[20171206]位图区一定在数据文件开头吗.txt --//如果问你oracle数据文件的位图区位于数据文件开头部分吗?我想大家的回答一定,实际上在10g下未必,因为10g建立的数据文件. --//在数据区前面仅仅8块,第1块作为文件头,第2块作为位图区头,第3-8块(共6块)作为位图区,一般1个位图区块能容纳 --//(494+2)*32*4= 63488区,1个区=64K(对于SEGMENT SPACE MANAGEMENT AUTO). --//这样1个位图块可以容纳63488*64*

[20171114]恢复数据文件块头2.txt

[20171114]恢复数据文件块头2.txt --//曾经写过一篇[20161111]数据库文件头的修复.txt,但是利用大小相似的数据文件头覆盖来恢复,那是属于特种恢复. --//参考链接:http://blog.itpub.net/267265/viewspace-2128309/ --//不在正常操作范围,完全是不得已而为之.基本写那篇在一年之前,这次做一个带引号"常规恢复"看看. --//后记:纯属无聊,千万不要把这当作常规的恢复. 1.环境: SCOTT@book>

[20171123]rman备份与数据文件变化6.txt

[20171123]rman备份与数据文件变化6.txt --//想象一下,如果备份文件时间很长,而这个时候数据文件大小发生了变化,oracle的备份如何解决这个问题呢? --//去年已经测试了建立备份集的情况,一直想做一次image copy的测试,一直脱,主要原因自己不想做这个测试.... --//而且当时的测试很乱,自己主要一边做一边想.... --//链接: http://blog.itpub.net/267265/viewspace-2127386/ http://blog.itpub

[20171122]恢复数据文件块头5.txt

[20171122]恢复数据文件块头5.txt --//前几天做了恢复数据文件块头,通过备份文件直接取出文件块头,覆盖原来的数据块,然后修复. --//今天测试使用image copy来恢复.也是直接使用直接取出文件块头覆盖原来的数据块. 1.环境: SCOTT@book> @ &r/ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -

[20161101]rman备份与数据文件变化4.txt

[20161101]rman备份与数据文件变化4.txt --想象一下,如果备份文件时间很长,而这个时候数据文件大小发生了变化,oracle的备份如何解决这个问题呢? --前面我已经做了增大数据文件,参考链接:http://blog.itpub.net/267265/viewspace-2127386/ --这次测试减少数据文件大小看看. --早上的测试太乱了,重复做1次看看. 1.环境: SCOTT@book> @ &r/ver1 PORT_STRING                  

[20161108]关于数据文件的问题.txt

[20161108]关于数据文件的问题.txt --昨天看了一些数据文件位图问题,今天探究数据文件的其他问题. 1.环境: SCOTT@book> @ &r/ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -----------------------------------------------------------------

[20161031]rman备份与数据文件变化3.txt

[20161031]rman备份与数据文件变化3.txt --想象一下,如果备份文件时间很长,而这个时候数据文件大小发生了变化,oracle的备份如何解决这个问题呢? --前面我已经做了增大数据文件,参考链接:http://blog.itpub.net/267265/viewspace-2127386/ --这次测试减少数据文件大小看看. 1.环境: SCOTT@book> @ &r/ver1 PORT_STRING                    VERSION        BAN