[Oracle] Redo Log File 관리하기(실습)

HYEOB KIM·2022년 12월 20일
oracle
0

Oracle

목록 보기
23/58

기본 실습

  1. Redo Log File 관련 정보 조회 방법
  2. 그룹 생성 방법
  3. 멤버 생성 방법
  4. 멤버 삭제 방법(그룹에 속한 멤버가 1개일 때는 그룹을 삭제해야 멤버가 지워집니다)
  5. 그룹 삭제 방법
SQL> a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
  2  from v$logfile a, v$log b
  3  where a.group#=b.group#
  4  order by 1, 2;

SQL> alter database add logfile group 4
  2  '/ORA19/app/oracle/oradata/ORACLE19/redo04_a.log' size 10M;
  
SQL> a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
  2  from v$logfile a, v$log b
  3  where a.group#=b.group#
  4  order by 1, 2;

SQL> alter database add logfile member
  2  '/ORA19/app/oracle/oradata/ORACLE19/redo04_b.log' to group 4;

SQL> a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
  2  from v$logfile a, v$log b
  3  where a.group#=b.group#
  4  order by 1, 2;

SQL> alter database drop logfile member
  2  '/ORA19/app/oracle/oradata/ORACLE19/redo04_b.log';

SQL> a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
  2  from v$logfile a, v$log b
  3  where a.group#=b.group#
  4  order by 1, 2;
  
SQL> alter database drop logfile group 4;

SQL> a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
  2  from v$logfile a, v$log b
  3  where a.group#=b.group#
  4  order by 1, 2;

실습 1

과제

모든 Redo Log File을 /home/oracle/disk1로 이동하세요.
그룹번호는 1, 2, 3으로 3개의 그룹을 만들고 각 멤버는 1개씩, 크기는 각 5M.

풀이

SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
from v$logfile a, v$log b
where a.group#=b.group#
order by 1, 2;

SQL> select member from v$logfile;

SQL> select status from v$instance;

SQL> shutdown immediate;

SQL> startup mount;

SQL> !mkdir /home/oracle/disk1

SQL> !cp /ORA19/app/oracle/oradata/ORACLE19/redo01.log /home/oracle/disk1/redo01.log
SQL> !cp /ORA19/app/oracle/oradata/ORACLE19/redo02.log /home/oracle/disk1/redo02.log
SQL> !cp /ORA19/app/oracle/oradata/ORACLE19/redo03.log /home/oracle/disk1/redo03.log

SQL> alter database rename
  2  file '/ORA19/app/oracle/oradata/ORACLE19/redo01.log'
  3  to '/home/oracle/disk1/redo01.log';

SQL> alter database rename
  2  file '/ORA19/app/oracle/oradata/ORACLE19/redo02.log'
  3  to '/home/oracle/disk1/redo02.log';

SQL> alter database rename
  2  file '/ORA19/app/oracle/oradata/ORACLE19/redo03.log'
  2  to '/home/oracle/disk1/redo03.log';

SQL> select member from v$logfile;

SQL> alter database open;

SQL> select status from v$instance;

SQL> rm /ORA19/app/oracle/oradata/ORACLE19/redo01.log /ORA19/app/oracle/oradata/ORACLE19/redo02.log /ORA19/app/oracle/oradata/ORACLE19/redo03.log

실습 2

과제

/home/oracle/disk1/redo01_a.log, redo02_a.log, redo03_a.log
/home/oracle/disk2/redo01_b.log, redo02_b.log, redo03_b.log
로 구성을 변경하세요.

  1. DB 종료
  2. startup mount
  3. Redo Log File을 대상 경로에 복사
  4. Control File 수정
  5. DB open

풀이

SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
  2  from v$logfile a, v$log b
  3  where a.group#=b.group#
  4  order by 1, 2;
  
SQL> select status from v$instance;

SQL> select member from v$logfile;

SQL> shutdown immediate;

SQL> startup mount;

SQL> !mkdir /home/oracle/disk1 /home/oracle/disk2

SQL> !cp /home/oracle/disk1/redo01.log /home/oracle/disk1/redo01_a.log
SQL> !cp /home/oracle/disk1/redo02.log /home/oracle/disk1/redo02_a.log
SQL> !cp /home/oracle/disk1/redo03.log /home/oracle/disk1/redo03_a.log
SQL> !cp /home/oracle/disk1/redo01_a.log /home/oracle/disk2/redo01_b.log
SQL> !cp /home/oracle/disk1/redo02_a.log /home/oracle/disk2/redo02_b.log
SQL> !cp /home/oracle/disk1/redo03_a.log /home/oracle/disk2/redo03_b.log

SQL> alter database rename
  2  file '/home/oracle/disk1/redo01.log'
  3  to '/home/oracle/disk1/redo01_a.log';
  
SQL> alter database rename
  2  file '/home/oracle/disk1/redo02.log'
  3  to '/home/oracle/disk1/redo02_a.log';

