分类目录归档：软件开发

Oracle Recovery Tools快速恢复重建ctl遗漏数据文件故障

发表于 2022 年 10 月 23 日由惜分飞

数据库被强制打开,由于重建ctl把部分文件没有列入其中导致数据库在resetlogs打开之后部分文件异常

Dictionary check beginning
Tablespace 'TEMP' #3 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Tablespace 'TEMP_HRP' #21 found in data dictionary,
but not in the controlfile. Adding to controlfile.
File #19 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00019' in the controlfile.
This file can no longer be recovered so it must be dropped.
File #25 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00025' in the controlfile.
This file can no longer be recovered so it must be dropped.
File #26 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00026' in the controlfile.
This file can no longer be recovered so it must be dropped.
File #27 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00027' in the controlfile.
This file can no longer be recovered so it must be dropped.
File #66 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00066' in the controlfile.
This file can no longer be recovered so it must be dropped.
File #67 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00067' in the controlfile.
This file can no longer be recovered so it must be dropped.
File #68 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00068' in the controlfile.
This file can no longer be recovered so it must be dropped.
File #69 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00069' in the controlfile.
This file can no longer be recovered so it must be dropped.
File #70 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00070' in the controlfile.
This file can no longer be recovered so it must be dropped.
File #91 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00091' in the controlfile.
This file can no longer be recovered so it must be dropped.
File #92 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00092' in the controlfile.
This file can no longer be recovered so it must be dropped.
File #93 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00093' in the controlfile.
This file can no longer be recovered so it must be dropped.
File #94 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00094' in the controlfile.
This file can no longer be recovered so it must be dropped.
File #95 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00095' in the controlfile.
This file can no longer be recovered so it must be dropped.
File #96 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00096' in the controlfile.
This file can no longer be recovered so it must be dropped.
File #97 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00097' in the controlfile.
This file can no longer be recovered so it must be dropped.
File #98 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00098' in the controlfile.
This file can no longer be recovered so it must be dropped.
File #99 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00099' in the controlfile.
This file can no longer be recovered so it must be dropped.
File #100 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00100' in the controlfile.
This file can no longer be recovered so it must be dropped.
File #102 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00102' in the controlfile.
This file can no longer be recovered so it must be dropped.

通过查询恢复之后的v$datafile发现

SQL> select name from v$datafile where status='RECOVER';

NAME
--------------------------------------------------------------------------------

C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00019
C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00025
C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00026
C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00027
C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00066
C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00067
C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00068
C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00069
C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00070
C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00091
C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00092
C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00093
C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00094
C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00095
C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00096
C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00097
C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00098
C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00099
C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00100
C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00102

20 rows selected.

对于这种情况,使用OraRecovery工具,可以快速修复
修复之前个文件头信息

修复之后和库中其他文件一致

然后批量重命名数据文件,recover datafile，online datafile一气呵成

