[20160524]rman备份与检查点4.txt
--链接:
http://blog.itpub.net/267265/viewspace-2105221/
http://blog.itpub.net/267265/viewspace-2105223/
--昨晚仔细思考,重复测试看看,使用新的控制文件是否可以恢复.感觉我的问题在于我做了catalog注册了备份文件时丢失某些信息.重新
--测试看看.
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
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET;
RMAN> configure channel 1 device type disk rate 50M;
RMAN> backup database format '/home/oracle/backup/full0523_%U.bak' filesperset=1;
....
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=/mnt/ramdisk/book/system01.dbf
channel ORA_DISK_1: starting piece 1 at 2016-05-24 07:58:04
channel ORA_DISK_1: finished piece 1 at 2016-05-24 07:58:29
piece handle=/home/oracle/backup/full0523_2jr6ch4c_1_1.bak tag=TAG20160524T075644 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
channel ORA_DISK_1: throttle time: 0:00:13
...
SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name FROM v$datafile_header order by 2;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- -------------------------------- ---------------
2 13237761004 2016-05-24 07:56:44 1834 13227286650 ONLINE 1289 YES /mnt/ramdisk/book/sysaux01.dbf SYSAUX
3 13237761020 2016-05-24 07:57:29 923328 13227286650 ONLINE 1209 YES /mnt/ramdisk/book/undotbs01.dbf UNDOTBS1
1 13237761036 2016-05-24 07:58:04 7 13227286650 ONLINE 1298 YES /mnt/ramdisk/book/system01.dbf SYSTEM
4 13237761046 2016-05-24 07:58:30 16143 13227286650 ONLINE 1295 YES /mnt/ramdisk/book/users01.dbf USERS
5 13237761052 2016-05-24 07:58:45 952916 13227286650 ONLINE 1206 YES /mnt/ramdisk/book/example01.dbf EXAMPLE
6 13237761058 2016-05-24 07:59:00 1314508 13227286650 ONLINE 1222 YES /mnt/ramdisk/book/sugar01.dbf SUGAR
7 13237761060 2016-05-24 07:59:03 13237575534 13227286650 ONLINE 43 YES /mnt/ramdisk/book/tea01.dbf TEA
7 rows selected.
--可以发现file#=1的 CHECKPOINT_CHANGE#=13237761036.时间2016-05-24 07:58:04,说明在备份前先写检查点,再备份数据文件的.在前面的例子已经说明.
RMAN> list backup by file;
List of Datafile Backups
========================
File Key TY LV S Ckp SCN Ckp Time #Pieces #Copies Compressed Tag
---- ------- - -- - ---------- ------------------- ------- ------- ---------- ---
1 58 B F A 13237761036 2016-05-24 07:58:04 1 1 NO TAG20160524T075644
2 56 B F A 13237761004 2016-05-24 07:56:44 1 1 NO TAG20160524T075644
3 57 B F A 13237761020 2016-05-24 07:57:29 1 1 NO TAG20160524T075644
4 59 B F A 13237761046 2016-05-24 07:58:30 1 1 NO TAG20160524T075644
5 60 B F A 13237761052 2016-05-24 07:58:45 1 1 NO TAG20160524T075644
6 61 B F A 13237761058 2016-05-24 07:59:00 1 1 NO TAG20160524T075644
7 62 B F A 13237761060 2016-05-24 07:59:03 1 1 NO TAG20160524T075644
List of Control File Backups
============================
CF Ckp SCN Ckp Time BS Key S #Pieces #Copies Compressed Tag
---------- ------------------- ------- - ------- ------- ---------- ---
13237761067 2016-05-24 07:59:06 63 A 1 1 NO TAG20160524T075906
List of SPFILE Backups
======================
Modification Time BS Key S #Pieces #Copies Compressed Tag
------------------- ------- - ------- ------- ---------- ---
2016-05-23 11:41:20 63 A 1 1 NO TAG20160524T075906
2.为做测试例子做准备:
--参看链接http://blog.itpub.net/267265/viewspace-1787037/
--参考前面的例子:忽略.
3.开始备份:
--session 1:
SCOTT@book> host sleep 30
RMAN> configure channel 1 device type disk rate 5M;
old RMAN configuration parameters:
CONFIGURE CHANNEL 1 DEVICE TYPE DISK RATE 50 M;
new RMAN configuration parameters:
CONFIGURE CHANNEL 1 DEVICE TYPE DISK RATE 5 M;
new RMAN configuration parameters are successfully stored
released channel: ORA_DISK_1
RMAN> backup datafile 4 format '/home/oracle/backup/users0524_%U.bak' ;
--session 1:
insert into DEMO values (3,dbms_flashback.get_system_change_number,userenv('commitscn'));
host sleep 9
commit ;
alter system checkpoint ;
SCOTT@book> select ora_rowscn,rowid,demo.* from demo;
ORA_ROWSCN ROWID ID UPDATE_SCN COMMIT_SCN
------------ ------------------ ------------ ------------ ------------
13237763272 AAAW6oAAEAAAT/0AAA 3 13237763268 13237763271
13237759253 AAAW6oAAEAAAT/2AAA 1 13237759024 13237759030
13237759253 AAAW6oAAEAAAT/2AAB 2 13237759251 13237759252
SCOTT@book> @ &r/rowid AAAW6oAAEAAAT/0AAA
OBJECT FILE BLOCK ROW DBA TEXT
------------ ------------ ------------ ------------ -------------------- ----------------------------------------
93864 4 81908 0 4,81908 alter system dump datafile 4 block 81908
--还没有备份完成,等待rman备份完成.....
RMAN> backup datafile 4 format '/home/oracle/backup/users0524_%U.bak' ;
Starting backup at 2016-05-24 10:07:54
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00004 name=/mnt/ramdisk/book/users01.dbf
channel ORA_DISK_1: starting piece 1 at 2016-05-24 10:07:54
channel ORA_DISK_1: finished piece 1 at 2016-05-24 10:10:19
piece handle=/home/oracle/backup/users0524_2tr6conq_1_1.bak tag=TAG20160524T100754 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:25
channel ORA_DISK_1: throttle time: 0:02:17
Finished backup at 2016-05-24 10:10:19
Starting Control File and SPFILE Autobackup at 2016-05-24 10:10:19
piece handle=/u01/app/oracle/fast_recovery_area/BOOK/autobackup/2016_05_24/o1_mf_s_912679819_cn7g0cxd_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2016-05-24 10:10:20
--需要2*60+25=145秒.
RMAN> list backup by file;
List of Datafile Backups
========================
File Key TY LV S Ckp SCN Ckp Time #Pieces #Copies Compressed Tag
---- ------- - -- - ---------- ------------------- ------- ------- ---------- ---
1 58 B F A 13237761036 2016-05-24 07:58:04 1 1 NO TAG20160524T075644
2 56 B F A 13237761004 2016-05-24 07:56:44 1 1 NO TAG20160524T075644
3 57 B F A 13237761020 2016-05-24 07:57:29 1 1 NO TAG20160524T075644
4 68 B F A 13237763259 2016-05-24 10:07:54 1 1 NO TAG20160524T100754
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
59 B F A 13237761046 2016-05-24 07:58:30 1 1 NO TAG20160524T075644
5 60 B F A 13237761052 2016-05-24 07:58:45 1 1 NO TAG20160524T075644
6 61 B F A 13237761058 2016-05-24 07:59:00 1 1 NO TAG20160524T075644
7 62 B F A 13237761060 2016-05-24 07:59:03 1 1 NO TAG20160524T075644
List of Control File Backups
============================
CF Ckp SCN Ckp Time BS Key S #Pieces #Copies Compressed Tag
---------- ------------------- ------- - ------- ------- ---------- ---
13237763329 2016-05-24 10:10:19 69 A 1 1 NO TAG20160524T101019
13237761067 2016-05-24 07:59:06 63 A 1 1 NO TAG20160524T075906
List of SPFILE Backups
======================
Modification Time BS Key S #Pieces #Copies Compressed Tag
------------------- ------- - ------- ------- ---------- ---
2016-05-24 08:22:42 69 A 1 1 NO TAG20160524T101019
2016-05-23 11:41:20 63 A 1 1 NO TAG20160524T075906
--注意第2次备份数据文件4,SCN=13237763259,而插入demo第3条记录的提交scn=13237763271.
--这样加入我们恢复到SCN=13237763260 ,oracle会如何做呢?
4.开始恢复操作:
SCOTT@book> alter system archive log current ;
System altered.
SCOTT@book> alter system archive log current ;
System altered.
SCOTT@book> alter system archive log current ;
System altered.
-- 实际上备份时最大的scn是记录在控制文件里面的.
SCOTT@book> select file#,CHECKPOINT_CHANGE#,ABSOLUTE_FUZZY_CHANGE# from v$backup_datafile;
FILE# CHECKPOINT_CHANGE# ABSOLUTE_FUZZY_CHANGE#
------------ ------------------ ----------------------
2 13237761004 0
3 13237761020 0
1 13237761036 0
4 13237761046 0
5 13237761052 0
6 13237761058 0
7 13237761060 0
0 13237761067 0
4 13237763259 13237763273
~~~~~~~~~~~~
0 13237763329 0
10 rows selected.
--如果我catalog会出现什么情况呢? 注意~部分内容,ABSOLUTE_FUZZY_CHANGE#=13237763273.
$ ls -l /home/oracle/backup/users0524_2tr6conq_1_1.bak
-rw-r----- 1 oracle oinstall 677715968 2016-05-24 10:10:14 /home/oracle/backup/users0524_2tr6conq_1_1.bak
$ mv /home/oracle/backup/users0524_2tr6conq_1_1.bak /home/oracle/backup/users0524_2tr6conq_1_1.bakxxx
RMAN> CROSSCHECK backup;
....//省略...
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/home/oracle/backup/users0524_2tr6conq_1_1.bak RECID=68 STAMP=912679674
Crosschecked 1 objects
RMAN> delete expired backupset ;
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
68 68 1 1 EXPIRED DISK /home/oracle/backup/users0524_2tr6conq_1_1.bak
Do you really want to delete the above objects (enter YES or NO)? yes
deleted backup piece
backup piece handle=/home/oracle/backup/users0524_2tr6conq_1_1.bak RECID=68 STAMP=912679674
Deleted 1 EXPIRED objects
SCOTT@book> select recid,file#,CHECKPOINT_CHANGE#,ABSOLUTE_FUZZY_CHANGE# from v$backup_datafile;
RECID FILE# CHECKPOINT_CHANGE# ABSOLUTE_FUZZY_CHANGE#
------------ ----- ------------------ ----------------------
71 2 13237761004 0
72 3 13237761020 0
73 1 13237761036 0
74 4 13237761046 0
75 5 13237761052 0
76 6 13237761058 0
77 7 13237761060 0
78 0 13237761067 0
84 0 13237763329 0
9 rows selected.
RMAN> catalog start with '/home/oracle/backup/users0524_2tr6conq_1_1.bakxxx';
using target database control file instead of recovery catalog
searching for all files that match the pattern /home/oracle/backup/users0524_2tr6conq_1_1.bakxxx
List of Files Unknown to the Database
=====================================
File Name: /home/oracle/backup/users0524_2tr6conq_1_1.bakxxx
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/users0524_2tr6conq_1_1.bakxxx
SCOTT@book> select recid,file#,CHECKPOINT_CHANGE#,ABSOLUTE_FUZZY_CHANGE# from v$backup_datafile;
RECID FILE# CHECKPOINT_CHANGE# ABSOLUTE_FUZZY_CHANGE#
------------ ----- ------------------ ----------------------
71 2 13237761004 0
72 3 13237761020 0
73 1 13237761036 0
74 4 13237761046 0
75 5 13237761052 0
76 6 13237761058 0
77 7 13237761060 0
78 0 13237761067 0
83 4 13237763259 13237763273
84 0 13237763329 0
10 rows selected.
--catelog后没有问题啊!ABSOLUTE_FUZZY_CHANGE#也加入了.继续做恢复测试:
$ /bin/cp * /home/oracle/book_good/
$ rm -rf /mnt/ramdisk/book/* */
RMAN> startup nomount
Oracle instance started
Total System Global Area 634732544 bytes
Fixed Size 2255792 bytes
Variable Size 197133392 bytes
Database Buffers 427819008 bytes
Redo Buffers 7524352 bytes
RMAN> configure channel 1 device type disk clear;
RMAN> restore controlfile FROM AUTOBACKUP;
Starting restore at 2016-05-24 10:35:44
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=254 device type=DISK
recovery area destination: /u01/app/oracle/fast_recovery_area
database name (or database unique name) used for search: BOOK
channel ORA_DISK_1: AUTOBACKUP /u01/app/oracle/fast_recovery_area/BOOK/autobackup/2016_05_24/o1_mf_s_912679819_cn7g0cxd_.bkp found in the recovery area
AUTOBACKUP search with format "%F" not attempted because DBID was not set
channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/app/oracle/fast_recovery_area/BOOK/autobackup/2016_05_24/o1_mf_s_912679819_cn7g0cxd_.bkp
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/mnt/ramdisk/book/control01.ctl
output file name=/mnt/ramdisk/book/control02.ctl
Finished restore at 2016-05-24 10:35:46
$ ls -l /u01/app/oracle/fast_recovery_area/BOOK/autobackup/2016_05_24/
total 20000
-rw-r----- 1 oracle oinstall 10223616 2016-05-24 07:59:06 o1_mf_s_912671946_cn76bbm6_.bkp
-rw-r----- 1 oracle oinstall 10223616 2016-05-24 10:10:19 o1_mf_s_912679819_cn7g0cxd_.bkp
--可以发现使用最新的控制文件备份.
RMAN> alter database mount ;
database mounted
released channel: ORA_DISK_1
run {
set until scn 13237763260;
restore database ;
recover database ;
}
channel ORA_DISK_3: starting datafile backup set restore
channel ORA_DISK_3: specifying datafile(s) to restore from backup set
channel ORA_DISK_3: restoring datafile 00004 to /mnt/ramdisk/book/users01.dbf
channel ORA_DISK_3: reading from backup piece /home/oracle/backup/full0523_2kr6ch56_1_1.bak
channel ORA_DISK_1: piece handle=/home/oracle/backup/full0523_2hr6ch1s_1_1.bak tag=TAG20160524T075644
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
--可以发现并不用/home/oracle/backup/users0524_2tr6conq_1_1.bakxxx.
run {
set until scn 13237763272;
restore database ;
recover database ;
}
--情况同上.不用/home/oracle/backup/users0524_2tr6conq_1_1.bakxxx.
SYS@book> alter database open read only ;
Database altered.
SYS@book> select ora_rowscn,rowid,demo.* from scott.demo;
ORA_ROWSCN ROWID ID UPDATE_SCN COMMIT_SCN
------------ ------------------ ------------ ------------ ------------
13237759253 AAAW6oAAEAAAT/2AAA 1 13237759024 13237759030
13237759253 AAAW6oAAEAAAT/2AAB 2 13237759251 13237759252
-- 看不到插入ID=3的信息,也就是我以前提到的set until scn 13237763272;实际上仅仅恢复到13237763272-1.你使用bbed是可以观察到的.
BBED> set dba 4,81908
DBA 0x01013ff4 (16859124 4,81908)
BBED> x /rnnn rowdata
rowdata[0] @8122
----------
flag@8122: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8123: 0x01
cols@8124: 3
col 0[2] @8125: 3
col 1[7] @8128: 13237763268
col 2[7] @8136: 13237763271
--记录已经存在,但是没有提交.
--如果你检查x$ktuxe内部视图.
SYS@book> select * from x$ktuxe where KTUXESTA<>'INACTIVE';
ADDR INDX INST_ID KTUXEUSN KTUXESLT KTUXESQN KTUXERDBF KTUXERDBB KTUXESCNB KTUXESCNW KTUXESTA KTUXECFL KTUXEUEL KTUXEDDBF KTUXEDDBB KTUXEPUSN KTUXEPSLT KTUXEPSQN KTUXESIZ
---------------- ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ -------- -------- -------- ------------ ------------ ------------ ------------ ------------ ------------
00007FAC63095628 401 1 9 31 12874 3 1156 352861380 3 ACTIVE NONE 3 0 0 0 0 0 1
--可以发现存在一个ACTIVE事务.也就是读取时要使用undo段.通过bbed观察也可以确定:
BBED> set dba 4,81908
DBA 0x01013ff4 (16859124 4,81908)
BBED> p /d ktbbh.ktbbhitl[0]
struct ktbbhitl[0], 24 bytes @44
struct ktbitxid, 8 bytes @44
ub2 kxidusn @44 9
ub2 kxidslt @46 31
ub4 kxidsqn @48 12874
struct ktbituba, 8 bytes @52
ub4 kubadba @52 12584068
ub2 kubaseq @56 1652
ub1 kubarec @58 13
ub2 ktbitflg @60 1 (NONE)
union _ktbitun, 2 bytes @62
sb2 _ktbitfsc @62 0
ub2 _ktbitwrp @62 0
ub4 ktbitbas @64 0
SYS@book> @ &r/dfb10 12584068
old 2: dbms_utility.data_block_address_file(&1) rfile#,
new 2: dbms_utility.data_block_address_file(12584068) rfile#,
old 3: dbms_utility.data_block_address_block(&&1) block#
new 3: dbms_utility.data_block_address_block(12584068) block#
RFILE# BLOCK#
------------ ------------
3 1156
old 1: select 'alter system dump datafile '||dbms_utility.data_block_address_file(&1)||' block '||
new 1: select 'alter system dump datafile '||dbms_utility.data_block_address_file(12584068)||' block '||
old 2: dbms_utility.data_block_address_block(&&1) ||' ;' text
new 2: dbms_utility.data_block_address_block(12584068) ||' ;' text
TEXT
------------------------------------------------------------
alter system dump datafile 3 block 1156 ;
--可以发现这些信息是一致的.
--关闭数据库.继续恢复看看.
run {
set until scn 13237763273;
restore database ;
recover database ;
}
channel ORA_DISK_3: starting datafile backup set restore
channel ORA_DISK_3: specifying datafile(s) to restore from backup set
channel ORA_DISK_3: restoring datafile 00004 to /mnt/ramdisk/book/users01.dbf
channel ORA_DISK_3: reading from backup piece /home/oracle/backup/users0524_2tr6conq_1_1.bak
channel ORA_DISK_1: piece handle=/home/oracle/backup/full0523_2mr6ch64_1_1.bak tag=TAG20160524T075644
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
--可以发现并用/home/oracle/backup/users0524_2tr6conq_1_1.bak.
--补充1点我的测试很奇怪,在使用catalog start with '/home/oracle/backup/users0524_2tr6conq_1_1.bakxxx'并不用,我从头做一次是使用的.
SYS@book> alter database open read only ;
Database altered.
SYS@book> select * from x$ktuxe where KTUXESTA<>'INACTIVE';
no rows selected
SYS@book> select ora_rowscn,rowid,demo.* from scott.demo;
ORA_ROWSCN ROWID ID UPDATE_SCN COMMIT_SCN
------------ ------------------ ------------ ------------ ------------
13237763272 AAAW6oAAEAAAT/0AAA 3 13237763268 13237763271
13237759253 AAAW6oAAEAAAT/2AAA 1 13237759024 13237759030
13237759253 AAAW6oAAEAAAT/2AAB 2 13237759251 13237759252
--也就是可以恢复了.
--我的感觉与备份时控制文件中记录的信息有关,可以参考前面的视图select file#,CHECKPOINT_CHANGE#,ABSOLUTE_FUZZY_CHANGE# from v$backup_datafile;有关.
5.使用第一次备份的控制文件看看:
--删除操作略.
RMAN> startup nomount
Oracle instance started
Total System Global Area 634732544 bytes
Fixed Size 2255792 bytes
Variable Size 197133392 bytes
Database Buffers 427819008 bytes
Redo Buffers 7524352 bytes
$ ls -l /u01/app/oracle/fast_recovery_area/BOOK/autobackup/2016_05_24/
total 20000
-rw-r----- 1 oracle oinstall 10223616 2016-05-24 07:59:06 o1_mf_s_912671946_cn76bbm6_.bkp
-rw-r----- 1 oracle oinstall 10223616 2016-05-24 10:10:19 o1_mf_s_912679819_cn7g0cxd_.bkp
RMAN> restore controlfile FROM '/u01/app/oracle/fast_recovery_area/BOOK/autobackup/2016_05_24/o1_mf_s_912671946_cn76bbm6_.bkp';
Starting restore at 2016-05-24 15:24:23
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=254 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 2016-05-24 15:24:25
RMAN> alter database mount ;
database mounted
released channel: ORA_DISK_1
SYS@book> select file#,CHECKPOINT_CHANGE#,ABSOLUTE_FUZZY_CHANGE# from v$backup_datafile;
FILE# CHECKPOINT_CHANGE# ABSOLUTE_FUZZY_CHANGE#
------------ ------------------ ----------------------
2 13237761004 0
3 13237761020 0
1 13237761036 0
4 13237761046 0
5 13237761052 0
6 13237761058 0
7 13237761060 0
7 rows selected.
--catalog看看:
RMAN> catalog start with '/home/oracle/backup/users0524_2tr6conq_1_1.bak';
Starting implicit crosscheck backup at 2016-05-24 15:25:43
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=12 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=24 device type=DISK
Crosschecked 7 objects
Finished implicit crosscheck backup at 2016-05-24 15:25:44
Starting implicit crosscheck copy at 2016-05-24 15:25:44
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
Finished implicit crosscheck copy at 2016-05-24 15:25:44
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/app/oracle/fast_recovery_area/BOOK/autobackup/2016_05_24/o1_mf_s_912679819_cn7g0cxd_.bkp
File Name: /u01/app/oracle/fast_recovery_area/BOOK/autobackup/2016_05_24/o1_mf_s_912671946_cn76bbm6_.bkp
searching for all files that match the pattern /home/oracle/backup/users0524_2tr6conq_1_1.bak
List of Files Unknown to the Database
=====================================
File Name: /home/oracle/backup/users0524_2tr6conq_1_1.bak
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/users0524_2tr6conq_1_1.bak
SYS@book> select file#,CHECKPOINT_CHANGE#,ABSOLUTE_FUZZY_CHANGE# from v$backup_datafile;
FILE# CHECKPOINT_CHANGE# ABSOLUTE_FUZZY_CHANGE#
----- ------------------ ----------------------
2 13237761004 0
3 13237761020 0
1 13237761036 0
4 13237761046 0
5 13237761052 0
6 13237761058 0
7 13237761060 0
4 13237763259 0
8 rows selected.
--可以发现问题在哪里了吗?这样catalog的问题ABSOLUTE_FUZZY_CHANGE#是0,这样问题就来了.
run {
set until scn 13237763272;
restore database ;
recover database ;
}
.....//省略
channel ORA_DISK_3: starting datafile backup set restore
channel ORA_DISK_3: specifying datafile(s) to restore from backup set
channel ORA_DISK_3: restoring datafile 00004 to /mnt/ramdisk/book/users01.dbf
channel ORA_DISK_3: reading from backup piece /home/oracle/backup/users0524_2tr6conq_1_1.bak
channel ORA_DISK_1: piece handle=/home/oracle/backup/full0523_2mr6ch64_1_1.bak tag=TAG20160524T075644
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:00
...//省略
starting media recovery
archived log file name=/u01/app/oracle/archivelog/book/1_204_907434361.dbf thread=1 sequence=204
archived log file name=/u01/app/oracle/archivelog/book/1_205_907434361.dbf thread=1 sequence=205
archived log file name=/u01/app/oracle/archivelog/book/1_206_907434361.dbf thread=1 sequence=206
archived log file name=/u01/app/oracle/archivelog/book/1_207_907434361.dbf thread=1 sequence=207
Oracle Error:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 4 needs more recovery to be consistent
ORA-01110: data file 4: '/mnt/ramdisk/book/users01.dbf'
media recovery complete, elapsed time: 00:00:00
Finished recover at 2016-05-24 15:28:37
--这样问题就出现了,实际上就是如果前面的测试catalog是在open状态下进行,重新注册时估计oracle还是能确定ABSOLUTE_FUZZY_CHANGE#,
--而当使用旧控制文件再catalog时,oracle仅仅定位记录CHECKPOINT_CHANGE#,而要确定ABSOLUTE_FUZZY_CHANGE#要扫描这个备份代价有
--点大,oracle索性不做.使用0代替.
--当我们使用使用restore时oracle是知道数据块里面那个scn最高的,这样它要知道这个,要取出的整个数据文件是否确定.
RMAN> validate datafile 4;
Starting validate at 2016-05-24 15:33:04
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00004 name=/mnt/ramdisk/book/users01.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
---- ------ -------------- ------------ --------------- ----------
4 OK 0 268 89628 13237763272
File Name: /mnt/ramdisk/book/users01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 81871
Index 0 45
Other 0 7416
Finished validate at 2016-05-24 15:33:05
--如果恢复到13237763273,就没有这个问题.
run {
set until scn 13237763273;
restore database ;
recover database ;
}
....//结果就不贴出来了.
SYS@book> alter database open read only ;
Database altered.
SYS@book> select * from x$ktuxe where KTUXESTA<>'INACTIVE';
no rows selected
SYS@book> select ora_rowscn,rowid,demo.* from scott.demo;
ORA_ROWSCN ROWID ID UPDATE_SCN COMMIT_SCN
------------ ------------------ ------------ ------------ ------------
13237763272 AAAW6oAAEAAAT/0AAA 3 13237763268 13237763271
13237759253 AAAW6oAAEAAAT/2AAA 1 13237759024 13237759030
13237759253 AAAW6oAAEAAAT/2AAB 2 13237759251 13237759252
--到这里终于明白为什么就是因为catalog 备份集时无法在控制文件中记录数据文件的high scn(已经不存在的情况下),对应视图
--v$backup_datafile的ABSOLUTE_FUZZY_CHANGE#,这样才执行不完全恢复时,oracle的判断仅仅是恢复的scn大于文件头的scn.这样无法知
--道数据文件里面有大于恢复的scn号,这样无法open resetlogs,出现报错.
--还可以通过如下例子证明我的判断是对的.从冷备份拷贝回来.
SYS@book> startup open read only ;
ORACLE instance started.
Total System Global Area 634732544 bytes
Fixed Size 2255792 bytes
Variable Size 197133392 bytes
Database Buffers 427819008 bytes
Redo Buffers 7524352 bytes
Database mounted.
Database opened.
SYS@book> select file#,CHECKPOINT_CHANGE#,ABSOLUTE_FUZZY_CHANGE# from v$backup_datafile;
FILE# CHECKPOINT_CHANGE# ABSOLUTE_FUZZY_CHANGE#
------------ ------------------ ----------------------
2 13237761004 0
3 13237761020 0
1 13237761036 0
4 13237761046 0
5 13237761052 0
6 13237761058 0
7 13237761060 0
0 13237761067 0
4 13237763259 13237763273
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
0 13237763329 0
10 rows selected.
$ mv users0524_2tr6conq_1_1.bak users0524_2tr6conq_1_1.bakx
RMAN> CROSSCHECK backup;
RMAN> delete expired backupset ;
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
70 70 1 1 EXPIRED DISK /home/oracle/backup/users0524_2tr6conq_1_1.bak
Do you really want to delete the above objects (enter YES or NO)? YES
deleted backup piece
backup piece handle=/home/oracle/backup/users0524_2tr6conq_1_1.bak RECID=70 STAMP=912680645
Deleted 1 EXPIRED objects
RMAN> catalog start with '/home/oracle/backup/users0524_2tr6conq_1_1.bakx';
searching for all files that match the pattern /home/oracle/backup/users0524_2tr6conq_1_1.bakx
List of Files Unknown to the Database
=====================================
File Name: /home/oracle/backup/users0524_2tr6conq_1_1.bakx
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/users0524_2tr6conq_1_1.bakx
SYS@book> select file#,CHECKPOINT_CHANGE#,ABSOLUTE_FUZZY_CHANGE# from v$backup_datafile;
FILE# CHECKPOINT_CHANGE# ABSOLUTE_FUZZY_CHANGE#
------------ ------------------ ----------------------
2 13237761004 0
3 13237761020 0
1 13237761036 0
4 13237761046 0
5 13237761052 0
6 13237761058 0
7 13237761060 0
0 13237761067 0
0 13237763329 0
9 rows selected.
--已经不存在了.
RMAN> catalog start with '/home/oracle/backup/users0524_2tr6conq_1_1.bakx';
searching for all files that match the pattern /home/oracle/backup/users0524_2tr6conq_1_1.bakx
List of Files Unknown to the Database
=====================================
File Name: /home/oracle/backup/users0524_2tr6conq_1_1.bakx
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/users0524_2tr6conq_1_1.bakx
SYS@book> select file#,CHECKPOINT_CHANGE#,ABSOLUTE_FUZZY_CHANGE# from v$backup_datafile;
FILE# CHECKPOINT_CHANGE# ABSOLUTE_FUZZY_CHANGE#
------------ ------------------ ----------------------
2 13237761004 0
3 13237761020 0
1 13237761036 0
4 13237761046 0
5 13237761052 0
6 13237761058 0
7 13237761060 0
0 13237761067 0
4 13237763259 13237763273
0 13237763329 0
10 rows selected.
--在open状态,原来有记录,可以正常恢复ABSOLUTE_FUZZY_CHANGE#.前面的测试也说明这一点.
--参考链接:[20121105]清除控制文件的信息.txt http://blog.itpub.net/267265/viewspace-748366/
SYS@book> select rownum -1 a, crs.* from v$controlfile_record_section crs;
A TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID
-- ----------------------------- ----------- ------------- ------------ ------------ ------------ ------------
0 DATABASE 316 1 1 0 0 0
1 CKPT PROGRESS 8180 11 0 0 0 0
2 REDO THREAD 256 8 1 0 0 0
3 REDO LOG 72 16 3 0 0 0
4 DATAFILE 520 100 8 0 0 47
5 FILENAME 524 2298 12 0 0 0
6 TABLESPACE 68 100 8 0 0 13
7 TEMPORARY FILENAME 56 100 1 0 0 3
8 RMAN CONFIGURATION 1108 50 4 0 0 23
9 LOG HISTORY 56 292 73 1 73 73
10 OFFLINE RANGE 200 163 6 1 6 6
11 ARCHIVED LOG 584 308 73 1 73 73
12 BACKUP SET 40 409 70 1 70 70
13 BACKUP PIECE 736 200 70 1 70 70
14 BACKUP DATAFILE 200 245 84 1 84 84
15 BACKUP REDOLOG 76 215 18 1 18 18
16 DATAFILE COPY 736 200 0 0 0 0
17 BACKUP CORRUPTION 44 371 0 0 0 0
18 COPY CORRUPTION 40 409 0 0 0 0
19 DELETED OBJECT 20 818 231 1 231 231
20 PROXY COPY 928 246 0 0 0 0
21 BACKUP SPFILE 124 131 17 1 17 17
22 DATABASE INCARNATION 56 292 1 1 1 1
23 FLASHBACK LOG 84 2048 0 0 0 0
24 RECOVERY DESTINATION 180 1 1 0 0 0
25 INSTANCE SPACE RESERVATION 28 1055 1 0 0 0
26 REMOVABLE RECOVERY FILES 32 1000 1 0 0 0
27 RMAN STATUS 116 282 199 1 199 199
28 THREAD INSTANCE NAME MAPPING 80 8 8 0 0 0
29 MTTR 100 8 1 0 0 0
30 DATAFILE HISTORY 568 57 0 0 0 0
31 STANDBY DATABASE MATRIX 400 31 31 0 0 0
32 GUARANTEED RESTORE POINT 212 2048 0 0 0 0
33 RESTORE POINT 212 2083 0 0 0 0
34 DATABASE BLOCK CORRUPTION 80 8384 0 0 0 0
35 ACM OPERATION 104 64 6 0 0 0
36 FOREIGN ARCHIVED LOG 604 1002 0 0 0 0
37 rows selected.
--TYPE='BACKUP DATAFILE',A=14.
SYS@book> execute dbms_backup_restore.resetcfilesection(14);
PL/SQL procedure successfully completed.
SYS@book> select file#,CHECKPOINT_CHANGE#,ABSOLUTE_FUZZY_CHANGE# from v$backup_datafile;
no rows selected
--数据文件备份信息已经清除.
RMAN> catalog start with '/home/oracle/backup/';
searching for all files that match the pattern /home/oracle/backup/
List of Files Unknown to the Database
=====================================
File Name: /home/oracle/backup/users0524_2tr6conq_1_1.bak
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/users0524_2tr6conq_1_1.bak
--奇怪,为什么全备份的文件不做catalog呢?
SYS@book> select file#,CHECKPOINT_CHANGE#,ABSOLUTE_FUZZY_CHANGE# from v$backup_datafile;
FILE# CHECKPOINT_CHANGE# ABSOLUTE_FUZZY_CHANGE#
------------ ------------------ ----------------------
4 13237763259 0
--可以发现做记录.但是这个操作不可能读取备份集来确定high scn,也就无法知道ABSOLUTE_FUZZY_CHANGE#.
RMAN> catalog start with '/home/oracle/backup/full';
searching for all files that match the pattern /home/oracle/backup/full
no files found to be unknown to the database
--全备份为什么不行呢?难道实际上并没有删除吗?改一下文件名看看.步骤忽略(0523换成0524)
RMAN> catalog start with '/home/oracle/backup/full';
searching for all files that match the pattern /home/oracle/backup/full
List of Files Unknown to the Database
=====================================
File Name: /home/oracle/backup/full0524_2ir6ch39_1_1.bak
File Name: /home/oracle/backup/full0524_2hr6ch1s_1_1.bak
File Name: /home/oracle/backup/full0524_2lr6ch5l_1_1.bak
File Name: /home/oracle/backup/full0524_2nr6ch67_1_1.bak
File Name: /home/oracle/backup/full0524_2jr6ch4c_1_1.bak
File Name: /home/oracle/backup/full0524_2mr6ch64_1_1.bak
File Name: /home/oracle/backup/full0524_2kr6ch56_1_1.bak
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/full0524_2ir6ch39_1_1.bak
File Name: /home/oracle/backup/full0524_2hr6ch1s_1_1.bak
File Name: /home/oracle/backup/full0524_2lr6ch5l_1_1.bak
File Name: /home/oracle/backup/full0524_2nr6ch67_1_1.bak
File Name: /home/oracle/backup/full0524_2jr6ch4c_1_1.bak
File Name: /home/oracle/backup/full0524_2mr6ch64_1_1.bak
File Name: /home/oracle/backup/full0524_2kr6ch56_1_1.bak
SYS@book> select file#,CHECKPOINT_CHANGE#,ABSOLUTE_FUZZY_CHANGE# from v$backup_datafile;
FILE# CHECKPOINT_CHANGE# ABSOLUTE_FUZZY_CHANGE#
------------ ------------------ ----------------------
4 13237763259 0
3 13237761020 0
2 13237761004 0
5 13237761052 0
7 13237761060 0
1 13237761036 0
6 13237761058 0
4 13237761046 0
8 rows selected.
--换一句话讲catalog丢失备份文件的一些信息,比如v$backup_datafile中记录的ABSOLUTE_FUZZY_CHANGE#(我的理解就是对应的high scn),.
