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

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
要扩容Oracle数据库的临表空间,可以按照以下步骤进行操作: 1. 查看当前临表空间的使用情况 可以执行以下SQL语句查看当前临表空间的使用情况: ``` SELECT tablespace_name, sum(bytes_used) / 1024 / 1024 AS used_mb, sum(bytes_free) / 1024 / 1024 AS free_mb, sum(bytes_total) / 1024 / 1024 AS total_mb FROM v$temp_space_header GROUP BY tablespace_name; ``` 2. 确认需要扩容的临表空间 根据上一步的查询结果,确认需要扩容的临表空间名称。 3. 创建新的数据文件 执行以下SQL语句在需要扩容的临表空间中创建新的数据文件: ``` ALTER TABLESPACE temp ADD TEMPFILE '/path/to/new/tempfile.dbf' SIZE 1024M; ``` 其中,`/path/to/new/tempfile.dbf`为新数据文件的路径和文件名,`1024M`为新数据文件的大小,可以根据需要进行修改。 4. 查看数据文件状态 执行以下SQL语句查看新数据文件的状态: ``` SELECT file_name, tablespace_name, bytes / 1024 / 1024 AS size_mb, status FROM dba_temp_files WHERE tablespace_name = 'TEMP'; ``` 确认新数据文件状态为`AVAILABLE`。 5. 删除旧的数据文件 执行以下SQL语句删除旧的数据文件: ``` ALTER TABLESPACE temp DROP TEMPFILE '/path/to/old/tempfile.dbf'; ``` 其中,`/path/to/old/tempfile.dbf`为旧数据文件的路径和文件名,需要根据实际情况进行修改。 6. 查看临表空间使用情况 执行第一步的SQL语句,确认临表空间的使用情况已经扩容。 注意:在进行上述操作前,建议备份数据库以防止意外情况发生。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值