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

HYEOB KIM·2022년 12월 20일
0

Oracle

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

기본 실습

과제

가정: 현재 초기화 파라미터 파일은 pfile이다.

현재 운영 중인 Control File의 경로를 확인한 후
/home/oracle/disk1/control01.ctl,
/home/oracle/disk2/control02.ctl,
/home/oracle/disk3/control03.ctl
로 다중화합니다.

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

풀이

SQL> select status from v$instance;

SQL> show parameter spfile;

SQL> create spfile from pfile;

$ cd $ORACLE_HOME/dbs
$ ls -al
$ rm init<SID>.ora

SQL> shutdown immediate;
SQL> startup;

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 경로>

실습 1

과제

가정 : 현재 초기화 파라미터 파일은 spfile입니다.

초기화 파라미터 파일이 pfile일 때
/home/oracle/disk1/control01.ctl,
/home/oracle/disk2/control02.ctl,
/home/oracle/disk3/control03.ctl
로 다중화합니다.

풀이

SQL> select status from v$instance;

SQL> show parameter spfile;

SQL> create pfile from spfile;

SQL> shutdown immediate;

$ cd $ORACLE_HOME/dbs
$ ls -al
$ rm init<SID>.ora

SQL> startup;

SQL> select status from v$instance;

SQL> show parameter spfile;

SQL> select name from v$controlfile;

SQL> shutdown immediate;

$ mkdir /home/oracle/disk1
$ mkdir /home/oracle/disk2
$ mkdir /home/oracle/disk3

$ cp /ORA19/app/oracle/oradata/ORACLE19/control01.ctl /home/oracle/disk1/control01.ctl
$ cp /ORA19/app/oracle/oradata/ORACLE19/control01.ctl /home/oracle/disk2/control02.ctl
$ cp /ORA19/app/oracle/oradata/ORACLE19/control01.ctl /home/oracle/disk3/control03.ctl

$ vi $ORACLE_HOME/dbs/init<SID>.ora

control_files='/home/oracle/disk1/control01.ctl','/home/oracle/disk2/control02.ctl','/home/oracle/disk3/control03.ctl'

:wq!

SQL> startup;

SQL> select status from v$instance;

SQL> select name from v$controlfile;

실습 2

과제

pfile을 사용하여 control file의 위치를 아래와 같이
/home/oracle/disk1/control01.ctl,
/home/oracle/disk2/control02.ctl,
/home/oracle/disk3/control03.ctl
로 구성하고,

spfile을 이용하여
/home/oracle/disk4/control01.ctl,
/home/oracle/disk5/control02.ctl,
/home/oracle/disk6/control03.ctl
이 되도록 구성하세요.

풀이

-- pfile 환경에서 control file 이동
SQL> select status from v$instance;

SQL> show parameter spfile;

SQL> create pfile from spfile;

$ rm $ORACLE_HOME/dbs/spfile<SID>.ora
$ ls -al $ORACLE_HOME/dbs

SQL> shutdown immediate;
SQL> startup;

SQL> select status from v$instance;

SQL> select name from v$controlfile;

SQL> shutdown immediate;

$ mkdir /home/oracle/disk1 /home/oracle/disk2 /home/oracle/disk3

$ cp /ORA19/app/oracle/oradata/ORACLE19/control01.ctl /home/oracle/disk1/control01.ctl
$ cp /ORA19/app/oracle/oradata/ORACLE19/control01.ctl /home/oracle/disk2/control02.ctl
$ cp /ORA19/app/oracle/oradata/ORACLE19/control01.ctl /home/oracle/disk3/control03.ctl

$ vi $ORACLE_HOME/dbs init<SID>.ora
...
control_files='/home/oracle/disk1/control01.ctl','/home/oracle/disk2/control02.ctl','/home/oracle/disk3/control03.ctl'

:wq!

SQL> startup;

SQL> select status from v$instance;

SQL> select name from v$controlfile;

$ rm /ORA19/app/oracle/oradata/ORACLE19/control01.ctl

-- spfile 환경에서 control file 이동
SQL> select status from v$instance;

SQL> show parameter spfile;

SQL> create spfile from pfile;

$ rm $ORACLE_HOME/dbs/init<SID>.ora
$ ls -al $ORACLE_HOME/dbs

SQL> shutdown immediate;
SQL> startup;

SQL> select status from v$instance;

SQL> show parameter spfile;

SQL> select name from v$instance;

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

SQL> shutdown immediate;

$ mkdir /home/oracle/disk4 /home/oracle/disk5 /home/oracle/disk6

$ cp /home/oracle/disk1/control01.ctl /home/oracle/disk4/control01.ctl
$ cp /home/oracle/disk2/control02.ctl /home/oracle/disk5/control02.ctl
$ cp /home/oracle/disk3/control03.ctl /home/oracle/disk6/control03.ctl

SQL> startup;

SQL> select status from v$instance;

SQL> select name from v$instance;

$ rm /home/oracle/disk1/control01.ctl /home/oracle/disk2/control02.ctl /home/oracle/disk3/control03.ctl
profile
Devops Engineer
post-custom-banner

0개의 댓글