联系：手机/微信(+86 17813235971) QQ(107644445)
标题：Oracle Recovery Tools快速解决sysaux文件不能online问题
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
又一客户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;
表空间已更改。
数据导出成功
