Oracle Recovery Tools快速解决SYSAUX表空间异常问题

又一客户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; 表空间已更改。

数据导出成功