数据库物理结构分为数据文件和临时文件,临时文件和数据文件基本相同,只不过是 数据文件在逻辑结构上对应着表空间,而临时文件对应着临时表空间而已。它对数据库的影响很小,丢失临时文件的后果是需要利用临时表空间的排序SQL将会失败。
下面我们将为数据库的临时空间进行替换和、删除和恢复测试
1、替换临时表空间
二、恢复临时文件的最简单方式就是重启数据库,在启动时,如果在原来的磁盘目录可用,oracle就会在该目录下创建所有丢失的临时文件
点击( 此处 )折叠或打开
下面我们将为数据库的临时空间进行替换和、删除和恢复测试
1、替换临时表空间
点击(此处)折叠或打开
- [oracle@Primary oradata]$ sqlplus / as sysdba
- SQL*Plus: Release 11.2.0.4.0 Production on Mon Jul 25 09:03:13 2016
- 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, OLAP, Data Mining and Real Application Testing options
- SQL> col name for a40
- SQL> select file#,name from v$tempfile;
- FILE# NAME
- ---------- ----------------------------------------
- 1 /oracle/database/oradata/jingfan/temp01.dbf
-
- #增加一个临时文件
- SQL> alter tablespace temp add tempfile '/oracle/database/oradata/jingfan/temp02.ora' size 25m;
- Tablespace altered.
-
- SQL> select file#,name from v$tempfile;
- FILE# NAME
---------- ----------------------------------------
1 /oracle/database/oradata/jingfan/temp01.dbf
2 /oracle/database/oradata/jingfan/temp02.ora
- #s删除一个临时文件
- SQL> alter tablespace temp drop tempfile '/oracle/database/oradata/jingfan/temp01.dbf';
- Tablespace altered.
- SQL> select file#,name from v$tempfile;
- FILE# NAME
- ---------- ----------------------------------------
- 2 /oracle/database/oradata/jingfan/temp02.ora
-
- SQL> alter tablespace temp drop tempfile '/oracle/database/oradata/jingfan/temp02.ora';
- alter tablespace temp drop tempfile '/oracle/database/oradata/jingfan/temp02.ora'
- *
- ERROR at line 1:
- ORA-03261: the tablespace TEMP has only one file
- SQL> shutdown
- ^CORA-01013: user requested cancel of current operation
- SQL> shutdown immediate
- Database closed.
- Database dismounted.
- ORACLE instance shut down.
- SQL> !rm /oracle/database/oradata/jingfan/temp02.ora
- SQL> host ls -lt /oracle/database/oradata/jingfan/
- total 1894160
-rw-r----- 1 oracle oinstall 10174464 Jul 25 09:07 control01.ctl
-rw-r----- 1 oracle oinstall 262152192 Jul 25 09:06 rman.dbf
-rw-r----- 1 oracle oinstall 5251072 Jul 25 09:06 users01.dbf
-rw-r----- 1 oracle oinstall 608182272 Jul 25 09:06 sysaux01.dbf
-rw-r----- 1 oracle oinstall 99622912 Jul 25 09:06 undotbs01.dbf
-rw-r----- 1 oracle oinstall 796925952 Jul 25 09:06 system01.dbf
-rw-r----- 1 oracle oinstall 52429312 Jul 25 09:06 redo01.log
-rw-r----- 1 oracle oinstall 52429312 Jul 25 07:49 redo03.log
-rw-r----- 1 oracle oinstall 52429312 Jul 25 07:49 redo02.log - SQL>
点击( 此处 )折叠或打开
- #我们启动数据库是没有错误提示
- SQL> startup
- ORACLE instance started.
- Total System Global Area 776646656 bytes
- Fixed Size 2257272 bytes
- Variable Size 478154376 bytes
- Database Buffers 289406976 bytes
- Redo Buffers 6828032 bytes
- Database mounted.
- Database opened.
- #然后,我们跟踪一下alert日志信息,发下有一段Re-creating命令,正是重新穿件临时文件的操作
- ARC0: STARTING ARCH PROCESSES COMPLETE
ARC0: Becoming the 'no FAL' ARCH
ARC0: Becoming the 'no SRL' ARCH
ARC2: Becoming the heartbeat ARCH
[95862] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:575491024 end:575491034 diff:10 (0 seconds)
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Re-creating tempfile /oracle/database/oradata/jingfan/temp02.ora
Database Characterset is AL32UTF8
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Mon Jul 25 09:07:43 2016
QMNC started with pid=25, OS id=95878
Completed: ALTER DATABASE OPEN
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/27039319/viewspace-2122664/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/27039319/viewspace-2122664/