[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.增量备份也可以弥补这种不足，但是及时备份也很重要。否则还是存在不能恢复的风险。
