[관리] 오라클 archive 모드로 변경하기

######################################################

  오라클 archive 모드로 변경하기

######################################################

———————————-

archive 상태 보기

———————————-

SQL> archive log list

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            /oracle/ora92/dbs/arch

Oldest online log sequence     3

Current log sequence           5

—————————————

현재 사용되는 redologfile 갯수 보기

—————————————

SQL> select group#, member from v$logfile;

    GROUP# MEMBER

———- ———————————–

         3 /oracle/oradata/ocp/redo03.log

         2 /oracle/oradata/ocp/redo02.log

         1 /oracle/oradata/ocp/redo01.log

SQL> archive log list

Database log mode             No Archive Mode

Automatic archival              Disabled                                  – 아래의 2번에 해당

Archive destination             /oracle/ora92/dbs/arch              – 아래의 1번에 해당

Oldest online log sequence     3

Current log sequence             5

SQL>

—————————————————————–

archive 모드로 변경시 수정할 파일들

1. log_archive_dest

2. log_archive_format (archive 파일명을 결정 ex) %t_%s.dbf )

3. log_archive_start (archive process가 뜨느 않뜨냐에 대한 설정)

—————————————————————–

SQL> sho parameters log_archive

NAME                                 TYPE        VALUE

———————————— ———– ——————————

log_archive_dest                     string

log_archive_dest_1                   string

log_archive_dest_10                  string

log_archive_dest_2                   string

log_archive_dest_3                   string

log_archive_dest_4                   string

log_archive_dest_5                   string

log_archive_dest_6                   string

log_archive_dest_7                   string

log_archive_dest_8                   string

log_archive_dest_9                   string

NAME                                 TYPE        VALUE

———————————— ———– ——————————

log_archive_dest_state_1             string      enable

log_archive_dest_state_10            string      enable

log_archive_dest_state_2             string      enable

log_archive_dest_state_3             string      enable

log_archive_dest_state_4             string      enable

log_archive_dest_state_5             string      enable

log_archive_dest_state_6             string      enable

log_archive_dest_state_7             string      enable

log_archive_dest_state_8             string      enable

log_archive_dest_state_9             string      enable

log_archive_duplex_dest              string

NAME                                 TYPE        VALUE

———————————— ———– ——————————

log_archive_format                   string      %t_%s.dbf

log_archive_max_processes            integer     2

log_archive_min_succeed_dest         integer     1

log_archive_start                    boolean     FALSE

log_archive_trace                    integer     0

SQL>

————————————————————-

log_archive_start를 TRUE로 설정 (방법 1 : 동적인 방법)

————————————————————-

SQL> alter system set

  2  log_archive_start = TRUE scope=spfile;

System altered.

SQL> alter system set

  2 log_archive_dest=’/oracle/admin/ocp/ARCH’ scope=spfile;

  

System altered.

——————————————————–

pfile의 다음의 두 줄을 추가 (방법2 : 수동적인 방법)

——————————————————–

SQL> startup

ORACLE instance started.

Total System Global Area  235999352 bytes

Fixed Size                   450680 bytes

Variable Size             201326592 bytes

Database Buffers           33554432 bytes

Redo Buffers                 667648 bytes

Database mounted.

Database opened.

SQL>

SQL>

SQL> create pfile from spfile;

File created.

—다음 두 줄을 추가한다.—-

*.log_archive_start=TRUE

*.log_archive_dest=’/oracle/admin/ocp/ARCH’

— 폴더 만들고 다음 넘어가자(pfile의 설정대로..)

/oracle/admin/ocp/ 밑에 ARCH 폴더를 만든다.

————————————————————–

다시 오라클에 접속 후

방법 1의 경우 : 바로 적용된 것을 볼 수 있다.

방법 2의 경우 : pfile을 spfile을 덮어쓴다.

그리고 archive log list하면 바뀐 내용 확인

————————————————————–

SQL> create spfile from pfile;

File created.

SQL> startup mount

ORACLE instance started.

Total System Global Area  235999352 bytes

Fixed Size                   450680 bytes

Variable Size             201326592 bytes

Database Buffers           33554432 bytes

Redo Buffers                 667648 bytes

Database mounted.

SQL> archive log list

Database log mode              No Archive Mode

Automatic archival             Enabled

Archive destination            /oracle/admin/ocp/ARCH

Oldest online log sequence     3

Current log sequence           5

——————————————–

startup mount상태에서

alter database archivelog하면

리스트 중에 한 줄이 늘어난다.

———————————————

SQL> startup mount

ORACLE instance started.

Total System Global Area  235999352 bytes

Fixed Size                   450680 bytes

Variable Size             201326592 bytes

Database Buffers           33554432 bytes

Redo Buffers                 667648 bytes

Database mounted.

SQL>      

SQL> alter database archivelog;

Database altered.

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /oracle/admin/ocp/ARCH

Oldest online log sequence     3

Next log sequence to archive   5

Current log sequence           5

Next log sequence to archive   5 의 의미는

redo log file 5번부터는 archive모드로 log파일 기록 된다.  

——————————————————

open 한 후

alter system switch logfile를 입력하면

sequence번호가 늘어난다.

없어진 것들은..

log_archive_dest의 경로에 저장이 된다.

——————————————————–  

SQL> alter database open;

Database altered.

SQL>

SQL>

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /oracle/admin/ocp/ARCH

Oldest online log sequence     3

Next log sequence to archive   5

Current log sequence           5

SQL>

SQL>

SQL> alter system switch logfile;

System altered.

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /oracle/admin/ocp/ARCH

Oldest online log sequence     4

Next log sequence to archive   6

Current log sequence           6