[관리] 파일 (data/redolog/control file)

2022. 3. 23. 07:49

Control file 다중화

요약

parameter file/control file 체크

show parameter spfile;
show parameter pfile;
show parameter control_file;

Controlfile 설정 변경 (spfile)

 alter system set control_files = '/oradata/EHR/control01.ctl','/oradata/EHR/control02.ctl','/oradata/EHR/control03.ctl'   scope=spfile;

shutdown immediate; 

startup;

예시

더보기
SQL> show parameter spfile;
NAME TYPE VALUE

  ------------------------------------ ----------- ------------------------------

  spfile string /app/oracle/product/10.2.0/db_1/dbs/spfileEHR.ora

SQL> select name from v$controlfile;

  NAME

  --------------------------------------------------------------------------------

  /oraindex/EHR/control01.ctl

SQL> alter system set control_files = '/oradata/EHR/control01.ctl','/oradata/EHR/control02.ctl',

'/oradata/EHR/control03.ctl' scope=spfile;

  System altered.

SQL> shutdown immediate;

  Database closed.

  Database dismounted.

  ORACLE instance shut down.

SQL> !

[localhost.localdomain:/app/oracle]cp /oraindex/EHR/control01.ctl /oradata/EHR/control01.ctl

[localhost.localdomain:/app/oracle]cp /oraindex/EHR/control01.ctl /oradata/EHR/control02.ctl

[localhost.localdomain:/app/oracle]cp /oraindex/EHR/control01.ctl /oradata/EHR/control03.ctl

[localhost.localdomain:/app/oracle]exit

  exit

SQL> startup;

  ORACLE instance started.

  Total System Global Area 1610612736 bytes

  Fixed Size 2096632 bytes

  Variable Size 385876488 bytes

  Database Buffers 1207959552 bytes

  Redo Buffers 14680064 bytes

  Database mounted.

  Database opened.

Redo log 관리

요약

멤버/그룹 정보파악

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;

그룹추가

alter database add logfile group 4 '/oradata/EHR/redo04_a.log' size 5M;

멤버추가

alter database add logfile member '/oraindex/EHR/redo04_b.log' to group 4;

멤버삭제 (controlfile 내 삭제, 실제파일 직접제거)

alter database drop logfile member '/oraindex/EHR/redo04_b.log';

그룹삭제(controlfile 내 삭제, 실제파일 직접제거)

alter database drop logfile group 4;

logswitch

alter system switch logfile;

checkpoint

alter system checkpoint;

예시 - redo log 추가 /삭제

더보기
SQL> set line 200;
SQL> col a.group# for 999;

SQL> col member for a50;

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;

GROUP# MEMBER MB ARC STATUS

---------- -------------------------------------------------- ---------- --- ----------------

1 /oradata/EHR/redo10.log 50 NO CURRENT

   1 /oraindex/EHR/redo11.rdo 50 NO CURRENT

2 /oradata/EHR/redo20.log 50 NO INACTIVE

2 /oraindex/EHR/redo21.rdo 50 NO INACTIVE

3 /oradata/EHR/redo30.log 50 NO INACTIVE

3 /oraindex/EHR/redo31.rdo 50 NO INACTIVE

6 rows selected.

SQL> alter database add logfile group 4 '/oradata/EHR/redo04_a.log' size 5M;

Database altered.



SQL> alter database add logfile member '/oraindex/EHR/redo04_b.log' to group 4;

Database altered.

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;

GROUP# MEMBER MB ARC STATUS

---------- -------------------------------------------------- ---------- --- ----------------

  1 /oradata/EHR/redo10.log 50 NO CURRENT

   1 /oraindex/EHR/redo11.rdo 50 NO CURRENT

   2 /oradata/EHR/redo20.log 50 NO INACTIVE

   2 /oraindex/EHR/redo21.rdo 50 NO INACTIVE

   3 /oradata/EHR/redo30.log 50 NO INACTIVE

   3 /oraindex/EHR/redo31.rdo 50 NO INACTIVE

   4 /oradata/EHR/redo04_a.log 5 YES UNUSED

4 /oraindex/EHR/redo04_b.log 5 YES UNUSED

8 rows selected.



