Oracle archive log mode 相關

發現自己搭建的RAC環境（vmware +OEL linux 5 + oracle 11g),總是包空間不夠，想了一下，應該是archive log mode 因為不管它了，瘋漲log了

如何發現是archive log mode呢？

SQL> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

SQL>

archive log 放在那個地方呢？

show parameter archive

SQL> show parameter archive

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target                   integer     0
log_archive_config                   string
log_archive_dest                     string
log_archive_dest_1                   string
log_archive_dest_10                  string
log_archive_dest_2                   string
log_archive_dest_3                   string
log_archive_dest_4                   string
log_archive_dest_5                   string
log_archive_dest_6                   string
log_archive_dest_7                   string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_8                   string
log_archive_dest_9                   string
log_archive_dest_state_1             string      enable
log_archive_dest_state_10            string      enable
log_archive_dest_state_2             string      enable
log_archive_dest_state_3             string      enable
log_archive_dest_state_4             string      enable
log_archive_dest_state_5             string      enable
log_archive_dest_state_6             string      enable
log_archive_dest_state_7             string      enable
log_archive_dest_state_8             string      enable

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_9             string      enable
log_archive_duplex_dest              string
log_archive_format                   string      %t_%s_%r.dbf
log_archive_local_first              boolean     TRUE
log_archive_max_processes            integer     4
log_archive_min_succeed_dest         integer     1
log_archive_start                    boolean     FALSE
log_archive_trace                    integer     0
standby_archive_dest                 string      ?/dbs/arch
SQL>

沒有顯示出來，如何解決呢？

用v$archive_dest這個view

SQL> desc v$archive_dest;
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
DEST_ID                                            NUMBER
DEST_NAME                                          VARCHAR2(256)
STATUS                                             VARCHAR2(9)
BINDING                                            VARCHAR2(9)
NAME_SPACE                                         VARCHAR2(7)
TARGET                                             VARCHAR2(7)
ARCHIVER                                           VARCHAR2(10)
SCHEDULE                                           VARCHAR2(8)
DESTINATION                                        VARCHAR2(256)
LOG_SEQUENCE                                       NUMBER
REOPEN_SECS                                        NUMBER
DELAY_MINS                                         NUMBER
MAX_CONNECTIONS                                    NUMBER
NET_TIMEOUT                                        NUMBER
PROCESS                                            VARCHAR2(10)
REGISTER                                           VARCHAR2(3)
FAIL_DATE                                          DATE
FAIL_SEQUENCE                                      NUMBER
FAIL_BLOCK                                         NUMBER
FAILURE_COUNT                                      NUMBER
MAX_FAILURE                                        NUMBER
ERROR                                              VARCHAR2(256)
ALTERNATE                                          VARCHAR2(256)
DEPENDENCY                                         VARCHAR2(256)
REMOTE_TEMPLATE                                    VARCHAR2(256)
QUOTA_SIZE                                         NUMBER
QUOTA_USED                                         NUMBER
MOUNTID                                            NUMBER
TRANSMIT_MODE                                      VARCHAR2(12)
ASYNC_BLOCKS                                       NUMBER
AFFIRM                                             VARCHAR2(3)
TYPE                                               VARCHAR2(7)
VALID_NOW                                          VARCHAR2(16)
VALID_TYPE                                         VARCHAR2(15)
VALID_ROLE                                         VARCHAR2(12)
DB_UNIQUE_NAME                                     VARCHAR2(30)
VERIFY                                             VARCHAR2(3)
COMPRESSION                                        VARCHAR2(7)

 

SQL> col dest_name for a50
SQL> col destination for a50
SQL> select dest_name, status, destination from v$archive_dest;

DEST_NAME                                          STATUS    DESTINATION
-------------------------------------------------- --------- --------------------------------------------------
LOG_ARCHIVE_DEST_1                                 VALID     /u01/app/oracle/product/11.1.0/db_1/dbs/arch
LOG_ARCHIVE_DEST_2                                 INACTIVE
LOG_ARCHIVE_DEST_3                                 INACTIVE
LOG_ARCHIVE_DEST_4                                 INACTIVE
LOG_ARCHIVE_DEST_5                                 INACTIVE
LOG_ARCHIVE_DEST_6                                 INACTIVE
LOG_ARCHIVE_DEST_7                                 INACTIVE
LOG_ARCHIVE_DEST_8                                 INACTIVE
LOG_ARCHIVE_DEST_9                                 INACTIVE
LOG_ARCHIVE_DEST_10                                VALID     USE_DB_RECOVERY_FILE_DEST

10 rows selected.

SQL>

到相應的目錄下找到時間比較久遠的文件，delete,空間就釋放了，

因為我這裡是測試環境，所以沒有考慮那麼複雜

[oracle@croracle02 dbs]$ df -lh
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
                       18G  8.8G  7.8G  54% /
/dev/sda1              99M   12M   82M  13% /boot
tmpfs                1006M  667M  340M  67% /dev/shm
/dev/sdd1             2.0G  843M  1.2G  42% /u02

 

記錄一下，備忘