【Oracle】ORA-00257 完全ガイド｜アーカイブログ満杯・FRA 不足の原因・空き確保・RMAN 削除手順まで解説

-- まず SYSDBA で接続する（ORA-00257 中も内部接続は可能）
-- SQL*Plus: sqlplus / as sysdba
-- または: sqlplus sys/password@orcl as sysdba

-- アーカイブログの宛先と状態を確認する
SELECT dest_id, dest_name, status, target,
       archiver, schedule, destination, error
FROM   V$ARCHIVE_DEST
WHERE  status != 'INACTIVE'
ORDER BY dest_id;
-- status: VALID（正常）, ERROR（エラー）, FULL（満杯）
-- error 列にエラーメッセージが表示される

-- FRA（高速リカバリ領域）の使用状況を確認する
SELECT name,
       ROUND(space_limit / 1073741824, 2) AS limit_gb,
       ROUND(space_used  / 1073741824, 2) AS used_gb,
       ROUND(space_reclaimable / 1073741824, 2) AS reclaimable_gb,
       ROUND(space_used / NULLIF(space_limit, 0) * 100, 2) AS pct_used
FROM   V$RECOVERY_FILE_DEST;
-- pct_used が 100% に近い場合は FRA 超過が原因

-- FRA 内のファイル種類別の使用量を確認する
SELECT file_type,
       ROUND(PERCENT_SPACE_USED, 2) AS pct_used,
       ROUND(PERCENT_SPACE_RECLAIMABLE, 2) AS pct_reclaimable,
       NUMBER_OF_FILES
FROM   V$RECOVERY_AREA_USAGE
ORDER BY PERCENT_SPACE_USED DESC;
-- ARCHIVED LOG が大量にある場合はアーカイブログの削除が有効

-- アーカイブログの一覧を確認する（サイズと日時）
SELECT sequence#, first_time, next_time,
       ROUND(blocks * block_size / 1073741824, 3) AS size_gb,
       name, deleted
FROM   V$ARCHIVED_LOG
WHERE  standby_dest = 'NO'
  AND  deleted = 'NO'
ORDER BY first_time;

-- アーカイブログ先のディスク使用量を確認する（OS コマンド）
-- SELECT value FROM V$PARAMETER WHERE name = 'log_archive_dest_1';
-- → 取得したパスで OS 側のディスク使用量を確認する

# RMAN に SYSDBA で接続する
rman target /

# ① まず CROSSCHECK でアーカイブログの状態を確認する
RMAN> CROSSCHECK ARCHIVELOG ALL;

# ② バックアップ済みのアーカイブログをすべて削除する（推奨）
# バックアップされていないログは削除されないため安全
RMAN> DELETE ARCHIVELOG ALL BACKED UP 1 TIMES TO DISK;

# ③ より古いアーカイブログを削除する（日時指定）
# 例: 3日より前のアーカイブログをすべて削除する
RMAN> DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-3';

# ④ EXPIRED のアーカイブログをカタログから削除する
RMAN> DELETE EXPIRED ARCHIVELOG ALL;

# ⑤ すべてのアーカイブログを削除する（最終手段・リカバリ不能になる可能性あり）
# ※ 直前にフルバックアップを取得した直後でないと使用禁止
# RMAN> DELETE NOPROMPT ARCHIVELOG ALL;

# 削除後に FRA の使用状況を再確認する
RMAN> LIST ARCHIVELOG ALL;

-- 現在の FRA 設定を確認する
SHOW PARAMETER DB_RECOVERY_FILE_DEST;
SHOW PARAMETER DB_RECOVERY_FILE_DEST_SIZE;

-- FRA のサイズを拡張する（例: 50GB → 100GB）
-- ※ この変更は即時に有効になり、DB の再起動は不要
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 100G SCOPE=BOTH;

-- FRA の場所を変更する（別ディスクに移す場合）
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/u02/fra' SCOPE=BOTH;

-- FRA を使用せず、固定ディレクトリにアーカイブする場合の設定
-- LOG_ARCHIVE_DEST_1 に明示的なパスを設定する
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1 = 'LOCATION=/u01/archive' SCOPE=BOTH;

-- FRA を無効にする場合（FRA を使っていない場合は関係なし）
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '' SCOPE=BOTH;

-- アーカイブログの保持期間と自動削除を設定する
-- RMAN の保持ポリシー: 7日以内にリカバリできるように保持する
-- アーカイブログは RMAN バックアップ後に自動で削除可能にする

# RMAN に接続する
rman target /

# 保持ポリシーを設定する（7日以内のリカバリを保証する）
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

# バックアップ後のアーカイブログを自動削除する設定
# DELETE INPUT: バックアップ時にアーカイブログを自動削除する
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DISK;

# バックアップスクリプトに DELETE INPUT を追加する
# 毎日のバックアップにアーカイブログの削除を組み込む
RMAN> RUN {
    BACKUP ARCHIVELOG ALL DELETE INPUT;  # アーカイブログのバックアップと削除
    BACKUP DATABASE;                     # データベースのフルバックアップ
    DELETE NOPROMPT OBSOLETE;            # 保持ポリシー外のバックアップを削除
}

# 現在の保持ポリシーを確認する
RMAN> SHOW ALL;

-- アーカイブ先の設定をすべて確認する
SELECT name, value FROM V$PARAMETER
WHERE  name LIKE 'log_archive_dest%'
ORDER BY name;
-- log_archive_dest_1: メインのアーカイブ先（ローカルまたは FRA）
-- log_archive_dest_2: Data Guard スタンバイなどの追加宛先
-- log_archive_dest_state_n: ENABLE / DEFER / ALTERNATE（宛先の状態）

-- 複数宛先のうち特定の宛先を一時的に無効にする
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2 = 'DEFER';  -- 宛先2を一時停止
-- ※ Data Guard 構成では宛先2を無効にするとスタンバイへのログ転送が止まるため注意

-- 代替宛先を設定する（メイン宛先が満杯になったら代替先にアーカイブする）
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1 =
    'LOCATION=/u01/archive REOPEN=30 MAX_FAILURE=5 ALTERNATE=LOG_ARCHIVE_DEST_2';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2 =
    'LOCATION=/u02/archive_alt';  -- 代替先

-- アーカイブログのディレクトリの使用量をOS側で確認する（SQL*Plus から）
-- ホスト側では df -h /u01/archive でディスク使用量を確認する

-- ①. 現在のモードを確認する
SELECT log_mode FROM V$DATABASE;

-- ②. データベースを停止する
SHUTDOWN IMMEDIATE;

-- ③. MOUNT モードで起動する
STARTUP MOUNT;

-- ④. NOARCHIVELOG モードに変更する（アーカイブログが作成されなくなる）
ALTER DATABASE NOARCHIVELOG;

-- ⑤. 通常起動する
ALTER DATABASE OPEN;

-- ⑥. ディスクの空き確保後（古いアーカイブログを手動削除後）、ARCHIVELOG に戻す
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
-- ARCHIVELOG に戻したら、すぐにフルバックアップを取得する
-- rman target / <<EOF
-- BACKUP DATABASE;
-- EOF