Oracle系统默认临时表空间以及redo日志文件问题处理
Oracle系统默认临时表空间以及redo日志文件问题处理
问题：本人现在要把Oracle的数据同步到MySQL，运用的ETL工具，由于数据量很大，而且有子查询要用到临时表空间，导致原来的该临时表空间，空间不足，根据报错直接想到了给该临时表空间添加临时文件。查看了它原有的临时文件的路径，也没有多想直接在这个路径下添加了一个文件，谁知道该路径空间不足了，还没有把新加的临时文件用完，数据库就down了，原因是redo日志文件也在这个挂载点下，我们知道任何操作都要先写redo,虽然redo是循环复写的，在如果大量产生日志的时候，还没有归档的redo日志文件是不能被复写的，最终由于空间不足，导致数据库down掉。
解决的过程：尝试起库，发现报错，说/shared_data 共享内存空间不足什么的，导致无法审计，直接想到去清理空间，
[oracle@rac1 trace]$ df -Th
文件系统 类型 容量 已用 可用 已用% 挂载点
/dev/sda2 ext3 142G 135G 0 100% /
/dev/sda6 ext3 66G 48G 16G 76% /data
/dev/sda3 ext3 48G 17G 29G 37% /software
/dev/sda1 ext3 190M 14M 167M 8% /boot
tmpfs tmpfs 16G 0 16G 0% /dev/shm
/dev/mapper/mpath2
ext3 2.0T 1.2T 764G 61% /backup
/dev/mapper/oraclep1
ext3 1008G 686G 272G 72% /software/oradata01
rac1:/shared_grid
nfs 142G 135G 0 100% /software/app/11.2.0/grid
rac1:/shared_home
nfs 142G 135G 0 100% /software/app/oracle/product/11.2.0/db_1
rac1:/shared_config
nfs 142G 135G 0 100% /software/shared_config
rac1:/shared_data
nfs 142G 135G 0 100% /software/oradata
none tmpfs 16G 128K 16G 1% /var/lib/xenstored
很显然 挂在点 / 空间被沾满，红色部分，很显然，有可能可以清理的只有 /software/oradata ，以所以在这个路径下找占用空间比较大的文件，
[oracle@rac1 JLPROJCT]$ pwd
/shared_data/JLPROJCT
[oracle@rac1 JLPROJCT]$ ll
总计 79771028
-rw-r----- 1 oracle oinstall 25706496 05-28 22:13 control01.ctl
-rw-r----- 1 oracle oinstall 1536 04-02 13:55 orapwJLPROJCT
-rw-r----- 1 oracle oinstall 2097152512 05-27 21:52 redo01A.log
-rw-r----- 1 oracle oinstall 2097152512 05-27 21:48 redo02A.log
-rw-r----- 1 oracle oinstall 2097152512 05-28 17:47 redo03A.log
-rw-r----- 1 oracle oinstall 2097152512 05-28 17:47 redo04A.log
-rw-r----- 1 oracle oinstall 2097152512 05-28 22:13 redo05A.log
-rw-r----- 1 oracle oinstall 2097152512 05-28 22:12 redo06A.log
-rw-r----- 1 oracle oinstall 2097152512 05-28 17:47 redo07A.log
-rw-r----- 1 oracle oinstall 2097152512 05-28 17:47 redo08A.log
-rw-r----- 1 oracle oinstall 5632 05-27 19:37 spfileJLPROJCT.ora
-rw-r----- 1 oracle oinstall 3330285568 05-28 22:12 sysaux01.dbf
-rw-r----- 1 oracle oinstall 13532930048 05-28 22:12 system01.dbf
-rw-r----- 1 oracle oinstall 34358697984 05-28 11:24 temp01.dbf
-rw-r----- 1 oracle oinstall 8017420288 05-28 22:13 undotbs01.dbf
-rw-r----- 1 oracle oinstall 6121594880 05-28 22:11 undotbs02.dbf
-rw-r----- 1 oracle oinstall 104865792 05-28 17:48 users01.dbf
发现redo的命名规范（有A ），显然是其中的一部分成员，根据redo成员镜像的关系，想到了把这八个成员移动到别的位置（其实不应该这样，应该移动临时文件，因为即便丢失了所有的临时表空间，只要不是数据库当中用到了order by、子查询、group by、distinct等需要消耗临时表空间的语句(而且要比较大才行，小的话就直接用pga的SORT_AREA区了)，那么也不会对业务造成错误导致中断，发现问题之后只需要新建一个临时表空间就可以了。你要是了解备份恢复的话，实际上在进行备份的时候临时表空间都不会进行备份，而只是有一个创建临时表空间的语句而已）
腾出空间之后，数据库终于算是起来了。
但是这显然是不行的，移动位置，相当于物理层面删掉了日志组的成员，这样每个组就只有一个成员了，非常危险.此时在数据库里查看时，被移动的文件的状态变成了invalid，
SQL> select GROUP#,STATUS ， from v$logfile;
GROUP# STATUS MEMBER
---------- -------
2 invalied /software/oradata/JLPROJCT/redo02A.log
1 invalied /software/oradata/JLPROJCT/redo01A.log
3 invalied /software/oradata/JLPROJCT/redo03A.log
1 /software/oradata01/JLPROJCT/redo01B.log
5 /software/oradata01/JLPROJCT/redo05B.log
6 invalied /software/oradata/JLPROJCT/redo06A.log
8 invalied /software/oradata/JLPROJCT/redo08A.log
7 invalied /software/oradata/JLPROJCT/redo07A.log
4 invalied /software/oradata/JLPROJCT/redo04A.log
2 /software/oradata01/JLPROJCT/redo02B.log
5 invalied /software/oradata/JLPROJCT/redo05A.log
GROUP# STATUS
---------- -------
7 /software/oradata01/JLPROJCT/redo07B.log
4 /software/oradata01/JLPROJCT/redo04B.log
3 /software/oradata01/JLPROJCT/redo03B.log
6 /software/oradata01/JLPROJCT/redo06B.log
8 /software/oradata01/JLPROJCT/redo08B.log
16 rows selected.
这时候如果你后悔了，你不想移动这个8个成员了，你只需要再把它们移动回去既可以，而且数据库是打开状态的，然后切换几次日志，再查看
他们的状态就会由 invalied 变回正常。
接下来应该做的事应该是：找个数据库空闲的时候，在数据层面删掉被移动的那8个成员，然后重新添加新的成员（换路径）。
alter database add logfile member '/backup/oradata/JLPROJCT/redo01A.log' TO GROUP 1,
删除日志组的原则：每个实例必须至少有两个日志组；当一个组处于ACTIVE或者CURRENT的状态时不可删除；删除日志组的操作只对数据库进行更改，操作系统的文件尚未删除；当删除时适用DROP LOGFILE GROUP N语句时，此时GROUP N内的所有成员都将被删除
删除日志成员的原则：当你删除一个是该组中最后一个成员的时候，你不能删除此成员；当组的状态处于current的状态时，不能删除组成员；在归档模式下，必须得归档之后才能删除；删除日志组成员的操作只对数据库进行更改，操作系统的文件尚未删除。
ALTER DATABASE DROP LOGFILE MEMBER '/software/oradata/JLPROJCT/redo05A.log';
当我知道是因为临时文件造成这个问题后，我想着把临时文件删掉，然后从新添加一个临时文件：主要思想是，oracle必须存在一个默认的临时表空间，缺省临时表空间的数据���件不能删除，先建立一个临时表空间temp1，并将其默认，然后删除掉temp临时表空间，再建立temp临时表空间，指定temp为默认临时表空间，最后将temp1临时表空间删除掉。 ##改变系统默认临时表空间后，原来的某个用户默认的临时表空间也会随之改变。
具体过程：
1.CREATE TEMPORARY TABLESPACE temp1 TEMPFILE '/opt/oracle/oradata/orcl/temp.dbf' SIZE 1024 m
AUTOEXTEND ON NEXT 10m MAXSIZE UNLIMITED;
2.将新建立的临时表空间temp1设置成系统默认临时表空间。
SQL> alter database default tablespace test1;
3.查看oracle的默认临时表空间
SQL> select * from database_properties;
SQL> select PROPERTY_NAME , PROPERTY_VALUE from database_properties;
PROPERTY_NAME PROPERTY_VALUE
-------------------------------------------------------------------------------------------------------------
DICT.BASE 2
DEFAULT_TEMP_TABLESPACE TEMP1
DEFAULT_PERMANENT_TABLESPACE USERS
4，删除原来的temp临时表空间。 删除临时表空间的一个数据文件：
SQL> alter database tempfile ‘/u01/app/oracle/oradata/orcl/temp02.dbf’ drop;
删除临时表空间(彻底删除)：
SQL> drop tablespace temp including contents and datafiles cascade constraints;
总结：临时表空间的损坏对数据库业务的影响最小， 在数据库没有进行order by、子查询、group by、distinct等需要消耗临时表空间的语句(而且要比较大才行，小的话就直接用pga的SORT_AREA区了) 时，丢失临时表空间，几乎对业务没有影响，并且，重启数据库后，临时表空间会自动重建
如果只是想不让默认临时表空间的一个文件占用空间位置，你可以直接再物理从面删除对应文件，即可，前提你这个临时表空间还有另一个临时文件。
alter tablespace temp drop tempfile '/software/oradata/JLPROJCT/temp02.dbf' 会导致该文件offlione 但是
select * from dba_users where temporary_tablespace = 'TEMP'; 还能查到
对于redo.log 文件，每个组只要保证有一个成员正常，那数据库没有问题，可以正常提过服务。
评论暂时关闭