迁移脚本的日志中报错RMAN-06571: datafile 78 does not have recoverable copy，经查看发现78号文件曾经被offline drop掉。于是重建控制文件，在控制文件中把78号文件去掉，重建控制后，数据库能够mount，mount后数据文件是一致，但是open 时会报错ora-600，异常宕掉。
|
1 2 3 4 5 6 7 8 9 |
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：
|
1 2 3 4 5 6 |
…… *.streams_pool_size=134217728 *.undo_retention=3600 *.undo_tablespace='UNDOTBS1' *.undo_management='manual' ~ |
2. 启动之后，做recovery：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
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: {<RET>=suggested | filename | AUTO | CANCEL} +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
|
1 2 3 4 5 |
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来重启了。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
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：
|
1 2 3 |
SYS@mydbtst> drop tablespace UNDOTBS1 including contents and datafiles; Tablespace dropped. |
6. 修改pfile为auto和新的undo_new
|
1 2 3 4 5 6 7 8 9 |
cnsz181007:mydbtst > vi pfile_bak.ora …… *.streams_pool_size=134217728 *.undo_retention=3600 *.undo_tablespace='undo_new' *.undo_management='auto' ~ |
7. 用该pfile重启：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
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> |