資料庫啟動歸檔模式

檢查資料庫是否啟動歸檔模式

SYS@PROD>archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u01/app/oracle/product/10.2.0/db_1/dbs/arch
Oldest online log sequence     9
Current log sequence           11
SYS@PROD>
SYS@PROD>select open_mode,log_mode from v$database;

OPEN_MODE  LOG_MODE
---------- ------------
READ WRITE NOARCHIVELOG

歸檔檔案存放路徑

SYS@PROD>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     2
log_archive_min_succeed_dest         integer     1
log_archive_start                    boolean     FALSE
log_archive_trace                    integer     0
remote_archive_enable                string      true
standby_archive_dest                 string      ?/dbs/arch
SYS@PROD>
SYS@PROD>alter system set log_archive_dest_1='/u01/app/oracle' scope=spfile;
alter system set log_archive_dest_1='/u01/app/oracle' scope=spfile
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-16179: incremental changes to "log_archive_dest_1" not allowed with SPFILE

錯誤資訊需要是需要在路徑前加上location。

SYS@PROD>alter system set log_archive_dest_1='location=/u01/app/oracle' scope=spfile;

System altered.

重啟資料庫到mount狀態

SYS@PROD>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@PROD>

mount
ORACLE instance started.

Total System Global Area  524288000 bytes
Fixed Size                  1220384 bytes
Variable Size             155189472 bytes
Database Buffers          364904448 bytes
Redo Buffers                2973696 bytes
Database mounted.
SYS@PROD>

啟動歸檔模式，關閉歸檔模式執行alter database no archivelog;

SYS@PROD>alter database archivelog;

Database altered.

開啟資料庫
SYS@PROD>alter database open;

Database altered.

SYS@PROD>

檢查資料庫模式

SYS@PROD>select open_mode,log_mode from v$database;

OPEN_MODE  LOG_MODE
---------- ------------
READ WRITE ARCHIVELOG

SYS@PROD>
SYS@PROD>archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle
Oldest online log sequence     9
Next log sequence to archive   11
Current log sequence           11
SYS@PROD>

檢查歸檔生成情況

SYS@PROD>set linesize 200
SYS@PROD>select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1         10  104857600          2 YES INACTIVE                659062 09-JAN-14
         2          1         11  104857600          2 NO  CURRENT                 753229 12-JAN-14
         3          1          9  104857600          2 YES INACTIVE                585681 07-JAN-14

使用下面的命令切換redo log產生歸檔日誌
SYS@PROD>alter system switch logfile;

System altered.

SYS@PROD>select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1         10  104857600          2 YES INACTIVE                659062 09-JAN-14
         2          1         11  104857600          2 YES ACTIVE                  753229 12-JAN-14
         3          1         12  104857600          2 NO  CURRENT                 787841 13-JAN-14

SYS@PROD>alter system switch logfile;

System altered.

SYS@PROD>select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1         13  104857600          2 NO  CURRENT                 787847 13-JAN-14
         2          1         11  104857600          2 YES ACTIVE                  753229 12-JAN-14
         3          1         12  104857600          2 YES ACTIVE                  787841 13-JAN-14

SYS@PROD>

檢查歸檔日誌路徑，發現產生歸檔日誌。

[oracle@ocm1 ~]$ cd /u01/app/oracle
[oracle@ocm1 oracle]$ ll
total 23032
-rw-r-----  1 oracle oinstall 23522816 Jan 13 15:05 1_11_835022046.dbf
-rw-r-----  1 oracle oinstall     1024 Jan 13 15:06 1_12_835022046.dbf
drwxr-xr-x  3 oracle oinstall     4096 Dec 24 14:02 admin
drwxr-xr-x 37 oracle oinstall     4096 Jan 13 15:02 agent10g
drwxr-xr-x  2 oracle oinstall     4096 Jan 13 14:58 archlog
drwxr-xr-x  2 oracle oinstall     4096 Jan 13 14:58 bakup
drwxr-xr-x  3 oracle oinstall     4096 Dec 24 14:02 oradata
drwxrwx---  7 oracle oinstall     4096 Jan 10 14:27 oraInventory
drwxrwx---  3 oracle oinstall     4096 Dec 23 21:10 product