Sun Oct 23 23:27:36 2022
alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00019' to 'E:\oradata\datafile\xifenfei.287.948643517'
Completed: alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00019' to 'E:\oradata\datafile\xifenfei.287.948643517'
alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00025' to 'E:\oradata\datafile\xifenfei.293.968102781'
Completed: alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00025' to 'E:\oradata\datafile\xifenfei.293.968102781'
alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00026' to 'E:\oradata\datafile\xifenfei.294.968102903'
Completed: alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00026' to 'E:\oradata\datafile\xifenfei.294.968102903'
alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00027' to 'E:\oradata\datafile\xifenfei.295.968103023'
Completed: alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00027' to 'E:\oradata\datafile\xifenfei.295.968103023'
alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00066' to 'E:\oradata\datafile\xifenfei.346.1050578857'
Completed: alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00066' to 'E:\oradata\datafile\xifenfei.346.1050578857'
alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00067' to 'E:\oradata\datafile\xifenfei.347.1050578871'
Completed: alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00067' to 'E:\oradata\datafile\xifenfei.347.1050578871'
alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00068' to 'E:\oradata\datafile\xifenfei.348.1050578873'
Completed: alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00068' to 'E:\oradata\datafile\xifenfei.348.1050578873'
alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00069' to 'E:\oradata\datafile\xifenfei.349.1050578875'
Completed: alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00069' to 'E:\oradata\datafile\xifenfei.349.1050578875'
alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00070' to 'E:\oradata\datafile\xifenfei.350.1050578877'
Completed: alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00070' to 'E:\oradata\datafile\xifenfei.350.1050578877'
alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00091' to 'E:\oradata\datafile\xifenfei.371.1081159403'
Completed: alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00091' to 'E:\oradata\datafile\xifenfei.371.1081159403'
alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00092' to 'E:\oradata\datafile\xifenfei.372.1081159409'
Completed: alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00092' to 'E:\oradata\datafile\xifenfei.372.1081159409'
alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00093' to 'E:\oradata\datafile\xifenfei.373.1081159425'
Completed: alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00093' to 'E:\oradata\datafile\xifenfei.373.1081159425'
alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00094' to 'E:\oradata\datafile\xifenfei.374.1081159427'
Completed: alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00094' to 'E:\oradata\datafile\xifenfei.374.1081159427'
alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00095' to 'E:\oradata\datafile\xifenfei.375.1088945947'
Completed: alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00095' to 'E:\oradata\datafile\xifenfei.375.1088945947'
alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00096' to 'E:\oradata\datafile\xifenfei.376.1088945949'
Completed: alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00096' to 'E:\oradata\datafile\xifenfei.376.1088945949'
alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00097' to 'E:\oradata\datafile\xifenfei.377.1088945953'
Completed: alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00097' to 'E:\oradata\datafile\xifenfei.377.1088945953'
alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00098' to 'E:\oradata\datafile\xifenfei.378.1088945955'
Completed: alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00098' to 'E:\oradata\datafile\xifenfei.378.1088945955'
alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00099' to 'E:\oradata\datafile\xifenfei.379.1088945957'
Completed: alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00099' to 'E:\oradata\datafile\xifenfei.379.1088945957'
alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00100' to 'E:\oradata\datafile\xifenfei.380.1100595805'
Completed: alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00100' to 'E:\oradata\datafile\xifenfei.380.1100595805'
alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00102' to 'E:\oradata\datafile\xifenfei.382.1100595821'
Completed: alter database rename file 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00102' to 'E:\oradata\datafile\xifenfei.382.1100595821'
Sun Oct 23 23:30:54 2022
ALTER DATABASE RECOVER  datafile 19,25,26,27,66,67,68,69,70,91,92,93,94,95,96,97,98,99,100,102  
Media Recovery Start
Serial Media Recovery started
Completed: ALTER DATABASE RECOVER  datafile 19,25,26,27,66,67,68,69,70,91,92,93,94,95,96,97,98,99,100,102  
Sun Oct 23 23:31:30 2022
alter database datafile  19,25,26,27,66,67,68,69,70,91,92,93,94,95,96,97,98,99,100,102 online
Completed: alter database datafile  19,25,26,27,66,67,68,69,70,91,92,93,94,95,96,97,98,99,100,102 online

通过OraRecovery工具快速实现MISSING数据文件恢复
软件下载:OraRecovery下载
使用说明:使用说明

发表在小工具 | 标签为 Oracle Recovery Tools, OraRecovery, OraRecovery恢复MISSING000 | 评论关闭

一键修改Oracle SCN工具升级(patch scn)

发表于 2022 年 10 月 6 日由惜分飞

前段时间开发了基于内存修改的patch scn小程序（修改oracle scn小工具(patch scn)），用来修改oracle数据库scn,实现快速调整oracle scn的功能（特别是在一些恢复的时候很有用）,最近对其功能进一步完善，扩展了通过修改oracle controlfile实现调整scn功能,简单测试如下
数据库当前scn情况

C:\Users\XFF>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on 星期四 10月 6 21:50:32 2022

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


连接到:
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> set num 16
SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
      141743308712

SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。

通过工具一键修改oracle scn

启动库查看修改之后scn值

SQL> startup mount;
ORACLE 例程已经启动。

Total System Global Area 6413680640 bytes
Fixed Size                  2293216 bytes
Variable Size            1275068960 bytes
Database Buffers         5117050880 bytes
Redo Buffers               19267584 bytes
数据库装载完毕。
SQL> set num 16
SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
      141743339676

SQL> alter database open;

数据库已更改。

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
      151743339602

数据库启动过程中scn会被改变，在mount的时候未发生改变
Patch_SCN下载:Patch_SCN下载
Patch_SCN使用说明:Patch_SCN使用说明
OraRecovery下载:OraRecovery下载
OraRecovery使用说明:OraRecovery使用说明

发表在小工具 | 标签为修改oracle scn, 修改控制文件scn, 推scn, 调整数据库scn | 评论关闭

Oracle Recovery Tools快速解决sysaux文件不能online问题

发表于 2022 年 8 月 29 日由惜分飞

又一客户sysaux表空间对应的数据文件离线(file 6 为测试表空间数据可以不要)

Tue Jul 26 11:33:41 2022
alter database datafile 2 offline drop
Completed: alter database datafile 2 offline drop
Tue Jul 26 11:35:26 2022
alter database datafile 6 offline drop
Completed: alter database datafile 6 offline drop
Tue Jul 26 11:36:04 2022
ALTER DATABASE OPEN
Beginning crash recovery of 1 threads
 parallel recovery started with 19 processes
Started redo scan
Completed redo scan
 read 14595 KB redo, 954 data blocks need recovery
