1. 环境模拟
1.1 创建测试表空间
create tablespace enmo5 datafile '/oradata/enmo/enmo05.dbf' size 10M;
create tablespace enmo6 datafile '/oradata/enmo/enmo06.dbf' size 10M;
create tablespace enmo7 datafile '/oradata/enmo/enmo07.dbf' size 10M;
create tablespace enmo8 datafile '/oradata/enmo/enmo08.dbf' size 10M;
create tablespace enmo9 datafile '/oradata/enmo/enmo09.dbf' size 10M;
create tablespace enmo10 datafile '/oradata/enmo/enmo10.dbf' size 10M;
复制1.2. 创建测试表
create table sys.test1 tablespace enmo5 as select * from dba_objects;
复制1.3. 重建控制文件
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 1202556928 bytes
Fixed Size 2252704 bytes
Variable Size 385876064 bytes
Database Buffers 805306368 bytes
Redo Buffers 9121792 bytes
SQL>
[oracle@sourcedb ~]$ cat cr_ctl.sql
CREATE CONTROLFILE REUSE DATABASE "ENMO" RESETLOGS NOARCHIVELOG
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXDATAFILES 200
MAXINSTANCES 1
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/oradata/enmo/redo01a.log' SIZE 100M BLOCKSIZE 512,
GROUP 2 '/oradata/enmo/redo02a.log' SIZE 100M BLOCKSIZE 512,
GROUP 3 '/oradata/enmo/redo03a.log' SIZE 100M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/oradata/enmo/system01.dbf',
'/oradata/enmo/sysaux01.dbf',
'/oradata/enmo/undotbs01.dbf',
'/oradata/enmo/users01.dbf'
--'/oradata/enmo/enmo05.dbf',
--'/oradata/enmo/enmo06.dbf',
--'/oradata/enmo/enmo07.dbf',
--'/oradata/enmo/enmo08.dbf',
--'/oradata/enmo/enmo09.dbf',
--'/oradata/enmo/enmo10.dbf'
CHARACTER SET ZHS16GBK
;
SQL> @cr_ctl.sql
Control file created.
SQL>
SQL> alter database open resetlogs;
Database altered.
SQL>
复制1.4. 创建测试表
非归档模式且online log全部已覆盖，
SQL> create table sys.test2(c1 number) tablespace users;
Table created.
SQL>
SQL> begin
2 for i_num in 1..699999 loop
3 insert into sys.test2 values(i_num);
4 commit;
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL>
SQL> set linesize 300
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
-------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --- ---------------- -------------------- ------------------- -------------------- -------------------
1 1 7 104857600 512 1 NO ACTIVE 1431167 2021-08-05 23:22:15 1613408 2021-08-05 23:22:27
2 1 8 104857600 512 1 NO CURRENT 1613408 2021-08-05 23:22:27 281474976710655
3 1 6 104857600 512 1 NO ACTIVE 1236207 2021-08-05 23:22:05 1431167 2021-08-05 23:22:15
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /arch1
Oldest online log sequence 6
Current log sequence 8
SQL>
复制1.5. 发现部分业务有无法访问
SQL> select * from test1;
select * from test1
*
ERROR at line 1:
ORA-00376: file 5 cannot be read at this time
ORA-01111: name for data file 5 is unknown - rename to correct file
ORA-01110: data file 5: '/u01/app/oracle/product/11.2.0/db_1/dbs/MISSING00005'
SQL>
复制1.6. 查询数据文件状态
SQL> set linesize 300 pagesize 2000
SQL> col ERROR for a20
SQL> col NAME for a60
SQL> set numw 20
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SQL> select FILE#,STATUS,TS#,RFILE#,RESETLOGS_CHANGE#,RESETLOGS_TIME,
2 CHECKPOINT_CHANGE#,CHECKPOINT_TIME,CHECKPOINT_COUNT,status,ERROR,RECOVER,FUZZY from v$datafile_header;
FILE# STATUS TS# RFILE# RESETLOGS_CHANGE# RESETLOGS_TIME CHECKPOINT_CHANGE# CHECKPOINT_TIME CHECKPOINT_COUNT STATUS ERROR REC FUZ
-------------------- ------- -------------------- -------------------- -------------------- ------------------- -------------------- ------------------- -------------------- ------- -------------------- --- ---
1 ONLINE 0 1 212685 2021-08-05 23:20:52 1613802 2021-08-05 23:24:57 19 ONLINE NO NO
2 ONLINE 1 2 212685 2021-08-05 23:20:52 1613802 2021-08-05 23:24:57 19 ONLINE NO NO
3 ONLINE 2 3 212685 2021-08-05 23:20:52 1613802 2021-08-05 23:24:57 19 ONLINE NO NO
4 ONLINE 4 4 212685 2021-08-05 23:20:52 1613802 2021-08-05 23:24:57 19 ONLINE NO NO
5 OFFLINE 0 0 0 0 0 OFFLINE FILE MISSING
6 OFFLINE 0 0 0 0 0 OFFLINE FILE MISSING
7 OFFLINE 0 0 0 0 0 OFFLINE FILE MISSING
8 OFFLINE 0 0 0 0 0 OFFLINE FILE MISSING
9 OFFLINE 0 0 0 0 0 OFFLINE FILE MISSING
10 OFFLINE 0 0 0 0 0 OFFLINE FILE MISSING
10 rows selected.
SQL>
复制2. 恢复过程
2.1 尝试重建控制文件
[oracle@sourcedb ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Aug 5 23:38:36 2021
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> startup nomount pfile='p.ora'
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> startup nomount force pfile='p.ora';
ORACLE instance started.
Total System Global Area 1202556928 bytes
Fixed Size 2252704 bytes
Variable Size 385876064 bytes
Database Buffers 805306368 bytes
Redo Buffers 9121792 bytes
SQL> !cat cr_ctl.sql
CREATE CONTROLFILE REUSE DATABASE "ENMO" RESETLOGS NOARCHIVELOG
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXDATAFILES 200
MAXINSTANCES 1
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/oradata/enmo/redo01a.log' SIZE 100M BLOCKSIZE 512,
GROUP 2 '/oradata/enmo/redo02a.log' SIZE 100M BLOCKSIZE 512,
GROUP 3 '/oradata/enmo/redo03a.log' SIZE 100M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/oradata/enmo/system01.dbf',
'/oradata/enmo/sysaux01.dbf',
'/oradata/enmo/undotbs01.dbf',
'/oradata/enmo/users01.dbf',
'/oradata/enmo/enmo05.dbf',
'/oradata/enmo/enmo06.dbf',
'/oradata/enmo/enmo07.dbf',
'/oradata/enmo/enmo08.dbf',
'/oradata/enmo/enmo09.dbf',
'/oradata/enmo/enmo10.dbf'
CHARACTER SET ZHS16GBK
;
SQL> @cr_ctl.sql
CREATE CONTROLFILE REUSE DATABASE "ENMO" RESETLOGS NOARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01189: file is from a different RESETLOGS than previous files
ORA-01110: data file 5: '/oradata/enmo/enmo05.dbf'
SQL>
复制2.2 数据库恢复
SQL> alter database rename file '/u01/app/oracle/product/11.2.0/db_1/dbs/MISSING00005' to '/oradata/enmo/enmo05.dbf';
Database altered.
SQL> alter database rename file '/u01/app/oracle/product/11.2.0/db_1/dbs/MISSING00006' to '/oradata/enmo/enmo06.dbf';
Database altered.
SQL> alter database rename file '/u01/app/oracle/product/11.2.0/db_1/dbs/MISSING00007' to '/oradata/enmo/enmo07.dbf';
Database altered.
SQL> alter database rename file '/u01/app/oracle/product/11.2.0/db_1/dbs/MISSING00008' to '/oradata/enmo/enmo08.dbf';
Database altered.
SQL> alter database rename file '/u01/app/oracle/product/11.2.0/db_1/dbs/MISSING00009' to '/oradata/enmo/enmo09.dbf';
Database altered.
SQL> alter database rename file '/u01/app/oracle/product/11.2.0/db_1/dbs/MISSING00010' to '/oradata/enmo/enmo10.dbf';
Database altered.
SQL>
复制SQL> set linesize 300 pagesize 2000
SQL> col ERROR for a20
SQL> col NAME for a60
SQL> set numw 20
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SQL> select FILE#,STATUS,TS#,RFILE#,RESETLOGS_CHANGE#,RESETLOGS_TIME,
CHECKPOINT_CHANGE#,CHECKPOINT_TIME,CHECKPOINT_COUNT,status,ERROR,RECOVER,FUZZY from v$datafile_header;
2
FILE# STATUS TS# RFILE# RESETLOGS_CHANGE# RESETLOGS_TIME CHECKPOINT_CHANGE# CHECKPOINT_TIME CHECKPOINT_COUNT STATUS ERROR REC FUZ
-------------------- ------- -------------------- -------------------- -------------------- ------------------- -------------------- ------------------- -------------------- ------- -------------------- --- ---
1 ONLINE 0 1 212685 2021-08-05 23:20:52 1613802 2021-08-05 23:24:57 19 ONLINE NO NO
2 ONLINE 1 2 212685 2021-08-05 23:20:52 1613802 2021-08-05 23:24:57 19 ONLINE NO NO
3 ONLINE 2 3 212685 2021-08-05 23:20:52 1613802 2021-08-05 23:24:57 19 ONLINE NO NO
4 ONLINE 4 4 212685 2021-08-05 23:20:52 1613802 2021-08-05 23:24:57 19 ONLINE NO NO
5 OFFLINE 5 5 1 2021-08-05 23:16:06 212684 2021-08-05 23:19:51 5 OFFLINE WRONG RESETLOGS NO
6 OFFLINE 6 6 1 2021-08-05 23:16:06 212684 2021-08-05 23:19:51 5 OFFLINE WRONG RESETLOGS NO
7 OFFLINE 7 7 1 2021-08-05 23:16:06 212684 2021-08-05 23:19:51 5 OFFLINE WRONG RESETLOGS NO
8 OFFLINE 8 8 1 2021-08-05 23:16:06 212684 2021-08-05 23:19:51 5 OFFLINE WRONG RESETLOGS NO
9 OFFLINE 9 9 1 2021-08-05 23:16:06 212684 2021-08-05 23:19:51 5 OFFLINE WRONG RESETLOGS NO
10 OFFLINE 10 10 1 2021-08-05 23:16:06 212684 2021-08-05 23:19:51 5 OFFLINE WRONG RESETLOGS NO
10 rows selected.
SQL>
复制SQL> alter database datafile 5,6,7,8,9,10 online;
Database altered.
SQL> select FILE#,STATUS,TS#,RFILE#,RESETLOGS_CHANGE#,RESETLOGS_TIME,
2 CHECKPOINT_CHANGE#,CHECKPOINT_TIME,CHECKPOINT_COUNT,status,ERROR,RECOVER,FUZZY from v$datafile_header;
FILE# STATUS TS# RFILE# RESETLOGS_CHANGE# RESETLOGS_TIME CHECKPOINT_CHANGE# CHECKPOINT_TIME CHECKPOINT_COUNT STATUS ERROR REC FUZ
-------------------- ------- -------------------- -------------------- -------------------- ------------------- -------------------- ------------------- -------------------- ------- -------------------- --- ---
1 ONLINE 0 1 212685 2021-08-05 23:20:52 1613802 2021-08-05 23:24:57 19 ONLINE NO NO
2 ONLINE 1 2 212685 2021-08-05 23:20:52 1613802 2021-08-05 23:24:57 19 ONLINE NO NO
3 ONLINE 2 3 212685 2021-08-05 23:20:52 1613802 2021-08-05 23:24:57 19 ONLINE NO NO
4 ONLINE 4 4 212685 2021-08-05 23:20:52 1613802 2021-08-05 23:24:57 19 ONLINE NO NO
5 ONLINE 5 5 1 2021-08-05 23:16:06 212684 2021-08-05 23:19:51 5 ONLINE WRONG RESETLOGS NO
6 ONLINE 6 6 1 2021-08-05 23:16:06 212684 2021-08-05 23:19:51 5 ONLINE WRONG RESETLOGS NO
7 ONLINE 7 7 1 2021-08-05 23:16:06 212684 2021-08-05 23:19:51 5 ONLINE WRONG RESETLOGS NO
8 ONLINE 8 8 1 2021-08-05 23:16:06 212684 2021-08-05 23:19:51 5 ONLINE WRONG RESETLOGS NO
9 ONLINE 9 9 1 2021-08-05 23:16:06 212684 2021-08-05 23:19:51 5 ONLINE WRONG RESETLOGS NO
10 ONLINE 10 10 1 2021-08-05 23:16:06 212684 2021-08-05 23:19:51 5 ONLINE WRONG RESETLOGS NO
10 rows selected.
SQL> recover database using backup controlfile;
ORA-00279: change 212685 generated at 08/05/2021 23:19:51 needed for thread 1
ORA-00289: suggestion : /arch1/1_1_1079824852.dbf
ORA-00280: change 212685 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> select FILE#,STATUS,TS#,RFILE#,RESETLOGS_CHANGE#,RESETLOGS_TIME,
2 CHECKPOINT_CHANGE#,CHECKPOINT_TIME,CHECKPOINT_COUNT,status,ERROR,RECOVER,FUZZY from v$datafile_header;
FILE# STATUS TS# RFILE# RESETLOGS_CHANGE# RESETLOGS_TIME CHECKPOINT_CHANGE# CHECKPOINT_TIME CHECKPOINT_COUNT STATUS ERROR REC FUZ
-------------------- ------- -------------------- -------------------- -------------------- ------------------- -------------------- ------------------- -------------------- ------- -------------------- --- ---
1 ONLINE 0 1 212685 2021-08-05 23:20:52 1613802 2021-08-05 23:24:57 19 ONLINE NO
2 ONLINE 1 2 212685 2021-08-05 23:20:52 1613802 2021-08-05 23:24:57 19 ONLINE NO
3 ONLINE 2 3 212685 2021-08-05 23:20:52 1613802 2021-08-05 23:24:57 19 ONLINE NO
4 ONLINE 4 4 212685 2021-08-05 23:20:52 1613802 2021-08-05 23:24:57 19 ONLINE NO
5 ONLINE 5 5 212685 2021-08-05 23:20:52 212685 2021-08-05 23:19:51 5 ONLINE NO
6 ONLINE 6 6 212685 2021-08-05 23:20:52 212685 2021-08-05 23:19:51 5 ONLINE NO
7 ONLINE 7 7 212685 2021-08-05 23:20:52 212685 2021-08-05 23:19:51 5 ONLINE NO
8 ONLINE 8 8 212685 2021-08-05 23:20:52 212685 2021-08-05 23:19:51 5 ONLINE NO
9 ONLINE 9 9 212685 2021-08-05 23:20:52 212685 2021-08-05 23:19:51 5 ONLINE NO
10 ONLINE 10 10 212685 2021-08-05 23:20:52 212685 2021-08-05 23:19:51 5 ONLINE NO
10 rows selected.
SQL>
复制2.3 恢复验证
[oracle@sourcedb ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Aug 5 23:46:36 2021
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> startup mount force pfile='p.ora'
ORACLE instance started.
Total System Global Area 1202556928 bytes
Fixed Size 2252704 bytes
Variable Size 385876064 bytes
Database Buffers 805306368 bytes
Redo Buffers 9121792 bytes
Database mounted.
SQL> show parameter _allow_resetlogs_corruption
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_allow_resetlogs_corruption boolean TRUE
SQL> alter database open resetlogs;
Database altered.
SQL> select count(1) from test1;
COUNT(1)
----------
13518
SQL>
复制最后修改时间：2021-09-08 14:53:47
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
评论
相关阅读
【纯干货】Oracle 19C RU 19.27 发布，如何快速升级和安装？
Lucifer三思而后行
766次阅读
2025-04-18 14:18:38
Oracle RAC 一键安装翻车？手把手教你如何排错！
Lucifer三思而后行
649次阅读
2025-04-15 17:24:06
Oracle数据库一键巡检并生成HTML结果，免费脚本速来下载!
陈举超
575次阅读
2025-04-20 10:07:02
【ORACLE】你以为的真的是你以为的么?--ORA-38104: Columns referenced in the ON Clause cannot be updated
DarkAthena
526次阅读
2025-04-22 00:13:51
【活动】分享你的压箱底干货文档，三篇解锁进阶奖励！
墨天轮编辑部
521次阅读
2025-04-17 17:02:24
【ORACLE】记录一些ORACLE的merge into语句的BUG
DarkAthena
499次阅读
2025-04-22 00:20:37
一页概览：Oracle GoldenGate
甲骨文云技术
484次阅读
2025-04-30 12:17:56
火焰图--分析复杂SQL执行计划的利器
听见风的声音
449次阅读
2025-04-17 09:30:30
3月“墨力原创作者计划”获奖名单公布
墨天轮编辑部
381次阅读
2025-04-15 14:48:05
OR+DBLINK的关联SQL优化思路
布衣
374次阅读
2025-05-05 19:28:36
