说明:
上一篇测试如果Redo、Undo、Control、SYSAUX、USER文件全部丢失了，无法正常启动数据库，还没找到方法。
https://mp.weixin.qq.com/s?__biz=MzI5OTY2NzQ5MA==&mid=2247490834&idx=1&sn=89fcf6dd2aaa5d6f245101f11a74d5b4&chksm=ec92445fdbe5cd49b0f8616b91977ee7314747381ab8a83331c2a6789ed98bf87584c1d6fba4&token=1974485438&lang=zh_CN#rd
这篇测试，如果只丢失Redo、Control、SYSAUX、USER文件，保留system01.dbf、undotbs01.dbf、cjc_data01.dbf文件，可以成功启动数据库，导出数据，不需要推进SCN，不需要BBED修改信息。
数据库版本:11.2.0.4.0
测试过程如下:
备份控制文件
SQL> alter database backup controlfile to trace as '/home/oracle/tmp/0817/control02.sql';
查看信息:
SQL> select ts#,file#,name from v$datafile;
TS# FILE# NAME
---------- ---------- --------------------------------------------------
0 1 /oracle/app/oracle/oradata/cjc/system01.dbf
1 2 /oracle/app/oracle/oradata/cjc/sysaux01.dbf
2 3 /oracle/app/oracle/oradata/cjc/undotbs01.dbf
4 4 /oracle/app/oracle/oradata/cjc/users01.dbf
6 5 /oracle/app/oracle/oradata/cjc/cjc01.dbf
SQL> select name from v$controlfile;
NAME
--------------------------------------------------
/oracle/app/oracle/oradata/cjc/control01.ctl
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/oracle/app/oracle/oradata/cjc/redo03.log
/oracle/app/oracle/oradata/cjc/redo02.log
/oracle/app/oracle/oradata/cjc/redo01.log
SQL> select DBID,NAME from v$database;
DBID NAME
---------- --------------------------------------------------
3819394245 CJC
更新数据不提交，直接kill
SQL> conn cjc/a
SQL> update cjc.t1 set name='cjc' where id=1;
1 row updated.
[oracle@cjc-db-01 ~]$ ps -ef|grep pmon|grep -v grep
oracle 18809 1 0 11:47 ? 00:00:00 ora_pmon_cjc
[oracle@cjc-db-01 ~]$ kill -9 18809
对应告警日志:
Sat Aug 17 13:20:24 2024
CJQ0 started with pid=27, OS id=18876
Sat Aug 17 13:22:20 2024
PSP0 (ospid: 18809): terminating the instance due to error 472
Sat Aug 17 13:22:20 2024
System state dump requested by (instance=1, osid=18809 (PSP0)), summary=[abnormal instance termination].
System State dumped to trace file /oracle/app/oracle/diag/rdbms/cjc/cjc/trace/cjc_diag_18818_20240817132220.trc
Dumping diagnostic data in directory=[cdmp_20240817132220], requested by (instance=1, osid=18809 (PSP0)), summary=[abnormal instance termination].
Instance terminated by PSP0, pid = 18809
删除文件
[oracle@cjc-db-01 oradata]$ cd /oracle/app/oracle/oradata/cjc/
[oracle@cjc-db-01 cjc]$ mkdir tmp
[oracle@cjc-db-01 cjc]$ mv control01.ctl tmp/
[oracle@cjc-db-01 cjc]$ mv redo0*.log tmp/
[oracle@cjc-db-01 cjc]$ mv sysaux01.dbf tmp/
[oracle@cjc-db-01 cjc]$ mv temp01.dbf tmp/
[oracle@cjc-db-01 cjc]$ mv users01.dbf tmp/
只保留了 system01.dbf、undotbs01.dbf、cjc01.dbf文件。
[oracle@cjc-db-01 cjc]$ ls -lrth
total 821M
-rw-r----- 1 oracle oinstall 741M Aug 17 13:20 system01.dbf
-rw-r----- 1 oracle oinstall 71M Aug 17 13:20 undotbs01.dbf
-rw-r----- 1 oracle oinstall 11M Aug 17 13:20 cjc01.dbf
drwxr-xr-x 2 oracle oinstall 138 Aug 17 13:23 tmp
重建控制文件
[oracle@cjc-db-01 0817]$ vi ctl02.sql
CREATE CONTROLFILE REUSE DATABASE "CJC" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/oracle/app/oracle/oradata/cjc/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/oracle/app/oracle/oradata/cjc/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/oracle/app/oracle/oradata/cjc/redo03.log' SIZE 50M BLOCKSIZE 512
DATAFILE
'/oracle/app/oracle/oradata/cjc/system01.dbf',
'/oracle/app/oracle/oradata/cjc/undotbs01.dbf',
'/oracle/app/oracle/oradata/cjc/cjc01.dbf'
CHARACTER SET AL32UTF8;
重启
SQL> shutdown immediate
SQL> startup nomount
SQL> @ctl02.sql
Control file created.
SQL> select status from v$instance;
STATUS
------------
MOUNTED
启动数据库
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: '/oracle/app/oracle/oradata/cjc/system01.dbf'
SQL> select file#,status,CHECKPOINT_CHANGE# from v$datafile;
FILE# STATUS CHECKPOINT_CHANGE#
---------- ------- ------------------
1 SYSTEM 966875
3 RECOVER 966875
5 RECOVER 966875
SQL> select file#,status,CHECKPOINT_CHANGE#,CHECKPOINT_COUNT from v$datafile_header;
FILE# STATUS CHECKPOINT_CHANGE# CHECKPOINT_COUNT
---------- ------- ------------------ ----------------
1 ONLINE 966875 96
3 ONLINE 966875 17
5 ONLINE 966875 8
SQL> select group#,thread#,SEQUENCE#,ARCHIVED,STATUS,FIRST_CHANGE#,NEXT_CHANGE# from v$log;
GROUP# THREAD# SEQUENCE# ARC STATUS FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- ---------- --- ---------------- ------------- ------------
1 1 0 YES UNUSED 0 0
3 1 0 YES CURRENT 0 0
2 1 0 YES UNUSED 0 0
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
SQL> recover database USING BACKUP CONTROLFILE;
ORA-00279: change 966875 generated at 08/17/2024 13:20:23 needed for thread 1
ORA-00289: suggestion : /arch/cjc_1_4_1177245832.arc
ORA-00280: change 966875 for thread 1 is in sequence #4
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
[oracle@cjc-db-01 dbs]$ ls -lrth /arch/cjc_1_4_1177245832.arc
ls: cannot access /arch/cjc_1_4_1177245832.arc: No such file or directory
对应告警日志:
Sat Aug 17 13:26:44 2024
ALTER DATABASE RECOVER database
Media Recovery Start
Serial Media Recovery started
Media Recovery failed with error 1610
ORA-283 signalled during: ALTER DATABASE RECOVER database ...
ALTER DATABASE RECOVER database USING BACKUP CONTROLFILE
Media Recovery Start
Serial Media Recovery started
WARNING! Recovering data file 1 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 3 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 5 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
ORA-279 signalled during: ALTER DATABASE RECOVER database USING BACKUP CONTROLFILE ...
Sat Aug 17 13:26:54 2024
ALTER DATABASE RECOVER CANCEL
Media Recovery Canceled
Completed: ALTER DATABASE RECOVER CANCEL
SQL> alter database open RESETLOGS;
alter database open RESETLOGS
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/oracle/app/oracle/oradata/cjc/system01.dbf'
添加参数
[oracle@cjc-db-01 dbs]$ vi initcjc.ora
*._allow_resetlogs_corruption=TRUE
SQL> shutdown immediate
SQL> startup nomount
SQL> @ctl02.sql
Control file created.
可以正常启动
SQL> alter database open resetlogs;
Database altered.
告警日志:
Sat Aug 17 13:28:56 2024
Starting background process CJQ0
Sat Aug 17 13:28:56 2024
CJQ0 started with pid=25, OS id=19651
Completed: alter database open resetlogs
Sat Aug 17 13:28:59 2024
Errors in file /oracle/app/oracle/diag/rdbms/cjc/cjc/trace/cjc_j000_19664.trc:
ORA-12012: error on auto execute of job 4002
ORA-00376: file 2 cannot be read at this time
ORA-01111: name for data file 2 is unknown - rename to correct file
ORA-01110: data file 2: '/oracle/app/oracle/product/11.2/db/dbs/MISSING00002'
ORA-06512: at "APEX_030200.WWV_FLOW_PLATFORM", line 9
ORA-06512: at line 1
Sat Aug 17 13:29:27 2024
Sweep [inc][115393]: completed
Sweep [inc2][115393]: completed
查看
SQL> select ts#,file#,name,status from v$datafile;
TS# FILE# NAME STATUS
---------- ---------- ------------------------------------------------------- -------
0 1 /oracle/app/oracle/oradata/cjc/system01.dbf SYSTEM
1 2 /oracle/app/oracle/product/11.2/db/dbs/MISSING00002 RECOVER
2 3 /oracle/app/oracle/oradata/cjc/undotbs01.dbf ONLINE
4 4 /oracle/app/oracle/product/11.2/db/dbs/MISSING00004 RECOVER
6 5 /oracle/app/oracle/oradata/cjc/cjc01.dbf ONLINE
SQL> select file#,status,CHECKPOINT_CHANGE# from v$datafile;
FILE# STATUS CHECKPOINT_CHANGE#
---------- ------- ------------------
1 SYSTEM 967285
2 RECOVER 0
3 ONLINE 967285
4 RECOVER 0
5 ONLINE 967285
SQL> select file#,status,CHECKPOINT_CHANGE#,CHECKPOINT_COUNT from v$datafile_header;
FILE# STATUS CHECKPOINT_CHANGE# CHECKPOINT_COUNT
---------- ------- ------------------ ----------------
1 ONLINE 967285 100
2 OFFLINE 0 0
3 ONLINE 967285 21
4 OFFLINE 0 0
5 ONLINE 967285 12
检查数据
SQL> select * from cjc.t1;
ID NAME
---------- -------------------------------------------------------
1 aaa
2 bbb
3 ccc
检查数据字典一致性
Primary Note: Troubleshooting Oracle Data Dictionary (Doc ID 1506140.1)
hcheck.sql - Script to Check Data Dictionary for Known Problems (Doc ID 136697.1)
Current Versions
For Oracle 10.2.0.5 onwards, use version 04Aug23 in file hcheck.sql
For Oracle 9i to 10.2.0.4, use version 07MAY18 in file hcheck_10204.sql
For Oracle 8i create package hOut and use version 2.02 in hcheck8i.sql
SQL> spool hcheck.log
SQL> @hcheck.sql
HCheck Version 04AUG23 on 17-AUG-2024 13:34:30
----------------------------------------------
Catalog Version 11.2.0.4.0 (1102000400)
db_name: CJC
Catalog Fixed
Procedure Name Version Vs Release Timestamp
Result
------------------------------ ... ---------- -- ---------- --------------
------
.- LobNotInObj ... 1102000400 <= *All Rel* 08/17 13:34:30 PASS
.- MissingOIDOnObjCol ... 1102000400 <= *All Rel* 08/17 13:34:30 PASS
.- SourceNotInObj ... 1102000400 <= *All Rel* 08/17 13:34:30 PASS
.- OversizedFiles ... 1102000400 <= *All Rel* 08/17 13:34:31 PASS
.- PoorDefaultStorage ... 1102000400 <= *All Rel* 08/17 13:34:31 PASS
.- PoorStorage ... 1102000400 <= *All Rel* 08/17 13:34:31 PASS
.- TabPartCountMismatch ... 1102000400 <= *All Rel* 08/17 13:34:31 PASS
.- OrphanedTabComPart ... 1102000400 <= *All Rel* 08/17 13:34:31 PASS
.- MissingSum$ ... 1102000400 <= *All Rel* 08/17 13:34:31 PASS
.- MissingDir$ ... 1102000400 <= *All Rel* 08/17 13:34:31 PASS
.- DuplicateDataobj ... 1102000400 <= *All Rel* 08/17 13:34:31 PASS
.- ObjSynMissing ... 1102000400 <= *All Rel* 08/17 13:34:32 PASS
.- ObjSeqMissing ... 1102000400 <= *All Rel* 08/17 13:34:32 PASS
.- OrphanedUndo ... 1102000400 <= *All Rel* 08/17 13:34:32 PASS
.- OrphanedIndex ... 1102000400 <= *All Rel* 08/17 13:34:32 PASS
.- OrphanedIndexPartition ... 1102000400 <= *All Rel* 08/17 13:34:32 PASS
.- OrphanedIndexSubPartition ... 1102000400 <= *All Rel* 08/17 13:34:32 PASS
.- OrphanedTable ... 1102000400 <= *All Rel* 08/17 13:34:32 PASS
.- OrphanedTablePartition ... 1102000400 <= *All Rel* 08/17 13:34:32 PASS
.- OrphanedTableSubPartition ... 1102000400 <= *All Rel* 08/17 13:34:32 PASS
.- MissingPartCol ... 1102000400 <= *All Rel* 08/17 13:34:32 PASS
.- OrphanedSeg$ ... 1102000400 <= *All Rel* 08/17 13:34:32 PASS
.- OrphanedIndPartObj# ... 1102000400 <= *All Rel* 08/17 13:34:32 PASS
.- DuplicateBlockUse ... 1102000400 <= *All Rel* 08/17 13:34:32 PASS
.- FetUet ... 1102000400 <= *All Rel* 08/17 13:34:32 PASS
.- Uet0Check ... 1102000400 <= *All Rel* 08/17 13:34:32 PASS
.- SeglessUET ... 1102000400 <= *All Rel* 08/17 13:34:32 PASS
.- BadInd$ ... 1102000400 <= *All Rel* 08/17 13:34:32 PASS
.- BadTab$ ... 1102000400 <= *All Rel* 08/17 13:34:32 PASS
.- BadIcolDepCnt ... 1102000400 <= *All Rel* 08/17 13:34:32 PASS
.- ObjIndDobj ... 1102000400 <= *All Rel* 08/17 13:34:32 PASS
.- TrgAfterUpgrade ... 1102000400 <= *All Rel* 08/17 13:34:32 PASS
.- ObjType0 ... 1102000400 <= *All Rel* 08/17 13:34:32 PASS
.- BadOwner ... 1102000400 <= *All Rel* 08/17 13:34:32 PASS
.- StmtAuditOnCommit ... 1102000400 <= *All Rel* 08/17 13:34:32 PASS
.- BadPublicObjects ... 1102000400 <= *All Rel* 08/17 13:34:32 PASS
.- BadSegFreelist ... 1102000400 <= *All Rel* 08/17 13:34:32 PASS
.- BadDepends ... 1102000400 <= *All Rel* 08/17 13:34:32 PASS
.- CheckDual ... 1102000400 <= *All Rel* 08/17 13:34:32 PASS
.- ObjectNames ... 1102000400 <= *All Rel* 08/17 13:34:33 PASS
.- BadCboHiLo ... 1102000400 <= 1202000000 08/17 13:34:33 PASS
.- ChkIotTs ... 1102000400 <= *All Rel* 08/17 13:34:33 PASS
.- NoSegmentIndex ... 1102000400 <= *All Rel* 08/17 13:34:33 PASS
.- BadNextObject ... 1102000400 <= *All Rel* 08/17 13:34:33 PASS
.- DroppedROTS ... 1102000400 <= *All Rel* 08/17 13:34:33 PASS
.- FilBlkZero ... 1102000400 <= *All Rel* 08/17 13:34:33 PASS
.- DbmsSchemaCopy ... 1102000400 <= *All Rel* 08/17 13:34:33 PASS
.- OrphanedObjError ... 1102000400 > 1102000000 08/17 13:34:33 PASS
.- ObjNotLob ... 1102000400 <= *All Rel* 08/17 13:34:33 PASS
.- MaxControlfSeq ... 1102000400 <= *All Rel* 08/17 13:34:33 PASS
.- SegNotInDeferredStg ... 1102000400 > 1102000000 08/17 13:34:33 PASS
.- SystemNotRfile1 ... 1102000400 > 902000000 08/17 13:34:33 PASS
.- DictOwnNonDefaultSYSTEM ... 1102000400 <= *All Rel* 08/17 13:34:33 PASS
.- OrphanTrigger ... 1102000400 <= *All Rel* 08/17 13:34:33 PASS
.- ObjNotTrigger ... 1102000400 <= *All Rel* 08/17 13:34:33 PASS
---------------------------------------
17-AUG-2024 13:34:33 Elapsed: 3 secs
---------------------------------------
Found 0 potential problem(s) and 0 warning(s)
PL/SQL procedure successfully completed.
Statement processed.
Complete output is in trace file:
/oracle/app/oracle/diag/rdbms/cjc/cjc/trace/cjc_ora_20012_HCHECK.trc
SQL> spool off
可以正常导出数据
[oracle@cjc-db-01 0817]$ exp cjc/a file=/home/oracle/tmp/t1.dmp log=/home/oracle/tmp/t1.log tables=t1
Export: Release 11.2.0.4.0 - Production on Sat Aug 17 13:32:52 2024
Copyright (c) 1982, 2011, Oracle and/or its affiliates. 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
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table T1 3 rows exported
Export terminated successfully without warnings.
###chenjuchao 20240817###
欢迎关注我的公众号IT小Chen
最后修改时间：2024-08-19 10:00:55
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