Started redo application at
 Thread 1: logseq 52560, block 31365
Recovery of Online Redo Log: Thread 1 Group 3 Seq 52560 Reading mem 0
  Mem# 0: D:\XXXX\DATAS\ORADATA\XXXX\REDO03.LOG
Completed redo application of 6.50MB
Completed crash recovery at
 Thread 1: logseq 52560, block 60555, scn 4397986801
 954 data blocks read, 954 data blocks written, 14595 redo k-bytes read
Tue Jul 26 11:36:11 2022
Thread 1 advanced to log sequence 52561 (thread open)
Thread 1 opened at log sequence 52561
  Current log# 1 seq# 52561 mem# 0: D:\XXXX\DATAS\ORADATA\XXXX\REDO01.LOG
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Tue Jul 26 11:36:11 2022
SMON: enabling cache recovery
Successfully onlined Undo Tablespace 2.
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is ZHS16GBK
Tue Jul 26 11:36:14 2022
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Errors in file d:\XXXX\datas\diag\rdbms\XXXX\XXXX\trace\XXXX_ora_8476.trc  (incident=275156):
ORA-25319: 队列表重新分区已中止
Incident details in: d:\XXXX\datas\diag\rdbms\XXXX\XXXX\incident\incdir_275156\XXXX_ora_8476_i275156.trc
error 25319 happened during Queue table repartitioning
Starting background process QMNC
Tue Jul 26 11:36:23 2022
QMNC started with pid=50, OS id=11200 
Tue Jul 26 11:36:23 2022
Trace dumping is performing id=[cdmp_20220726113623]
 XDB UNINITIALIZED: XDB$SCHEMA not accessible 
Tue Jul 26 11:36:27 2022
Completed: ALTER DATABASE OPEN

SQL> select file#,status from v$datafile;

     FILE# STATUS
---------- -------
         1 SYSTEM
         2 OFFLINE
         3 ONLINE
         4 ONLINE
         5 ONLINE
         6 OFFLINE

7月份offline datafile 2,然后open数据库一直运行至今,数据库一直无法进行备份,需要我们进行解决

SQL> archive log list;
数据库日志模式             非存档模式
自动存档             禁用
存档终点            D:\APP\DATAS\product\11.2.0.4\dbhome_1\RDBMS
最早的联机日志序列     55557
当前日志序列           55559

SQL> recover datafile 2;
ORA-00279: 更改 4397905894 (在 07/25/2022 18:26:58 生成) 对于线程 1 是必需的
ORA-00289: 建议:
D:\XXXX\DATAS\FLASH_RECOVERY_AREA\XXXX\ARCHIVELOG\2022_08_29\O1_MF_1_52560_%U_.ARC
ORA-00280: 更改 4397905894 (用于线程 1) 在序列 #52560 中


指定日志: {<RET>=suggested | filename | AUTO | CANCEL}

数据库为非归档,常规方法无法直接online datafile 2,对于这样的情况,使用Oracle Recovery Tools工具,进行快速修改文件头信息

查询文件头信息

