오라클 control file 경로 변경 및 이중화

OS환경 : Oracle Linux 5.8 (64bit)

DB 환경 : Oracle Database 10.2.0.5

방법 : oracle controlfile 경로 변경 및 이중화

오라클 컨트롤파일 경로 변경 및 다중화

기존 컨트롤파일 확인

SQL> show parameter control
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time         integer     7
control_files                 string     /oracle/app/oracle/oradata/ORCL10/control01.ctl

각각 /oracle/conbak1, /oracle/conbak2, /oracle/conbak3 에 저장 할 예정

alter 명령으로 control file 위치 변경(scope = spfile)

SQL> alter system set control_files='/oracle/conbak1/control01.ctl','/oracle/conbak2/control02.ctl','/oracle/conbak3/control03.ctl' scope=spfile;
 
System altered.

db 종료

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

컨트롤파일 백업용 경로 생성

$ mkdir -p /oracle/conbak1 /oracle/conbak2 /oracle/conbak3

기존 컨트롤파일을 새경로로 복사

$ cd /oracle/app/oracle/oradata/ORCL10/
$ cp control01.ctl /oracle/conbak1/control01.ctl
$ cp control01.ctl /oracle/conbak2/control02.ctl
$ cp control01.ctl /oracle/conbak3/control03.ctl

db 기동

SQL> startup
ORACLE instance started.
 
Total System Global Area 1073741824 bytes
Fixed Size            2101808 bytes
Variable Size          377490896 bytes
Database Buffers      687865856 bytes
Redo Buffers            6283264 bytes
Database mounted.
Database opened.

컨트롤파일 확인

SQL> show parameter control    
 
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time         integer     7
control_files                 string     /oracle/conbak1/control01.ctl,
                          /oracle/conbak2/control02.ctl
                         , /oracle/conbak3/control03.ctl

정상적으로 변경됨

추가

컨트롤파일을 하나만 복사하고 나머지를 복사하지 않았을 경우

기존 컨트롤파일을 새경로1에만 복사

$ cp control01.ctl /oracle/conbak1/control01.ctl
$ cp control01.ctl /oracle/conbak2/control02.ctl <- 복사하지 않음
$ cp control01.ctl /oracle/conbak3/control03.ctl <- 복사하지 않음

db 기동

SQL> startup
ORACLE instance started.
 
Total System Global Area 1073741824 bytes
Fixed Size            2101808 bytes
Variable Size          377490896 bytes
Database Buffers      687865856 bytes
Redo Buffers            6283264 bytes
ORA-00205: error in identifying control file, check alert log for more info

장애 발생

해당시점 alert log

Tue Feb 11 16:17:22 KST 2020
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 3
IMODE=BR
ILAT =61
LICENSE_MAX_USERS = 0
SYS auditing is disabled
ksdpec: called for event 13740 prior to event group initialization
Starting up ORACLE RDBMS Version: 10.2.0.5.0.
System parameters with non-default values:
  processes                = 500
  sga_max_size             = 1073741824
  __shared_pool_size       = 213909504
  shared_pool_size         = 209715200
  __large_pool_size        = 4194304
  __java_pool_size         = 54525952
  java_pool_size           = 54525952
  __streams_pool_size      = 104857600
  streams_pool_size        = 104857600
  shared_pool_reserved_size= 104857600
  sga_target               = 1073741824
  control_files            = /oracle/conbak1/control01.ctl, /oracle/conbak2/control02.ctl, /oracle/conbak3/control03.ctl
  db_block_size            = 8192
  __db_cache_size          = 687865856
  db_cache_size            = 104857600
  compatible               = 10.2.0.5.0
  log_archive_dest         = 
  log_archive_dest_1       = location=/oracle/app/oracle/arch2
  log_archive_format       = orcl10_%t%s%r.arc
  db_file_multiblock_read_count= 16
  undo_management          = AUTO
  undo_tablespace          = UNDOTBS1
  remote_login_passwordfile= EXCLUSIVE
  job_queue_processes      = 10
  background_dump_dest     = /oracle/app/oracle/admin/ORCL10/bdump
  user_dump_dest           = /oracle/app/oracle/admin/ORCL10/udump
  core_dump_dest           = /oracle/app/oracle/admin/ORCL10/cdump
  audit_file_dest          = /oracle/app/oracle/admin/ORCL10/adump
  db_name                  = ORCL10
  open_cursors             = 300
  pga_aggregate_target     = 52428800
PMON started with pid=2, OS id=1283
PSP0 started with pid=3, OS id=1285
MMAN started with pid=4, OS id=1287
DBW0 started with pid=5, OS id=1289
LGWR started with pid=6, OS id=1291
CKPT started with pid=7, OS id=1293
SMON started with pid=8, OS id=1295
RECO started with pid=9, OS id=1297
CJQ0 started with pid=10, OS id=1299
MMON started with pid=11, OS id=1301
MMNL started with pid=12, OS id=1303
Tue Feb 11 16:17:22 KST 2020
ALTER DATABASE   MOUNT
Tue Feb 11 16:17:22 KST 2020
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/oracle/conbak2/control02.ctl'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Tue Feb 11 16:17:22 KST 2020
ORA-205 signalled during: ALTER DATABASE   MOUNT...

/oracle/conbak2/경로에 컨트롤파일을 찾을수 없다고 나오고

mount까지 올라가지 않음

조치방법

nomount 로 기동된 db 종료

SQL> shutdown immediate
ORA-01507: database not mounted
 
ORACLE instance shut down.

컨트롤파일 제대로 복사

$ cp control01.ctl /oracle/conbak2/control02.ctl
$ cp control01.ctl /oracle/conbak3/control03.ctl

db 기동

SQL> startup
ORACLE instance started.
 
Total System Global Area 1073741824 bytes
Fixed Size            2101808 bytes
Variable Size          377490896 bytes
Database Buffers      687865856 bytes
Redo Buffers            6283264 bytes
Database mounted.
Database opened.

정상적으로 open 됨

참조 :