说明: 上一篇测试如果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