SQL> set pages 1000
SQL> set linesize 150
SQL> select ts#,file#,TABLESPACE_NAME,status,
  2  to_char(CREATION_TIME,'yyyy-mm-dd hh24:mi:ss') CREATE_TIME,
  3  to_char(checkpoint_change#,'9999999999999999') "SCN",
  4  to_char(RESETLOGS_CHANGE#,'9999999999999999') "RESETLOGS SCN",FUZZY
  5  from v$datafile_header;

       TS#      FILE# TABLESPACE_NAME                STATUS  CREATE_TIME         SCN               RESETLOGS SCN     FUZ

---------- ---------- ------------------------------ ------- ------------------- ----------------- ----------------- ---

         0          1 SYSTEM                         ONLINE  2010-03-30 10:07:48        4599488977            947455 NO
         1          2 SYSAUX                         ONLINE  2010-03-30 10:07:52        4599488977            947455 YES
         2          3 UNDOTBS1                       ONLINE  2010-03-30 11:07:21        4599488977            947455 NO
         4          4 USERS                          ONLINE  2010-03-30 10:08:04        4599488977            947455 NO
         6          5 XXXX                           ONLINE  2020-05-29 09:45:48        4599488977            947455 NO

并且尝试online datafile 2

SQL> recover datafile 2;
ORA-00283: 恢复会话因错误而取消
ORA-01122: 数据库文件 2 验证失败
ORA-01110: 数据文件 2: 'D:\XXXX\DATAS\ORADATA\XXXX\SYSAUX01.DBF'
ORA-01207: 文件比控制文件更新 - 旧的控制文件

由于ctl中的关于datafile2 的信息没有更新，因此数据文件的信息比ctl中的新,无法正常recover,需要重建ctl

SQL> startup nomount;
ORACLE 例程已经启动。

Total System Global Area 1.3195E+10 bytes
Fixed Size                  2188168 bytes
Variable Size            1.0301E+10 bytes
Database Buffers         2885681152 bytes
Redo Buffers                5738496 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "XXXX" NORESETLOGS  NOARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 2336
  7  LOGFILE
  8    GROUP 1 'D:\XXXX\DATAS\ORADATA\XXXX\REDO01.LOG'  SIZE 50M BLOCKSIZE 512,
  9    GROUP 2 'D:\XXXX\DATAS\ORADATA\XXXX\REDO02.LOG'  SIZE 50M BLOCKSIZE 512,
 10    GROUP 3 'D:\XXXX\DATAS\ORADATA\XXXX\REDO03.LOG'  SIZE 50M BLOCKSIZE 512
 11  DATAFILE
 12    'D:\XXXX\DATAS\ORADATA\XXXX\SYSTEM01.DBF',
 13    'D:\XXXX\DATAS\ORADATA\XXXX\SYSAUX01.DBF',
 14    'D:\XXXX\DATAS\ORADATA\XXXX\UNDOTBS01.DBF',
 15    'D:\XXXX\DATAS\ORADATA\XXXX\USERS01.DBF',
 16    'D:\XXXX\DATAS\ZYSPACE\XXXX.DBF',
 17    'E:\XXXX\DATAS\BACKUP\XXXXX.DBF'
 18  CHARACTER SET ZHS16GBK
 19  ;
CREATE CONTROLFILE REUSE DATABASE "XXXX" NORESETLOGS  NOARCHIVELOG
*
第 1 行出现错误:
ORA-01503: CREATE CONTROLFILE ??
ORA-01229: ???? 2 ??????
ORA-01110: ???? 2: 'D:\XXXX\DATAS\ORADATA\XXXX\SYSAUX01.DBF'

由于redo中信息也不对,重建需要使用resetlogs方式进行

SQL> CREATE CONTROLFILE REUSE DATABASE "XXXX" RESETLOGS  NOARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 2336
  7  LOGFILE
  8    GROUP 1 'D:\XXXX\DATAS\ORADATA\XXXX\REDO01.LOG'  SIZE 50M BLOCKSIZE 512,
  9    GROUP 2 'D:\XXXX\DATAS\ORADATA\XXXX\REDO02.LOG'  SIZE 50M BLOCKSIZE 512,
 10    GROUP 3 'D:\XXXX\DATAS\ORADATA\XXXX\REDO03.LOG'  SIZE 50M BLOCKSIZE 512
 11  DATAFILE
 12    'D:\XXXX\DATAS\ORADATA\XXXX\SYSTEM01.DBF',
 13    'D:\XXXX\DATAS\ORADATA\XXXX\SYSAUX01.DBF',
 14    'D:\XXXX\DATAS\ORADATA\XXXX\UNDOTBS01.DBF',
 15    'D:\XXXX\DATAS\ORADATA\XXXX\USERS01.DBF',
 16    'D:\XXXX\DATAS\ZYSPACE\XXXX.DBF',
 17    'E:\XXXX\DATAS\BACKUP\XXXXX.DBF'
 18  CHARACTER SET ZHS16GBK
 19  ;

控制文件已创建。

后续处理

SQL> alter database datafile 6 offline drop;

数据库已更改。

SQL> recover database using backup controlfile;
ORA-00279: ?? 4599488977 (? 08/29/2022 20:59:25 ??) ???? 1 ????
ORA-00289: ??: D:\XXXX\DATAS\FLASH_RECOVERY_AREA\XXXX\ARCHIVELOG\2022_08_29\O1_MF_1_55279_%U_.ARC
ORA-00280: ?? 4599488977 (???? 1) ??? #55279 ?


指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
D:\XXXX\DATAS\ORADATA\XXXX\REDO01.LOG
已应用的日志。
完成介质恢复。
SQL> alter database open resetlogs;

数据库已更改。

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE 'D:\XXXX\DATAS\ORADATA\XXXX\TEMP01.DBF' REUSE;

表空间已更改。

数据导出成功

发表在小工具 | 标签为 Oracle Recovery Tools, sysaux offline, sysaux online | 评论关闭

17813235971

QQ 咨询

分类目录归档：软件开发

Oracle Recovery Tools快速恢复重建ctl遗漏数据文件故障

一键修改Oracle SCN工具升级(patch scn)

Oracle Recovery Tools快速解决sysaux文件不能online问题

文章分类

最近发表

分类目录归档：软件开发

Oracle Recovery Tools快速恢复重建ctl遗漏数据文件故障

一键修改Oracle SCN工具升级(patch scn)

Oracle Recovery Tools快速解决sysaux文件不能online问题

标签云

文章分类

最近发表