Oracle redo文件损坏怎么恢复
短信预约 -IT技能 免费直播动态提醒
这篇“Oracle redo文件损坏怎么恢复”文章的知识点大部分人都不太理解，所以小编给大家总结了以下内容，内容详细，步骤清晰，具有一定的借鉴价值，希望大家阅读完这篇文章能有所收获，下面我们一起来看看这篇“Oracle redo文件损坏怎么恢复”文章吧。
redo文件损坏涉及到多种多样场景，具体场景可以分四大部分：
1、按照redo的状态可以分为current、active和inactive；
2、按照数据库归档模式可以分为归档和非归档；
3、按照脏块有没写入数据文件可以分为有和无；
4、按照损坏时数据库的状态可以分为在线和关闭；
现在主要通过两部分来介绍redo文件恢复相关的内容：
1、按照redo状态维度来介绍各种场景的恢复方法；
2、模拟几种恢复方法的操作；
一、按照redo状态维度来介绍各种场景的恢复方法。
1.1、current redo文件恢复介绍：
1.2、active redo文件恢复介绍：
1.3、inactive redo文件恢复介绍：
二、模拟几种恢复方法的操作；
下面主要选取 “current的redo文件在归档模式下采用不正常关闭数据库时还有脏块没有写入数据文件时的损坏” 和 “current的redo文件在非归档模式下数据库在线时还有脏块没有写入数据文件时的损坏” 这两个场景来模拟和恢复，其他场景的恢复请参考上面的恢复操作。
2.1、current的redo文件在归档模式下采用不正常关闭数据库时还有脏块没有写入数据文件时的损坏模拟恢复：
|
1、数据库基本信息和redo情况 SQL> show parameter db_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string leonliao SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination /home/oracle/oradata/leonliao/arch Oldest online log sequence 7 Next log sequence to archive 9 Current log sequence 9 SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- 1 1 9 52428800 512 1 NO CURRENT 1250771 07-MAR-16 2.8147E+14 2 1 8 52428800 512 1 YES INACTIVE 1250768 07-MAR-16 1250771 07-MAR-16 3 1 7 52428800 512 1 YES INACTIVE 1250765 07-MAR-16 1250768 07-MAR-16 SQL> select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_ ---------- ------- ------- --------------------------------------------- --- 3 ONLINE /home/oracle/oradata/leonliao/redo03.log NO 2 ONLINE /home/oracle/oradata/leonliao/redo02.log NO 1 ONLINE /home/oracle/oradata/leonliao/redo01.log NO 2、在t_redo表插入一条记录2，并shutdown abort关闭数据库 SQL> select * from t_redo; ID ---------- 1 SQL> insert into t_redo values(2); 1 row created. SQL> commit; Commit complete. SQL> shutdown abort ORACLE instance shut down. 3、删掉current 的redo文件 [oracle@leon1 leonliao]$ pwd /home/oracle/oradata/leonliao [oracle@leon1 leonliao]$ rm -rf redo01.log 4、启动数据库到mount状态并尝试打开数据库 SQL> startup mount ORACLE instance started. Total System Global Area 626327552 bytes Fixed Size 2230952 bytes Variable Size 184550744 bytes Database Buffers 432013312 bytes Redo Buffers 7532544 bytes Database mounted. SQL> alter database open; alter database open * ERROR at line 1: ORA-00313: open failed for members of log group 1 of thread 1 ORA-00312: online log 1 thread 1: '/home/oracle/oradata/leonliao/redo01.log' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 SQL> alter database clear logfile group 1; alter database clear logfile group 1 * ERROR at line 1: ORA-01624: log 1 needed for crash recovery of instance leonliao (thread 1) ORA-00312: online log 1 thread 1: '/home/oracle/oradata/leonliao/redo01.log' SQL> alter database clear unarchived logfile group 1; alter database clear unarchived logfile group 1 * ERROR at line 1: ORA-01624: log 1 needed for crash recovery of instance leonliao (thread 1) ORA-00312: online log 1 thread 1: '/home/oracle/oradata/leonliao/redo01.log' SQL> recover database until cancel; ORA-00279: change 1250771 generated at 03/07/2016 07:35:44 needed for thread 1 ORA-00289: suggestion : /home/oracle/oradata/leonliao/arch/1_9_905840705.dbf ORA-00280: change 1250771 for thread 1 is in sequence #9 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} auto ORA-00308: cannot open archived log '/home/oracle/oradata/leonliao/arch/1_9_905840705.dbf' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 ORA-00308: cannot open archived log '/home/oracle/oradata/leonliao/arch/1_9_905840705.dbf' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below ORA-01194: file 1 needs more recovery to be consistent ORA-01110: data file 1: '/home/oracle/oradata/leonliao/system01.dbf' SQL> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-01194: file 1 needs more recovery to be consistent ORA-01110: data file 1: '/home/oracle/oradata/leonliao/system01.dbf' 5、设置隐含参数_allow_resetlogs_corruption为true SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile; System altered. SQL> shutdown immediate ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 626327552 bytes Fixed Size 2230952 bytes Variable Size 184550744 bytes Database Buffers 432013312 bytes Redo Buffers 7532544 bytes Database mounted. SQL> alter database open resetlogs; Database altered. 6、验证数据是否丢失，数据为2的记录已经丢失 SQL> select * from t_redo; ID ---------- 1 |
2.2、current的redo文件在非归档模式下数据库在线时还有脏块没有写入数据文件时的损坏模拟恢复：
|
1、数据库基本信息和redo情况 SQL> show parameter db_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string leonliao SQL> archive log list; Database log mode No Archive Mode Automatic archival Disabled Archive destination /home/oracle/oradata/leonliao/arch Oldest online log sequence 2 Current log sequence 4 SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- 1 1 4 52428800 512 1 NO CURRENT 1250086 07-MAR-16 2.8147E+14 2 1 2 52428800 512 1 NO INACTIVE 1250080 07-MAR-16 1250083 07-MAR-16 3 1 3 52428800 512 1 NO INACTIVE 1250083 07-MAR-16 1250086 07-MAR-16 SQL> select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_ ---------- ------- ------- --------------------------------------------- --- 3 ONLINE /home/oracle/oradata/leonliao/redo03.log NO 2 ONLINE /home/oracle/oradata/leonliao/redo02.log NO 1 ONLINE /home/oracle/oradata/leonliao/redo01.log NO 2、创建t_redo表并插入一条数据 SQL> create table t_redo (id number); Table created. SQL> insert into t_redo values(1); 1 row created. SQL> commit; Commit complete. SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- 1 1 4 52428800 512 1 NO CURRENT 1250086 07-MAR-16 2.8147E+14 2 1 2 52428800 512 1 NO INACTIVE 1250080 07-MAR-16 1250083 07-MAR-16 3 1 3 52428800 512 1 NO INACTIVE 1250083 07-MAR-16 1250086 07-MAR-16 3、删除current的redo01.log文件 [oracle@leon1 leonliao]$ pwd /home/oracle/oradata/leonliao [oracle@leon1 leonliao]$ rm -rf redo01.log 4、尝试直接通过不归档等方式初始化redo01.log文件，无法初始化current的redo文件 SQL> alter database clear unarchived logfile group 1; alter database clear unarchived logfile group 1 * ERROR at line 1: ORA-01624: log 1 needed for crash recovery of instance leonliao (thread 1) ORA-00312: online log 1 thread 1: '/home/oracle/oradata/leonliao/redo01.log' SQL> alter database clear logfile group 1; alter database clear logfile group 1 * ERROR at line 1: ORA-01624: log 1 needed for crash recovery of instance leonliao (thread 1) ORA-00312: online log 1 thread 1: '/home/oracle/oradata/leonliao/redo01.log' 5、通过将current状态切换到active状态，并初始化redo01.log文件 SQL> alter system switch logfile; System altered. SQL> alter database clear logfile group 1; Database altered. SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- 1 1 0 52428800 512 1 NO UNUSED 1250086 07-MAR-16 1250147 07-MAR-16 2 1 5 52428800 512 1 NO CURRENT 1250147 07-MAR-16 2.8147E+14 3 1 3 52428800 512 1 NO INACTIVE 1250083 07-MAR-16 1250086 07-MAR-16 6、验证数据是否丢失，数据没有丢失 SQL> select * from t_redo; ID ---------- 1 |
以上就是关于“Oracle redo文件损坏怎么恢复”这篇文章的内容，相信大家都有了一定的了解，希望小编分享的内容对大家有帮助，若想了解更多相关的知识内容，请关注亿速云行业资讯频道。
免责声明：
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
![[mysql]mysql8修改root密码](https://static.528045.com/imgs/39.jpg?imageMogr2/format/webp/blur/1x0/quality/35)
