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 檔案，每個組只要保證有一個成員正常，那資料庫沒有問題，可以正常提過服務。