[8i] 컨트롤로 파일 관리(Dictionary View)

/********************************************************************************************
-- Title : [8i] 컨트롤 파일 관리(Dictionary View)
-- Reference : OLN
-- Key word : Dictionary View 콘트롤
********************************************************************************************/

ㅁ요약참조

관련내용	참조
초기화 파라미터	CONTROL_FILES
동적 성능 뷰	V$PARAMETER  V$CONTROL FILE  V$CONTROLFILE_RECORD_SECTION
데이터 딕셔너리 뷰	없슴
명령어	없슴
패키지 프로시저와 함수	없슴

/****************************************************************************************
-- 콘트롤 파일 정보 얻기
****************************************************************************************/
SQL> SELECT name
  2  FROM v$controlfile;

NAME
--------------------------------------------------------------------------------
/export/home/oracle8i/iORCL/init/control01.ctl
/export/home/oracle8i/iORCL/init/control02.ctl
/export/home/oracle8i/iORCL/init/control03.ctl

SQL> show parameter control

NAME                                 TYPE    VALUE
------------------------------------ ------- ------------------------------
control_file_record_keep_time        integer 7
control_files                        string  /export/home/oracle8i/disk5/or
                                             cl_01.ctl, /export/home/oracle
                                             8i/disk6/orcl_02.ctl

SQL> select value from v$parameter
  2  where name = 'control_files';

VALUE
---------------------------------------------------------------------------------
/export/home/oracle8i/disk5/orcl_01.ctl, /export/home/oracle8i/disk6/orcl_02.ctl

/****************************************************************************************
-- 콘트롤 파일의 다른 섹션에 대한 정보 보기
****************************************************************************************/
SQL> SELECT type, record_size, records_total, records_used
  2  FROM v$controlfile_record_section
  3  WHERE type = 'DATAFILE';

TYPE              RECORD_SIZE RECORDS_TOTAL RECORDS_USED
----------------- ----------- ------------- ------------
DATAFILE                  180           254            6

/****************************************************************************************
-- 다음과 같은 여러 다른 동적 성능 뷰에 있는 정보는 제어 파일에서 얻을 수 있다.
****************************************************************************************/
   - V$BACKUP
   - V$DATAFILE
   - V$TEMPFILE
   - V$TABLESPACE
   - V$ARCHIVE
   - V$LOG
   - V$LOGFILE
   - V$LOGHIST
   - V$ARCHIVED_LOG
   - V$DATABASE

/****************************************************************************************
-- control file 추가하기
-- [10g]에서 spfile에 추가/삭제하기(http://dbrang.tistory.com/512)
****************************************************************************************/
SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.

$ cp control01.ctl control04.ctl   /* controlfile 추가 */

$ vi initORCL.ora                  /* 파라미터 파일의 control_files에 추가 */
...
control_files = ("control01.ctl", "control02.ctl", "control03.ctl", "control04.ctl")
...

SQL> STARTUP
ORACLE instance started.

...
Database mounted.
Database opened.

SQL> SELECT *
  2  FROM v$controlfile;

STATUS  NAME
------- ----------------------------------------------------------
        /export/home/oracle8i/iORCL/init/control01.ctl
        /export/home/oracle8i/iORCL/init/control02.ctl
        /export/home/oracle8i/iORCL/init/control03.ctl
        /export/home/oracle8i/iORCL/init/control04.ctl    /* 추가 됨 */

SQL> SHOW PARAMETER control_files

NAME                                 TYPE    VALUE
------------------------------------ ------- ------------------------------
control_files                        string  /export/home/oracle8i/iORCL/in
                                             it/control01.ctl, /export/home
                                             /oracle8i/iORCL/init/control02
                                             .ctl, /export/home/oracle8i/iO
                                             RCL/init/control03.ctl, /expor
                                             t/home/oracle8i/iORCL/init/con
                                             trol04.ctl

/****************************************************************************************
-- control file 삭제하기
****************************************************************************************/
SQL> SHUTDOWN
Database closed.
Database dismounted.
ORACLE instance shut down.

$ rm control04.ctl        /* control file 삭제 */

$ vi initORCL.ora                  /* control04.ctl 제거 */
...
control_files = ("control01.ctl", "control02.ctl", "control03.ctl")
...

SQL> STARTUP
ORACLE instance started.

...
Database mounted.
Database opened.

SQL> SELECT *
  2  FROM v$controlfile;

STATUS  NAME
------- ----------------------------------------------------------
        /export/home/oracle8i/iORCL/init/control01.ctl
        /export/home/oracle8i/iORCL/init/control02.ctl
        /export/home/oracle8i/iORCL/init/control03.ctl

SQL> SHOW PARAMETER control_files

NAME                                 TYPE    VALUE
------------------------------------ ------- ------------------------------
control_files                        string  /export/home/oracle8i/iORCL/in
                                             it/control01.ctl, /export/home
                                             /oracle8i/iORCL/init/control02
                                             .ctl, /export/home/oracle8i/iO
                                             RCL/init/control03.ctl