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

HYEOB KIM·2022년 12월 20일
oracle
0

Oracle

목록 보기
24/58
post-custom-banner

기본 실습

-- 일반 테이블스페이스 생성
SQL> create tablespace khyup
  2  datafile '/ORA19/app/oracle/oradata/ORACLE19/khyup01.dbf' size 10M
  3  segment space management auto;

-- 일반 테이블스페이스 조회
SQL> select tablespace_name, status, contents, extent_management, segment_space_management
  2  from dba_tablespaces;

SQL> select tablespace_name, bytes/1024/1024 MB, file_name
  2  from dba_data_files;

-- 각 데이터 파일의 실제 사용량 확인
SQL> set line 200;
SQL> col file# for 999;
SQL> col ts_name for a10;
SQL> col total_blocks for 9999999;
SQL> col used_blocks for 9999999;
SQL> col pct_used for a10
SQL> select distinct d.file_id file#,
  2  d.tablespace_name ts_name,
  3  d.bytes/1024/1024 MB,
  4  d.bytes/8192 total_blocks,
  5  sum(e.blocks) used_blocks,
  6  to_char(nvl(round(sum(e.blocks)/(d.bytes/8192),4),0) * 100,'09.00') || ' %' pct_used
  7  from dba_extents e, dba_data_files d
  8  where d.file_id = e.file_id
  9  group by d.file_id, d.tablespace_name, d.bytes
 10  order by 1, 2;

-- 테이블스페이스 용량 관리하기
-- 수동으로 테이블스페이스에 Data File 추가하기
SQL> alter tablespace khyup add datafile
  2  '/ORA19/app/oracle/oradata/ORACLE19/khyup02.dbf' size 20M;

SQL> select tablespace_name, bytes/1024/1024 MB, file_name
  2  from dba_data_files;

-- 수동으로 Data File 크기 늘려주기
SQL> alter database datafile
  2  '/ORA19/app/oracle/oradata/ORACLE19/khyup01.dbf' resize 20M;

SQL> select tablespace_name, bytes/1024/1024 MB, file_name
  2  from dba_data_files;

-- 자동으로 Data File 크기 증가시키도록 설정
SQL> alter database datafile
  2  '/ORA19/app/oracle/oradata/ORACLE19/khyup01.dbf' autoextend on;
  
SQL> set line 200
SQL> col tablespace_name for a10
SQL> col file_name for a50
SQL> select tablespace_name, bytes/1024/1024 MB, file_name, autoextensible "Auto", online_status
  2  from dba_data_files;

-- (번외) autoextend 테스트
SQL> create table khyup
  2  (no number);
  
SQL> begin
  2  for i in 1..500000 loop
  3    insert into khyup values(i);
  4  end loop;
  5  commit;
  6  end;
  7  /

SQL> select tablespace_name, bytes/1024/1024 MB, file_name, checkpoint_change#
  2  from dba_data_files;

-- Tablespace Offline
SQL> alter tablespace khyup offline;

SQL> alter tablespace khyup offline temporary;

-- DB가 archive log mode 일 경우 Data File offline 방법
SQL> alter database datafile '/ORA19/app/oracle/oradata/ORACLE19/khyup01.dbf' offline;

-- DB가 no archive log mode 일 경우 Data File offline 방법
SQL> alter database datafile
'/ORA19/app/oracle/oradata/ORACLE19/khyup01.dbf' offline drop;

SQL> select file#, name, status from v$datafile;

SQL> alter tablespace khyup online;

SQL> alter tablespace khyup offline temporary;

SQL> recover tablespace khyup;

-- 테이블 스페이스 삭제하기
SQL> drop tablespace khyup including contents and datafiles;

과제

offline 되는 Tablespace의 Data File 이동하기

  1. Tablespace Offline
  2. Data File을 대상 위치로 복사
  3. Control File 내 해당 Data File 위치 변경
  4. Tablespace Online

parameter file에는 control file에 대한 경로가 들어있고,
control file에는 data file, redo log file에 대한 경로가 들어 있습니다.

풀이

SQL> alter tablespace khyup offline;

$ cp /ORA19/app/oracle/oradata/ORACLE19/khyup01.dbf /home/oracle/disk1/khyup01.dbf
$ cp /ORA19/app/oracle/oradata/ORACLE19/khyup02.dbf /home/oracle/disk2/khyup02.dbf

SQL> select name, status from v$datafile;

SQL> alter tablespace khyup rename
  2  datafile '/ORA19/app/oracle/oradata/ORACLE19/khyup01.dbf'
  3  to '/home/oracle/disk1/khyup01.dbf';

SQL> alter tablespace khyup rename
  2  datafile '/ORA19/app/oracle/oradata/ORACLE19/khyup02.dbf'
  3  to '/home/oracle/disk2/khyup02.dbf';

SQL> alter tablespace khyup online;

SQL> select name, status from v$datafile;

과제

offline 되지 않는 Tablespace(system tablespace, undo tablespace, default temporary tablespace)의 Data File 이동하기

  1. DB 종료
  2. startup mount
  3. Data File을 대상 위치로 복사
  4. Control File의 내용 변경
  5. DB open

풀이

SQL> shutdown immediate;

SQL> startup mount;

