[ORACLE] control file 관리

Control file

: 데이터베이스의 물리적 구조를 기록한 binary file, Database Open 시 Mount 단계에서 읽혀지는 File이다.

database instance마다 각각의 controlfile을 갖는다.

다중화하여 보관하는 것이 권장(필수)된다.

 

control file에 포함된 내용

select * from v$controlfile_record_section ;

DATABASE
CKPT PROGRESS
REDO THREAD
REDO LOG
DATAFILE
FILENAME
TABLESPACE
TEMPORARY FILENAME
RMAN CONFIGURATION
LOG HISTORY
OFFLINE RANGE
ARCHIVED LOG
BACKUP SET
BACKUP PIECE
BACKUP DATAFILE
BACKUP REDOLOG
DATAFILE COPY
BACKUP CORRUPTION
COPY CORRUPTION
DELETED OBJECT
PROXY COPY
BACKUP SPFILE
DATABASE INCARNATION
FLASHBACK LOG
RECOVERY DESTINATION
INSTANCE SPACE RESERVATION
REMOVABLE RECOVERY FILES
RMAN STATUS
THREAD INSTANCE NAME MAPPING
MTTR
DATAFILE HISTORY
STANDBY DATABASE MATRIX
GUARANTEED RESTORE POINT
RESTORE POINT
DATABASE BLOCK CORRUPTION
ACM OPERATION
FOREIGN ARCHIVED LOG
PDB RECORD
AUXILIARY DATAFILE COPY
MULTI INSTANCE REDO APPLY
PDBINC RECORD
TABLESPACE KEY HISTORY

- Database Name

- Datafile, Redo log file 위치와 파일명

- Database 생성 시간

- Current log sequence number

- Checkpoint 정보

등

 

현재 사용 중인 Control file 확인

SQL> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      +DATA/UG19/CONTROLFILE/current
                                                 .257.1141989067, +RECO/UG19/CO
                                                 NTROLFILE/current.256.11419890
                                                 69

select * from v$controlfile;

 

Parameter File의 Control File 정보 변경

alter system set control_files='경로1/파일명1','경로2/파일명2' scope=spfile sid='*' ;
후 DB 재기동

Control File을 Trace File로 생성

- binary file을 읽을 수 있는 문자로 된 File로 생성 (spfile <-> pfile 같은 넉김)

alter database backup controlfile to trace as '경로/filename';

현재 존재하는 Control File 복제본 생성 (binary file)

ALTER DATABASE BACKUP CONTROLFILE TO '경로/파일명';

백업된 (restore 가능한 상태) Control file 정보 확인

-- Control file Backupset Detail Information 확인
SELECT * FROM V$BACKUP_CONTROLFILE_DETAILS;

-- Backupset 요약
SELECT * FROM V$BACKUP_CONTROLFILE_SUMMARY;

 

Control file 생성

Control file을 새로 생성해야 하는 특정 상황

- Database의 모든 Control File이 영구적으로 손상되었고, 백업이 없을 때

- Database Name을 변경할 때 (애초에 이 상황은 만들지 않는게 낫다....)

 

1. Oracle Doc 참조 방법 >>

1. 모든 datafile, redo log file의 정보와 현재 control file 알아야 함
SELECT MEMBER FROM V$LOGFILE;
SELECT NAME,BYTES/1024/1024 FROM V$DATAFILE; 
SELECT VALUE FROM V$PARAMETER WHERE NAME = 'control_files';

2. Shutdown immediate

3. 모든 datafile, redo log file backup

4. startup mount

5. create controlfile
-> statement 예시)

CREATE CONTROLFILE
   SET DATABASE DB_NAME
   -- 1에서 조회한 모든 REDO LOG FILE 정보
   LOGFILE GROUP 1 ('/u01/oracle/DB_NAME/redo01_01.log', 
                    '/u01/oracle/DB_NAME/redo01_02.log'),
           GROUP 2 ('/u01/oracle/DB_NAME/redo02_01.log', 
                    '/u01/oracle/DB_NAME/redo02_02.log'),
           GROUP 3 ('/u01/oracle/DB_NAME/redo03_01.log', 
                    '/u01/oracle/DB_NAME/redo03_02.log') 
   RESETLOGS
   -- 1에서 조회한 모든 DATAFILE 정보
   DATAFILE '/u01/oracle/DB_NAME/system01.dbf' SIZE 3M,
            '/u01/oracle/DB_NAME/rbs01.dbs' SIZE 5M,
            '/u01/oracle/DB_NAME/users01.dbs' SIZE 5M,
            '/u01/oracle/DB_NAME/temp01.dbs' SIZE 5M
   -- 최대 LOGFILE, MEMBER, DATAFILE 속성 정의 
   MAXLOGFILES 50
   MAXLOGMEMBERS 3
   MAXLOGHISTORY 400
   MAXDATAFILES 200
   MAXINSTANCES 6
   ARCHIVELOG;
   
