记录一次 ORA-30012 ORA-01092错误
由于修改归档路径错了导致数据库startup的时候在spfile里读取LOG_ARCHIVE_DEST错误
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORA-16032: parameter LOG_ARCHIVE_DEST destination string cannot be translated
ORA-07286: sksagdi: cannot obtain device information.
Solaris-AMD64 Error: 2: No such file or directory
于是找了个老的init.ora的pfile来启动数据库
oracle@oracle:/oracle/diag/rdbms/sem/SEM/trace$ ls -l /oracle/admin/SEM/pfile/init.ora.622201365825
-rw-r----- 1 oracle oinstall 2020 7月 22日 05:43 /oracle/admin/SEM/pfile/init.ora.622201365825
startup数据库
SQL> startup pfile='/oracle/admin/SEM/pfile/init.ora.622201365825'
ORACLE 例程已经启动。
Total System Global Area 1670221824 bytes
Fixed Size 2361784 bytes
Variable Size 1073743432 bytes
Database Buffers 587202560 bytes
Redo Buffers 6914048 bytes
数据库装载完毕。
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-30012: undo tablespace 'UNDOTBS1' does not exist or of wrong type
进程 ID: 4613
会话 ID: 242 序列号: 3
启动失败出现了这个错误然后区看trace日志
alert_SID.log中有如下内容:
Undo initialization errored: err:30012 serial:0 start:1630156312 end:1630156420 diff:108 ms (0.1 seconds) //初始化undo错误
Wed Aug 21 03:26:03 2013
Errors in file /oracle/diag/rdbms/sem/SEM/trace/SEM_ora_4613.trc: //错误在/oracle/diag/rdbms/sem/SEM/trace/SEM_ora_4613.trc这个文件中
ORA-30012: 还原表空间 'UNDOTBS1' 不存在或类型不正确
由以上内容得知详细错误得查阅trace文件/oracle/diag/rdbms/sem/SEM/trace/SEM_ora_4613.trc
Using 45 overflow buffers per recovery slave
Thread 1 checkpoint: logseq 98, block 2, scn 5016786 #检查点记录的scn是5016786
on-disk rba: logseq 95, block 22523, scn 4956772 #而在redo log中记录的是4956772
start recovery at logseq 98, block 2, scn 5016786 #所以需要将redo恢复到scn 5016786的状态
需要对数据库进行还原(recovery),在对undo进行还原的时候出现如下错误
*** 2013-08-21 03:26:03.421
ORA-30012: 还原表空间 'UNDOTBS1' 不存在或类型不正确
ORA-30012: 还原表空间 'UNDOTBS1' 不存在或类型不正确
然后启动数据库到mount的状态看下undo表空间的情况
SQL> select t1.name as TBNM,t2.name as DTNM from v$tablespace t1, v$datafile t2 where t1.ts# = t2.ts# and t1.name like '%UNDO%' and t2.name like '%UNDO%';TBNM
--------------------------------------------------------------------------------
DTNM
--------------------------------------------------------------------------------
UNDOTP
/oracle/oradata/SEM/UNDOTP01.dbf
原来我的undo表空间叫UNDOTP,而oracle去找UNDOTBS1这个表空间了 看来找来的这个pfile有些不靠谱 修改pfile继续startup
oracle@oracle:/oracle/12.1/dbs$ sed s/UNDOTBS1/UNDOTP/g /oracle/admin/SEM/pfile/init.ora.622201365825 >initSEM.ora //将UNDOTBS1替换成UNDOTP导入到/oracle/12.1/dbs/initSEM.ora这个文件中
SQL> startup mount pfile='/oracle/12.1/dbs/initSEM.ora'
ORACLE 例程已经启动。
Total System Global Area 1670221824 bytes
Fixed Size 2361784 bytes
Variable Size 1073743432 bytes
Database Buffers 587202560 bytes
Redo Buffers 6914048 bytes
数据库装载完毕。
SQL> alter database open;
数据库已更改。
SQL> create spfile from pfile='/oracle/12.1/dbs/initSEM.ora';
文件已创建。
SQL> show parameter undo;
NAME TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
temp_undo_enabled boolean
FALSE
undo_management string
AUTO
undo_retention integer
900
undo_tablespace string
UNDOTP
至此数据库就正常open了!
总结:此次失误是由于更改log_archive_dest错误导致spfile无法应用
从$ORACLE_BASE/admin/pfile下找到的init.ora.nnnnnnn是很早很早以前的pfile
而后期我对这个库的undo表空间做过修改,所有导致这个pfile初始化实例时指定了错误的undo表空间
所以才出现了ORA-30012
这个错误的描述是:
30012, 00000, "undo tablespace '%s' does not exist or of wrong type"
// *Cause: the specified undo tablespace does not exist or of the
// wrong type.
// *Action: Correct the tablespace name and reissue the statement.
因此导致这个错误的原因一般是因为文件丢失,误删除,或者存储系统故障等等原因!!!
###############################################
迷途运维随笔
作者:john
转载请注明出处