兩隻小虎,一隻豬

將新建立的DB 設成archive log mode

1. set log_archive_dest_n
SQL> show parameter log_arch

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
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
log_archive_dest_8                   string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
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
log_archive_dest_state_9             string      enable

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_duplex_dest              string
log_archive_format                   string      %t_%s_%r.dbf
log_archive_local_first              boolean     TRUE
log_archive_max_processes            integer     2
log_archive_min_succeed_dest         integer     1
log_archive_start                    boolean     FALSE
log_archive_trace                    integer     0

SQL> alter system set log_archive_dest_1='location=/u02/orcl/arch_01';

System altered.

SQL> show parameter log_archive

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest                     string
log_archive_dest_1                   string      location=/u02/orcl/arch_01
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
log_archive_dest_8                   string
log_archive_dest_9                   string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
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
log_archive_dest_state_9             string      enable
log_archive_duplex_dest              string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_format                   string      %t_%s_%r.dbf
log_archive_local_first              boolean     TRUE
log_archive_max_processes            integer     2
log_archive_min_succeed_dest         integer     1
log_archive_start                    boolean     FALSE
log_archive_trace                    integer     0

2. Rename log_archive_format

log_archive_format 可以使用參數如下
%s ：log sequence number
%S： log sequence number, zero filled
%t ：thread number， RAC的節點中設有THREAD參數, 就是這個值了
%T：thread number, zero filled
其中這前4項是可以用在 9I 中的
%a： activation ID
%d： database ID
%r： resetlogs ID that ensures unique names are constructed for the archived log files across multiple incarnations of the database

Oracle 10g開始,log_archive_format初始化參數必須包含%s, %t 和 %r,以確保生成的archive log 名稱唯一.
（原因：10g能做穿越resetlog的恢復，所以要加%r。而9i的不能做穿越resetlog的恢復，所以沒有%r的參數）

SQL> alter system set log_archive_format='arch_%t_%s_%r.arc' scope=spfile ;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL>startup nomount ;
ORACLE instance started.

Total System Global Area 205520896 bytes
Fixed Size 2019576 bytes
Variable Size 113250056 bytes
Database Buffers 83886080 bytes
Redo Buffers 6365184 bytes

SQL> show parameter log_archive_format

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
log_archive_format                   string                            arch_%t_%s_%r.arc

3. alter database to archive log mode

SQL>alter database mount;

Database altered.

SQL> alter database archivelog ;

10g 以前的版本還需要另外執行啟動 Archive LOG Mode
SQL>alter system archive log start/stop ;

Database altered.
SQL> alter database open ;

Database altered.

使用archive log list 指令來看結果

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u02/orcl/arch_01
Oldest online log sequence 5
Next log sequence to archive 7
Current log sequence 7

SQL> alter system switch logfile;

System altered.

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u02/orcl/arch_01
Oldest online log sequence 6
Next log sequence to archive 8
Current log sequence 8

或者查詢v$database view 的log_mode 欄位是否為ARCHIVELOG

SQL> select log_mode from v$database ;

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