迁移脚本的日志中报错RMAN-06571: datafile 78 does not have recoverable copy,经查看发现78号文件曾经被offline drop掉。于是重建控制文件,在控制文件中把78号文件去掉,重建控制后,数据库能够mount,mount后数据文件是一致,但是open 时会报错ora-600,异常宕掉。
SYS@mydbtst> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [25025], [78], [], [], [], [], [],
[], [], [], [], []
Process ID: 6028
Session ID: 521 Serial number: 15
经检查,这个78号文件是undo的一个文件。上述的处理方法,对于处理一般的数据文件是可行的,但是对于undo文件的问题,就不能用上述这个方法处理了。应该用下面的处理方法,具体的处理方法如下:
1. 加上管理方式为manual:
……
*.streams_pool_size=134217728
*.undo_retention=3600
*.undo_tablespace='UNDOTBS1'
*.undo_management='manual'
~
2. 启动之后,做recovery:
SYS@mydbtst> startup pfile='/tmp/pfile_bak.ora';
ORACLE instance started.
Total System Global Area 2522189824 bytes
Fixed Size 2230912 bytes
Variable Size 1157629312 bytes
Database Buffers 1342177280 bytes
Redo Buffers 20152320 bytes
Database mounted.
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '+DG_DATA001/mydbtst/datafile/system.453.813666469'
SYS@mydbtst> recover database using backup controlfile;
ORA-00279: change 9436796441761 generated at 08/01/2014 10:07:10 needed for
thread 1
ORA-00289: suggestion : +FRA_SMALL_MDG
ORA-15173: entry 'ARCHIVELOG' does not exist in directory 'mydbtst'
ORA-00280: change 9436796441761 for thread 1 is in sequence #4 <<<<<<<需要sequence # 4的日志,查v$log和v$logfile之后,确认哪个redo log放入
Specify log: {
+DATA_SMALL_MDG/mydbtst/onlinelog/group_1.1156.854401821
Log applied.
Media recovery complete.
SYS@mydbtst>
SYS@mydbtst> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SYS@mydbtst> alter database open resetlogs;
Database altered.
SYS@mydbtst>
SYS@mydbtst>
3. 新建新的undo
SYS@mydbtst> create undo tablespace undo_new datafile '+DG_DATA001/mydbtst/datafile/undo_new01.dbf' size 200m;
Tablespace created.
SYS@mydbtst>
4. 检查UNDOTBS1的undo segment,幸运的是,我没有发现needs recovery的undo segment,所以后续也不用隐含参数”_corrupted_rollback_segments”跳过need recovery的undo segment来重启了。
SYS@mydbtst> select tablespace_name,segment_name,status from dba_rollback_segs;
TABLESPACE_NAME SEGMENT_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
UNDOTBS1 _SYSSMU1_3981220827$ OFFLINE
UNDOTBS1 _SYSSMU2_2541240231$ OFFLINE
UNDOTBS1 _SYSSMU3_4103266798$ OFFLINE
UNDOTBS1 _SYSSMU4_1110676785$ OFFLINE
UNDOTBS1 _SYSSMU5_3829116805$ OFFLINE
UNDOTBS1 _SYSSMU6_347720470$ OFFLINE
UNDOTBS1 _SYSSMU7_3507999319$ OFFLINE
UNDOTBS1 _SYSSMU8_3681584916$ OFFLINE
UNDOTBS1 _SYSSMU9_630455542$ OFFLINE
UNDOTBS1 _SYSSMU10_2221096320$ OFFLINE
……
TABLESPACE_NAME SEGMENT_NAME STATUS
------------------------------ ------------------------------ ----------------
UNDO_NEW _SYSSMU110_852046607$ OFFLINE
UNDO_NEW _SYSSMU111_1868020771$ OFFLINE
112 rows selected.
SYS@mydbtst>
5. 由于不需要隐含参数跳过,可以直接drop原来的undo:
SYS@mydbtst> drop tablespace UNDOTBS1 including contents and datafiles;
Tablespace dropped.
6. 修改pfile为auto和新的undo_new
cnsz181007:mydbtst > vi pfile_bak.ora
……
*.streams_pool_size=134217728
*.undo_retention=3600
*.undo_tablespace='undo_new'
*.undo_management='auto'
~
7. 用该pfile重启:
cnsz181007:mydbtst > sqlplus "/ as sysdba"
SQL*Plus: Release 11.2.0.3.0 Production on Fri Aug 1 11:59:56 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SYS@mydbtst> startup pfile='/tmp/pfile_bak.ora';
ORACLE instance started.
Total System Global Area 2522189824 bytes
Fixed Size 2230912 bytes
Variable Size 1157629312 bytes
Database Buffers 1342177280 bytes
Redo Buffers 20152320 bytes
Database mounted.
Database opened.
SYS@mydbtst>