복구 원리 (No Archive Log Mode)

1. 사용자 데이터 A 입력 후 백업, 이후 데이터 B, C 입력

1. 새로운 데이터 D, E 입력 - LGWR 이 기존 리두 로그 파일을 덮어 씀

1. 데이터 파일 삭제 장애 발생

1. 1. Data File 복원 (Restore)
   2. Data File 복구 (Recover)
      • Control File 의 Checkpoint scn 과 Data File 의 Checkpoint scn 비교 : 5 != 1, SCN[2] 해당 Redo Log File 필요
      • 필요시 ONLINE REDO LOG FILE 반영 하여 복구 시도 : SCN[2] 에 해당 하는 Redo Log File 은 D, E 데이터 입력 시 덮어 씌워진 바 있음
      • 필요한 Redo Log File 이 없으므로 복구 불가 (순차적 복구시 중간 Redo Log File 이 없으면 이후도 복구 불가)

복구 원리 (Archive Log Mode)

• Redo Log File 을 덮어쓰기 전에 기존 내용을 다른 곳으로 복사 (위와 같은 케이스에서 복구 가능해짐)
• Log Switch 가 발생하면 Archiver(ARCn)가 즉시 동작

1. 사용자 데이터 A 입력 후 백업, 이후 데이터 B, C 입력

1. 새로운 데이터 D, E 입력 - LGWR 이 기존 리두 로그 파일을 Archive 하고 덮어 씀

1. 데이터 파일 삭제 장애 발생

1. Archived Redo Log File 로 정상 복구

• 데이터를 안전하게 지키기 위해서는 Archived Log Mode 사용
  • Archived Redo Log File 저장 공간 필요
  • 저장 공간 부족, 쓰기 권한 없음 등 으로 Archive Hang 발생 시 데이터베이스가 중단 됨
  • 상대적으로 관리가 까다로움, 처음 설치시 기본 값이 아님

Archived Log Mode 로 변경하기

SQL>  show parameter log_archive_format

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
log_archive_format                   string                 %t_%s_%r.dbf
SQL> show parameter log_archive_dest_1

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
log_archive_dest_1                   string
log_archive_dest_10                  string
log_archive_dest_11                  string
log_archive_dest_12                  string
log_archive_dest_13                  string
log_archive_dest_14                  string
log_archive_dest_15                  string
log_archive_dest_16                  string
log_archive_dest_17                  string
log_archive_dest_18                  string
log_archive_dest_19                  string
SQL> alter system set log_archive_dest_1='location=/ORA_ARCH/utf8' scope=spfile;	<<< 미 지정시 db_recovery_file_dest 경로 적용 됨 ($ORACLE_HOME/dbs)

System altered.

SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  534462464 bytes
Fixed Size                  2221336 bytes
Variable Size             339741416 bytes
Database Buffers          184549376 bytes
Redo Buffers                7950336 bytes
Database mounted.
SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /ORA_ARCH/utf8
Oldest online log sequence     2347
Current log sequence           2349
SQL> alter database archivelog;

Database altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /ORA_ARCH/utf8
Oldest online log sequence     2347
Next log sequence to archive   2349
Current log sequence           2349
SQL> alter database open;

Database altered.

SQL> alter system switch logfile;		<<< 아카이브 로그 모드로 변경

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> !ls -al /ORA_ARCH/utf8
total 816
drwxr-xr-x    2 oracle   dba             256 Oct 23 09:29 .
drwxr-xr-x    5 oracle   dba            4096 Oct 23 09:26 ..
-rw-r-----    1 oracle   dba          402432 Oct 23 09:29 1_2349_767444221.dbf
-rw-r-----    1 oracle   dba            1024 Oct 23 09:29 1_2350_767444221.dbf
-rw-r-----    1 oracle   dba            2048 Oct 23 09:29 1_2351_767444221.dbf

SQL>

Archive Hang 발생시 해결하는 방법

• 데이터베이스 종료 없이 해결
• 발생 원인 : 경로 삭제, 공간 부족, 권한 없음

