BLOG文檔結果圖:
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIn5GcuMjM3gDNkVGZyETYlN2NhZGOjJzMiV2Y4kTY4gTZ2MDNfdWbp9CXt92Yu4GZjlGbh5SZslmZxl3Lc9CX6MHc0RHaiojIsJye.png)
隻讀資料檔案是隻讀表空間的資料檔案,其資料塊包括檔案頭在内不允許更改(少數管理性指令除外)。
将表空間設定為隻讀狀态的指令:
SQL> alter tablespace TBS_READ read only;
Tablespace altered.
将表空間重新設定為正常的讀/寫狀态的指令:
SQL> alter tablespace TBS_READ read write;
獲得隻讀表空間及其資料檔案的sql語句:
SQL> set line 9999
SQL> col file_name format a50
SQL> select t.TABLESPACE_NAME, d.FILE_ID, d.FILE_NAME
2 from dba_tablespaces t, dba_data_files d
3 WHERE t.TABLESPACE_NAME = d.TABLESPACE_NAME
4 and t.STATUS = 'READ ONLY' ;
TABLESPACE_NAME FILE_ID FILE_NAME
----------------------- ---------- --------------------------------------------------
TBS_READ 5 /u01/app/oracle/oradata/utf8test/tbs_read01.dbf
SQL>
使用隻讀表空間避免對靜态資料的頻繁備份
當使用alter tablespace tbs read only時,資料檔案會執行檢查點程序(将所有髒緩沖區的内容寫至磁盤),目前的SCN号會被标注,同時存儲了SCN的資料檔案頭部被當機.控制檔案内也會記錄該資料檔案的當機資訊。
可以清除隻讀表空間的對象
當一個表空間從讀/寫狀态更改為隻讀狀态時,其資料檔案中的髒資料塊必須由DBWn程序悉數寫回磁盤,完成一次不完整的完全檢查點,該表空間内資料檔案即稱為隻讀資料檔案,其資料塊及檔案頭資訊包括檢查點在内從此均不再更新,每次打開資料庫執行個體也不會在乎隻讀檔案頭的檢查點SCN是否和其他資料檔案的活線上日志同步,但各種錯誤(ORA-01116,ORA-01110,ORA-01578,ORA-01157等等錯誤)仍然會發生。以下圖檔為從電子書上截取過來的:
不像其他類型的資料檔案,在隻讀檔案頭損壞後,在發生檢查點時,所有程序視其為無物,執行個體不會崩潰(關鍵資料檔案頭損壞的後果),檔案也不會自動下線(普通資料檔案頭損壞的後果),總體上隻讀檔案安然無恙,隻是當執行需要通路頭部的操作時才在告警日志和追蹤檔案中留下痕迹而已,比如:
SQL> select checkpoint_change# from v$datafile where file#=5;
CHECKPOINT_CHANGE#
------------------
1865187
但告警日志報錯:
ORA-19563: datafile header validation failed for file /u01/app/oracle/oradata/utf8test/tbs_read01.dbf
ORA-01251: Unknown File Header Version read for file number 5
ORA-01578: ORACLE data block corrupted (file # 5, block # 130)
ORA-01110: data file 5: '/u01/app/oracle/oradata/utf8test/tbs_read01.dbf'
此刻其内部的所有隊形還是可以查詢的(隻要對應的資料塊沒有損壞),但是 alter tablspace ... read write 和 alter tablespace ... offline 之後的online回報ora-01210資料檔案頭損壞錯誤。
由于隻讀資料檔案内沒有一個資料塊能夠被修改,是以,一般情況下,隻讀表空間隻需要進行一次備份,尤其是當隻讀資料檔案占用很大空間的時候,這樣做可以節省備份資料庫的時間。即當表空間狀态發生改變時應立即進行備份。可以使用OS系統cp指令來備份或RMAN進行備份隻讀表空間。備份其他類型資料檔案的方式均适用于隻讀資料檔案,比如:
v backup as backupset tablespace TBS_READ;
v backup as copy tablespace TBS_READ;
v backup as backupset datafile 5;
使用RMAN時建議啟用備份優化選項,具體保留幾份備份由備份保留政策決定:使用備援度時保留數量為備援度加1,使用恢複視窗時保留數量為1.
RMAN> CONFIGURE BACKUP OPTIMIZATION ON;
以下例子示範備份優化功能,初始狀态下是沒有任何備份的:
首先啟用備份優化:
[oracle@rhel6_lhr dbs]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Tue Feb 3 10:02:05 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: lilove (DBID=888888)
using target database control file instead of recovery catalog
new RMAN configuration parameters:
CONFIGURE BACKUP OPTIMIZATION ON;
new RMAN configuration parameters are successfully stored
設定備份保留政策,使用恢複視窗3天:
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 3 DAYS;
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 3 DAYS;
RMAN> show retention policy;
RMAN configuration parameters for database with db_unique_name LILOVE are:
使用backup database 指令備份資料庫内所有的資料檔案,注意有tbs_read01.dbf檔案:
RMAN> backup database;
Starting backup at 2015-02-03 10:03:31
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=264 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/utf8test/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/utf8test/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/utf8test/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/utf8test/users01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/utf8test/tbs_read01.dbf
channel ORA_DISK_1: starting piece 1 at 2015-02-03 10:03:32
channel ORA_DISK_1: finished piece 1 at 2015-02-03 10:05:18
piece handle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/1opuba7k_1_1 tag=TAG20150203T100332 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:46
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 2015-02-03 10:05:20
channel ORA_DISK_1: finished piece 1 at 2015-02-03 10:05:21
piece handle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/1ppubaau_1_1 tag=TAG20150203T100332 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2015-02-03 10:05:21
RMAN>
當第二次執行backup database指令時,輸出中是找不到tbs_read01.dbf檔案的,rman認為沒有必要反複備份隻讀檔案:
Starting backup at 2015-02-03 10:08:48
using channel ORA_DISK_1
skipping datafile 5; already backed up 1 time(s)
channel ORA_DISK_1: starting piece 1 at 2015-02-03 10:08:48
channel ORA_DISK_1: finished piece 1 at 2015-02-03 10:10:13
piece handle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/1qpubahg_1_1 tag=TAG20150203T100848 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:26
channel ORA_DISK_1: starting piece 1 at 2015-02-03 10:10:15
channel ORA_DISK_1: finished piece 1 at 2015-02-03 10:10:16
piece handle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/1rpubak6_1_1 tag=TAG20150203T100848 comment=NONE
Finished backup at 2015-02-03 10:10:16
若保留政策是備援度3,則需要等到第5次執行backup database 時才會不帶tbs_read01.dbf 檔案。另外,如果備份時使用backup tablespace 或 backup datafile指令顯式備份隻讀資料檔案,那麼RMAN将忽略優化政策。
備份隻讀資料檔案除了可以使用以上備份其他資料檔案的方法之外,還可以無需任何準備工作直接使用作業系統的cp複制指令備份,比如:
[oracle@rhel6_lhr dbs]$ cp /u01/app/oracle/oradata/utf8test/tbs_read01.dbf /tmp/tbs_read01.bak
另外:隻讀表空間不支援熱備
SQL> alter tablespace tbs_read begin backup;
alter tablespace tbs_read begin backup
*
ERROR at line 1:
ORA-01642: begin backup not needed for read-only tablespace 'TBS_READ'
SQL>
需要特别注意的是,當一個表空間從隻讀(READ ONLY)狀态改變為讀/寫(READ WRITE)狀态後,應該立即備份其資料檔案及當時(成為READ WRITE狀态後)的控制檔案,否則将來若控制檔案連同原來的隻讀資料檔案同時損壞,在恢複流程中可能出現“ORA-01152:資料檔案不夠舊”的錯誤。
其他類型的資料檔案恢複過程均包括2個必要步驟:還原(restore)和恢複(recover),對于不可能有更改操作的隻讀檔案來說,重做日志是沒有意義的,當然也就沒有恢複的必要的,是以,所謂的恢複實際上隻有一個步驟:還原。
在mount狀态下低可用性恢複政策的步驟如下:
① startup mount
② rman的restore 或switch指令還原資料檔案
③ alter database open
在open狀态下高可用性恢複政策的步驟如下:
① alter database datafile xx offline
② rman的restore force 或switch指令還原資料檔案。
③ alter database datafile xx online
隻讀資料檔案的恢複可以采用低可用性恢複政策和高可用性政策,前者是在資料庫mount狀态下進行,或者是在資料庫open狀态下進行。低可用性恢複政策要求參數檔案和控制檔案必須就位,高可用性政策額外要求資料檔案必須就位,若不滿足條件必須先進行相應的恢複。
控制檔案無損情況下的恢複指:隻讀資料檔案損壞時控制檔案沒有損壞
場景1:隻讀資料檔案tbs_read01.dbf 丢失,資料庫無法正常啟動,停留在mount狀态,錯誤号“ORA-01157:cannot identify/locak data file 5 -see DBWR trace file.”
遇到以上情況隻要使用rman執行restore(合适備份集)或switch(合适鏡像複制)指令還原資料檔案,然後打開資料庫即可:
RMAN> restore datafile 5;
Starting restore at 2015-02-03 11:19:45
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 00005 to /u01/app/oracle/oradata/utf8test/tbs_read01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/11.2.0/dbhome_1/dbs/1tpubeef_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/1tpubeef_1_1 tag=TAG20150203T111527
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 2015-02-03 11:19:46
RMAN> alter database open;
Database altered.
場景2:隻讀資料檔案tbs_read01.dbf 在執行個體運作時丢失,導緻資料無法通路,錯誤如下:
SQL> select * from aabbcc;
select * from aabbcc
*
ORA-01116: error in opening database file 5
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
該情況可以将資料檔案下線後通過restore 或switch指令還原,然後上線:
run{
sql 'alter database datafile 5 offline';
restore datafile 5;
sql 'alter database datafile 5 online';
}
場景3:運作時隻讀資料檔案tbs_read01.dbf内部資料塊損壞,導緻資料無法通路,但是檔案依然存在,錯誤:
SQL> select * from tst;
select * from tst
*
ORA-01578: ORACLE data block corrupted (file # 5, block # 131)
以上情況還原時需要加force關鍵字,否則不能正确還原資料檔案(若在mount狀态下執行restore指令則無需force關鍵字):
restore datafile 5 force;
使用鏡像複制的switch指令不必使用force關鍵字:
switch datafile 5 to datafilecopy '/tmp/ol_mf_exam.dbf';
場景4:起先資料檔案tbs_read01.dbf備份時是隻讀的,後來修改表空間為讀寫,但沒有備份,現在該檔案損壞了:
recover datafile 5;
場景5:起先資料檔案tbs_read01.dbf備份時是讀寫狀态,後來其所在的表空間為隻讀狀态,但沒有備份,現在資料檔案損壞了:
所謂控制檔案損壞情況下的恢複是指隻讀資料檔案和控制檔案一并損壞時的恢複,一般步驟包括恢複控制檔案和隻讀資料檔案,步驟如下:
(一)将資料庫啟動到nomount狀态
(二)用restore指令還原控制檔案
(三)将資料庫啟動至mount狀态
(四)用restore還原隻讀資料檔案
(五)用recover指令恢複整個資料庫
(六)用resetlogs方式打開資料庫
場景1:隻讀資料檔案tbs_read01.dbf連同控制檔案一并損壞和丢失,啟動執行個體隻能停留在nomount狀态,錯誤報告:“ORA-00205:error in identifying control file,check alert log for more info”
假設擁有控制檔案的自動備份,則:
restore controlfile from autobackup;
mount database;
recover database;
alter database open resetlogs;
實驗,此實驗室首先備份整個資料庫,然後修改隻讀表空間屬性,總之就是經過一大堆的修改操作,然後再進行還原:
删除控制檔案和隻讀檔案:
[root@rhel6_lhr utf8test]# ll
total 1597788
-rw-r----- 1 oracle asmadmin 10076160 Feb 3 16:09 control01.ctl
-rw-r----- 1 oracle asmadmin 10076160 Feb 3 16:09 control02.ctl
-rw-r----- 1 oracle asmadmin 52429312 Feb 3 09:27 redo01.log
-rw-r----- 1 oracle asmadmin 52429312 Feb 3 15:57 redo02.log
-rw-r----- 1 oracle asmadmin 52429312 Feb 3 16:09 redo03.log
-rw-r----- 1 oracle asmadmin 608182272 Feb 3 16:05 sysaux01.dbf
-rw-r----- 1 oracle asmadmin 775954432 Feb 3 16:05 system01.dbf
-rw-r----- 1 oracle asmadmin 10493952 Feb 3 16:05 tbs_read01.dbf
-rw-r----- 1 oracle asmadmin 20979712 Feb 3 10:27 temp01.dbf
-rw-r----- 1 oracle asmadmin 52436992 Feb 3 16:05 undotbs01.dbf
-rw-r----- 1 oracle asmadmin 10493952 Feb 3 16:05 users01.dbf
[root@rhel6_lhr utf8test]# rm -rf control0*
[root@rhel6_lhr utf8test]# rm tbs_read01.dbf
rm: remove regular file `tbs_read01.dbf'? y
total 1567860
-rw-r----- 1 oracle asmadmin 52429312 Feb 3 16:10 redo03.log
-rw-r----- 1 oracle asmadmin 608182272 Feb 3 16:10 sysaux01.dbf
-rw-r----- 1 oracle asmadmin 775954432 Feb 3 16:10 system01.dbf
-rw-r----- 1 oracle asmadmin 52436992 Feb 3 16:10 undotbs01.dbf
重新開機資料庫:
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 501059584 bytes
Fixed Size 2229744 bytes
Variable Size 348129808 bytes
Database Buffers 142606336 bytes
Redo Buffers 8093696 bytes
ORA-00205: error in identifying control file, check alert log for more info
進行rman恢複:
Recovery Manager: Release 11.2.0.3.0 - Production on Tue Feb 3 16:11:52 2015
connected to target database: LILOVE (not mounted)
RMAN> restore controlfile from autobackup;
Starting restore at 2015-02-03 16:11:57
channel ORA_DISK_1: SID=10 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-03002: failure of restore command at 02/03/2015 16:11:57
RMAN-06495: must explicitly specify DBID with SET DBID command
RMAN> set dbid 888888
executing command: SET DBID
Starting restore at 2015-02-03 16:12:25
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150203
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150202
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150201
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150131
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150130
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150129
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150128
channel ORA_DISK_1: no AUTOBACKUP in 7 days found
RMAN-03002: failure of restore command at 02/03/2015 16:12:27
RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece
RMAN> restore controlfile from '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_LIHUARONG.f';
Starting restore at 2015-02-03 16:14:40
channel ORA_DISK_1: copied control file copy
output file name=/u01/app/oracle/oradata/utf8test/control01.ctl
output file name=/u01/app/oracle/oradata/utf8test/control02.ctl
Finished restore at 2015-02-03 16:14:42
RMAN> mount database;
database mounted
released channel: ORA_DISK_1
Starting restore at 2015-02-03 16:15:06
channel ORA_DISK_1: SID=237 device type=DISK
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/11.2.0/dbhome_1/dbs/1opuba7k_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/1opuba7k_1_1 tag=TAG20150203T100332
Finished restore at 2015-02-03 16:15:07
RMAN> recover database;
Starting recover at 2015-02-03 16:15:15
datafile 5 not processed because file is read-only
starting media recovery
archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/oradata/utf8test/redo02.log
archived log for thread 1 with sequence 3 is already on disk as file /u01/app/oracle/oradata/utf8test/redo03.log
archived log file name=/u01/app/oracle/oradata/utf8test/redo02.log thread=1 sequence=2
archived log file name=/u01/app/oracle/oradata/utf8test/redo03.log thread=1 sequence=3
Oracle Error:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 5 was not restored from a sufficiently old backup
media recovery complete, elapsed time: 00:00:01
Finished recover at 2015-02-03 16:15:17
注意:上邊的恢複過程中提示:datafile 5 not processed because file is read-only,ORA-01152: file 5 was not restored from a sufficiently old backup ,造成此問題的原因是資料檔案頭和控制檔案内資訊一緻,5号檔案沒有被列入恢複的範疇,是以有:datafile 5 not processed because file is read-only,但是随着恢複的進行,在重做日志中發現有将5号檔案置為讀/寫狀态的redo記錄,是以在recover指令結束後抛出錯誤:ORA-01152: file 5 was not restored from a sufficiently old backup,是以解決辦法也很簡單就是再次執行recover database。
經過以上分析,我們再次執行recover database:
Starting recover at 2015-02-03 16:15:46
applied offline range to datafile 00005
offline range RECID=2 STAMP=870711316
Finished recover at 2015-02-03 16:15:47
RMAN> alter database open resetlogs;
database opened
下面對表空間TBS_READ置為隻讀後對比前後生成的重建控制檔案的腳本
對比兩者可以發現,設定隻讀屬性後,腳本中并沒有列出隻讀表空間的資料檔案。
是以:
1.使用create controlfile指令時,datafile中未列出隻讀表空間的資料檔案
2.成功建立控制檔案并打開後,使用alter database rename file指令重命名隻讀表空間的資料檔案
3.使用alter tablespace readonly_tablespacename online 将隻讀表空間聯機
SQL> select file#,name,enabled from v$datafile where file#=6;
FILE# NAME ENABLED
---------- --------------------------------------------- ----------
6 /u01/app/oracle/oradata/orcl/tbs01.dbf READ ONLY
SQL> select segment_name,segment_type,tablespace_name,owner from dba_segments where
2 tablespace_name='TBS1' and segment_name='TB2';
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME OWNER
-------------------- ------------------ ------------------------------ ----------
TB2 TABLE TBS1 SCOTT
SQL> drop table scott.tb2;
Table dropped.
? 1. 表空間置為隻讀後将減少資料的備份量
? 2. 表空間置為隻讀後,不能對其中的對象執行任何DML操作
? 3. 隻讀表空間内的對象可以被清除,因為drop指令更新了資料字典,而不更新對象本身
? 4. 當表空間的狀态發生變化時,應立即備份該表空間,以減少恢複工作,一旦表空間從隻讀狀态更改為讀/寫狀态,應該立即對其進行備份。
? 5. 對于狀态多次發生改變且未及時備份的情況,日志未損壞時,可以使用聯機重做、歸檔日志來進行恢複
使用下列指令來實作:
删除受損的資料檔案(rm dbfile.dbf)
重建受損的資料檔案(alter database create datafile n)
進行媒體恢複(recover datafile n)
使受損的資料檔案聯機(alter database datafile n online)
? 6. 示範多為在mount狀态下來恢複,生産環境中多在open狀态下恢複,可以按下列步驟實作
先将受損的隻讀表空間(資料檔案)脫機(offline)
使用備份的表空間(資料檔案)來還原(restore)
使用歸檔、聯機日志進行媒體恢複(recover)
使恢複成功的表空間(資料檔案)聯機(online)
? 7. 對于原始媒體受損,不能恢複到原始位置的情況下,使用下面的指令實作轉移
alter database rename file '' to '';
? 建議啟用控制檔案自動備份功能,這樣在“alter tablespace ... read only”和“alter tablespace ... read write”後,控制檔案可以自動備份。
? 當控制檔案和資料檔案(隻讀或曾經處于隻讀)同時損壞,一個保險的run塊應該這樣寫(假設控制檔案自動備份已啟用,隻讀檔案為5):
startup force nomount;
restore controlfile from autobackup;
mount database;
restore datafile 5;
recover database;
alter database open resetlogs;