数据文件头的检查点被冻结之后，检查点无法更新，数据库重启的时候就需要recovery，但是很可能之前的归档已经不在，无法recovery，导致数据库无法OPEN，本文通过begin backup模拟冻结检查点，然后恢复。
1，创建测试表
SQL> create table begin_bkup_test(id number) tablespace users; Table created. SQL> insert into begin_bkup_test values(1); 1 row created. SQL> commit; Commit complete. SQL> alter system switch logfile ; System altered. SQL> alter system checkpoint; System altered.
2，begin backup当前表空间
SQL> alter tablespace users begin backup; Tablespace altered. SQL> select * from v$backup; FILE# STATUS CHANGE# TIME CON_ID ---------- ------------------ ---------- --------- ---------- 1 NOT ACTIVE 0 0 3 NOT ACTIVE 0 0 4 NOT ACTIVE 0 0 7 ACTIVE 3231265 02-SEP-20 0 SQL> insert into begin_bkup_test values(2); 1 row created. SQL> commit; Commit complete. SQL> alter system switch logfile ; System altered. SQL> / System altered. SQL> / System altered. SQL> / System altered. SQL> alter system checkpoint; System altered.
确定检查点情况：
SQL> select name,checkpoint_change# from v$datafile; NAME CHECKPOINT_CHANGE# ------------------------------------------------------- ------------------ +DATA/ORA19C/DATAFILE/system.256.1049639321 3231300 +DATA/ORA19C/DATAFILE/sysaux.257.1049639387 3231300 +DATA/ORA19C/DATAFILE/undotbs1.258.1049639421 3231300 +DATA/ORA19C/DATAFILE/user.dbf 3231265 ---可以看到user表空间的检查点没有更新
3，备份数据库，并删除备份时间点之前的归档
RMAN> backup database format '/home/oracle/backup/db_%d_%T_%U'; Starting backup at 02-SEP-20 using channel ORA_DISK_1 RMAN-06554: warning: file 7 is in backup mode 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=+DATA/ORA19C/DATAFILE/system.256.1049639321 input datafile file number=00003 name=+DATA/ORA19C/DATAFILE/sysaux.257.1049639387 input datafile file number=00004 name=+DATA/ORA19C/DATAFILE/undotbs1.258.1049639421 input datafile file number=00007 name=+DATA/ORA19C/DATAFILE/user.dbf channel ORA_DISK_1: starting piece 1 at 02-SEP-20 channel ORA_DISK_1: finished piece 1 at 02-SEP-20 piece handle=/home/oracle/backup/db_ORA19C_20200902_11v9dlo6_1_1 tag=TAG20200902T145358 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07 Finished backup at 02-SEP-20 Starting Control File and SPFILE Autobackup at 02-SEP-20 piece handle=+DATA/ORA19C/AUTOBACKUP/2020_09_02/s_1050072846.293.1050072847 comment=NONE Finished Control File and SPFILE Autobackup at 02-SEP-20
删除备份时间点之前的归档
ASMCMD> rm -rf thread_1_seq_19.284.1050072439 ASMCMD> rm -rf thread_1_seq_20.286.1050072635 ASMCMD> rm -rf thread_1_seq_21.292.1050072691 ASMCMD> rm -rf thread_1_seq_22.290.1050072691 ASMCMD> rm -rf thread_1_seq_23.291.1050072695 ASMCMD> rm -rf thread_1_seq_24.288.1050072697 ASMCMD> rm -rf thread_1_seq_25.289.1050072777 ASMCMD> rm -rf thread_1_seq_26.287.1050072777
插入第三条测试数据
SQL> insert into begin_bkup_test values(3); 1 row created. SQL> commit; Commit complete. SQL> select * from begin_bkup_test; ID ---------- 1 2 3 SQL> alter system switch logfile; System altered. SQL> / System altered. SQL> alter system checkpoint; System altered.
4，Abort方式重启数据库
SQL> shutdown abort; ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 4294963272 bytes Fixed Size 8904776 bytes Variable Size 822083584 bytes Database Buffers 3456106496 bytes Redo Buffers 7868416 bytes Database mounted. ORA-10873: file 7 needs to be either taken out of backup mode or media recovered ORA-01110: data file 7: '+DATA/ORA19C/DATAFILE/user.dbf'
可以看到file 7是需要恢复的，接下来尝试恢复file 7
SQL> alter database recover datafile 7; alter database recover datafile 7 * ERROR at line 1: ORA-00279: change 3231265 generated at 09/02/2020 14:50:57 needed for thread 1 ORA-00289: suggestion : +DATA/ORA19C/ARCHIVELOG/2020_09_02/thread_1_seq_21.292.1050072691 ORA-00280: change 3231265 for thread 1 is in sequence #21
请求的#21号归档日志已经删除，无法恢复，接下来继续尝试从备份中restore file 7
SQL> alter database datafile 7 offline; alter database datafile 7 offline * ERROR at line 1: ORA-01156: recovery or flashback in progress may need access to files SQL> alter database recover cancel; Database altered. SQL> alter database datafile 7 offline; Database altered. RMAN> restore datafile 7; Starting restore at 02-SEP-20 using channel ORA_DISK_1 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 00007 to +DATA/ORA19C/DATAFILE/user.dbf channel ORA_DISK_1: reading from backup piece /home/oracle/backup/db_ORA19C_20200902_11v9dlo6_1_1 channel ORA_DISK_1: piece handle=/home/oracle/backup/db_ORA19C_20200902_11v9dlo6_1_1 tag=TAG20200902T145358 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 Finished restore at 02-SEP-20 RMAN> recover datafile 7; Starting recover at 02-SEP-20 using channel ORA_DISK_1 starting media recovery archived log for thread 1 with sequence 27 is already on disk as file +DATA/ORA19C/ARCHIVELOG/2020_09_02/thread_1_seq_27.285.1050072777 archived log for thread 1 with sequence 28 is already on disk as file +DATA/ORA19C/ARCHIVELOG/2020_09_02/thread_1_seq_28.291.1050072955 archived log for thread 1 with sequence 29 is already on disk as file +DATA/ORA19C/ARCHIVELOG/2020_09_02/thread_1_seq_29.290.1050072955 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 09/02/2020 16:27:27 RMAN-06053: unable to perform media recovery because of missing log RMAN-06025: no backup of archived log for thread 1 with sequence 26 and starting SCN of 3231331 found to restore RMAN-06025: no backup of archived log for thread 1 with sequence 25 and starting SCN of 3231299 found to restore RMAN-06025: no backup of archived log for thread 1 with sequence 24 and starting SCN of 3231296 found to restore RMAN-06025: no backup of archived log for thread 1 with sequence 23 and starting SCN of 3231293 found to restore RMAN-06025: no backup of archived log for thread 1 with sequence 22 and starting SCN of 3231290 found to restore RMAN-06025: no backup of archived log for thread 1 with sequence 21 and starting SCN of 3231256 found to restore
可以看到，还是因为归档丢失，无法恢复，说明即使从备份中重新restore，file 7的检查点还是一样的，总结： 因为begin backup冻结了数据文件头的检查点导致检查点无法更新，而最近的一次备份也是备份的begin backup状态下的数据文件，即使restore，检查点也是没有改变的，接下来要做的是更新datafile 7的数据文件头的检查点到最近一次备份，然后向后应用归档。
5，BBED修改数据文件头
将system的数据文件恢复出来用做参考：
RMAN> run{
2> set newname for datafile 1 to '/home/oracle/system01.dbf';
3> restore datafile 1;
4> }
executing command: SET NEWNAME
Starting restore at 02-SEP-20
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /home/oracle/system01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/db_ORA19C_20200902_11v9dlo6_1_1
channel ORA_DISK_1: piece handle=/home/oracle/backup/db_ORA19C_20200902_11v9dlo6_1_1 tag=TAG20200902T145358
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 02-SEP-20
Restore datafile 7：
RMAN> run{
2> set newname for datafile 7 to '/home/oracle/user01.dbf';
3> restore datafile 7;
4> }
executing command: SET NEWNAME
Starting restore at 02-SEP-20
using channel ORA_DISK_1
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 00007 to /home/oracle/user01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/db_ORA19C_20200902_11v9dlo6_1_1
channel ORA_DISK_1: piece handle=/home/oracle/backup/db_ORA19C_20200902_11v9dlo6_1_1 tag=TAG20200902T145358
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 02-SEP-20
BBED修改数据文件头：
[oracle@enmo ~]$ bbed parfile=bbed.par BBED: Release 2.0.0.0.0 - Limited Production on Wed Sep 2 16:32:36 2020 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. ************* !!! For Oracle Internal Use only !!! *************** BBED> info File# Name Size(blks) ----- ---- ---------- 1 /home/oracle/system01.dbf 0 2 /home/oracle/user01.dbf 0 BBED> set file 1 block 1 FILE# 1 BLOCK# 1 BBED> p kcvfhckp struct kcvfhckp, 36 bytes @484 struct kcvcpscn, 8 bytes @484 ub4 kscnbas @484 0x00314e88 ub2 kscnwrp @488 0x8000 ub2 kscnwrp2 @490 0x0000 ub4 kcvcptim @492 0x3e96d706 ub2 kcvcpthr @496 0x0001 union u, 12 bytes @500 struct kcvcprba, 12 bytes @500 ub4 kcrbaseq @500 0x0000001c ub4 kcrbabno @504 0x00000003 ub2 kcrbabof @508 0x0010 ub1 kcvcpetb[0] @512 0x02 ub1 kcvcpetb[1] @513 0x00 ub1 kcvcpetb[2] @514 0x00 ub1 kcvcpetb[3] @515 0x00 ub1 kcvcpetb[4] @516 0x00 ub1 kcvcpetb[5] @517 0x00 ub1 kcvcpetb[6] @518 0x00 ub1 kcvcpetb[7] @519 0x00 BBED> set file 2 block 1 FILE# 2 BLOCK# 1 BBED> p kcvfhckp struct kcvfhckp, 36 bytes @484 struct kcvcpscn, 8 bytes @484 ub4 kscnbas @484 0x00314e21 ub2 kscnwrp @488 0x8000 ub2 kscnwrp2 @490 0x0000 ub4 kcvcptim @492 0x3e96d651 ub2 kcvcpthr @496 0x0001 union u, 12 bytes @500 struct kcvcprba, 12 bytes @500 ub4 kcrbaseq @500 0x00000015 ub4 kcrbabno @504 0x00000009 ub2 kcrbabof @508 0x0010 ub1 kcvcpetb[0] @512 0x02 ub1 kcvcpetb[1] @513 0x00 ub1 kcvcpetb[2] @514 0x00 ub1 kcvcpetb[3] @515 0x00 ub1 kcvcpetb[4] @516 0x00 ub1 kcvcpetb[5] @517 0x00 ub1 kcvcpetb[6] @518 0x00 ub1 kcvcpetb[7] @519 0x00
对比system文件，需要修改的内容为： @484 kscnbas @492 kscnwrp2 @500 kcrbaseq @504 kcrbabno
BBED> set file 1 offset 484 count 8 FILE# 1 OFFSET 484 COUNT 8 BBED> d File: /home/oracle/system01.dbf (1) Block: 1 Offsets: 484 to 491 Dba:0x00400001 ------------------------------------------------------------------------ 884e3100 00800000 <32 bytes per line> BBED> set file 2 offset 484 count 8 FILE# 2 OFFSET 484 COUNT 8 BBED> d File: /home/oracle/user01.dbf (2) Block: 1 Offsets: 484 to 491 Dba:0x00800001 ------------------------------------------------------------------------ 214e3100 00800000 <32 bytes per line> BBED> m /x 884e File: /home/oracle/user01.dbf (2) Block: 1 Offsets: 484 to 491 Dba:0x00800001 ------------------------------------------------------------------------ 884e3100 00800000 <32 bytes per line> BBED> set offset 486 OFFSET 486 BBED> m /x 3100 File: /home/oracle/user01.dbf (2) Block: 1 Offsets: 486 to 493 Dba:0x00800001 ------------------------------------------------------------------------ 31000080 000051d6 <32 bytes per line> BBED> set file 1 offset 492 FILE# 1 OFFSET 492 BBED> d File: /home/oracle/system01.dbf (1) Block: 1 Offsets: 492 to 499 Dba:0x00400001 ------------------------------------------------------------------------ 06d7963e 01000000 <32 bytes per line> BBED> set file 2 offset 492 FILE# 2 OFFSET 492 BBED> m /x 06d7 File: /home/oracle/user01.dbf (2) Block: 1 Offsets: 492 to 499 Dba:0x00800001 ------------------------------------------------------------------------ 06d7963e 01000000 <32 bytes per line> BBED> set file 1 offset 500 count 8 FILE# 1 OFFSET 500 COUNT 8 BBED> d File: /home/oracle/system01.dbf (1) Block: 1 Offsets: 500 to 507 Dba:0x00400001 ------------------------------------------------------------------------ 1c000000 03000000 <32 bytes per line> BBED> set file 2 offset 500 FILE# 2 OFFSET 500 BBED> m /x 1c00 File: /home/oracle/user01.dbf (2) Block: 1 Offsets: 500 to 507 Dba:0x00800001 ------------------------------------------------------------------------ 1c000000 09000000 <32 bytes per line> BBED> set file 1 offset 504 FILE# 1 OFFSET 504 BBED> d File: /home/oracle/system01.dbf (1) Block: 1 Offsets: 504 to 511 Dba:0x00400001 ------------------------------------------------------------------------ 03000000 10000000 <32 bytes per line> BBED> set file 2 offset 504 FILE# 2 OFFSET 504 BBED> m /x 0300 File: /home/oracle/user01.dbf (2) Block: 1 Offsets: 504 to 511 Dba:0x00800001 ------------------------------------------------------------------------ 03000000 10000000 <32 bytes per line> BBED> set file 2 block 1 FILE# 2 BLOCK# 1 BBED> p kcvfhckp struct kcvfhckp, 36 bytes @484 struct kcvcpscn, 8 bytes @484 ub4 kscnbas @484 0x00314e88 ub2 kscnwrp @488 0x8000 ub2 kscnwrp2 @490 0x0000 ub4 kcvcptim @492 0x3e96d706 ub2 kcvcpthr @496 0x0001 union u, 12 bytes @500 struct kcvcprba, 12 bytes @500 ub4 kcrbaseq @500 0x0000001c ub4 kcrbabno @504 0x00000003 ub2 kcrbabof @508 0x0010 ub1 kcvcpetb[0] @512 0x02 ub1 kcvcpetb[1] @513 0x00 ub1 kcvcpetb[2] @514 0x00 ub1 kcvcpetb[3] @515 0x00 ub1 kcvcpetb[4] @516 0x00 ub1 kcvcpetb[5] @517 0x00 ub1 kcvcpetb[6] @518 0x00 ub1 kcvcpetb[7] @519 0x00
修改完成，确认修改无误，应用修改
BBED> sum apply Check value for File 2, Block 1: current = 0x9c07, required = 0x9c07
用dbms_file_transfer包将修改好的user01.dbf copy到asm
SQL> alter database open; Database altered. SQL> create directory DESTINATIONDIR as '/home/oracle'; Directory created. SQL> create directory sourcedir as '+data/ora19c/datafile'; Directory created. SQL> begin dbms_file_transfer.copy_file(source_directory_object => 'destinationdir', source_file_name => 'user01.dbf', destination_directory_object => 'sourcedir', destination_file_name => 'user.dbf'); end; / PL/SQL procedure successfully completed.
恢复datafile 7： 此次oracle提示需要从#28号开始恢复（未修改文件头的时候需要从#21号开始恢复）
SQL> recover datafile 7;
ORA-00279: change 3231368 generated at 09/02/2020 14:53:58 needed for thread 1
ORA-00289: suggestion : +DATA/ORA19C/ARCHIVELOG/2020_09_02/thread_1_seq_28.291.1050072955
ORA-00280: change 3231368 for thread 1 is in sequence #28
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
Log applied.
Media recovery complete.
SQL> alter database datafile 7 online;
Database altered.
SQL> alter database open;
Database altered.
检查，数据文件检查点已经一致
SQL> select name,checkpoint_change# from v$datafile; NAME CHECKPOINT_CHANGE# ---------------- ---------------------------------------------------- +DATA/ORA19C/DATAFILE/system.256.1049639321 3332919 +DATA/ORA19C/DATAFILE/sysaux.257.1049639387 3332919 +DATA/ORA19C/DATAFILE/undotbs1.258.1049639421 3332919 +DATA/ORA19C/DATAFILE/user.dbf 3332919
最后验证数据，数据无丢失
SQL> select * from begin_bkup_test; ID ---------- 3 1 2
编辑推荐：
- 模拟数据文件检查点被冻结之后的数据库恢复03-03
- Oracle Hybrid Columnar Compression(HCC) 混合列压缩03-03
- OGG抽取进程延迟100多小时03-03
- HTML注册 Canvas 安信6参考手册 HTML 颜色名+641480 HTML 视频/音频 HTML Canvas03-03
- HTML 音频/视频参考代理无极3 HTML 画布+641480 HTML 字符集 HTML5 视频和音频的03-03
- 超实用的短视频脚本写作技巧+脚本公式解析!干货快收藏!03-03
- 淘宝死店如何盘活，如何做到日销千单，干货收藏03-03
- HTML URL 安信6参考代理手册 URL - 统一+641480资源定位器03-03
下一篇：
相关推荐
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效
2 月 9 日消息，雷神 (THUNDEROBOT) 现已宣布推出基于英
-
制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用
2 月 10 日消息，制造商 Musnap 现已在海外推出一款 Oce
热文推荐
- OGG抽取进程延迟100多小时
OGG抽取进程延迟100多小时
26-03-03 - 超实用的短视频脚本写作技巧+脚本公式解析!干货快收藏!
超实用的短视频脚本写作技巧+脚本公式解析!干货快收藏!
26-03-03 - 淘宝死店如何盘活，如何做到日销千单，干货收藏
淘宝死店如何盘活，如何做到日销千单，干货收藏
26-03-03 - 数据库连接异常故障报告
数据库连接异常故障报告
26-03-03 - 哪些行业需要注册香港公司及海外公司？
哪些行业需要注册香港公司及海外公司？
26-03-03 - 浅谈Oracle Result Cache
浅谈Oracle Result Cache
26-03-03 - 基于VIP漂移业务层数据库连接的解读
基于VIP漂移业务层数据库连接的解读
26-03-03 - 10G自动收集统计信息修改
10G自动收集统计信息修改
26-03-03 - 解决gc current request等待事件
解决gc current request等待事件
26-03-03 - 一次library cache lock 问题分析
一次library cache lock 问题分析
26-03-03