SQL> alter database rename
  2  file '/home/oracle/disk1/redo03.log'
  3  to '/home/oracle/disk1/redo03_a.log';

SQL> alter database add logfile member
  2  '/home/oracle/disk2/redo01_b.log' to group 1;

SQL> alter database add logfile member
  2  '/home/oracle/disk2/redo02_b.log' to group 2;
  
SQL> alter database add logfile member
  2  '/home/oracle/disk2/redo03_b.log' to group 3;

SQL> select member from v$logfile;

SQL> alter database open;

SQL> select status from v$instance;

$ rm /home/oracle/disk1/redo01.log /home/oracle/disk1/redo02.log /home/oracle/disk1/redo03.log

실습 3

/home/oracle/disk3/control01.ctl, redo01_a.log, redo02_a.log, redo03_a.log

/home/oracle/disk4/control02.ctl, redo01_b.log, redo02_b.log, redo03_b.log

/home/oracle/disk5/control03.ctl, redo01_c.log, redo02_c.log, redo03_c.log

로 구성하세요.

Control File 작업

  1. Control File을 대상 경로에 복사
  2. 초기화 Parameter File 수정
  3. DB 종료
  4. startup

Redo Log File 작업

  1. DB 종료
  2. startup mount
  3. Redo Log File을 대상 경로에 복사
  4. Control File 수정
  5. DB open
-- Control File 작업
SQL> select status from v$instance;

SQL> show parameter spfile;

SQL> select name from v$controlfile;

SQL> !mkdir /home/oracle/disk3 /home/oracle/disk4 /home/oracle/disk5

SQL> !cp <기존 Control File 경로> /home/oracle/disk3/control01.dbf

SQL> !cp <기존 Control File 경로> /home/oracle/disk4/control02.dbf

SQL> !cp <기존 Control File 경로> /home/oracle/disk5/control03.dbf

SQL> alter system set control_files='/home/oracle/disk3/control01.ctl',
  2  '/home/oracle/disk4/control02.ctl',
  3  '/home/oracle/disk5/control03.ctl'
  4  scope=spfile;

SQL> shutdown immediate;

SQL> startup;

SQL> select status from v$instance;

SQL> select name from v$controlfile;

SQL> !rm <기존 Control File 경로>

-- Redo Log File 작업
SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
  2  from v$logfile a, v$log b
  3  where a.group#=b.group#
  4  order by 1, 2;

SQL> select member from v$logfile;

SQL> show parameter spfile;

SQL> shutdown immediate;

SQL> startup mount;

SQL> !cp /home/oracle/disk1/redo01_a.log /home/oracle/disk3/redo01_a.log
SQL> !cp /home/oracle/disk1/redo02_a.log /home/oracle/disk3/redo02_a.log
SQL> !cp /home/oracle/disk1/redo03_a.log /home/oracle/disk3/redo03_a.log

SQL> !cp /home/oracle/disk2/redo01_b.log /home/oracle/disk4/redo01_b.log
SQL> !cp /home/oracle/disk2/redo02_b.log /home/oracle/disk4/redo02_b.log
SQL> !cp /home/oracle/disk2/redo03_b.log /home/oracle/disk4/redo03_b.log

SQL> alter database rename
  2  file '/home/oracle/disk1/redo01_a.log'
  3  to '/home/oracle/disk3/redo01_a.log';

SQL> alter database rename
  2  file '/home/oracle/disk1/redo02_a.log'
  3  to '/home/oracle/disk3/redo02_a.log';

SQL> alter database rename
  2  file '/home/oracle/disk1/redo03_a.log'
  3  to '/home/oracle/disk3/redo03_a.log';

SQL> alter database rename
  2  file '/home/oracle/disk2/redo01_b.log'
  3  to '/home/oracle/disk4/redo01_b.log';

SQL> alter database rename
  2  file '/home/oracle/disk2/redo02_b.log'
  3  to '/home/oracle/disk4/redo02_b.log';

SQL> alter database rename
  2  file '/home/oracle/disk2/redo03_b.log'
  3  to '/home/oracle/disk4/redo03_b.log';

SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
  2  from v$logfile a, v$log b
  3  where a.group#=b.group#
  4  order by 1, 2;
  
SQL> alter database open;

SQL> select status from v$instance;

SQL> alter database add logfile member
  2  '/home/oracle/disk5/redo01_c.log' to group 1;
  
SQL> alter database add logfile member
  2  '/home/oracle/disk5/redo02_c.log' to group 2;
  
SQL> alter database add logfile member
  2  '/home/oracle/disk5/redo03_c.log' to group 3;

SQL> select a.group#, a.member, b.bytes/1024/1024 MB, b.archived, b.status
  2  from v$logfile a, v$log b
  3  where a.group#=b.group#
  4  order by 1, 2;

SQL> !rm <기존 Redo Log File들>
profile
HYEOB KIM
Devops Engineer
이전 포스트

[Oracle] Control File 관리하기(실습)

다음 포스트

[Oracle] Data File 관리하기(실습)

0개의 댓글