- 环境模拟
- 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; - 创建测试表
create table sys.test1 tablespace enmo5 as select * from dba_objects;
- 创建测试表
- 重建控制文件
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.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: {
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>
