oracle系統預設臨時表空間以及redo日誌檔案問題處理

賀子_DBA時代發表於2015-05-29
問題:本人現在要把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 檔案,每個組只要保證有一個成員正常,那資料庫沒有問題,可以正常提過服務。












來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29654823/viewspace-1677587/,如需轉載,請註明出處,否則將追究法律責任。

相關文章