更换temp表空间时hang住

最新推荐文章于 2022-10-27 17:53:08 发布

msdnchina

最新推荐文章于 2022-10-27 17:53:08 发布

阅读量847

点赞数

分类专栏： Oracle DB

本文链接：https://blog.csdn.net/msdnchina/article/details/49718063

版权

Oracle DB 专栏收录该内容

773 篇文章 3 订阅

订阅专栏

更换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_.tmp

SQL> select tablespace_name from dba_tablespaces where contents='TEMPORARY';

TABLESPACE_NAME
------------------------------
TEMP

SQL> 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 sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Oct 22 16:32:18 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> SET LINES 2000
SQL> 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 client

SQL>  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 timer

SQL>

这个现象与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 COMMAND
In 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 sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Oct 22 16:32:18 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  897740800 bytes
Fixed Size                  2258440 bytes
Variable Size             633342456 bytes
Database Buffers          255852544 bytes
Redo Buffers                6287360 bytes
Database mounted.
Database opened.
SQL> drop tablespace TEMP including contents and datafiles;

Tablespace dropped.

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/u02/oradata/orcl/temp01.dbf

SQL>

另外，关于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 file
ORA-01110: data file 201: '/u01/app/oracle/oradata/TEST/temp01.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Sun Mar 27 20:29:00 2005
Errors in file /u01/app/oracle/admin/TEST/bdump/test_dbw0_15457.trc:
ORA-01186: file 201 failed verification tests
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110: data file 201: '/u01/app/oracle/oradata/TEST/temp01.dbf'
Sun Mar 27 20:29:00 2005
File 201 not verified due to error ORA-01157
Sun Mar 27 20:29:00 2005
Dictionary check complete
Sun Mar 27 20:29:00 2005
SMON: enabling tx recovery
Sun Mar 27 20:29:00 2005
Re-creating tempfile /u01/app/oracle/oradata/TEST/temp01.dbf

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