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 文件,每个组只要保证有一个成员正常,那数据库没有问题,可以正常提过服务。

相关推荐