暂无图片
暂无图片
14
暂无图片
暂无图片
6
暂无图片
首页 / Oracle 丢失Redo、Control、SYSAUX、USER文件，无备份，成功启库！

Oracle 丢失Redo、Control、SYSAUX、USER文件，无备份，成功启库！

原创 陈举超 2024-08-17
1476

图片.png

说明:

上一篇测试如果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
图片.png

墨力计划oracle
最后修改时间：2024-08-19 10:00:55
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论

夜神月
暂无图片
1月前
评论
暂无图片 0
1月前
暂无图片 点赞
评论
筱悦星辰
暂无图片
6月前
评论
暂无图片 0
运动，不仅能够滋养身体，还能丰盈你的精神世界。唯有身心健康，生活才能充满阳光。养成运动的习惯，并且长期坚持下去，终有一天，你会遇见一个全新的自己。
6月前
暂无图片 点赞
评论
筱悦星辰
暂无图片
6月前
评论
暂无图片 0
运动，不仅能够滋养身体，还能丰盈你的精神世界。唯有身心健康，生活才能充满阳光。养成运动的习惯，并且长期坚持下去，终有一天，你会遇见一个全新的自己。
6月前
暂无图片 点赞
评论
星星之火
暂无图片
6月前
评论
暂无图片 0
生活就像一杯白开水，加上糖，它就是甜的，加上盐，它就是咸的，其中的滋味往往来自我们自己。
6月前
暂无图片 点赞
评论
鲁鲁
暂无图片
7月前
评论
暂无图片 0
7月前
暂无图片 点赞
评论
陈举超
暂无图片 暂无图片
7月前
评论
暂无图片 0
1
7月前
暂无图片 点赞
评论

相关阅读

【专家有话说第五期】在不同年龄段，DBA应该怎样规划自己的职业发展？
墨天轮编辑部
1223次阅读
2025-03-13 11:40:53
Oracle RAC ASM 磁盘组满了，无法扩容怎么在线处理？
Lucifer三思而后行
740次阅读
2025-03-17 11:33:53
Oracle+Deepseek+Dify 实现数据库数据实时分析
bicewow
653次阅读
2025-03-06 09:41:49
【ORACLE】ORACLE19C在19.13版本前的一个严重BUG-24761824
DarkAthena
545次阅读
2025-03-04 14:33:31
Oracle避坑指南｜同名表导出难题：如何精准排除指定用户下的表？
szrsu
503次阅读
2025-03-05 00:42:34
2月“墨力原创作者计划”获奖名单公布
墨天轮编辑部
441次阅读
2025-03-13 14:38:19
Ogg23ai高手必看-MySQL Innodb Cluster跟oracle的亲密接触
曹海峰
438次阅读
2025-03-04 21:56:13
【ORACLE】char类型和sql优化器发生的“错误”反应
DarkAthena
400次阅读
2025-03-04 23:05:01
什么，oracle 主机用户被删了？原来是虚惊一场！
Lucifer三思而后行
394次阅读
2025-03-03 21:12:09
Oracle 如何修改 db_unique_name？强迫症福音！
Lucifer三思而后行
326次阅读
2025-03-12 21:27:56