3
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

Oracle アーカイブログ代替出力先

Posted at

##概要
アーカイブログ用領域が満杯になると、REDOログをアーカイブすることができなくなり、DBが停止したり更新できなくなる。
それを避けるため、バックアップやアーカイブログのサイズが一定以上になった時に別領域にアーカイブログを出すことが可能。
設定は初期パラメータで行う。

  • 通常はFRA領域にアーカイブログを出力。
  • FRA領域の最大サイズを設定し、それを超える場合に別領域に出力する
  • 出力先を元に戻すには、ファイルを削除してFRA領域を最大サイズ以下にして手動で戻す

##手順
###設定

  • db_recovery_file_dest_size(FRA最大)を900M(テストのため)に設定
  • log_archive_dest1とlog_archive_dest2を設定
$ sqlplus / as sysdba
SQL> alter system set db_recovery_file_dest_size=900M;
SQL> select space_limit/1024/1024/1024 as "limit(GB)",space_used/1024/1024/1024 as "used(GB)" from v$recovery_file_dest;

 limit(GB)   used(GB)
---------- ----------
 .87890625 .753207684

SQL> alter system set log_archive_dest_2="location=/u01/app/oracle/oradata/datastore/ro121";
SQL> alter system set log_archive_dest_1=
     "location=USE_DB_RECOVERY_FILE_DEST REOPEN=0 MAX_FAILURE=1 ALTERNATE=LOG_ARCHIVE_DEST_2 MANDATORY";
SQL> alter system set log_archive_dest_state_2=alternate;
SQL> set linesize 120;
SQL> show parameter log_archive_dest;

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
log_archive_dest_1                   string                            location=USE_DB_RECOVERY_FILE_
                                                                       DEST REOPEN=0 MAX_FAILURE=1 AL
                                                                       TERNATE=LOG_ARCHIVE_DEST_2 MAN
                                                                       DATORY
log_archive_dest_2                   string                            location=/u01/app/oracle/oradata/datastore/ro121

###DBを停止・起動

##切り替えのテスト
###データを登録しアーカイブログを作成

SQL> create table test1( v1 varchar2(2000) );
SQL> insert into test1 values ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');
SQL> commit;
SQL> insert into test1 select * from test1;
繰り返して増やす。
適宜アーカイブログをスイッチする。
SQL> alter system switch logfile;
log_archive_dest_2下にアーカイブログ(拡張子はdbf)ができるまで繰り返す。

###切り替わったアーカイブログの確認

set linesize 200
set pagesize 1000
col name format a110
select recid,name from v$archived_log where name is not null order by 1;

    RECID NAME
---------- --------------------------------------------------------------------------------------------------------------
         3 /u01/app/oracle/fast_recovery_area/datastore/ro121/RO121/archivelog/2018_05_07/o1_mf_1_23_fgzh2my6_.arc
         4 /u01/app/oracle/fast_recovery_area/datastore/ro121/RO121/archivelog/2018_05_07/o1_mf_1_24_fgzh3649_.arc
         5 /u01/app/oracle/fast_recovery_area/datastore/ro121/RO121/archivelog/2018_05_07/o1_mf_1_25_fgzh3r7n_.arc
         6 /u01/app/oracle/fast_recovery_area/datastore/ro121/RO121/archivelog/2018_05_07/o1_mf_1_26_fgzh7f7l_.arc
         7 /u01/app/oracle/fast_recovery_area/datastore/ro121/RO121/archivelog/2018_05_07/o1_mf_1_27_fgzh8cbq_.arc
         8 /u01/app/oracle/fast_recovery_area/datastore/ro121/RO121/archivelog/2018_05_07/o1_mf_1_28_fgzh93r2_.arc
         9 /u01/app/oracle/fast_recovery_area/datastore/ro121/RO121/archivelog/2018_05_07/o1_mf_1_29_fgzh9789_.arc
        10 /u01/app/oracle/fast_recovery_area/datastore/ro121/RO121/archivelog/2018_05_07/o1_mf_1_30_fgzh9c5g_.arc
        11 /u01/app/oracle/fast_recovery_area/datastore/ro121/RO121/archivelog/2018_05_07/o1_mf_1_31_fgzhbxno_.arc
        12 /u01/app/oracle/fast_recovery_area/datastore/ro121/RO121/archivelog/2018_05_07/o1_mf_1_32_fgzhc5v1_.arc
        13 /u01/app/oracle/oradata/datastore/ro121/1_33_975257397.dbf

###FRA領域を減らす

###領域が開いたことを確認

SQL> select space_limit/1024/1024/1024 as "limit(GB)",space_used/1024/1024/1024 as "used(GB)" from v$recovery_file_dest;

 limit(GB)   used(GB)
---------- ----------
 .87890625 .145755291

###更にアーカイブログを出力

