只读表空间和临时表空间的恢复

在shutdown 的情况下恢复临时表空间

丢失临时表空间的dbf没事，虽临时表空间不能用，当大规模排序时才报错，
这种情况下会将相关信息写入alert文件中
SQL> col tablespace format a10
SQL> col File format a50
SQL> select f.TS#,f.FILE#,f.name "File",t.name "Tablespace"
  2  from v$tablespace t,v$tempfile f
  3  where f.TS#=t.TS#;

       TS#      FILE# File                                               Tablespace
---------- ---------- -------------------------------------------------- ----------
         3          1 /u01/app/oracle/oradata/orcl/temp01.dbf            TEMP

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> ho rm /u01/app/oracle/oradata/orcl/temp01.dbf -rf

SQL> ho ls /u01/app/oracle/oradata/orcl/temp01.dbf
ls: /u01/app/oracle/oradata/orcl/temp01.dbf: No such file or directory

查看告警文件alert.log中显示：
Thu Dec  1 00:12:43 2011
Re-creating tempfile /u01/app/oracle/oradata/orcl/temp01.dbf
证明了数据库在重建所需临时文件

SQL> startup
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1218316 bytes
Variable Size              62916852 bytes
Database Buffers          100663296 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
SQL> ho ls /u01/app/oracle/oradata/orcl/temp01.dbf——发现又回来了
/u01/app/oracle/oradata/orcl/temp01.dbf

SQL> select f.TS#,f.FILE#,f.name "File",t.name "Tablespace"
  2  from v$tablespace t,v$tempfile f
  3  where f.TS#=t.TS#;

       TS#      FILE# File                                               Tablespace
---------- ---------- -------------------------------------------------- ----------
         3          1 /u01/app/oracle/oradata/orcl/temp01.dbf            TEMP


＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
数据开启不停机，没有备份的情况下，的临时表空间的恢复


SQL> select f.name "File",t.name "Tablespace",bytes/1024/1024 MB
  2  from v$tablespace t,v$tempfile f
  3  where f.TS#=t.TS#;

File                                               Tablespace         MB
-------------------------------------------------- ---------- ----------
/u01/app/oracle/oradata/orcl/temp01.dbf            TEMP               20



SQL> ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/orcl/temp01.dbf' RESIZE 1M;
ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/orcl/temp01.dbf' RESIZE 1M
*
ERROR at line 1:
ORA-03214: File Size specified is smaller than minimum required
把临时表空间的size改为2M，注意最小为2M（oracle默认），低于2M会报错
SQL> ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/orcl/temp01.dbf' RESIZE 2M ;

Database altered.

SQL> select f.name "File",t.name "Tablespace",bytes/1024/1024 MB
  2  from v$tablespace t,v$tempfile f
  3  where f.TS#=t.TS#;

File                                               Tablespace         MB
-------------------------------------------------- ---------- ----------
/u01/app/oracle/oradata/orcl/temp01.dbf            TEMP                2

SQL> create table test_tmp as select * from dba_objects;
SQL> insert into test_tmp select * from dba_objects;

SQL> select count(*) from test_tmp;

  COUNT(*)
----------
   2113440


SQL> select f.name "File",t.name "Tablespace",bytes/1024/1024 MB
  2  from v$tablespace t,v$tempfile f
  3  where f.TS#=t.TS#;

File                                               Tablespace         MB
-------------------------------------------------- ---------- ----------
/u01/app/oracle/oradata/orcl/temp01.dbf            TEMP               40

SQL> ho rm -rf /u01/app/oracle/oradata/orcl/temp01.dbf

SQL> select f.name "File",t.name "Tablespace",bytes/1024/1024 MB
  2  from v$tablespace t,v$tempfile f
  3  where f.TS#=t.TS#;

File                                               Tablespace         MB
-------------------------------------------------- ---------- ----------
/u01/app/oracle/oradata/orcl/temp01.dbf            TEMP               40


SQL> select OWNER,OBJECT_NAME from test_tmp order by  OBJECT_NAME desc;
select OWNER,OBJECT_NAME from test_tmp order by  OBJECT_NAME desc
                              *
ERROR at line 1:
ORA-01565: error in identifying file '/u01/app/oracle/oradata/orcl/temp01.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
模拟大规模的排序的SQL，因为数据量太大，PGA内存放不下，而临时表空间只有2M所以oracle才会报错




SQL> alter tablespace temp add tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf' size 20M;
alter tablespace temp add tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf' size 20M
*
ERROR at line 1:
ORA-01537: cannot add file '/u01/app/oracle/oradata/orcl/temp01.dbf' - file already part of database


SQL> alter tablespace temp add tempfile '/u01/app/oracle/oradata/orcl/temp02.dbf' size 20M;

Tablespace altered.


SQL> alter tablespace temp drop tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf';

Tablespace altered.

SQL> select f.name "File",t.name "Tablespace",bytes/1024/1024 MB
  2  from v$tablespace t,v$tempfile f
  3  where f.TS#=t.TS#;

File                                               Tablespace         MB
-------------------------------------------------- ---------- ----------
/u01/app/oracle/oradata/orcl/temp02.dbf            TEMP               20

SQL> select f.TS#,f.FILE#,f.name "File",t.name "Tablespace"
  2  from v$tablespace t,v$tempfile f
  3  where f.TS#=t.TS#;

       TS#      FILE# File                                               Tablespace
---------- ---------- -------------------------------------------------- ----------
         3          2 /u01/app/oracle/oradata/orcl/temp02.dbf            TEMP

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26465805/viewspace-712486/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26465805/viewspace-712486/