對這文章發表回應
發表限制: 非會員 可以發表
發表者： 冷日 發表時間： 2016/2/1 4:37:00
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;
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;
原文出處：平凡的幸福: Oracle 11g 啟用 Archive Log Mode