• Hang 유발

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /ORA_ARCH/utf8
Oldest online log sequence     2350
Next log sequence to archive   2352
Current log sequence           2352
SQL> !ls -ald /ORA_ARCH/utf8
drwxr-xr-x    2 oracle   dba             256 Oct 23 09:29 /ORA_ARCH/utf8

SQL> !chmod -w /ORA_ARCH/utf8

SQL> !ls -ald /ORA_ARCH/utf8
dr-xr-xr-x    2 oracle   dba             256 Oct 23 09:29 /ORA_ARCH/utf8

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /
-- Hang 발생

• Alert Log

Fri Oct 23 09:56:10 2015
Thread 1 advanced to log sequence 2353 (LGWR switch)
  Current log# 1 seq# 2353 mem# 0: /oradata2/system/redo01.log
Fri Oct 23 09:56:10 2015
ARC0: Error 19504 Creating archive log file to '/ORA_ARCH/utf8/1_2352_767444221.dbf'
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance UTF8 - Archival Error
ORA-16038: log 3 sequence# 2352 cannot be archived
ORA-19504: failed to create file ""
ORA-00312: online log 3 thread 1: '/oradata2/system/redo03.log'
Thread 1 advanced to log sequence 2354 (LGWR switch)
  Current log# 2 seq# 2354 mem# 0: /oradata2/system/redo02.log
Fri Oct 23 09:56:17 2015
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance UTF8 - Archival Error
ORA-16014: log 3 sequence# 2352 not archived, no available destinations
ORA-00312: online log 3 thread 1: '/oradata2/system/redo03.log'
Fri Oct 23 09:56:20 2015
Thread 1 cannot allocate new log, sequence 2355
Checkpoint not complete
  Current log# 2 seq# 2354 mem# 0: /oradata2/system/redo02.log
ORACLE Instance UTF8 - Can not allocate log, archival required
Thread 1 cannot allocate new log, sequence 2355
All online logs needed archiving
  Current log# 2 seq# 2354 mem# 0: /oradata2/system/redo02.log

• Hang 해소

$ ls -ald /ORA_ARCH/utf8
dr-xr-xr-x    2 oracle   dba             256 Oct 23 09:29 /ORA_ARCH/utf8
$ chmod +w /ORA_ARCH/utf8
$ ls -ald /ORA_ARCH/utf8
drwxr-xr-x    2 oracle   dba             256 Oct 23 09:29 /ORA_ARCH/utf8

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Fri Oct 23 10:03:09 2015

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning option

SQL> alter system switch logfile;

System altered.

SQL>

-- Alert Log
Fri Oct 23 10:01:55 2015
Archived Log entry 25 added for thread 1 sequence 2352 ID 0x79288dfd dest 1:
krse_arc_driver_core: Successful archiving of previously failed ORL
Archiver process freed from errors. No longer stopped
Fri Oct 23 10:01:55 2015
Archived Log entry 26 added for thread 1 sequence 2353 ID 0x79288dfd dest 1:
Fri Oct 23 10:01:55 2015
Thread 1 advanced to log sequence 2355 (LGWR switch)
  Current log# 3 seq# 2355 mem# 0: /oradata2/system/redo03.log
Fri Oct 23 10:01:55 2015
Archived Log entry 27 added for thread 1 sequence 2354 ID 0x79288dfd dest 1:
Fri Oct 23 10:03:13 2015
Thread 1 advanced to log sequence 2356 (LGWR switch)
  Current log# 1 seq# 2356 mem# 0: /oradata2/system/redo01.log
Fri Oct 23 10:03:13 2015
Archived Log entry 28 added for thread 1 sequence 2355 ID 0x79288dfd dest 1:

Archive log 압축하여 저장공간 줄이기

• 50% 이상 압축 됨
• 데이터 복제 솔루션(DataGuard, GoldenGate 등)과 호환성 문제 있음 (문서 ID 1308033.1)
  • There is currently no Oracle GoldenGate version that supports Archive log compression or any immediate plans to make this function available.

SQL> select archivelog_compression from v$database;

ARCHIVELOG_COMPR
----------------
DISABLED

SQL> alter database archivelog compress enable;

Database altered.

SQL> select archivelog_compression from v$database;

ARCHIVELOG_COMPR
----------------
ENABLED

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.