Oracle诊断案例-Spfile案例一则

发现系统没有使用spfile,而初始化参数设置的undo表空间为UNDOTBS1

5. 检查参数文件

 

  bash-2.03$ cd $ORACLE_HOME/dbs
  bash-2.03$ ls

  init.ora initgzhs.ora initgzhs.ora.old orapwgzhs
  initdw.ora initgzhs.ora.hurray lkGZHS snapcf_gzhs.f
  bash-2.03$ vi initgzhs.ora

"initgzhs.ora" [Incomplete last line] 105 lines, 3087 characters 

  ####################################################
  # Copyright (c) 1991, 2001, 2002 by Oracle Corporation
  ####################################################

  ###########################################
  # Archive
  ###########################################
  log_archive_dest_1=''''LOCATION=/u06/oradata/gzhs/arch''''
  log_archive_format=%t_%s.dbf
  log_archive_start=true
  ###########################################
  # Cache and I/O
  ###########################################
  db_block_size=8192
  db_cache_size=2516582400
  db_file_multiblock_read_count=16
  ###########################################
  # Cursors and Library Cache
  ###########################################
  open_cursors=300
  ......................


   ###########################################
  # System Managed Undo and Rollback Segments
  ###########################################
  undo_management=AUTO
  undo_retention=10800
  undo_tablespace=UNDOTBS1

  :q!
.............

这个设置是极其可疑的.
怀疑参数文件和实际数据库设置不符.

6. 再次检查alert文件
查找对于UNDO表空间的操作

第一部分，创建数据库时的信息:

 

  Sat Feb 7 20:30:12 2004
  CREATE DATABASE gzhs
  MAXINSTANCES 1
  MAXLOGHISTORY 1
  MAXLOGFILES 5
  MAXLOGMEMBERS 3
  MAXDATAFILES 100
  DATAFILE ''''/u01/oradata/gzhs/system01.dbf'''' SIZE 500M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
  EXTENT MANAGEMENT LOCAL
  DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE ''''/u01/oradata/gzhs/temp01.dbf'''' SIZE 1000M REUSE AUTOEXTEND 
  ON NEXT 250M MAXSIZE UNLIMITED
  UNDO TABLESPACE "UNDOTBS1" DATAFILE ''''/u01/oradata/gzhs/undotbs01.dbf'''' SIZE 1000M REUSE AUTOEXTEND ON 
NEXT 100M MAXSIZE UNLIMITED
  CHARACTER SET ZHS16GBK
  NATIONAL CHARACTER SET AL16UTF16
  LOGFILE GROUP 1 (''''/u01/oradata/gzhs/redo01.log'''') SIZE 256M,
  GROUP 2 (''''/u01/oradata/gzhs/redo02.log'''') SIZE 256M,
  GROUP 3 (''''/u01/oradata/gzhs/redo03.log'''') SIZE 256M
.............

注意，这也是OCP教材上提到的两种创建UNDO表空间的方式之一

第二部分,发现创建UNDOTBS2的记录信息:

 

      Wed Mar 24 20:20:58 2004
      /* OracleOEM */ CREATE UNDO TABLESPACE "UNDOTBS2" 
DATAFILE ''''/u01/oradata/gzhs/UNDOTBS2.dbf'''' SIZE 1024M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
  Wed Mar 24 20:22:37 2004
  Created Undo Segment _SYSSMU11$
  Created Undo Segment _SYSSMU12$
  Created Undo Segment _SYSSMU13$
  Created Undo Segment _SYSSMU14$
  Created Undo Segment _SYSSMU15$
  Created Undo Segment _SYSSMU16$
  Created Undo Segment _SYSSMU17$
  Created Undo Segment _SYSSMU18$
  Created Undo Segment _SYSSMU19$
  Created Undo Segment _SYSSMU20$
  Completed: /* OracleOEM */ CREATE UNDO TABLESPACE "UNDOTBS2"
  Wed Mar 24 20:24:25 2004
  Undo Segment 11 Onlined
  Undo Segment 12 Onlined
  Undo Segment 13 Onlined
  Undo Segment 14 Onlined
  Undo Segment 15 Onlined
  Undo Segment 16 Onlined
  Undo Segment 17 Onlined
  Undo Segment 18 Onlined
  Undo Segment 19 Onlined
  Undo Segment 20 Onlined
  Successfully onlined Undo Tablespace 15.
  Undo Segment 1 Offlined
  Undo Segment 2 Offlined
  Undo Segment 3 Offlined
  Undo Segment 4 Offlined
  Undo Segment 5 Offlined
  Undo Segment 6 Offlined
  Undo Segment 7 Offlined
  Undo Segment 8 Offlined
  Undo Segment 9 Offlined
  Undo Segment 10 Offlined
  Undo Tablespace 1 successfully switched out.
.............

第三部分，新的UNDO表空间被应用

Wed Mar 24 20:24:25 2004
ALTER SYSTEM SET undo_tablespace=''''UNDOTBS2'''' SCOPE=MEMORY;

我们发现问题就在这里,创建了新的UNDO表空间以后，因为使用的是pfile文件，修改的只对当前实例生效，操作人员忘记了修改pfile文件.

如果使用spfile，缺省的修改范围是both,会同时修改spfile文件，就可以避免以上问题的出现.

第四部分，删除了UNDOTBS1的信息

 

Wed Mar 24 20:25:01 2004
  /* OracleOEM */ DROP TABLESPACE "UNDOTBS1" INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS
  Wed Mar 24 20:25:03 2004
  Deleted file /u01/oradata/gzhs/undotbs01.dbf
  Completed: /* OracleOEM */ DROP TABLESPACE "UNDOTBS1" INCLUDI
.............

这样再次重新启动数据库的时候，问题出现了，pfile中定义的UNDOTBS1找不到了，而且操作实在很久以前，没人能回忆起来，甚至无法得知是什么人的操作。

7. 更改pfile，启动数据库

修改undo表空间

###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_management=AUTO
undo_retention=10800
undo_tablespace=UNDOTBS2

....

bash-2.03$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.3.0 - Production on 星期四 4月 1 11:55:11 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


连接到:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production
PL/SQL Release 9.2.0.3.0 - Production
CORE 9.2.0.3.0 Production
TNS for Solaris: Version 9.2.0.3.0 - Production
NLSRTL Version 9.2.0.3.0 - Production

SQL> exit
从Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production中断开
bash-2.03$

在这里我们可以看到，使用spfile可以免去手工修改pfile文件的麻烦，减少了犯错的可能。

既然Oracle9i给我们提供了这个新特性，就值得我们学习使用它.

上一页  [1] [2] 

Copyright ＠ 2007-2012 敏韬网(敏而好学，文韬武略--MinTao.Net)（学习笔记） Inc All Rights Reserved.
闵涛 E_mail:admin@mintao.net(欢迎提供学习资源)

鄂公网安备 42011102001154号

站长：MinTao ICP备案号：鄂ICP备11006601号-18