ITPub博客

首页 > 数据库 > Oracle > [20151218]数据文件的unrecover与增量备份

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

Oracle 作者:lfree 时间:2015-12-18 10:10:16 0 删除 编辑

[20151218]数据文件的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/sugar01.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 ;

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    630      SYSAUX               ***     /mnt/ramdisk/book/sysaux01.dbf
3    85       UNDOTBS1             ***     /mnt/ramdisk/book/undotbs01.dbf
4    11       USERS                ***     /mnt/ramdisk/book/users01.dbf
5    346      EXAMPLE              ***     /mnt/ramdisk/book/example01.dbf
6    100      SUGAR                ***     /mnt/ramdisk/book/sugar01.dbf

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

SCOTT@book> column name format a50
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            1454236 2015-12-18 05:00:26                     0                                                     925701         925702 SYSTEM  /mnt/ramdisk/book/system01.dbf
     2            1454236 2015-12-18 05:00:26                     0                                                     925701         925702 ONLINE  /mnt/ramdisk/book/sysaux01.dbf
     3            1454236 2015-12-18 05:00:26                     0                                                     925701         925702 ONLINE  /mnt/ramdisk/book/undotbs01.dbf
     4            1454236 2015-12-18 05:00:26                     0                                                     925701         925702 ONLINE  /mnt/ramdisk/book/users01.dbf
     5            1454236 2015-12-18 05:00:26                     0                                                     952916         952921 ONLINE  /mnt/ramdisk/book/example01.dbf
     6            1462137 2015-12-18 09:20:31               1316313 2015-12-15 11:15:45                                      0              0 ONLINE  /mnt/ramdisk/book/sugar01.dbf
6 rows selected.

--做一个备份全备份加archive log日志.步骤忽略。
RMAN> backup incremental level 0 database  format '/home/oracle/backup/inc0_%U' TAG='INC0';
RMAN> backup archivelog all format '/home/oracle/backup/archivelog_%U';
--注意我这次做的增量全备份。

2.开始建立测试数据,为恢复做准备:

SCOTT@book> create table t2 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            1462782 2015-12-18 09:29:42                     0                                                     925701         925702 SYSTEM  /mnt/ramdisk/book/system01.dbf
     2            1462782 2015-12-18 09:29:42                     0                                                     925701         925702 ONLINE  /mnt/ramdisk/book/sysaux01.dbf
     3            1462782 2015-12-18 09:29:42                     0                                                     925701         925702 ONLINE  /mnt/ramdisk/book/undotbs01.dbf
     4            1462782 2015-12-18 09:29:42                     0                                                     925701         925702 ONLINE  /mnt/ramdisk/book/users01.dbf
     5            1462782 2015-12-18 09:29:42                     0                                                     952916         952921 ONLINE  /mnt/ramdisk/book/example01.dbf
     6            1462782 2015-12-18 09:29:42               1462849 2015-12-18 09:30:21                                      0              0 ONLINE  /mnt/ramdisk/book/sugar01.dbf
6 rows selected.
--注意 FILE# = 6, UNRECOVERABLE_CHANGE#,UNRECOVERABLE_TIME已经发生变化,记录最新的 UNRECOVERABLE_CHANGE#。

SCOTT@book> select sysdate,current_scn from v$database;
SYSDATE             CURRENT_SCN
------------------- -----------
2015-12-18 09:31:12     1462876

3.做一个增量备份level=1.

RMAN> backup incremental level 1 database  format '/home/oracle/backup/inc1_%U' TAG='INC1';

SCOTT@book> truncate table t2;
Table truncated.

SCOTT@book> select sysdate,current_scn from v$database;
SYSDATE             CURRENT_SCN
------------------- -----------
2015-12-18 09:32:26     1462970

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

--关闭数据库。

4.开始恢复测试:
--我仅仅一台机器,改名并且建立新目录。
$ cd /mnt/ramdisk/
$ mv book book.org
$ mkdir -p book

$ ll -l /home/oracle/backup/*                                                          --*/
-rw-r----- 1 oracle oinstall   13110272 2015-12-18 09:29:42 /home/oracle/backup/archivelog_0hqp4646_1_1
-rw-r----- 1 oracle oinstall 1269702656 2015-12-18 09:29:15 /home/oracle/backup/inc0_0fqp4638_1_1
-rw-r----- 1 oracle oinstall    9830400 2015-12-18 09:29:20 /home/oracle/backup/inc0_0gqp463f_1_1
-rw-r----- 1 oracle oinstall    2088960 2015-12-18 09:32:03 /home/oracle/backup/inc1_0iqp468j_1_1
-rw-r----- 1 oracle oinstall    9830400 2015-12-18 09:32:05 /home/oracle/backup/inc1_0jqp468k_1_1

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/inc0_0gqp463f_1_1';
Starting restore at 2015-12-18 09:36:22
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-18 09:36:24


