Oracle pfile UNDOTBS参数配置错误案例模仿

《DBA入门、进阶与诊断案例》中2.3.1章节的案例进行模拟

[oracle@021Y-SH-BKAP dbs]$ vi initorcl.oraorcl.__db_cache_size=2885681152orcl.__java_pool_size=67108864orcl.__large_pool_size=67108864orcl.__oracle_base='/DBBK/oracle'#ORACLE_BASE set from environmentorcl.__pga_aggregate_target=2684354560orcl.__sga_target=4026531840orcl.__shared_io_pool_size=0orcl.__shared_pool_size=939524096orcl.__streams_pool_size=0*.audit_file_dest='/DBBK/oracle/admin/orcl/adump'*.audit_trail='db'*.compatible='11.2.0.0.0'*.control_files='/DBBK/oracle/oradata/orcl/control01.ctl','/DBBK/oracle/oradata/orcl/control03.ctl','/DBBK/oracle/flash_recovery_area/orcl/control02.ctl'*.db_block_size=8192*.db_cache_size=25165824*.db_domain=''*.db_name='orcl'*.db_recovery_file_dest='/DBBK/oracle/flash_recovery_area'*.db_recovery_file_dest_size=4070572032*.diagnostic_dest='/DBBK/oracle'*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'*.fast_start_mttr_target=180*.memory_target=6689914880*.open_cursors=300*.processes=150*.remote_login_passwordfile='EXCLUSIVE'*.undo_tablespace='UNDOTBS2'

我修改了pfile中的undo_tablespace='UNDOTBS2'参数值，重新以加载pfile文件的方式启动数据库。

SQL> startup pfile='/DBBK/oracle/product/11.2.0.1.0/dbs/initorcl.ora'ORACLE instance started.Total System Global Area 6680915968 bytesFixed Size                  2213936 bytesVariable Size            3758098384 bytesDatabase Buffers         2885681152 bytesRedo Buffers               34922496 bytesDatabase mounted.ORA-01092: ORACLE instance terminated. Disconnection forcedORA-30012: undo tablespace 'UNDOTBS2' does not exist or of wrong typeProcess ID: 11428Session ID: 96 Serial number: 3

查看alter_.log中的记录发现如下报错：

Errors in file /DBBK/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_11428.trc:ORA-30012: undo tablespace 'UNDOTBS2' does not exist or of wrong typeErrors in file /DBBK/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_11428.trc:ORA-30012: undo tablespace 'UNDOTBS2' does not exist or of wrong typeError 30012 happened during db open, shutting down databaseUSER (ospid: 11428): terminating the instance due to error 30012Instance terminated by USER, pid = 11428ORA-1092 signalled during: ALTER DATABASE OPEN...opiodr aborting process unknown ospid (11428) as a result of ORA-1092Tue Jan 27 17:43:33 2015ORA-1092 : opitsk aborting process

查看ORA报错信息的解释

[oracle@021Y-SH-BKAP dbs]$ oerr ora 3001230012, 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.

[oracle@021Y-SH-BKAP dbs]$ oerr ora 109201092, 00000, "ORACLE instance terminated. Disconnection forced"// *Cause:  The instance this process was connected to was terminated//          abnormally, probably via a shutdown abort. This process//          was forced to disconnect from the instance.// *Action: Examine the alert log for more details. When the instance has been //          restarted, retry action.

检查UNDO控件是否存在：

cd $ORACLE_BASE/oradata/orcl-rw-r----- 1 oracle oinstall 104865792 Jan 27 17:43 undotbs01.dbf

启动数据库都mount状态

SQL> startup mount pfile='/DBBK/oracle/product/11.2.0.1.0/dbs/initorcl.ora';ORACLE instance started.Total System Global Area 6680915968 bytesFixed Size                  2213936 bytesVariable Size            3758098384 bytesDatabase Buffers         2885681152 bytesRedo Buffers               34922496 bytesDatabase mounted.

查看启动参数，发现数据库mount的是UNDOTBS2

SQL> show parameter undo_tablespaceNAME                                 TYPE                              VALUE------------------------------------ --------------------------------- ------------------------------undo_tablespace                      string                            UNDOTBS2SQL> show parameter pfileNAME                                 TYPE                              VALUE------------------------------------ --------------------------------- ------------------------------spfile                               string

那么这里可以确定，因为我启动的时候没有使用SPFILE，或者另一种情况SPFILE中的参数值被我不小心改错了。导致启动时候加载错误而参数的报错。