[20160523]rman备份与检查点3.txt
--从前面的测试可以发现:
--1.通过以上信息可以确定备份文件会写检查点.
--2.而且备份文件是先写检查点,再做备份.
--我这里就产生1个疑问,假设某个数据文件备份时间很长,这样数据文件的某个数据块的scn可能大于文件头的CHECKPOINT_CHANGE#.当我
--们恢复使用不完全恢复到某个时间点或者SCN,应该使用那个备份文件呢?做一个测试来说明问题.
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
SCOTT@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name FROM v$datafile_header order by CHECKPOINT_TIME ;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- -------------------------------- ----------------
2 13237754002 2016-05-23 08:56:50 1834 13227286650 ONLINE 1266 YES /mnt/ramdisk/book/sysaux01.dbf SYSAUX
3 13237754026 2016-05-23 08:57:35 923328 13227286650 ONLINE 1186 YES /mnt/ramdisk/book/undotbs01.dbf UNDOTBS1
1 13237754045 2016-05-23 08:58:10 7 13227286650 ONLINE 1275 YES /mnt/ramdisk/book/system01.dbf SYSTEM
4 13237754054 2016-05-23 08:58:35 16143 13227286650 ONLINE 1271 YES /mnt/ramdisk/book/users01.dbf USERS
5 13237754060 2016-05-23 08:58:50 952916 13227286650 ONLINE 1183 YES /mnt/ramdisk/book/example01.dbf EXAMPLE
6 13237754066 2016-05-23 08:59:05 1314508 13227286650 ONLINE 1199 YES /mnt/ramdisk/book/sugar01.dbf SUGAR
7 13237754068 2016-05-23 08:59:08 13237575534 13227286650 ONLINE 20 YES /mnt/ramdisk/book/tea01.dbf TEA
7 rows selected.
2.写满users表空间:
SCOTT@book> create table tx as select * from dba_objects ;
Table created.
SCOTT@book> insert into tx select * from tx ;
87029 rows created.
SCOTT@book> commit ;
Commit complete.
insert into tx select * from tx ;
/
/
/
/
/
commit;
--参看链接http://blog.itpub.net/267265/viewspace-1787037/
SCOTT@book> create table DEMO (id number, update_scn number, commit_scn number);
Table created.
SCOTT@book> insert into DEMO values (1,dbms_flashback.get_system_change_number,userenv('commitscn'));
1 row created.
SCOTT@book> commit ;
Commit complete.
SCOTT@book> select rowid,demo.* from demo;
ROWID ID UPDATE_SCN COMMIT_SCN
------------------ ------------ ------------ ------------
AAAW6oAAEAAAT/2AAA 1 13237759024 13237759030
SCOTT@book> @ &r/rowid AAAW6oAAEAAAT/2AAA
OBJECT FILE BLOCK ROW DBA TEXT
------------ ------------ ------------ ------------ -------------------- ----------------------------------------
93864 4 81910 0 4,81910 alter system dump datafile 4 block 81910
--这个步骤为后面的操作做准备.注意块号很靠后.
SCOTT@book> select 81910*8/1024 MB from dual ;
MB
------------
639.921875
SCOTT@book> select 640/5 from dual ;
640/5
------------
128
--大约128秒才备份到哪里.
3.设置rman参数,开始备份:
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
--这样可以减慢备份的速度.
--session 1:
SCOTT@book> host sleep 50
RMAN> backup datafile 4 format '/home/oracle/backup/users0523_%U.bak' ;
--切换会话session 1:
SCOTT@book> insert into DEMO values (2,dbms_flashback.get_system_change_number,userenv('commitscn'));
1 row created.
SCOTT@book> commit ;
Commit complete.
SCOTT@book> alter system checkpoint ;
System altered.
SCOTT@book> select rowid,demo.* from demo;
ROWID ID UPDATE_SCN COMMIT_SCN
------------------ ------------ ------------ ------------
AAAW6oAAEAAAT/2AAA 1 13237759024 13237759030
AAAW6oAAEAAAT/2AAB 2 13237759251 13237759252
$ du users0523_2fr6a38d_1_1.bak -m
536 users0523_2fr6a38d_1_1.bak
--还没有备份到,等待rman备份完成.
RMAN> backup datafile 4 format '/home/oracle/backup/users0523_%U.bak' ;
Starting backup at 2016-05-23 09:49:01
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=101 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=00004 name=/mnt/ramdisk/book/users01.dbf
channel ORA_DISK_1: starting piece 1 at 2016-05-23 09:49:01
channel ORA_DISK_1: finished piece 1 at 2016-05-23 09:51:26
piece handle=/home/oracle/backup/users0523_2fr6a38d_1_1.bak tag=TAG20160523T094901 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-23 09:51:26
Starting Control File and SPFILE Autobackup at 2016-05-23 09:51:26
piece handle=/u01/app/oracle/fast_recovery_area/BOOK/autobackup/2016_05_23/o1_mf_s_912592286_cn4rjz0r_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2016-05-23 09:51:27
--需要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 48 B F A 13237754045 2016-05-23 08:58:10 1 1 NO TAG20160523T085649
2 46 B F A 13237754002 2016-05-23 08:56:50 1 1 NO TAG20160523T085649
3 47 B F A 13237754026 2016-05-23 08:57:35 1 1 NO TAG20160523T085649
4 54 B F A 13237759229 2016-05-23 09:49:01 1 1 NO TAG20160523T094901
49 B F A 13237754054 2016-05-23 08:58:35 1 1 NO TAG20160523T085649
5 50 B F A 13237754060 2016-05-23 08:58:50 1 1 NO TAG20160523T085649
6 51 B F A 13237754066 2016-05-23 08:59:05 1 1 NO TAG20160523T085649
7 52 B F A 13237754068 2016-05-23 08:59:08 1 1 NO TAG20160523T085649
List of Control File Backups
============================
CF Ckp SCN Ckp Time BS Key S #Pieces #Copies Compressed Tag
---------- ------------------- ------- - ------- ------- ---------- ---
13237759293 2016-05-23 09:51:26 55 A 1 1 NO TAG20160523T095126
13237754075 2016-05-23 08:59:11 53 A 1 1 NO TAG20160523T085911
List of SPFILE Backups
======================
Modification Time BS Key S #Pieces #Copies Compressed Tag
------------------- ------- - ------- ------- ---------- ---
2016-05-17 09:12:21 55 A 1 1 NO TAG20160523T095126
2016-05-17 09:12:21 53 A 1 1 NO TAG20160523T085911
--注意第2次备份数据文件4,SCN=13237759229,而插入demo第2条记录的提交scn=13237759252.
--这样加入我们恢复到SCN=13237759230 ,oracle会如何做呢?
4.开始恢复操作:
SYS@book> alter system archive log current ;
System altered.
SYS@book> alter system archive log current ;
System altered.
SYS@book> alter system archive log current ;
System altered.
--这些恢复需要这些归档.
SYS@book> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@book> 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
$ mv /mnt/ramdisk/book /mnt/ramdisk/book_good
$ mkdir /mnt/ramdisk/book
RMAN> restore controlfile FROM '/u01/app/oracle/fast_recovery_area/BOOK/autobackup/2016_05_23/o1_mf_s_912589151_cn4ogzy2_.bkp';
Starting restore at 2016-05-23 10:22:21
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 2016-05-23 10:22:22
--说明应该使用第一次备份的控制文件.因为第2次备份的控制文件scn=13237759293.大于13237759230.
--但是要注意后面的数据文件4要catalog,不然控制文件不存在.
RMAN> alter database mount ;
database mounted
--注意要取消前面rate 5M的限制,不然很慢.
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 3 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 3 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters are successfully stored
RMAN> configure channel 1 device type disk clear;
old RMAN configuration parameters:
CONFIGURE CHANNEL 1 DEVICE TYPE DISK RATE 5 M;
old RMAN configuration parameters are successfully deleted
RMAN> catalog start with '/home/oracle/backup/users0523_2fr6a38d_1_1.bak';
Starting implicit crosscheck backup at 2016-05-23 10:27:18
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-23 10:27:19
Starting implicit crosscheck copy at 2016-05-23 10:27:19
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
Finished implicit crosscheck copy at 2016-05-23 10:27:19
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_23/o1_mf_s_912592286_cn4rjz0r_.bkp
File Name: /u01/app/oracle/fast_recovery_area/BOOK/autobackup/2016_05_23/o1_mf_s_912589151_cn4ogzy2_.bkp
searching for all files that match the pattern /home/oracle/backup/users0523_2fr6a38d_1_1.bak
List of Files Unknown to the Database
=====================================
File Name: /home/oracle/backup/users0523_2fr6a38d_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/users0523_2fr6a38d_1_1.bak
--必须catalog这个文件,不然根本不会使用/home/oracle/backup/users0523_2fr6a38d_1_1.bak.
RMAN> list backup by file;
List of Datafile Backups
========================
File Key TY LV S Ckp SCN Ckp Time #Pieces #Copies Compressed Tag
---- ------- - -- - ---------- ------------------- ------- ------- ---------- ---
1 48 B F A 13237754045 2016-05-23 08:58:10 1 1 NO TAG20160523T085649
2 46 B F A 13237754002 2016-05-23 08:56:50 1 1 NO TAG20160523T085649
3 47 B F A 13237754026 2016-05-23 08:57:35 1 1 NO TAG20160523T085649
4 53 B F A 13237759229 2016-05-23 09:49:01 1 1 NO TAG20160523T094901
49 B F A 13237754054 2016-05-23 08:58:35 1 1 NO TAG20160523T085649
5 50 B F A 13237754060 2016-05-23 08:58:50 1 1 NO TAG20160523T085649
6 51 B F A 13237754066 2016-05-23 08:59:05 1 1 NO TAG20160523T085649
7 52 B F A 13237754068 2016-05-23 08:59:08 1 1 NO TAG20160523T085649
--然后执行如下:
run {
set until scn 13237759230;
restore database ;
recover database ;
}
executing command: SET until clause
Starting restore at 2016-05-23 10:29:22
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
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 00002 to /mnt/ramdisk/book/sysaux01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/full0523_27r6a06i_1_1.bak
channel ORA_DISK_2: starting datafile backup set restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_DISK_2: restoring datafile 00003 to /mnt/ramdisk/book/undotbs01.dbf
channel ORA_DISK_2: reading from backup piece /home/oracle/backup/full0523_28r6a07v_1_1.bak
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 00001 to /mnt/ramdisk/book/system01.dbf
channel ORA_DISK_3: reading from backup piece /home/oracle/backup/full0523_29r6a092_1_1.bak
channel ORA_DISK_3: piece handle=/home/oracle/backup/full0523_29r6a092_1_1.bak tag=TAG20160523T085649
channel ORA_DISK_3: restored backup piece 1
channel ORA_DISK_3: restore complete, elapsed time: 00:00:01
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 00005 to /mnt/ramdisk/book/example01.dbf
channel ORA_DISK_3: reading from backup piece /home/oracle/backup/full0523_2br6a0aa_1_1.bak
channel ORA_DISK_1: piece handle=/home/oracle/backup/full0523_27r6a06i_1_1.bak tag=TAG20160523T085649
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
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 /home/oracle/backup/full0523_2cr6a0ap_1_1.bak
channel ORA_DISK_2: piece handle=/home/oracle/backup/full0523_28r6a07v_1_1.bak tag=TAG20160523T085649
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:00:02
channel ORA_DISK_2: starting datafile backup set restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_DISK_2: restoring datafile 00007 to /mnt/ramdisk/book/tea01.dbf
channel ORA_DISK_2: reading from backup piece /home/oracle/backup/full0523_2dr6a0as_1_1.bak
channel ORA_DISK_3: piece handle=/home/oracle/backup/full0523_2br6a0aa_1_1.bak tag=TAG20160523T085649
channel ORA_DISK_3: restored backup piece 1
channel ORA_DISK_3: restore complete, elapsed time: 00:00:01
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/users0523_2fr6a38d_1_1.bak
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
channel ORA_DISK_1: piece handle=/home/oracle/backup/full0523_2cr6a0ap_1_1.bak tag=TAG20160523T085649
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:00
channel ORA_DISK_2: piece handle=/home/oracle/backup/full0523_2dr6a0as_1_1.bak tag=TAG20160523T085649
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:00:00
channel ORA_DISK_3: piece handle=/home/oracle/backup/users0523_2fr6a38d_1_1.bak tag=TAG20160523T094901
channel ORA_DISK_3: restored backup piece 1
channel ORA_DISK_3: restore complete, elapsed time: 00:00:01
Finished restore at 2016-05-23 10:29:26
Starting recover at 2016-05-23 10:29:26
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
starting media recovery
archived log file name=/u01/app/oracle/archivelog/book/1_186_907434361.dbf thread=1 sequence=186
archived log file name=/u01/app/oracle/archivelog/book/1_187_907434361.dbf thread=1 sequence=187
archived log file name=/u01/app/oracle/archivelog/book/1_188_907434361.dbf thread=1 sequence=188
archived log file name=/u01/app/oracle/archivelog/book/1_189_907434361.dbf thread=1 sequence=189
archived log file name=/u01/app/oracle/archivelog/book/1_190_907434361.dbf thread=1 sequence=190
archived log file name=/u01/app/oracle/archivelog/book/1_191_907434361.dbf thread=1 sequence=191
archived log file name=/u01/app/oracle/archivelog/book/1_192_907434361.dbf thread=1 sequence=192
archived log file name=/u01/app/oracle/archivelog/book/1_193_907434361.dbf thread=1 sequence=193
archived log file name=/u01/app/oracle/archivelog/book/1_194_907434361.dbf thread=1 sequence=194
archived log file name=/u01/app/oracle/archivelog/book/1_195_907434361.dbf thread=1 sequence=195
archived log file name=/u01/app/oracle/archivelog/book/1_196_907434361.dbf thread=1 sequence=196
archived log file name=/u01/app/oracle/archivelog/book/1_197_907434361.dbf thread=1 sequence=197
archived log file name=/u01/app/oracle/archivelog/book/1_198_907434361.dbf thread=1 sequence=198
archived log file name=/u01/app/oracle/archivelog/book/1_199_907434361.dbf thread=1 sequence=199
archived log file name=/u01/app/oracle/archivelog/book/1_200_907434361.dbf thread=1 sequence=200
archived log file name=/u01/app/oracle/archivelog/book/1_201_907434361.dbf thread=1 sequence=201
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:04
Finished recover at 2016-05-23 10:29:31
--注意看~,使用文件是/home/oracle/backup/users0523_2fr6a38d_1_1.bak,但是最后的结果是不能OPEN RESETLOGS.
--也就是讲以备份记录的scn取出备份有可能出现问题.通过bbed观察数据文件4也能说明问题:
BBED> set dba 4,81910
DBA 0x01013ff6 (16859126 4,81910)
BBED> x /2rnnn rowdata
rowdata[0] @8144
----------
flag@8144: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8145: 0x02
cols@8146: 3
col 0[2] @8147: 2
col 1[7] @8150: 13237759251
col 2[7] @8158: 13237759252
rowdata[22] @8166
-----------
flag@8166: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8167: 0x01
cols@8168: 3
col 0[2] @8169: 1
col 1[7] @8172: 13237759024
col 2[7] @8180: 13237759030
-- 可以发现后面的记录还记载在相应的数据块中.dbv与rman的 validate datafile 4检查并不会报错.不过仔细观察:
RMAN> validate datafile 4;
Starting validate at 2016-05-23 10:50:59
using channel ORA_DISK_1
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 13237759253
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-23 10:51:00
--你可以发现最高的SCN是13237759253,换一句话讲要恢复最少要到13237759253.
-- 好了,继续恢复.先测试到SCN=13237759253看看.
run {
set until scn 13237759253;
restore database;
recover database ;
}
--问题依旧.
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:03
Finished recover at 2016-05-23 11:02:29
run {
set until scn 13237759254;
restore database;
recover database ;
}
--必须要比最高的SCN+1.
SYS@book> @ &r/db_status
SYS@book> set numw 12
SYS@book> column name format a50
SYS@book> column file# format 9999
SYS@book>
SYS@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 13237760064 2016-05-23 10:18:17 0 0 0 SYSTEM /mnt/ramdisk/book/system01.dbf
2 13237760064 2016-05-23 10:18:17 0 0 0 RECOVER /mnt/ramdisk/book/sysaux01.dbf
3 13237760064 2016-05-23 10:18:17 0 0 0 ONLINE /mnt/ramdisk/book/undotbs01.dbf
4 13237760064 2016-05-23 10:18:17 0 0 0 ONLINE /mnt/ramdisk/book/users01.dbf
5 13237760064 2016-05-23 10:18:17 0 0 0 ONLINE /mnt/ramdisk/book/example01.dbf
6 13237760064 2016-05-23 10:18:17 0 0 0 ONLINE /mnt/ramdisk/book/sugar01.dbf
7 13237760064 2016-05-23 10:18:17 0 13237575558 13237575672 ONLINE /mnt/ramdisk/book/tea01.dbf
7 rows selected.
SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name FROM v$datafile_header;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZZY NAME TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ---------- ---------------- ------ -------------------------------------------------- ------------------------------
1 13237759254 2016-05-23 09:49:54 7 13227286650 ONLINE 1275 NO /mnt/ramdisk/book/system01.dbf SYSTEM
2 13237759254 2016-05-23 09:49:54 1834 13227286650 ONLINE 1266 NO /mnt/ramdisk/book/sysaux01.dbf SYSAUX
3 13237759254 2016-05-23 09:49:54 923328 13227286650 ONLINE 1186 NO /mnt/ramdisk/book/undotbs01.dbf UNDOTBS1
4 13237759254 2016-05-23 09:49:54 16143 13227286650 ONLINE 1287 NO /mnt/ramdisk/book/users01.dbf USERS
5 13237759254 2016-05-23 09:49:54 952916 13227286650 ONLINE 1183 NO /mnt/ramdisk/book/example01.dbf EXAMPLE
6 13237759254 2016-05-23 09:49:54 1314508 13227286650 ONLINE 1199 NO /mnt/ramdisk/book/sugar01.dbf SUGAR
7 13237759254 2016-05-23 09:49:54 13237575534 13227286650 ONLINE 20 NO /mnt/ramdisk/book/tea01.dbf TEA
7 rows selected.
SYS@book> SELECT 'controlfile' "SCN location", 'SYSTEM checkpoint' NAME, checkpoint_change#,open_mode,current_scn FROM v$database;
SCN location NAME CHECKPOINT_CHANGE# OPEN_MODE CURRENT_SCN
---------------------- -------------------------------------------------- ------------------ ---------------------------------------- ------------
controlfile SYSTEM checkpoint 13237753660 MOUNTED 0
SYS@book> set echo off
NAME CONTROL_STAT DATAFILE_STATUS FILE# CONTROL_FILE_SCN DATAFILE_SCN FUZZY DATAFILE_STATUS SCN location SYSTEM_CHECKPOINT# OPEN_MODE
-------------------------------- ------------ --------------- ----- ---------------- ------------ ------ --------------- ------------ ------------------ ---------
/mnt/ramdisk/book/system01.dbf SYSTEM ONLINE 1 13237760064 13237759254 NO Media Recovery controlfile 13237753660 MOUNTED
/mnt/ramdisk/book/sysaux01.dbf RECOVER ONLINE 2 13237760064 13237759254 NO Media Recovery controlfile 13237753660 MOUNTED
/mnt/ramdisk/book/undotbs01.dbf ONLINE ONLINE 3 13237760064 13237759254 NO Media Recovery controlfile 13237753660 MOUNTED
/mnt/ramdisk/book/users01.dbf ONLINE ONLINE 4 13237760064 13237759254 NO Media Recovery controlfile 13237753660 MOUNTED
/mnt/ramdisk/book/example01.dbf ONLINE ONLINE 5 13237760064 13237759254 NO Media Recovery controlfile 13237753660 MOUNTED
/mnt/ramdisk/book/sugar01.dbf ONLINE ONLINE 6 13237760064 13237759254 NO Media Recovery controlfile 13237753660 MOUNTED
/mnt/ramdisk/book/tea01.dbf ONLINE ONLINE 7 13237760064 13237759254 NO Media Recovery controlfile 13237753660 MOUNTED
7 rows selected.
THREAD# OPEN_MODE STATUS STATUS
------- -------------------- ---------- ----------
1 MOUNTED OPEN Crash Reco
very req.
SYS@book> select * from scott.demo;
ID UPDATE_SCN COMMIT_SCN
------------ ------------ ------------
1 13237759024 13237759030
2 13237759251 13237759252
--控制文件的CHECKPOINT_CHANGE#=13237760064从哪里来的呢?
SYS@book> column name format a60
SYS@book> select SEQUENCE#,FIRST_CHANGE#,NEXT_CHANGE#,name from v$archived_log where SEQUENCE#>=200;
SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# NAME
--------- ------------- ------------ ------------------------------------------------------------
200 13237757017 13237757128 /u01/app/oracle/archivelog/book/1_200_907434361.dbf
201 13237757128 13237760056 /u01/app/oracle/archivelog/book/1_201_907434361.dbf
202 13237760056 13237760060 /u01/app/oracle/archivelog/book/1_202_907434361.dbf
203 13237760060 13237760064 /u01/app/oracle/archivelog/book/1_203_907434361.dbf
--来自于最后记录的SEQUENCE#=203的NEXT_CHANGE#=13237760064.
--花一个上午写这个东西,因为正好星期6帮别人恢复时遇到这个问题,估计他们星期5晚上做的全备,而数据文件很大备份时间很长,出现这个情况,
--我只好重新restore上个星期5的对应备份集中取出数据文件.花了N久的时间,做一个记录,再次说明细节很重要,要理解oracle的内部原理,遇到这个问题就很容易了.
--问题在于在现场很难保持绝对的冷静.
--补充如果我不catalog.这个问题就不存在了.补充测试:
SYS@book> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
$ rm -rf /mnt/ramdisk/book*
SYS@book> 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> restore controlfile FROM '/u01/app/oracle/fast_recovery_area/BOOK/autobackup/2016_05_23/o1_mf_s_912589151_cn4ogzy2_.bkp';
Starting restore at 2016-05-23 11:35:16
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 2016-05-23 11:35:18
RMAN> alter database mount ;
database mounted
released channel: ORA_DISK_1
RMAN> list backup by file;
List of Datafile Backups
========================
File Key TY LV S Ckp SCN Ckp Time #Pieces #Copies Compressed Tag
---- ------- - -- - ---------- ------------------- ------- ------- ---------- ---
1 48 B F A 13237754045 2016-05-23 08:58:10 1 1 NO TAG20160523T085649
2 46 B F A 13237754002 2016-05-23 08:56:50 1 1 NO TAG20160523T085649
3 47 B F A 13237754026 2016-05-23 08:57:35 1 1 NO TAG20160523T085649
4 49 B F A 13237754054 2016-05-23 08:58:35 1 1 NO TAG20160523T085649
5 50 B F A 13237754060 2016-05-23 08:58:50 1 1 NO TAG20160523T085649
6 51 B F A 13237754066 2016-05-23 08:59:05 1 1 NO TAG20160523T085649
7 52 B F A 13237754068 2016-05-23 08:59:08 1 1 NO TAG20160523T085649
SYS@book> @ &r/db_status
SYS@book> set numw 12
SYS@book> column name format a50
SYS@book> column file# format 9999
SYS@book>
SYS@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 13237754045 2016-05-23 08:58:10 0 0 0 SYSTEM /mnt/ramdisk/book/system01.dbf
2 13237754002 2016-05-23 08:56:50 0 0 0 ONLINE /mnt/ramdisk/book/sysaux01.dbf
3 13237754026 2016-05-23 08:57:35 0 0 0 ONLINE /mnt/ramdisk/book/undotbs01.dbf
4 13237754054 2016-05-23 08:58:35 0 0 0 ONLINE /mnt/ramdisk/book/users01.dbf
5 13237754060 2016-05-23 08:58:50 0 0 0 ONLINE /mnt/ramdisk/book/example01.dbf
6 13237754066 2016-05-23 08:59:05 0 0 0 ONLINE /mnt/ramdisk/book/sugar01.dbf
7 13237754068 2016-05-23 08:59:08 0 13237575558 13237575672 ONLINE /mnt/ramdisk/book/tea01.dbf
7 rows selected.
SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name FROM v$datafile_header;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZZY NAME TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ---------- ---------------- ------ -------------------------------------------------- ------------------------------
1 0 0 0 ONLINE 0
2 0 0 0 ONLINE 0
3 0 0 0 ONLINE 0
4 0 0 0 ONLINE 0
5 0 0 0 ONLINE 0
6 0 0 0 ONLINE 0
7 0 0 0 ONLINE 0
7 rows selected.
SYS@book> SELECT 'controlfile' "SCN location", 'SYSTEM checkpoint' NAME, checkpoint_change#,open_mode,current_scn FROM v$database;
SCN location NAME CHECKPOINT_CHANGE# OPEN_MODE CURRENT_SCN
---------------------- -------------------------------------------------- ------------------ ---------------------------------------- ------------
controlfile SYSTEM checkpoint 13237753660 MOUNTED 0
SYS@book> set echo off
NAME CONTROL_STAT DATAFILE_STATUS FILE# CONTROL_FILE_SCN DATAFILE_SCN FUZZY DATAFILE_STATUS SCN location SYSTEM_CHECKPOINT# OPEN_MODE
-------------------------------------------------- ------------ --------------- ----- ---------------- ------------ ------ --------------- ---------------------- ------------------ ----------------------------------------
/mnt/ramdisk/book/system01.dbf SYSTEM ONLINE 1 13237754045 0 File Missing? controlfile 13237753660 MOUNTED
/mnt/ramdisk/book/sysaux01.dbf ONLINE ONLINE 2 13237754002 0 File Missing? controlfile 13237753660 MOUNTED
/mnt/ramdisk/book/undotbs01.dbf ONLINE ONLINE 3 13237754026 0 File Missing? controlfile 13237753660 MOUNTED
/mnt/ramdisk/book/users01.dbf ONLINE ONLINE 4 13237754054 0 File Missing? controlfile 13237753660 MOUNTED
/mnt/ramdisk/book/example01.dbf ONLINE ONLINE 5 13237754060 0 File Missing? controlfile 13237753660 MOUNTED
/mnt/ramdisk/book/sugar01.dbf ONLINE ONLINE 6 13237754066 0 File Missing? controlfile 13237753660 MOUNTED
/mnt/ramdisk/book/tea01.dbf ONLINE ONLINE 7 13237754068 0 File Missing? controlfile 13237753660 MOUNTED
7 rows selected.
THREAD# OPEN_MODE STATUS STATUS
------- ---------------------------------------- ---------- ----------
1 MOUNTED OPEN Crash Reco
very req.
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 3 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 3 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters are successfully stored
RMAN> configure channel 1 device type disk clear;
old RMAN configuration parameters:
CONFIGURE CHANNEL 1 DEVICE TYPE DISK RATE 50 M;
old RMAN configuration parameters are successfully deleted
run {
set until scn 13237759230;
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_2ar6a09r_1_1.bak
channel ORA_DISK_1: piece handle=/home/oracle/backup/full0523_27r6a06i_1_1.bak tag=TAG20160523T085649
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
....
archived log file name=/u01/app/oracle/archivelog/book/1_201_907434361.dbf thread=1 sequence=201
media recovery complete, elapsed time: 00:00:05
Finished recover at 2016-05-23 11:37:19
--这样就没有问题了.
SYS@book> alter database open read only ;
Database altered.
SYS@book> select * from scott.demo;
ID UPDATE_SCN COMMIT_SCN
------------ ------------ ------------
1 13237759024 13237759030
--没有第2条记录.
SYS@book> select SEQUENCE#,FIRST_CHANGE#,NEXT_CHANGE#,name from v$archived_log where SEQUENCE#>=200;
SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# NAME
--------- ------------- ------------ ------------------------------------------------------------
200 13237757017 13237757128 /u01/app/oracle/archivelog/book/1_200_907434361.dbf
201 13237757128 13237760056 /u01/app/oracle/archivelog/book/1_201_907434361.dbf
--可以看出上面控制文件的CHECKPOINT_CHANGE#也与catelog操作有关.
SYS@book> @ &r/db_status
SYS@book> set numw 12
SYS@book> column name format a50
SYS@book> column file# format 9999
SYS@book>
SYS@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 13237759230 2016-05-23 09:49:46 0 0 0 SYSTEM /mnt/ramdisk/book/system01.dbf
2 13237759230 2016-05-23 09:49:46 0 0 0 RECOVER /mnt/ramdisk/book/sysaux01.dbf
3 13237759230 2016-05-23 09:49:46 0 0 0 ONLINE /mnt/ramdisk/book/undotbs01.dbf
4 13237759230 2016-05-23 09:49:46 0 0 0 RECOVER /mnt/ramdisk/book/users01.dbf
5 13237759230 2016-05-23 09:49:46 0 0 0 ONLINE /mnt/ramdisk/book/example01.dbf
6 13237759230 2016-05-23 09:49:46 0 0 0 ONLINE /mnt/ramdisk/book/sugar01.dbf
7 13237759230 2016-05-23 09:49:46 0 13237575558 13237575672 ONLINE /mnt/ramdisk/book/tea01.dbf
7 rows selected.
SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name FROM v$datafile_header;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ---------- ---------------- --- -------------------------------------------------- ------------------------------
1 13237759230 2016-05-23 09:49:46 7 13227286650 ONLINE 1275 NO /mnt/ramdisk/book/system01.dbf SYSTEM
2 13237759230 2016-05-23 09:49:46 1834 13227286650 ONLINE 1266 NO /mnt/ramdisk/book/sysaux01.dbf SYSAUX
3 13237759230 2016-05-23 09:49:46 923328 13227286650 ONLINE 1186 NO /mnt/ramdisk/book/undotbs01.dbf UNDOTBS1
4 13237759230 2016-05-23 09:49:46 16143 13227286650 ONLINE 1271 NO /mnt/ramdisk/book/users01.dbf USERS
5 13237759230 2016-05-23 09:49:46 952916 13227286650 ONLINE 1183 NO /mnt/ramdisk/book/example01.dbf EXAMPLE
6 13237759230 2016-05-23 09:49:46 1314508 13227286650 ONLINE 1199 NO /mnt/ramdisk/book/sugar01.dbf SUGAR
7 13237759230 2016-05-23 09:49:46 13237575534 13227286650 ONLINE 20 NO /mnt/ramdisk/book/tea01.dbf TEA
7 rows selected.
SYS@book> SELECT 'controlfile' "SCN location", 'SYSTEM checkpoint' NAME, checkpoint_change#,open_mode,current_scn FROM v$database;
SCN locatio NAME CHECKPOINT_CHANGE# OPEN_MODE CURRENT_SCN
----------- -------------------------------------------------- ------------------ -------------------- ------------
controlfile SYSTEM checkpoint 13237753660 READ ONLY 13237759229
SYS@book> set echo off
NAME CONTROL_STAT DATAFILE_STATUS FILE# CONTROL_FILE_SCN DATAFILE_SCN FUZ DATAFILE_STATUS SCN locatio SYSTEM_CHECKPOINT# OPEN_MODE
-------------------------------------------------- ------------ --------------- ----- ---------------- ------------ --- --------------- ----------- ------------------ --------------------
/mnt/ramdisk/book/system01.dbf SYSTEM ONLINE 1 13237759230 13237759230 NO Startup Normal controlfile 13237753660 READ ONLY
/mnt/ramdisk/book/sysaux01.dbf RECOVER ONLINE 2 13237759230 13237759230 NO Startup Normal controlfile 13237753660 READ ONLY
/mnt/ramdisk/book/undotbs01.dbf ONLINE ONLINE 3 13237759230 13237759230 NO Startup Normal controlfile 13237753660 READ ONLY
/mnt/ramdisk/book/users01.dbf RECOVER ONLINE 4 13237759230 13237759230 NO Startup Normal controlfile 13237753660 READ ONLY
/mnt/ramdisk/book/example01.dbf ONLINE ONLINE 5 13237759230 13237759230 NO Startup Normal controlfile 13237753660 READ ONLY
/mnt/ramdisk/book/sugar01.dbf ONLINE ONLINE 6 13237759230 13237759230 NO Startup Normal controlfile 13237753660 READ ONLY
/mnt/ramdisk/book/tea01.dbf ONLINE ONLINE 7 13237759230 13237759230 NO Startup Normal controlfile 13237753660 READ ONLY
7 rows selected.
THREAD# OPEN_MODE STATUS STATUS
------- -------------------- ---------- ----------
1 READ ONLY OPEN huh?
