[20151218]資料檔案的unrecover與增量備份.txt
--前一陣子我給别人示範truncate的不完全恢複，結果非常難堪的遇到無法恢複的情況。
--問題是我建立的資料庫按照這個連結建立的。
<a href="http://blog.itpub.net/267265/viewspace-1845062/">http://blog.itpub.net/267265/viewspace-1845062/</a>
--而這樣建立的資料庫表空間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的表空間資料檔案.
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.
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
--注意 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.
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
--在恢複前先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.
*
--不行！！！
--再選擇參數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
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
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
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**
COUNT(*)
----------
100000
總結：
1.注意一些資料庫沒有打開force_logging=no,有一些操作會存在不可恢複的情況，這個在一些生産系統要特别注意。特别沒有dg的環境。
如果在生産系統做了這些操作，要注意檢查視圖v$datafile的UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME字段，及時備份相關資料檔案。
總之及時備份很重要。
2.增量備份也可以彌補這種不足，但是及時備份也很重要。否則還是存在不能恢複的風險。
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)