[20161031]rman備份與資料檔案變化3.txt
[20161031]rman備份與資料檔案變化3.txt
--想象一下,如果備份檔案時間很長,而這個時候資料檔案大小發生了變化,oracle的備份如何解決這個問題呢?
--前面我已經做了增大資料檔案,參考連結:http://blog.itpub.net/267265/viewspace-2127386/
--這次測試減少資料檔案大小看看。
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 40M AUTOEXTEND ON NEXT 16M MAXSIZE UNLIMITED
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
create table t1 tablespace sugar as select rownum id ,lpad('A',32,'A') name from dual connect by level<=1e5;
--建立大小5M的表。
create table t2 tablespace sugar as select rownum id ,lpad('B',32,'B') name from dual connect by level<=2e5;
create table t3 tablespace sugar as select rownum id ,lpad('C',32,'C') name from dual connect by level<=2e5;
alter system checkpoint;
SCOTT@book> select sum(bytes) from dba_extents where owner=user and segment_name like 'T%';
SUM(BYTES)
------------
26214400
--大約佔用26214400/1024/1024=25m。
$ ls -l /mnt/ramdisk/book/sugar01.dbf
-rw-r----- 1 oracle oinstall 41951232 2016-11-01 09:41:29 /mnt/ramdisk/book/sugar01.dbf
--當前大小40M+8k。 40*1024*1024+8192=41951232
2.備份:
RMAN> CONFIGURE CHANNEL 1 DEVICE TYPE DISK RATE 128 K;
new RMAN configuration parameters:
CONFIGURE CHANNEL 1 DEVICE TYPE DISK RATE 128 K;
new RMAN configuration parameters are successfully stored
--//主要目的減慢備份速度。
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET;
old RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters are successfully stored
--開始備份:
RMAN> backup datafile 6 format '/u01/backup/d6_A_%U' ;
.....
--切換會話刪除表T2,T3,操作有點多,寫入1個指令碼abc.sql執行它。
drop table t2 purge ;
host sleep 1.5
drop table t3 purge ;
host sleep 1
alter system checkpoint;
ALTER DATABASE DATAFILE '/mnt/ramdisk/book/sugar01.dbf' RESIZE 10M;
alter system checkpoint;
$ ls -l /u01/backup/d6_A*
-rw-r----- 1 oracle oinstall 41975808 2016-11-01 09:47:37 /u01/backup/d6_A_15rjqgkp_1_1
--可以發現是先產生備份檔案的大小,然後再寫入操作。
--指令碼執行期間遇到
SCOTT@book> @ abc.sql
Table dropped.
Table dropped.
System altered.
ALTER DATABASE DATAFILE '/mnt/ramdisk/book/sugar01.dbf' RESIZE 10M
*
ERROR at line 1:
ORA-19567: cannot shrink file /mnt/ramdisk/book/sugar01.dbf because it is being backed up or copied
System altered.
--可以發現在備份期間不能shrink表。
==============
$ oerr ora 19567
19567, 00000, "cannot shrink file %s because it is being backed up or copied"
// *Cause: An ALTER statement attempted to reduce the size of the indicated
// file while the same file is being backed up or copied.
// *Action: Retry the resize after the backup or copy is complete.
====================================
SCOTT@book> ALTER DATABASE DATAFILE '/mnt/ramdisk/book/sugar01.dbf' RESIZE 10M;
Database altered.
--奇怪我手工執行又ok。非常奇怪。
--說明:這裡我重複多次,都是先報錯ora-19567,第2次執行都可以透過,估計是bug嗎?
$ ls -l /mnt/ramdisk/book/sugar01.dbf
-rw-r----- 1 oracle oinstall 41951232 2016-11-01 09:47:14 /mnt/ramdisk/book/sugar01.dbf
--但是檔案大小沒有改變。
RMAN> backup datafile 6 format '/u01/backup/d6_A_%U' ;
Starting backup at 2016-11-01 09:47:04
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=90 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=00006 name=/mnt/ramdisk/book/sugar01.dbf
channel ORA_DISK_1: starting piece 1 at 2016-11-01 09:47:05
channel ORA_DISK_1: finished piece 1 at 2016-11-01 09:52:30
piece handle=/u01/backup/d6_A_15rjqgkp_1_1 tag=TAG20161101T094705 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:05:25
channel ORA_DISK_1: throttle time: 0:05:20
Finished backup at 2016-11-01 09:52:30
Starting Control File and SPFILE Autobackup at 2016-11-01 09:52:30
piece handle=/u01/app/oracle/fast_recovery_area/BOOK/autobackup/2016_11_01/o1_mf_s_926761950_d1hxbyh6_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2016-11-01 09:52:31
--需要將近5分25秒.
$ ls -l /mnt/ramdisk/book/sugar01.dbf
-rw-r----- 1 oracle oinstall 41951232 2016-11-01 09:47:14 /mnt/ramdisk/book/sugar01.dbf
--可以發現檔案並沒有shrink到10m。
SCOTT@book> ALTER DATABASE DATAFILE '/mnt/ramdisk/book/sugar01.dbf' RESIZE 10M;
Database altered.
$ ls -l /mnt/ramdisk/book/sugar01.dbf
-rw-r----- 1 oracle oinstall 41951232 2016-11-01 09:47:14 /mnt/ramdisk/book/sugar01.dbf
--可以發現這個時候已經出現異常,無法shrink。或者內部一些字典已經修改了。
BBED> p /d dba 6,1 kcvfhhdr.kccfhfsz
ub4 kccfhfsz @44 5120
--可以發現執行ALTER DATABASE DATAFILE '/mnt/ramdisk/book/sugar01.dbf' RESIZE 10M;是失敗的。
-- 5120*8192+8192=41951232
$ ls -l /u01/backup/d6_A_15rjqgkp_1_1
-rw-r----- 1 oracle oinstall 26083328 2016-11-01 09:52:25 /u01/backup/d6_A_15rjqgkp_1_1
$ strings /u01/backup/d6_A* | grep 'BBBB'|wc
200000 340080 7243655
$ strings /u01/backup/d6_A* | grep 'CCCC'|wc
200000 340080 7243655
$ strings /u01/backup/d6_A* | grep 'AAAA'|wc
100000 170040 3624269
--可以發現備份也做了T2,T3備份。
SCOTT@book> select file#,CHECKPOINT_CHANGE#,ABSOLUTE_FUZZY_CHANGE# from v$backup_datafile;
FILE# CHECKPOINT_CHANGE# ABSOLUTE_FUZZY_CHANGE#
---------- ------------------ ----------------------
6 2404824 0
0 2405169 0
--而且備份期間沒有出現高於檢查點scn高於2404824的scn號。
SCOTT@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name FROM v$datafile_header where file#=6;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- ----------------------------- ---------------
6 2404875 2016-11-01 09:47:14 2404192 2002065 ONLINE 6 YES /mnt/ramdisk/book/sugar01.dbf SUGAR
--從這裡可以看出備份時視乎已經確定要備份檔案的大小,而且我覺得備份期間讀取了點陣圖資訊,僅僅非NULL的塊已經確定,應該是從文
--件頭點陣圖確定,這個時候實際上不能縮小資料檔案的。
--你可以看出我已經發出了檢查點,但是T2的資訊,T3的資訊依舊出現備份集中。
--而且從前面的測試,明視訊記憶體在問題,建議不要在備份期間做shrink資料檔案的錯誤。
3.繼續看看資料檔案頭在備份集什麼位置:
--透過bbed觀察:
BBED> set dba 6,1
DBA 0x01800001 (25165825 6,1)
BBED> p kcvfh.kcvfhtln
ub2 kcvfhtln @336 0x0005
BBED> p kcvfh.kcvfhtnm
text kcvfhtnm[0] @338 S
text kcvfhtnm[1] @339 U
text kcvfhtnm[2] @340 G
text kcvfhtnm[3] @341 A
text kcvfhtnm[4] @342 R
text kcvfhtnm[5] @343
--可以發現表資料檔案裡面記錄了表空間名字。前面一個欄位kcvfh.kcvfhtln記錄表空間名長度,正好是5。
$ strings -t d /u01/backup/d6_A_15rjqgkp_1_1 | grep SUGAR
26067282 SUGAR
-- 26067282/8192=3182.041259765625M,相關記錄在備份集中3182塊。
-- 而實際備份集檔案大小26083328,26083328/8192=3184塊。也就是除掉備份集塊頭有3183塊(備份集也有1個OS塊)
-- 從這裡可以看出檔案頭先讀出,但是是最後寫入備份集的,當然最後還有1塊不知道寫的是什麼。
BBED> set filename '/u01/backup/d6_A_15rjqgkp_1_1'
FILENAME /u01/backup/d6_A_15rjqgkp_1_1
BBED> set block 3182
BLOCK# 3182
BBED> p kcvfh.kcvfhtln
ub2 kcvfhtln @336 0x0005
BBED> p kcvfh.kcvfhtnm
text kcvfhtnm[0] @338 S
text kcvfhtnm[1] @339 U
text kcvfhtnm[2] @340 G
text kcvfhtnm[3] @341 A
text kcvfhtnm[4] @342 R
text kcvfhtnm[5] @343
..
BBED> p /d kcvfh.kcvfhcrs.kscnbas
ub4 kscnbas @100 2404192
BBED> p /d kcvfh.kcvfhckp.kcvcpscn.kscnbas
ub4 kscnbas @484 2404824
--//這個結果與資料塊對應的是:
BBED> p /d dba 6,1 kcvfh.kcvfhcrs.kscnbas
ub4 kscnbas @100 2404192
BBED> p /d dba 6,1 kcvfh.kcvfhckp.kcvcpscn.kscnbas
ub4 kscnbas @484 2404875
--看看資料檔案大小:
BBED> p /d dba 6,1 kcvfhhdr.kccfhfsz
ub4 kccfhfsz @44 5120
BBED> p /d filename '/u01/backup/d6_A_15rjqgkp_1_1' block 3182 kcvfhhdr.kccfhfsz
ub4 kccfhfsz @44 5120
-- 5376*8192+8192=44048384,兩者一致。
$ ls -l /mnt/ramdisk/book/sugar01.dbf
-rw-r----- 1 oracle oinstall 41951232 2016-11-01 09:47:14 /mnt/ramdisk/book/sugar01.dbf
4.資料字典不一致問題:
SCOTT@book> select * from DBA_DATA_FILES;
FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_
-------------------------------- ------- --------------- ------------ ------------ --------- ------------ --- ------------ ------------ ------------ ------------ ------------ -------
/mnt/ramdisk/book/users01.dbf 4 USERS 52428800 6400 AVAILABLE 4 YES 34359721984 4194302 160 51380224 6272 ONLINE
/mnt/ramdisk/book/undotbs01.dbf 3 UNDOTBS1 89128960 10880 AVAILABLE 3 YES 1073741824 131072 640 88080384 10752 ONLINE
/mnt/ramdisk/book/sysaux01.dbf 2 SYSAUX 817889280 99840 AVAILABLE 2 YES 34359721984 4194302 1280 816840704 99712 ONLINE
/mnt/ramdisk/book/system01.dbf 1 SYSTEM 786432000 96000 AVAILABLE 1 YES 34359721984 4194302 1280 785383424 95872 SYSTEM
/mnt/ramdisk/book/example01.dbf 5 EXAMPLE 328335360 40080 AVAILABLE 5 YES 34359721984 4194302 80 327286784 39952 ONLINE
/mnt/ramdisk/book/sugar01.dbf 6 SUGAR 10485760 1280 AVAILABLE 6 YES 34359721984 4194302 2048 9437184 1152 ONLINE
6 rows selected.
--可以發現這個時候file_id=6,bytes=10485760,blocks=1280.
SCOTT@book> column SPARE3 noprint
SCOTT@book> column SPARE4 noprint
SCOTT@book> select * from sys.file$ f where file#=6;
FILE# STATUS$ BLOCKS TS# RELFILE# MAXEXTEND INC CRSCNWRP CRSCNBAS OWNERINSTANCE SPARE1 SPARE2
----- ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------- ------------ ------------
6 2 5120 7 6 4194302 2048 0 2404192 25165826
--二者已經存在不一致。
我看了檢視DBA_DATA_FILES定義,出現問題是:
SELECT v.name
,f.file#
,ts.name
,DECODE (hc.ktfbhccval, 0, ts.blocksize * hc.ktfbhcsz, NULL)
,DECODE (hc.ktfbhccval, 0, hc.ktfbhcsz, NULL)
,DECODE (f.status$, 1, 'INVALID', 2, 'AVAILABLE', 'UNDEFINED')
,f.relfile#
,DECODE
(
hc.ktfbhccval
,0, DECODE (hc.ktfbhcinc, 0, 'NO', 'YES')
,NULL
)
,DECODE (hc.ktfbhccval, 0, ts.blocksize * hc.ktfbhcmaxsz, NULL)
,DECODE (hc.ktfbhccval, 0, hc.ktfbhcmaxsz, NULL)
,DECODE (hc.ktfbhccval, 0, hc.ktfbhcinc, NULL)
,DECODE (hc.ktfbhccval, 0, hc.ktfbhcusz * ts.blocksize, NULL)
,DECODE (hc.ktfbhccval, 0, hc.ktfbhcusz, NULL)
,DECODE
(
fe.fetsn
,0, DECODE (BITAND (fe.festa, 2), 0, 'SYSOFF', 'SYSTEM')
,DECODE
(
BITAND (fe.festa, 18)
,0, 'OFFLINE'
,2, 'ONLINE'
,'RECOVER'
)
)
FROM sys.v$dbfile v
,sys.file$ f
,sys.x$ktfbhc hc
,sys.ts$ ts
,x$kccfe fe
WHERE v.file# = f.file#
AND f.spare1 IS NOT NULL
AND v.file# = hc.ktfbhcafno
AND hc.ktfbhctsn = ts.ts#
AND fe.fenum = f.file#;
--訪問sys.x$ktfbhc 與實際不符合。
SYS@book> select * from x$ktfbhc ;
ADDR INDX INST_ID KTFBHCAFNO KTFBHCTSN KTFBHCFNO KTFBHCHAFNO KTFBHCHRFNO KTFBHCHBNO KTFBHCHSZ KTFBHCSZ KTFBHCINC KTFBHCMAXSZ KTFBHCFREE KTFBHCUSZ KTFBHCCVAL KTFBHCDBS KTFBHCDWRP
---------------- ---------- ---------- ---------- ---------- ---------- ----------- ----------- ---------- ---------- ---------- ---------- ----------- ---------- ---------- ---------- ---------- ----------
00007F21956C5A08 0 1 1 0 1 1 1 2 126 96000 1280 4194302 768 95872 0 2356313 0
00007F21956C5A08 1 1 2 1 2 2 2 2 126 99840 1280 4194302 5968 99712 0 2353749 0
00007F21956C5A08 2 1 3 2 3 3 3 2 126 10880 640 131072 9936 10752 0 2383655 0
00007F21956C5A08 3 1 4 4 4 4 4 2 126 6400 160 4194302 5872 6272 0 2358965 0
00007F21956C5A08 4 1 5 6 5 5 5 2 126 40080 80 4194302 376 39952 0 942056 0
00007F21956C5A08 5 1 6 7 6 6 6 2 126 1280 2048 4194302 512 1152 0 2404867 0
6 rows selected.
--重啟資料庫看看。
SYS@book> select * from DBA_DATA_FILES where file_id=6;
FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_
----------------------------- ------- --------------- ---------- ---------- --------- ------------ --- ---------- ---------- ------------ ---------- ----------- -------
/mnt/ramdisk/book/sugar01.dbf 6 SUGAR 10485760 1280 AVAILABLE 6 YES 3.4360E+10 4194302 2048 9437184 1152 ONLINE
--這裡標識使用塊數量USER_BLOCKS=1152
SYS@book> select * from x$ktfbhc ;
ADDR INDX INST_ID KTFBHCAFNO KTFBHCTSN KTFBHCFNO KTFBHCHAFNO KTFBHCHRFNO KTFBHCHBNO KTFBHCHSZ KTFBHCSZ KTFBHCINC KTFBHCMAXSZ KTFBHCFREE KTFBHCUSZ KTFBHCCVAL KTFBHCDBS KTFBHCDWRP
---------------- ---------- ---------- ---------- ---------- ---------- ----------- ----------- ---------- ---------- ---------- ---------- ----------- ---------- ---------- ---------- ---------- ----------
00007F781B888C20 0 1 1 0 1 1 1 2 126 96000 1280 4194302 768 95872 0 2356313 0
00007F781B888C20 1 1 2 1 2 2 2 2 126 99840 1280 4194302 5968 99712 0 2353749 0
00007F781B888C20 2 1 3 2 3 3 3 2 126 10880 640 131072 9936 10752 0 2383655 0
00007F781B888C20 3 1 4 4 4 4 4 2 126 6400 160 4194302 5872 6272 0 2358965 0
00007F781B888C20 4 1 5 6 5 5 5 2 126 40080 80 4194302 376 39952 0 942056 0
00007F781B888C20 5 1 6 7 6 6 6 2 126 1280 2048 4194302 512 1152 0 2404867 0
6 rows selected.
SYS@book> select sum(blocks),sum(bytes) from DBA_EXTENTS where file_id=6;
SUM(BLOCKS) SUM(BYTES)
------------ ------------
640 5242880
-- 而這裡看才僅僅使用640塊,存在1152-640=512差距。
$ dbv file=/mnt/ramdisk/book/sugar01.dbf
DBVERIFY: Release 11.2.0.4.0 - Production on Tue Nov 1 10:42:26 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /mnt/ramdisk/book/sugar01.dbf
DBVERIFY - Verification complete
Total Pages Examined : 5120
Total Pages Processed (Data) : 2974
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 201
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 1945
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 2404872 (0.2404872)
--dbv檢查沒有問題。
RMAN> validate datafile 6;
Starting validate at 2016-11-01 10:44:10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=68 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00006 name=/mnt/ramdisk/book/sugar01.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
6 OK 0 1945 5120 2404872
File Name: /mnt/ramdisk/book/sugar01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 2974
Index 0 0
Other 0 201
Finished validate at 2016-11-01 10:44:12
SYS@book> ALTER DATABASE DATAFILE '/mnt/ramdisk/book/sugar01.dbf' RESIZE 9M;
Database altered.
$ ls -l /mnt/ramdisk/book/sugar01.dbf
-rw-r----- 1 oracle oinstall 9445376 2016-11-01 10:46:17 /mnt/ramdisk/book/sugar01.dbf
--這樣才shrink
SYS@book> select * from DBA_DATA_FILES;
FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_
-------------------------------- ------- --------------- ------------ ------------ --------- ------------ --- ------------ ------------ ------------ ------------ ------------ -------
/mnt/ramdisk/book/users01.dbf 4 USERS 52428800 6400 AVAILABLE 4 YES 34359721984 4194302 160 51380224 6272 ONLINE
/mnt/ramdisk/book/undotbs01.dbf 3 UNDOTBS1 89128960 10880 AVAILABLE 3 YES 1073741824 131072 640 88080384 10752 ONLINE
/mnt/ramdisk/book/sysaux01.dbf 2 SYSAUX 817889280 99840 AVAILABLE 2 YES 34359721984 4194302 1280 816840704 99712 ONLINE
/mnt/ramdisk/book/system01.dbf 1 SYSTEM 786432000 96000 AVAILABLE 1 YES 34359721984 4194302 1280 785383424 95872 SYSTEM
/mnt/ramdisk/book/example01.dbf 5 EXAMPLE 328335360 40080 AVAILABLE 5 YES 34359721984 4194302 80 327286784 39952 ONLINE
/mnt/ramdisk/book/sugar01.dbf 6 SUGAR 9437184 1152 AVAILABLE 6 YES 34359721984 4194302 2048 8388608 1024 ONLINE
6 rows selected.
--//繼續做一個備份:
RMAN> backup datafile 6 format '/u01/backup/d6_B_%U' ;
Starting backup at 2016-11-01 10:49:35
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/mnt/ramdisk/book/sugar01.dbf
channel ORA_DISK_1: starting piece 1 at 2016-11-01 10:49:35
channel ORA_DISK_1: finished piece 1 at 2016-11-01 10:49:36
piece handle=/u01/backup/d6_B_1arjqk9v_1_1 tag=TAG20161101T104935 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2016-11-01 10:49:36
Starting Control File and SPFILE Autobackup at 2016-11-01 10:49:36
piece handle=/u01/app/oracle/fast_recovery_area/BOOK/autobackup/2016_11_01/o1_mf_s_926765376_d1j0p0p1_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2016-11-01 10:49:37
$ ls -l d6_B_1arjqk9v_1_1
-rw-r----- 1 oracle oinstall 6111232 2016-11-01 10:49:35 d6_B_1arjqk9v_1_1
4.測試恢復看看:
$ mv d6_B_1arjqk9v_1_1 d6_B
--//改名的主要目的是不使用這個備份來恢復。
SCOTT@book> alter database datafile 6 offline ;
Database altered.
RMAN> restore datafile 6;
Starting restore at 2016-11-01 10:52:26
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=68 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 00006 to /mnt/ramdisk/book/sugar01.dbf
channel ORA_DISK_1: reading from backup piece /u01/backup/d6_B_1arjqk9v_1_1
channel ORA_DISK_1: ORA-19870: error while restoring backup piece /u01/backup/d6_B_1arjqk9v_1_1
ORA-19505: failed to identify file "/u01/backup/d6_B_1arjqk9v_1_1"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
failover to previous backup
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 00006 to /mnt/ramdisk/book/sugar01.dbf
channel ORA_DISK_1: reading from backup piece /u01/backup/d6_A_15rjqgkp_1_1
channel ORA_DISK_1: piece handle=/u01/backup/d6_A_15rjqgkp_1_1 tag=TAG20161101T094705
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 2016-11-01 10:52:29
$ ls -l /mnt/ramdisk/book/sugar01.dbf
-rw-r----- 1 oracle oinstall 41951232 2016-11-01 10:52:28 /mnt/ramdisk/book/sugar01.dbf
RMAN> recover datafile 6 ;
Starting recover at 2016-11-01 10:53:06
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 2016-11-01 10:53:06
SCOTT@book> alter database datafile 6 online ;
Database altered.
$ ls -l /mnt/ramdisk/book/sugar01.dbf
-rw-r----- 1 oracle oinstall 9445376 2016-11-01 10:53:26 /mnt/ramdisk/book/sugar01.dbf
SCOTT@book> select rowid from t3 where rownum=1;
select rowid from t3 where rownum=1
*
ERROR at line 1:
ORA-00942: table or view does not exist
SCOTT@book> select rowid from t2 where rownum=1;
select rowid from t2 where rownum=1
*
ERROR at line 1:
ORA-00942: table or view does not exist
--看來不能在備份期間做這種操作!!!
總結:
1.測試有一些亂,包括思路都有點亂。
2.我僅僅猜測備份時,檔案大小就已經確定,最多40M。而具體讀取那些塊,我估計已經透過點陣圖確定下來。
你可以看到即使我在備份drop表,後發了alter system checkpoint命令,T2,T3表的資訊依舊備份。
3.而且在備份操作時提示ORA-19567: cannot shrink file /mnt/ramdisk/book/sugar01.dbf because it is being backed up or copied
看來無論何種情況下,在備份期間做資料庫維護方面的工作。
4.補充在沒有shrink到9M前,我自己還做了1次備份(昨天做的):
RMAN> CONFIGURE CHANNEL 1 DEVICE TYPE DISK clear;
old RMAN configuration parameters:
CONFIGURE CHANNEL 1 DEVICE TYPE DISK RATE 128 K;
old RMAN configuration parameters are successfully deleted
released channel: ORA_DISK_1
RMAN> backup datafile 6 format '/u01/backup/d6_3_%U' ;
Starting backup at 2016-10-31 17:41:35
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=68 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=00006 name=/mnt/ramdisk/book/sugar01.dbf
channel ORA_DISK_1: starting piece 1 at 2016-10-31 17:41:35
channel ORA_DISK_1: finished piece 1 at 2016-10-31 17:41:36
piece handle=/u01/backup/d6_3_0urjoo2f_1_1 tag=TAG20161031T174135 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2016-10-31 17:41:36
$ ls -l /u01/backup/d6_3_0urjoo2f_1_1
-rw-r----- 1 oracle oinstall 21897216 2016-10-31 17:41:35 /u01/backup/d6_3_0urjoo2f_1_1
--???奇怪了。大小很奇怪把,我已經drop purge表 T2,T3,備份不應該有這個大。
--實際上這個時候已經存在問題了。
--看來不能在備份期間做這種操作!!!
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-2127396/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- [20161031]rman備份與資料檔案變化2.txt
- [20161031]rman備份與資料檔案OS塊.txt
- RMAN說,我能備份(4)--RMAN備份資料檔案和控制檔案
- [20161101]rman備份與資料檔案變化4.txt
- [20161102]rman備份與資料檔案變化5.txt
- [20171123]rman備份與資料檔案變化6.txt
- [20161101]rman備份與資料檔案變化7.txt
- RMAN備份資料檔案+控制檔案+歸檔日誌
- rman備份-(1) 利用備份級恢復資料檔案和控制檔案
- rman備份但丟失一個資料檔案,但有歸檔備份
- rman備份恢復-rman恢復資料檔案測試
- RMAN說,我能備份(5)--RMAN備份歸檔檔案
- RMAN備份檔案格式
- 非歸檔資料庫RMAN備份資料庫
- rman恢復資料庫--用備份的控制檔案資料庫
- rman備份檔案的格式
- Backup And Recovery User's Guide-備份資料庫-使用RMAN備份資料庫檔案GUIIDE資料庫
- rman全庫備份備份歸檔日誌檔案
- Backup And Recovery User's Guide-備份資料庫-使用RMAN備份控制檔案GUIIDE資料庫
- RMAN備份恢復典型案例——資料檔案存在壞快
- 非系統資料檔案損壞,rman備份恢復
- RMAN 驗證 資料檔案 和 備份 的有效性
- [20121127]rman備份資料檔案大小與truncate.txt
- Oracle RMAN 不完全恢復(只有資料檔案備份,丟失歸檔日誌備份)Oracle
- RMAN備份檔案遠大於資料庫大小的原因分析資料庫
- Backup And Recovery User's Guide-備份資料庫-使用RMAN備份表空間和資料檔案GUIIDE資料庫
- 基於oracle 11.2.0.4 rman data file copy以及增量scn備份資料檔案持續變化系列之一Oracle
- 基於oracle 11.2.0.4 rman data file copy以及增量scn備份資料檔案持續變化系列之二Oracle
- 用rman建立dataguard備用資料庫繼續(無法找到備份檔案)資料庫
- ORACLE 只讀資料檔案備份與恢復Oracle
- 使用RMAN備份資料庫資料庫
- rman資料庫全庫備份與恢復資料庫
- Oracle資料庫備份與恢復之RMANOracle資料庫
- 【備份恢復】歸檔模式下丟失系統關鍵資料檔案 利用RMAN備份恢復模式
- 【備份】RMAN中對控制檔案的幾種備份方法
- rman恢復--歸檔模式有備份,丟失資料檔案的恢復模式
- rman恢復--歸檔模式無備份,丟失資料檔案的恢復模式
- rman在歸檔與非歸檔時備份資料庫的簡單示例資料庫