SQL> alter system switch logfile;
を3回実施してから下記
SQL> select recid,name from v$archived_log where name is not null order by 1;

     RECID NAME
---------- --------------------------------------------------------------------------------------------------------------
         5 /u01/app/oracle/fast_recovery_area/datastore/ro121/RO121/archivelog/2018_05_07/o1_mf_1_25_fgzh3r7n_.arc
         6 /u01/app/oracle/fast_recovery_area/datastore/ro121/RO121/archivelog/2018_05_07/o1_mf_1_26_fgzh7f7l_.arc
         7 /u01/app/oracle/fast_recovery_area/datastore/ro121/RO121/archivelog/2018_05_07/o1_mf_1_27_fgzh8cbq_.arc
         8 /u01/app/oracle/fast_recovery_area/datastore/ro121/RO121/archivelog/2018_05_07/o1_mf_1_28_fgzh93r2_.arc
         9 /u01/app/oracle/fast_recovery_area/datastore/ro121/RO121/archivelog/2018_05_07/o1_mf_1_29_fgzh9789_.arc
        10 /u01/app/oracle/fast_recovery_area/datastore/ro121/RO121/archivelog/2018_05_07/o1_mf_1_30_fgzh9c5g_.arc
        11 /u01/app/oracle/fast_recovery_area/datastore/ro121/RO121/archivelog/2018_05_07/o1_mf_1_31_fgzhbxno_.arc
        12 /u01/app/oracle/fast_recovery_area/datastore/ro121/RO121/archivelog/2018_05_07/o1_mf_1_32_fgzhc5v1_.arc
        13 /u01/app/oracle/oradata/datastore/ro121/1_33_975257397.dbf
        14 /u01/app/oracle/oradata/datastore/ro121/1_34_975257397.dbf
        15 /u01/app/oracle/oradata/datastore/ro121/1_35_975257397.dbf
        16 /u01/app/oracle/oradata/datastore/ro121/1_36_975257397.dbf

FRA領域が空いても出力先は戻らない。

###状況確認

SQL> select dest_id,status from v$archive_dest_status where dest_id <= 2;

   DEST_ID STATUS
---------- ---------------------------
         1 DEFERRED
         2 VALID
  • 1が利用禁止になっている。(show parameterの値とは異なる場合がある。RACの場合各インスタンスで確認必要)

###出力先を戻す

SQL> alter system set log_archive_dest_state_1=enable;
SQL> alter system set log_archive_dest_state_2=alternate;
SQL> select dest_id,status from v$archive_dest_status where dest_id <= 2;

   DEST_ID STATUS
---------- ---------------------------
         1 VALID
         2 ALTERNATE

###確認のため、アーカイブログ出力

SQL> alter system switch logfile;
SQL> select recid,name from v$archived_log where name is not null order by 1;

     RECID NAME
---------- --------------------------------------------------------------------------------------------------------------
         5 /u01/app/oracle/fast_recovery_area/datastore/ro121/RO121/archivelog/2018_05_07/o1_mf_1_25_fgzh3r7n_.arc
         6 /u01/app/oracle/fast_recovery_area/datastore/ro121/RO121/archivelog/2018_05_07/o1_mf_1_26_fgzh7f7l_.arc
         7 /u01/app/oracle/fast_recovery_area/datastore/ro121/RO121/archivelog/2018_05_07/o1_mf_1_27_fgzh8cbq_.arc
         8 /u01/app/oracle/fast_recovery_area/datastore/ro121/RO121/archivelog/2018_05_07/o1_mf_1_28_fgzh93r2_.arc
         9 /u01/app/oracle/fast_recovery_area/datastore/ro121/RO121/archivelog/2018_05_07/o1_mf_1_29_fgzh9789_.arc
        10 /u01/app/oracle/fast_recovery_area/datastore/ro121/RO121/archivelog/2018_05_07/o1_mf_1_30_fgzh9c5g_.arc
        11 /u01/app/oracle/fast_recovery_area/datastore/ro121/RO121/archivelog/2018_05_07/o1_mf_1_31_fgzhbxno_.arc
        12 /u01/app/oracle/fast_recovery_area/datastore/ro121/RO121/archivelog/2018_05_07/o1_mf_1_32_fgzhc5v1_.arc
        13 /u01/app/oracle/oradata/datastore/ro121/1_33_975257397.dbf
        14 /u01/app/oracle/oradata/datastore/ro121/1_34_975257397.dbf
        15 /u01/app/oracle/oradata/datastore/ro121/1_35_975257397.dbf
        16 /u01/app/oracle/oradata/datastore/ro121/1_36_975257397.dbf
        17 /u01/app/oracle/fast_recovery_area/datastore/ro121/RO121/archivelog/2018_05_07/o1_mf_1_37_fgzol0g4_.arc
  • 17が元の出力先になっている。
3
2
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
3
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?