RMAN> sql 'alter database mount ';
sql statement: alter database mount
released channel: ORA_DISK_1

--建立恢复脚本:

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

RMAN> catalog start with '/home/oracle/backup/inc1' ;
searching for all files that match the pattern /home/oracle/backup/inc1

List of Files Unknown to the Database
=====================================
File Name: /home/oracle/backup/inc1_0iqp468j_1_1
File Name: /home/oracle/backup/inc1_0jqp468k_1_1

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /home/oracle/backup/inc1_0iqp468j_1_1
File Name: /home/oracle/backup/inc1_0jqp468k_1_1

--在恢复前先catalog注册level=1的备份,先恢复到truncate之前,scn=1462876.
--执行以上脚本,带入参数1462876.

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

--可以发现问题依旧,为什么呢?实际是上很简单因为选择我建立了level 1,但是我恢复的scn=1462876在建立之前,不会使用增量
--level=1的备份,所以依旧无法恢复。

RMAN> list backupset 18;
List of Backup Sets
===================
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
18      Incr 1  1.98M      DISK        00:00:00     2015-12-18 09:32:03
        BP Key: 18   Status: AVAILABLE  Compressed: NO  Tag: INC1
        Piece Name: /home/oracle/backup/inc1_0iqp468j_1_1
  List of Datafiles in backup set 18
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  1    1  Incr 1462898    2015-12-18 09:32:03 /mnt/ramdisk/book/system01.dbf
  2    1  Incr 1462898    2015-12-18 09:32:03 /mnt/ramdisk/book/sysaux01.dbf
  3    1  Incr 1462898    2015-12-18 09:32:03 /mnt/ramdisk/book/undotbs01.dbf
  4    1  Incr 1462898    2015-12-18 09:32:03 /mnt/ramdisk/book/users01.dbf
  5    1  Incr 1462898    2015-12-18 09:32:03 /mnt/ramdisk/book/example01.dbf
  6    1  Incr 1462898    2015-12-18 09:32:03 /mnt/ramdisk/book/sugar01.dbf

--也就是level =1 的备份在Ckp SCN=1462898.也就是恢复到这里应该可以恢复正常,继续测试,重启关闭数据库,到mount状态,继续恢
--复(以下测试都是这样,不再重复述说):

--先选择参数1462897.小1.
SYS@book> alter database open read only ;
Database altered.

SYS@book> select count(*) from scott.t2;
select count(*) from scott.t2
                           *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 131)
ORA-01110: data file 6: '/mnt/ramdisk/book/sugar01.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
 
--不行!!!
--再选择参数1462898.

RMAN> @ a.rman 1462898

RMAN> run
2> {
3>      set until scn 1462898;
4>      restore database;
5>      recover database;
6> }
executing command: SET until clause

Starting restore at 2015-12-18 10:02:53
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: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /mnt/ramdisk/book/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /mnt/ramdisk/book/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /mnt/ramdisk/book/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /mnt/ramdisk/book/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /mnt/ramdisk/book/example01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /mnt/ramdisk/book/sugar01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/inc0_0fqp4638_1_1
channel ORA_DISK_1: piece handle=/home/oracle/backup/inc0_0fqp4638_1_1 tag=INC0
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 2015-12-18 10:02:57

Starting recover at 2015-12-18 10:02:57
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /mnt/ramdisk/book/system01.dbf
destination for restore of datafile 00002: /mnt/ramdisk/book/sysaux01.dbf
destination for restore of datafile 00003: /mnt/ramdisk/book/undotbs01.dbf
destination for restore of datafile 00004: /mnt/ramdisk/book/users01.dbf
destination for restore of datafile 00005: /mnt/ramdisk/book/example01.dbf
destination for restore of datafile 00006: /mnt/ramdisk/book/sugar01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/inc1_0iqp468j_1_1
channel ORA_DISK_1: piece handle=/home/oracle/backup/inc1_0iqp468j_1_1 tag=INC1
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
starting media recovery
archived log for thread 1 with sequence 26 is already on disk as file /u01/app/oracle/archivelog/book/1_26_896605872.dbf
archived log file name=/u01/app/oracle/archivelog/book/1_26_896605872.dbf thread=1 sequence=26
media recovery complete, elapsed time: 00:00:00
Finished recover at 2015-12-18 10:03:00
RMAN> **end-of-file**

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

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


总结:
1.注意一些数据库没有打开force_logging=no,有一些操作会存在不可恢复的情况,这个在一些生产系统要特别注意。特别没有dg的环境。
  如果在生产系统做了这些操作,要注意检查视图v$datafile的UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME字段,及时备份相关数据文件。
  总之及时备份很重要。
2.增量备份也可以弥补这种不足,但是及时备份也很重要。否则还是存在不能恢复的风险。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-1874358/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2488
  • 访问量
    6293210