SQL> alter database drop logfile member '/oraindex/EHR/redo04_b.log';

Database altered.

SQL> alter database drop logfile group 4;

Database altered.

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;

GROUP# MEMBER MB ARC STATUS

---------- -------------------------------------------------- ---------- --- ----------------

  1 /oradata/EHR/redo10.log 50 NO CURRENT

   1 /oraindex/EHR/redo11.rdo 50 NO CURRENT

  2 /oradata/EHR/redo20.log 50 NO INACTIVE

2 /oraindex/EHR/redo21.rdo 50 NO INACTIVE

  3 /oradata/EHR/redo30.log 50 NO INACTIVE

3 /oraindex/EHR/redo31.rdo 50 NO INACTIVE

6 rows selected.



SQL> !

[localhost.localdomain:/app/oracle]rm /oradata/EHR/redo04_a.log -f

[localhost.localdomain:/app/oracle]rm /oraindex/EHR/redo04_b.log -f

[localhost.localdomain:/app/oracle]exit

data file 이동

설명

offline 가능/불가능에 따라 방법 구분
offline 불가 대상
- system /undo /redo log
Offline 가능 대상 순서
- 테이블스페이스 offline->파일복사 ->컨트롤파일 변경->테이블스페이스 online
Offline 불가능 대상 순서
- shutdown immediate->startup mount->파일복사->컨트롤파일 변경->DB Open

예시 -offline 가능 대상

더보기
SQL> select name from v$datafile;
NAME

---------------------------------------------------------------------------------------------------

/oradata/EHR/system01.dbf

/oradata/EHR/undotbs01.dbf

/oradata/EHR/sysaux01.dbf

/oradata/EHR/users01.dbf

/oradata/EHR/undo01.dbf

/oradata/EHR/haksa01.dbf

6 rows selected.

SQL> alter tablespace haksa offline;

Tablespace altered.

SQL> !cp /oradata/EHR/haksa01.dbf /oraindex/EHR/

SQL> alter tablespace haksa rename

datafile '/oradata/EHR/haksa01.dbf'

to '/oraindex/EHR/haksa01.dbf';

Tablespace altered.

SQL> select name from v$datafile;

NAME

---------------------------------------------------------------------------------------------------

/oradata/EHR/system01.dbf

/oradata/EHR/undotbs01.dbf

/oradata/EHR/sysaux01.dbf

/oradata/EHR/users01.dbf

/oradata/EHR/undo01.dbf

/oraindex/EHR/haksa01.dbf

예시 -offline 불가능 대상

더보기
SQL> shutdown immediate;
Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 1610612736 bytes

Fixed Size 2096632 bytes

Variable Size 385876488 bytes

Database Buffers 1207959552 bytes

Redo Buffers 14680064 bytes

Database mounted.

SQL> select name from v$datafile;

NAME

---------------------------------------------------------------------------------------------------

/oradata/EHR/system01.dbf

/oradata/EHR/undotbs01.dbf

/oradata/EHR/sysaux01.dbf

/oradata/EHR/users01.dbf

/oradata/EHR/undo01.dbf

/oraindex/EHR/haksa01.dbf

6 rows selected.

SQL> !cp /oradata/EHR/system01.dbf /oraindex/EHR/

SQL> alter database rename

file '/oradata/EHR/system01.dbf'

to '/oraindex/EHR/system01.dbf';

Database altered.

SQL> alter database open;

Database altered.

SQL> select name from v$datafile;

NAME

---------------------------------------------------------------------------------------------------

/oraindex/EHR/system01.dbf

/oradata/EHR/undotbs01.dbf

/oradata/EHR/sysaux01.dbf

/oradata/EHR/users01.dbf

/oradata/EHR/undo01.dbf

/oraindex/EHR/haksa01.dbf

저작자표시 비영리 변경금지

'Oracle > 기타' 카테고리의 다른 글

[관리] 계정/ArchiveLog (0)	2022.03.23
[관리] 테이블스페이스 (0)	2022.03.23
[기타] DB Link 구성 (0)	2022.03.23
[기타]다중 인스턴스 구성 (clone 생성 및 복원) (0)	2022.03.23

일상및기술기록