檢查 Archive Log Mode 狀態

關閉時：

SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     2178
Current log sequence           2180
SQL> select log_mode, open_mode, name from v$database;
LOG_MODE     OPEN_MODE  NAME
------------ ---------- ---------
NOARCHIVELOG READ WRITE WIN

開啟時：

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /opt/oracle/arclog
Oldest online log sequence     2178
Next log sequence to archive   2180
Current log sequence           2180

第一次啟用 Archive Log

NOTE:

1. 要啟用 Archive Log 時，必須重啟一次 DB，為了縮短 downtime，可以在停機前先設定參數 log_archive_dest_1。
2. log_archive_dest 與 log_archive_dest_1 只能設定其中一個
3. 如果沒有設定 log_archive_dest_1，啟動後，預設目錄會使用
   db_recovery_file_dest = /opt/oracle/flash_recovery_area
    

設定 Archive Log 的儲存路徑

sqlplus / as sysdba
SQL> show parameters recovery_file_dest
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /opt/oracle/flash_recovery_area
db_recovery_file_dest_size           big integer 2G
SQL> alter system set log_archive_dest_1='LOCATION=/opt/oracle/arclog' scope=spfile;
SQL> alter system set log_archive_format='mysid_%s_%t_%r.arc' scope=spfile;
SQL> show parameters log_archive;
SQL> shutdown immediate
SQL> startup mount
SQL> show parameters log_archive
SQL> alter database archivelog;
SQL> alter database open;
SQL> archive log list
SQL> shutdown immediate
SQL> startup

Enable Force_Logging

SQL> alter database force logging;

TIPs:

To disable Force Logging:
> alter database no force logging;
> alter tablespace <tablespace-name> no force logging;

To check the status:
> select log_mode,force_logging from v$database;
> select tablespace_name,logging,force_logging from dba_tablespaces;

驗證

SQL> alter system switch logfile;
或
SQL> alter system archive log current;

SQL> select log_mode, force_logging from v$database;

關閉 Archive Log

關閉

SQL> shutdown immediate
SQL> startup mount
SQL> alter database noarchivelog;
SQL> alter database open;

開啟

SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open; 

技巧：Archive Log 目錄爆掉了，怎辦？

cd /opt/oracle/arclog
for ((n=24;n<=43;n++));do mv win_23${n}_1_827060640.arc bak_tmp/; done
sqlplus / as sysdba
// validate all archvelogs in your disk. If some are missing, it will treat it as expired.
RMAN> crosscheck archivelog all;
// it ill delete expired archivelog and updates your controlfile.
RMAN> delete expired archivelog all;

rman target / nocatalog
RMAN > backup archivelog all delete input format '/opt/oracle/oradata/orabak/win/backup_arc/arc_20140328-1729';

RMAN> delete archivelog all;
RMAN> delete force archivelog all;
RMAN> delete archivelog sequence 209; 

RMAN> list archivelog all completed before 'sysdate-1';
RMAN> delete archivelog all completed before 'sysdate-1';

技巧：如何檢查 Archive Log 的紀錄

SQL> select THREAD#,sequence#,FIRST_TIME,COMPLETION_TIME from v$archived_log order by thread#,sequence#;