Oracle 11g 啟用 Archive Log Mode

Oracle 11g 啟用 Archive Log Mode

我從Oracle下載Pre-Built Developer VMs，內建11gR2的Database，但是並沒有啟用Archive Log Mode

SQL> archive log list;
Database log mode            No Archive Mode
Automatic archival            Disabled
Archive destination           
Oldest online log sequence     389
Current log sequence            391

關閉DB，重啟到mount階段
SQL> startup mount
SQL> alter database archivelog
（alter database noarchivelog; –停用Archive Log Mode）
SQL> alter database open;
SQL> alter system set log_archive_start=true scope=spfile;

查詢是否已啟用
SQL> archive log list;
Database log mode            Archive Mode
Automatic archival            Enabled
Archive destination            /backup/archive
Oldest online log sequence     389
Next log sequence to archive   391
Current log sequence            391

接下來的步驟就發生錯誤了，是因為11g已廢棄這個參數，所以不用再設定這個參數
SQL> alter system set log_archive_start=true scope=spfile;
ORA-32006: LOG_ARCHIVE_START initialization parameter has been deprecated

設定Archive Log存放的路徑，記很要加上LOCATION才能正確設定
SQL> alter system Set LOG_ARCHIVE_DEST_1='LOCATION=/backup/archive' scope=spfile;

設定Archive Log檔名的格式
SQL> alter system set log_archive_format='orcl_%t_%s_%r.arc' scope=spfile ;

格式說明如下：
%s log sequence number
%S log sequence number, zero filled
%tthread number
%Tthread number, zero filled
%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

另外，在Document上有提到為了讓Archive log的檔名具有唯一值，檔名格式必需有%s、%t、%r。
SQL> alter system set log_archive_format='orcl_%s.arc' scope=spfile;
SQL> shutdowm immediate
SQL> startup
ORA-19905: log_archive_format must contain %s, %t and %r

最後，建立新的pfile
create pfile from spfile;