51工具盒子说明:
上一篇测试如果Redo、Undo、Control、SYSAUX、USER文件全部丢失了，无法正常启动数据库，还没找到方法，链接如下:
Oracle 丢失redo、undo、control、sysaux文件，如何启库？ chenjuchao，Oracle 丢失redo、undo、control、sysaux文件，如何启库？
这篇测试，如果丢失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/aSQL> update cjc.t1 set name='cjc' where id=1;1 row updated.另一个窗口[oracle@cjc-db-01 ~]$ ps -ef|grep pmon|grep -v greporacle 18809 1 0 11:47 ? 00:00:00 ora_pmon_cjc
[oracle@cjc-db-01 ~]$ kill -9 18809
对应告警日志:
*
*
*
*
*
*
*
*
*
Sat Aug 17 13:20:24 2024CJQ0 started with pid=27, OS id=18876 Sat Aug 17 13:22:20 2024PSP0 (ospid: 18809): terminating the instance due to error 472Sat Aug 17 13:22:20 2024System 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.trcDumping 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 -lrthtotal 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.dbfdrwxr-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 292LOGFILE 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 512DATAFILE '/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 immediateSQL> startup nomount SQL> @ctl02.sqlControl 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 consistentORA-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 errorsORA-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 1ORA-00289: suggestion : /arch/cjc_1_4_1177245832.arcORA-00280: change 966875 for thread 1 is in sequence #4
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}CANCELMedia recovery cancelled.
[oracle@cjc-db-01 dbs]$ ls -lrth /arch/cjc_1_4_1177245832.arcls: cannot access /arch/cjc_1_4_1177245832.arc: No such file or directory
对应告警日志:
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
Sat Aug 17 13:26:44 2024ALTER DATABASE RECOVER database Media Recovery StartSerial Media Recovery startedMedia Recovery failed with error 1610ORA-283 signalled during: ALTER DATABASE RECOVER database ...ALTER DATABASE RECOVER database USING BACKUP CONTROLFILE Media Recovery StartSerial Media Recovery startedWARNING! Recovering data file 1 from a fuzzy file. If not the current fileit 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 fileit 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 fileit 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 2024ALTER DATABASE RECOVER CANCEL Media Recovery CanceledCompleted: ALTER DATABASE RECOVER CANCEL
SQL> alter database open RESETLOGS;alter database open RESETLOGS*ERROR at line 1:ORA-01113: file 1 needs media recoveryORA-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 immediateSQL> startup nomountSQL> @ctl02.sqlControl file created.
可以正常启动
*
*
SQL> alter database open resetlogs;Database altered.
告警日志:
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
Sat Aug 17 13:28:56 2024Starting background process CJQ0Sat Aug 17 13:28:56 2024CJQ0 started with pid=25, OS id=19651 Completed: alter database open resetlogsSat Aug 17 13:28:59 2024Errors in file /oracle/app/oracle/diag/rdbms/cjc/cjc/trace/cjc_j000_19664.trc:ORA-12012: error on auto execute of job 4002ORA-00376: file 2 cannot be read at this timeORA-01111: name for data file 2 is unknown - rename to correct fileORA-01110: data file 2: '/oracle/app/oracle/product/11.2/db/dbs/MISSING00002'ORA-06512: at "APEX_030200.WWV_FLOW_PLATFORM", line 9ORA-06512: at line 1Sat Aug 17 13:29:27 2024Sweep [inc][115393]: completedSweep [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 VersionsFor Oracle 10.2.0.5 onwards, use version 04Aug23 in file hcheck.sqlFor Oracle 9i to 10.2.0.4, use version 07MAY18 in file hcheck_10204.sqlFor Oracle 8i create package hOut and use version 2.02 in hcheck8i.sql
SQL> spool hcheck.logSQL> @hcheck.sqlHCheck Version 04AUG23 on 17-AUG-2024 13:34:30----------------------------------------------Catalog Version 11.2.0.4.0 (1102000400)db_name: CJC
Catalog FixedProcedure Name Version Vs Release TimestampResult------------------------------ ... ---------- -- ---------- --------------------.- 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=t1Export: Release 11.2.0.4.0 - Production on Sat Aug 17 13:32:52 2024Copyright (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 ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsExport done in AL32UTF8 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path .... . exporting table T1 3 rows exportedExport terminated successfully without warnings.
