ORA-01552: 非系统表空间 'TEMP' 不能使用系统回退段

由于修改系统初始化参数失败导致spfile没法用了，测试系统也没有备份的pfile

所以就找了个很早很早的来startup数据库

过程中出现了ORA-30012: undo tablespace 'UNDOTBS1' does not exist or of wrong type

于是将修改undo参数undo_management=manual 然后实例成功startup之后

查看我的数据库用的undo是UNDOTP这个表空间

然后再将undo_management=auto，关闭实例之后将pfile中的undo_tablespace参数改成

undo_tablespace=UNDOTP

成功启动实例

 

然后今天周一查了下alert日志，就是这个结果！

环境solaris11_amd64

oracle 12cR1

alert.log内容入下：

Thread 1 advanced to log sequence 113 (LGWR switch)
  Current log# 2 seq# 113 mem# 0: /oracle/oradata/SEM/redo02.log
Wed Aug 21 16:41:22 2013
Archived Log entry 6 added for thread 1 sequence 112 ID 0xb933572f dest 1:
Wed Aug 21 17:43:15 2013
Warning: VKTM detected a time drift.
Time drifts can result in an unexpected behavior such as time-outs. Please check trace file for more details.
Wed Aug 21 19:06:31 2013
Warning: VKTM detected a time drift.
Time drifts can result in an unexpected behavior such as time-outs. Please check trace file for more details.
Wed Aug 21 20:49:06 2013
Warning: VKTM detected a time drift.
Time drifts can result in an unexpected behavior such as time-outs. Please check trace file for more details.
Wed Aug 21 21:00:11 2013
Thread 1 advanced to log sequence 114 (LGWR switch)
  Current log# 3 seq# 114 mem# 0: /oracle/oradata/SEM/redo03.log
Wed Aug 21 21:00:13 2013
Archived Log entry 7 added for thread 1 sequence 113 ID 0xb933572f dest 1:
Wed Aug 21 22:00:00 2013
Setting Resource Manager plan SCHEDULER[0x420D]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Wed Aug 21 22:00:00 2013
Starting background process VKRM
Wed Aug 21 22:00:00 2013
VKRM started with pid=47, OS id=21503
Wed Aug 21 22:00:02 2013
Errors in file /oracle/diag/rdbms/sem/SEM/trace/SEM_m002_21507.trc:
ORA-01552: 非系统表空间 'TEMP' 不能使用系统回退段

下边附上trc文件：

*** 2013-08-26 04:11:37.584
*** SESSION ID:(252.34951) 2013-08-26 04:11:37.584
*** CLIENT ID:() 2013-08-26 04:11:37.584
*** SERVICE NAME:(SYS$BACKGROUND) 2013-08-26 04:11:37.584
*** MODULE NAME:(MMON_SLAVE) 2013-08-26 04:11:37.584
*** ACTION NAME:(DDE async action) 2013-08-26 04:11:37.584
 
========= Dump for error ORA 1110 (no incident) ========
—-- DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (Async) —--
dbkh_reactive_run_check: BEGIN
dbkh_reactive_run_check:; incident_id=0
dbkh_run_check_internal: BEGIN; check_namep=DB Structure Integrity Check, run_namep=<null>
dbkh_run_check_internal: BEGIN; timeout=0
dbkh_run_check_internal: AFTER RUN CREATE; run_id=141861
DDE rules only execution for: ORA 1110
—-- START Event Driven Actions Dump —-
—- END Event Driven Actions Dump —-
—-- START DDE Actions Dump —--
Executing SYNC actions
Executing ASYNC actions
—-- END DDE Actions Dump (total 0 csec) —--
dbkh_create_finding: BEGIN
dbkhu_prepare_default_msgobj: BEGIN
dbkhu_prepare_default_msgobj:; name_id=20, type=2, flags=1
dbkhu_get_default_msg_def: BEGIN
dbkhu_get_default_msg_def: END
dbkhu_prepare_default_msgobj:: MSG PARAMS-1; i=0
dbkhu_prepare_default_msgobj: END
dbkhu_prepare_default_msgobj: BEGIN
dbkhu_prepare_default_msgobj:; name_id=20, type=2, flags=2
dbkhu_get_default_msg_def: BEGIN
dbkhu_get_default_msg_def: END
dbkhu_prepare_default_msgobj:: MSG PARAMS-2; i=0
dbkhu_prepare_default_msgobj: END
dbkhu_prepare_default_msgobj: BEGIN
dbkhu_prepare_default_msgobj:; name_id=23, type=2, flags=1
dbkhu_get_default_msg_def: BEGIN
dbkhu_get_default_msg_def: END
dbkhu_prepare_default_msgobj:: MSG PARAMS-1; i=2
dbkhu_prepare_default_msgobj: END
dbkhu_prepare_default_msgobj: BEGIN
dbkhu_prepare_default_msgobj:; name_id=23, type=2, flags=2
dbkhu_get_default_msg_def: BEGIN
dbkhu_get_default_msg_def: END
dbkhu_prepare_default_msgobj:: MSG PARAMS-2; i=1
dbkhu_prepare_default_msgobj: END
dbkh_create_finding: END
cross-check executed
dbkh_post_process_run: BEGIN
dbkh_post_process_run: NEW FAILURE COUNT: 0; DBKH_NUM_NEW_FAILURES_CTX(ctxp)=dbkh_post_process_run: END
dbkh_run_check_internal: END
dbkh_reactive_run_check: END
——————————————————-

解决方法：

SQL> create undo tablespace SEM_UNDO datafile '/oracle/oradata/SEM/SEM_UNDO.dbf' size 100m reuse autoextend on next 20m maxsize 4096m;

表空间已创建。

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------------------------------------------------------------------
SYSTEM
SYSAUX
TEMP
USERS

。。。。。。。
UNDOTP
SEM_UNDO

已选择 17 行。

SQL> alter system set undo_tablespace=SEM_UNDO;

系统已更改。

SQL>
SQL>
SQL> drop tablespace UNDOTP
  2  ;

表空间已删除。

SQL>

SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。

Total System Global Area 1670221824 bytes
Fixed Size                  2361784 bytes
Variable Size            1023411784 bytes
Database Buffers          637534208 bytes
Redo Buffers                6914048 bytes
数据库装载完毕。
数据库已经打开。

########################################

迷途小运维记录一次故障

作者：john

转载请注明出处