6. 새로 생성한 control file의 백업본 생성

7. control file parameter 변경 (alter system set ...)

8. 필요 시 database recovery -> alter database open [resetlogs];

 

2. Rman으로 재생성(및 다중화)하는 방법 >>

show parameter control_files
-----> +DATA/UG19/CONTROLFILE/current.257.1141989067

# RMAN 접속
rman target /

# NOMOUNT로 재기동
shut immediate
startup nomount

# Control File 재생성
restore controlfile to '+RECO' from '+DATA/UG19/CONTROLFILE/current.257.1141989067' ;

[UG191]oracle@ug191:/home/oracle# rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Tue Jul 18 09:03:57 2023
Version 19.18.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: UG19 (not mounted)

RMAN> restore controlfile to '+RECO' from '+DATA/UG19/CONTROLFILE/current.257.1141989067' ;

Starting restore at 2023/07/18 09:04:00
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=30 instance=UG191 device type=DISK

channel ORA_DISK_1: copied control file copy
Finished restore at 2023/07/18 09:04:03

 

# 생성된 Control file 확인
[+ASM1]grid@ug191:/home/grid# asmcmd -p
ASMCMD [+] > cd +RECO/UG19/CONTROLFILE

ASMCMD [+RECO/UG19/CONTROLFILE] > ls -l
CONTROLFILE  UNPROT  FINE     JUL 18 09:00:00  Y    current.551.1142499843

# spfile에 적용
alter system set control_files = '+DATA/UG19/CONTROLFILE/current.257.1141989067', '+RECO/UG19/CONTROLFILE/current.256.1141989069','+RECO/UG19/CONTROLFILE/current.551.1142499843' scope=spfile sid='*';

# open
alter database open;

 

3. trace file로 control file 재생성 방법 >>

SQL> alter database backup controlfile to trace as '/media/rsp/ug19_ctl.trc';

# trace file의 NORESETLOG CASE를 참고하여 control file을 재생성

vi /media/rsp/ug19_ctl.trc

--     Set #1. NORESETLOGS case
STARTUP NOMOUNT ;

alter system set cluster_database=false scope=spfile; 
SHUT IMMEDIATE
STARTUP NOMOUNT;

SQL> 
CREATE CONTROLFILE REUSE DATABASE "UG19" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 192
    MAXLOGMEMBERS 3
    MAXDATAFILES 1024
    MAXINSTANCES 32
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    '+DATA/UG19/ONLINELOG/group_1.258.1141989069',
    '+RECO/UG19/ONLINELOG/group_1.257.1141989071'
  ) SIZE 200M BLOCKSIZE 512,
  GROUP 2 (
    '+DATA/UG19/ONLINELOG/group_2.259.1141989071',
    '+RECO/UG19/ONLINELOG/group_2.258.1141989071'
  ) SIZE 200M BLOCKSIZE 512,
  GROUP 3 (
    '+DATA/UG19/ONLINELOG/group_3.266.1141991983',
    '+RECO/UG19/ONLINELOG/group_3.259.1141991985'
  ) SIZE 200M BLOCKSIZE 512,
  GROUP 4 (
    '+DATA/UG19/ONLINELOG/group_4.267.1141991985',
    '+RECO/UG19/ONLINELOG/group_4.260.1141991985'
  ) SIZE 200M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '+DATA/UG19/DATAFILE/system.260.1141989073',
  '+DATA/UG19/DATAFILE/sysaux.261.1141989079',
  '+DATA/UG19/DATAFILE/undotbs1.262.1141989081',
  '+DATA/UG19/DATAFILE/undotbs2.264.1141989099',
  '+DATA/UG19/DATAFILE/users.265.1141989099',
  '+DATA/UG19/DATAFILE/emr_d08_01.269.1142067339',
  '+DATA/UG19/DATAFILE/emr_i08_01.270.1142067345',
  '+DATA/UG19/DATAFILE/his_d08_01.271.1142067347',
  '+DATA/UG19/DATAFILE/his_i08_01.272.1142067347',
  '+DATA/UG19/DATAFILE/pam_d08_01.273.1142067349',
  '+DATA/UG19/DATAFILE/pam_i08_01.274.1142067351',
  '+DATA/UG19/DATAFILE/sec_d08_01.275.1142067355',
  '+DATA/UG19/DATAFILE/test1.276.1142096089',
  '+DATA/UG19/DATAFILE/encrypt_ts.277.1142097111'
CHARACTER SET AL32UTF8
;

Control file created.

SQL> alter system set cluster_database=true scope=spfile;

System altered.

SQL> shut immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

startup mount 
SQL> recover database
Media recovery complete.

SQL> alter database open;

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA/UG19/TEMPFILE/temp.263.1141989081'
     SIZE 1561M REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;  

Tablespace altered.