$ cp /ORA19/app/oracle/oradata/ORACLE19/system01.dbf /home/oracle/disk3/system01.dbf

SQL> alter database rename
  2  file '/ORA19/app/oracle/oradata/ORACLE19/system01.dbf'
  3  to '/home/oracle/disk3/system01.dbf';

SQL> select name, status from v$datafile;

SQL> alter database open;

과제

Redo Log File 이동하기

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

풀이

SQL> select status from v$instance;

SQL> shutdown immediate;

SQL> startup mount;

SQL> select member from v$logfile;

SQL> !cp <기존 경로> <새로운 경로>

SQL> alter database rename
  2  file '<기존 경로>'
  3  to '<새로운 경로>';

SQL> select member from v$logfile;

SQL> alter database open;

실습

과제

example Tablespace를 생성합니다.
/ORA19/app/oracle/oradata/ORACLE19/example01.dbf: 10M

khyup Tablespace를 생성합니다.
/ORA19/app/oracle/oradata/ORACLE19/khyup01.dbf: 10M
/ORA19/app/oracle/oradata/ORACLE19/khyup02.dbf: 20M

풀이

SQL>

과제

Control File, Redo Log File, Data File을 아래와 같이 이동시키세요.

/home/oracle/disk1/control01.ctl, redo01_a.log, redo02_a.log, redo03_a.log
/home/oracle/disk2/control02.ctl, redo01_b.log, redo02_b.log, redo03_b.log
/home/oracle/disk3/control03.ctl, undotbs01.dbf
/home/oracle/disk4/system01.dbf, sysaux01.dbf, khyup01.dbf
/home/oracle/disk5/khyup02.dbf, example01.dbf

풀이

-- Control File 작업
SQL> select status from v$instance;

SQL> show parameter spfile;

SQL> select name from v$controlfile;

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

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

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

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

SQL> alter system set control_files='/home/oracle/disk1/control01.ctl',
  2  '/home/oracle/disk2/control02.ctl',
  3  '/home/oracle/disk3/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 status from v$instance;

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> shutdown immediate;

SQL> startup mount;

SQL> select status from v$instance;

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

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

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

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

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

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

SQL> select member from v$logfile;

SQL> alter database open;

SQL> select status from v$instance;

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 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 /ORA19/app/oracle/oradata/ORACLE19/redo01.log /ORA19/app/oracle/oradata/ORACLE19/redo02.log /ORA19/app/oracle/oradata/ORACLE19/redo03.log

-- Data File 작업(system, undo tablespace)
SQL> select status from v$instance;

SQL> select tablespace_name, bytes/1024/1024 MB, file_name
  2  from dba_data_files;

SQL> shutdown immediate;

SQL> startup mount;

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

SQL> !cp <기존 system01.dbf 파일 경로> /home/oracle/disk4/system01.dbf

SQL> !cp <기존 undotbs01.dbf 파일 경로> /home/oracle/disk3/undotbs01.dbf

SQL> alter database rename
  2  file '<기존 system01.dbf 파일 경로>'
  3  to '/home/oracle/disk4/system01.dbf';
  
SQL> alter database rename
  2  file '<기존 undotbs01.dbf 파일 경로>'
  3  to '/home/oracle/disk3/undotbs01.dbf';

SQL> select tablespace_name, bytes/1024/1024 MB, file_name
  2  from dba_data_files;
  
SQL> alter database open;

-- Data File 작업(일반 Tablespace: sysaux01.dbf, khyup01.dbf, khyup02.dbf, example01.dbf)
SQL> select tablespace_name, bytes/1024/1024 MB, file_name
  2  dba_data_files;

SQL> alter tablespace sysaux offline;
SQL> alter tablespace khyup offline;
SQL> alter tablespace example offline;

SQL> !cp <기존 sysaux Tablespace 경로> /home/oracle/disk4/sysaux01.dbf
SQL> !cp <기존 khyup Tablespace 경로> /home/oracle/disk4/khyup01.dbf
SQL> !cp <기존 khuyp Tablespace 경로> /home/oracle/disk5/khyup02.dbf
SQL> !cp <기존 example Tablespace 경로> /home/oracle/disk5/example01.dbf

SQL> alter tablespace sysaux rename
  2  datafile '<기존 sysaux Tablespace 경로>'
  3  to '/home/oracle/disk4/sysaux01.dbf';

SQL> alter tablespace khyup rename
  2  datafile '<기존 khyup Tablespace 경로>'
  3  to '/home/oracle/disk4/khyup01.dbf';

SQL> alter tablespace khyup rename
  2  datafile '<기존 khyup Tablespace 경로>'
  3  to '/home/oracle/disk5/khyup02.dbf';
  
SQL> alter tablespace example rename
  2  datafile '<기존 example Tablespace 경로>'
  3  to '/home/oracle/disk5/example01.dbf';
  
SQL> select tablespace_name, bytes/1024/1024 MB, file_name
  2  from dba_data_files;

SQL> alter tablespace sysaux online;
SQL> alter tablespace khyup online;
SQL> alter tablespace example online;
profile
HYEOB KIM
Devops Engineer
이전 포스트

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

다음 포스트

[Oracle] 사용자 관리(실습)

post-custom-banner

0개의 댓글