메뉴 건너뛰기

Korea Oracle User Group

Backup & Recovery

 Test Version : 12.1.0.2.0

 Fils system : ASM

 Engine : Oracle Enterprise / Grid 

 OS : CentOS 6.7

 

 

 0. ASM 으로 구성 되있는 디비에 컨트롤 파일이 손상이 발생 ,  미러링 파일을 이용해 복구를 하는 과정 입니다.

 

 1. 컨트롤 파일 손상을 발견

 

alter database open
Mon Jul 15 13:57:03 2019
WARNING: Read Failed. group:1 disk:0 AU:63 offset:131072 size:131072
path:/dev/oracleasm/disks/DATA1
         incarnation:0x0 synchronous result:'I/O error'
         subsys:System krq:0x7f6b11fb3578 bufp:0x7f6b119e7e00 osderr1:0x69b5 osderr2:0x0
         IO elapsed time: 0 usec Time waited on I/O: 0 usec
WARNING: failed to read mirror side 1 of virtual extent 1 logical extent 0 of file 257 in group [1.649683322] from disk DATA_0000  allocation unit 63 reason error; if possible, will try another mirror side
Mon Jul 15 13:57:03 2019
Errors in file /home/oracle/app/oracle/diag/rdbms/testdb/testdb/trace/testdb_ora_6298.trc:
ORA-00202: control file: '+DATA/TESTDB/CONTROLFILE/current.257.996584973'
ORA-15081: failed to submit an I/O operation to a disk
Mon Jul 15 13:57:03 2019
Errors in file /home/oracle/app/oracle/diag/rdbms/testdb/testdb/trace/testdb_ora_6298.trc:
ORA-00204: error in reading (block 23, # blocks 64) of control file
ORA-00202: control file: '+DATA/TESTDB/CONTROLFILE/current.257.996584973'
ORA-15081: failed to submit an I/O operation to a disk
ORA-204 signalled during: alter database open...
Mon Jul 15 14:01:29 2019

 

 

 2. 미러링 컨트롤 파일을 이용해 오픈 시도 정상 유무 확인

 

sys@testdb> 
sys@testdb> alter system set control_files = '+FRG/TESTDB/CONTROLFILE/current.256.902932949' scope=spfile;
            << spfile 에 미러링 파일만 인식 하도록 값을 변경
System altered.

sys@testdb> 
sys@testdb> 
sys@testdb> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
sys@testdb> startup;

## 로그 파일 내용 ##
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options.
ORACLE_HOME = /home/oracle/app/oracle/product/12.1.0
System name:    Linux
Node name:      testora.localdomain
Release:        2.6.32-573.el6.x86_64
Version:        #1 SMP Thu Jul 23 15:44:03 UTC 2015
Machine:        x86_64
Using parameter settings in server-side pfile /home/oracle/app/oracle/product/12.1.0/dbs/inittestdb.ora
System parameters with non-default values:
  processes                = 300
  spfile                   = "+DATA/testdb/PARAMETERFILE/spfile.269.902946971"
  nls_language             = "AMERICAN"
  nls_territory            = "AMERICA"
  sga_target               = 872M
  control_files            = "+FRG/TESTDB/CONTROLFILE/current.256.902932949"
  db_block_size            = 8192
  compatible               = "12.1.0.2.0"
  db_create_file_dest      = "+DATA"
  db_recovery_file_dest    = "+FRG"
  db_recovery_file_dest_size= 4815M
  undo_tablespace          = "UNDOTBS1"
  remote_login_passwordfile= "EXCLUSIVE"
  db_domain                = ""
  dispatchers              = "(PROTOCOL=TCP) (SERVICE=testdbXDB)"
  local_listener           = "LISTENER_TESTDB"
  utl_file_dir             = "/tmp"
  parallel_min_servers     = 4
  audit_file_dest          = "/home/oracle/app/oracle/admin/testdb/adump"
  audit_trail              = "NONE"
  db_name                  = "testdb"
  open_cursors             = 300
  pga_aggregate_target     = 290M
  diagnostic_dest          = "/home/oracle/app/oracle"
  enable_pluggable_database= TRUE
NOTE: remote asm mode is local (mode 0x1; from cluster type)
Starting background process PMON
Mon Jul 15 14:02:30 2019
PMON started with pid=2, OS id=6569
.
.
.
Starting background process CJQ0
Completed: alter database open
Mon Jul 15 14:03:52 2019
CJQ0 started with pid=47, OS id=6894
Mon Jul 15 14:04:08 2019
db_recovery_file_dest_size of 4815 MB is 3.57% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Mon Jul 15 14:04:18 2019

 

 

3. 컨트롤 파일 복사를 위해 디비 종료

 

sys@testdb> 
sys@testdb> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
sys@testdb> startup mount;
ORACLE instance started.

 

 

4. 에러가 발생한 컨트롤 파일 삭제

 

ASMCMD> 
ASMCMD> cd testdb
ASMCMD> ls
2AEC347502BE0A03E053B4AB2134F623/
2B011A2FEEAC384EE053B4AB213417DA/
CONTROLFILE/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
TEMPFILE/
ASMCMD> cd controlfile
ASMCMD> ls
current.257.996584973
ASMCMD> rm current.257.996584973          << 에러가 발생한 파일을 삭제
ASMCMD> ls -l
ASMCMD-8002: entry 'controlfile' does not exist in directory '+data/testdb/'
ASMCMD> ls
ASMCMD-8002: entry 'controlfile' does not exist in directory '+data/testdb/'
ASMCMD> cd ..
ls
ASMCMD> 2AEC347502BE0A03E053B4AB2134F623/
2B011A2FEEAC384EE053B4AB213417DA/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
TEMPFILE/
ASMCMD>      
ASMCMD> 
ASMCMD> 
ASMCMD> ls -l

 

 

5. Rman 으로 컨트롤 복구

 

testora.localdomain@oracle:testdb:/home/oracle> rman target=/

Recovery Manager: Release 12.1.0.2.0 - Production on Mon Jul 15 14:14:02 2019

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

connected to target database (not started)

RMAN> 

RMAN> startup nomount;             << Rman 에서 ASM 에 접근 하기 위해 디비를 nomount 단계로 올린다.

Oracle instance started

Total System Global Area     914358272 bytes

Fixed Size                     2930800 bytes
Variable Size                352323472 bytes
Database Buffers             553648128 bytes
Redo Buffers                   5455872 bytes

RMAN> restore controlfile to '+DATA' from '+FRG/TESTDB/CONTROLFILE/current.256.902932949';
       << 미러링 파일을 이용해 복구
Starting restore at 15-JUL-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=355 device type=DISK

channel ORA_DISK_1: copied control file copy
Finished restore at 15-JUL-19

RMAN> 
### ASM 에서 복사된 컨트롤 파일 확인

ASMCMD>
ASMCMD>
ASMCMD> cd testdb
ASMCMD-8002: entry 'testdb' does not exist in directory '+data/testdb/'
ASMCMD> ls
2AEC347502BE0A03E053B4AB2134F623/
2B011A2FEEAC384EE053B4AB213417DA/
CONTROLFILE/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
TEMPFILE/
ASMCMD> cd controlfile
ASMCMD> ls
current.257.1013696153
ASMCMD> ls -l
Type         Redund  Striped  Time             Sys  Name
CONTROLFILE  UNPROT  FINE     JUL 15 14:00:00  Y    current.257.1013696153
ASMCMD>
### asm to filesystem 컨트롤 파일 카피


RMAN> restore controlfile to '/home/oracle/app/oracle/datafile/controlfile/control03.ctl' from  '+FRG/TESTDB/CONTROLFILE/current.256.902932949';

Starting restore at 15-JUL-19
using channel ORA_DISK_1

channel ORA_DISK_1: copied control file copy
Finished restore at 15-JUL-19

RMAN>

## filesystem 에서 정상 복구 됐는지 확인

testora.localdomain@oracle:testdb:/home/oracle/app/oracle/datafile/controlfile> ls -l
⑷ 17584
-rw-r-----. 1 oracle dba 18006016 2019-07-15 14:36 control03.ctl
You have mail in /var/spool/mail/oracle
testora.localdomain@oracle:testdb:/home/oracle/app/oracle/datafile/controlfile>

 

 

 

6. 복구 된 컨트롤 파일 spfile 에 적용

 

alter system set control_files = '+DATA/TESTDB/CONTROLFILE/current.257.1013696153','+FRG/TESTDB/CONTROLFILE/current.256.902932949','/home/oracle/app/oracle/datafile/controlfile/control03.ctl' scope=spfile;

 

7. 정상 오픈 확인

 


Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options.
ORACLE_HOME = /home/oracle/app/oracle/product/12.1.0
System name:Linux
Node name:testora.localdomain
Release:2.6.32-573.el6.x86_64
Version:#1 SMP Thu Jul 23 15:44:03 UTC 2015
Machine:x86_64
Using parameter settings in server-side pfile /home/oracle/app/oracle/product/12.1.0/dbs/inittestdb.ora
System parameters with non-default values:
  processes                = 300
  spfile                   = "+DATA/testdb/PARAMETERFILE/spfile.269.902946971"
  nls_language             = "AMERICAN"
  nls_territory            = "AMERICA"
  sga_target               = 872M
  control_files            = "+DATA/TESTDB/CONTROLFILE/current.257.1013696153"
  control_files            = "+FRG/TESTDB/CONTROLFILE/current.256.902932949"
  control_files            = "/home/oracle/app/oracle/datafile/controlfile/control03.ctl"
< 파일 시스템 복구도 적용 확인
  db_block_size            = 8192
  compatible               = "12.1.0.2.0"
  db_create_file_dest      = "+DATA"
  db_recovery_file_dest    = "+FRG"
  db_recovery_file_dest_size= 4815M
  undo_tablespace          = "UNDOTBS1"
  remote_login_passwordfile= "EXCLUSIVE"
  db_domain                = ""
  dispatchers              = "(PROTOCOL=TCP) (SERVICE=testdbXDB)"
  local_listener           = "LISTENER_TESTDB"
  utl_file_dir             = "/tmp"
  parallel_min_servers     = 4
  audit_file_dest          = "/home/oracle/app/oracle/admin/testdb/adump"
  audit_trail              = "NONE"
  db_name                  = "testdb"
  open_cursors             = 300
  pga_aggregate_target     = 290M
  diagnostic_dest          = "/home/oracle/app/oracle"
  enable_pluggable_database= TRUE
NOTE: remote asm mode is local (mode 0x1; from cluster type)
2019-07-15 14:41:33.545000 +09:00
Starting background process PMON
PMON started with pid=2, OS id=8234
Starting background process PSP0
PSP0 started with pid=3, OS id=8236
Starting background process VKTM
2019-07-15 14:41:34.629000 +09:00
VKTM started with pid=4, OS id=8238 at elevated (RT) priority
Starting background process GEN0
VKTM running at (1)millisec precision with DBRM quantum (100)ms
GEN0 started with pid=5, OS id=8242
Starting background process MMAN
MMAN started with pid=6, OS id=8244
....
Successful mount of redo thread 1, with mount id 2796999978
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE   MOUNT
2019-07-15 14:42:21.509000 +09:00
alter database open
Ping without log force is disabled
.
2019-07-15 14:42:22.667000 +09:00
Starting background process TMON
TMON started with pid=29, OS id=8337
Thread 1 opened at log sequence 8775
  Current log# 3 seq# 8775 mem# 0: +DATA/TESTDB/ONLINELOG/group_3.260.902932961
  Current log# 3 seq# 8775 mem# 1: +FRG/TESTDB/ONLINELOG/group_3.259.902932965
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Network Resource Management enabled for Process LG00 (pid 8262) for Exadata I/O
SMON: enabling cache recovery
2019-07-15 14:42:26.235000 +09:00
[8314] Successfully onlined Undo Tablespace 2.
Network Resource Management enabled for Process LG01 (pid 8266) for Exadata I/O
Undo initialization finished serial:0 start:7462494 end:7464324 diff:1830 ms (1.8 seconds)
Verifying minimum file header compatibility (11g) for tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Starting background process SMCO
SMCO started with pid=33, OS id=8341
Database Characterset is KO16KSC5601
2019-07-15 14:42:27.702000 +09:00
No Resource Manager plan active
2019-07-15 14:42:30.516000 +09:00
replication_dependency_tracking turned off (no async multimaster replication found)
2019-07-15 14:42:31.968000 +09:00
Starting background process AQPC
AQPC started with pid=36, OS id=8347
2019-07-15 14:42:36.723000 +09:00
Database Characterset for PDB$SEED is KO16KSC5601
2019-07-15 14:42:40.817000 +09:00
Due to limited space in shared pool (need 6094848 bytes, have 3981120 bytes), limiting Resource Manager entities from 2048 to 3
2
2019-07-15 14:42:47.696000 +09:00
Opening pdb PDB$SEED (2) with no Resource Manager plan active
2019-07-15 14:42:51.905000 +09:00
alter pluggable database all open
2019-07-15 14:42:56.000000 +09:00
Database Characterset for PDB1 is KO16KSC5601
2019-07-15 14:43:02.649000 +09:00
Due to limited space in shared pool (need 6094848 bytes, have 3981120 bytes), limiting Resource Manager entities from 2048 to 3
2
2019-07-15 14:43:10.201000 +09:00
Shared IO Pool defaulting to 36MB. Trying to get it from Buffer Cache for process 8278.
2019-07-15 14:43:11.748000 +09:00
Opening pdb PDB1 (3) with no Resource Manager plan active
===========================================================
Dumping current patch information
===========================================================
No patches have been applied
===========================================================
2019-07-15 14:43:12.966000 +09:00
Pluggable database PDB1 opened read write
Completed: alter pluggable database all open
Starting background process CJQ0
CJQ0 started with pid=37, OS id=8517
Completed: alter database open
2019-07-15 14:43:34.610000 +09:00

 

 

참조 문서 : How to copy control file in ASM (문서 ID 2006213.1)

위로