更换temp表空间时hang住

更换temp表空间时hang住。
操作描述：
该"更换temp表空间"操作是rac的rman备份恢复到一个单机上的最后一个步骤，
不过，在执行"drop tablespace TEMP including contents and datafiles;"时hang住。

环境描述：
oracle database 11.2.0.4单机，未打任何的psu以及其他的patch
该单机是虚拟机，物理内存才2.1g，因此，在"drop tablespace TEMP "时，不存在前台业务连入的情况。

当时的操作log：

SQL> select name from v$tempfile;NAME--------------------------------------------------------------------------------/u02/oradata/arch/ORCL/datafile/o1_mf_temp_c2k37h7p_.tmpSQL> select tablespace_name from dba_tablespaces where contents='TEMPORARY';TABLESPACE_NAME------------------------------TEMPSQL> create temporary tablespace TEMP1  2  tempfile '/u02/oradata/orcl/temp01.dbf'  3  size 50M;Tablespace created.SQL> alter database default temporary tablespace TEMP1;Database altered.SQL> drop tablespace TEMP including contents and datafiles;----->>hang住。

问题分析：
一、查找如上session的等待事件：

[oracle@rhel63single ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Thu Oct 22 16:32:18 2015Copyright (c) 1982, 2013, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, Automatic Storage Management, OLAP, Data Miningand Real Application Testing optionsSQL> SET LINES 2000SQL> select sid, program, status,BLOCKING_SESSION ,event from v$session where type='USER';        SID PROGRAM                                          STATUS   BLOCKING_SESSION EVENT---------- ------------------------------------------------ -------- ---------------- ----------------------------------------------------------------         7 sqlplus@rhel63single (TNS V1-V3)                 INACTIVE                  SQL*Net message from client      1144 sqlplus@rhel63single (TNS V1-V3)                 ACTIVE                384 enq: TS - contention      1156 sqlplus@rhel63single (TNS V1-V3)                 ACTIVE                    SQL*Net message to clientSQL>  select sid, program, status,BLOCKING_SESSION ,event from v$session where sid='384';       SID PROGRAM                                          STATUS   BLOCKING_SESSION EVENT---------- ------------------------------------------------ -------- ---------------- ----------------------------------------------------------------       384 oracle@rhel63single (SMON)                       ACTIVE                    smon timerSQL> 

这个现象与mos文章SMON Blocking Drop Temporary Tablespace (文档 ID 1500044.1)描述的现象符合，该文章定位到该现象是bug

This seems to be caused by Bug 13028161: DROP TEMP TABLESPACE HANGS DUE TO TS ENQUEUE CONTENTION which was closed as duplicate of Bug 15913577 : MAIN_LINUX.X64 LRG9H8 RUNS WILL BE SUSPENDED 12/02/12; TOO MANY HANGS

该问题最终的解决方案如下：

Install patch 15913577 if avaiaable to your platform or use the following workaround- Complete all the needed prerequisites (create the new temp tablespace and set as default and no user is assigned to the old tablespace)- Bounce the DB- Once the DB starts up disconnect from SQLPLUS completely and close the console- Open a new console and launch SQLPLUS- Run the drop command as the VERY FIRST COMMANDIn case of temporary tablespace groups being used, create a new temporary tablespace Group and drop the old temporary tablespace group.

 

重启db之后，立即执行drop tablespace TEMP including contents and datafiles命令

[oracle@rhel63single ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Thu Oct 22 16:32:18 2015Copyright (c) 1982, 2013, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, Automatic Storage Management, OLAP, Data Miningand Real Application Testing optionsSQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL> startupORACLE instance started.Total System Global Area  897740800 bytesFixed Size                  2258440 bytesVariable Size             633342456 bytesDatabase Buffers          255852544 bytesRedo Buffers                6287360 bytesDatabase mounted.Database opened.SQL> drop tablespace TEMP including contents and datafiles;Tablespace dropped.SQL> select name from v$tempfile;NAME--------------------------------------------------------------------------------/u02/oradata/orcl/temp01.dbfSQL> 

另外，关于rman restore 数据库时的对temporay file的改进：

In Oracle Database 10g Release 2, it does.When you recover a database, the temporary tablespace files are automatically recreated too. Here's an excerpt from the alert log file: ORA-01157: cannot identify/lock data file 201 - see DBWR trace fileORA-01110: data file 201: '/u01/app/oracle/oradata/TEST/temp01.dbf'ORA-27037: unable to obtain file statusLinux Error: 2: No such file or directoryAdditional information: 3Sun Mar 27 20:29:00 2005Errors in file /u01/app/oracle/admin/TEST/bdump/test_dbw0_15457.trc:ORA-01186: file 201 failed verification testsORA-01157: cannot identify/lock data file 201 - see DBWR trace fileORA-01110: data file 201: '/u01/app/oracle/oradata/TEST/temp01.dbf'Sun Mar 27 20:29:00 2005File 201 not verified due to error ORA-01157Sun Mar 27 20:29:00 2005Dictionary check completeSun Mar 27 20:29:00 2005SMON: enabling tx recoverySun Mar 27 20:29:00 2005Re-creating tempfile /u01/app/oracle/oradata/TEST/temp01.dbf

如上摘自：Recovery Manager and Tempfiles (文档 ID 305993.1)